From stored procedures to dbt

-- ================================================================
-- Stored Procedure: build_customer_order_summary
-- Runs nightly. Touch with care.
-- ================================================================

CREATE OR REPLACE PROCEDURE build_customer_order_summary()
AS $$
BEGIN

  -- Step 1: clean customers
  CREATE TEMP TABLE tmp_customers AS  ⚠ temp — gone when session ends, nothing to inspect on failure
  SELECT
      customer_id,
      TRIM(LOWER(email))               AS email,
      COALESCE(first_name, 'Unknown')  AS first_name,
      COALESCE(last_name,  'Unknown')  AS last_name,
      CASE WHEN region IS NULL THEN 'Unassigned' ELSE UPPER(region) END AS region,
      created_at
  FROM raw.customers;  ⚠ hardcoded schema — rename silently breaks this

  -- Step 2: clean orders
  CREATE TEMP TABLE tmp_orders AS  ⚠ temp — same problem
  SELECT
      order_id, customer_id, order_date, status, total_amount
  FROM raw.orders
  WHERE order_id IS NOT NULL AND status != 'cancelled';

  -- Step 3: clean payments
  CREATE TEMP TABLE tmp_payments AS  ⚠ temp — same problem
  SELECT
      payment_id, order_id, payment_method,
      amount AS payment_amount, created_at AS payment_date
  FROM raw.payments
  WHERE payment_id IS NOT NULL AND amount > 0;

  -- Step 4: join orders + payments
  CREATE TEMP TABLE tmp_orders_with_payments AS  ⚡ must wait for steps 2 & 3 — no parallelism possible
  SELECT
      o.order_id, o.customer_id, o.order_date, o.status, o.total_amount,
      SUM(p.payment_amount)                            AS total_paid,
      COUNT(p.payment_id)                               AS payment_count,
      o.total_amount - COALESCE(SUM(p.payment_amount),0) AS outstanding_balance
  FROM tmp_orders o
  LEFT JOIN tmp_payments p ON o.order_id = p.order_id
  GROUP BY o.order_id, o.customer_id, o.order_date, o.status, o.total_amount;

  -- Step 5: final output
  CREATE OR REPLACE TABLE analytics.fct_customer_order_summary AS  ⚠ full overwrite every run — no incremental option
  SELECT
      c.customer_id, c.email,
      c.first_name || ' ' || c.last_name AS full_name, c.region,
      COUNT(op.order_id)                   AS lifetime_orders,
      COALESCE(SUM(op.total_amount),0)   AS lifetime_revenue,
      COALESCE(SUM(op.total_paid),0)     AS lifetime_payments,
      MAX(op.order_date)                   AS most_recent_order_date
  FROM tmp_customers c
  LEFT JOIN tmp_orders_with_payments op ON c.customer_id = op.customer_id
  GROUP BY c.customer_id, c.email, full_name, c.region;

  -- Proc ends. All 4 temp tables dropped automatically.
  -- If step 5 failed: no intermediate state survives to inspect.  ⚠ debug from scratch

END;
$$ LANGUAGE plpgsql;