namespace WiDAL.Query.Wi;

public static class WorkstationQB
{
    // Index: IX_MWORKSTATION_WORKCENTERID_TENANTID on (WORKCENTERID, TENANTID)
    //        INCLUDE (WORKSTATIONCODE, WORKSTATIONNAME, WORKSTATIONTYPE, ISACTIVE)
    public const string GET_LIST = @"
        SELECT  w.WORKSTATIONID        AS WorkstationId,
                w.WORKSTATIONCODE      AS WorkstationCode,
                w.WORKSTATIONNAME      AS WorkstationName,
                w.WORKSTATIONTYPE      AS WorkstationType,
                w.WORKCENTERID         AS WorkCenterId,
                wc.WORKCENTERNAME      AS WorkCenterName
        FROM    DBO.MWORKSTATION w
        LEFT JOIN DBO.MWORKCENTER wc ON wc.WORKCENTERID = w.WORKCENTERID
                                    AND wc.WORKCENTERID <> -1
        WHERE   w.TENANTID = @TenantId
          AND   (@WorkCenterId = -1 OR w.WORKCENTERID = @WorkCenterId)
          AND   (@ActiveOnly = 0 OR w.STATUS = 1)
        ORDER BY w.WORKSTATIONCODE";

    public const string GET_BY_ID = @"SELECT
                                        ws.WORKSTATIONID        AS WorkstationId,
                                        ws.WORKCENTERID         AS WorkCenterId,
                                        wc.WORKCENTERCODE       AS WorkCenterCode,
                                        wc.WORKCENTERNAME       AS WorkCenterName,
                                        ws.WORKSTATIONCODE      AS WorkstationCode,
                                        ws.WORKSTATIONNAME      AS WorkstationName,
                                        ws.WORKSTATIONTYPE      AS WorkstationType,
                                        ws.BAYCODE              AS BayCode,
                                        ws.FLOORDESCRIPTION     AS FloorDescription,
                                        ws.IPADDRESS            AS IpAddress,
                                        ws.MACADDRESS           AS MacAddress,
                                        ws.MAXOPERATORS         AS MaxOperators,
                                        ws.REMARKS              AS Remarks
                                    FROM DBO.MWORKSTATION ws
                                    LEFT JOIN DBO.MWORKCENTER wc
                                        ON ws.WORKCENTERID = wc.WORKCENTERID
                                    WHERE ws.WORKSTATIONID = @WorkstationId;";

    public const string INSERT = @"
        INSERT INTO DBO.MWORKSTATION
            (WORKSTATIONID, WORKCENTERID, WORKSTATIONCODE, WORKSTATIONNAME,
             WORKSTATIONTYPE, BAYCODE, FLOORDESCRIPTION, IPADDRESS, MACADDRESS,
             MAXOPERATORS, REMARKS,
             CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID)
        VALUES
            (@WorkstationId, @WorkCenterId, @WorkstationCode, @WorkstationName,
             @WorkstationType, @BayCode, @FloorDescription, @IpAddress, @MacAddress,
             @MaxOperators, @Remarks, 
             @UserId, GETDATE(), @UserId, GETDATE(), @TenantId)";

    public const string UPDATE = @"
        UPDATE DBO.MWORKSTATION
        SET    WORKCENTERID      = @WorkCenterId,
               WORKSTATIONNAME   = @WorkstationName,
               WORKSTATIONTYPE   = @WorkstationType,
               BAYCODE           = @BayCode,
               FLOORDESCRIPTION  = @FloorDescription,
               IPADDRESS         = @IpAddress,
               MACADDRESS        = @MacAddress,
               MAXOPERATORS      = @MaxOperators,
               REMARKS           = @Remarks,
               MODIFIEDBYID      = @UserId,
               MODIFIEDON        = GETDATE()
        WHERE  WORKSTATIONID = @WorkstationId
          AND  TENANTID      = @TenantId";

    public const string GET_SELECT_LIST = @"WITH PagedData AS
                                (
                                    SELECT
                                        ws.WORKSTATIONID      AS WorkstationId,
                                        ws.WORKSTATIONCODE    AS WorkstationCode,
                                        ws.WORKSTATIONNAME    AS WorkstationName,
                                        ws.WORKSTATIONTYPE    AS WorkstationType,
                                        ws.WORKCENTERID       AS WorkCenterId,
                                        wc.WORKCENTERNAME     AS WorkCenterName,
                                        ws.BAYCODE            AS BayCode,
                                        ws.FLOORDESCRIPTION   AS FloorDescription,
                                        ws.IPADDRESS          AS IpAddress,
                                        ws.MACADDRESS         AS MacAddress,
                                        ws.MAXOPERATORS       AS MaxOperators,
                                        ws.VERSION,
                                        ws.STATUS,
                                        ws.SORTORDER,
                                        ws.TENANTID,
                                        ROW_NUMBER() OVER
                                        (
                                            ORDER BY ws.WORKSTATIONNAME, ws.WORKSTATIONCODE
                                        ) AS RowNum
                                    FROM MWORKSTATION ws
                                    LEFT JOIN MWORKCENTER wc
                                        ON wc.WORKCENTERID = ws.WORKCENTERID
                                    WHERE ws.STATUS = 1
                                      AND (@TenantId = -1 OR ws.TENANTID = @TenantId)
                                )
                                SELECT *
                                FROM PagedData
                                WHERE (@FirstNumber = -1 AND @MaxResult = -1)
                                   OR (RowNum BETWEEN @FirstNumber AND @MaxResult);";

    public const string DELETE_WORKSTATION = @"DELETE FROM MWORKSTATION
                                                WHERE WORKSTATIONID =@workstationid;";
}