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

namespace InvercargillSql.Orm.Projections {
    
    /**
     * Query builder for projection types.
     * 
     * ProjectionQuery<TProjection> provides a fluent interface for building
     * database queries that return projection objects instead of full entities.
     * It supports WHERE clauses, ORDER BY, LIMIT, and OFFSET.
     * 
     * The key difference from EntityQuery<T> is that ProjectionQuery uses the
     * ProjectionSqlBuilder to generate SQL that includes JOINs and aggregates
     * based on the projection definition.
     * 
     * Example usage:
     * {{{
     * var results = session.projection_query<UserOrderStats>()
     *     .where(expr("user_id > $0", new NativeElement<int?>(100)))
     *     .or_where(expr("order_count >= $0", new NativeElement<int?>(5)))
     *     .order_by_desc(expr("total_spent"))
     *     .limit(10)
     *     .materialise();
     * }}}
     * 
     * Note: This class extends Query<TProjection> and uses the base class
     * for common query operations. The where() method accepts Expression objects
     * which are stored in _where_expressions from the base class.
     * 
     * @param TProjection The projection result type
     */
    public class ProjectionQuery<TProjection> : Query<TProjection> {
        
        /**
         * The projection definition for building SQL.
         */
        private ProjectionDefinition _query_definition;
        
        /**
         * The SQL builder for this projection.
         */
        private ProjectionSqlBuilder _query_sql_builder;
        
        /**
         * Creates a new ProjectionQuery.
         * 
         * This constructor is internal - use OrmSession.projection_query<T>() 
         * to create queries.
         * 
         * @param session The OrmSession that will execute this query
         * @param definition The projection definition
         * @param sql_builder The SQL builder for this projection
         */
        internal ProjectionQuery(
            OrmSession session, 
            ProjectionDefinition definition,
            ProjectionSqlBuilder sql_builder
        ) {
            base(session);  // Initialize base class with session
            _query_definition = definition;
            _query_sql_builder = sql_builder;
        }
        
        // === Abstract property implementations ===
        
        /**
         * The filter expression for this query.
         *
         * Returns the combined WHERE expression from _where_expressions.
         */
        internal override owned Expression? filter {
            owned get { return get_combined_where(); }
        }
        
        /**
         * The ORDER BY clauses for this query.
         * 
         * Returns the _orderings vector from the base class.
         */
        internal override Vector<OrderByClause> orderings {
            get { return _orderings; }
        }
        
        /**
         * The LIMIT value for this query, or null if not set.
         * 
         * Returns the _limit value from the base class.
         */
        internal override int64? limit_value {
            get { return _limit; }
        }
        
        /**
         * The OFFSET value for this query, or null if not set.
         * 
         * Returns the _offset value from the base class.
         */
        internal override int64? offset_value {
            get { return _offset; }
        }
        
        /**
         * Materializes results synchronously.
         * 
         * Executes the query and returns all matching projections.
         * 
         * @return An ImmutableLot of TProjection instances
         * @throws SqlError if query execution fails
         */
        public override Invercargill.ImmutableLot<TProjection> materialise() throws SqlError {
            // Get the combined WHERE expression from base class
            Expression? where_expr = get_combined_where();
            
            // Translate ORDER BY expressions to strings for ProjectionSqlBuilder
            Vector<OrderByClause>? translated_orderings = translate_orderings_for_builder();
            
            BuiltQuery built = _query_sql_builder.build_with_split(
                where_expr,  // Pass Expression directly
                translated_orderings,
                _limit,
                _offset
            );
            
            string sql = built.sql;
            
            // Execute through session's connection using _session from base class
            var connection = _session.get_connection();
            var command = connection.create_command(sql);
            var results = command.execute_query();
            
            // Materialize the results using the projection mapper
            var mapper = new ProjectionMapper<TProjection>(_query_definition);
            
            try {
                var vector = mapper.map_all(results);
                return vector.to_immutable_buffer();
            } catch (ProjectionError e) {
                throw new SqlError.GENERAL_ERROR(
                    @"Failed to materialize projection: $(e.message)"
                );
            }
        }
        
