<?php
// ─── Savuki Drilling — Database Layer ────────────────────────────────────

// Set timezone to South Africa (UTC+2) for all date/time operations
date_default_timezone_set('Africa/Johannesburg');

class DB {
    private static ?DB $instance = null;
    private PDO $pdo;

    private function __construct() {
        $cfg = require dirname(__DIR__) . '/config.php';
        $dsn = "mysql:host={$cfg['host']};dbname={$cfg['db']};charset={$cfg['charset']}";
        $this->pdo = new PDO($dsn, $cfg['user'], $cfg['pass'], [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false,
        ]);
        // Set MySQL session timezone to match PHP
        $this->pdo->exec("SET time_zone = '+02:00'");
    }

    public static function get(): self {
        if (!self::$instance) self::$instance = new self();
        return self::$instance;
    }

    /** Run a SELECT and return all rows. */
    public function rows(string $sql, array $params = []): array {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    /** Run a SELECT and return the first row, or null. */
    public function row(string $sql, array $params = []): ?array {
        $rows = $this->rows($sql, $params);
        return $rows[0] ?? null;
    }

    /** Run a SELECT and return a single scalar value. */
    public function scalar(string $sql, array $params = []): mixed {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchColumn();
    }

    /** Run an INSERT / UPDATE / DELETE. Returns affected row count. */
    public function run(string $sql, array $params = []): int {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->rowCount();
    }

    public function lastId(): string {
        return $this->pdo->lastInsertId();
    }
}