namespace QMSDAL.Query.PcsCardStage
{
    // INDEX RECOMMENDATION:
    //   IX_TPCSCARDSTAGE_PCSCARDID  ON TPCSCARDSTAGE (PCSCARDID, STAGENO)
    //     INCLUDE (STAGESTATUS, STAGERESULT, ACTUALSTART, ACTUALEND,
    //              SUPERVISORID, CHECKLISTLOGID, GOODQUANTITY, REWORKQUANTITY, REJECTEDQUANTITY)
    //     — primary access pattern: all stages for one card
    //   IX_TPCSCARDSTAGE_OPEN_SUPERVISOR  ON TPCSCARDSTAGE (STAGESTATUS, REQUIRESSUPERVISOR)
    //     INCLUDE (PCSCARDID, STAGENO, STAGENAME, STAGEGROUP, CHECKLISTID)
    //     WHERE STAGESTATUS IN (1,4)  — filtered: open stages needing supervisor action
    public static class PcsCardStageQB
    {
        public const string GET_PCSCARDSTAGE_BY_CARD = @"
            SELECT
                s.PCSCARDSTAEID        AS PcsCardStaeId,
                s.PCSCARDID            AS PcsCardId,
                s.ROUTINGDETAILID      AS RoutingDetailId,
                s.STAGENO              AS StageNo,
                s.STAGECODE            AS StageCode,
                s.STAGENAME            AS StageName,
                s.STAGEGROUP           AS StageGroup,
                s.REQUIRESSUPERVISOR   AS RequiresSupervisor,
                s.REQUIRESMATERIALLOG  AS RequiresMaterialLog,
                s.ALLOWQUANTITY        AS AllowQuantity,
                s.CHECKLISTID          AS ChecklistId,
                s.PLANNEDSTART         AS PlannedStart,
                s.PLANNEDEND           AS PlannedEnd,
                s.ACTUALSTART          AS ActualStart,
                s.ACTUALEND            AS ActualEnd,
                s.OPERATORID           AS OperatorId,
                s.SUPERVISORID         AS SupervisorId,
                s.SUPERVISOREDON       AS Supervisedon,
                s.GOODQUANTITY         AS GoodQuantity,
                s.REWORKQUANTITY       AS ReworkQuantity,
                s.REJECTEDQUANTITY     AS RejectedQuantity,
                s.OTHERQUANTITY        AS OtherQuantity,
                s.CHECKLISTLOGID       AS ChecklistLogId,
                s.STAGESTATUS          AS StageStatus,
                s.STAGERESULT          AS StageResult,
                s.REMARKS              AS Remarks,
                u1.USERNAME            AS OperatorName,
                u2.USERNAME            AS SupervisorName
            FROM   TPCSCARDSTAGE s
            LEFT   JOIN MUSER u1 ON s.OPERATORID   = u1.USERID
            LEFT   JOIN MUSER u2 ON s.SUPERVISORID = u2.USERID
            WHERE  s.PCSCARDID = @PcsCardId
            ORDER  BY s.STAGENO";

