package gudusoft.gsqlparser.sqlenv;


import gudusoft.gsqlparser.TBaseType;
import gudusoft.gsqlparser.util.SQLUtil;

import java.util.*;

/**
 * SQL schema, contains a list of schema objects.
 */
public class TSQLSchema extends TSQLObject {


    public String getQualifiedName(){
        return catalog.name+"."+name;
    }

    private TSQLCatalog catalog;

    public TSQLCatalog getCatalog() {
        return catalog;
    }

    // ===== Shadow Introduction: New type-specific indexes (Phase 2) =====
    // These LinkedHashMaps provide O(1) lookup per object type, replacing the mixed schemaObjectMap
    private final LinkedHashMap<String, TSQLTable> tables = new LinkedHashMap<>();
    private final LinkedHashMap<String, TSQLFunction> functions = new LinkedHashMap<>();
    private final LinkedHashMap<String, TSQLProcedure> procedures = new LinkedHashMap<>();
    private final LinkedHashMap<String, TSQLTrigger> triggers = new LinkedHashMap<>();
    private final LinkedHashMap<String, TSQLOraclePackage> oraclePackages = new LinkedHashMap<>();
    private final LinkedHashMap<String, TSQLSynonyms> synonyms = new LinkedHashMap<>();

    // ===== Phase 3: Bucketed indexes for SQL Server COLLATION_BASED =====
    // These provide ~25x performance improvement for SQL Server (10,000 objects: 20μs → 800ns)
    private BucketedIndex bucketedTables;
    private BucketedIndex bucketedFunctions;
    private BucketedIndex bucketedProcedures;
    private BucketedIndex bucketedTriggers;
    private BucketedIndex bucketedOraclePackages;
    private BucketedIndex bucketedSynonyms;

    // IdentifierService for consistent key generation (lazy initialization)
    private IdentifierService identifierService;

    // CollatorProvider for SQL Server (lazy initialization)
    private CollatorProvider collatorProvider;

    // ===== Phase 6: Schema seal mechanism for thread-safety hardening =====
    /**
     * Seal state flag (volatile for thread visibility).
     */
    private volatile boolean sealed = false;

    /**
     * Seal this schema to prevent further modifications (Phase 6).
     *
     * <p>See {@link TSQLEnv#seal()} for detailed documentation.
     */
    public void seal() {
        this.sealed = true;
    }

    /**
     * Check if this schema is sealed (Phase 6).
     *
     * @return true if sealed, false otherwise
     */
    public boolean isSealed() {
        return sealed;
    }

    /**
     * Check if schema is not sealed, throw exception if sealed and enforcement is enabled.
     *
     * @throws IllegalStateException if sealed and TBaseType.ENFORCE_CATALOG_SEAL is true
     */
    private void checkNotSealed() {
        if (sealed && TBaseType.ENFORCE_CATALOG_SEAL) {
            throw new IllegalStateException("Cannot modify sealed schema. Call seal() marks schema as read-only.");
        }
    }

    /**
     * Create a new instance of the schema, added to the catalog.
     *
     * @param sqlCatalog catalog contains this schema
     * @param schemaName name of the schema
     */
    public TSQLSchema(TSQLCatalog sqlCatalog,String schemaName){
        super(sqlCatalog.getSqlEnv(), schemaName,ESQLDataObjectType.dotSchema);
        this.catalog = sqlCatalog;
        this.catalog.addSchema(this);
    }

    /**
     * Get or create IdentifierService (lazy initialization)
     */
    private IdentifierService getIdentifierService() {
        if (identifierService == null) {
            IdentifierProfile profile = IdentifierProfile.forVendor(
                this.sqlEnv.getDBVendor(),
                IdentifierProfile.VendorFlags.defaults()
            );
            identifierService = new IdentifierService(profile, null);
        }
        return identifierService;
    }

    /**
     * Get or create CollatorProvider (lazy initialization, SQL Server only)
     */
    private CollatorProvider getCollatorProvider() {
        if (collatorProvider == null) {
            collatorProvider = new CollatorProvider();
        }
        return collatorProvider;
    }

