-- vendor_spend_ops_roi.sql
-- Companion SQL rebuild of "Vendor Spend & Ops ROI Analytics.xlsx"
-- Same underlying analysis as the spreadsheet (vendor ROI ranking, staffing-weighted
-- Ops budget rollup), reimplemented as a Postgres schema + views. Sample data below is
-- anonymized and reduced from the real workbook, matching the row shapes/values used there.

BEGIN;

CREATE SCHEMA IF NOT EXISTS analytics;

-- ---------------------------------------------------------------------------
-- Raw tables
-- ---------------------------------------------------------------------------

DROP TABLE IF EXISTS analytics.ops_personnel CASCADE;
CREATE TABLE analytics.ops_personnel (
    full_name           TEXT PRIMARY KEY,
    title               TEXT NOT NULL,
    employee_type       TEXT NOT NULL,              -- FTE / CON / INT-CON
    start_date          DATE NOT NULL,
    end_date            DATE,                        -- NULL = still active
    monthly_salary      NUMERIC(12,2) NOT NULL,
    monthly_benefits    NUMERIC(12,2) NOT NULL DEFAULT 0
);

INSERT INTO analytics.ops_personnel (full_name, title, employee_type, start_date, end_date, monthly_salary, monthly_benefits) VALUES
    ('Dana Whitmore',    'Owner',                     'FTE',     '2018-01-01', NULL,         19583.33, 1958.33),
    ('Elena Marsh',      'Creative Director',         'CON',     '2025-10-08', NULL,         12000.00, 0),
    ('Claire Bennett',   'Executive Assistant',       'FTE',     '2026-02-09', NULL,          9166.67,  916.67),
    ('Nadia Torres',     'RevOps Strategist',         'INT-CON', '2025-09-04', NULL,          8333.33, 0),
    ('Marcus Reyes',     'People Operations Manager', 'FTE',     '2026-02-16', NULL,          7083.33,  708.33),
    ('Mohammed Beheiry', 'Business Ops Analyst',      'CON',     '2026-02-02', NULL,          7083.33, 0),
    ('Diego Fontaine',   'VP of Operations',          'FTE',     '2025-12-08', '2026-03-31',  18750.00, 0),
    ('Morgan Ellis',     'President, WO Enterprise',  'FTE',     '2020-10-05', '2026-01-27',  14166.67, 1416.67),
    ('Jordan Blake',     'Executive Assistant',       'CON',     '2023-08-28', NULL,          1250.00, 0),
    ('Maya Ferris',      'Executive Assistant',       'FTE',     '2022-08-08', '2026-02-13',   6666.67,  666.67),
    ('Priya Nandan',     'Curriculum Designer',       'CON',     '2024-10-07', NULL,           693.33, 0);

DROP TABLE IF EXISTS analytics.vendor_spend CASCADE;
CREATE TABLE analytics.vendor_spend (
    vendor              TEXT PRIMARY KEY,
    total_vendor_cost    NUMERIC(12,2) NOT NULL,
    teams_using_tool     INT NOT NULL,
    shared_across_teams  BOOLEAN NOT NULL
);

INSERT INTO analytics.vendor_spend (vendor, total_vendor_cost, teams_using_tool, shared_across_teams) VALUES
    ('Hyros',      13194.00, 2, TRUE),
    ('OnceHub',     7012.58, 1, FALSE),
    ('Hubspot',    29492.35, 4, TRUE),
    ('DocuSign',   23862.50, 4, TRUE),
    ('CRM Closer', 12101.08, 4, TRUE),
    ('Zoom',       17115.89, 1, FALSE),
    ('Monday.com', 14872.09, 5, TRUE),
    ('Aloware',     4934.32, 3, TRUE),
    ('Zapier',      7620.31, 1, FALSE),
    ('Slack',       5646.76, 1, FALSE);

-- ---------------------------------------------------------------------------
-- Views - staffed-month weighted Ops budget (mirrors OpsAdminExec_Personnel -> Ops_Budget_Summary)
-- ---------------------------------------------------------------------------

