One Hat Cyber Team

View File Name : database.php

<?php
/**
 * Database Configuration File
 * Farmers Loan Management SAAS System
 * Version: 1.0.0
 */

// =============================================
// DATABASE CONFIGURATION
// =============================================

// Development Environment (XAMPP Defaults)
define('DB_HOST', 'localhost');
define('DB_PORT', '3306');
define('DB_NAME', 'farmers_loan_management');
define('DB_USER', 'root');          // XAMPP default
define('DB_PASS', '');              // XAMPP default (empty)
define('DB_CHARSET', 'utf8mb4');
define('DB_COLLATION', 'utf8mb4_unicode_ci');

// Connection Options
define('DB_PERSISTENT', false);
define('DB_EMULATE_PREPARES', false);
define('DB_ERROR_MODE', PDO::ERRMODE_EXCEPTION);
define('DB_DEFAULT_FETCH_MODE', PDO::FETCH_ASSOC);

// Connection Pool Settings
define('DB_MAX_CONNECTIONS', 20);
define('DB_IDLE_TIMEOUT', 60); // seconds

// =============================================
// DATABASE CLASS
// =============================================

class Database {
    private static $instance = null;
    private $pdo;
    private $stmt;
    private $error;
    private $transactionLevel = 0;

    /**
     * Private constructor - Singleton pattern
     */
    private function __construct() {
        $this->connect();
    }

    /**
     * Get singleton instance
     */
    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    /**
     * Establish database connection
     */
    private function connect() {
        $dsn = "mysql:host=" . DB_HOST . ";port=" . DB_PORT . ";dbname=" . DB_NAME . ";charset=" . DB_CHARSET;
        
        $options = [
            PDO::ATTR_PERSISTENT => DB_PERSISTENT,
            PDO::ATTR_ERRMODE => DB_ERROR_MODE,
            PDO::ATTR_DEFAULT_FETCH_MODE => DB_DEFAULT_FETCH_MODE,
            PDO::ATTR_EMULATE_PREPARES => DB_EMULATE_PREPARES,
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES " . DB_CHARSET . " COLLATE " . DB_COLLATION,
            PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
            PDO::MYSQL_ATTR_FOUND_ROWS => true,
            PDO::ATTR_TIMEOUT => 30
        ];

        try {
            $this->pdo = new PDO($dsn, DB_USER, DB_PASS, $options);
            
            // Set timezone to match application
            $this->pdo->exec("SET time_zone = '+00:00'");
            $this->pdo->exec("SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'");
            
        } catch (PDOException $e) {
            $this->error = $e->getMessage();
            $this->logError('Connection Error', $e);
            throw new Exception("Database connection failed: " . $this->error);
        }
    }

    /**
     * Prepare statement with query
     */
    public function prepare($sql) {
        try {
            $this->stmt = $this->pdo->prepare($sql);
            return $this;
        } catch (PDOException $e) {
            $this->error = $e->getMessage();
            $this->logError('Prepare Error', $e, ['sql' => $sql]);
            throw $e;
        }
    }

    /**
     * Bind parameters
     */
    public function bind($param, $value, $type = null) {
        if (is_null($type)) {
            switch (true) {
                case is_int($value):
                    $type = PDO::PARAM_INT;
                    break;
                case is_bool($value):
                    $type = PDO::PARAM_BOOL;
                    break;
                case is_null($value):
                    $type = PDO::PARAM_NULL;
                    break;
                default:
                    $type = PDO::PARAM_STR;
            }
        }
        
        $this->stmt->bindValue($param, $value, $type);
        return $this;
    }

    /**
     * Bind multiple parameters
     */
    public function bindMultiple($params) {
        foreach ($params as $param => $value) {
            $this->bind($param, $value);
        }
        return $this;
    }

    /**
     * Execute the prepared statement
     */
    public function execute($params = []) {
        try {
            if (!empty($params)) {
                return $this->stmt->execute($params);
            }
            return $this->stmt->execute();
        } catch (PDOException $e) {
            $this->error = $e->getMessage();
            $this->logError('Execute Error', $e, [
                'sql' => $this->stmt->queryString,
                'params' => $params
            ]);
            throw $e;
        }
    }

    /**
     * Get result set as array of objects
     */
    public function resultSet($params = []) {
        $this->execute($params);
        return $this->stmt->fetchAll(PDO::FETCH_OBJ);
    }

    /**
     * Get single record as object
     */
    public function single($params = []) {
        $this->execute($params);
        return $this->stmt->fetch(PDO::FETCH_OBJ);
    }

    /**
     * Get single record as associative array
     */
    public function singleArray($params = []) {
        $this->execute($params);
        return $this->stmt->fetch(PDO::FETCH_ASSOC);
    }

    /**
     * Get result set as associative array
     */
    public function resultArray($params = []) {
        $this->execute($params);
        return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Get row count
     */
    public function rowCount() {
        return $this->stmt->rowCount();
    }

    /**
     * Get last inserted ID
     */
    public function lastInsertId() {
        return $this->pdo->lastInsertId();
    }

    /**
     * Get column count
     */
    public function columnCount() {
        return $this->stmt->columnCount();
    }

    /**
     * Get column metadata
     */
    public function getColumnMeta($column) {
        return $this->stmt->getColumnMeta($column);
    }

    /**
     * Begin transaction
     */
    public function beginTransaction() {
        if ($this->transactionLevel === 0) {
            $this->pdo->beginTransaction();
        }
        $this->transactionLevel++;
        return $this;
    }

    /**
     * Commit transaction
     */
    public function commit() {
        if ($this->transactionLevel === 1) {
            $this->pdo->commit();
        }
        $this->transactionLevel = max(0, $this->transactionLevel - 1);
        return $this;
    }

    /**
     * Rollback transaction
     */
    public function rollBack() {
        if ($this->transactionLevel === 1) {
            $this->pdo->rollBack();
        }
        $this->transactionLevel = max(0, $this->transactionLevel - 1);
        return $this;
    }

    /**
     * Check if in transaction
     */
    public function inTransaction() {
        return $this->pdo->inTransaction();
    }

    /**
     * Execute raw SQL query (for non-select operations)
     */
    public function query($sql, $params = []) {
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->rowCount();
        } catch (PDOException $e) {
            $this->error = $e->getMessage();
            $this->logError('Query Error', $e, ['sql' => $sql, 'params' => $params]);
            throw $e;
        }
    }

    /**
     * Select records with flexible conditions
     */
    public function select($table, $conditions = [], $columns = '*', $orderBy = '', $limit = '', $offset = 0) {
        $sql = "SELECT $columns FROM $table";
        $params = [];
        
        if (!empty($conditions)) {
            $whereClauses = [];
            foreach ($conditions as $column => $value) {
                if (is_array($value)) {
                    // Handle IN clause
                    $placeholders = implode(',', array_fill(0, count($value), '?'));
                    $whereClauses[] = "$column IN ($placeholders)";
                    $params = array_merge($params, $value);
                } else {
                    $whereClauses[] = "$column = ?";
                    $params[] = $value;
                }
            }
            $sql .= " WHERE " . implode(' AND ', $whereClauses);
        }
        
        if (!empty($orderBy)) {
            $sql .= " ORDER BY $orderBy";
        }
        
        if (!empty($limit)) {
            $sql .= " LIMIT $limit";
            if ($offset > 0) {
                $sql .= " OFFSET $offset";
            }
        }
        
        return $this->prepare($sql)->resultArray($params);
    }

    /**
     * Select single record
     */
    public function selectOne($table, $conditions = [], $columns = '*', $orderBy = '') {
        $result = $this->select($table, $conditions, $columns, $orderBy, 1);
        return $result[0] ?? null;
    }

    /**
     * Insert record
     */
    public function insert($table, $data) {
        $columns = implode(', ', array_keys($data));
        $placeholders = implode(', ', array_fill(0, count($data), '?'));
        $sql = "INSERT INTO $table ($columns) VALUES ($placeholders)";
        
        $this->prepare($sql)->execute(array_values($data));
        return $this->lastInsertId();
    }

    /**
     * Insert multiple records
     */
    public function insertMultiple($table, $dataArray) {
        if (empty($dataArray)) {
            return 0;
        }
        
        $columns = implode(', ', array_keys($dataArray[0]));
        $placeholders = '(' . implode(', ', array_fill(0, count($dataArray[0]), '?')) . ')';
        $allPlaceholders = implode(', ', array_fill(0, count($dataArray), $placeholders));
        
        $values = [];
        foreach ($dataArray as $data) {
            $values = array_merge($values, array_values($data));
        }
        
        $sql = "INSERT INTO $table ($columns) VALUES $allPlaceholders";
        return $this->prepare($sql)->execute($values);
    }

    /**
     * Update records
     */
    public function update($table, $data, $conditions = []) {
        $setClauses = [];
        $params = [];
        
        foreach ($data as $column => $value) {
            $setClauses[] = "$column = ?";
            $params[] = $value;
        }
        
        $sql = "UPDATE $table SET " . implode(', ', $setClauses);
        
        if (!empty($conditions)) {
            $whereClauses = [];
            foreach ($conditions as $column => $value) {
                $whereClauses[] = "$column = ?";
                $params[] = $value;
            }
            $sql .= " WHERE " . implode(' AND ', $whereClauses);
        }
        
        $this->prepare($sql)->execute($params);
        return $this->rowCount();
    }

    /**
     * Delete records
     */
    public function delete($table, $conditions = []) {
        $sql = "DELETE FROM $table";
        $params = [];
        
        if (!empty($conditions)) {
            $whereClauses = [];
            foreach ($conditions as $column => $value) {
                $whereClauses[] = "$column = ?";
                $params[] = $value;
            }
            $sql .= " WHERE " . implode(' AND ', $whereClauses);
        }
        
        $this->prepare($sql)->execute($params);
        return $this->rowCount();
    }

    /**
     * Check if record exists
     */
    public function exists($table, $conditions = []) {
        $result = $this->selectOne($table, $conditions, '1');
        return !empty($result);
    }

    /**
     * Count records
     */
    public function count($table, $conditions = []) {
        $result = $this->selectOne($table, $conditions, 'COUNT(*) as count');
        return $result['count'] ?? 0;
    }

    /**
     * Sum column values
     */
    public function sum($table, $column, $conditions = []) {
        $result = $this->selectOne($table, $conditions, "SUM($column) as total");
        return $result['total'] ?? 0;
    }

    /**
     * Get paginated results
     */
    public function paginate($table, $page = 1, $perPage = 20, $conditions = [], $columns = '*', $orderBy = 'id DESC') {
        $offset = ($page - 1) * $perPage;
        
        $data = $this->select($table, $conditions, $columns, $orderBy, $perPage, $offset);
        $total = $this->count($table, $conditions);
        $totalPages = ceil($total / $perPage);
        
        return [
            'data' => $data,
            'pagination' => [
                'current_page' => $page,
                'per_page' => $perPage,
                'total' => $total,
                'total_pages' => $totalPages,
                'has_next' => $page < $totalPages,
                'has_prev' => $page > 1
            ]
        ];
    }

