using MMDAL.Query.Item;
using NPOI.SS.Formula.Functions;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static NPOI.HSSF.Util.HSSFColor;

namespace MMDAL.Query.BOM
{
    // Required indexes:
    //   MBOMDetail: (ITEMID, SKUID, PROCESSID) INCLUDE (BOMID, BOMLINEID, SLNO, QUANTITY, UOMID)
    //   MBOMDetail: (BOMID) INCLUDE (BOMLINEID, ITEMID, SKUID, QUANTITY, PERCENTAGE)
    //   MBOM: (BOMID) INCLUDE (PRODUCTIONITEMID, FROMDATE, TODATE, VERSIONNAME)
    //   MItem: (ITEMID) INCLUDE (ITEMCODE, ITEMNAME)          -- covers ItemId, SubItemId, ProductionItemId JOINs
    //   TSTOCKPOSITION: (STOREID, ITEMID, OUID, SKUID) INCLUDE (QUANTITY)
    public static class BOMQB
    {
        // GB4 parity: merges BOM.svc GetSelectListBOM (Type: Product=0/Order=1/Consumable=2,
        // criteria-driven) and GetSelectListBOMNew (Code/Name generic search) into one query.
        // Type=0/2 path — CriteriaDTO fields (Name, Code, DefaultBOMId, ProductionItemId) are
        // applied generically via CriteriaBuilder/{DYNAMIC_WHERE}; BOMTYPE/tenant/legacy
        // BOMProductionItemId-list/invsearch filters are appended explicitly in BOMDAL since
        // they aren't expressible as single-field criteria.
        public const string GET_SELECTLISTBOM = @"
   SELECT
    MB.BOMID            AS Id,
    MB.VERSIONNAME      AS VersionName,
    MB.BOMCODE          AS Code,
    MB.BOMNAME          AS Name,
    MB.DEFAULTBOMID     AS DefaultBOMId,
    MB.PRODUCTIONITEMID AS ProductionItemId,
    MI.ITEMCODE         AS ProductionItemCode,
    MI.ITEMNAME         AS ProductionItemName
FROM MBOM MB
LEFT JOIN MITEM MI
    ON MB.PRODUCTIONITEMID = MI.ITEMID
WHERE 1=1
{DYNAMIC_WHERE}
";

        // GB4 parity: BOM_SELECTLIST_SALESORDERNEW (Type=1, order-linked BOM lookup).
        // BOMTYPE flips to 1 only when a prior sales-order BOM already exists for the
        // same ProductionItemId/LinkId/LinkDetailId — otherwise falls back to the
        // production/default BOM (BOMTYPE=0).
        public const string GET_SELECTLISTBOM_SALESORDER = @"
SELECT
    MB.BOMID       AS Id,
    MB.VERSIONNAME AS VersionName,
    MB.BOMCODE     AS Code,
    MB.BOMNAME     AS Name
FROM MBOM MB
OUTER APPLY (
    SELECT COUNT(*) AS SaleNum
    FROM MBOM
    WHERE PRODUCTIONITEMID = MB.PRODUCTIONITEMID
      AND BOMTYPE = @Type
      AND LINKID = @LinkId
      AND LINKDETAILID = @LinkDetailId
) B
WHERE CASE WHEN ISNULL(B.SaleNum, 0) = 0 THEN 0 ELSE 1 END = MB.BOMTYPE
  AND MB.PRODUCTIONITEMID = @BOMProductionItemId;
";

        public const string GET_BOMDETAILS_FOR_TRANSACTION = @"
SELECT T.*
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY bomdetail.BOMLINEID) AS RowNum,
           bomdetail.BOMLINEID AS BOMDetailLineId,
           bomdetail.BOMId AS BOMDetailBOMId,
           bom.PRODUCTIONITEMID AS BOMDetailBOMBOMProductionItemId,
           proditem.ItemName AS BOMDetailBOMBOMProductionItemName,
           bomdetail.SlNo AS BOMDetailSlNo,
           bomdetail.ProductionStage AS BOMDetailProductionStage,
           bomdetail.ProductionProcessId AS BOMDetailProductionProcessId,
           bomdetail.ItemId AS BOMDetailItemId,
           item.ItemCode AS BOMDetailItemCode,
           item.ItemName AS BOMDetailItemName,
           bomdetail.SubItemId AS SubItemId,
           subitem.ItemCode AS SubItemCode,
           subitem.ItemName AS SubItemName,
           bomdetail.ProcessId AS BOMDetailProcessId,
           process.ProcessName AS BOMDetailProcessName,
           bomdetail.Quantity AS BOMDetailQuantity,
           bomdetail.Percentage AS BOMDetailPercentage,
           bomdetail.UOMId AS BOMDetailUOMId,
           uom.UOMCode AS BOMDetailUOMCode,
           uom.UOMName AS BOMDetailUOMName,
           bomdetail.SKUId AS SKUId,
           sku.SKUName AS SKUName,
           (bomdetail.FinalQuantity + bomdetail.IssuedQuantity) AS BOMDetailFinalQuantity,
           bom.FromDate AS FromDate,
           bom.ToDate AS ToDate,
           bom.VersionName AS BOMVersionName
    FROM MBOM bom
    INNER JOIN MBOMDetail bomdetail ON bom.BOMId = bomdetail.BOMId
    LEFT JOIN MItem item ON bomdetail.ItemId = item.ItemId
    LEFT JOIN MItem subitem ON bomdetail.SubItemId = subitem.ItemId
    LEFT JOIN MProcess process ON bomdetail.ProcessId = process.ProcessId
    LEFT JOIN MUOM uom ON bomdetail.UOMId = uom.UOMId
    LEFT JOIN MItem proditem ON bom.PRODUCTIONITEMID = proditem.ItemId
    LEFT JOIN MSKU sku ON bomdetail.SKUId = sku.SKUId
    WHERE (bomdetail.FinalQuantity + bomdetail.IssuedQuantity) > 0
      AND (@ItemId IS NULL OR bomdetail.ItemId = @ItemId)
      AND (@SKUId IS NULL OR bomdetail.SKUId = @SKUId)
      AND (@BOMDetailProcessId IS NULL OR bomdetail.ProcessId = @BOMDetailProcessId)
) AS T
WHERE
(@firstnumber = -1 AND @maxresult = -1)
OR (T.RowNum BETWEEN @firstnumber AND @maxresult)
ORDER BY T.RowNum;
";

        public const string GET_STOCK_POSITION_QTY_FOR_BOM = @"
SELECT Quantity AS StockPositionQuantity
FROM TSTOCKPOSITION
WHERE (@StoreId IS NULL OR StoreId = @StoreId)
  AND (@ItemId IS NULL OR ItemId = @ItemId)
  AND (@OUId IS NULL OR OUId = @OUId)
  AND (@SKUId IS NULL OR SKUId = @SKUId);
";

        public const string GET_BOM = @"
 
SELECT
 