        public const string GET_PCSCARDSTAGE = @"
            SELECT
                s.PCSCARDSTAEID        AS PcsCardStaeId,
                s.PCSCARDID            AS PcsCardId,
                s.ROUTINGDETAILID      AS RoutingDetailId,
                s.STAGENO              AS StageNo,
                s.STAGECODE            AS StageCode,
                s.STAGENAME            AS StageName,
                s.STAGEGROUP           AS StageGroup,
                s.REQUIRESSUPERVISOR   AS RequiresSupervisor,
                s.REQUIRESMATERIALLOG  AS RequiresMaterialLog,
                s.ALLOWQUANTITY        AS AllowQuantity,
                s.CHECKLISTID          AS ChecklistId,
                s.PLANNEDSTART         AS PlannedStart,
                s.PLANNEDEND           AS PlannedEnd,
                s.ACTUALSTART          AS ActualStart,
                s.ACTUALEND            AS ActualEnd,
                s.OPERATORID           AS OperatorId,
                s.SUPERVISORID         AS SupervisorId,
                s.SUPERVISOREDON       AS Supervisedon,
                s.GOODQUANTITY         AS GoodQuantity,
                s.REWORKQUANTITY       AS ReworkQuantity,
                s.REJECTEDQUANTITY     AS RejectedQuantity,
                s.OTHERQUANTITY        AS OtherQuantity,
                s.CHECKLISTLOGID       AS ChecklistLogId,
                s.STAGESTATUS          AS StageStatus,
                s.STAGERESULT          AS StageResult,
                s.REMARKS              AS Remarks,
                u1.USERNAME            AS OperatorName,
                u2.USERNAME            AS SupervisorName
            FROM   TPCSCARDSTAGE s
            LEFT   JOIN MUSER u1 ON s.OPERATORID   = u1.USERID
            LEFT   JOIN MUSER u2 ON s.SUPERVISORID = u2.USERID
            WHERE  s.PCSCARDSTAEID = @PcsCardStaeId";

        public const string INSERT_PCSCARDSTAGE = @"
            INSERT INTO TPCSCARDSTAGE
            (
                PCSCARDSTAEID, PCSCARDID, ROUTINGDETAILID,
                STAGENO, STAGECODE, STAGENAME, STAGEGROUP,
                REQUIRESSUPERVISOR, REQUIRESMATERIALLOG, ALLOWQUANTITY, CHECKLISTID,
                PLANNEDSTART, PLANNEDEND, ACTUALSTART, ACTUALEND,
                OPERATORID, SUPERVISORID, SUPERVISOREDON,
                GOODQUANTITY, REWORKQUANTITY, REJECTEDQUANTITY, OTHERQUANTITY,
                CHECKLISTLOGID, STAGESTATUS, STAGERESULT, REMARKS
            )
            VALUES
            (
                @PcsCardStaeId, @PcsCardId, @RoutingDetailId,
                @StageNo, @StageCode, @StageName, @StageGroup,
                @RequiresSupervisor, @RequiresMaterialLog, @AllowQuantity, @ChecklistId,
                @PlannedStart, @PlannedEnd, NULL, NULL,
                -1, -1, NULL,
                0, 0, 0, 0,
                -1, 0, 0, NULL
            )";

        public const string UPDATE_PCSCARDSTAGE_OPEN = @"
            UPDATE TPCSCARDSTAGE
            SET
                STAGESTATUS  = 1,
                ACTUALSTART  = GETUTCDATE(),
                OPERATORID   = CASE WHEN @OperatorId > 0 THEN @OperatorId ELSE OPERATORID END
            WHERE  PCSCARDSTAEID = @PcsCardStaeId";

        public const string UPDATE_PCSCARDSTAGE_CHECKLISTLOGID = @"
            UPDATE TPCSCARDSTAGE
            SET    CHECKLISTLOGID = @ChecklistLogId
            WHERE  PCSCARDSTAEID  = @PcsCardStaeId";

        public const string UPDATE_PCSCARDSTAGE_COMPLETE = @"
            UPDATE TPCSCARDSTAGE
            SET
                STAGESTATUS    = 2,
                STAGERESULT    = @StageResult,
                ACTUALEND      = GETUTCDATE(),
                SUPERVISORID   = @SupervisorId,
                SUPERVISOREDON = GETUTCDATE(),
                REMARKS        = @Remarks
            WHERE  PCSCARDSTAEID = @PcsCardStaeId";

        public const string UPDATE_PCSCARDSTAGE_NCR = @"
            UPDATE TPCSCARDSTAGE
            SET
                STAGESTATUS  = 4,
                STAGERESULT  = 2,
                REMARKS      = @Description
            WHERE  PCSCARDSTAEID = @PcsCardStaeId
            AND    STAGESTATUS   NOT IN (2, 4)";

