package gudusoft.gsqlparser.ir.builder.mssql;

import gudusoft.gsqlparser.*;
import gudusoft.gsqlparser.analyzer.v2.AnalyzerV2Config;
import gudusoft.gsqlparser.ir.bound.*;
import gudusoft.gsqlparser.ir.common.*;
import gudusoft.gsqlparser.nodes.*;
import gudusoft.gsqlparser.stmt.*;
import gudusoft.gsqlparser.stmt.mssql.*;

import java.util.*;

/**
 * T-SQL AST visitor that collects routine declarations, call references,
 * variable declarations, and table references from SQL Server scripts.
 * <p>
 * Extends {@link TParseTreeVisitor} and uses preVisit/postVisit pairs
 * for scope-creating nodes and preVisit only for leaf collection.
 */
public class TsqlSymbolCollector extends TParseTreeVisitor {

    /** T-SQL XML data type methods and .WRITE() column update method.
     *  When these appear as something.method(), they are NOT user-defined function calls. */
    private static final Set<String> DOT_METHOD_NAMES = new HashSet<String>();
    static {
        // XML data type methods
        DOT_METHOD_NAMES.add("NODES");
        DOT_METHOD_NAMES.add("VALUE");
        DOT_METHOD_NAMES.add("QUERY");
        DOT_METHOD_NAMES.add("EXIST");
        DOT_METHOD_NAMES.add("MODIFY");
        // .WRITE() partial column update
        DOT_METHOD_NAMES.add("WRITE");
    }

    private final BoundProgram program;
    private final Deque<BoundScope> scopeStack;
    private final Deque<String> routineIdStack;
    private final Set<TFunctionCall> visitedFunctionCalls;
    private final AnalyzerV2Config config;
    private final String fileId;
    private int batchCounter = 0;

    public TsqlSymbolCollector(BoundProgram program, BoundScope globalScope,
                               AnalyzerV2Config config, String fileId) {
        this.program = program;
        this.scopeStack = new ArrayDeque<BoundScope>();
        this.routineIdStack = new ArrayDeque<String>();
        this.visitedFunctionCalls = new HashSet<TFunctionCall>();
        this.config = config;
        this.fileId = fileId != null ? fileId : "";

        scopeStack.push(globalScope);
    }

    // ---- Scope helpers ----

    private BoundScope currentScope() {
        return scopeStack.peek();
    }

    private BoundScope pushScope(EScopeKind kind, SourceAnchor anchor) {
        BoundScope scope = new BoundScope(kind, currentScope(), anchor);
        scopeStack.push(scope);
        program.addScope(scope);
        return scope;
    }

    private void popScope() {
        if (scopeStack.size() > 1) {
            scopeStack.pop();
        }
    }

    private String currentRoutineId() {
        return routineIdStack.isEmpty() ? null : routineIdStack.peek();
    }

    private SourceAnchor anchor(TParseTreeNode node) {
        SourceAnchor base = SourceAnchor.from(node);
        if (base != null && !fileId.isEmpty()) {
            return new SourceAnchor(fileId,
                    base.startOffset, base.endOffset,
                    base.startLine, base.startCol,
                    base.endLine, base.endCol,
                    base.statementKey, base.snippet);
        }
        return base;
    }

    // ==========================================
    // Routine declaration visitors
    // ==========================================

    // ---- CREATE PROCEDURE ----
    // Note: TMssqlCreateProcedure extends TCreateProcedureStmt.
    // The visitor dispatches to TCreateProcedureStmt, not TMssqlCreateProcedure.

    @Override
    public void preVisit(TCreateProcedureStmt node) {
        // Only handle MSSQL procedures
        if (node.dbvendor != EDbVendor.dbvmssql) return;

        TObjectName procName = node.getStoredProcedureName();
        if (procName == null) return;

        String rawName = procName.toString().trim();
        MssqlNameNormalizer.NormalizedName normalized = MssqlNameNormalizer.normalize(rawName);

        String schema = normalized.getSchema();
        String name = normalized.getObject();

        List<BoundParameterSymbol> params = extractParameters(node.getParameterDeclarations());

        BoundScope routineScope = pushScope(EScopeKind.ROUTINE, anchor(node));

        BoundRoutineSymbol routine = new BoundRoutineSymbol(
                name.toUpperCase(), schema != null ? schema.toUpperCase() : null,
                routineScope, anchor(node), params, null, ERoutineKind.PROCEDURE);
        program.registerRoutine(routine);
        routineIdStack.push(routine.getRoutineId());

        // Register params in scope
        for (BoundParameterSymbol p : params) {
            routineScope.declareSymbol(p.getParamName(), p);
        }
    }

