using Invercargill.DataStructures;
using Invercargill.Expressions;
using InvercargillSql.Migrations;
using InvercargillSql.Orm;
using InvercargillSql.Orm.Projections;

namespace InvercargillSql.Dialects {

    /**
     * PostgreSQL implementation of the SqlDialect interface: type translations,
     * SQL generation and schema introspection for PostgreSQL databases.
     *
     * Placeholders: the CRUD builders emit named ":column" placeholders, exactly
     * like the SQLite dialect. PostgresCommand rewrites ":name" placeholders to
     * positional "$n" references at execution time, numbering them in order of
     * first occurrence and binding values from the named parameter dictionary,
     * so OrmSession's by-name binding works unchanged. The shared
     * ExpressionToSqlVisitor likewise emits ":pN" placeholders which the adapter
     * translates the same way.
     */
    public class PostgresDialect : Object, SqlDialect {

        /** Translates a ColumnType to the PostgreSQL type system. */
        public string translate_type(ColumnType type) {
            switch (type) {
                case ColumnType.INT_32:
                    return "INTEGER";
                case ColumnType.INT_64:
                    return "BIGINT";
                case ColumnType.BOOLEAN:
                    return "BOOLEAN";
                case ColumnType.TEXT:
                    return "TEXT";
                case ColumnType.UUID:
                    return "UUID";
                case ColumnType.DECIMAL:
                    return "DOUBLE PRECISION";
                case ColumnType.DATETIME:
                    // Timestamp with time zone (stored internally in UTC)
                    return "TIMESTAMPTZ";
                case ColumnType.BINARY:
                    return "BYTEA";
                default:
                    return "TEXT";
            }
        }

        /**
         * Translates an expression tree to SQL.
         *
         * Mirrors SqliteDialect: filter expressions are translated at the call
         * sites (EntityQuery, ProjectionSqlBuilder) via ExpressionToSqlVisitor,
         * whose ":pN" placeholders PostgresCommand converts to "$n".
         */
        public string translate_expression(Expression expr, EntityMapper mapper) {
            return "";
        }

        /** Builds a SELECT statement for all rows in a table. */
        public string build_select_all(string table_name) {
            return "SELECT * FROM %s".printf(table_name);
        }

        /** Builds a SELECT for one row by ID with a named ":column" placeholder. */
        public string build_select_by_id(string table_name, string id_column) {
            return "SELECT * FROM %s WHERE %s = :%s".printf(table_name, id_column, id_column);
        }

        /**
         * Builds an INSERT with named ":column" placeholders.
         *
         * The adapter numbers them "$1..$n" in order of first occurrence, which
         * is the order of the columns vector, so binding is deterministic.
         */
        public string build_insert_sql(string table_name, Vector<string> columns) {
            var cols = new string[0];
            var placeholders = new string[0];
            foreach (var col in columns) {
                cols += col;
                placeholders += ":" + col;
            }
            return "INSERT INTO %s (%s) VALUES (%s)".printf(
                table_name,
                string.joinv(", ", cols),
                string.joinv(", ", placeholders)
            );
        }

        /**
         * Builds an UPDATE with named ":column" placeholders.
         *
         * The ID column is excluded from the SET clause and used only in the
         * WHERE clause.
         */
        public string build_update_sql(string table_name, Vector<string> columns, string id_column) {
            var set_clauses = new string[0];
            foreach (var col in columns) {
                if (col != id_column) {
                    set_clauses += "%s = :%s".printf(col, col);
                }
            }
            return "UPDATE %s SET %s WHERE %s = :%s".printf(
                table_name,
                string.joinv(", ", set_clauses),
                id_column,
                id_column
            );
        }

        /** Builds a DELETE with a named ":column" placeholder for the ID. */
        public string build_delete_sql(string table_name, string id_column) {
            return "DELETE FROM %s WHERE %s = :%s".printf(table_name, id_column, id_column);
        }

