namespace AnalyticsDAL.Query
{
    public static class OEEEngineQB
    {
        // ── Input data from TPRODUCTION tables ────────────────────────────────
        // Streams production data for OEE calculation. Use StreamAsync — can exceed 5k rows.
        // Required index (MM module): IX_TPRODUCTION_OUID_DATE (OUID, PRODUCTIONDATE, TENANTID)

        public const string GET_OEE_INPUT_DATA_SQL = @"
            SELECT pr.MACHINEID,
                   pr.WORKCENTERID,
                   CAST(p.PRODUCTIONDATE AS DATE)   AS ProductionDate,
                   p.SHIFTID,
                   SUM(pd.GOODQUANTITY)             AS GoodQuantity,
                   SUM(pd.REJECTEDQUANTITY)         AS RejectedQuantity,
                   SUM(pd.REWORKQUANTITY)           AS ReworkQuantity,
                   SUM(ISNULL(pd.ENDTIMEMINUTES, 0) - ISNULL(pd.STARTTIMEMINUTES, 0)) AS RunTimeMinutes,
                   SUM(ISNULL(ps.STOPPAGEMINUTES, 0))    AS DowntimeMinutes,
                   SUM(ISNULL(sh.SHIFTDURATIONMINUTES, 480)) AS PlannedMinutes,
                   ISNULL(AVG(rvd.FIXEDLEADTIME), 0)     AS IdealCycleTimeMin
            FROM   TPRODUCTION p
            JOIN   TPRODUCTIONDETAIL  pd  ON pd.PRODUCTIONID   = p.PRODUCTIONID
            JOIN   TPRODUCTIONRESOURCE pr ON pr.PRODUCTIONID   = p.PRODUCTIONID
            LEFT   JOIN TPRODUCTIONSTOPPAGE ps ON ps.PRODUCTIONID = p.PRODUCTIONID
            LEFT   JOIN MSHIFT sh ON sh.SHIFTID = p.SHIFTID
            LEFT   JOIN MROUTINGVERSIONDETAIL rvd ON rvd.ROUTINGDETAILID = pd.ROUTINGDETAILID
            WHERE  p.PRODUCTIONDATE >= @FromDate
            AND    p.PRODUCTIONDATE  < @ToDate
            AND    p.TENANTID        = @TenantId
            AND    pr.MACHINEID     IS NOT NULL
            AND    pr.MACHINEID     != -1
            GROUP  BY pr.MACHINEID, pr.WORKCENTERID, CAST(p.PRODUCTIONDATE AS DATE), p.SHIFTID";

        public const string GET_OEE_INPUT_DATA_PG = @"
            SELECT pr.MACHINEID,
                   pr.WORKCENTERID,
                   CAST(p.PRODUCTIONDATE AS DATE)   AS ""ProductionDate"",
                   p.SHIFTID,
                   SUM(pd.GOODQUANTITY)             AS ""GoodQuantity"",
                   SUM(pd.REJECTEDQUANTITY)         AS ""RejectedQuantity"",
                   SUM(pd.REWORKQUANTITY)           AS ""ReworkQuantity"",
                   SUM(COALESCE(pd.ENDTIMEMINUTES, 0) - COALESCE(pd.STARTTIMEMINUTES, 0)) AS ""RunTimeMinutes"",
                   SUM(COALESCE(ps.STOPPAGEMINUTES, 0))    AS ""DowntimeMinutes"",
                   SUM(COALESCE(sh.SHIFTDURATIONMINUTES, 480)) AS ""PlannedMinutes"",
                   COALESCE(AVG(rvd.FIXEDLEADTIME), 0)     AS ""IdealCycleTimeMin""
            FROM   TPRODUCTION p
            JOIN   TPRODUCTIONDETAIL  pd  ON pd.PRODUCTIONID   = p.PRODUCTIONID
            JOIN   TPRODUCTIONRESOURCE pr ON pr.PRODUCTIONID   = p.PRODUCTIONID
            LEFT   JOIN TPRODUCTIONSTOPPAGE ps ON ps.PRODUCTIONID = p.PRODUCTIONID
            LEFT   JOIN MSHIFT sh ON sh.SHIFTID = p.SHIFTID
            LEFT   JOIN MROUTINGVERSIONDETAIL rvd ON rvd.ROUTINGDETAILID = pd.ROUTINGDETAILID
            WHERE  p.PRODUCTIONDATE >= :FromDate
            AND    p.PRODUCTIONDATE  < :ToDate
            AND    p.TENANTID        = :TenantId
            AND    pr.MACHINEID     IS NOT NULL
            AND    pr.MACHINEID     != -1
            GROUP  BY pr.MACHINEID, pr.WORKCENTERID, CAST(p.PRODUCTIONDATE AS DATE), p.SHIFTID";

