namespace MMDAL.Query.Routing
{
    public static class RoutingQB
    {
        // ── Picklist ─────────────────────────────────────────────────────────

        public const string GET_SELECTLIST_ROUTING = @"
            SELECT r.ROUTINGID   AS Id,
                   r.ROUTINGCODE AS Code,
                   r.ROUTINGNAME AS Name
            FROM   MROUTING r
            WHERE  r.STATUS != 2";

        // ── Get Header ────────────────────────────────────────────────────────

        public const string GET_ROUTING_HEADER_SQL = @"
            SELECT r.ROUTINGID, r.ROUTINGCODE, r.ROUTINGNAME, r.ITEMID, r.PROCESSID,
                   r.ROUTINGTYPE, r.REMARKS, r.ISBORPOST, r.TENANTID
            FROM   MROUTING r
            WHERE  r.ROUTINGID = @RoutingId AND r.TENANTID = @TenantId AND r.STATUS != 2";

        public const string GET_ROUTING_HEADER_PG = @"
            SELECT r.ROUTINGID, r.ROUTINGCODE, r.ROUTINGNAME, r.ITEMID, r.PROCESSID,
                   r.ROUTINGTYPE, r.REMARKS, r.ISBORPOST, r.TENANTID
            FROM   MROUTING r
            WHERE  r.ROUTINGID = :RoutingId AND r.TENANTID = :TenantId AND r.STATUS != 2";

        // ── Get Versions ──────────────────────────────────────────────────────

        public const string GET_ROUTING_VERSIONS_SQL = @"
            SELECT rv.ROUTINGVERSIONID, rv.ROUTINGID, rv.VERSION, rv.ACTIVEFROMDATE,
                   rv.ACTIVETODATE, rv.REMARKS, rv.WORKORDERTYPE, rv.QUANTITY,
                   rv.ITEMID, rv.SKUID, rv.ISSKUPOSTING, rv.ISSTAGEORPROCESS,
                   rv.ISLASTPROCESSWIP, rv.TENANTID
            FROM   MROUTINGVERSION rv
            WHERE  rv.ROUTINGID = @RoutingId AND rv.TENANTID = @TenantId
            ORDER  BY rv.VERSION";

        public const string GET_ROUTING_VERSIONS_PG = @"
            SELECT rv.ROUTINGVERSIONID, rv.ROUTINGID, rv.VERSION, rv.ACTIVEFROMDATE,
                   rv.ACTIVETODATE, rv.REMARKS, rv.WORKORDERTYPE, rv.QUANTITY,
                   rv.ITEMID, rv.SKUID, rv.ISSKUPOSTING, rv.ISSTAGEORPROCESS,
                   rv.ISLASTPROCESSWIP, rv.TENANTID
            FROM   MROUTINGVERSION rv
            WHERE  rv.ROUTINGID = :RoutingId AND rv.TENANTID = :TenantId
            ORDER  BY rv.VERSION";

        // ── Version Picklist ──────────────────────────────────────────────────
        // GB4 parity: Routing.svc/RoutingVersionPicklist — RoutingBLL.RoutingVersionPicklist
        // default path (ItemQueryBuilder.GET_ITEM_BASED_ON_VERSION): Routing joined to RoutingVersion.
        // No RoutingId filter — paged via FirstNumber/MaxResult (same ROW_NUMBER + sentinel
        // convention as CityQB.GET_CITYPICKLIST_FOR_SELECT2: FirstNumber=-1 AND MaxResult=-1
        // returns the full unpaged list).

