namespace MMDAL.Query.JobCard
{
    public static class JobCardQB
    {
        // ── Save header ───────────────────────────────────────────────────────

        public const string SAVE_JOBCARD_SQL = @"
            INSERT INTO TJOBCARD
                (JOBCARDID, BIZTRANSACTIONTYPEID, OUID, PERIODID,
                 JOBCARDDATE, JOBCARDNUMBER, WORKCENTERID, MACHINEID,
                 SHIFTID, OPERATORID, REMARKS, STATUS, TENANTID)
            VALUES
                (@JobCardId, @BizTransactionTypeId, @OUId, @PeriodId,
                 @JobCardDate, @JobCardNumber, @WorkCenterId, @MachineId,
                 @ShiftId, @OperatorId, @Remarks, 1, @TenantId);";

        public const string SAVE_JOBCARD_PG = @"
            INSERT INTO TJOBCARD
                (JOBCARDID, BIZTRANSACTIONTYPEID, OUID, PERIODID,
                 JOBCARDDATE, JOBCARDNUMBER, WORKCENTERID, MACHINEID,
                 SHIFTID, OPERATORID, REMARKS, STATUS, TENANTID)
            VALUES
                (:JobCardId, :BizTransactionTypeId, :OUId, :PeriodId,
                 :JobCardDate, :JobCardNumber, :WorkCenterId, :MachineId,
                 :ShiftId, :OperatorId, :Remarks, 1, :TenantId);";

        // ── Save detail ───────────────────────────────────────────────────────

        public const string SAVE_JOBCARD_DETAIL_SQL = @"
            INSERT INTO TJOBCARDDETAIL
                (JOBCARDDETAILID, JOBCARDID, SLNO, INDENTDETAILID, ROUTINGDETAILID,
                 PLANNEDQUANTITY, ACTUALQUANTITY, REJECTEDQUANTITY, TENANTID)
            VALUES
                (@JobCardDetailId, @JobCardId, @SlNo, @IndentDetailId, @RoutingDetailId,
                 @PlannedQuantity, @ActualQuantity, @RejectedQuantity, @TenantId);";

        public const string SAVE_JOBCARD_DETAIL_PG = @"
            INSERT INTO TJOBCARDDETAIL
                (JOBCARDDETAILID, JOBCARDID, SLNO, INDENTDETAILID, ROUTINGDETAILID,
                 PLANNEDQUANTITY, ACTUALQUANTITY, REJECTEDQUANTITY, TENANTID)
            VALUES
                (:JobCardDetailId, :JobCardId, :SlNo, :IndentDetailId, :RoutingDetailId,
                 :PlannedQuantity, :ActualQuantity, :RejectedQuantity, :TenantId);";

        // ── Save material ─────────────────────────────────────────────────────

        public const string SAVE_JOBCARD_MATERIAL_SQL = @"
            INSERT INTO TJOBCARDMATERIAL
                (JOBCARDMATERIALID, JOBCARDID, SLNO, ITEMID, SKUID,
                 ISSUEDQUANTITY, CONSUMEDQUANTITY, TENANTID)
            VALUES
                (@JobCardMaterialId, @JobCardId, @SlNo, @ItemId, @SkuId,
                 @IssuedQuantity, @ConsumedQuantity, @TenantId);";

        public const string SAVE_JOBCARD_MATERIAL_PG = @"
            INSERT INTO TJOBCARDMATERIAL
                (JOBCARDMATERIALID, JOBCARDID, SLNO, ITEMID, SKUID,
                 ISSUEDQUANTITY, CONSUMEDQUANTITY, TENANTID)
            VALUES
                (:JobCardMaterialId, :JobCardId, :SlNo, :ItemId, :SkuId,
                 :IssuedQuantity, :ConsumedQuantity, :TenantId);";

        // ── Save process ──────────────────────────────────────────────────────

        public const string SAVE_JOBCARD_PROCESS_SQL = @"
            INSERT INTO TJOBCARDPROCESS
                (JOBCARDPROCESSID, JOBCARDID, SLNO, PROCESSID,
                 STARTMINUTES, ENDMINUTES, TENANTID)
            VALUES
                (@JobCardProcessId, @JobCardId, @SlNo, @ProcessId,
                 @StartMinutes, @EndMinutes, @TenantId);";

