<?php
namespace App\Core;

use PDO;
use PDOException;
use PDOStatement;

/**
 * Pembungkus PDO tunggal (singleton) dengan prepared statement wajib.
 * Semua akses database di aplikasi ini melewati kelas ini.
 */
class Database
{
    private static ?Database $instance = null;
    private PDO $pdo;
    private int $txDepth = 0;
    private array $queryLog = [];
    private bool $logQueries = false;

    private function __construct(array $cfg)
    {
        $dsn = sprintf(
            'mysql:host=%s;port=%d;dbname=%s;charset=%s',
            $cfg['host'], (int)($cfg['port'] ?? 3306), $cfg['database'], $cfg['charset'] ?? 'utf8mb4'
        );
        $this->pdo = new PDO($dsn, $cfg['username'], $cfg['password'], [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false,
            PDO::ATTR_STRINGIFY_FETCHES  => false,
        ]);
        $this->pdo->exec("SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'");
        $this->pdo->exec("SET time_zone = '+07:00'");
    }

    public static function init(array $cfg): void
    {
        self::$instance = new self($cfg);
    }

    public static function i(): Database
    {
        if (self::$instance === null) {
            $cfg = Config::get('db');
            if (!$cfg) {
                throw new \RuntimeException('Konfigurasi basis data belum tersedia.');
            }
            self::$instance = new self($cfg);
        }
        return self::$instance;
    }

    public function pdo(): PDO { return $this->pdo; }

    public function enableQueryLog(bool $on = true): void { $this->logQueries = $on; }
    public function queryLog(): array { return $this->queryLog; }

    /**
     * Menganalisis SQL: mendaftar placeholder bernama dan menulis ulang
     * placeholder yang muncul lebih dari sekali menjadi nama unik.
     *
     * MySQL dengan native prepared statement (EMULATE_PREPARES = false) tidak
     * mendukung satu placeholder bernama yang dipakai berulang. Penulisan ulang
     * ini membuat query dengan referensi berulang (mis. tanggal pelaporan yang
     * dipakai pada setiap bucket aging) tetap berjalan tanpa mengorbankan
     * keamanan prepared statement.
     *
     * @return array{0:string,1:array<string,int>,2:array<string,string>}
     *         [sql hasil tulis ulang, daftar placeholder, peta nama baru => nama asal]
     */
    private static function analyze(string $sql): array
    {
        static $cache = [];
        $key = md5($sql);
        if (isset($cache[$key])) return $cache[$key];

        $len = strlen($sql);
        $out = '';
        $seen = [];
        $alias = [];
        $inStr = false;
        $q = '';
        for ($i = 0; $i < $len; $i++) {
            $ch = $sql[$i];
            if ($inStr) {
                $out .= $ch;
                if ($ch === '\\' && $i + 1 < $len) { $out .= $sql[$i + 1]; $i++; continue; }
                if ($ch === $q) $inStr = false;
                continue;
            }
            if ($ch === "'" || $ch === '"' || $ch === '`') { $inStr = true; $q = $ch; $out .= $ch; continue; }
            if ($ch === ':' && $i + 1 < $len && (ctype_alpha($sql[$i + 1]) || $sql[$i + 1] === '_')
                && ($i === 0 || $sql[$i - 1] !== ':')) {
                $j = $i + 1;
                while ($j < $len && (ctype_alnum($sql[$j]) || $sql[$j] === '_')) $j++;
                $name = substr($sql, $i + 1, $j - $i - 1);
                $seen[$name] = ($seen[$name] ?? 0) + 1;
                if ($seen[$name] === 1) {
                    $out .= ':' . $name;
                } else {
                    $nn = $name . '__r' . $seen[$name];
                    $alias[$nn] = $name;
                    $out .= ':' . $nn;
                }
                $i = $j - 1;
                continue;
            }
            $out .= $ch;
        }
        $names = $seen;
        foreach ($alias as $nn => $orig) $names[$nn] = 1;
        return $cache[$key] = [$out, $names, $alias];
    }

