-- =============================================
-- CRYPTO EXCHANGE DATABASE SCHEMA
-- Database: tokfreequestion_4trade
-- Total Tables: 23
-- =============================================

SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL,
    email VARCHAR(255) NOT NULL,
    username VARCHAR(50) DEFAULT NULL,
    password VARCHAR(255) NOT NULL,
    referral_code VARCHAR(20) DEFAULT NULL,
    referred_by BIGINT UNSIGNED DEFAULT NULL,
    two_factor_secret VARCHAR(255) DEFAULT NULL,
    two_factor_confirmed_at TIMESTAMP NULL DEFAULT NULL,
    email_verified_at TIMESTAMP NULL DEFAULT NULL,
    status ENUM('active', 'suspended', 'banned') DEFAULT 'active',
    role ENUM('user', 'vip', 'admin', 'super_admin') DEFAULT 'user',
    last_login_at TIMESTAMP NULL DEFAULT NULL,
    last_login_ip VARCHAR(45) DEFAULT NULL,
    remember_token VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY users_email_unique (email),
    UNIQUE KEY users_uuid_unique (uuid),
    UNIQUE KEY users_referral_code_unique (referral_code),
    KEY users_referred_by_index (referred_by),
    KEY users_status_index (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_profiles (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    first_name VARCHAR(100) DEFAULT NULL,
    last_name VARCHAR(100) DEFAULT NULL,
    phone VARCHAR(20) DEFAULT NULL,
    date_of_birth DATE DEFAULT NULL,
    gender ENUM('male', 'female', 'other') DEFAULT NULL,
    country VARCHAR(100) DEFAULT NULL,
    state VARCHAR(100) DEFAULT NULL,
    city VARCHAR(100) DEFAULT NULL,
    address TEXT DEFAULT NULL,
    postal_code VARCHAR(20) DEFAULT NULL,
    avatar VARCHAR(255) DEFAULT NULL,
    kyc_status ENUM('pending', 'approved', 'rejected', 'not_submitted') DEFAULT 'not_submitted',
    kyc_verified_at TIMESTAMP NULL DEFAULT NULL,
    notification_email TINYINT(1) DEFAULT 1,
    notification_sms TINYINT(1) DEFAULT 0,
    notification_push TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY user_profiles_user_id_unique (user_id),
    CONSTRAINT fk_user_profiles_user_id FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kyc_documents (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    document_type ENUM('id_card', 'passport', 'driving_license') NOT NULL,
    document_number VARCHAR(100) DEFAULT NULL,
    front_image VARCHAR(255) NOT NULL,
    back_image VARCHAR(255) DEFAULT NULL,
    selfie_image VARCHAR(255) DEFAULT NULL,
    status ENUM('pending', 'under_review', 'approved', 'rejected') DEFAULT 'pending',
    reviewed_by BIGINT UNSIGNED DEFAULT NULL,
    reviewed_at TIMESTAMP NULL DEFAULT NULL,
    rejection_reason TEXT DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY kyc_documents_user_id_index (user_id),
    CONSTRAINT fk_kyc_documents_user_id FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS password_reset_tokens (
    email VARCHAR(255) NOT NULL,
    token VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS markets (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    symbol VARCHAR(20) NOT NULL,
    name VARCHAR(100) NOT NULL,
    type ENUM('spot', 'futures', 'p2p') DEFAULT 'spot',
    base_currency VARCHAR(10) NOT NULL,
    quote_currency VARCHAR(10) NOT NULL,
    icon VARCHAR(255) DEFAULT NULL,
    description TEXT DEFAULT NULL,
    status ENUM('active', 'inactive', 'maintenance') DEFAULT 'active',
    is_popular TINYINT(1) DEFAULT 0,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY markets_symbol_unique (symbol),
    KEY markets_status_index (status),
    KEY markets_type_index (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS market_pairs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    market_id BIGINT UNSIGNED NOT NULL,
    pair_symbol VARCHAR(30) NOT NULL,
    base_asset VARCHAR(10) NOT NULL,
    quote_asset VARCHAR(10) NOT NULL,
    min_order_size DECIMAL(28, 8) DEFAULT 0.00000001,
    max_order_size DECIMAL(28, 8) DEFAULT 999999999.00000000,
    price_precision INT DEFAULT 8,
    quantity_precision INT DEFAULT 8,
    maker_fee_percent DECIMAL(5, 2) DEFAULT 0.10,
    taker_fee_percent DECIMAL(5, 2) DEFAULT 0.15,
    min_notional_value DECIMAL(28, 8) DEFAULT 10.00000000,
    current_price DECIMAL(28, 8) DEFAULT NULL,
    price_change_24h DECIMAL(10, 2) DEFAULT 0.00,
    price_change_percent_24h DECIMAL(10, 2) DEFAULT 0.00,
    high_24h DECIMAL(28, 8) DEFAULT NULL,
    low_24h DECIMAL(28, 8) DEFAULT NULL,
    volume_24h DECIMAL(28, 8) DEFAULT 0.00000000,
    trade_count_24h INT UNSIGNED DEFAULT 0,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY market_pairs_pair_symbol_unique (pair_symbol),
    KEY market_pairs_market_id_index (market_id),
    KEY market_pairs_status_index (status),
    CONSTRAINT fk_market_pairs_market_id FOREIGN KEY (market_id) REFERENCES markets (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_id VARCHAR(32) NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    market_pair_id BIGINT UNSIGNED NOT NULL,
    type ENUM('limit', 'market', 'stop_limit') NOT NULL,
    side ENUM('buy', 'sell') NOT NULL,
    price DECIMAL(28, 8) DEFAULT NULL,
    stop_price DECIMAL(28, 8) DEFAULT NULL,
    quantity DECIMAL(28, 8) NOT NULL,
    filled_quantity DECIMAL(28, 8) DEFAULT 0.00000000,
    remaining_quantity DECIMAL(28, 8) DEFAULT 0.00000000,
    avg_fill_price DECIMAL(28, 8) DEFAULT NULL,
    amount DECIMAL(28, 8) NOT NULL,
    filled_amount DECIMAL(28, 8) DEFAULT 0.00000000,
    fee DECIMAL(28, 8) DEFAULT 0.00000000,
    fee_currency VARCHAR(10) DEFAULT NULL,
    status ENUM('pending', 'open', 'partially_filled', 'filled', 'cancelled', 'rejected', 'expired') DEFAULT 'pending',
    time_in_force ENUM('GTC', 'IOC', 'FOK') DEFAULT 'GTC',
    client_order_id VARCHAR(64) DEFAULT NULL,
    margin_mode ENUM('isolated', 'cross') DEFAULT NULL,
    leverage INT UNSIGNED DEFAULT 1,
    error_code VARCHAR(50) DEFAULT NULL,
    error_message TEXT DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    cancelled_at TIMESTAMP NULL DEFAULT NULL,
    expired_at TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY orders_order_id_unique (order_id),
    KEY orders_user_id_index (user_id),
    KEY orders_market_pair_id_index (market_pair_id),
    KEY orders_status_index (status),
    KEY orders_side_index (side),
    KEY orders_type_index (type),
    KEY orders_created_at_index (created_at),
    CONSTRAINT orders_user_id_foreign FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT orders_market_pair_id_foreign FOREIGN KEY (market_pair_id) REFERENCES market_pairs (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS trades (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    trade_id VARCHAR(32) NOT NULL,
    order_id BIGINT UNSIGNED NOT NULL,
    maker_order_id BIGINT UNSIGNED DEFAULT NULL,
    taker_order_id BIGINT UNSIGNED DEFAULT NULL,
    market_pair_id BIGINT UNSIGNED NOT NULL,
    buyer_user_id BIGINT UNSIGNED NOT NULL,
    seller_user_id BIGINT UNSIGNED NOT NULL,
    side ENUM('buy', 'sell') NOT NULL,
    price DECIMAL(28, 8) NOT NULL,
    quantity DECIMAL(28, 8) NOT NULL,
    amount DECIMAL(28, 8) NOT NULL,
    fee DECIMAL(28, 8) DEFAULT 0.00000000,
    fee_currency VARCHAR(10) DEFAULT NULL,
    is_maker TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY trades_trade_id_unique (trade_id),
    KEY trades_order_id_index (order_id),
    KEY trades_market_pair_id_index (market_pair_id),
    KEY trades_buyer_user_id_index (buyer_user_id),
    KEY trades_seller_user_id_index (seller_user_id),
    KEY trades_created_at_index (created_at),
    CONSTRAINT trades_order_id FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE,
    CONSTRAINT trades_market_pair_id FOREIGN KEY (market_pair_id) REFERENCES market_pairs (id) ON DELETE CASCADE,
    CONSTRAINT trades_buyer_user_id FOREIGN KEY (buyer_user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT trades_seller_user_id FOREIGN KEY (seller_user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wallets (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    currency VARCHAR(10) NOT NULL,
    balance DECIMAL(28, 8) DEFAULT 0.00000000,
    available_balance DECIMAL(28, 8) DEFAULT 0.00000000,
    frozen_balance DECIMAL(28, 8) DEFAULT 0.00000000,
    total_deposit DECIMAL(28, 8) DEFAULT 0.00000000,
    total_withdrawal DECIMAL(28, 8) DEFAULT 0.00000000,
    address VARCHAR(255) DEFAULT NULL,
    status ENUM('active', 'frozen', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY wallets_user_id_currency_unique (user_id, currency),
    KEY wallets_user_id_index (user_id),
    KEY wallets_currency_index (currency),
    CONSTRAINT wallets_user_id FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS transactions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tx_id VARCHAR(64) NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    wallet_id BIGINT UNSIGNED NOT NULL,
    type ENUM('deposit', 'withdrawal', 'transfer', 'trade_buy', 'trade_sell', 'fee', 'reward', 'airdrop', 'staking', 'conversion') NOT NULL,
    subtype VARCHAR(50) DEFAULT NULL,
    amount DECIMAL(28, 8) NOT NULL,
    fee DECIMAL(28, 8) DEFAULT 0.00000000,
    currency VARCHAR(10) NOT NULL,
    balance_before DECIMAL(28, 8) DEFAULT 0.00000000,
    balance_after DECIMAL(28, 8) DEFAULT 0.00000000,
    status ENUM('pending', 'completed', 'failed', 'cancelled', 'processing') DEFAULT 'pending',
    reference_id VARCHAR(64) DEFAULT NULL,
    reference_type VARCHAR(50) DEFAULT NULL,
    description TEXT DEFAULT NULL,
    metadata JSON DEFAULT NULL,
    confirmed_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY transactions_tx_id_unique (tx_id),
    KEY transactions_user_id_index (user_id),
    KEY transactions_wallet_id_index (wallet_id),
    KEY transactions_type_index (type),
    KEY transactions_status_index (status),
    KEY transactions_created_at_index (created_at),
    CONSTRAINT transactions_user_id FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT transactions_wallet_id FOREIGN KEY (wallet_id) REFERENCES wallets (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS deposits (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    deposit_id VARCHAR(32) NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    wallet_id BIGINT UNSIGNED NOT NULL,
    transaction_id VARCHAR(64) DEFAULT NULL,
    currency VARCHAR(10) NOT NULL,
    network VARCHAR(20) DEFAULT NULL,
    address VARCHAR(255) NOT NULL,
    tag_memo VARCHAR(100) DEFAULT NULL,
    amount DECIMAL(28, 8) NOT NULL,
    tx_hash VARCHAR(128) DEFAULT NULL,
    confirmations INT UNSIGNED DEFAULT 0,
    required_confirmations INT UNSIGNED DEFAULT 3,
    status ENUM('pending', 'confirming', 'completed', 'failed', 'expired') DEFAULT 'pending',
    error_message TEXT DEFAULT NULL,
    confirmed_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY deposits_deposit_id_unique (deposit_id),
    KEY deposits_user_id_index (user_id),
    KEY deposits_wallet_id_index (wallet_id),
    KEY deposits_status_index (status),
    KEY deposits_tx_hash_index (tx_hash),
    CONSTRAINT deposits_user_id FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT deposits_wallet_id FOREIGN KEY (wallet_id) REFERENCES wallets (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS withdrawals (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    withdrawal_id VARCHAR(32) NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    wallet_id BIGINT UNSIGNED NOT NULL,
    transaction_id VARCHAR(64) DEFAULT NULL,
    currency VARCHAR(10) NOT NULL,
    network VARCHAR(20) DEFAULT NULL,
    address VARCHAR(255) NOT NULL,
    tag_memo VARCHAR(100) DEFAULT NULL,
    amount DECIMAL(28, 8) NOT NULL,
    fee DECIMAL(28, 8) DEFAULT 0.00000000,
    tx_hash VARCHAR(128) DEFAULT NULL,
    status ENUM('pending', 'processing', 'awaiting_approval', 'completed', 'failed', 'cancelled', 'rejected') DEFAULT 'pending',
    note TEXT DEFAULT NULL,
    admin_id BIGINT UNSIGNED DEFAULT NULL,
    approved_at TIMESTAMP NULL DEFAULT NULL,
    rejected_at TIMESTAMP NULL DEFAULT NULL,
    completed_at TIMESTAMP NULL DEFAULT NULL,
    error_message TEXT DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY withdrawals_withdrawal_id_unique (withdrawal_id),
    KEY withdrawals_user_id_index (user_id),
    KEY withdrawals_wallet_id_index (wallet_id),
    KEY withdrawals_status_index (status),
    KEY withdrawals_tx_hash_index (tx_hash),
    CONSTRAINT withdrawals_user_id FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT withdrawals_wallet_id FOREIGN KEY (wallet_id) REFERENCES wallets (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS transfers (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    transfer_id VARCHAR(32) NOT NULL,
    sender_user_id BIGINT UNSIGNED NOT NULL,
    receiver_user_id BIGINT UNSIGNED NOT NULL,
    sender_wallet_id BIGINT UNSIGNED NOT NULL,
    receiver_wallet_id BIGINT UNSIGNED NOT NULL,
    currency VARCHAR(10) NOT NULL,
    amount DECIMAL(28, 8) NOT NULL,
    fee DECIMAL(28, 8) DEFAULT 0.00000000,
    status ENUM('pending', 'completed', 'failed', 'cancelled') DEFAULT 'pending',
    note TEXT DEFAULT NULL,
    completed_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY transfers_transfer_id_unique (transfer_id),
    KEY transfers_sender_user_id_index (sender_user_id),
    KEY transfers_receiver_user_id_index (receiver_user_id),
    KEY transfers_sender_wallet_id_index (sender_wallet_id),
    KEY transfers_receiver_wallet_id_index (receiver_wallet_id),
    KEY transfers_status_index (status),
    CONSTRAINT transfers_sender_user_id FOREIGN KEY (sender_user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT transfers_receiver_user_id FOREIGN KEY (receiver_user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT transfers_sender_wallet_id FOREIGN KEY (sender_wallet_id) REFERENCES wallets (id) ON DELETE CASCADE,
    CONSTRAINT transfers_receiver_wallet_id FOREIGN KEY (receiver_wallet_id) REFERENCES wallets (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notifications (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    type ENUM('system', 'trade', 'deposit', 'withdrawal', 'security', 'marketing', 'kyc', 'price_alert') NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    data JSON DEFAULT NULL,
    is_read TINYINT(1) DEFAULT 0,
    read_at TIMESTAMP NULL DEFAULT NULL,
    action_url VARCHAR(500) DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY notifications_user_id_index (user_id),
    KEY notifications_type_index (type),
    KEY notifications_is_read_index (is_read),
    KEY notifications_created_at_index (created_at),
    CONSTRAINT notifications_user_id FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS referrals (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    referrer_id BIGINT UNSIGNED NOT NULL,
    referred_id BIGINT UNSIGNED NOT NULL,
    commission_rate DECIMAL(5, 2) DEFAULT 10.00,
    total_commission DECIMAL(28, 8) DEFAULT 0.00000000,
    paid_commission DECIMAL(28, 8) DEFAULT 0.00000000,
    unpaid_commission DECIMAL(28, 8) DEFAULT 0.00000000,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY referrals_referred_id_unique (referred_id),
    KEY referrals_referrer_id_index (referrer_id),
    CONSTRAINT referrals_referrer_id FOREIGN KEY (referrer_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT referrals_referred_id FOREIGN KEY (referred_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admins (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('super_admin', 'admin', 'moderator', 'support') DEFAULT 'admin',
    permissions JSON DEFAULT NULL,
    last_login_at TIMESTAMP NULL DEFAULT NULL,
    last_login_ip VARCHAR(45) DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    remember_token VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY admins_email_unique (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admin_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    admin_id BIGINT UNSIGNED NOT NULL,
    action VARCHAR(100) NOT NULL,
    model_type VARCHAR(100) DEFAULT NULL,
    model_id BIGINT UNSIGNED DEFAULT NULL,
    old_values JSON DEFAULT NULL,
    new_values JSON DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY admin_logs_admin_id_index (admin_id),
    KEY admin_logs_action_index (action),
    KEY admin_logs_model_type_index (model_type),
    KEY admin_logs_created_at_index (created_at),
    CONSTRAINT admin_logs_admin_id FOREIGN KEY (admin_id) REFERENCES admins (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS settings (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `group` VARCHAR(100) NOT NULL,
    `key` VARCHAR(100) NOT NULL,
    value TEXT DEFAULT NULL,
    type ENUM('string', 'integer', 'boolean', 'json', 'array') DEFAULT 'string',
    is_public TINYINT(1) DEFAULT 0,
    description VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY settings_group_key_unique (`group`, `key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fees (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    type ENUM('trading', 'deposit', 'withdrawal', 'transfer') NOT NULL,
    tier INT UNSIGNED DEFAULT 1,
    min_volume_30d DECIMAL(28, 8) DEFAULT 0.00000000,
    maker_fee_percent DECIMAL(5, 2) DEFAULT 0.10,
    taker_fee_percent DECIMAL(5, 2) DEFAULT 0.15,
    deposit_fee_percent DECIMAL(5, 2) DEFAULT 0.00,
    deposit_fixed_fee DECIMAL(28, 8) DEFAULT 0.00000000,
    withdrawal_fee_percent DECIMAL(5, 2) DEFAULT 0.00,
    withdrawal_fixed_fee DECIMAL(28, 8) DEFAULT 0.00000000,
    currency VARCHAR(10) DEFAULT NULL,
    network VARCHAR(20) DEFAULT NULL,
    min_withdrawal DECIMAL(28, 8) DEFAULT 0.00000000,
    max_withdrawal DECIMAL(28, 8) DEFAULT 999999999.00000000,
    daily_withdrawal_limit DECIMAL(28, 8) DEFAULT 999999999.00000000,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY fees_type_index (type),
    KEY fees_tier_index (tier),
    KEY fees_currency_index (currency)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS banners (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    image VARCHAR(255) NOT NULL,
    link VARCHAR(500) DEFAULT NULL,
    position ENUM('home_top', 'home_middle', 'home_bottom', 'trading_page', 'sidebar') DEFAULT 'home_top',
    type ENUM('image', 'video', 'html') DEFAULT 'image',
    start_date DATETIME DEFAULT NULL,
    end_date DATETIME DEFAULT NULL,
    sort_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    click_count INT UNSIGNED DEFAULT 0,
    view_count INT UNSIGNED DEFAULT 0,
    created_by BIGINT UNSIGNED DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY banners_position_index (position),
    KEY banners_is_active_index (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS announcements (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    type ENUM('general', 'maintenance', 'new_feature', 'urgent', 'promotion') DEFAULT 'general',
    target_audience ENUM('all', 'users', 'admins', 'verified_only') DEFAULT 'all',
    is_pinned TINYINT(1) DEFAULT 0,
    published_at TIMESTAMP NULL DEFAULT NULL,
    expires_at TIMESTAMP NULL DEFAULT NULL,
    created_by BIGINT UNSIGNED DEFAULT NULL,
    view_count INT UNSIGNED DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY announcements_type_index (type),
    KEY announcements_target_audience_index (target_audience),
    KEY announcements_is_active_index (is_active),
    KEY announcements_published_at_index (published_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED DEFAULT NULL,
    user_type ENUM('user', 'admin') DEFAULT 'user',
    action VARCHAR(100) NOT NULL,
    model_type VARCHAR(100) DEFAULT NULL,
    model_id BIGINT UNSIGNED DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    request_data JSON DEFAULT NULL,
    response_data JSON DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY audit_logs_user_id_index (user_id),
    KEY audit_logs_user_type_index (user_type),
    KEY audit_logs_action_index (action),
    KEY audit_logs_created_at_index (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sessions (
    id VARCHAR(255) NOT NULL,
    user_id BIGINT UNSIGNED DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    payload LONGTEXT NOT NULL,
    last_activity INT NOT NULL,
    PRIMARY KEY (id),
    KEY sessions_user_id_index (user_id),
    KEY sessions_last_activity_index (last_activity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;