using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Runtime.CompilerServices;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using Dapper;
using GB5Shared.DTO.Framework.Login;
using Microsoft.Data.SqlClient;

namespace GB5Shared.QueryExecutor
{
    /// <summary>
    /// Defines the contract for executing database queries and commands.
    /// This interface supports both automatic connection management (single calls) 
    /// and explicit transaction management (multiple calls on a single connection).
    /// </summary>
    public interface IQueryExecutor
    {
        #region Transaction Management

        /// <summary>
        /// Begins a new database transaction and returns the connection and transaction objects.
        /// <para>
        /// <strong>Important:</strong> This method creates a new, dedicated connection for this transaction.
        /// You must pass the returned <paramref name="transaction"/> object to subsequent Query/Execute calls
        /// to ensure they run on this specific connection. This allows multiple transactions to run
        /// concurrently on different connections.
        /// </para>
        /// </summary>
        /// <param name="LoginDTO">The login context containing database connection details.</param>
        /// <returns>A tuple containing the active <see cref="DbConnection"/> and <see cref="DbTransaction"/>.</returns>
        Task< DbTransaction> BeginTransactionAsync(LoginDTO LoginDTO);

        /// <summary>
        /// Commits the active transaction and disposes the associated connection.
        /// </summary>
        /// <param name="connection">The connection object returned by <see cref="BeginTransactionAsync"/>.</param>
        /// <param name="transaction">The transaction object returned by <see cref="BeginTransactionAsync"/>.</param>
        Task CommitAsync(DbTransaction transaction);

        /// <summary>
        /// Rolls back the active transaction and disposes the associated connection.
        /// </summary>
        /// <param name="connection">The connection object returned by <see cref="BeginTransactionAsync"/>.</param>
        /// <param name="transaction">The transaction object returned by <see cref="BeginTransactionAsync"/>.</param>
        Task RollbackAsync( DbTransaction transaction);

        #endregion

        #region Standard Query & Execute (Automatic Transaction Support)

        /// <summary>
        /// Fetches a single record as a DTO based on the SQL query.
        /// </summary>
        /// <typeparam name="T">The type of DTO.</typeparam>
        /// <param name="LoginDTO">Login context for connection resolution.</param>
        /// <param name="sql">The SQL query string.</param>
        /// <param name="parameters">Optional parameters for the SQL query.</param>
        /// <param name="transaction">
        /// Optional. If provided, the query executes on this specific transaction/connection.
        /// If null, a temporary connection is created and closed automatically.
        /// </param>
        /// <param name="useReadUncommitted">
        /// Optional. When <c>true</c>, prepends <c>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED</c>
        /// to the query, allowing dirty reads for non-critical, high-throughput read scenarios.
        /// <para><strong>WARNING:</strong> READ UNCOMMITTED may return dirty reads — uncommitted,
        /// potentially rolled-back data from concurrent transactions. Never use for financial,
        /// inventory, or any data that must be consistent. Ignored when an external
        /// <paramref name="transaction"/> is supplied.</para>
        /// </param>
        /// <returns>A single DTO or default(T) if no match is found.</returns>
        Task<T> QuerySingleAsync<T>(LoginDTO LoginDTO, string sql, object parameters = null!, DbTransaction? transaction = null, bool useReadUncommitted = false, CancellationToken cancellationToken = default);

        /// <summary>
        /// Fetches multiple records as a list of DTOs based on the SQL query.
        /// </summary>
        /// <typeparam name="T">The type of DTO.</typeparam>
        /// <param name="LoginDTO">Login context for connection resolution.</param>
        /// <param name="sql">The SQL query string.</param>
        /// <param name="parameters">Optional parameters for the SQL query.</param>
        /// <param name="transaction">
        /// Optional. If provided, the query executes on this specific transaction/connection.
        /// If null, a temporary connection is created and closed automatically.
        /// </param>
        /// <param name="useReadUncommitted">
        /// Optional. When <c>true</c>, prepends <c>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED</c>
        /// to the query. See <see cref="QuerySingleAsync{T}"/> for full dirty-read warning.
        /// Ignored when an external <paramref name="transaction"/> is supplied.
        /// </param>
        /// <returns>A list of DTOs.</returns>
        Task<IEnumerable<T>> QueryAsync<T>(
            LoginDTO LoginDTO,
            string sql,
            object parameters = null!,
            DbTransaction? transaction = null,
            bool useReadUncommitted = false,
            CancellationToken cancellationToken = default
        );

