<?php
require_once 'config.php';
require_once 'Database.php';

// Database installation script
class DatabaseInstaller {
    private $db;
    
    public function __construct() {
        $this->db = new Database();
    }
    
    public function install() {
        try {
            $this->createTables();
            $this->insertInitialData();
            echo "<h2>Database installed successfully!</h2>";
            echo "<p><a href='index.php'>Go to Application</a></p>";
        } catch (Exception $e) {
            echo "<h2>Installation Error:</h2>";
            echo "<p>" . $e->getMessage() . "</p>";
        }
    }
    
    private function createTables() {
        // Users table with KYC fields
        $this->db->query("CREATE TABLE IF NOT EXISTS users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(50) UNIQUE NOT NULL,
            email VARCHAR(100) UNIQUE NOT NULL,
            password_hash VARCHAR(255) NOT NULL,
            first_name VARCHAR(50) NOT NULL,
            last_name VARCHAR(50) NOT NULL,
            phone VARCHAR(20),
            country VARCHAR(50),
            date_of_birth DATE,
            address TEXT,
            kyc_status ENUM('pending', 'verified', 'rejected') DEFAULT 'pending',
            kyc_level INT DEFAULT 0,
            is_active BOOLEAN DEFAULT TRUE,
            is_admin BOOLEAN DEFAULT FALSE,
            two_factor_enabled BOOLEAN DEFAULT FALSE,
            two_factor_secret VARCHAR(32),
            login_attempts INT DEFAULT 0,
            locked_until DATETIME NULL,
            email_verified BOOLEAN DEFAULT FALSE,
            verification_token VARCHAR(100),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_email (email),
            INDEX idx_username (username),
            INDEX idx_kyc_status (kyc_status)
        ) ENGINE=InnoDB");
        $this->db->execute();

        // KYC Documents table
        $this->db->query("CREATE TABLE IF NOT EXISTS kyc_documents (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            document_type ENUM('passport', 'driver_license', 'national_id', 'utility_bill', 'bank_statement') NOT NULL,
            document_number VARCHAR(50),
            file_path VARCHAR(255) NOT NULL,
            status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
            rejection_reason TEXT,
            submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            reviewed_at TIMESTAMP NULL,
            reviewed_by INT,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL
        ) ENGINE=InnoDB");
        $this->db->execute();

        // Cryptocurrencies table
        $this->db->query("CREATE TABLE IF NOT EXISTS cryptocurrencies (
            id INT AUTO_INCREMENT PRIMARY KEY,
            symbol VARCHAR(10) UNIQUE NOT NULL,
            name VARCHAR(50) NOT NULL,
            current_price DECIMAL(18,8) NOT NULL DEFAULT 0,
            is_active BOOLEAN DEFAULT TRUE,
            min_trade_amount DECIMAL(18,8) DEFAULT 0.00000001,
            max_trade_amount DECIMAL(18,8) DEFAULT 999999.99999999,
            decimals INT DEFAULT 8,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_symbol (symbol)
        ) ENGINE=InnoDB");
        $this->db->execute();

        // Wallets table
        $this->db->query("CREATE TABLE IF NOT EXISTS wallets (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            crypto_id INT NOT NULL,
            balance DECIMAL(18,8) NOT NULL DEFAULT 0.00000000,
            locked_balance DECIMAL(18,8) NOT NULL DEFAULT 0.00000000,
            wallet_address VARCHAR(100) UNIQUE,
            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) ON DELETE CASCADE,
            UNIQUE KEY unique_user_crypto (user_id, crypto_id),
            INDEX idx_user_id (user_id)
        ) ENGINE=InnoDB");
        $this->db->execute();

