<?php

/**
 * Centralized PostgreSQL connection manager for MCH API.
 *
 * Replaces per-request pg_connect/pg_close with:
 * - Singleton connection reuse within each PHP worker process
 * - Optional persistent connections (pg_pconnect) or PgBouncer-backed pooling
 * - Connection health checks, automatic retry, and structured logging
 *
 * @see db_config.php  — configuration constants and $con global
 * @see db_helpers.php — query/transaction helpers
 */
class DatabaseManager
{
    /** @var DatabaseManager|null */
    private static $instance = null;

    /** @var \PgSql\Connection|null PostgreSQL connection handle */
    private $connection = null;

    /** @var float|null Unix timestamp when the current connection was opened */
    private $connectedAt = null;

    /** @var int Total queries executed through this manager in the current request */
    private $queryCount = 0;

    /** @var int Queries exceeding the slow-query threshold */
    private $slowQueryCount = 0;

    /** @var int Connection acquisition failures (after all retries) */
    private $connectionFailures = 0;

    /** @var bool Whether shutdown logging has been registered */
    private $shutdownRegistered = false;

    /** @var \PDO|null PDO handle for bulk-sync endpoints */
    private $pdo = null;

    /**
     * Singleton accessor — one manager per PHP worker process.
     */
    public static function getInstance()
    {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    private function __construct()
    {
        $this->registerShutdownLogging();
    }

    /**
     * Returns a live PostgreSQL connection, reconnecting when stale or broken.
     *
     * @return \PgSql\Connection
     * @throws RuntimeException when connection cannot be established
     */
    public function getConnection()
    {
        if ($this->isConnectionHealthy()) {
            return $this->connection;
        }

        $this->disconnect(false);

        $maxRetries = defined('DB_MAX_RETRIES') ? (int) DB_MAX_RETRIES : 3;
        $lastError = 'unknown error';

        for ($attempt = 1; $attempt <= $maxRetries; $attempt++) {
            $started = microtime(true);
            $connection = $this->openConnection();

            if ($connection !== false && $connection !== null) {
                $this->connection = $connection;
                $this->connectedAt = microtime(true);
                $elapsedMs = round((microtime(true) - $started) * 1000, 2);

                $this->log(
                    'info',
                    sprintf(
                        'Connection established (attempt %d/%d, %sms, persistent=%s, pgbouncer=%s)',
                        $attempt,
                        $maxRetries,
                        $elapsedMs,
                        $this->usePersistent() ? 'yes' : 'no',
                        $this->usePgBouncer() ? 'yes' : 'no'
                    )
                );

                return $this->connection;
            }

            $lastError = $this->lastConnectError();
            $this->log(
                'error',
                sprintf('Connection attempt %d/%d failed: %s', $attempt, $maxRetries, $lastError)
            );

            if ($attempt < $maxRetries) {
                // Exponential backoff: 100ms, 200ms, 400ms …
                usleep((int) (100000 * pow(2, $attempt - 1)));
            }
        }

        $this->connectionFailures++;
        throw new RuntimeException('PostgreSQL connection failed after ' . $maxRetries . ' attempts: ' . $lastError);
    }

    /**
     * Returns a shared PDO PostgreSQL connection (used by bulk sync endpoints).
     *
     * @return \PDO
     */
    public function getPdo()
    {
        if ($this->pdo !== null) {
            return $this->pdo;
        }

        $params = $this->getConnectionParams();
        $dsn = sprintf(
            'pgsql:host=%s;port=%d;dbname=%s',
            $params['host'],
            $params['port'],
            $params['dbname']
        );

        $started = microtime(true);
        $this->pdo = new \PDO($dsn, $params['user'], $params['password'], array(
            \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
            \PDO::ATTR_EMULATE_PREPARES => false,
            \PDO::ATTR_PERSISTENT => $this->usePersistent() && !$this->usePgBouncer(),
        ));

        $elapsedMs = round((microtime(true) - $started) * 1000, 2);
        $this->log('info', sprintf('PDO connection established (%sms)', $elapsedMs));

        return $this->pdo;
    }

    /**
     * Releases the connection at end of request.
     *
     * - PgBouncer / non-persistent: closes so the pool can reuse the backend slot.
     * - PHP persistent pool: intentionally does NOT call pg_close (would evict from pool).
     *
     * @param \PgSql\Connection|null $conn Unused; kept for backward compatibility with mch_db_release($con)
     */
    public function releaseConnection($conn = null)
    {
        $this->releasePdo();

        if ($this->usePersistent() && !$this->usePgBouncer()) {
            return;
        }

        $this->disconnect(true);
    }

    /**
     * Release PDO handle only.
     */
    public function releasePdo()
    {
        $this->pdo = null;
    }

    /**
     * Execute a prepared statement with timing and slow-query logging.
     *
     * @param string $stmtName Unique prepared-statement name on this connection
     * @param string $sql      SQL with $1, $2, … placeholders
     * @param array  $params   Bound parameters
     * @param bool   $prepare  When true, calls pg_prepare before execute
     * @return \PgSql\Result|false
     */
    public function executePrepared($stmtName, $sql, array $params = array(), $prepare = true)
    {
        $con = $this->getConnection();
        $started = microtime(true);

        if ($prepare) {
            $prepared = @pg_prepare($con, $stmtName, $sql);
            if ($prepared === false) {
                $this->logQueryFailure($sql, $stmtName, pg_last_error($con));
                return false;
            }
        }

        $result = @pg_execute($con, $stmtName, $params);
        $this->recordQueryTiming($sql, $stmtName, $started, $result !== false);

        if ($result === false) {
            $this->logQueryFailure($sql, $stmtName, pg_last_error($con));
        }

        return $result;
    }

    /**
     * Run a callback inside a database transaction (BEGIN / COMMIT / ROLLBACK).
     *
     * @param callable $callback Receives the \PgSql\Connection
     * @return mixed Return value of $callback
     * @throws Exception Re-throws any exception after rolling back
     */
    public function transaction(callable $callback)
    {
        $con = $this->getConnection();

        if (@pg_query($con, 'BEGIN') === false) {
            throw new RuntimeException('Failed to start transaction: ' . pg_last_error($con));
        }

        try {
            $result = $callback($con);

            if (@pg_query($con, 'COMMIT') === false) {
                throw new RuntimeException('Failed to commit transaction: ' . pg_last_error($con));
            }

            return $result;
        } catch (Exception $e) {
            @pg_query($con, 'ROLLBACK');
            throw $e;
        }
    }

    /**
     * Request-level statistics for shutdown logging.
     */
    public function getStats()
    {
        return array(
            'queries' => $this->queryCount,
            'slow_queries' => $this->slowQueryCount,
            'connection_failures' => $this->connectionFailures,
            'connected_seconds' => $this->connectedAt ? round(microtime(true) - $this->connectedAt, 3) : 0,
        );
    }

    // -------------------------------------------------------------------------
    // Internal helpers
    // -------------------------------------------------------------------------

    private function openConnection()
    {
        $connectionString = $this->buildConnectionString();
        $connectFunction = $this->usePersistent() && !$this->usePgBouncer() ? 'pg_pconnect' : 'pg_connect';

        return @$connectFunction($connectionString);
    }

    /**
     * @return array{host:string,port:int,dbname:string,user:string,password:string}
     */
    private function getConnectionParams()
    {
        if ($this->usePgBouncer()) {
            echo 1;
            $host = defined('DB_PGBOUNCER_HOST') ? DB_PGBOUNCER_HOST : '127.0.0.1';
            $port = defined('DB_PGBOUNCER_PORT') ? (int) DB_PGBOUNCER_PORT : 6432;
        } else {
            echo 2;
            $host = $this->extractDefineValue('DB_SERVER', 'host', '172.16.1.222');
            $port = (int) $this->extractDefineValue('DB_PORT', 'port', '5432');
        }

        return array(
            'host' => $host,
            'port' => $port,
            'dbname' => $this->extractDefineValue('DB_DATABASE', 'dbname', 'mch_prod'),
            'user' => $this->extractDefineValue('DB_USER', 'user', 'postgres'),
            'password' => $this->extractDefineValue('DB_PASSWORD', 'password', ''),
        );
    }

    private function buildConnectionString()
    {
        $params = $this->getConnectionParams();
        $host = $params['host'];
        $port = $params['port'];
        $dbname = $params['dbname'];
        $user = $params['user'];
        $password = $params['password'];

        $parts = array(
            'host=' . $host,
            'port=' . $port,
            'dbname=' . $dbname,
            'user=' . $user,
            'password=' . $password,
            'connect_timeout=' . (defined('DB_CONNECT_TIMEOUT') ? (int) DB_CONNECT_TIMEOUT : 5),
            'application_name=mch_api',
        );

        // Required when connecting through PgBouncer in transaction pooling mode
        // if prepared statements are used (this project uses pg_prepare extensively).
        if ($this->usePgBouncer()) {
            $parts[] = 'options=' . rawurlencode('-c statement_timeout=60000');
        }

        return implode(' ', $parts);
    }

    /**
     * Supports legacy define format: define('DB_USER', "user=postgres")
     */
    private function extractDefineValue($constant, $key, $default)
    {
        if (!defined($constant)) {
            return $default;
        }

        $value = constant($constant);
        if (strpos($value, '=') !== false) {
            $segments = preg_split('/\s+/', trim($value));
            foreach ($segments as $segment) {
                if (strpos($segment, $key . '=') === 0) {
                    return substr($segment, strlen($key) + 1);
                }
            }
        }

        return $value !== '' ? $value : $default;
    }

    private function isConnectionHealthy()
    {
        if ($this->connection === null) {
            return false;
        }

        $status = @pg_connection_status($this->connection);
        if ($status !== PGSQL_CONNECTION_OK) {
            return false;
        }

        // Lightweight health check — catches dropped connections early
        $probe = @pg_query($this->connection, 'SELECT 1');
        return $probe !== false;
    }

    private function disconnect($logRelease)
    {
        if ($this->connection !== null) {
            if ($logRelease) {
                $this->log('info', 'Connection released to pool');
            }
            @pg_close($this->connection);
            $this->connection = null;
            $this->connectedAt = null;
        }
    }

    private function usePersistent()
    {
        return defined('DB_USE_PERSISTENT') ? (bool) DB_USE_PERSISTENT : false;
    }

    private function usePgBouncer()
    {
        return defined('DB_USE_PGBOUNCER') ? (bool) DB_USE_PGBOUNCER : false;
    }

    private function lastConnectError()
    {
        $error = function_exists('pg_last_error') ? pg_last_error(null) : '';
        return $error !== false && $error !== '' ? $error : 'pg_connect returned false';
    }

    private function recordQueryTiming($sql, $stmtName, $started, $success)
    {
        $this->queryCount++;
        $elapsedMs = round((microtime(true) - $started) * 1000, 2);
        $slowThreshold = defined('DB_SLOW_QUERY_MS') ? (int) DB_SLOW_QUERY_MS : 500;

        if ($elapsedMs >= $slowThreshold) {
            $this->slowQueryCount++;
            $this->log(
                'warn',
                sprintf(
                    'Slow query (%sms) stmt=%s sql=%s',
                    $elapsedMs,
                    $stmtName,
                    $this->truncateSql($sql)
                )
            );
        } elseif (defined('DB_LOG_ALL_QUERIES') && DB_LOG_ALL_QUERIES) {
            $this->log(
                'debug',
                sprintf('Query (%sms) stmt=%s', $elapsedMs, $stmtName)
            );
        }
    }

    private function logQueryFailure($sql, $stmtName, $error)
    {
        $this->log(
            'error',
            sprintf(
                'Query failed stmt=%s error=%s sql=%s',
                $stmtName,
                $error,
                $this->truncateSql($sql)
            )
        );
    }

    private function truncateSql($sql)
    {
        $sql = preg_replace('/\s+/', ' ', trim($sql));
        return strlen($sql) > 300 ? substr($sql, 0, 300) . '…' : $sql;
    }

    private function registerShutdownLogging()
    {
        if ($this->shutdownRegistered) {
            return;
        }

        register_shutdown_function(array($this, 'logRequestSummary'));
        $this->shutdownRegistered = true;
    }

    /**
     * Logs per-request connection and query statistics.
     */
    public function logRequestSummary()
    {
        if ($this->queryCount === 0 && $this->connection === null) {
            return;
        }

        $stats = $this->getStats();
        $script = isset($_SERVER['SCRIPT_NAME']) ? $_SERVER['SCRIPT_NAME'] : 'cli';

        $this->log(
            'info',
            sprintf(
                'Request summary script=%s queries=%d slow=%d failures=%d connected_for=%ss',
                $script,
                $stats['queries'],
                $stats['slow_queries'],
                $stats['connection_failures'],
                $stats['connected_seconds']
            )
        );
    }

    /**
     * Writes to log4php when available, otherwise error_log.
     */
    private function log($level, $message)
    {
        $line = '[DatabaseManager] ' . $message;

        if (class_exists('Logger')) {
            $logger = Logger::getLogger('DatabaseManager');
            switch ($level) {
                case 'error':
                    $logger->error($line);
                    break;
                case 'warn':
                    $logger->warn($line);
                    break;
                case 'debug':
                    $logger->debug($line);
                    break;
                default:
                    $logger->info($line);
                    break;
            }
            return;
        }

        error_log($line);
    }
}
