Late-arriving: correct the right windows.

BEGIN;

-- 1. Land the new facts once. A fact whose customer is unknown yet links to the placeholder (-1).
INSERT INTO silver.orders (order_id, customer_key, amount, event_ts, arrived_at)
SELECT b.order_id, COALESCE(c.customer_key, -1), b.amount, b.event_ts, b.arrived_at
FROM staged.orders_batch AS b
LEFT JOIN gold.dim_customer AS c
  ON c.customer_id = b.customer_id
 AND b.event_ts >= c.valid_from AND b.event_ts < c.valid_to
WHERE NOT EXISTS (SELECT 1 FROM silver.orders AS o WHERE o.order_id = b.order_id);

-- 2. Recompute only the event dates this batch touched, inside the 7-day reprocess window.
--    Facts older than the window are reported separately, not dropped.
DELETE FROM gold.daily_revenue
WHERE revenue_date IN (SELECT DISTINCT CAST(event_ts AS DATE) FROM staged.orders_batch)
  AND revenue_date >= CURRENT_DATE - INTERVAL '7' DAY;

INSERT INTO gold.daily_revenue (revenue_date, revenue)
SELECT CAST(o.event_ts AS DATE), SUM(o.amount)
FROM silver.orders AS o
WHERE CAST(o.event_ts AS DATE) IN (SELECT DISTINCT CAST(event_ts AS DATE) FROM staged.orders_batch)
  AND CAST(o.event_ts AS DATE) >= CURRENT_DATE - INTERVAL '7' DAY
GROUP BY CAST(o.event_ts AS DATE);

COMMIT;