namespace MMDAL.Query.Production
{
    // Required indexes for report performance:
    //   TPRODUCTION:        IX_TPRODUCTION_OUID_DATE (already in Phase 0)
    //   TPRODUCTIONSTOPPAGE: IX_TPRODUCTIONSTOPPAGE_PROD (PRODUCTIONID, TENANTID)
    //   TRESOURCEPUNCH:     IX_TRESOURCEPUNCH_OUID_DATE (OUID, PUNCHDATE, TENANTID)
    //   TINDENTDETAIL:      IX_TINDENTDETAIL_INDENT (INDENTID, TENANTID)
    public static class ProductionReportsQB
    {
        // ── Production Status Report ──────────────────────────────────────────

        public const string GET_PRODUCTION_STATUS_REPORT_SQL = @"
            SELECT p.PRODUCTIONID                    AS ProductionId,
                   p.PRODUCTIONNUMBER               AS ProductionNumber,
                   p.PRODUCTIONDATE                 AS ProductionDate,
                   ISNULL(wc.WORKCENTERNAME, '')    AS WorkCenterName,
                   ISNULL(i.ITEMNAME, '')           AS ItemName,
                   SUM(pd.GOODQUANTITY)             AS GoodQty,
                   SUM(pd.REJECTEDQUANTITY)         AS RejectedQty,
                   SUM(pd.REWORKQUANTITY)           AS ReworkQty,
                   SUM(pd.PRODUCTIONQUANTITY)       AS TotalQty,
                   SUM(
                        CASE
                            WHEN pd.STARTTIME IS NOT NULL
                             AND pd.ENDTIME IS NOT NULL
                            THEN DATEDIFF(MINUTE, pd.STARTTIME, pd.ENDTIME)
                            ELSE 0
                        END
                    ) AS RunTimeMinutes,
                   p.STATUS                         AS Status
            FROM   TPRODUCTION p
            JOIN   TPRODUCTIONDETAIL pd ON pd.PRODUCTIONID = p.PRODUCTIONID
            LEFT JOIN MWORKCENTER wc    ON wc.WORKCENTERID = p.WORKCENTERID
            LEFT JOIN MITEM i           ON i.ITEMID        = pd.ITEMID
            WHERE  p.OUID         = @OUId
            AND    p.STATUS      <> 2
            AND    p.PRODUCTIONDATE >= @FromDate
            AND    p.PRODUCTIONDATE <= @ToDate
            GROUP BY p.PRODUCTIONID, p.PRODUCTIONNUMBER, p.PRODUCTIONDATE,
                     wc.WORKCENTERNAME, i.ITEMNAME, p.STATUS
            ORDER BY p.PRODUCTIONDATE DESC;";

        public const string GET_PRODUCTION_STATUS_REPORT_PG = @"SELECT
                                                                    p.PRODUCTIONID                    AS ""ProductionId"",
                                                                    p.PRODUCTIONNUMBER                AS ""ProductionNumber"",
                                                                    p.PRODUCTIONDATE                  AS ""ProductionDate"",
                                                                    COALESCE(wc.WORKCENTERNAME, '')   AS ""WorkCenterName"",
                                                                    COALESCE(i.ITEMNAME, '')          AS ""ItemName"",
                                                                    SUM(pd.GOODQUANTITY)              AS ""GoodQty"",
                                                                    SUM(pd.REJECTEDQUANTITY)          AS ""RejectedQty"",
                                                                    SUM(pd.REWORKQUANTITY)            AS ""ReworkQty"",
                                                                    SUM(pd.PRODUCTIONQUANTITY)        AS ""TotalQty"",
                                                                    SUM(
                                                                        CASE
                                                                            WHEN pd.STARTTIME IS NOT NULL
                                                                             AND pd.ENDTIME IS NOT NULL
                                                                            THEN EXTRACT(EPOCH FROM (pd.ENDTIME - pd.STARTTIME)) / 60
                                                                            ELSE 0
                                                                        END
                                                                    ) AS ""RunTimeMinutes"",
                                                                    p.STATUS                          AS ""Status""
                                                                FROM TPRODUCTION p
                                                                INNER JOIN TPRODUCTIONDETAIL pd
                                                                    ON pd.PRODUCTIONID = p.PRODUCTIONID
                                                                LEFT JOIN MWORKCENTER wc
                                                                    ON wc.WORKCENTERID = p.WORKCENTERID
                                                                LEFT JOIN MITEM i
                                                                    ON i.ITEMID = pd.ITEMID
                                                                WHERE p.OUID = :OUId
                                                                  AND p.STATUS <> 2
                                                                  AND p.PRODUCTIONDATE >= :FromDate
                                                                  AND p.PRODUCTIONDATE <= :ToDate
                                                                GROUP BY
                                                                    p.PRODUCTIONID,
                                                                    p.PRODUCTIONNUMBER,
                                                                    p.PRODUCTIONDATE,
                                                                    wc.WORKCENTERNAME,
                                                                    i.ITEMNAME,
                                                                    p.STATUS
                                                                ORDER BY p.PRODUCTIONDATE DESC;";

