-- ============================================================
-- SIMPS - Sistem Informasi Manajemen Nominatif
-- Database schema + seed data
-- Charset: utf8mb4 / InnoDB throughout
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ------------------------------------------------------------
-- roles: the 4 fixed roles the app recognizes
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS roles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30) NOT NULL UNIQUE,
  display_name VARCHAR(50) NOT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- Organizational hierarchy: Cabang > Ranting > Rayon
-- (Ranting is the branch office under a Cabang; a Ranting can contain
--  several Rayon subdivisions. A member may belong directly to a Ranting
--  with no Rayon set, matching the "-" seen in the sample nominatif data.)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cabang (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(100) NOT NULL,
  provinsi VARCHAR(100) NULL,
  kabupaten VARCHAR(100) NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ranting (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cabang_id INT UNSIGNED NOT NULL,
  nama VARCHAR(100) NOT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_ranting_cabang FOREIGN KEY (cabang_id) REFERENCES cabang(id) ON DELETE CASCADE,
  INDEX idx_ranting_cabang (cabang_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS rayon (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ranting_id INT UNSIGNED NOT NULL,
  nama VARCHAR(100) NOT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_rayon_ranting FOREIGN KEY (ranting_id) REFERENCES ranting(id) ON DELETE CASCADE,
  INDEX idx_rayon_ranting (ranting_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- users: one row per login. cabang_id/ranting_id/rayon_id define
-- how far down the hierarchy this account can see, depending on role:
--   super_admin    -> all NULL   (sees everything)
--   admin_cabang   -> cabang_id set
--   admin_ranting  -> cabang_id + ranting_id set (sees every Rayon under it)
--   admin_rayon    -> cabang_id + ranting_id + rayon_id set (narrowest)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role_id INT UNSIGNED NOT NULL,
  cabang_id INT UNSIGNED NULL,
  rayon_id INT UNSIGNED NULL,
  ranting_id INT UNSIGNED NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id),
  CONSTRAINT fk_users_cabang FOREIGN KEY (cabang_id) REFERENCES cabang(id) ON DELETE SET NULL,
  CONSTRAINT fk_users_rayon FOREIGN KEY (rayon_id) REFERENCES rayon(id) ON DELETE SET NULL,
  CONSTRAINT fk_users_ranting FOREIGN KEY (ranting_id) REFERENCES ranting(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- anggota: one row per FORM NOMINATIF entry (calon warga / siswa).
-- Columns mirror the supplied Excel attachment 1:1 so the export
-- module (next phase) can round-trip without a mapping layer.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS anggota (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tahun_angkatan YEAR NOT NULL,
  cabang_id INT UNSIGNED NOT NULL,
  rayon_id INT UNSIGNED NULL,
  ranting_id INT UNSIGNED NULL,
  nama_lengkap VARCHAR(150) NOT NULL,
  nik VARCHAR(20) NOT NULL,
  tingkat_sabuk VARCHAR(30) NOT NULL,
  jenis_kelamin ENUM('L','P') NOT NULL,
  tempat_lahir VARCHAR(100) NOT NULL,
  tanggal_lahir DATE NOT NULL,
  agama VARCHAR(30) NOT NULL,
  pekerjaan VARCHAR(100) NULL,
  alamat_ktp TEXT NULL,
  provinsi VARCHAR(100) NULL,
  kabupaten VARCHAR(100) NULL,
  no_hp VARCHAR(20) NULL,
  tinggi_badan SMALLINT UNSIGNED NULL,
  berat_badan SMALLINT UNSIGNED NULL,
  no_pas_photo VARCHAR(255) NULL,
  no_foto_kk VARCHAR(255) NULL,
  keterangan ENUM('PRIVAT','REGULER') NOT NULL DEFAULT 'REGULER',
  status ENUM('MENUNGGU_VERIFIKASI','TERVERIFIKASI') NOT NULL DEFAULT 'TERVERIFIKASI',
  verified_by INT UNSIGNED NULL,
  verified_at TIMESTAMP NULL,
  created_by INT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_anggota_cabang FOREIGN KEY (cabang_id) REFERENCES cabang(id),
  CONSTRAINT fk_anggota_rayon FOREIGN KEY (rayon_id) REFERENCES rayon(id) ON DELETE SET NULL,
  CONSTRAINT fk_anggota_ranting FOREIGN KEY (ranting_id) REFERENCES ranting(id) ON DELETE SET NULL,
  CONSTRAINT fk_anggota_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_anggota_verifier FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_anggota_cabang (cabang_id),
  INDEX idx_anggota_rayon (rayon_id),
  INDEX idx_anggota_ranting (ranting_id),
  INDEX idx_anggota_status (status),
  UNIQUE KEY uniq_anggota_nik (nik)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- Seed data
-- ============================================================

INSERT INTO roles (name, display_name) VALUES
  ('super_admin',   'Super Admin'),
  ('admin_cabang',  'Admin Cabang'),
  ('admin_rayon',   'Admin Rayon'),
  ('admin_ranting', 'Admin Ranting')
ON DUPLICATE KEY UPDATE display_name = VALUES(display_name);

-- Sample wilayah, taken from the reference attachment, so the app has
-- something to browse immediately after install. Replace/expand freely.
INSERT INTO cabang (id, nama, provinsi, kabupaten) VALUES
  (1, 'PACITAN', 'JAWA TIMUR', 'PACITAN')
ON DUPLICATE KEY UPDATE nama = VALUES(nama);

INSERT INTO ranting (id, cabang_id, nama) VALUES
  (1, 1, 'PACITAN'),
  (2, 1, 'PENDAPA PACITAN')
ON DUPLICATE KEY UPDATE nama = VALUES(nama);

INSERT INTO rayon (id, ranting_id, nama) VALUES
  (1, 1, 'NANGGUNGAN')
ON DUPLICATE KEY UPDATE nama = VALUES(nama);

-- Default Super Admin login.
-- Username: superadmin   Password: SuperAdmin#123
-- CHANGE THIS PASSWORD IMMEDIATELY AFTER FIRST LOGIN.
INSERT INTO users (name, username, email, password, role_id, is_active) VALUES
  ('Super Administrator', 'superadmin', 'superadmin@simps.local',
   '$2y$10$J8ghmMoJtGe9uBHurJC3tOesRMfTRZY9LnMluYFjliZELv8k.8Yo6',
   (SELECT id FROM roles WHERE name = 'super_admin'), 1)
ON DUPLICATE KEY UPDATE name = VALUES(name);