-- CEDEX Exchange MySQL Schema

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

-- Drop existing tables if re-running
DROP TABLE IF EXISTS site_settings;
DROP TABLE IF EXISTS pages;
DROP TABLE IF EXISTS idempotency_keys;
DROP TABLE IF EXISTS audit_logs;
DROP TABLE IF EXISTS fee_logs;
DROP TABLE IF EXISTS trades;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS withdrawals;
DROP TABLE IF EXISTS deposits;
DROP TABLE IF EXISTS balances;
DROP TABLE IF EXISTS wallets;
DROP TABLE IF EXISTS login_sessions;
DROP TABLE IF EXISTS api_keys;
DROP TABLE IF EXISTS vip_tiers;
DROP TABLE IF EXISTS users;

-- USERS
CREATE TABLE users (
    id VARCHAR(36) PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    two_factor_secret VARCHAR(64),
    is_2fa_enabled BOOLEAN DEFAULT FALSE,
    risk_level INT DEFAULT 1,
    is_frozen BOOLEAN DEFAULT FALSE,
    kyc_level INT DEFAULT 0,
    role ENUM('USER', 'ADMIN', 'SUPER_ADMIN') DEFAULT 'USER',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- VIP TIERS
CREATE TABLE vip_tiers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    tier_level INT DEFAULT 0,
    rolling_30d_volume DECIMAL(30,18) DEFAULT 0,
    maker_fee_override DECIMAL(5,4),
    taker_fee_override DECIMAL(5,4),
    is_market_maker BOOLEAN DEFAULT FALSE,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE(user_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- API KEYS
CREATE TABLE api_keys (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    api_key VARCHAR(64) UNIQUE NOT NULL,
    api_secret_hash VARCHAR(255) NOT NULL,
    permissions JSON NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- SESSIONS
CREATE TABLE login_sessions (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- WALLETS
CREATE TABLE wallets (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    currency VARCHAR(10) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, currency),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- BALANCES
CREATE TABLE balances (
    id VARCHAR(36) PRIMARY KEY,
    wallet_id VARCHAR(36) NOT NULL,
    available DECIMAL(30,18) NOT NULL DEFAULT 0,
    locked DECIMAL(30,18) NOT NULL DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE(wallet_id),
    FOREIGN KEY (wallet_id) REFERENCES wallets(id) ON DELETE CASCADE,
    CHECK (available >= 0),
    CHECK (locked >= 0)
);

-- DEPOSITS
CREATE TABLE deposits (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    currency VARCHAR(10) NOT NULL,
    amount DECIMAL(30,18) NOT NULL,
    tx_hash VARCHAR(255) UNIQUE,
    status ENUM('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED', 'REJECTED') DEFAULT 'PENDING',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),
    CHECK (amount > 0)
);

-- WITHDRAWALS
CREATE TABLE withdrawals (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    currency VARCHAR(10) NOT NULL,
    amount DECIMAL(30,18) NOT NULL,
    fee DECIMAL(30,18) NOT NULL,
    net_amount DECIMAL(30,18) NOT NULL,
    destination_address VARCHAR(255) NOT NULL,
    tx_hash VARCHAR(255) UNIQUE,
    status ENUM('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED', 'REJECTED') DEFAULT 'PENDING',
    aml_flag BOOLEAN DEFAULT FALSE,
    idempotency_key VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),
    CHECK (amount > 0),
    CHECK (fee >= 0),
    CHECK (net_amount > 0)
);

-- TRADING PAIRS (MARKETS)
CREATE TABLE trading_pairs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    symbol VARCHAR(20) UNIQUE NOT NULL,
    base_asset VARCHAR(10) NOT NULL,
    quote_asset VARCHAR(10) NOT NULL,
    maker_fee DECIMAL(5,4) DEFAULT 0.0010,
    taker_fee DECIMAL(5,4) DEFAULT 0.0020,
    min_order_value DECIMAL(30,18) DEFAULT 10.00,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- ORDERS
CREATE TABLE orders (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    symbol VARCHAR(20) NOT NULL,
    side ENUM('BUY', 'SELL') NOT NULL,
    type ENUM('LIMIT', 'MARKET') NOT NULL,
    price DECIMAL(30,18) NOT NULL,
    amount DECIMAL(30,18) NOT NULL,
    filled_amount DECIMAL(30,18) NOT NULL DEFAULT 0,
    status ENUM('PENDING', 'OPEN', 'PARTIALLY_FILLED', 'FILLED', 'CANCELED', 'REJECTED') DEFAULT 'PENDING',
    idempotency_key VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    CHECK (price >= 0),
    CHECK (amount > 0),
    CHECK (filled_amount >= 0)
);

CREATE INDEX idx_orders_matching ON orders (symbol, status, side, price);

-- TRADES
CREATE TABLE trades (
    id VARCHAR(36) PRIMARY KEY,
    symbol VARCHAR(20) NOT NULL,
    maker_order_id VARCHAR(36) NOT NULL,
    taker_order_id VARCHAR(36) NOT NULL,
    maker_user_id VARCHAR(36) NOT NULL,
    taker_user_id VARCHAR(36) NOT NULL,
    price DECIMAL(30,18) NOT NULL,
    amount DECIMAL(30,18) NOT NULL,
    maker_fee DECIMAL(30,18) NOT NULL,
    taker_fee DECIMAL(30,18) NOT NULL,
    executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (maker_order_id) REFERENCES orders(id),
    FOREIGN KEY (taker_order_id) REFERENCES orders(id),
    FOREIGN KEY (maker_user_id) REFERENCES users(id),
    FOREIGN KEY (taker_user_id) REFERENCES users(id),
    CHECK (price > 0),
    CHECK (amount > 0),
    CHECK (maker_fee >= 0),
    CHECK (taker_fee >= 0)
);

-- FEE LOGS
CREATE TABLE fee_logs (
    id VARCHAR(36) PRIMARY KEY,
    trade_id VARCHAR(36),
    currency VARCHAR(10) NOT NULL,
    amount DECIMAL(30,18) NOT NULL,
    collected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (trade_id) REFERENCES trades(id)
);

-- AUDIT LOGS
CREATE TABLE audit_logs (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    action VARCHAR(100) NOT NULL,
    entity VARCHAR(100) NOT NULL,
    entity_id VARCHAR(100),
    old_value JSON,
    new_value JSON,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- IDEMPOTENCY
CREATE TABLE idempotency_keys (
    idempotency_key VARCHAR(255) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    request_path VARCHAR(255) NOT NULL,
    response_body JSON,
    response_status INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- CMS PAGES
CREATE TABLE pages (
    id VARCHAR(36) PRIMARY KEY,
    slug VARCHAR(255) UNIQUE NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    is_published BOOLEAN DEFAULT TRUE,
    author_id VARCHAR(36),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users(id)
);

-- SITE SETTINGS
CREATE TABLE site_settings (
    settings_key VARCHAR(100) PRIMARY KEY,
    settings_value JSON NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

COMMIT;