Supply Chain Inventory Projection Tool (v2 — SKU-Level)

Projects per-SKU inventory from today through 2027-03-31 by combining EC inventory levels, demand forecasts, and purchase order arrivals from both manual input and Endless Commerce. Designed to power a Retool dashboard connected to Snowflake. (The date spine extends to 2027-12-31 to capture distant PO arrivals, but output is truncated to output_end_date in the projection_bounds CTE.)

SKUs

SKU	Color
TCMOD1AQUA	Aqua
TCMOD1LEMON	Lemon
TCMOD1WHITE	White
TCMOD1LILAC	Lilac

Snowflake objects

All tables and views live in UTILITIES_DB.SUPPLY_CHAIN.

INPUT_PURCHASE_ORDERS

Wide-format table — one row per PO, columns per SKU. po_number matches EC po_number for reconciliation joins.

Column	Type	Notes
po_number	varchar (PK)	Matches EC po_number for joins
expected_delivery_date	date	When units are expected at DC
unit_cost	number(10,2)	Per-unit hardware cost
ship_mode	varchar	Air or Ocean
notes	varchar	Context
qty_aqua, qty_lemon, qty_white, qty_lilac	integer	Per-SKU quantities (default 0)
qty_total	integer (computed)	Sum of 4 SKU columns
created_at	timestamp_ntz	Auto-set

DDL: create_input_purchase_orders.sql
Seeded with 20 POs migrated from legacy PURCHASE_ORDER table (even 25% SKU split)

INPUT_FORECAST_DEMAND

Monthly demand forecast with per-SKU percentage splits.

Column	Type	Notes
month	date (PK)	First of month
daily_units_sold	integer	Total daily demand across all SKUs
pcnt_aqua, pcnt_lemon, pcnt_white, pcnt_lilac	number(5,4)	SKU split (should sum to 1.0)

DDL: create_input_forecast_demand.sql
Important: Months with no row default to 0 demand. Flat line on the chart = extend forecast.

EC_INVENTORY

Endless Commerce inventory levels for our 4 Tin Can SKUs, loaded via Retool. The available column is the starting balance for the projection — it already accounts for outstanding orders (physical minus allocated minus ordered).

DDL: create_ec_inventory.sql
Fetch: endless_commerce_integration/fetch_inventory.js
Upsert: endless_commerce_integration/upsert_ec_inventory.sql

EC_INVENTORY_HISTORY

Daily snapshots of ALL Endless Commerce inventory items (not just Tin Can SKUs — includes boxes, parts, etc.). One row per (insert_date, id). Populated by a scheduled Retool workflow.

Column	Type	Notes
id	varchar (PK)	EC inventory item ID
product_id	varchar	EC product ID
sku	varchar	Product SKU
name	varchar	Product name
stock	integer	Total stock
available	integer	Available (physical minus allocated minus ordered)
allocated	integer	Allocated to orders
ordered	integer	Ordered by customers
incoming	integer	Incoming from POs
unallocated	integer	Unallocated stock
unavailable	integer	Unavailable stock
physical	integer	Physical count
insert_timestamp_pt	timestamp_ntz	Pacific time when snapshot was taken
insert_date	date (PK)	Pacific date of snapshot
created_at	timestamp_ntz	Auto-set

DDL: create_ec_inventory_history.sql
Fetch: endless_commerce_integration/fetch_inventory_snapshot.js
Insert: db_operations/insert_ec_inventory_history.sql

EC_PURCHASE_ORDERS / EC_SHIPMENTS

Raw EC data loaded via Retool (see endless_commerce_integration/). These are the source tables for the EC views below.

VW_EC_PO_ITEMS (view)

Flattens ec_purchase_orders.po_items variant → one row per (PO, SKU). Filters to our 4 SKUs.

Column	Source
po_number	PO number
sku	`item.value:sku`
qty_ordered	`item.value:qtyOrdered`
unit_cost	`item.value:unitCost:amount`
expected_date	`ecp.expected_date::date`
po_status	`ecp.status_name`

DDL: create_vw_ec_po_items.sql

VW_EC_PO_ITEMS_WIDE (view)