    @Override
    public void postVisit(TCreateProcedureStmt node) {
        if (node.dbvendor != EDbVendor.dbvmssql) return;
        if (!routineIdStack.isEmpty()) routineIdStack.pop();
        popScope();
    }

    // ---- CREATE FUNCTION ----

    @Override
    public void preVisit(TCreateFunctionStmt node) {
        if (!(node instanceof TMssqlCreateFunction)) return;

        TObjectName funcName = node.getFunctionName();
        if (funcName == null) return;

        String rawName = funcName.toString().trim();
        MssqlNameNormalizer.NormalizedName normalized = MssqlNameNormalizer.normalize(rawName);
        String schema = normalized.getSchema();
        String name = normalized.getObject();

        List<BoundParameterSymbol> params = extractParameters(node.getParameterDeclarations());

        ERoutineKind kind;
        EFunctionReturnsType returnsType = node.getReturnsType();
        if (returnsType == EFunctionReturnsType.frtInlineTableValue) {
            kind = ERoutineKind.FUNCTION; // IF
        } else if (returnsType == EFunctionReturnsType.frtMultiStatementTableValue) {
            kind = ERoutineKind.FUNCTION; // TF
        } else {
            kind = ERoutineKind.FUNCTION; // SF
        }

        BoundTypeRef returnType = MssqlTypeRefMapper.map(node.getReturnDataType());
        BoundScope routineScope = pushScope(EScopeKind.ROUTINE, anchor(node));

        BoundRoutineSymbol routine = new BoundRoutineSymbol(
                name.toUpperCase(), schema != null ? schema.toUpperCase() : null,
                routineScope, anchor(node), params, returnType, kind);

        // Note: function sub-type (SF/IF/TF) not stored on BoundRoutineSymbol
        // since it lacks a properties map. The kindCode() always returns "F".
        // Sub-type can be inferred from the source anchor if needed.

        program.registerRoutine(routine);
        routineIdStack.push(routine.getRoutineId());

        for (BoundParameterSymbol p : params) {
            routineScope.declareSymbol(p.getParamName(), p);
        }
    }

    @Override
    public void postVisit(TCreateFunctionStmt node) {
        if (!(node instanceof TMssqlCreateFunction)) return;
        if (!routineIdStack.isEmpty()) routineIdStack.pop();
        popScope();
    }

    // ---- CREATE TRIGGER ----
    // Note: MSSQL triggers parse as TCreateTriggerStmt, not TMssqlCreateTrigger.

    @Override
    public void preVisit(TCreateTriggerStmt node) {
        if (node.dbvendor != EDbVendor.dbvmssql) return;

        TObjectName triggerName = node.getTriggerName();
        if (triggerName == null) return;

        String rawName = triggerName.toString().trim();
        MssqlNameNormalizer.NormalizedName normalized = MssqlNameNormalizer.normalize(rawName);
        String schema = normalized.getSchema();
        String name = normalized.getObject();

        BoundScope routineScope = pushScope(EScopeKind.ROUTINE, anchor(node));

        BoundRoutineSymbol routine = new BoundRoutineSymbol(
                name.toUpperCase(), schema != null ? schema.toUpperCase() : null,
                routineScope, anchor(node),
                Collections.<BoundParameterSymbol>emptyList(), null, ERoutineKind.TRIGGER);

        // Track trigger table via object ref
        TTable onTable = node.getOnTable();
        if (onTable != null) {
            collectTableRef(onTable, null);
        }

        program.registerRoutine(routine);
        routineIdStack.push(routine.getRoutineId());

        // Register inserted/deleted pseudo tables in trigger scope
        routineScope.declareSymbol("INSERTED",
                new BoundVariableSymbol("INSERTED", routineScope, null, null, false));
        routineScope.declareSymbol("DELETED",
                new BoundVariableSymbol("DELETED", routineScope, null, null, false));
    }

    @Override
    public void postVisit(TCreateTriggerStmt node) {
        if (node.dbvendor != EDbVendor.dbvmssql) return;
        if (!routineIdStack.isEmpty()) routineIdStack.pop();
        popScope();
    }

    // ==========================================
    // Control flow / scope visitors
    // ==========================================

