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

namespace InvercargillSql.Orm {
    
    /**
     * Main entry point for ORM operations.
     * 
     * OrmSession coordinates entity mappers, database connections, and SQL dialects
     * to provide a high-level API for database operations.
     * 
     * Example usage:
     * {{{
     * var registry = new TypeRegistry();
     * // Register types with registry first...
     * var session = new OrmSession(connection, registry, new SqliteDialect());
     * 
     * var users = session.query<User>()
     *     .where("name LIKE 'A%'")
     *     .materialise();
     * }}}
     */
    public class OrmSession : Object {
        private Connection _connection;
        private SqlDialect _dialect;
        private TypeProvider _type_provider;
        
        /**
         * Creates a new OrmSession.
         * 
         * @param connection The database connection to use
         * @param type_provider The type provider for entity mappers and projections
         * @param dialect The SQL dialect to use (defaults to SqliteDialect if null)
         */
        public OrmSession(Connection connection, TypeProvider type_provider, SqlDialect? dialect = null) {
            _connection = connection;
            _type_provider = type_provider;
            _dialect = dialect ?? new SqliteDialect();
        }
        
        /**
         * Creates a new query for type T.
         *
         * Returns EntityQuery<T> if T is a registered entity type.
         * Returns ProjectionQuery<T> if T is a registered projection type.
         *
         * @return A new Query<T> instance appropriate for type T
         * @throws SqlError.GENERAL_ERROR if T is neither a registered entity nor projection
         */
        public Query<T> query<T>() throws Error {
            var type = typeof(T);
            
            // Check if it's a registered entity
            var mapper = _type_provider.get_mapper_for_type(type);
            if (mapper != null) {
                return new EntityQuery<T>(this);
            }
            
            // Check if it's a registered projection
            var projection_def = _type_provider.get_projection_for_type(type);
            if (projection_def != null) {
                var sql_builder = new ProjectionSqlBuilder(projection_def, _dialect);
                return new ProjectionQuery<T>(this, projection_def, sql_builder);
            }
            
            throw new SqlError.GENERAL_ERROR(
                "Type %s is not registered as an entity or projection".printf(type.name())
            );
        }
        
        /**
         * Inserts an entity into the database.
         * 
         * @param entity The entity to insert
         * @throws SqlError if insertion fails
         */
        public void insert<T>(T entity) throws Error {
            var mapper = get_mapper<T>();
            Invercargill.Properties properties;
            try {
                properties = mapper.map_from(entity);
            } catch (Error e) {
                throw new SqlError.GENERAL_ERROR("Failed to map entity: %s".printf(e.message));
            }
            
            // Build column list, excluding auto-increment columns (discovered via schema introspection)
            var columns = new Vector<string>();
            foreach (var col in mapper.columns) {
                if (!mapper.is_auto_increment(col.name)) {
                    columns.add(col.name);
                }
            }
            
            var sql = _dialect.build_insert_sql(mapper.table_name, columns);
            var command = _connection.create_command(sql);
            
            // Add parameters in column order (excluding auto-increment)
            foreach (var col in mapper.columns) {
                if (mapper.is_auto_increment(col.name)) {
                    continue;  // Skip auto-increment columns
                }
                var value = properties.get(col.name);
                if (value != null) {
                    command.with_parameter<Invercargill.Element>(col.name, value);
                } else {
                    command.with_null(col.name);
                }
            }
            
            command.execute_non_query();
            
            // Back-populate the generated primary key
            var pk_column = mapper.get_effective_primary_key();
            if (pk_column != null && mapper.is_auto_increment(pk_column)) {
                int64 generated_id = _connection.last_insert_rowid;
                try {
                    mapper.set_property_value(entity, pk_column, generated_id);
                } catch (Error e) {
                    throw new SqlError.GENERAL_ERROR("Failed to back-populate primary key: %s".printf(e.message));
                }
            }
        }
        