    /**
     * Check if SQL Server and bucketed index is enabled
     */
    private boolean shouldUseBucketedIndex() {
        return TBaseType.USE_BUCKETED_INDEX &&
               (this.sqlEnv.getDBVendor() == gudusoft.gsqlparser.EDbVendor.dbvmssql ||
                this.sqlEnv.getDBVendor() == gudusoft.gsqlparser.EDbVendor.dbvazuresql);
    }

    /**
     * Slice S3: cache of whether the hierarchical type-specific map is globally
     * legacy-compatible for a given {@code (vendor, objectType)} pair.
     *
     * <p>Key format: {@code "<EDbVendor.name()>|<ESQLDataObjectType.name()>"}.
     * Both the IdentifierProfile (built from {@link IdentifierProfile#forVendor})
     * and the legacy {@link TSQLEnv} per-vendor case-sensitivity maps are static
     * relative to the (vendor, type) tuple, so the answer is stable for the
     * JVM lifetime and safe to cache process-wide.
     */
    private static final java.util.concurrent.ConcurrentHashMap<String, Boolean>
        HIERARCHICAL_COMPAT_CACHE = new java.util.concurrent.ConcurrentHashMap<>();

    /**
     * Slice S3: detect whether the hierarchical type-specific map can be safely
     * consulted for the entire {@code (vendor, objectType)} pair.
     *
     * <p>The hierarchical map keys come from {@link IdentifierService#keyForMap}.
     * The legacy {@code schemaObjectMap} keys come from
     * {@link gudusoft.gsqlparser.util.SQLUtil#getIdentifierNormalName}.
     *
     * <p>For several vendor/type combinations the two key functions diverge:
     * <ul>
     *   <li>SQL Server / Azure SQL with {@code COLLATION_BASED} rules
     *       ({@code IdentifierRules.forSQLServer}: {@code CaseFold.NONE}) when
     *       {@link TSQLEnv#tableCollationCaseSensitive} reports {@code false}
     *       (legacy adds a {@code toUpperCase} pass).</li>
     *   <li>BigQuery tables ({@code IdentifierRules.forBigQueryTable}:
     *       {@code CaseFold.NONE / SENSITIVE}) where
     *       {@link TSQLEnv#tableCollationCaseSensitive} is {@code false}.</li>
     *   <li>Hive/Teradata routines and any vendor whose {@link IdentifierRules}
     *       differ from the legacy {@link TSQLEnv} per-type case-sensitivity
     *       map.</li>
     * </ul>
     *
     * <p>When the two diverge, distinct case-different writes land under
     * different keys in hierarchical (both retained) but collapse to the same
     * key in legacy (last write wins). A subsequent lookup that happens to
     * use a probe input where the two functions <em>locally</em> agree (e.g.
     * an already-uppercase {@code "FOO"} on BigQuery) would still hit the
     * stale earlier hierarchical entry while legacy holds the authoritative
     * later write. So input-local parity is insufficient — the gate must be
     * vendor/type-global.
     *
     * <p>We probe once with a case-mixed ASCII input and cache the result.
     * If the two key functions agree on the probe they agree on every input
     * (they only differ in fold direction; a probe that survives both folds
     * is sufficient). When they diverge, hierarchical is skipped permanently
     * for that {@code (vendor, type)} and the lookup falls through to legacy.
     *
     * <p>This is intentionally conservative; once the strict-mode switch from
     * plan §3.3 lands (slice S7) the gate can flip to "always trust
     * hierarchical" and accept the (more correct) behavior change.
     */
    private boolean isHierarchicalCompatibleForType(ESQLDataObjectType objectType) {
        try {
            gudusoft.gsqlparser.EDbVendor vendor = this.sqlEnv.getDBVendor();
            String cacheKey = vendor.name() + "|" + objectType.name();
            Boolean cached = HIERARCHICAL_COMPAT_CACHE.get(cacheKey);
            if (cached != null) {
                return cached;
            }
            // Probe both unquoted and quoted representative inputs because
            // IdentifierRules has independent {@code unquotedFold/Compare} and
            // {@code quotedFold/Compare} axes; a vendor/type can match for one
            // representation and diverge for the other (e.g. Snowflake where
            // unquoted folds to UPPER on both sides but quoted preserves case
            // in keyForMap while legacy still appends toUpperCase via
            // tableCollationCaseSensitive=false).
            String unquotedProbe = "ProbeXyz";
            String quotedProbe = quoteIdentifierForProbe(vendor, unquotedProbe);
            IdentifierService svc = getIdentifierService();
            boolean compat = probeMatches(svc, vendor, unquotedProbe, objectType)
                && probeMatches(svc, vendor, quotedProbe, objectType);
            HIERARCHICAL_COMPAT_CACHE.put(cacheKey, compat);
            return compat;
        } catch (Throwable ignore) {
            return false;
        }
    }

