using Dapper;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.ListQuery;
using IDMSDAL.DTOs;

namespace IDMSDAL.QueryBuilders;

// Required indexes:
//   TIMPLEMENTATIONENGAGEMENT: (TENANTID, STATUS) INCLUDE (ENGAGEMENTCODE, ENGAGEMENTNAME)
//   TIMPLEMENTATIONENGAGEMENT: (TENANTID, CLIENTID)
//   TENGAGEMENTMODULE:         (ENGAGEMENTID, TENANTID) INCLUDE (MODULECODE, ISPRIMARY)

// ── Engagement list query builder ─────────────────────────────────────────────

public sealed class EngagementQB : IQueryBuilder<EngagementListQuery, EngagementListDTO>
{
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["engagementcode"] = "E.ENGAGEMENTCODE",
            ["engagementname"] = "E.ENGAGEMENTNAME",
            ["status"]         = "E.STATUS",
            ["health"]         = "E.HEALTHSTATUS",
            ["createdon"]      = "E.CREATEDON"
        };

    public (string Sql, DynamicParameters Params) Build(EngagementListQuery query, LoginDTO login, ISqlDialect dialect)
    {
        var orderBy = SqlClauses.OrderBy(
            query.Criteria.SortBy, query.Criteria.SortDesc,
            _sort, "E.CREATEDON DESC");
        return BuildEngagementList(query.Criteria, dialect, orderBy);
    }

    public static (string Sql, DynamicParameters Params) BuildEngagementList(
        EngagementListCriteria c, ISqlDialect d, string orderBy)
    {
        var ctx = new QueryContext(d).Register("engagement", "E");

        var (whereSql, whereParams) = SqlClause.AsWhere(new[]
        {
            new SqlClause("E.TENANTID = @TenantId", new DynamicParameters()),
            SqlClauses.ExactShort(ctx,  c.Status,         "engagement", "STATUS"),
            SqlClauses.ExactShort(ctx,  c.EngagementType, "engagement", "ENGAGEMENTTYPE"),
            SqlClauses.ExactString(ctx, c.Source,         "engagement", "ENGAGEMENTSOURCE"),
            SqlClauses.ExactString(ctx, c.HealthStatus,   "engagement", "HEALTHSTATUS"),
            SqlClauses.CodeNameSearch(ctx, c.SearchText,  "engagement", "ENGAGEMENTCODE", "ENGAGEMENTNAME"),
        });

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

        string sql = $@"
SELECT {d.TotalCountExpr()},
       E.ENGAGEMENTID   AS EngagementId,
       E.ENGAGEMENTCODE AS EngagementCode,
       E.ENGAGEMENTNAME AS EngagementName,
       E.ENGAGEMENTTYPE AS EngagementType,
       E.STATUS         AS Status,
       E.HEALTHSTATUS   AS HealthStatus,
       E.DELIVERYMODE   AS DeliveryMode
FROM   TIMPLEMENTATIONENGAGEMENT E
{whereSql}
{pagingSql}";

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

    // ── CRUD SQL ──────────────────────────────────────────────────────────────

    public const string GET_BY_ID = @"
SELECT E.ENGAGEMENTID               AS EngagementId,
       E.TENANTID                   AS TenantId,
       E.CLIENTID                   AS ClientId,
       E.ENGAGEMENTCODE             AS EngagementCode,
       E.ENGAGEMENTNAME             AS EngagementName,
       E.ENGAGEMENTTYPE             AS EngagementType,
       E.ENGAGEMENTSOURCE           AS EngagementSource,
       E.DELIVERYMODE               AS DeliveryMode,
       E.STATUS                     AS Status,
       E.ACTIVEDOMAINS              AS ActiveDomains,
       E.PIEENGAGEMENTID            AS PieEngagementId,
       E.PIEESTIMATIONSNAPSHOTREF   AS PieEstimationSnapshotRef,
       E.QUICKSTARTPROFILEID        AS QuickstartProfileId,
       E.QUICKSTARTPROFILEVERSION   AS QuickstartProfileVersion,
       E.DEVIATIONANSWERS           AS DeviationAnswers,
       E.PROFILEMATCHCONFIDENCE     AS ProfileMatchConfidence,
       E.BILLINGMODEL               AS BillingModel,
       E.CONTRACTVALUE              AS ContractValue,
       E.CONTRACTREF                AS ContractRef,
       E.HEALTHSTATUS               AS HealthStatus,
       E.UPGRADESIGNALSCORE         AS UpgradeSignalScore,
       E.UPGRADESUGGSTEDON          AS UpgradeSuggstedOn,
       E.ESCALATEDON                AS EscalatedOn,
       E.ESCALATEDBYID              AS EscalatedById,
       E.ESCALATIONREASON           AS EscalationReason,
       E.SHADOWCONSULTANTID         AS ShadowConsultantId,
       E.SHADOWASSIGNEDON           AS ShadowAssignedOn,
       E.SHADOWLASTREVIEWEDON       AS ShadowLastReviewedOn,
       E.PORTALACTIVATEDON          AS PortalActivatedOn,
       E.SELFPROVISIONCOMPLETEDON   AS SelfProvisionCompletedOn,
       E.REMOTESESSIONCOUNT         AS RemoteSessionCount,
       E.ROITRACKINGENABLED         AS RoiTrackingEnabled,
       E.GOLIVEON                   AS GoLiveOn,
       E.PLANNEDGOLIVEON            AS PlannedGoLiveOn,
       E.HYPERCAREENDON             AS HypercareEndOn,
       E.LEGALVAULTLEVEL            AS LegalVaultLevel,
       E.VERSION                    AS Version,
       E.SORTORDER                  AS SortOrder,
       E.SOURCETYPE                 AS SourceType,
       E.CREATEDBYID                AS EngagementCreatedById,
       E.CREATEDON                  AS EngagementCreatedOn,
       E.MODIFIEDBYID               AS EngagementModifiedById,
       E.MODIFIEDON                 AS EngagementModifiedOn
FROM   TIMPLEMENTATIONENGAGEMENT E
WHERE  E.ENGAGEMENTID = @EngagementId
  AND  E.TENANTID     = @TenantId";

    public const string GET_MODULES = @"
SELECT EM.ENGAGEMENTMODULEID  AS EngagementModuleId,
       EM.ENGAGEMENTID        AS EngagementId,
       EM.TENANTID            AS TenantId,
       EM.SLNO                AS Slno,
       EM.MODULEID            AS ModuleId,
       EM.MODULECODE          AS ModuleCode,
       EM.MODULENAME          AS ModuleName,
       EM.PIEVARIANTSSELECTED AS PieVariantsSelected,
       EM.ESTIMATEDHOURS      AS EstimatedHours,
       EM.ACTUALHOURS         AS ActualHours,
       EM.REWORKHOURS         AS ReworkHours,
       EM.ISPRIMARY           AS IsPrimary,
       EM.SEEDINGSTATUS       AS SeedingStatus,
       EM.SEEDEDON            AS SeededOn
FROM   TENGAGEMENTMODULE EM
WHERE  EM.ENGAGEMENTID = @EngagementId
  AND  EM.TENANTID     = @TenantId
ORDER BY EM.ISPRIMARY DESC, EM.MODULECODE";

    public const string INSERT = @"
INSERT INTO TIMPLEMENTATIONENGAGEMENT
    (ENGAGEMENTID, TENANTID, CLIENTID, ENGAGEMENTCODE, ENGAGEMENTNAME,
     ENGAGEMENTTYPE, ENGAGEMENTSOURCE, DELIVERYMODE, STATUS, ACTIVEDOMAINS,
     PIEENGAGEMENTID, PIEESTIMATIONSNAPSHOTREF,
     BILLINGMODEL, CONTRACTVALUE, CONTRACTREF,
     LEGALVAULTLEVEL, VERSION, SORTORDER, SOURCETYPE,
     CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
VALUES
    (@EngagementId, @TenantId, @ClientId, @EngagementCode, @EngagementName,
     @EngagementType, @EngagementSource, @DeliveryMode, @Status, @ActiveDomains,
     @PieEngagementId, @PieEstimationSnapshotRef,
     @BillingModel, @ContractValue, @ContractRef,
     @LegalVaultLevel, @Version, @SortOrder, @SourceType,
     @EngagementCreatedById, @EngagementCreatedOn, @EngagementModifiedById, @EngagementModifiedOn)";

    public const string UPDATE = @"
UPDATE TIMPLEMENTATIONENGAGEMENT SET
    ENGAGEMENTNAME             = @EngagementName,
    ENGAGEMENTSOURCE           = @EngagementSource,
    DELIVERYMODE               = @DeliveryMode,
    PIEENGAGEMENTID            = @PieEngagementId,
    PIEESTIMATIONSNAPSHOTREF   = @PieEstimationSnapshotRef,
    BILLINGMODEL               = @BillingModel,
    CONTRACTVALUE              = @ContractValue,
    CONTRACTREF                = @ContractRef,
    LEGALVAULTLEVEL            = @LegalVaultLevel,
    MODIFIEDBYID               = @EngagementModifiedById,
    MODIFIEDON                 = @EngagementModifiedOn
WHERE  ENGAGEMENTID = @EngagementId AND TENANTID = @TenantId";

    public const string ACTIVATE = @"
UPDATE TIMPLEMENTATIONENGAGEMENT SET
    STATUS         = 2,
    ACTIVEDOMAINS  = @ActiveDomains,
    MODIFIEDBYID   = @EngagementModifiedById,
    MODIFIEDON     = @EngagementModifiedOn
WHERE  ENGAGEMENTID = @EngagementId AND TENANTID = @TenantId AND STATUS = 1";

    public const string DECLARE_GOLIVE = @"
UPDATE TIMPLEMENTATIONENGAGEMENT SET
    STATUS       = 6,
    GOLIVEON     = @GoLiveOn,
    MODIFIEDBYID = @EngagementModifiedById,
    MODIFIEDON   = @EngagementModifiedOn
WHERE  ENGAGEMENTID = @EngagementId AND TENANTID = @TenantId";

    public const string UPDATE_HEALTH = @"
UPDATE TIMPLEMENTATIONENGAGEMENT SET
    HEALTHSTATUS         = @HealthStatus,
    UPGRADESIGNALSCORE   = @UpgradeSignalScore,
    MODIFIEDBYID         = @EngagementModifiedById,
    MODIFIEDON           = @EngagementModifiedOn
WHERE  ENGAGEMENTID = @EngagementId AND TENANTID = @TenantId";

    public const string INSERT_MODULE = @"
INSERT INTO TENGAGEMENTMODULE
    (ENGAGEMENTMODULEID, ENGAGEMENTID, TENANTID, SLNO,
     MODULEID, MODULECODE, MODULENAME, PIEVARIANTSSELECTED,
     ESTIMATEDHOURS, ISPRIMARY, SEEDINGSTATUS)
VALUES
    (@EngagementModuleId, @EngagementId, @TenantId, @Slno,
     @ModuleId, @ModuleCode, @ModuleName, @PieVariantsSelected,
     @EstimatedHours, @IsPrimary, @SeedingStatus)";

    public const string UPDATE_MODULE_SEEDING = @"
UPDATE TENGAGEMENTMODULE SET
    SEEDINGSTATUS = @SeedingStatus,
    SEEDEDON      = @SeededOn
WHERE  ENGAGEMENTMODULEID = @EngagementModuleId AND TENANTID = @TenantId";

    public const string DELETE_MODULES = @"
DELETE FROM TENGAGEMENTMODULE
WHERE  ENGAGEMENTID = @EngagementId AND TENANTID = @TenantId";

    public const string GET_DASHBOARD = @"
SELECT E.ENGAGEMENTID           AS EngagementId,
       E.ENGAGEMENTCODE         AS EngagementCode,
       E.ENGAGEMENTNAME         AS EngagementName,
       E.STATUS                 AS Status,
       E.HEALTHSTATUS           AS HealthStatus,
       E.UPGRADESIGNALSCORE     AS UpgradeSignalScore,
       COUNT(DI.DELIVERYITEMID) AS TotalItems,
       SUM(CASE WHEN DI.STATUS = 3 THEN 1 ELSE 0 END)                              AS CompletedItems,
       SUM(CASE WHEN DI.STATUS < 3 AND DI.DUEON < GETDATE() THEN 1 ELSE 0 END)     AS OverdueItems,
       SUM(CASE WHEN DI.ISREWORK = 1 THEN 1 ELSE 0 END)                            AS ReworkCount,
       COUNT(DISTINCT CASE WHEN S.ISACTIVE = 1 THEN S.SIGNALID END)                AS ActiveSignals
FROM   TIMPLEMENTATIONENGAGEMENT E
LEFT   JOIN TDELIVERYITEM DI
       ON DI.ENGAGEMENTID = E.ENGAGEMENTID AND DI.TENANTID = E.TENANTID
LEFT   JOIN LENGAGEMENTSIGNAL S
       ON S.ENGAGEMENTID = E.ENGAGEMENTID AND S.TENANTID = E.TENANTID
WHERE  E.ENGAGEMENTID = @EngagementId
  AND  E.TENANTID     = @TenantId
GROUP  BY E.ENGAGEMENTID, E.ENGAGEMENTCODE, E.ENGAGEMENTNAME,
          E.STATUS, E.HEALTHSTATUS, E.UPGRADESIGNALSCORE";
}