using System.Data;
using Dapper;
using Microsoft.Data.SqlClient;
using SIGCM2.Application.Abstractions.Persistence;
using SIGCM2.Domain.Pricing.ChargeableChars;
using SIGCM2.Domain.Pricing.Exceptions;

namespace SIGCM2.Infrastructure.Persistence;

/// <summary>
/// PRC-001 — Dapper implementation of IChargeableCharConfigRepository against dbo.ChargeableCharConfig.
///
/// InsertWithCloseAsync: invokes usp_ChargeableCharConfig_InsertWithClose and maps:
///   - SqlException 50404 → ChargeableCharConfigInvalidException (ProductType not found)
///   - SqlException 50409 → ChargeableCharConfigForwardOnlyException
///
/// GetActiveForProductTypeAsync: invokes usp_ChargeableCharConfig_GetActiveForProductType.
///   Returns all rows (global + per-ProductType) — the Application service applies priority.
///
/// ReactivateAsync: invokes usp_ChargeableCharConfig_ReactivateWithGuard and maps:
///   - SqlException 50410 → ChargeableCharConfigReactivationNotAllowedException(ALREADY_ACTIVE)
///   - SqlException 50411 → ChargeableCharConfigReactivationNotAllowedException(VIGENTE_EXISTS)
///   - SqlException 50412 → ChargeableCharConfigReactivationNotAllowedException(POSTERIOR_ROWS_EXIST)
///   - SqlException 50404 → ChargeableCharConfigInvalidException (row not found)
///
/// DeleteAsync: simple parameterized DELETE. If 0 rows affected, throws KeyNotFoundException.
///   NOTE: With SYSTEM_VERSIONING ON, the DELETE physically removes the row from the current
///   table and SQL Server moves it to the history table (_History) with SysEndTime set to the
///   deletion time. The row is still queryable via FOR SYSTEM_TIME. Temporal audit preserved.
///
/// DateOnly mapping: SQL DATE columns are received as DateTime by Dapper; converted via
///   DateOnly.FromDateTime() in the row mapper — same pattern as ProductPriceRepository.
///
/// ProductTypeId: the SP accepts INT NULL; int? cast from long? is performed in this layer.
/// </summary>
public sealed class ChargeableCharConfigRepository : IChargeableCharConfigRepository
{
    private readonly SqlConnectionFactory _factory;

    public ChargeableCharConfigRepository(SqlConnectionFactory factory)
    {
        _factory = factory;
    }

    /// <inheritdoc/>
    public async Task<long> InsertWithCloseAsync(
        long? productTypeId,
        string symbol,
        string category,
        decimal price,
        DateOnly validFrom,
        CancellationToken ct = default)
    {
        var p = new DynamicParameters();
        // SP parameter is INT NULL — cast long? → int? here; DB uses INT for ProductTypeId (V023)
        p.Add("@ProductTypeId", productTypeId.HasValue ? (int?)checked((int)productTypeId.Value) : null, DbType.Int32);
        p.Add("@Symbol",        symbol,                                                                   DbType.String, size: 4);
        p.Add("@Category",      category,                                                                 DbType.String, size: 32);
        p.Add("@PricePerUnit",  price,                                                                    DbType.Decimal, precision: 18, scale: 4);
        p.Add("@ValidFrom",     validFrom.ToDateTime(TimeOnly.MinValue),                                  DbType.Date);
        p.Add("@NewId",         dbType: DbType.Int64, direction: ParameterDirection.Output);
        p.Add("@ClosedId",      dbType: DbType.Int64, direction: ParameterDirection.Output);

        await using var connection = _factory.CreateConnection();
        await connection.OpenAsync(ct);

        try
        {
            await connection.ExecuteAsync(
                new CommandDefinition(
                    "dbo.usp_ChargeableCharConfig_InsertWithClose",
                    p,
                    commandType: CommandType.StoredProcedure,
                    cancellationToken: ct));
        }
        catch (SqlException ex) when (ex.Number == 50404)
        {
            // ProductType not found (SP validates ProductTypeId when not null)
            throw new ChargeableCharConfigInvalidException(
                nameof(productTypeId),
                $"ProductType with Id={productTypeId} not found.");
        }
        catch (SqlException ex) when (ex.Number == 50409)
        {
            // Forward-only violation: new ValidFrom <= active.ValidFrom
            throw new ChargeableCharConfigForwardOnlyException(
                productTypeId.HasValue ? (int?)checked((int)productTypeId.Value) : null,
                symbol,
                validFrom,
                DateOnly.MinValue); // active.ValidFrom not returned by SP; safe placeholder
        }

        return p.Get<long>("@NewId");
    }

