namespace MMDAL.Query.Indent
{
    public static class SupervisorWorkbenchQB
    {
        // ── Supervisor WO List (paged) ────────────────────────────────────────
        // Required index: IX_TINDENTDETAIL_RELEASESTATUS (OUID, RELEASESTATUS, TENANTID)

        public const string GET_SUPERVISOR_WO_LIST_SQL = @"
            SELECT id.INDENTDETAILID, id.INDENTID,
                   h.INDENTNUMBER, h.OUID,
                   ISNULL(i.ITEMCODE, '')       AS ItemCode,
                   ISNULL(i.ITEMNAME, '')       AS ItemName,
                   id.INDENTDETAILQUANTITY      AS PlannedQuantity,
                   id.PERQUANTITY,
                   id.RELEASESTATUS,
                   ISNULL(rv.TOTALSTAGES, 0)    AS TotalStages,
                   0                            AS CompletedStages,
                   CASE id.RELEASESTATUS
                       WHEN 0 THEN 'Planned'
                       WHEN 1 THEN 'Released'
                       WHEN 2 THEN 'InProgress'
                       WHEN 3 THEN 'PendingResource'
                       WHEN 4 THEN 'PendingRelease'
                       WHEN 5 THEN 'Complete'
                       WHEN 6 THEN 'Closed'
                       WHEN 7 THEN 'ShortClosed'
                       WHEN 8 THEN 'Cancelled'
                       ELSE 'Unknown'
                   END                          AS Priority
            FROM   TINDENTDETAIL id
            JOIN   TINDENT       h  ON h.INDENTID       = id.INDENTID
            LEFT   JOIN MITEM    i  ON i.ITEMID          = id.ITEMID
            OUTER APPLY (
                SELECT COUNT(*) AS TOTALSTAGES
                FROM   MROUTINGVERSIONDETAIL rvd
                JOIN   MROUTINGVERSION       rv2 ON rv2.ROUTINGVERSIONID = rvd.ROUTINGVERSIONID
                JOIN   MROUTING              r   ON r.ROUTINGID = rv2.ROUTINGID
                WHERE  r.ITEMID = id.ITEMID
            ) rv
            WHERE  h.OUID       = @OuId
            AND    (@StatusFilter = -1 OR id.RELEASESTATUS = @StatusFilter)
            ORDER  BY id.INDENTDETAILID
            OFFSET @FirstNumber ROWS FETCH NEXT @MaxResult ROWS ONLY";

        public const string GET_SUPERVISOR_WO_LIST_COUNT_SQL = @"
            SELECT COUNT(*)
            FROM   TINDENTDETAIL id
            JOIN   TINDENT       h ON h.INDENTID = id.INDENTID
            WHERE  h.OUID      = @OuId
            AND    (@StatusFilter = -1 OR id.RELEASESTATUS = @StatusFilter)";

        public const string GET_SUPERVISOR_WO_LIST_PG = @"
            SELECT id.INDENTDETAILID, id.INDENTID,
                   h.INDENTNUMBER, h.OUID,
                   COALESCE(i.ITEMCODE, '')        AS ""ItemCode"",
                   COALESCE(i.ITEMNAME, '')        AS ""ItemName"",
                   id.INDENTDETAILQUANTITY         AS ""PlannedQuantity"",
                   id.RELEASEDQUANTITY             AS ""ReleasedQuantity"",
                   id.RELEASESTATUS                AS ""ReleaseStatus"",
                   id.PLANNEDSTARTDATE             AS ""PlannedStartDate"",
                   id.PLANNEDENDDATE               AS ""PlannedEndDate"",
                   COALESCE(wc.WORKCENTERID, -1)   AS ""WorkCenterId"",
                   COALESCE(wc.WORKCENTERNAME, '') AS ""WorkCenterName"",
                   0                              AS ""TotalStages"",
                   0                              AS ""CompletedStages"",
                   CASE id.RELEASESTATUS
                       WHEN 0 THEN 'Planned'
                       WHEN 1 THEN 'Released'
                       WHEN 2 THEN 'InProgress'
                       WHEN 3 THEN 'PendingResource'
                       WHEN 4 THEN 'PendingRelease'
                       WHEN 5 THEN 'Complete'
                       WHEN 6 THEN 'Closed'
                       WHEN 7 THEN 'ShortClosed'
                       WHEN 8 THEN 'Cancelled'
                       ELSE 'Unknown'
                   END                            AS ""Priority""
            FROM   TINDENTDETAIL id
            JOIN   TINDENT       h  ON h.INDENTID  = id.INDENTID AND h.TENANTID = id.TENANTID
            LEFT   JOIN MITEM    i  ON i.ITEMID     = id.ITEMID
            LEFT   JOIN MWORKCENTER wc ON wc.WORKCENTERID = id.WORKCENTERID
            WHERE  h.OUID      = :OuId
            AND    id.TENANTID = :TenantId
            AND    (:StatusFilter = -1 OR id.RELEASESTATUS = :StatusFilter)
            ORDER  BY id.PLANNEDSTARTDATE, id.INDENTDETAILID
            LIMIT  :MaxResult OFFSET :FirstNumber";

