-- Cryptocurrency Exchange Database Schema
-- Created for the YELLOW crypto exchange system

CREATE DATABASE IF NOT EXISTS crypto_exchange;
USE crypto_exchange;

-- Users table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL,
    phone VARCHAR(20) UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    country VARCHAR(3) NOT NULL DEFAULT 'UG', -- ISO country code
    currency VARCHAR(3) NOT NULL DEFAULT 'UGX', -- Default currency
    is_verified BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    kyc_status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    referral_code VARCHAR(20) UNIQUE,
    referred_by INT,
    two_factor_enabled BOOLEAN DEFAULT FALSE,
    two_factor_secret VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (referred_by) REFERENCES users(id)
);

-- User profiles and KYC data
CREATE TABLE user_profiles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    date_of_birth DATE,
    address TEXT,
    city VARCHAR(100),
    state VARCHAR(100),
    postal_code VARCHAR(20),
    id_type ENUM('passport', 'national_id', 'drivers_license') DEFAULT 'national_id',
    id_number VARCHAR(100),
    id_front_image VARCHAR(255),
    id_back_image VARCHAR(255),
    selfie_image VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Supported cryptocurrencies
CREATE TABLE cryptocurrencies (
    id INT PRIMARY KEY AUTO_INCREMENT,
    symbol VARCHAR(10) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    logo_url VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    min_withdrawal DECIMAL(20,8) DEFAULT 0.00000001,
    max_withdrawal DECIMAL(20,8) DEFAULT 1000000.00000000,
    withdrawal_fee DECIMAL(20,8) DEFAULT 0.00000000,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Supported networks
CREATE TABLE networks (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    symbol VARCHAR(20) NOT NULL,
    icon VARCHAR(50),
    fee_usd DECIMAL(10,2) DEFAULT 0.00,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- User wallets
CREATE TABLE user_wallets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    crypto_id INT NOT NULL,
    network_id INT NOT NULL,
    address VARCHAR(255) NOT NULL,
    private_key_encrypted TEXT, -- Encrypted private key
    balance DECIMAL(20,8) DEFAULT 0.00000000,
    locked_balance DECIMAL(20,8) DEFAULT 0.00000000, -- For pending transactions
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (crypto_id) REFERENCES cryptocurrencies(id),
    FOREIGN KEY (network_id) REFERENCES networks(id),
    UNIQUE KEY unique_wallet (user_id, crypto_id, network_id)
);

-- Fiat currencies
CREATE TABLE fiat_currencies (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(3) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    symbol VARCHAR(10),
    country VARCHAR(3),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- User fiat balances
CREATE TABLE user_fiat_balances (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    currency_id INT NOT NULL,
    balance DECIMAL(20,2) DEFAULT 0.00,
    locked_balance DECIMAL(20,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (currency_id) REFERENCES fiat_currencies(id),
    UNIQUE KEY unique_fiat_balance (user_id, currency_id)
);

-- Trading pairs
CREATE TABLE trading_pairs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    base_crypto_id INT NOT NULL,
    quote_crypto_id INT NOT NULL,
    symbol VARCHAR(20) NOT NULL, -- e.g., 'USDT/UGX'
    is_active BOOLEAN DEFAULT TRUE,
    min_trade_amount DECIMAL(20,8) DEFAULT 0.00000001,
    max_trade_amount DECIMAL(20,8) DEFAULT 1000000.00000000,
    price_precision INT DEFAULT 8,
    quantity_precision INT DEFAULT 8,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (base_crypto_id) REFERENCES cryptocurrencies(id),
    FOREIGN KEY (quote_crypto_id) REFERENCES cryptocurrencies(id),
    UNIQUE KEY unique_pair (base_crypto_id, quote_crypto_id)
);

-- Market prices (real-time)
CREATE TABLE market_prices (
    id INT PRIMARY KEY AUTO_INCREMENT,
    pair_id INT NOT NULL,
    price DECIMAL(20,8) NOT NULL,
    volume_24h DECIMAL(20,8) DEFAULT 0.00000000,
    change_24h DECIMAL(10,4) DEFAULT 0.0000,
    high_24h DECIMAL(20,8) DEFAULT 0.00000000,
    low_24h DECIMAL(20,8) DEFAULT 0.00000000,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (pair_id) REFERENCES trading_pairs(id),
    INDEX idx_pair_updated (pair_id, updated_at)
);

-- Buy/Sell orders
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    pair_id INT NOT NULL,
    type ENUM('buy', 'sell') NOT NULL,
    side ENUM('market', 'limit') NOT NULL,
    amount DECIMAL(20,8) NOT NULL,
    price DECIMAL(20,8), -- NULL for market orders
    filled_amount DECIMAL(20,8) DEFAULT 0.00000000,
    status ENUM('pending', 'filled', 'cancelled', 'partial') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (pair_id) REFERENCES trading_pairs(id),
    INDEX idx_user_status (user_id, status),
    INDEX idx_pair_status (pair_id, status)
);

-- Transactions (all types)
CREATE TABLE transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    type ENUM('buy', 'sell', 'send', 'receive', 'deposit', 'withdrawal', 'convert') NOT NULL,
    crypto_id INT,
    network_id INT,
    amount DECIMAL(20,8),
    fee DECIMAL(20,8) DEFAULT 0.00000000,
    from_address VARCHAR(255),
    to_address VARCHAR(255),
    tx_hash VARCHAR(255),
    status ENUM('pending', 'confirmed', 'failed', 'cancelled') DEFAULT 'pending',
    confirmation_count INT DEFAULT 0,
    required_confirmations INT DEFAULT 3,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (crypto_id) REFERENCES cryptocurrencies(id),
    FOREIGN KEY (network_id) REFERENCES networks(id),
    INDEX idx_user_type (user_id, type),
    INDEX idx_status (status),
    INDEX idx_tx_hash (tx_hash)
);