-- =====================================================
-- BOM HEADER
-- =====================================================
B.BOMID                             AS BOMId,
B.BOMTYPE                           AS BOMType,
B.LINKID                            AS BOMLinkId,
B.PRODUCTIONITEMID                  AS BOMProductionItemId,
PI.ITEMCODE                         AS BOMProductionItemCode,
PI.ITEMNAME                         AS BOMProductionItemName,
B.VERSIONNAME                       AS BOMVersionName,
B.ISDEFAULTVERSION                  AS BOMIsDefaultVersion,
B.PRODUCTIONQTY                     AS BOMProductionQty,
B.ISCHANGEABLE                      AS BOMIsChangeable,
 
-- FK: MBOM.LINKTYPEID → MENTITY.ENTITYID
B.LINKTYPEID                        AS LinkTypeId,
LTET.ENTITYCODE                     AS LinkTypeCode,
LTET.ENTITYNAME                     AS LinkTypeName,
 
B.UOMID                             AS UOMId,
-- FK: MBOM.UOMID → MUOM.UOMID
BU.UOMCODE                          AS UOMCode,
BU.UOMNAME                          AS UOMName,
 
B.FROMDATE                          AS BOMFromDate,
B.TODATE                            AS BOMToDate,
B.ISPROCESSLEVEL                    AS BOMIsProcessLevel,
 
-- FK: MBOM.ROUTINGID → MROUTING.ROUTINGID
B.ROUTINGID                         AS RoutingId,
RT.ROUTINGCODE                      AS RoutingCode,
RT.ROUTINGNAME                      AS RoutingName,
 
B.WASTEPERCENTAGE                   AS BOMWastePercentage,
B.REQUIREDPRODUCTIONQUANTITY        AS BOMRequiredProductionQuantity,
B.REMARKS                           AS BOMRemarks,
B.SORTORDER                         AS BOMSortOrder,
B.STATUS                            AS BOMStatus,
B.VERSION                           AS BOMVersion,
B.BOMCODE                           AS BOMCode,
B.BOMNAME                           AS BOMName,
 
-- FK: MBOM.LINKDETAILID → TMMDETAIL.DOCUMENTDETAILID
B.LINKDETAILID                      AS LinkDetailId,
 
-- FK: MBOM.LINKDETAILTYPEID → MENTITY.ENTITYID
B.LINKDETAILTYPEID                  AS LinkDetailTypeId,
LDET.ENTITYCODE                     AS LinkDetailTypeCode,
LDET.ENTITYNAME                     AS LinkDetailTypeName,
 
-- FK: MBOM.DEFAULTBOMID → MBOM.BOMID (self-reference)
B.DEFAULTBOMID                      AS DefaultBOMId,
DB.BOMCODE                          AS DefaultBOMCode,
DB.BOMNAME                          AS DefaultBOMName,
 
B.CREATEDBYID                       AS BOMCreatedById,
B.CREATEDON                         AS BOMCreatedOn,
B.MODIFIEDBYID                      AS BOMModifiedById,
B.MODIFIEDON                        AS BOMModifiedOn,
 
-- =====================================================
-- BOM DETAIL
-- =====================================================
D.BOMLINEID                         AS BOMDetailLineId,
D.BOMID                             AS BOMDetailBOMId,
D.SLNO                              AS BOMDetailSlNo,
D.PRODUCTIONSTAGE                   AS BOMDetailProductionStage,
 
-- FK: MBOMDETAIL.PRODUCTIONPROCESSID → MPROCESS.PROCESSID
D.PRODUCTIONPROCESSID               AS BOMDetailProductionProcessId,
PP.PROCESSCODE                      AS BOMDetailProductionProcessCode,
PP.PROCESSNAME                      AS BOMDetailProductionProcessName,
 
D.MATERIALTYPE                      AS BOMDetailMaterialType,
D.INCLUDEEXCLUDETYPE                AS BOMDetailIncludeExcludeType,
 
-- FK: MBOMDETAIL.ITEMID → MITEM.ITEMID
D.ITEMID                            AS BOMDetailItemId,
DI.ITEMCODE                         AS BOMDetailItemCode,
DI.ITEMNAME                         AS BOMDetailItemName,
 
-- SUBITEMID has NO FK in the provided list — kept as raw ID only
D.SUBITEMID                         AS SubItemId,
SI.ITEMCODE                         AS SubItemCode,
SI.ITEMNAME                         AS SubItemName,
 
-- FK: MBOMDETAIL.PROCESSID → MPROCESS.PROCESSID
D.PROCESSID                         AS BOMDetailProcessId,
DP.PROCESSCODE                      AS BOMDetailProcessCode,
DP.PROCESSNAME                      AS BOMDetailProcessName,
 
D.QUANTITY                          AS BOMDetailQuantity,
D.PERCENTAGE                        AS BOMDetailPercentage,
 
-- FK: MBOMDETAIL.UOMID → MUOM.UOMID
D.UOMID                             AS BOMDetailUOMId,
DU.UOMCODE                          AS BOMDetailUOMCode,
DU.UOMNAME                          AS BOMDetailUOMName,
 
D.ISCHANGEABLE                      AS BOMDetailIsChangeable,
D.BOMLEVEL                          AS BOMDetailLevel,
D.PRODUCTIONSTEP                    AS BOMDetailProductionStep,
D.IOTYPE                            AS BOMDetailIOType,
CASE D.IOTYPE
    WHEN 0 THEN 'OUTPUT'
    WHEN 1 THEN 'INPUT'
    WHEN 2 THEN 'WASTE'
END                                 AS BOMDetailIOTypeName,
D.MAKEORBUY                         AS BOMDetailMakeOrBuy,
 
-- FK: MBOMDETAIL.SKUID → MSKU.SKUID
D.SKUID                             AS BOMDetailSKUId,
DS.SKUCODE                          AS BOMDetailSKUCode,
DS.SKUNAME                          AS BOMDetailSKUName,
 
D.CALCULATIONTYPE                   AS BOMDetailCalculationType,
D.UNITQUANTITYFORMULAID             AS BOMDetailUnitQuantityFormulaId,
D.PERPRODUCTIONQUANTITYFORMULAID    AS BOMDetailPerProductionQuantityFormulaId,
D.PERPRODUCTIONQUANTITY             AS BOMDetailPerProductionQuantity,
 
-- FK: MBOMDETAIL.PRODUCTIONUOMID → MUOM.UOMID
D.PRODUCTIONUOMID                   AS ProductionUOMId,
PU.UOMCODE                          AS ProductionUOMCode,
PU.UOMNAME                          AS ProductionUOMName,
 
D.REQUIREDPRODUCTIONQUANTITY        AS BOMDetailRequiredProductionQuantity,
D.CALCULATEDQUANTITY                AS BOMDetailCalculatedQuantity,
D.WASTEPERCENTAGE                   AS BOMDetailWastePercentage,
D.NETQUANTITY                       AS BOMDetailNetQuantity,
D.ADJUSTQUANTITY                    AS BOMDetailAdjustQuantity,
D.FINALQUANTITY                     AS BOMDetailFinalQuantity,
D.ISSUEDQUANTITY                    AS BOMDetailIssuedQuantity,
 
-- FK: MBOMDETAIL.PARAMETERSETID → MPARAMETERSET.PARAMETERSETID
D.PARAMETERSETID                    AS ParameterSetId,
PS.PARAMETERSETCODE                 AS ParameterSetCode,
PS.PARAMETERSETNAME                 AS ParameterSetName,
 