    @Override
    public void preVisit(TMssqlBlock node) {
        pushScope(EScopeKind.BLOCK, anchor(node));
    }

    @Override
    public void postVisit(TMssqlBlock node) {
        popScope();
    }

    @Override
    public void preVisit(TMssqlTryCatch node) {
        // TRY...CATCH treated as a single scope unit; calls inside
        // inherit the current routine context (owningRoutineId).
        // No extra scope push needed — the body statements are visited normally.
    }

    // ==========================================
    // Variable declaration
    // ==========================================

    @Override
    public void preVisit(TMssqlDeclare node) {
        if (node.getVariables() != null) {
            TDeclareVariableList vars = node.getVariables();
            for (int i = 0; i < vars.size(); i++) {
                TDeclareVariable var = vars.getDeclareVariable(i);
                if (var.getVariableName() != null) {
                    String varName = var.getVariableName().toString().trim();
                    BoundTypeRef typeRef = var.getDatatype() != null
                            ? MssqlTypeRefMapper.map(var.getDatatype()) : null;
                    BoundVariableSymbol varSym = new BoundVariableSymbol(
                            varName, currentScope(), anchor(node), typeRef, false);
                    currentScope().declareSymbol(varName.toUpperCase(), varSym);
                }
            }
        }
        // Cursor declaration
        if (node.getDeclareType() == EDeclareType.cursor && node.getCursorName() != null) {
            String cursorName = node.getCursorName().toString().trim();
            BoundVariableSymbol cursorSym = new BoundVariableSymbol(
                    cursorName, currentScope(), anchor(node),
                    new BoundTypeRef("CURSOR", ETypeCategory.REF_CURSOR), false);
            currentScope().declareSymbol(cursorName.toUpperCase(), cursorSym);
        }
    }

    // ==========================================
    // Call reference collection
    // ==========================================

    // ---- EXEC / EXECUTE ----

    @Override
    public void preVisit(TMssqlExecute node) {
        int execType = node.getExecType();

        if (execType == TBaseType.metExecSp || execType == TBaseType.metNoExecKeyword) {
            handleExecModule(node);
        } else if (execType == TBaseType.metExecStringCmd
                || execType == TBaseType.metExecStringCmdLinkServer) {
            handleDynamicExecString(node);
        }
    }

    private void handleExecModule(TMssqlExecute node) {
        TObjectName moduleName = node.getModuleName();
        if (moduleName == null) return;

        String rawName = moduleName.toString().trim();
        if (rawName.isEmpty()) return;

        MssqlNameNormalizer.NormalizedName normalized = MssqlNameNormalizer.normalize(rawName);

        // Cross-database or remote call → external dependency
        if (normalized.getPartCount() >= 4) {
            addExternalRef(rawName, "REMOTE_CALL", node);
            return;
        }
        if (normalized.getPartCount() >= 3 && normalized.getDatabase() != null) {
            addExternalRef(rawName, "CROSS_DATABASE", node);
            return;
        }

        // Check if sp_executesql
        if (MssqlExternalDepClassifier.isSpExecuteSql(rawName)) {
            addExternalRef(rawName, "SYSTEM_PROC", node);
            handleSpExecuteSql(node);
            return;
        }

        // System / extended proc
        if (MssqlExternalDepClassifier.isExternal(rawName)) {
            String extType = MssqlExternalDepClassifier.classify(rawName);
            addExternalRef(rawName, extType, node);
            return;
        }

        // User procedure call
        int argCount = node.getParameters() != null ? node.getParameters().size() : 0;
        addRoutineRef(rawName, argCount, node, Confidence.HIGH, "EXEC module");
    }

    private void handleSpExecuteSql(TMssqlExecute node) {
        // Try to extract the SQL literal from the first parameter
        String sqlText = null;
        if (node.getParameters() != null && node.getParameters().size() > 0) {
            TExecParameter firstParam = node.getParameters().getExecParameter(0);
            if (firstParam != null && firstParam.getParameterValue() != null) {
                sqlText = extractStringLiteral(firstParam.getParameterValue());
            }
        }
        // Also check node.getSqlText() which the parser may have resolved
        if (sqlText == null) {
            sqlText = node.getSqlText();
        }

        if (sqlText != null && !sqlText.isEmpty()) {
            parseInnerSql(sqlText);
        }
    }

