-- =====================================================================
-- Admin-configurable pricing: exchange rate overrides and fee rules.
-- Run AFTER the other migrations.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- exchange_rates — platform-set rates/markups layered on top of the
-- provider's live rate (e.g. FxService can apply a markup_bps on top
-- of Cashwyre's sell rate before quoting a user).
-- ---------------------------------------------------------------------
CREATE TABLE exchange_rates (
    id             CHAR(36)     NOT NULL PRIMARY KEY,
    base_currency  CHAR(6)      NOT NULL COMMENT 'e.g. NGN, or an asset like USDT',
    quote_currency CHAR(6)      NOT NULL COMMENT 'e.g. USD',
    markup_bps     INT          NOT NULL DEFAULT 0 COMMENT 'basis points added on top of the provider rate',
    is_active      TINYINT(1)   NOT NULL DEFAULT 1,
    updated_by     CHAR(36)     NULL,
    created_at     DATETIME     NOT NULL,
    updated_at     DATETIME     NOT NULL,
    UNIQUE KEY uq_exchange_rates_pair (base_currency, quote_currency),
    CONSTRAINT fk_exchange_rates_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- fees — platform fee schedule per transaction type, flat + percentage,
-- with an optional cap. TransactionService/BillPaymentService/
-- TransferService look these up before debiting.
-- ---------------------------------------------------------------------
CREATE TABLE fees (
    id               CHAR(36)     NOT NULL PRIMARY KEY,
    transaction_type VARCHAR(30)  NOT NULL COMMENT 'deposit, withdrawal, transfer_out, airtime, data, electricity, cabletv, card_funding, payout',
    flat_minor       BIGINT       NOT NULL DEFAULT 0,
    percentage_bps   INT          NOT NULL DEFAULT 0 COMMENT 'basis points, e.g. 150 = 1.5%',
    cap_minor        BIGINT       NULL COMMENT 'maximum fee regardless of percentage',
    currency         CHAR(3)      NOT NULL DEFAULT 'NGN',
    is_active        TINYINT(1)   NOT NULL DEFAULT 1,
    updated_by       CHAR(36)     NULL,
    created_at       DATETIME     NOT NULL,
    updated_at       DATETIME     NOT NULL,
    UNIQUE KEY uq_fees_type_currency (transaction_type, currency),
    CONSTRAINT fk_fees_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

INSERT INTO exchange_rates (id, base_currency, quote_currency, markup_bps, is_active, created_at, updated_at)
VALUES (UUID(), 'NGN', 'USD', 150, 1, NOW(), NOW());

INSERT INTO fees (id, transaction_type, flat_minor, percentage_bps, cap_minor, currency, is_active, created_at, updated_at) VALUES
    (UUID(), 'transfer_out', 5000, 0, NULL, 'NGN', 1, NOW(), NOW()),
    (UUID(), 'airtime', 0, 0, NULL, 'NGN', 1, NOW(), NOW()),
    (UUID(), 'data', 0, 0, NULL, 'NGN', 1, NOW(), NOW()),
    (UUID(), 'electricity', 10000, 0, NULL, 'NGN', 1, NOW(), NOW()),
    (UUID(), 'cabletv', 0, 0, NULL, 'NGN', 1, NOW(), NOW()),
    (UUID(), 'card_funding', 0, 150, 200000, 'NGN', 1, NOW(), NOW());