<?php
// ============================================================
// KONFIGURASI DATABASE – WebGIS SPBU
// File: config.php
// ============================================================

define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', '');
define('DB_NAME', 'webgis_spbu');

function jsonError($message, $code = 500) {
    http_response_code($code);
    echo json_encode(['error' => $message]);
    exit();
}

function getDB() {
    mysqli_report(MYSQLI_REPORT_OFF);

    $conn = new mysqli(DB_HOST, DB_USER, DB_PASS);
    if ($conn->connect_error) {
        http_response_code(500);
        die(json_encode(['error' => 'Koneksi MySQL gagal: ' . $conn->connect_error]));
    }

    $dbName = DB_NAME;
    if (!$conn->query("CREATE DATABASE IF NOT EXISTS `{$dbName}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")) {
        http_response_code(500);
        die(json_encode(['error' => 'Gagal membuat database: ' . $conn->error]));
    }

    if (!$conn->select_db($dbName)) {
        http_response_code(500);
        die(json_encode(['error' => 'Gagal memilih database: ' . $conn->error]));
    }

    $conn->set_charset('utf8mb4');
    return $conn;
}

function ensureSpbuSchema($db) {
    if (!$db->query("
        CREATE TABLE IF NOT EXISTS data_spbu (
            id          INT AUTO_INCREMENT PRIMARY KEY,
            nama_spbu   VARCHAR(200) NOT NULL,
            alamat      VARCHAR(300) NOT NULL DEFAULT '',
            no_wa       VARCHAR(20)  NOT NULL,
            buka_24_jam ENUM('Ya','Tidak') NOT NULL DEFAULT 'Tidak',
            latitude    DOUBLE NOT NULL,
            longitude   DOUBLE NOT NULL,
            created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ")) {
        jsonError('Gagal membuat tabel data_spbu: ' . $db->error);
    }

    $check = $db->query("SHOW COLUMNS FROM data_spbu LIKE 'alamat'");
    if ($check && $check->num_rows === 0) {
        $db->query("ALTER TABLE data_spbu ADD COLUMN alamat VARCHAR(300) NOT NULL DEFAULT '' AFTER nama_spbu");
        $db->query("UPDATE data_spbu SET alamat = CONCAT(nama_spbu, ', Pontianak') WHERE alamat = ''");
    }

    $countRes = $db->query("SELECT COUNT(*) AS total FROM data_spbu");
    if ($countRes && (int)$countRes->fetch_assoc()['total'] === 0) {
        seedSpbuSampleData($db);
    }
}

function seedSpbuSampleData($db) {
    $samples = [
        ['SPBU Jl. Ahmad Yani',       'Jl. Ahmad Yani No. 12, Pontianak',       '0812-1111-0001', 'Ya',    -0.0400, 109.3200],
        ['SPBU Jl. Gajah Mada',       'Jl. Gajah Mada No. 45, Pontianak',       '0812-1111-0002', 'Ya',    -0.0520, 109.3450],
        ['SPBU Jl. Diponegoro',       'Jl. Diponegoro No. 88, Pontianak',       '0812-1111-0003', 'Tidak', -0.0610, 109.3600],
        ['SPBU Jl. Sutan Syahrir',    'Jl. Sutan Syahrir No. 5, Pontianak',     '0812-1111-0004', 'Tidak', -0.0480, 109.3700],
        ['SPBU Jl. Kom. Yos Sudarso', 'Jl. Kom. Yos Sudarso No. 22, Pontianak', '0812-1111-0005', 'Ya',    -0.0350, 109.3550],
        ['SPBU Jl. Tanjung Raya',     'Jl. Tanjung Raya No. 101, Pontianak',   '0812-1111-0006', 'Tidak', -0.0580, 109.3300],
        ['SPBU Jl. Veteran',          'Jl. Veteran No. 33, Pontianak',          '0812-1111-0007', 'Ya',    -0.0450, 109.3380],
    ];

    $stmt = $db->prepare(
        "INSERT INTO data_spbu (nama_spbu, alamat, no_wa, buka_24_jam, latitude, longitude)
         VALUES (?, ?, ?, ?, ?, ?)"
    );

    foreach ($samples as $row) {
        [$nama, $alamat, $wa, $jam, $lat, $lng] = $row;
        $stmt->bind_param('ssssdd', $nama, $alamat, $wa, $jam, $lat, $lng);
        $stmt->execute();
    }
    $stmt->close();
}

header('Content-Type: application/json; charset=utf-8');
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, POST, PUT, DELETE, OPTIONS');
header('Access-Control-Allow-Headers: Content-Type');

if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') {
    http_response_code(200);
    exit();
}