    /**
     * Menjalankan query dengan parameter terikat.
     *
     * - Placeholder bernama yang berulang ditulis ulang otomatis.
     * - Parameter yang tidak dipakai di dalam SQL diabaikan dengan aman, sehingga
     *   pemanggil boleh mengirim satu himpunan parameter untuk beberapa varian query.
     */
    public function run(string $sql, array $params = []): PDOStatement
    {
        $t0 = microtime(true);
        $isNamed = $params && !array_is_list($params);
        $allowed = null;
        if ($isNamed) {
            [$sql, $allowed, $alias] = self::analyze($sql);
            foreach ($alias as $newName => $origName) {
                $lookup = array_key_exists($origName, $params) ? $origName : ':' . $origName;
                if (array_key_exists($lookup, $params)) $params[$newName] = $params[$lookup];
            }
        }
        try {
            $st = $this->pdo->prepare($sql);
            foreach ($params as $k => $v) {
                if ($allowed !== null && !is_int($k)) {
                    $name = ltrim((string)$k, ':');
                    if (!isset($allowed[$name])) continue;
                }
                $key = is_int($k) ? $k + 1 : (str_starts_with((string)$k, ':') ? $k : ':' . $k);
                if (is_int($v))       $type = PDO::PARAM_INT;
                elseif (is_bool($v))  $type = PDO::PARAM_BOOL;
                elseif ($v === null)  $type = PDO::PARAM_NULL;
                else                  $type = PDO::PARAM_STR;
                $st->bindValue($key, $v, $type);
            }
            $st->execute();
        } catch (PDOException $e) {
            Logger::error('Query gagal: ' . $e->getMessage() . ' | SQL: ' . $sql);
            throw $e;
        }
        if ($this->logQueries) {
            $this->queryLog[] = ['sql' => $sql, 'params' => $params, 'ms' => round((microtime(true) - $t0) * 1000, 2)];
        }
        return $st;
    }

    public function all(string $sql, array $p = []): array   { return $this->run($sql, $p)->fetchAll(); }
    public function one(string $sql, array $p = []): ?array  { $r = $this->run($sql, $p)->fetch(); return $r === false ? null : $r; }
    public function value(string $sql, array $p = [])        { $r = $this->run($sql, $p)->fetch(PDO::FETCH_NUM); return $r === false ? null : $r[0]; }
    public function column(string $sql, array $p = []): array{ return $this->run($sql, $p)->fetchAll(PDO::FETCH_COLUMN, 0); }
    public function exec(string $sql, array $p = []): int    { return $this->run($sql, $p)->rowCount(); }

    public function insert(string $table, array $data): int
    {
        $cols = array_keys($data);
        $sql  = 'INSERT INTO `' . $table . '` (`' . implode('`,`', $cols) . '`) VALUES ('
              . implode(',', array_map(fn($c) => ':' . $c, $cols)) . ')';
        $this->run($sql, $data);
        return (int)$this->pdo->lastInsertId();
    }

    public function insertMany(string $table, array $cols, array $rowsets, int $chunk = 400): int
    {
        $total = 0;
        foreach (array_chunk($rowsets, $chunk) as $chunkRows) {
            $ph = '(' . implode(',', array_fill(0, count($cols), '?')) . ')';
            $sql = 'INSERT INTO `' . $table . '` (`' . implode('`,`', $cols) . '`) VALUES '
                 . implode(',', array_fill(0, count($chunkRows), $ph));
            $flat = [];
            foreach ($chunkRows as $r) foreach ($r as $v) $flat[] = $v;
            $total += $this->exec($sql, $flat);
        }
        return $total;
    }

    public function update(string $table, array $data, string $where, array $whereParams = []): int
    {
        $sets = [];
        $params = [];
        foreach ($data as $k => $v) { $sets[] = "`$k` = :s_$k"; $params["s_$k"] = $v; }
        foreach ($whereParams as $k => $v) { $params[is_int($k) ? $k : $k] = $v; }
        $sql = 'UPDATE `' . $table . '` SET ' . implode(', ', $sets) . ' WHERE ' . $where;
        return $this->exec($sql, $params);
    }

    /** Soft delete: mengisi kolom deleted_at, bukan menghapus baris. */
    public function softDelete(string $table, int $id): int
    {
        return $this->exec("UPDATE `$table` SET deleted_at = NOW() WHERE id = :id AND deleted_at IS NULL", ['id' => $id]);
    }

    // --------------------------- TRANSAKSI ---------------------------
    public function begin(): void
    {
        if ($this->txDepth === 0) $this->pdo->beginTransaction();
        else $this->pdo->exec('SAVEPOINT sp_' . $this->txDepth);
        $this->txDepth++;
    }

    public function commit(): void
    {
        if ($this->txDepth <= 0) return;
        $this->txDepth--;
        if ($this->txDepth === 0) $this->pdo->commit();
        else $this->pdo->exec('RELEASE SAVEPOINT sp_' . $this->txDepth);
    }

    public function rollback(): void
    {
        if ($this->txDepth <= 0) return;
        $this->txDepth--;
        if ($this->txDepth === 0) {
            if ($this->pdo->inTransaction()) $this->pdo->rollBack();
        } else {
            $this->pdo->exec('ROLLBACK TO SAVEPOINT sp_' . $this->txDepth);
        }
    }

    /** Menjalankan callback di dalam transaksi; rollback otomatis bila terjadi exception. */
    public function transaction(callable $fn)
    {
        $this->begin();
        try {
            $result = $fn($this);
            $this->commit();
            return $result;
        } catch (\Throwable $e) {
            $this->rollback();
            throw $e;
        }
    }

    public function tableExists(string $table): bool
    {
        $n = $this->value(
            'SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?',
            [$table]
        );
        return (int)$n > 0;
    }
}
