<?php
/**
 * ===========================================
 * FLOWBOT DCI - DATABASE CONNECTION POOL v1.0
 * ===========================================
 * High-performance database connection pooling for massive scale.
 *
 * Features:
 * - Multiple persistent connections
 * - Round-robin load balancing
 * - Connection health checking
 * - Automatic reconnection
 * - Read/Write splitting ready
 */

declare(strict_types=1);

namespace FlowbotDCI\Core;

use PDO;
use PDOException;

class ConnectionPool
{
    /** @var PDO[] Pool of connections */
    private static array $connections = [];

    /** @var array Connection configurations */
    private static array $config = [];

    /** @var int Current connection index for round-robin */
    private static int $currentIndex = 0;

    /** @var int Maximum connections in pool */
    private static int $maxConnections = 5;

    /** @var int Minimum connections to maintain */
    private static int $minConnections = 2;

    /** @var array Connection health status */
    private static array $connectionHealth = [];

    /** @var int Last health check timestamp */
    private static int $lastHealthCheck = 0;

    /** @var int Health check interval in seconds */
    private const HEALTH_CHECK_INTERVAL = 60;

    /**
     * Initialize the connection pool
     */
    public static function init(array $config): void
    {
        self::$config = $config;
        self::$maxConnections = (int)($config['pool_max_connections'] ?? 5);
        self::$minConnections = (int)($config['pool_min_connections'] ?? 2);

        // Pre-create minimum connections
        for ($i = 0; $i < self::$minConnections; $i++) {
            try {
                self::$connections[$i] = self::createConnection();
                self::$connectionHealth[$i] = time();
            } catch (\Exception $e) {
                error_log("ConnectionPool: Failed to create initial connection {$i}: " . $e->getMessage());
            }
        }
    }

    /**
     * Get a connection from the pool (round-robin)
     */
    public static function getConnection(): PDO
    {
        // Initialize if not done
        if (empty(self::$connections)) {
            throw new \RuntimeException("ConnectionPool not initialized. Call ConnectionPool::init() first.");
        }

        // Periodic health check
        if (time() - self::$lastHealthCheck > self::HEALTH_CHECK_INTERVAL) {
            self::healthCheck();
        }

        // Try to get a healthy connection
        $attempts = 0;
        $maxAttempts = count(self::$connections) * 2;

        while ($attempts < $maxAttempts) {
            $index = self::$currentIndex;
            self::$currentIndex = (self::$currentIndex + 1) % count(self::$connections);

            if (isset(self::$connections[$index])) {
                $conn = self::$connections[$index];

                // Quick ping to check if connection is alive
                try {
                    $conn->query('SELECT 1');
                    return $conn;
                } catch (\PDOException $e) {
                    // Connection dead, try to recreate
                    error_log("ConnectionPool: Connection {$index} dead, recreating...");
                    self::$connections[$index] = self::createConnection();
                    self::$connectionHealth[$index] = time();
                    return self::$connections[$index];
                }
            }

            $attempts++;
        }

        // All connections failed, create a new one
        return self::createConnection();
    }

    /**
     * Get a connection for read operations (can use replicas)
     * For future use with read replicas
     */
    public static function getReadConnection(): PDO
    {
        // For now, same as regular connection
        // Future: implement read replica rotation
        return self::getConnection();
    }

    /**
     * Get a connection for write operations (always primary)
     */
    public static function getWriteConnection(): PDO
    {
        // Always use primary for writes
        return self::getConnection();
    }

    /**
     * Create a new database connection
     */
    private static function createConnection(): PDO
    {
        $config = self::$config;

        $dsn = sprintf(
            "mysql:host=%s;port=%s;dbname=%s;charset=%s",
            $config['host'] ?? 'localhost',
            $config['port'] ?? 3306,
            $config['name'] ?? 'flowbot',
            $config['charset'] ?? 'utf8mb4'
        );

        $options = [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false,
            PDO::ATTR_PERSISTENT         => true, // Persistent connections
            PDO::ATTR_TIMEOUT            => 5,
            PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
        ];

        try {
            $pdo = new PDO($dsn, $config['user'] ?? 'root', $config['password'] ?? '', $options);

            // Set session variables for performance
            $pdo->exec("SET NAMES " . ($config['charset'] ?? 'utf8mb4'));
            $pdo->exec("SET SESSION sql_mode = 'TRADITIONAL'");

            return $pdo;

        } catch (PDOException $e) {
            $host = $config['host'] ?? 'unknown';
            $name = $config['name'] ?? 'unknown';
            throw new PDOException("ConnectionPool failed to {$host}/{$name}: " . $e->getMessage());
        }
    }

    /**
     * Health check all connections
     */
    private static function healthCheck(): void
    {
        self::$lastHealthCheck = time();

        foreach (self::$connections as $index => $conn) {
            try {
                $conn->query('SELECT 1');
                self::$connectionHealth[$index] = time();
            } catch (\PDOException $e) {
                error_log("ConnectionPool: Connection {$index} failed health check, recreating...");
                try {
                    self::$connections[$index] = self::createConnection();
                    self::$connectionHealth[$index] = time();
                } catch (\Exception $ex) {
                    error_log("ConnectionPool: Failed to recreate connection {$index}: " . $ex->getMessage());
                    unset(self::$connections[$index]);
                    unset(self::$connectionHealth[$index]);
                }
            }
        }

        // Ensure minimum connections
        while (count(self::$connections) < self::$minConnections) {
            try {
                $newIndex = count(self::$connections);
                self::$connections[$newIndex] = self::createConnection();
                self::$connectionHealth[$newIndex] = time();
            } catch (\Exception $e) {
                error_log("ConnectionPool: Failed to create minimum connection: " . $e->getMessage());
                break;
            }
        }

        // Re-index arrays
        self::$connections = array_values(self::$connections);
        self::$connectionHealth = array_values(self::$connectionHealth);
        self::$currentIndex = 0;
    }

