119 lines
4.3 KiB
PHP
119 lines
4.3 KiB
PHP
<?php
|
|
// Script untuk membuat tabel utama jika belum ada
|
|
include 'db_config.php';
|
|
|
|
$statements = [
|
|
"CREATE TABLE IF NOT EXISTS jalan_rusak (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
latitude DECIMAL(10, 8) NOT NULL,
|
|
longitude DECIMAL(11, 8) NOT NULL,
|
|
jenis_kerusakan VARCHAR(100) NOT NULL COMMENT 'Jenis: lubang, retak, ambles, dll',
|
|
deskripsi TEXT,
|
|
severity VARCHAR(50) DEFAULT 'sedang' COMMENT 'tertarik: ringan, sedang, berat',
|
|
gambar VARCHAR(255),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_coords (latitude, longitude)
|
|
)",
|
|
"CREATE TABLE IF NOT EXISTS rumah_ibadah (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama VARCHAR(255) NOT NULL,
|
|
alamat TEXT NOT NULL,
|
|
pic VARCHAR(255) NOT NULL,
|
|
latitude DECIMAL(10, 8) NOT NULL,
|
|
longitude DECIMAL(11, 8) NOT NULL,
|
|
radius_m INT NOT NULL DEFAULT 1000,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_rumah_ibadah_coords (latitude, longitude)
|
|
)",
|
|
"CREATE TABLE IF NOT EXISTS penduduk_miskin (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama_ketua_kk VARCHAR(255) NOT NULL,
|
|
jumlah_anggota INT NOT NULL,
|
|
latitude DECIMAL(10, 8) NOT NULL,
|
|
longitude DECIMAL(11, 8) NOT NULL,
|
|
rumah_ibadah_id INT DEFAULT NULL,
|
|
rumah_ibadah_nama VARCHAR(255) DEFAULT NULL,
|
|
jarak_ke_rumah_ibadah_m DECIMAL(10, 2) DEFAULT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_penduduk_coords (latitude, longitude),
|
|
INDEX idx_penduduk_rumah_ibadah (rumah_ibadah_id)
|
|
)",
|
|
"CREATE TABLE IF NOT EXISTS verifikasi_lapangan (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
jenis_target VARCHAR(50) NOT NULL DEFAULT 'penduduk_miskin',
|
|
target_nama VARCHAR(255) DEFAULT NULL,
|
|
petugas_nama VARCHAR(255) NOT NULL,
|
|
status_verifikasi VARCHAR(30) NOT NULL DEFAULT 'menunggu',
|
|
hasil_temuan TEXT,
|
|
tindak_lanjut TEXT,
|
|
foto VARCHAR(255) DEFAULT NULL,
|
|
latitude DECIMAL(10, 8) NOT NULL,
|
|
longitude DECIMAL(11, 8) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_verifikasi_coords (latitude, longitude),
|
|
INDEX idx_verifikasi_target (jenis_target, target_nama)
|
|
)",
|
|
"CREATE TABLE IF NOT EXISTS aduan_warga (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
kategori VARCHAR(100) NOT NULL,
|
|
pelapor_nama VARCHAR(255) NOT NULL,
|
|
kontak_pelapor VARCHAR(100) DEFAULT NULL,
|
|
deskripsi TEXT NOT NULL,
|
|
status_tindak_lanjut VARCHAR(30) NOT NULL DEFAULT 'baru',
|
|
tindak_lanjut TEXT,
|
|
foto VARCHAR(255) DEFAULT NULL,
|
|
latitude DECIMAL(10, 8) NOT NULL,
|
|
longitude DECIMAL(11, 8) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_aduan_coords (latitude, longitude),
|
|
INDEX idx_aduan_status (status_tindak_lanjut)
|
|
)",
|
|
"CREATE TABLE IF NOT EXISTS locations (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama VARCHAR(255) NOT NULL,
|
|
nomor VARCHAR(100) NOT NULL,
|
|
buka_24jam VARCHAR(10) NOT NULL DEFAULT 'tidak',
|
|
latitude DECIMAL(10, 8) NOT NULL,
|
|
longitude DECIMAL(11, 8) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_locations_coords (latitude, longitude)
|
|
)",
|
|
"CREATE TABLE IF NOT EXISTS spatial_features (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama_objek VARCHAR(255) NOT NULL,
|
|
kategori VARCHAR(100) NOT NULL,
|
|
status_objek VARCHAR(100) NOT NULL,
|
|
type VARCHAR(50) NOT NULL,
|
|
geometry_data LONGTEXT NOT NULL,
|
|
nilai_ukur DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_features_kategori (kategori)
|
|
)"
|
|
];
|
|
|
|
foreach ($statements as $sql) {
|
|
if ($conn->query($sql) !== TRUE) {
|
|
echo json_encode(['status' => 'error', 'message' => $conn->error]);
|
|
$conn->close();
|
|
exit;
|
|
}
|
|
}
|
|
|
|
$alterStatements = [
|
|
"ALTER TABLE penduduk_miskin ADD COLUMN bantuan_status VARCHAR(20) NOT NULL DEFAULT 'belum' AFTER jarak_ke_rumah_ibadah_m",
|
|
"ALTER TABLE penduduk_miskin ADD COLUMN bantuan_catatan TEXT DEFAULT NULL AFTER bantuan_status",
|
|
"ALTER TABLE penduduk_miskin ADD COLUMN bantuan_updated_at TIMESTAMP NULL DEFAULT NULL AFTER bantuan_catatan"
|
|
];
|
|
|
|
foreach ($alterStatements as $alterSql) {
|
|
try {
|
|
@$conn->query($alterSql);
|
|
} catch (Exception $e) {
|
|
// Abaikan jika kolom sudah ada
|
|
}
|
|
}
|
|
|
|
echo json_encode(['status' => 'success', 'message' => 'Tabel berhasil dibuat atau sudah ada']);
|
|
$conn->close();
|
|
?>
|