    /**
     * Execute stored procedure
     */
    public function callProcedure($procedureName, $params = []) {
        $placeholders = implode(', ', array_fill(0, count($params), '?'));
        $sql = "CALL $procedureName($placeholders)";
        return $this->prepare($sql)->resultArray($params);
    }

    /**
     * Backup database
     */
    public function backup($backupPath) {
        $tables = $this->resultArray("SHOW TABLES");
        $backup = "";
        
        foreach ($tables as $table) {
            $tableName = current($table);
            
            // Drop table if exists
            $backup .= "DROP TABLE IF EXISTS `$tableName`;\n\n";
            
            // Create table
            $createTable = $this->singleArray("SHOW CREATE TABLE `$tableName`");
            $backup .= $createTable['Create Table'] . ";\n\n";
            
            // Insert data
            $rows = $this->resultArray("SELECT * FROM `$tableName`");
            if (!empty($rows)) {
                $columns = array_keys($rows[0]);
                $columnList = '`' . implode('`, `', $columns) . '`';
                
                foreach ($rows as $row) {
                    $values = array_map(function($value) {
                        if (is_null($value)) {
                            return 'NULL';
                        } else {
                            return "'" . addslashes($value) . "'";
                        }
                    }, array_values($row));
                    
                    $backup .= "INSERT INTO `$tableName` ($columnList) VALUES (" . implode(', ', $values) . ");\n";
                }
                $backup .= "\n";
            }
        }
        
        // Save to file
        file_put_contents($backupPath, $backup);
        return true;
    }

    /**
     * Restore database from backup
     */
    public function restore($backupPath) {
        if (!file_exists($backupPath)) {
            throw new Exception("Backup file not found: $backupPath");
        }
        
        $sql = file_get_contents($backupPath);
        $queries = explode(";\n", $sql);
        
        $this->beginTransaction();
        try {
            foreach ($queries as $query) {
                $query = trim($query);
                if (!empty($query)) {
                    $this->pdo->exec($query);
                }
            }
            $this->commit();
            return true;
        } catch (Exception $e) {
            $this->rollBack();
            throw $e;
        }
    }

    /**
     * Get database size
     */
    public function getDatabaseSize() {
        $sql = "SELECT 
                    table_schema as 'database',
                    SUM(data_length + index_length) as 'size_bytes',
                    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) as 'size_mb'
                FROM information_schema.TABLES 
                WHERE table_schema = ?
                GROUP BY table_schema";
        
        return $this->prepare($sql)->singleArray([DB_NAME]);
    }

    /**
     * Get table sizes
     */
    public function getTableSizes() {
        $sql = "SELECT 
                    table_name,
                    ROUND((data_length + index_length) / 1024 / 1024, 2) as 'size_mb',
                    table_rows
                FROM information_schema.TABLES 
                WHERE table_schema = ?
                ORDER BY (data_length + index_length) DESC";
        
        return $this->prepare($sql)->resultArray([DB_NAME]);
    }

    /**
     * Optimize all tables
     */
    public function optimizeTables() {
        $tables = $this->resultArray("SHOW TABLES");
        foreach ($tables as $table) {
            $tableName = current($table);
            $this->pdo->exec("OPTIMIZE TABLE `$tableName`");
        }
        return true;
    }
    /**
     * Repair all tables
     */
    public function repairTables() {
        $tables = $this->resultArray("SHOW TABLES");
        foreach ($tables as $table) {
            $tableName = current($table);
            $this->pdo->exec("REPAIR TABLE `$tableName`");
        }
        return true;
    }

    /**
     * Get query execution plan (EXPLAIN)
     */
    public function explain($sql, $params = []) {
        $explainSql = "EXPLAIN " . $sql;
        return $this->prepare($explainSql)->resultArray($params);
    }

    /**
     * Get slow queries (requires slow query log enabled)
     */
    public function getSlowQueries($limit = 10) {
        $sql = "SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT ?";
        return $this->prepare($sql)->resultArray([$limit]);
    }

    /**
     * Get connection status
     */
    public function getConnectionStatus() {
        $status = $this->singleArray("SHOW STATUS LIKE '%onn%'");
        $variables = $this->singleArray("SHOW VARIABLES LIKE '%onn%'");
        
        return [
            'status' => $status,
            'variables' => $variables,
            'connection_info' => [
                'host' => DB_HOST,
                'database' => DB_NAME,
                'charset' => DB_CHARSET,
                'connected' => $this->pdo !== null,
                'in_transaction' => $this->inTransaction(),
                'transaction_level' => $this->transactionLevel
            ]
        ];
    }

    /**
     * Get last error
     */
    public function getError() {
        return $this->error;
    }

    /**
     * Get PDO instance (for advanced operations)
     */
    public function getPdo() {
        return $this->pdo;
    }

    /**
     * Close connection
     */
    public function close() {
        $this->stmt = null;
        $this->pdo = null;
        self::$instance = null;
    }

    /**
     * Log database errors
     */
    private function logError($type, $exception, $context = []) {
        $logDir = __DIR__ . '/../logs/';
        if (!file_exists($logDir)) {
            mkdir($logDir, 0755, true);
        }
        
        $logFile = $logDir . 'database_errors.log';
        $timestamp = date('Y-m-d H:i:s');
        $message = $exception->getMessage();
        $code = $exception->getCode();
        $file = $exception->getFile();
        $line = $exception->getLine();
        $trace = $exception->getTraceAsString();
        
        $logEntry = "[$timestamp] [$type] [$code] $message\n";
        $logEntry .= "File: $file (Line: $line)\n";
        
        if (!empty($context)) {
            $logEntry .= "Context: " . json_encode($context, JSON_PRETTY_PRINT) . "\n";
        }
        
        $logEntry .= "Stack Trace:\n$trace\n";
        $logEntry .= str_repeat("-", 80) . "\n\n";
        
        error_log($logEntry, 3, $logFile);
        
        // Also log to PHP error log for development
        if (defined('ENVIRONMENT') && ENVIRONMENT === 'development') {
            error_log("Database Error [$type]: $message in $file on line $line");
        }
    }

    /**
     * Sanitize input (alternative to bind)
     */
    public function sanitize($input) {
        if (is_array($input)) {
            return array_map([$this, 'sanitize'], $input);
        }
        
        // Remove HTML tags
        $input = strip_tags($input);
        
        // Escape special characters
        $input = htmlspecialchars($input, ENT_QUOTES, 'UTF-8');
        
        return $input;
    }

    /**
     * Generate WHERE clause from conditions
     */
    public function buildWhereClause($conditions, $operator = 'AND') {
        if (empty($conditions)) {
            return ['clause' => '', 'params' => []];
        }
        
        $clauses = [];
        $params = [];
        
        foreach ($conditions as $column => $value) {
            if (is_array($value)) {
                // Handle operators: ['>', 100] or ['BETWEEN', [100, 200]]
                if (isset($value[0]) && is_string($value[0])) {
                    $operatorType = strtoupper($value[0]);
                    
                    switch ($operatorType) {
                        case '>':
                        case '<':
                        case '>=':
                        case '<=':
                        case '!=':
                        case '<>':
                            $clauses[] = "$column $operatorType ?";
                            $params[] = $value[1];
                            break;
                            
                        case 'LIKE':
                            $clauses[] = "$column LIKE ?";
                            $params[] = $value[1];
                            break;
                            
                        case 'BETWEEN':
                            $clauses[] = "$column BETWEEN ? AND ?";
                            $params = array_merge($params, $value[1]);
                            break;
                            
                        case 'IN':
                            $placeholders = implode(',', array_fill(0, count($value[1]), '?'));
                            $clauses[] = "$column IN ($placeholders)";
                            $params = array_merge($params, $value[1]);
                            break;
                            
                        case 'NOT IN':
                            $placeholders = implode(',', array_fill(0, count($value[1]), '?'));
                            $clauses[] = "$column NOT IN ($placeholders)";
                            $params = array_merge($params, $value[1]);
                            break;
                    }
                }
            } else if (is_null($value)) {
                $clauses[] = "$column IS NULL";
            } else {
                $clauses[] = "$column = ?";
                $params[] = $value;
            }
        }
        
        $clause = implode(" $operator ", $clauses);
        return ['clause' => $clause, 'params' => $params];
    }

    /**
     * Execute raw SQL and return results
     */
    public function raw($sql, $params = []) {
        return $this->prepare($sql)->resultArray($params);
    }

    /**
     * Execute raw SQL without returning results
     */
    public function exec($sql) {
        return $this->pdo->exec($sql);
    }

    /**
     * Get table columns
     */
    public function getTableColumns($table) {
        $sql = "SHOW COLUMNS FROM $table";
        return $this->prepare($sql)->resultArray();
    }

    /**
     * Check if table exists
     */
    public function tableExists($table) {
        $sql = "SHOW TABLES LIKE ?";
        $result = $this->prepare($sql)->singleArray([$table]);
        return !empty($result);
    }

    /**
     * Create table from array definition
     */
    public function createTable($tableName, $columns) {
        $columnDefinitions = [];
        
        foreach ($columns as $columnName => $definition) {
            $columnDefinitions[] = "`$columnName` " . $definition;
        }
        
        $sql = "CREATE TABLE IF NOT EXISTS `$tableName` (\n" .
               implode(",\n", $columnDefinitions) . "\n" .
               ") ENGINE=InnoDB DEFAULT CHARSET=" . DB_CHARSET . " COLLATE=" . DB_COLLATION;
        
        return $this->exec($sql);
    }

    /**
     * Drop table
     */
    public function dropTable($tableName) {
        $sql = "DROP TABLE IF EXISTS `$tableName`";
        return $this->exec($sql);
    }

    /**
     * Truncate table
     */
    public function truncateTable($tableName) {
        $sql = "TRUNCATE TABLE `$tableName`";
        return $this->exec($sql);
    }

    /**
     * Add column to table
     */
    public function addColumn($tableName, $columnName, $definition) {
        $sql = "ALTER TABLE `$tableName` ADD COLUMN `$columnName` $definition";
        return $this->exec($sql);
    }

    /**
     * Drop column from table
     */
    public function dropColumn($tableName, $columnName) {
        $sql = "ALTER TABLE `$tableName` DROP COLUMN `$columnName`";
        return $this->exec($sql);
    }

    /**
     * Modify column in table
     */
    public function modifyColumn($tableName, $columnName, $newDefinition) {
        $sql = "ALTER TABLE `$tableName` MODIFY COLUMN `$columnName` $newDefinition";
        return $this->exec($sql);
    }

    /**
     * Add index to table
     */
    public function addIndex($tableName, $indexName, $columns) {
        $columns = is_array($columns) ? implode(', ', $columns) : $columns;
        $sql = "ALTER TABLE `$tableName` ADD INDEX `$indexName` ($columns)";
        return $this->exec($sql);
    }

    /**
     * Add unique constraint
     */
    public function addUniqueConstraint($tableName, $constraintName, $columns) {
        $columns = is_array($columns) ? implode(', ', $columns) : $columns;
        $sql = "ALTER TABLE `$tableName` ADD UNIQUE `$constraintName` ($columns)";
        return $this->exec($sql);
    }

    /**
     * Add foreign key constraint
     */
    public function addForeignKey($tableName, $column, $referenceTable, $referenceColumn, $onDelete = 'RESTRICT', $onUpdate = 'RESTRICT') {
        $constraintName = "fk_{$tableName}_{$column}";
        $sql = "ALTER TABLE `$tableName` 
                ADD CONSTRAINT `$constraintName` 
                FOREIGN KEY (`$column`) 
                REFERENCES `$referenceTable` (`$referenceColumn`)
                ON DELETE $onDelete
                ON UPDATE $onUpdate";
        return $this->exec($sql);
    }

    /**
     * Get database version
     */
    public function getVersion() {
        $result = $this->singleArray("SELECT VERSION() as version");
        return $result['version'];
    }

    /**
     * Check if MySQL supports specific feature
     */
    public function supports($feature) {
        $version = $this->getVersion();
        $versionParts = explode('.', $version);
        $major = (int)$versionParts[0];
        $minor = (int)$versionParts[1];
        
        switch ($feature) {
            case 'json':
                return $major >= 5 && $minor >= 7;
            case 'window_functions':
                return $major >= 8;
            case 'common_table_expressions':
                return $major >= 8;
            case 'fulltext_indexing':
                return true; // Supported since MySQL 5.6
            case 'spatial_indexing':
                return true; // Supported since MySQL 5.7
            default:
                return false;
        }
    }

    /**
     * Escape identifier (table/column names)
     */
    public function quoteIdentifier($identifier) {
        return "`" . str_replace("`", "``", $identifier) . "`";
    }

    /**
     * Escape value (for raw SQL, use bind parameters instead)
     */
    public function quote($value) {
        return $this->pdo->quote($value);
    }

    /**
     * Get last query executed
     */
    public function getLastQuery() {
        return $this->stmt ? $this->stmt->queryString : null;
    }

    /**
     * Get query execution time
     */
    public function getQueryTime() {
        // This would need to be implemented with microtime tracking
        // For now, returns null - can be enhanced with profiling
        return null;
    }

    /**
     * Enable query logging
     */
    public function enableQueryLog() {
        $this->queryLog = [];
        $this->loggingEnabled = true;
    }

    /**
     * Disable query logging
     */
    public function disableQueryLog() {
        $this->loggingEnabled = false;
    }

    /**
     * Get query log
     */
    public function getQueryLog() {
        return $this->queryLog ?? [];
    }

    /**
     * Clear query log
     */
    public function clearQueryLog() {
        $this->queryLog = [];
    }

    /**
     * Destructor
     */
    public function __destruct() {
        $this->close();
    }
}

