using System.Data.Common;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.ResponseStandard;
using PIEDAL.DTO.Estimation;
using PIEDAL.Query.Estimation;

namespace PIEDAL.CustomCode.Estimation;

public class PIEEstimationDAL : IPIEEstimationDAL
{
    private readonly IQueryExecutor _QueryExecutor;

    public PIEEstimationDAL(IQueryExecutor queryExecutor)
    {
        _QueryExecutor = queryExecutor;
    }

    // ── Rule reads ────────────────────────────────────────────────────────────

    public async Task<PIEEstimationRuleDTO?> GetPIEEstimationRuleAsync(
        Guid ruleId, LoginDTO login, CancellationToken ct)
        => await _QueryExecutor.QuerySingleAsync<PIEEstimationRuleDTO>(
            login, PIEEstimationRuleQB.GET_BY_ID,
            new { RuleId = ruleId },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task<IEnumerable<PIEEstimationRuleDTO>> GetActiveRulesByModuleIdsAsync(
        Guid[] moduleIds, short engagementType, short complexityLevel,
        LoginDTO login, CancellationToken ct)
    {
        // ANY(@ModuleIds) — Npgsql resolves Guid[] automatically
        var param = new
        {
            ModuleIds       = moduleIds,
            EngagementType  = engagementType,
            ComplexityLevel = complexityLevel
        };

        return await _QueryExecutor.QueryAsync<PIEEstimationRuleDTO>(
            login, PIEEstimationRuleQB.GET_ACTIVE_RULES_BY_MODULE_IDS, param,
            cancellationToken: ct).ConfigureAwait(false)
            ?? Enumerable.Empty<PIEEstimationRuleDTO>();
    }

    // ── Snapshot reads ────────────────────────────────────────────────────────

    public async Task<PIEEstimationSnapshotDTO?> GetSnapshotByEngagementAsync(
        string engagementRef, LoginDTO login, CancellationToken ct)
        => await _QueryExecutor.QuerySingleAsync<PIEEstimationSnapshotDTO>(
            login, PIEEstimationRuleQB.GET_SNAPSHOT_BY_ENGAGEMENT,
            new { EngagementRef = engagementRef },
            cancellationToken: ct).ConfigureAwait(false);

    // ── Rule writes ───────────────────────────────────────────────────────────

    public async Task<Result<string>> SavePIEEstimationRuleAsync(
        PIEEstimationRuleDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
    {
        dto.CreatedBy = login.UserId;
        dto.CreatedAt = DateTime.UtcNow;
        dto.UpdatedBy = login.UserId;
        dto.UpdatedAt = DateTime.UtcNow;

        int rows = await _QueryExecutor.ExecuteAsync(
            login, PIEEstimationRuleQB.INSERT, dto, tx,
            cancellationToken: ct).ConfigureAwait(false);

        return rows > 0
            ? Result<string>.Success("PIE estimation rule saved.")
            : Result<string>.Failure("Failed to save PIE estimation rule.");
    }

    public async Task<Result<string>> UpdatePIEEstimationRuleAsync(
        PIEEstimationRuleDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
    {
        dto.UpdatedBy = login.UserId;
        dto.UpdatedAt = DateTime.UtcNow;

        int rows = await _QueryExecutor.ExecuteAsync(
            login, PIEEstimationRuleQB.UPDATE, dto, tx,
            cancellationToken: ct).ConfigureAwait(false);

        return rows > 0
            ? Result<string>.Success("PIE estimation rule updated.")
            : Result<string>.Failure("PIE estimation rule not found or not updated.");
    }

    public async Task<Result<string>> SoftDeletePIEEstimationRuleAsync(
        Guid ruleId, LoginDTO login, DbTransaction tx, CancellationToken ct)
    {
        var param = new
        {
            RuleId    = ruleId,
            UpdatedAt = DateTime.UtcNow,
            UpdatedBy = login.UserId
        };

        int rows = await _QueryExecutor.ExecuteAsync(
            login, PIEEstimationRuleQB.SOFT_DELETE, param, tx,
            cancellationToken: ct).ConfigureAwait(false);

        return rows > 0
            ? Result<string>.Success("PIE estimation rule deactivated.")
            : Result<string>.Failure("PIE estimation rule not found or already inactive.");
    }

    // ── Snapshot writes (insert-only) ─────────────────────────────────────────

    public async Task<Result<string>> InsertSnapshotAsync(
        PIEEstimationSnapshotDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
    {
        dto.CreatedBy = login.UserId;
        dto.CreatedAt = DateTime.UtcNow;

        int rows = await _QueryExecutor.ExecuteAsync(
            login, PIEEstimationRuleQB.INSERT_SNAPSHOT, dto, tx,
            cancellationToken: ct).ConfigureAwait(false);

        return rows > 0
            ? Result<string>.Success("PIE estimation snapshot recorded.")
            : Result<string>.Failure("Failed to record PIE estimation snapshot.");
    }
}