-- budgetdb_full_warehouse.sql
-- BudgetDB — Finance & Workforce Analytics Warehouse (full architecture)
--
-- This is the real architecture behind the BudgetDB portfolio piece, reduced from a 34-file
-- migration history into one readable build: raw -> dimensional -> fact -> scenario engine,
-- with the exec-facing QA reconciliation layer that makes it trustworthy. Anonymized sample
-- data below (same cast as the companion Vendor Spend & Ops ROI model) stands in for the real
-- payroll/vendor/commission/T&E CSVs the real warehouse imports.
--
-- Section map:
--   1. Schemas & raw landing tables
--   2. dim_employee (canonical employee dimension, normalized name keys)
--   3. Vendor spend (clean table + views)
--   4. Commission bridge + 3-tier fuzzy match (the hardest real-world join problem here)
--   5. T&E (travel & expense)
--   6. fact_cost_monthly (unified payroll + vendors + commission + T&E, one row per month)
--   7. Scenario engine (what-if multipliers over the fact table)
--   8. Exec QA reconciliation (source totals vs. fact totals, PASS/FAIL)
--   9. Exec dashboard views

BEGIN;

CREATE SCHEMA IF NOT EXISTS raw;
CREATE SCHEMA IF NOT EXISTS analytics;

-- =====================================================================================
-- 1. Raw landing tables (everything imported as TEXT first; real CSVs are messy)
-- =====================================================================================

CREATE TABLE raw.payroll_2025 (
    employee_id         TEXT,   -- email, the authoritative employee key
    first_name          TEXT,
    last_name           TEXT,
    team                TEXT,
    role_title          TEXT,
    employment_type     TEXT,
    base_salary_annual  TEXT,   -- imported as text: source data mixes "$120,000", "120000", blanks
    benefits_rate       TEXT,   -- mixes "0.20", "20%", blanks
    start_date          TEXT,
    end_date            TEXT
);

CREATE TABLE raw.vendors_2025 (
    vendor_id               TEXT,
    vendor_name              TEXT,
    canonical_vendor_name    TEXT,
    category                 TEXT,
    department_owner         TEXT,
    total_spend_2025         TEXT,
    avg_monthly_spend_2025   TEXT,
    recurring_flag           TEXT
);

CREATE TABLE raw.commission_2025 (
    employee_name          TEXT,  -- messy: "Jordan Blake (EE-CSM)", typos, trailing tags
    role_group              TEXT,
    commission_month_date   TEXT,
    commission_amount       TEXT
);

CREATE TABLE raw.te_2025 (
    month_date       TEXT,
    cost_category    TEXT,
    subcategory       TEXT,
    amount            TEXT
);

-- Sample rows (anonymized) - in production these are COPY'd in from CSV exports
INSERT INTO raw.payroll_2025 VALUES
    ('dana@wellops.example',    'Dana',    'Whitmore', 'Ops/Admin/Exec', 'Owner',                     'FTE',     '235000', '0.10', '2018-01-01', NULL),
    ('elena@wellops.example',   'Elena',   'Marsh',    'Ops/Admin/Exec', 'Creative Director',         'CON',     '144000', '0',    '2025-10-08', NULL),
    ('claire@wellops.example',  'Claire',  'Bennett',  'Ops/Admin/Exec', 'Executive Assistant',       'FTE',     '110000', '0.10', '2026-02-09', NULL),
    ('nadia@wellops.example',   'Nadia',   'Torres',   'Ops/Admin/Exec', 'RevOps Strategist',         'INT-CON', '100000', '0',    '2025-09-04', NULL),
    ('marcus@wellops.example',  'Marcus',  'Reyes',    'Ops/Admin/Exec', 'People Operations Manager', 'FTE',     '85000',  '0.10', '2026-02-16', NULL),
    ('mohammed@wellops.example','Mohammed','Beheiry',  'Ops/Admin/Exec', 'Business Ops Analyst',      'CON',     '85000',  '0',    '2026-02-02', NULL),
    ('diego@wellops.example',   'Diego',   'Fontaine', 'Ops/Admin/Exec', 'VP of Operations',          'FTE',     '225000', '0',    '2025-12-08', '2026-03-31'),
    ('jordan@wellops.example',  'Jordan',  'Blake',    'Sales',          'Account Executive',         'CON',     '60000',  '0',    '2023-08-28', NULL),
    ('maya@wellops.example',    'Maya',    'Ferris',   'Customer Success','CSM',                      'FTE',     '72000',  '0.10', '2022-08-08', '2026-02-13');