        /**
         * Inserts an entity into the database asynchronously.
         *
         * This method performs the same operation as insert() but in a non-blocking
         * manner, allowing the main loop to process other events while waiting
         * for the database operation to complete.
         *
         * After insertion, if the entity has an auto-increment primary key,
         * the key value is back-populated into the entity.
         *
         * @param entity The entity to insert
         * @throws SqlError if insertion fails
         */
        public async void insert_async<T>(T entity) throws Error {
            var mapper = get_mapper<T>();
            Invercargill.Properties properties;
            try {
                properties = mapper.map_from(entity);
            } catch (Error e) {
                throw new SqlError.GENERAL_ERROR("Failed to map entity: %s".printf(e.message));
            }
            
            // Build column list, excluding auto-increment columns
            var columns = new Vector<string>();
            foreach (var col in mapper.columns) {
                if (!mapper.is_auto_increment(col.name)) {
                    columns.add(col.name);
                }
            }
            
            var sql = _dialect.build_insert_sql(mapper.table_name, columns);
            var command = _connection.create_command(sql);
            
            // Add parameters in column order (excluding auto-increment)
            foreach (var col in mapper.columns) {
                if (mapper.is_auto_increment(col.name)) {
                    continue;  // Skip auto-increment columns
                }
                var value = properties.get(col.name);
                if (value != null) {
                    command.with_parameter<Invercargill.Element>(col.name, value);
                } else {
                    command.with_null(col.name);
                }
            }
            
            // Execute asynchronously
            yield command.execute_non_query_async();
            
            // Back-populate the generated primary key
            var pk_column = mapper.get_effective_primary_key();
            if (pk_column != null && mapper.is_auto_increment(pk_column)) {
                int64 generated_id = _connection.last_insert_rowid;
                try {
                    mapper.set_property_value(entity, pk_column, generated_id);
                } catch (Error e) {
                    throw new SqlError.GENERAL_ERROR("Failed to back-populate primary key: %s".printf(e.message));
                }
            }
        }
        
        /**
         * Updates an entity in the database.
         * 
         * @param entity The entity to update
         * @throws SqlError if update fails
         */
        public void update<T>(T entity) throws Error {
            var mapper = get_mapper<T>();
            Invercargill.Properties properties;
            try {
                properties = mapper.map_from(entity);
            } catch (Error e) {
                throw new SqlError.GENERAL_ERROR("Failed to map entity: %s".printf(e.message));
            }
            
            var columns = new Vector<string>();
            foreach (var col in mapper.columns) {
                columns.add(col.name);
            }
            
            var pk_column = mapper.get_effective_primary_key();
            var sql = _dialect.build_update_sql(mapper.table_name, columns, pk_column);
            var command = _connection.create_command(sql);
            
            // Add parameters for SET clause
            foreach (var col in mapper.columns) {
                var value = properties.get(col.name);
                if (value != null) {
                    command.with_parameter<Invercargill.Element>(col.name, value);
                } else {
                    command.with_null(col.name);
                }
            }
            
            // Add primary key parameter for WHERE clause
            var pk_value = properties.get(pk_column);
            if (pk_value != null) {
                command.with_parameter<Invercargill.Element>(pk_column, pk_value);
            } else {
                command.with_null(pk_column);
            }
            
            command.execute_non_query();
        }
        
        /**
         * Updates an entity in the database asynchronously.
         *
         * This method performs the same operation as update() but in a non-blocking
         * manner, allowing the main loop to process other events while waiting
         * for the database operation to complete.
         *
         * The entity is identified by its primary key.
         *
         * @param entity The entity to update
         * @throws SqlError if update fails
         */
        public async void update_async<T>(T entity) throws Error {
            var mapper = get_mapper<T>();
            Invercargill.Properties properties;
            try {
                properties = mapper.map_from(entity);
            } catch (Error e) {
                throw new SqlError.GENERAL_ERROR("Failed to map entity: %s".printf(e.message));
            }
            
            var columns = new Vector<string>();
            foreach (var col in mapper.columns) {
                columns.add(col.name);
            }
            
            var pk_column = mapper.get_effective_primary_key();
            var sql = _dialect.build_update_sql(mapper.table_name, columns, pk_column);
            var command = _connection.create_command(sql);
            
            // Add parameters for SET clause
            foreach (var col in mapper.columns) {
                var value = properties.get(col.name);
                if (value != null) {
                    command.with_parameter<Invercargill.Element>(col.name, value);
                } else {
                    command.with_null(col.name);
                }
            }
            
            // Add primary key parameter for WHERE clause
            var pk_value = properties.get(pk_column);
            if (pk_value != null) {
                command.with_parameter<Invercargill.Element>(pk_column, pk_value);
            } else {
                command.with_null(pk_column);
            }
            
            // Execute asynchronously
            yield command.execute_non_query_async();
        }
        