DROP VIEW IF EXISTS analytics.v_ops_months_active_2026;
CREATE VIEW analytics.v_ops_months_active_2026 AS
SELECT
    full_name,
    title,
    employee_type,
    monthly_salary,
    monthly_benefits,
    GREATEST(
        0,
        (DATE_PART('year', LEAST(COALESCE(end_date, DATE '2026-12-31'), DATE '2026-12-31'))::int * 12
         + DATE_PART('month', LEAST(COALESCE(end_date, DATE '2026-12-31'), DATE '2026-12-31'))::int)
        -
        (DATE_PART('year', GREATEST(start_date, DATE '2026-01-01'))::int * 12
         + DATE_PART('month', GREATEST(start_date, DATE '2026-01-01'))::int)
        + 1
    ) AS months_active_2026
FROM analytics.ops_personnel
WHERE start_date <= DATE '2026-12-31'
  AND (end_date IS NULL OR end_date >= DATE '2026-01-01');

DROP VIEW IF EXISTS analytics.v_ops_headcount_cost;
CREATE VIEW analytics.v_ops_headcount_cost AS
SELECT
    full_name,
    title,
    employee_type,
    months_active_2026,
    ROUND(monthly_salary * months_active_2026, 2)                       AS salary_paid,
    ROUND(monthly_benefits * months_active_2026, 2)                     AS benefits_paid,
    ROUND((monthly_salary + monthly_benefits) * months_active_2026, 2)  AS total_loaded_cost
FROM analytics.v_ops_months_active_2026
ORDER BY total_loaded_cost DESC;

DROP VIEW IF EXISTS analytics.v_ops_budget_summary;
CREATE VIEW analytics.v_ops_budget_summary AS
WITH totals AS (
    SELECT
        SUM(salary_paid)   AS payroll_total,
        SUM(benefits_paid) AS benefits_total,
        SUM(total_loaded_cost) AS grand_total,
        COUNT(*)           AS active_headcount
    FROM analytics.v_ops_headcount_cost
)
SELECT
    'Payroll'  AS category, payroll_total  AS annual_cost, ROUND(payroll_total  / 12, 2) AS avg_monthly,
    ROUND(payroll_total  / NULLIF(grand_total, 0), 4) AS pct_of_total
FROM totals
UNION ALL
SELECT
    'Benefits' AS category, benefits_total AS annual_cost, ROUND(benefits_total / 12, 2) AS avg_monthly,
    ROUND(benefits_total / NULLIF(grand_total, 0), 4) AS pct_of_total
FROM totals
UNION ALL
SELECT
    'Total Ops Budget' AS category, grand_total AS annual_cost, ROUND(grand_total / 12, 2) AS avg_monthly,
    1.0 AS pct_of_total
FROM totals;

-- ---------------------------------------------------------------------------
-- Vendor ROI - annualized cost per active employee, ranked, for renewal review
-- ---------------------------------------------------------------------------

DROP VIEW IF EXISTS analytics.v_vendor_roi;
CREATE VIEW analytics.v_vendor_roi AS
SELECT
    vendor,
    total_vendor_cost,
    teams_using_tool,
    shared_across_teams,
    ROUND(
        total_vendor_cost / NULLIF((SELECT COUNT(*) FROM analytics.v_ops_headcount_cost), 0),
        2
    ) AS cost_per_active_employee,
    RANK() OVER (ORDER BY total_vendor_cost DESC) AS spend_rank
FROM analytics.vendor_spend
ORDER BY total_vendor_cost DESC;

-- Quick QA check: flags vendors worth reviewing before renewal (high cost, single-team use)
DROP VIEW IF EXISTS analytics.v_vendor_renewal_review;
CREATE VIEW analytics.v_vendor_renewal_review AS
SELECT vendor, total_vendor_cost, teams_using_tool, cost_per_active_employee
FROM analytics.v_vendor_roi
WHERE shared_across_teams = FALSE
  AND total_vendor_cost > 5000
ORDER BY total_vendor_cost DESC;

COMMIT;

-- Example queries:
-- SELECT * FROM analytics.v_ops_budget_summary;
-- SELECT * FROM analytics.v_vendor_roi LIMIT 10;
-- SELECT * FROM analytics.v_vendor_renewal_review;   -- "what to validate before renewal"