    /**
     * Add a connection to the pool (for scaling up)
     */
    public static function addConnection(): bool
    {
        if (count(self::$connections) >= self::$maxConnections) {
            return false;
        }

        try {
            $index = count(self::$connections);
            self::$connections[$index] = self::createConnection();
            self::$connectionHealth[$index] = time();
            return true;
        } catch (\Exception $e) {
            error_log("ConnectionPool: Failed to add connection: " . $e->getMessage());
            return false;
        }
    }

    /**
     * Remove a connection from the pool (for scaling down)
     */
    public static function removeConnection(): bool
    {
        if (count(self::$connections) <= self::$minConnections) {
            return false;
        }

        $index = count(self::$connections) - 1;
        self::$connections[$index] = null;
        unset(self::$connections[$index]);
        unset(self::$connectionHealth[$index]);

        return true;
    }

    /**
     * Get pool statistics
     */
    public static function getStats(): array
    {
        return [
            'total_connections' => count(self::$connections),
            'max_connections' => self::$maxConnections,
            'min_connections' => self::$minConnections,
            'current_index' => self::$currentIndex,
            'last_health_check' => self::$lastHealthCheck,
            'connection_ages' => array_map(
                fn($time) => time() - $time,
                self::$connectionHealth
            ),
        ];
    }

    /**
     * Close all connections
     */
    public static function closeAll(): void
    {
        foreach (self::$connections as $index => $conn) {
            self::$connections[$index] = null;
        }
        self::$connections = [];
        self::$connectionHealth = [];
        self::$currentIndex = 0;
    }

    /**
     * Execute a query with automatic retry on connection failure
     */
    public static function query(string $sql, array $params = []): \PDOStatement
    {
        $maxRetries = 3;
        $lastException = null;

        for ($i = 0; $i < $maxRetries; $i++) {
            try {
                $conn = self::getConnection();
                $stmt = $conn->prepare($sql);
                $stmt->execute($params);
                return $stmt;
            } catch (\PDOException $e) {
                $lastException = $e;
                // If connection error, force health check
                if (self::isConnectionError($e)) {
                    self::healthCheck();
                } else {
                    // Query error, not connection - throw immediately
                    throw $e;
                }
            }
        }

        throw $lastException ?? new \RuntimeException("Query failed after {$maxRetries} retries");
    }

    /**
     * Execute a query that modifies data (INSERT, UPDATE, DELETE)
     */
    public static function execute(string $sql, array $params = []): int
    {
        $stmt = self::query($sql, $params);
        return $stmt->rowCount();
    }

    /**
     * Fetch all results from a query
     */
    public static function fetchAll(string $sql, array $params = []): array
    {
        $stmt = self::query($sql, $params);
        return $stmt->fetchAll();
    }

    /**
     * Fetch single row from a query
     */
    public static function fetchOne(string $sql, array $params = []): ?array
    {
        $stmt = self::query($sql, $params);
        $result = $stmt->fetch();
        return $result ?: null;
    }

    /**
     * Fetch single column value
     */
    public static function fetchColumn(string $sql, array $params = [], int $column = 0)
    {
        $stmt = self::query($sql, $params);
        return $stmt->fetchColumn($column);
    }

    /**
     * Begin transaction on primary connection
     */
    public static function beginTransaction(): PDO
    {
        $conn = self::getWriteConnection();
        $conn->beginTransaction();
        return $conn;
    }

    /**
     * Check if exception is a connection error
     */
    private static function isConnectionError(\PDOException $e): bool
    {
        $connectionErrorCodes = [
            'HY000', // General error (often connection)
            '08003', // Connection does not exist
            '08006', // Connection failure
            '08S01', // Communication link failure
            '2006',  // MySQL server has gone away
            '2013',  // Lost connection during query
        ];

        $code = $e->getCode();
        $message = strtolower($e->getMessage());

        // Check SQLSTATE codes
        if (in_array($code, $connectionErrorCodes)) {
            return true;
        }

        // Check message patterns
        $patterns = ['gone away', 'lost connection', 'connection refused', 'connection timed out'];
        foreach ($patterns as $pattern) {
            if (strpos($message, $pattern) !== false) {
                return true;
            }
        }

        return false;
    }

    /**
     * Execute batch insert (optimized for large data)
     */
    public static function batchInsert(string $table, array $columns, array $rows, int $batchSize = 1000): int
    {
        if (empty($rows)) {
            return 0;
        }

        $totalInserted = 0;
        $chunks = array_chunk($rows, $batchSize);

        $columnList = '`' . implode('`, `', $columns) . '`';
        $placeholders = '(' . implode(', ', array_fill(0, count($columns), '?')) . ')';

        foreach ($chunks as $chunk) {
            $valuePlaceholders = implode(', ', array_fill(0, count($chunk), $placeholders));
            $sql = "INSERT INTO `{$table}` ({$columnList}) VALUES {$valuePlaceholders}";

            $params = [];
            foreach ($chunk as $row) {
                foreach ($columns as $col) {
                    $params[] = $row[$col] ?? null;
                }
            }

            $totalInserted += self::execute($sql, $params);
        }

        return $totalInserted;
    }
}