        public const string SAVE_JOBCARD_PROCESS_PG = @"
            INSERT INTO TJOBCARDPROCESS
                (JOBCARDPROCESSID, JOBCARDID, SLNO, PROCESSID,
                 STARTMINUTES, ENDMINUTES, TENANTID)
            VALUES
                (:JobCardProcessId, :JobCardId, :SlNo, :ProcessId,
                 :StartMinutes, :EndMinutes, :TenantId);";

        // ── Get details ───────────────────────────────────────────────────────

        public const string GET_JOBCARD_DETAILS_SQL = @"
            SELECT JOBCARDDETAILID, JOBCARDID, SLNO, INDENTDETAILID, ROUTINGDETAILID,
                   PLANNEDQUANTITY, ACTUALQUANTITY, REJECTEDQUANTITY, TENANTID
            FROM   TJOBCARDDETAIL
            WHERE  JOBCARDID = @JobCardId AND TENANTID = @TenantId
            ORDER  BY SLNO;";

        public const string GET_JOBCARD_DETAILS_PG = @"
            SELECT JOBCARDDETAILID, JOBCARDID, SLNO, INDENTDETAILID, ROUTINGDETAILID,
                   PLANNEDQUANTITY, ACTUALQUANTITY, REJECTEDQUANTITY, TENANTID
            FROM   TJOBCARDDETAIL
            WHERE  JOBCARDID = :JobCardId AND TENANTID = :TenantId
            ORDER  BY SLNO;";

        // ── Get single ────────────────────────────────────────────────────────

        public const string GET_JOBCARD_SQL = @"
            SELECT j.JOBCARDID, j.BIZTRANSACTIONTYPEID, j.OUID, j.PERIODID,
                   j.JOBCARDDATE, j.JOBCARDNUMBER,
                   j.WORKCENTERID, ISNULL(wc.WORKCENTERNAME, '') AS WorkCenterName,
                   j.MACHINEID, ISNULL(m.MACHINENAME, '') AS MachineName,
                   j.SHIFTID, j.OPERATORID, j.REMARKS, j.STATUS, j.TENANTID
            FROM   TJOBCARD j
            LEFT   JOIN MWORKCENTER wc ON wc.WORKCENTERID = j.WORKCENTERID AND wc.TENANTID = j.TENANTID
            LEFT   JOIN MMACHINE    m  ON m.MACHINEID     = j.MACHINEID     AND m.TENANTID  = j.TENANTID
            WHERE  j.JOBCARDID = @JobCardId AND j.TENANTID = @TenantId;";

        public const string GET_JOBCARD_PG = @"
            SELECT j.JOBCARDID, j.BIZTRANSACTIONTYPEID, j.OUID, j.PERIODID,
                   j.JOBCARDDATE, j.JOBCARDNUMBER,
                   j.WORKCENTERID, COALESCE(wc.WORKCENTERNAME, '') AS ""WorkCenterName"",
                   j.MACHINEID, COALESCE(m.MACHINENAME, '') AS ""MachineName"",
                   j.SHIFTID, j.OPERATORID, j.REMARKS, j.STATUS, j.TENANTID
            FROM   TJOBCARD j
            LEFT   JOIN MWORKCENTER wc ON wc.WORKCENTERID = j.WORKCENTERID AND wc.TENANTID = j.TENANTID
            LEFT   JOIN MMACHINE    m  ON m.MACHINEID     = j.MACHINEID     AND m.TENANTID  = j.TENANTID
            WHERE  j.JOBCARDID = :JobCardId AND j.TENANTID = :TenantId;";

        // ── Paged list ────────────────────────────────────────────────────────

        public const string GET_JOBCARD_LIST_SQL = @"
            SELECT j.JOBCARDID, j.JOBCARDNUMBER, j.JOBCARDDATE,
                   ISNULL(wc.WORKCENTERNAME, '') AS WorkCenterName,
                   j.STATUS, j.TENANTID
            FROM   TJOBCARD j
            LEFT   JOIN MWORKCENTER wc ON wc.WORKCENTERID = j.WORKCENTERID AND wc.TENANTID = j.TENANTID
            WHERE  j.OUID    = @OUId AND j.STATUS <> 2 AND j.TENANTID = @TenantId
            ORDER  BY j.JOBCARDDATE DESC
            OFFSET @FirstNumber ROWS FETCH NEXT @MaxResult ROWS ONLY;";