    private void handleDynamicExecString(TMssqlExecute node) {
        // EXEC('sql string') or EXEC(@variable)
        String sqlText = null;
        if (node.getStringValues() != null && node.getStringValues().size() > 0) {
            TExpression firstExpr = node.getStringValues().getExpression(0);
            if (firstExpr != null) {
                sqlText = extractStringLiteral(firstExpr);
            }
        }
        if (sqlText == null) {
            sqlText = node.getSqlText();
        }

        if (sqlText != null && !sqlText.isEmpty()) {
            parseInnerSql(sqlText);
        }

        // Record dynamic SQL as external dependency
        BoundRoutineRef ref = createRoutineRef(
                "EXEC_DYNAMIC_SQL", 0, node, Confidence.LOW, "EXEC string");
        ref.setProperty("externalDependency", true);
        ref.setProperty("externalType", "DYNAMIC_SQL");
        program.addRoutineRef(ref);
    }

    /**
     * Attempts to parse inner SQL from dynamic SQL and extract refs.
     */
    private void parseInnerSql(String sqlText) {
        try {
            TGSqlParser innerParser = new TGSqlParser(EDbVendor.dbvmssql);
            innerParser.sqltext = sqlText;
            if (innerParser.parse() == 0) {
                // Extract table refs from inner SQL
                for (int i = 0; i < innerParser.sqlstatements.size(); i++) {
                    TCustomSqlStatement stmt = innerParser.sqlstatements.get(i);
                    if (stmt != null && stmt.tables != null) {
                        for (int j = 0; j < stmt.tables.size(); j++) {
                            TTable table = stmt.tables.getTable(j);
                            collectTableRef(table, stmt);
                        }
                    }
                }
            }
        } catch (Exception e) {
            // Ignore parse failures in dynamic SQL
        }
    }

    // ---- SET @var = expr (expression not traversed by TMssqlSet.acceptChildren) ----

    @Override
    public void preVisit(TMssqlSet node) {
        if (node.getSetType() == TBaseType.mstLocalVar && node.getVarExpr() != null) {
            // TMssqlSet.acceptChildren() does not traverse varExpr,
            // so function calls in SET assignments are missed.
            // Manually traverse the expression tree to pick up TFunctionCall nodes.
            node.getVarExpr().acceptChildren(this);
        }
    }

    // ---- Function calls in expressions ----

    @Override
    public void preVisit(TFunctionCall funcCall) {
        if (visitedFunctionCalls.contains(funcCall)) return;
        visitedFunctionCalls.add(funcCall);

        if (funcCall.getFunctionName() == null) return;
        String funcName = funcCall.getFunctionName().toString().trim();
        if (funcName.isEmpty()) return;

        // Filter XML data type methods (.nodes/.value/.query/.exist/.modify)
        // and .WRITE() partial column update. These produce TFunctionCall nodes
        // but are NOT user-defined function calls. Only filter when the name
        // has a dot prefix (e.g., "alias.value", "@var.nodes", "col.WRITE").
        int lastDot = funcName.lastIndexOf('.');
        if (lastDot > 0) {
            String lastPart = funcName.substring(lastDot + 1).toUpperCase();
            if (DOT_METHOD_NAMES.contains(lastPart)) {
                return;
            }
        }

        // Skip known aggregate/window functions that the parser handles
        EFunctionType ftype = funcCall.getFunctionType();
        if (isParserHandledFunction(ftype)) {
            // These are SQL-level aggregates/window functions, not user UDF calls.
            // Still mark as external if they are built-in.
            if (MssqlExternalDepClassifier.isExternal(funcName)) {
                addExternalRef(funcName, MssqlExternalDepClassifier.classify(funcName), funcCall);
            }
            return;
        }

        MssqlNameNormalizer.NormalizedName normalized = MssqlNameNormalizer.normalize(funcName);

        // Cross-database/remote → external
        if (normalized.getPartCount() >= 3 && normalized.getDatabase() != null) {
            addExternalRef(funcName, "CROSS_DATABASE", funcCall);
            return;
        }

        // Check if known external
        if (MssqlExternalDepClassifier.isExternal(funcName)) {
            addExternalRef(funcName, MssqlExternalDepClassifier.classify(funcName), funcCall);
            return;
        }

        // Check if it's a declared variable (not a function call)
        String objectPart = normalized.getObject();
        if (objectPart.startsWith("@")) {
            // @variable is not a function call
            return;
        }

        // User-defined function call
        int argCount = 0;
        if (funcCall.getArgs() != null) {
            argCount = funcCall.getArgs().size();
        }
        addRoutineRef(funcName, argCount, funcCall, Confidence.HIGH, "function call in expression");
    }

