-- =========================================================
-- SISTEM KEHADIRAN PENGUNDI - DATABASE SCHEMA
-- =========================================================
CREATE DATABASE IF NOT EXISTS sistem_pengundi
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE sistem_pengundi;

-- ---------------------------------------------------------
-- users : login 3 langkah -> No MyKad -> Kod Kawasan (KodDM) -> PIN 6 angka
-- ---------------------------------------------------------
DROP TABLE IF EXISTS users;
CREATE TABLE users (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  NoMyKad       VARCHAR(14)  NOT NULL,
  KodDM         VARCHAR(20)  NOT NULL,
  PinHash       VARCHAR(255) NOT NULL,
  Nama          VARCHAR(150) NOT NULL,
  Role          ENUM('admin','user') NOT NULL DEFAULT 'user',
  Status        TINYINT(1)   NOT NULL DEFAULT 1,
  last_login    DATETIME NULL,
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_mykad (NoMyKad),
  INDEX idx_koddm (KodDM)
) ENGINE=InnoDB;

-- Gunakan generate_pin.php (disertakan) untuk jana PIN hash sebenar.
-- Baris contoh di bawah semuanya menggunakan PIN = 123456
-- (jangan guna hash placeholder ini dalam produksi — jana semula)
INSERT INTO users (NoMyKad, KodDM, PinHash, Nama, Role, Status) VALUES
('880101015001','ADMIN',    '$2y$10$k8N0m5nQvXY1H1sVwq0dHOe1r1i0O9pQvW1XyZ2aB3cD4eF5gH6iK','Admin Pentadbir','admin',1),
('850312056713','N29-DM01', '$2y$10$k8N0m5nQvXY1H1sVwq0dHOe1r1i0O9pQvW1XyZ2aB3cD4eF5gH6iK','Ahmad Irfan Bin Zulkefli','user',1),
('780815014221','N29-DM02', '$2y$10$k8N0m5nQvXY1H1sVwq0dHOe1r1i0O9pQvW1XyZ2aB3cD4eF5gH6iK','Ariff Bin Mohd Salleh','user',1);

-- ---------------------------------------------------------
-- pengundi
-- ---------------------------------------------------------
DROP TABLE IF EXISTS pengundi;
CREATE TABLE pengundi (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  KodParlimen   VARCHAR(10)  NOT NULL,
  NamaParlimen  VARCHAR(100) NOT NULL,
  KodDUN        VARCHAR(10)  NOT NULL,
  NamaDUN       VARCHAR(100) NOT NULL,
  KodDM         VARCHAR(20)  NOT NULL,
  NamaDM        VARCHAR(100) NOT NULL,
  NoSiri        VARCHAR(20)  NULL,
  TempatMengundi VARCHAR(150) NOT NULL,
  Saluran       VARCHAR(10)  NOT NULL,
  NoMyKad       VARCHAR(14)  NOT NULL,
  Nama          VARCHAR(150) NOT NULL,
  NoTel         VARCHAR(20)  NULL,
  Sikap         ENUM('Menyokong','Tidak Menyokong','Belum Pasti') NOT NULL DEFAULT 'Belum Pasti',
  Status        ENUM('Aktif','Meninggal Dunia','Pindah','Tidak Aktif') NOT NULL DEFAULT 'Aktif',
  Catatan       TEXT NULL,
  Hadir         TINYINT(1) NOT NULL DEFAULT 0,
  TarikhHadir   DATETIME NULL,
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_mykad (NoMyKad),
  INDEX idx_koddm (KodDM),
  INDEX idx_koddun (KodDUN),
  INDEX idx_sikap (Sikap),
  INDEX idx_status (Status),
  INDEX idx_tempatmengundi (TempatMengundi),
  FULLTEXT KEY ft_nama (Nama)
) ENGINE=InnoDB;

