namespace QMSDAL.Query.ChecklistLog
{
    public static class ChecklistLogQB
    {
        // ── Get single log header ─────────────────────────────────────────────

        public const string GET_CHECKLISTLOG = @"
SELECT
    L.CHECKLISTLOGID                AS ChecklistLogId,
    L.OUID                          AS OuId,
    L.PERIODID                      AS PeriodId,
    L.BIZTRANSACTIONTYPEID          AS BizTransactionTypeId,
    L.CHECKLISTID                   AS ChecklistId,
    CL.CHECKLISTNAME                AS ChecklistName,
    L.NUMBER                        AS LogNo,
    L.DATE                          AS LogDate,
    L.REFERENCENUMBER               AS ReferenceNo,
    L.REFERENCEDATE                 AS ReferenceDate,
    L.OBJECTBIZTRANSACTIONTYPEID    AS ObjectBizTransactionTypeId,
    L.OBJECTHEADERTYPEID            AS ObjectHeaderTypeId,
    L.OBJECTHEADERID                AS ObjectHeaderId,
    L.OBJECTTYPEID                  AS ObjectTypeId,
    L.OBJECTID                      AS ObjectId,
    L.COMPLETEDBYID                 AS CompletedById,
    CB.USERNAME                     AS CompletedByName,
    L.COMPLETEDON                   AS CompletedOn,
    L.OVERALLRESULT                 AS OverallResult,
    L.SKIPPEDREASON                 AS SkippedReason,
    L.STATUS                        AS Status,
    L.TENANTID                      AS TenantId,
    L.CREATEDBYID                   AS CreatedById,
    L.CREATEDON                     AS CreatedOn,
    L.MODIFIEDBYID                  AS ModifiedById,
    L.MODIFIEDON                    AS ModifiedOn
FROM TCHECKLISTLOG L
JOIN MCHECKLIST CL ON CL.CHECKLISTID = L.CHECKLISTID
LEFT JOIN MUSER CB ON CB.USERID = L.COMPLETEDBYID
WHERE L.CHECKLISTLOGID = @ChecklistLogId;";

        // ── Get log list (paginated) ──────────────────────────────────────────

        public const string GET_CHECKLISTLOG_LIST = @"
SELECT
    L.CHECKLISTLOGID    AS ChecklistLogId,
    L.CHECKLISTID       AS ChecklistId,
    CL.CHECKLISTNAME    AS ChecklistName,
    L.NUMBER            AS LogNo,
    L.DATE              AS LogDate,
    L.REFERENCENUMBER   AS ReferenceNo,
    L.COMPLETEDBYID     AS CompletedById,
    CB.USERNAME         AS CompletedByName,
    L.COMPLETEDON       AS CompletedOn,
    L.OVERALLRESULT     AS OverallResult,
    L.STATUS            AS Status,
    L.TENANTID          AS TenantId
FROM TCHECKLISTLOG L
JOIN MCHECKLIST CL ON CL.CHECKLISTID = L.CHECKLISTID
LEFT JOIN MUSER CB ON CB.USERID = L.COMPLETEDBYID
WHERE L.TENANTID = @TenantId
AND (@ChecklistId = -1 OR L.CHECKLISTID = @ChecklistId)
AND (@OverallResult = -1 OR L.OVERALLRESULT = @OverallResult)
AND (@FromDate IS NULL OR L.DATE >= @FromDate)
AND (@ToDate IS NULL OR L.DATE <= @ToDate)
ORDER BY L.DATE DESC, L.CHECKLISTLOGID DESC
OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY;";

        public const string GET_CHECKLISTLOG_LIST_COUNT = @"
SELECT COUNT(*)
FROM TCHECKLISTLOG L
WHERE L.TENANTID = @TenantId
AND (@ChecklistId = -1 OR L.CHECKLISTID = @ChecklistId)
AND (@OverallResult = -1 OR L.OVERALLRESULT = @OverallResult)
AND (@FromDate IS NULL OR L.DATE >= @FromDate)
AND (@ToDate IS NULL OR L.DATE <= @ToDate);";

        // ── Get log detail rows ──────────────────────────────────────────────

