package gudusoft.gsqlparser.dlineage.dynamicsql;

import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TCustomSqlStatement;
import gudusoft.gsqlparser.TSourceToken;
import gudusoft.gsqlparser.TStatementList;
import gudusoft.gsqlparser.dlineage.DataFlowAnalyzer;
import gudusoft.gsqlparser.dlineage.dataflow.model.Option;
import gudusoft.gsqlparser.dlineage.dataflow.model.xml.dataflow;
import gudusoft.gsqlparser.dlineage.dataflow.model.xml.relationship;
import gudusoft.gsqlparser.dlineage.dataflow.model.xml.sourceColumn;
import gudusoft.gsqlparser.dlineage.dataflow.model.xml.table;
import gudusoft.gsqlparser.dlineage.dataflow.model.xml.targetColumn;
import gudusoft.gsqlparser.nodes.TParameterDeclaration;
import gudusoft.gsqlparser.nodes.TParameterDeclarationList;
import gudusoft.gsqlparser.sqlenv.TSQLEnv;
import gudusoft.gsqlparser.stmt.mssql.TMssqlCreateProcedure;

import java.io.UnsupportedEncodingException;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

/**
 * Orchestrates dynamic-SQL lineage resolution: evaluate the procedure's
 * dynamic-SQL string building under the given bindings
 * ({@link TDynamicSqlStringEvaluator}), then for each materialized EXEC site run
 * ordinary dlineage on the concrete string and tag the resulting edges with
 * dynamic provenance.
 *
 * <p>This is purely additive — it constructs fresh, throwaway
 * {@link DataFlowAnalyzer} instances for the materialized strings and never
 * touches the default analysis path, so default dlineage output is unchanged.
 */
public final class DynamicSqlLineageResolver {

    private DynamicSqlLineageResolver() {
    }

    public static DynamicLineageResult resolve(TCustomSqlStatement procAst, EDbVendor vendor, TSQLEnv sqlEnv,
            String currentDatabase, String defaultSchema, Map<String, SqlValue> bindings,
            DynamicLineageOptions options) {

        if (options == null) {
            options = DynamicLineageOptions.createDefault();
        }
        if (bindings != null && bindings.size() > options.getMaxBindings()) {
            throw new IllegalArgumentException(
                    "binding count " + bindings.size() + " exceeds maxBindings " + options.getMaxBindings());
        }

        TStatementList body = extractBody(procAst);
        List<TParameterDeclaration> params = extractParams(procAst);
        String sourceProc = extractProcName(procAst);
        String bindingHash = hashBindings(bindings);

        DynamicLineageResult result = new DynamicLineageResult(sourceProc, bindingHash);
        if (body == null) {
            return result; // not a recognized procedure shape — nothing to evaluate
        }

        TDynamicSqlStringEvaluator evaluator = new TDynamicSqlStringEvaluator(vendor, options);
        evaluator.seedBindings(bindings);
        evaluator.seedParameterDefaults(params);

        List<TDynamicSqlStringEvaluator.RawSite> rawSites = evaluator.evaluate(body);
        for (TDynamicSqlStringEvaluator.RawSite raw : rawSites) {
            result.addSite(buildSite(raw, vendor, sqlEnv, currentDatabase, defaultSchema, sourceProc,
                    bindingHash, options));
        }
        // Never let a truncated walk look complete — emit an honest diagnostic.
        if (evaluator.isSitesTruncated()) {
            result.addSite(new DynamicSiteResult(DynamicSiteResult.Kind.EXEC_STRING,
                    DynamicSiteResult.Status.UNRESOLVED, null, null, sourceProc,
                    dynamicSiteId(sourceProc, "truncated"), bindingHash,
                    "site/time limit reached (maxSites=" + options.getMaxSites()
                            + ", timeout=" + options.getTimeoutMillis() + "ms); further sites dropped",
                    0L, 0L, new ArrayList<DynamicLineageEdge>()));
        }
        return result;
    }

    /* --------------------------------------------------------- site building */