// =============================================
// HELPER FUNCTIONS
// =============================================

/**
 * Get database instance (global helper)
 */
function db() {
    return Database::getInstance();
}

/**
 * Execute transaction with callback
 */
function transaction(callable $callback) {
    $db = Database::getInstance();
    $db->beginTransaction();
    
    try {
        $result = $callback($db);
        $db->commit();
        return $result;
    } catch (Exception $e) {
        $db->rollBack();
        throw $e;
    }
}

/**
 * Execute query and return first result
 */
function db_first($sql, $params = []) {
    return Database::getInstance()->prepare($sql)->singleArray($params);
}

/**
 * Execute query and return all results
 */
function db_all($sql, $params = []) {
    return Database::getInstance()->prepare($sql)->resultArray($params);
}

/**
 * Execute query and return single value
 */
function db_value($sql, $params = []) {
    $result = Database::getInstance()->prepare($sql)->singleArray($params);
    return $result ? reset($result) : null;
}

/**
 * Insert record and return ID
 */
function db_insert($table, $data) {
    return Database::getInstance()->insert($table, $data);
}

/**
 * Update records
 */
function db_update($table, $data, $conditions = []) {
    return Database::getInstance()->update($table, $data, $conditions);
}

/**
 * Delete records
 */
function db_delete($table, $conditions = []) {
    return Database::getInstance()->delete($table, $conditions);
}

/**
 * Check if record exists
 */
function db_exists($table, $conditions = []) {
    return Database::getInstance()->exists($table, $conditions);
}

/**
 * Count records
 */
function db_count($table, $conditions = []) {
    return Database::getInstance()->count($table, $conditions);
}

// =============================================
// INITIALIZATION
// =============================================

// Create global database instance
$GLOBALS['db'] = Database::getInstance();

// Test connection on include (optional)
if (defined('TEST_DB_CONNECTION') && TEST_DB_CONNECTION === true) {
    try {
        $test = Database::getInstance()->singleArray("SELECT 1 as test");
        if ($test['test'] != 1) {
            throw new Exception("Database test query failed");
        }
    } catch (Exception $e) {
        die("Database connection test failed: " . $e->getMessage());
    }
}

// =============================================
// ENVIRONMENT SPECIFIC CONFIGURATION
// =============================================

// Detect environment
if (file_exists(__DIR__ . '/environment.php')) {
    require_once __DIR__ . '/environment.php';
} else {
    // Default to development
    define('ENVIRONMENT', 'development');
}

// Environment-specific overrides
if (ENVIRONMENT === 'production') {
    // Production settings
    ini_set('display_errors', 0);
    error_reporting(0);
    
    // You might want to use different credentials in production
    // These would be set in environment.php
} else {
    // Development settings
    ini_set('display_errors', 1);
    error_reporting(E_ALL);
}

// =============================================
// DATABASE CONSTANTS FOR APPLICATION USE
// =============================================

// Table names as constants for type safety
define('TABLE_USERS', 'users');
define('TABLE_BORROWERS', 'borrowers');
define('TABLE_LOANS', 'loans');
define('TABLE_LOAN_PRODUCTS', 'loan_products');
define('TABLE_LOAN_INSTALLMENTS', 'loan_installments');
define('TABLE_REPAYMENTS', 'repayments');
define('TABLE_TRANSACTIONS', 'transactions');
define('TABLE_BRANCHES', 'branches');
define('TABLE_ROLES', 'roles');
define('TABLE_PERMISSIONS', 'permissions');
define('TABLE_AUDIT_LOGS', 'audit_logs');
define('TABLE_NOTIFICATIONS', 'notifications');
define('TABLE_SETTINGS', 'settings');

// Common status constants
define('STATUS_ACTIVE', 'active');
define('STATUS_INACTIVE', 'inactive');
define('STATUS_PENDING', 'pending');
define('STATUS_APPROVED', 'approved');
define('STATUS_REJECTED', 'rejected');
define('STATUS_COMPLETED', 'completed');
define('STATUS_DEFAULTED', 'defaulted');

// Loan status flow
define('LOAN_STATUS_PENDING', 'pending');
define('LOAN_STATUS_APPROVED', 'approved');
define('LOAN_STATUS_DISBURSED', 'disbursed');
define('LOAN_STATUS_ACTIVE', 'active');
define('LOAN_STATUS_COMPLETED', 'completed');
define('LOAN_STATUS_DEFAULTED', 'defaulted');
define('LOAN_STATUS_WRITTEN_OFF', 'written_off');

// Borrower status
define('BORROWER_STATUS_ACTIVE', 'active');
define('BORROWER_STATUS_INACTIVE', 'inactive');
define('BORROWER_STATUS_BLACKLISTED', 'blacklisted');
define('BORROWER_STATUS_PENDING', 'pending');

// User roles
define('ROLE_ADMIN', 1);
define('ROLE_LOAN_OFFICER', 2);
define('ROLE_BRANCH_MANAGER', 3);
define('ROLE_BORROWER', 4);

// Payment methods
define('PAYMENT_CASH', 1);
define('PAYMENT_BANK_TRANSFER', 2);
define('PAYMENT_MOBILE_MONEY', 3);
define('PAYMENT_CHEQUE', 4);
define('PAYMENT_CARD', 5);

// Interest types
define('INTEREST_FLAT', 'flat');
define('INTEREST_REDUCING', 'reducing');
define('INTEREST_COMPOUND', 'compound');

// Term units
define('TERM_DAYS', 'days');
define('TERM_WEEKS', 'weeks');
define('TERM_MONTHS', 'months');
define('TERM_YEARS', 'years');

// Risk categories
define('RISK_LOW', 'low');
define('RISK_MEDIUM', 'medium');
define('RISK_HIGH', 'high');

// Gender options
define('GENDER_MALE', 'male');
define('GENDER_FEMALE', 'female');
define('GENDER_OTHER', 'other');

// Marital status
define('MARITAL_SINGLE', 'single');
define('MARITAL_MARRIED', 'married');
define('MARITAL_DIVORCED', 'divorced');
define('MARITAL_WIDOWED', 'widowed');

// Employment status
define('EMPLOYMENT_EMPLOYED', 'employed');
define('EMPLOYMENT_SELF_EMPLOYED', 'self_employed');
define('EMPLOYMENT_UNEMPLOYED', 'unemployed');
define('EMPLOYMENT_RETIRED', 'retired');

// Document types
define('DOCUMENT_NATIONAL_ID', 'national_id');
define('DOCUMENT_PASSPORT', 'passport');
define('DOCUMENT_UTILITY_BILL', 'utility_bill');
define('DOCUMENT_BANK_STATEMENT', 'bank_statement');
define('DOCUMENT_PAY_SLIP', 'pay_slip');
define('DOCUMENT_TAX_RETURN', 'tax_return');

// Loan product types
define('LOAN_PRODUCT_AGRICULTURE', 'agriculture');
define('LOAN_PRODUCT_LIVESTOCK', 'livestock');
define('LOAN_PRODUCT_EQUIPMENT', 'equipment');
define('LOAN_PRODUCT_EMERGENCY', 'emergency');
define('LOAN_PRODUCT_EDUCATION', 'education');
define('LOAN_PRODUCT_BUSINESS', 'business');

// Collateral types
define('COLLATERAL_LAND', 'land');
define('COLLATERAL_VEHICLE', 'vehicle');
define('COLLATERAL_EQUIPMENT', 'equipment');
define('COLLATERAL_LIVESTOCK', 'livestock');
define('COLLATERAL_BUILDING', 'building');
define('COLLATERAL_JEWELRY', 'jewelry');

// Notification types
define('NOTIFICATION_INFO', 'info');
define('NOTIFICATION_SUCCESS', 'success');
define('NOTIFICATION_WARNING', 'warning');
define('NOTIFICATION_ERROR', 'error');
define('NOTIFICATION_SYSTEM', 'system');

