package gudusoft.gsqlparser.resolver2.scope;

import gudusoft.gsqlparser.ETableEffectType;
import gudusoft.gsqlparser.nodes.TParseTreeNode;
import gudusoft.gsqlparser.nodes.TTable;
import gudusoft.gsqlparser.resolver2.ColumnLevel;
import gudusoft.gsqlparser.resolver2.ScopeType;
import gudusoft.gsqlparser.resolver2.matcher.INameMatcher;
import gudusoft.gsqlparser.resolver2.model.ResolvePath;
import gudusoft.gsqlparser.resolver2.model.ScopeChild;
import gudusoft.gsqlparser.resolver2.namespace.INamespace;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * Scope for SELECT statement.
 * This is typically the root scope for a query.
 *
 * Contains:
 * - FROM clause scope (child)
 * - WHERE clause scope (child)
 * - SELECT list namespace (for ORDER BY to reference)
 */
public class SelectScope extends AbstractScope {

    /** FROM clause scope */
    private FromScope fromScope;

    /** WHERE clause scope */
    private IScope whereScope;

    /** Namespace representing the SELECT list output */
    private INamespace selectListNamespace;

    /**
     * One {@code JOIN ... USING} merge in this scope: the names it merged, the relations that took
     * part, in JOIN ORDER, and the join type.
     *
     * <p>SQL exposes a merged column ONCE, so an unqualified reference to it is not ambiguous
     * across the sides that merged it. All three fields are needed to say that safely:
     * <ul>
     *   <li>the NAMES, because only those columns merge;
     *   <li>the PARTICIPANTS, because a third relation in the same scope carrying the same name is
     *       still a genuine ambiguity — {@code SELECT c FROM a JOIN b USING(c), d} has three
     *       candidates and only two of them merged;
     *   <li>the ORDER and the join TYPE, because which side the merged value comes from depends on
     *       them: the left for INNER and LEFT, the right for RIGHT, and both for FULL.
     * </ul>
     */
    public static final class UsingMerge {
        private final java.util.List<String> columnNames = new java.util.ArrayList<String>();
        private final java.util.List<gudusoft.gsqlparser.nodes.TTable> participants =
                new java.util.ArrayList<gudusoft.gsqlparser.nodes.TTable>();
        private final gudusoft.gsqlparser.EJoinType joinType;

        public UsingMerge(gudusoft.gsqlparser.EJoinType joinType) {
            this.joinType = joinType;
        }

        public void addColumnName(String name) {
            if (name != null && !name.isEmpty()) {
                columnNames.add(name);
            }
        }

        /** Participants are added LEFT side first, right side last, and the order is meaningful. */
        public void addParticipant(gudusoft.gsqlparser.nodes.TTable table) {
            if (table != null && !containsIdentical(table)) {
                participants.add(table);
            }
        }

        private boolean containsIdentical(gudusoft.gsqlparser.nodes.TTable table) {
            for (gudusoft.gsqlparser.nodes.TTable seen : participants) {
                if (seen == table) {
                    return true;
                }
            }
            return false;
        }

        /** Identifiers fold by the vendor's rules, so never compared with equals. */
        public boolean merges(gudusoft.gsqlparser.EDbVendor vendor, String columnName) {
            return merges(gudusoft.gsqlparser.sqlenv.IdentifierService.defaultServiceFor(vendor), columnName);
        }

        /** {@link #merges(gudusoft.gsqlparser.EDbVendor, String)} under one analysis's identifier rules. */
        public boolean merges(gudusoft.gsqlparser.sqlenv.IdentifierService names, String columnName) {
            if (columnName == null) {
                return false;
            }
            for (String merged : columnNames) {
                if (names.areEqual(merged, columnName, gudusoft.gsqlparser.sqlenv.ESQLDataObjectType.dotColumn)) {
                    return true;
                }
            }
            return false;
        }

        public boolean hasParticipant(gudusoft.gsqlparser.nodes.TTable table) {
            return table != null && containsIdentical(table);
        }

        public java.util.List<gudusoft.gsqlparser.nodes.TTable> getParticipants() {
            return java.util.Collections.unmodifiableList(participants);
        }

