<?php
// includes/db.php - Database connection with MySQL and SQLite support

/**
 * MySQLiPDOWrapper - Wraps MySQLi connection in PDO-compatible interface
 * 
 * This class provides a PDO-like interface for MySQLi connections,
 * allowing the application to use the same API for both MySQL and SQLite.
 */
class MySQLiPDOWrapper {
    private $mysqli;
    private $lastInsertId = 0;
    
    public function __construct($mysqli) {
        $this->mysqli = $mysqli;
    }
    
    /**
     * Prepare a SQL statement
     * Returns a MySQLiStatementWrapper that mimics PDOStatement
     * Automatically translates SQLite syntax to MySQL syntax
     */
    public function prepare($sql) {
        // Translate SQLite syntax to MySQL syntax
        $sql = translate_query_to_mysql($sql);
        
        $stmt = $this->mysqli->prepare($sql);
        if (!$stmt) {
            throw new Exception("Prepare failed: " . $this->mysqli->error);
        }
        return new MySQLiStatementWrapper($stmt, $this);
    }
    
    /**
     * Execute a SQL statement (for DDL statements like CREATE, ALTER, DROP)
     * Returns number of affected rows or FALSE on failure
     * Automatically translates SQLite syntax to MySQL syntax
     */
    public function exec($sql) {
        // Translate SQLite syntax to MySQL syntax
        $sql = translate_query_to_mysql($sql);
        
        $result = $this->mysqli->query($sql);
        if ($result === false) {
            throw new Exception("Exec failed: " . $this->mysqli->error);
        }
        return $this->mysqli->affected_rows;
    }
    
    /**
     * Execute a simple query and return result
     * For SELECT queries that don't need parameter binding
     * Automatically translates SQLite syntax to MySQL syntax
     */
    public function query($sql) {
        // Translate SQLite syntax to MySQL syntax
        $sql = translate_query_to_mysql($sql);
        
        $result = $this->mysqli->query($sql);
        if ($result === false) {
            throw new Exception("Query failed: " . $this->mysqli->error);
        }
        
        // For SELECT queries, wrap result in MySQLiResultWrapper
        if ($result instanceof mysqli_result) {
            return new MySQLiResultWrapper($result);
        }
        
        // For INSERT/UPDATE/DELETE, return TRUE
        return true;
    }
    
    /**
     * Get the last inserted auto-increment ID
     */
    public function lastInsertId() {
        return $this->mysqli->insert_id;
    }
    
    /**
     * Set last insert ID (called by statement wrapper)
     */
    public function setLastInsertId($id) {
        $this->lastInsertId = $id;
    }
    
    /**
     * Get error message
     */
    public function errorInfo() {
        return [null, $this->mysqli->errno, $this->mysqli->error];
    }
    
    /**
     * Begin transaction
     */
    public function beginTransaction() {
        return $this->mysqli->begin_transaction();
    }
    
    /**
     * Commit transaction
     */
    public function commit() {
        return $this->mysqli->commit();
    }
    
    /**
     * Rollback transaction
     */
    public function rollback() {
        return $this->mysqli->rollback();
    }
    
    /**
     * Get the underlying MySQLi connection
     */
    public function getMySQLi() {
        return $this->mysqli;
    }
}

/**
 * MySQLiStatementWrapper - Wraps MySQLi prepared statement in PDOStatement-compatible interface
 */
class MySQLiStatementWrapper {
    private $stmt;
    private $wrapper;
    private $result;
    private $boundParams = [];
    
    public function __construct($stmt, $wrapper) {
        $this->stmt = $stmt;
        $this->wrapper = $wrapper;
    }
    
    /**
     * Execute the prepared statement with parameters
     * Accepts array of parameters (PDO style)
     */
    public function execute($params = []) {
        if (!empty($params)) {
            // Build type string and bind parameters
            $types = '';
            $bindParams = [];
            
            foreach ($params as $param) {
                if (is_int($param)) {
                    $types .= 'i';
                } elseif (is_float($param)) {
                    $types .= 'd';
                } elseif (is_string($param)) {
                    $types .= 's';
                } else {
                    $types .= 's'; // Default to string
                }
                $bindParams[] = $param;
            }
            
            // Bind parameters using call_user_func_array
            $bindArray = [$types];
            foreach ($bindParams as $key => $value) {
                $bindArray[] = &$bindParams[$key];
            }
            call_user_func_array([$this->stmt, 'bind_param'], $bindArray);
        }
        
        $result = $this->stmt->execute();
        if (!$result) {
            throw new Exception("Execute failed: " . $this->stmt->error);
        }
        
        // Store result for fetching
        $this->result = $this->stmt->get_result();
        
        // Update last insert ID if applicable
        if ($this->stmt->insert_id > 0) {
            $this->wrapper->setLastInsertId($this->stmt->insert_id);
        }
        
        return $result;
    }
    