// Transaction types
define('TRANSACTION_DISBURSEMENT', 'disbursement');
define('TRANSACTION_REPAYMENT', 'repayment');
define('TRANSACTION_FEE', 'fee');
define('TRANSACTION_PENALTY', 'penalty');
define('TRANSACTION_INTEREST', 'interest');
define('TRANSACTION_ADJUSTMENT', 'adjustment');
define('TRANSACTION_REFUND', 'refund');

// =============================================
// DATABASE MIGRATION SUPPORT
// =============================================

class DatabaseMigration {
    private $db;
    private $migrationsTable = 'migrations';
    
    public function __construct() {
        $this->db = Database::getInstance();
        $this->createMigrationsTable();
    }
    
    private function createMigrationsTable() {
        $sql = "CREATE TABLE IF NOT EXISTS {$this->migrationsTable} (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            migration VARCHAR(255) NOT NULL,
            batch INT NOT NULL,
            executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uk_migration (migration)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
        
        $this->db->exec($sql);
    }
    
    public function runMigrations($migrationsPath) {
        $executedMigrations = $this->getExecutedMigrations();
        $migrationFiles = glob($migrationsPath . '/*.php');
        
        $batch = $this->getNextBatchNumber();
        $newMigrations = [];
        
        foreach ($migrationFiles as $file) {
            $migrationName = basename($file, '.php');
            
            if (!in_array($migrationName, $executedMigrations)) {
                require_once $file;
                
                $className = $this->getClassNameFromFileName($migrationName);
                
                if (class_exists($className)) {
                    $migration = new $className($this->db);
                    
                    try {
                        $this->db->beginTransaction();
                        $migration->up();
                        $this->recordMigration($migrationName, $batch);
                        $this->db->commit();
                        
                        $newMigrations[] = $migrationName;
                        echo "Migrated: $migrationName\n";
                    } catch (Exception $e) {
                        $this->db->rollBack();
                        throw new Exception("Migration failed: $migrationName - " . $e->getMessage());
                    }
                }
            }
        }
        
        return $newMigrations;
    }
    
    public function rollbackMigrations($batch = null) {
        $query = "SELECT migration FROM {$this->migrationsTable}";
        $params = [];
        
        if ($batch !== null) {
            $query .= " WHERE batch = ?";
            $params[] = $batch;
        } else {
            $query .= " WHERE batch = (SELECT MAX(batch) FROM {$this->migrationsTable})";
        }
        
        $query .= " ORDER BY id DESC";
        
        $migrations = $this->db->prepare($query)->resultArray($params);
        
        foreach ($migrations as $migration) {
            $migrationName = $migration['migration'];
            $file = __DIR__ . "/../migrations/$migrationName.php";
            
            if (file_exists($file)) {
                require_once $file;
                $className = $this->getClassNameFromFileName($migrationName);
                
                if (class_exists($className)) {
                    $migration = new $className($this->db);
                    
                    try {
                        $this->db->beginTransaction();
                        $migration->down();
                        $this->removeMigration($migrationName);
                        $this->db->commit();
                        
                        echo "Rolled back: $migrationName\n";
                    } catch (Exception $e) {
                        $this->db->rollBack();
                        throw new Exception("Rollback failed: $migrationName - " . $e->getMessage());
                    }
                }
            }
        }
    }
    
    private function getExecutedMigrations() {
        $sql = "SELECT migration FROM {$this->migrationsTable} ORDER BY id";
        $results = $this->db->prepare($sql)->resultArray();
        
        return array_column($results, 'migration');
    }
    
    private function getNextBatchNumber() {
        $sql = "SELECT MAX(batch) as max_batch FROM {$this->migrationsTable}";
        $result = $this->db->prepare($sql)->singleArray();
        
        return ($result['max_batch'] ?? 0) + 1;
    }
    
    private function recordMigration($migrationName, $batch) {
        $sql = "INSERT INTO {$this->migrationsTable} (migration, batch) VALUES (?, ?)";
        $this->db->prepare($sql)->execute([$migrationName, $batch]);
    }
    
    private function removeMigration($migrationName) {
        $sql = "DELETE FROM {$this->migrationsTable} WHERE migration = ?";
        $this->db->prepare($sql)->execute([$migrationName]);
    }
    
    private function getClassNameFromFileName($fileName) {
        $parts = explode('_', $fileName);
        $className = '';
        
        foreach ($parts as $part) {
            $className .= ucfirst($part);
        }
        
        return $className;
    }
    
    public function getMigrationStatus() {
        $sql = "SELECT 
                    migration,
                    batch,
                    executed_at,
                    DATE_FORMAT(executed_at, '%Y-%m-%d %H:%i:%s') as formatted_date
                FROM {$this->migrationsTable}
                ORDER BY batch DESC, id DESC";
        
        return $this->db->prepare($sql)->resultArray();
    }
}

// =============================================
// DATABASE SEEDER SUPPORT
// =============================================

class DatabaseSeeder {
    private $db;
    
    public function __construct() {
        $this->db = Database::getInstance();
    }
    
    public function seed($seederClass) {
        if (class_exists($seederClass)) {
            $seeder = new $seederClass($this->db);
            $seeder->run();
            echo "Seeded: $seederClass\n";
        } else {
            throw new Exception("Seeder class not found: $seederClass");
        }
    }
    
    public function seedAll($seedersPath) {
        $seederFiles = glob($seedersPath . '/*.php');
        
        foreach ($seederFiles as $file) {
            $seederName = basename($file, '.php');
            require_once $file;
            
            $className = $this->getClassNameFromFileName($seederName);
            $this->seed($className);
        }
    }
    
    private function getClassNameFromFileName($fileName) {
        $parts = explode('_', $fileName);
        $className = '';
        
        foreach ($parts as $part) {
            $className .= ucfirst($part);
        }
        
        return $className . 'Seeder';
    }
}

// =============================================
// QUERY BUILDER (OPTIONAL)
// =============================================

class QueryBuilder {
    private $db;
    private $table;
    private $select = '*';
    private $where = [];
    private $orderBy = '';
    private $limit = '';
    private $offset = 0;
    private $joins = [];
    private $groupBy = '';
    private $having = '';
    private $params = [];
    
    public function __construct($table) {
        $this->db = Database::getInstance();
        $this->table = $table;
    }
    
    public function select($columns) {
        $this->select = is_array($columns) ? implode(', ', $columns) : $columns;
        return $this;
    }
    
    public function where($column, $operator, $value = null) {
        if ($value === null) {
            $value = $operator;
            $operator = '=';
        }
        
        $this->where[] = [
            'column' => $column,
            'operator' => $operator,
            'value' => $value,
            'connector' => 'AND'
        ];
        
        $this->params[] = $value;
        return $this;
    }
    
    public function orWhere($column, $operator, $value = null) {
        if ($value === null) {
            $value = $operator;
            $operator = '=';
        }
        
        $this->where[] = [
            'column' => $column,
            'operator' => $operator,
            'value' => $value,
            'connector' => 'OR'
        ];
        
        $this->params[] = $value;
        return $this;
    }
    
    public function whereIn($column, $values) {
        $placeholders = implode(',', array_fill(0, count($values), '?'));
        $this->where[] = [
            'column' => $column,
            'operator' => 'IN',
            'value' => "($placeholders)",
            'connector' => 'AND'
        ];
        
        $this->params = array_merge($this->params, $values);
        return $this;
    }
    
    public function whereNull($column) {
        $this->where[] = [
            'column' => $column,
            'operator' => 'IS',
            'value' => 'NULL',
            'connector' => 'AND'
        ];
        return $this;
    }
    
    public function whereNotNull($column) {
        $this->where[] = [
            'column' => $column,
            'operator' => 'IS NOT',
            'value' => 'NULL',
            'connector' => 'AND'
        ];
        return $this;
    }
    
    public function join($table, $first, $operator, $second, $type = 'INNER') {
        $this->joins[] = [
            'table' => $table,
            'first' => $first,
            'operator' => $operator,
            'second' => $second,
            'type' => $type
        ];
        return $this;
    }
    
    public function leftJoin($table, $first, $operator, $second) {
        return $this->join($table, $first, $operator, $second, 'LEFT');
    }
    
    public function rightJoin($table, $first, $operator, $second) {
        return $this->join($table, $first, $operator, $second, 'RIGHT');
    }
    
    public function orderBy($column, $direction = 'ASC') {
        $this->orderBy = "$column $direction";
        return $this;
    }
    
    public function groupBy($column) {
        $this->groupBy = $column;
        return $this;
    }
    
    public function having($column, $operator, $value) {
        $this->having = "$column $operator ?";
        $this->params[] = $value;
        return $this;
    }
    
    public function limit($limit, $offset = 0) {
        $this->limit = $limit;
        $this->offset = $offset;
        return $this;
    }
    
    public function get() {
        $sql = $this->buildSelectQuery();
        return $this->db->prepare($sql)->resultArray($this->params);
    }
    
    public function first() {
        $sql = $this->buildSelectQuery() . " LIMIT 1";
        return $this->db->prepare($sql)->singleArray($this->params);
    }
    
    public function count() {
        $this->select = 'COUNT(*) as count';
        $result = $this->first();
        return $result['count'] ?? 0;
    }
    
    public function sum($column) {
        $this->select = "SUM($column) as total";
        $result = $this->first();
        return $result['total'] ?? 0;
    }
    
    public function avg($column) {
        $this->select = "AVG($column) as average";
        $result = $this->first();
        return $result['average'] ?? 0;
    }
    
    public function max($column) {
        $this->select = "MAX($column) as maximum";
        $result = $this->first();
        return $result['maximum'] ?? 0;
    }
    
    public function min($column) {
        $this->select = "MIN($column) as minimum";
        $result = $this->first();
        return $result['minimum'] ?? 0;
    }
    
    public function paginate($perPage = 15, $page = 1) {
        $offset = ($page - 1) * $perPage;
        $this->limit($perPage, $offset);
        
        $data = $this->get();
        
        // Get total count without limit
        $totalQuery = clone $this;
        $total = $totalQuery->count();
        
        return [
            'data' => $data,
            'pagination' => [
                'total' => $total,
                'per_page' => $perPage,
                'current_page' => $page,
                'total_pages' => ceil($total / $perPage),
                'has_more' => $page < ceil($total / $perPage)
            ]
        ];
    }
    