        // ── Stoppage Register ─────────────────────────────────────────────────

        public const string GET_STOPPAGE_REGISTER_SQL = @"
            SELECT ps.PRODUCTIONSTOPPAGEID          AS StoppageId,
                   p.PRODUCTIONID                   AS ProductionId,
                   p.PRODUCTIONDATE                 AS ProductionDate,
                   ISNULL(wc.WORKCENTERNAME, '')    AS WorkCenterName,
                   ISNULL(i.ITEMNAME, '')           AS ItemName,
                   ISNULL(sr.REASONNAME, '')        AS StopReason,
                   ps.STOPPAGEDURATION              AS StoppageMinutes,
                   ISNULL(ps.STARTDATE, p.PRODUCTIONDATE) AS StoppageDate
            FROM   TPRODUCTIONSTOPPAGE ps
            JOIN   TPRODUCTION p         ON p.PRODUCTIONID     = ps.PRODUCTIONID
            JOIN   TPRODUCTIONDETAIL pd  ON pd.PRODUCTIONDETAILID = ps.PRODUCTIONDETAILID
            LEFT JOIN MWORKCENTER wc     ON wc.WORKCENTERID    = p.WORKCENTERID
            LEFT JOIN MITEM i            ON i.ITEMID           = pd.ITEMID
            LEFT JOIN MREASON sr         ON sr.REASONID        = ps.STOPPAGEREASONID
            WHERE  p.OUID         = @OUId
            AND    p.STATUS      <> 2
            AND    p.PRODUCTIONDATE >= @FromDate
            AND    p.PRODUCTIONDATE <= @ToDate
            ORDER BY p.PRODUCTIONDATE DESC, ps.PRODUCTIONSTOPPAGEID;";

        public const string GET_STOPPAGE_REGISTER_PG = @"
            SELECT ps.PRODUCTIONSTOPPAGEID          AS ""StoppageId"",
                   p.PRODUCTIONID                   AS ""ProductionId"",
                   p.PRODUCTIONDATE                 AS ""ProductionDate"",
                   COALESCE(wc.WORKCENTERNAME, '')  AS ""WorkCenterName"",
                   COALESCE(i.ITEMNAME, '')         AS ""ItemName"",
                   COALESCE(sr.REASONNAME, '')      AS ""StopReason"",
                   ps.STOPPAGEDURATION              AS ""StoppageMinutes"",
                   COALESCE(ps.STARTDATE, p.PRODUCTIONDATE) AS ""StoppageDate""
            FROM   TPRODUCTIONSTOPPAGE ps
            JOIN   TPRODUCTION p         ON p.PRODUCTIONID     = ps.PRODUCTIONID
            JOIN   TPRODUCTIONDETAIL pd  ON pd.PRODUCTIONDETAILID = ps.PRODUCTIONDETAILID
            LEFT JOIN MWORKCENTER wc     ON wc.WORKCENTERID    = p.WORKCENTERID
            LEFT JOIN MITEM i            ON i.ITEMID           = pd.ITEMID
            LEFT JOIN MREASON sr         ON sr.REASONID        = ps.STOPPAGEREASONID
            WHERE  p.OUID         = :OUId
            AND    p.STATUS      <> 2
            AND    p.PRODUCTIONDATE >= :FromDate
            AND    p.PRODUCTIONDATE <= :ToDate
            ORDER BY p.PRODUCTIONDATE DESC, ps.PRODUCTIONSTOPPAGEID;";