    /**
     * Fetch next row as associative array
     */
    public function fetch($fetchStyle = PDO::FETCH_ASSOC) {
        if (!$this->result) {
            return false;
        }
        
        if ($fetchStyle === PDO::FETCH_ASSOC) {
            return $this->result->fetch_assoc();
        } elseif ($fetchStyle === PDO::FETCH_NUM) {
            return $this->result->fetch_row();
        } elseif ($fetchStyle === PDO::FETCH_BOTH) {
            return $this->result->fetch_array(MYSQLI_BOTH);
        }
        
        return $this->result->fetch_assoc();
    }
    
    /**
     * Fetch all rows as array of associative arrays
     */
    public function fetchAll($fetchStyle = PDO::FETCH_ASSOC) {
        if (!$this->result) {
            return [];
        }
        
        $rows = [];
        while ($row = $this->fetch($fetchStyle)) {
            $rows[] = $row;
        }
        return $rows;
    }
    
    /**
     * Fetch single column from next row
     */
    public function fetchColumn($columnNumber = 0) {
        if (!$this->result) {
            return false;
        }
        
        $row = $this->result->fetch_row();
        if ($row && isset($row[$columnNumber])) {
            return $row[$columnNumber];
        }
        return false;
    }
    
    /**
     * Get row count (for SELECT queries)
     */
    public function rowCount() {
        if ($this->result) {
            return $this->result->num_rows;
        }
        return $this->stmt->affected_rows;
    }
    
    /**
     * Close the statement
     */
    public function closeCursor() {
        if ($this->result) {
            $this->result->free();
        }
        return true;
    }
}

/**
 * MySQLiResultWrapper - Wraps MySQLi result in PDOStatement-compatible interface
 * Used for simple query() calls
 */
class MySQLiResultWrapper {
    private $result;
    
    public function __construct($result) {
        $this->result = $result;
    }
    
    public function fetch($fetchStyle = PDO::FETCH_ASSOC) {
        if ($fetchStyle === PDO::FETCH_ASSOC) {
            return $this->result->fetch_assoc();
        } elseif ($fetchStyle === PDO::FETCH_NUM) {
            return $this->result->fetch_row();
        } elseif ($fetchStyle === PDO::FETCH_BOTH) {
            return $this->result->fetch_array(MYSQLI_BOTH);
        }
        return $this->result->fetch_assoc();
    }
    
    public function fetchAll($fetchStyle = PDO::FETCH_ASSOC) {
        $rows = [];
        while ($row = $this->fetch($fetchStyle)) {
            $rows[] = $row;
        }
        return $rows;
    }
    
    public function fetchColumn($columnNumber = 0) {
        $row = $this->result->fetch_row();
        if ($row && isset($row[$columnNumber])) {
            return $row[$columnNumber];
        }
        return false;
    }
    
    public function rowCount() {
        return $this->result->num_rows;
    }
}

/**
 * Get database connection
 * Returns PDO for SQLite or MySQLiPDOWrapper for MySQL
 */