INSERT INTO raw.vendors_2025 VALUES
    ('v1', 'Hyros',   'Hyros',   'Attribution',    'Sales',            '13194.00', '1099.50', 'true'),
    ('v2', 'Monday',  'Monday.com','Project Mgmt', 'Ops/Admin/Exec',   '14872.09', '1239.34', 'true'),
    ('v3', 'DocuSign','DocuSign','Legal/Docs',     'Ops/Admin/Exec',   '23862.50', '1988.54', 'true'),
    ('v4', 'HubSpot', 'HubSpot', 'CRM',            'Sales',            '29492.35', '2457.70', 'true');

INSERT INTO raw.commission_2025 VALUES
    ('Jordan Blake (EE-AE)',  'CLOSER', '2025-01-01', '1877.76'),
    ('Jordn Blake (EE-AE)',   'CLOSER', '2025-02-01', '530.09'),    -- typo on purpose: tests the fuzzy match
    ('Maya Ferris (EE-CSM)',  'CSM',    '2025-01-01', '412.00'),
    ('Unknown Contractor',    'OTHER',  '2025-01-01', '150.00');    -- intentionally unmatchable

INSERT INTO raw.te_2025 VALUES
    ('2025-01-01', 'Travel',   'Flights',  '1245.30'),
    ('2025-01-01', 'Software', 'Ops Tools','389.00'),
    ('2025-02-01', 'Travel',   'Lodging',  '980.15');

-- =====================================================================================
-- 2. dim_employee - canonical employee dimension with normalized name-matching keys
-- =====================================================================================

CREATE TABLE analytics.dim_employee (
    employee_id          TEXT PRIMARY KEY,
    first_name           TEXT,
    last_name            TEXT,
    full_name            TEXT,
    team                 TEXT,
    role_title           TEXT,
    employment_type      TEXT,
    base_salary_annual   NUMERIC,
    benefits_rate        NUMERIC,
    start_date           DATE,
    end_date              DATE,
    name_key_full         TEXT,   -- normalized "firstlast", alnum only, for exact matching
    name_key_firstlast     TEXT    -- normalized first + last token (handles middle names)
);

INSERT INTO analytics.dim_employee (
    employee_id, first_name, last_name, full_name, team, role_title, employment_type,
    base_salary_annual, benefits_rate, start_date, end_date, name_key_full, name_key_firstlast
)
SELECT
    employee_id,
    first_name,
    last_name,
    TRIM(first_name || ' ' || last_name) AS full_name,
    team,
    role_title,
    employment_type,
    NULLIF(base_salary_annual, '')::NUMERIC,
    CASE WHEN benefits_rate LIKE '%\%%' THEN REPLACE(benefits_rate, '%', '')::NUMERIC / 100
         ELSE NULLIF(benefits_rate, '')::NUMERIC END,
    NULLIF(start_date, '')::DATE,
    NULLIF(end_date, '')::DATE,
    LOWER(REGEXP_REPLACE(TRIM(first_name || ' ' || last_name), '[^a-zA-Z0-9]+', '', 'g')),
    LOWER(REGEXP_REPLACE(TRIM(first_name || ' ' || last_name), '[^a-zA-Z0-9]+', '', 'g'))
FROM raw.payroll_2025;

CREATE INDEX idx_dim_employee_name_key_full ON analytics.dim_employee (name_key_full);

-- =====================================================================================
-- 3. Vendor spend - cleaned table + reporting views
-- =====================================================================================