        public gudusoft.gsqlparser.EJoinType getJoinType() {
            return joinType;
        }
    }

    private final java.util.List<UsingMerge> usingMerges = new java.util.ArrayList<UsingMerge>();

    public SelectScope(IScope parent, TParseTreeNode node) {
        super(parent, node, ScopeType.SELECT);
    }

    public void setFromScope(FromScope fromScope) {
        this.fromScope = fromScope;
    }

    public FromScope getFromScope() {
        return fromScope;
    }

    public void setWhereScope(IScope whereScope) {
        this.whereScope = whereScope;
    }

    public IScope getWhereScope() {
        return whereScope;
    }

    public void setSelectListNamespace(INamespace namespace) {
        this.selectListNamespace = namespace;
    }

    public INamespace getSelectListNamespace() {
        return selectListNamespace;
    }

    @Override
    public INamespace resolveTable(String tableName) {
        // First try FROM scope
        if (fromScope != null) {
            INamespace ns = fromScope.resolveTable(tableName);
            if (ns != null) {
                return ns;
            }
        }

        // Then delegate to parent
        return super.resolveTable(tableName);
    }

    @Override
    public List<INamespace> getVisibleNamespaces() {
        List<INamespace> visible = new ArrayList<>();

        // The FROM scope's own relations. The FROM scope's parent is this scope, so asking it for ITS visible
        // namespaces would come straight back here and never terminate (Mantis 4820).
        addOwnNamespaces(visible, fromScope);

        // Add parent's namespaces
        addAllDistinct(visible, getLookupParent().getVisibleNamespaces());

        return visible;
    }

