$ok, 'msg' => $msg, 'detail' => $detail];
if (!$ok) $hasError = true;
}
// ---- Connect WITHOUT selecting a database first -------------
try {
$dsn = "mysql:host={$DB_HOST};port={$DB_PORT};charset={$DB_CHARSET}";
$pdo = new PDO($dsn, $DB_USER, $DB_PASS, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]);
step('Connect to MySQL server', true, "{$DB_HOST}:{$DB_PORT}");
} catch (PDOException $e) {
step('Connect to MySQL server', false, $e->getMessage());
renderPage($results, true);
exit;
}
// ---- Create database ----------------------------------------
try {
$pdo->exec("CREATE DATABASE IF NOT EXISTS `{$DB_NAME}`
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci");
step("Create database `{$DB_NAME}`", true);
} catch (PDOException $e) {
step("Create database `{$DB_NAME}`", false, $e->getMessage());
renderPage($results, true);
exit;
}
// ---- Select database ----------------------------------------
$pdo->exec("USE `{$DB_NAME}`");
step("USE `{$DB_NAME}`", true);
// ---- Enable FK checks off for migration ---------------------
$pdo->exec('SET FOREIGN_KEY_CHECKS = 0');
// ---- Table definitions (ordered: no FK before parent) -------
$tables = [];
// 1 — users
$tables['users'] = "
CREATE TABLE IF NOT EXISTS `users` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(150) NOT NULL,
`email` VARCHAR(255) NOT NULL,
`password_hash` VARCHAR(255) NOT NULL,
`role` ENUM('admin','field_officer','viewer') NOT NULL DEFAULT 'viewer',
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
`last_login_at` TIMESTAMP NULL DEFAULT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_users_email` (`email`),
KEY `idx_users_role` (`role`),
KEY `idx_users_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
";
// 2 — religious_centers
$tables['religious_centers'] = "
CREATE TABLE IF NOT EXISTS `religious_centers` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`worship_type` ENUM('masjid','gereja','klenteng','pura','vihara') NOT NULL DEFAULT 'masjid',
`address` TEXT NOT NULL,
`kelurahan` VARCHAR(100) NULL DEFAULT NULL,
`kecamatan` VARCHAR(100) NULL DEFAULT NULL,
`latitude` DECIMAL(10,7) NOT NULL,
`longitude` DECIMAL(10,7) NOT NULL,
`radius` INT UNSIGNED NOT NULL DEFAULT 300,
`contact_person` VARCHAR(150) NULL DEFAULT NULL,
`contact_phone` VARCHAR(20) NULL DEFAULT NULL,
`notes` TEXT NULL DEFAULT NULL,
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_rc_worship_type` (`worship_type`),
KEY `idx_rc_is_active` (`is_active`),
KEY `idx_rc_location` (`latitude`, `longitude`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
";
// 3 — households
$tables['households'] = "
CREATE TABLE IF NOT EXISTS `households` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`rt` VARCHAR(10) NULL DEFAULT NULL,
`rw` VARCHAR(10) NULL DEFAULT NULL,
`kelurahan` VARCHAR(100) NULL DEFAULT NULL,
`kecamatan` VARCHAR(100) NULL DEFAULT NULL,
`full_address` TEXT NOT NULL,
`latitude` DECIMAL(10,7) NOT NULL,
`longitude` DECIMAL(10,7) NOT NULL,
`house_condition` ENUM('layak','tidak_layak') NOT NULL DEFAULT 'layak',
`land_ownership` ENUM('milik','sewa','numpang','lainnya') NOT NULL DEFAULT 'milik',
`house_photos` JSON NULL DEFAULT NULL,
`head_name` VARCHAR(150) NOT NULL,
`head_nik` VARCHAR(16) NULL DEFAULT NULL,
`head_gender` ENUM('male','female') NOT NULL DEFAULT 'male',
`head_date_of_birth` DATE NULL DEFAULT NULL,
`head_education` ENUM('tidak_sekolah','sd','smp','sma','diploma','sarjana','pascasarjana') NOT NULL DEFAULT 'sd',
`head_employment_status` VARCHAR(50) NOT NULL DEFAULT 'unemployed',
`head_job_name` VARCHAR(150) NULL DEFAULT NULL,
`head_institution_name` VARCHAR(200) NULL DEFAULT NULL,
`head_monthly_income` INT UNSIGNED NOT NULL DEFAULT 0,
`poverty_score` TINYINT UNSIGNED NOT NULL DEFAULT 0,
`poverty_status` ENUM('terdata','rentan_miskin','miskin','sangat_miskin') NOT NULL DEFAULT 'terdata',
`aid_status` ENUM('not_yet','received') NOT NULL DEFAULT 'not_yet',
`managing_center_id` INT UNSIGNED NULL DEFAULT NULL,
`notes` TEXT NULL DEFAULT NULL,
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_hh_poverty_status` (`poverty_status`),
KEY `idx_hh_aid_status` (`aid_status`),
KEY `idx_hh_house_condition` (`house_condition`),
KEY `idx_hh_managing_center` (`managing_center_id`),
KEY `idx_hh_is_active` (`is_active`),
KEY `idx_hh_location` (`latitude`, `longitude`),
KEY `idx_hh_head_nik` (`head_nik`),
KEY `idx_hh_created_at` (`created_at`),
CONSTRAINT `fk_hh_managing_center`
FOREIGN KEY (`managing_center_id`) REFERENCES `religious_centers` (`id`)
ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
";
// 4 — household_members
$tables['household_members'] = "
CREATE TABLE IF NOT EXISTS `household_members` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`household_id` INT UNSIGNED NOT NULL,
`name` VARCHAR(150) NOT NULL,
`nik` VARCHAR(16) NULL DEFAULT NULL,
`gender` ENUM('male','female') NOT NULL DEFAULT 'male',
`date_of_birth` DATE NULL DEFAULT NULL,
`education` ENUM('tidak_sekolah','sd','smp','sma','diploma','sarjana','pascasarjana') NOT NULL DEFAULT 'sd',
`relationship` VARCHAR(50) NOT NULL DEFAULT 'lainnya',
`employment_status` VARCHAR(50) NOT NULL DEFAULT 'unemployed',
`job_name` VARCHAR(150) NULL DEFAULT NULL,
`institution_name` VARCHAR(200) NULL DEFAULT NULL,
`monthly_income` INT UNSIGNED NOT NULL DEFAULT 0,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_hm_household_id` (`household_id`),
KEY `idx_hm_nik` (`nik`),
CONSTRAINT `fk_hm_household`
FOREIGN KEY (`household_id`) REFERENCES `households` (`id`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
";
// 5 — aid_history
$tables['aid_history'] = "
CREATE TABLE IF NOT EXISTS `aid_history` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`household_id` INT UNSIGNED NOT NULL,
`center_id` INT UNSIGNED NULL DEFAULT NULL,
`aid_type` ENUM('sembako','pendanaan','pelatihan','sembako_pendanaan','sembako_pelatihan','pendanaan_pelatihan','lengkap') NOT NULL,
`aid_date` DATE NOT NULL,
`amount` INT UNSIGNED NULL DEFAULT NULL,
`description` TEXT NULL DEFAULT NULL,
`given_by` INT UNSIGNED NULL DEFAULT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_ah_household_id` (`household_id`),
KEY `idx_ah_center_id` (`center_id`),
KEY `idx_ah_aid_date` (`aid_date`),
KEY `idx_ah_aid_type` (`aid_type`),
CONSTRAINT `fk_ah_household`
FOREIGN KEY (`household_id`) REFERENCES `households` (`id`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_ah_center`
FOREIGN KEY (`center_id`) REFERENCES `religious_centers` (`id`)
ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `fk_ah_given_by`
FOREIGN KEY (`given_by`) REFERENCES `users` (`id`)
ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
";
// 6 — emergency_reports
$tables['emergency_reports'] = "
CREATE TABLE IF NOT EXISTS `emergency_reports` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`household_id` INT UNSIGNED NOT NULL,
`type` ENUM('sakit','kecelakaan','bencana','kehilangan_pekerjaan','kematian','lainnya') NOT NULL,
`severity` ENUM('ringan','sedang','berat','kritis') NOT NULL DEFAULT 'ringan',
`description` TEXT NOT NULL,
`status` ENUM('open','in_progress','resolved','closed') NOT NULL DEFAULT 'open',
`resolved_at` TIMESTAMP NULL DEFAULT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_er_household_id` (`household_id`),
KEY `idx_er_status` (`status`),
KEY `idx_er_severity` (`severity`),
KEY `idx_er_created_at` (`created_at`),
CONSTRAINT `fk_er_household`
FOREIGN KEY (`household_id`) REFERENCES `households` (`id`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
";
// 7 — public_reports
$tables['public_reports'] = "
CREATE TABLE IF NOT EXISTS `public_reports` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`reporter_name` VARCHAR(150) NOT NULL,
`reporter_phone` VARCHAR(20) NOT NULL,
`rt` VARCHAR(10) NOT NULL DEFAULT '',
`rw` VARCHAR(10) NOT NULL DEFAULT '',
`head_name` VARCHAR(150) NOT NULL,
`address` TEXT NOT NULL,
`kelurahan` VARCHAR(100) NOT NULL,
`kecamatan` VARCHAR(100) NOT NULL,
`latitude` DECIMAL(10,7) NOT NULL,
`longitude` DECIMAL(10,7) NOT NULL,
`description` TEXT NOT NULL,
`severity` ENUM('ringan','sedang','berat','kritis') NOT NULL DEFAULT 'ringan',
`urgent_need` TEXT NULL DEFAULT NULL,
`proof_photos` JSON NULL DEFAULT NULL,
`status` ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
`admin_notes` TEXT NULL DEFAULT NULL,
`reviewed_at` TIMESTAMP NULL DEFAULT NULL,
`converted_household_id` INT UNSIGNED NULL DEFAULT NULL,
`ip_address` VARCHAR(45) NOT NULL DEFAULT '',
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_pr_status` (`status`),
KEY `idx_pr_severity` (`severity`),
KEY `idx_pr_ip_address` (`ip_address`),
KEY `idx_pr_created_at` (`created_at`),
KEY `idx_pr_converted` (`converted_household_id`),
CONSTRAINT `fk_pr_converted_household`
FOREIGN KEY (`converted_household_id`) REFERENCES `households` (`id`)
ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
";
// 8 — audit_logs
$tables['audit_logs'] = "
CREATE TABLE IF NOT EXISTS `audit_logs` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`action` VARCHAR(100) NOT NULL,
`table_name` VARCHAR(100) NOT NULL,
`record_id` INT UNSIGNED NULL DEFAULT NULL,
`old_values` JSON NULL DEFAULT NULL,
`new_values` JSON NULL DEFAULT NULL,
`ip_address` VARCHAR(45) NOT NULL DEFAULT '',
`user_agent` TEXT NULL DEFAULT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_al_table_name` (`table_name`),
KEY `idx_al_record_id` (`record_id`),
KEY `idx_al_action` (`action`),
KEY `idx_al_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
";
// ---- Execute each table -------------------------------------
foreach ($tables as $tableName => $sql) {
try {
$pdo->exec($sql);
step("CREATE TABLE IF NOT EXISTS `{$tableName}`", true);
} catch (PDOException $e) {
step("CREATE TABLE IF NOT EXISTS `{$tableName}`", false, $e->getMessage());
}
}
// ---- Re-enable FK checks ------------------------------------
$pdo->exec('SET FOREIGN_KEY_CHECKS = 1');
step('Re-enable FOREIGN_KEY_CHECKS', true);
// ---- Verify: count created tables ---------------------------
try {
$count = (int)$pdo->query("SELECT COUNT(*) FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '{$DB_NAME}'")->fetchColumn();
step("Verify: {$count} table(s) exist in `{$DB_NAME}`", $count >= 8,
$count < 8 ? "Expected at least 8 tables." : "");
} catch (PDOException $e) {
step('Verify table count', false, $e->getMessage());
}
renderPage($results, $hasError);
// ---- Render HTML --------------------------------------------
function renderPage(array $results, bool $hasError): void
{
$title = $hasError ? '⚠ Migration completed with errors' : '✅ Migration successful';
$color = $hasError ? '#c0392b' : '#27ae60';
?>
DB Migration — poverty_mapping