-- ============================================================
-- structure.sql — Full Schema for poverty_mapping
-- Database: poverty_mapping
-- Charset:  utf8mb4 / utf8mb4_unicode_ci
-- Generated by: DB Reconstruction from source code analysis
-- ============================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+07:00";

-- ============================================================
-- CREATE DATABASE
-- ============================================================
CREATE DATABASE IF NOT EXISTS `poverty_mapping`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `poverty_mapping`;

-- ============================================================
-- TABLE 1: users
-- Source: api/auth/check.php — SELECT id, name, email,
--         password_hash, role, is_active, last_login_at
-- ============================================================
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;

-- ============================================================
-- TABLE 2: religious_centers
-- Source: api/centers/index.php — worship_type, address,
--         kelurahan, kecamatan, latitude, longitude, radius,
--         contact_person, contact_phone, notes, is_active
-- ============================================================
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 COMMENT 'Coverage radius in meters',
  `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;

-- ============================================================
-- TABLE 3: households
-- Source: api/houses/index.php — the main data table.
--         Columns verified from INSERT, UPDATE, SELECT queries.
-- ============================================================
CREATE TABLE IF NOT EXISTS `households` (
  `id`                    INT UNSIGNED     NOT NULL AUTO_INCREMENT,

  -- Location (administrative)
  `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,

  -- Spatial coordinates
  `latitude`              DECIMAL(10,7)    NOT NULL,
  `longitude`             DECIMAL(10,7)    NOT NULL,

  -- House characteristics
  `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 COMMENT 'JSON array of photo filenames',

  -- Household head info
  `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 assessment
  `poverty_score`         TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0-100 poverty score',
  `poverty_status`        ENUM('terdata','rentan_miskin','miskin','sangat_miskin') NOT NULL DEFAULT 'terdata',

  -- Aid tracking
  `aid_status`            ENUM('not_yet','received') NOT NULL DEFAULT 'not_yet',

  -- Relations
  `managing_center_id`    INT UNSIGNED     NULL DEFAULT NULL,

  -- Meta
  `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;

-- ============================================================
-- TABLE 4: household_members
-- Source: api/houses/index.php — saveMembers() function &
--         SELECT * FROM household_members in SHOW action.
--         Stats: SELECT COUNT(*) FROM 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'
                      COMMENT 'e.g. suami,istri,anak,orang_tua,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;

-- ============================================================
-- TABLE 5: aid_history
-- Source: api/aid/index.php — INSERT/UPDATE/SELECT queries.
--         Columns: household_id, center_id, aid_type, aid_date,
--                  amount, description, given_by
-- ============================================================
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 COMMENT 'Monetary value in IDR (optional)',
  `description`  TEXT             NULL DEFAULT NULL COMMENT 'Notes / keterangan bantuan',
  `given_by`     INT UNSIGNED     NULL DEFAULT NULL COMMENT 'FK to users.id (who recorded the aid)',
  `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;

-- ============================================================
-- TABLE 6: emergency_reports
-- Source: api/reports/index.php — INSERT, UPDATE, SELECT.
--         Columns: household_id, type, severity, description,
--                  status, resolved_at
-- ============================================================
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;

-- ============================================================
-- TABLE 7: public_reports
-- Source: api/public/report.php — INSERT, SELECT, UPDATE.
--         Columns: reporter_name, reporter_phone, rt, rw,
--                  head_name, address, kelurahan, kecamatan,
--                  latitude, longitude, description, severity,
--                  urgent_need, status, proof_photos,
--                  ip_address, converted_household_id,
--                  admin_notes, reviewed_at
-- ============================================================
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 COMMENT 'JSON array of photo filenames',
  `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
                          COMMENT 'Set when admin approves and creates household',
  `ip_address`            VARCHAR(45)      NOT NULL DEFAULT '' COMMENT 'Supports IPv6',
  `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;

-- ============================================================
-- TABLE 8: audit_logs
-- Source: models/AuditLog.php — INSERT INTO audit_logs
--         (action, table_name, record_id, old_values,
--          new_values, ip_address, user_agent)
-- ============================================================
CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id`          INT UNSIGNED     NOT NULL AUTO_INCREMENT,
  `action`      VARCHAR(100)     NOT NULL COMMENT 'e.g. auth.login, Tambah Rumah, Update Bantuan',
  `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;