Pivoted version of VW_EC_PO_ITEMS — one row per PO with per-SKU quantity columns. Same format as INPUT_PURCHASE_ORDERS.

Column	Description
po_number	PO number
expected_date	Expected delivery date
po_status	EC status (CLOSED, DELIVERED, IN_TRANSIT, etc.)
qty_aqua, qty_lemon, qty_white, qty_lilac	Per-SKU ordered quantities
qty_total	Sum of all SKUs

DDL: create_vw_ec_po_items_wide.sql

VW_EC_SHIPMENT_ITEMS (view)

Flattens ec_shipments.items variant → one row per (shipment, SKU). Filters to our 4 SKUs.

Column	Source
shipment_number	Shipment identifier
po_number	Linked PO
sku	`item.value:sku`
quantity	`item.value:quantity`
received_quantity	`item.value:receivedQuantity`
arrival_date	`coalesce(actual_arrival_date, expected_arrival_date, po.expected_date)::date`
shipment_status	`ecs.status_name`

DDL: create_vw_ec_shipment_items.sql
Note: Excludes rows where po_number is null (returns/defective shipments)

VW_UNIFIED_SHIPMENTS (view)

Combined view of actual EC shipments and synthetic shipments, mirroring the reconciliation logic from daily_inventory_projection.sql. One row per (PO, arrival_date) in wide SKU format.

Column	Description
po	PO number
po_source	`EC` if PO exists in EC, `INPUT` if input-only
shipment_type	`ACTUAL` (real EC shipment) or `SYNTHETIC` (projected)
expected_date	Arrival date
ec_shipment_number	EC shipment number(s) for actual shipments, NULL for synthetic
units_aqua	Units for TCMOD1AQUA
units_lilac	Units for TCMOD1LILAC
units_lemon	Units for TCMOD1LEMON
units_white	Units for TCMOD1WHITE

DDL: create_vw_unified_shipments.sql

VW_UNIFIED_SHIPMENTS_BY_SKU (view)

Normalized version of VW_UNIFIED_SHIPMENTS — one row per (PO, arrival_date, SKU) with a single units column. Zero-unit rows are excluded.

Column	Description
po	PO number
po_source	`EC` or `INPUT`
shipment_type	`ACTUAL` or `SYNTHETIC`
expected_date	Arrival date
ec_shipment_number	EC shipment number(s) for actual shipments, NULL for synthetic
sku	SKU (e.g. TCMOD1AQUA)
units	Units for this SKU

DDL: create_vw_unified_shipments_by_sku.sql

Queries

daily_inventory_projection.sql — the centerpiece

Output: One row per (projection_date, sku) with:

Column	Description
projection_date	Calendar date
sku	One of the 4 SKUs
daily_demand	Units consumed (from INPUT_FORECAST_DEMAND × SKU percentage)
supply_arriving	Units arriving (from real EC shipments + synthetic shipments)
net_daily_change	supply - demand
projected_on_hand	Running total from starting_balance, partitioned by SKU
stockout_flag	TRUE when projected_on_hand < 0
weeks_of_inventory	projected_on_hand / (trailing 14-day avg daily demand × 7)

Starting balance (per SKU):

starting_balance = available (from EC_INVENTORY)

The available column in EC_INVENTORY already equals physical stock minus outstanding orders, so no separate unfulfilled-order or fulfilled-since-count adjustments are needed.

Supply reconciliation logic:

POs from INPUT_PURCHASE_ORDERS and EC are reconciled via FULL OUTER JOIN on po_number. EC data wins when both exist. Each PO is classified:

Scenario	Meaning	Synthetic shipments
A	Input-only (no EC match)	Yes — starting at `expected_delivery_date`, 25,056 units every 7 days
B	EC PO, no shipments yet	Yes — starting at EC `expected_date`, 25,056 units every 7 days
C	EC PO, per-SKU shortfalls remain	Yes — starting 7 days after last real shipment, 25,056 units every 7 days
D	EC PO, all SKUs fully shipped or CLOSED	No — real shipments cover the PO

Synthetic shipments distribute units across SKUs proportionally based on each PO's unallocated SKU mix.

Sandbox