    private static DynamicSiteResult buildSite(TDynamicSqlStringEvaluator.RawSite raw, EDbVendor vendor,
            TSQLEnv sqlEnv, String currentDatabase, String defaultSchema, String sourceProc, String bindingHash,
            DynamicLineageOptions options) {

        long[] pos = coordinate(raw.node);
        String dynamicSite = dynamicSiteId(sourceProc, raw.ordinalPath);
        SqlStringValue sql = raw.sql;
        // The EXEC site is wrapped in an IF whose predicate we cannot decide from
        // the bindings (e.g. IF @Debug = 0 EXEC(@sql) with @Debug unbound).
        boolean executionGated = !raw.controlFlowCertain;

        // Honest unresolved reasons — never guess.
        // Case (a): the dynamic string itself is not usable — NULL, opaque, or
        // assigned under an undecidable branch and collapsed to UNKNOWN. Its
        // *value* is untrustworthy, so suppress regardless of the gate.
        if (sql == null || !sql.usable()) {
            String reason = sql != null && sql.reason != null ? sql.reason
                    : executionGated ? "site guarded by an undecidable conditional"
                            : "dynamic SQL did not reduce to a concrete string";
            return unresolved(raw, dynamicSite, sourceProc, bindingHash, pos, reason);
        }
        // Case (b): the EXEC site is execution-gated, but the string itself is usable
        // (CONCRETE or PARTIAL) — only the *did-it-run* fact is uncertain, so we
        // degrade-don't-fail rather than dropping the lineage. We fall through to
        // analyzeResolvedSql either way: a fully-concrete gated site emits all its
        // edges (flagged CONDITIONAL below), and a PARTIAL gated site emits exactly
        // the placeholder-free subset a non-gated PARTIAL site would, suppressing only
        // the placeholder edges. The downstream status/reason block surfaces both the
        // placeholder suppression and the execution gate. (Case (a) — an unusable
        // string, NULL/UNKNOWN — was already returned UNRESOLVED above.)
        String resolvedSql = sql.text;
        if (resolvedSql.length() > options.getMaxResolvedSqlLength()) {
            return unresolved(raw, dynamicSite, sourceProc, bindingHash, pos,
                    "resolved SQL length " + resolvedSql.length() + " exceeds maxResolvedSqlLength");
        }

        String concreteHash = sha1Hex(resolvedSql);
        List<DynamicLineageEdge> edges;
        boolean parseError;
        boolean partialParseError;
        try {
            Analysis a = analyzeResolvedSql(resolvedSql, vendor, sqlEnv, currentDatabase, defaultSchema,
                    sourceProc, dynamicSite, bindingHash, options);
            edges = a.edges;
            // No lineage + recorded errors = parse failure (generateDataFlow()
            // does not throw on bad SQL). Errors WITH some lineage = a mixed batch
            // where one statement failed; keep the good edges but flag it honestly.
            parseError = a.hadErrors && a.edges.isEmpty();
            partialParseError = a.hadErrors && !a.edges.isEmpty();
        } catch (RuntimeException ex) {
            edges = new ArrayList<DynamicLineageEdge>();
            parseError = true;
            partialParseError = false;
        }

        boolean partialPlaceholders = sql.state == SqlStringValue.State.PARTIAL;

        DynamicSiteResult.Status status;
        if (parseError) {
            status = DynamicSiteResult.Status.PARSE_ERROR;
        } else if (executionGated) {
            // The undecidable gate dominates the status: whether the string is fully
            // concrete or PARTIAL, every emitted edge is reached only if the guarded
            // branch runs. CONDITIONAL keeps that "might not execute" caveat visible —
            // the strictly-stronger signal to preserve — and the reason below adds the
            // placeholder-suppression note when the string is also PARTIAL.
            status = DynamicSiteResult.Status.CONDITIONAL;
        } else if (partialPlaceholders) {
            status = DynamicSiteResult.Status.PARTIAL;
        } else {
            status = DynamicSiteResult.Status.RESOLVED;
        }

        // Compose the reason from every applicable fact rather than picking one — a
        // single site can be partial-parse AND placeholder-suppressed AND gated at
        // once, and a consumer needs all three to gauge how far to trust the edges.
        List<String> reasons = new ArrayList<String>();
        if (parseError) {
            reasons.add("materialized SQL failed to parse");
        } else if (partialParseError) {
            reasons.add("some statements in the materialized SQL failed to parse; emitted edges are from the parseable statements");
        }
        if (partialPlaceholders) {
            reasons.add("materialized SQL still contains unbound placeholder(s); placeholder edges suppressed");
        }
        if (executionGated) {
            boolean anyEdges = !edges.isEmpty();
            reasons.add("site reached through an undecidable conditional"
                    + (anyEdges ? " — emitted edges assume the guarded statement executed"
                            : " — no resolvable edges were produced"));
        }
        String reason = reasons.isEmpty() ? null : String.join("; ", reasons);

        return new DynamicSiteResult(raw.kind, status, resolvedSql, concreteHash, sourceProc, dynamicSite,
                bindingHash, reason, pos[0], pos[1], edges);
    }