    private function buildSelectQuery() {
        $sql = "SELECT {$this->select} FROM {$this->table}";
        
        // Build joins
        foreach ($this->joins as $join) {
            $sql .= " {$join['type']} JOIN {$join['table']} ON {$join['first']} {$join['operator']} {$join['second']}";
        }
        
        // Build where clause
        if (!empty($this->where)) {
            $sql .= " WHERE ";
            $whereParts = [];
            
            foreach ($this->where as $index => $condition) {
                if ($index > 0) {
                    $whereParts[] = $condition['connector'];
                }
                
                if ($condition['operator'] === 'IN') {
                    $whereParts[] = "{$condition['column']} {$condition['operator']} {$condition['value']}";
                } else if (in_array($condition['operator'], ['IS', 'IS NOT'])) {
                    $whereParts[] = "{$condition['column']} {$condition['operator']} {$condition['value']}";
                } else {
                    $whereParts[] = "{$condition['column']} {$condition['operator']} ?";
                }
            }
            
            $sql .= implode(' ', $whereParts);
        }
        
        // Build group by
        if (!empty($this->groupBy)) {
            $sql .= " GROUP BY {$this->groupBy}";
        }
        
        // Build having
        if (!empty($this->having)) {
            $sql .= " HAVING {$this->having}";
        }
        
        // Build order by
        if (!empty($this->orderBy)) {
            $sql .= " ORDER BY {$this->orderBy}";
        }
        
        // Build limit
        if (!empty($this->limit)) {
            $sql .= " LIMIT {$this->limit}";
            if ($this->offset > 0) {
                $sql .= " OFFSET {$this->offset}";
            }
        }
        
        return $sql;
    }
    
    public function insert($data) {
        return $this->db->insert($this->table, $data);
    }

        public function update($data) {
        if (empty($this->where)) {
            throw new Exception("Update requires WHERE conditions");
        }
        
        return $this->db->update($this->table, $data, $this->buildWhereConditions());
    }
    
    public function delete() {
        if (empty($this->where)) {
            throw new Exception("Delete requires WHERE conditions");
        }
        
        return $this->db->delete($this->table, $this->buildWhereConditions());
    }
    
    private function buildWhereConditions() {
        $conditions = [];
        foreach ($this->where as $condition) {
            if (!in_array($condition['operator'], ['IN', 'IS', 'IS NOT'])) {
                $conditions[$condition['column']] = $condition['value'];
            }
        }
        return $conditions;
    }
    
    public function getSql() {
        return $this->buildSelectQuery();
    }
    
    public function getParams() {
        return $this->params;
    }
}

// Helper function for query builder
function table($tableName) {
    return new QueryBuilder($tableName);
}

// =============================================
// DATABASE VALIDATION RULES
// =============================================

class DatabaseValidation {
    private $db;
    
    public function __construct() {
        $this->db = Database::getInstance();
    }
    
    /**
     * Check if value is unique in table
     */
    public function unique($table, $column, $value, $ignoreId = null) {
        $query = "SELECT COUNT(*) as count FROM $table WHERE $column = ?";
        $params = [$value];
        
        if ($ignoreId !== null) {
            $query .= " AND id != ?";
            $params[] = $ignoreId;
        }
        
        $result = $this->db->prepare($query)->singleArray($params);
        return $result['count'] == 0;
    }
    
    /**
     * Check if value exists in table
     */
    public function exists($table, $column, $value) {
        $query = "SELECT COUNT(*) as count FROM $table WHERE $column = ?";
        $result = $this->db->prepare($query)->singleArray([$value]);
        return $result['count'] > 0;
    }
    
    /**
     * Validate email format and uniqueness
     */
    public function validEmail($email, $ignoreId = null) {
        if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
            return false;
        }
        
        return $this->unique('users', 'email', $email, $ignoreId);
    }
    
    /**
     * Validate phone number format
     */
    public function validPhone($phone) {
        // Remove all non-digit characters
        $cleanPhone = preg_replace('/[^0-9]/', '', $phone);
        
        // Check length (adjust for your country)
        return strlen($cleanPhone) >= 10 && strlen($cleanPhone) <= 15;
    }
    
    /**
     * Validate national ID format
     */
    public function validNationalId($id) {
        // Basic validation - adjust for your country's ID format
        return preg_match('/^[A-Z0-9]{6,20}$/', $id);
    }
    
    /**
     * Validate date is not in the past
     */
    public function futureDate($date) {
        $inputDate = new DateTime($date);
        $today = new DateTime();
        return $inputDate >= $today;
    }
    
    /**
     * Validate date is not too far in the future
     */
    public function reasonableFutureDate($date, $maxYears = 10) {
        $inputDate = new DateTime($date);
        $today = new DateTime();
        $maxDate = (new DateTime())->modify("+$maxYears years");
        
        return $inputDate >= $today && $inputDate <= $maxDate;
    }
    
    /**
     * Validate age is within range
     */
    public function validAge($dateOfBirth, $minAge = 18, $maxAge = 100) {
        $birthDate = new DateTime($dateOfBirth);
        $today = new DateTime();
        $age = $today->diff($birthDate)->y;
        
        return $age >= $minAge && $age <= $maxAge;
    }
    
    /**
     * Validate loan amount is within product limits
     */
    public function validLoanAmount($productId, $amount) {
        $query = "SELECT min_amount, max_amount FROM loan_products WHERE id = ?";
        $product = $this->db->prepare($query)->singleArray([$productId]);
        
        if (!$product) {
            return false;
        }
        
        return $amount >= $product['min_amount'] && $amount <= $product['max_amount'];
    }
    
    /**
     * Validate loan term is within product limits
     */
    public function validLoanTerm($productId, $term) {
        $query = "SELECT min_term, max_term FROM loan_products WHERE id = ?";
        $product = $this->db->prepare($query)->singleArray([$productId]);
        
        if (!$product) {
            return false;
        }
        
        return $term >= $product['min_term'] && $term <= $product['max_term'];
    }
    
    /**
     * Check if borrower has active loans
     */
    public function hasActiveLoans($borrowerId) {
        $query = "SELECT COUNT(*) as count FROM loans 
                  WHERE borrower_id = ? AND status IN ('active', 'disbursed')";
        $result = $this->db->prepare($query)->singleArray([$borrowerId]);
        return $result['count'] > 0;
    }
    
    /**
     * Check if borrower is blacklisted
     */
    public function isBlacklisted($borrowerId) {
        $query = "SELECT status FROM borrowers WHERE id = ?";
        $borrower = $this->db->prepare($query)->singleArray([$borrowerId]);
        return $borrower && $borrower['status'] === 'blacklisted';
    }
    
    /**
     * Validate credit score is acceptable
     */
    public function acceptableCreditScore($borrowerId, $minScore = 300) {
        $query = "SELECT credit_score FROM borrowers WHERE id = ?";
        $borrower = $this->db->prepare($query)->singleArray([$borrowerId]);
        
        if (!$borrower) {
            return false;
        }
        
        return $borrower['credit_score'] >= $minScore;
    }
}

// =============================================
// DATABASE CACHE SUPPORT
// =============================================

class DatabaseCache {
    private $cache = [];
    private $ttl = 300; // 5 minutes default
    private $enabled = true;
    
    public function __construct($enabled = true) {
        $this->enabled = $enabled;
    }
    
    public function enable() {
        $this->enabled = true;
    }
    
    public function disable() {
        $this->enabled = false;
        $this->clear();
    }
    
    public function setTtl($seconds) {
        $this->ttl = $seconds;
    }
    
    public function get($key) {
        if (!$this->enabled) {
            return null;
        }
        
        if (isset($this->cache[$key])) {
            $entry = $this->cache[$key];
            
            if (time() - $entry['timestamp'] < $this->ttl) {
                return $entry['data'];
            } else {
                unset($this->cache[$key]);
            }
        }
        
        return null;
    }
    
    public function set($key, $data) {
        if (!$this->enabled) {
            return;
        }
        
        $this->cache[$key] = [
            'data' => $data,
            'timestamp' => time()
        ];
    }
    
    public function delete($key) {
        unset($this->cache[$key]);
    }
    
    public function clear() {
        $this->cache = [];
    }
    
    public function remember($key, $callback, $ttl = null) {
        $cached = $this->get($key);
        
        if ($cached !== null) {
            return $cached;
        }
        
        $data = $callback();
        
        if ($data !== null) {
            $this->set($key, $data, $ttl);
        }
        
        return $data;
    }
    
    public function getStats() {
        return [
            'enabled' => $this->enabled,
            'ttl' => $this->ttl,
            'entries' => count($this->cache),
            'memory_usage' => memory_get_usage(true)
        ];
    }
}

// =============================================
// DATABASE EVENT LISTENERS
// =============================================

class DatabaseEvent {
    private static $listeners = [];
    
    public static function listen($event, $callback) {
        if (!isset(self::$listeners[$event])) {
            self::$listeners[$event] = [];
        }
        
        self::$listeners[$event][] = $callback;
    }
    
    public static function trigger($event, $data = null) {
        if (isset(self::$listeners[$event])) {
            foreach (self::$listeners[$event] as $callback) {
                call_user_func($callback, $data);
            }
        }
    }
    
    public static function getListeners() {
        return self::$listeners;
    }
}

// Register default events
DatabaseEvent::listen('query.executed', function($data) {
    // Log query execution
    if (defined('LOG_QUERIES') && LOG_QUERIES) {
        $logDir = __DIR__ . '/../logs/';
        if (!file_exists($logDir)) {
            mkdir($logDir, 0755, true);
        }
        
        $logFile = $logDir . 'queries.log';
        $timestamp = date('Y-m-d H:i:s');
        $logEntry = "[$timestamp] Query: {$data['sql']}\n";
        
        if (!empty($data['params'])) {
            $logEntry .= "Params: " . json_encode($data['params']) . "\n";
        }
        
        $logEntry .= "Execution time: {$data['time']}ms\n";
        $logEntry .= str_repeat("-", 80) . "\n";
        
        error_log($logEntry, 3, $logFile);
    }
});

DatabaseEvent::listen('connection.established', function() {
    // Log successful connection
    if (defined('LOG_CONNECTIONS') && LOG_CONNECTIONS) {
        $logDir = __DIR__ . '/../logs/';
        if (!file_exists($logDir)) {
            mkdir($logDir, 0755, true);
        }
        
        $logFile = $logDir . 'connections.log';
        $timestamp = date('Y-m-d H:i:s');
        $logEntry = "[$timestamp] Database connection established to " . DB_HOST . "\n";
        error_log($logEntry, 3, $logFile);
    }
});

DatabaseEvent::listen('transaction.began', function() {
    // Log transaction start
});

DatabaseEvent::listen('transaction.committed', function() {
    // Log transaction commit
});

DatabaseEvent::listen('transaction.rolledback', function() {
    // Log transaction rollback
});

// =============================================
// DATABASE HEALTH CHECK
// =============================================

class DatabaseHealth {
    private $db;
    
    public function __construct() {
        $this->db = Database::getInstance();
    }
    