        // ── Work Order Register ───────────────────────────────────────────────

        public const string GET_WO_REGISTER_SQL = @"
            SELECT i.INDENTID                              AS IndentId,
                   i.INDENTNUMBER                         AS IndentNumber,
                   i.INDENTDATE                           AS IndentDate,
                   ISNULL(mi.ITEMNAME, '')                AS ItemName,
                   ISNULL(id_.INDENTDETAILQUANTITY, 0)    AS PlannedQty,
                   CASE WHEN ISNULL(id_.RELEASESTATUS, 0) >= 1
                        THEN ISNULL(id_.INDENTDETAILQUANTITY, 0)
                        ELSE 0 END                         AS ReleasedQty,
                   ISNULL(id_.RELEASESTATUS, 0)           AS ReleaseStatus,
                   ISNULL(i.EXPECTEDCOMPLETIONDATE, i.INDENTDATE) AS ExpectedCompletionDate
            FROM   TINDENT i
            JOIN   TINDENTDETAIL id_  ON id_.INDENTID  = i.INDENTID
            JOIN   MORGANIZATIONUNIT ou ON ou.OUID     = i.OUID
            JOIN   MBIZTRANSACTIONTYPE bt ON bt.BIZTRANSACTIONTYPEID = i.BIZTRANSACTIONTYPEID
            LEFT JOIN MITEM mi         ON mi.ITEMID    = id_.ITEMID
            WHERE  i.OUID     = @OUId
            AND    i.STATUS  <> 2
            AND    i.INDENTDATE >= @FromDate
            AND    i.INDENTDATE <= @ToDate
            {BizTransactionClassFilter}
            ORDER BY i.INDENTDATE DESC;";

        public const string GET_WO_REGISTER_PG = @"
            SELECT i.INDENTID                              AS ""IndentId"",
                   i.INDENTNUMBER                         AS ""IndentNumber"",
                   i.INDENTDATE                           AS ""IndentDate"",
                   COALESCE(mi.ITEMNAME, '')              AS ""ItemName"",
                   COALESCE(id_.INDENTDETAILQUANTITY, 0)  AS ""PlannedQty"",
                   CASE WHEN COALESCE(id_.RELEASESTATUS, 0) >= 1
                        THEN COALESCE(id_.INDENTDETAILQUANTITY, 0)
                        ELSE 0 END                         AS ""ReleasedQty"",
                   COALESCE(id_.RELEASESTATUS, 0)         AS ""ReleaseStatus"",
                   COALESCE(i.EXPECTEDCOMPLETIONDATE, i.INDENTDATE) AS ""ExpectedCompletionDate""
            FROM   TINDENT i
            JOIN   TINDENTDETAIL id_  ON id_.INDENTID  = i.INDENTID
            JOIN   MORGANIZATIONUNIT ou ON ou.OUID     = i.OUID
            JOIN   MBIZTRANSACTIONTYPE bt ON bt.BIZTRANSACTIONTYPEID = i.BIZTRANSACTIONTYPEID
            LEFT JOIN MITEM mi         ON mi.ITEMID    = id_.ITEMID
            WHERE  i.OUID     = :OUId
            AND    i.STATUS  <> 2
            AND    i.INDENTDATE >= :FromDate
            AND    i.INDENTDATE <= :ToDate
            {BizTransactionClassFilter}
            ORDER BY i.INDENTDATE DESC;";

        // ── Resource Utilization (StreamAsync — can exceed 5k rows) ──────────

        public const string GET_RESOURCE_UTILIZATION_SQL = @"SELECT
                                                        ISNULL(e.EMPLOYEENAME, '')      AS EmployeeName,
                                                        ISNULL(wc.WORKCENTERNAME, '')   AS WorkCenterName,
                                                        rp.WORKORDERID                  AS WorkOrderId,
                                                        rp.PUNCHDATETIME                AS PunchDateTime,
                                                        rp.STATUS,
                                                        rp.QUANTITY,
                                                        rp.REMARKS,
                                                        rp.DEVICEID,
                                                        rp.GPSLAT,
                                                        rp.GPSLON,
                                                        rp.CAPTUREMODE
                                                    FROM TRESOURCEPUNCH rp
                                                    LEFT JOIN MEMPLOYEE e
                                                        ON e.EMPLOYEEID = rp.EMPLOYEEID
                                                    LEFT JOIN MRESOURCE r
                                                        ON r.RESOURCEID = rp.RESOURCEID
                                                    LEFT JOIN MWORKCENTER wc
                                                        ON wc.WORKCENTERID = r.WORKCENTERID
                                                    WHERE rp.PUNCHDATETIME >= @FromDate
                                                      AND rp.PUNCHDATETIME <= @ToDate
                                                    ORDER BY rp.PUNCHDATETIME DESC, rp.EMPLOYEEID;";