        /**
         * Builds a CREATE TABLE statement.
         *
         * Uses IF NOT EXISTS so re-running schema creation is idempotent.
         */
        public string build_create_table(string table_name, Vector<ColumnDefinition> columns) {
            var column_defs = new string[0];
            foreach (var col in columns) {
                column_defs += build_column_def(col);
            }
            return "CREATE TABLE IF NOT EXISTS %s (%s)".printf(
                table_name,
                string.joinv(", ", column_defs)
            );
        }

        /** Builds a CREATE INDEX statement (IF NOT EXISTS for idempotency). */
        public string build_create_index(string table_name, IndexDefinition index) {
            var unique = index.is_unique ? "UNIQUE " : "";
            var cols = new string[0];
            foreach (var col in index.columns) {
                cols += col;
            }
            return "CREATE %sINDEX IF NOT EXISTS %s ON %s (%s)".printf(
                unique,
                index.name,
                table_name,
                string.joinv(", ", cols)
            );
        }

        /**
         * Builds a single column definition for CREATE TABLE.
         *
         * Auto-increment primary keys become GENERATED ALWAYS AS IDENTITY
         * columns (PostgreSQL 10+), sized to match the base type.
         */
        private string build_column_def(ColumnDefinition col) {
            var parts = new string[0];
            parts += col.name;
            parts += translate_type(col.column_type);
            if (col.is_primary_key) {
                parts += "PRIMARY KEY";
            }
            if (col.auto_increment && col.is_primary_key) {
                parts += "GENERATED ALWAYS AS IDENTITY";
            }
            if (col.is_required && !col.is_primary_key) {
                parts += "NOT NULL";
            }
            if (col.is_unique && !col.is_primary_key) {
                parts += "UNIQUE";
            }
            if (col.default_now) {
                parts += "DEFAULT CURRENT_TIMESTAMP";
            }
            return string.joinv(" ", parts);
        }

        /**
         * Generates SQL for a CREATE TABLE migration operation with constraints.
         *
         * Multi-column PRIMARY KEY / UNIQUE / FOREIGN KEY constraints are
         * emitted as table constraints after the column definitions.
         */
        public string create_table_sql(CreateTableOperation op) {
            var sql = new StringBuilder();
            sql.append("CREATE TABLE ");
            sql.append(op.table_name);
            sql.append(" (\n");

            var parts = new Vector<string>();
            foreach (var col in op.columns) {
                parts.add(build_column_sql(col));
            }
            foreach (var constraint in op.constraints) {
                parts.add(build_constraint_sql(constraint));
            }

            bool first = true;
            foreach (var part in parts) {
                if (!first) {
                    sql.append(",\n");
                }
                first = false;
                sql.append("    ");
                sql.append(part);
            }
            sql.append("\n)");
            return sql.str;
        }

        /**
         * Builds a column definition SQL string for migration operations.
         *
         * An auto-increment integer primary key becomes a GENERATED ALWAYS AS
         * IDENTITY column (PostgreSQL 10+). IDENTITY is skipped when the
         * column also carries an explicit DEFAULT, which PostgreSQL forbids.
         */
        private string build_column_sql(ColumnDefinition col) {
            var parts = new Vector<string>();
            parts.add(col.name);
            parts.add(translate_type(col.column_type));

            if (col.is_primary_key) parts.add("PRIMARY KEY");
            if (col.auto_increment && col.default_value == null && !col.default_now) {
                parts.add("GENERATED ALWAYS AS IDENTITY");
            }
            if (col.is_required) parts.add("NOT NULL");
            if (col.is_unique) parts.add("UNIQUE");
            if (col.default_value != null) {
                parts.add("DEFAULT " + element_to_sql(col.default_value));
            }
            if (col.default_now) {
                parts.add("DEFAULT CURRENT_TIMESTAMP");
            }
            return string.joinv(" ", parts.to_array());
        }