function get_db() {
    static $db = null;
    if ($db !== null) return $db;

    // Check if configuration file exists
    $configFile = __DIR__ . '/../config/database.php';
    if (!file_exists($configFile)) {
        die('
            <h1>Configuration Required</h1>
            <p>Database configuration file is missing.</p>
            <h3>Setup Instructions:</h3>
            <ol>
                <li>Copy <code>config/database.php.example</code> to <code>config/database.php</code></li>
                <li>Edit <code>config/database.php</code> with your database credentials</li>
                <li>For SQLite: Ensure <code>data/</code> directory is writable</li>
                <li>For MySQL: Create database and configure credentials</li>
            </ol>
            <p><strong>Note:</strong> Never commit <code>config/database.php</code> to version control!</p>
        ');
    }

    // Load configuration
    require_once $configFile;

    // Determine database type (default to SQLite if not specified)
    $dbType = defined('DB_TYPE') ? DB_TYPE : 'sqlite';

    try {
        if ($dbType === 'mysql') {
            // MySQL connection using MySQLi
            $host = defined('DB_HOST') ? DB_HOST : 'localhost';
            $dbname = defined('DB_NAME') ? DB_NAME : 'mygb_school';
            $user = defined('DB_USER') ? DB_USER : 'root';
            $pass = defined('DB_PASS') ? DB_PASS : '';
            $port = defined('DB_PORT') ? DB_PORT : 3306;
            $charset = defined('DB_CHARSET') ? DB_CHARSET : 'utf8mb4';

            // Create MySQLi connection
            $mysqli = new mysqli($host, $user, $pass, $dbname, $port);

            // Check connection
            if ($mysqli->connect_error) {
                throw new Exception("MySQL Connection Failed: " . $mysqli->connect_error);
            }

            // Set charset
            if (!$mysqli->set_charset($charset)) {
                throw new Exception("Error setting charset: " . $mysqli->error);
            }

            // Wrap in PDO-compatible interface
            $db = new MySQLiPDOWrapper($mysqli);

        } else {
            // SQLite connection using PDO
            $dataDir = __DIR__ . '/../data';
            if (!is_dir($dataDir)) {
                mkdir($dataDir, 0755, true);
            }

            // Use SQLITE_DB_PATH if defined, otherwise use default
            $dbFile = defined('SQLITE_DB_PATH') ? SQLITE_DB_PATH : $dataDir . '/school.db';

            $dsn = 'sqlite:' . $dbFile;
            $db = new PDO($dsn);
            $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            
            // Enable foreign keys for SQLite
            $db->exec('PRAGMA foreign_keys = ON');
        }

    } catch (Exception $e) {
        // User-friendly error message with troubleshooting guidance
        $errorMsg = htmlspecialchars($e->getMessage());
        
        if ($dbType === 'mysql') {
            die('
                <h1>Database Connection Error</h1>
                <p>Unable to connect to MySQL database.</p>
                <p><strong>Error:</strong> ' . $errorMsg . '</p>
                <h3>Troubleshooting:</h3>
                <ul>
                    <li>Verify MySQL server is running (check XAMPP Control Panel)</li>
                    <li>Check database credentials in <code>config/database.php</code></li>
                    <li>Ensure database "' . htmlspecialchars($dbname) . '" exists</li>
                    <li>Verify user "' . htmlspecialchars($user) . '" has proper permissions</li>
                    <li>Check if port ' . $port . ' is correct</li>
                    <li>Try connecting with phpMyAdmin to verify credentials</li>
                </ul>
                <h3>Quick Fix:</h3>
                <p>If you want to use SQLite instead, change <code>DB_TYPE</code> to <code>\'sqlite\'</code> in <code>config/database.php</code></p>
            ');
        } else {
            die('
                <h1>Database Connection Error</h1>
                <p>Unable to connect to SQLite database.</p>
                <p><strong>Error:</strong> ' . $errorMsg . '</p>
                <h3>Troubleshooting:</h3>
                <ul>
                    <li>Ensure <code>data/</code> directory exists and is writable</li>
                    <li>Check file permissions on database file</li>
                    <li>Verify SQLite extension is enabled in PHP</li>
                    <li>Check <code>SQLITE_DB_PATH</code> in <code>config/database.php</code></li>
                </ul>
            ');
        }
    }

    return $db;
}

/**
 * Convert SQLite schema to MySQL-compatible schema
 * 
 * This function takes a SQLite CREATE TABLE statement and converts it to MySQL format.
 * Handles data type conversions, auto-increment syntax, and adds MySQL-specific options.
 * 
 * @param string $sqliteSchema SQLite CREATE TABLE statement
 * @return string MySQL-compatible CREATE TABLE statement
 */
function convert_sqlite_to_mysql_schema($sqliteSchema) {
    $mysqlSchema = $sqliteSchema;
    
    // Convert INTEGER PRIMARY KEY AUTOINCREMENT to INT AUTO_INCREMENT PRIMARY KEY
    $mysqlSchema = preg_replace(
        '/INTEGER\s+PRIMARY\s+KEY\s+AUTOINCREMENT/i',
        'INT AUTO_INCREMENT PRIMARY KEY',
        $mysqlSchema
    );
    
    // Convert standalone AUTOINCREMENT to AUTO_INCREMENT
    $mysqlSchema = preg_replace(
        '/\bAUTOINCREMENT\b/i',
        'AUTO_INCREMENT',
        $mysqlSchema
    );
    
    // Convert TEXT types to appropriate MySQL types
    // For columns that likely need VARCHAR (names, titles, short text)
    $mysqlSchema = preg_replace_callback(
        '/(\w+)\s+TEXT(\s+(?:NOT\s+NULL|UNIQUE|DEFAULT|PRIMARY|REFERENCES))/i',
        function($matches) {
            $columnName = strtolower($matches[1]);
            // Short text fields get VARCHAR(255)
            if (preg_match('/(name|title|nis|username|email|phone|grade|key|type|status)/', $columnName)) {
                return $matches[1] . ' VARCHAR(255)' . $matches[2];
            }
            // Medium text fields get TEXT
            return $matches[1] . ' TEXT' . $matches[2];
        },
        $mysqlSchema
    );
    
    // Convert remaining TEXT at end of line or before comma
    $mysqlSchema = preg_replace_callback(
        '/(\w+)\s+TEXT(\s*[,)])/i',
        function($matches) {
            $columnName = strtolower($matches[1]);
            // Short text fields get VARCHAR(255)
            if (preg_match('/(name|title|nis|username|email|phone|grade|key|type|status)/', $columnName)) {
                return $matches[1] . ' VARCHAR(255)' . $matches[2];
            }
            // Content/description fields get LONGTEXT
            if (preg_match('/(content|description|text|body|message)/', $columnName)) {
                return $matches[1] . ' LONGTEXT' . $matches[2];
            }
            // Default to TEXT
            return $matches[1] . ' TEXT' . $matches[2];
        },
        $mysqlSchema
    );
    
    // Convert REAL to DECIMAL for currency/precision fields
    $mysqlSchema = preg_replace(
        '/\bREAL\b/i',
        'DECIMAL(10,2)',
        $mysqlSchema
    );
    
    // Convert BLOB (keep as is, MySQL supports BLOB)
    // No conversion needed for BLOB
    
    // Handle CURRENT_TIMESTAMP - MySQL format is compatible, but ensure proper syntax
    // SQLite uses CURRENT_TIMESTAMP, MySQL uses CURRENT_TIMESTAMP or NOW()
    // Both are compatible, no conversion needed
    
    // Add ENGINE=InnoDB and CHARSET=utf8mb4 to CREATE TABLE statements
    // Find the closing parenthesis of CREATE TABLE and add MySQL options
    if (preg_match('/CREATE\s+TABLE/i', $mysqlSchema)) {
        // Remove any existing semicolon at the end
        $mysqlSchema = rtrim($mysqlSchema, "; \t\n\r\0\x0B");
        
        // Check if it already has ENGINE or CHARSET
        if (!preg_match('/ENGINE\s*=/i', $mysqlSchema)) {
            // Find the last closing parenthesis
            $lastParen = strrpos($mysqlSchema, ')');
            if ($lastParen !== false) {
                $mysqlSchema = substr($mysqlSchema, 0, $lastParen + 1) . 
                              ' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci' .
                              substr($mysqlSchema, $lastParen + 1);
            }
        }
    }
    
    // Handle MySQL reserved keywords by adding backticks
    // Common reserved keywords that might be used as column names
    $reservedKeywords = ['key', 'value', 'order', 'group', 'index', 'table', 'database', 'select', 'where', 'from'];
    foreach ($reservedKeywords as $keyword) {
        // Add backticks around reserved keywords when used as column names
        // Match word boundaries to avoid partial matches
        $mysqlSchema = preg_replace(
            '/\b(' . $keyword . ')\s+(VARCHAR|TEXT|INT|DECIMAL|BLOB|TIMESTAMP|DATE|DATETIME)/i',
            '`$1` $2',
            $mysqlSchema
        );
    }
    
    return $mysqlSchema;
}

/**
 * Translate SQLite query syntax to MySQL syntax
 * 
 * This function converts SQLite-specific SQL syntax to MySQL-compatible syntax.
 * Handles INSERT OR IGNORE, INSERT OR REPLACE, PRAGMA statements, and other differences.
 * 
 * @param string $sqliteQuery SQLite SQL query
 * @return string MySQL-compatible SQL query
 */
function translate_query_to_mysql($sqliteQuery) {
    $mysqlQuery = $sqliteQuery;
    
    // Convert INSERT OR IGNORE to INSERT IGNORE
    $mysqlQuery = preg_replace(
        '/INSERT\s+OR\s+IGNORE\s+INTO/i',
        'INSERT IGNORE INTO',
        $mysqlQuery
    );
    
    // Convert INSERT OR REPLACE to REPLACE INTO
    $mysqlQuery = preg_replace(
        '/INSERT\s+OR\s+REPLACE\s+INTO/i',
        'REPLACE INTO',
        $mysqlQuery
    );
    
    // Convert PRAGMA statements to MySQL equivalents
    // PRAGMA foreign_keys = ON (no direct equivalent, InnoDB has it by default)
    if (preg_match('/PRAGMA\s+foreign_keys\s*=\s*(ON|OFF)/i', $mysqlQuery, $matches)) {
        // MySQL InnoDB has foreign keys enabled by default
        // We can use SET foreign_key_checks = 1/0 for similar behavior
        $value = strtoupper($matches[1]) === 'ON' ? '1' : '0';
        $mysqlQuery = "SET foreign_key_checks = $value";
    }
    
    // PRAGMA table_info(table_name) -> SHOW COLUMNS FROM table_name
    $mysqlQuery = preg_replace(
        '/PRAGMA\s+table_info\s*\(\s*([\'"]?)(\w+)\1\s*\)/i',
        'SHOW COLUMNS FROM $2',
        $mysqlQuery
    );
    
    // PRAGMA table_list -> SHOW TABLES
    $mysqlQuery = preg_replace(
        '/PRAGMA\s+table_list/i',
        'SHOW TABLES',
        $mysqlQuery
    );
    
    // PRAGMA database_list -> SHOW DATABASES
    $mysqlQuery = preg_replace(
        '/PRAGMA\s+database_list/i',
        'SHOW DATABASES',
        $mysqlQuery
    );
    
    // PRAGMA index_list(table_name) -> SHOW INDEX FROM table_name
    $mysqlQuery = preg_replace(
        '/PRAGMA\s+index_list\s*\(\s*([\'"]?)(\w+)\1\s*\)/i',
        'SHOW INDEX FROM $2',
        $mysqlQuery
    );
    
    // Handle backticks for MySQL reserved keywords
    // MySQL uses backticks for identifiers, SQLite uses double quotes or brackets
    // Convert double quotes around identifiers to backticks
    $mysqlQuery = preg_replace(
        '/"(\w+)"/i',
        '`$1`',
        $mysqlQuery
    );
    
    // Convert SQLite's strftime to MySQL's DATE_FORMAT
    // This is a complex conversion, handling basic cases
    $mysqlQuery = preg_replace(
        '/strftime\s*\(\s*[\'"]%Y-%m-%d[\'"]\s*,\s*([^)]+)\)/i',
        'DATE_FORMAT($1, \'%Y-%m-%d\')',
        $mysqlQuery
    );
    
    // Convert SQLite's datetime('now') to MySQL's NOW()
    $mysqlQuery = preg_replace(
        '/datetime\s*\(\s*[\'"]now[\'"]\s*\)/i',
        'NOW()',
        $mysqlQuery
    );
    
    // Convert SQLite's date('now') to MySQL's CURDATE()
    $mysqlQuery = preg_replace(
        '/date\s*\(\s*[\'"]now[\'"]\s*\)/i',
        'CURDATE()',
        $mysqlQuery
    );
    
    // Convert SQLite's time('now') to MySQL's CURTIME()
    $mysqlQuery = preg_replace(
        '/time\s*\(\s*[\'"]now[\'"]\s*\)/i',
        'CURTIME()',
        $mysqlQuery
    );
    
    // Convert AUTOINCREMENT to AUTO_INCREMENT in ALTER TABLE statements
    $mysqlQuery = preg_replace(
        '/\bAUTOINCREMENT\b/i',
        'AUTO_INCREMENT',
        $mysqlQuery
    );
    
    return $mysqlQuery;
}