        public const string GET_RESOURCE_UTILIZATION_PG = @"SELECT
                                                        COALESCE(e.EMPLOYEENAME, '')      AS ""EmployeeName"",
                                                        COALESCE(wc.WORKCENTERNAME, '')   AS ""WorkCenterName"",
                                                        rp.WORKORDERID                    AS ""WorkOrderId"",
                                                        rp.PUNCHDATETIME                  AS ""PunchDateTime"",
                                                        rp.STATUS                         AS ""Status"",
                                                        rp.QUANTITY                       AS ""Quantity"",
                                                        rp.REMARKS                        AS ""Remarks"",
                                                        rp.DEVICEID                       AS ""DeviceId"",
                                                        rp.GPSLAT                         AS ""GpsLat"",
                                                        rp.GPSLON                         AS ""GpsLon"",
                                                        rp.CAPTUREMODE                    AS ""CaptureMode""
                                                    FROM TRESOURCEPUNCH rp
                                                    LEFT JOIN MEMPLOYEE e
                                                        ON e.EMPLOYEEID = rp.EMPLOYEEID
                                                    LEFT JOIN MRESOURCE r
                                                        ON r.RESOURCEID = rp.RESOURCEID
                                                    LEFT JOIN MWORKCENTER wc
                                                        ON wc.WORKCENTERID = r.WORKCENTERID
                                                    WHERE rp.PUNCHDATETIME >= :FromDate
                                                      AND rp.PUNCHDATETIME <= :ToDate
                                                    ORDER BY
                                                        rp.PUNCHDATETIME DESC,
                                                        rp.EMPLOYEEID;";

        // ── Production Summary ────────────────────────────────────────────────

        public const string GET_PRODUCTION_SUMMARY_SQL = @"
            SELECT CAST(p.PRODUCTIONDATE AS DATE)    AS ProductionDate,
                   p.WORKCENTERID                    AS WorkCenterId,
                   ISNULL(wc.WORKCENTERNAME, '')     AS WorkCenterName,
                   SUM(pd.GOODQUANTITY)              AS TotalGoodQty,
                   SUM(pd.REJECTEDQUANTITY)          AS TotalRejectedQty,
                   SUM(pd.PRODUCTIONQUANTITY)        AS TotalProduced,
                  SUM(
                        CASE
                            WHEN pd.STARTTIME IS NOT NULL
                             AND pd.ENDTIME IS NOT NULL
                            THEN DATEDIFF(MINUTE, pd.STARTTIME, pd.ENDTIME)
                            ELSE 0
                        END
                    ) AS RunTimeMinutes
            FROM   TPRODUCTION p
            JOIN   TPRODUCTIONDETAIL pd ON pd.PRODUCTIONID = p.PRODUCTIONID
            LEFT JOIN MWORKCENTER wc    ON wc.WORKCENTERID = p.WORKCENTERID
            WHERE  p.OUID         = @OUId
            AND    p.STATUS      <> 2
            AND    p.PRODUCTIONDATE >= @FromDate
            AND    p.PRODUCTIONDATE <= @ToDate
            GROUP BY CAST(p.PRODUCTIONDATE AS DATE), p.WORKCENTERID, wc.WORKCENTERNAME
            ORDER BY CAST(p.PRODUCTIONDATE AS DATE) DESC;";

