using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MMDAL.Query.Production
{
    // GB4 parity: Production.svc/RejectionReport → ProductionBLL.GetRejectionReport
    // GB4 built this report by string-concatenating a SELECT/GROUP BY pair chosen from a fixed
    // set of grouping dimensions onto one of three base joins (Summary / Periodic / ItemCategory),
    // driven by (Type, SubType). We keep the exact same three base joins as fixed SQL, and the
    // caller (RejectionReportDAL) substitutes {FIELDS}/{GROUPBY} from a fixed, non-user-supplied
    // whitelist (RejectionReportDimensions) — never from request input — so this stays as safe as
    // a static parameterized query while preserving GB4's per-dimension pivoting.
    //
    // Indexes recommended:
    //   TPRODUCTIONDETAIL: IX_TPRODUCTIONDETAIL_INDENTDETAILID (INDENTDETAILID, TENANTID)
    //   TPRODUCTION:       IX_TPRODUCTION_PRODUCTIONDATE (PRODUCTIONDATE, TENANTID)
    public static class RejectionReportQB
    {
        // ── SubType 0: Summary (grouped only by the chosen dimension) ───────

        public const string BASE_SUMMARY = @"
            SELECT
                {FIELDS},
                SUM(CASE WHEN A.TYPE = 0 THEN ISNULL(B.PRODUCTIONQUANTITY, 0) ELSE 0 END) AS Produced,
                SUM(CASE WHEN A.TYPE = 1 THEN ISNULL(B.PRODUCTIONQUANTITY, 0) ELSE 0 END) AS ReProcessed,
                SUM(B.GOODQUANTITY) AS Accepeted,
                SUM(B.REWORKQUANTITY) AS Rework,
                SUM(B.REJECTEDQUANTITY) AS Rejected,
                SUM(B.OTHERQUANTITY) AS Others,
                SUM(B.PRODUCTIONQUANTITY * B.PROCESSCOST) AS ProcessValue,
                SUM(B.MATERIALCOST * B.PRODUCTIONQUANTITY) AS MaterialValue,
                SUM(B.REJECTEDQUANTITY * B.PROCESSCOST) AS RejectionProcessValue,
                SUM(B.MATERIALCOST * B.REJECTEDQUANTITY) AS RejectionMaterialValue
            FROM   TPRODUCTION A
            INNER JOIN TPRODUCTIONDETAIL B ON A.PRODUCTIONID = B.PRODUCTIONID
            INNER JOIN MITEM C ON B.ITEMID = C.ITEMID
            INNER JOIN MSKU D ON B.SKUID = D.SKUID
            INNER JOIN TINDENTDETAIL E ON E.INDENTDETAILID = B.INDENTDETAILID
            INNER JOIN TINDENT H ON E.INDENTID = H.INDENTID
            INNER JOIN MPROCESS F ON H.PROCESSID = F.PROCESSID
            INNER JOIN MUOM G ON B.UOMID = G.UOMID
            INNER JOIN MWORKCENTER I ON A.WORKCENTERID = I.WORKCENTERID
            INNER JOIN MEMPLOYEE J ON J.EMPLOYEEID = A.INCHARGEID
            WHERE  1 = 1
            AND    A.PRODUCTIONDATE >= @periodfromdate
            AND    A.PRODUCTIONDATE <= @periodtodate
            AND    (@indentid = -1 OR E.INDENTID = @indentid)
            AND    (@itemid = -1 OR B.ITEMID = @itemid)
            AND    (@skuid = -1 OR B.SKUID = @skuid)
            AND    (@processid = -1 OR F.PROCESSID = @processid)
            AND    (@workcenterid = -1 OR A.WORKCENTERID = @workcenterid)
            AND    (@employeeid = -1 OR A.INCHARGEID = @employeeid)
            AND    (@ouid = -1 OR A.OUID = @ouid)
            AND    ((B.REJECTEDQUANTITY) <> 0 OR (B.REWORKQUANTITY) <> 0)";
            // Caller appends "GROUP BY {dimension columns}" (RejectionReportDimensions) when the
            // chosen dimension has grouping columns — omitted entirely for the Type=9 (Total) case.

        public const string BASE_SUMMARY_COUNT = @"
            SELECT COUNT(*)
            FROM   TPRODUCTION A
            INNER JOIN TPRODUCTIONDETAIL B ON A.PRODUCTIONID = B.PRODUCTIONID
            INNER JOIN MITEM C ON B.ITEMID = C.ITEMID
            INNER JOIN MSKU D ON B.SKUID = D.SKUID
            INNER JOIN TINDENTDETAIL E ON E.INDENTDETAILID = B.INDENTDETAILID
            INNER JOIN TINDENT H ON E.INDENTID = H.INDENTID
            INNER JOIN MPROCESS F ON H.PROCESSID = F.PROCESSID
            INNER JOIN MUOM G ON B.UOMID = G.UOMID
            INNER JOIN MWORKCENTER I ON A.WORKCENTERID = I.WORKCENTERID
            INNER JOIN MEMPLOYEE J ON J.EMPLOYEEID = A.INCHARGEID
            WHERE  1 = 1
            AND    A.PRODUCTIONDATE >= @periodfromdate
            AND    A.PRODUCTIONDATE <= @periodtodate
            AND    (@indentid = -1 OR E.INDENTID = @indentid)
            AND    (@itemid = -1 OR B.ITEMID = @itemid)
            AND    (@skuid = -1 OR B.SKUID = @skuid)
            AND    (@processid = -1 OR F.PROCESSID = @processid)
            AND    (@workcenterid = -1 OR A.WORKCENTERID = @workcenterid)
            AND    (@employeeid = -1 OR A.INCHARGEID = @employeeid)
            AND    (@ouid = -1 OR A.OUID = @ouid)
            AND    ((B.REJECTEDQUANTITY) <> 0 OR (B.REWORKQUANTITY) <> 0);";

        // ── SubType 1: Periodic (Summary joined to the GB period calendar) ──

        public const string BASE_PERIODIC = @"
            SELECT
                {FIELDS},
                SUM(CASE WHEN A.TYPE = 0 THEN ISNULL(B.PRODUCTIONQUANTITY, 0) ELSE 0 END) AS Produced,
                SUM(CASE WHEN A.TYPE = 1 THEN ISNULL(B.PRODUCTIONQUANTITY, 0) ELSE 0 END) AS ReProcessed,
                SUM(B.GOODQUANTITY) AS Accepeted,
                SUM(B.REWORKQUANTITY) AS Rework,
                SUM(B.REJECTEDQUANTITY) AS Rejected,
                SUM(B.OTHERQUANTITY) AS Others,
                SUM(B.PRODUCTIONQUANTITY * B.PROCESSCOST) AS ProcessValue,
                SUM(B.MATERIALCOST * B.PRODUCTIONQUANTITY) AS MaterialValue,
                SUM(B.REJECTEDQUANTITY * B.PROCESSCOST) AS RejectionProcessValue,
                SUM(B.MATERIALCOST * B.REJECTEDQUANTITY) AS RejectionMaterialValue
            FROM   TPRODUCTION A
            INNER JOIN TPRODUCTIONDETAIL B ON A.PRODUCTIONID = B.PRODUCTIONID
            INNER JOIN MITEM C ON B.ITEMID = C.ITEMID
            INNER JOIN MSKU D ON B.SKUID = D.SKUID
            INNER JOIN TINDENTDETAIL E ON E.INDENTDETAILID = B.INDENTDETAILID
            INNER JOIN TINDENT H ON E.INDENTID = H.INDENTID
            INNER JOIN MPROCESS F ON H.PROCESSID = F.PROCESSID
            INNER JOIN MUOM G ON B.UOMID = G.UOMID
            INNER JOIN MWORKCENTER I ON A.WORKCENTERID = I.WORKCENTERID
            INNER JOIN MEMPLOYEE J ON J.EMPLOYEEID = A.INCHARGEID
            INNER JOIN MGBPERIOD P ON P.GBPERIODFROMDATE <= A.PRODUCTIONDATE AND P.GBPERIODTODATE >= A.PRODUCTIONDATE
            INNER JOIN MGBPERIODDETAIL Q ON P.GBPERIODID = Q.GBPERIODID
                AND Q.SUBPERIODFROMDATE <= A.PRODUCTIONDATE AND Q.SUBPERIODTODATE >= A.PRODUCTIONDATE
            WHERE  1 = 1
            AND    A.PRODUCTIONDATE >= @periodfromdate
            AND    A.PRODUCTIONDATE <= @periodtodate
            AND    (@indentid = -1 OR E.INDENTID = @indentid)
            AND    (@itemid = -1 OR B.ITEMID = @itemid)
            AND    (@skuid = -1 OR B.SKUID = @skuid)
            AND    (@processid = -1 OR F.PROCESSID = @processid)
            AND    (@workcenterid = -1 OR A.WORKCENTERID = @workcenterid)
            AND    (@employeeid = -1 OR A.INCHARGEID = @employeeid)
            AND    (@ouid = -1 OR A.OUID = @ouid)
            AND    (@periodtype = -1 OR P.PERIODTYPE = @periodtype)
            AND    ((B.REJECTEDQUANTITY) <> 0 OR (B.REWORKQUANTITY) <> 0)";
            // Caller appends "GROUP BY {dimension columns} ORDER BY Q.SUBPERIODFROMDATE".

        public const string BASE_PERIODIC_COUNT = @"
            SELECT COUNT(*)
            FROM   TPRODUCTION A
            INNER JOIN TPRODUCTIONDETAIL B ON A.PRODUCTIONID = B.PRODUCTIONID
            INNER JOIN MITEM C ON B.ITEMID = C.ITEMID
            INNER JOIN MSKU D ON B.SKUID = D.SKUID
            INNER JOIN TINDENTDETAIL E ON E.INDENTDETAILID = B.INDENTDETAILID
            INNER JOIN TINDENT H ON E.INDENTID = H.INDENTID
            INNER JOIN MPROCESS F ON H.PROCESSID = F.PROCESSID
            INNER JOIN MUOM G ON B.UOMID = G.UOMID
            INNER JOIN MWORKCENTER I ON A.WORKCENTERID = I.WORKCENTERID
            INNER JOIN MEMPLOYEE J ON J.EMPLOYEEID = A.INCHARGEID
            INNER JOIN MGBPERIOD P ON P.GBPERIODFROMDATE <= A.PRODUCTIONDATE AND P.GBPERIODTODATE >= A.PRODUCTIONDATE
            INNER JOIN MGBPERIODDETAIL Q ON P.GBPERIODID = Q.GBPERIODID
                AND Q.SUBPERIODFROMDATE <= A.PRODUCTIONDATE AND Q.SUBPERIODTODATE >= A.PRODUCTIONDATE
            WHERE  1 = 1
            AND    A.PRODUCTIONDATE >= @periodfromdate
            AND    A.PRODUCTIONDATE <= @periodtodate
            AND    (@indentid = -1 OR E.INDENTID = @indentid)
            AND    (@itemid = -1 OR B.ITEMID = @itemid)
            AND    (@skuid = -1 OR B.SKUID = @skuid)
            AND    (@processid = -1 OR F.PROCESSID = @processid)
            AND    (@workcenterid = -1 OR A.WORKCENTERID = @workcenterid)
            AND    (@employeeid = -1 OR A.INCHARGEID = @employeeid)
            AND    (@ouid = -1 OR A.OUID = @ouid)
            AND    (@periodtype = -1 OR P.PERIODTYPE = @periodtype)
            AND    ((B.REJECTEDQUANTITY) <> 0 OR (B.REWORKQUANTITY) <> 0);";

        // ── SubType 2: Item-Category summary (Type=12 only) ─────────────────

        public const string BASE_ITEMCATEGORY = @"
            SELECT
                CAT.ITEMCATEGORYID AS CategoryId,
                CAT.ITEMCATEGORYCODE AS ItemCategoryCode,
                CAT.ITEMCATEGORYNAME AS ItemCategoryName,
                B.ITEMID AS Id,
                C.ITEMCODE AS Code,
                C.ITEMNAME AS Name,
                B.SKUID AS SKUId,
                D.SKUCODE AS SKUCode,
                D.SKUNAME AS SKUName,
                SUM(CASE WHEN A.TYPE = 0 THEN ISNULL(B.PRODUCTIONQUANTITY, 0) ELSE 0 END) AS Produced,
                SUM(CASE WHEN A.TYPE = 1 THEN ISNULL(B.PRODUCTIONQUANTITY, 0) ELSE 0 END) AS ReProcessed,
                SUM(B.GOODQUANTITY) AS Accepeted,
                SUM(B.REWORKQUANTITY) AS Rework,
                SUM(B.REJECTEDQUANTITY) AS Rejected,
                SUM(B.OTHERQUANTITY) AS Others,
                SUM(B.PRODUCTIONQUANTITY * B.PROCESSCOST) AS ProcessValue,
                SUM(B.MATERIALCOST * B.PRODUCTIONQUANTITY) AS MaterialValue,
                SUM(B.REJECTEDQUANTITY * B.PROCESSCOST) AS RejectionProcessValue,
                SUM(B.MATERIALCOST * B.REJECTEDQUANTITY) AS RejectionMaterialValue
            FROM   TPRODUCTION A
            INNER JOIN TPRODUCTIONDETAIL B ON A.PRODUCTIONID = B.PRODUCTIONID
            INNER JOIN MITEM C ON B.ITEMID = C.ITEMID
            INNER JOIN MSKU D ON B.SKUID = D.SKUID
            INNER JOIN TINDENTDETAIL E ON E.INDENTDETAILID = B.INDENTDETAILID
            INNER JOIN TINDENT H ON E.INDENTID = H.INDENTID
            INNER JOIN MPROCESS F ON H.PROCESSID = F.PROCESSID
            INNER JOIN MUOM G ON B.UOMID = G.UOMID
            INNER JOIN MWORKCENTER I ON A.WORKCENTERID = I.WORKCENTERID
            INNER JOIN MEMPLOYEE J ON J.EMPLOYEEID = A.INCHARGEID
            INNER JOIN MITEMCATEGORY CAT ON C.CATEGORYID = CAT.ITEMCATEGORYID
            WHERE  1 = 1
            AND    A.PRODUCTIONDATE >= @periodfromdate
            AND    A.PRODUCTIONDATE <= @periodtodate
            AND    (@indentid = -1 OR E.INDENTID = @indentid)
            AND    (@itemid = -1 OR B.ITEMID = @itemid)
            AND    (@skuid = -1 OR B.SKUID = @skuid)
            AND    (@processid = -1 OR F.PROCESSID = @processid)
            AND    (@workcenterid = -1 OR A.WORKCENTERID = @workcenterid)
            AND    (@employeeid = -1 OR A.INCHARGEID = @employeeid)
            AND    (@ouid = -1 OR A.OUID = @ouid)
            GROUP BY CAT.ITEMCATEGORYID, CAT.ITEMCATEGORYCODE, CAT.ITEMCATEGORYNAME, B.ITEMID, C.ITEMCODE, C.ITEMNAME, B.SKUID, D.SKUCODE, D.SKUNAME";

        public const string BASE_ITEMCATEGORY_COUNT = @"
            SELECT COUNT(*)
            FROM   TPRODUCTION A
            INNER JOIN TPRODUCTIONDETAIL B ON A.PRODUCTIONID = B.PRODUCTIONID
            INNER JOIN MITEM C ON B.ITEMID = C.ITEMID
            INNER JOIN MSKU D ON B.SKUID = D.SKUID
            INNER JOIN TINDENTDETAIL E ON E.INDENTDETAILID = B.INDENTDETAILID
            INNER JOIN TINDENT H ON E.INDENTID = H.INDENTID
            INNER JOIN MPROCESS F ON H.PROCESSID = F.PROCESSID
            INNER JOIN MUOM G ON B.UOMID = G.UOMID
            INNER JOIN MWORKCENTER I ON A.WORKCENTERID = I.WORKCENTERID
            INNER JOIN MEMPLOYEE J ON J.EMPLOYEEID = A.INCHARGEID
            INNER JOIN MITEMCATEGORY CAT ON C.CATEGORYID = CAT.ITEMCATEGORYID
            WHERE  1 = 1
            AND    A.PRODUCTIONDATE >= @periodfromdate
            AND    A.PRODUCTIONDATE <= @periodtodate
            AND    (@indentid = -1 OR E.INDENTID = @indentid)
            AND    (@itemid = -1 OR B.ITEMID = @itemid)
            AND    (@skuid = -1 OR B.SKUID = @skuid)
            AND    (@processid = -1 OR F.PROCESSID = @processid)
            AND    (@workcenterid = -1 OR A.WORKCENTERID = @workcenterid)
            AND    (@employeeid = -1 OR A.INCHARGEID = @employeeid)
            AND    (@ouid = -1 OR A.OUID = @ouid);";
    }
}