    public function check() {
        $checks = [];
        
        // Check connection
        try {
            $this->db->singleArray("SELECT 1 as test");
            $checks['connection'] = ['status' => 'healthy', 'message' => 'Connected successfully'];
        } catch (Exception $e) {
            $checks['connection'] = ['status' => 'unhealthy', 'message' => $e->getMessage()];
        }
        
        // Check database size
        try {
            $size = $this->db->getDatabaseSize();
            $checks['database_size'] = [
                'status' => 'healthy',
                'message' => "Database size: {$size['size_mb']} MB",
                'data' => $size
            ];
        } catch (Exception $e) {
            $checks['database_size'] = ['status' => 'unhealthy', 'message' => $e->getMessage()];
        }
        
        // Check table status
        try {
            $tables = $this->db->getTableSizes();
            $checks['tables'] = [
                'status' => 'healthy',
                'message' => count($tables) . ' tables found',
                'data' => $tables
            ];
        } catch (Exception $e) {
            $checks['tables'] = ['status' => 'unhealthy', 'message' => $e->getMessage()];
        }
        
        // Check for slow queries
        try {
            $slowQueries = $this->db->getSlowQueries(5);
            $checks['slow_queries'] = [
                'status' => count($slowQueries) > 0 ? 'warning' : 'healthy',
                'message' => count($slowQueries) . ' slow queries found',
                'data' => $slowQueries
            ];
        } catch (Exception $e) {
            $checks['slow_queries'] = ['status' => 'unhealthy', 'message' => $e->getMessage()];
        }
        
        // Check connection pool
        try {
            $status = $this->db->getConnectionStatus();
            $checks['connection_pool'] = [
                'status' => 'healthy',
                'message' => 'Connection pool active',
                'data' => $status
            ];
        } catch (Exception $e) {
            $checks['connection_pool'] = ['status' => 'unhealthy', 'message' => $e->getMessage()];
        }
        
        // Calculate overall status
        $overallStatus = 'healthy';
        foreach ($checks as $check) {
            if ($check['status'] === 'unhealthy') {
                $overallStatus = 'unhealthy';
                break;
            } elseif ($check['status'] === 'warning') {
                $overallStatus = 'warning';
            }
        }
        
        return [
            'overall' => $overallStatus,
            'timestamp' => date('Y-m-d H:i:s'),
            'checks' => $checks
        ];
    }
    
    public function getRecommendations() {
        $recommendations = [];
        
        // Check if tables need optimization
        try {
            $tables = $this->db->getTableSizes();
            $largeTables = array_filter($tables, function($table) {
                return $table['size_mb'] > 100; // Tables larger than 100MB
            });
            
            if (count($largeTables) > 0) {
                $recommendations[] = [
                    'type' => 'optimization',
                    'message' => 'Consider optimizing large tables',
                    'tables' => array_column($largeTables, 'table_name')
                ];
            }
        } catch (Exception $e) {
            // Ignore errors in recommendations
        }
        
        // Check if indexes are needed
        try {
            // This would require analyzing query patterns
            // For now, just a placeholder
        } catch (Exception $e) {
            // Ignore errors
        }
        
        return $recommendations;
    }
}

// =============================================
// FINAL INITIALIZATION
// =============================================

// Create global instances
$GLOBALS['db_cache'] = new DatabaseCache(true);
$GLOBALS['db_validation'] = new DatabaseValidation();
$GLOBALS['db_health'] = new DatabaseHealth();

// Test connection on first load (development only)
if (defined('ENVIRONMENT') && ENVIRONMENT === 'development') {
    try {
        $test = db()->singleArray("SELECT 1 as test");
        if ($test['test'] == 1) {
            DatabaseEvent::trigger('connection.established');
        }
    } catch (Exception $e) {
        // Don't die in production, just log
        error_log("Database connection test failed: " . $e->getMessage());
    }
}

// =============================================
// COMPATIBILITY CHECK FOR XAMPP
// =============================================

/**
 * Check XAMPP compatibility
 */
function checkXamppCompatibility() {
    $issues = [];
    
    // Check PHP version
    if (version_compare(PHP_VERSION, '7.4.0', '<')) {
        $issues[] = "PHP 7.4 or higher required. Current: " . PHP_VERSION;
    }
    
    // Check MySQL version
    try {
        $db = Database::getInstance();
        $version = $db->getVersion();
        if (version_compare($version, '5.7.0', '<')) {
            $issues[] = "MySQL 5.7 or higher recommended. Current: $version";
        }
    } catch (Exception $e) {
        $issues[] = "Cannot check MySQL version: " . $e->getMessage();
    }
    
    // Check required extensions
    $requiredExtensions = ['pdo_mysql', 'mysqli', 'json', 'mbstring'];
    foreach ($requiredExtensions as $ext) {
        if (!extension_loaded($ext)) {
            $issues[] = "PHP extension required: $ext";
        }
    }
    
    // Check directory permissions
    $requiredDirs = [
        __DIR__ . '/../uploads' => 'writable',
        __DIR__ . '/../logs' => 'writable',
        __DIR__ . '/../sessions' => 'writable'
    ];
    
    foreach ($requiredDirs as $dir => $requirement) {
        if (!file_exists($dir)) {
            @mkdir($dir, 0755, true);
        }
        
        if ($requirement === 'writable' && !is_writable($dir)) {
            $issues[] = "Directory must be writable: $dir";
        }
    }
    
    return [
        'compatible' => empty($issues),
        'issues' => $issues,
        'environment' => [
            'php_version' => PHP_VERSION,
            'server_software' => $_SERVER['SERVER_SOFTWARE'] ?? 'Unknown',
                        'xampp_detected' => strpos(strtolower($_SERVER['SERVER_SOFTWARE'] ?? ''), 'xampp') !== false,
            'operating_system' => PHP_OS,
            'memory_limit' => ini_get('memory_limit'),
            'upload_max_filesize' => ini_get('upload_max_filesize'),
            'post_max_size' => ini_get('post_max_size')
        ]
    ];
}

// =============================================
// ERROR HANDLING AND DEBUGGING
// =============================================

/**
 * Custom database error handler
 */
function handleDatabaseError($exception, $context = []) {
    $logDir = __DIR__ . '/../logs/';
    if (!file_exists($logDir)) {
        mkdir($logDir, 0755, true);
    }
    
    $logFile = $logDir . 'database_errors.log';
    $timestamp = date('Y-m-d H:i:s');
    
    $logEntry = "[$timestamp] Database Error\n";
    $logEntry .= "Message: " . $exception->getMessage() . "\n";
    $logEntry .= "Code: " . $exception->getCode() . "\n";
    $logEntry .= "File: " . $exception->getFile() . " (Line: " . $exception->getLine() . ")\n";
    
    if (!empty($context)) {
        $logEntry .= "Context: " . json_encode($context, JSON_PRETTY_PRINT) . "\n";
    }
    
    $logEntry .= "Stack Trace:\n" . $exception->getTraceAsString() . "\n";
    $logEntry .= str_repeat("=", 80) . "\n\n";
    
    error_log($logEntry, 3, $logFile);
    
    // In development, show error details
    if (defined('ENVIRONMENT') && ENVIRONMENT === 'development') {
        echo "<div style='background: #f8d7da; color: #721c24; padding: 15px; margin: 10px; border: 1px solid #f5c6cb; border-radius: 5px;'>";
        echo "<h3>Database Error</h3>";
        echo "<p><strong>Message:</strong> " . htmlspecialchars($exception->getMessage()) . "</p>";
        echo "<p><strong>File:</strong> " . $exception->getFile() . " (Line: " . $exception->getLine() . ")</p>";
        
        if (!empty($context)) {
            echo "<p><strong>Context:</strong></p>";
            echo "<pre>" . htmlspecialchars(json_encode($context, JSON_PRETTY_PRINT)) . "</pre>";
        }
        
        echo "<p><strong>Stack Trace:</strong></p>";
        echo "<pre>" . htmlspecialchars($exception->getTraceAsString()) . "</pre>";
        echo "</div>";
    } else {
        // In production, log but don't show details
        error_log("Database error occurred. Check error log for details.");
    }
}

/**
 * Set custom error handler for database exceptions
 */
set_exception_handler(function($exception) {
    if ($exception instanceof PDOException) {
        handleDatabaseError($exception);
    }
});

// =============================================
// DATABASE UTILITY FUNCTIONS
// =============================================

/**
 * Generate next loan number
 */
function generateLoanNumber() {
    $db = Database::getInstance();
    
    // Get current year and month
    $year = date('Y');
    $month = date('m');
    
    // Count loans this month
    $count = $db->count('loans', [
        'YEAR(created_at)' => $year,
        'MONTH(created_at)' => $month
    ]);
    
    $nextNumber = $count + 1;
    return "LN-$year$month-" . str_pad($nextNumber, 5, '0', STR_PAD_LEFT);
}

/**
 * Generate next borrower number
 */
function generateBorrowerNumber() {
    $db = Database::getInstance();
    
    // Get current year
    $year = date('Y');
    
    // Count borrowers this year
    $count = $db->count('borrowers', [
        'YEAR(created_at)' => $year
    ]);
    
    $nextNumber = $count + 1;
    return "BR-$year-" . str_pad($nextNumber, 6, '0', STR_PAD_LEFT);
}

/**
 * Generate next repayment number
 */
function generateRepaymentNumber() {
    $db = Database::getInstance();
    
    // Get current date
    $date = date('Ymd');
    
    // Count repayments today
    $count = $db->count('repayments', [
        'DATE(payment_date)' => date('Y-m-d')
    ]);
    
    $nextNumber = $count + 1;
    return "RP-$date-" . str_pad($nextNumber, 4, '0', STR_PAD_LEFT);
}

/**
 * Format currency for database storage
 */
function formatCurrencyForDb($amount) {
    // Remove any non-numeric characters except decimal point
    $clean = preg_replace('/[^0-9.]/', '', $amount);
    return (float) $clean;
}

/**
 * Calculate age from date of birth
 */
function calculateAge($dateOfBirth) {
    $birthDate = new DateTime($dateOfBirth);
    $today = new DateTime();
    return $today->diff($birthDate)->y;
}

/**
 * Calculate loan maturity date
 */
function calculateMaturityDate($startDate, $term, $termUnit) {
    $date = new DateTime($startDate);
    
    switch ($termUnit) {
        case 'days':
            $date->modify("+$term days");
            break;
        case 'weeks':
            $date->modify("+$term weeks");
            break;
        case 'months':
            $date->modify("+$term months");
            break;
        case 'years':
            $date->modify("+$term years");
            break;
    }
    
    return $date->format('Y-m-d');
}

/**
 * Calculate installment amount
 */
function calculateInstallmentAmount($principal, $interestRate, $term, $termUnit, $interestType = 'reducing') {
    if ($interestType === 'flat') {
        // Flat interest calculation
        $totalInterest = ($principal * $interestRate * $term) / 100;
        $totalAmount = $principal + $totalInterest;
        return $totalAmount / $term;
    } else {
        // Reducing balance calculation (simplified)
        // For accurate reducing balance, use amortization formula
        $monthlyRate = $interestRate / 12 / 100;
        $installment = ($principal * $monthlyRate * pow(1 + $monthlyRate, $term)) / 
                      (pow(1 + $monthlyRate, $term) - 1);
        return $installment;
    }
}

// =============================================
// DATABASE SECURITY FUNCTIONS
// =============================================

