<?php
/**
 * DATABASE CONFIGURATION
 * File: config/database.php
 * Database connection and query builder configuration
 */

if (!defined('DB_HOST')) {
    // Fallback to environment variables if not defined
    define('DB_HOST', $_ENV['DB_HOST'] ?? 'localhost');
    define('DB_NAME', $_ENV['DB_NAME'] ?? 'rentidium');
    define('DB_USER', $_ENV['DB_USER'] ?? 'root');
    define('DB_PASS', $_ENV['DB_PASS'] ?? '');
    define('DB_CHARSET', $_ENV['DB_CHARSET'] ?? 'utf8mb4');
    define('DB_PORT', $_ENV['DB_PORT'] ?? 3306);
}

// Database configuration array
return [
    'default' => [
        'host' => DB_HOST,
        'database' => DB_NAME,
        'username' => DB_USER,
        'password' => DB_PASS,
        'charset' => DB_CHARSET,
        'port' => DB_PORT,
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'options' => [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES " . DB_CHARSET . " COLLATE " . 'utf8mb4_unicode_ci'
        ]
    ],

    // Read replica configuration (for scaling)
    'read' => [
        [
            'host' => $_ENV['DB_READ_HOST'] ?? DB_HOST,
            'database' => DB_NAME,
            'username' => $_ENV['DB_READ_USER'] ?? DB_USER,
            'password' => $_ENV['DB_READ_PASS'] ?? DB_PASS,
            'charset' => DB_CHARSET,
            'port' => DB_PORT,
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'options' => [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
                PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES " . DB_CHARSET . " COLLATE " . 'utf8mb4_unicode_ci'
            ]
        ]
    ],

    // Test database configuration
    'testing' => [
        'host' => $_ENV['DB_TEST_HOST'] ?? 'localhost',
        'database' => $_ENV['DB_TEST_NAME'] ?? 'rentidium_test',
        'username' => $_ENV['DB_TEST_USER'] ?? 'root',
        'password' => $_ENV['DB_TEST_PASS'] ?? '',
        'charset' => DB_CHARSET,
        'port' => DB_PORT,
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'options' => [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES " . DB_CHARSET . " COLLATE " . 'utf8mb4_unicode_ci'
        ]
    ]
];

// Table prefix for multi-tenant setups
if (!defined('DB_PREFIX')) {
    define('DB_PREFIX', '');
}

// Connection timeout
if (!defined('DB_TIMEOUT')) {
    define('DB_TIMEOUT', 30);
}

// SSL configuration for production
if (!defined('DB_SSL')) {
    define('DB_SSL', $_ENV['DB_SSL'] ?? false);
}

// Max connections pool size
if (!defined('DB_POOL_SIZE')) {
    define('DB_POOL_SIZE', 10);
}

// Query cache settings
if (!defined('DB_CACHE_ENABLED')) {
    define('DB_CACHE_ENABLED', $_ENV['DB_CACHE_ENABLED'] ?? false);
}

if (!defined('DB_CACHE_LIFETIME')) {
    define('DB_CACHE_LIFETIME', 3600); // 1 hour
}

// Logging configuration
if (!defined('DB_LOG_QUERIES')) {
    define('DB_LOG_QUERIES', $_ENV['DB_LOG_QUERIES'] ?? false);
}

if (!defined('DB_LOG_FILE')) {
    define('DB_LOG_FILE', STORAGE_PATH . '/logs/database.log');
}

// Database connection retry configuration
if (!defined('DB_RETRY_ATTEMPTS')) {
    define('DB_RETRY_ATTEMPTS', 3);
}

if (!defined('DB_RETRY_DELAY')) {
    define('DB_RETRY_DELAY', 1000); // milliseconds
}

// Database optimization settings
if (!defined('DB_SLOW_QUERY_TIME')) {
    define('DB_SLOW_QUERY_TIME', 2.0); // seconds
}

// Database maintenance settings
if (!defined('DB_MAINTENANCE_MODE')) {
    define('DB_MAINTENANCE_MODE', $_ENV['DB_MAINTENANCE_MODE'] ?? false);
}

