namespace WiDAL.Query.Wi;

public static class LineMapQB
{
    // Q-LINEMAP-1: Full operation map for a line × routing version
    // Index: IX_MLINEOPERATIONMAP_LINEID_EFFECTIVETO
    //        on (PRODUCTIONLINEID, TENANTID, EFFECTIVETO) INCLUDE (ROUTINGDETAILID, WORKSTATIONID, MANPOWERCOUNT, ISACTIVE)
    // BOR cycle time joined — never stored in MLINEOPERATIONMAP
    public const string GET_CURRENT_MAP = @"SELECT
                                         lom.LINEOPERATIONMAPID    AS LineOperationMapId,
                                         lom.PRODUCTIONLINEID      AS ProductionLineId,
                                         pl.LINENAME               AS LineName,

                                         rvd.ROUTINGDETAILID       AS RoutingDetailId,
                                         rvd.SLNO                  AS OperationSlNo,
                                         rvd.STAGE                 AS Stage,
                                         rvd.STEP                  AS Step,

                                         rvd.WORKCENTERID          AS WorkCenterId,
                                         wc.WORKCENTERNAME         AS WorkCenterName,

                                         lom.WORKSTATIONID         AS WorkstationId,
                                         ws.WORKSTATIONCODE        AS WorkstationCode,
                                         ws.WORKSTATIONNAME        AS WorkstationName,

                                         lom.MANPOWERCOUNT         AS ManpowerCount,
                                         lom.EFFECTIVEFROM         AS EffectiveFrom,
                                         lom.EFFECTIVETO           AS EffectiveTo,

                                         CAST(lom.ISACTIVE AS BIT) AS IsActive,
                                         lom.REMARKS               AS Remarks,

                                         bor.CYCLETIME             AS CycleTime,
                                         bor.SETUPTIME             AS SetupTime,
                                         bor.TIMEUOM               AS BorTimeUom

                                     FROM DBO.MLINEOPERATIONMAP lom

                                     LEFT JOIN DBO.MPRODUCTIONLINE pl
                                         ON pl.LINEID = lom.PRODUCTIONLINEID

                                     LEFT JOIN DBO.MROUTINGVERSIONDETAIL rvd
                                         ON rvd.ROUTINGDETAILID = lom.ROUTINGDETAILID

                                     LEFT JOIN DBO.MWORKCENTER wc
                                         ON wc.WORKCENTERID = rvd.WORKCENTERID

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

                                     LEFT JOIN DBO.MBILLOFRESOURCE bor
                                         ON bor.ROUTINGDETAILID = lom.ROUTINGDETAILID
                                         AND bor.TYPE = 1
                                         AND bor.FROMDATE <= GETDATE()
                                         AND bor.TODATE >= GETDATE()

                                     WHERE pl.LINEID = @ProductionLineId
                                       AND rvd.ROUTINGVERSIONID = @RoutingVersionId
                                       AND lom.TENANTID = @TenantId
                                       AND lom.ISACTIVE = 1
                                     ORDER BY rvd.SLNO";

    public const string GET_BY_ID = @"
        SELECT  LINEOPERATIONMAPID              AS LineOperationMapId,
                PRODUCTIONLINEID                AS ProductionLineId,
                ROUTINGDETAILID                 AS RoutingDetailId,
                WORKSTATIONID                   AS WorkstationId,
                EFFECTIVEFROM                   AS EffectiveFrom,
                EFFECTIVETO                     AS EffectiveTo,
                MANPOWERCOUNT                   AS ManpowerCount,
                CAST(ISACTIVE AS BIT)           AS IsActive,
                REMARKS                         AS Remarks
        FROM    DBO.MLINEOPERATIONMAP
        WHERE   LINEOPERATIONMAPID = @LineOperationMapId;";

    // Only permitted UPDATE on this table — close the current open mapping
    public const string CLOSE_CURRENT = @"
        UPDATE DBO.MLINEOPERATIONMAP
        SET    EFFECTIVETO   = @EffectiveTo,
               MODIFIEDBYID  = @UserId,
               MODIFIEDON    = GETDATE()
        WHERE  PRODUCTIONLINEID = @ProductionLineId
          AND  ROUTINGDETAILID  = @RoutingDetailId
          AND  EFFECTIVETO      IS NULL;";

    // IDENTITY PK — never supply LINEOPERATIONMAPID
    public const string INSERT_NEW = @"
        INSERT INTO DBO.MLINEOPERATIONMAP
            (PRODUCTIONLINEID, ROUTINGDETAILID, WORKSTATIONID,
             EFFECTIVEFROM, EFFECTIVETO, MANPOWERCOUNT, ISACTIVE, REMARKS,
             CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID)
        VALUES
            (@ProductionLineId, @RoutingDetailId, @WorkstationId,
             @EffectiveFrom, NULL, @ManpowerCount, @IsActive, @Remarks,
             @UserId, GETDATE(), @UserId, GETDATE(), @TenantId)";

    // Q-LINEMAP-2: Staffing gap check — required vs assigned operators per step
    // Index: IX_MLINEOPERATIONMAP_LINEID_EFFECTIVETO
    public const string GET_STAFFING_GAP = @"
        SELECT  lom.WORKSTATIONID                   AS WorkstationId,
                ws.WORKSTATIONCODE                  AS WorkstationCode,
                ws.WORKSTATIONNAME                  AS WorkstationName,
                lom.MANPOWERCOUNT                   AS RequiredCount,
                COUNT(CASE WHEN stf.ISABSENT = 0
                           THEN stf.LINESHIFTSTAFFINGID END)
                                                    AS AssignedCount,
                CASE
                    WHEN COUNT(CASE WHEN stf.ISABSENT = 0
                                    THEN stf.LINESHIFTSTAFFINGID END)
                         >= lom.MANPOWERCOUNT       THEN 'OK'
                    ELSE 'UNDERSTAFFED'
                END                                 AS StaffingStatus
        FROM       DBO.MLINEOPERATIONMAP     lom
        JOIN       DBO.MWORKSTATION          ws  ON  ws.WORKSTATIONID    = lom.WORKSTATIONID
        JOIN       DBO.MROUTINGVERSIONDETAIL rvd ON  rvd.ROUTINGDETAILID = lom.ROUTINGDETAILID
        LEFT JOIN  DBO.TLINESHIFTROSTER      r   ON  r.PRODUCTIONLINEID  = lom.PRODUCTIONLINEID
                                                 AND  r.ROSTERDATE        = @RosterDate
                                                 AND  r.SHIFTID           = @ShiftId
                                                 AND  r.ROSTERTYPE        = 1
        LEFT JOIN  DBO.TLINESHIFTSTAFFING    stf ON  stf.LINESHIFTROSTERID = r.LINESHIFTROSTERID
                                                 AND  stf.WORKSTATIONID   = lom.WORKSTATIONID
        WHERE  lom.PRODUCTIONLINEID = @ProductionLineId
          AND  lom.TENANTID         = @TenantId
          AND  lom.EFFECTIVETO      IS NULL
          AND  lom.ISACTIVE         = 1
          AND  rvd.ROUTINGDETAILID = @RoutingDetailId
        GROUP BY
            lom.WORKSTATIONID,
            ws.WORKSTATIONCODE,
            ws.WORKSTATIONNAME,
            lom.MANPOWERCOUNT
        ORDER BY ws.WORKSTATIONCODE";
}