namespace QMSDAL.Query.ChecklistDetail
{
    // ─────────────────────────────────────────────────────────────────────────
    //  ChecklistDetailQB  – SQL constants for MCHECKLISTDETAIL
    //
    //  Rules:
    //    • All column names UPPERCASE
    //    • All parameters use @ParameterName (Dapper named binding)
    //    • TENANTID filter always included — IQueryExecutor auto-populates @TenantId
    //    • Delete+reinsert pattern on save (no UPDATE on detail rows)
    //
    //  Indexes required:
    //    MCHECKLISTDETAIL: (CHECKLISTID) — already defined in DDL
    // ─────────────────────────────────────────────────────────────────────────
    public static class ChecklistDetailQB
    {
        // ── Get all detail rows for a checklist (with LOV options via LEFT JOIN) ──
        // Caller groups flat rows by ChecklistDetailId to build LovOptions list.

        public const string GET_CHECKLISTDETAIL = @"
SELECT
    D.CHECKLISTDETAILID AS ChecklistDetailId,
    D.CHECKLISTID       AS ChecklistId,
    D.SLNO              AS SlNo,
    D.SECTION           AS Section,
    D.DETAILTEXT        AS DetailText,
    D.ACTIONTYPE        AS ActionType,
    D.UOMID             AS UomId,
    U.UOMNAME           AS UomName,
    D.MINVAL            AS MinVal,
    D.MAXVAL            AS MaxVal,
    D.LOVTYPEID         AS LovTypeId,
    D.ISMANDATORY       AS IsMandatory,
    D.HELPTEXT          AS HelpText,
    D.FAILACTION        AS FailAction,
    D.TENANTID          AS TenantId,              -- already correct here
    L.LOVID             AS LovId,
    L.LOVNAME           AS LovName
FROM  MCHECKLISTDETAIL D
LEFT JOIN MUOM U ON U.UOMID     = D.UOMID     AND D.UOMID     != -1
LEFT JOIN MLOV L ON L.LOVTYPEID = D.LOVTYPEID AND D.LOVTYPEID != -1
WHERE D.CHECKLISTID = @ChecklistId
ORDER BY D.SLNO;";

        // ── Delete all detail rows for a checklist (before reinsert on update) ─

        public const string DELETE_CHECKLISTDETAIL = @"
DELETE FROM MCHECKLISTDETAIL
WHERE  CHECKLISTID = @ChecklistId
AND    TENANTID    = @TenantId;";

        // ── Insert one detail row ─────────────────────────────────────────────

        public const string SAVE_CHECKLISTDETAIL = @"
INSERT INTO MCHECKLISTDETAIL
(
    CHECKLISTID,
    SLNO,
    SECTION,
    DETAILTEXT,
    ACTIONTYPE,
    UOMID,
    MINVAL,
    MAXVAL,
    LOVTYPEID,
    ISMANDATORY,
    HELPTEXT,
    FAILACTION,
    TENANTID,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON
)
VALUES
(
    @ChecklistId,
    @SlNo,
    @Section,
    @DetailText,
    @ActionType,
    @UomId,
    @MinVal,
    @MaxVal,
    @LovTypeId,
    @IsMandatory,
    @HelpText,
    @FailAction,
    @TenantId,
    @CreatedById,
    @CreatedOn,
    @ModifiedById,
    @ModifiedOn
);";
        public const string UPDATE_CHECKLISTDETAIL = @"
UPDATE MCHECKLISTDETAIL
SET
    SLNO            = @SlNo,
    SECTION         = @Section,
    DETAILTEXT      = @DetailText,
    ACTIONTYPE      = @ActionType,
    UOMID           = @UomId,
    MINVAL          = @MinVal,
    MAXVAL          = @MaxVal,
    LOVTYPEID       = @LovTypeId,
    ISMANDATORY     = @IsMandatory,
    HELPTEXT        = @HelpText,
    FAILACTION      = @FailAction,

    MODIFIEDBYID    = @ModifiedById,
    MODIFIEDON      = @ModifiedOn,

    SORTORDER       = @SortOrder,
    STATUS          = @Status,
    VERSION         = VERSION + 1
WHERE
    CHECKLISTDETAILID = @ChecklistDetailId
    AND CHECKLISTID   = @ChecklistId
    AND TENANTID      = @TenantId;
";
    }
}