        /**
         * Converts an Element default value to its SQL literal representation.
         *
         * Booleans render as TRUE/FALSE (not 1/0), timestamps as ISO 8601
         * text, binary data as hex-encoded BYTEA literals, and strings are
         * escaped by doubling single quotes. The E'' literal form is used
         * when the value contains a backslash.
         */
        private string element_to_sql(Invercargill.Element element) {
            if (element.is_null()) {
                return "NULL";
            }

            if (element.assignable_to_type(typeof(Invercargill.BinaryData))) {
                Invercargill.BinaryData? blob;
                if (element.try_get_as<Invercargill.BinaryData>(out blob) && blob != null) {
                    uint8[] blob_data = blob.to_array();
                    return "\\\\x" + bytes_to_hex(blob_data);
                }
            }

            if (element.assignable_to_type(typeof(bool))) {
                bool? b;
                if (element.try_get_as<bool>(out b) && b != null) {
                    return b ? "TRUE" : "FALSE";
                }
            }

            if (element.assignable_to_type(typeof(DateTime))) {
                DateTime? dt;
                if (element.try_get_as<DateTime>(out dt) && dt != null) {
                    return "'" + dt.format_iso8601() + "'";
                }
            }

            string? result = try_element_as_string(element);
            if (result != null) {
                return quote_string_literal(result);
            }
            return "NULL";
        }

        /**
         * Renders a SQL string literal, using E'' escaping when the value
         * contains a backslash (PostgreSQL treats backslashes specially in
         * ordinary literals when standard_conforming_strings is off).
         */
        private string quote_string_literal(string s) {
            string escaped = s.replace("'", "''");
            if (s.index_of("\\") >= 0) {
                escaped = escaped.replace("\\", "\\\\");
                return "E'" + escaped + "'";
            }
            return "'" + escaped + "'";
        }

        /** Lowercase hex encoding of a byte array. */
        private string bytes_to_hex(uint8[] data) {
            var sb = new StringBuilder();
            foreach (uint8 b in data) {
                sb.append_printf("%02x", b);
            }
            return sb.str;
        }

        /** Extracts a numeric or string representation of an Element, or null. */
        private string? try_element_as_string(Invercargill.Element element) {
            if (element.assignable_to_type(typeof(string))) {
                string? s;
                if (element.try_get_as<string>(out s) && s != null) {
                    return s;
                }
            }
            if (element.assignable_to_type(typeof(int64))) {
                int64? i;
                if (element.try_get_as<int64?>(out i) && i != null) {
                    return i.to_string();
                }
            }
            if (element.assignable_to_type(typeof(int))) {
                int? i;
                if (element.try_get_as<int?>(out i) && i != null) {
                    return i.to_string();
                }
            }
            if (element.assignable_to_type(typeof(long))) {
                long? l;
                if (element.try_get_as<long?>(out l) && l != null) {
                    return l.to_string();
                }
            }
            if (element.assignable_to_type(typeof(double))) {
                double? d;
                if (element.try_get_as<double?>(out d) && d != null) {
                    return d.to_string();
                }
            }
            return null;
        }

        /**
         * Builds a table constraint SQL string.
         *
         * Constraint types are the SQL-standard keywords ("PRIMARY KEY",
         * "UNIQUE", "FOREIGN KEY"), matching the SQLite implementation.
         */
        private string build_constraint_sql(TableConstraint constraint) {
            var sql = new StringBuilder();

            if (constraint.name != null) {
                sql.append("CONSTRAINT ");
                sql.append(constraint.name);
                sql.append(" ");
            }

            sql.append(constraint.constraint_type);
            sql.append(" (");
            sql.append(string.joinv(", ", constraint.columns.to_array()));
            sql.append(")");

            if (constraint.constraint_type == "FOREIGN KEY" && constraint.reference_table != null) {
                sql.append(" REFERENCES ");
                sql.append(constraint.reference_table);
                sql.append(" (");
                sql.append(string.joinv(", ", constraint.reference_columns.to_array()));
                sql.append(")");

                if (constraint.on_delete_action != ReferentialAction.NO_ACTION) {
                    sql.append(" ON DELETE ");
                    sql.append(constraint.on_delete_action.to_sql());
                }
                if (constraint.on_update_action != ReferentialAction.NO_ACTION) {
                    sql.append(" ON UPDATE ");
                    sql.append(constraint.on_update_action.to_sql());
                }
            }

            return sql.str;
        }