    private boolean probeMatches(IdentifierService svc,
                                 gudusoft.gsqlparser.EDbVendor vendor,
                                 String probe,
                                 ESQLDataObjectType objectType) {
        String hierarchicalKey = svc.keyForMap(probe, objectType);
        String legacyKey = SQLUtil.getIdentifierNormalName(vendor, probe, objectType);
        return hierarchicalKey != null && hierarchicalKey.equals(legacyKey);
    }

    /**
     * Wrap {@code name} in the vendor's quoted-identifier delimiter so the
     * probe exercises {@code IdentifierRules.quotedFold/quotedCompare} and the
     * legacy {@link gudusoft.gsqlparser.util.SQLUtil#normalizeIdentifier}
     * branch that strips quotes before folding. Mirrors the dispatch in
     * {@link TSQLEnv#isDelimitedIdentifier(gudusoft.gsqlparser.EDbVendor, String)}.
     */
    private static String quoteIdentifierForProbe(gudusoft.gsqlparser.EDbVendor vendor, String name) {
        switch (vendor) {
            case dbvmssql:
            case dbvazuresql:
                return "[" + name + "]";
            case dbvmysql:
            case dbvbigquery:
            case dbvcouchbase:
            case dbvhive:
            case dbvimpala:
                return "`" + name + "`";
            case dbvdax:
                return "'" + name + "'";
            default:
                return "\"" + name + "\"";
        }
    }

    /**
     * Get or create bucketed index for a specific object type
     */
    private BucketedIndex getBucketedIndex(ESQLDataObjectType objectType) {
        if (!shouldUseBucketedIndex()) {
            return null;
        }

        String collation = getIdentifierService().getProfile().getFlags().defaultCollation;
        CollatorProvider provider = getCollatorProvider();

        switch (objectType) {
            case dotTable:
                if (bucketedTables == null) {
                    bucketedTables = new BucketedIndex(provider, collation, objectType);
                }
                return bucketedTables;

            case dotFunction:
                if (bucketedFunctions == null) {
                    bucketedFunctions = new BucketedIndex(provider, collation, objectType);
                }
                return bucketedFunctions;

            case dotProcedure:
                if (bucketedProcedures == null) {
                    bucketedProcedures = new BucketedIndex(provider, collation, objectType);
                }
                return bucketedProcedures;

            case dotTrigger:
                if (bucketedTriggers == null) {
                    bucketedTriggers = new BucketedIndex(provider, collation, objectType);
                }
                return bucketedTriggers;

            case dotOraclePackage:
                if (bucketedOraclePackages == null) {
                    bucketedOraclePackages = new BucketedIndex(provider, collation, objectType);
                }
                return bucketedOraclePackages;

            case dotSynonyms:
                if (bucketedSynonyms == null) {
                    bucketedSynonyms = new BucketedIndex(provider, collation, objectType);
                }
                return bucketedSynonyms;

            default:
                return null;
        }
    }

    public List<TSQLSchemaObject> getSchemaObjectList() {
        List<TSQLSchemaObject> schemaObjectList = new LinkedList<>();
        synchronized (schemaObjectMap) {
            for(String schemaObjectKey: schemaObjectMap.keySet()){
                TSQLSchemaObject schemaObject = schemaObjectMap.get(schemaObjectKey);
                if(schemaObject instanceof TSQLProcedure){
                    if(((TSQLProcedure)schemaObject).isOraclePackageProcedure()){
                        continue;
                    }
                }
                schemaObjectList.add(schemaObject);
            }
        }
        return schemaObjectList;
    }

