<?php
declare(strict_types=1);

function initializeDatabase(): array
{
    require_once __DIR__ . '/../backend/koneksi.php';

    $db = getConnection();

    $statements = [
        "CREATE TABLE IF NOT EXISTS spbu (
            id INT NOT NULL AUTO_INCREMENT,
            nama VARCHAR(150) NOT NULL,
            no_spbu VARCHAR(50) NOT NULL,
            status ENUM('24jam', 'tidak') NOT NULL DEFAULT 'tidak',
            latitude DECIMAL(10, 8) NOT NULL,
            longitude DECIMAL(11, 8) NOT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

        "CREATE TABLE IF NOT EXISTS jalan (
            id INT NOT NULL AUTO_INCREMENT,
            nama_jalan VARCHAR(150) NOT NULL,
            status ENUM('Nasional', 'Provinsi', 'Kabupaten') NOT NULL,
            panjang DECIMAL(10, 2) NOT NULL,
            geom LONGTEXT NOT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

        "CREATE TABLE IF NOT EXISTS parsil (
            id INT NOT NULL AUTO_INCREMENT,
            nama_pemilik VARCHAR(150) NOT NULL,
            status ENUM('SHM', 'HGB', 'HGU', 'HP') NOT NULL,
            luas DECIMAL(12, 2) NOT NULL,
            geom LONGTEXT NOT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

        "CREATE TABLE IF NOT EXISTS rumah_ibadah (
            id INT NOT NULL AUTO_INCREMENT,
            nama VARCHAR(150) NOT NULL,
            alamat TEXT NOT NULL,
            latitude DECIMAL(10, 8) NOT NULL,
            longitude DECIMAL(11, 8) NOT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

        "CREATE TABLE IF NOT EXISTS penduduk_miskin (
            id INT NOT NULL AUTO_INCREMENT,
            nama_kk VARCHAR(150) NOT NULL,
            keterangan TEXT,
            latitude DECIMAL(10, 8) NOT NULL,
            longitude DECIMAL(11, 8) NOT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

        "CREATE TABLE IF NOT EXISTS users (
            id INT NOT NULL AUTO_INCREMENT,
            username VARCHAR(50) NOT NULL UNIQUE,
            password VARCHAR(255) NOT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
    ];

    foreach ($statements as $statement) {
        $db->query($statement);
    }

    $adminPassword = getenv('ADMIN_PASSWORD') ?: 'admin123';
    $adminHash = password_hash($adminPassword, PASSWORD_DEFAULT);

    $adminResult = $db->query("SELECT password FROM users WHERE username = 'admin' LIMIT 1");
    $adminAction = 'unchanged';
    if ($adminResult->num_rows === 0) {
        $stmt = $db->prepare("INSERT INTO users (username, password) VALUES ('admin', ?)");
        $stmt->bind_param('s', $adminHash);
        $stmt->execute();
        $adminAction = 'created';
    } else {
        $currentHash = (string) $adminResult->fetch_assoc()['password'];
        if (!password_verify($adminPassword, $currentHash)) {
            $stmt = $db->prepare("UPDATE users SET password = ? WHERE username = 'admin'");
            $stmt->bind_param('s', $adminHash);
            $stmt->execute();
            $adminAction = 'updated';
        }
    }

    $seededSpbu = 0;
    $count = (int) $db->query('SELECT COUNT(*) AS total FROM spbu')->fetch_assoc()['total'];
    if ($count === 0) {
        $seed = $db->prepare(
            "INSERT INTO spbu (nama, no_spbu, status, latitude, longitude)
             VALUES (?, ?, ?, ?, ?)"
        );

        $rows = [
            ['SPBU Sudirman', '64.751.01', '24jam', -0.0260000, 109.3425000],
            ['SPBU Ahmad Yani', '64.751.02', 'tidak', -0.0310000, 109.3218000],
            ['SPBU Sutan Syahrir', '64.751.03', '24jam', -0.0195000, 109.3612000],
        ];

        foreach ($rows as [$name, $number, $status, $latitude, $longitude]) {
            $seed->bind_param('sssdd', $name, $number, $status, $latitude, $longitude);
            $seed->execute();
            $seededSpbu++;
        }
    }

    return [
        'database' => DB_NAME,
        'admin_username' => 'admin',
        'admin_password_source' => getenv('ADMIN_PASSWORD') ? 'ADMIN_PASSWORD env' : 'default',
        'admin_action' => $adminAction,
        'seeded_spbu' => $seededSpbu,
    ];
}