    /**
     * Resolve a name within this SELECT scope.
     *
     * Resolution strategy:
     * 1. For qualified names (t.col): find table t in FROM scope, then resolve col
     * 2. For unqualified names (col): search all visible namespaces in FROM scope
     * 3. Delegate to parent for names not found locally
     */
    @Override
    public void resolve(List<String> names,
                       INameMatcher matcher,
                       boolean deep,
                       IResolved resolved) {
        if (names.isEmpty()) {
            return;
        }

        boolean foundLocally = false;

        // First, try to resolve via FROM scope
        if (fromScope != null) {
            if (names.size() >= 2) {
                // Qualified name like "t.col" - let FROM scope handle it
                // But don't let FROM scope delegate back to parent (we'll do that)
                String firstName = names.get(0);

                // Check if first name matches any child in FROM scope
                for (ScopeChild child : fromScope.getChildren()) {
                    if (matcher.matches(child.getAlias(), firstName)) {
                        // Found matching table/subquery
                        INamespace namespace = child.getNamespace();
                        List<String> remaining = names.subList(1, names.size());
                        resolved.found(namespace, child.isNullable(), this, new ResolvePath(), remaining);
                        foundLocally = true;
                    }
                }
            } else {
                // Single name - could be a table alias or unqualified column
                String name = names.get(0);

                // First check if it's a table alias
                for (ScopeChild child : fromScope.getChildren()) {
                    if (matcher.matches(child.getAlias(), name)) {
                        // It's a table alias - resolve as table
                        resolved.found(child.getNamespace(), child.isNullable(),
                                      this, new ResolvePath(), Collections.emptyList());
                        foundLocally = true;
                    }
                }

                // If not a table alias, try to resolve as unqualified column
                if (!foundLocally) {
                    // First, determine if we have a "sole implicit derived table" scenario
                    // According to teradata_implicit_derived_tables_zh.md:
                    // "当前作用域只可见 1 张表：未限定列默认归属该表"
                    // If there are NO explicit tables AND exactly 1 implicit derived table,
                    // unqualified columns should be linked to that implicit derived table
                    List<INamespace> explicitTables = new ArrayList<>();
                    List<INamespace> implicitDerivedTables = new ArrayList<>();

                    for (ScopeChild child : fromScope.getChildren()) {
                        INamespace ns = child.getNamespace();
                        TTable table = ns.getFinalTable();
                        if (table != null && table.getEffectType() == ETableEffectType.tetImplicitLateralDerivedTable) {
                            implicitDerivedTables.add(ns);
                        } else {
                            explicitTables.add(ns);
                        }
                    }

                    // If no explicit tables and exactly 1 implicit derived table,
                    // include it for unqualified column resolution
                    boolean useSoleImplicitDerivedTable = explicitTables.isEmpty() &&
                                                           implicitDerivedTables.size() == 1;

                    // Search through all visible namespaces
                    List<INamespace> candidates = new ArrayList<>();
                    List<INamespace> maybeCandidates = new ArrayList<>();

                    for (ScopeChild child : fromScope.getChildren()) {
                        INamespace ns = child.getNamespace();

                        // Skip implicit lateral derived tables (Teradata-specific)
                        // They should only match qualified references like "employee.employee_id"
                        // UNLESS this is a "sole implicit derived table" scenario where there
                        // are no explicit tables and exactly 1 implicit derived table
                        TTable table = ns.getFinalTable();
                        if (table != null && table.getEffectType() == ETableEffectType.tetImplicitLateralDerivedTable) {
                            if (!useSoleImplicitDerivedTable) {
                                continue;
                            }
                        }

                        ColumnLevel level = ns.hasColumn(name);

                        if (level == ColumnLevel.EXISTS) {
                            candidates.add(ns);
                        } else if (level == ColumnLevel.MAYBE) {
                            // Star column or table without metadata - might have this column
                            maybeCandidates.add(ns);
                        }
                    }

                    // If we found exact matches, use them
                    if (!candidates.isEmpty()) {
                        for (INamespace ns : candidates) {
                            resolved.found(ns, false, this, new ResolvePath(), names);
                        }
                        foundLocally = true;
                    }
                    // If we found MAYBE matches (star columns), prioritize subqueries that can
                    // trace columns through qualified references over tables with just inferred columns
                    else if (!maybeCandidates.isEmpty()) {
                        // Separate subqueries that can trace the column from tables that can't
                        List<INamespace> tracedCandidates = new ArrayList<>();
                        List<INamespace> inferredCandidates = new ArrayList<>();
                        // Mantis 4550: an ambiguous-star subquery (SELECT * over a
                        // multi-table FROM) cannot trace an unqualified column to a
                        // single base table, so it was historically dropped here
                        // ("will be missed"). But at THIS (outer) scope the subquery
                        // as a whole is a legitimate source for the column, so when
                        // there is at least one OTHER candidate the column is genuinely
                        // ambiguous across the derived tables. Collect these separately
                        // and only report them when doing so yields >= 2 candidates,
                        // preserving the "missed" behavior for a lone ambiguous star.
                        List<INamespace> ambiguousStarMaybeCandidates = new ArrayList<>();

                        for (INamespace ns : maybeCandidates) {
                            if (ns instanceof gudusoft.gsqlparser.resolver2.namespace.SubqueryNamespace) {
                                gudusoft.gsqlparser.resolver2.namespace.SubqueryNamespace subNs =
                                    (gudusoft.gsqlparser.resolver2.namespace.SubqueryNamespace) ns;
                                // Check if this subquery can trace the column through qualified refs
                                // A subquery with SELECT * and JOIN condition can trace columns
                                if (subNs.hasStarColumn()) {
                                    // Try to resolve the column - if it returns a source with specific table, prefer it
                                    gudusoft.gsqlparser.resolver2.model.ColumnSource source = subNs.resolveColumn(name);
                                    if (source != null) {
                                        // Check if the source has a specific table (unique resolution)
                                        // or has evidence of qualified/traced resolution
                                        if (source.getFinalTable() != null ||
                                            (source.getEvidence() != null &&
                                             (source.getEvidence().contains("traced") ||
                                              source.getEvidence().contains("qualified") ||
                                              source.getConfidence() >= 0.9))) {
                                            tracedCandidates.add(ns);
                                        } else if (!subNs.hasAmbiguousStar()) {
                                            // Only add to inferred if NOT ambiguous star
                                            // Ambiguous star (SELECT * from multiple tables) means column is truly ambiguous
                                            inferredCandidates.add(ns);
                                        } else {
                                            // Ambiguous star, no traced resolution: outer-scope candidate
                                            ambiguousStarMaybeCandidates.add(ns);
                                        }
                                    } else if (!subNs.hasAmbiguousStar()) {
                                        // Only add to inferred if NOT ambiguous star
                                        inferredCandidates.add(ns);
                                    } else {
                                        // Ambiguous star, column not traceable: outer-scope candidate
                                        ambiguousStarMaybeCandidates.add(ns);
                                    }
                                } else {
                                    tracedCandidates.add(ns); // Non-star subquery, use as-is
                                }
                            } else {
                                // TableNamespace without metadata - add as candidate
                                inferredCandidates.add(ns);
                            }
                        }

                        // Prefer traced candidates over inferred candidates. In either
                        // case, also surface ambiguous-star siblings when including them
                        // produces a genuinely ambiguous (>= 2 candidate) result, so the
                        // outer column is reported as ambiguous across all derived tables
                        // rather than collapsing onto the single traceable one.
                        List<INamespace> primary = !tracedCandidates.isEmpty() ? tracedCandidates
                                : (!inferredCandidates.isEmpty() ? inferredCandidates : null);
                        int totalWithAmbStar = (primary != null ? primary.size() : 0)
                                + ambiguousStarMaybeCandidates.size();
                        boolean includeAmbStar = !ambiguousStarMaybeCandidates.isEmpty()
                                && totalWithAmbStar >= 2;
                        if (primary != null) {
                            for (INamespace ns : primary) {
                                resolved.found(ns, false, this, new ResolvePath(), names);
                            }
                            if (includeAmbStar) {
                                for (INamespace ns : ambiguousStarMaybeCandidates) {
                                    resolved.found(ns, false, this, new ResolvePath(), names);
                                }
                            }
                            foundLocally = true;
                        } else if (includeAmbStar) {
                            for (INamespace ns : ambiguousStarMaybeCandidates) {
                                resolved.found(ns, false, this, new ResolvePath(), names);
                            }
                            foundLocally = true;
                        }
                    }
                }
            }
        }

        // If not found locally, delegate to parent (but NOT back through FROM scope)
        if (!foundLocally) {
            getLookupParent().resolve(names, matcher, deep, resolved);
        }
    }