    private boolean isParserHandledFunction(EFunctionType ftype) {
        if (ftype == null) return false;
        switch (ftype) {
            case cast_t:
            case convert_t:
            case trim_t:
            case extract_t:
            case treat_t:
            case contains_t:
            case freetext_t:
            case rank_t:
            case builtin_t:
                return true;
            default:
                return false;
        }
    }

    // ==========================================
    // Table reference collection
    // ==========================================

    @Override
    public void preVisit(TSelectSqlStatement node) {
        // Only process leaf SELECTs (not UNION containers)
        if (node.getSetOperatorType() != ESetOperatorType.none) return;
        collectTablesFromStatement(node);
    }

    @Override
    public void preVisit(TInsertSqlStatement node) {
        collectTablesFromStatement(node);
    }

    @Override
    public void preVisit(TUpdateSqlStatement node) {
        collectTablesFromStatement(node);
    }

    @Override
    public void preVisit(TDeleteSqlStatement node) {
        collectTablesFromStatement(node);
    }

    @Override
    public void preVisit(TMergeSqlStatement node) {
        collectTablesFromStatement(node);
    }

    private void collectTablesFromStatement(TCustomSqlStatement stmt) {
        if (stmt == null || stmt.tables == null) return;
        for (int i = 0; i < stmt.tables.size(); i++) {
            TTable table = stmt.tables.getTable(i);
            collectTableRef(table, stmt);
        }
    }

    private void collectTableRef(TTable table, TCustomSqlStatement stmt) {
        if (table == null || table.getTableName() == null) return;
        String tableName = table.getTableName().toString().trim();
        if (tableName.isEmpty()) return;

        // Classify table
        String category = classifyTable(tableName, table);

        List<String> nameParts = MssqlNameNormalizer.splitParts(tableName);
        for (int i = 0; i < nameParts.size(); i++) {
            nameParts.set(i, MssqlNameNormalizer.stripQuotes(nameParts.get(i)));
        }

        EObjectRefKind refKind = mapTableType(table.getTableType());

        BoundObjectRef objRef = new BoundObjectRef(
                tableName, nameParts,
                EBindingStatus.UNRESOLVED_SOFT, null, null,
                refKind, null);
        objRef.setSourceAnchor(anchor(table));
        objRef.setProperty("owningRoutineId", currentRoutineId());

        // Set table effect type
        ETableEffectType effectType = table.getEffectType();
        if (effectType != null) {
            objRef.setProperty("tableEffectType", effectType);
        }
        // Fallback: statement type
        if (stmt != null) {
            objRef.setProperty("statementType", stmt.sqlstatementtype);
        }

        // Mssql table category
        objRef.setProperty("mssql.tableCategory", category);

        program.addObjectRef(objRef);
    }

    private String classifyTable(String tableName, TTable table) {
        String upper = tableName.toUpperCase().trim();
        String stripped = MssqlNameNormalizer.stripQuotes(upper);
        // Check last part for prefix patterns
        int dot = stripped.lastIndexOf('.');
        String objectPart = dot >= 0 ? stripped.substring(dot + 1) : stripped;

        if (objectPart.equals("INSERTED") || objectPart.equals("DELETED")) {
            return "PSEUDO";
        }
        if (objectPart.startsWith("##")) {
            return "GLOBAL_TEMP";
        }
        if (objectPart.startsWith("#")) {
            return "TEMP";
        }
        if (objectPart.startsWith("@")) {
            return "TABLE_VARIABLE";
        }
        return "REAL";
    }

    private EObjectRefKind mapTableType(ETableSource tableType) {
        if (tableType == null) return EObjectRefKind.TABLE;
        switch (tableType) {
            case subquery:
                return EObjectRefKind.DERIVED_TABLE;
            case function:
                return EObjectRefKind.TABLE_FUNCTION;
            case pivoted_table:
                return EObjectRefKind.PIVOT_TABLE;
            default:
                return EObjectRefKind.TABLE;
        }
    }

    // ==========================================
    // Anonymous batch handling
    // ==========================================

