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

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
        $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 TINYINT(1) DEFAULT 1,
            is_admin TINYINT(1) DEFAULT 0,
            two_factor_enabled TINYINT(1) DEFAULT 0,
            two_factor_secret VARCHAR(32),
            login_attempts INT DEFAULT 0,
            locked_until DATETIME NULL,
            email_verified TINYINT(1) DEFAULT 0,
            verification_token VARCHAR(100),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL,
            INDEX idx_email (email),
            INDEX idx_username (username),
            INDEX idx_kyc_status (kyc_status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
        $this->db->execute();

        // KYC DOCUMENTS
        $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 DEFAULT CHARSET=utf8");
        $this->db->execute();

        // CRYPTOCURRENCIES
        $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 TINYINT(1) DEFAULT 1,
            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 NULL,
            INDEX idx_symbol (symbol)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
        $this->db->execute();

        // WALLETS
        $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,
            locked_balance DECIMAL(18,8) NOT NULL DEFAULT 0,
            wallet_address VARCHAR(100) UNIQUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL,
            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)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
        $this->db->execute();

        // TRADES
        $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 NULL,
            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
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
        $this->db->execute();

        // ESCROW
        $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 DEFAULT CHARSET=utf8");
        $this->db->execute();

        // TRADING FEES
        $this->db->query("CREATE TABLE IF NOT EXISTS trading_fees (
            id INT AUTO_INCREMENT PRIMARY KEY,
            crypto_id INT NOT NULL,
            fee_percentage DECIMAL(6,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 TINYINT(1) DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL,
            FOREIGN KEY (crypto_id) REFERENCES cryptocurrencies(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
        $this->db->execute();

        // TRANSACTIONS
        $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
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
        $this->db->execute();

        // KNOWLEDGE BASE
        $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 TINYINT(1) DEFAULT 1,
            views INT DEFAULT 0,
            created_by INT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL,
            FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
        $this->db->execute();

        // ADMIN SETTINGS
        $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 VARCHAR(20) DEFAULT 'string',
            description TEXT,
            is_public TINYINT(1) DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
        $this->db->execute();

        // ACTIVITY LOGS
        $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
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
        $this->db->execute();
    }

    private function insertInitialData() {
        // Use BCRYPT for PHP 5.6
        $password = password_hash('admin123', PASSWORD_BCRYPT);

        $this->db->query("INSERT INTO users (username, email, password_hash, first_name, last_name, is_admin, email_verified, kyc_status)
                          VALUES ('admin', 'admin@cryptoexchange.com', :pass, 'System', 'Administrator', 1, 1, 'verified')");
        $this->db->bind(':pass', $password);
        $this->db->execute();

        // Popular cryptos
        $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 $c) {
            $this->db->query("INSERT INTO cryptocurrencies (symbol, name, current_price) VALUES (:s, :n, :p)");
            $this->db->bind(':s', $c[0]);
            $this->db->bind(':n', $c[1]);
            $this->db->bind(':p', $c[2]);
            $this->db->execute();
        }
    }
}

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