namespace WiDAL.Query.Wi;

public static class LineRosterQB
{
    // Index: IX_TLINESHIFTROSTER_LINE_DATE_SHIFT
    //        on (PRODUCTIONLINEID, ROSTERDATE, SHIFTID, TENANTID)
    //        INCLUDE (ROSTERTYPE, ROSTERSTATUS, INCHARGEEMPLOYEEID, LINESHIFTROSTERID)
    public const string GET_ROSTER_HEADER = @"
        SELECT  LINESHIFTROSTERID      AS LineShiftRosterId,
                PRODUCTIONLINEID      AS ProductionLineId,
                SHIFTID               AS ShiftId,
                ROSTERDATE            AS RosterDate,
                ROSTERTYPE            AS RosterType,
                ROSTERSTATUS          AS RosterStatus,
                INCHARGEEMPLOYEEID    AS InChargeEmployeeId,
                PLANNEDROSTERID       AS PlannedRosterId,
                REMARKS               AS Remarks
        FROM    DBO.TLINESHIFTROSTER
        WHERE   PRODUCTIONLINEID = @ProductionLineId
          AND   ROSTERDATE       = @RosterDate
          AND   SHIFTID          = @ShiftId
          AND   ROSTERTYPE       = @RosterType
          AND   TENANTID         = @TenantId";

    // Used by LineRosterBLL.ConfirmRosterAsync to fetch the exact roster row being
    // transitioned (GET_ROSTER_HEADER looks up by (line,date,shift,type) — no ID-based lookup existed).
    public const string GET_ROSTER_BY_ID = @"
        SELECT  LINESHIFTROSTERID      AS LineShiftRosterId,
                PRODUCTIONLINEID      AS ProductionLineId,
                SHIFTID               AS ShiftId,
                ROSTERDATE            AS RosterDate,
                ROSTERTYPE            AS RosterType,
                ROSTERSTATUS          AS RosterStatus,
                INCHARGEEMPLOYEEID    AS InChargeEmployeeId,
                PLANNEDROSTERID       AS PlannedRosterId,
                REMARKS               AS Remarks
        FROM    DBO.TLINESHIFTROSTER
        WHERE   LINESHIFTROSTERID = @LineShiftRosterId
          AND   TENANTID          = @TenantId";

    public const string INSERT_ROSTER = @"
        INSERT INTO DBO.TLINESHIFTROSTER
            (LINESHIFTROSTERID, PRODUCTIONLINEID, SHIFTID, ROSTERDATE,
             ROSTERTYPE, ROSTERSTATUS, INCHARGEEMPLOYEEID, PLANNEDROSTERID,
             REMARKS, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID)
        VALUES
            (@LineShiftRosterId, @ProductionLineId, @ShiftId, @RosterDate,
             @RosterType, @RosterStatus, @InChargeEmployeeId, @PlannedRosterId,
             @Remarks, @UserId, GETDATE(), @UserId, GETDATE(), @TenantId)";

    public const string UPDATE_ROSTER_STATUS = @"
        UPDATE DBO.TLINESHIFTROSTER
        SET    ROSTERSTATUS        = @RosterStatus,
               INCHARGEEMPLOYEEID = @InChargeEmployeeId,
               MODIFIEDBYID       = @UserId,
               MODIFIEDON         = GETDATE()
        WHERE  LINESHIFTROSTERID = @LineShiftRosterId
          AND  TENANTID          = @TenantId";

    // Index: IX_TLINESHIFTSTAFFING_LINESHIFTROSTERID on (LINESHIFTROSTERID, TENANTID)
    //        INCLUDE (WORKSTATIONID, EMPLOYEEID, STAFFINGROLE, ISPRIMARY, ISABSENT)
    public const string GET_STAFFING_BY_ROSTER = @"
        SELECT  LINESHIFTSTAFFINGID   AS LineShiftStaffingId,
                LINESHIFTROSTERID      AS LineShiftRosterId,
                WORKSTATIONID         AS WorkstationId,
                EMPLOYEEID            AS EmployeeId,
                STAFFINGROLE          AS StaffingRole,
                CAST(ISPRIMARY  AS BIT) AS IsPrimary,
                PLANNEDSTARTTIME      AS PlannedStartTime,
                PLANNEDENDTIME        AS PlannedEndTime,
                CAST(ISABSENT   AS BIT) AS IsAbsent,
                ABSENCEREASON         AS AbsenceReason,
                REPLACEDBYEMPLOYEEID  AS ReplacedByEmployeeId,
                REMARKS               AS Remarks
        FROM    DBO.TLINESHIFTSTAFFING
        WHERE   LINESHIFTROSTERID = @LineShiftRosterId
          AND   TENANTID          = @TenantId
        ORDER BY WORKSTATIONID, ISPRIMARY DESC";

    // IDENTITY PK — never supply LINESHIFTSTAFFINGID
    public const string INSERT_STAFFING = @"
        INSERT INTO DBO.TLINESHIFTSTAFFING
            (LINESHIFTROSTERID, WORKSTATIONID, EMPLOYEEID,
             STAFFINGROLE, ISPRIMARY, PLANNEDSTARTTIME, PLANNEDENDTIME,
             ISABSENT, ABSENCEREASON, REPLACEDBYEMPLOYEEID, REMARKS,
             CREATEDBYID, CREATEDON, TENANTID)
        VALUES
            (@LineShiftRosterId, @WorkstationId, @EmployeeId,
             @StaffingRole, @IsPrimary, @PlannedStartTime, @PlannedEndTime,
             @IsAbsent, @AbsenceReason, @ReplacedByEmployeeId, @Remarks,
             @UserId, GETDATE(), @TenantId)";

