namespace MMDAL.Query.Scheduling
{
    // Indexes required:
    //   MWORKCENTERSHIFTMAP: (WORKCENTERID, DAYOFWEEK, TENANTID)
    //   MWORKCENTERSHIFTMAP: (WORKCENTERID, EFFECTIVEFROM, EFFECTIVETO, TENANTID)
    public static class WorkCenterShiftMapQB
    {
        // ── Get single shift map record ──────────────────────────────────────

        public const string GET_SHIFTMAP = @"
SELECT
    SM.SHIFTMAPID          AS ShiftMapId,
    SM.WORKCENTERID        AS WorkCenterId,
    WC.WORKCENTERCODE      AS WorkCenterCode,
    WC.WORKCENTERNAME      AS WorkCenterName,
    SM.SHIFTID             AS ShiftId,
    SH.SHIFTCODE           AS ShiftCode,
    SH.SHIFTDESCRIPTION    AS ShiftDescription,
    SM.DAYOFWEEK           AS DayOfWeek,
    SM.EFFECTIVEFROM       AS EffectiveFrom,
    SM.EFFECTIVETO         AS EffectiveTo,
    SH.STARTTIME           AS ShiftStartTime,
    SH.ENDTIME             AS ShiftEndTime,
    SH.WORKINGMINUTES      AS ShiftWorkingMinutes,
    SH.FIRSTHALFENDTIME    AS BreakStartTime,
    SH.SECONDHALFSTARTTIME AS BreakEndTime,
    SM.STATUS              AS Status,
    SM.TENANTID            AS TenantId,
    SM.CREATEDBYID         AS CreatedById,
    SM.CREATEDON           AS CreatedOn,
    SM.MODIFIEDBYID        AS ModifiedById,
    SM.MODIFIEDON          AS ModifiedOn
FROM  MWORKCENTERSHIFTMAP SM
INNER JOIN MWORKCENTER WC ON WC.WORKCENTERID = SM.WORKCENTERID
INNER JOIN MSHIFT      SH ON SH.SHIFTID      = SM.SHIFTID
WHERE SM.SHIFTMAPID = @ShiftMapId
AND   SM.TENANTID   = @TenantId;";

        // ── Get all active shift maps for a workcenter (for management UI) ──

        public const string GET_SHIFTMAP_LIST = @"
SELECT
    SM.SHIFTMAPID          AS ShiftMapId,
    SM.WORKCENTERID        AS WorkCenterId,
    WC.WORKCENTERCODE      AS WorkCenterCode,
    WC.WORKCENTERNAME      AS WorkCenterName,
    SM.SHIFTID             AS ShiftId,
    SH.SHIFTCODE           AS ShiftCode,
    SH.SHIFTDESCRIPTION    AS ShiftDescription,
    SM.DAYOFWEEK           AS DayOfWeek,
    SM.EFFECTIVEFROM       AS EffectiveFrom,
    SM.EFFECTIVETO         AS EffectiveTo,
    SH.STARTTIME           AS ShiftStartTime,
    SH.ENDTIME             AS ShiftEndTime,
    SH.WORKINGMINUTES      AS ShiftWorkingMinutes,
    SH.FIRSTHALFENDTIME    AS BreakStartTime,
    SH.SECONDHALFSTARTTIME AS BreakEndTime,
    SM.STATUS              AS Status
FROM  MWORKCENTERSHIFTMAP SM
INNER JOIN MWORKCENTER WC ON WC.WORKCENTERID = SM.WORKCENTERID
INNER JOIN MSHIFT      SH ON SH.SHIFTID      = SM.SHIFTID
WHERE SM.WORKCENTERID = @WorkCenterId
AND   SM.STATUS       = 1
AND   SM.TENANTID     = @TenantId
ORDER BY SM.DAYOFWEEK, SM.EFFECTIVEFROM;";

        // ── Get effective shifts for a workcenter on a given date ────────────
        // Used by CalendarService during scheduling runs.
        // Matches by DAYOFWEEK and EFFECTIVEFROM <= @ScheduleDate < EFFECTIVETO (or EFFECTIVETO IS NULL).