    /// <inheritdoc/>
    public async Task<IReadOnlyList<ChargeableCharConfig>> GetActiveForProductTypeAsync(
        long productTypeId,
        DateOnly asOfDate,
        CancellationToken ct = default)
    {
        var p = new DynamicParameters();
        // SP @ProductTypeId is INT
        p.Add("@ProductTypeId", checked((int)productTypeId),              DbType.Int32);
        p.Add("@AsOfDate",      asOfDate.ToDateTime(TimeOnly.MinValue),   DbType.Date);

        await using var connection = _factory.CreateConnection();
        await connection.OpenAsync(ct);

        var rows = await connection.QueryAsync<ChargeableCharConfigRow>(
            new CommandDefinition(
                "dbo.usp_ChargeableCharConfig_GetActiveForProductType",
                p,
                commandType: CommandType.StoredProcedure,
                cancellationToken: ct));

        return rows.Select(MapRow).ToList();
    }

    /// <inheritdoc/>
    public async Task<IReadOnlyList<ChargeableCharConfig>> ListAsync(
        long? productTypeId,
        bool activeOnly,
        int skip,
        int take,
        CancellationToken ct = default)
    {
        // NULL-aware ProductTypeId filter:
        //   - productTypeId provided → filter to that ProductType only
        //   - productTypeId null    → return all rows regardless of ProductType
        // activeOnly filters by IsActive = 1.
        const string sql = """
            SELECT Id, ProductTypeId, Symbol, Category, PricePerUnit, ValidFrom, ValidTo, IsActive
            FROM dbo.ChargeableCharConfig
            WHERE (@ProductTypeId IS NULL OR ProductTypeId = @ProductTypeId)
              AND (@ActiveOnly = 0 OR IsActive = 1)
            ORDER BY ValidFrom DESC, Id DESC
            OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY
            """;

        await using var connection = _factory.CreateConnection();
        await connection.OpenAsync(ct);

        var rows = await connection.QueryAsync<ChargeableCharConfigRow>(
            new CommandDefinition(
                sql,
                new
                {
                    ProductTypeId = productTypeId.HasValue ? (int?)checked((int)productTypeId.Value) : (int?)null,
                    ActiveOnly    = activeOnly ? 1 : 0,
                    Skip          = skip,
                    Take          = take
                },
                cancellationToken: ct));

        return rows.Select(MapRow).ToList();
    }

    /// <inheritdoc/>
    public async Task<int> CountAsync(
        long? productTypeId,
        bool activeOnly,
        CancellationToken ct = default)
    {
        const string sql = """
            SELECT COUNT(1)
            FROM dbo.ChargeableCharConfig
            WHERE (@ProductTypeId IS NULL OR ProductTypeId = @ProductTypeId)
              AND (@ActiveOnly = 0 OR IsActive = 1)
            """;

        await using var connection = _factory.CreateConnection();
        await connection.OpenAsync(ct);

        return await connection.ExecuteScalarAsync<int>(
            new CommandDefinition(
                sql,
                new
                {
                    ProductTypeId = productTypeId.HasValue ? (int?)checked((int)productTypeId.Value) : (int?)null,
                    ActiveOnly    = activeOnly ? 1 : 0
                },
                cancellationToken: ct));
    }

    /// <inheritdoc/>
    public async Task<ChargeableCharConfig?> GetByIdAsync(
        long id,
        CancellationToken ct = default)
    {
        const string sql = """
            SELECT Id, ProductTypeId, Symbol, Category, PricePerUnit, ValidFrom, ValidTo, IsActive
            FROM dbo.ChargeableCharConfig
            WHERE Id = @Id
            """;

        await using var connection = _factory.CreateConnection();
        await connection.OpenAsync(ct);

        var row = await connection.QuerySingleOrDefaultAsync<ChargeableCharConfigRow>(
            new CommandDefinition(sql, new { Id = id }, cancellationToken: ct));

        return row is null ? null : MapRow(row);
    }

    /// <inheritdoc/>
    public async Task DeactivateAsync(
        long id,
        DateOnly today,
        CancellationToken ct = default)
    {
        // Idempotent: WHERE ... AND IsActive = 1 — no-op if already inactive.
        const string sql = """
            UPDATE dbo.ChargeableCharConfig
               SET IsActive = 0,
                   ValidTo  = @Today
             WHERE Id = @Id
               AND IsActive = 1
            """;

        await using var connection = _factory.CreateConnection();
        await connection.OpenAsync(ct);

        await connection.ExecuteAsync(
            new CommandDefinition(
                sql,
                new
                {
                    Id    = id,
                    Today = today.ToDateTime(TimeOnly.MinValue)
                },
                cancellationToken: ct));
    }