CREATE TABLE analytics.vendors_2025_clean AS
SELECT
    vendor_id,
    vendor_name,
    canonical_vendor_name,
    category,
    department_owner,
    NULLIF(total_spend_2025, '')::NUMERIC AS total_spend_2025,
    NULLIF(avg_monthly_spend_2025, '')::NUMERIC AS avg_monthly_spend_2025,
    (recurring_flag = 'true') AS recurring_flag
FROM raw.vendors_2025;

DROP VIEW IF EXISTS analytics.v_top_vendors_2025;
CREATE VIEW analytics.v_top_vendors_2025 AS
SELECT vendor_name, category, department_owner, total_spend_2025, avg_monthly_spend_2025
FROM analytics.vendors_2025_clean
ORDER BY total_spend_2025 DESC;

-- =====================================================================================
-- 4. Commission bridge + 3-tier fuzzy match
--    This is the hardest real join problem in the warehouse: commission exports use
--    free-text names with role tags and typos ("Jordn Blake (EE-AE)"), payroll uses
--    structured first/last names. Rather than guess with a single fuzzy match, this
--    resolves in three explicit, auditable tiers and flags anything that still fails.
-- =====================================================================================

-- Tier 1: manual override map for names that will never match algorithmically (typos, aliases)
CREATE TABLE analytics.employee_commission_name_map (
    commission_employee_name_raw   TEXT PRIMARY KEY,
    employee_id                     TEXT NOT NULL REFERENCES analytics.dim_employee(employee_id),
    notes                            TEXT
);

INSERT INTO analytics.employee_commission_name_map VALUES
    ('Jordn Blake (EE-AE)', 'jordan@wellops.example', 'Typo in commission export: "Jordn" -> "Jordan"');

CREATE TABLE analytics.commission_2025_clean AS
SELECT
    employee_name AS employee_name_raw,
    TRIM(SPLIT_PART(employee_name, '(', 1)) AS employee_name_clean,
    role_group,
    NULLIF(commission_month_date, '')::DATE AS commission_month_date,
    NULLIF(commission_amount, '')::NUMERIC AS commission_amount,
    LOWER(REGEXP_REPLACE(TRIM(SPLIT_PART(employee_name, '(', 1)), '[^a-zA-Z0-9]+', '', 'g')) AS name_key_full
FROM raw.commission_2025;

DROP VIEW IF EXISTS analytics.commission_2025_joined_payroll;
CREATE VIEW analytics.commission_2025_joined_payroll AS
WITH mapped AS (
    SELECT c.*, m.employee_id AS mapped_employee_id
    FROM analytics.commission_2025_clean c
    LEFT JOIN analytics.employee_commission_name_map m
      ON m.commission_employee_name_raw = c.employee_name_raw
)
SELECT
    m.employee_name_raw,
    m.employee_name_clean,
    m.role_group,
    m.commission_month_date,
    m.commission_amount,
    COALESCE(m.mapped_employee_id, e.employee_id) AS employee_id,
    e.team,
    CASE
        WHEN m.mapped_employee_id IS NOT NULL THEN 'MAP_OVERRIDE'      -- Tier 1: manual override
        WHEN e.employee_id IS NOT NULL THEN 'NAME_KEY_EXACT'           -- Tier 2: normalized name match
        ELSE 'UNMATCHED'                                                -- Tier 3: nothing worked, flag it
    END AS match_method,
    CASE WHEN COALESCE(m.mapped_employee_id, e.employee_id) IS NULL THEN 'UNMATCHED' ELSE 'MATCHED' END AS join_status
FROM mapped m
LEFT JOIN analytics.dim_employee e
  ON m.mapped_employee_id IS NULL
 AND m.name_key_full = e.name_key_full;

-- Audit: every unmatched commission row, so nothing silently disappears
DROP VIEW IF EXISTS analytics.v_commission_unmatched;
CREATE VIEW analytics.v_commission_unmatched AS
SELECT employee_name_raw, commission_month_date, commission_amount
FROM analytics.commission_2025_joined_payroll
WHERE join_status = 'UNMATCHED'
ORDER BY commission_amount DESC;