        public const string GET_CHECKLISTLOGDETAIL = @"
SELECT
    LD.CHECKLISTLOGDETAILID AS ChecklistLogDetailId,
    LD.CHECKLISTLOGID       AS ChecklistLogId,
    LD.SLNO                 AS SlNo,
    LD.CHECKLISTDETAILID    AS ChecklistDetailId,
    LD.INPUTVALUE           AS InputValue,
    LD.INPUTTEXT            AS InputText,
    LD.INPUTID              AS InputId,
    LV.LOVNAME              AS LovName,
    LD.ATTACHMENTID         AS AttachmentId,
    LD.RESULT               AS Result,
    LD.REMARKS              AS Remarks,
    LD.TENANTID             AS TenantId,
    D.SECTION               AS Section,
    D.DETAILTEXT            AS DetailText,
    D.ACTIONTYPE            AS ActionType,
    D.FAILACTION            AS FailAction,
    D.ISMANDATORY           AS IsMandatory,
    D.MINVAL                AS MinVal,
    D.MAXVAL                AS MaxVal
FROM TCHECKLISTLOGDETAIL LD
JOIN MCHECKLISTDETAIL D ON D.CHECKLISTDETAILID = LD.CHECKLISTDETAILID
LEFT JOIN MLOV LV ON LV.LOVID = LD.INPUTID AND LD.INPUTID <> -1
WHERE LD.CHECKLISTLOGID = @ChecklistLogId
ORDER BY LD.SLNO;";

        // ── INSERT log header (CHECKLISTLOGID is IDENTITY) ──────────────────

//        public const string SAVE_CHECKLISTLOG = @"
//INSERT INTO TCHECKLISTLOG
//(
//    OUID,
//    BIZTRANSACTIONTYPEID,
//    PERIODID,
//    CHECKLISTID,
//    NUMBER,
//    DATE,
//    REFERENCENUMBER,
//    REFERENCEDATE,
//    OBJECTBIZTRANSACTIONTYPEID,
//    OBJECTHEADERTYPEID,
//    OBJECTHEADERID,
//    OBJECTTYPEID,
//    OBJECTID,
//    COMPLETEDBYID,
//    COMPLETEDON,
//    OVERALLRESULT,
//    SKIPPEDREASON,
//    STATUS,
//    TENANTID,
//    CREATEDBYID,
//    CREATEDON,
//    MODIFIEDBYID,
//    MODIFIEDON,
//    SORTORDER,
//    VERSION,
//    SOURCETYPE
//)
//VALUES
//(
//    @OuId,
//    @BizTransactionTypeId,
//    @PeriodId,
//    @ChecklistId,
//    @LogNo,
//    @LogDate,
//    @ReferenceNo,
//    @ReferenceDate,
//    @ObjectBizTransactionTypeId,
//    @ObjectHeaderTypeId,
//    @ObjectHeaderId,
//    @ObjectTypeId,
//    @ObjectId,
//    @CompletedById,
//    @CompletedOn,
//    @OverallResult,
//    @SkippedReason,
//    @Status,
//    @TenantId,
//    @CreatedById,
//    @CreatedOn,
//    @ModifiedById,
//    @ModifiedOn,
//    9999,
//    1,
//    5
//);
//SELECT CAST(SCOPE_IDENTITY() AS INT);";

        // ── UPDATE log header ──────────────────────────────────────────────────

        public const string UPDATE_CHECKLISTLOG = @"
UPDATE TCHECKLISTLOG SET
    OUID = @OuId,
    BIZTRANSACTIONTYPEID = @BizTransactionTypeId,
    PERIODID = @PeriodId,
    CHECKLISTID = @ChecklistId,
    NUMBER = @LogNo,
    DATE = @LogDate,
    REFERENCENUMBER = @ReferenceNo,
    REFERENCEDATE = @ReferenceDate,
    OBJECTBIZTRANSACTIONTYPEID = @ObjectBizTransactionTypeId,
    OBJECTHEADERTYPEID = @ObjectHeaderTypeId,
    OBJECTHEADERID = @ObjectHeaderId,
    OBJECTTYPEID = @ObjectTypeId,
    OBJECTID = @ObjectId,
    COMPLETEDBYID = @CompletedById,
    COMPLETEDON = @CompletedOn,
    OVERALLRESULT = @OverallResult,
    SKIPPEDREASON = @SkippedReason,
    STATUS = @Status,
    TENANTID = @TenantId,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON = @ModifiedOn
WHERE CHECKLISTLOGID = @ChecklistLogId";