        /**
         * Deletes an entity from the database.
         * 
         * @param entity The entity to delete
         * @throws SqlError if deletion fails
         */
        public void delete<T>(T entity) throws Error {
            var mapper = get_mapper<T>();
            Invercargill.Properties properties;
            try {
                properties = mapper.map_from(entity);
            } catch (Error e) {
                throw new SqlError.GENERAL_ERROR("Failed to map entity: %s".printf(e.message));
            }
            
            var pk_column = mapper.get_effective_primary_key();
            var sql = _dialect.build_delete_sql(mapper.table_name, pk_column);
            var command = _connection.create_command(sql);
            
            var pk_value = properties.get(pk_column);
            if (pk_value != null) {
                command.with_parameter<Invercargill.Element>(pk_column, pk_value);
            } else {
                command.with_null(pk_column);
            }
            
            command.execute_non_query();
        }
        
        /**
         * Deletes an entity from the database asynchronously.
         *
         * This method performs the same operation as delete() but in a non-blocking
         * manner, allowing the main loop to process other events while waiting
         * for the database operation to complete.
         *
         * The entity is identified by its primary key.
         *
         * @param entity The entity to delete
         * @throws SqlError if deletion fails
         */
        public async void delete_async<T>(T entity) throws Error {
            var mapper = get_mapper<T>();
            Invercargill.Properties properties;
            try {
                properties = mapper.map_from(entity);
            } catch (Error e) {
                throw new SqlError.GENERAL_ERROR("Failed to map entity: %s".printf(e.message));
            }
            
            var pk_column = mapper.get_effective_primary_key();
            var sql = _dialect.build_delete_sql(mapper.table_name, pk_column);
            var command = _connection.create_command(sql);
            
            var pk_value = properties.get(pk_column);
            if (pk_value != null) {
                command.with_parameter<Invercargill.Element>(pk_column, pk_value);
            } else {
                command.with_null(pk_column);
            }
            
            // Execute asynchronously
            yield command.execute_non_query_async();
        }
        
        /**
         * Gets the SQL dialect for internal use.
         * Used by EntityQuery and ProjectionQuery to build SQL.
         *
         * @return The SQL dialect
         */
        internal SqlDialect get_dialect() {
            return _dialect;
        }
        
        /**
         * Gets the entity mapper for type T.
         *
         * This method is useful for testing and advanced scenarios where
         * you need direct access to the entity mapper.
         *
         * @return The EntityMapper<T> for type T
         * @throws SqlError if no mapper is registered for type T
         */
        public EntityMapper<T> get_mapper<T>() throws Error {
            return _type_provider.get_mapper<T>();
        }
        
        /**
         * Gets the entity mapper for a specific type.
         * 
         * @param type The entity type to look up
         * @return The EntityMapper for the type, or null if not registered
         */
        public EntityMapper? get_mapper_for_type(Type type) throws Error {
            return _type_provider.get_mapper_for_type(type);
        }
    
        
        /**
         * Gets the projection definition for a type.
         * 
         * @return The ProjectionDefinition for the type, or null if not registered
         */
        public ProjectionDefinition? get_projection_definition<TProjection>() throws Error
        {
            return _type_provider.get_projection<TProjection>();
        }
        
        /**
         * Gets the projection definition by type.
         * 
         * @param type The projection type to look up
         * @return The ProjectionDefinition for the type, or null if not registered
         */
        public ProjectionDefinition? get_projection_definition_for_type(Type type)  throws Error {
            return _type_provider.get_projection_for_type(type);
        }
        
        /**
         * Gets the connection for internal use.
         * Used by ProjectionQuery to execute queries.
         * 
         * @return The database connection
         */
        internal Connection get_connection() {
            return _connection;
        }
    }
}