        /** Generates SQL for a DROP TABLE operation (IF EXISTS for idempotency). */
        public string drop_table_sql(DropTableOperation op) {
            return "DROP TABLE IF EXISTS " + op.table_name;
        }

        /**
         * Generates SQL for an ADD COLUMN operation.
         *
         * When the operation carries a foreign_key_constraint, the REFERENCES
         * clause is inlined in the column definition (PostgreSQL, unlike
         * SQLite, also accepts a separate ADD CONSTRAINT clause, but the
         * inline form keeps a single statement).
         */
        public string add_column_sql(AddColumnOperation op) {
            var sb = new StringBuilder();
            sb.append("ALTER TABLE ");
            sb.append(op.table_name);
            sb.append(" ADD COLUMN ");
            sb.append(build_column_sql(op.column));

            if (op.foreign_key_constraint != null) {
                var fk = op.foreign_key_constraint;
                sb.append(" REFERENCES ");
                sb.append(fk.reference_table);
                sb.append(" (");
                sb.append(string.joinv(", ", fk.reference_columns.to_array()));
                sb.append(")");

                if (fk.on_delete_action != ReferentialAction.NO_ACTION) {
                    sb.append(" ON DELETE ");
                    sb.append(fk.on_delete_action.to_sql());
                }
                if (fk.on_update_action != ReferentialAction.NO_ACTION) {
                    sb.append(" ON UPDATE ");
                    sb.append(fk.on_update_action.to_sql());
                }
            }

            return sb.str;
        }

        /** Generates SQL for a DROP COLUMN operation. */
        public string drop_column_sql(DropColumnOperation op) {
            return "ALTER TABLE " + op.table_name + " DROP COLUMN " + op.column_name;
        }

        /** Generates SQL for a RENAME COLUMN operation. */
        public string rename_column_sql(RenameColumnOperation op) {
            return "ALTER TABLE " + op.table_name + " RENAME COLUMN " + op.old_name + " TO " + op.new_name;
        }

        /** Generates SQL for a CREATE INDEX migration operation. */
        public string create_index_sql(CreateIndexOperation op) {
            var sql = new StringBuilder();
            sql.append(op.is_unique ? "CREATE UNIQUE INDEX " : "CREATE INDEX ");
            sql.append(op.index_name);
            sql.append(" ON ");
            sql.append(op.table_name);
            sql.append(" (");
            sql.append(string.joinv(", ", op.columns.to_array()));
            sql.append(")");
            return sql.str;
        }

        /** Generates SQL for a DROP INDEX migration operation (IF EXISTS). */
        public string drop_index_sql(DropIndexOperation op) {
            return "DROP INDEX IF EXISTS " + op.index_name;
        }

        /**
         * Generates SQL for a DROP FOREIGN KEY operation.
         *
         * PostgreSQL supports dropping constraints natively, so unlike the
         * SQLite dialect there is no table-recreation dance: the operation
         * maps directly to ALTER TABLE ... DROP CONSTRAINT. When the caller
         * identifies the constraint by column instead of by name, the
         * auto-generated name is derived the way PostgreSQL names FK
         * constraints (table_column_fkey).
         */
        public string drop_foreign_key_sql(DropForeignKeyOperation op) {
            var sb = new StringBuilder();
            sb.append("ALTER TABLE ");
            sb.append(op.table_name);
            sb.append(" DROP CONSTRAINT ");

            if (op.constraint_name != null) {
                sb.append(op.constraint_name);
            } else if (op.column_name != null) {
                // PostgreSQL names an unnamed FK constraint table_column_fkey
                sb.append(op.table_name);
                sb.append("_");
                sb.append(op.column_name);
                sb.append("_fkey");
            } else {
                // No usable identifier: keep the statement invalid on purpose
                // so the failure is visible rather than silently dropping
                // some other constraint.
                sb.append("<unnamed>");
            }

            return sb.str;
        }