// Table engine preferences
if (!defined('DB_DEFAULT_ENGINE')) {
    define('DB_DEFAULT_ENGINE', 'InnoDB');
}

// Character set settings
if (!defined('DB_COLLATION')) {
    define('DB_COLLATION', 'utf8mb4_unicode_ci');
}

// Additional MySQL configuration options
$mysql_options = [
    'sql_mode' => 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO',
    'time_zone' => '+00:00',
    'autocommit' => 1,
    'character_set_server' => DB_CHARSET,
    'collation_server' => DB_COLLATION
];

// Connection string builder function
function buildConnectionString($config) {
    $dsn = "mysql:host={$config['host']};port={$config['port']};dbname={$config['database']};charset={$config['charset']}";
    
    if (DB_SSL) {
        $dsn .= ";sslmode=require";
    }
    
    return $dsn;
}

// Database configuration validation
function validateDatabaseConfig($config) {
    $required = ['host', 'database', 'username', 'password'];
    $errors = [];
    
    foreach ($required as $field) {
        if (empty($config[$field])) {
            $errors[] = "Missing required database configuration: {$field}";
        }
    }
    
    // Validate host format
    if (isset($config['host']) && !filter_var($config['host'], FILTER_VALIDATE_DOMAIN) && 
        !filter_var($config['host'], FILTER_VALIDATE_IP)) {
        $errors[] = "Invalid database host format: {$config['host']}";
    }
    
    // Validate port
    if (isset($config['port']) && (!is_numeric($config['port']) || $config['port'] < 1 || $config['port'] > 65535)) {
        $errors[] = "Invalid database port: {$config['port']}";
    }
    
    return empty($errors) ? true : $errors;
}

// Database health check function
function checkDatabaseHealth() {
    try {
        $db = Database::getInstance();
        $result = $db->fetch("SELECT 1 as health_check");
        return $result['health_check'] == 1;
    } catch (Exception $e) {
        return false;
    }
}

// Database backup configuration
if (!defined('DB_BACKUP_ENABLED')) {
    define('DB_BACKUP_ENABLED', $_ENV['DB_BACKUP_ENABLED'] ?? false);
}

if (!defined('DB_BACKUP_PATH')) {
    define('DB_BACKUP_PATH', STORAGE_PATH . '/backups');
}

if (!defined('DB_BACKUP_RETENTION_DAYS')) {
    define('DB_BACKUP_RETENTION_DAYS', 30);
}

// Connection monitoring
if (!defined('DB_MONITOR_CONNECTIONS')) {
    define('DB_MONITOR_CONNECTIONS', $_ENV['DB_MONITOR_CONNECTIONS'] ?? false);
}

if (!defined('DB_MAX_CONNECTS')) {
    define('DB_MAX_CONNECTS', $_ENV['DB_MAX_CONNECTS'] ?? 100);
}

// Database indexing strategy
$indexing_strategy = [
    'users' => ['email', 'role', 'created_at'],
    'items' => ['vendor_id', 'category_id', 'approval_status', 'is_active', 'city', 'state', 'price_per_day'],
    'orders' => ['customer_id', 'vendor_id', 'item_id', 'status', 'start_date', 'end_date', 'created_at'],
    'reviews' => ['item_id', 'customer_id', 'order_id', 'rating', 'created_at'],
    'vendor_wallets' => ['vendor_id', 'status'],
    'vendor_withdrawals' => ['vendor_id', 'status', 'requested_at']
];

// Query optimization hints
$query_hints = [
    'use_indexes' => true,
    'analyze_tables' => true,
    'optimize_tables' => true,
    'check_fragmentation' => true
];

// Connection pool configuration
$connection_pool = [
    'max_connections' => DB_POOL_SIZE,
    'min_connections' => 2,
    'idle_timeout' => 300, // 5 minutes
    'wait_timeout' => 30, // 30 seconds
    'max_waiting' => 10
];

// Database migration configuration
if (!defined('DB_MIGRATION_PATH')) {
    define('DB_MIGRATION_PATH', DATABASE_PATH . '/migrations');
}

if (!defined('DB_MIGRATION_BATCH_SIZE')) {
    define('DB_MIGRATION_BATCH_SIZE', 10);
}