-- =====================================================================
-- Provider-backed financial modules — run AFTER schema.sql
-- Adds: reserved accounts, dynamic accounts, virtual cards + their
-- transaction log, crypto deposit addresses + deposits, beneficiaries,
-- and a bill_payments log. All money columns are BIGINT minor units,
-- consistent with schema.sql. Every table links back to `providers`
-- so provider-switching is reflected in stored data, not just code.
-- =====================================================================


-- ---------------------------------------------------------------------
-- reserved_accounts — permanent, KYC-gated virtual accounts
-- ---------------------------------------------------------------------
CREATE TABLE reserved_accounts (
    id                 CHAR(36)     NOT NULL PRIMARY KEY,
    user_id            CHAR(36)     NOT NULL,
    wallet_id          CHAR(36)     NOT NULL,
    provider_id        CHAR(36)     NULL,
    account_reference  VARCHAR(150) NOT NULL COMMENT 'unique reference we send the provider; used to reconcile webhooks',
    bank_code          VARCHAR(20)  NOT NULL,
    bank_name          VARCHAR(150) NOT NULL,
    account_number     VARCHAR(30)  NOT NULL,
    account_name       VARCHAR(150) NOT NULL,
    currency           CHAR(3)      NOT NULL DEFAULT 'NGN',
    status             ENUM('active','inactive','closed') NOT NULL DEFAULT 'active',
    kyc_verified       TINYINT(1)   NOT NULL DEFAULT 0,
    created_at         DATETIME     NOT NULL,
    updated_at         DATETIME     NOT NULL,
    UNIQUE KEY uq_reserved_accounts_reference (account_reference),
    KEY idx_reserved_accounts_user (user_id),
    KEY idx_reserved_accounts_account_number (account_number),
    CONSTRAINT fk_reserved_accounts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_reserved_accounts_wallet FOREIGN KEY (wallet_id) REFERENCES wallets(id) ON DELETE RESTRICT,
    CONSTRAINT fk_reserved_accounts_provider FOREIGN KEY (provider_id) REFERENCES providers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- dynamic_accounts — temporary, auto-expiring, single-use accounts
-- ---------------------------------------------------------------------
CREATE TABLE dynamic_accounts (
    id                   CHAR(36)     NOT NULL PRIMARY KEY,
    user_id              CHAR(36)     NOT NULL,
    wallet_id            CHAR(36)     NOT NULL,
    provider_id          CHAR(36)     NULL,
    reference            VARCHAR(150) NOT NULL,
    account_number       VARCHAR(30)  NOT NULL,
    account_name         VARCHAR(150) NOT NULL,
    bank_code            VARCHAR(20)  NOT NULL,
    bank_name            VARCHAR(150) NOT NULL,
    currency             CHAR(3)      NOT NULL DEFAULT 'NGN',
    amount_minor         BIGINT       NOT NULL,
    fee_minor            BIGINT       NOT NULL DEFAULT 0,
    total_payable_minor  BIGINT       NOT NULL,
    status               ENUM('pending','paid','expired','cancelled') NOT NULL DEFAULT 'pending',
    expires_at           DATETIME     NOT NULL,
    settled_transaction_id CHAR(36)   NULL,
    created_at           DATETIME     NOT NULL,
    updated_at           DATETIME     NOT NULL,
    UNIQUE KEY uq_dynamic_accounts_reference (reference),
    KEY idx_dynamic_accounts_user (user_id),
    KEY idx_dynamic_accounts_status_expiry (status, expires_at),
    KEY idx_dynamic_accounts_account_number (account_number),
    CONSTRAINT fk_dynamic_accounts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_dynamic_accounts_wallet FOREIGN KEY (wallet_id) REFERENCES wallets(id) ON DELETE RESTRICT,
    CONSTRAINT fk_dynamic_accounts_provider FOREIGN KEY (provider_id) REFERENCES providers(id) ON DELETE SET NULL,
    CONSTRAINT fk_dynamic_accounts_txn FOREIGN KEY (settled_transaction_id) REFERENCES transactions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- virtual_cards — USD card issuance. Funding is always NGN -> USD
-- converted at debit time; we never hold a USD wallet balance.
-- ---------------------------------------------------------------------
CREATE TABLE virtual_cards (
    id                    CHAR(36)     NOT NULL PRIMARY KEY,
    user_id               CHAR(36)     NOT NULL,
    provider_id           CHAR(36)     NULL,
    provider_customer_code VARCHAR(100) NULL,
    card_code             VARCHAR(100) NOT NULL COMMENT 'provider card identifier',
    card_brand            VARCHAR(30)  NOT NULL DEFAULT 'Visa',
    card_type             VARCHAR(30)  NOT NULL DEFAULT 'virtual',
    card_name             VARCHAR(150) NULL,
    last4                 CHAR(4)      NULL,
    expiry_month_year     VARCHAR(7)   NULL COMMENT 'MM/YYYY',
    status                ENUM('pending','active','frozen','closed') NOT NULL DEFAULT 'pending',
    cached_balance_minor_usd BIGINT    NOT NULL DEFAULT 0 COMMENT 'display cache only; provider is source of truth',
    created_at            DATETIME     NOT NULL,
    updated_at            DATETIME     NOT NULL,
    UNIQUE KEY uq_virtual_cards_code (card_code),
    KEY idx_virtual_cards_user (user_id),
    CONSTRAINT fk_virtual_cards_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_virtual_cards_provider FOREIGN KEY (provider_id) REFERENCES providers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- card_transactions — statement lines pulled from / pushed by provider
-- ---------------------------------------------------------------------
CREATE TABLE card_transactions (
    id                  CHAR(36)     NOT NULL PRIMARY KEY,
    virtual_card_id     CHAR(36)     NOT NULL,
    wallet_transaction_id CHAR(36)   NULL COMMENT 'the NGN-side transactions.id for topups/withdrawals we funded',
    provider_reference  VARCHAR(150) NULL,
    type                ENUM('topup','withdrawal','debit','credit','reversal','declined') NOT NULL,
    amount_minor_usd    BIGINT       NOT NULL,
    fee_minor_usd       BIGINT       NOT NULL DEFAULT 0,
    description         VARCHAR(255) NULL,
    status              VARCHAR(30)  NOT NULL DEFAULT 'success',
    occurred_at          DATETIME    NOT NULL,
    created_at          DATETIME     NOT NULL,
    KEY idx_card_transactions_card (virtual_card_id, occurred_at),
    KEY idx_card_transactions_provider_ref (provider_reference),
    CONSTRAINT fk_card_transactions_card FOREIGN KEY (virtual_card_id) REFERENCES virtual_cards(id) ON DELETE CASCADE,
    CONSTRAINT fk_card_transactions_wallet_txn FOREIGN KEY (wallet_transaction_id) REFERENCES transactions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- crypto_addresses — USDT (TRC20) deposit addresses per user.
-- No balance column: crypto never rests as a balance in this system.
-- ---------------------------------------------------------------------
CREATE TABLE crypto_addresses (
    id           CHAR(36)     NOT NULL PRIMARY KEY,
    user_id      CHAR(36)     NOT NULL,
    provider_id  CHAR(36)     NULL,
    asset_type   VARCHAR(30)  NOT NULL DEFAULT 'usdt',
    network      VARCHAR(30)  NOT NULL DEFAULT 'TRC20',
    address      VARCHAR(150) NOT NULL,
    provider_code VARCHAR(100) NULL,
    status       ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at   DATETIME     NOT NULL,
    UNIQUE KEY uq_crypto_addresses_address (address),
    KEY idx_crypto_addresses_user (user_id),
    CONSTRAINT fk_crypto_addresses_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_crypto_addresses_provider FOREIGN KEY (provider_id) REFERENCES providers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- crypto_deposits — one row per on-chain deposit, tracked through:
-- webhook received -> confirmed on-chain -> swapped to NGN -> wallet
-- credited. `ngn_amount_minor` and `wallet_ledger_id` are only ever
-- populated after the swap; there is no intermediate crypto balance.
-- ---------------------------------------------------------------------
CREATE TABLE crypto_deposits (
    id                CHAR(36)     NOT NULL PRIMARY KEY,
    user_id           CHAR(36)     NOT NULL,
    crypto_address_id CHAR(36)     NOT NULL,
    tx_hash           VARCHAR(150) NOT NULL,
    asset_type        VARCHAR(30)  NOT NULL DEFAULT 'usdt',
    network            VARCHAR(30) NOT NULL DEFAULT 'TRC20',
    amount_crypto     DECIMAL(24,8) NOT NULL COMMENT 'raw on-chain amount; informational only, never a spendable balance',
    confirmations     INT UNSIGNED NOT NULL DEFAULT 0,
    ngn_amount_minor  BIGINT       NULL COMMENT 'set once swapped to NGN',
    swap_reference    VARCHAR(150) NULL,
    wallet_ledger_id  CHAR(36)     NULL,
    status            ENUM('pending','confirmed','converted','credited','failed') NOT NULL DEFAULT 'pending',
    created_at        DATETIME     NOT NULL,
    updated_at        DATETIME     NOT NULL,
    UNIQUE KEY uq_crypto_deposits_hash (tx_hash),
    KEY idx_crypto_deposits_user (user_id),
    KEY idx_crypto_deposits_status (status),
    CONSTRAINT fk_crypto_deposits_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_crypto_deposits_address FOREIGN KEY (crypto_address_id) REFERENCES crypto_addresses(id) ON DELETE RESTRICT,
    CONSTRAINT fk_crypto_deposits_ledger FOREIGN KEY (wallet_ledger_id) REFERENCES wallet_ledger(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- beneficiaries — saved transfer recipients for faster repeat payouts
-- ---------------------------------------------------------------------
CREATE TABLE beneficiaries (
    id             CHAR(36)     NOT NULL PRIMARY KEY,
    user_id        CHAR(36)     NOT NULL,
    bank_code      VARCHAR(20)  NOT NULL,
    bank_name      VARCHAR(150) NOT NULL,
    account_number VARCHAR(30)  NOT NULL,
    account_name   VARCHAR(150) NOT NULL,
    currency       CHAR(3)      NOT NULL DEFAULT 'NGN',
    country        CHAR(2)      NOT NULL DEFAULT 'NG',
    nickname       VARCHAR(100) NULL,
    created_at     DATETIME     NOT NULL,
    UNIQUE KEY uq_beneficiary_user_account (user_id, bank_code, account_number),
    KEY idx_beneficiaries_user (user_id),
    CONSTRAINT fk_beneficiaries_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Seed the Cashwyre provider row so provider_id FKs resolve immediately.
INSERT INTO providers (id, name, slug, type, is_active, created_at, updated_at)
VALUES (UUID(), 'Cashwyre', 'cashwyre', 'payment', 1, NOW(), NOW());