        public const string GET_JOBCARD_LIST_COUNT_SQL = @"
            SELECT COUNT(1) FROM TJOBCARD j
            WHERE  j.OUID = @OUId AND j.STATUS <> 2 AND j.TENANTID = @TenantId;";

        public const string GET_JOBCARD_LIST_PG = @"
            SELECT j.JOBCARDID, j.JOBCARDNUMBER, j.JOBCARDDATE,
                   COALESCE(wc.WORKCENTERNAME, '') AS ""WorkCenterName"",
                   j.STATUS, j.TENANTID
            FROM   TJOBCARD j
            LEFT   JOIN MWORKCENTER wc ON wc.WORKCENTERID = j.WORKCENTERID AND wc.TENANTID = j.TENANTID
            WHERE  j.OUID    = :OUId AND j.STATUS <> 2 AND j.TENANTID = :TenantId
            ORDER  BY j.JOBCARDDATE DESC
            LIMIT  :MaxResult OFFSET :FirstNumber;";

        public const string GET_JOBCARD_LIST_COUNT_PG = @"
            SELECT COUNT(1) FROM TJOBCARD j
            WHERE  j.OUID = :OUId AND j.STATUS <> 2 AND j.TENANTID = :TenantId;";

        // ── Get for operator ──────────────────────────────────────────────────

        public const string GET_JOBCARDS_FOR_OPERATOR_SQL = @"
            SELECT j.JOBCARDID, j.JOBCARDNUMBER, j.JOBCARDDATE,
                   jd.INDENTDETAILID, jd.PLANNEDQUANTITY, jd.ACTUALQUANTITY,
                   j.STATUS, j.TENANTID
            FROM   TJOBCARD j
            JOIN   TJOBCARDDETAIL jd ON jd.JOBCARDID = j.JOBCARDID AND jd.TENANTID = j.TENANTID
            WHERE  j.OPERATORID = @OperatorId
            AND    j.STATUS     IN (1, 2)
            AND    j.TENANTID   = @TenantId
            ORDER  BY j.JOBCARDDATE DESC;";

        public const string GET_JOBCARDS_FOR_OPERATOR_PG = @"
            SELECT j.JOBCARDID, j.JOBCARDNUMBER, j.JOBCARDDATE,
                   jd.INDENTDETAILID, jd.PLANNEDQUANTITY, jd.ACTUALQUANTITY,
                   j.STATUS, j.TENANTID
            FROM   TJOBCARD j
            JOIN   TJOBCARDDETAIL jd ON jd.JOBCARDID = j.JOBCARDID AND jd.TENANTID = j.TENANTID
            WHERE  j.OPERATORID = :OperatorId
            AND    j.STATUS     IN (1, 2)
            AND    j.TENANTID   = :TenantId
            ORDER  BY j.JOBCARDDATE DESC;";

        // ── Complete ──────────────────────────────────────────────────────────

        public const string COMPLETE_JOBCARD_SQL = @"
            UPDATE TJOBCARD SET STATUS = 5
            WHERE  JOBCARDID = @JobCardId AND TENANTID = @TenantId;";

        public const string COMPLETE_JOBCARD_PG = @"
            UPDATE TJOBCARD SET STATUS = 5
            WHERE  JOBCARDID = :JobCardId AND TENANTID = :TenantId;";

        // ── Soft delete ───────────────────────────────────────────────────────

        public const string SOFT_DELETE_JOBCARD_SQL = @"
            UPDATE TJOBCARD SET STATUS = 2
            WHERE  JOBCARDID = @JobCardId AND TENANTID = @TenantId;";

        public const string SOFT_DELETE_JOBCARD_PG = @"
            UPDATE TJOBCARD SET STATUS = 2
            WHERE  JOBCARDID = :JobCardId AND TENANTID = :TenantId;";
    }
}