    private static DynamicSiteResult unresolved(TDynamicSqlStringEvaluator.RawSite raw, String dynamicSite,
            String sourceProc, String bindingHash, long[] pos, String reason) {
        String text = raw.sql != null && raw.sql.usable() ? raw.sql.text : null;
        return new DynamicSiteResult(raw.kind, DynamicSiteResult.Status.UNRESOLVED, text,
                text != null ? sha1Hex(text) : null, sourceProc, dynamicSite, bindingHash, reason,
                pos[0], pos[1], new ArrayList<DynamicLineageEdge>());
    }

    /**
     * Shared "analyze a materialized dynamic-SQL string" routine: run ordinary
     * dlineage with the proc's database/schema context and lift each column edge
     * into a provenance-tagged {@link DynamicLineageEdge}.
     */
    /** Edges from analyzing one materialized string, plus whether dlineage recorded parse/analysis errors. */
    private static final class Analysis {
        final List<DynamicLineageEdge> edges;
        final boolean hadErrors;

        Analysis(List<DynamicLineageEdge> edges, boolean hadErrors) {
            this.edges = edges;
            this.hadErrors = hadErrors;
        }
    }

    private static Analysis analyzeResolvedSql(String resolvedSql, EDbVendor vendor,
            TSQLEnv sqlEnv, String currentDatabase, String defaultSchema, String sourceProc, String dynamicSite,
            String bindingHash, DynamicLineageOptions options) {

        Option opt = new Option();
        opt.setVendor(vendor);
        opt.setSimpleOutput(false);
        opt.setOutput(false);
        opt.setShowImplicitSchema(options.isShowImplicitSchema());
        if (currentDatabase != null && !currentDatabase.isEmpty()) {
            opt.setDefaultDatabase(currentDatabase);
        }
        if (defaultSchema != null && !defaultSchema.isEmpty()) {
            opt.setDefaultSchema(defaultSchema);
        }

        DataFlowAnalyzer nested = new DataFlowAnalyzer(resolvedSql, opt);
        if (sqlEnv != null) {
            nested.setSqlEnv(sqlEnv);
        }
        nested.generateDataFlow();
        dataflow df = nested.getDataFlow();

        List<DynamicLineageEdge> edges = new ArrayList<DynamicLineageEdge>();
        if (df == null) {
            return new Analysis(edges, true);
        }
        boolean hadErrors = df.getErrors() != null && !df.getErrors().isEmpty();
        if (df.getRelationships() == null) {
            return new Analysis(edges, hadErrors);
        }

        // Index the real tables/views by id so we can rebuild fully-qualified
        // names from the structured model and fill in the proc's database/schema
        // for names the dynamic SQL left unqualified.
        Map<String, table> realTables = new HashMap<String, table>();
        indexTables(realTables, df.getTables());
        indexTables(realTables, df.getViews());

        boolean showImplicit = options.isShowImplicitSchema();
        for (relationship rel : df.getRelationships()) {
            if (rel == null || rel.getTarget() == null || rel.getSources() == null) {
                continue;
            }
            String target = fullName(rel.getTarget(), realTables, currentDatabase, defaultSchema, showImplicit);
            // An edge touching an unresolved placeholder (a name still carrying a
            // @variable from an unbound binding) must NOT be emitted as resolved
            // lineage — it would be a guess. The site stays PARTIAL and its
            // resolvedSql still shows the placeholder; only the fully-resolved
            // edges flow out.
            if (target == null || referencesPlaceholder(target)) {
                continue;
            }
            for (sourceColumn src : rel.getSources()) {
                if (isSystem(src)) {
                    continue;
                }
                String source = fullName(src, realTables, currentDatabase, defaultSchema, showImplicit);
                if (source == null || referencesPlaceholder(source)) {
                    continue;
                }
                edges.add(new DynamicLineageEdge(source, target, rel.getEffectType(), rel.getType(),
                        sourceProc, dynamicSite, bindingHash));
            }
        }
        return new Analysis(edges, hadErrors);
    }