    /// <inheritdoc/>
    public async Task<ChargeableCharConfig> ReactivateAsync(
        long id,
        CancellationToken ct = default)
    {
        // IMPORTANT: the SP invocation and the subsequent SELECT MUST run on the SAME connection.
        // Opening a second connection within the ambient TransactionScope would promote the
        // transaction to DTC (distributed) — and MSDTC is typically not enabled on dev/prod
        // servers. That promotion surfaces as an opaque 500 at the API boundary. Keeping both
        // commands on a single SqlConnection keeps the tx as a local LTM (lightweight transaction).
        var p = new DynamicParameters();
        p.Add("@Id", id, DbType.Int64);

        await using var connection = _factory.CreateConnection();
        await connection.OpenAsync(ct);

        try
        {
            await connection.ExecuteAsync(
                new CommandDefinition(
                    "dbo.usp_ChargeableCharConfig_ReactivateWithGuard",
                    p,
                    commandType: CommandType.StoredProcedure,
                    cancellationToken: ct));
        }
        catch (SqlException ex) when (ex.Number == 50404)
        {
            throw new ChargeableCharConfigInvalidException(
                nameof(id), $"ChargeableCharConfig with Id={id} not found.");
        }
        catch (SqlException ex) when (ex.Number == 50410)
        {
            throw new ChargeableCharConfigReactivationNotAllowedException(id, "ALREADY_ACTIVE");
        }
        catch (SqlException ex) when (ex.Number == 50411)
        {
            throw new ChargeableCharConfigReactivationNotAllowedException(id, "VIGENTE_EXISTS");
        }
        catch (SqlException ex) when (ex.Number == 50412)
        {
            throw new ChargeableCharConfigReactivationNotAllowedException(id, "POSTERIOR_ROWS_EXIST");
        }

        // Fetch the reactivated row on the SAME connection (avoids DTC promotion).
        const string selectSql = """
            SELECT Id, ProductTypeId, Symbol, Category, PricePerUnit, ValidFrom, ValidTo, IsActive
            FROM dbo.ChargeableCharConfig
            WHERE Id = @Id
            """;

        var row = await connection.QuerySingleOrDefaultAsync<ChargeableCharConfigRow>(
            new CommandDefinition(selectSql, new { Id = id }, cancellationToken: ct));

        return row is null
            ? throw new ChargeableCharConfigInvalidException(
                nameof(id), $"ChargeableCharConfig with Id={id} not found after reactivation (unexpected).")
            : MapRow(row);
    }

    /// <inheritdoc/>
    public async Task DeleteAsync(
        long id,
        CancellationToken ct = default)
    {
        // NOTE: With SYSTEM_VERSIONING ON on dbo.ChargeableCharConfig, this DELETE moves
        // the row to dbo.ChargeableCharConfig_History (SysEndTime = deletion timestamp).
        // The row disappears from current-state queries but is still queryable via
        // FOR SYSTEM_TIME. Temporal audit trail is preserved.
        // Future FAC-001 will add a guard to block delete if the row was used in invoicing.
        const string sql = "DELETE FROM dbo.ChargeableCharConfig WHERE Id = @Id";

        await using var connection = _factory.CreateConnection();
        await connection.OpenAsync(ct);

        var rowsAffected = await connection.ExecuteAsync(
            new CommandDefinition(sql, new { Id = id }, cancellationToken: ct));

        if (rowsAffected == 0)
            throw new KeyNotFoundException($"ChargeableCharConfig with Id={id} not found.");
    }

    // ── Row mapper ────────────────────────────────────────────────────────────
    // Dapper maps SQL DATE columns to DateTime; we convert to DateOnly here.
    // Same pattern as ProductPriceRepository.

    private static ChargeableCharConfig MapRow(ChargeableCharConfigRow r)
        => ChargeableCharConfig.Rehydrate(
            id:            r.Id,
            productTypeId: r.ProductTypeId,
            symbol:        r.Symbol,
            category:      r.Category,
            price:         r.PricePerUnit,
            validFrom:     DateOnly.FromDateTime(r.ValidFrom),
            validTo:       r.ValidTo.HasValue ? DateOnly.FromDateTime(r.ValidTo.Value) : (DateOnly?)null,
            isActive:      r.IsActive);

    private sealed record ChargeableCharConfigRow(
        long      Id,
        int?      ProductTypeId,
        string    Symbol,
        string    Category,
        decimal   PricePerUnit,
        DateTime  ValidFrom,
        DateTime? ValidTo,
        bool      IsActive);
}
