package gudusoft.gsqlparser.ir.builder.oracle;

import gudusoft.gsqlparser.EExpressionType;
import gudusoft.gsqlparser.TBaseType;
import gudusoft.gsqlparser.TCustomSqlStatement;
import gudusoft.gsqlparser.TStatementList;
import gudusoft.gsqlparser.analyzer.v2.AnalyzerV2Config;
import gudusoft.gsqlparser.ir.bound.*;
import gudusoft.gsqlparser.ir.common.Confidence;
import gudusoft.gsqlparser.ir.common.Evidence;
import gudusoft.gsqlparser.ir.common.EvidenceKind;
import gudusoft.gsqlparser.ir.common.SourceAnchor;
import gudusoft.gsqlparser.ir.builder.IBoundIRBuilder;
import gudusoft.gsqlparser.nodes.TExpression;
import gudusoft.gsqlparser.nodes.TObjectName;
import gudusoft.gsqlparser.nodes.TParseTreeVisitor;
import gudusoft.gsqlparser.stmt.TAssignStmt;
import gudusoft.gsqlparser.stmt.TCommonBlock;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateFunction;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlCreatePackage;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateProcedure;

import java.util.*;

/**
 * Oracle PL/SQL implementation of {@link IBoundIRBuilder}.
 * <p>
 * Orchestrates three internal phases:
 * <ol>
 *   <li>Create global scope, iterate statements</li>
 *   <li>Run {@link PlsqlSymbolCollector} (visitor) to extract routines, variables, cursors, calls</li>
 *   <li>Run {@link RoutineRefResolver} to match {@code BoundRoutineRef} to {@code BoundRoutineSymbol}</li>
 * </ol>
 * <p>
 * Leverages TSQLResolver2's already-populated results (auto-invoked by {@code parse()})
 * for table/column resolution, and supplements with PL/SQL-specific symbol extraction.
 */
public class OracleBoundIRBuilder implements IBoundIRBuilder {

    @Override
    public BoundProgram build(TStatementList stmts, AnalyzerV2Config config) {
        BoundProgram program = new BoundProgram();

        // Phase 1: Create global scope
        BoundScope globalScope = new BoundScope(EScopeKind.GLOBAL, null, null);
        program.addScope(globalScope);

        if (stmts == null || stmts.size() == 0) {
            return program;
        }

        // Phase 2: Walk AST with PlsqlSymbolCollector
        PlsqlSymbolCollector collector = new PlsqlSymbolCollector(program, globalScope, config);
        for (int i = 0; i < stmts.size(); i++) {
            TCustomSqlStatement stmt = stmts.get(i);
            if (stmt != null) {
                stmt.acceptChildren(collector);
            }
        }

        // Phase 2b: Merge forward declarations with implementations.
        // In PL/SQL package bodies, a forward declaration (no body) followed by its
        // implementation produces two routine entries differing only in name case.
        // Keep only the implementation.
        mergeForwardDeclarations(program);

        // Phase 2c: Detect parameterless function calls in assignment RHS.
        // PL/SQL allows calling zero-param functions without parentheses (v := func_name;).
        // These appear as simple_object_name_t expressions, not TFunctionCall nodes.
        // We need the complete routine table (after Phase 2b) to distinguish them from variables.
        detectParameterlessFunctionCalls(program, stmts);

        // Phase 3: Resolve routine references
        RoutineRefResolver.resolve(program);

        return program;
    }

    /**
     * Walks the AST a second time to find assignments where the RHS is a bare identifier
     * that matches a declared function name. Creates BoundRoutineRef entries for these.
     */
    private void detectParameterlessFunctionCalls(BoundProgram program, TStatementList stmts) {
        // Build set of function names (case-insensitive)
        Set<String> functionNames = new HashSet<String>();
        for (BoundRoutineSymbol routine : program.getRoutineIndex().values()) {
            ERoutineKind kind = routine.getRoutineKind();
            if (kind == ERoutineKind.FUNCTION || kind == ERoutineKind.NESTED_FUNCTION) {
                functionNames.add(routine.getRoutineName().toUpperCase());
            }
        }
        if (functionNames.isEmpty()) {
            return;
        }

        // Collect existing routine ref names to avoid duplicates
        Set<String> existingRefKeys = new HashSet<String>();
        for (BoundRoutineRef ref : program.getAllRoutineRefs()) {
            String owning = ref.getProperty("owningRoutineId");
            existingRefKeys.add((owning != null ? owning : "") + "|" + ref.getOriginalText().toUpperCase());
        }

        ParameterlessFuncDetector detector = new ParameterlessFuncDetector(
                program, functionNames, existingRefKeys);
        for (int i = 0; i < stmts.size(); i++) {
            TCustomSqlStatement stmt = stmts.get(i);
            if (stmt != null) {
                stmt.acceptChildren(detector);
            }
        }
    }