    @Override
    public String toString() {
        return String.format("SelectScope(from=%s)", fromScope != null ? "present" : "null");
    }

    /** Records a JOIN USING merge of this scope. */
    public void addUsingMerge(UsingMerge merge) {
        if (merge != null) {
            usingMerges.add(merge);
        }
    }

    /**
     * The merge that covers this column name and EVERY one of the given relations, or null.
     *
     * <p>Requiring the merge to cover every candidate is what keeps a third relation carrying the
     * same name from being swallowed: those candidates are not merged with anything and the
     * reference really is ambiguous.
     */
    public UsingMerge findCoveringMerge(gudusoft.gsqlparser.EDbVendor vendor, String columnName,
                                        java.util.List<gudusoft.gsqlparser.nodes.TTable> candidateTables) {
        return findCoveringMerge(gudusoft.gsqlparser.sqlenv.IdentifierService.defaultServiceFor(vendor),
                columnName, candidateTables);
    }

    /**
     * {@link #findCoveringMerge(gudusoft.gsqlparser.EDbVendor, String, java.util.List)} with the
     * column names compared under one analysis's identifier rules (a configured profile).
     */
    public UsingMerge findCoveringMerge(gudusoft.gsqlparser.sqlenv.IdentifierService names, String columnName,
                                        java.util.List<gudusoft.gsqlparser.nodes.TTable> candidateTables) {
        if (candidateTables == null || candidateTables.isEmpty()) {
            return null;
        }
        for (UsingMerge merge : usingMerges) {
            if (!merge.merges(names, columnName)) {
                continue;
            }
            boolean coversAll = true;
            for (gudusoft.gsqlparser.nodes.TTable table : candidateTables) {
                if (!merge.hasParticipant(table)) {
                    coversAll = false;
                    break;
                }
            }
            if (coversAll) {
                return merge;
            }
        }
        return null;
    }

}