<?php

$dbHost = 'localhost';
$dbName = 'bsukgdtq_db_gatepass';
$dbUser = 'bsukgdtq_bsukgdtq';
$dbPass = '09204353341_Account';

date_default_timezone_set('Asia/Singapore');
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $conn = new mysqli($dbHost, $dbUser, $dbPass, $dbName);
    $conn->set_charset('utf8mb4');
} catch (mysqli_sql_exception $e) {
    http_response_code(500);
    exit('Database connection failed. Import database.sql in phpMyAdmin and check config/database.php.');
}

function db_all(mysqli $conn, string $sql, string $types = '', array $params = []): array
{
    if ($types === '') {
        $result = $conn->query($sql);
        return $result ? $result->fetch_all(MYSQLI_ASSOC) : [];
    }

    $stmt = $conn->prepare($sql);
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $result = $stmt->get_result();
    $rows = $result ? $result->fetch_all(MYSQLI_ASSOC) : [];
    $stmt->close();

    return $rows;
}

function db_one(mysqli $conn, string $sql, string $types = '', array $params = []): ?array
{
    $rows = db_all($conn, $sql, $types, $params);
    return $rows[0] ?? null;
}

function db_value(mysqli $conn, string $sql, string $types = '', array $params = []): mixed
{
    $row = db_one($conn, $sql, $types, $params);

    if (!$row) {
        return null;
    }

    return array_values($row)[0] ?? null;
}

function db_execute(mysqli $conn, string $sql, string $types = '', array $params = []): int
{
    if ($types === '') {
        $conn->query($sql);
        return $conn->affected_rows;
    }

    $stmt = $conn->prepare($sql);
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $affectedRows = $stmt->affected_rows;
    $stmt->close();

    return $affectedRows;
}
