569 lines
22 KiB
SQL
569 lines
22 KiB
SQL
-- phpMyAdmin SQL Dump
|
|
-- version 4.9.0.1
|
|
-- https://www.phpmyadmin.net/
|
|
--
|
|
-- Host: sql212.infinityfree.com
|
|
-- Generation Time: Jun 05, 2026 at 08:51 AM
|
|
-- Server version: 11.4.12-MariaDB
|
|
-- PHP Version: 7.2.22
|
|
|
|
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
|
|
SET AUTOCOMMIT = 0;
|
|
START TRANSACTION;
|
|
SET time_zone = "+00:00";
|
|
|
|
|
|
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
|
|
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
|
|
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
|
|
/*!40101 SET NAMES utf8mb4 */;
|
|
|
|
--
|
|
-- Database: `if0_41813164_sig`
|
|
--
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `area_statistics`
|
|
--
|
|
|
|
CREATE TABLE `area_statistics` (
|
|
`id` int(11) NOT NULL,
|
|
`pontianak_area_id` int(11) NOT NULL,
|
|
`statistic_type` enum('population_density','road_density','damaged_roads_density') NOT NULL,
|
|
`value` double NOT NULL,
|
|
`data_date` date NOT NULL,
|
|
`created_at` datetime DEFAULT current_timestamp(),
|
|
`updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `assistance_distributions`
|
|
--
|
|
|
|
CREATE TABLE `assistance_distributions` (
|
|
`id` int(11) NOT NULL,
|
|
`household_id` int(11) NOT NULL,
|
|
`assistance_type` enum('pangan','tunai','kesehatan','pendidikan','usaha_mikro','lainnya') NOT NULL,
|
|
`assistance_value` double NOT NULL DEFAULT 0,
|
|
`assistance_frequency` enum('sekali','mingguan','bulanan','triwulanan','tahunan','insidental') NOT NULL DEFAULT 'sekali',
|
|
`distribution_date` date NOT NULL,
|
|
`delivery_status` enum('pending','delivered','failed') NOT NULL DEFAULT 'pending',
|
|
`delivered_by` varchar(255) DEFAULT NULL,
|
|
`notes` text DEFAULT NULL,
|
|
`created_at` datetime DEFAULT current_timestamp(),
|
|
`updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `households`
|
|
--
|
|
|
|
CREATE TABLE `households` (
|
|
`id` int(11) NOT NULL,
|
|
`household_code` varchar(100) NOT NULL,
|
|
`head_name` varchar(255) NOT NULL,
|
|
`nik_hash` varchar(255) DEFAULT NULL,
|
|
`latitude` double NOT NULL,
|
|
`longitude` double NOT NULL,
|
|
`need_point_id` int(11) DEFAULT NULL,
|
|
`masjid_id` int(11) DEFAULT NULL,
|
|
`pontianak_area_id` int(11) DEFAULT NULL,
|
|
`monthly_income` double NOT NULL DEFAULT 0,
|
|
`dependents_count` int(11) NOT NULL DEFAULT 0,
|
|
`housing_condition_score` tinyint(4) NOT NULL DEFAULT 3,
|
|
`employment_status` enum('formal','informal','unemployed','daily_worker','micro_business','retired','unknown') NOT NULL DEFAULT 'unknown',
|
|
`economic_status` enum('sangat_miskin','miskin','rentan','cukup','baik','unknown') NOT NULL DEFAULT 'unknown',
|
|
`verification_status` enum('unverified','field_verified','admin_approved','rejected','needs_review') NOT NULL DEFAULT 'unverified',
|
|
`vulnerability_notes` text DEFAULT NULL,
|
|
`created_at` datetime DEFAULT current_timestamp(),
|
|
`updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
--
|
|
-- Dumping data for table `households`
|
|
--
|
|
|
|
INSERT INTO `households` (`id`, `household_code`, `head_name`, `nik_hash`, `latitude`, `longitude`, `need_point_id`, `masjid_id`, `pontianak_area_id`, `monthly_income`, `dependents_count`, `housing_condition_score`, `employment_status`, `economic_status`, `verification_status`, `vulnerability_notes`, `created_at`, `updated_at`) VALUES
|
|
(1, 'NP-000001', 'A', NULL, -0.009583, 109.349413, 1, 1, NULL, 0, 0, 3, 'unknown', 'rentan', 'unverified', 'Auto-synced from need_point #1', '2026-06-05 05:41:55', '2026-06-05 05:41:55'),
|
|
(2, 'NP-000002', 'B', NULL, -7.379257, 109.923019, 2, 2, NULL, 0, 0, 3, 'unknown', 'cukup', 'unverified', 'Auto-synced from need_point #2', '2026-06-05 05:44:59', '2026-06-05 05:44:59');
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `household_scores`
|
|
--
|
|
|
|
CREATE TABLE `household_scores` (
|
|
`id` int(11) NOT NULL,
|
|
`household_id` int(11) NOT NULL,
|
|
`skr` double NOT NULL DEFAULT 0,
|
|
`sag` double NOT NULL DEFAULT 0,
|
|
`spi` double NOT NULL DEFAULT 0,
|
|
`priority_level` enum('very_high','high','medium','low') NOT NULL DEFAULT 'low',
|
|
`score_version` varchar(30) NOT NULL DEFAULT 'v1',
|
|
`components_json` text DEFAULT NULL,
|
|
`computed_at` datetime DEFAULT current_timestamp(),
|
|
`updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
--
|
|
-- Dumping data for table `household_scores`
|
|
--
|
|
|
|
INSERT INTO `household_scores` (`id`, `household_id`, `skr`, `sag`, `spi`, `priority_level`, `score_version`, `components_json`, `computed_at`, `updated_at`) VALUES
|
|
(1, 1, 0.585482, 0.697029, 0.512181, 'medium', 'v1', '{\"ECO_economic_vulnerability\":0.65000000000000002220446049250313080847263336181640625,\"I_income_deprivation\":1,\"E_employment_vulnerability\":0.5,\"D_dependency_burden\":0,\"H_housing_vulnerability\":0.5,\"A_accessibility_deprivation\":0.12470599999999999740740719289533444680273532867431640625,\"S_shock_factor\":0.6999999999999999555910790149937383830547332763671875,\"J_distance_access\":0.8752940000000000164703806149191223084926605224609375,\"R_road_access\":0.5,\"T_time_access_proxy\":0.68764700000000000823519030745956115424633026123046875}', '2026-06-05 05:41:55', '2026-06-05 05:41:55'),
|
|
(2, 2, 0.468205, 0.673208, 0.448961, 'low', 'v1', '{\"ECO_economic_vulnerability\":0.34999999999999997779553950749686919152736663818359375,\"I_income_deprivation\":1,\"E_employment_vulnerability\":0.5,\"D_dependency_burden\":0,\"H_housing_vulnerability\":0.5,\"A_accessibility_deprivation\":0.170080000000000008952838470577262341976165771484375,\"S_shock_factor\":0.6999999999999999555910790149937383830547332763671875,\"J_distance_access\":0.829919999999999991047161529422737658023834228515625,\"R_road_access\":0.5,\"T_time_access_proxy\":0.6649599999999999955235807647113688290119171142578125}', '2026-06-05 05:44:59', '2026-06-05 05:44:59');
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `land_parcels`
|
|
--
|
|
|
|
CREATE TABLE `land_parcels` (
|
|
`id` int(11) NOT NULL,
|
|
`name` varchar(255) NOT NULL,
|
|
`certificate_type` enum('SHM','HGB','HGU','HP') NOT NULL,
|
|
`coordinates` longtext NOT NULL,
|
|
`area_sqm` double NOT NULL,
|
|
`created_at` datetime DEFAULT current_timestamp(),
|
|
`updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
--
|
|
-- Dumping data for table `land_parcels`
|
|
--
|
|
|
|
INSERT INTO `land_parcels` (`id`, `name`, `certificate_type`, `coordinates`, `area_sqm`, `created_at`, `updated_at`) VALUES
|
|
(1, 'A', 'HP', '[[-0.0033817290912692842159981143623781463247723877429962158203125,109.3456363677978657733547152020037174224853515625],[-0.00503396983992641412763990871326313936151564121246337890625,109.3485116958618306171047152020037174224853515625],[-0.01009798041425125254832995125298111815936863422393798828125,109.3464517593383931171047152020037174224853515625],[-0.00793075555715849929427907483159287949092686176300048828125,109.34383392333984375],[-0.005012512167884436604936571058033223380334675312042236328125,109.3425893783569478046047152020037174224853515625],[-0.005012512167884436604936571058033223380334675312042236328125,109.3425893783569478046047152020037174224853515625],[-0.0049953460466319841992710593103765859268605709075927734375,109.3425679206848286639797152020037174224853515625],[-0.0049953460466319841992710593103765859268605709075927734375,109.3425679206848286639797152020037174224853515625],[-0.004970669731977454819382700179630774073302745819091796875,109.3425679206848286639797152020037174224853515625],[-0.0048794746258085941459370360462344251573085784912109375,109.3425786495208882342922152020037174224853515625],[-0.004316210734487234933209887088878531358204782009124755859375,109.3430238962173604022609652020037174224853515625],[-0.004316210734487234933209887088878531358204782009124755859375,109.3430238962173604022609652020037174224853515625],[-0.0043253302492083089969554521303507499396800994873046875,109.3431740999221943866359652020037174224853515625],[-0.0043253302492083089969554521303507499396800994873046875,109.3431821465492390643703402020037174224853515625]]', 285081.25, '2026-06-05 05:41:10', '2026-06-05 05:41:10');
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `markers`
|
|
--
|
|
|
|
CREATE TABLE `markers` (
|
|
`id` int(11) NOT NULL,
|
|
`name` varchar(255) NOT NULL,
|
|
`nomor_spbu` varchar(100) DEFAULT NULL,
|
|
`latitude` double NOT NULL,
|
|
`longitude` double NOT NULL,
|
|
`open_24_hours` tinyint(1) DEFAULT 0,
|
|
`created_at` datetime DEFAULT current_timestamp()
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
--
|
|
-- Dumping data for table `markers`
|
|
--
|
|
|
|
INSERT INTO `markers` (`id`, `name`, `nomor_spbu`, `latitude`, `longitude`, `open_24_hours`, `created_at`) VALUES
|
|
(1, 'Test SPBU', '1', -0.006008, 109.357224, 1, '2026-06-05 05:39:18');
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `masjids`
|
|
--
|
|
|
|
CREATE TABLE `masjids` (
|
|
`id` int(11) NOT NULL,
|
|
`name` varchar(255) NOT NULL,
|
|
`latitude` double NOT NULL,
|
|
`longitude` double NOT NULL,
|
|
`radius_meters` double NOT NULL DEFAULT 500,
|
|
`created_at` datetime DEFAULT current_timestamp(),
|
|
`updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
--
|
|
-- Dumping data for table `masjids`
|
|
--
|
|
|
|
INSERT INTO `masjids` (`id`, `name`, `latitude`, `longitude`, `radius_meters`, `created_at`, `updated_at`) VALUES
|
|
(1, 'Masjid A', -0.00738143918856, 109.348983765, 500, '2026-06-05 05:39:55', '2026-06-05 05:39:58'),
|
|
(2, 'B', -7.381534, 109.920959, 500, '2026-06-05 05:44:33', '2026-06-05 05:44:33');
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `need_points`
|
|
--
|
|
|
|
CREATE TABLE `need_points` (
|
|
`id` int(11) NOT NULL,
|
|
`name` varchar(255) NOT NULL,
|
|
`latitude` double NOT NULL,
|
|
`longitude` double NOT NULL,
|
|
`masjid_id` int(11) NOT NULL,
|
|
`household_name` varchar(255) DEFAULT NULL,
|
|
`head_name` varchar(255) DEFAULT NULL,
|
|
`economic_status` varchar(50) DEFAULT NULL,
|
|
`is_verified` tinyint(1) NOT NULL DEFAULT 0,
|
|
`created_at` datetime DEFAULT current_timestamp(),
|
|
`updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
--
|
|
-- Dumping data for table `need_points`
|
|
--
|
|
|
|
INSERT INTO `need_points` (`id`, `name`, `latitude`, `longitude`, `masjid_id`, `household_name`, `head_name`, `economic_status`, `is_verified`, `created_at`, `updated_at`) VALUES
|
|
(1, 'Point A', -0.009583, 109.349413, 1, 'A', 'A', 'rentan', 0, '2026-06-05 05:40:10', '2026-06-05 05:40:10'),
|
|
(2, 'B', -7.379257, 109.923019, 2, 'B', 'B', 'cukup', 0, '2026-06-05 05:44:44', '2026-06-05 05:44:44');
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `pontianak_areas`
|
|
--
|
|
|
|
CREATE TABLE `pontianak_areas` (
|
|
`id` int(11) NOT NULL,
|
|
`name` varchar(255) NOT NULL,
|
|
`area_type` enum('kelurahan','kecamatan','city') NOT NULL,
|
|
`geometry` longtext NOT NULL,
|
|
`population` int(11) DEFAULT 0,
|
|
`created_at` datetime DEFAULT current_timestamp(),
|
|
`updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `roads`
|
|
--
|
|
|
|
CREATE TABLE `roads` (
|
|
`id` int(11) NOT NULL,
|
|
`name` varchar(255) NOT NULL,
|
|
`road_type` enum('Nasional','Provinsi','Kabupaten') NOT NULL,
|
|
`coordinates` longtext NOT NULL,
|
|
`length_meters` double NOT NULL,
|
|
`condition_status` enum('normal','minor_damage','major_damage') DEFAULT 'normal',
|
|
`created_at` datetime DEFAULT current_timestamp(),
|
|
`updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
--
|
|
-- Dumping data for table `roads`
|
|
--
|
|
|
|
INSERT INTO `roads` (`id`, `name`, `road_type`, `coordinates`, `length_meters`, `condition_status`, `created_at`, `updated_at`) VALUES
|
|
(1, 'A', 'Nasional', '[[-0.01012372963676469971028293315384871675632894039154052734375,109.3421602249145649921047152020037174224853515625],[-0.009512185990342932051522240044505451805889606475830078125,109.3424820899963521014797152020037174224853515625],[-0.00882554049132260563792495844381846836768090724945068359375,109.3430399894714497577297152020037174224853515625],[-0.00882554049132260563792495844381846836768090724945068359375,109.3430399894714497577297152020037174224853515625]]', 175.22, 'normal', '2026-06-05 05:40:43', '2026-06-05 05:40:43');
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `score_metadata`
|
|
--
|
|
|
|
CREATE TABLE `score_metadata` (
|
|
`key_name` varchar(100) NOT NULL,
|
|
`key_value` text NOT NULL,
|
|
`updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
--
|
|
-- Dumping data for table `score_metadata`
|
|
--
|
|
|
|
INSERT INTO `score_metadata` (`key_name`, `key_value`, `updated_at`) VALUES
|
|
('distance_max_reference', '2000', '2026-06-05 05:38:46'),
|
|
('income_max_reference', '5000000', '2026-06-05 05:38:46'),
|
|
('population_reference', '288315899', '2026-06-05 05:42:18'),
|
|
('sag_weights', '{\"J\":0.40,\"R\":0.35,\"T\":0.25}', '2026-06-05 05:38:46'),
|
|
('score_version', 'v1', '2026-06-05 05:38:46'),
|
|
('skr_weights', '{\"I\":0.25,\"E\":0.20,\"D\":0.15,\"H\":0.15,\"A\":0.15,\"S\":0.10}', '2026-06-05 05:38:46'),
|
|
('spi_weights', '{\"SKR\":0.60,\"SAG_INV\":0.30,\"K\":0.10}', '2026-06-05 05:38:46'),
|
|
('target_nasional', '5.00', '2026-06-05 05:42:18');
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `users`
|
|
--
|
|
|
|
CREATE TABLE `users` (
|
|
`id` int(11) NOT NULL,
|
|
`username` varchar(100) NOT NULL,
|
|
`email` varchar(255) NOT NULL,
|
|
`password_hash` varchar(255) NOT NULL,
|
|
`role` enum('Admin','Petugas','Pimpinan') NOT NULL DEFAULT 'Petugas',
|
|
`is_active` tinyint(1) NOT NULL DEFAULT 1,
|
|
`created_at` datetime DEFAULT current_timestamp(),
|
|
`updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
--
|
|
-- Dumping data for table `users`
|
|
--
|
|
|
|
INSERT INTO `users` (`id`, `username`, `email`, `password_hash`, `role`, `is_active`, `created_at`, `updated_at`) VALUES
|
|
(1, 'admin', 'admin@system.local', '$2y$12$vmPN9xlkrBtRiCM67HuwxuaRoR8Y2hVYmKjNExMv8rfOUOZOwwqPC', 'Admin', 1, '2026-06-05 05:38:46', '2026-06-05 05:38:46');
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `verification_logs`
|
|
--
|
|
|
|
CREATE TABLE `verification_logs` (
|
|
`id` int(11) NOT NULL,
|
|
`household_id` int(11) NOT NULL,
|
|
`verification_status` enum('field_verified','admin_approved','rejected','needs_review') NOT NULL,
|
|
`verifier_name` varchar(255) NOT NULL,
|
|
`verifier_role` varchar(100) DEFAULT NULL,
|
|
`field_note` text DEFAULT NULL,
|
|
`evidence_photo_count` int(11) NOT NULL DEFAULT 0,
|
|
`data_confidence_score` double DEFAULT NULL,
|
|
`verified_at` datetime DEFAULT current_timestamp(),
|
|
`created_at` datetime DEFAULT current_timestamp()
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
--
|
|
-- Indexes for dumped tables
|
|
--
|
|
|
|
--
|
|
-- Indexes for table `area_statistics`
|
|
--
|
|
ALTER TABLE `area_statistics`
|
|
ADD PRIMARY KEY (`id`),
|
|
ADD UNIQUE KEY `uq_area_stat_date` (`pontianak_area_id`,`statistic_type`,`data_date`),
|
|
ADD KEY `idx_stats_area_type` (`pontianak_area_id`,`statistic_type`);
|
|
|
|
--
|
|
-- Indexes for table `assistance_distributions`
|
|
--
|
|
ALTER TABLE `assistance_distributions`
|
|
ADD PRIMARY KEY (`id`),
|
|
ADD KEY `idx_assistance_household_id` (`household_id`),
|
|
ADD KEY `idx_assistance_date` (`distribution_date`),
|
|
ADD KEY `idx_assistance_status` (`delivery_status`);
|
|
|
|
--
|
|
-- Indexes for table `households`
|
|
--
|
|
ALTER TABLE `households`
|
|
ADD PRIMARY KEY (`id`),
|
|
ADD UNIQUE KEY `household_code` (`household_code`),
|
|
ADD UNIQUE KEY `need_point_id` (`need_point_id`),
|
|
ADD KEY `idx_households_code` (`household_code`),
|
|
ADD KEY `idx_households_verification_status` (`verification_status`),
|
|
ADD KEY `idx_households_masjid_id` (`masjid_id`),
|
|
ADD KEY `idx_households_area_id` (`pontianak_area_id`),
|
|
ADD KEY `idx_households_created_at` (`created_at`);
|
|
|
|
--
|
|
-- Indexes for table `household_scores`
|
|
--
|
|
ALTER TABLE `household_scores`
|
|
ADD PRIMARY KEY (`id`),
|
|
ADD UNIQUE KEY `household_id` (`household_id`),
|
|
ADD KEY `idx_household_scores_spi` (`spi`),
|
|
ADD KEY `idx_household_scores_priority` (`priority_level`),
|
|
ADD KEY `idx_household_scores_computed_at` (`computed_at`);
|
|
|
|
--
|
|
-- Indexes for table `land_parcels`
|
|
--
|
|
ALTER TABLE `land_parcels`
|
|
ADD PRIMARY KEY (`id`),
|
|
ADD KEY `idx_parcels_cert_type` (`certificate_type`),
|
|
ADD KEY `idx_parcels_created_at` (`created_at`);
|
|
|
|
--
|
|
-- Indexes for table `markers`
|
|
--
|
|
ALTER TABLE `markers`
|
|
ADD PRIMARY KEY (`id`);
|
|
|
|
--
|
|
-- Indexes for table `masjids`
|
|
--
|
|
ALTER TABLE `masjids`
|
|
ADD PRIMARY KEY (`id`),
|
|
ADD KEY `idx_masjids_created_at` (`created_at`),
|
|
ADD KEY `idx_masjids_name` (`name`);
|
|
|
|
--
|
|
-- Indexes for table `need_points`
|
|
--
|
|
ALTER TABLE `need_points`
|
|
ADD PRIMARY KEY (`id`),
|
|
ADD KEY `idx_need_points_masjid_id` (`masjid_id`),
|
|
ADD KEY `idx_need_points_created_at` (`created_at`);
|
|
|
|
--
|
|
-- Indexes for table `pontianak_areas`
|
|
--
|
|
ALTER TABLE `pontianak_areas`
|
|
ADD PRIMARY KEY (`id`),
|
|
ADD UNIQUE KEY `uq_area_name_type` (`name`,`area_type`),
|
|
ADD KEY `idx_pontianak_areas_type` (`area_type`),
|
|
ADD KEY `idx_pontianak_areas_name` (`name`);
|
|
|
|
--
|
|
-- Indexes for table `roads`
|
|
--
|
|
ALTER TABLE `roads`
|
|
ADD PRIMARY KEY (`id`),
|
|
ADD KEY `idx_roads_road_type` (`road_type`),
|
|
ADD KEY `idx_roads_created_at` (`created_at`);
|
|
|
|
--
|
|
-- Indexes for table `score_metadata`
|
|
--
|
|
ALTER TABLE `score_metadata`
|
|
ADD PRIMARY KEY (`key_name`);
|
|
|
|
--
|
|
-- Indexes for table `users`
|
|
--
|
|
ALTER TABLE `users`
|
|
ADD PRIMARY KEY (`id`),
|
|
ADD UNIQUE KEY `username` (`username`),
|
|
ADD UNIQUE KEY `email` (`email`),
|
|
ADD KEY `idx_users_email` (`email`),
|
|
ADD KEY `idx_users_username` (`username`),
|
|
ADD KEY `idx_users_role` (`role`);
|
|
|
|
--
|
|
-- Indexes for table `verification_logs`
|
|
--
|
|
ALTER TABLE `verification_logs`
|
|
ADD PRIMARY KEY (`id`),
|
|
ADD KEY `idx_verification_household_id` (`household_id`),
|
|
ADD KEY `idx_verification_status` (`verification_status`),
|
|
ADD KEY `idx_verification_verified_at` (`verified_at`);
|
|
|
|
--
|
|
-- AUTO_INCREMENT for dumped tables
|
|
--
|
|
|
|
--
|
|
-- AUTO_INCREMENT for table `area_statistics`
|
|
--
|
|
ALTER TABLE `area_statistics`
|
|
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
|
|
|
|
--
|
|
-- AUTO_INCREMENT for table `assistance_distributions`
|
|
--
|
|
ALTER TABLE `assistance_distributions`
|
|
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
|
|
|
|
--
|
|
-- AUTO_INCREMENT for table `households`
|
|
--
|
|
ALTER TABLE `households`
|
|
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
|
|
|
|
--
|
|
-- AUTO_INCREMENT for table `household_scores`
|
|
--
|
|
ALTER TABLE `household_scores`
|
|
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
|
|
|
|
--
|
|
-- AUTO_INCREMENT for table `land_parcels`
|
|
--
|
|
ALTER TABLE `land_parcels`
|
|
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
|
|
|
|
--
|
|
-- AUTO_INCREMENT for table `markers`
|
|
--
|
|
ALTER TABLE `markers`
|
|
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
|
|
|
|
--
|
|
-- AUTO_INCREMENT for table `masjids`
|
|
--
|
|
ALTER TABLE `masjids`
|
|
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
|
|
|
|
--
|
|
-- AUTO_INCREMENT for table `need_points`
|
|
--
|
|
ALTER TABLE `need_points`
|
|
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
|
|
|
|
--
|
|
-- AUTO_INCREMENT for table `pontianak_areas`
|
|
--
|
|
ALTER TABLE `pontianak_areas`
|
|
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
|
|
|
|
--
|
|
-- AUTO_INCREMENT for table `roads`
|
|
--
|
|
ALTER TABLE `roads`
|
|
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=2;
|
|
|
|
--
|
|
-- AUTO_INCREMENT for table `verification_logs`
|
|
--
|
|
ALTER TABLE `verification_logs`
|
|
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
|
|
|
|
--
|
|
-- Constraints for dumped tables
|
|
--
|
|
|
|
--
|
|
-- Constraints for table `area_statistics`
|
|
--
|
|
ALTER TABLE `area_statistics`
|
|
ADD CONSTRAINT `area_statistics_ibfk_1` FOREIGN KEY (`pontianak_area_id`) REFERENCES `pontianak_areas` (`id`) ON DELETE CASCADE;
|
|
|
|
--
|
|
-- Constraints for table `assistance_distributions`
|
|
--
|
|
ALTER TABLE `assistance_distributions`
|
|
ADD CONSTRAINT `assistance_distributions_ibfk_1` FOREIGN KEY (`household_id`) REFERENCES `households` (`id`) ON DELETE CASCADE;
|
|
|
|
--
|
|
-- Constraints for table `households`
|
|
--
|
|
ALTER TABLE `households`
|
|
ADD CONSTRAINT `households_ibfk_1` FOREIGN KEY (`need_point_id`) REFERENCES `need_points` (`id`) ON DELETE SET NULL,
|
|
ADD CONSTRAINT `households_ibfk_2` FOREIGN KEY (`masjid_id`) REFERENCES `masjids` (`id`) ON DELETE SET NULL,
|
|
ADD CONSTRAINT `households_ibfk_3` FOREIGN KEY (`pontianak_area_id`) REFERENCES `pontianak_areas` (`id`) ON DELETE SET NULL;
|
|
|
|
--
|
|
-- Constraints for table `household_scores`
|
|
--
|
|
ALTER TABLE `household_scores`
|
|
ADD CONSTRAINT `household_scores_ibfk_1` FOREIGN KEY (`household_id`) REFERENCES `households` (`id`) ON DELETE CASCADE;
|
|
|
|
--
|
|
-- Constraints for table `need_points`
|
|
--
|
|
ALTER TABLE `need_points`
|
|
ADD CONSTRAINT `need_points_ibfk_1` FOREIGN KEY (`masjid_id`) REFERENCES `masjids` (`id`) ON DELETE CASCADE;
|
|
|
|
--
|
|
-- Constraints for table `verification_logs`
|
|
--
|
|
ALTER TABLE `verification_logs`
|
|
ADD CONSTRAINT `verification_logs_ibfk_1` FOREIGN KEY (`household_id`) REFERENCES `households` (`id`) ON DELETE CASCADE;
|
|
COMMIT;
|
|
|
|
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
|
|
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
|
|
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
|