        /// <summary>
        /// Executes a SQL command (INSERT, UPDATE, DELETE, etc.) and returns the number of affected rows.
        /// </summary>
        /// <param name="LoginDTO">Login context for connection resolution.</param>
        /// <param name="sql">The SQL command string.</param>
        /// <param name="parameters">Optional parameters for the SQL command.</param>
        /// <param name="transaction">
        /// Optional. If provided, the command executes on this specific transaction/connection. 
        /// If null, a temporary connection is created and closed automatically.
        /// </param>
        /// <returns>The number of rows affected.</returns>
        Task<int> ExecuteAsync(LoginDTO LoginDTO, string sql, object parameters = null!, DbTransaction? transaction = null, CancellationToken cancellationToken = default);

        /// <summary>
        /// Executes a SQL command using an explicit transaction and connection, 
        /// typically used after manually retrieving a transaction.
        /// </summary>
        /// <param name="sql">The SQL command string.</param>
        /// <param name="param">Parameters for the SQL command.</param>
        /// <param name="transaction">The explicit transaction to use.</param>
        /// <returns>The number of rows affected.</returns>
        Task<int> ExecuteAsync(string sql, object param, DbTransaction transaction);

        /// <summary>
        /// Executes a command and returns the database-generated identity (ID) value (e.g., SCOPE_IDENTITY()).
        /// </summary>
        /// <param name="loginDTO">Login context for connection resolution.</param>
        /// <param name="sql">The INSERT SQL statement.</param>
        /// <param name="parameters">Optional parameters.</param>
        /// <param name="transaction">Optional transaction to enlist in.</param>
        /// <returns>The generated ID of the inserted record.</returns>
        Task<int> ExecuteIdentityAsync(LoginDTO loginDTO, string sql, object? parameters = null, DbTransaction? transaction = null);

        /// <summary>
        /// Executes a query and returns the first column of the first row (e.g., COUNT, SUM).
        /// </summary>
        /// <typeparam name="T">The return type.</typeparam>
        /// <param name="loginDTO">Login context.</param>
        /// <param name="sql">The SQL query.</param>
        /// <param name="parameters">Optional parameters.</param>
        /// <param name="transaction">Optional transaction to enlist in.</param>
        /// <param name="useReadUncommitted">
        /// Optional. When <c>true</c>, prepends <c>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED</c>.
        /// See <see cref="QuerySingleAsync{T}"/> for full dirty-read warning.
        /// Ignored when an external <paramref name="transaction"/> is supplied.
        /// </param>
        /// <returns>The scalar value.</returns>
        Task<T> ExecuteScalarAsync<T>(LoginDTO loginDTO, string sql, object? parameters = null, DbTransaction? transaction = null, bool useReadUncommitted = false, CancellationToken cancellationToken = default);

        #endregion

        #region Streaming & Bulk Operations

        /// <summary>
        /// Streams the results of a query asynchronously, suitable for large datasets.
        /// Uses CommandBehavior.SequentialAccess for efficient memory usage.
        /// </summary>
        /// <typeparam name="T">The type of DTO.</typeparam>
        /// <param name="LoginDTO">Login context.</param>
        /// <param name="sql">The SQL query string.</param>
        /// <param name="parameters">Optional parameters.</param>
        /// <param name="transaction">Optional transaction to enlist in.</param>
        /// <param name="useReadUncommitted">
        /// Optional. When <c>true</c>, prepends <c>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED</c>.
        /// See <see cref="QuerySingleAsync{T}"/> for full dirty-read warning.
        /// Ignored when an external <paramref name="transaction"/> is supplied.
        /// </param>
        /// <returns>An asynchronous stream of DTOs.</returns>
        /// <remarks>
        /// WARNING: This method buffers the full result set in memory before yielding.
        /// For true row-by-row streaming on large result sets (&gt;5 000 rows), use
        /// <see cref="QueryStreamAsync{T}"/> which uses a DataReader with SequentialAccess.
        /// </remarks>
        IAsyncEnumerable<T> StreamAsync<T>(
            LoginDTO LoginDTO,
            string sql,
            object parameters = null!,
            [System.Runtime.CompilerServices.EnumeratorCancellation] CancellationToken cancellationToken = default,
            DbTransaction? transaction = null,
            bool useReadUncommitted = false
        );

        /// <summary>
        /// Streams query results asynchronously using a DataReader, ideal for processing large result sets row-by-row.
        /// </summary>
        /// <param name="useReadUncommitted">
        /// Optional. When <c>true</c>, prepends <c>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED</c>.
        /// See <see cref="QuerySingleAsync{T}"/> for full dirty-read warning.
        /// Ignored when an external <paramref name="transaction"/> is supplied.
        /// </param>
        IAsyncEnumerable<T> QueryStreamAsync<T>(
            LoginDTO LoginDTO,
            string sql,
            object parameters = null!,
            [EnumeratorCancellation] CancellationToken cancellationToken = default,
            DbTransaction? transaction = null,
            bool useReadUncommitted = false
        );