    /** A resolved object name never legitimately contains '@'; if it does, it still holds an unbound placeholder. */
    private static boolean referencesPlaceholder(String name) {
        return name != null && name.indexOf('@') >= 0;
    }

    /* ------------------------------------------------------------- helpers */

    private static void indexTables(Map<String, table> index, List<table> tables) {
        if (tables == null) {
            return;
        }
        for (table t : tables) {
            if (t != null && t.getId() != null) {
                index.put(t.getId(), t);
            }
        }
    }

    private static String fullName(targetColumn c, Map<String, table> realTables, String currentDatabase,
            String defaultSchema, boolean showImplicit) {
        if (c == null || "system".equals(c.getSource())) {
            return null;
        }
        return joinName(c.getParent_id(), c.getParent_name(), c.getColumn(), realTables, currentDatabase,
                defaultSchema, showImplicit);
    }

    private static String fullName(sourceColumn c, Map<String, table> realTables, String currentDatabase,
            String defaultSchema, boolean showImplicit) {
        return joinName(c.getParent_id(), c.getParent_name(), c.getColumn(), realTables, currentDatabase,
                defaultSchema, showImplicit);
    }

    private static boolean isSystem(sourceColumn c) {
        return c == null || "system".equals(c.getSource()) || "RelationRows".equals(c.getColumn());
    }

    private static String joinName(String parentId, String parentName, String column,
            Map<String, table> realTables, String currentDatabase, String defaultSchema, boolean showImplicit) {
        if (column == null) {
            return null;
        }
        return qualifiedParent(parentId, parentName, realTables, currentDatabase, defaultSchema, showImplicit)
                + "." + stripBrackets(column);
    }

    /**
     * Build the qualified parent name from the real-table model. dlineage may
     * return the name already qualified (db.schema.table), partly qualified
     * (schema.table), bare (table), or with an empty schema slot (db..table). We
     * normalize per-segment brackets positionally — last = table, then schema,
     * then database — and, only when {@code showImplicit} is on, fill a missing
     * schema/database from the proc's {@code defaultSchema}/{@code currentDatabase}
     * so an unqualified {@code RECON_JE_02_QSP_…} binds to
     * {@code JOURNALENGINE.dbo.RECON_JE_02_QSP_…}. Intermediate result sets
     * (no model table) keep their display name unchanged.
     */
    private static String qualifiedParent(String parentId, String parentName, Map<String, table> realTables,
            String currentDatabase, String defaultSchema, boolean showImplicit) {
        table t = parentId == null ? null : realTables.get(parentId);
        if (t == null) {
            return stripBrackets(parentName); // result set / function / intermediate node
        }
        String full = t.getFullName() != null ? t.getFullName() : t.getName();
        if (full == null || full.isEmpty()) {
            full = parentName;
        }
        if (full == null) {
            return stripBrackets(parentName);
        }

        // Keep empty segments so positions are preserved (e.g. db..table).
        String[] raw = full.split("\\.", -1);
        for (int i = 0; i < raw.length; i++) {
            raw[i] = stripBrackets(raw[i].trim());
        }
        int n = raw.length;
        String table = n >= 1 ? raw[n - 1] : stripBrackets(parentName);
        String schema = n >= 2 ? raw[n - 2] : "";
        String db = n >= 3 ? raw[n - 3] : "";
        // Anything left of the database segment (e.g. linked-server) is kept as a prefix.
        StringBuilder prefix = new StringBuilder();
        for (int i = 0; i < n - 3; i++) {
            if (!raw[i].isEmpty()) {
                prefix.append(raw[i]).append('.');
            }
        }
        if (showImplicit) {
            if (schema.isEmpty()) {
                schema = stripBrackets(defaultSchema);
            }
            if (db.isEmpty()) {
                db = stripBrackets(currentDatabase);
            }
        }
        StringBuilder sb = new StringBuilder(prefix.toString());
        if (db != null && !db.isEmpty()) {
            sb.append(db).append('.');
        }
        if (schema != null && !schema.isEmpty()) {
            sb.append(schema).append('.');
        }
        sb.append(table == null ? "" : table);
        return sb.length() == 0 ? stripBrackets(parentName) : sb.toString();
    }