-- FK: MBOMDETAIL.CATEGORYID → MITEMCATEGORY.ITEMCATEGORYID
D.CATEGORYID                        AS BOMDetailCategoryId,
IC.ITEMCATEGORYCODE                 AS BOMDetailCategoryCode,
IC.ITEMCATEGORYNAME                 AS BOMDetailCategoryName,
 
-- FK: MBOMDETAIL.SUBCATEGORYID → MITEMSUBCATEGORY.ITEMSUBCATEGORYID
D.SUBCATEGORYID                     AS BOMDetailSubCategoryId,
ISC.ITEMSUBCATEGORYCODE             AS BOMDetailSubCategoryCode,
ISC.ITEMSUBCATEGORYNAME             AS BOMDetailSubCategoryName,
 
-- FK: MBOMDETAIL.ITEMGROUPID → MITEMGROUP.ITEMGROUPID
D.ITEMGROUPID                       AS BOMDetailItemGroupId,
IG.ITEMGROUPCODE                    AS BOMDetailItemGroupCode,
IG.ITEMGROUPNAME                    AS BOMDetailItemGroupName,
 
D.LEVELCODE                         AS BOMDetailLevelCode,
D.DEFAULTLOCATIONTYPE               AS BOMDetailDefaultLocationType,
 
-- FK: MBOMDETAIL.PREVIOUSPARENTBOMLINEID → MBOMDETAIL.BOMLINEID (self-reference)
D.PREVIOUSPARENTBOMLINEID           AS PreviousParentBOMLineId,
PBD.BOMLINEID                       AS PreviousParentBOMLineRef,
 
D.D1                                AS BOMDetailD1,
D.D2                                AS BOMDetailD2,
D.D3                                AS BOMDetailD3,
D.D4                                AS BOMDetailD4,
D.D5                                AS BOMDetailD5,
D.PARTNUMBER                        AS BOMDetailPartNumber,
D.PARTNAME                          AS BOMDetailPartName,
D.PARTICULARS                       AS BOMDetailParticulars,
D.PARTDRAWINGNUMBER                 AS BOMDetailPartDrawingNumber,
 
-- FK: MBOMDETAIL.PARTMAINITEMID → MITEM.ITEMID
D.PARTMAINITEMID                    AS PartMainItemId,
PMI.ITEMCODE                        AS PartMainItemCode,
PMI.ITEMNAME                        AS PartMainItemName,
 
-- FK: MBOMDETAIL.PARTMAINSKUID → MSKU.SKUID
D.PARTMAINSKUID                     AS PartMainSKUId,
PMS.SKUCODE                         AS PartMainSKUCode,
PMS.SKUNAME                         AS PartMainSKUName,
 
-- =====================================================
-- BOM DETAIL PARAMETER
-- =====================================================
P.BOMDETAILPARAMETERID              AS BOMDetailParameterId,
P.BOMLINEID                         AS BOMDetailParameterBOMLineId,
P.SLNO                              AS BOMDetailParameterSlNo,
 
-- FK: MBOMDETAILPARAMETER.PARAMETERID → MPARAMETER.PARAMETERID
P.PARAMETERID                       AS ParameterId,
PAR.PARAMETERCODE                   AS ParameterCode,
PAR.PARAMETERNAME                   AS ParameterName,
 
P.PARAMETERTYPE                     AS BOMDetailParameterType,
P.PARAMETERINPUTTYPE                AS BOMDetailParameterInputType,
P.PARAMETERVALUE                    AS BOMDetailParameterValue,
P.FINALVALUE                        AS BOMDetailParameterFinalValue
 
FROM MBOM B
 
-- ─── BOM Header FKs ─────────────────────────────────────────────────────────
 
-- Production Item  (MBOM.PRODUCTIONITEMID → MITEM.ITEMID)
LEFT JOIN MITEM PI
       ON PI.ITEMID = B.PRODUCTIONITEMID
 
-- BOM UOM  (MBOM.UOMID → MUOM.UOMID)
LEFT JOIN MUOM BU
       ON BU.UOMID = B.UOMID
 
-- Routing  (MBOM.ROUTINGID → MROUTING.ROUTINGID)
LEFT JOIN MROUTING RT
       ON RT.ROUTINGID = B.ROUTINGID
 
-- LinkType entity  (MBOM.LINKTYPEID → MENTITY.ENTITYID)
LEFT JOIN MENTITY LTET
       ON LTET.ENTITYID = B.LINKTYPEID
 
-- LinkDetailType entity  (MBOM.LINKDETAILTYPEID → MENTITY.ENTITYID)
LEFT JOIN MENTITY LDET
       ON LDET.ENTITYID = B.LINKDETAILTYPEID
 
-- Default BOM (self-reference)  (MBOM.DEFAULTBOMID → MBOM.BOMID)
LEFT JOIN MBOM DB
       ON DB.BOMID = B.DEFAULTBOMID
 
-- ─── BOM Detail ─────────────────────────────────────────────────────────────
 
LEFT JOIN MBOMDETAIL D
       ON D.BOMID = B.BOMID
 
-- Detail Item  (MBOMDETAIL.ITEMID → MITEM.ITEMID)
LEFT JOIN MITEM DI
       ON DI.ITEMID = D.ITEMID
 
-- Sub Item  (SUBITEMID has no FK listed — safe left join)
LEFT JOIN MITEM SI
       ON SI.ITEMID = D.SUBITEMID
 
-- Detail UOM  (MBOMDETAIL.UOMID → MUOM.UOMID)
LEFT JOIN MUOM DU
       ON DU.UOMID = D.UOMID
 
-- Production UOM  (MBOMDETAIL.PRODUCTIONUOMID → MUOM.UOMID)
LEFT JOIN MUOM PU
       ON PU.UOMID = D.PRODUCTIONUOMID
 
-- Detail SKU  (MBOMDETAIL.SKUID → MSKU.SKUID)
LEFT JOIN MSKU DS
       ON DS.SKUID = D.SKUID
 
-- Detail Process  (MBOMDETAIL.PROCESSID → MPROCESS.PROCESSID)
LEFT JOIN MPROCESS DP
       ON DP.PROCESSID = D.PROCESSID
 
-- Production Process  (MBOMDETAIL.PRODUCTIONPROCESSID → MPROCESS.PROCESSID)
LEFT JOIN MPROCESS PP
       ON PP.PROCESSID = D.PRODUCTIONPROCESSID
 
-- Parameter Set  (MBOMDETAIL.PARAMETERSETID → MPARAMETERSET.PARAMETERSETID)
LEFT JOIN MPARAMETERSET PS
       ON PS.PARAMETERSETID = D.PARAMETERSETID
 
-- Item Category  (MBOMDETAIL.CATEGORYID → MITEMCATEGORY.ITEMCATEGORYID)
LEFT JOIN MITEMCATEGORY IC
       ON IC.ITEMCATEGORYID = D.CATEGORYID
 
