<?php
require_once __DIR__ . '/constants.php';

class Database {
    private static $instance = null;
    private $conn;
    
    private function __construct() {
        try {
            $this->conn = new PDO(
                "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=utf8mb4",
                DB_USER,
                DB_PASS,
                [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES => false,
                    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4"
                ]
            );
        } catch(PDOException $e) {
            error_log("Database Error: " . $e->getMessage());
            die("Database connection failed. Please try again later.");
        }
    }
    
    public static function getInstance() {
        if (self::$instance == null) {
            self::$instance = new Database();
        }
        return self::$instance;
    }
    
    public function getConnection() {
        return $this->conn;
    }
    
    // Alias for getConnection() for backward compatibility
    public function connect() {
        return $this->conn;
    }
    
    // Prevent cloning
    private function __clone() {}
    
    // Prevent unserialization
    public function __wakeup() {}
}

// Helper function to get database connection
function getDB() {
    return Database::getInstance()->getConnection();
}

// Initialize database tables
function initDatabase() {
    $db = getDB();
    
    // Check if tables exist, if not create them
    $tables = [
        'users' => "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 VARCHAR(255) NOT NULL,
            phone VARCHAR(20),
            full_name VARCHAR(100),
            country VARCHAR(50),
            city VARCHAR(50),
            address TEXT,
            postal_code VARCHAR(20),
            balance DECIMAL(15,2) DEFAULT 0.00,
            total_deposit DECIMAL(15,2) DEFAULT 0.00,
            total_withdrawal DECIMAL(15,2) DEFAULT 0.00,
            profit_balance DECIMAL(15,2) DEFAULT 0.00,
            referral_balance DECIMAL(15,2) DEFAULT 0.00,
            referral_code VARCHAR(20) UNIQUE,
            referred_by INT,
            account_status ENUM('pending', 'active', 'suspended', 'blocked') DEFAULT 'pending',
            email_verified BOOLEAN DEFAULT FALSE,
            email_verification_token VARCHAR(100),
            email_verification_expiry TIMESTAMP NULL,
            email_verified_at TIMESTAMP NULL,
            password_reset_token VARCHAR(100),
            password_reset_expiry TIMESTAMP NULL,
            two_fa_enabled BOOLEAN DEFAULT FALSE,
            two_fa_secret VARCHAR(255),
            kyc_status ENUM('pending', 'verified', 'rejected') DEFAULT 'pending',
            kyc_document VARCHAR(255),
            kyc_verified_at TIMESTAMP NULL,
            profile_picture VARCHAR(255) NULL,
            date_of_birth DATE NULL,
            language VARCHAR(10) DEFAULT 'en',
            timezone VARCHAR(50) DEFAULT 'UTC',
            currency VARCHAR(10) DEFAULT 'USD',
            login_notifications BOOLEAN DEFAULT TRUE,
            investment_notifications BOOLEAN DEFAULT TRUE,
            profit_notifications BOOLEAN DEFAULT TRUE,
            marketing_notifications BOOLEAN DEFAULT FALSE,
            security_notifications BOOLEAN DEFAULT TRUE,
            deactivation_reason TEXT NULL,
            deactivated_at TIMESTAMP NULL,
            last_login TIMESTAMP NULL,
            last_ip VARCHAR(45),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_status (account_status),
            INDEX idx_referral (referred_by),
            INDEX idx_email (email),
            INDEX idx_created (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        
        'admin_users' => "CREATE TABLE IF NOT EXISTS admin_users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(50) UNIQUE NOT NULL,
            email VARCHAR(100) UNIQUE NOT NULL,
            password VARCHAR(255) NOT NULL,
            role ENUM('superadmin', 'admin', 'support', 'moderator') DEFAULT 'admin',
            permissions TEXT,
            last_login TIMESTAMP NULL,
            last_ip VARCHAR(45),
            status ENUM('active', 'inactive') DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        
        'investment_plans' => "CREATE TABLE IF NOT EXISTS investment_plans (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            description TEXT,
            min_amount DECIMAL(15,2) NOT NULL,
            max_amount DECIMAL(15,2) NOT NULL,
            daily_return DECIMAL(5,2) NOT NULL,
            duration_days INT NOT NULL,
            total_return DECIMAL(5,2) NOT NULL,
            principal_return BOOLEAN DEFAULT TRUE,
            compound_interest BOOLEAN DEFAULT FALSE,
            status ENUM('active', 'inactive') DEFAULT 'active',
            features TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_status (status),
            INDEX idx_amount (min_amount, max_amount)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        
        'investments' => "CREATE TABLE IF NOT EXISTS investments (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            plan_id INT NOT NULL,
            amount DECIMAL(15,2) NOT NULL,
            daily_profit DECIMAL(15,2) NOT NULL,
            total_profit DECIMAL(15,2) DEFAULT 0.00,
            start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            end_date TIMESTAMP NULL,
            status ENUM('running', 'completed', 'cancelled') DEFAULT 'running',
            last_profit_date DATE NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_user (user_id),
            INDEX idx_status (status),
            INDEX idx_dates (start_date, end_date),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (plan_id) REFERENCES investment_plans(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        
        'transactions' => "CREATE TABLE IF NOT EXISTS transactions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            type ENUM('deposit', 'withdrawal', 'investment', 'profit', 'referral_bonus', 'referral_commission', 'bonus', 'debit', 'credit') NOT NULL,
            amount DECIMAL(15,2) NOT NULL,
            fee DECIMAL(15,2) DEFAULT 0.00,
            net_amount DECIMAL(15,2) DEFAULT 0.00,
            method VARCHAR(50),
            description TEXT,
            reference VARCHAR(100) UNIQUE,
            status ENUM('pending', 'approved', 'rejected', 'completed', 'cancelled') DEFAULT 'pending',
            admin_action_by INT NULL,
            admin_note TEXT,
            payment_proof VARCHAR(255),
            wallet_address VARCHAR(255),
            transaction_hash VARCHAR(255),
            details JSON,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_user (user_id),
            INDEX idx_type (type),
            INDEX idx_status (status),
            INDEX idx_reference (reference),
            INDEX idx_created (created_at),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (admin_action_by) REFERENCES admin_users(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        
        'support_tickets' => "CREATE TABLE IF NOT EXISTS support_tickets (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            ticket_id VARCHAR(20) UNIQUE NOT NULL,
            subject VARCHAR(255) NOT NULL,
            message TEXT NOT NULL,
            category ENUM('general', 'deposit', 'withdrawal', 'investment', 'account', 'technical', 'other') DEFAULT 'general',
            priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
            status ENUM('open', 'answered', 'closed', 'resolved') DEFAULT 'open',
            admin_reply TEXT,
            replied_by INT NULL,
            replied_at TIMESTAMP NULL,
            attachments JSON,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_user (user_id),
            INDEX idx_status (status),
            INDEX idx_ticket_id (ticket_id),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (replied_by) REFERENCES admin_users(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        
        'ticket_replies' => "CREATE TABLE IF NOT EXISTS ticket_replies (
            id INT AUTO_INCREMENT PRIMARY KEY,
            ticket_id INT NOT NULL,
            user_id INT NULL,
            user_type ENUM('user', 'admin') NOT NULL DEFAULT 'user',
            message TEXT NOT NULL,
            attachments JSON,
            is_internal BOOLEAN DEFAULT FALSE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_ticket (ticket_id),
            INDEX idx_user (user_id),
            INDEX idx_user_type (user_type),
            INDEX idx_created (created_at),
            FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        
        'activity_logs' => "CREATE TABLE IF NOT EXISTS activity_logs (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NULL,
            admin_id INT NULL,
            action VARCHAR(100) NOT NULL,
            description TEXT,
            ip_address VARCHAR(45),
            user_agent TEXT,
            details JSON,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_user (user_id),
            INDEX idx_admin (admin_id),
            INDEX idx_action (action),
            INDEX idx_created (created_at),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
            FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        
        'login_attempts' => "CREATE TABLE IF NOT EXISTS login_attempts (
            id INT AUTO_INCREMENT PRIMARY KEY,
            ip_address VARCHAR(45) NOT NULL,
            username VARCHAR(100),
            success BOOLEAN DEFAULT FALSE,
            user_agent TEXT,
            attempt_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_ip (ip_address),
            INDEX idx_time (attempt_time)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        
        'email_templates' => "CREATE TABLE IF NOT EXISTS email_templates (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            subject VARCHAR(255) NOT NULL,
            content TEXT NOT NULL,
            variables TEXT,
            status ENUM('active', 'inactive') DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        
        'notifications' => "CREATE TABLE IF NOT EXISTS notifications (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            type VARCHAR(50) NOT NULL,
            title VARCHAR(255) NOT NULL,
            message TEXT NOT NULL,
            is_read BOOLEAN DEFAULT FALSE,
            data JSON,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_user (user_id),
            INDEX idx_read (is_read),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        
        'settings' => "CREATE TABLE IF NOT EXISTS settings (
            id INT AUTO_INCREMENT PRIMARY KEY,
            `key` VARCHAR(100) NOT NULL,
            `value` TEXT,
            `type` VARCHAR(50) DEFAULT 'text',
            category VARCHAR(50),
            description TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY `idx_key_unique` (`key`),
            INDEX idx_key (`key`),
            INDEX idx_category (category)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
    ];
    
    foreach ($tables as $table => $sql) {
        try {
            $db->exec($sql);
        } catch (PDOException $e) {
            error_log("Table creation error for $table: " . $e->getMessage());
        }
    }
}

// Call initialization
initDatabase();
?>