        /** Quotes an identifier using PostgreSQL double-quote rules. */
        private string quote_identifier(string identifier) {
            return "\"" + identifier.replace("\"", "\"\"") + "\"";
        }

        // === Schema introspection ===

        /**
         * Introspects table schema from a PostgreSQL database.
         *
         * Column metadata comes from information_schema.columns; primary key
         * membership from information_schema.key_column_usage joined with
         * table_constraints. Identity columns (GENERATED ... AS IDENTITY) and
         * serial-style defaults are reported as auto-increment. Note that on
         * PostgreSQL 10+ identity columns report is_identity = 'YES' with an
         * empty column_default, so both signals are checked.
         *
         * @param connection The database connection
         * @param table_name The table to introspect
         * @return A TableSchema containing column metadata
         * @throws SqlError if introspection fails
         */
        public TableSchema introspect_schema(Connection connection, string table_name) throws SqlError {
            var schema = new TableSchema();
            schema.table_name = table_name;

            var columns_sql = new StringBuilder();
            columns_sql.append("SELECT column_name, data_type, is_nullable, column_default, is_identity ");
            columns_sql.append("FROM information_schema.columns ");
            columns_sql.append("WHERE table_name = :table_name ");
            columns_sql.append("ORDER BY ordinal_position");
            var command = connection.create_command(columns_sql.str);
            command.with_parameter<string>("table_name", table_name);
            var results = command.execute_query();

            foreach (var row in results) {
                var column = new ColumnSchema();

                var name_elem = row.get("column_name");
                if (name_elem != null) {
                    string? name_val = null;
                    name_elem.try_get_as<string>(out name_val);
                    column.name = name_val ?? "";
                }

                var type_elem = row.get("data_type");
                if (type_elem != null) {
                    string? type_str = null;
                    type_elem.try_get_as<string>(out type_str);
                    column.column_type = parse_postgres_type(type_str ?? "text");
                }

                var nullable_elem = row.get("is_nullable");
                if (nullable_elem != null) {
                    string? nullable = null;
                    nullable_elem.try_get_as<string>(out nullable);
                    column.is_required = (nullable == "NO");
                }

                var default_elem = row.get("column_default");
                if (default_elem != null && !default_elem.is_null()) {
                    string? default_str = null;
                    default_elem.try_get_as<string>(out default_str);
                    column.auto_increment = is_identity_default(default_str);
                }

                var identity_elem = row.get("is_identity");
                if (identity_elem != null && !identity_elem.is_null()) {
                    string? identity_str = null;
                    identity_elem.try_get_as<string>(out identity_str);
                    if (identity_str == "YES") {
                        column.auto_increment = true;
                    }
                }

                schema.columns.add(column);
            }

            // Determine the primary key column(s)
            var pk_sql = new StringBuilder();
            pk_sql.append("SELECT kcu.column_name ");
            pk_sql.append("FROM information_schema.table_constraints tc ");
            pk_sql.append("JOIN information_schema.key_column_usage kcu ");
            pk_sql.append("  ON tc.constraint_name = kcu.constraint_name ");
            pk_sql.append(" AND tc.table_name = kcu.table_name ");
            pk_sql.append("WHERE tc.constraint_type = 'PRIMARY KEY' ");
            pk_sql.append("  AND tc.table_name = :table_name ");
            pk_sql.append("ORDER BY kcu.ordinal_position");
            var pk_command = connection.create_command(pk_sql.str);
            pk_command.with_parameter<string>("table_name", table_name);
            var pk_results = pk_command.execute_query();

            foreach (var pk_row in pk_results) {
                var pk_elem = pk_row.get("column_name");
                if (pk_elem == null) {
                    continue;
                }
                string? pk_name = null;
                pk_elem.try_get_as<string>(out pk_name);
                if (pk_name == null) {
                    continue;
                }

                var col = schema.get_column(pk_name);
                if (col != null) {
                    col.is_primary_key = true;
                    if (schema.primary_key_column == null) {
                        schema.primary_key_column = pk_name;
                    }
                    // Integer PKs created by this dialect are identity columns
                    if (col.column_type == ColumnType.INT_64
                        || col.column_type == ColumnType.INT_32) {
                        col.auto_increment = true;
                    }
                }
            }

            return schema;
        }