-- Item Sub Category  (MBOMDETAIL.SUBCATEGORYID → MITEMSUBCATEGORY.ITEMSUBCATEGORYID)
LEFT JOIN MITEMSUBCATEGORY ISC
       ON ISC.ITEMSUBCATEGORYID = D.SUBCATEGORYID
 
-- Item Group  (MBOMDETAIL.ITEMGROUPID → MITEMGROUP.ITEMGROUPID)
LEFT JOIN MITEMGROUP IG
       ON IG.ITEMGROUPID = D.ITEMGROUPID
 
-- Part Main Item  (MBOMDETAIL.PARTMAINITEMID → MITEM.ITEMID)
LEFT JOIN MITEM PMI
       ON PMI.ITEMID = D.PARTMAINITEMID
 
-- Part Main SKU  (MBOMDETAIL.PARTMAINSKUID → MSKU.SKUID)
LEFT JOIN MSKU PMS
       ON PMS.SKUID = D.PARTMAINSKUID
 
-- Previous Parent BOM Line (self-reference)
-- (MBOMDETAIL.PREVIOUSPARENTBOMLINEID → MBOMDETAIL.BOMLINEID)
LEFT JOIN MBOMDETAIL PBD
       ON PBD.BOMLINEID = D.PREVIOUSPARENTBOMLINEID
 
-- ─── BOM Detail Parameter FKs ───────────────────────────────────────────────
 
LEFT JOIN MBOMDETAILPARAMETER P
       ON P.BOMLINEID = D.BOMLINEID
 
-- Parameter  (MBOMDETAILPARAMETER.PARAMETERID → MPARAMETER.PARAMETERID)
LEFT JOIN MPARAMETER PAR
       ON PAR.PARAMETERID = P.PARAMETERID
 
WHERE B.BOMID = @BOMId
 
ORDER BY
    B.BOMID,
    D.BOMLINEID,
    P.BOMDETAILPARAMETERID;";
        // ── BOM Header ──────────────────────────────────────────
        public const string SAVE_BOM = @"
INSERT INTO MBOM (
    BOMID,
    BOMTYPE,
    LINKID,
    PRODUCTIONITEMID,
    VERSIONNAME,
    ISDEFAULTVERSION,
    PRODUCTIONQTY,
    ISCHANGEABLE,
    VERSION,
    STATUS,
    SORTORDER,
    LINKTYPEID,
    UOMID,
    FROMDATE,
    TODATE,
    ISPROCESSLEVEL,
    ROUTINGID,
    WASTEPERCENTAGE,
    REQUIREDPRODUCTIONQUANTITY,
    REMARKS,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SOURCETYPE,
    TENANTID,
    BOMCODE,
    BOMNAME,
    LINKDETAILID,
    LINKDETAILTYPEID,
    DEFAULTBOMID
)
VALUES (
    @BOMId,
    @BOMType,
    @BOMLinkId,
    @BOMProductionItemId,
    @BOMVersionName,
    @BOMIsDefaultVersion,
    @BOMProductionQty,
    @BOMIsChangeable,
    @BOMVersion,
    @BOMStatus,
    @BOMSortOrder,
    @LinkTypeId,
    @UOMId,
    @BOMFromDate,
    @BOMToDate,
    @BOMIsProcessLevel,
    @RoutingId,
    @BOMWastePercentage,
    @BOMRequiredProductionQuantity,
    @BOMRemarks,
    @BOMCreatedById,
    @BOMCreatedOn,
    @BOMModifiedById,
    @BOMModifiedOn,
    5,
    -1,
    @BOMCode,
    @BOMName,
    @LinkDetailId,
    @LinkDetailTypeId,
    @DefaultBOMId
);";

            public const string UPDATE_BOM = @"
UPDATE MBOM
SET
    BOMTYPE                    = @BOMType,
    LINKID                     = @BOMLinkId,
    PRODUCTIONITEMID           = @BOMProductionItemId,
    VERSIONNAME                = @BOMVersionName,
    ISDEFAULTVERSION           = @BOMIsDefaultVersion,
    PRODUCTIONQTY              = @BOMProductionQty,
    ISCHANGEABLE               = @BOMIsChangeable,
    VERSION                    = @BOMVersion,
    STATUS                     = @BOMStatus,
    SORTORDER                  = @BOMSortOrder,
    LINKTYPEID                 = @LinkTypeId,
    UOMID                      = @UOMId,
    FROMDATE                   = @BOMFromDate,
    TODATE                     = @BOMToDate,
    ISPROCESSLEVEL             = @BOMIsProcessLevel,
    ROUTINGID                  = @RoutingId,
    WASTEPERCENTAGE            = @BOMWastePercentage,
    REQUIREDPRODUCTIONQUANTITY = @BOMRequiredProductionQuantity,
    REMARKS                    = @BOMRemarks,
    MODIFIEDBYID               = @BOMModifiedById,
    MODIFIEDON                 = @BOMModifiedOn,
    BOMCODE                    = @BOMCode,
    BOMNAME                    = @BOMName,
    LINKDETAILID               = @LinkDetailId,
    LINKDETAILTYPEID           = @LinkDetailTypeId,
    DEFAULTBOMID               = @DefaultBOMId
WHERE BOMID = @BOMId;";

            // ── BOM Version Checks ───────────────────────────────────
            public const string GET_BOM_PREVIOUS_VERSION = @"
SELECT TOP 1
    B.BOMID       AS BOMId,
    B.VERSIONNAME AS BOMVersionName,
    B.FROMDATE    AS BOMFromDate
FROM MBOM B
WHERE B.PRODUCTIONITEMID = @BOMProductionItemId
  AND B.DEFAULTBOMID     = @DefaultBOMId
  AND B.FROMDATE         < @BOMFromDate
  AND B.BOMTYPE          = 0
ORDER BY B.FROMDATE DESC;";

            public const string GET_BOMLINKTYPE_PREVIOUS_VERSION = @"
SELECT TOP 1
    B.BOMID       AS BOMId,
    B.VERSIONNAME AS BOMVersionName,
    B.FROMDATE    AS BOMFromDate
FROM MBOM B
WHERE B.PRODUCTIONITEMID = @BOMProductionItemId
  AND B.DEFAULTBOMID     = @DefaultBOMId
  AND B.LINKID           = @BOMLinkId
  AND B.FROMDATE         < @BOMFromDate
  AND B.BOMTYPE          = 1
ORDER BY B.FROMDATE DESC;";

            public const string UPDATE_TO_DATE_FOR_BOM = @"
UPDATE MBOM
SET TODATE = DATEADD(DAY, -1, @BOMFromDate)
WHERE PRODUCTIONITEMID = @BOMProductionItemId
  AND DEFAULTBOMID     = @DefaultBOMId
  AND BOMTYPE          = 0
  AND FROMDATE         < @BOMFromDate
  AND TODATE           = '9999-12-31';";

            public const string UPDATE_TO_DATE_FOR_BOMLINKTYPE = @"
