superbia-17
123 lines
4.6 KiB
SQL
123 lines
4.6 KiB
SQL
-- Create Database
|
|
CREATE DATABASE IF NOT EXISTS `webgis_poverty` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
USE `webgis_poverty`;
|
|
|
|
-- Table: users
|
|
CREATE TABLE IF NOT EXISTS `users` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`nama` VARCHAR(255) NOT NULL,
|
|
`email` VARCHAR(255) NOT NULL UNIQUE,
|
|
`password_hash` VARCHAR(255) NOT NULL,
|
|
`role` ENUM('super_admin', 'admin_daerah', 'pengurus_ri', 'donatur', 'masyarakat') NOT NULL DEFAULT 'masyarakat',
|
|
`wilayah` VARCHAR(255) DEFAULT NULL,
|
|
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Table: rumah_ibadah
|
|
CREATE TABLE IF NOT EXISTS `rumah_ibadah` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`nama` VARCHAR(255) NOT NULL,
|
|
`tipe` VARCHAR(100) NOT NULL,
|
|
`agama` VARCHAR(100) DEFAULT NULL,
|
|
`alamat` TEXT,
|
|
`kelurahan` VARCHAR(100) DEFAULT NULL,
|
|
`kecamatan` VARCHAR(100) DEFAULT NULL,
|
|
`lat` DOUBLE NOT NULL,
|
|
`lng` DOUBLE NOT NULL,
|
|
`radius` DOUBLE DEFAULT 500,
|
|
`kapasitas` INT DEFAULT 10,
|
|
`pengurus_id` INT DEFAULT NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`pengurus_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Table: rumah_tangga
|
|
CREATE TABLE IF NOT EXISTS `rumah_tangga` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`nama_kk` VARCHAR(255) NOT NULL,
|
|
`alamat` TEXT,
|
|
`kelurahan` VARCHAR(100) DEFAULT NULL,
|
|
`kecamatan` VARCHAR(100) DEFAULT NULL,
|
|
`lat` DOUBLE NOT NULL,
|
|
`lng` DOUBLE NOT NULL,
|
|
`anggota` INT DEFAULT 1,
|
|
`penghasilan` VARCHAR(255) DEFAULT NULL,
|
|
`kondisi` VARCHAR(255) DEFAULT NULL,
|
|
`kebutuhan` TEXT,
|
|
`catatan` TEXT,
|
|
`dilaporkan_oleh` INT DEFAULT NULL,
|
|
`status` ENUM('pending', 'verified', 'rejected') DEFAULT 'pending',
|
|
`confirmations` INT DEFAULT 0,
|
|
`assigned_ri` INT DEFAULT NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`dilaporkan_oleh`) REFERENCES `users`(`id`) ON DELETE SET NULL,
|
|
FOREIGN KEY (`assigned_ri`) REFERENCES `rumah_ibadah`(`id`) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Table: tanggungan
|
|
CREATE TABLE IF NOT EXISTS `tanggungan` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`rumah_tangga_id` INT NOT NULL,
|
|
`nama` VARCHAR(255) NOT NULL,
|
|
`tanggal_lahir` DATE NOT NULL,
|
|
`jenis_kelamin` ENUM('L', 'P') DEFAULT 'L',
|
|
`hubungan` VARCHAR(100) DEFAULT NULL,
|
|
`pendidikan` VARCHAR(100) DEFAULT NULL,
|
|
`pekerjaan` VARCHAR(100) DEFAULT NULL,
|
|
FOREIGN KEY (`rumah_tangga_id`) REFERENCES `rumah_tangga`(`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Table: verifikasi
|
|
CREATE TABLE IF NOT EXISTS `verifikasi` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`rumah_tangga_id` INT NOT NULL,
|
|
`user_id` INT NOT NULL,
|
|
`aksi` ENUM('confirm', 'reject', 'admin_approve') NOT NULL,
|
|
`alasan` TEXT,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`rumah_tangga_id`) REFERENCES `rumah_tangga`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Table: program_bantuan
|
|
CREATE TABLE IF NOT EXISTS `program_bantuan` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`ri_id` INT NOT NULL,
|
|
`nama` VARCHAR(255) NOT NULL,
|
|
`tipe` VARCHAR(100) DEFAULT 'Pangan',
|
|
`deskripsi` TEXT,
|
|
`target` INT DEFAULT 10,
|
|
`tanggal` DATE NOT NULL,
|
|
`status` VARCHAR(50) DEFAULT 'Aktif',
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`ri_id`) REFERENCES `rumah_ibadah`(`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Table: donasi
|
|
CREATE TABLE IF NOT EXISTS `donasi` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`donatur_id` INT NOT NULL,
|
|
`ri_id` INT NOT NULL,
|
|
`jumlah` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
|
|
`keterangan` TEXT,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`donatur_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`ri_id`) REFERENCES `rumah_ibadah`(`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Table: notifikasi
|
|
CREATE TABLE IF NOT EXISTS `notifikasi` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`pesan` TEXT NOT NULL,
|
|
`tipe` VARCHAR(50) DEFAULT 'info',
|
|
`sudah_baca` TINYINT(1) DEFAULT 0,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Insert default Super Admin (Password: admin123 -> SHA256)
|
|
-- Hash for 'admin123' is 8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918
|
|
INSERT INTO `users` (`nama`, `email`, `password_hash`, `role`, `wilayah`, `is_active`)
|
|
VALUES ('Super Admin', 'admin@webgis.com', '8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918', 'super_admin', 'Semua', 1)
|
|
ON DUPLICATE KEY UPDATE `id`=`id`;
|