The sandbox lets you test alternate demand/PO scenarios without overwriting the baseline plan. It copies only the two user-editable input tables while sharing all EC data, inventory, and Shopify data with the baseline.

What gets sandboxed vs. shared

Table/View	Sandboxed?	Rationale
`INPUT_PURCHASE_ORDERS`	Yes → `SANDBOX_INPUT_PURCHASE_ORDERS`	User edits PO scenarios
`INPUT_FORECAST_DEMAND`	Yes → `SANDBOX_INPUT_FORECAST_DEMAND`	User edits demand scenarios
`VW_UNIFIED_SHIPMENTS`	Yes → `SANDBOX_VW_UNIFIED_SHIPMENTS`	Depends on INPUT_PURCHASE_ORDERS
`VW_UNIFIED_SHIPMENTS_BY_SKU`	Yes → `SANDBOX_VW_UNIFIED_SHIPMENTS_BY_SKU`	Depends on VW_UNIFIED_SHIPMENTS
`EC_INVENTORY`	No	Shared — real EC starting balance
`VW_EC_PO_ITEMS`	No	Shared — real EC PO data
`VW_EC_SHIPMENT_ITEMS`	No	Shared — real EC shipment data
`raw_db.shopify.orders`	No	Shared — real Shopify trailing demand

Setup

Run db_operations/create_sandbox_tables.sql once to create and populate the sandbox tables.

Usage

Edit SANDBOX_INPUT_PURCHASE_ORDERS and/or SANDBOX_INPUT_FORECAST_DEMAND with your scenario changes
Run sandbox_daily_inventory_projection.sql to see the projected impact
Compare results against the baseline daily_inventory_projection.sql

Reset

Run db_operations/reset_sandbox.sql to truncate both sandbox tables and repopulate them from the current baseline.

Updating the tool

To extend the demand forecast

insert into utilities_db.supply_chain.input_forecast_demand
    (month, daily_units_sold, pcnt_aqua, pcnt_lemon, pcnt_white, pcnt_lilac)
values
    ('2027-01-01', 1500, 0.3000, 0.1500, 0.1500, 0.4000);

To add a purchase order

insert into utilities_db.supply_chain.input_purchase_orders
    (po_number, expected_delivery_date, unit_cost, ship_mode, notes,
     qty_aqua, qty_lemon, qty_white, qty_lilac)
values
    ('PO-021', '2029-03-01', 35.36, 'Ocean', '2029 Demand',
     75000, 75000, 75000, 75000);

To link an input PO to EC

Update the po_number in INPUT_PURCHASE_ORDERS to match the EC PO number. On the next query run, EC data will take precedence for qty and dates.

TODOs

1. Input PO → EC PO linking

The 20 seed POs (PO-001 through PO-020) use placeholder names that don't match EC PO numbers. As real POs exist in EC, update po_number in INPUT_PURCHASE_ORDERS to match so the reconciliation join kicks in and EC data takes precedence. This is a manual data-entry task, not a code change.

2. SKU mix calibration (periodic)

Both INPUT_FORECAST_DEMAND and INPUT_PURCHASE_ORDERS have been seeded with trailing 30-day Shopify mix (Aqua 38.70%, Lemon 14.03%, White 16.26%, Lilac 31.01% — see sku_mix_t30d.sql). Re-run the query periodically to update splits as the mix shifts.

3. NULL-SKU unfulfilled orders (monitor)

Unfulfilled Shopify orders with NULL SKU (pre-SKU-tracking, ~42K historical line items) are excluded from the per-SKU projection. This slightly overstates available stock. Should diminish over time as legacy orders are fulfilled, but worth monitoring.

Done

Replace starting balance with EC_INVENTORY.available ✓

Replaced the 3 starting-balance CTEs (current_stock, unfulfilled_orders, fulfilled_since_count) with a single read from EC_INVENTORY.available. The available column already accounts for outstanding orders, eliminating ~80 lines of Shopify/stock-count logic. This also makes the separate EC unfulfilled orders TODO unnecessary.

EC inventory API (`listInventory`) ✓

Auth 401 issue resolved (was missing X-Company-Id: tin-can header). Created fetch_inventory.js and upsert_ec_inventory.sql. The API returns 32 total inventory items; the fetch script filters to our 4 Tin Can SKUs and maps to the EC_INVENTORY table schema.

