ilham_gmail
178 lines
7.4 KiB
SQL
178 lines
7.4 KiB
SQL
-- -----------------------------------------------------
|
|
-- DATABASE 1: poverty_db (Poverty Mapping Project)
|
|
-- -----------------------------------------------------
|
|
CREATE DATABASE IF NOT EXISTS poverty_db;
|
|
USE poverty_db;
|
|
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
DROP TABLE IF EXISTS bantuan_detail;
|
|
DROP TABLE IF EXISTS lokasi;
|
|
DROP TABLE IF EXISTS users;
|
|
DROP TABLE IF EXISTS migrations;
|
|
DROP TABLE IF EXISTS verification_attempts;
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
|
|
CREATE TABLE users (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
email VARCHAR(255) NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
name VARCHAR(255) DEFAULT NULL,
|
|
organization VARCHAR(255) DEFAULT NULL,
|
|
org_address TEXT DEFAULT NULL,
|
|
org_phone VARCHAR(32) DEFAULT NULL,
|
|
org_proof_path VARCHAR(255) DEFAULT NULL,
|
|
role ENUM('manager','admin') NOT NULL DEFAULT 'manager',
|
|
status ENUM('pending','active','rejected','pending_verification') NOT NULL DEFAULT 'pending',
|
|
email_verified TINYINT(1) NOT NULL DEFAULT 0,
|
|
email_verification_token_hash VARCHAR(128) DEFAULT NULL,
|
|
email_verification_expires DATETIME DEFAULT NULL,
|
|
email_verification_sent_at DATETIME DEFAULT NULL,
|
|
email_verification_attempts INT NOT NULL DEFAULT 0,
|
|
email_verification_last_sent DATETIME DEFAULT NULL,
|
|
email_verification_locked_until DATETIME DEFAULT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
approved_by INT UNSIGNED DEFAULT NULL,
|
|
approved_at DATETIME DEFAULT NULL,
|
|
rejection_reason TEXT DEFAULT NULL,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uq_users_email (email),
|
|
KEY idx_users_status (status),
|
|
KEY idx_users_role (role),
|
|
KEY idx_users_approved_by (approved_by),
|
|
CONSTRAINT fk_users_approved_by FOREIGN KEY (approved_by) REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE verification_attempts (
|
|
email VARCHAR(255) NOT NULL,
|
|
attempts INT NOT NULL DEFAULT 0,
|
|
last_attempt_at DATETIME NOT NULL,
|
|
locked_until DATETIME DEFAULT NULL,
|
|
code_hash VARCHAR(128) DEFAULT NULL,
|
|
expires_at DATETIME DEFAULT NULL,
|
|
PRIMARY KEY (email)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Insert Admin and Manager demo accounts (Password: password)
|
|
INSERT INTO users (email, password_hash, name, organization, role, status, email_verified)
|
|
VALUES
|
|
('admin@example.com', '$2y$10$7YbX4D/QUo1OFJw.ZxyDl.plX03wY/7POuPNx3/ZfyX0vWwaKPNR.', 'Admin Demo', 'SIG WebGIS', 'admin', 'active', 1),
|
|
('manager@example.com', '$2y$10$7YbX4D/QUo1OFJw.ZxyDl.plX03wY/7POuPNx3/ZfyX0vWwaKPNR.', 'Manager Demo', 'Pemberdayaan Masyarakat', 'manager', 'active', 1)
|
|
ON DUPLICATE KEY UPDATE password_hash = VALUES(password_hash), status = 'active';
|
|
|
|
CREATE TABLE lokasi (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
nama VARCHAR(255) NOT NULL,
|
|
no_telp VARCHAR(32) DEFAULT NULL,
|
|
buka_24_jam TINYINT(1) NOT NULL DEFAULT 0,
|
|
latitude DECIMAL(10,6) DEFAULT NULL,
|
|
longitude DECIMAL(10,6) DEFAULT NULL,
|
|
alamat TEXT,
|
|
jenis VARCHAR(50) NOT NULL,
|
|
nama_kk VARCHAR(255) DEFAULT NULL,
|
|
rumah_status VARCHAR(100) DEFAULT NULL,
|
|
members INT DEFAULT NULL,
|
|
monthly_income DECIMAL(18,0) DEFAULT NULL,
|
|
assisted TINYINT(1) NOT NULL DEFAULT 0,
|
|
last_assisted_at DATETIME DEFAULT NULL,
|
|
assistance_notes TEXT DEFAULT NULL,
|
|
photo_path VARCHAR(255) DEFAULT NULL,
|
|
sumber_bantuan INT UNSIGNED DEFAULT NULL,
|
|
bentuk_bantuan VARCHAR(100) DEFAULT NULL,
|
|
created_by INT UNSIGNED DEFAULT NULL,
|
|
PRIMARY KEY (id),
|
|
KEY idx_lokasi_assisted (assisted),
|
|
KEY idx_lokasi_jenis (jenis),
|
|
KEY idx_lokasi_sumber_bantuan (sumber_bantuan),
|
|
KEY idx_lokasi_created_by (created_by),
|
|
KEY idx_lokasi_last_assisted_at (last_assisted_at),
|
|
CONSTRAINT fk_lokasi_sumber_bantuan FOREIGN KEY (sumber_bantuan) REFERENCES lokasi (id) ON DELETE SET NULL ON UPDATE CASCADE,
|
|
CONSTRAINT fk_lokasi_created_by FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE bantuan_detail (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
lokasi_id INT UNSIGNED NOT NULL,
|
|
tanggal_bantuan DATE DEFAULT NULL,
|
|
pemberi_bantuan VARCHAR(255) DEFAULT NULL,
|
|
catatan TEXT DEFAULT NULL,
|
|
pemberi_lokasi_id INT UNSIGNED DEFAULT NULL,
|
|
jumlah INT NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id),
|
|
KEY idx_bantuan_lokasi (lokasi_id),
|
|
KEY idx_bantuan_pemberi_lokasi (pemberi_lokasi_id),
|
|
KEY idx_bantuan_tanggal (tanggal_bantuan),
|
|
KEY idx_bantuan_lokasi_tanggal (lokasi_id, tanggal_bantuan),
|
|
KEY idx_bantuan_pemberi_tanggal (pemberi_lokasi_id, tanggal_bantuan),
|
|
CONSTRAINT fk_bantuan_lokasi FOREIGN KEY (lokasi_id) REFERENCES lokasi (id) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT fk_bantuan_pemberi_lokasi FOREIGN KEY (pemberi_lokasi_id) REFERENCES lokasi (id) ON DELETE SET NULL ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE migrations (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
name VARCHAR(255) NOT NULL,
|
|
applied_at DATETIME NOT NULL,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uq_migrations_name (name)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
INSERT INTO migrations (name, applied_at)
|
|
VALUES ('001_init_schema', CURRENT_TIMESTAMP);
|
|
|
|
|
|
-- -----------------------------------------------------
|
|
-- DATABASE 2: spbu_db (SPBU Mapping Project)
|
|
-- -----------------------------------------------------
|
|
CREATE DATABASE IF NOT EXISTS spbu_db;
|
|
USE spbu_db;
|
|
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
DROP TABLE IF EXISTS `jalan`;
|
|
DROP TABLE IF EXISTS `lokasi`;
|
|
DROP TABLE IF EXISTS `tanah`;
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
|
|
CREATE TABLE `jalan` (
|
|
`id` int NOT NULL AUTO_INCREMENT,
|
|
`nama` varchar(100) DEFAULT NULL,
|
|
`status` varchar(50) DEFAULT NULL,
|
|
`panjang` double DEFAULT NULL,
|
|
`geom` text,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE `lokasi` (
|
|
`id` varchar(8) NOT NULL,
|
|
`nama` varchar(50) NOT NULL,
|
|
`no_telp` varchar(20) DEFAULT NULL,
|
|
`buka_24_jam` tinyint(1) DEFAULT NULL,
|
|
`latitude` decimal(10,6) DEFAULT NULL,
|
|
`longitude` decimal(10,6) DEFAULT NULL,
|
|
`alamat` text,
|
|
`jenis` varchar(50) NOT NULL DEFAULT 'spbu',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_lokasi_alamat` ((left(`alamat`,255)))
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Seed SPBU map data
|
|
INSERT INTO `lokasi` VALUES
|
|
('5092','Rumah','',0,-0.040882,109.335197,'Daeng Abdul Hadi, Akcaya, Pontianak Selatan, Pontianak, Kalimantan Barat, Kalimantan, 78117, Indonesia','rumah'),
|
|
('7853','Masjid Mujahiddin','',1,-0.041462,109.336345,'Mujahiddin, Jalan Mujahidin, Akcaya, Pontianak Selatan, Pontianak, Kalimantan Barat, Kalimantan, 78121, Indonesia','masjid'),
|
|
('8476','SPBU OSO MT. Haryono','',0,-0.044863,109.336726,'SPBU OSO MT. Haryono, Jalan M.T. Haryono, Akcaya, Pontianak Selatan, Pontianak, Kalimantan Barat, Kalimantan, 78121, Indonesia','spbu')
|
|
ON DUPLICATE KEY UPDATE nama = VALUES(nama);
|
|
|
|
CREATE TABLE `tanah` (
|
|
`id` int NOT NULL AUTO_INCREMENT,
|
|
`nama` varchar(100) DEFAULT NULL,
|
|
`status` varchar(50) DEFAULT NULL,
|
|
`luas` double DEFAULT NULL,
|
|
`geom` text,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Grant privileges on both databases to webgis_user
|
|
GRANT ALL PRIVILEGES ON poverty_db.* TO 'webgis_user'@'%';
|
|
GRANT ALL PRIVILEGES ON spbu_db.* TO 'webgis_user'@'%';
|
|
FLUSH PRIVILEGES;
|