        public const string GET_SELECTLIST_ROUTINGVERSION_SQL = @"
            WITH PagedRoutingVersion AS (
                SELECT
                       rv.ROUTINGID          AS RoutingId,
                       r.ROUTINGNAME         AS RoutingName,
                       rv.ROUTINGVERSIONID   AS RoutingVersionId,
                       rv.VERSION            AS RoutingVersion,
                       ROW_NUMBER() OVER (ORDER BY rv.VERSION) AS RowNum
                FROM   MROUTINGVERSION rv
                JOIN   MROUTING r ON r.ROUTINGID = rv.ROUTINGID
            )
            SELECT RoutingId, RoutingName, RoutingVersionId, RoutingVersion
            FROM   PagedRoutingVersion
            WHERE  (@FirstNumber = -1 AND @MaxResult = -1)
                OR (RowNum BETWEEN @FirstNumber AND @MaxResult)
            ORDER  BY RoutingVersion";

        public const string GET_SELECTLIST_ROUTINGVERSION_PG = @"
            WITH PagedRoutingVersion AS (
                SELECT
                       rv.ROUTINGID          AS RoutingId,
                       r.ROUTINGNAME         AS RoutingName,
                       rv.ROUTINGVERSIONID   AS RoutingVersionId,
                       rv.VERSION            AS RoutingVersion,
                       ROW_NUMBER() OVER (ORDER BY rv.VERSION) AS RowNum
                FROM   MROUTINGVERSION rv
                JOIN   MROUTING r ON r.ROUTINGID = rv.ROUTINGID
            )
            SELECT RoutingId, RoutingName, RoutingVersionId, RoutingVersion
            FROM   PagedRoutingVersion
            WHERE  (:FirstNumber = -1 AND :MaxResult = -1)
                OR (RowNum BETWEEN :FirstNumber AND :MaxResult)
            ORDER  BY RoutingVersion";

        // ── Get Version Details ───────────────────────────────────────────────

        public const string GET_ROUTING_VERSION_DETAILS_SQL = @"
            SELECT rvd.ROUTINGDETAILID, rvd.ROUTINGVERSIONID, rvd.SLNO, rvd.STAGE, rvd.STEP,
                   rvd.PROCESSID, rvd.WORKCENTERID, rvd.LOCATIONTYPE, rvd.FIXEDLEADTIME,
                   rvd.LEADTIMEUOM, rvd.PREDECESSOR, rvd.REMARKS, rvd.TENANTID,
                   rvd.ISINTERRUPTIBLE, rvd.MINIMUMRUNMINUTES, rvd.MAXWAITMINUTES,
                   rvd.EXECUTIONMODE, rvd.SUBCONTRACTLEADTIMEDAYS, rvd.REWORKROUTINGDETAILID,
                   rvd.BATCHCONTINUITY, rvd.MONITORGROUP, rvd.ISGROUPOUTPUTSTEP,
                   rvd.CONNECTIONTYPE, rvd.ISSHIPMENT, rvd.ISPACKFLAG
            FROM   MROUTINGVERSIONDETAIL rvd
            WHERE  rvd.ROUTINGVERSIONID = @RoutingVersionId AND rvd.TENANTID = @TenantId
            ORDER  BY rvd.SLNO";

        public const string GET_ROUTING_VERSION_DETAILS_PG = @"
            SELECT rvd.ROUTINGDETAILID, rvd.ROUTINGVERSIONID, rvd.SLNO, rvd.STAGE, rvd.STEP,
                   rvd.PROCESSID, rvd.WORKCENTERID, rvd.LOCATIONTYPE, rvd.FIXEDLEADTIME,
                   rvd.LEADTIMEUOM, rvd.PREDECESSOR, rvd.REMARKS, rvd.TENANTID,
                   rvd.ISINTERRUPTIBLE, rvd.MINIMUMRUNMINUTES, rvd.MAXWAITMINUTES,
                   rvd.EXECUTIONMODE, rvd.SUBCONTRACTLEADTIMEDAYS, rvd.REWORKROUTINGDETAILID,
                   rvd.BATCHCONTINUITY, rvd.MONITORGROUP, rvd.ISGROUPOUTPUTSTEP,
                   rvd.CONNECTIONTYPE, rvd.ISSHIPMENT, rvd.ISPACKFLAG
            FROM   MROUTINGVERSIONDETAIL rvd
            WHERE  rvd.ROUTINGVERSIONID = :RoutingVersionId AND rvd.TENANTID = :TenantId
            ORDER  BY rvd.SLNO";