Files

Root

File	Purpose
`supply_chain_tool.md`	This doc
`daily_inventory_projection.sql`	Main projection query (per-SKU daily rows)
`sandbox_daily_inventory_projection.sql`	Sandbox projection query (reads sandbox input tables)

`supplementary_queries/`

Supporting queries referenced by the projection or used for periodic calibration.

File	Purpose
`sku_mix_t30d.sql`	Trailing 30-day SKU mix from Shopify

`endless_commerce_integration/`

EC API fetch scripts and reference docs (run in Retool).

File	Purpose
`endless_commerce_api_notes.md`	API connection details, schema quirks, permissions
`fetch_purchase_orders.js`	Paginated fetch of all POs
`fetch_shipments.js`	Paginated fetch of recent shipments
`fetch_inventory.js`	Fetch inventory for 4 Tin Can SKUs
`fetch_inventory_snapshot.js`	Fetch ALL inventory items for daily snapshots

`db_operations/`

Snowflake DDL (create table/view) and upsert queries.

File	Purpose
`create_input_purchase_orders.sql`	DDL + seed for INPUT_PURCHASE_ORDERS (20 POs)
`create_input_forecast_demand.sql`	DDL + seed for INPUT_FORECAST_DEMAND (Mar–Dec 2026)
`create_ec_inventory.sql`	DDL for EC_INVENTORY table
`create_ec_purchase_orders.sql`	DDL for EC_PURCHASE_ORDERS table
`create_ec_shipments.sql`	DDL for EC_SHIPMENTS table
`create_vw_ec_po_items.sql`	View: flattened EC PO items by SKU
`create_vw_ec_po_items_wide.sql`	View: pivoted EC PO items (one row per PO, SKU qty columns)
`create_vw_ec_shipment_items.sql`	View: flattened EC shipment items by SKU (excludes null-PO returns)
`create_vw_unified_shipments.sql`	View: combined actual + synthetic shipments (wide SKU format)
`create_vw_unified_shipments_by_sku.sql`	View: normalized version (one row per PO/date/SKU)
`upsert_ec_purchase_orders.sql`	Retool MERGE for EC POs
`upsert_ec_shipments.sql`	Retool MERGE for EC shipments
`upsert_ec_inventory.sql`	Retool MERGE for EC inventory
`create_ec_inventory_history.sql`	DDL for EC_INVENTORY_HISTORY table
`insert_ec_inventory_history.sql`	Retool MERGE for daily inventory snapshots
`create_sandbox_tables.sql`	DDL + initial population for sandbox input tables
`create_sandbox_views.sql`	DDL for sandbox unified shipment views
`reset_sandbox.sql`	Truncate and repopulate sandbox tables from baseline
`migrate_to_utilities_db.sql`	One-time migration script (DEV_MJB_DB → UTILITIES_DB)

Query

-- Daily Inventory Projection (SKU-level)
-- Projects on-hand inventory from today through output_end_date (see projection_bounds).
-- One row per (projection_date, sku) with independent per-SKU running totals.
-- Reconciles manual INPUT_PURCHASE_ORDERS with EC PO/shipment data:
--   EC wins for qty and dates when both sources exist for a PO.
--   Unshipped PO quantities generate synthetic 25K-unit shipments at 7-day intervals.

with sku_list as (
    select column1 as sku
    from values ('TCMOD1AQUA'), ('TCMOD1LEMON'), ('TCMOD1WHITE'), ('TCMOD1LILAC')
),

-- ============================================================
-- STARTING BALANCE
-- ============================================================

starting_balance as (
    select
        sku,
        available as balance
    from utilities_db.supply_chain.ec_inventory
),

-- ============================================================
-- DATE × SKU SPINE
-- ============================================================

projection_bounds as (
    -- spine_end_date: how far the date spine extends (must cover all possible PO arrivals)
    -- output_end_date: how far the final output is truncated (controls dashboard range)
    select
        current_date as start_date,
        '2027-12-31'::date as spine_end_date,
        '2027-03-31'::date as output_end_date
),