/**
 * Sanitize database input
 */
function sanitizeDbInput($input) {
    if (is_array($input)) {
        return array_map('sanitizeDbInput', $input);
    }
    
    // Remove potentially harmful characters
    $input = strip_tags($input);
    $input = htmlspecialchars($input, ENT_QUOTES, 'UTF-8');
    
    return $input;
}

/**
 * Validate database output
 */
function validateDbOutput($output) {
    if (is_array($output)) {
        return array_map('validateDbOutput', $output);
    }
    
    // Ensure output is safe for display
    return htmlspecialchars_decode($output, ENT_QUOTES);
}

/**
 * Generate secure database backup filename
 */
function generateSecureBackupFilename() {
    $timestamp = date('Y-m-d-H-i-s');
    $random = bin2hex(random_bytes(8));
    return "backup-$timestamp-$random.sql";
}

/**
 * Encrypt sensitive database data
 */
function encryptDbData($data, $key = null) {
    if ($key === null) {
        $key = defined('ENCRYPTION_KEY') ? ENCRYPTION_KEY : 'default-secret-key';
    }
    
    $iv = random_bytes(16);
    $encrypted = openssl_encrypt($data, 'AES-256-CBC', $key, 0, $iv);
    return base64_encode($iv . $encrypted);
}

/**
 * Decrypt sensitive database data
 */
function decryptDbData($encryptedData, $key = null) {
    if ($key === null) {
        $key = defined('ENCRYPTION_KEY') ? ENCRYPTION_KEY : 'default-secret-key';
    }
    
    $data = base64_decode($encryptedData);
    $iv = substr($data, 0, 16);
    $encrypted = substr($data, 16);
    return openssl_decrypt($encrypted, 'AES-256-CBC', $key, 0, $iv);
}

// =============================================
// DATABASE PERFORMANCE MONITORING
// =============================================

class DatabasePerformanceMonitor {
    private $db;
    private $queries = [];
    private $startTime;
    
    public function __construct() {
        $this->db = Database::getInstance();
        $this->startTime = microtime(true);
    }
    
    public function startQuery($sql) {
        $this->queries[] = [
            'sql' => $sql,
            'start' => microtime(true),
            'end' => null,
            'duration' => null,
            'memory_before' => memory_get_usage(true),
            'memory_after' => null,
            'memory_used' => null
        ];
    }
    
    public function endQuery() {
        $lastIndex = count($this->queries) - 1;
        if ($lastIndex >= 0) {
            $this->queries[$lastIndex]['end'] = microtime(true);
            $this->queries[$lastIndex]['duration'] = 
                $this->queries[$lastIndex]['end'] - $this->queries[$lastIndex]['start'];
            
            $this->queries[$lastIndex]['memory_after'] = memory_get_usage(true);
            $this->queries[$lastIndex]['memory_used'] = 
                $this->queries[$lastIndex]['memory_after'] - $this->queries[$lastIndex]['memory_before'];
        }
    }
    
    public function getStats() {
        $totalDuration = 0;
        $totalMemory = 0;
        $slowQueries = [];
        
        foreach ($this->queries as $query) {
            $totalDuration += $query['duration'];
            $totalMemory += $query['memory_used'];
            
            if ($query['duration'] > 0.1) { // Queries slower than 100ms
                $slowQueries[] = $query;
            }
        }
        
        return [
            'total_queries' => count($this->queries),
            'total_duration' => $totalDuration,
            'average_duration' => count($this->queries) > 0 ? $totalDuration / count($this->queries) : 0,
            'total_memory_used' => $totalMemory,
            'slow_queries' => $slowQueries,
            'queries' => $this->queries
        ];
    }
    
    public function generateReport() {
        $stats = $this->getStats();
        $report = "Database Performance Report\n";
        $report .= "Generated: " . date('Y-m-d H:i:s') . "\n";
        $report .= "Total Queries: " . $stats['total_queries'] . "\n";
        $report .= "Total Duration: " . number_format($stats['total_duration'] * 1000, 2) . " ms\n";
        $report .= "Average Query Time: " . number_format($stats['average_duration'] * 1000, 2) . " ms\n";
        $report .= "Total Memory Used: " . number_format($stats['total_memory_used'] / 1024, 2) . " KB\n";
        
        if (!empty($stats['slow_queries'])) {
            $report .= "\nSlow Queries (>100ms):\n";
            foreach ($stats['slow_queries'] as $query) {
                $report .= "- " . substr($query['sql'], 0, 100) . "...\n";
                $report .= "  Duration: " . number_format($query['duration'] * 1000, 2) . " ms\n";
                $report .= "  Memory: " . number_format($query['memory_used'] / 1024, 2) . " KB\n";
            }
        }
        
        return $report;
    }
}

// =============================================
// FINAL EXPORTS AND GLOBALS
// =============================================

// Export commonly used functions and classes
if (!function_exists('db')) {
    function db() {
        return Database::getInstance();
    }
}

if (!function_exists('table')) {
    function table($tableName) {
        return new QueryBuilder($tableName);
    }
}

if (!function_exists('transaction')) {
    function transaction(callable $callback) {
        $db = Database::getInstance();
        $db->beginTransaction();
        
        try {
            $result = $callback($db);
            $db->commit();
            return $result;
        } catch (Exception $e) {
            $db->rollBack();
            throw $e;
        }
    }
}

// Global database instance
global $database;
$database = Database::getInstance();

// Performance monitor (optional)
global $db_performance_monitor;
$db_performance_monitor = new DatabasePerformanceMonitor();

// Cache instance
global $db_cache;
$db_cache = new DatabaseCache(true);

// =============================================
// AUTO-CONFIGURATION FOR XAMPP
// =============================================

/**
 * Auto-configure for XAMPP environment
 */
function autoConfigureForXampp() {
    // Check if running on XAMPP
    $isXampp = strpos(strtolower($_SERVER['SERVER_SOFTWARE'] ?? ''), 'xampp') !== false;
    
    if ($isXampp) {
        // Set XAMPP-specific configurations
        ini_set('display_errors', '1');
        ini_set('error_reporting', E_ALL);
        
        // Enable query logging for development
        if (!defined('LOG_QUERIES')) {
            define('LOG_QUERIES', true);
        }
        
        // Enable detailed error reporting
        if (!defined('ENVIRONMENT')) {
            define('ENVIRONMENT', 'development');
        }
        
        // Create required directories if they don't exist
        $requiredDirs = [
            __DIR__ . '/../uploads',
            __DIR__ . '/../logs',
            __DIR__ . '/../sessions',
            __DIR__ . '/../cache'
        ];
        
        foreach ($requiredDirs as $dir) {
            if (!file_exists($dir)) {
                mkdir($dir, 0755, true);
            }
        }
        
        return true;
    }
    
    return false;
}

// Run auto-configuration
autoConfigureForXampp();

// =============================================
// DATABASE READY CHECK
// =============================================

/**
 * Check if database is ready for use
 */
function isDatabaseReady() {
    try {
        $db = Database::getInstance();
        
        // Test connection
        $test = $db->singleArray("SELECT 1 as test");
        if ($test['test'] != 1) {
            return false;
        }
        
        // Check if essential tables exist
        $essentialTables = ['users', 'roles', 'branches', 'borrowers', 'loan_products'];
        foreach ($essentialTables as $table) {
            if (!$db->tableExists($table)) {
                return false;
            }
        }
        
        return true;
    } catch (Exception $e) {
        return false;
    }
}

/**
 * Get database readiness status
 */
function getDatabaseStatus() {
    $status = [
        'ready' => false,
        'connection' => false,
        'tables' => [],
        'issues' => []
    ];
    
    try {
        $db = Database::getInstance();
        
        // Test connection
        $test = $db->singleArray("SELECT 1 as test");
        $status['connection'] = ($test['test'] == 1);
        
        if ($status['connection']) {
            // Check essential tables
            $essentialTables = [
                'users' => 'System Users',
                'roles' => 'User Roles',
                'branches' => 'Branches',
                'borrowers' => 'Borrowers',
                'loan_products' => 'Loan Products',
                'loans' => 'Loans',
                'repayments' => 'Repayments'
            ];
            
            foreach ($essentialTables as $table => $description) {
                $exists = $db->tableExists($table);
                $status['tables'][$table] = [
                    'exists' => $exists,
                    'description' => $description
                ];
                
                if (!$exists) {
                    $status['issues'][] = "Missing table: $table ($description)";
                }
            }
            
            $status['ready'] = empty($status['issues']);
        } else {
            $status['issues'][] = "Database connection failed";
        }
        
    } catch (Exception $e) {
        $status['issues'][] = "Database error: " . $e->getMessage();
    }
    
    return $status;
}

// =============================================
// FINAL EXPORT
// =============================================

// Export the main Database class
return [
    'Database' => Database::class,
    'QueryBuilder' => QueryBuilder::class,
    'DatabaseMigration' => DatabaseMigration::class,
    'DatabaseSeeder' => DatabaseSeeder::class,
    'DatabaseValidation' => DatabaseValidation::class,
    'DatabaseCache' => DatabaseCache::class,
    'DatabaseHealth' => DatabaseHealth::class,
    'DatabasePerformanceMonitor' => DatabasePerformanceMonitor::class,
    'functions' => [
        'db' => 'db',
        'table' => 'table',
        'transaction' => 'transaction',
        'db_first' => 'db_first',
        'db_all' => 'db_all',
        'db_value' => 'db_value',
        'db_insert' => 'db_insert',
        'db_update' => 'db_update',
        'db_delete' => 'db_delete',
        'db_exists' => 'db_exists',
        'db_count' => 'db_count',
        'generateLoanNumber' => 'generateLoanNumber',
        'generateBorrowerNumber' => 'generateBorrowerNumber',
        'generateRepaymentNumber' => 'generateRepaymentNumber',
        'isDatabaseReady' => 'isDatabaseReady',
        'getDatabaseStatus' => 'getDatabaseStatus',
        'checkXamppCompatibility' => 'checkXamppCompatibility'
    ],
    'constants' => [
        'DB_HOST' => DB_HOST,
        'DB_NAME' => DB_NAME,
        'DB_USER' => DB_USER,
        'DB_CHARSET' => DB_CHARSET,
        'ENVIRONMENT' => defined('ENVIRONMENT') ? ENVIRONMENT : 'development'
    ]
];

// =============================================
// DATABASE INITIALIZATION SCRIPT
// =============================================

/**
 * Initialize database with required data
 * This should be run once during installation
 */