        // ── Get List (paged) ──────────────────────────────────────────────────
        // Required index: IX_MROUTING_ITEM (ITEMID, STATUS, TENANTID)

        public const string GET_ROUTING_LIST_SQL = @"
            SELECT r.ROUTINGID, r.ROUTINGCODE, r.ROUTINGNAME, r.ROUTINGTYPE, r.STATUS,
                   r.CREATEDON,
                   ISNULL(i.ITEMCODE, '')    AS ItemCode,
                   ISNULL(i.ITEMNAME, '')    AS ItemName,
                   ISNULL(p.PROCESSNAME, '') AS ProcessName,
                   (SELECT COUNT(*) FROM MROUTINGVERSION rv
                    WHERE rv.ROUTINGID = r.ROUTINGID AND rv.TENANTID = r.TENANTID) AS VersionCount
            FROM   MROUTING r
            LEFT   JOIN MITEM    i ON i.ITEMID    = r.ITEMID
            LEFT   JOIN MPROCESS p ON p.PROCESSID = r.PROCESSID
            WHERE  r.TENANTID = @TenantId AND r.STATUS != 2
            ORDER  BY r.ROUTINGID
            OFFSET @FirstNumber ROWS FETCH NEXT @MaxResult ROWS ONLY";

        public const string GET_ROUTING_LIST_COUNT_SQL = @"
            SELECT COUNT(*) FROM MROUTING r WHERE r.TENANTID = @TenantId AND r.STATUS != 2";

        public const string GET_ROUTING_LIST_PG = @"
            SELECT r.ROUTINGID, r.ROUTINGCODE, r.ROUTINGNAME, r.ROUTINGTYPE, r.STATUS,
                   r.CREATEDON,
                   COALESCE(i.ITEMCODE, '')    AS ""ItemCode"",
                   COALESCE(i.ITEMNAME, '')    AS ""ItemName"",
                   COALESCE(p.PROCESSNAME, '') AS ""ProcessName"",
                   (SELECT COUNT(*) FROM MROUTINGVERSION rv
                    WHERE rv.ROUTINGID = r.ROUTINGID AND rv.TENANTID = r.TENANTID) AS ""VersionCount""
            FROM   MROUTING r
            LEFT   JOIN MITEM    i ON i.ITEMID    = r.ITEMID
            LEFT   JOIN MPROCESS p ON p.PROCESSID = r.PROCESSID
            WHERE  r.TENANTID = :TenantId AND r.STATUS != 2
            ORDER  BY r.ROUTINGID
            LIMIT  :MaxResult OFFSET :FirstNumber";

        public const string GET_ROUTING_LIST_COUNT_PG = @"
            SELECT COUNT(*) FROM MROUTING r WHERE r.TENANTID = :TenantId AND r.STATUS != 2";

        // ── Save / Update Routing Header ──────────────────────────────────────

        public const string SAVE_ROUTING_SQL = @"
            INSERT INTO MROUTING
                (ROUTINGID, ROUTINGCODE, ROUTINGNAME, ITEMID, PROCESSID,
                 ROUTINGTYPE, REMARKS, ISBORPOST, STATUS, TENANTID,
                 CREATEDON, CREATEDBYID)
            VALUES
                (@RoutingId, @RoutingCode, @RoutingName, @ItemId, @ProcessId,
                 @RoutingType, @Remarks, @IsBORPost, @Status, @TenantId,
                 GETUTCDATE(), @UserId)";

        public const string SAVE_ROUTING_PG = @"
            INSERT INTO MROUTING
                (ROUTINGID, ROUTINGCODE, ROUTINGNAME, ITEMID, PROCESSID,
                 ROUTINGTYPE, REMARKS, ISBORPOST, STATUS, TENANTID,
                 CREATEDON, CREATEDBYID)
            VALUES
                (:RoutingId, :RoutingCode, :RoutingName, :ItemId, :ProcessId,
                 :RoutingType, :Remarks, :IsBORPost, :Status, :TenantId,
                 NOW() AT TIME ZONE 'UTC', :UserId)";