        public const string GET_PRODUCTION_SUMMARY_PG = @"SELECT
                                                    CAST(p.PRODUCTIONDATE AS DATE)      AS ""ProductionDate"",
                                                    p.WORKCENTERID                      AS ""WorkCenterId"",
                                                    COALESCE(wc.WORKCENTERNAME, '')     AS ""WorkCenterName"",
                                                    SUM(pd.GOODQUANTITY)                AS ""TotalGoodQty"",
                                                    SUM(pd.REJECTEDQUANTITY)            AS ""TotalRejectedQty"",
                                                    SUM(pd.PRODUCTIONQUANTITY)          AS ""TotalProduced"",
                                                    SUM(
                                                        CASE
                                                            WHEN pd.STARTTIME IS NOT NULL
                                                             AND pd.ENDTIME IS NOT NULL
                                                            THEN EXTRACT(EPOCH FROM (pd.ENDTIME - pd.STARTTIME)) / 60
                                                            ELSE 0
                                                        END
                                                    ) AS ""RunTimeMinutes""
                                                FROM TPRODUCTION p
                                                INNER JOIN TPRODUCTIONDETAIL pd
                                                    ON pd.PRODUCTIONID = p.PRODUCTIONID
                                                LEFT JOIN MWORKCENTER wc
                                                    ON wc.WORKCENTERID = p.WORKCENTERID
                                                WHERE p.OUID = :OUId
                                                  AND p.STATUS <> 2
                                                  AND p.PRODUCTIONDATE >= :FromDate
                                                  AND p.PRODUCTIONDATE <= :ToDate
                                                GROUP BY
                                                    CAST(p.PRODUCTIONDATE AS DATE),
                                                    p.WORKCENTERID,
                                                    wc.WORKCENTERNAME
                                                ORDER BY
                                                    CAST(p.PRODUCTIONDATE AS DATE) DESC;";

        // ── WIP Status (replicates FNINDENTWIP) ───────────────────────────────

        public const string GET_WIP_STATUS_SQL = @"
            SELECT id_.ITEMID                        AS ItemId,
                   ISNULL(mi.ITEMNAME, '')           AS ItemName,
                   SUM(id_.INDENTDETAILQUANTITY)     AS WorkOrderQty,
                   ISNULL(SUM(pd.GOODQUANTITY), 0)  AS ProducedQty,
                   SUM(id_.INDENTDETAILQUANTITY)
                       - ISNULL(SUM(pd.GOODQUANTITY), 0) AS PendingQty,
                   ISNULL(SUM(CASE WHEN id_.RELEASESTATUS = 2 THEN id_.INDENTDETAILQUANTITY ELSE 0 END), 0) AS InProgressQty
            FROM   TINDENT i
            JOIN   TINDENTDETAIL id_    ON id_.INDENTID  = i.INDENTID
            LEFT JOIN TPRODUCTIONDETAIL pd ON pd.INDENTDETAILID = id_.INDENTDETAILID
            LEFT JOIN MITEM mi          ON mi.ITEMID     = id_.ITEMID
            WHERE  i.OUID      = @OUId
            AND    i.STATUS   <> 2
            AND    i.INDENTDATE <= @AsOnDate
            GROUP BY id_.ITEMID, mi.ITEMNAME
            ORDER BY mi.ITEMNAME;";

        public const string GET_WIP_STATUS_PG = @"
            SELECT id_.ITEMID                        AS ""ItemId"",
                   COALESCE(mi.ITEMNAME, '')         AS ""ItemName"",
                   SUM(id_.INDENTDETAILQUANTITY)     AS ""WorkOrderQty"",
                   COALESCE(SUM(pd.GOODQUANTITY), 0) AS ""ProducedQty"",
                   SUM(id_.INDENTDETAILQUANTITY)
                       - COALESCE(SUM(pd.GOODQUANTITY), 0) AS ""PendingQty"",
                   COALESCE(SUM(CASE WHEN id_.RELEASESTATUS = 2 THEN id_.INDENTDETAILQUANTITY ELSE 0 END), 0) AS ""InProgressQty""
            FROM   TINDENT i
            JOIN   TINDENTDETAIL id_    ON id_.INDENTID  = i.INDENTID
            LEFT JOIN TPRODUCTIONDETAIL pd ON pd.INDENTDETAILID = id_.INDENTDETAILID
            LEFT JOIN MITEM mi          ON mi.ITEMID     = id_.ITEMID
            WHERE  i.OUID      = :OUId
            AND    i.STATUS   <> 2
            AND    i.INDENTDATE <= :AsOnDate
            GROUP BY id_.ITEMID, mi.ITEMNAME
            ORDER BY mi.ITEMNAME;";
    }
}