CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE users (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    email text NOT NULL,
    full_name text NOT NULL,
    password_hash text NOT NULL,
    role text NOT NULL DEFAULT 'customer' CHECK (role IN ('customer', 'admin')),
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX users_email_lower_uniq ON users (lower(email));

CREATE TABLE kyc_profiles (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL UNIQUE REFERENCES users(id) ON DELETE RESTRICT,
    legal_name text NOT NULL,
    date_of_birth date NOT NULL,
    country char(2) NOT NULL,
    address_line1 text NOT NULL,
    city text NOT NULL,
    postal_code text NOT NULL,
    status text NOT NULL DEFAULT 'not_started' CHECK (status IN ('not_started', 'pending', 'verified', 'rejected', 'manual_review')),
    provider text NOT NULL DEFAULT 'local_identity',
    external_verification_id text UNIQUE,
    rejection_reason text,
    submitted_at timestamptz,
    verified_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX kyc_profiles_status_idx ON kyc_profiles (status);
CREATE INDEX kyc_profiles_user_id_status_idx ON kyc_profiles (user_id, status);

CREATE TABLE kyc_documents (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    kyc_profile_id uuid NOT NULL REFERENCES kyc_profiles(id) ON DELETE RESTRICT,
    document_type text NOT NULL CHECK (document_type IN ('passport', 'national_id', 'drivers_license')),
    document_number text,
    country char(2) NOT NULL,
    status text NOT NULL DEFAULT 'uploaded' CHECK (status IN ('uploaded', 'verified', 'rejected')),
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX kyc_documents_profile_id_idx ON kyc_documents (kyc_profile_id);

CREATE TABLE aml_screenings (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    kyc_profile_id uuid REFERENCES kyc_profiles(id) ON DELETE SET NULL,
    screening_type text NOT NULL,
    provider text NOT NULL,
    status text NOT NULL CHECK (status IN ('clear', 'review', 'hit')),
    risk_score integer NOT NULL CHECK (risk_score BETWEEN 0 AND 100),
    matched boolean NOT NULL DEFAULT false,
    match_details jsonb NOT NULL DEFAULT '{}'::jsonb,
    external_screening_id text,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX aml_screenings_user_id_created_at_idx ON aml_screenings (user_id, created_at DESC);
CREATE INDEX aml_screenings_status_idx ON aml_screenings (status);

CREATE TABLE aml_cases (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    aml_screening_id uuid REFERENCES aml_screenings(id) ON DELETE SET NULL,
    case_type text NOT NULL,
    status text NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'reviewing', 'closed', 'escalated')),
    severity text NOT NULL CHECK (severity IN ('low', 'medium', 'high', 'critical')),
    reason text NOT NULL,
    resolution_note text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX aml_cases_user_id_status_idx ON aml_cases (user_id, status);
CREATE INDEX aml_cases_status_updated_at_idx ON aml_cases (status, updated_at DESC);

CREATE TABLE currencies (
    code char(3) PRIMARY KEY,
    name text NOT NULL,
    minor_unit smallint NOT NULL DEFAULT 2 CHECK (minor_unit BETWEEN 0 AND 4),
    enabled boolean NOT NULL DEFAULT true,
    created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO currencies (code, name, minor_unit) VALUES
    ('EUR', 'Euro', 2),
    ('USD', 'US Dollar', 2),
    ('GBP', 'British Pound', 2),
    ('CHF', 'Swiss Franc', 2),
    ('CAD', 'Canadian Dollar', 2),
    ('NGN', 'Nigerian Naira', 2);

CREATE TABLE wallets (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    name text NOT NULL,
    status text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'frozen', 'closed')),
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX wallets_user_id_created_at_idx ON wallets (user_id, created_at DESC);

CREATE TABLE wallet_balances (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    wallet_id uuid NOT NULL REFERENCES wallets(id) ON DELETE RESTRICT,
    currency char(3) NOT NULL REFERENCES currencies(code) ON DELETE RESTRICT,
    available_balance_cents bigint NOT NULL DEFAULT 0 CHECK (available_balance_cents >= 0),
    reserved_balance_cents bigint NOT NULL DEFAULT 0 CHECK (reserved_balance_cents >= 0),
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (wallet_id, currency)
);

CREATE INDEX wallet_balances_wallet_id_idx ON wallet_balances (wallet_id);
CREATE INDEX wallet_balances_currency_idx ON wallet_balances (currency);

CREATE TABLE crypto_assets (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    symbol text NOT NULL,
    name text NOT NULL,
    network text NOT NULL,
    asset_type text NOT NULL CHECK (asset_type IN ('native', 'token', 'stablecoin')),
    contract_address text,
    decimals smallint NOT NULL CHECK (decimals BETWEEN 0 AND 30),
    enabled boolean NOT NULL DEFAULT true,
    created_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (symbol, network)
);

INSERT INTO crypto_assets (symbol, name, network, asset_type, contract_address, decimals) VALUES
    ('BTC', 'Bitcoin', 'bitcoin', 'native', NULL, 8),
    ('ETH', 'Ether', 'ethereum', 'native', NULL, 18),
    ('USDC', 'USD Coin', 'ethereum', 'stablecoin', '0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48', 6),
    ('USDT', 'Tether USD', 'ethereum', 'stablecoin', '0xdAC17F958D2ee523a2206206994597C13D831ec7', 6),
    ('SOL', 'Solana', 'solana', 'native', NULL, 9);

CREATE INDEX crypto_assets_enabled_idx ON crypto_assets (enabled);

CREATE TABLE crypto_wallets (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    name text NOT NULL,
    custody_provider text NOT NULL DEFAULT 'local_custody',
    external_wallet_id text NOT NULL UNIQUE,
    status text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'frozen', 'closed')),
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX crypto_wallets_user_id_created_at_idx ON crypto_wallets (user_id, created_at DESC);
CREATE INDEX crypto_wallets_status_idx ON crypto_wallets (status);

CREATE TABLE crypto_wallet_balances (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    crypto_wallet_id uuid NOT NULL REFERENCES crypto_wallets(id) ON DELETE RESTRICT,
    crypto_asset_id uuid NOT NULL REFERENCES crypto_assets(id) ON DELETE RESTRICT,
    available_amount_base_units numeric(78, 0) NOT NULL DEFAULT 0 CHECK (available_amount_base_units >= 0),
    reserved_amount_base_units numeric(78, 0) NOT NULL DEFAULT 0 CHECK (reserved_amount_base_units >= 0),
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (crypto_wallet_id, crypto_asset_id)
);

CREATE INDEX crypto_wallet_balances_wallet_id_idx ON crypto_wallet_balances (crypto_wallet_id);
CREATE INDEX crypto_wallet_balances_asset_id_idx ON crypto_wallet_balances (crypto_asset_id);

CREATE TABLE crypto_addresses (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    crypto_wallet_id uuid NOT NULL REFERENCES crypto_wallets(id) ON DELETE RESTRICT,
    crypto_asset_id uuid NOT NULL REFERENCES crypto_assets(id) ON DELETE RESTRICT,
    network text NOT NULL,
    address text NOT NULL UNIQUE,
    address_tag text,
    status text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'disabled')),
    created_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (crypto_wallet_id, crypto_asset_id, network)
);

