<?php
// ============================================================
//  MJPOS – Database Connection (PDO Singleton)
//  Fix: "Too many connections" on cPanel shared hosting
// ============================================================

class Database
{
    private static ?PDO $instance = null;

    private function __construct() {}
    private function __clone() {}

    public static function getInstance(): PDO
    {
        if (self::$instance === null) {
            self::$instance = self::connect();
        }
        return self::$instance;
    }

    private static function connect(int $maxRetries = 3): PDO
    {
        $dsn = sprintf(
            'mysql:host=%s;dbname=%s;charset=%s',
            DB_HOST, DB_NAME, DB_CHARSET
        );

        $options = [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false,
            // Persistent connections reuse existing MySQL connections
            // instead of opening a new one on every PHP request —
            // this is the primary fix for "Too many connections" on
            // cPanel shared hosting with low max_connections limits.
            PDO::ATTR_PERSISTENT         => true,
            // Auto-reconnect if the connection was dropped
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci",
        ];

        $lastError = null;
        for ($attempt = 1; $attempt <= $maxRetries; $attempt++) {
            try {
                return new PDO($dsn, DB_USER, DB_PASS, $options);
            } catch (PDOException $e) {
                $lastError = $e;
                $code = (int) $e->getCode();

                // 1040 = Too many connections, 2002 = Connection refused
                // Wait briefly and retry (only for connection-limit errors)
                if (in_array($code, [1040, 2002, 1203], true) && $attempt < $maxRetries) {
                    usleep(250000 * $attempt); // wait 0.25s, 0.5s, ...
                    continue;
                }
                break;
            }
        }

        // All retries failed — show user-friendly error
        $msg = $lastError?->getMessage() ?? 'Unknown error';
        $code = (int)($lastError?->getCode() ?? 0);

        if ($code === 1040 || $code === 1203) {
            self::showBusyPage();
        }

        if (defined('APP_DEBUG') && APP_DEBUG) {
            die('<pre style="font-family:monospace;padding:20px">Database connection failed: ' . htmlspecialchars($msg) . '</pre>');
        }

        self::showBusyPage();
    }

    /** User-friendly "busy" page instead of raw PDO error */
    private static function showBusyPage(): never
    {
        http_response_code(503);
        $shopName = defined('APP_NAME') ? APP_NAME : 'MJPOS';
        echo '<!DOCTYPE html><html><head>
        <meta charset="UTF-8">
        <meta http-equiv="refresh" content="10">
        <title>Busy — ' . htmlspecialchars($shopName) . '</title>
        <style>
          body{margin:0;min-height:100vh;background:linear-gradient(135deg,#0f172a,#1e3a5f);
               display:flex;align-items:center;justify-content:center;
               font-family:system-ui,sans-serif;color:#fff}
          .box{text-align:center;padding:40px;max-width:400px}
          .icon{font-size:48px;margin-bottom:16px}
          h2{font-size:1.4rem;font-weight:700;margin-bottom:8px}
          p{color:rgba(255,255,255,.75);font-size:.9rem;line-height:1.6}
          .dot{display:inline-block;width:8px;height:8px;border-radius:50%;
               background:#0891b2;margin:0 3px;animation:bounce .8s infinite alternate}
          .dot:nth-child(2){animation-delay:.2s}
          .dot:nth-child(3){animation-delay:.4s}
          @keyframes bounce{to{transform:translateY(-6px);opacity:.4}}
        </style></head>
        <body><div class="box">
          <div class="icon">🔄</div>
          <h2>Server is Busy</h2>
          <p>The system is handling many requests right now.<br>
             This page will refresh automatically in a few seconds.</p>
          <div style="margin-top:20px">
            <span class="dot"></span><span class="dot"></span><span class="dot"></span>
          </div>
        </div></body></html>';
        exit;
    }

    // ── Query helpers ────────────────────────────────────────

    public static function fetchAll(string $sql, array $params = []): array
    {
        $stmt = self::getInstance()->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public static function fetchOne(string $sql, array $params = []): array|false
    {
        $stmt = self::getInstance()->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetch();
    }

    public static function fetchColumn(string $sql, array $params = []): mixed
    {
        $stmt = self::getInstance()->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchColumn();
    }

    public static function execute(string $sql, array $params = []): int
    {
        $stmt = self::getInstance()->prepare($sql);
        $stmt->execute($params);
        return $stmt->rowCount();
    }

    public static function insert(string $sql, array $params = []): string
    {
        $stmt = self::getInstance()->prepare($sql);
        $stmt->execute($params);
        return self::getInstance()->lastInsertId();
    }

    public static function beginTransaction(): bool
    {
        return self::getInstance()->beginTransaction();
    }

    public static function commit(): bool
    {
        return self::getInstance()->commit();
    }

    public static function rollback(): bool
    {
        return self::getInstance()->rollBack();
    }

    public static function paginate(
        string $sql,
        array  $params  = [],
        int    $page    = 1,
        int    $perPage = PER_PAGE
    ): array {
        $countSql = 'SELECT COUNT(*) FROM (' . $sql . ') AS _count_query';
        $total    = (int) self::fetchColumn($countSql, $params);

        $page   = max(1, $page);
        $offset = ($page - 1) * $perPage;

        $pagedSql    = $sql . ' LIMIT ? OFFSET ?';
        $pagedParams = array_values($params);
        $pagedParams[] = $perPage;
        $pagedParams[] = $offset;

        $stmt = self::getInstance()->prepare($pagedSql);
        $stmt->execute($pagedParams);

        return [
            'data'    => $stmt->fetchAll(),
            'total'   => $total,
            'pages'   => (int) ceil($total / $perPage),
            'current' => $page,
            'perPage' => $perPage,
        ];
    }
}