package gudusoft.gsqlparser.dlineage.dynamicsql;

import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TCustomSqlStatement;
import gudusoft.gsqlparser.TStatementList;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Analysis-scoped index of routine DEFINITIONS and top-level candidate CALL-SITE
 * statements across the units of a multi-file analysis (plan §5.6, Phase 5
 * slice 3 — the seed of the shared routine/signature index).
 *
 * <p>Lazily built and memoized per unit text: a unit is parsed (through the
 * shared, caller-owned parse cache) and its statement list walked at most ONCE
 * per analysis, no matter how many procedures consult the catalog. Callers keep
 * their own cheap contains-prefilter, so units that never mention a routine of
 * interest are still never parsed at all — cost semantics are identical to the
 * previous per-procedure scans, minus the repeated walking.
 *
 * <p>The catalog only INDEXES; every matching, vetting, and ambiguity decision
 * stays in {@link DynamicSqlLineageResolver} so this refactor is behavior-
 * preserving by construction.
 *
 * <h3>How this differs from {@code TSQLEnv} / {@code TSQLProcedure}</h3>
 *
 * The sqlenv package already keeps a procedure registry
 * ({@code gudusoft.gsqlparser.sqlenv.TSQLProcedure} under {@code TSQLSchema},
 * with typed parameters and even the definition source string). The two serve
 * different layers and are NOT redundant:
 *
 * <ul>
 * <li><b>AST working set vs metadata registry.</b> {@code TSQLEnv} stores
 *     metadata — names, parameter TYPES, the definition as a STRING — and
 *     serves NAME RESOLUTION (resolver2/dlineage resolving {@code a.b.c}
 *     against catalog objects). This catalog stores live
 *     {@link gudusoft.gsqlparser.TCustomSqlStatement AST nodes} plus each
 *     statement's unit provenance and effective USE database/schema, because
 *     the dynamic-SQL binding machinery must hand the callee's AST to the
 *     evaluator/interpreter and extract literal arguments from caller ASTs.
 *     An env definition string would have to be re-parsed anyway — which is
 *     exactly what this catalog's parse cache does, while also indexing what
 *     the env has no concept of at all: <b>call sites</b>.</li>
 * <li><b>Duplicate visibility vs registry merging.</b> An env is keyed by
 *     qualified name, so re-registering a same-named routine merges or
 *     overwrites. The binding rules ("never guess") need the opposite: every
 *     definition OCCURRENCE with its origin unit must stay visible so a
 *     same-named duplicate can be detected and refused. The information a
 *     registry naturally collapses is precisely what the ambiguity verdicts
 *     here require.</li>
 * <li><b>No environment assumed.</b> dlineage frequently runs with no
 *     {@code TSQLEnv} attached (and env construction is option-gated); this
 *     catalog depends only on the analysis unit texts themselves.</li>
 * <li><b>Planned convergence.</b> Plan §5.6 promotes one routine/signature
 *     index shared by production dlineage and Analyzer v2. This class starts
 *     analysis-side because behavior preservation was the gate; unifying it
 *     with env registration and v2's {@code BoundProgram} is later Phase 5/6
 *     work.</li>
 * </ul>
 *
 * <h3>When metadata already contains stored-procedure definitions</h3>
 *
 * <ul>
 * <li><b>Metadata carrying routine SOURCE</b> (sqlflow JSON {@code sourceCode},
 *     grabit exports, DDL dumps) already flows in with no extra wiring: those
 *     definitions become ordinary analysis units, so this catalog indexes them
 *     like any other file while {@code SQLEnvParser} independently registers
 *     them for name resolution.</li>
 * <li><b>Metadata carrying only SIGNATURES</b> (name + parameter types, no
 *     body — the catalog-input-interface direction) cannot produce lineage
 *     THROUGH a body, but enables correct IN/OUT binding at call sites,
 *     overload disambiguation, and a "known external routine, effects unknown"
 *     diagnostic instead of a bare unresolved.</li>
 * <li><b>Future slice ("env-backed routine bodies"):</b> when an attached env
 *     holds a {@code TSQLRoutine} definition string for a routine that is NOT
 *     among the analysis units, this catalog could parse that definition as a
 *     virtual unit — covering the "caller file plus metadata-only definition"
 *     scenario.</li>
 * </ul>
 */