        // ── Upsert OEE fact ───────────────────────────────────────────────────
        // Required index: IX_FPRODUCTIONMACHINE_MACHINE_DATE (MACHINEID, FACTDATE, TENANTID)

        public const string UPSERT_OEE_FACT_SQL = @"
            MERGE FPRODUCTIONMACHINE AS tgt
            USING (SELECT @MachineId AS MACHINEID, @FactDate AS FACTDATE,
                          @ShiftId AS SHIFTID, @TenantId AS TENANTID) AS src
            ON (tgt.MACHINEID = src.MACHINEID AND tgt.FACTDATE = src.FACTDATE
                AND tgt.SHIFTID = src.SHIFTID AND tgt.TENANTID = src.TENANTID)
            WHEN MATCHED THEN UPDATE SET
                WORKCENTERID      = @WorkCenterId,
                PLANNEDMINUTES    = @PlannedMinutes,
                RUNTIMEMINUTES    = @RunTimeMinutes,
                DOWNTIMEMINUTES   = @DowntimeMinutes,
                TOTALPRODUCED     = @TotalProduced,
                GOODQUANTITY      = @GoodQuantity,
                REJECTEDQUANTITY  = @RejectedQuantity,
                REWORKQUANTITY    = @ReworkQuantity,
                AVAILABILITY      = @Availability,
                PERFORMANCE       = @Performance,
                QUALITY           = @Quality,
                OEE               = @OEE
            WHEN NOT MATCHED THEN INSERT
                (FACTID, MACHINEID, WORKCENTERID, FACTDATE, SHIFTID,
                 PLANNEDMINUTES, RUNTIMEMINUTES, DOWNTIMEMINUTES,
                 TOTALPRODUCED, GOODQUANTITY, REJECTEDQUANTITY, REWORKQUANTITY,
                 AVAILABILITY, PERFORMANCE, QUALITY, OEE, TENANTID)
            VALUES
                (@FactId, @MachineId, @WorkCenterId, @FactDate, @ShiftId,
                 @PlannedMinutes, @RunTimeMinutes, @DowntimeMinutes,
                 @TotalProduced, @GoodQuantity, @RejectedQuantity, @ReworkQuantity,
                 @Availability, @Performance, @Quality, @OEE, @TenantId);";