        public const string UPDATE_ROUTING_SQL = @"
            UPDATE MROUTING
            SET    ROUTINGCODE  = @RoutingCode,
                   ROUTINGNAME  = @RoutingName,
                   ITEMID       = @ItemId,
                   PROCESSID    = @ProcessId,
                   ROUTINGTYPE  = @RoutingType,
                   REMARKS      = @Remarks,
                   ISBORPOST    = @IsBORPost,
                   STATUS       = @Status,
                   MODIFIEDON   = GETUTCDATE(),
                   MODIFIEDBYID = @UserId
            WHERE  ROUTINGID = @RoutingId AND TENANTID = @TenantId";

        public const string UPDATE_ROUTING_PG = @"
            UPDATE MROUTING
            SET    ROUTINGCODE  = :RoutingCode,
                   ROUTINGNAME  = :RoutingName,
                   ITEMID       = :ItemId,
                   PROCESSID    = :ProcessId,
                   ROUTINGTYPE  = :RoutingType,
                   REMARKS      = :Remarks,
                   ISBORPOST    = :IsBORPost,
                   STATUS       = :Status,
                   MODIFIEDON   = NOW() AT TIME ZONE 'UTC',
                   MODIFIEDBYID = :UserId
            WHERE  ROUTINGID = :RoutingId AND TENANTID = :TenantId";

        // ── Save Routing Version ──────────────────────────────────────────────

        public const string SAVE_ROUTING_VERSION_SQL = @"
            INSERT INTO MROUTINGVERSION
                (ROUTINGVERSIONID, ROUTINGID, VERSION, ACTIVEFROMDATE, ACTIVETODATE,
                 REMARKS, WORKORDERTYPE, QUANTITY, ITEMID, SKUID, ISSKUPOSTING,
                 ISSTAGEORPROCESS, ISLASTPROCESSWIP, TENANTID, CREATEDON, CREATEDBYID)
            VALUES
                (@RoutingVersionId, @RoutingId, @Version, @ActiveFromDate, @ActiveToDate,
                 @Remarks, @WorkOrderType, @Quantity, @ItemId, @SkuId, @IsSKUPosting,
                 @IsStageOrProcess, @IsLastProcessWIP, @TenantId, GETUTCDATE(), @UserId)";

        public const string SAVE_ROUTING_VERSION_PG = @"
            INSERT INTO MROUTINGVERSION
                (ROUTINGVERSIONID, ROUTINGID, VERSION, ACTIVEFROMDATE, ACTIVETODATE,
                 REMARKS, WORKORDERTYPE, QUANTITY, ITEMID, SKUID, ISSKUPOSTING,
                 ISSTAGEORPROCESS, ISLASTPROCESSWIP, TENANTID, CREATEDON, CREATEDBYID)
            VALUES
                (:RoutingVersionId, :RoutingId, :Version, :ActiveFromDate, :ActiveToDate,
                 :Remarks, :WorkOrderType, :Quantity, :ItemId, :SkuId, :IsSKUPosting,
                 :IsStageOrProcess, :IsLastProcessWIP, :TenantId, NOW() AT TIME ZONE 'UTC', :UserId)";

        // ── Save Routing Version Detail (for BulkInsertAsync) ────────────────
        // Required index: IX_MROUTINGVERSIONDETAIL_VERSION (ROUTINGVERSIONID, SLNO, TENANTID)