public final class RoutineCatalog {

    /**
     * B4a: everything needed to re-wrap ONE package member back into a
     * standalone, analyzable single-member package.
     *
     * <p>A member routine node carries its own token range, so its
     * {@code toString()} yields just {@code PROCEDURE foo(...) IS ... END foo}
     * — but that fragment is not a parseable top-level statement. Wrapping it
     * in a synthetic package that keeps the declaration section (globals) and
     * drops every sibling body is what makes per-member summarization possible:
     * globals stay in scope (arch-F3 boundary endpoints), while an absent
     * sibling makes an intra-package call surface as a raw call site, enforcing
     * no-inline-callee by construction exactly as {@code definitionSlice} does
     * for top-level routines.
     */
    static final class PackageContext {
        /** Qualified package name for the wrapper header. */
        final String qualifiedName;
        /** Simple package name for the trailing {@code END <name>;}. */
        final String endName;
        /** Concatenated non-routine declare elements (variables, cursors, types). */
        final String globalsText;

        PackageContext(String qualifiedName, String endName, String globalsText) {
            this.qualifiedName = qualifiedName;
            this.endName = endName;
            this.globalsText = globalsText;
        }
    }

    /** One routine definition found at the top level of a unit. */
    static final class Definition {
        final TCustomSqlStatement stmt;
        /** mssql: explicit three-part catalog or effective USE database; oracle: null. */
        final String db;
        /** oracle: schema segment of a qualified definition name; mssql: unused (null). */
        final String schema;
        final String simple;
        // ---- B0 additive signature/provenance fields (design §2.1a). The
        // legacy matching fields above keep their exact prior semantics; the
        // fields below are consumed only by summary-layer code (B2+), never
        // by the shipped resolver paths — behavior-preserving by construction.
        /** Kind discriminator; top-level definitions today are PROCEDURE/FUNCTION. */
        final RoutineKind kind;
        /** Raw package simple name for package members; null otherwise. */
        final String packageName;
        /** True for a package SPEC declaration (signature only, no body). */
        final boolean specOnly;
        /** Occurrence provenance: index of the defining statement in its unit. */
        final int statementIndex;
        /** Canonical signature descriptor (never used for legacy matching). */
        final RoutineIdentity identity;
        /** B4a: re-wrap context for package members; null for top-level defs. */
        final PackageContext pkgContext;

        Definition(TCustomSqlStatement stmt, String db, String schema, String simple,
                RoutineKind kind, String packageName, boolean specOnly,
                int statementIndex, RoutineIdentity identity) {
            this(stmt, db, schema, simple, kind, packageName, specOnly,
                    statementIndex, identity, null);
        }

        Definition(TCustomSqlStatement stmt, String db, String schema, String simple,
                RoutineKind kind, String packageName, boolean specOnly,
                int statementIndex, RoutineIdentity identity,
                PackageContext pkgContext) {
            this.stmt = stmt;
            this.db = db;
            this.schema = schema;
            this.simple = simple;
            this.kind = kind;
            this.packageName = packageName;
            this.specOnly = specOnly;
            this.statementIndex = statementIndex;
            this.identity = identity;
            this.pkgContext = pkgContext;
        }
    }

    /** One top-level non-definition statement — a potential caller. */
    static final class CallCandidate {
        final TCustomSqlStatement stmt;
        /** mssql: effective USE database at the statement; oracle: null. */
        final String db;

        CallCandidate(TCustomSqlStatement stmt, String db) {
            this.stmt = stmt;
            this.db = db;
        }
    }

    /** Everything the catalog knows about one unit. */
    static final class UnitIndex {
        final List<Definition> definitions = new ArrayList<Definition>();
        final List<CallCandidate> callCandidates = new ArrayList<CallCandidate>();
        /**
         * B0: Oracle package-member definitions (spec declarations and body
         * definitions), kept SEPARATE from {@link #definitions} so every
         * shipped resolver path sees exactly the pre-B0 view. The enclosing
         * package statement still appears in {@link #callCandidates} exactly
         * as before (its body can contain call sites). Summary-layer slices
         * (B2+) are the only consumers.
         */
        final List<Definition> memberDefinitions = new ArrayList<Definition>();
    }

