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

namespace InvercargillSql.Dialects {
    
    /**
     * Interface for SQL dialects.
     * 
     * SqlDialect provides methods for translating types, expressions,
     * and generating SQL statements for a specific database backend.
     * 
     * It also provides schema introspection capabilities for discovering
     * database metadata at runtime.
     */
    public interface SqlDialect : Object {
        
        /**
         * Translates a ColumnType to the database-specific type string.
         * 
         * @param type The column type to translate
         * @return The database-specific type string
         */
        public abstract string translate_type(ColumnType type);
        
        /**
         * Translates an expression tree to SQL.
         * 
         * @param expr The expression to translate
         * @param mapper The entity mapper for column resolution
         * @return The SQL string representation
         */
        public abstract string translate_expression(Expression expr, EntityMapper mapper);
        
        /**
         * Builds a SELECT statement for all rows in a table.
         * 
         * @param table_name The name of the table
         * @return The SQL SELECT statement
         */
        public abstract string build_select_all(string table_name);
        
        /**
         * Builds a SELECT statement for a single row by ID.
         * 
         * @param table_name The name of the table
         * @param id_column The name of the ID column
         * @return The SQL SELECT statement with parameter placeholder
         */
        public abstract string build_select_by_id(string table_name, string id_column);
        
        /**
         * Builds an INSERT statement.
         * 
         * @param table_name The name of the table
         * @param columns The column names to insert into
         * @return The SQL INSERT statement with parameter placeholders
         */
        public abstract string build_insert_sql(string table_name, Vector<string> columns);
        
        /**
         * Builds an UPDATE statement.
         * 
         * @param table_name The name of the table
         * @param columns The column names to update
         * @param id_column The name of the ID column for the WHERE clause
         * @return The SQL UPDATE statement with parameter placeholders
         */
        public abstract string build_update_sql(string table_name, Vector<string> columns, string id_column);
        
        /**
         * Builds a DELETE statement.
         * 
         * @param table_name The name of the table
         * @param id_column The name of the ID column for the WHERE clause
         * @return The SQL DELETE statement with parameter placeholder
         */
        public abstract string build_delete_sql(string table_name, string id_column);
        
        /**
         * Builds a CREATE TABLE statement.
         * 
         * @param table_name The name of the table
         * @param columns The column definitions
         * @return The SQL CREATE TABLE statement
         */
        public abstract string build_create_table(string table_name, Vector<ColumnDefinition> columns);
        
        /**
         * Builds a CREATE INDEX statement.
         * 
         * @param table_name The name of the table
         * @param index The index definition
         * @return The SQL CREATE INDEX statement
         */
        public abstract string build_create_index(string table_name, IndexDefinition index);
        
        // Migration DDL methods
        
        /**
         * Generates SQL for a CREATE TABLE operation.
         * 
         * @param op The create table operation
         * @return The SQL CREATE TABLE statement
         */
        public abstract string create_table_sql(CreateTableOperation op);
        
        /**
         * Generates SQL for a DROP TABLE operation.
         * 
         * @param op The drop table operation
         * @return The SQL DROP TABLE statement
         */
        public abstract string drop_table_sql(DropTableOperation op);
        
        /**
         * Generates SQL for an ADD COLUMN operation.
         * 
         * @param op The add column operation
         * @return The SQL ALTER TABLE ADD COLUMN statement
         */
        public abstract string add_column_sql(AddColumnOperation op);
        
        /**
         * Generates SQL for a DROP COLUMN operation.
         * 
         * @param op The drop column operation
         * @return The SQL ALTER TABLE DROP COLUMN statement
         */
        public abstract string drop_column_sql(DropColumnOperation op);
        
        /**
         * Generates SQL for a RENAME COLUMN operation.
         * 
         * @param op The rename column operation
         * @return The SQL ALTER TABLE RENAME COLUMN statement
         */
        public abstract string rename_column_sql(RenameColumnOperation op);
        
        /**
         * Generates SQL for a CREATE INDEX operation.
         * 
         * @param op The create index operation
         * @return The SQL CREATE INDEX statement
         */
        public abstract string create_index_sql(CreateIndexOperation op);
        
        /**
         * Generates SQL for a DROP INDEX operation.
         * 
         * @param op The drop index operation
         * @return The SQL DROP INDEX statement
         */
        public abstract string drop_index_sql(DropIndexOperation op);
        
        /**
         * Generates SQL for a DROP FOREIGN KEY operation.
         * 
         * Note: SQLite requires table recreation to drop constraints,
         * which is handled transparently by the dialect implementation.
         * 
         * @param op The drop foreign key operation
         * @return The SQL statement(s) to drop the foreign key constraint
         */
        public abstract string drop_foreign_key_sql(DropForeignKeyOperation op);
        
        // Schema introspection methods
        
        /**
         * Introspects table schema from the database.
         * 
         * This method queries the database to discover column metadata,
         * primary keys, auto-increment settings, and other schema information.
         * 
         * @param connection The database connection
         * @param table_name The table to introspect
         * @return A TableSchema containing column metadata
         * @throws SqlError if introspection fails
         */
        public abstract TableSchema introspect_schema(Connection connection, string table_name) throws SqlError;
        
        // Projection query methods
        
        /**
         * Builds a SELECT statement with JOINs for a projection query.
         * 
         * This method constructs a complete SELECT query from a ProjectionDefinition,
         * including all JOINs, selections, WHERE/HAVING clauses, GROUP BY, ORDER BY,
         * LIMIT, and OFFSET.
         * 
         * @param definition The projection definition containing source, joins, and selections
         * @param translator The variable translator for alias resolution
         * @param where_clause Optional pre-built WHERE clause SQL
         * @param having_clause Optional pre-built HAVING clause SQL
         * @param order_by ORDER BY clauses
         * @param limit Optional LIMIT value
         * @param offset Optional OFFSET value
         * @return The complete SQL SELECT statement
         */
        public abstract string build_projection_select(
            ProjectionDefinition definition,
            VariableTranslator translator,
            string? where_clause,
            string? having_clause,
            Vector<OrderByClause> order_by,
            int64? limit,
            int64? offset
        );
        
        /**
         * Builds a subquery wrapper for mixed aggregate/non-aggregate OR conditions.
         * 
         * When a WHERE clause contains OR conditions that mix aggregate and non-aggregate
         * expressions, SQL requires special handling. This method wraps the inner query
         * and applies the combined WHERE clause to the outer query.
         * 
         * Example output:
         * {{{
         * SELECT * FROM (inner_query) subq WHERE combined_where
         * }}}
         * 
         * @param inner_query The inner SELECT query
         * @param combined_where The WHERE clause to apply to the outer query
         * @return The wrapped query SQL
         */
        public abstract string wrap_subquery_for_mixed_or(
            string inner_query,
            string combined_where
        );
        
        /**
         * Generates a table alias with type information for debugging.
         * 
         * The alias format includes an index and the entity type name to make
         * generated SQL more readable and easier to debug.
         * 
         * Format: val_N_TypeName (e.g., val_1_User, val_2_Order)
         * 
         * @param index The 1-based index for this alias
         * @param type_name The entity type name
         * @return The generated alias string
         */
        public abstract string generate_table_alias(int index, string type_name);
    }
}