        /**
         * Reports whether a column_default string denotes an auto-generated
         * identity value (IDENTITY columns or old-style serial sequences).
         */
        private bool is_identity_default(string? default_str) {
            if (default_str == null) {
                return false;
            }
            var upper = default_str.up();
            if ("GENERATED" in upper && "IDENTITY" in upper) {
                return true;
            }
            // serial pseudo-types surface as nextval('..._seq'::regclass)
            if ("NEXTVAL" in upper) {
                return true;
            }
            return false;
        }

        /**
         * Parses a PostgreSQL data_type string (as reported by
         * information_schema.columns) to a ColumnType.
         *
         * Matching is keyword-based, mirroring the SQLite implementation.
         *
         * @param type_str The PostgreSQL type string
         * @return The corresponding ColumnType
         */
        private ColumnType parse_postgres_type(string type_str) {
            var upper = type_str.up();

            if ("BIGINT" in upper || "INT8" in upper) {
                return ColumnType.INT_64;
            }
            if ("SMALLINT" in upper || "INTEGER" in upper || "INT4" in upper
                || "INT" == upper.strip() || "SERIAL" in upper) {
                return ColumnType.INT_32;
            }
            if ("UUID" in upper) {
                return ColumnType.UUID;
            }
            if ("BYTEA" in upper || "BLOB" in upper || "BINARY" in upper) {
                return ColumnType.BINARY;
            }
            if ("BOOLEAN" in upper || "BOOL" in upper) {
                return ColumnType.BOOLEAN;
            }
            if ("TIMESTAMP" in upper || "DATE" in upper || "TIME" in upper) {
                return ColumnType.DATETIME;
            }
            if ("DOUBLE" in upper || "REAL" in upper || "FLOAT" in upper
                || "NUMERIC" in upper || "DECIMAL" in upper) {
                return ColumnType.DECIMAL;
            }
            if ("CHAR" in upper || "CLOB" in upper || "TEXT" in upper
                || "JSON" in upper || "XML" in upper) {
                return ColumnType.TEXT;
            }

            // Default to TEXT
            return ColumnType.TEXT;
        }

        // === Projection query methods ===

        /**
         * Builds a SELECT statement with JOINs for a projection query.
         *
         * Constructs a complete SELECT from a ProjectionDefinition including
         * all JOINs, selections, WHERE/HAVING clauses, GROUP BY, ORDER BY,
         * LIMIT and OFFSET — all standard SQL on PostgreSQL.
         */
        public string build_projection_select(
            ProjectionDefinition definition,
            VariableTranslator translator,
            string? where_clause,
            string? having_clause,
            Vector<OrderByClause> order_by,
            int64? limit,
            int64? offset
        ) {
            var sql = new StringBuilder();

            sql.append("SELECT ");
            build_select_clause(sql, definition, translator);

            sql.append("\nFROM ");
            build_from_clause(sql, definition, translator);

            foreach (var join in definition.joins) {
                sql.append("\n");
                build_join_clause(sql, join, translator);
            }

            if (where_clause != null && where_clause.length > 0) {
                sql.append("\nWHERE ");
                sql.append(where_clause);
            }

            if (definition.group_by_expressions.length > 0) {
                sql.append("\nGROUP BY ");
                bool first = true;
                foreach (var group_expr in definition.group_by_expressions) {
                    if (!first) {
                        sql.append(", ");
                    }
                    first = false;
                    sql.append(translator.translate_expression(group_expr));
                }
            }

            if (having_clause != null && having_clause.length > 0) {
                sql.append("\nHAVING ");
                sql.append(having_clause);
            }

            if (order_by.length > 0) {
                sql.append("\nORDER BY ");
                bool first = true;
                foreach (var order in order_by) {
                    if (!first) {
                        sql.append(", ");
                    }
                    first = false;
                    sql.append(translator.translate_expression(order.expression));
                    if (order.descending) {
                        sql.append(" DESC");
                    }
                }
            }

            if (limit != null) {
                sql.append("\nLIMIT ");
                sql.append(limit.to_string());
            }

            if (offset != null) {
                sql.append("\nOFFSET ");
                sql.append(offset.to_string());
            }

            return sql.str;
        }