        public const string UPDATE_PCSCARDSTAGE_SKIP = @"
            UPDATE TPCSCARDSTAGE
            SET
                STAGESTATUS    = 5,
                SUPERVISORID   = @SupervisorId,
                SUPERVISOREDON = GETUTCDATE(),
                REMARKS        = @Reason
            WHERE  PCSCARDSTAEID = @PcsCardStaeId";

        public const string GET_PCSCARDSTAGE_COMPLETED_COUNT = @"
            SELECT COUNT(*)
            FROM   TPCSCARDSTAGE
            WHERE  PCSCARDID   = @PcsCardId
            AND    STAGESTATUS = 2";

        public const string GET_PCSCARDSTAGE_NEXT_PENDING = @"
            SELECT TOP 1
                s.PCSCARDSTAEID        AS PcsCardStaeId,
                s.PCSCARDID            AS PcsCardId,
                s.ROUTINGDETAILID      AS RoutingDetailId,
                s.STAGENO              AS StageNo,
                s.STAGECODE            AS StageCode,
                s.STAGENAME            AS StageName,
                s.STAGEGROUP           AS StageGroup,
                s.REQUIRESSUPERVISOR   AS RequiresSupervisor,
                s.REQUIRESMATERIALLOG  AS RequiresMaterialLog,
                s.ALLOWQUANTITY        AS AllowQuantity,
                s.CHECKLISTID          AS ChecklistId,
                s.PLANNEDSTART         AS PlannedStart,
                s.PLANNEDEND           AS PlannedEnd,
                s.ACTUALSTART          AS ActualStart,
                s.ACTUALEND            AS ActualEnd,
                s.OPERATORID           AS OperatorId,
                s.SUPERVISORID         AS SupervisorId,
                s.SUPERVISOREDON       AS Supervisedon,
                s.GOODQUANTITY         AS GoodQuantity,
                s.REWORKQUANTITY       AS ReworkQuantity,
                s.REJECTEDQUANTITY     AS RejectedQuantity,
                s.OTHERQUANTITY        AS OtherQuantity,
                s.CHECKLISTLOGID       AS ChecklistLogId,
                s.STAGESTATUS          AS StageStatus,
                s.STAGERESULT          AS StageResult,
                s.REMARKS              AS Remarks,
                NULL                   AS OperatorName,
                NULL                   AS SupervisorName
            FROM   TPCSCARDSTAGE s
            WHERE  s.PCSCARDID   = @PcsCardId
            AND    s.STAGENO     > @AfterStageno
            AND    s.STAGESTATUS = 0
            ORDER  BY s.STAGENO";

        // Loads all MROUTINGDETAIL rows for a given routing + version.
        // VARIANTS is stored as JSON (e.g. '["VARIM","RTM"]') — filtering to applicable
        // variants is done in BLL after deserializing the JSON column.
        public const string GET_MROUTINGDETAIL_FOR_SKU = @"
            SELECT
                ROUTINGDETAILID       AS RoutingDetailId,
                SLNO                  AS StageNo,
                STAGECODE             AS StageCode,
                OPERATIONNAME         AS StageName,
                NULL                  AS StageGroup,
                REQUIRESSUPERVISOR    AS RequiresSupervisor,
                REQUIRESMATERIALLOG   AS RequiresMaterialLog,
                ALLOWQUANTITY         AS AllowQuantity,
                CHECKLISTID           AS ChecklistId,
                NULL                  AS Variants
            FROM   MROUTINGDETAIL
            WHERE  ROUTINGID = @RoutingId
            AND    STATUS    <> 2
            ORDER  BY SLNO";

        // Updates TCHECKLISTLOG after stage completion — called within CompleteStageAsync tx
        public const string UPDATE_TCHECKLISTLOG_COMPLETE = @"
            UPDATE TCHECKLISTLOG
            SET
                OVERALLRESULT   = @OverallResult,
                COMPLETEDBYID   = @CompletedById,
                COMPLETEDON     = GETUTCDATE(),
                MODIFIEDBYID    = @CompletedById,
                MODIFIEDON      = GETUTCDATE()
            WHERE  CHECKLISTLOGID = @ChecklistLogId";
    }
}