    private final EDbVendor vendor;
    private final List<String> unitTexts;
    private final Map<String, TStatementList> parseCache;
    private final Map<String, UnitIndex> unitIndexes = new HashMap<String, UnitIndex>();

    public RoutineCatalog(EDbVendor vendor, List<String> unitTexts, Map<String, TStatementList> parseCache) {
        this.vendor = vendor;
        this.unitTexts = unitTexts;
        this.parseCache = parseCache;
    }

    /** The raw texts of every analysis unit, in analysis order (includes the caller's own). */
    List<String> unitTexts() {
        return unitTexts;
    }

    /**
     * Index of one unit; parses on first use (cached), walks the statement list on
     * first use (memoized). Null when the unit failed to parse — a failed unit's
     * statement boundaries are not trustworthy and contribute nothing.
     */
    UnitIndex indexOf(String unitText) {
        if (unitIndexes.containsKey(unitText)) {
            return unitIndexes.get(unitText);
        }
        TStatementList stmts;
        if (parseCache.containsKey(unitText)) {
            stmts = parseCache.get(unitText);
        } else {
            stmts = parseUnit(unitText);
            parseCache.put(unitText, stmts);
        }
        UnitIndex index = stmts == null ? null : buildIndex(stmts);
        unitIndexes.put(unitText, index);
        return index;
    }

    private UnitIndex buildIndex(TStatementList stmts) {
        UnitIndex index = new UnitIndex();
        String currentDb = null;
        for (int i = 0; i < stmts.size(); i++) {
            TCustomSqlStatement stmt = stmts.get(i);
            if (stmt instanceof gudusoft.gsqlparser.stmt.TUseDatabase) {
                currentDb = String.valueOf(
                        ((gudusoft.gsqlparser.stmt.TUseDatabase) stmt).getDatabaseName());
                continue;
            }
            Definition definition = definitionOf(stmt, currentDb, i);
            if (definition != null) {
                index.definitions.add(definition);
            } else {
                indexPackageMembers(stmt, i, index);
                index.callCandidates.add(new CallCandidate(stmt,
                        vendor == EDbVendor.dbvmssql ? currentDb : null));
            }
        }
        return index;
    }

    /** Vendor-specific definition extraction; null when {@code stmt} defines no routine. */
    private Definition definitionOf(TCustomSqlStatement stmt, String currentDb,
            int statementIndex) {
        if (vendor == EDbVendor.dbvmssql) {
            if (stmt instanceof gudusoft.gsqlparser.stmt.mssql.TMssqlCreateProcedure) {
                gudusoft.gsqlparser.stmt.mssql.TMssqlCreateProcedure proc =
                        (gudusoft.gsqlparser.stmt.mssql.TMssqlCreateProcedure) stmt;
                if (proc.getProcedureName() == null) {
                    return null;
                }
                String db = DynamicSqlLineageResolver.mssqlDefinitionDatabase(proc, currentDb);
                // B3 (codex-B3-r1 finding 6): deprecated numbered procedures
                // ("p;2") carry the ;N as the ratified overload discriminator
                // — without it dbo.p;1 and dbo.p;2 collapse into duplicates.
                // The parser DROPS ";N" from the AST name, so it is recovered
                // from the token stream right after the name; the text-based
                // helpers below stay as fallback for callers that carry it.
                String objectText = proc.getProcedureName().getObjectString();
                String discriminator = numberedGroupAfter(proc.getProcedureName());
                if (discriminator == null) {
                    discriminator = mssqlNumberedSuffixOf(objectText);
                }
                return new Definition(stmt, db, null,
                        DynamicSqlLineageResolver.simpleNameOf(proc.getProcedureName().toString()),
                        RoutineKind.PROCEDURE, null, false, statementIndex,
                        identityOf(RoutineKind.PROCEDURE, db,
                                proc.getProcedureName().getSchemaString(), null,
                                stripMssqlNumberedSuffix(objectText), stmt,
                                discriminator));
            }
            return null;
        }
        String simple = DynamicSqlLineageResolver.plsqlRoutineSimpleNameOf(stmt);
        if (simple == null) {
            return null;
        }
        RoutineKind kind =
                stmt instanceof gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateFunction
                        ? RoutineKind.FUNCTION : RoutineKind.PROCEDURE;
        String schema = DynamicSqlLineageResolver.plsqlRoutineSchemaOf(stmt);
        return new Definition(stmt, null, schema, simple, kind, null, false,
                statementIndex, identityOf(kind, null, schema, null, simple, stmt));
    }