    // Never DELETE — mark ISABSENT=1 to preserve attendance record
    public const string MARK_ABSENT = @"
        UPDATE DBO.TLINESHIFTSTAFFING
        SET    ISABSENT              = 1,
               ABSENCEREASON         = @AbsenceReason,
               REPLACEDBYEMPLOYEEID  = @ReplacedByEmployeeId
        WHERE  LINESHIFTSTAFFINGID = @LineShiftStaffingId
          AND  TENANTID            = @TenantId";

    // Push notification support — MarkAbsentAsync only carries staffingId, not roster context.
    public const string GET_ROSTERID_FOR_STAFFING = @"
        SELECT LINESHIFTROSTERID
        FROM   DBO.TLINESHIFTSTAFFING
        WHERE  LINESHIFTSTAFFINGID = @LineShiftStaffingId
          AND  TENANTID            = @TenantId";

    // Q-ROSTER-1: Planned vs Actual staffing comparison for a line+date+shift
    // Index: IX_TLINESHIFTROSTER_LINE_DATE_SHIFT
    public const string GET_PLANNED_VS_ACTUAL = @"
    SELECT
        -- Roster Details
        r.LINESHIFTROSTERID                  AS LineShiftRosterId,
        r.PRODUCTIONLINEID                   AS ProductionLineId,
        r.SHIFTID                            AS ShiftId,
        r.ROSTERDATE                         AS RosterDate,
        r.ROSTERTYPE                         AS RosterType,
        r.ROSTERSTATUS                       AS RosterStatus,
        r.INCHARGEEMPLOYEEID                 AS InChargeId,
        e_ic.EMPLOYEENAME                    AS InChargeName,
        e_ic.EMPLOYEECODE                    AS InChargeCode,
        r.PLANNEDROSTERID                    AS PlannedRosterId,
        r.REMARKS                            AS RosterRemarks,

        -- Staffing Details
        stf.WORKSTATIONID                    AS WorkstationId,
        ws.WORKSTATIONCODE                   AS WorkstationCode,
        ws.WORKSTATIONNAME                   AS WorkstationName,

        stf.EMPLOYEEID                       AS EmployeeId,
        e.EMPLOYEENAME                       AS EmployeeName,
        e.EMPLOYEECODE                       AS EmployeeCode,

        stf.STAFFINGROLE                     AS StaffingRole,
        CAST(stf.ISPRIMARY AS BIT)           AS IsPrimary,

        stf.PLANNEDSTARTTIME                 AS PlannedStartTime,
        stf.PLANNEDENDTIME                   AS PlannedEndTime,

        CAST(stf.ISABSENT AS BIT)            AS IsAbsent,
        stf.ABSENCEREASON                    AS AbsenceReason,

        stf.REPLACEDBYEMPLOYEEID             AS ReplacedByEmployeeId,
        e_rep.EMPLOYEENAME                   AS ReplacementName,
        e_rep.EMPLOYEECODE                   AS ReplacementCode,

        stf.REMARKS                          AS StaffingRemarks

    FROM DBO.TLINESHIFTROSTER r

    LEFT JOIN DBO.TLINESHIFTSTAFFING stf
        ON stf.LINESHIFTROSTERID = r.LINESHIFTROSTERID

    LEFT JOIN DBO.MEMPLOYEE e_ic
        ON e_ic.EMPLOYEEID = r.INCHARGEEMPLOYEEID
        AND r.INCHARGEEMPLOYEEID <> -1

    LEFT JOIN DBO.MWORKSTATION ws
        ON ws.WORKSTATIONID = stf.WORKSTATIONID

    LEFT JOIN DBO.MEMPLOYEE e
        ON e.EMPLOYEEID = stf.EMPLOYEEID

    LEFT JOIN DBO.MEMPLOYEE e_rep
        ON e_rep.EMPLOYEEID = stf.REPLACEDBYEMPLOYEEID

    WHERE r.PRODUCTIONLINEID = @ProductionLineId
      AND r.ROSTERDATE = @RosterDate
      AND r.SHIFTID = @ShiftId
      AND r.INCHARGEEMPLOYEEID = @PlannedInChargeEmployeeId
      AND r.ROSTERTYPE IN (0, 1)

    ORDER BY
        r.ROSTERTYPE,
        ws.WORKSTATIONCODE,
        stf.ISPRIMARY DESC,
        e.EMPLOYEENAME";

    // Q-ROSTER-2: Copy-forward — get latest same-shift staffing for a line
    // Used by LineRosterBLL.CopyForwardAsync
    public const string GET_COPY_FORWARD_SOURCE = @"
        SELECT TOP 1
            r.LINESHIFTROSTERID    AS LineShiftRosterId,
            r.ROSTERDATE          AS RosterDate,
            r.INCHARGEEMPLOYEEID  AS InChargeEmployeeId
        FROM   DBO.TLINESHIFTROSTER r
        WHERE  r.PRODUCTIONLINEID = @ProductionLineId
          AND  r.SHIFTID          = @ShiftId
          AND  r.ROSTERTYPE       = @RosterType
          AND  r.ROSTERDATE       < @NewRosterDate
          AND  r.TENANTID         = @TenantId
        ORDER BY r.ROSTERDATE DESC";
}