        public const string GET_SHIFTMAP_FOR_DATE = @"
SELECT
    SM.SHIFTMAPID          AS ShiftMapId,
    SM.WORKCENTERID        AS WorkCenterId,
    SM.SHIFTID             AS ShiftId,
    SH.SHIFTCODE           AS ShiftCode,
    SM.DAYOFWEEK           AS DayOfWeek,
    SM.EFFECTIVEFROM       AS EffectiveFrom,
    SM.EFFECTIVETO         AS EffectiveTo,
    SH.STARTTIME           AS ShiftStartTime,
    SH.ENDTIME             AS ShiftEndTime,
    SH.WORKINGMINUTES      AS ShiftWorkingMinutes,
    SH.FIRSTHALFENDTIME    AS BreakStartTime,
    SH.SECONDHALFSTARTTIME AS BreakEndTime,
    SM.STATUS              AS Status,
    SM.TENANTID            AS TenantId
FROM  MWORKCENTERSHIFTMAP SM
INNER JOIN MSHIFT SH ON SH.SHIFTID = SM.SHIFTID
WHERE SM.WORKCENTERID = @WorkCenterId
AND   SM.DAYOFWEEK    = @DayOfWeek
AND   SM.STATUS       = 1
AND   SM.EFFECTIVEFROM <= @ScheduleDate
AND   (SM.EFFECTIVETO IS NULL OR SM.EFFECTIVETO > @ScheduleDate)
AND   SM.TENANTID     = @TenantId
ORDER BY SM.EFFECTIVEFROM DESC;";

        // ── Batch-load all effective shift patterns for a set of work centers ───
        // Called once at scheduling run start to pre-populate the calendar cache.
        // Returns all patterns whose effective range overlaps the planning horizon.
        // Ordered DESC by EFFECTIVEFROM so the latest pattern is first for each (WC, DOW).
        public const string GET_SHIFTMAPS_FOR_HORIZON = @"
SELECT
    SM.WORKCENTERID             AS WorkCenterId,
    SM.DAYOFWEEK                AS DayOfWeek,
    SH.STARTTIME                AS ShiftStartTime,
    SH.ENDTIME                  AS ShiftEndTime,
    SH.FIRSTHALFENDTIME         AS BreakStartTime,
    SH.SECONDHALFSTARTTIME      AS BreakEndTime,
    SM.EFFECTIVEFROM            AS EffectiveFrom,
    SM.EFFECTIVETO              AS EffectiveTo
FROM   MWORKCENTERSHIFTMAP SM
INNER JOIN MSHIFT SH ON SH.SHIFTID = SM.SHIFTID
WHERE  SM.WORKCENTERID IN @WorkCenterIds
AND    SM.STATUS        = 1
AND    SM.EFFECTIVEFROM <= @HorizonTo
AND    (SM.EFFECTIVETO IS NULL OR SM.EFFECTIVETO > @HorizonFrom)
AND    SM.TENANTID      = @TenantId
ORDER BY SM.WORKCENTERID, SM.DAYOFWEEK, SM.EFFECTIVEFROM DESC;";

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

        public const string SAVE_SHIFTMAP = @"
INSERT INTO MWORKCENTERSHIFTMAP
(
    SHIFTMAPID, WORKCENTERID, SHIFTID, DAYOFWEEK,
    EFFECTIVEFROM, EFFECTIVETO, STATUS,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID
)
VALUES
(
    @ShiftMapId, @WorkCenterId, @ShiftId, @DayOfWeek,
    @EffectiveFrom, @EffectiveTo, @Status,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @TenantId
);";

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

        public const string UPDATE_SHIFTMAP = @"
UPDATE MWORKCENTERSHIFTMAP
SET
    WORKCENTERID  = @WorkCenterId,
    SHIFTID       = @ShiftId,
    DAYOFWEEK     = @DayOfWeek,
    EFFECTIVEFROM = @EffectiveFrom,
    EFFECTIVETO   = @EffectiveTo,
    STATUS        = @Status,
    MODIFIEDBYID  = @ModifiedById,
    MODIFIEDON    = @ModifiedOn
WHERE SHIFTMAPID = @ShiftMapId
AND   TENANTID   = @TenantId;";

        // ── Delete (hard delete — shift maps are configuration, not transactional) ──

        public const string DELETE_SHIFTMAP = @"
DELETE FROM MWORKCENTERSHIFTMAP
WHERE SHIFTMAPID = @ShiftMapId
AND   TENANTID   = @TenantId;";
    }
}