        public const string GET_SUPERVISOR_WO_LIST_COUNT_PG = @"
            SELECT COUNT(*)
            FROM   TINDENTDETAIL id
            JOIN   TINDENT       h ON h.INDENTID = id.INDENTID AND h.TENANTID = id.TENANTID
            WHERE  h.OUID      = :OuId
            AND    id.TENANTID = :TenantId
            AND    (:StatusFilter = -1 OR id.RELEASESTATUS = :StatusFilter)";

        // ── Assign Resource (delete + bulk re-insert pattern) ─────────────────

        public const string DELETE_INDENT_RESOURCES_SQL = @"
            DELETE FROM TINDENTRESOURCE
            WHERE  INDENTDETAILID = @IndentDetailId;";

        public const string DELETE_INDENT_RESOURCES_PG = @"
            DELETE FROM TINDENTRESOURCE
            WHERE  INDENTDETAILID = :IndentDetailId;";

        public const string SAVE_INDENT_RESOURCE_SQL = @"INSERT INTO TINDENTRESOURCE
                                                        (
                                                            INDENTRESOURCEID,
                                                            INDENTDETAILID,
                                                            SLNO,
                                                            BILLOFRESOURCEID,
                                                            RESOURCETYPEID,
                                                            MACHINEID,
                                                            EMPLOYEEID,
                                                            PLANDURATION,
                                                            PLANSTARTDATE,
                                                            PLANSTARTTIME,
                                                            PLANENDDATE,
                                                            PLANENDTIME,
                                                            RESOURCEID,
                                                            CALENDARDETAILID,
                                                            PLANNEDQUANTITY,
                                                            PERQUANTITY,
                                                            TIMEUOM,
                                                            REMARKS
                                                        )
                                                        VALUES
                                                        (
                                                            @IndentResourceId,
                                                            @IndentDetailId,
                                                            @SlNo,
                                                            @BillOfResourceId,
                                                            @ResourceTypeId,
                                                            @MachineId,
                                                            @EmployeeId,
                                                            @PlanDuration,
                                                            @PlanStartDate,
                                                            @PlanStartTime,
                                                            @PlanEndDate,
                                                            @PlanEndTime,
                                                            @ResourceId,
                                                            @CalendarDetailId,
                                                            @PlannedQuantity,
                                                            @PerQuantity,
                                                            @TimeUOM,
                                                            @Remarks
                                                        );";

        public const string SAVE_INDENT_RESOURCE_PG = @"
            INSERT INTO TINDENTRESOURCE
                (INDENTRESOURCEID, INDENTDETAILID, WORKCENTERID, MACHINEID, MACHINETYPEID, DURATION, TENANTID)
            VALUES
                (:IndentResourceId, :IndentDetailId, :WorkCenterId, :MachineId, :MachineTypeId, :Duration, :TenantId)";

        // ── Release Work Order ────────────────────────────────────────────────

        public const string RELEASE_WORK_ORDER_SQL = @"
            UPDATE TINDENTDETAIL
            SET    RELEASESTATUS = 1
            WHERE  INDENTDETAILID = @IndentDetailId";

        public const string RELEASE_WORK_ORDER_PG = @"
            UPDATE TINDENTDETAIL
            SET    RELEASESTATUS = 1
            WHERE  INDENTDETAILID = :IndentDetailId";
    }
}