/*
FILE: 19_step6_scenario_engine_foundation.sql
PROJECT: budgetdb finance warehouse
LAYER: analytics
STEP: 6

PURPOSE
- Create the "scenario engine" foundation: a simple, reliable way to apply what-if multipliers
  (payroll, vendors, commission) over a date window and compare vs baseline.
- Output a scenario-aware monthly fact view that dashboards / UI / AI can query.

WHAT THIS ENABLES (PLAIN ENGLISH)
- Baseline is your truth: analytics.fact_cost_monthly_2025
- Scenario = baseline * multipliers (e.g., vendors -10%, payroll +5%, commission +20%)
- You can store scenarios in a table, then the view computes the monthly scenario totals automatically.

CREATES / UPDATES (OBJECTS)
- Table: analytics.scenario
- View : analytics.fact_cost_monthly_2025_scenario

DEPENDS ON (MUST EXIST)
- View: analytics.fact_cost_monthly_2025   (Step 5)

HOW TO RUN
- Run the entire file top-to-bottom.

NOTES
- Multipliers are numeric factors:
    1.00 = no change
    0.90 = -10%
    1.05 = +5%
- start_month / end_month are inclusive month boundaries, stored as YYYY-MM-01.
*/

BEGIN;

CREATE SCHEMA IF NOT EXISTS analytics;

-- -------------------------------------------------------------------
-- 0) Guardrail: baseline must exist
-- -------------------------------------------------------------------
DO $$
BEGIN
  IF to_regclass('analytics.fact_cost_monthly_2025') IS NULL THEN
    RAISE EXCEPTION 'Missing required baseline view: analytics.fact_cost_monthly_2025. Run Step 5 first.';
  END IF;
END $$;


-- -------------------------------------------------------------------
-- 1) Scenario registry (simple on purpose)
-- -------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS analytics.scenario (
  scenario_id           bigserial PRIMARY KEY,
  scenario_name         text UNIQUE NOT NULL,

  -- multipliers
  payroll_multiplier    numeric NOT NULL DEFAULT 1.0,
  vendors_multiplier    numeric NOT NULL DEFAULT 1.0,
  commission_multiplier numeric NOT NULL DEFAULT 1.0,

  -- active window (inclusive months; store as YYYY-MM-01)
  start_month           date NOT NULL DEFAULT '2025-01-01'::date,
  end_month             date NOT NULL DEFAULT '2025-12-01'::date,

  created_at            timestamptz NOT NULL DEFAULT now(),
  updated_at            timestamptz NOT NULL DEFAULT now()
);

-- Helpful index for filtering by time window
CREATE INDEX IF NOT EXISTS idx_scenario_window
ON analytics.scenario (start_month, end_month);


-- -------------------------------------------------------------------
-- 2) Seed scenarios (safe upsert)
--    - Baseline scenario is always useful
--    - Example scenario to prove the engine works
-- -------------------------------------------------------------------
INSERT INTO analytics.scenario (
  scenario_name,
  payroll_multiplier,
  vendors_multiplier,
  commission_multiplier,
  start_month,
  end_month
)
VALUES
  ('Baseline (1.0x)', 1.0, 1.0, 1.0, '2025-01-01'::date, '2025-12-01'::date),
  ('Test: Vendors -10%', 1.0, 0.90, 1.0, '2025-01-01'::date, '2025-12-01'::date)
ON CONFLICT (scenario_name)
DO UPDATE SET
  payroll_multiplier    = EXCLUDED.payroll_multiplier,
  vendors_multiplier    = EXCLUDED.vendors_multiplier,
  commission_multiplier = EXCLUDED.commission_multiplier,
  start_month           = EXCLUDED.start_month,
  end_month             = EXCLUDED.end_month,
  updated_at            = now();


-- -------------------------------------------------------------------
-- 3) Scenario fact view (baseline + scenario-applied totals)
-- -------------------------------------------------------------------
CREATE OR REPLACE VIEW analytics.fact_cost_monthly_2025_scenario AS
SELECT
  s.scenario_id,
  s.scenario_name,

  b.month_date,

  -- baseline components
  b.payroll_total_monthly    AS payroll_total_monthly_base,
  b.vendors_total_monthly    AS vendors_total_monthly_base,
  b.commission_total_monthly AS commission_total_monthly_base,
  b.total_cost_monthly       AS total_cost_monthly_base,

  -- scenario components
  (b.payroll_total_monthly    * s.payroll_multiplier)    AS payroll_total_monthly_scn,
  (b.vendors_total_monthly    * s.vendors_multiplier)    AS vendors_total_monthly_scn,
  (b.commission_total_monthly * s.commission_multiplier) AS commission_total_monthly_scn,

  (
    (b.payroll_total_monthly    * s.payroll_multiplier)
    + (b.vendors_total_monthly  * s.vendors_multiplier)
    + (b.commission_total_monthly * s.commission_multiplier)
  ) AS total_cost_monthly_scn,

  -- deltas
  (
    (
      (b.payroll_total_monthly    * s.payroll_multiplier)
      + (b.vendors_total_monthly  * s.vendors_multiplier)
      + (b.commission_total_monthly * s.commission_multiplier)
    ) - b.total_cost_monthly
  ) AS delta_monthly

FROM analytics.scenario s
JOIN analytics.fact_cost_monthly_2025 b
  ON b.month_date BETWEEN s.start_month AND s.end_month
ORDER BY s.scenario_id, b.month_date;


COMMIT;


-- -------------------------------------------------------------------
-- 4) Quick validation queries (run manually)
-- -------------------------------------------------------------------

-- A) Confirm scenarios exist
-- SELECT scenario_id, scenario_name, payroll_multiplier, vendors_multiplier, commission_multiplier, start_month, end_month
-- FROM analytics.scenario
-- ORDER BY scenario_id;

-- B) Confirm the view returns rows
-- SELECT scenario_name, COUNT(*) AS months
-- FROM analytics.fact_cost_monthly_2025_scenario
-- GROUP BY 1
-- ORDER BY 2 DESC;

-- C) Year totals + delta for a specific scenario
-- SELECT
--   scenario_name,
--   SUM(total_cost_monthly_base) AS baseline_year_total,
--   SUM(total_cost_monthly_scn)  AS scenario_year_total,
--   SUM(delta_monthly)           AS delta_year
-- FROM analytics.fact_cost_monthly_2025_scenario
-- WHERE scenario_name = 'Test: Vendors -10%'
-- GROUP BY 1;



SELECT
  scenario_name,
  SUM(total_cost_monthly_base) AS baseline_year_total,
  SUM(total_cost_monthly_scn)  AS scenario_year_total,
  SUM(delta_monthly)           AS delta_year
FROM analytics.fact_cost_monthly_2025_scenario
WHERE scenario_name = 'Test: Vendors -10%'
GROUP BY 1;



SELECT scenario_id, scenario_name, payroll_multiplier, vendors_multiplier, commission_multiplier, start_month, end_month
FROM analytics.scenario
ORDER BY scenario_id;


SELECT
  scenario_name,
  SUM(total_cost_monthly_base) AS baseline_year_total,
  SUM(total_cost_monthly_scn)  AS scenario_year_total,
  SUM(delta_monthly)           AS delta_year
FROM analytics.fact_cost_monthly_2025_scenario
GROUP BY 1
ORDER BY delta_year;