    /**
     * Called for top-level DML statements that are not inside any routine definition.
     * These are grouped as anonymous batches.
     */
    private void ensureAnonymousBatchContext(TParseTreeNode node) {
        if (currentRoutineId() == null) {
            // Create anonymous batch routine
            batchCounter++;
            String batchName = "<batch_" + batchCounter + ">";

            BoundScope batchScope = pushScope(EScopeKind.ROUTINE, anchor(node));
            BoundRoutineSymbol batch = new BoundRoutineSymbol(
                    batchName, null, batchScope, anchor(node),
                    Collections.<BoundParameterSymbol>emptyList(), null,
                    ERoutineKind.ANONYMOUS_BLOCK);
            program.registerRoutine(batch);
            routineIdStack.push(batch.getRoutineId());
        }
    }

    // ==========================================
    // Helper methods
    // ==========================================

    private List<BoundParameterSymbol> extractParameters(TParameterDeclarationList paramList) {
        List<BoundParameterSymbol> params = new ArrayList<BoundParameterSymbol>();
        if (paramList == null) return params;

        for (int i = 0; i < paramList.size(); i++) {
            TParameterDeclaration pd = paramList.getParameterDeclarationItem(i);
            if (pd == null || pd.getParameterName() == null) continue;

            String paramName = pd.getParameterName().toString().trim();
            BoundTypeRef typeRef = MssqlTypeRefMapper.map(pd.getDataType());

            gudusoft.gsqlparser.ir.bound.EParameterMode mode =
                    gudusoft.gsqlparser.ir.bound.EParameterMode.IN;
            gudusoft.gsqlparser.EParameterMode pdMode = pd.getParameterMode();
            if (pdMode == gudusoft.gsqlparser.EParameterMode.output
                    || pdMode == gudusoft.gsqlparser.EParameterMode.out
                    || pdMode == gudusoft.gsqlparser.EParameterMode.inout) {
                mode = gudusoft.gsqlparser.ir.bound.EParameterMode.IN_OUT;
            }

            BoundParameterSymbol param = new BoundParameterSymbol(
                    paramName, null, anchor(pd), typeRef, mode);
            params.add(param);
        }
        return params;
    }

    private void addRoutineRef(String rawName, int argCount, TParseTreeNode node,
                               Confidence confidence, String evidenceMsg) {
        BoundRoutineRef ref = createRoutineRef(rawName, argCount, node, confidence, evidenceMsg);
        program.addRoutineRef(ref);
    }

    private BoundRoutineRef createRoutineRef(String rawName, int argCount,
                                              TParseTreeNode node,
                                              Confidence confidence, String evidenceMsg) {
        List<String> nameParts = MssqlNameNormalizer.splitParts(rawName);
        for (int i = 0; i < nameParts.size(); i++) {
            nameParts.set(i, MssqlNameNormalizer.stripQuotes(nameParts.get(i)));
        }

        Evidence evidence = new Evidence(EvidenceKind.STATIC_RESOLVED, evidenceMsg);
        BoundRoutineRef ref = new BoundRoutineRef(
                rawName, nameParts,
                EBindingStatus.UNRESOLVED_SOFT, null, null,
                Collections.<BoundArgument>emptyList(),
                evidence, confidence);
        ref.setSourceAnchor(anchor(node));
        ref.setProperty("owningRoutineId", currentRoutineId());
        ref.setProperty("argCount", argCount);
        return ref;
    }

    private void addExternalRef(String rawName, String externalType, TParseTreeNode node) {
        BoundRoutineRef ref = createRoutineRef(rawName, 0, node, Confidence.HIGH,
                "External: " + externalType + " " + rawName);
        ref.setProperty("externalDependency", true);
        ref.setProperty("externalType", externalType);
        ref.setProperty("externalName", MssqlNameNormalizer.stripQuotes(rawName));
        if (MssqlExternalDepClassifier.isSecuritySensitive(rawName)) {
            ref.setProperty("securitySensitive", true);
        }
        program.addRoutineRef(ref);
    }

    private String extractStringLiteral(TExpression expr) {
        if (expr == null) return null;
        String text = expr.toString().trim();
        // Strip N prefix and surrounding quotes
        if (text.toUpperCase().startsWith("N'") && text.endsWith("'") && text.length() >= 3) {
            return text.substring(2, text.length() - 1).replace("''", "'");
        }
        if (text.startsWith("'") && text.endsWith("'") && text.length() >= 2) {
            return text.substring(1, text.length() - 1).replace("''", "'");
        }
        return null;
    }
}