DROP VIEW IF EXISTS analytics.commission_cost_2025_monthly;
CREATE VIEW analytics.commission_cost_2025_monthly AS
SELECT
    DATE_TRUNC('month', commission_month_date)::DATE AS month_date,
    SUM(commission_amount) AS commission_total_monthly
FROM analytics.commission_2025_joined_payroll
GROUP BY 1;

-- =====================================================================================
-- 5. T&E (travel & expense)
-- =====================================================================================

CREATE TABLE analytics.te_2025_clean AS
SELECT
    NULLIF(month_date, '')::DATE AS month_date,
    cost_category,
    subcategory,
    NULLIF(amount, '')::NUMERIC AS amount
FROM raw.te_2025;

DROP VIEW IF EXISTS analytics.te_cost_2025_monthly;
CREATE VIEW analytics.te_cost_2025_monthly AS
SELECT DATE_TRUNC('month', month_date)::DATE AS month_date, SUM(amount) AS te_total_monthly
FROM analytics.te_2025_clean
GROUP BY 1;

-- =====================================================================================
-- 6. fact_cost_monthly_2025 - the unified source of truth: one row per month, per category
--    Everything downstream (dashboards, scenario engine, exec QA) reads from this, never
--    from the individual source tables directly.
-- =====================================================================================

DROP VIEW IF EXISTS analytics.payroll_cost_2025_monthly;
CREATE VIEW analytics.payroll_cost_2025_monthly AS
SELECT
    d.month_start::DATE AS month_date,
    SUM(e.base_salary_annual / 12.0) AS payroll_salary_monthly,
    SUM((e.base_salary_annual / 12.0) * COALESCE(e.benefits_rate, 0)) AS payroll_benefits_monthly,
    SUM((e.base_salary_annual / 12.0) * (1 + COALESCE(e.benefits_rate, 0))) AS payroll_total_monthly
FROM analytics.dim_employee e
CROSS JOIN LATERAL (
    SELECT generate_series('2025-01-01'::DATE, '2025-12-01'::DATE, INTERVAL '1 month') AS month_start
) d
WHERE (e.start_date IS NULL OR e.start_date <= (d.month_start + INTERVAL '1 month' - INTERVAL '1 day')::DATE)
  AND (e.end_date   IS NULL OR e.end_date   >= d.month_start::DATE)
GROUP BY 1;

DROP VIEW IF EXISTS analytics.vendors_cost_2025_monthly;
CREATE VIEW analytics.vendors_cost_2025_monthly AS
SELECT m.month_date, SUM(v.avg_monthly_spend_2025) AS vendors_total_monthly
FROM (SELECT generate_series('2025-01-01'::DATE, '2025-12-01'::DATE, INTERVAL '1 month')::DATE AS month_date) m
CROSS JOIN analytics.vendors_2025_clean v
GROUP BY 1;

DROP VIEW IF EXISTS analytics.fact_cost_monthly_2025;
CREATE VIEW analytics.fact_cost_monthly_2025 AS
SELECT
    p.month_date,
    COALESCE(p.payroll_total_monthly, 0)         AS payroll_total_monthly,
    COALESCE(v.vendors_total_monthly, 0)         AS vendors_total_monthly,
    COALESCE(c.commission_total_monthly, 0)      AS commission_total_monthly,
    COALESCE(t.te_total_monthly, 0)              AS te_total_monthly,
    COALESCE(p.payroll_total_monthly, 0) + COALESCE(v.vendors_total_monthly, 0)
        + COALESCE(c.commission_total_monthly, 0) + COALESCE(t.te_total_monthly, 0) AS total_cost_monthly
FROM analytics.payroll_cost_2025_monthly p
LEFT JOIN analytics.vendors_cost_2025_monthly v ON v.month_date = p.month_date
LEFT JOIN analytics.commission_cost_2025_monthly c ON c.month_date = p.month_date
LEFT JOIN analytics.te_cost_2025_monthly t ON t.month_date = p.month_date;

-- =====================================================================================
-- 7. Scenario engine - what-if multipliers over the baseline fact table
-- =====================================================================================