UPDATE MBOM
SET TODATE = DATEADD(DAY, -1, @BOMFromDate)
WHERE PRODUCTIONITEMID = @BOMProductionItemId
  AND DEFAULTBOMID     = @DefaultBOMId
  AND LINKID           = @BOMLinkId
  AND BOMTYPE          = 1
  AND FROMDATE         < @BOMFromDate
  AND TODATE           = '9999-12-31';";

            // ── Item Lookup ───────────────────────────────────────────
            public const string GET_ITEM_BY_ID = @"
SELECT ITEMID AS ItemId,
       ITEMCODE AS ItemCode,
       ITEMNAME AS ItemName
FROM MITEM
WHERE ITEMID = @ItemId;";

        // ── BOM Detail ───────────────────────────────────────────
        public const string SAVE_BOM_DETAIL = @"
INSERT INTO MBOMDETAIL (
    BOMLINEID,
    BOMID,
    SLNO,
    PRODUCTIONSTAGE,
    PRODUCTIONPROCESSID,
    MATERIALTYPE,
    INCLUDEEXCLUDETYPE,
    ITEMID,
    SUBITEMID,
    PROCESSID,
    QUANTITY,
    PERCENTAGE,
    UOMID,
    ISCHANGEABLE,
    BOMLEVEL,
    PRODUCTIONSTEP,
    IOTYPE,
    MAKEORBUY,
    SKUID,
    CALCULATIONTYPE,
    UNITQUANTITYFORMULAID,
    PERPRODUCTIONQUANTITYFORMULAID,
    PERPRODUCTIONQUANTITY,
    PRODUCTIONUOMID,
    REQUIREDPRODUCTIONQUANTITY,
    CALCULATEDQUANTITY,
    WASTEPERCENTAGE,
    NETQUANTITY,
    ADJUSTQUANTITY,
    FINALQUANTITY,
    PARAMETERSETID,
    CATEGORYID,
    SUBCATEGORYID,
    ITEMGROUPID,
    ISSUEDQUANTITY,
    LEVELCODE,
    DEFAULTLOCATIONTYPE,
    PREVIOUSPARENTBOMLINEID,
    D1,
    D2,
    D3,
    D4,
    D5,
    PARTNUMBER,
    PARTNAME,
    PARTICULARS,
    PARTDRAWINGNUMBER,
    PARTMAINITEMID,
    PARTMAINSKUID
)
VALUES (
    @BOMDetailLineId,
    @BOMDetailBOMId,
    @BOMDetailSlNo,
    @BOMDetailProductionStage,
    @BOMDetailProductionProcessId,
    @BOMDetailMaterialType,
    @BOMDetailIncludeExcludeType,
    @BOMDetailItemId,
    @SubItemId,
    @BOMDetailProcessId,
    @BOMDetailQuantity,
    @BOMDetailPercentage,
    @BOMDetailUOMId,
    @BOMDetailIschangeable,
    @BOMDetailLevel,
    @BOMDetailProductionStep,
    @BOMDetailIOType,
    @BOMDetailMakeOrBuy,
    @SKUId,
    @BOMDetailCalculationType,
    @BOMDetailUnitQuantityFormulaId,
    @BOMDetailPerProductionQuantityFormulaId,
    @BOMDetailPerProductionQuantity,
    @ProductionUOMId,
    @BOMDetailRequiredProductionQuantity,
    @BOMDetailCalculatedQuantity,
    @BOMDetailWastePercentage,
    @BOMDetailNetQuantity,
    @BOMDetailAdjustQuantity,
    @BOMDetailFinalQuantity,
    @ParameterSetId,
    @CategoryId,
    @SubCategoryId,
    @ItemGroupId,
    @BOMDetailIssuedQuantity,
    @BOMDetailLevelCode,
    @BOMDetailDefaultLocationType,
    @PreviousParentBOMLineId,
    @BOMDetailD1,
    @BOMDetailD2,
    @BOMDetailD3,
    @BOMDetailD4,
    @BOMDetailD5,
    @BOMDetailPartNumber,
    @BOMDetailPartName,
    @BOMDetailParticulars,
    @BOMDetailPartDrawingNumber,
    @PartMainItemId,
    @PartMainSkuId
);";

        public const string UPDATE_BOM_DETAIL = @"
UPDATE MBOMDETAIL
SET
    BOMID                       = @BOMDetailBOMId,
    SLNO                        = @BOMDetailSlNo,
    PRODUCTIONSTAGE             = @BOMDetailProductionStage,
    PRODUCTIONPROCESSID          = @BOMDetailProductionProcessId,
    MATERIALTYPE                = @BOMDetailMaterialType,
    INCLUDEEXCLUDETYPE          = @BOMDetailIncludeExcludeType,
    ITEMID                       = @BOMDetailItemId,
    SUBITEMID                    = @SubItemId,
    PROCESSID                    = @BOMDetailProcessId,
    QUANTITY                     = @BOMDetailQuantity,
    PERCENTAGE                   = @BOMDetailPercentage,
    UOMID                        = @BOMDetailUOMId,
    ISCHANGEABLE                 = @BOMDetailIschangeable,
    BOMLEVEL                     = @BOMDetailLevel,
    PRODUCTIONSTEP               = @BOMDetailProductionStep,
    IOTYPE                       = @BOMDetailIOType,
    MAKEORBUY                     = @BOMDetailMakeOrBuy,
    SKUID                        = @SKUId,
    CALCULATIONTYPE              = @BOMDetailCalculationType,
    UNITQUANTITYFORMULAID        = @BOMDetailUnitQuantityFormulaId,
    PERPRODUCTIONQUANTITYFORMULAID = @BOMDetailPerProductionQuantityFormulaId,
    PERPRODUCTIONQUANTITY        = @BOMDetailPerProductionQuantity,
    PRODUCTIONUOMID              = @ProductionUOMId,
    REQUIREDPRODUCTIONQUANTITY   = @BOMDetailRequiredProductionQuantity,
    CALCULATEDQUANTITY           = @BOMDetailCalculatedQuantity,
    WASTEPERCENTAGE              = @BOMDetailWastePercentage,
    NETQUANTITY                  = @BOMDetailNetQuantity,
    ADJUSTQUANTITY               = @BOMDetailAdjustQuantity,
    FINALQUANTITY                = @BOMDetailFinalQuantity,
    PARAMETERSETID               = @ParameterSetId,
    CATEGORYID                   = @CategoryId,
    SUBCATEGORYID                = @SubCategoryId,
    ITEMGROUPID                  = @ItemGroupId,
    ISSUEDQUANTITY               = @BOMDetailIssuedQuantity,
    LEVELCODE                    = @BOMDetailLevelCode,
    DEFAULTLOCATIONTYPE          = @BOMDetailDefaultLocationType,
    PREVIOUSPARENTBOMLINEID      = @PreviousParentBOMLineId,
    D1                           = @BOMDetailD1,
    D2                           = @BOMDetailD2,
    D3                           = @BOMDetailD3,
    D4                           = @BOMDetailD4,
    D5                           = @BOMDetailD5,
    PARTNUMBER                   = @BOMDetailPartNumber,
    PARTNAME                     = @BOMDetailPartName,
    PARTICULARS                  = @BOMDetailParticulars,
    PARTDRAWINGNUMBER             = @BOMDetailPartDrawingNumber,
    PARTMAINITEMID               = @PartMainItemId,
    PARTMAINSKUID                = @PartMainSkuId