        public const string SAVE_ROUTING_VERSION_DETAIL_SQL = @"
            INSERT INTO MROUTINGVERSIONDETAIL
                (ROUTINGDETAILID, ROUTINGVERSIONID, SLNO, STAGE, STEP,
                 PROCESSID, WORKCENTERID, LOCATIONTYPE, FIXEDLEADTIME, LEADTIMEUOM,
                 PREDECESSOR, REMARKS, TENANTID,
                 ISINTERRUPTIBLE, MINIMUMRUNMINUTES, MAXWAITMINUTES, EXECUTIONMODE,
                 SUBCONTRACTLEADTIMEDAYS, REWORKROUTINGDETAILID, BATCHCONTINUITY,
                 MONITORGROUP, ISGROUPOUTPUTSTEP,
                 CONNECTIONTYPE, ISSHIPMENT, ISPACKFLAG)
            VALUES
                (@RoutingDetailId, @RoutingVersionId, @SlNo, @Stage, @Step,
                 @ProcessId, @WorkCenterId, @LocationType, @FixedLeadTime, @LeadTimeUOM,
                 @Predecessor, @Remarks, @TenantId,
                 @IsInterruptible, @MinimumRunMinutes, @MaxWaitMinutes, @ExecutionMode,
                 @SubcontractLeadTimeDays, @ReworkRoutingDetailId, @BatchContinuity,
                 @MonitorGroup, @IsGroupOutputStep,
                 @ConnectionType, @IsShipment, @IsPackFlag)";

        public const string SAVE_ROUTING_VERSION_DETAIL_PG = @"
            INSERT INTO MROUTINGVERSIONDETAIL
                (ROUTINGDETAILID, ROUTINGVERSIONID, SLNO, STAGE, STEP,
                 PROCESSID, WORKCENTERID, LOCATIONTYPE, FIXEDLEADTIME, LEADTIMEUOM,
                 PREDECESSOR, REMARKS, TENANTID,
                 ISINTERRUPTIBLE, MINIMUMRUNMINUTES, MAXWAITMINUTES, EXECUTIONMODE,
                 SUBCONTRACTLEADTIMEDAYS, REWORKROUTINGDETAILID, BATCHCONTINUITY,
                 MONITORGROUP, ISGROUPOUTPUTSTEP,
                 CONNECTIONTYPE, ISSHIPMENT, ISPACKFLAG)
            VALUES
                (:RoutingDetailId, :RoutingVersionId, :SlNo, :Stage, :Step,
                 :ProcessId, :WorkCenterId, :LocationType, :FixedLeadTime, :LeadTimeUOM,
                 :Predecessor, :Remarks, :TenantId,
                 :IsInterruptible, :MinimumRunMinutes, :MaxWaitMinutes, :ExecutionMode,
                 :SubcontractLeadTimeDays, :ReworkRoutingDetailId, :BatchContinuity,
                 :MonitorGroup, :IsGroupOutputStep,
                 :ConnectionType, :IsShipment, :IsPackFlag)";

        // ── Delete / Soft Delete ──────────────────────────────────────────────

        public const string DELETE_ROUTING_VERSION_DETAILS_SQL = @"
            DELETE FROM MROUTINGVERSIONDETAIL
            WHERE  ROUTINGVERSIONID = @RoutingVersionId AND TENANTID = @TenantId";

        public const string DELETE_ROUTING_VERSION_DETAILS_PG = @"
            DELETE FROM MROUTINGVERSIONDETAIL
            WHERE  ROUTINGVERSIONID = :RoutingVersionId AND TENANTID = :TenantId";

        public const string SOFT_DELETE_ROUTING_SQL = @"
            UPDATE MROUTING
            SET    STATUS = 2, MODIFIEDON = GETUTCDATE(), MODIFIEDBYID = @UserId
            WHERE  ROUTINGID = @RoutingId AND TENANTID = @TenantId";

        public const string SOFT_DELETE_ROUTING_PG = @"
            UPDATE MROUTING
            SET    STATUS = 2, MODIFIEDON = NOW() AT TIME ZONE 'UTC', MODIFIEDBYID = :UserId
            WHERE  ROUTINGID = :RoutingId AND TENANTID = :TenantId";
    }
}