96 lines
2.3 KiB
SQL
96 lines
2.3 KiB
SQL
CREATE TABLE religious_centers (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
address TEXT,
|
|
kas DECIMAL(15,2) DEFAULT 0,
|
|
latitude DOUBLE NOT NULL,
|
|
longitude DOUBLE NOT NULL,
|
|
radius INT DEFAULT 300
|
|
);
|
|
|
|
CREATE TABLE houses (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
latitude DOUBLE NOT NULL,
|
|
longitude DOUBLE NOT NULL,
|
|
address TEXT,
|
|
rt VARCHAR(20),
|
|
rw VARCHAR(20),
|
|
kelurahan VARCHAR(100),
|
|
status_miskin VARCHAR(50),
|
|
jumlah_anggota INT DEFAULT 0,
|
|
anggota LONGTEXT,
|
|
aid_status VARCHAR(50) DEFAULT 'outside',
|
|
has_data TINYINT(1) DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE laporan (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
pelapor VARCHAR(255) DEFAULT 'Anonim',
|
|
deskripsi TEXT NOT NULL,
|
|
foto_base64 LONGTEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE aid_logs (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
house_id INT,
|
|
religious_center_id INT,
|
|
status VARCHAR(50),
|
|
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE `password_resets` (
|
|
`id` int(11) NOT NULL,
|
|
`user_id` int(11) NOT NULL,
|
|
`email` varchar(255) NOT NULL,
|
|
`status` enum('pending','done') DEFAULT 'pending',
|
|
`created_at` timestamp NOT NULL DEFAULT current_timestamp()
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
CREATE TABLE `users` (
|
|
`id` int(11) NOT NULL,
|
|
`nama` varchar(255) NOT NULL,
|
|
`email` varchar(255) NOT NULL,
|
|
`password` varchar(255) NOT NULL,
|
|
`role` enum('admin','surveyer','viewer') DEFAULT 'viewer',
|
|
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
|
|
`status` enum('pending','approved','rejected') DEFAULT 'pending'
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
ALTER TABLE `password_resets`
|
|
ADD PRIMARY KEY (`id`),
|
|
ADD KEY `user_id` (`user_id`);
|
|
|
|
--
|
|
-- Indexes for table `users`
|
|
--
|
|
ALTER TABLE `users`
|
|
ADD PRIMARY KEY (`id`),
|
|
ADD UNIQUE KEY `email` (`email`);
|
|
|
|
--
|
|
|
|
--
|
|
-- AUTO_INCREMENT for table `password_resets`
|
|
--
|
|
ALTER TABLE `password_resets`
|
|
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
|
|
|
|
--
|
|
-- AUTO_INCREMENT for table `users`
|
|
--
|
|
ALTER TABLE `users`
|
|
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
|
|
|
|
--
|
|
-- Constraints for dumped tables
|
|
--
|
|
|
|
--
|
|
-- Constraints for table `password_resets`
|
|
--
|
|
ALTER TABLE `password_resets`
|
|
ADD CONSTRAINT `password_resets_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
|
|
COMMIT;
|
|
|