namespace QMSDAL.Query.ChecklistApplicability
{
    // ─────────────────────────────────────────────────────────────────────────
    //  ChecklistApplicabilityQB  – SQL constants for MCHECKLISTAPPLICABILITY
    //
    //  Rules:
    //    • All column names UPPERCASE
    //    • All parameters use @ParameterName (Dapper named binding)
    //    • TENANTID filter always included — IQueryExecutor auto-populates @TenantId
    //    • Hard delete (not soft) — applicability rules have no STATUS column
    //
    //  Indexes required:
    //    MCHECKLISTAPPLICABILITY: IX_MCHKAPP_RESOLVE already defined in DDL
    // ─────────────────────────────────────────────────────────────────────────
    public static class ChecklistApplicabilityQB
    {
        // ── Get single applicability rule ─────────────────────────────────────

        public const string GET_CHECKLISTAPPLICABILITY = @"
SELECT
    A.CHECKLISTAPPLICABILITYID  AS ChecklistApplicabilityId,
    A.CHECKLISTID               AS ChecklistId,
    CL.CHECKLISTNAME            AS ChecklistName,
    A.SCOPETYPE                 AS ScopeType,
    A.PROCESSID                 AS ProcessId,
    A.ITEMID                    AS ItemId,
    A.SKUID                     AS SkuId,
    A.INDENTID                  AS IndentId,
    A.INDENTDETAILID            AS IndentDetailId,
    A.WORKCENTERID              AS WorkcenterId,
    A.BIZPROCESSSTAGEID         AS BizProcessStageId,
    A.FREQUENCYTYPE             AS FrequencyType,
    A.FREQUENCYVALUE            AS FrequencyValue,
    A.EFFECTIVEFROM             AS EffectiveFrom,
    A.EFFECTIVETO               AS EffectiveTo,
    A.PRIORITY                  AS Priority,
    A.VERSION                   AS Version,
    A.TENANTID                  AS TenantId,
    A.CREATEDBYID               AS CreatedById,
    A.CREATEDON                 AS CreatedOn,
    A.MODIFIEDBYID              AS ModifiedById,
    A.MODIFIEDON                AS ModifiedOn
FROM  MCHECKLISTAPPLICABILITY A
JOIN  MCHECKLIST CL ON CL.CHECKLISTID = A.CHECKLISTID
WHERE A.CHECKLISTAPPLICABILITYID = @ChecklistApplicabilityId;";

        // ── Get all applicability rules for a checklist ───────────────────────

        public const string GET_CHECKLISTAPPLICABILITY_LIST = @"
SELECT
    A.CHECKLISTAPPLICABILITYID  AS ChecklistApplicabilityId,
    A.CHECKLISTID               AS ChecklistId,
    CL.CHECKLISTNAME            AS ChecklistName,
    A.SCOPETYPE                 AS ScopeType,
    A.PROCESSID                 AS ProcessId,
    A.ITEMID                    AS ItemId,
    A.SKUID                     AS SkuId,
    A.INDENTID                  AS IndentId,
    A.INDENTDETAILID            AS IndentDetailId,
    A.WORKCENTERID              AS WorkcenterId,
    A.BIZPROCESSSTAGEID         AS BizProcessStageId,
    A.FREQUENCYTYPE             AS FrequencyType,
    A.FREQUENCYVALUE            AS FrequencyValue,
    A.EFFECTIVEFROM             AS EffectiveFrom,
    A.EFFECTIVETO               AS EffectiveTo,
    A.PRIORITY                  AS Priority,
    A.VERSION                   AS Version,
    A.TENANTID                  AS TenantId
FROM  MCHECKLISTAPPLICABILITY A
JOIN  MCHECKLIST CL ON CL.CHECKLISTID = A.CHECKLISTID
ORDER BY A.PRIORITY DESC, A.CHECKLISTAPPLICABILITYID DESC;";

        // ── Check duplicate (same checklist + scope combination) ─────────────
        // Used before save to prevent duplicate scope rules for same checklist.

//        public const string CHECK_DUPLICATE_APPLICABILITY = @"
//SELECT COUNT(*)
//FROM   MCHECKLISTAPPLICABILITY
//WHERE  TENANTID    = @TenantId
//AND    CHECKLISTID = @ChecklistId
//AND    SCOPETYPE   = @ScopeType
//AND    PROCESSID   = @ProcessId
//AND    ITEMID      = @ItemId
//AND    SKUID       = @SkuId
//AND    WORKCENTERID = @WorkcenterId
//AND    CHECKLISTAPPLICABILITYID <> @ChecklistApplicabilityId;";

        // ── Insert applicability rule ─────────────────────────────────────────

        public const string SAVE_CHECKLISTAPPLICABILITY = @"
INSERT INTO MCHECKLISTAPPLICABILITY
(
    CHECKLISTID, SCOPETYPE, PROCESSID, ITEMID, SKUID,
    INDENTID, INDENTDETAILID, WORKCENTERID, BIZPROCESSSTAGEID,
    FREQUENCYTYPE, FREQUENCYVALUE,
    EFFECTIVEFROM, EFFECTIVETO, PRIORITY, VERSION,
    TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @ChecklistId, @ScopeType, @ProcessId, @ItemId, @SkuId,
    @IndentId, @IndentDetailId, @WorkcenterId, @BizProcessStageId,
    @FrequencyType, @FrequencyValue,
    @EffectiveFrom, @EffectiveTo, @Priority, @Version,
    @TenantId, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
);

-- ✅ This must be the ONLY SELECT — returns new PK to QuerySingleAsync
SELECT CAST(SCOPE_IDENTITY() AS INT);
";

        // ── Update applicability rule (optimistic concurrency on VERSION) ─────

        public const string UPDATE_CHECKLISTAPPLICABILITY = @"
UPDATE MCHECKLISTAPPLICABILITY
SET
    SCOPETYPE       = @ScopeType,
    PROCESSID       = @ProcessId,
    ITEMID          = @ItemId,
    SKUID           = @SkuId,
    INDENTID        = @IndentId,
    INDENTDETAILID  = @IndentDetailId,
    WORKCENTERID    = @WorkcenterId,
    BIZPROCESSSTAGEID = @BizProcessStageId,
    FREQUENCYTYPE   = @FrequencyType,
    FREQUENCYVALUE  = @FrequencyValue,
    EFFECTIVEFROM   = @EffectiveFrom,
    EFFECTIVETO     = @EffectiveTo,
    PRIORITY        = @Priority,
    VERSION         = VERSION + 1,
    MODIFIEDBYID    = @ModifiedById,
    MODIFIEDON      = @ModifiedOn
WHERE CHECKLISTAPPLICABILITYID = @ChecklistApplicabilityId
AND   TENANTID                 = @TenantId
AND   VERSION                  = @Version;";

        // ── Hard delete (no STATUS on applicability rules) ────────────────────

        public const string DELETE_CHECKLISTAPPLICABILITY = @"
DELETE FROM MCHECKLISTAPPLICABILITY
WHERE  CHECKLISTAPPLICABILITYID = @ChecklistApplicabilityId";
    }
}