CREATE INDEX crypto_addresses_wallet_id_idx ON crypto_addresses (crypto_wallet_id);
CREATE INDEX crypto_addresses_asset_id_idx ON crypto_addresses (crypto_asset_id);

CREATE TABLE accounts (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    wallet_id uuid REFERENCES wallets(id) ON DELETE RESTRICT,
    account_number text NOT NULL UNIQUE,
    iban text NOT NULL UNIQUE CHECK (iban ~ '^[A-Z]{2}[0-9]{2}[A-Z0-9]{11,30}$'),
    bic text NOT NULL CHECK (bic ~ '^[A-Z]{4}[A-Z]{2}[A-Z0-9]{2}([A-Z0-9]{3})?$'),
    bank_provider text NOT NULL,
    external_account_id text NOT NULL UNIQUE,
    currency char(3) NOT NULL REFERENCES currencies(code) ON DELETE RESTRICT,
    balance_cents bigint NOT NULL DEFAULT 0 CHECK (balance_cents >= 0),
    status text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'frozen', 'closed')),
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX accounts_user_id_idx ON accounts (user_id);
CREATE INDEX accounts_wallet_id_idx ON accounts (wallet_id);
CREATE INDEX accounts_bank_provider_idx ON accounts (bank_provider);

CREATE TABLE savings_goals (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    account_id uuid NOT NULL REFERENCES accounts(id) ON DELETE RESTRICT,
    name text NOT NULL,
    currency char(3) NOT NULL REFERENCES currencies(code) ON DELETE RESTRICT,
    target_amount_cents bigint NOT NULL CHECK (target_amount_cents > 0),
    current_amount_cents bigint NOT NULL DEFAULT 0 CHECK (current_amount_cents >= 0),
    status text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'paused', 'completed', 'closed')),
    target_date date,
    completed_at timestamptz,
    closed_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CHECK (current_amount_cents <= target_amount_cents)
);

