SQL Recursive CTE for Bill of Materials

-- Schema assumptions (PostgreSQL 15+):
--   parts(part_no text PRIMARY KEY, name text NOT NULL, unit_cost_cents integer NOT NULL)
--   bom(parent_part_no text REFERENCES parts, child_part_no text REFERENCES parts,
--       qty integer NOT NULL CHECK (qty > 0), PRIMARY KEY (parent_part_no, child_part_no))
-- The TEMP tables mirror those definitions for a scratch run.

CREATE TEMP TABLE parts (
    part_no         text PRIMARY KEY,
    name            text NOT NULL,
    unit_cost_cents integer NOT NULL CHECK (unit_cost_cents >= 0)
);

CREATE TEMP TABLE bom (
    parent_part_no text NOT NULL REFERENCES parts(part_no),
    child_part_no  text NOT NULL REFERENCES parts(part_no),
    qty            integer NOT NULL CHECK (qty > 0),
    PRIMARY KEY (parent_part_no, child_part_no)
);

INSERT INTO parts (part_no, name, unit_cost_cents) VALUES
    ('ASM-100', 'Desk frame assembly', 0),
    ('SUB-200', 'Left leg kit', 0),
    ('SUB-300', 'Right leg kit', 0),
    ('BOLT-10', 'M6 bolt', 25),
    ('PLATE-1', 'Steel plate', 500);

INSERT INTO bom (parent_part_no, child_part_no, qty) VALUES
    ('ASM-100', 'SUB-200', 2),
    ('ASM-100', 'SUB-300', 1),
    ('SUB-200', 'BOLT-10', 4),
    ('SUB-200', 'PLATE-1', 1),
    ('SUB-300', 'BOLT-10', 6);

WITH RECURSIVE exploded AS (
    SELECT
        b.parent_part_no AS root_part_no,
        b.child_part_no  AS part_no,
        1                AS depth,
        b.qty            AS qty,
        ARRAY[b.parent_part_no, b.child_part_no]::text[] AS path
    FROM bom b
    WHERE b.parent_part_no = 'ASM-100'

    UNION ALL

    SELECT
        e.root_part_no,
        b.child_part_no,
        e.depth + 1,
        e.qty * b.qty,
        e.path || b.child_part_no
    FROM exploded e
    JOIN bom b ON b.parent_part_no = e.part_no
    WHERE e.depth < 10
      AND NOT (b.child_part_no = ANY (e.path))
)
SELECT
    e.root_part_no,
    REPEAT('  ', e.depth) || p.name AS indented_name,
    e.part_no,
    e.qty,
    p.unit_cost_cents,
    e.qty * p.unit_cost_cents AS extended_cost_cents,
    SUM(
        CASE
            WHEN EXISTS (SELECT 1 FROM bom child WHERE child.parent_part_no = e.part_no)
                THEN 0
            ELSE e.qty * p.unit_cost_cents
        END
    ) OVER (PARTITION BY e.root_part_no) AS rolled_up_leaf_cost_cents
FROM exploded e
JOIN parts p ON p.part_no = e.part_no
ORDER BY e.path;