        // ── INSERT detail row (CHECKLISTLOGDETAILID is IDENTITY) ────────────

        //        public const string SAVE_CHECKLISTLOGDETAIL = @"
        //INSERT INTO TCHECKLISTLOGDETAIL
        //(
        //    CHECKLISTLOGID,
        //    SLNO,
        //    CHECKLISTDETAILID,
        //    INPUTVALUE,
        //    INPUTTEXT,
        //    INPUTID,
        //    ATTACHMENTID,
        //    RESULT,
        //    REMARKS,
        //    TENANTID,
        //    CREATEDBYID,
        //    CREATEDON,
        //    MODIFIEDBYID,
        //    MODIFIEDON,
        //    SORTORDER,
        //    STATUS,
        //    VERSION,
        //    SOURCETYPE
        //)
        //VALUES
        //(
        //    @ChecklistLogId,
        //    @SlNo,
        //    @ChecklistDetailId,
        //    @InputValue,
        //    @InputText,
        //    @InputId,
        //    @AttachmentId,
        //    @Result,
        //    @Remarks,
        //    @TenantId,
        //    @CreatedById,
        //    @CreatedOn,
        //    @ModifiedById,
        //    @ModifiedOn,
        //    9999,
        //    1,
        //    1,
        //    5
        //);";
        // ChecklistLogQB.cs

        public const string SAVE_CHECKLISTLOG = @"
INSERT INTO TCHECKLISTLOG
(
    OUID,
    BIZTRANSACTIONTYPEID,
    PERIODID,
    CHECKLISTID,
    NUMBER,
    DATE,
    REFERENCENUMBER,
    REFERENCEDATE,
    OBJECTBIZTRANSACTIONTYPEID,
    OBJECTHEADERTYPEID,
    OBJECTHEADERID,
    OBJECTTYPEID,
    OBJECTID,
    COMPLETEDBYID,
    COMPLETEDON,
    OVERALLRESULT,
    SKIPPEDREASON,
    STATUS,
    TENANTID,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SORTORDER,
    VERSION,
    SOURCETYPE
)
VALUES
(
    @OuId,
    @BizTransactionTypeId,
    @PeriodId,
    @ChecklistId,
    @LogNo,
    @LogDate,
    @ReferenceNo,
    @ReferenceDate,
    @ObjectBizTransactionTypeId,
    @ObjectHeaderTypeId,
    @ObjectHeaderId,
    @ObjectTypeId,
    @ObjectId,
    @CompletedById,
    @CompletedOn,
    @OverallResult,
    @SkippedReason,
    @Status,
    @TenantId,
    @CreatedById,
    @CreatedOn,
    @ModifiedById,
    @ModifiedOn,
    @SortOrder,
    @Version,
    @SourceType
);
SELECT CAST(SCOPE_IDENTITY() AS INT);";

        public const string SAVE_CHECKLISTLOGDETAIL = @"
INSERT INTO TCHECKLISTLOGDETAIL
(
    CHECKLISTLOGID,
    SLNO,
    CHECKLISTDETAILID,
    INPUTVALUE,
    INPUTTEXT,
    INPUTID,
    ATTACHMENTID,
    RESULT,
    REMARKS,
    TENANTID,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SORTORDER,
    STATUS,
    VERSION,
    SOURCETYPE
)
VALUES
(
    @ChecklistLogId,
    @SlNo,
    @ChecklistDetailId,
    @InputValue,
    @InputText,
    @InputId,
    @AttachmentId,
    @Result,
    @Remarks,
    @TenantId,
    @CreatedById,
    @CreatedOn,
    @ModifiedById,
    @ModifiedOn,
    @SortOrder,
    @Status,
    @Version,
    @SourceType
);";
        public const string GET_SELECTLIST_CHECKLISTLOG = @"SELECT
                                                                     L.CHECKLISTLOGID   AS Id,
                                                                     L.NUMBER                        AS LogNo,
                                                                    L.REFERENCENUMBER               AS ReferenceNo
                                                                        FROM TCHECKLISTLOG L;";

    }
}