-- Deposits
CREATE TABLE deposits (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    currency_id INT NOT NULL,
    amount DECIMAL(20,2) NOT NULL,
    method ENUM('bank_transfer', 'mobile_money', 'card') NOT NULL,
    reference VARCHAR(255),
    status ENUM('pending', 'completed', 'failed', 'cancelled') DEFAULT 'pending',
    processed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (currency_id) REFERENCES fiat_currencies(id)
);

-- Payments (Bitmuk integration)
CREATE TABLE payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    transaction_id VARCHAR(255) NOT NULL UNIQUE,
    phone VARCHAR(20) NOT NULL,
    amount DECIMAL(20,8) NOT NULL,
    currency VARCHAR(10) DEFAULT 'UGX',
    payment_method VARCHAR(50) DEFAULT 'mobile_money',
    status ENUM('pending', 'completed', 'failed', 'cancelled') DEFAULT 'pending',
    bitmuk_reference VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Withdrawals
CREATE TABLE withdrawals (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    crypto_id INT NOT NULL,
    network_id INT NOT NULL,
    amount DECIMAL(20,8) NOT NULL,
    fee DECIMAL(20,8) NOT NULL,
    to_address VARCHAR(255) NOT NULL,
    tx_hash VARCHAR(255),
    status ENUM('pending', 'processing', 'completed', 'failed', 'cancelled') DEFAULT 'pending',
    processed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (crypto_id) REFERENCES cryptocurrencies(id),
    FOREIGN KEY (network_id) REFERENCES networks(id)
);

-- Referrals and rewards
CREATE TABLE referrals (
    id INT PRIMARY KEY AUTO_INCREMENT,
    referrer_id INT NOT NULL,
    referred_id INT NOT NULL,
    commission_rate DECIMAL(5,2) DEFAULT 20.00, -- Percentage
    total_earned DECIMAL(20,8) DEFAULT 0.00000000,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (referrer_id) REFERENCES users(id),
    FOREIGN KEY (referred_id) REFERENCES users(id),
    UNIQUE KEY unique_referral (referrer_id, referred_id)
);

