namespace MMDAL.Query.Scheduling
{
    // Indexes required:
    //   MBATCHRULE: (WORKCENTERID, PROCESSID, TENANTID)
    //   MBATCHRULE: (TENANTID, STATUS)
    public static class BatchRuleQB
    {
        // ── Get single batch rule ────────────────────────────────────────

        public const string GET_BATCHRULE = @"
SELECT
    BR.MBATCHRULEID        AS BatchRuleId,
    BR.WORKCENTERID        AS WorkCenterId,
    WC.WORKCENTERCODE      AS WorkCenterCode,
    WC.WORKCENTERNAME      AS WorkCenterName,
    BR.PROCESSID           AS ProcessId,
    PR.PROCESSCODE         AS ProcessCode,
    PR.PROCESSNAME         AS ProcessName,
    BR.BATCHBASISTYPE      AS BatchBasisType,
    BR.BATCHMINIMUM        AS BatchMinimum,
    BR.BATCHMAXIMUM        AS BatchMaximum,
    BR.PREFERREDBATCHQTY   AS PreferredBatchQty,
    BR.BATCHCONTINUITY     AS BatchContinuity,
    BR.STATUS              AS Status,
    BR.TENANTID            AS TenantId,
    BR.CREATEDBYID         AS CreatedById,
    BR.CREATEDON           AS CreatedOn,
    BR.MODIFIEDBYID        AS ModifiedById,
    BR.MODIFIEDON          AS ModifiedOn
FROM  MBATCHRULE BR
INNER JOIN MWORKCENTER WC ON WC.WORKCENTERID = BR.WORKCENTERID
INNER JOIN MPROCESS    PR ON PR.PROCESSID    = BR.PROCESSID
WHERE BR.MBATCHRULEID = @BatchRuleId
AND   BR.TENANTID     = @TenantId;";

        // ── Get all active batch rules for a workcenter ──────────────────

        public const string GET_BATCHRULE_LIST = @"
SELECT
    BR.MBATCHRULEID        AS BatchRuleId,
    BR.WORKCENTERID        AS WorkCenterId,
    WC.WORKCENTERCODE      AS WorkCenterCode,
    WC.WORKCENTERNAME      AS WorkCenterName,
    BR.PROCESSID           AS ProcessId,
    PR.PROCESSCODE         AS ProcessCode,
    PR.PROCESSNAME         AS ProcessName,
    BR.BATCHBASISTYPE      AS BatchBasisType,
    BR.BATCHMINIMUM        AS BatchMinimum,
    BR.BATCHMAXIMUM        AS BatchMaximum,
    BR.PREFERREDBATCHQTY   AS PreferredBatchQty,
    BR.BATCHCONTINUITY     AS BatchContinuity,
    BR.STATUS              AS Status
FROM  MBATCHRULE BR
INNER JOIN MWORKCENTER WC ON WC.WORKCENTERID = BR.WORKCENTERID
INNER JOIN MPROCESS    PR ON PR.PROCESSID    = BR.PROCESSID
WHERE BR.WORKCENTERID = @WorkCenterId
AND   BR.STATUS       = 1
AND   BR.TENANTID     = @TenantId
ORDER BY PR.PROCESSNAME;";

        // ── Get batch rule by workcenter + process (engine lookup) ───────

        public const string GET_BATCHRULE_FOR_PROCESS = @"
SELECT
    BR.MBATCHRULEID        AS BatchRuleId,
    BR.WORKCENTERID        AS WorkCenterId,
    BR.PROCESSID           AS ProcessId,
    BR.BATCHBASISTYPE      AS BatchBasisType,
    BR.BATCHMINIMUM        AS BatchMinimum,
    BR.BATCHMAXIMUM        AS BatchMaximum,
    BR.PREFERREDBATCHQTY   AS PreferredBatchQty,
    BR.BATCHCONTINUITY     AS BatchContinuity,
    BR.STATUS              AS Status,
    BR.TENANTID            AS TenantId
FROM  MBATCHRULE BR
WHERE BR.WORKCENTERID = @WorkCenterId
AND   BR.PROCESSID    = @ProcessId
AND   BR.STATUS       = 1
AND   BR.TENANTID     = @TenantId;";

        // ── Insert ───────────────────────────────────────────────────────

        public const string SAVE_BATCHRULE = @"
INSERT INTO MBATCHRULE
(
    MBATCHRULEID, WORKCENTERID, PROCESSID,
    BATCHBASISTYPE, BATCHMINIMUM, BATCHMAXIMUM, PREFERREDBATCHQTY,
    BATCHCONTINUITY, STATUS,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID
)
VALUES
(
    @BatchRuleId, @WorkCenterId, @ProcessId,
    @BatchBasisType, @BatchMinimum, @BatchMaximum, @PreferredBatchQty,
    @BatchContinuity, @Status,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @TenantId
);";

        // ── Update ───────────────────────────────────────────────────────

        public const string UPDATE_BATCHRULE = @"
UPDATE MBATCHRULE
SET
    WORKCENTERID       = @WorkCenterId,
    PROCESSID          = @ProcessId,
    BATCHBASISTYPE     = @BatchBasisType,
    BATCHMINIMUM       = @BatchMinimum,
    BATCHMAXIMUM       = @BatchMaximum,
    PREFERREDBATCHQTY  = @PreferredBatchQty,
    BATCHCONTINUITY    = @BatchContinuity,
    STATUS             = @Status,
    MODIFIEDBYID       = @ModifiedById,
    MODIFIEDON         = @ModifiedOn
WHERE MBATCHRULEID = @BatchRuleId
AND   TENANTID     = @TenantId;";

        // ── Delete (hard delete — batch rules are configuration, not transactional) ──

        public const string DELETE_BATCHRULE = @"
DELETE FROM MBATCHRULE
WHERE MBATCHRULEID = @BatchRuleId
AND   TENANTID     = @TenantId;";
    }
}