date_spine as (
    select dateadd(day, seq4(), pb.start_date) as projection_date
    from table(generator(rowcount => 1500)) as gen
    cross join projection_bounds as pb
    where dateadd(day, seq4(), pb.start_date) <= pb.spine_end_date
),

date_sku_spine as (
    select ds.projection_date, sl.sku
    from date_spine as ds
    cross join sku_list as sl
),

-- ============================================================
-- DEMAND
-- ============================================================

daily_demand as (
    select
        dss.projection_date,
        dss.sku,
        coalesce(
            case dss.sku
                when 'TCMOD1AQUA'  then fd.daily_units_sold * fd.pcnt_aqua
                when 'TCMOD1LEMON' then fd.daily_units_sold * fd.pcnt_lemon
                when 'TCMOD1WHITE' then fd.daily_units_sold * fd.pcnt_white
                when 'TCMOD1LILAC' then fd.daily_units_sold * fd.pcnt_lilac
            end,
            0
        ) as daily_demand
    from date_sku_spine as dss
    left join utilities_db.supply_chain.input_forecast_demand as fd
        on date_trunc('month', dss.projection_date) = fd.month
),

-- ============================================================
-- SUPPLY: EC DATA
-- ============================================================

ec_po_summary as (
    select
        po_number,
        expected_date,
        po_status,
        sum(case when sku = 'TCMOD1AQUA'  then qty_ordered else 0 end) as qty_aqua,
        sum(case when sku = 'TCMOD1LEMON' then qty_ordered else 0 end) as qty_lemon,
        sum(case when sku = 'TCMOD1WHITE' then qty_ordered else 0 end) as qty_white,
        sum(case when sku = 'TCMOD1LILAC' then qty_ordered else 0 end) as qty_lilac,
        sum(qty_ordered) as qty_total
    from utilities_db.supply_chain.vw_ec_po_items
    group by po_number, expected_date, po_status
),

ec_shipment_summary as (
    select
        po_number,
        sku,
        sum(quantity) as shipped_qty
    from utilities_db.supply_chain.vw_ec_shipment_items
    group by po_number, sku
),

ec_shipped_total as (
    select po_number, sum(shipped_qty) as total_shipped
    from ec_shipment_summary
    group by po_number
),

ec_real_arrivals as (
    select
        po_number,
        sku,
        arrival_date,
        sum(quantity) as units_arriving
    from utilities_db.supply_chain.vw_ec_shipment_items
    group by po_number, sku, arrival_date
),

ec_last_arrival as (
    select
        po_number,
        max(arrival_date) as last_arrival_date
    from utilities_db.supply_chain.vw_ec_shipment_items
    group by po_number
),

-- ============================================================
-- SUPPLY: INPUT POs
-- ============================================================

input_po_summary as (
    select
        po_number,
        expected_delivery_date,
        qty_aqua,
        qty_lemon,
        qty_white,
        qty_lilac,
        qty_total
    from utilities_db.supply_chain.input_purchase_orders
),

-- ============================================================
-- SUPPLY: RECONCILIATION
-- ============================================================

unified_po as (
    -- FULL OUTER JOIN input + EC on po_number; EC wins for qty and dates
    select
        coalesce(ecp.po_number, ipo.po_number) as po_number,
        coalesce(ecp.expected_date, ipo.expected_delivery_date) as expected_date,
        coalesce(ecp.qty_aqua, ipo.qty_aqua) as qty_aqua,
        coalesce(ecp.qty_lemon, ipo.qty_lemon) as qty_lemon,
        coalesce(ecp.qty_white, ipo.qty_white) as qty_white,
        coalesce(ecp.qty_lilac, ipo.qty_lilac) as qty_lilac,
        coalesce(ecp.qty_total, ipo.qty_total) as qty_total,
        ecp.po_status as ec_status,
        ecp.po_number is not null as has_ec,
        ipo.po_number is not null as has_input
    from ec_po_summary as ecp
    full outer join input_po_summary as ipo
        on ecp.po_number = ipo.po_number
),