        /// <summary>
        /// Executes a bulk insert for a collection of DTOs in a single roundtrip if possible.
        /// </summary>
        /// <typeparam name="T">The type of DTO.</typeparam>
        /// <param name="LoginDTO">Login context.</param>
        /// <param name="sql">The SQL insert statement.</param>
        /// <param name="items">The collection of items to insert.</param>
        /// <param name="transaction">Optional transaction to enlist in.</param>
        /// <returns>The number of rows affected.</returns>
        Task<int> BulkInsertAsync<T>(LoginDTO LoginDTO, string sql, IEnumerable<T> items, DbTransaction? transaction = null);

        /// <summary>
        /// Performs a bulk insert using Table-Valued Parameters (TVP). Specific to SQL Server.
        /// </summary>
        Task<int> BulkInsertMultipleAsync(
            LoginDTO loginDTO,
            string commandText,
            Dictionary<string, (DataTable value, string typeName)> tvpParameters
        );

        #endregion

        #region Specialized Query Methods

        /// <summary>
        /// Fetches records in a paginated fashion.
        /// Executes the main query and a COUNT query to determine total records.
        /// </summary>
        /// <param name="useReadUncommitted">
        /// Optional. When <c>true</c>, prepends <c>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED</c>.
        /// See <see cref="QuerySingleAsync{T}"/> for full dirty-read warning.
        /// Ignored when an external <paramref name="transaction"/> is supplied.
        /// </param>
        Task<PagedResult<T>> QueryPagedAsync<T>(
            LoginDTO LoginDTO,
            string sql,
            object parameters = null!,
            DbTransaction? transaction = null,
            bool useReadUncommitted = false
        );

        /// <summary>
        /// Executes a query mapping two related tables (Join) into a single DTO hierarchy.
        /// </summary>
        /// <param name="useReadUncommitted">
        /// Optional. When <c>true</c>, prepends <c>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED</c>.
        /// See <see cref="QuerySingleAsync{T}"/> for full dirty-read warning.
        /// Ignored when an external <paramref name="transaction"/> is supplied.
        /// </param>
        Task<IEnumerable<TReturn>> QueryMultiMapAsync<TFirst, TSecond, TReturn>(
            LoginDTO loginDTO,
            string sql,
            Func<TFirst, TSecond, TReturn> map,
            object parameters = null!,
            string splitOn = "Id",
            DbTransaction? transaction = null,
            bool useReadUncommitted = false
        );

        /// <summary>
        /// Executes a query mapping three related tables (Join) into a single DTO hierarchy.
        /// </summary>
        /// <param name="useReadUncommitted">
        /// Optional. When <c>true</c>, prepends <c>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED</c>.
        /// See <see cref="QuerySingleAsync{T}"/> for full dirty-read warning.
        /// Ignored when an external <paramref name="transaction"/> is supplied.
        /// </param>
        Task<IEnumerable<TReturn>> QueryMultiMapAsync<TFirst, TSecond, TThird, TReturn>(
            LoginDTO loginDTO,
            string sql,
            Func<TFirst, TSecond, TThird, TReturn> map,
            object parameters = null!,
            string splitOn = "Id,Id",
            DbTransaction? transaction = null,
            bool useReadUncommitted = false
        );

        /// <summary>
        /// Executes a query mapping five related tables.
        /// </summary>
        /// <param name="useReadUncommitted">
        /// Optional. When <c>true</c>, prepends <c>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED</c>.
        /// See <see cref="QuerySingleAsync{T}"/> for full dirty-read warning.
        /// Ignored when an external <paramref name="transaction"/> is supplied.
        /// </param>
        Task<IEnumerable<TReturn>> QueryMultiMapAsync<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(
            LoginDTO loginDTO,
            string sql,
            Func<TFirst, TSecond, TThird, TFourth, TFifth, TReturn> map,
            object parameters = null!,
            string splitOn = "Id,Id,Id,Id",
            DbTransaction? transaction = null,
            bool useReadUncommitted = false
        );

        /// <summary>
        /// Executes a query mapping seven related tables.
        /// </summary>
        /// <param name="useReadUncommitted">
        /// Optional. When <c>true</c>, prepends <c>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED</c>.
        /// See <see cref="QuerySingleAsync{T}"/> for full dirty-read warning.
        /// Ignored when an external <paramref name="transaction"/> is supplied.
        /// </param>
        Task<IEnumerable<TReturn>> QueryMultiMapAsync<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, TReturn>(
            LoginDTO loginDTO,
            string sql,
            Func<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, TReturn> map,
            object parameters = null!,
            string splitOn = "Id,Id,Id,Id,Id,Id",
            DbTransaction? transaction = null,
            bool useReadUncommitted = false
        );