    public List<TSQLSchemaObject> getPackageObjectList(TSQLOraclePackage oraclePackage) {
        List<TSQLSchemaObject> schemaObjectList = new LinkedList<>();
        synchronized (schemaObjectMap) {
            for (String schemaObjectKey : schemaObjectMap.keySet()) {
                TSQLSchemaObject schemaObject = schemaObjectMap.get(schemaObjectKey);
                if (schemaObject instanceof TSQLProcedure) {
                    if (!((TSQLProcedure) schemaObject).isOraclePackageProcedure()) {
                        continue;
                    }
                    TSQLProcedure procedure = (TSQLProcedure) schemaObject;
                    if (procedure.getOraclePackage() == oraclePackage) {
                        schemaObjectList.add(schemaObject);
                    }
                }
            }
        }
        return schemaObjectList;
    }

    private Map<String, TSQLSchemaObject> schemaObjectMap = Collections.synchronizedMap(new LinkedHashMap<String, TSQLSchemaObject>( ));

//    public boolean addTable(TSQLTable sqlTable){
//        return addDataObject(sqlTable);
//    }
//
//    public boolean addProcedure(TSQLProcedure sqlProcedure){
//        return addDataObject(sqlProcedure);
//    }
//
//    public boolean addFunction(TSQLFunction sqlFunction){
//        return addDataObject(sqlFunction);
//    }
//
//    public boolean addTrigger(TSQLTrigger sqlTrigger){
//        return addDataObject(sqlTrigger);
//    }

    /**
     * add database object
     *
     * <p><strong>Dual-write strategy:</strong> Updates both legacy schemaObjectMap
     * and new type-specific maps for backward compatibility and performance optimization.
     *
     * @param schemaObject schema object to add
     */
    protected void addSchemaObject(TSQLSchemaObject schemaObject){
        checkNotSealed();  // Phase 6: Prevent modification after seal
        // ===== Legacy path: Update schemaObjectMap (backward compatibility) =====
        String normalName = SQLUtil.getIdentifierNormalName(this.sqlEnv.getDBVendor(), schemaObject.name, schemaObject.getDataObjectType());
        if (schemaObject.getDataObjectType() == ESQLDataObjectType.dotFunction)
        {
            normalName = normalName+"$function";
        }
        else if (schemaObject.getDataObjectType() == ESQLDataObjectType.dotProcedure)
        {
            normalName = normalName+"$procedure";
        }
        else if (schemaObject.getDataObjectType() == ESQLDataObjectType.dotTrigger)
        {
            normalName = normalName+"$trigger";
        }
        else if (schemaObject.getDataObjectType() == ESQLDataObjectType.dotOraclePackage)
        {
            normalName = normalName+"$package";
        }
        schemaObjectMap.put(normalName, schemaObject);
        this.catalog.getSqlEnv().putSchemaObject(schemaObject.getQualifiedName(),schemaObject);

        // ===== New path: Update type-specific maps (Phase 2 optimization) =====
        if (TBaseType.USE_HIERARCHICAL_INDEX) {
            try {
                String key = getIdentifierService().keyForMap(schemaObject.name, schemaObject.getDataObjectType());

                switch (schemaObject.getDataObjectType()) {
                    case dotTable:
                        tables.put(key, (TSQLTable) schemaObject);
                        break;
                    case dotFunction:
                        functions.put(key, (TSQLFunction) schemaObject);
                        break;
                    case dotProcedure:
                        procedures.put(key, (TSQLProcedure) schemaObject);
                        break;
                    case dotTrigger:
                        triggers.put(key, (TSQLTrigger) schemaObject);
                        break;
                    case dotOraclePackage:
                        oraclePackages.put(key, (TSQLOraclePackage) schemaObject);
                        break;
                    case dotSynonyms:
                        synonyms.put(key, (TSQLSynonyms) schemaObject);
                        break;
                }
            } catch (Throwable ignore) {
                // Silently fail to maintain backward compatibility
            }
        }

        // ===== Phase 3: Update bucketed index for SQL Server =====
        if (shouldUseBucketedIndex()) {
            try {
                BucketedIndex bucketedIndex = getBucketedIndex(schemaObject.getDataObjectType());
                if (bucketedIndex != null) {
                    bucketedIndex.put(schemaObject.name, schemaObject);
                }
            } catch (Throwable ignore) {
                // Silently fail to maintain backward compatibility
            }
        }
    }