INSERT INTO pengundi
(KodParlimen,NamaParlimen,KodDUN,NamaDUN,KodDM,NamaDM,NoSiri,TempatMengundi,Saluran,NoMyKad,Nama,NoTel,Sikap,Status,Catatan,Hadir) VALUES
('P105','Serdang','N29','Seri Kembangan','N29-DM01','SK Seri Serdang','0001','SK Seri Serdang','1','850312056713','Ahmad Irfan Bin Zulkefli','012-2272227','Menyokong','Aktif',NULL,1),
('P105','Serdang','N29','Seri Kembangan','N29-DM01','SK Seri Serdang','0002','SK Seri Serdang','1','900525076443','Ahmad Syakir Bin Man',NULL,'Belum Pasti','Aktif',NULL,0),
('P105','Serdang','N29','Seri Kembangan','N29-DM01','SK Seri Serdang','0003','SK Seri Serdang','2','921104076321','Ahmad Yusmal Danial Bin Mohd Yusof',NULL,'Belum Pasti','Aktif',NULL,0),
('P105','Serdang','N29','Seri Kembangan','N29-DM01','SK Seri Serdang','0004','SK Seri Serdang','2','941203076101','Ahmad Yusrie Farith Bin Mohd Yusof',NULL,'Tidak Menyokong','Aktif',NULL,0),
('P105','Serdang','N29','Seri Kembangan','N29-DM02','Dewan Jalan Bayam','0005','Dewan Serbaguna Jalan Bayam','1','780815014221','Ariff @ Che Mohd Ariff B.Mohd Salleh','012-7721772','Menyokong','Aktif',NULL,1),
('P105','Serdang','N29','Seri Kembangan','N29-DM02','Dewan Jalan Bayam','0006','Dewan Serbaguna Jalan Bayam','1','870622016751','Asma Niza Bin Abdullah',NULL,'Belum Pasti','Aktif',NULL,0),
('P105','Serdang','N29','Seri Kembangan','N29-DM02','Dewan Jalan Bayam','0007','Dewan Serbaguna Jalan Bayam','2','820304015343','Azrol Affendi Bin Razali','012-7722822','Menyokong','Aktif',NULL,1),
('P105','Serdang','N29','Seri Kembangan','N29-DM02','Dewan Jalan Bayam','0008','Dewan Serbaguna Jalan Bayam','2','760920016231','Bahrom Bin Man',NULL,'Tidak Menyokong','Pindah',NULL,0),
('P105','Serdang','N29','Seri Kembangan','N29-DM03','SJK(C) Serdang','0009','SJK(C) Serdang','1','730516015561','Che Hassin Bin Mohd Salleh',NULL,'Belum Pasti','Aktif',NULL,0),
('P105','Serdang','N29','Seri Kembangan','N29-DM03','SJK(C) Serdang','0010','SJK(C) Serdang','1','910318056231','Che Mohammad Syukriey',NULL,'Menyokong','Aktif',NULL,0),
('P105','Serdang','N29','Seri Kembangan','N29-DM03','SJK(C) Serdang','0011','SJK(C) Serdang','2','830712076102','Che Noraini Binti Che Samsudin',NULL,'Belum Pasti','Aktif',NULL,0),
('P105','Serdang','N29','Seri Kembangan','N29-DM03','SJK(C) Serdang','0012','SJK(C) Serdang','2','791005076331','Cik Siti Meriam @ Mariani Binti Abdullah',NULL,'Menyokong','Aktif',NULL,1),
('P105','Serdang','N30','Balakong','N30-DM01','SK Balakong Jaya','0013','SK Balakong Jaya','1','650412015678','Daud Bin Hj Yusoff',NULL,'Tidak Menyokong','Meninggal Dunia',NULL,0),
('P105','Serdang','N30','Balakong','N30-DM01','SK Balakong Jaya','0014','SK Balakong Jaya','1','880920076001','Dzulkiflee Bin Mohd Yusof',NULL,'Belum Pasti','Aktif',NULL,0),
('P105','Serdang','N30','Balakong','N30-DM01','SK Balakong Jaya','0015','SK Balakong Jaya','2','800130034442','Mohd Ali Bin Abu','011-7751212','Menyokong','Aktif',NULL,0),
('P106','Puchong','N31','Kinrara','N31-DM01','SK Bandar Kinrara','0016','SK Bandar Kinrara 1','1','830516076221','Siti Aishah Binti Kamal','013-3344556','Menyokong','Aktif',NULL,1),
('P106','Puchong','N31','Kinrara','N31-DM01','SK Bandar Kinrara','0017','SK Bandar Kinrara 1','1','710214015331','Roslan Bin Hamid',NULL,'Tidak Menyokong','Aktif',NULL,0),
('P106','Puchong','N31','Kinrara','N31-DM02','Dewan Bandar Puteri','0018','Dewan Bandar Puteri','1','920714076112','Nurul Hidayah Binti Hashim','017-9988776','Menyokong','Aktif',NULL,1),
('P106','Puchong','N31','Kinrara','N31-DM02','Dewan Bandar Puteri','0019','Dewan Bandar Puteri','2','880811056781','Hafizuddin Bin Razak','012-6677889','Belum Pasti','Aktif',NULL,0),
('P106','Puchong','N31','Kinrara','N31-DM02','Dewan Bandar Puteri','0020','Dewan Bandar Puteri','2','750630076441','Zakiah Binti Othman',NULL,'Menyokong','Tidak Aktif',NULL,1);

-- ---------------------------------------------------------
-- Migrasi untuk pangkalan data sedia ada (jika jadual pengundi
-- sudah wujud tanpa lajur baharu ini, jalankan ALTER berikut
-- dan bukan DROP/CREATE di atas):
-- ---------------------------------------------------------
-- ALTER TABLE pengundi
--   ADD COLUMN NoSiri VARCHAR(20) NULL AFTER NamaDM,
--   ADD COLUMN TempatMengundi VARCHAR(150) NOT NULL DEFAULT '' AFTER NoSiri,
--   ADD COLUMN Saluran VARCHAR(10) NOT NULL DEFAULT '' AFTER TempatMengundi,
--   ADD INDEX idx_tempatmengundi (TempatMengundi);