namespace WiDAL.Query.Wi
{
    // Combines three availability signals for a workstation:
    //   - TCALL (CRM)  — breakdown calls, joined via MWORKSTATION.MACHINEID
    //   - TTASK (CRM)  — TASKNATURE=2 planned maintenance/downtime, same join
    //   - TWORKSTATIONOUTAGE (WI) — direct capture, used when MACHINEID is unlinked
    //     (-1) or the outage simply isn't tracked in Maintenance/CRM for this tenant.
    // Mirrors MMDAL/Query/Scheduling/CalendarQB.cs's TCALL/TTASK filter logic exactly —
    // same DB, no cross-service call, despite WI having zero ProjectReference to MM/CRM.
    // Indexes required:
    //   MWORKSTATION: (MACHINEID) — new column, low cardinality, no dedicated index needed
    //     beyond the existing PK scan for the handful of rows per workstation lookup
    //   TWORKSTATIONOUTAGE: IX_TWORKSTATIONOUTAGE_WORKSTATIONID_ENDEDON (added in this migration)
    public static class WorkstationAvailabilityQB
    {
        public const string GET_AVAILABILITY = @"
DECLARE @MachineId INT = (SELECT MACHINEID FROM DBO.MWORKSTATION WHERE WORKSTATIONID = @WorkstationId);

-- Breakdown calls (TCALL) — open (CALLSTATUS <> 4) and started before the window ends
SELECT
    0                       AS Source,
    0                       AS OutageType,
    C.CALLDATE              AS StartedOn,
    CASE WHEN C.CALLSTATUS = 4 THEN C.CLOSEDON ELSE NULL END AS EndedOn,
    NULL                    AS Remarks
FROM  TCALL C
WHERE @MachineId <> -1
AND   C.CALLONOBJECTID     = @MachineId
AND   C.CALLONOBJECTTYPEID = -1899996694
AND   C.CALLSTATUS        <> 4
AND   C.CALLDATE           < @RangeTo
AND   C.TENANTID           = @TenantId

UNION ALL

-- Planned maintenance/downtime (TTASK TASKNATURE=2), open window overlapping the range
SELECT
    1                       AS Source,
    1                       AS OutageType,
    T.PLANSTARTON           AS StartedOn,
    T.PLANENDON             AS EndedOn,
    NULL                    AS Remarks
FROM  TTASK T
WHERE @MachineId <> -1
AND   T.MACHINEID   = @MachineId
AND   T.TASKNATURE  = 2
AND   T.STATUS     <> 3
AND   T.PLANSTARTON < @RangeTo
AND   T.PLANENDON   > @RangeFrom
AND   T.TENANTID    = @TenantId

UNION ALL

-- Direct capture (this workstation, no Maintenance/CRM link required)
SELECT
    2                       AS Source,
    O.OUTAGETYPE            AS OutageType,
    O.STARTEDON             AS StartedOn,
    O.ENDEDON               AS EndedOn,
    O.REMARKS               AS Remarks
FROM  DBO.TWORKSTATIONOUTAGE O
WHERE O.WORKSTATIONID = @WorkstationId
AND   O.STARTEDON     < @RangeTo
AND   (O.ENDEDON IS NULL OR O.ENDEDON > @RangeFrom)
AND   O.TENANTID      = @TenantId
ORDER BY StartedOn;";

        public const string INSERT_OUTAGE = @"
INSERT INTO DBO.TWORKSTATIONOUTAGE
    (WORKSTATIONOUTAGEID, WORKSTATIONID, OUTAGETYPE, STARTEDON, ENDEDON, REMARKS,
     CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID)
VALUES
    (@WorkstationOutageId, @WorkstationId, @OutageType, @StartedOn, NULL, @Remarks,
     @UserId, GETDATE(), @UserId, GETDATE(), @TenantId);";

        public const string CLOSE_OUTAGE = @"
UPDATE DBO.TWORKSTATIONOUTAGE
SET    ENDEDON      = @EndedOn,
       MODIFIEDBYID = @UserId,
       MODIFIEDON   = GETDATE()
WHERE  WORKSTATIONOUTAGEID = @WorkstationOutageId
AND    TENANTID             = @TenantId
AND    ENDEDON IS NULL;";
    }
}