        public const string UPSERT_OEE_FACT_PG = @"
            INSERT INTO FPRODUCTIONMACHINE
                (FACTID, MACHINEID, WORKCENTERID, FACTDATE, SHIFTID,
                 PLANNEDMINUTES, RUNTIMEMINUTES, DOWNTIMEMINUTES,
                 TOTALPRODUCED, GOODQUANTITY, REJECTEDQUANTITY, REWORKQUANTITY,
                 AVAILABILITY, PERFORMANCE, QUALITY, OEE, TENANTID)
            VALUES
                (:FactId, :MachineId, :WorkCenterId, :FactDate, :ShiftId,
                 :PlannedMinutes, :RunTimeMinutes, :DowntimeMinutes,
                 :TotalProduced, :GoodQuantity, :RejectedQuantity, :ReworkQuantity,
                 :Availability, :Performance, :Quality, :OEE, :TenantId)
            ON CONFLICT (MACHINEID, FACTDATE, SHIFTID, TENANTID)
            DO UPDATE SET
                WORKCENTERID     = EXCLUDED.WORKCENTERID,
                PLANNEDMINUTES   = EXCLUDED.PLANNEDMINUTES,
                RUNTIMEMINUTES   = EXCLUDED.RUNTIMEMINUTES,
                DOWNTIMEMINUTES  = EXCLUDED.DOWNTIMEMINUTES,
                TOTALPRODUCED    = EXCLUDED.TOTALPRODUCED,
                GOODQUANTITY     = EXCLUDED.GOODQUANTITY,
                REJECTEDQUANTITY = EXCLUDED.REJECTEDQUANTITY,
                REWORKQUANTITY   = EXCLUDED.REWORKQUANTITY,
                AVAILABILITY     = EXCLUDED.AVAILABILITY,
                PERFORMANCE      = EXCLUDED.PERFORMANCE,
                QUALITY          = EXCLUDED.QUALITY,
                OEE              = EXCLUDED.OEE;";

        // ── Get OEE Report (reads FPRODUCTIONMACHINE) ─────────────────────────

        public const string GET_OEE_REPORT_SQL = @"
            SELECT f.MACHINEID, ISNULL(m.MACHINENAME, '') AS MachineName,
                   f.WORKCENTERID, ISNULL(wc.WORKCENTERNAME, '') AS WorkCenterName,
                   f.FACTDATE, f.AVAILABILITY, f.PERFORMANCE, f.QUALITY, f.OEE,
                   f.TOTALPRODUCED, f.GOODQUANTITY, f.REJECTEDQUANTITY
            FROM   FPRODUCTIONMACHINE f
            LEFT   JOIN MMACHINE     m  ON m.MACHINEID     = f.MACHINEID
            LEFT   JOIN MWORKCENTER  wc ON wc.WORKCENTERID = f.WORKCENTERID
            WHERE  f.TENANTID   = @TenantId
            AND    f.FACTDATE  >= @FromDate
            AND    f.FACTDATE  <= @ToDate
            AND    (@WorkCenterId = -1 OR f.WORKCENTERID = @WorkCenterId)
            ORDER  BY f.FACTDATE DESC, f.MACHINEID";

        public const string GET_OEE_REPORT_PG = @"
            SELECT f.MACHINEID, COALESCE(m.MACHINENAME, '') AS ""MachineName"",
                   f.WORKCENTERID, COALESCE(wc.WORKCENTERNAME, '') AS ""WorkCenterName"",
                   f.FACTDATE, f.AVAILABILITY, f.PERFORMANCE, f.QUALITY, f.OEE,
                   f.TOTALPRODUCED, f.GOODQUANTITY, f.REJECTEDQUANTITY
            FROM   FPRODUCTIONMACHINE f
            LEFT   JOIN MMACHINE     m  ON m.MACHINEID     = f.MACHINEID
            LEFT   JOIN MWORKCENTER  wc ON wc.WORKCENTERID = f.WORKCENTERID
            WHERE  f.TENANTID   = :TenantId
            AND    f.FACTDATE  >= :FromDate
            AND    f.FACTDATE  <= :ToDate
            AND    (:WorkCenterId = -1 OR f.WORKCENTERID = :WorkCenterId)
            ORDER  BY f.FACTDATE DESC, f.MACHINEID";

        // ── Live OEE (reads TPRODUCTIONDETAIL for today) ──────────────────────

