-- BOM definition, sample data, and single-level explosion UDTF

-- 1. Create ITEM master table
CREATE OR REPLACE TABLE SANDBOX.PUBLIC.ITEM (
    ITEM_ID     VARCHAR,
    DESCRIPTION VARCHAR
);

INSERT INTO SANDBOX.PUBLIC.ITEM VALUES
    ('BICYCLE',  'Finished bicycle assembly'),
    ('FRAME',    'Welded steel frame'),
    ('WHEEL',    'Spoked wheel with tire'),
    ('SEAT',     'Padded seat'),
    ('CHAIN',    'Drive chain'),
    ('PEDAL',    'Foot pedal'),
    ('TUBE',     'Steel tube'),
    ('WELD_ROD', 'Welding rod'),
    ('RIM',      'Aluminum rim'),
    ('SPOKE',    'Steel spoke'),
    ('TIRE',     'Rubber tire');

-- 2. Create BOM table
CREATE OR REPLACE TABLE SANDBOX.PUBLIC.BOM (
    PARENT_ITEM    VARCHAR,
    COMPONENT_ITEM VARCHAR,
    QTY_PER        NUMBER(10,2)
);

INSERT INTO SANDBOX.PUBLIC.BOM VALUES
    ('BICYCLE', 'FRAME',    1),
    ('BICYCLE', 'WHEEL',    2),
    ('BICYCLE', 'SEAT',     1),
    ('BICYCLE', 'CHAIN',    1),
    ('BICYCLE', 'PEDAL',    2),
    ('FRAME',   'TUBE',     3),
    ('FRAME',   'WELD_ROD', 6),
    ('WHEEL',   'RIM',      1),
    ('WHEEL',   'SPOKE',   32),
    ('WHEEL',   'TIRE',     1);

-- 3. Create recursive multi-level explosion UDTF
CREATE OR REPLACE FUNCTION SANDBOX.PUBLIC.EXPLODE_BOM(P_ITEM VARCHAR)
RETURNS TABLE (DEPTH INT, PARENT_ITEM VARCHAR, COMPONENT_ITEM VARCHAR, QTY_PER NUMBER(10,2))
AS
$$
    WITH RECURSIVE BOM_EXPLODE AS (
        SELECT 1 AS DEPTH, PARENT_ITEM, COMPONENT_ITEM, QTY_PER
        FROM SANDBOX.PUBLIC.BOM
        WHERE PARENT_ITEM = P_ITEM
        UNION ALL
        SELECT E.DEPTH + 1, B.PARENT_ITEM, B.COMPONENT_ITEM, B.QTY_PER
        FROM SANDBOX.PUBLIC.BOM B
        JOIN BOM_EXPLODE E ON B.PARENT_ITEM = E.COMPONENT_ITEM
    )
    SELECT DEPTH, PARENT_ITEM, COMPONENT_ITEM, QTY_PER
    FROM BOM_EXPLODE
$$;

-- 4. Demo: full explosion of BICYCLE down to raw materials
SELECT * FROM TABLE(SANDBOX.PUBLIC.EXPLODE_BOM('BICYCLE')) ORDER BY DEPTH, PARENT_ITEM;