CREATE DATABASE IF NOT EXISTS posyandu_care CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE posyandu_care;

DROP TABLE IF EXISTS inventory_transactions;
DROP TABLE IF EXISTS inventory_items;
DROP TABLE IF EXISTS consultations;
DROP TABLE IF EXISTS articles;
DROP TABLE IF EXISTS schedules;
DROP TABLE IF EXISTS pregnancy_checks;
DROP TABLE IF EXISTS pregnancies;
DROP TABLE IF EXISTS immunization_records;
DROP TABLE IF EXISTS immunization_types;
DROP TABLE IF EXISTS measurements;
DROP TABLE IF EXISTS children;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(160) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','kader','orang_tua') NOT NULL DEFAULT 'orang_tua',
  phone VARCHAR(30) NULL,
  rt_rw VARCHAR(60) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE children (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  name VARCHAR(120) NOT NULL,
  parent_name VARCHAR(120) NOT NULL,
  gender ENUM('Laki-laki','Perempuan') NOT NULL,
  birth_date DATE NOT NULL,
  rt_rw VARCHAR(60) NOT NULL,
  address TEXT NULL,
  phone VARCHAR(30) NULL,
  notes TEXT NULL,
  vitamin_a TINYINT(1) NOT NULL DEFAULT 0,
  deworming TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_children_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE measurements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  child_id INT NOT NULL,
  measured_at DATE NOT NULL,
  age_month INT NOT NULL,
  weight DECIMAL(5,2) NOT NULL,
  height DECIMAL(5,2) NOT NULL,
  head_circumference DECIMAL(5,2) NOT NULL,
  officer_id INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_measurements_child FOREIGN KEY (child_id) REFERENCES children(id) ON DELETE CASCADE,
  CONSTRAINT fk_measurements_officer FOREIGN KEY (officer_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE immunization_types (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL UNIQUE,
  recommended_age_month INT NULL
);

CREATE TABLE immunization_records (
  id INT AUTO_INCREMENT PRIMARY KEY,
  child_id INT NOT NULL,
  immunization_type_id INT NOT NULL,
  given_at DATE NULL,
  status ENUM('belum','sudah') NOT NULL DEFAULT 'belum',
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_child_immunization (child_id, immunization_type_id),
  CONSTRAINT fk_immunization_child FOREIGN KEY (child_id) REFERENCES children(id) ON DELETE CASCADE,
  CONSTRAINT fk_immunization_type FOREIGN KEY (immunization_type_id) REFERENCES immunization_types(id) ON DELETE CASCADE
);

CREATE TABLE pregnancies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  mother_name VARCHAR(120) NOT NULL,
  user_id INT NULL,
  gestational_age INT NOT NULL,
  weight DECIMAL(5,2) NOT NULL,
  blood_pressure VARCHAR(30) NOT NULL,
  ttd_taken INT NOT NULL DEFAULT 0,
  risk ENUM('Rendah','Perlu Pantau','Tinggi') NOT NULL DEFAULT 'Rendah',
  next_check DATE NULL,
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_pregnancy_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE pregnancy_checks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pregnancy_id INT NOT NULL,
  checked_at DATE NOT NULL,
  gestational_age INT NOT NULL,
  weight DECIMAL(5,2) NOT NULL,
  blood_pressure VARCHAR(30) NOT NULL,
  ttd_taken INT NOT NULL DEFAULT 0,
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_pregnancy_checks FOREIGN KEY (pregnancy_id) REFERENCES pregnancies(id) ON DELETE CASCADE
);

CREATE TABLE schedules (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(160) NOT NULL,
  schedule_date DATE NOT NULL,
  schedule_time TIME NOT NULL,
  type VARCHAR(100) NOT NULL,
  description TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE articles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(180) NOT NULL,
  category VARCHAR(80) NOT NULL,
  content TEXT NOT NULL,
  reading_time VARCHAR(40) NOT NULL DEFAULT '3 menit',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE consultations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  sender_name VARCHAR(120) NOT NULL,
  question TEXT NOT NULL,
  answer TEXT NULL,
  status ENUM('Menunggu','Dijawab Bidan') NOT NULL DEFAULT 'Menunggu',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_consultation_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE inventory_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  item_name VARCHAR(140) NOT NULL,
  category VARCHAR(80) NOT NULL,
  quantity INT NOT NULL DEFAULT 0,
  unit VARCHAR(40) NOT NULL,
  minimum_stock INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE inventory_transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  inventory_item_id INT NOT NULL,
  type ENUM('masuk','keluar') NOT NULL,
  quantity INT NOT NULL,
  notes TEXT NULL,
  transaction_date DATE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_inventory_transaction_item FOREIGN KEY (inventory_item_id) REFERENCES inventory_items(id) ON DELETE CASCADE
);

INSERT INTO users (name, email, password_hash, role, phone, rt_rw) VALUES
('Admin Posyandu', 'admin@posyandu.test', '$2y$10$QwTqLt7fT3VJFoKhVPm9xO0Wiq.Hpf/MVXxzSN1D1Dglye1hGxHYa', 'admin', '6281111111111', 'RW 03'),
('Kader Melati', 'kader@posyandu.test', '$2y$10$QwTqLt7fT3VJFoKhVPm9xO0Wiq.Hpf/MVXxzSN1D1Dglye1hGxHYa', 'kader', '6282222222222', 'RW 03'),
('Siti Aminah', 'orangtua@posyandu.test', '$2y$10$QwTqLt7fT3VJFoKhVPm9xO0Wiq.Hpf/MVXxzSN1D1Dglye1hGxHYa', 'orang_tua', '6281234567890', 'RT 01/RW 03');

INSERT INTO children (user_id, name, parent_name, gender, birth_date, rt_rw, address, phone, notes, vitamin_a, deworming) VALUES
(3, 'Alya Putri', 'Siti Aminah', 'Perempuan', '2024-04-10', 'RT 01/RW 03', 'Jl. Melati No. 12', '6281234567890', 'Riwayat lahir cukup bulan', 1, 0),
(NULL, 'Bima Pratama', 'Rina Lestari', 'Laki-laki', '2023-11-21', 'RT 02/RW 03', 'Jl. Kenanga No. 9', '6289876543210', 'Perlu pemantauan makan harian', 0, 0),
(NULL, 'Citra Nabila', 'Dewi Kartika', 'Perempuan', '2025-01-07', 'RT 03/RW 04', 'Jl. Anggrek No. 4', '6281122334455', 'Aktif, ASI dan MPASI baik', 1, 0);

INSERT INTO measurements (child_id, measured_at, age_month, weight, height, head_circumference, officer_id) VALUES
(1, '2025-10-10', 18, 9.80, 78.00, 46.10, 2),
(1, '2025-11-10', 19, 10.00, 79.00, 46.30, 2),
(1, '2025-12-10', 20, 10.10, 80.00, 46.50, 2),
(1, '2026-01-10', 21, 10.40, 81.00, 46.80, 2),
(1, '2026-02-10', 22, 10.80, 82.00, 47.00, 2),
(1, '2026-03-10', 23, 11.00, 83.00, 47.20, 2),
(1, '2026-04-10', 24, 11.30, 84.00, 47.40, 2),
(1, '2026-05-10', 25, 11.60, 85.00, 47.50, 2),
(2, '2025-10-10', 23, 9.20, 78.00, 45.50, 2),
(2, '2025-11-10', 24, 9.10, 78.50, 45.70, 2),
(2, '2025-12-10', 25, 9.30, 79.00, 45.80, 2),
(2, '2026-01-10', 26, 9.40, 79.30, 46.00, 2),
(2, '2026-02-10', 27, 9.50, 79.80, 46.10, 2),
(2, '2026-03-10', 28, 9.60, 80.10, 46.10, 2),
(2, '2026-04-10', 29, 9.70, 80.40, 46.20, 2),
(2, '2026-05-10', 30, 9.70, 80.60, 46.30, 2),
(3, '2025-10-10', 9, 7.30, 68.00, 43.20, 2),
(3, '2025-11-10', 10, 7.70, 69.20, 43.60, 2),
(3, '2025-12-10', 11, 8.00, 70.30, 44.00, 2),
(3, '2026-01-10', 12, 8.30, 71.20, 44.30, 2),
(3, '2026-02-10', 13, 8.60, 72.40, 44.60, 2),
(3, '2026-03-10', 14, 8.80, 73.50, 44.80, 2),
(3, '2026-04-10', 15, 9.00, 74.40, 45.00, 2),
(3, '2026-05-10', 16, 9.30, 75.40, 45.20, 2);

INSERT INTO immunization_types (name, recommended_age_month) VALUES
('BCG', 1),
('Hepatitis B', 0),
('Polio 1', 1),
('Polio 2', 2),
('DPT-HB-Hib 1', 2),
('DPT-HB-Hib 2', 3),
('DPT-HB-Hib 3', 4),
('Campak', 9);

INSERT INTO immunization_records (child_id, immunization_type_id, given_at, status) VALUES
(1,1,'2024-05-01','sudah'),(1,2,'2024-04-10','sudah'),(1,3,'2024-05-10','sudah'),(1,4,'2024-06-10','sudah'),(1,5,'2024-06-10','sudah'),(1,6,'2024-07-10','sudah'),(1,7,NULL,'belum'),(1,8,NULL,'belum'),
(2,1,'2023-12-01','sudah'),(2,2,'2023-11-21','sudah'),(2,3,'2023-12-21','sudah'),(2,4,'2024-01-21','sudah'),(2,5,'2024-01-21','sudah'),(2,6,NULL,'belum'),(2,7,NULL,'belum'),(2,8,NULL,'belum'),
(3,1,'2025-02-07','sudah'),(3,2,'2025-01-07','sudah'),(3,3,'2025-02-07','sudah'),(3,4,'2025-03-07','sudah'),(3,5,'2025-03-07','sudah'),(3,6,'2025-04-07','sudah'),(3,7,'2025-05-07','sudah'),(3,8,NULL,'belum');

INSERT INTO pregnancies (mother_name, user_id, gestational_age, weight, blood_pressure, ttd_taken, risk, next_check, notes) VALUES
('Nadia Safitri', NULL, 28, 62.00, '110/75', 54, 'Rendah', '2026-06-14', 'Kontrol rutin'),
('Wulan Prameswari', NULL, 34, 69.00, '135/88', 41, 'Perlu Pantau', '2026-06-08', 'Pantau tekanan darah');

INSERT INTO schedules (title, schedule_date, schedule_time, type, description) VALUES
('Posyandu Melati', '2026-06-10', '08:00:00', 'Penimbangan Bulanan', 'Penimbangan dan pengukuran balita'),
('Imunisasi Campak', '2026-06-17', '09:00:00', 'Imunisasi', 'Layanan imunisasi campak'),
('Kelas Ibu Hamil', '2026-06-22', '15:30:00', 'Edukasi', 'Edukasi ibu hamil dan konsumsi TTD');

INSERT INTO articles (title, category, content, reading_time) VALUES
('MPASI 6 Bulan: Porsi, Tekstur, dan Frekuensi', 'MPASI', 'Mulai MPASI dengan tekstur lumat, porsi kecil, dan naik bertahap sesuai respons anak.', '4 menit'),
('Tanda Risiko Stunting yang Perlu Dipantau', 'Stunting', 'Pantau tinggi badan, berat badan, nafsu makan, serta riwayat infeksi berulang.', '5 menit'),
('Tips Menyusui Saat Ibu Kembali Bekerja', 'ASI', 'Siapkan jadwal perah ASI, penyimpanan yang aman, dan dukungan keluarga.', '3 menit');

INSERT INTO consultations (user_id, sender_name, question, answer, status) VALUES
(3, 'Siti Aminah', 'Anak susah makan sayur, apa alternatifnya?', 'Coba variasikan tekstur dan campur sayur dalam lauk favorit anak.', 'Dijawab Bidan'),
(NULL, 'Rina Lestari', 'Berat anak naik sedikit bulan ini, perlu PMT?', NULL, 'Menunggu');

INSERT INTO inventory_items (item_name, category, quantity, unit, minimum_stock) VALUES
('Vitamin A Biru', 'Vitamin', 78, 'kapsul', 25),
('Vitamin A Merah', 'Vitamin', 44, 'kapsul', 25),
('PMT Biskuit', 'PMT', 18, 'pak', 20),
('Obat Cacing', 'Obat', 22, 'tablet', 15),
('DPT-HB-Hib', 'Vaksin', 11, 'vial', 10);


CREATE TABLE IF NOT EXISTS notification_templates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  category VARCHAR(80) NOT NULL,
  message TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS notification_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  recipient_name VARCHAR(120) NOT NULL,
  phone VARCHAR(30) NOT NULL,
  message TEXT NOT NULL,
  channel ENUM('whatsapp_link','gateway') NOT NULL DEFAULT 'whatsapp_link',
  status VARCHAR(60) NOT NULL DEFAULT 'prepared',
  sent_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_notification_sent_by FOREIGN KEY (sent_by) REFERENCES users(id) ON DELETE SET NULL
);

INSERT INTO notification_templates (name, category, message) VALUES
('Pengingat Penimbangan', 'jadwal', 'Halo {nama_orang_tua}, kami mengingatkan jadwal {judul_jadwal} pada {tanggal} pukul {jam}. Mohon hadir di Posyandu.'),
('Pengingat Imunisasi', 'imunisasi', 'Halo {nama_orang_tua}, jadwal imunisasi anak {nama_anak} sudah mendekati. Silakan datang ke Posyandu sesuai jadwal.'),
('Pantau Gizi Anak', 'gizi', 'Halo {nama_orang_tua}, mohon pantau asupan makan anak {nama_anak}. Kader akan membantu pemantauan tumbuh kembang bulan ini.');