using Dapper;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.ListQuery;
using PIEDAL.DTO.Estimation;

namespace PIEDAL.Query.Estimation;

// Required indexes:
//   ix_pie_estimation_rule_tenant ON pie.MPIEESTIMATIONRULE(TENANTID)
//   ix_pie_est_rule               ON pie.MPIEESTIMATIONRULE(PIEMODULEID, ENGAGEMENTTYPE, COMPLEXITYLEVEL)
//   ix_pie_est_snapshot           ON pie.LPIEESTIMATIONSNAPSHOT(ENGAGEMENTREF)
//   ix_pie_estimation_snapshot_tenant ON pie.LPIEESTIMATIONSNAPSHOT(TENANTID)

public sealed class PIEEstimationRuleQB : IQueryBuilder<PIEEstimationRuleListQuery, PIEEstimationRuleListDTO>
{
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["engagementtype"]  = "er.ENGAGEMENTTYPE",
            ["complexitylevel"] = "er.COMPLEXITYLEVEL",
            ["basehours"]       = "er.BASEHOURS",
            ["validfrom"]       = "er.VALIDFROM"
        };

    public (string Sql, DynamicParameters Params) Build(PIEEstimationRuleListQuery query, LoginDTO login, ISqlDialect dialect)
    {
        var orderBy = SqlClauses.OrderBy(
            query.Criteria.SortBy, query.Criteria.SortDesc, _sort,
            "m.PIEMODULECODE ASC, er.ENGAGEMENTTYPE ASC, er.COMPLEXITYLEVEL ASC");
        return BuildEstimationRuleList(query.Criteria, login, dialect, orderBy);
    }

    public static (string Sql, DynamicParameters Params) BuildEstimationRuleList(
        PIEEstimationRuleListCriteria c, LoginDTO login, ISqlDialect d, string orderBy)
    {
        var ctx = new QueryContext(d)
            .Register("estimrule", "er", schema: "pie")
            .Register("module",    "m",  schema: "pie");

        var tenantParams = new DynamicParameters();
        tenantParams.Add("TenantId", login.ClientId);

        var (whereSql, whereParams) = SqlClause.AsWhere(new[]
        {
            new SqlClause("er.TENANTID = @TenantId", tenantParams),
            SqlClauses.ExactGuid(ctx,  c.ModuleId,        "estimrule", "PIEMODULEID"),
            SqlClauses.ExactShort(ctx, c.EngagementType,  "estimrule", "ENGAGEMENTTYPE"),
            SqlClauses.ExactShort(ctx, c.ComplexityLevel, "estimrule", "COMPLEXITYLEVEL"),
            SqlClauses.ExactShort(ctx,  c.IsActive,       "estimrule", "STATUS"),
        });

        var (pagingSql, pagingParams) = SqlClauses.Paging(ctx, c, orderBy);

        string sql = $@"
SELECT {d.TotalCountExpr()},
       er.PIEESTIMATIONRULEID AS RuleId,
       er.PIEMODULEID          AS ModuleId,
       m.PIEMODULENAME         AS ModuleName,
       er.ENGAGEMENTTYPE       AS EngagementType,
       er.COMPLEXITYLEVEL      AS ComplexityLevel,
       er.BASEHOURS            AS BaseHours,
       er.CONFIDENCESCORE      AS ConfidenceScore,
       er.STATUS               AS IsActive
FROM   {ctx.Table("estimrule", "MPIEESTIMATIONRULE")} er
JOIN   {ctx.Table("module", "MPIEMODULE")} m
       ON  m.PIEMODULEID = er.PIEMODULEID
       AND m.TENANTID    = er.TENANTID
{whereSql}
{pagingSql}";

        return (sql, SqlClause.Merge(whereParams, pagingParams));
    }

    // ── Estimation rule CRUD ──────────────────────────────────────────────────

    public const string GET_BY_ID = @"
        SELECT er.PIEESTIMATIONRULEID AS RuleId,
               er.PIEMODULEID         AS ModuleId,
               m.PIEMODULENAME        AS ModuleName,
               er.ENGAGEMENTTYPE      AS EngagementType,
               er.COMPLEXITYLEVEL     AS ComplexityLevel,
               er.BASEHOURS           AS BaseHours,
               er.CONFIGHOURS         AS ConfigHours,
               er.TRAININGHOURS       AS TrainingHours,
               er.MIGRATIONHOURS      AS MigrationHours,
               er.UATHOURS            AS UatHours,
               er.PMOVERHEADPCT       AS PmOverheadPct,
               er.CONFIDENCESCORE     AS ConfidenceScore,
               er.STATUS              AS IsActive,
               er.VALIDFROM           AS ValidFrom,
               er.VALIDTO             AS ValidTo,
               er.CREATEDON           AS CreatedAt,
               er.CREATEDBYID         AS CreatedBy,
               er.MODIFIEDON          AS UpdatedAt,
               er.MODIFIEDBYID        AS UpdatedBy,
               er.TENANTID            AS TenantId
        FROM   pie.MPIEESTIMATIONRULE er
        JOIN   pie.MPIEMODULE m ON m.PIEMODULEID = er.PIEMODULEID AND m.TENANTID = er.TENANTID
        WHERE  er.PIEESTIMATIONRULEID = @RuleId
          AND  er.TENANTID            = @TenantId";

    /// <summary>Returns active rules for given modules — used by estimation calculation engine.</summary>
    public const string GET_ACTIVE_RULES_BY_MODULE_IDS = @"
        SELECT er.PIEESTIMATIONRULEID AS RuleId,
               er.PIEMODULEID         AS ModuleId,
               er.ENGAGEMENTTYPE      AS EngagementType,
               er.COMPLEXITYLEVEL     AS ComplexityLevel,
               er.BASEHOURS           AS BaseHours,
               er.CONFIGHOURS         AS ConfigHours,
               er.TRAININGHOURS       AS TrainingHours,
               er.MIGRATIONHOURS      AS MigrationHours,
               er.UATHOURS            AS UatHours,
               er.PMOVERHEADPCT       AS PmOverheadPct,
               er.CONFIDENCESCORE     AS ConfidenceScore
        FROM   pie.MPIEESTIMATIONRULE er
        WHERE  er.PIEMODULEID      = ANY(@ModuleIds)
          AND  er.ENGAGEMENTTYPE   = @EngagementType
          AND  er.COMPLEXITYLEVEL  = @ComplexityLevel
          AND  er.STATUS           = 1
          AND  er.VALIDFROM        <= CURRENT_DATE
          AND  (er.VALIDTO IS NULL OR er.VALIDTO >= CURRENT_DATE)
          AND  er.TENANTID         = @TenantId";

    public const string INSERT = @"
        INSERT INTO pie.MPIEESTIMATIONRULE
            (PIEESTIMATIONRULEID, PIEMODULEID, ENGAGEMENTTYPE, COMPLEXITYLEVEL,
             BASEHOURS, CONFIGHOURS, TRAININGHOURS, MIGRATIONHOURS, UATHOURS,
             PMOVERHEADPCT, CONFIDENCESCORE, STATUS, VALIDFROM, VALIDTO,
             CREATEDON, CREATEDBYID, MODIFIEDON, MODIFIEDBYID, TENANTID)
        VALUES
            (@RuleId, @ModuleId, @EngagementType, @ComplexityLevel,
             @BaseHours, @ConfigHours, @TrainingHours, @MigrationHours, @UatHours,
             @PmOverheadPct, @ConfidenceScore, @IsActive, @ValidFrom, @ValidTo,
             @CreatedAt, @CreatedBy, @UpdatedAt, @UpdatedBy, @TenantId)";

    public const string UPDATE = @"
        UPDATE pie.MPIEESTIMATIONRULE SET
            ENGAGEMENTTYPE  = @EngagementType,
            COMPLEXITYLEVEL = @ComplexityLevel,
            BASEHOURS       = @BaseHours,
            CONFIGHOURS     = @ConfigHours,
            TRAININGHOURS   = @TrainingHours,
            MIGRATIONHOURS  = @MigrationHours,
            UATHOURS        = @UatHours,
            PMOVERHEADPCT   = @PmOverheadPct,
            CONFIDENCESCORE = @ConfidenceScore,
            STATUS          = @IsActive,
            VALIDFROM       = @ValidFrom,
            VALIDTO         = @ValidTo,
            MODIFIEDON      = @UpdatedAt,
            MODIFIEDBYID    = @UpdatedBy
        WHERE PIEESTIMATIONRULEID = @RuleId AND TENANTID = @TenantId";

    public const string SOFT_DELETE = @"
        UPDATE pie.MPIEESTIMATIONRULE SET
            STATUS       = 0,
            MODIFIEDON   = @UpdatedAt,
            MODIFIEDBYID = @UpdatedBy
        WHERE PIEESTIMATIONRULEID = @RuleId AND TENANTID = @TenantId";

    // ── pie_estimation_snapshot — insert-only (immutable) ─────────────────────
    // DB rule no_update_pie_estimation_snapshot prevents updates at DB level.

    public const string INSERT_SNAPSHOT = @"
        INSERT INTO pie.LPIEESTIMATIONSNAPSHOT
            (LPIEESTIMATIONSNAPSHOTID, ENGAGEMENTREF, SNAPSHOTDATA, TOTALHOURS, CONFIDENCESCORE,
             CREATEDON, CREATEDBYID, TENANTID, STATUS)
        VALUES
            (@SnapshotId, @EngagementRef, @SnapshotData, @TotalHours, @ConfidenceScore,
             @CreatedAt, @CreatedBy, @TenantId, @Status)";

    public const string GET_SNAPSHOT_BY_ENGAGEMENT = @"
        SELECT s.LPIEESTIMATIONSNAPSHOTID AS SnapshotId,
               s.ENGAGEMENTREF            AS EngagementRef,
               s.SNAPSHOTDATA             AS SnapshotData,
               s.TOTALHOURS               AS TotalHours,
               s.CONFIDENCESCORE          AS ConfidenceScore,
               s.CREATEDON                AS CreatedAt,
               s.CREATEDBYID              AS CreatedBy,
               s.TENANTID                 AS TenantId,
               s.STATUS                   AS Status
        FROM   pie.LPIEESTIMATIONSNAPSHOT s
        WHERE  s.ENGAGEMENTREF = @EngagementRef
          AND  s.TENANTID      = @TenantId
        ORDER BY s.CREATEDON DESC
        LIMIT 1";
}