        /**
         * Builds the SELECT clause with all selections.
         *
         * Each selection is formatted as: expression AS unique_alias.
         * Scalar selections and SCALAR-mode collections translate their
         * expressions; nested projections and non-scalar collections use
         * NULL placeholders, matching the SQLite behaviour.
         */
        private void build_select_clause(
            StringBuilder sql,
            ProjectionDefinition definition,
            VariableTranslator translator
        ) {
            bool first = true;
            int column_index = 0;

            foreach (var selection in definition.selections) {
                if (!first) {
                    sql.append(", ");
                }
                first = false;
                column_index++;

                string column_alias = @"col_$(column_index)_$(definition.projection_type.name())_$(selection.friendly_name)";
                selection.column_alias = column_alias;

                var scalar_selection = selection as ScalarSelection<Object, Object>;
                if (scalar_selection != null) {
                    string translated_expr = translator.translate_expression(scalar_selection.expression);
                    sql.append(translated_expr);
                    sql.append(" AS ");
                    sql.append(column_alias);
                    continue;
                }

                var collection_selection = selection as CollectionSelection;
                if (collection_selection != null && collection_selection.item_mode == CollectionItemMode.SCALAR) {
                    string translated_expr = translator.translate_expression(collection_selection.entry_point_expression);
                    sql.append(translated_expr);
                    sql.append(" AS ");
                    sql.append(column_alias);
                    continue;
                }

                sql.append("NULL AS ");
                sql.append(column_alias);
            }
        }

        /**
         * Builds the FROM clause with the primary source.
         *
         * Format: table_name alias
         */
        private void build_from_clause(
            StringBuilder sql,
            ProjectionDefinition definition,
            VariableTranslator translator
        ) {
            if (definition.source == null) {
                return;
            }

            sql.append(definition.source.table_name);
            sql.append(" ");
            sql.append(translator.translate_variable(definition.source.variable_name));
        }

        /**
         * Builds a JOIN clause for a join definition.
         *
         * Format: LEFT JOIN table_name alias ON condition
         */
        private void build_join_clause(
            StringBuilder sql,
            JoinDefinition join,
            VariableTranslator translator
        ) {
            sql.append("LEFT JOIN ");
            sql.append(join.table_name);
            sql.append(" ");
            sql.append(translator.translate_variable(join.variable_name));
            sql.append(" ON ");
            sql.append(translator.translate_expression(join.join_condition));
        }

        /**
         * Builds a subquery wrapper for mixed aggregate/non-aggregate OR
         * conditions: SELECT * FROM (inner) subq WHERE combined_where.
         */
        public string wrap_subquery_for_mixed_or(
            string inner_query,
            string combined_where
        ) {
            return @"SELECT * FROM ($inner_query) subq WHERE $combined_where";
        }

        /**
         * Generates a table alias with type information for debugging.
         *
         * Format: val_N_TypeName (e.g., val_1_User, val_2_Order)
         */
        public string generate_table_alias(int index, string type_name) {
            return "val_%d_%s".printf(index, type_name);
        }
    }
}