po_scenario as (
    -- Classify each PO:
    --   A = input_only (no EC match)
    --   B = EC, no shipments
    --   C = EC, per-SKU shortfalls remain (some SKU shipped < ordered)
    --   D = EC, all SKUs fully shipped (no per-SKU shortfalls)
    select
        upo.po_number,
        upo.expected_date,
        upo.qty_aqua,
        upo.qty_lemon,
        upo.qty_white,
        upo.qty_lilac,
        upo.qty_total,
        case
            when not upo.has_ec then 'A'
            when upo.ec_status = 'CLOSED' then 'D'
            when coalesce(ess_total.total_shipped, 0) = 0 then 'B'
            when greatest(upo.qty_aqua  - coalesce(ess_aqua.shipped_qty, 0), 0)
               + greatest(upo.qty_lemon - coalesce(ess_lemon.shipped_qty, 0), 0)
               + greatest(upo.qty_white - coalesce(ess_white.shipped_qty, 0), 0)
               + greatest(upo.qty_lilac - coalesce(ess_lilac.shipped_qty, 0), 0) > 0 then 'C'
            else 'D'
        end as scenario,
        greatest(upo.qty_aqua  - coalesce(ess_aqua.shipped_qty, 0), 0) as unalloc_aqua,
        greatest(upo.qty_lemon - coalesce(ess_lemon.shipped_qty, 0), 0) as unalloc_lemon,
        greatest(upo.qty_white - coalesce(ess_white.shipped_qty, 0), 0) as unalloc_white,
        greatest(upo.qty_lilac - coalesce(ess_lilac.shipped_qty, 0), 0) as unalloc_lilac,
        greatest(upo.qty_aqua  - coalesce(ess_aqua.shipped_qty, 0), 0)
        + greatest(upo.qty_lemon - coalesce(ess_lemon.shipped_qty, 0), 0)
        + greatest(upo.qty_white - coalesce(ess_white.shipped_qty, 0), 0)
        + greatest(upo.qty_lilac - coalesce(ess_lilac.shipped_qty, 0), 0) as unalloc_total
    from unified_po as upo
    left join ec_shipped_total as ess_total
        on upo.po_number = ess_total.po_number
    left join ec_shipment_summary as ess_aqua
        on upo.po_number = ess_aqua.po_number and ess_aqua.sku = 'TCMOD1AQUA'
    left join ec_shipment_summary as ess_lemon
        on upo.po_number = ess_lemon.po_number and ess_lemon.sku = 'TCMOD1LEMON'
    left join ec_shipment_summary as ess_white
        on upo.po_number = ess_white.po_number and ess_white.sku = 'TCMOD1WHITE'
    left join ec_shipment_summary as ess_lilac
        on upo.po_number = ess_lilac.po_number and ess_lilac.sku = 'TCMOD1LILAC'
),

-- ============================================================
-- SUPPLY: SYNTHETIC SHIPMENTS (for unshipped PO quantities)
-- ============================================================

synthetic_shipment_count as (
    -- For A/B/C: how many 25K synthetic shipments needed
    select
        ps.po_number,
        ps.scenario,
        ps.unalloc_aqua,
        ps.unalloc_lemon,
        ps.unalloc_white,
        ps.unalloc_lilac,
        ps.unalloc_total,
        case
            when ps.scenario in ('A', 'B') then ps.expected_date
            when ps.scenario = 'C' then dateadd(day, 7, ela.last_arrival_date)
        end as first_synthetic_date,
        ceil(ps.unalloc_total / 25056.0)::integer as num_shipments,
        case when ps.unalloc_total > 0
            then ps.unalloc_aqua / ps.unalloc_total::float else 0.25 end as pct_aqua,
        case when ps.unalloc_total > 0
            then ps.unalloc_lemon / ps.unalloc_total::float else 0.25 end as pct_lemon,
        case when ps.unalloc_total > 0
            then ps.unalloc_white / ps.unalloc_total::float else 0.25 end as pct_white,
        case when ps.unalloc_total > 0
            then ps.unalloc_lilac / ps.unalloc_total::float else 0.25 end as pct_lilac
    from po_scenario as ps
    left join ec_last_arrival as ela on ps.po_number = ela.po_number
    where ps.scenario in ('A', 'B', 'C')
      and ps.unalloc_total > 0
),