    TSQLSchemaObject findSchemaObject(ESQLDataObjectType dataObjectType, String schemaObjectName) {
        // Phase 3.5: Use bucketed index for SQL Server (25x faster for 10,000+ objects)
        if (shouldUseBucketedIndex()) {
            try {
                BucketedIndex bucketedIndex = getBucketedIndex(dataObjectType);
                if (bucketedIndex != null) {
                    TSQLSchemaObject result = bucketedIndex.get(schemaObjectName);
                    if (result != null) {
                        if (TBaseType.LOG_INDEX_HIT_RATE) {
                            System.out.println("[BucketedIndex] Hit: " + dataObjectType + " - " + schemaObjectName);
                        }
                        return result;
                    }
                }
            } catch (Throwable ignore) {
                // Fall through to legacy path
            }
        }

        // Phase 2: Use hierarchical index (type-specific maps) when flag is enabled.
        //
        // Slice S3: Only consult hierarchical when its IdentifierService.keyForMap
        // key function is globally compatible with legacy
        // SQLUtil.getIdentifierNormalName for this {@code (vendor, type)} pair.
        // See {@link #isHierarchicalCompatibleForType} for the rationale; in
        // particular, an input-local parity check is insufficient because a
        // case-different earlier write may already have populated hierarchical
        // under a key that legacy collapsed (e.g. BigQuery: writes "FOO" then
        // "foo"; legacy retains only the last; hierarchical retains both;
        // querying "FOO" would still return the stale earlier hierarchical
        // entry). Falling through to legacy preserves write/read symmetry.
        if (TBaseType.USE_HIERARCHICAL_INDEX
                && isHierarchicalCompatibleForType(dataObjectType)) {
            try {
                String key = getIdentifierService().keyForMap(schemaObjectName, dataObjectType);
                TSQLSchemaObject result = null;

                switch (dataObjectType) {
                    case dotTable:
                        result = tables.get(key);
                        break;
                    case dotFunction:
                        result = functions.get(key);
                        break;
                    case dotProcedure:
                        result = procedures.get(key);
                        break;
                    case dotTrigger:
                        result = triggers.get(key);
                        break;
                    case dotOraclePackage:
                        result = oraclePackages.get(key);
                        break;
                    case dotSynonyms:
                        result = synonyms.get(key);
                        break;
                }

                if (result != null) {
                    // Log hit rate if enabled
                    if (TBaseType.LOG_INDEX_HIT_RATE) {
                        System.out.println("[HierarchicalIndex] Hit: " + dataObjectType + " - " + schemaObjectName);
                    }
                    return result;
                }
            } catch (Throwable ignore) {
                // Fall through to legacy path
            }
        }

        // Legacy path: use suffix hacks and local map (backward compatibility)
        String normalName = SQLUtil.getIdentifierNormalName(this.sqlEnv.getDBVendor(), schemaObjectName, dataObjectType);
        if (dataObjectType == ESQLDataObjectType.dotFunction)
        {
            normalName = normalName+"$function";
        }
        else if (dataObjectType == ESQLDataObjectType.dotProcedure)
        {
            normalName = normalName+"$procedure";
        }
        else if (dataObjectType == ESQLDataObjectType.dotTrigger)
        {
            normalName = normalName+"$trigger";
        }
        else if (dataObjectType == ESQLDataObjectType.dotOraclePackage)
        {
            normalName = normalName+"$package";
        }
        return schemaObjectMap.get(normalName);
    }

    /**
     * create a oracle package belong to this schema
     *
     * @param oraclePackageName oracle package name
     * @return an instance of the oracle package
     */
    public TSQLOraclePackage createOraclePackage(String oraclePackageName){
        return (TSQLOraclePackage)createSchemaObject(oraclePackageName,ESQLDataObjectType.dotOraclePackage);
    }

    /**
     * create a procedure belong to this schema
     *
     * @param procedureName procedure name
     * @return an instance of the SQL procedure
     */
    public TSQLProcedure createProcedure(String procedureName){
        return (TSQLProcedure)createSchemaObject(procedureName,ESQLDataObjectType.dotProcedure);
    }

    public TSQLProcedure createProcedure(String procedureName, ESQLDataObjectType type){
        return (TSQLProcedure)createSchemaObject(procedureName,type);
    }

    /**
     * create a function belong to this schema
     *
     * @param functionName function name
     * @return an instance of the SQL function.
     */
    public TSQLFunction createFunction(String functionName){
        return (TSQLFunction)createSchemaObject(functionName,ESQLDataObjectType.dotFunction);
    }