        /// <summary>
        /// Executes a query and maps results to an array structure.
        /// </summary>
        /// <param name="useReadUncommitted">
        /// Optional. When <c>true</c>, prepends <c>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED</c>.
        /// See <see cref="QuerySingleAsync{T}"/> for full dirty-read warning.
        /// Ignored when an external <paramref name="transaction"/> is supplied.
        /// </param>
        Task<IEnumerable<TReturn>> QueryAsyncArray<TFirst, TSecond, TReturn>(
            LoginDTO loginDTO,
            string sql,
            Func<TFirst, TSecond, TReturn> map,
            object parameters = null!,
            string splitOn = "Id",
            DbTransaction? transaction = null,
            bool useReadUncommitted = false
        );

        /// <summary>
        /// Executes a COUNT query based on a provided SQL query string.
        /// Can wrap the query or use the query directly if it is already a count query.
        /// </summary>
        /// <param name="useReadUncommitted">
        /// Optional. When <c>true</c>, prepends <c>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED</c>.
        /// See <see cref="QuerySingleAsync{T}"/> for full dirty-read warning.
        /// Ignored when an external <paramref name="transaction"/> is supplied.
        /// </param>
        Task<int> TotalCountSql(
            string sqlQuery,
            LoginDTO loginDTO,
            object? parameters = null,
            bool isCountQuery = false,
            DbTransaction? transaction = null,
            bool useReadUncommitted = false
        );

        /// <summary>
        /// Executes a query returning an asynchronous enumerable.
        /// </summary>
        /// <param name="useReadUncommitted">
        /// Optional. When <c>true</c>, prepends <c>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED</c>.
        /// See <see cref="QuerySingleAsync{T}"/> for full dirty-read warning.
        /// Ignored when an external <paramref name="transaction"/> is supplied.
        /// </param>
        IAsyncEnumerable<T> ExecuteQueryAsync<T>(
            LoginDTO LoginDTO,
            string sql,
            object parameters = null!,
            DbTransaction? transaction = null,
            bool useReadUncommitted = false
        );

        #endregion

        #region Connection Name & Procedures

        /// <summary>
        /// Fetches data using a specific named connection string (defined in configuration),
        /// separate from the default LoginDTO connection.
        /// </summary>
        Task<IEnumerable<T>> QueryAsync<T>(
            string ConnectionName,
            string sql,
            object parameters = null!
        );

        /// <summary>
        /// Executes a list of Stored Procedures and returns a list of string status messages.
        /// Each procedure execution is treated as a step.
        /// </summary>
        Task<List<string>> ProcedureExecuteAsync(
            LoginDTO loginDTO,
            IEnumerable<(string ProcedureName, object Param)> procedures
        );

        /// <summary>
        /// Executes a Count query specifically designed for TVP scenarios.
        /// </summary>
        Task<int> ExecuteTvpCountQueryAsync(
            LoginDTO loginDTO,
            string sqlQuery,
            Dictionary<string, (DataTable value, string typeName)> tvpParameters
        );

        #endregion

        #region Legacy Session Methods (HttpContext Based)

        /// <summary>
        /// Executes a query within a "Session" managed by HttpContext Items.
        /// <para><strong>Note:</strong> This relies on the HTTP context and may not work in background tasks.
        /// Consider using explicit transactions (<see cref="BeginTransactionAsync"/>) for better control.</para>
        /// </summary>
        Task<IEnumerable<T>> SessionQueryAsync<T>(LoginDTO loginDTO, string sql, object param = null!);

        /// <summary>
        /// Fetches a single result within a "Session" managed by HttpContext.
        /// </summary>
        Task<T> SessionQuerySingleAsync<T>(LoginDTO loginDTO, string sql, object param = null!);

        /// <summary>
        /// Executes a command within a "Session" managed by HttpContext.
        /// </summary>
        Task<int> SessionExecuteAsync(LoginDTO loginDTO, string sql, object param = null!);

        /// <summary>
        /// Commits the transaction stored in the current HttpContext Session.
        /// </summary>
        Task SameSessionCommitAsync();

        /// <summary>
        /// Rolls back the transaction stored in the current HttpContext Session.
        /// </summary>
        Task SameSessionRollbackAsync();

        #endregion

        #region Helper Methods

        /// <summary>
        /// Executes a list of SQL statements inside a single isolated transaction.
        /// This method manages the connection and transaction lifecycle internally.
        /// </summary>
        Task ExecuteInTransactionAsync(
            LoginDTO LoginDTO,
            IEnumerable<(string sql, object param)> statements
        );
        Task<SqlMapper.GridReader> QueryMultipleAsync(
                            LoginDTO loginDTO,
                            string sql,
                            object parameters = null!,
                            DbTransaction? transaction = null
                        );
        Task<SqlMapper.GridReader> QueryMultipleAsync(
    LoginDTO loginDTO,
    string sql,
    object parameters = null!,
    DbTransaction? transaction = null,
    CommandType commandType = CommandType.StoredProcedure);
        #endregion
    }


}