        public const string GET_OEE_LIVE_SQL = @"
            SELECT pr.MACHINEID, ISNULL(m.MACHINENAME, '') AS MachineName,
                   pr.WORKCENTERID, ISNULL(wc.WORKCENTERNAME, '') AS WorkCenterName,
                   CAST(p.PRODUCTIONDATE AS DATE) AS FactDate,
                   SUM(pd.GOODQUANTITY)       AS GoodQuantity,
                   SUM(pd.REJECTEDQUANTITY)   AS RejectedQuantity,
                   SUM(pd.REWORKQUANTITY)     AS ReworkQuantity,
                   CAST(0 AS DECIMAL(5,4))    AS Availability,
                   CAST(0 AS DECIMAL(5,4))    AS Performance,
                   CAST(0 AS DECIMAL(5,4))    AS Quality,
                   CAST(0 AS DECIMAL(5,4))    AS OEE,
                   SUM(pd.GOODQUANTITY) + SUM(pd.REJECTEDQUANTITY) + SUM(pd.REWORKQUANTITY) AS TotalProduced
            FROM   TPRODUCTION       p
            JOIN   TPRODUCTIONDETAIL pd ON pd.PRODUCTIONID   = p.PRODUCTIONID
            JOIN   TPRODUCTIONRESOURCE pr ON pr.PRODUCTIONID = p.PRODUCTIONID
            LEFT   JOIN MMACHINE    m  ON m.MACHINEID    = pr.MACHINEID
            LEFT   JOIN MWORKCENTER wc ON wc.WORKCENTERID = pr.WORKCENTERID
            WHERE  CAST(p.PRODUCTIONDATE AS DATE) = CAST(GETUTCDATE() AS DATE)
            AND    p.TENANTID = @TenantId
            AND    pr.MACHINEID IS NOT NULL AND pr.MACHINEID != -1
            GROUP  BY pr.MACHINEID, m.MACHINENAME, pr.WORKCENTERID, wc.WORKCENTERNAME,
                      CAST(p.PRODUCTIONDATE AS DATE)";

        public const string GET_OEE_LIVE_PG = @"
            SELECT pr.MACHINEID, COALESCE(m.MACHINENAME, '') AS ""MachineName"",
                   pr.WORKCENTERID, COALESCE(wc.WORKCENTERNAME, '') AS ""WorkCenterName"",
                   CAST(p.PRODUCTIONDATE AS DATE) AS ""FactDate"",
                   SUM(pd.GOODQUANTITY)       AS ""GoodQuantity"",
                   SUM(pd.REJECTEDQUANTITY)   AS ""RejectedQuantity"",
                   SUM(pd.REWORKQUANTITY)     AS ""ReworkQuantity"",
                   CAST(0 AS DECIMAL(5,4))    AS ""Availability"",
                   CAST(0 AS DECIMAL(5,4))    AS ""Performance"",
                   CAST(0 AS DECIMAL(5,4))    AS ""Quality"",
                   CAST(0 AS DECIMAL(5,4))    AS ""OEE"",
                   SUM(pd.GOODQUANTITY) + SUM(pd.REJECTEDQUANTITY) + SUM(pd.REWORKQUANTITY) AS ""TotalProduced""
            FROM   TPRODUCTION       p
            JOIN   TPRODUCTIONDETAIL pd ON pd.PRODUCTIONID   = p.PRODUCTIONID
            JOIN   TPRODUCTIONRESOURCE pr ON pr.PRODUCTIONID = p.PRODUCTIONID
            LEFT   JOIN MMACHINE    m  ON m.MACHINEID    = pr.MACHINEID
            LEFT   JOIN MWORKCENTER wc ON wc.WORKCENTERID = pr.WORKCENTERID
            WHERE  CAST(p.PRODUCTIONDATE AS DATE) = CURRENT_DATE
            AND    p.TENANTID = :TenantId
            AND    pr.MACHINEID IS NOT NULL AND pr.MACHINEID != -1
            GROUP  BY pr.MACHINEID, m.MACHINENAME, pr.WORKCENTERID, wc.WORKCENTERNAME,
                      CAST(p.PRODUCTIONDATE AS DATE)";
    }
}