    /**
     * Scans the routine index for case-insensitive duplicates within the same
     * package and parameter count (forward declaration vs. implementation).
     * Removes the earlier entry (forward declaration) and keeps the later one (implementation).
     */
    private void mergeForwardDeclarations(BoundProgram program) {
        Map<String, BoundRoutineSymbol> routineIndex = program.getRoutineIndex();
        if (routineIndex.size() <= 1) {
            return;
        }

        // Group by case-insensitive routineId
        Map<String, List<String>> ciGroups = new LinkedHashMap<String, List<String>>();
        for (String routineId : routineIndex.keySet()) {
            String ciKey = routineId.toUpperCase();
            List<String> group = ciGroups.get(ciKey);
            if (group == null) {
                group = new ArrayList<String>();
                ciGroups.put(ciKey, group);
            }
            group.add(routineId);
        }

        // For groups with duplicates, keep the last entry (implementation) and remove others
        for (List<String> group : ciGroups.values()) {
            if (group.size() > 1) {
                // Keep the last entry (implementation comes after forward declaration in source)
                for (int i = 0; i < group.size() - 1; i++) {
                    program.unregisterRoutine(group.get(i));
                }
            }
        }
    }

    /**
     * Visitor that tracks routine context and detects parameterless function
     * calls across all expression contexts (assignments, IF conditions, SQL, etc.).
     * <p>
     * PL/SQL allows calling zero-param functions without parentheses. These appear
     * as simple_object_name_t or object_access_t expressions rather than TFunctionCall
     * nodes. This detector matches such expressions against the collected function
     * name set to identify them as calls.
     */
    private static class ParameterlessFuncDetector extends TParseTreeVisitor {

        private final BoundProgram program;
        private final Set<String> functionNames;
        private final Set<String> existingRefKeys;
        private final Deque<String> routineIdStack = new ArrayDeque<String>();
        private String currentRoutineId;
        private String currentPackageName;
        /** Tracks whether each TCommonBlock preVisit pushed a routine. */
        private final Deque<Boolean> commonBlockPushedRoutine = new ArrayDeque<Boolean>();

        /** Known Oracle pseudo-columns to skip. */
        private static boolean isOraclePseudoColumn(String name) {
            switch (name.toUpperCase()) {
                case "SYSDATE": case "SYSTIMESTAMP":
                case "CURRENT_DATE": case "CURRENT_TIMESTAMP": case "LOCALTIMESTAMP":
                case "USER": case "UID":
                case "ROWNUM": case "ROWID":
                case "LEVEL":
                case "SQLCODE": case "SQLERRM":
                case "TRUE": case "FALSE":
                case "NULL":
                    return true;
                default:
                    return false;
            }
        }

        ParameterlessFuncDetector(BoundProgram program, Set<String> functionNames,
                                   Set<String> existingRefKeys) {
            this.program = program;
            this.functionNames = functionNames;
            this.existingRefKeys = existingRefKeys;
        }

        // ---- Package tracking ----

        @Override
        public void preVisit(TPlsqlCreatePackage node) {
            TObjectName pkgName = node.getPackageName();
            String name = pkgName != null ? pkgName.toString().trim() : "";
            int dot = name.lastIndexOf('.');
            if (dot >= 0) {
                name = name.substring(dot + 1);
            }
            currentPackageName = name;
        }

        @Override
        public void postVisit(TPlsqlCreatePackage node) {
            currentPackageName = null;
        }

        // ---- Routine tracking ----

        @Override
        public void preVisit(TPlsqlCreateProcedure node) {
            String name = node.getProcedureName() != null
                    ? node.getProcedureName().toString().trim() : "";
            int dot = name.lastIndexOf('.');
            if (dot >= 0) {
                name = name.substring(dot + 1);
            }
            boolean isNested = node.getKind() != TBaseType.kind_create;
            String kind = isNested ? "NP" : "P";
            int paramCount = node.getParameterDeclarations() != null
                    ? node.getParameterDeclarations().size() : 0;
            pushRoutineId(buildRoutineId(name, kind, paramCount));
        }

        @Override
        public void postVisit(TPlsqlCreateProcedure node) {
            popRoutineId();
        }

        @Override
        public void preVisit(TPlsqlCreateFunction node) {
            String name = node.getFunctionName() != null
                    ? node.getFunctionName().toString().trim() : "";
            int dot = name.lastIndexOf('.');
            if (dot >= 0) {
                name = name.substring(dot + 1);
            }
            boolean isNested = node.getKind() != TBaseType.kind_create;
            String kind = isNested ? "NF" : "F";
            int paramCount = node.getParameterDeclarations() != null
                    ? node.getParameterDeclarations().size() : 0;
            pushRoutineId(buildRoutineId(name, kind, paramCount));
        }

