<?php
/**
 * PDO singleton. Use db() everywhere; never instantiate directly.
 *
 *   $pdo = db();
 *   $row = $pdo->prepare('SELECT * FROM users WHERE id = ?');
 *   $row->execute([$id]);
 *   $user = $row->fetch();
 *
 * ALWAYS use prepared statements. Never interpolate user input into SQL.
 */

/** Build a fresh, tuned PDO connection.  Used by db() and db_reconnect(). */
function db_make_pdo(): PDO
{
    $dsn = sprintf(
        'mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4',
        DB_HOST, DB_PORT, DB_NAME
    );
    $pdo = new PDO($dsn, DB_USER, DB_PASS, [
        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 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'",
        // Enables LOAD DATA LOCAL INFILE on the client side.  Server-side local_infile
        // may still be off — the engine attempts a fast bulk load and falls back to
        // batched INSERTs if the server refuses.
        PDO::MYSQL_ATTR_LOCAL_INFILE => true,
    ]);
    // Keep big ingests alive.  Each is best-effort: a cPanel/Percona user may lack
    // rights (e.g. max_allowed_packet is read-only at SESSION scope on MySQL 8),
    // so failures are ignored — the engine also byte-bounds its INSERT packets.
    // Pin the DB session to US Eastern so NOW()/CURRENT_TIMESTAMP and DATE()/GROUP-BY buckets are Eastern (the app's
    // canonical stamping zone). PREFER the NAMED zone — MySQL then applies the correct per-DATE DST offset to every
    // row, so even cross-DST-season history is right. Shared hosting (GlobeHost) often lacks the mysql.time_zone_*
    // tables, where the named form fails; then fall back to a numeric offset for the current moment. EITHER WAY the
    // exact zone we set is remembered in $GLOBALS so the PHP display helpers (fmt_dt/disp_dt) re-interpret DB strings
    // in that SAME zone to recover the true instant and render per-date-DST-correct wall times (see db_session_tz()).
    // All timestamp columns are TIMESTAMP (UTC-stored), so old rows re-express in Eastern with no data migration.
    $GLOBALS['__medmecs_db_tz'] = APP_TIMEZONE;
    try {
        $pdo->exec("SET time_zone = 'America/New_York'");
        $GLOBALS['__medmecs_db_tz'] = 'America/New_York';
    } catch (Throwable $t) {
        try {
            $offset = (new DateTimeImmutable('now', new DateTimeZone(APP_TIMEZONE)))->format('P');   // e.g. -05:00 / -04:00
            $pdo->exec("SET time_zone = '" . $offset . "'");
            $GLOBALS['__medmecs_db_tz'] = $offset;
        } catch (Throwable $t2) { /* leave the server default; helpers then assume APP_TIMEZONE */ }
    }
    foreach ([
        "SET SESSION max_allowed_packet = 268435456",   // 256 MB (works on MariaDB; no-op on MySQL 8)
        "SET SESSION wait_timeout = 28800",             // 8 h — don't drop a long ingest
        "SET SESSION net_read_timeout = 600",
        "SET SESSION net_write_timeout = 600",
        "SET SESSION innodb_lock_wait_timeout = 300",
    ] as $tune) {
        try { $pdo->exec($tune); } catch (Throwable $t) { /* ignore — best effort */ }
    }
    return $pdo;
}

function db(bool $fresh = false): PDO
{
    static $pdo = null;
    if ($fresh) { $pdo = null; }
    if ($pdo === null) {
        try {
            $pdo = db_make_pdo();
        } catch (PDOException $e) {
            if (APP_DEBUG) {
                http_response_code(500);
                echo '<h2>Database connection failed</h2><pre>' . htmlspecialchars($e->getMessage()) . '</pre>';
                echo '<p>Check the credentials in <code>includes/config.php</code> '
                    . 'and confirm the database exists in phpMyAdmin.</p>';
                exit;
            }
            error_log('DB connect failed: ' . $e->getMessage());
            http_response_code(500);
            exit('Database unavailable.');
        }
    }
    return $pdo;
}

/**
 * Force a brand-new connection (used after "MySQL server has gone away" mid-ingest).
 * Resets the singleton so every subsequent db() returns the live handle.
 */
function db_reconnect(): PDO
{
    return db(true);
}
