-- Revenue transactions
CREATE TABLE transactions (
    id VARCHAR PRIMARY KEY,
    customer_id VARCHAR,
    type VARCHAR,  -- 'new_business', 'expansion', 'contraction', 'churn', 'reactivation'
    arr_delta DECIMAL(12, 2),  -- Annual Recurring Revenue change
    mrr_delta DECIMAL(12, 2),  -- Monthly Recurring Revenue change
    occurred_at DATE
);
 
-- Expenses
CREATE TABLE expenses (
    id VARCHAR PRIMARY KEY,
    category VARCHAR,  -- 'cogs', 'sales', 'marketing', 'r_and_d', 'g_and_a'
    description VARCHAR,
    amount DECIMAL(12, 2),
    period DATE,
    is_recurring BOOLEAN
);
 
-- Headcount
CREATE TABLE employees (
    id VARCHAR PRIMARY KEY,
    name VARCHAR,
    department VARCHAR,
    annual_salary DECIMAL(12, 2),
    equity_pct DECIMAL(8, 6),
    start_date DATE,
    end_date DATE
);

SELECT 
    DATE_TRUNC('month', occurred_at) AS month,
    SUM(CASE WHEN type = 'new_business' THEN mrr_delta ELSE 0 END) AS new_mrr,
    SUM(CASE WHEN type = 'expansion' THEN mrr_delta ELSE 0 END) AS expansion_mrr,
    SUM(CASE WHEN type = 'contraction' THEN mrr_delta ELSE 0 END) AS contraction_mrr,
    SUM(CASE WHEN type = 'churn' THEN mrr_delta ELSE 0 END) AS churned_mrr,
    SUM(CASE WHEN type = 'reactivation' THEN mrr_delta ELSE 0 END) AS reactivation_mrr,
    SUM(mrr_delta) AS net_new_mrr,
    SUM(SUM(mrr_delta)) OVER (ORDER BY DATE_TRUNC('month', occurred_at) ROWS UNBOUNDED PRECEDING) AS ending_mrr
FROM transactions
GROUP BY month
ORDER BY month;

-- Monthly P&L
WITH revenue AS (
    SELECT 
        DATE_TRUNC('month', occurred_at) AS month,
        SUM(SUM(mrr_delta)) OVER (ORDER BY DATE_TRUNC('month', occurred_at) ROWS UNBOUNDED PRECEDING) AS mrr
    FROM transactions GROUP BY month
),
costs AS (
    SELECT 
        period AS month,
        SUM(CASE WHEN category = 'cogs' THEN amount ELSE 0 END) AS cogs,
        SUM(CASE WHEN category = 'sales' THEN amount ELSE 0 END) AS sales_cost,
        SUM(CASE WHEN category = 'marketing' THEN amount ELSE 0 END) AS marketing_cost,
        SUM(CASE WHEN category = 'r_and_d' THEN amount ELSE 0 END) AS r_and_d_cost,
        SUM(CASE WHEN category = 'g_and_a' THEN amount ELSE 0 END) AS g_and_a_cost
    FROM expenses
    GROUP BY period
)
SELECT 
    r.month,
    r.mrr AS revenue,
    c.cogs,
    r.mrr - c.cogs AS gross_profit,
    ROUND((r.mrr - c.cogs) * 100.0 / NULLIF(r.mrr, 0), 1) AS gross_margin_pct,
    c.sales_cost + c.marketing_cost AS s_and_m,
    c.r_and_d_cost AS r_and_d,
    c.g_and_a_cost AS g_and_a,
    r.mrr - c.cogs - c.sales_cost - c.marketing_cost - c.r_and_d_cost - c.g_and_a_cost AS ebitda
FROM revenue r
JOIN costs c ON r.month = c.month
ORDER BY r.month;

