128 lines
4.5 KiB
SQL
128 lines
4.5 KiB
SQL
CREATE TABLE spbu_markers (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama_spbu VARCHAR(100) NOT NULL,
|
|
status_operasional ENUM('Buka 24 Jam', 'Tidak 24 Jam') NOT NULL,
|
|
no_telp VARCHAR(20) NOT NULL,
|
|
latitude DECIMAL(10, 8) NOT NULL,
|
|
longitude DECIMAL(11, 8) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE TABLE jalan_polylines (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama_jalan VARCHAR(100) NOT NULL,
|
|
status_jalan ENUM('Jalan Nasional', 'Jalan Provinsi', 'Jalan Kabupaten') NOT NULL,
|
|
jarak_meter INT NOT NULL,
|
|
koordinat JSON NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE TABLE tanah_polygons (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama_pemilik VARCHAR(100) NOT NULL,
|
|
status_kepemilikan ENUM('SHM', 'HGB', 'HGU', 'HP') NOT NULL,
|
|
luas_meter INT NOT NULL,
|
|
koordinat JSON NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
ALTER TABLE spbu_markers
|
|
ADD COLUMN alamat TEXT AFTER no_telp;
|
|
CREATE TABLE rumah_ibadah (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama VARCHAR(150) NOT NULL,
|
|
jenis ENUM('Masjid', 'Gereja', 'Vihara', 'Pura', 'Klenteng') NOT NULL,
|
|
radius_meter INT NOT NULL,
|
|
alamat TEXT,
|
|
latitude DOUBLE NOT NULL,
|
|
longitude DOUBLE NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE rumah_penduduk (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama_kk VARCHAR(100) NOT NULL,
|
|
agama VARCHAR(50),
|
|
jml_tanggungan INT DEFAULT 0,
|
|
pengeluaran INT NOT NULL,
|
|
pmt_lantai INT DEFAULT 0,
|
|
pmt_dinding INT DEFAULT 0,
|
|
pmt_sanitasi INT DEFAULT 0,
|
|
pmt_penerangan INT DEFAULT 0,
|
|
pmt_air INT DEFAULT 0,
|
|
pmt_score INT NOT NULL,
|
|
status_kemiskinan ENUM('Sangat Miskin', 'Miskin', 'Rentan Miskin', 'Tidak Miskin') NOT NULL,
|
|
alamat TEXT,
|
|
latitude DOUBLE NOT NULL,
|
|
longitude DOUBLE NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE log_bantuan (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
id_penduduk INT NOT NULL,
|
|
beras_kg INT DEFAULT 0,
|
|
minyak_l INT DEFAULT 0,
|
|
gula_kg INT DEFAULT 0,
|
|
telur_kg INT DEFAULT 0,
|
|
susu_kaleng INT DEFAULT 0,
|
|
tunai_rp INT DEFAULT 0,
|
|
catatan TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (id_penduduk) REFERENCES rumah_penduduk(id) ON DELETE CASCADE
|
|
);
|
|
CREATE TABLE IF NOT EXISTS garis_kemiskinan (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
tahun YEAR NOT NULL,
|
|
nilai_rupiah INT NOT NULL,
|
|
is_active BOOLEAN DEFAULT FALSE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
INSERT INTO garis_kemiskinan (tahun, nilai_rupiah, is_active)
|
|
VALUES (2026, 700000, TRUE);
|
|
|
|
CREATE TABLE master_pelatihan (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama_pelatihan VARCHAR(255) NOT NULL,
|
|
instansi_penyelenggara VARCHAR(255) NOT NULL,
|
|
tanggal_mulai DATE NOT NULL,
|
|
tanggal_selesai DATE NOT NULL,
|
|
deskripsi TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE anggota_keluarga (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
id_penduduk INT NOT NULL,
|
|
nik VARCHAR(16) NOT NULL UNIQUE,
|
|
nama_lengkap VARCHAR(255) NOT NULL,
|
|
tanggal_lahir DATE NOT NULL,
|
|
pendidikan_terakhir ENUM('Tidak Sekolah', 'SD', 'SMP', 'SMA/SMK', 'D3', 'S1', 'S2', 'S3') NOT NULL,
|
|
pekerjaan VARCHAR(255) DEFAULT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (id_penduduk) REFERENCES rumah_penduduk(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE log_pelatihan (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
id_anggota INT NOT NULL,
|
|
id_pelatihan INT NOT NULL,
|
|
tanggal_daftar TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (id_anggota) REFERENCES anggota_keluarga(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (id_pelatihan) REFERENCES master_pelatihan(id) ON DELETE CASCADE
|
|
);
|
|
CREATE TABLE laporan_musibah (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
id_penduduk INT NOT NULL,
|
|
tanggal_lapor DATE NOT NULL,
|
|
jenis_musibah ENUM('Kecelakaan', 'Kematian', 'Sakit Keras', 'Kebakaran', 'Bencana Alam', 'Lainnya') NOT NULL,
|
|
deskripsi TEXT,
|
|
status_penanganan ENUM('Belum Ditangani', 'Sudah Ditangani') DEFAULT 'Belum Ditangani',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (id_penduduk) REFERENCES rumah_penduduk(id) ON DELETE CASCADE
|
|
);
|
|
CREATE TABLE users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|
password VARCHAR(255) NOT NULL,
|
|
role VARCHAR(20) DEFAULT 'admin',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
); |