        // Trades table
        $this->db->query("CREATE TABLE IF NOT EXISTS trades (
            id INT AUTO_INCREMENT PRIMARY KEY,
            seller_id INT NOT NULL,
            buyer_id INT,
            crypto_id INT NOT NULL,
            amount DECIMAL(18,8) NOT NULL,
            price_per_unit DECIMAL(18,8) NOT NULL,
            total_amount DECIMAL(18,8) NOT NULL,
            trading_fee DECIMAL(18,8) NOT NULL DEFAULT 0,
            status ENUM('open', 'matched', 'in_escrow', 'payment_pending', 'completed', 'cancelled', 'disputed') DEFAULT 'open',
            payment_method VARCHAR(50),
            payment_details TEXT,
            escrow_address VARCHAR(100),
            expires_at DATETIME,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            completed_at TIMESTAMP NULL,
            FOREIGN KEY (seller_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (buyer_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (crypto_id) REFERENCES cryptocurrencies(id) ON DELETE CASCADE,
            INDEX idx_status (status),
            INDEX idx_crypto_id (crypto_id),
            INDEX idx_seller_id (seller_id),
            INDEX idx_buyer_id (buyer_id)
        ) ENGINE=InnoDB");
        $this->db->execute();

        // Escrow table
        $this->db->query("CREATE TABLE IF NOT EXISTS escrow (
            id INT AUTO_INCREMENT PRIMARY KEY,
            trade_id INT UNIQUE NOT NULL,
            amount DECIMAL(18,8) NOT NULL,
            status ENUM('locked', 'released', 'refunded') DEFAULT 'locked',
            locked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            released_at TIMESTAMP NULL,
            released_by INT,
            notes TEXT,
            FOREIGN KEY (trade_id) REFERENCES trades(id) ON DELETE CASCADE,
            FOREIGN KEY (released_by) REFERENCES users(id) ON DELETE SET NULL
        ) ENGINE=InnoDB");
        $this->db->execute();

        // Trading fees table
        $this->db->query("CREATE TABLE IF NOT EXISTS trading_fees (
            id INT AUTO_INCREMENT PRIMARY KEY,
            crypto_id INT NOT NULL,
            fee_percentage DECIMAL(5,4) NOT NULL DEFAULT 0.0050,
            minimum_fee DECIMAL(18,8) NOT NULL DEFAULT 0.00000100,
            maximum_fee DECIMAL(18,8) NOT NULL DEFAULT 1.00000000,
            is_active BOOLEAN DEFAULT TRUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (crypto_id) REFERENCES cryptocurrencies(id) ON DELETE CASCADE
        ) ENGINE=InnoDB");
        $this->db->execute();

        // Transactions table
        $this->db->query("CREATE TABLE IF NOT EXISTS transactions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            crypto_id INT NOT NULL,
            transaction_type ENUM('deposit', 'withdrawal', 'trade_buy', 'trade_sell', 'fee', 'bonus') NOT NULL,
            amount DECIMAL(18,8) NOT NULL,
            balance_before DECIMAL(18,8) NOT NULL,
            balance_after DECIMAL(18,8) NOT NULL,
            reference_id INT,
            reference_type VARCHAR(20),
            transaction_hash VARCHAR(100),
            status ENUM('pending', 'confirmed', 'failed') DEFAULT 'pending',
            notes TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (crypto_id) REFERENCES cryptocurrencies(id) ON DELETE CASCADE,
            INDEX idx_user_id (user_id),
            INDEX idx_type (transaction_type),
            INDEX idx_status (status)
        ) ENGINE=InnoDB");
        $this->db->execute();

        // Knowledge base table
        $this->db->query("CREATE TABLE IF NOT EXISTS knowledge_base (
            id INT AUTO_INCREMENT PRIMARY KEY,
            category VARCHAR(50) NOT NULL,
            title VARCHAR(200) NOT NULL,
            content TEXT NOT NULL,
            tags VARCHAR(200),
            is_active BOOLEAN DEFAULT TRUE,
            views INT DEFAULT 0,
            created_by INT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
            INDEX idx_category (category),
            INDEX idx_active (is_active)
        ) ENGINE=InnoDB");
        $this->db->execute();

        // Admin settings table
        $this->db->query("CREATE TABLE IF NOT EXISTS admin_settings (
            id INT AUTO_INCREMENT PRIMARY KEY,
            setting_key VARCHAR(50) UNIQUE NOT NULL,
            setting_value TEXT,
            setting_type ENUM('string', 'number', 'boolean', 'json') DEFAULT 'string',
            description TEXT,
            is_public BOOLEAN DEFAULT FALSE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_key (setting_key)
        ) ENGINE=InnoDB");
        $this->db->execute();

        // Activity logs table
        $this->db->query("CREATE TABLE IF NOT EXISTS activity_logs (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT,
            action VARCHAR(100) NOT NULL,
            details TEXT,
            ip_address VARCHAR(45),
            user_agent TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            INDEX idx_user_id (user_id),
            INDEX idx_action (action),
            INDEX idx_created_at (created_at)
        ) ENGINE=InnoDB");
        $this->db->execute();
    }
    
    private function insertInitialData() {
        // Create admin user
        $password = password_hash('admin123', PASSWORD_ARGON2ID);
        $this->db->query("INSERT INTO users (username, email, password_hash, first_name, last_name, is_admin, email_verified, kyc_status) 
                         VALUES (:username, :email, :password, :fname, :lname, 1, 1, 'verified')");
        $this->db->bind(':username', 'admin');
        $this->db->bind(':email', 'admin@cryptoexchange.com');
        $this->db->bind(':password', $password);
        $this->db->bind(':fname', 'System');
        $this->db->bind(':lname', 'Administrator');
        $this->db->execute();

        // Insert popular cryptocurrencies
        $cryptos = [
            ['BTC', 'Bitcoin', 45000.00000000],
            ['ETH', 'Ethereum', 3000.00000000],
            ['USDT', 'Tether', 1.00000000],
            ['BNB', 'Binance Coin', 400.00000000],
            ['ADA', 'Cardano', 0.50000000],
            ['XRP', 'Ripple', 0.60000000],
            ['DOT', 'Polkadot', 8.00000000],
            ['LTC', 'Litecoin', 150.00000000]
        ];

        foreach ($cryptos as $crypto) {
            $this->db->query("INSERT INTO cryptocurrencies (symbol, name, current_price) VALUES (:symbol, :name, :price)");
            $this->db->bind(':symbol', $crypto[0]);
            $this->db->bind(':name', $crypto[1]);
            $this->db->bind(':price', $crypto[2]);
            $this->db->execute();
        }

        // Insert default trading fees
        for ($i = 1; $i <= 8; $i++) {
            $this->db->query("INSERT INTO trading_fees (crypto_id, fee_percentage, minimum_fee, maximum_fee) 
                             VALUES (:crypto_id, 0.0025, 0.00000100, 1.00000000)");
            $this->db->bind(':crypto_id', $i);
            $this->db->execute();
        }

        // Insert admin settings
        $settings = [
            ['maintenance_mode', '0', 'boolean', 'Enable/Disable maintenance mode'],
            ['registration_enabled', '1', 'boolean', 'Enable/Disable user registration'],
            ['kyc_required', '1', 'boolean', 'Require KYC verification for trading'],
            ['max_daily_withdrawal', '10000', 'number', 'Maximum daily withdrawal limit'],
            ['site_email', 'noreply@cryptoexchange.com', 'string', 'Site email address'],
            ['trade_timeout_minutes', '30', 'number', 'Trade timeout in minutes']
        ];

        foreach ($settings as $setting) {
            $this->db->query("INSERT INTO admin_settings (setting_key, setting_value, setting_type, description) 
                             VALUES (:key, :value, :type, :desc)");
            $this->db->bind(':key', $setting[0]);
            $this->db->bind(':value', $setting[1]);
            $this->db->bind(':type', $setting[2]);
            $this->db->bind(':desc', $setting[3]);
            $this->db->execute();
        }

        // Insert sample knowledge base articles
        $articles = [
            ['Getting Started', 'How to Create an Account', 'Learn how to create and verify your account on our platform...'],
            ['Security', 'Two-Factor Authentication', 'Enable 2FA to secure your account...'],
            ['Trading', 'How to Buy Cryptocurrency', 'Step-by-step guide to buying cryptocurrency...'],
            ['Trading', 'How to Sell Cryptocurrency', 'Step-by-step guide to selling cryptocurrency...'],
            ['KYC', 'Identity Verification Process', 'Learn about our KYC verification process...']
        ];

        foreach ($articles as $article) {
            $this->db->query("INSERT INTO knowledge_base (category, title, content, created_by) 
                             VALUES (:category, :title, :content, 1)");
            $this->db->bind(':category', $article[0]);
            $this->db->bind(':title', $article[1]);
            $this->db->bind(':content', $article[2]);
            $this->db->execute();
        }
    }
}

// Run installation
echo "<h1>CryptoExchange Pro - Database Installation</h1>";
$installer = new DatabaseInstaller();
$installer->install();
?>