CREATE INDEX savings_goals_user_id_created_at_idx ON savings_goals (user_id, created_at DESC);
CREATE INDEX savings_goals_account_id_idx ON savings_goals (account_id);
CREATE INDEX savings_goals_status_idx ON savings_goals (status);

CREATE TABLE savings_goal_transactions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    savings_goal_id uuid NOT NULL REFERENCES savings_goals(id) ON DELETE RESTRICT,
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    account_id uuid NOT NULL REFERENCES accounts(id) ON DELETE RESTRICT,
    transaction_type text NOT NULL CHECK (transaction_type IN ('contribution', 'withdrawal', 'close_return')),
    amount_cents bigint NOT NULL CHECK (amount_cents > 0),
    currency char(3) NOT NULL REFERENCES currencies(code) ON DELETE RESTRICT,
    description text,
    idempotency_key text,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX savings_goal_transactions_user_idempotency_key_uniq
    ON savings_goal_transactions (user_id, idempotency_key)
    WHERE idempotency_key IS NOT NULL;

CREATE INDEX savings_goal_transactions_goal_created_at_idx ON savings_goal_transactions (savings_goal_id, created_at DESC);
CREATE INDEX savings_goal_transactions_user_created_at_idx ON savings_goal_transactions (user_id, created_at DESC);

CREATE TABLE virtual_cards (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    account_id uuid NOT NULL REFERENCES accounts(id) ON DELETE RESTRICT,
    external_card_id text NOT NULL UNIQUE,
    nickname text,
    cardholder_name text NOT NULL,
    network text NOT NULL CHECK (network IN ('visa', 'mastercard')),
    card_type text NOT NULL DEFAULT 'multi_use' CHECK (card_type IN ('multi_use', 'single_use')),
    pan_fingerprint text NOT NULL UNIQUE,
    last4 char(4) NOT NULL,
    exp_month smallint NOT NULL CHECK (exp_month BETWEEN 1 AND 12),
    exp_year smallint NOT NULL,
    spending_limit_cents bigint NOT NULL DEFAULT 0 CHECK (spending_limit_cents >= 0),
    limit_sync_status text NOT NULL DEFAULT 'synced' CHECK (limit_sync_status IN ('pending', 'synced', 'failed')),
    limit_synced_at timestamptz,
    limit_sync_error text,
    status text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'frozen', 'canceled')),
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX virtual_cards_user_id_created_at_idx ON virtual_cards (user_id, created_at DESC);
CREATE INDEX virtual_cards_account_id_idx ON virtual_cards (account_id);
CREATE INDEX virtual_cards_status_idx ON virtual_cards (status);

CREATE TABLE card_authorizations (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    virtual_card_id uuid NOT NULL REFERENCES virtual_cards(id) ON DELETE RESTRICT,
    external_authorization_id text NOT NULL UNIQUE,
    amount_cents bigint NOT NULL CHECK (amount_cents > 0),
    currency char(3) NOT NULL REFERENCES currencies(code) ON DELETE RESTRICT,
    merchant_name text,
    merchant_category_code text,
    merchant_country text,
    status text NOT NULL CHECK (status IN ('approved', 'declined', 'cleared', 'reversed')),
    decline_reason text,
    raw_event jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX card_authorizations_card_created_at_idx ON card_authorizations (virtual_card_id, created_at DESC);
CREATE INDEX card_authorizations_status_idx ON card_authorizations (status);

CREATE TABLE card_issuer_webhook_events (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    external_event_id text NOT NULL UNIQUE,
    event_type text NOT NULL,
    payload jsonb NOT NULL,
    processed_at timestamptz NOT NULL DEFAULT now(),
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX card_issuer_webhook_events_type_created_at_idx ON card_issuer_webhook_events (event_type, created_at DESC);

CREATE TABLE transfers (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    from_account_id uuid NOT NULL REFERENCES accounts(id) ON DELETE RESTRICT,
    to_account_id uuid NOT NULL REFERENCES accounts(id) ON DELETE RESTRICT,
    amount_cents bigint NOT NULL CHECK (amount_cents > 0),
    currency char(3) NOT NULL REFERENCES currencies(code) ON DELETE RESTRICT,
    status text NOT NULL CHECK (status IN ('pending', 'completed', 'failed', 'reversed')),
    description text,
    idempotency_key text,
    created_at timestamptz NOT NULL DEFAULT now(),
    CHECK (from_account_id <> to_account_id)
);

CREATE UNIQUE INDEX transfers_user_idempotency_key_uniq
    ON transfers (user_id, idempotency_key)
    WHERE idempotency_key IS NOT NULL;

CREATE INDEX transfers_user_id_created_at_idx ON transfers (user_id, created_at DESC);
CREATE INDEX transfers_from_account_id_idx ON transfers (from_account_id);
CREATE INDEX transfers_to_account_id_idx ON transfers (to_account_id);

CREATE TABLE ledger_accounts (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    reference_type text NOT NULL,
    reference_id text NOT NULL,
    currency char(3) NOT NULL REFERENCES currencies(code) ON DELETE RESTRICT,
    normal_balance text NOT NULL CHECK (normal_balance IN ('debit', 'credit')),
    status text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'closed')),
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (reference_type, reference_id, currency)
);