    /**
     * create a trigger belong to this schema
     *
     * @param triggerName trigger name
     * @return an instance of the SQL trigger.
     */
    public TSQLTrigger createTrigger(String triggerName){
        return (TSQLTrigger)createSchemaObject(triggerName,ESQLDataObjectType.dotTrigger);
    }

    /**
     * create a synonyms belong to this schema
     *
     * @param synonymsName synonyms name
     * @return an instance of the SQL synonyms
     */
    public TSQLSynonyms createSynonyms(String synonymsName){
        return (TSQLSynonyms)createSchemaObject(synonymsName,ESQLDataObjectType.dotSynonyms);
    }

    /**
     * create a table belong to this schema. If table with the same name already exists in the schema
     * return the existing table.
     *
     * @param tableName table name
     * @return an instance of the SQL table
     */
    public TSQLTable createTable(String tableName){
        return (TSQLTable)createSchemaObject(tableName,ESQLDataObjectType.dotTable);
    }

    public TSQLTable createTable(String tableName, int priority){
        return (TSQLTable)createSchemaObject(tableName,ESQLDataObjectType.dotTable, priority);
    }

    // Slice S3: route public lookups through findSchemaObject so they go through
    // the bucketed → hierarchical (IdentifierService.keyForMap) → legacy fallback
    // chain, instead of bypassing both indexes via raw schemaObjectMap.
    public boolean containsTable(String tableName) {
        return findSchemaObject(ESQLDataObjectType.dotTable, tableName) != null;
    }

    public TSQLTable findTable(String tableName) {
        return (TSQLTable) findSchemaObject(ESQLDataObjectType.dotTable, tableName);
    }
    
        public TSQLSchemaObject findSchemaObject(String schemaObjectName) {
                TSQLSchemaObject object = findSchemaObject(ESQLDataObjectType.dotFunction, schemaObjectName);
                if (object == null) {
                        object = findSchemaObject(ESQLDataObjectType.dotProcedure, schemaObjectName);
                }
                if (object == null) {
                        object = findSchemaObject(ESQLDataObjectType.dotTrigger, schemaObjectName);
                }
                if (object == null) {
                        object = findSchemaObject(ESQLDataObjectType.dotOraclePackage, schemaObjectName);
                }
                return object;
        }

    /**
     *
     * @param schemaObjectName 该名称不带 catalog, schema 前缀
     * @param dataObjectType
     * @return
     */
    public TSQLSchemaObject createSchemaObject(String schemaObjectName, ESQLDataObjectType dataObjectType){
        return createSchemaObject(schemaObjectName, dataObjectType, 0);
    }

    /**
     *
     * @param schemaObjectName 该名称不带 catalog, schema 前缀
     * @param dataObjectType
     * @param priority
     * @return
     */
    protected TSQLSchemaObject createSchemaObject(String schemaObjectName, ESQLDataObjectType dataObjectType, int priority){
        TSQLSchemaObject result = null;
        TSQLSchemaObject schemaObject = findSchemaObject(dataObjectType, schemaObjectName);
        if (schemaObject == null){
            switch (dataObjectType){
                case dotTable:
                    result = new TSQLTable(this,schemaObjectName);
                    break;
                case dotOraclePackage:
                    result = new TSQLOraclePackage(this,schemaObjectName);
                    break;
                case dotProcedure:
                    result = new TSQLProcedure(this,schemaObjectName);
                    break;
                case dotFunction:
                    result = new TSQLFunction(this,schemaObjectName);
                    break;
                case dotTrigger:
                    result = new TSQLTrigger(this,schemaObjectName);
                    break;
                case dotSynonyms:
                    result = new TSQLSynonyms(this,schemaObjectName);
                    break;
            }
        }else if (dataObjectType == schemaObject.getDataObjectType()){
            result = schemaObject;
        }else{
            System.out.println("object name conflict:"+getQualifiedName()+"."+schemaObjectName+",type:"+dataObjectType+" VS "+schemaObject.getQualifiedName()+", type:"+schemaObject.getDataObjectType());
        };

        if (result!=null && priority > result.getPriority()) {
            result.setPriority(priority);
        }
        return result;
    }

}