-- remittance_revenue_model.sql
-- Companion SQL rebuild of "Revenue Modeling - Remittances.xlsx" (payments/remittance fee &
-- revenue-projection model). No anonymization needed here - the source spreadsheet is a generic
-- pricing/fee schedule with no personal or company-identifying data.

BEGIN;

CREATE SCHEMA IF NOT EXISTS analytics;

-- ---------------------------------------------------------------------------
-- Fee schedule (from the "Calculation" tab: NRC = non-recurring, MRC = monthly recurring)
-- ---------------------------------------------------------------------------

DROP TABLE IF EXISTS analytics.remittance_fee_schedule CASCADE;
CREATE TABLE analytics.remittance_fee_schedule (
    fee_name        TEXT PRIMARY KEY,
    fee_type        TEXT NOT NULL CHECK (fee_type IN ('NRC', 'MRC', 'Per-Transaction')),
    amount          NUMERIC(10,4) NOT NULL,
    unit            TEXT NOT NULL
);

INSERT INTO analytics.remittance_fee_schedule (fee_name, fee_type, amount, unit) VALUES
    ('Website and Mobile App Implementation', 'NRC',              12500.00, 'one-time'),
    ('Solution Provider (RaaS)',               'NRC',              12000.00, 'one-time'),
    ('Monthly Recurring Fee',                  'MRC',               1800.00, 'per month'),
    ('Compliance Support Fee',                 'MRC',                500.00, 'per month'),
    ('Merchant Account Maintenance Fee',       'MRC',                250.00, 'per month'),
    ('ACH Fee (same day)',                     'Per-Transaction',      0.95, 'per transaction'),
    ('ACH Fee (next day)',                     'Per-Transaction',      0.75, 'per transaction'),
    ('KYC - Basic Verification',               'Per-Transaction',      1.50, 'per customer sign-up, valid 12 months'),
    ('KYC - ID Verification',                  'Per-Transaction',      0.15, 'one-time, per ID'),
    ('Bank Account Verification',              'Per-Transaction',      1.84, 'one-time, per bank account'),
    ('Debit Card Interchange Fee',             'Per-Transaction',      0.0125, 'pct of transaction'),
    ('Debit Card Network Fee',                 'Per-Transaction',      0.50, 'per transaction'),
    ('Transaction Risk Assessment Fee',        'Per-Transaction',      0.07, 'per transaction'),
    ('International Wire Fee (SWIFT)',         'Per-Transaction',     55.00, 'per transfer'),
    ('Domestic FedWire Fee',                   'Per-Transaction',     15.00, 'per transfer'),
    ('ACH Return Fee',                         'Per-Transaction',     35.00, 'per return');

-- ---------------------------------------------------------------------------
-- Transaction volume assumptions (from "Revenue Projections": tiered by avg transaction size)
-- ---------------------------------------------------------------------------

DROP TABLE IF EXISTS analytics.remittance_transaction_volume CASCADE;
CREATE TABLE analytics.remittance_transaction_volume (
    tier                    TEXT NOT NULL,
    transaction_type        TEXT NOT NULL,   -- Debit Card / ACH (Same Day) / ACH (Next Day)
    client_segment          TEXT NOT NULL,   -- New / Recurring
    avg_transaction_value   NUMERIC(10,2) NOT NULL,
    working_days_per_month  INT NOT NULL,
    transactions_per_day    NUMERIC(10,2) NOT NULL,
    PRIMARY KEY (tier, transaction_type, client_segment)
);

INSERT INTO analytics.remittance_transaction_volume
    (tier, transaction_type, client_segment, avg_transaction_value, working_days_per_month, transactions_per_day) VALUES
    ('Tier I',   'Debit Card',      'New',        200.00, 22, 10.00),
    ('Tier I',   'ACH (Same Day)',  'New',        200.00, 22, 10.00),
    ('Tier I',   'ACH (Next Day)',  'New',        200.00, 22, 10.00),
    ('Tier I',   'Debit Card',      'Recurring',  200.00, 22,  0.75),
    ('Tier II',  'Debit Card',      'New',        500.00, 22,  5.00),
    ('Tier II',  'ACH (Same Day)',  'New',        500.00, 22,  5.00),
    ('Tier II',  'ACH (Next Day)',  'New',        500.00, 22,  5.00),
    ('Tier III', 'Debit Card',      'New',       1000.00, 22,  2.00),
    ('Tier III', 'ACH (Same Day)',  'New',       1000.00, 22,  2.00),
    ('Tier III', 'ACH (Next Day)',  'New',       1000.00, 22,  2.00);

-- ---------------------------------------------------------------------------
-- Monthly revenue projection: volume x fee schedule, per-transaction fee applied by type
-- ---------------------------------------------------------------------------

DROP VIEW IF EXISTS analytics.v_remittance_monthly_revenue;
CREATE VIEW analytics.v_remittance_monthly_revenue AS
SELECT
    v.tier,
    v.transaction_type,
    v.client_segment,
    ROUND(v.working_days_per_month * v.transactions_per_day, 0) AS transactions_per_month,
    ROUND(v.working_days_per_month * v.transactions_per_day * v.avg_transaction_value, 2) AS volume_processed,
    f.amount AS per_transaction_fee,
    ROUND(
        v.working_days_per_month * v.transactions_per_day *
        CASE WHEN f.fee_name = 'Debit Card Interchange Fee'
             THEN v.avg_transaction_value * f.amount   -- percentage fee
             ELSE f.amount                              -- flat per-transaction fee
        END,
        2
    ) AS projected_monthly_fee_revenue
FROM analytics.remittance_transaction_volume v
JOIN analytics.remittance_fee_schedule f
  ON f.fee_name = CASE v.transaction_type
                       WHEN 'ACH (Same Day)' THEN 'ACH Fee (same day)'
                       WHEN 'ACH (Next Day)' THEN 'ACH Fee (next day)'
                       WHEN 'Debit Card'     THEN 'Debit Card Interchange Fee'
                   END
ORDER BY v.tier, v.transaction_type, v.client_segment;

DROP VIEW IF EXISTS analytics.v_remittance_revenue_summary;
CREATE VIEW analytics.v_remittance_revenue_summary AS
SELECT
    tier,
    SUM(transactions_per_month)          AS total_transactions,
    SUM(volume_processed)                AS total_volume_processed,
    SUM(projected_monthly_fee_revenue)   AS total_projected_fee_revenue
FROM analytics.v_remittance_monthly_revenue
GROUP BY tier
ORDER BY tier;

COMMIT;

-- Example queries:
-- SELECT * FROM analytics.v_remittance_monthly_revenue;
-- SELECT * FROM analytics.v_remittance_revenue_summary;   -- rolled up by tier