CREATE TABLE analytics.scenario (
    scenario_id             BIGSERIAL PRIMARY KEY,
    scenario_name            TEXT UNIQUE NOT NULL,
    payroll_multiplier       NUMERIC NOT NULL DEFAULT 1.0,
    vendors_multiplier       NUMERIC NOT NULL DEFAULT 1.0,
    commission_multiplier    NUMERIC NOT NULL DEFAULT 1.0,
    start_month              DATE NOT NULL,
    end_month                 DATE NOT NULL
);

INSERT INTO analytics.scenario (scenario_name, payroll_multiplier, vendors_multiplier, commission_multiplier, start_month, end_month) VALUES
    ('Baseline',            1.00, 1.00, 1.00, '2025-01-01', '2025-12-01'),
    ('Vendors -10% review', 1.00, 0.90, 1.00, '2025-01-01', '2025-12-01');

DROP VIEW IF EXISTS analytics.fact_cost_monthly_2025_scenario;
CREATE VIEW analytics.fact_cost_monthly_2025_scenario AS
SELECT
    s.scenario_name,
    b.month_date,
    b.total_cost_monthly AS total_cost_monthly_base,
    (b.payroll_total_monthly * s.payroll_multiplier
     + b.vendors_total_monthly * s.vendors_multiplier
     + b.commission_total_monthly * s.commission_multiplier
     + b.te_total_monthly) AS total_cost_monthly_scenario,
    (b.payroll_total_monthly * s.payroll_multiplier
     + b.vendors_total_monthly * s.vendors_multiplier
     + b.commission_total_monthly * s.commission_multiplier
     + b.te_total_monthly) - 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;

-- =====================================================================================
-- 8. Exec QA reconciliation - compares source totals to fact totals, PASS/FAIL
--    Nothing reaches a leadership dashboard without passing this first.
-- =====================================================================================

DROP VIEW IF EXISTS analytics.v_exec_qa_checks_2025;
CREATE VIEW analytics.v_exec_qa_checks_2025 AS
WITH checks AS (
    SELECT 'Payroll' AS category,
           (SELECT ROUND(SUM(payroll_total_monthly), 2) FROM analytics.payroll_cost_2025_monthly) AS source_total,
           (SELECT ROUND(SUM(payroll_total_monthly), 2) FROM analytics.fact_cost_monthly_2025) AS fact_total
    UNION ALL
    SELECT 'Vendors',
           (SELECT ROUND(SUM(vendors_total_monthly), 2) FROM analytics.vendors_cost_2025_monthly),
           (SELECT ROUND(SUM(vendors_total_monthly), 2) FROM analytics.fact_cost_monthly_2025)
    UNION ALL
    SELECT 'Commission',
           (SELECT ROUND(SUM(commission_total_monthly), 2) FROM analytics.commission_cost_2025_monthly),
           (SELECT ROUND(SUM(commission_total_monthly), 2) FROM analytics.fact_cost_monthly_2025)
)
SELECT category, source_total, fact_total, ROUND(fact_total - source_total, 2) AS variance,
       CASE WHEN ROUND(fact_total - source_total, 2) = 0 THEN 'PASS' ELSE 'FAIL' END AS qa_status
FROM checks;

-- =====================================================================================
-- 9. Exec dashboard views
-- =====================================================================================

DROP VIEW IF EXISTS analytics.v_monthly_burn_mom_2025;
CREATE VIEW analytics.v_monthly_burn_mom_2025 AS
SELECT
    month_date,
    total_cost_monthly,
    LAG(total_cost_monthly) OVER (ORDER BY month_date) AS prior_month,
    ROUND(total_cost_monthly - LAG(total_cost_monthly) OVER (ORDER BY month_date), 2) AS mom_change
FROM analytics.fact_cost_monthly_2025;

COMMIT;

-- Example queries:
-- SELECT * FROM analytics.v_exec_qa_checks_2025;                              -- trust check before presenting
-- SELECT * FROM analytics.commission_2025_joined_payroll WHERE join_status='UNMATCHED';  -- audit the fuzzy match
-- SELECT * FROM analytics.fact_cost_monthly_2025_scenario WHERE scenario_name='Vendors -10% review';
