-- =====================================================================
-- Crypto Trading Platform — Database Schema (MySQL 5.7+ / MariaDB 10.3+)
-- Designed for shared hosting: InnoDB only, no stored procedures/events
-- that typically require elevated privileges.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- USERS
-- ---------------------------------------------------------------------
CREATE TABLE users (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email               VARCHAR(190) NOT NULL UNIQUE,
    phone               VARCHAR(30)  NULL,
    password_hash       VARCHAR(255) NOT NULL,          -- password_hash() bcrypt/argon2id
    full_name           VARCHAR(150) NOT NULL,
    display_currency    CHAR(3) NOT NULL DEFAULT 'USD',
    status              ENUM('pending','active','suspended','closed') NOT NULL DEFAULT 'pending',
    kyc_status          ENUM('unverified','pending','verified','rejected') NOT NULL DEFAULT 'unverified',
    two_factor_enabled  TINYINT(1) NOT NULL DEFAULT 0,
    two_factor_secret   VARCHAR(64) NULL,
    email_verified_at   DATETIME NULL,
    last_login_at       DATETIME NULL,
    last_login_ip       VARCHAR(45) NULL,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE email_verification_tokens (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     BIGINT UNSIGNED NOT NULL,
    token_hash  VARCHAR(255) NOT NULL,
    purpose     ENUM('signup','password_reset','login_otp','withdrawal_otp') NOT NULL,
    expires_at  DATETIME NOT NULL,
    used_at     DATETIME NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_purpose (user_id, purpose)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE sessions (
    id              VARCHAR(64) PRIMARY KEY,             -- random token id, not sequential
    user_id         BIGINT UNSIGNED NOT NULL,
    ip_address      VARCHAR(45) NULL,
    user_agent      VARCHAR(255) NULL,
    expires_at      DATETIME NOT NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- ASSETS  (the 20 supported cryptocurrencies)
-- ---------------------------------------------------------------------
CREATE TABLE assets (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    symbol          VARCHAR(10) NOT NULL UNIQUE,         -- BTC, ETH, LTC, SOL, USDT...
    name            VARCHAR(60) NOT NULL,
    chain           VARCHAR(30) NOT NULL,                 -- bitcoin, ethereum, solana, tron...
    is_stablecoin   TINYINT(1) NOT NULL DEFAULT 0,
    decimals        TINYINT UNSIGNED NOT NULL DEFAULT 8,
    icon_url        VARCHAR(255) NULL,
    min_deposit     DECIMAL(24,8) NOT NULL DEFAULT 0,
    withdraw_fee    DECIMAL(24,8) NOT NULL DEFAULT 0,
    is_active        TINYINT(1) NOT NULL DEFAULT 1,
    sort_order      SMALLINT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Cached market data, refreshed by a cron/poller hitting a market data API
CREATE TABLE market_prices (
    asset_id            INT UNSIGNED PRIMARY KEY,
    price_usd           DECIMAL(24,8) NOT NULL,
    change_24h_pct      DECIMAL(8,4)  NOT NULL DEFAULT 0,
    high_24h_usd        DECIMAL(24,8) NULL,
    low_24h_usd         DECIMAL(24,8) NULL,
    volume_24h_usd      DECIMAL(24,2) NULL,
    updated_at          DATETIME NOT NULL,
    FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Historical candles for charting (OHLC), populated by the same poller
CREATE TABLE market_candles (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    asset_id      INT UNSIGNED NOT NULL,
    interval_code ENUM('1m','5m','15m','1h','4h','1d') NOT NULL,
    open_time     DATETIME NOT NULL,
    open          DECIMAL(24,8) NOT NULL,
    high          DECIMAL(24,8) NOT NULL,
    low           DECIMAL(24,8) NOT NULL,
    close         DECIMAL(24,8) NOT NULL,
    volume        DECIMAL(24,2) NOT NULL DEFAULT 0,
    FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_candle (asset_id, interval_code, open_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- WALLETS  (one deposit address per user per asset, from custody provider)
-- ---------------------------------------------------------------------
-- IMPORTANT: no private_key column. Keys live with the custody/WaaS
-- provider (e.g. Tatum/Fireblocks/BitGo). This table only stores the
-- provider's account/address reference so a compromised DB never leaks funds.
CREATE TABLE wallets (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id             BIGINT UNSIGNED NOT NULL,
    asset_id            INT UNSIGNED NOT NULL,
    provider            VARCHAR(40) NOT NULL,             -- e.g. 'tatum', 'fireblocks'
    provider_account_id VARCHAR(120) NULL,                -- provider-side sub-account/vault id
    deposit_address     VARCHAR(120) NOT NULL,
    address_tag         VARCHAR(60) NULL,                 -- memo/tag for chains that need it (XRP, XLM, etc.)
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_user_asset (user_id, asset_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- BALANCES  (off-chain ledger balance per user per asset — the number
-- shown in the app; reconciled against on-chain deposits via webhooks)
-- ---------------------------------------------------------------------
CREATE TABLE balances (
    user_id         BIGINT UNSIGNED NOT NULL,
    asset_id        INT UNSIGNED NOT NULL,
    available       DECIMAL(24,8) NOT NULL DEFAULT 0,
    locked          DECIMAL(24,8) NOT NULL DEFAULT 0,      -- reserved by an open order
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, asset_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- TRANSACTIONS  (deposits & withdrawals — funding movement in/out)
-- ---------------------------------------------------------------------
CREATE TABLE transactions (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         BIGINT UNSIGNED NOT NULL,
    asset_id        INT UNSIGNED NOT NULL,
    type            ENUM('deposit','withdrawal') NOT NULL,
    amount          DECIMAL(24,8) NOT NULL,
    fee             DECIMAL(24,8) NOT NULL DEFAULT 0,
    status          ENUM('pending','confirming','completed','failed','cancelled') NOT NULL DEFAULT 'pending',
    tx_hash         VARCHAR(120) NULL,                     -- on-chain transaction hash
    confirmations   INT UNSIGNED NOT NULL DEFAULT 0,
    to_address       VARCHAR(120) NULL,                     -- withdrawal destination
    provider_ref    VARCHAR(120) NULL,                     -- custody provider's transaction id
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE RESTRICT,
    INDEX idx_user_created (user_id, created_at),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- TRADES / ORDERS  (buy/sell one asset for another, e.g. BTC -> USDT)
-- ---------------------------------------------------------------------
CREATE TABLE orders (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         BIGINT UNSIGNED NOT NULL,
    base_asset_id   INT UNSIGNED NOT NULL,                 -- asset being bought/sold, e.g. BTC
    quote_asset_id  INT UNSIGNED NOT NULL,                 -- priced in, e.g. USDT
    side            ENUM('buy','sell') NOT NULL,
    order_type      ENUM('market','limit') NOT NULL DEFAULT 'market',
    quantity        DECIMAL(24,8) NOT NULL,
    limit_price     DECIMAL(24,8) NULL,                    -- null for market orders
    filled_price    DECIMAL(24,8) NULL,
    status          ENUM('open','filled','partially_filled','cancelled') NOT NULL DEFAULT 'open',
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    filled_at       DATETIME NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (base_asset_id) REFERENCES assets(id) ON DELETE RESTRICT,
    FOREIGN KEY (quote_asset_id) REFERENCES assets(id) ON DELETE RESTRICT,
    INDEX idx_user_status (user_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- CONVERSIONS  (direct asset-to-asset swap, distinct from an order book trade)
-- ---------------------------------------------------------------------
CREATE TABLE conversions (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         BIGINT UNSIGNED NOT NULL,
    from_asset_id   INT UNSIGNED NOT NULL,
    to_asset_id     INT UNSIGNED NOT NULL,
    from_amount     DECIMAL(24,8) NOT NULL,
    to_amount       DECIMAL(24,8) NOT NULL,
    rate            DECIMAL(24,8) NOT NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (from_asset_id) REFERENCES assets(id) ON DELETE RESTRICT,
    FOREIGN KEY (to_asset_id) REFERENCES assets(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- NOTIFICATIONS
-- ---------------------------------------------------------------------
CREATE TABLE notifications (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     BIGINT UNSIGNED NOT NULL,
    type        VARCHAR(40) NOT NULL,                      -- deposit_confirmed, trade_filled, security_alert...
    title       VARCHAR(150) NOT NULL,
    body        TEXT NULL,
    read_at     DATETIME NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_created (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- AUDIT LOG  (security-relevant events — logins, withdrawals, settings changes)
-- ---------------------------------------------------------------------
CREATE TABLE audit_log (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     BIGINT UNSIGNED NULL,
    action      VARCHAR(60) NOT NULL,
    ip_address  VARCHAR(45) NULL,
    metadata    JSON NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- ADMINS
-- ---------------------------------------------------------------------
CREATE TABLE admins (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email           VARCHAR(190) NOT NULL UNIQUE,
    password_hash   VARCHAR(255) NOT NULL,
    full_name       VARCHAR(150) NOT NULL,
    role            ENUM('super_admin','support','ops') NOT NULL DEFAULT 'support',
    is_active       TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at   DATETIME NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Every admin action against a user (suspend, reactivate, balance edit, etc.)
CREATE TABLE admin_actions (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_id        BIGINT UNSIGNED NOT NULL,
    target_user_id  BIGINT UNSIGNED NULL,
    action          VARCHAR(60) NOT NULL,               -- 'suspend_user', 'reactivate_user', 'note', ...
    reason          TEXT NULL,
    metadata        JSON NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE,
    FOREIGN KEY (target_user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_target_user (target_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- SUPPORT CHAT
-- ---------------------------------------------------------------------
CREATE TABLE support_conversations (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id             BIGINT UNSIGNED NOT NULL,
    assigned_admin_id   BIGINT UNSIGNED NULL,
    status              ENUM('open','closed') NOT NULL DEFAULT 'open',
    subject             VARCHAR(150) NULL,
    last_message_at     DATETIME NULL,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_admin_id) REFERENCES admins(id) ON DELETE SET NULL,
    INDEX idx_status (status),
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE support_messages (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    conversation_id     BIGINT UNSIGNED NOT NULL,
    sender_type         ENUM('user','admin') NOT NULL,
    sender_id           BIGINT UNSIGNED NOT NULL,        -- user_id or admin_id depending on sender_type
    body                TEXT NOT NULL,
    read_at             DATETIME NULL,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (conversation_id) REFERENCES support_conversations(id) ON DELETE CASCADE,
    INDEX idx_conversation_created (conversation_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- SEED: the 20 supported assets
-- ---------------------------------------------------------------------
INSERT INTO assets (symbol, name, chain, is_stablecoin, decimals, sort_order) VALUES
('BTC',  'Bitcoin',        'bitcoin',   0, 8, 1),
('ETH',  'Ethereum',       'ethereum',  0, 18, 2),
('USDT', 'Tether',         'ethereum',  1, 6, 3),
('USDC', 'USD Coin',       'ethereum',  1, 6, 4),
('BNB',  'BNB',            'bsc',       0, 18, 5),
('SOL',  'Solana',         'solana',    0, 9, 6),
('XRP',  'XRP',            'ripple',    0, 6, 7),
('ADA',  'Cardano',        'cardano',   0, 6, 8),
('DOGE', 'Dogecoin',       'dogecoin',  0, 8, 9),
('TRX',  'TRON',           'tron',      0, 6, 10),
('LTC',  'Litecoin',       'litecoin',  0, 8, 11),
('DOT',  'Polkadot',       'polkadot',  0, 10, 12),
('MATIC','Polygon',        'polygon',   0, 18, 13),
('AVAX', 'Avalanche',      'avalanche', 0, 18, 14),
('LINK', 'Chainlink',      'ethereum',  0, 18, 15),
('SHIB', 'Shiba Inu',      'ethereum',  0, 18, 16),
('DAI',  'Dai',            'ethereum',  1, 18, 17),
('ATOM', 'Cosmos',         'cosmos',    0, 6, 18),
('XLM',  'Stellar',        'stellar',   0, 7, 19),
('BCH',  'Bitcoin Cash',   'bitcoincash', 0, 8, 20);

SET FOREIGN_KEY_CHECKS = 1;