synthetic_shipments as (
    -- One row per synthetic shipment, spread at 7-day intervals
    select
        ssc.po_number,
        gen.value::integer + 1 as shipment_n,
        dateadd(day, gen.value::integer * 7, ssc.first_synthetic_date) as arrival_date,
        least(25056, ssc.unalloc_total - gen.value::integer * 25056) as units,
        ssc.pct_aqua,
        ssc.pct_lemon,
        ssc.pct_white,
        ssc.pct_lilac
    from synthetic_shipment_count as ssc,
    lateral flatten(input => array_generate_range(0, ssc.num_shipments)) as gen
),

synthetic_arrivals as (
    -- Expand to per-SKU using proportional mix
    select ss.po_number, ss.arrival_date, 'TCMOD1AQUA' as sku,
        round(ss.units * ss.pct_aqua)::integer as units_arriving
    from synthetic_shipments as ss
    union all
    select ss.po_number, ss.arrival_date, 'TCMOD1LEMON',
        round(ss.units * ss.pct_lemon)::integer
    from synthetic_shipments as ss
    union all
    select ss.po_number, ss.arrival_date, 'TCMOD1WHITE',
        round(ss.units * ss.pct_white)::integer
    from synthetic_shipments as ss
    union all
    select ss.po_number, ss.arrival_date, 'TCMOD1LILAC',
        round(ss.units * ss.pct_lilac)::integer
    from synthetic_shipments as ss
),

-- ============================================================
-- SUPPLY: COMBINED
-- ============================================================

all_supply_arrivals as (
    select sku, arrival_date, units_arriving
    from ec_real_arrivals
    union all
    select sku, arrival_date, units_arriving
    from synthetic_arrivals
),

supply_arriving as (
    select
        arrival_date,
        sku,
        sum(units_arriving) as units_arriving
    from all_supply_arrivals
    where arrival_date >= current_date
    group by arrival_date, sku
),

-- ============================================================
-- FINAL: COMBINE DEMAND + SUPPLY
-- ============================================================

trailing_14d_demand as (
    -- Actual trailing 14-day average daily demand per SKU from Shopify
    select
        item.value:sku::varchar as sku,
        sum(item.value:quantity::integer) / 14.0 as avg_daily_demand
    from raw_db.shopify.orders as ord,
    lateral flatten(input => ord.line_items) as item
    where ord.cancelled_at is null
        and ord.test = false
        and ord.created_at >= dateadd(day, -14, current_date)
        and ord.created_at < current_date
        and item.value:sku::varchar in ('TCMOD1AQUA', 'TCMOD1LEMON', 'TCMOD1WHITE', 'TCMOD1LILAC')
    group by item.value:sku::varchar
),

daily_changes as (
    select
        dd.projection_date,
        dd.sku,
        dd.daily_demand,
        coalesce(sa.units_arriving, 0) as supply_arriving,
        coalesce(sa.units_arriving, 0) - dd.daily_demand as net_daily_change
    from daily_demand as dd
    left join supply_arriving as sa
        on dd.projection_date = sa.arrival_date
        and dd.sku = sa.sku
)

select
    dc.projection_date,
    dc.sku,
    dc.daily_demand,
    dc.supply_arriving,
    dc.net_daily_change,
    sb.balance + sum(dc.net_daily_change) over (
        partition by dc.sku
        order by dc.projection_date
        rows between unbounded preceding and current row
    ) as projected_on_hand,
    sb.balance + sum(dc.net_daily_change) over (
        partition by dc.sku
        order by dc.projection_date
        rows between unbounded preceding and current row
    ) < 0 as stockout_flag,
    round(
        (sb.balance + sum(dc.net_daily_change) over (
            partition by dc.sku
            order by dc.projection_date
            rows between unbounded preceding and current row
        )) / nullif(t14.avg_daily_demand * 7, 0),
        1
    ) as weeks_of_inventory
from daily_changes as dc
inner join starting_balance as sb on dc.sku = sb.sku
left join trailing_14d_demand as t14 on dc.sku = t14.sku
cross join projection_bounds as pb
where dc.projection_date <= pb.output_end_date
order by dc.projection_date, dc.sku;