CREATE INDEX ledger_accounts_owner_user_id_idx ON ledger_accounts (owner_user_id);
CREATE INDEX ledger_accounts_reference_idx ON ledger_accounts (reference_type, reference_id);
CREATE INDEX ledger_accounts_status_idx ON ledger_accounts (status);

CREATE TABLE ledger_journal_entries (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    event_type text NOT NULL,
    source_type text NOT NULL,
    source_id text NOT NULL,
    idempotency_key text,
    description text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (source_type, source_id)
);

CREATE INDEX ledger_journal_entries_idempotency_key_idx
    ON ledger_journal_entries (idempotency_key)
    WHERE idempotency_key IS NOT NULL;

CREATE INDEX ledger_journal_entries_source_idx ON ledger_journal_entries (source_type, source_id);
CREATE INDEX ledger_journal_entries_created_at_idx ON ledger_journal_entries (created_at DESC);

CREATE TABLE ledger_journal_lines (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    journal_entry_id uuid NOT NULL REFERENCES ledger_journal_entries(id) ON DELETE RESTRICT,
    ledger_account_id uuid NOT NULL REFERENCES ledger_accounts(id) ON DELETE RESTRICT,
    direction text NOT NULL CHECK (direction IN ('debit', 'credit')),
    amount_cents bigint NOT NULL CHECK (amount_cents > 0),
    currency char(3) NOT NULL REFERENCES currencies(code) ON DELETE RESTRICT,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX ledger_journal_lines_entry_id_idx ON ledger_journal_lines (journal_entry_id);
CREATE INDEX ledger_journal_lines_account_created_at_idx ON ledger_journal_lines (ledger_account_id, created_at DESC);

CREATE TABLE audit_events (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    actor_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    event_type text NOT NULL,
    target_type text NOT NULL,
    target_id text NOT NULL,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    remote_ip text,
    user_agent text,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX audit_events_created_at_idx ON audit_events (created_at DESC);
CREATE INDEX audit_events_actor_user_id_idx ON audit_events (actor_user_id);

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER accounts_set_updated_at
BEFORE UPDATE ON accounts
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TRIGGER ledger_accounts_set_updated_at
BEFORE UPDATE ON ledger_accounts
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TRIGGER savings_goals_set_updated_at
BEFORE UPDATE ON savings_goals
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TRIGGER kyc_profiles_set_updated_at
BEFORE UPDATE ON kyc_profiles
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TRIGGER aml_cases_set_updated_at
BEFORE UPDATE ON aml_cases
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TRIGGER wallets_set_updated_at
BEFORE UPDATE ON wallets
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TRIGGER crypto_wallets_set_updated_at
BEFORE UPDATE ON crypto_wallets
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TRIGGER crypto_wallet_balances_set_updated_at
BEFORE UPDATE ON crypto_wallet_balances
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TRIGGER wallet_balances_set_updated_at
BEFORE UPDATE ON wallet_balances
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TRIGGER virtual_cards_set_updated_at
BEFORE UPDATE ON virtual_cards
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TRIGGER card_authorizations_set_updated_at
BEFORE UPDATE ON card_authorizations
FOR EACH ROW EXECUTE FUNCTION set_updated_at();