⛵Lineage modeling

-- Raw transaction data from various systems
CREATE TABLE raw_transactions (
    transaction_id BIGINT PRIMARY KEY,
    transaction_date DATE,
    posting_date DATE,
    account_id INT,
    counterparty_id INT,
    amount DECIMAL(15,2),
    currency VARCHAR(3),
    transaction_type VARCHAR(50),
    department_id INT,
    project_id INT,
    raw_transaction_data JSON
);

-- First level: Cleaned and standardized transactions
CREATE TABLE clean_transactions AS
SELECT
    transaction_id,
    transaction_date,
    posting_date,
    account_id,
    counterparty_id,
    amount,
    currency,
    transaction_type,
    department_id,
    project_id,
    raw_transaction_data->>'description' AS transaction_description,
    raw_transaction_data->>'reference_number' AS reference_number
FROM raw_transactions;

-- Second level: Currency conversion to company's base currency (assuming USD)
CREATE TABLE normalized_transactions AS
SELECT
    t.*,
    CASE 
        WHEN t.currency = 'USD' THEN t.amount
        ELSE t.amount * e.exchange_rate
    END AS amount_usd
FROM clean_transactions t
LEFT JOIN exchange_rates e ON t.currency = e.currency AND t.transaction_date = e.date;

-- Third level: Daily account balances
CREATE TABLE daily_account_balances AS
SELECT
    posting_date,
    account_id,
    SUM(amount_usd) AS daily_net_change,
    SUM(SUM(amount_usd)) OVER (PARTITION BY account_id ORDER BY posting_date) AS running_balance
FROM normalized_transactions
GROUP BY posting_date, account_id;

-- Fourth level: Monthly financial statements
CREATE TABLE monthly_financial_statements AS
SELECT
    DATE_TRUNC('month', posting_date) AS month,
    a.account_type,
    SUM(CASE WHEN a.account_type IN ('Asset', 'Expense') THEN daily_net_change ELSE -daily_net_change END) AS net_change,
    SUM(CASE WHEN a.account_type IN ('Asset', 'Expense') THEN running_balance ELSE -running_balance END) AS end_balance
FROM daily_account_balances b
JOIN accounts a ON b.account_id = a.account_id
GROUP BY DATE_TRUNC('month', posting_date), a.account_type;

-- Fifth level: Quarterly financial ratios
CREATE TABLE quarterly_financial_ratios AS
WITH quarterly_financials AS (
    SELECT
        DATE_TRUNC('quarter', month) AS quarter,
        account_type,
        SUM(net_change) AS quarterly_change,
        SUM(end_balance) AS quarter_end_balance
    FROM monthly_financial_statements
    GROUP BY DATE_TRUNC('quarter', month), account_type
)
SELECT
    quarter,
    SUM(CASE WHEN account_type = 'Asset' THEN quarter_end_balance ELSE 0 END) AS total_assets,
    SUM(CASE WHEN account_type = 'Liability' THEN quarter_end_balance ELSE 0 END) AS total_liabilities,
    SUM(CASE WHEN account_type = 'Equity' THEN quarter_end_balance ELSE 0 END) AS total_equity,
    SUM(CASE WHEN account_type = 'Revenue' THEN quarterly_change ELSE 0 END) AS total_revenue,
    SUM(CASE WHEN account_type = 'Expense' THEN quarterly_change ELSE 0 END) AS total_expenses,
    SUM(CASE WHEN account_type = 'Revenue' THEN quarterly_change ELSE 0 END) - 
    SUM(CASE WHEN account_type = 'Expense' THEN quarterly_change ELSE 0 END) AS net_income,
    (SUM(CASE WHEN account_type = 'Revenue' THEN quarterly_change ELSE 0 END) - 
     SUM(CASE WHEN account_type = 'Expense' THEN quarterly_change ELSE 0 END)) /
    NULLIF(SUM(CASE WHEN account_type = 'Revenue' THEN quarterly_change ELSE 0 END), 0) AS profit_margin,
    SUM(CASE WHEN account_type = 'Asset' THEN quarter_end_balance ELSE 0 END) /
    NULLIF(SUM(CASE WHEN account_type = 'Liability' THEN quarter_end_balance ELSE 0 END), 0) AS current_ratio
FROM quarterly_financials
GROUP BY quarter;

-- User information
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP,
    last_login TIMESTAMP,
    is_active BOOLEAN
);