function initializeDatabase() {
    $db = Database::getInstance();
    
    try {
        $db->beginTransaction();
        
        // 1. Insert default roles
        $roles = [
            ['name' => 'Administrator', 'description' => 'Full system access', 'hierarchy_level' => 1, 'is_system' => 1],
            ['name' => 'Loan Officer', 'description' => 'Can process loans and manage borrowers', 'hierarchy_level' => 2, 'is_system' => 1],
            ['name' => 'Branch Manager', 'description' => 'Manages branch operations and staff', 'hierarchy_level' => 3, 'is_system' => 1],
            ['name' => 'Borrower', 'description' => 'Loan applicant and customer', 'hierarchy_level' => 4, 'is_system' => 1]
        ];
        
        foreach ($roles as $role) {
            if (!$db->exists('roles', ['name' => $role['name']])) {
                $db->insert('roles', $role);
            }
        }
        
        // 2. Insert default permissions (simplified)
        $permissions = [
            ['name' => 'view_dashboard', 'description' => 'View dashboard', 'module' => 'dashboard', 'action' => 'view'],
            ['name' => 'manage_users', 'description' => 'Manage system users', 'module' => 'users', 'action' => 'manage'],
            ['name' => 'manage_borrowers', 'description' => 'Manage borrowers', 'module' => 'borrowers', 'action' => 'manage'],
            ['name' => 'manage_loans', 'description' => 'Manage loans', 'module' => 'loans', 'action' => 'manage'],
            ['name' => 'approve_loans', 'description' => 'Approve loan applications', 'module' => 'loans', 'action' => 'approve'],
            ['name' => 'disburse_loans', 'description' => 'Disburse approved loans', 'module' => 'loans', 'action' => 'disburse'],
            ['name' => 'process_repayments', 'description' => 'Process loan repayments', 'module' => 'repayments', 'action' => 'process'],
            ['name' => 'view_reports', 'description' => 'View system reports', 'module' => 'reports', 'action' => 'view'],
            ['name' => 'manage_settings', 'description' => 'Manage system settings', 'module' => 'settings', 'action' => 'manage']
        ];
        
        foreach ($permissions as $permission) {
            if (!$db->exists('permissions', ['name' => $permission['name']])) {
                $db->insert('permissions', $permission);
            }
        }
        
        // 3. Create default branch
        if (!$db->exists('branches', ['code' => 'MAIN'])) {
            $db->insert('branches', [
                'code' => 'MAIN',
                'name' => 'Main Branch',
                'address' => '123 Main Street, City, Country',
                'phone' => '+265 123 456 789',
                'email' => 'main@farmersloan.com',
                'status' => 'active',
                'currency_code' => 'MWK'
            ]);
        }
        
        // 4. Create default loan products
        $loanProducts = [
            [
                'code' => 'AGRI-001',
                'name' => 'Agricultural Loan',
                'description' => 'For farming activities including seeds, fertilizers, and equipment',
                'product_type' => 'agriculture',
                'min_amount' => 50000,
                'max_amount' => 5000000,
                'interest_rate' => 12.5,
                'interest_type' => 'reducing',
                'min_term' => 3,
                'max_term' => 24,
                'term_unit' => 'months',
                'grace_period' => 1,
                'penalty_rate' => 2.0,
                'requires_guarantor' => 1,
                'min_guarantors' => 1,
                'requires_collateral' => 1,
                'collateral_coverage' => 150,
                'processing_fee' => 1.5,
                'insurance_required' => 1,
                'insurance_rate' => 0.5,
                'status' => 'active'
            ],
            [
                'code' => 'BUS-001',
                'name' => 'Small Business Loan',
                'description' => 'For small business expansion and working capital',
                'product_type' => 'business',
                'min_amount' => 100000,
                'max_amount' => 3000000,
                'interest_rate' => 15.0,
                'interest_type' => 'reducing',
                'min_term' => 6,
                'max_term' => 36,
                'term_unit' => 'months',
                'grace_period' => 1,
                'penalty_rate' => 2.5,
                'requires_guarantor' => 1,
                'min_guarantors' => 2,
                'requires_collateral' => 1,
                'collateral_coverage' => 120,
                'processing_fee' => 2.0,
                'insurance_required' => 1,
                'insurance_rate' => 0.75,
                'status' => 'active'
            ]
        ];
        
        foreach ($loanProducts as $product) {
            if (!$db->exists('loan_products', ['code' => $product['code']])) {
                $db->insert('loan_products', $product);
            }
        }
        
        // 5. Create default admin user (password: admin123)
        if (!$db->exists('users', ['username' => 'admin'])) {
            $adminPassword = password_hash('admin123', PASSWORD_DEFAULT);
            $db->insert('users', [
                'username' => 'admin',
                'email' => 'admin@farmersloan.com',
                'password_hash' => $adminPassword,
                'first_name' => 'System',
                'last_name' => 'Administrator',
                'phone' => '+265 888 888 888',
                'role_id' => 1, // Administrator
                'branch_id' => 1, // Main branch
                'is_active' => 1
            ]);
        }
        
        // 6. Insert default settings
        $settings = [
            ['setting_key' => 'company_name', 'setting_value' => 'Farmers Loan Management System', 'setting_group' => 'general', 'is_public' => 1],
            ['setting_key' => 'company_address', 'setting_value' => '123 Main Street, City, Country', 'setting_group' => 'general', 'is_public' => 1],
            ['setting_key' => 'company_phone', 'setting_value' => '+265 123 456 789', 'setting_group' => 'general', 'is_public' => 1],
            ['setting_key' => 'company_email', 'setting_value' => 'info@farmersloan.com', 'setting_group' => 'general', 'is_public' => 1],
            ['setting_key' => 'currency_code', 'setting_value' => 'MWK', 'setting_group' => 'financial', 'is_public' => 1],
            ['setting_key' => 'currency_symbol', 'setting_value' => 'MK', 'setting_group' => 'financial', 'is_public' => 1],
            ['setting_key' => 'date_format', 'setting_value' => 'Y-m-d', 'setting_group' => 'general', 'is_public' => 1],
            ['setting_key' => 'timezone', 'setting_value' => 'Africa/Blantyre', 'setting_group' => 'general', 'is_public' => 1],
            ['setting_key' => 'items_per_page', 'setting_value' => '20', 'setting_group' => 'general', 'is_public' => 0],
            ['setting_key' => 'enable_sms_notifications', 'setting_value' => '1', 'setting_group' => 'notifications', 'is_public' => 0],
            ['setting_key' => 'enable_email_notifications', 'setting_value' => '1', 'setting_group' => 'notifications', 'is_public' => 0],
            ['setting_key' => 'default_interest_rate', 'setting_value' => '12.5', 'setting_group' => 'loans', 'is_public' => 0],
            ['setting_key' => 'default_loan_term', 'setting_value' => '12', 'setting_group' => 'loans', 'is_public' => 0],
            ['setting_key' => 'max_upload_size', 'setting_value' => '5242880', 'setting_group' => 'system', 'is_public' => 0], // 5MB
            ['setting_key' => 'allowed_file_types', 'setting_value' => 'jpg,jpeg,png,pdf,doc,docx', 'setting_group' => 'system', 'is_public' => 0]
        ];
        
        foreach ($settings as $setting) {
            if (!$db->exists('settings', ['setting_key' => $setting['setting_key']])) {
                $db->insert('settings', $setting);
            }
        }
        
        $db->commit();
        
        return [
            'success' => true,
            'message' => 'Database initialized successfully',
            'admin_credentials' => [
                'username' => 'admin',
                'password' => 'admin123',
                'note' => 'Please change the default password immediately'
            ]
        ];
        
    } catch (Exception $e) {
        $db->rollBack();
        return [
            'success' => false,
            'message' => 'Database initialization failed: ' . $e->getMessage()
        ];
    }
}

// =============================================
// INSTALLATION CHECK
// =============================================

/**
 * Check if system needs installation
 */
function needsInstallation() {
    try {
        $db = Database::getInstance();
        
        // Check if users table exists and has at least one user
        if (!$db->tableExists('users')) {
            return true;
        }
        
        $userCount = $db->count('users');
        return $userCount === 0;
        
    } catch (Exception $e) {
        // If we can't connect to database, installation is needed
        return true;
    }
}

/**
 * Run installation wizard
 */
function runInstallation() {
    $steps = [];
    
    // Step 1: Check requirements
    $compatibility = checkXamppCompatibility();
    $steps['requirements'] = $compatibility;
    
    if (!$compatibility['compatible']) {
        return [
            'success' => false,
            'message' => 'System requirements not met',
            'steps' => $steps
        ];
    }
    
    // Step 2: Test database connection
    try {
        $db = Database::getInstance();
        $test = $db->singleArray("SELECT 1 as test");
        $steps['database_connection'] = [
            'success' => ($test['test'] == 1),
            'message' => 'Database connection successful'
        ];
    } catch (Exception $e) {
        $steps['database_connection'] = [
            'success' => false,
            'message' => 'Database connection failed: ' . $e->getMessage()
        ];
        return [
            'success' => false,
            'message' => 'Database connection failed',
            'steps' => $steps
        ];
    }
    
    // Step 3: Import database schema
    $schemaFile = __DIR__ . '/../database/schema.sql';
    if (file_exists($schemaFile)) {
        try {
            $sql = file_get_contents($schemaFile);
            $db->exec($sql);
            $steps['schema_import'] = [
                'success' => true,
                'message' => 'Database schema imported successfully'
            ];
        } catch (Exception $e) {
            $steps['schema_import'] = [
                'success' => false,
                'message' => 'Schema import failed: ' . $e->getMessage()
            ];
            return [
                'success' => false,
                'message' => 'Schema import failed',
                'steps' => $steps
            ];
        }
    }
    
    // Step 4: Initialize with default data
    $initResult = initializeDatabase();
    $steps['data_initialization'] = $initResult;
    
    if (!$initResult['success']) {
        return [
            'success' => false,
            'message' => 'Data initialization failed',
            'steps' => $steps
        ];
    }
    
    // Step 5: Create configuration file
    $configFile = __DIR__ . '/../config/installed.php';
    $configContent = "<?php\n// Installation completed on " . date('Y-m-d H:i:s') . "\ndefine('INSTALLED', true);\n";
    
    if (file_put_contents($configFile, $configContent)) {
        $steps['configuration'] = [
            'success' => true,
            'message' => 'Configuration file created'
        ];
    } else {
        $steps['configuration'] = [
            'success' => false,
            'message' => 'Failed to create configuration file'
        ];
    }
    
    return [
        'success' => true,
        'message' => 'Installation completed successfully',
        'steps' => $steps,
        'admin_credentials' => $initResult['admin_credentials'] ?? null
    ];
}

// =============================================
// FINAL CHECK AND READY MESSAGE
// =============================================

// Check if this is the first run
if (!defined('INSTALLED') && needsInstallation()) {
    // Set a flag to indicate installation is needed
    define('NEEDS_INSTALLATION', true);
    
    // In development, show installation prompt
    if (defined('ENVIRONMENT') && ENVIRONMENT === 'development') {
        // You might want to redirect to install.php or show a message
        // For now, just set a constant
        define('SHOW_INSTALL_PROMPT', true);
    }
}

// =============================================
// END OF FILE - DATABASE.PHP
// =============================================
?>