    /**
     * B0: index Oracle package members (spec declarations and body
     * definitions) into {@link UnitIndex#memberDefinitions}. The package
     * statement itself is not consumed — it stays a call candidate exactly as
     * before, so shipped matching behavior is untouched.
     */
    private void indexPackageMembers(TCustomSqlStatement stmt, int statementIndex,
            UnitIndex index) {
        if (!(stmt instanceof gudusoft.gsqlparser.stmt.oracle.TPlsqlCreatePackage)) {
            return;
        }
        gudusoft.gsqlparser.stmt.oracle.TPlsqlCreatePackage pkg =
                (gudusoft.gsqlparser.stmt.oracle.TPlsqlCreatePackage) stmt;
        if (pkg.getPackageName() == null) {
            return;
        }
        String pkgSimple = pkg.getPackageName().getObjectString();
        String pkgSchema = pkg.getPackageName().getSchemaString();
        boolean specOnly =
                pkg.getKind() != gudusoft.gsqlparser.TBaseType.kind_create_body;
        if (pkg.getDeclareStatements() == null) {
            return;
        }
        // B4a: the declaration section is not exposed as a unit — derive it as
        // the complement of the routine filter below (variables, cursors, types,
        // pragmas). These are the package globals (arch-F3); they must travel
        // with every member slice or a member referencing one loses that
        // endpoint entirely.
        StringBuilder globals = new StringBuilder();
        for (int i = 0; i < pkg.getDeclareStatements().size(); i++) {
            Object decl = pkg.getDeclareStatements().get(i);
            if (decl instanceof gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateFunction
                    || decl instanceof gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateProcedure) {
                continue;
            }
            String declText = String.valueOf(decl);
            if (declText == null || declText.length() == 0
                    // non-identifier-compare: TParseTreeNode.toString() returns
                    // null (rendered "null" by valueOf) when token bounds are
                    // missing; such a declaration cannot be reproduced.
                    || "null".equals(declText)) {
                continue;
            }
            declText = declText.trim();
            globals.append("  ").append(declText);
            if (!declText.endsWith(";")) {
                globals.append(";");
            }
            globals.append("\n");
        }
        PackageContext pkgContext = new PackageContext(
                pkg.getPackageName().toString(), pkgSimple, globals.toString());
        for (int i = 0; i < pkg.getDeclareStatements().size(); i++) {
            Object member = pkg.getDeclareStatements().get(i);
            RoutineKind kind;
            String simple;
            if (member instanceof gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateFunction) {
                kind = RoutineKind.PACKAGE_MEMBER_FUNCTION;
                simple = DynamicSqlLineageResolver.plsqlRoutineSimpleNameOf(
                        (TCustomSqlStatement) member);
            } else if (member instanceof gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateProcedure) {
                kind = RoutineKind.PACKAGE_MEMBER_PROCEDURE;
                simple = DynamicSqlLineageResolver.plsqlRoutineSimpleNameOf(
                        (TCustomSqlStatement) member);
            } else {
                continue;
            }
            if (simple == null) {
                continue;
            }
            index.memberDefinitions.add(new Definition((TCustomSqlStatement) member,
                    null, pkgSchema, simple, kind, pkgSimple, specOnly, statementIndex,
                    identityOf(kind, null, pkgSchema, pkgSimple, simple,
                            (TCustomSqlStatement) member),
                    pkgContext));
        }
    }

    /** Identity of one definition; parameter shape read from the routine AST. */
    private RoutineIdentity identityOf(RoutineKind kind, String catalog, String schema,
            String packageName, String simple, TCustomSqlStatement stmt) {
        return identityOf(kind, catalog, schema, packageName, simple, stmt, null);
    }