WHERE BOMLINEID = @BOMDetailLineId;";

        // ── BOM Detail Parameter ─────────────────────────────────
        public const string SAVE_BOM_DETAIL_PARAMETER = @"
INSERT INTO MBOMDETAILPARAMETER (
    BOMDETAILPARAMETERID,
    BOMLINEID,
    PARAMETERID,
    PARAMETERVALUE,
    FINALVALUE,
    SLNO,
    PARAMETERTYPE,
    PARAMETERINPUTTYPE
)
VALUES (
    @BOMDetailParameterId,
    @BOMDetailParameterBOMLineId,
    @ParameterId,
    @BOMDetailParameterValue,
    @BOMDetailParameterFinalValue,
    @BOMDetailParameterSlNo,
    @BOMDetailParaMeterType,
    @BOMDetailParaMeterInPutType
);";

        public const string UPDATE_BOM_DETAIL_PARAMETER = @"
UPDATE MBOMDETAILPARAMETER
SET
    BOMLINEID          = @BOMDetailParameterBOMLineId,
    PARAMETERID        = @ParameterId,
    PARAMETERVALUE     = @BOMDetailParameterValue,
    FINALVALUE         = @BOMDetailParameterFinalValue,
    SLNO               = @BOMDetailParameterSlNo,
    PARAMETERTYPE      = @BOMDetailParaMeterType,
    PARAMETERINPUTTYPE = @BOMDetailParaMeterInPutType