        @Override
        public void postVisit(TPlsqlCreateFunction node) {
            popRoutineId();
        }

        @Override
        public void preVisit(TCommonBlock node) {
            // Anonymous blocks and nested routines
            if (node.getDeclareStatements() != null || node.getBodyStatements() != null) {
                if (currentRoutineId == null) {
                    pushRoutineId("<anonymous>/A(0)");
                    commonBlockPushedRoutine.push(true);
                } else {
                    commonBlockPushedRoutine.push(false);
                }
            } else {
                commonBlockPushedRoutine.push(false);
            }
        }

        @Override
        public void postVisit(TCommonBlock node) {
            if (!commonBlockPushedRoutine.isEmpty() && commonBlockPushedRoutine.pop()) {
                popRoutineId();
            }
        }

        // ---- Expression-level detection ----

        @Override
        public void preVisit(TExpression node) {
            if (currentRoutineId == null) {
                return;
            }
            EExpressionType exprType = node.getExpressionType();
            if (exprType == EExpressionType.simple_object_name_t) {
                checkBareIdentifier(node);
            } else if (exprType == EExpressionType.object_access_t) {
                checkQualifiedIdentifier(node);
            }
        }

        /**
         * Checks an identifier (simple_object_name_t) against known function names.
         * Handles both bare names ({@code get_value}) and dotted names ({@code pkg.get_value}).
         * For dotted names, checks the terminal part against the function name set.
         */
        private void checkBareIdentifier(TExpression expr) {
            String name = expr.toString().trim();
            if (name.isEmpty()) {
                return;
            }
            int dot = name.lastIndexOf('.');
            if (dot >= 0) {
                // Dotted name (e.g., pkg.func) — check terminal part
                String terminal = name.substring(dot + 1).trim();
                if (terminal.isEmpty() || isOraclePseudoColumn(terminal)) {
                    return;
                }
                if (!functionNames.contains(terminal.toUpperCase())) {
                    return;
                }
            } else {
                // Bare name — check directly
                if (isOraclePseudoColumn(name)) {
                    return;
                }
                if (!functionNames.contains(name.toUpperCase())) {
                    return;
                }
            }
            addRefIfNew(name, PlsqlSymbolCollector.splitName(name), expr);
        }

        /**
         * Checks a qualified identifier (object_access_t like pkg.func) where the
         * terminal part matches a known function name. The full qualified name is
         * used as the ref text so the resolver can match it.
         */
        private void checkQualifiedIdentifier(TExpression expr) {
            String qualName = expr.toString().trim();
            if (qualName.isEmpty()) {
                return;
            }
            int dot = qualName.lastIndexOf('.');
            if (dot < 0) {
                return;
            }
            String terminal = qualName.substring(dot + 1).trim();
            if (terminal.isEmpty() || isOraclePseudoColumn(terminal)) {
                return;
            }
            if (!functionNames.contains(terminal.toUpperCase())) {
                return;
            }
            addRefIfNew(qualName, PlsqlSymbolCollector.splitName(qualName), expr);
        }

        private void addRefIfNew(String name, List<String> nameParts, TExpression expr) {
            String refKey = (currentRoutineId != null ? currentRoutineId : "")
                    + "|" + name.toUpperCase();
            if (existingRefKeys.contains(refKey)) {
                return;
            }
            existingRefKeys.add(refKey);

            BoundRoutineRef ref = new BoundRoutineRef(
                    name, nameParts,
                    EBindingStatus.UNRESOLVED_SOFT, null,
                    null, Collections.<BoundArgument>emptyList(),
                    new Evidence(EvidenceKind.STATIC_RESOLVED,
                            "Parameterless function call"),
                    Confidence.HIGH);
            ref.setSourceAnchor(SourceAnchor.from(expr));
            if (currentRoutineId != null) {
                ref.setProperty("owningRoutineId", currentRoutineId);
            }
            program.addRoutineRef(ref);
        }

        // ---- Helpers ----

        private String buildRoutineId(String name, String kindCode, int paramCount) {
            StringBuilder sb = new StringBuilder();
            if (currentPackageName != null && !currentPackageName.isEmpty()) {
                sb.append(currentPackageName).append('.');
            }
            sb.append(name).append('/').append(kindCode).append('(').append(paramCount).append(')');
            return sb.toString();
        }

        private void pushRoutineId(String routineId) {
            routineIdStack.push(routineId);
            currentRoutineId = routineId;
        }

        private void popRoutineId() {
            if (!routineIdStack.isEmpty()) {
                routineIdStack.pop();
            }
            currentRoutineId = routineIdStack.isEmpty() ? null : routineIdStack.peek();
        }
    }
}