    private RoutineIdentity identityOf(RoutineKind kind, String catalog, String schema,
            String packageName, String simple, TCustomSqlStatement stmt,
            String overloadDiscriminator) {
        gudusoft.gsqlparser.nodes.TParameterDeclarationList params =
                stmt instanceof gudusoft.gsqlparser.stmt.TStoredProcedureSqlStatement
                        ? ((gudusoft.gsqlparser.stmt.TStoredProcedureSqlStatement) stmt)
                                .getParameterDeclarations()
                        : null;
        return RoutineIdentity.of(vendor, kind, catalog, schema, packageName, simple,
                params, overloadDiscriminator);
    }

    /**
     * The {@code ;N} group of a deprecated numbered procedure, scanned from
     * the source tokens right after the name — the parser drops it from the
     * AST name entirely. {@code ;1} normalizes to null ({@code p} and
     * {@code p;1} are one identity in T-SQL).
     */
    public static String numberedGroupAfter(gudusoft.gsqlparser.nodes.TObjectName name) {
        if (name == null || name.getEndToken() == null) {
            return null;
        }
        gudusoft.gsqlparser.TSourceToken semi = name.getEndToken().nextSolidToken();
        if (semi == null || !";".equals(semi.toString())) { // non-identifier-compare: punctuation token
            return null;
        }
        gudusoft.gsqlparser.TSourceToken num = semi.nextSolidToken();
        if (num == null) {
            return null;
        }
        return canonicalGroup(num.toString().trim());
    }

    /** Numeric canonical form of a group suffix: {@code ";01"} ≡ {@code ";1"}
     *  (codex-B3-r3 f6); group 1 ≡ the unnumbered procedure ⇒ null. */
    private static String canonicalGroup(String text) {
        if (text == null || text.isEmpty()) {
            return null;
        }
        for (int i = 0; i < text.length(); i++) {
            if (!Character.isDigit(text.charAt(i))) {
                return null;
            }
        }
        long value;
        try {
            value = Long.parseLong(text);
        } catch (NumberFormatException overflow) {
            return text; // absurdly long digit run: keep spelling, never crash
        }
        return value == 1 ? null : String.valueOf(value);
    }

    /** Trailing {@code ;N} of a deprecated numbered-procedure name, or null.
     *  {@code ;1} is the unnumbered procedure itself in T-SQL, so it
     *  normalizes to null — {@code p} and {@code p;1} are one identity. */
    static String mssqlNumberedSuffixOf(String objectText) {
        if (objectText == null) {
            return null;
        }
        int semi = objectText.lastIndexOf(';');
        if (semi < 0 || semi == objectText.length() - 1) {
            return null;
        }
        return canonicalGroup(objectText.substring(semi + 1).trim());
    }

    /** The name with any trailing numeric {@code ;N} removed (any spelling —
     *  {@code ;1}, {@code ;01}, {@code ;2} — the group itself is carried by
     *  {@link #mssqlNumberedSuffixOf}). */
    static String stripMssqlNumberedSuffix(String objectText) {
        if (objectText == null) {
            return null;
        }
        int semi = objectText.lastIndexOf(';');
        if (semi <= 0 || semi == objectText.length() - 1) {
            return objectText;
        }
        String suffix = objectText.substring(semi + 1).trim();
        for (int i = 0; i < suffix.length(); i++) {
            if (!Character.isDigit(suffix.charAt(i))) {
                return objectText;
            }
        }
        return objectText.substring(0, semi);
    }

    /** Parse of a unit's text; null when parsing failed or crashed. */
    private TStatementList parseUnit(String sqlText) {
        try {
            gudusoft.gsqlparser.TGSqlParser parser = new gudusoft.gsqlparser.TGSqlParser(vendor);
            parser.sqltext = sqlText;
            if (parser.parse() != 0) {
                return null;
            }
            return parser.getSqlstatements();
        } catch (RuntimeException ex) {
            return null;
        } catch (Error err) {
            if (err instanceof ThreadDeath) {
                throw err;
            }
            // StackOverflow on a pathological unit must not kill the analysis.
            return null;
        }
    }
}