        /**
         * Materializes results asynchronously.
         *
         * Executes the query asynchronously and returns all matching projections.
         * Uses the async/yield pattern to properly propagate async behavior.
         *
         * @return An ImmutableLot of TProjection instances
         * @throws SqlError if query execution fails
         */
        public override async Invercargill.ImmutableLot<TProjection> materialise_async() throws SqlError {
            // Get the combined WHERE expression from base class
            Expression? where_expr = get_combined_where();
            
            // Translate ORDER BY expressions to strings for ProjectionSqlBuilder
            Vector<OrderByClause>? translated_orderings = translate_orderings_for_builder();
            
            BuiltQuery built = _query_sql_builder.build_with_split(
                where_expr,  // Pass Expression directly
                translated_orderings,
                _limit,
                _offset
            );
            
            string sql = built.sql;
            
            // Execute through session's connection using _session from base class
            var connection = _session.get_connection();
            var command = connection.create_command(sql);
            
            // Execute asynchronously using yield
            var results = yield command.execute_query_async();
            
            // Materialize the results using the projection mapper
            var mapper = new ProjectionMapper<TProjection>(_query_definition);
            
            try {
                var vector = mapper.map_all(results);
                return vector.to_immutable_buffer();
            } catch (ProjectionError e) {
                throw new SqlError.GENERAL_ERROR(
                    @"Failed to materialize projection: $(e.message)"
                );
            }
        }
        
        /**
         * Gets the SQL for this query.
         * 
         * This method is useful for debugging and logging.
         * 
         * @return The SQL SELECT statement
         */
        public override string to_sql() {
            // Get the combined WHERE expression from base class
            Expression? where_expr = get_combined_where();
            
            // Translate ORDER BY expressions to strings for ProjectionSqlBuilder
            Vector<OrderByClause>? translated_orderings = translate_orderings_for_builder();
            
            BuiltQuery built = _query_sql_builder.build_with_split(
                where_expr,  // Pass Expression directly
                translated_orderings,
                _limit,
                _offset
            );
            
            return built.sql;
        }
        
        // === Internal accessor methods for compatibility ===
        
        /**
         * Gets the projection definition for this query.
         * 
         * @return The ProjectionDefinition
         */
        internal ProjectionDefinition get_definition() {
            return _query_definition;
        }
        
        /**
         * Gets the SQL builder for this query.
         * 
         * @return The ProjectionSqlBuilder
         */
        internal ProjectionSqlBuilder get_sql_builder() {
            return _query_sql_builder;
        }
        
        /**
         * Gets the WHERE expressions for this query.
         * 
         * Returns the _where_expressions vector from the base class.
         * 
         * @return The Vector of WHERE expressions
         */
        internal Vector<Expression> get_where_expressions() {
            return _where_expressions;
        }
        
        /**
         * Gets the ORDER BY clauses for this query.
         * 
         * Returns the _orderings vector from the base class.
         * 
         * @return The Vector of OrderByClause
         */
        internal Vector<OrderByClause> get_order_by_clauses() {
            return _orderings;
        }
        
        /**
         * Gets the LIMIT value for this query.
         * 
         * Returns the _limit value from the base class.
         * 
         * @return The LIMIT value, or null if not set
         */
        internal int64? get_limit_value() {
            return _limit;
        }
        
        /**
         * Gets the OFFSET value for this query.
         * 
         * Returns the _offset value from the base class.
         * 
         * @return The OFFSET value, or null if not set
         */
        internal int64? get_offset_value() {
            return _offset;
        }
        
        /**
         * Gets whether OR logic is used for WHERE clauses.
         * 
         * Returns the _use_or value from the base class.
         * 
         * @return True if OR logic is used
         */
        internal bool get_use_or() {
            return _use_or;
        }
        
        // === Private helper methods ===
        
        /**
         * Converts an Expression to a SQL string using ExpressionToSqlVisitor.
         * 
         * This method uses the projection context to properly translate
         * variable names and resolve friendly names.
         * 
         * @param expr The expression to convert
         * @return The SQL string representation
         */
        private string expression_to_sql_string(Expression expr) {
            // Get the entity mapper for the primary source type
            // For projections, we use a simplified approach that leverages
            // the ProjectionSqlBuilder's translate_expression method
            return _query_sql_builder.translate_expression(expr);
        }
        
        /**
         * Translates ORDER BY clauses for use with ProjectionSqlBuilder.
         * 
         * The ProjectionSqlBuilder expects OrderByClause with string expressions,
         * so we need to convert our Expression-based OrderByClause to string-based.
         * 
         * @return A new vector of OrderByClause with string expressions
         */
        private Vector<OrderByClause>? translate_orderings_for_builder() {
            if (_orderings.length == 0) {
                return null;
            }
            
            var result = new Vector<OrderByClause>();
            
            foreach (var clause in _orderings) {
                string expr_str = expression_to_sql_string(clause.expression);
                // Create a new OrderByClause with the translated string expression
                // Note: We create a string-based OrderByClause using a LiteralExpression
                // as a carrier for the string
                result.add(new OrderByClause(new LiteralExpression(new Invercargill.NativeElement<string?>(expr_str)), clause.descending));
            }
            
            return result;
        }
    }
}