    private static TStatementList extractBody(TCustomSqlStatement procAst) {
        if (procAst instanceof TMssqlCreateProcedure) {
            return ((TMssqlCreateProcedure) procAst).getBodyStatements();
        }
        return null;
    }

    private static List<TParameterDeclaration> extractParams(TCustomSqlStatement procAst) {
        List<TParameterDeclaration> out = new ArrayList<TParameterDeclaration>();
        if (procAst instanceof TMssqlCreateProcedure) {
            TParameterDeclarationList list = ((TMssqlCreateProcedure) procAst).getParameterDeclarations();
            if (list != null) {
                for (int i = 0; i < list.size(); i++) {
                    out.add(list.getParameterDeclarationItem(i));
                }
            }
        }
        return out;
    }

    private static String extractProcName(TCustomSqlStatement procAst) {
        if (procAst instanceof TMssqlCreateProcedure) {
            TMssqlCreateProcedure cp = (TMssqlCreateProcedure) procAst;
            if (cp.getProcedureName() != null) {
                return cp.getProcedureName().toString();
            }
        }
        return procAst == null ? "" : procAst.getClass().getSimpleName();
    }

    private static long[] coordinate(TCustomSqlStatement node) {
        try {
            TSourceToken t = node.getStartToken();
            if (t != null) {
                return new long[] { t.lineNo, t.columnNo };
            }
        } catch (Throwable ignore) {
            // fall through
        }
        return new long[] { 0L, 0L };
    }

    /** Stable id robust to reformatting: proc identity + structural ordinal path, hashed. */
    private static String dynamicSiteId(String sourceProc, String ordinalPath) {
        return "dyn:" + sha1Hex(stripBrackets(sourceProc) + "#" + ordinalPath).substring(0, 12);
    }

    private static String hashBindings(Map<String, SqlValue> bindings) {
        if (bindings == null || bindings.isEmpty()) {
            return sha1Hex("");
        }
        TreeMap<String, String> sorted = new TreeMap<String, String>();
        for (Map.Entry<String, SqlValue> e : bindings.entrySet()) {
            if (e.getKey() == null) {
                continue;
            }
            sorted.put(stripBrackets(e.getKey().trim()).toLowerCase(),
                    e.getValue() == null ? "NULL" : e.getValue().toString());
        }
        StringBuilder sb = new StringBuilder();
        for (Map.Entry<String, String> e : sorted.entrySet()) {
            sb.append(e.getKey()).append('=').append(e.getValue()).append(';');
        }
        return sha1Hex(sb.toString());
    }

    private static String stripBrackets(String s) {
        if (s == null) {
            return "";
        }
        s = s.trim();
        if (s.length() >= 2) {
            char a = s.charAt(0);
            char z = s.charAt(s.length() - 1);
            if ((a == '[' && z == ']') || (a == '"' && z == '"') || (a == '`' && z == '`')) {
                return s.substring(1, s.length() - 1);
            }
        }
        return s;
    }

    private static String sha1Hex(String input) {
        try {
            MessageDigest md = MessageDigest.getInstance("SHA-1");
            byte[] digest = md.digest(input.getBytes("UTF-8"));
            StringBuilder sb = new StringBuilder(digest.length * 2);
            for (byte b : digest) {
                sb.append(Character.forDigit((b >> 4) & 0xF, 16));
                sb.append(Character.forDigit(b & 0xF, 16));
            }
            return sb.toString();
        } catch (NoSuchAlgorithmException | UnsupportedEncodingException e) {
            return Integer.toHexString(input.hashCode());
        }
    }
}