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

namespace PIEDAL.Query.Outcome;

// Required indexes:
//   ix_pie_outcome_tenant       ON pie.TPIEOUTCOME(TENANTID)
//   ix_pie_outcome_module       ON pie.TPIEOUTCOME(PIEMODULEID, RECORDEDON DESC)
//   ix_pie_outcome_profile      ON pie.TPIEOUTCOME(PIEPROFILEID, RECORDEDON DESC) WHERE PIEPROFILEID IS NOT NULL

// Outcomes are insert-only — no UPDATE or DELETE SQL needed.

public sealed class PIEOutcomeQB : IQueryBuilder<PIEOutcomeListQuery, PIEOutcomeListDTO>
{
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["recordedat"]     = "o.RECORDEDON",
            ["actualhours"]    = "o.ACTUALHOURS",
            ["estimatedhours"] = "o.ESTIMATEDHOURS",
            ["industry"]       = "o.INDUSTRY",
            ["geography"]      = "o.GEOGRAPHY"
        };

    public (string Sql, DynamicParameters Params) Build(PIEOutcomeListQuery query, LoginDTO login, ISqlDialect dialect)
    {
        var orderBy = SqlClauses.OrderBy(
            query.Criteria.SortBy, query.Criteria.SortDesc, _sort,
            "o.RECORDEDON DESC");
        return BuildOutcomeList(query.Criteria, login, dialect, orderBy);
    }

    public static (string Sql, DynamicParameters Params) BuildOutcomeList(
        PIEOutcomeListCriteria c, LoginDTO login, ISqlDialect d, string orderBy)
    {
        var ctx = new QueryContext(d)
            .Register("outcome", "o", schema: "pie")
            .Register("module",  "m", schema: "pie");

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

        var (whereSql, whereParams) = SqlClause.AsWhere(new[]
        {
            new SqlClause("o.TENANTID = @TenantId", tenantParams),
            SqlClauses.ExactGuid(ctx,  c.ModuleId,       "outcome", "PIEMODULEID"),
            SqlClauses.ExactGuid(ctx,  c.ProfileId,      "outcome", "PIEPROFILEID"),
            SqlClauses.ExactString(ctx, c.Industry,      "outcome", "INDUSTRY"),
            SqlClauses.ExactString(ctx, c.Geography,     "outcome", "GEOGRAPHY"),
            SqlClauses.ExactShort(ctx,  c.EngagementType,"outcome", "ENGAGEMENTTYPE"),
            SqlClauses.ExactShort(ctx,  c.GoLiveSuccess, "outcome", "GOLIVESUCCESS"),
        });

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

        string sql = $@"
SELECT {d.TotalCountExpr()},
       o.PIEOUTCOMEID     AS OutcomeId,
       o.ENGAGEMENTREF    AS EngagementRef,
       o.PIEMODULEID      AS ModuleId,
       m.PIEMODULENAME    AS ModuleName,
       o.INDUSTRY         AS Industry,
       o.GEOGRAPHY        AS Geography,
       o.ACTUALHOURS      AS ActualHours,
       o.ESTIMATEDHOURS   AS EstimatedHours,
       o.GOLIVESUCCESS    AS GoLiveSuccess,
       o.RECORDEDON       AS RecordedAt
FROM   {ctx.Table("outcome", "TPIEOUTCOME")} o
JOIN   {ctx.Table("module", "MPIEMODULE")} m
       ON  m.PIEMODULEID = o.PIEMODULEID
       AND m.TENANTID    = o.TENANTID
{whereSql}
{pagingSql}";

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

    // ── Outcome CRUD ──────────────────────────────────────────────────────────

    public const string GET_BY_ID = @"
        SELECT o.PIEOUTCOMEID      AS OutcomeId,
               o.ENGAGEMENTREF     AS EngagementRef,
               o.PIEMODULEID       AS ModuleId,
               o.PIEPROFILEID      AS ProfileId,
               o.PROFILEVERSION    AS ProfileVersion,
               o.ENGAGEMENTTYPE    AS EngagementType,
               o.INDUSTRY          AS Industry,
               o.COMPANYSIZE       AS CompanySize,
               o.GEOGRAPHY         AS Geography,
               o.ESTIMATEDHOURS    AS EstimatedHours,
               o.ACTUALHOURS       AS ActualHours,
               o.REWORKHOURS       AS ReworkHours,
               o.REWORKCOUNT       AS ReworkCount,
               o.UATPASSRATE       AS UatPassRate,
               o.GOLIVESUCCESS     AS GoLiveSuccess,
               o.COMPLETIONDAYS    AS CompletionDays,
               o.REWORKREASONS     AS ReworkReasons,
               o.DEVIATIONMAP      AS DeviationMap,
               o.RECORDEDON        AS RecordedAt,
               o.STATUS            AS Status,
               o.TENANTID          AS TenantId
        FROM   pie.TPIEOUTCOME o
        WHERE  o.PIEOUTCOMEID = @OutcomeId
          AND  o.TENANTID     = @TenantId";

    /// <summary>Insert-only. Outcomes are never updated — they are the historical record.</summary>
    public const string INSERT = @"
        INSERT INTO pie.TPIEOUTCOME
            (PIEOUTCOMEID, ENGAGEMENTREF, PIEMODULEID, PIEPROFILEID, PROFILEVERSION,
             ENGAGEMENTTYPE, INDUSTRY, COMPANYSIZE, GEOGRAPHY,
             ESTIMATEDHOURS, ACTUALHOURS, REWORKHOURS, REWORKCOUNT,
             UATPASSRATE, GOLIVESUCCESS, COMPLETIONDAYS,
             REWORKREASONS, DEVIATIONMAP, RECORDEDON, STATUS, TENANTID)
        VALUES
            (@OutcomeId, @EngagementRef, @ModuleId, @ProfileId, @ProfileVersion,
             @EngagementType, @Industry, @CompanySize, @Geography,
             @EstimatedHours, @ActualHours, @ReworkHours, @ReworkCount,
             @UatPassRate, @GoLiveSuccess, @CompletionDays,
             @ReworkReasons, @DeviationMap, @RecordedAt, @Status, @TenantId)";

    /// <summary>Returns aggregated outcome signals for profile pattern detection.</summary>
    public const string GET_OUTCOMES_FOR_SIGNAL_ANALYSIS = @"
        SELECT o.PIEOUTCOMEID    AS OutcomeId,
               o.PIEMODULEID     AS ModuleId,
               o.PIEPROFILEID    AS ProfileId,
               o.ENGAGEMENTTYPE  AS EngagementType,
               o.INDUSTRY        AS Industry,
               o.DEVIATIONMAP    AS DeviationMap,
               o.ACTUALHOURS     AS ActualHours,
               o.ESTIMATEDHOURS  AS EstimatedHours,
               o.REWORKCOUNT     AS ReworkCount,
               o.GOLIVESUCCESS   AS GoLiveSuccess,
               o.RECORDEDON      AS RecordedAt
        FROM   pie.TPIEOUTCOME o
        WHERE  o.PIEPROFILEID  = @ProfileId
          AND  o.TENANTID      = @TenantId
          AND  o.RECORDEDON   >= @FromDate
        ORDER BY o.RECORDEDON DESC";
}