new-site/api/migrations/011_sales_agents.sql

-- 011_sales_agents.sql
-- Sales agent referral system with commission tracking.
-- Agents get auto-generated REF-XXXXX codes. Clients get 5% off service fee.
-- Commissions paid 14 days after order delivery.

BEGIN;

CREATE TABLE IF NOT EXISTS sales_agents (
    id                  SERIAL PRIMARY KEY,
    agent_code          TEXT UNIQUE NOT NULL,        -- REF-XXXXX (auto-generated)
    discount_code_id    INTEGER REFERENCES discount_codes(id),

    -- Agent info
    name                TEXT NOT NULL,
    email               TEXT NOT NULL UNIQUE,
    phone               TEXT,
    company             TEXT,

    -- ERPNext user link
    erpnext_user        TEXT,                        -- ERPNext username for portal login

    -- Payout info (for Relay ACH)
    bank_name           TEXT,
    bank_routing        TEXT,
    bank_account_last4  TEXT,                        -- last 4 only in plain text
    bank_account_encrypted TEXT,                     -- full account in ERPNext Sensitive ID
    payout_email        TEXT,                        -- PayPal/e-transfer email alternative

    -- Commission config (defaults, overridable per agent)
    commission_type     TEXT DEFAULT 'flat',         -- 'flat' or 'percent'
    commission_default_cents INTEGER DEFAULT 30000,  -- $300 default for Canada CRTC
    commission_pct      INTEGER DEFAULT 10,          -- 10% for compliance services

    -- Per-service commission overrides (JSON)
    -- e.g., {"canada-crtc": 30000, "formation-basic": 5000, "formation-complete": 5000, "bundle": 10000}
    commission_overrides JSONB DEFAULT '{}',

    -- Stats (denormalized for fast queries)
    total_referrals     INTEGER DEFAULT 0,
    total_earned_cents  INTEGER DEFAULT 0,
    total_paid_cents    INTEGER DEFAULT 0,
    total_pending_cents INTEGER DEFAULT 0,

    -- Status
    active              BOOLEAN DEFAULT TRUE,
    onboarded_at        TIMESTAMPTZ,
    notes               TEXT,
    created_at          TIMESTAMPTZ DEFAULT now(),
    updated_at          TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_agents_code ON sales_agents(agent_code);
CREATE INDEX IF NOT EXISTS idx_agents_email ON sales_agents(email);
CREATE INDEX IF NOT EXISTS idx_agents_active ON sales_agents(active);

-- Commission ledger — one row per earned commission
CREATE TABLE IF NOT EXISTS commission_ledger (
    id                  SERIAL PRIMARY KEY,
    agent_id            INTEGER NOT NULL REFERENCES sales_agents(id),
    agent_code          TEXT NOT NULL,

    -- What was sold
    order_type          TEXT NOT NULL,               -- 'canada_crtc', 'formation', 'service', 'bundle'
    order_id            INTEGER,
    order_number        TEXT NOT NULL,
    service_slug        TEXT,
    customer_name       TEXT NOT NULL,
    customer_email      TEXT NOT NULL,

    -- Money
    order_amount_cents  INTEGER NOT NULL,            -- what the client paid (total)
    discount_cents      INTEGER DEFAULT 0,           -- 5% discount given to client
    commission_cents    INTEGER NOT NULL,             -- what the agent earns

    -- Lifecycle
    -- pending: order placed, not yet delivered
    -- eligible: order delivered + 14 day holdback passed
    -- approved: admin reviewed and approved for payout
    -- processing: payout being sent via Relay
    -- paid: money sent to agent
    -- cancelled: order was cancelled/refunded, commission voided
    status              TEXT DEFAULT 'pending' CHECK (status IN (
        'pending', 'eligible', 'approved', 'processing', 'paid', 'cancelled'
    )),

    -- Dates
    order_delivered_at  TIMESTAMPTZ,
    eligible_at         TIMESTAMPTZ,                 -- delivered_at + 14 days
    approved_by         INTEGER,                     -- admin_users.id
    approved_at         TIMESTAMPTZ,
    paid_at             TIMESTAMPTZ,
    payment_method      TEXT,                        -- 'relay_ach', 'paypal', 'check'
    payment_reference   TEXT,                        -- transaction ID or check number

    notes               TEXT,
    created_at          TIMESTAMPTZ DEFAULT now(),
    updated_at          TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_commission_agent ON commission_ledger(agent_id);
CREATE INDEX IF NOT EXISTS idx_commission_status ON commission_ledger(status);
CREATE INDEX IF NOT EXISTS idx_commission_eligible ON commission_ledger(eligible_at);
CREATE INDEX IF NOT EXISTS idx_commission_order ON commission_ledger(order_type, order_id);

COMMIT;