-- Commission transactions
CREATE TABLE commission_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    referral_id INT NOT NULL,
    transaction_id INT NOT NULL,
    commission_amount DECIMAL(20,8) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (referral_id) REFERENCES referrals(id),
    FOREIGN KEY (transaction_id) REFERENCES transactions(id)
);

-- System settings
CREATE TABLE system_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- User sessions
CREATE TABLE user_sessions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    session_token VARCHAR(255) UNIQUE NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_token (session_token),
    INDEX idx_user (user_id)
);

-- Audit logs
CREATE TABLE audit_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    action VARCHAR(100) NOT NULL,
    table_name VARCHAR(100),
    record_id INT,
    old_values JSON,
    new_values JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user_action (user_id, action),
    INDEX idx_created_at (created_at)
);

-- Insert initial data
INSERT INTO cryptocurrencies (symbol, name, logo_url) VALUES
('USDT', 'Tether', 'https://lh3.googleusercontent.com/aida-public/AB6AXuBjeal1Ll7qsNNfmNAz3K1871s0o4k4Z9deMRTZiv8bcbt_CkHU8Nt2jpv3ezWmNRl7quWNnsmJM132FKeSZQ1crYBObrpQ4ZxVhaWYK35N5rBXNW6W_oGgO6t1pE01KUkaiSi5hFiA8LIbYXw5brx6fVUtorwgtUXyL7sEtDtWF7P2CUz-oD9_M5iCbGXF1m16hIB_ZIixWR9URiIEWc0Ma_sLUqKiy-lZqXsmAYo6ioVsvrsmAnC8mco1BwNpWtV3VjuF3jgbsl4'),
('USDC', 'USD Coin', 'https://lh3.googleusercontent.com/aida-public/AB6AXuBjeal1Ll7qsNNfmNAz3K1871s0o4k4Z9deMRTZiv8bcbt_CkHU8Nt2jpv3ezWmNRl7quWNnsmJM132FKeSZQ1crYBObrpQ4ZxVhaWYK35N5rBXNW6W_oGgO6t1pE01KUkaiSi5hFiA8LIbYXw5brx6fVUtorwgtUXyL7sEtDtWF7P2CUz-oD9_M5iCbGXF1m16hIB_ZIixWR9URiIEWc0Ma_sLUqKiy-lZqXsmAYo6ioVsvrsmAnC8mco1BwNpWtV3VjuF3jgbsl4'),
('BTC', 'Bitcoin', 'https://cryptologos.cc/logos/bitcoin-btc-logo.png'),
('ETH', 'Ethereum', 'https://cryptologos.cc/logos/ethereum-eth-logo.png');

INSERT INTO networks (name, symbol, icon, fee_usd) VALUES
('Ethereum', 'ERC20', 'public', 2.50),
('Tron', 'TRC20', 'account_balance', 1.00),
('BSC', 'BEP20', 'hub', 0.50),
('Polygon', 'MATIC', 'hexagon', 0.20),
('Base', 'BASE', 'layers', 0.30),
('Solana', 'SOL', 'bolt', 0.01);

INSERT INTO fiat_currencies (code, name, symbol, country) VALUES
('UGX', 'Ugandan Shilling', 'USh', 'UG'),
('KES', 'Kenyan Shilling', 'KSh', 'KE'),
('TZS', 'Tanzanian Shilling', 'TSh', 'TZ'),
('RWF', 'Rwandan Franc', 'RF', 'RW'),
('CDF', 'Congolese Franc', 'FC', 'CD'),
('NGN', 'Nigerian Naira', '₦', 'NG'),
('USD', 'US Dollar', '$', 'US');

INSERT INTO system_settings (setting_key, setting_value, description) VALUES
('site_name', 'YELLOW Crypto Exchange', 'Website name'),
('default_currency', 'UGX', 'Default fiat currency'),
('min_deposit_amount', '1000', 'Minimum deposit amount'),
('max_deposit_amount', '10000000', 'Maximum deposit amount'),
('referral_commission_rate', '20', 'Default referral commission rate (%)'),
('maintenance_mode', '0', 'Maintenance mode (0=off, 1=on)'),
('api_rate_limit', '100', 'API requests per minute limit');
