-- Finance reporting views (deploy manually)
USE db_abos_v0.1;

CREATE OR REPLACE VIEW vw_wallet_ledger_summary AS
SELECT
    w.tenant_id,
    w.id AS wallet_id,
    w.wallet_name,
    w.balance,
    w.currency,
    es.id AS subcategory_id,
    es.subcategory_name,
    COALESCE(SUM(CASE WHEN le.entry_type = 'Debit' THEN le.amount ELSE 0 END), 0) AS total_debits,
    COALESCE(SUM(CASE WHEN le.entry_type = 'Credit' THEN le.amount ELSE 0 END), 0) AS total_credits
FROM dim_wallets w
LEFT JOIN fact_ledger_entries le ON le.wallet_id = w.id AND le.is_deleted = 0
LEFT JOIN dim_expense_subcategories es ON es.id = le.subcategory_id
WHERE w.is_deleted = 0
GROUP BY w.tenant_id, w.id, w.wallet_name, w.balance, w.currency, es.id, es.subcategory_name;

CREATE OR REPLACE VIEW vw_cash_flow AS
SELECT
    t.tenant_id,
    t.id AS transaction_id,
    t.from_wallet_id,
    fw.wallet_name AS from_wallet_name,
    t.to_wallet_id,
    tw.wallet_name AS to_wallet_name,
    t.amount,
    t.currency,
    t.transaction_date,
    t.status,
    t.reference,
    t.type
FROM fact_transactions t
LEFT JOIN dim_wallets fw ON fw.id = t.from_wallet_id
LEFT JOIN dim_wallets tw ON tw.id = t.to_wallet_id
WHERE t.is_deleted = 0;

-- NOTE: originally written against dim_bank_statements/fact_bank_transactions, which never
-- existed under those names — the real tables are fact_statements (joined to
-- dim_financial_entities for the bank/account label) and fact_statement_transactions, whose
-- cash_in_or_out enum is 'Cash In'/'Cash Out', not 'In'/'Out'. Fixed to match the actual schema.
CREATE OR REPLACE VIEW vw_bank_reconciliation AS
SELECT
    s.tenant_id,
    s.id AS statement_id,
    e.bank_or_issuer_name AS bank_name,
    e.account_number,
    s.beginning_balance,
    s.current_balance,
    COALESCE(SUM(CASE WHEN st.cash_in_or_out = 'Cash In' THEN st.amount ELSE 0 END), 0) AS total_cash_in,
    COALESCE(SUM(CASE WHEN st.cash_in_or_out = 'Cash Out' THEN st.amount ELSE 0 END), 0) AS total_cash_out,
    s.beginning_balance
        + COALESCE(SUM(CASE WHEN st.cash_in_or_out = 'Cash In' THEN st.amount WHEN st.cash_in_or_out = 'Cash Out' THEN -st.amount ELSE 0 END), 0) AS reconciled_balance,
    COUNT(st.id) AS transaction_count
FROM fact_statements s
JOIN dim_financial_entities e ON e.id = s.entity_id AND e.is_deleted = 0
LEFT JOIN fact_statement_transactions st ON st.statement_id = s.id AND st.is_deleted = 0
WHERE s.is_deleted = 0
GROUP BY s.tenant_id, s.id, e.bank_or_issuer_name, e.account_number, s.beginning_balance, s.current_balance;

CREATE OR REPLACE VIEW vw_commission_report AS
SELECT
    c.tenant_id,
    c.id AS commission_id,
    c.agent_id,
    c.agent_name,
    c.period,
    c.deal_ref,
    c.gross_amount,
    c.rate,
    c.earned,
    c.bonus,
    c.total,
    c.currency,
    c.status,
    c.paid_date
FROM fact_commissions c
WHERE c.is_deleted = 0;