-- LTV by segment
WITH customer_revenue AS (
    SELECT 
        customer_id,
        MIN(occurred_at) AS first_revenue_date,
        MAX(occurred_at) AS last_revenue_date,
        SUM(CASE WHEN type != 'churn' THEN mrr_delta ELSE 0 END) AS total_mrr_added,
        BOOL_OR(type = 'churn') AS churned
    FROM transactions
    GROUP BY customer_id
),
ltv AS (
    SELECT 
        customer_id,
        total_mrr_added * 12 AS arr,
        DATEDIFF('month', first_revenue_date, COALESCE(last_revenue_date, CURRENT_DATE)) AS months_active,
        total_mrr_added AS avg_mrr
    FROM customer_revenue
)
SELECT 
    AVG(avg_mrr) AS avg_monthly_revenue,
    AVG(months_active) AS avg_customer_lifetime_months,
    AVG(avg_mrr * months_active) AS avg_ltv,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY avg_mrr * months_active) AS median_ltv
FROM ltv;

-- NRR by cohort
WITH cohort_mrr AS (
    SELECT 
        customer_id,
        DATE_TRUNC('quarter', MIN(occurred_at)) AS cohort_quarter,
        SUM(CASE WHEN occurred_at < DATE_TRUNC('quarter', MIN(occurred_at)) + INTERVAL '3 months'
                 THEN mrr_delta ELSE 0 END) AS starting_mrr
    FROM transactions
    GROUP BY customer_id
),
current_mrr AS (
    SELECT 
        t.customer_id,
        SUM(t.mrr_delta) AS current_mrr
    FROM transactions t
    JOIN cohort_mrr c ON t.customer_id = c.customer_id
    WHERE t.occurred_at >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY t.customer_id
)
SELECT 
    c.cohort_quarter,
    SUM(c.starting_mrr) AS starting_mrr,
    SUM(cur.current_mrr) AS current_mrr,
    ROUND(SUM(cur.current_mrr) * 100.0 / NULLIF(SUM(c.starting_mrr), 0), 1) AS nrr_pct
FROM cohort_mrr c
LEFT JOIN current_mrr cur ON c.customer_id = cur.customer_id
GROUP BY c.cohort_quarter
ORDER BY c.cohort_quarter;

-- Current headcount and payroll by department
SELECT 
    department,
    COUNT(*) AS headcount,
    SUM(annual_salary) AS annual_payroll,
    AVG(annual_salary) AS avg_salary,
    ROUND(SUM(annual_salary) / SUM(SUM(annual_salary)) OVER () * 100, 1) AS pct_of_total_payroll
FROM employees
WHERE (end_date IS NULL OR end_date > CURRENT_DATE)
    AND start_date <= CURRENT_DATE
GROUP BY department
ORDER BY annual_payroll DESC;
 
-- Monthly burn from headcount
SELECT 
    DATE_TRUNC('month', CURRENT_DATE) AS month,
    SUM(annual_salary / 12) AS monthly_payroll_burn,
    SUM(annual_salary / 12) * 1.25 AS fully_loaded_monthly_burn  -- 25% benefits/overhead
FROM employees
WHERE (end_date IS NULL OR end_date > CURRENT_DATE)
    AND start_date <= CURRENT_DATE;

WITH monthly_revenue AS (
    SELECT 
        DATE_TRUNC('month', occurred_at) AS month,
        SUM(SUM(mrr_delta)) OVER (ORDER BY DATE_TRUNC('month', occurred_at) ROWS UNBOUNDED PRECEDING) AS mrr
    FROM transactions GROUP BY month
),
monthly_expenses AS (
    SELECT period AS month, SUM(amount) AS total_expenses
    FROM expenses GROUP BY period
),
monthly_payroll AS (
    SELECT 
        DATE_TRUNC('month', CURRENT_DATE) AS month,
        SUM(annual_salary / 12) * 1.25 AS payroll
    FROM employees
    WHERE (end_date IS NULL OR end_date > CURRENT_DATE)
)
SELECT 
    r.month,
    r.mrr AS revenue,
    COALESCE(e.total_expenses, 0) + COALESCE(p.payroll, 0) AS total_expenses,
    r.mrr - (COALESCE(e.total_expenses, 0) + COALESCE(p.payroll, 0)) AS net_cash_flow,
    SUM(r.mrr - (COALESCE(e.total_expenses, 0) + COALESCE(p.payroll, 0))) 
        OVER (ORDER BY r.month ROWS UNBOUNDED PRECEDING) AS cumulative_cash_flow
FROM monthly_revenue r
LEFT JOIN monthly_expenses e ON r.month = e.month
LEFT JOIN monthly_payroll p ON r.month = p.month
ORDER BY r.month;