WHERE BOMDETAILPARAMETERID = @BOMDetailParameterId;";

        public const string DELETE_BOM = @"DELETE FROM MBOM WHERE BOMID = @BOMId";

        #region BOM Tree (reads this one BOM's own already-materialized MBOMDETAIL rows)
        // Was adapted from MRPRunQB.GET_BOM_EXPLOSION, which walks into each component's OWN
        // separate default BOM (a genuine multi-level MRP explosion need). That's wrong here:
        // MBOMDETAIL for a single BOMID is already fully expanded/materialized at save time
        // (PARENTBOMLINEID/BOMLEVEL/FINALQUANTITY etc. all pre-computed across every level) --
        // confirmed live, this view must never re-derive that dynamically. It also matched
        // subdet.PARENTBOMLINEID (scoped to the sub-BOM's own rows) against t.BOMLINEID (a row
        // from the unrelated outer BOM) -- a copy-paste defect that, combined with the cross-BOM
        // jump, was pulling in extra/garbage rows ("data is coming more"). Recursion here now
        // never leaves @BOMId's own MBOMDETAIL rows. If the BOM structure itself changes, that is
        // a separate SaveBOM/re-expansion posting, never something this read-only view computes.
        // Required covering index: MBOMDETAIL (BOMID, PARENTBOMLINEID) INCLUDE (BOMLINEID, SLNO, BOMLEVEL)
        public const string GET_BOM_TREE = @"
                            ;WITH NUMBERED_DETAILS AS
                                (
                                    SELECT
                                        det.*,

                                        ROW_NUMBER() OVER
                                        (
                                            PARTITION BY
                                                ISNULL(det.PARENTBOMLINEID, 0)
                                            ORDER BY
                                                det.SLNO,
                                                det.BOMLINEID
                                        ) AS SiblingNo

                                    FROM MBOMDETAIL det

                                    INNER JOIN MBOM bom
                                        ON bom.BOMID = det.BOMID
                                        AND bom.TENANTID = @TenantId

                                    WHERE det.BOMID = @BOMId
                                ),

                                BOM_TREE AS
                                (
                                    /* ============================================================
                                       ROOT LEVEL
                                       ============================================================ */

                                    SELECT

                                        det.BOMLINEID,
                                        det.PARENTBOMLINEID,

                                        /* Root Code */
                                        CAST(
                                            RIGHT(
                                                '000' + CAST(det.SiblingNo AS VARCHAR(10)),
                                                3
                                            )
                                            AS VARCHAR(900)
                                        ) AS Code,

                                        /*
                                           Parent should NOT be NULL.
                                           For root, parent = its own code.

                                           This matches the old GB4 behavior where root Parent
                                           becomes '001'.
                                        */
                                        CAST(
                                            RIGHT(
                                                '000' + CAST(det.SiblingNo AS VARCHAR(10)),
                                                3
                                            )
                                            AS VARCHAR(900)
                                        ) AS Parent,

                                        /*
                                           Use the actual BOMLEVEL from MBOMDETAIL.
                                        */
                                        CAST(
                                            det.BOMLEVEL AS SMALLINT
                                        ) AS BOMLevel

                                    FROM NUMBERED_DETAILS det

                                    /*
                                       Root details only.
                                    */
                                    WHERE
                                    (
                                        det.PARENTBOMLINEID IS NULL
                                        OR det.PARENTBOMLINEID = 0
                                        OR det.BOMLEVEL = 0
                                    )


                                    UNION ALL


                                    /* ============================================================
                                       CHILD LEVELS -- same BOM's own already-materialized rows only
                                       ============================================================ */

                                    SELECT

                                        subdet.BOMLINEID,

                                        subdet.PARENTBOMLINEID,

                                        /*
                                           Code = Parent Code + Child Sibling Number

                                           Example:

                                           Parent       Child
                                           001          001-001
                                           001          001-002
                                           001-001      001-001-001
                                        */
                                        CAST(
                                            t.Code
                                            + '-'
                                            + RIGHT(
                                                '000' + CAST(subdet.SiblingNo AS VARCHAR(10)),
                                                3
                                            )
                                            AS VARCHAR(900)
                                        ) AS Code,

                                        /*
                                           Parent is ALWAYS the immediate parent's Code.
                                        */
                                        CAST(
                                            t.Code
                                            AS VARCHAR(900)
                                        ) AS Parent,

                                        /*
                                           IMPORTANT:
                                           Use actual BOMLEVEL from MBOMDETAIL.
                                        */
                                        CAST(
                                            subdet.BOMLEVEL AS SMALLINT
                                        ) AS BOMLevel

                                    FROM BOM_TREE t

                                    INNER JOIN NUMBERED_DETAILS subdet
                                        ON subdet.PARENTBOMLINEID = t.BOMLINEID

                                    WHERE t.BOMLevel < @MaxRecursion
                                )


                                SELECT

                                    /* ============================================================
                                       HIERARCHY
                                       ============================================================ */

                                    bt.Code AS Code,

                                    /*
                                       Never NULL.
                                    */
                                    ISNULL(bt.Parent, bt.Code) AS Parent,

                                    /*
                                       Actual BOM level from MBOMDETAIL.
                                    */
                                    bt.BOMLevel AS Level,

                                    /* ============================================================
                                       BOM INFORMATION
                                       ============================================================ */

                                    bt.BOMLINEID AS BOMDetailId,

                                    det.BOMID AS BOMId,

                                    /* ============================================================
                                       ROOT ITEM (the item this whole BOM produces)
                                       ============================================================ */

                                    bom.PRODUCTIONITEMID AS ItemId,

                                    ISNULL(rootItem.ITEMCODE, '') AS ItemCode,

                                    ISNULL(rootItem.ITEMNAME, '') AS ItemName,

                                    /* Root item thumbnail */
                                    ISNULL(rootItem.THUMBNAIL, '') AS ThumbNail,

                                    /* ============================================================
                                       DETAIL ITEM
                                       ============================================================ */

                                    det.ITEMID AS DetailItemId,

                                    ISNULL(detailItem.ITEMCODE, '') AS DetailItemCode,

                                    ISNULL(detailItem.ITEMNAME, '') AS DetailItemName,

                                    ISNULL(detailItem.THUMBNAIL, '') AS DetailThumbNail,

                                    /* ============================================================
                                       ITEM UOM
                                       ============================================================ */

                                    /*
                                       FIX: ItemUOMName is this row's OWN component item's stock
                                       UOM, not the root/produced item's -- it was constant across
                                       every row of the tree before, which read as always-empty
                                       whenever the root item had no STOCKUOMID match.
                                    */
                                    ISNULL(detailItemUom.UOMID, 0) AS ItemUOMId,

                                    ISNULL(detailItemUom.UOMNAME, '') AS ItemUOMName,

                                    /* ============================================================
                                       DETAIL UOM (this BOM line's own transactional UOM)
                                       ============================================================ */

                                    ISNULL(detailUom.UOMID, 0) AS UOMId,

                                    ISNULL(detailUom.UOMNAME, '') AS UOMName,

                                    ISNULL(detailUom.NOOFDECIMALS, 0) AS UOMDecimals,

                                    /* ============================================================
                                       BOM DETAIL
                                       ============================================================ */

                                    det.SLNO AS SlNo,

                                    ISNULL(det.PRODUCTIONSTAGE, '') AS ProductionStage,

                                    ISNULL(det.PRODUCTIONSTEP, '') AS ProductionStep,

                                    det.PROCESSID AS ProcessId,

                                    ISNULL(processMaster.PROCESSNAME, '') AS ProcessName,

                                    /* ============================================================
                                       STAGE (tree depth -- used by the FE tree's own level
                                       styling; the real business stage text is ProductionStage
                                       above, kept as a separate field on purpose)
                                       ============================================================ */

                                    det.BOMLEVEL AS Stage,

                                    ISNULL(det.PRODUCTIONSTAGE, '') AS BomDetailProductionStage,

                                    /* ============================================================
                                       MATERIAL TYPE (0-ITEM,1-CATEGORY,2-SUBCATEGORY,3-ITEMGROUP,
                                       4-INFO,5-PART per MBOMDETAIL.MATERIALTYPE's own DDL comment
                                       -- INFO/PART were missing from this decode)
                                       ============================================================ */

                                    CASE det.MATERIALTYPE
                                        WHEN 0 THEN 'ITEM'
                                        WHEN 1 THEN 'CATEGORY'
                                        WHEN 2 THEN 'SUBCATEGORY'
                                        WHEN 3 THEN 'ITEMGROUP'
                                        WHEN 4 THEN 'INFO'
                                        WHEN 5 THEN 'PART'
                                        ELSE ''
                                    END AS MaterialType,

                                    det.CATEGORYID AS CategoryId,

                                    ISNULL(category.ITEMCATEGORYCODE, '') AS CategoryCode,

                                    ISNULL(category.ITEMCATEGORYNAME, '') AS CategoryName,

                                    det.SUBCATEGORYID AS SubCategoryId,

                                    ISNULL(subcategory.ITEMSUBCATEGORYCODE, '') AS SubCategoryCode,

                                    ISNULL(subcategory.ITEMSUBCATEGORYNAME, '') AS SubCategoryName,

                                    det.ITEMGROUPID AS ItemGroupId,

                                    ISNULL(itemgroup.ITEMGROUPCODE, '') AS ItemGroupCode,

                                    ISNULL(itemgroup.ITEMGROUPNAME, '') AS ItemGroupName,

                                    /* ============================================================
                                       PART
                                       ============================================================ */

                                    ISNULL(det.PARTNUMBER, '') AS PartNumber,

                                    ISNULL(det.PARTNAME, '') AS PartName,

                                    /* ============================================================
                                       QUANTITY -- FINALQUANTITY/PRODUCTIONQTY are already the
                                       fully-computed, already-materialized values for this row;
                                       no runtime proportional recompute needed now that this is a
                                       single-BOM read.
                                       ============================================================ */

                                    CAST(
                                        det.FINALQUANTITY
                                        AS FLOAT
                                    ) AS BOMDetailItemQuantity,

                                    CAST(
                                        det.FINALQUANTITY
                                        AS FLOAT
                                    ) AS RequiredQuantity,

                                    /*
                                       FIX: decimal, not double/FLOAT -- this is a real quantity
                                       figure, matching this repo's own decimal convention for
                                       quantity/financial fields.
                                    */
                                    CAST(
                                        bom.PRODUCTIONQTY
                                        AS DECIMAL(18,6)
                                    ) AS ProductionQuantity,

                                    CAST(
                                        bom.RequiredProductionQuantity
                                        AS FLOAT
                                    ) AS BOMQuantity,

                                    /* ============================================================
                                       LOCATION / MAKE-OR-BUY
                                       ============================================================ */

                                    CASE det.DEFAULTLOCATIONTYPE
                                        WHEN 0 THEN 'IN-HOUSE'
                                        WHEN 1 THEN 'ON-SITE'
                                        WHEN 2 THEN 'ANY'
                                        ELSE ''
                                    END AS Location,

                                    det.DEFAULTLOCATIONTYPE AS DefaultLocationType,

                                    /* ============================================================
                                       DIMENSIONS / REMARKS
                                       ============================================================ */

                                    det.D1 AS D1,
                                    det.D2 AS D2,
                                    det.D3 AS D3,
                                    det.D4 AS D4,
                                    det.D5 AS D5,

                                    ISNULL(det.PARTICULARS, '') AS Remarks,

                                    /* ============================================================
                                       PARENT ITEM
                                       ============================================================ */

                                    /*
                                       Find parent through PARENTBOMLINEID.

                                       FIX: -1 sentinel instead of raw NULL for root rows -- this
                                       codebase's own convention (see ParentItemId != -1 checks on
                                       the FE), and mapping a SQL NULL into this non-nullable int
                                       property was undefined behavior.
                                    */
                                    ISNULL(parentDet.ITEMID, -1) AS ParentItemId,

                                    ISNULL(parentItem.ITEMCODE, '') AS ParentItemCode,

                                    ISNULL(parentItem.ITEMNAME, '') AS ParentItemName,

                                    ISNULL(parentItem.THUMBNAIL, '') AS ParentThumbNail,

                                    ISNULL(parentUom.UOMNAME, '') AS ParentUomName,

                                    /* ============================================================
                                       MAKE / BUY
                                       ============================================================ */

                                    CASE
                                        WHEN det.MAKEORBUY = 0 THEN 'M'
                                        ELSE 'B'
                                    END AS MakeOrBuy,

                                    CASE
                                        WHEN detailItem.ITEMORTEMPLATE = 0 THEN 'I'
                                        ELSE 'T'
                                    END AS ItemOrTemplate,

                                    det.MAKEORBUY AS DetailItemMakeOrBuy,

                                    detailItem.ITEMORTEMPLATE AS DetailItemOrTemplate,

                                    /* ============================================================
                                       SKU
                                       ============================================================ */

                                    det.SKUID AS BomDetailSKUId,

                                    ISNULL(sku.SKUCODE, '') AS BomDetailSKUCode,

                                    ISNULL(sku.SKUNAME, '') AS BomDetailSKUName,

                                    det.SKUID AS SKUId,

                                    ISNULL(sku.SKUCODE, 'NONE') AS SKUCode,

                                    ISNULL(sku.SKUNAME, 'NONE') AS SKUName,

                                    /* ============================================================
                                       OTHER BOM DETAIL VALUES
                                       ============================================================ */

                                    det.BOMLINEID AS BOMDetailLineId,

                                    det.IOTYPE AS IOType,

                                    CASE det.IOTYPE
                                        WHEN 0 THEN 'OUTPUT'
                                        WHEN 1 THEN 'INPUT'
                                        WHEN 2 THEN 'WASTE'
                                        ELSE ''
                                    END AS IOTypeName,

                                    det.PERCENTAGE AS Percentage,

                                    det.WASTEPERCENTAGE AS WastePercentage,

                                    det.NETQUANTITY AS NetQuantity,

                                    det.ADJUSTQUANTITY AS AdjustQuantity,

                                    det.REQUIREDPRODUCTIONQUANTITY AS RequiredProductionQuantity,

                                    det.CALCULATEDQUANTITY AS CalculatedQuantity,

                                    det.PERPRODUCTIONQUANTITY AS PerProductionQuantity,

                                    det.PRODUCTIONUOMID AS ProductionUOMId

                                FROM BOM_TREE bt

                                INNER JOIN MBOMDETAIL det
                                    ON det.BOMLINEID = bt.BOMLINEID
                                    AND det.BOMID = @BOMId

                                INNER JOIN MBOM bom
                                    ON bom.BOMID = det.BOMID

                                /* ============================================================
                                   ROOT ITEM
                                   ============================================================ */

                                LEFT JOIN MITEM rootItem
                                    ON rootItem.ITEMID = bom.PRODUCTIONITEMID
                                    AND rootItem.TENANTID = @TenantId

                                /* ============================================================
                                   DETAIL ITEM
                                   ============================================================ */

                                LEFT JOIN MITEM detailItem
                                    ON detailItem.ITEMID = det.ITEMID
                                    AND detailItem.TENANTID = @TenantId

                                LEFT JOIN MUOM detailItemUom
                                    ON detailItemUom.UOMID = detailItem.STOCKUOMID

                                LEFT JOIN MUOM detailUom
                                    ON detailUom.UOMID = det.UOMID

                                /* ============================================================
                                   PROCESS
                                   ============================================================ */

                                LEFT JOIN MPROCESS processMaster
                                    ON processMaster.PROCESSID = det.PROCESSID

                                /* ============================================================
                                   PARENT ITEM

                                   Direct relationship:
                                   Current detail.PARENTBOMLINEID
                                       ↓
                                   Parent BOM detail
                                       ↓
                                   Parent ITEMID
                                   ============================================================ */

                                LEFT JOIN MBOMDETAIL parentDet
                                    ON parentDet.BOMLINEID = det.PARENTBOMLINEID
                                    AND parentDet.BOMID = @BOMId

                                LEFT JOIN MITEM parentItem
                                    ON parentItem.ITEMID = parentDet.ITEMID
                                    AND parentItem.TENANTID = @TenantId

                                LEFT JOIN MUOM parentUom
                                    ON parentUom.UOMID = parentItem.STOCKUOMID

                                /* ============================================================
                                   CATEGORY
                                   ============================================================ */

                                LEFT JOIN MITEMCATEGORY category
                                    ON category.ITEMCATEGORYID = det.CATEGORYID

                                LEFT JOIN MITEMSUBCATEGORY subcategory
                                    ON subcategory.ITEMSUBCATEGORYID = det.SUBCATEGORYID

                                LEFT JOIN MITEMGROUP itemgroup
                                    ON itemgroup.ITEMGROUPID = det.ITEMGROUPID

                                /* ============================================================
                                   SKU
                                   ============================================================ */

                                LEFT JOIN MSKU sku
                                    ON sku.SKUID = det.SKUID

                                ORDER BY
                                    bt.Code

                                OPTION (MAXRECURSION 32)
                            ";
        #endregion

        #region Cyclical BOM guard (real-time, BLL-invoked before every BOM/BOMDetail save)
        // Walks forward from each candidate component item's own active/default BOM chain; a cycle
        // exists if the walk ever reaches back to the current BOM's own production item.
        public const string GET_CYCLICAL_BOM_CHECK = @"
    ;WITH FORWARD_CTE AS (
        SELECT c.value                              AS RootComponentItemId,
               det.ITEMID                            AS DetailItemId,
               det.BOMID, det.BOMLINEID,
               CAST(1 AS SMALLINT)                   AS Depth
        FROM   STRING_SPLIT(@CandidateItemIds, ',') c
        JOIN   MBOM       bom ON bom.PRODUCTIONITEMID = TRY_CAST(c.value AS INT)
                              AND bom.ISDEFAULTVERSION = 1 AND bom.TENANTID = @TenantId
                              AND bom.TODATE >= CAST(GETDATE() AS DATE) AND bom.FROMDATE <= CAST(GETDATE() AS DATE)
        JOIN   MBOMDETAIL det ON det.BOMID = bom.BOMID

        UNION ALL

        SELECT f.RootComponentItemId, subdet.ITEMID, subdet.BOMID, subdet.BOMLINEID, CAST(f.Depth + 1 AS SMALLINT)
        FROM   FORWARD_CTE f
        JOIN   MBOM       sub    ON sub.PRODUCTIONITEMID = f.DetailItemId
                                 AND sub.ISDEFAULTVERSION = 1 AND sub.TENANTID = @TenantId
                                 AND sub.TODATE >= CAST(GETDATE() AS DATE) AND sub.FROMDATE <= CAST(GETDATE() AS DATE)
        JOIN   MBOMDETAIL subdet ON subdet.BOMID = sub.BOMID
        WHERE  f.Depth < @MaxRecursion
    )
    SELECT TOP (1)
        f.BOMID                                       AS BomId,
        ri.ITEMCODE                                    AS ProudctionItemCode,
        ri.ITEMNAME                                    AS ProudctionItemName,
        di.ITEMCODE                                     AS ItemCode,
        di.ITEMNAME                                     AS ItemName,
        f.BOMLINEID                                     AS BomDetailLineId
    FROM   FORWARD_CTE f
    JOIN   MITEM ri ON ri.ITEMID = @ProductionItemId AND ri.TENANTID = @TenantId
    JOIN   MITEM di ON di.ITEMID = f.DetailItemId    AND di.TENANTID = @TenantId
    WHERE  f.DetailItemId = @ProductionItemId
    OPTION (MAXRECURSION 32)";
        #endregion
    }
}