<?php
$conn = mysqli_connect("127.0.0.1", "root", "", "webgis_kemiskinan");

if (!$conn) {
    die("Koneksi gagal: " . mysqli_connect_error());
}

mysqli_set_charset($conn, "utf8mb4");

// Helper: tambah kolom hanya jika belum ada
function addColumnIfNotExists($conn, $table, $column, $definition)
{
    $db = mysqli_select_db($conn, 'webgis_kemiskinan') ? 'webgis_kemiskinan' : '';
    $check = mysqli_query(
        $conn,
        "SELECT COUNT(*) as cnt FROM information_schema.COLUMNS
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '$table' AND COLUMN_NAME = '$column'"
    );
    if ($check) {
        $row = mysqli_fetch_assoc($check);
        if ((int) $row['cnt'] === 0) {
            mysqli_query($conn, "ALTER TABLE `$table` ADD COLUMN `$column` $definition");
        }
    }
}

$bootstrapQueries = [
    // ── Tabel: kemiskinan
    "CREATE TABLE IF NOT EXISTS kemiskinan (
        id                    INT AUTO_INCREMENT PRIMARY KEY,
        nama_kepala_keluarga  VARCHAR(255) NOT NULL,
        jumlah_kk             INT          NOT NULL,
        alamat                TEXT         NOT NULL,
        latitude              DECIMAL(10,8) NOT NULL,
        longitude             DECIMAL(11,8) NOT NULL,
        status_keluarga       ENUM('aktif','meninggal','pindah','sudah_mampu') NOT NULL DEFAULT 'aktif',
        created_at            TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
        updated_at            TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    // ── Tabel: masjid (rumah ibadah)
    "CREATE TABLE IF NOT EXISTS masjid (
        id            INT AUTO_INCREMENT PRIMARY KEY,
        nama_masjid   VARCHAR(255) NOT NULL,
        pic_masjid    VARCHAR(255) NOT NULL,
        alamat        TEXT         NOT NULL,
        latitude      DECIMAL(10,8) NOT NULL,
        longitude     DECIMAL(11,8) NOT NULL,
        radius        INT          NOT NULL DEFAULT 500,
        jenis_ibadah  ENUM('masjid','gereja','vihara','pura','klenteng','kapel','lainnya') NOT NULL DEFAULT 'masjid',
        created_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    // ── Tabel: pengguna (login multi-role)
    "CREATE TABLE IF NOT EXISTS pengguna (
        id            INT AUTO_INCREMENT PRIMARY KEY,
        username      VARCHAR(100) NOT NULL UNIQUE,
        password_hash VARCHAR(255) NOT NULL,
        nama_lengkap  VARCHAR(255) NOT NULL,
        role          ENUM('pengurus_masjid','pimpinan_daerah') NOT NULL DEFAULT 'pengurus_masjid',
        masjid_id     INT NULL,
        aktif         TINYINT(1) NOT NULL DEFAULT 1,
        created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    // ── Tabel: anggota_keluarga (individu per KK)
    "CREATE TABLE IF NOT EXISTS anggota_keluarga (
        id                  INT AUTO_INCREMENT PRIMARY KEY,
        kemiskinan_id       INT NOT NULL,
        nama                VARCHAR(255) NOT NULL,
        tanggal_lahir       DATE NULL,
        jenis_kelamin       ENUM('laki_laki','perempuan') NOT NULL DEFAULT 'laki_laki',
        hubungan_keluarga   ENUM('kepala_keluarga','istri','anak','orang_tua','saudara','lainnya') NOT NULL DEFAULT 'anak',
        status_hidup        ENUM('hidup','meninggal','pindah','menikah_keluar') NOT NULL DEFAULT 'hidup',
        pendidikan          ENUM('belum_sekolah','paud','sd','smp','sma','kuliah','pesantren','pelatihan','bekerja','tidak_sekolah') NOT NULL DEFAULT 'belum_sekolah',
        catatan_khusus      TEXT NULL,
        updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (kemiskinan_id) REFERENCES kemiskinan(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    // ── Tabel: histori_bantuan
    "CREATE TABLE IF NOT EXISTS histori_bantuan (
        id                  INT AUTO_INCREMENT PRIMARY KEY,
        kemiskinan_id       INT NOT NULL,
        masjid_id           INT NULL,
        jenis_bantuan       ENUM('sembako','uang','pendidikan','kesehatan','pakaian','pelatihan','lainnya') NOT NULL DEFAULT 'lainnya',
        deskripsi_bantuan   TEXT NOT NULL,
        jumlah_bantuan      DECIMAL(10,2) NULL,
        satuan              VARCHAR(50) NULL COMMENT 'e.g. karung, kg, Rp, bulan',
        tanggal_bantuan     DATE NOT NULL,
        periode_berikutnya  DATE NULL COMMENT 'Untuk bantuan berkala',
        status_penyaluran   ENUM('tersalur','belum','batal') NOT NULL DEFAULT 'belum',
        dicatat_oleh        VARCHAR(255) NULL,
        created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (kemiskinan_id) REFERENCES kemiskinan(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    // ── Tabel: laporan_kondisi (dari warga)
    "CREATE TABLE IF NOT EXISTS laporan_kondisi (
        id              INT AUTO_INCREMENT PRIMARY KEY,
        kemiskinan_id   INT NULL,
        nama_pelapor    VARCHAR(255) NOT NULL,
        nomor_wa        VARCHAR(20) NULL,
        kategori        ENUM('sakit','kematian','kelahiran','pernikahan','pindah','darurat_ekonomi','lainnya') NOT NULL DEFAULT 'lainnya',
        deskripsi       TEXT NOT NULL,
        status_laporan  ENUM('menunggu','diproses','selesai','ditolak') NOT NULL DEFAULT 'menunggu',
        ditangani_oleh  VARCHAR(255) NULL,
        created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    // ── Tabel: pesan_komunikasi
    "CREATE TABLE IF NOT EXISTS pesan_komunikasi (
        id           INT AUTO_INCREMENT PRIMARY KEY,
        pengirim_id  INT NOT NULL,
        penerima_id  INT NULL COMMENT 'NULL = broadcast ke semua',
        judul        VARCHAR(255) NOT NULL,
        isi          TEXT NOT NULL,
        jenis        ENUM('permintaan_bantuan','koordinasi','pengumuman') NOT NULL DEFAULT 'koordinasi',
        status       ENUM('baru','dibaca','diarsip') NOT NULL DEFAULT 'baru',
        created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
];

foreach ($bootstrapQueries as $query) {
    mysqli_query($conn, $query);
}

// ── Migrasi aman: tambah kolom baru ke tabel yang sudah ada
addColumnIfNotExists(
    $conn,
    'kemiskinan',
    'status_keluarga',
    "ENUM('aktif','meninggal','pindah','sudah_mampu') NOT NULL DEFAULT 'aktif'"
);
addColumnIfNotExists(
    $conn,
    'kemiskinan',
    'updated_at',
    "TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
);
addColumnIfNotExists(
    $conn,
    'masjid',
    'jenis_ibadah',
    "ENUM('masjid','gereja','vihara','pura','klenteng','kapel','lainnya') NOT NULL DEFAULT 'masjid'"
);

// ── Seed akun default jika tabel pengguna masih kosong
$checkUser = mysqli_query($conn, "SELECT COUNT(*) as total FROM pengguna");
if ($checkUser) {
    $row = mysqli_fetch_assoc($checkUser);
    if ((int) $row['total'] === 0) {
        $defaultUsers = [
            ['pengurus1', password_hash('password', PASSWORD_DEFAULT), 'Pengurus Masjid Al-Ikhlas', 'pengurus_masjid'],
            ['pimpinan1', password_hash('password', PASSWORD_DEFAULT), 'Kepala Dinas Sosial', 'pimpinan_daerah'],
        ];
        $stmt = mysqli_prepare($conn, "INSERT INTO pengguna (username, password_hash, nama_lengkap, role) VALUES (?, ?, ?, ?)");
        if ($stmt) {
            foreach ($defaultUsers as $u) {
                mysqli_stmt_bind_param($stmt, 'ssss', $u[0], $u[1], $u[2], $u[3]);
                mysqli_stmt_execute($stmt);
            }
            mysqli_stmt_close($stmt);
        }
    }
}

// ── Helper: cek autentikasi (panggil di endpoint yang butuh login)
function requireAuth($requiredRole = null)
{
    if (session_status() === PHP_SESSION_NONE) {
        session_start();
    }
    if (empty($_SESSION['user_id'])) {
        http_response_code(401);
        header('Content-Type: application/json');
        echo json_encode(['error' => 'Tidak terautentikasi. Silakan login.']);
        exit;
    }
    if ($requiredRole && $_SESSION['user_role'] !== $requiredRole) {
        http_response_code(403);
        header('Content-Type: application/json');
        echo json_encode(['error' => 'Akses ditolak. Role tidak sesuai.']);
        exit;
    }
}

function isLoggedIn()
{
    if (session_status() === PHP_SESSION_NONE) {
        session_start();
    }
    return !empty($_SESSION['user_id']);
}

function currentRole()
{
    if (session_status() === PHP_SESSION_NONE) {
        session_start();
    }
    return $_SESSION['user_role'] ?? null;
}
?>