-- ============================================================
--  BEASISWA ORANGTUA ASUH · PAROKI HKY JONGGOL
--  Import file ini ke phpMyAdmin: pilih database lalu klik Import
--  Versi: 1.0  |  Engine: InnoDB  |  Charset: utf8mb4
-- ============================================================

CREATE DATABASE IF NOT EXISTS `lspiimco_hky`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `lspiimco_hky`;

-- ─────────────────────────────────────────
--  TABLE: admin_users
-- ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `admin_users` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `username`   VARCHAR(50)  NOT NULL UNIQUE,
  `password`   VARCHAR(255) NOT NULL COMMENT 'bcrypt hash via password_hash()',
  `full_name`  VARCHAR(120) NOT NULL,
  `email`      VARCHAR(120) DEFAULT NULL,
  `role`       ENUM('superadmin','admin','viewer') NOT NULL DEFAULT 'admin',
  `last_login` TIMESTAMP    NULL DEFAULT NULL,
  `created_at` TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Admin awal dibuat oleh setup.php (bukan di sini)
-- Jalankan setup.php satu kali untuk membuat akun admin pertama

-- ─────────────────────────────────────────
--  TABLE: scholars (Penerima Beasiswa)
-- ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `scholars` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`          VARCHAR(150) NOT NULL,
  `pob`           VARCHAR(100) DEFAULT NULL COMMENT 'Tempat Lahir',
  `dob`           DATE         DEFAULT NULL COMMENT 'Tanggal Lahir',
  `address`       TEXT         DEFAULT NULL,
  `phone`         VARCHAR(30)  NOT NULL,
  `email`         VARCHAR(120) DEFAULT NULL,
  `kk_katolik`    VARCHAR(60)  NOT NULL COMMENT 'No. KK Katolik',
  `status`        ENUM('Aktif','Lulus','Tidak Aktif') NOT NULL DEFAULT 'Aktif',
  `school`        VARCHAR(150) NOT NULL,
  `grade`         VARCHAR(60)  DEFAULT NULL,
  `major`         VARCHAR(100) DEFAULT NULL,
  `year_in`       SMALLINT     DEFAULT NULL,
  `year_out`      SMALLINT     DEFAULT NULL,
  `father_name`   VARCHAR(150) DEFAULT NULL,
  `father_job`    VARCHAR(100) DEFAULT NULL,
  `mother_name`   VARCHAR(150) DEFAULT NULL,
  `mother_job`    VARCHAR(100) DEFAULT NULL,
  `parent_phone`  VARCHAR(30)  DEFAULT NULL,
  `family_income` VARCHAR(80)  DEFAULT NULL,
  `notes`         TEXT         DEFAULT NULL,
  `created_at`    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_status` (`status`),
  INDEX `idx_name`   (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `scholars`
  (`name`,`pob`,`dob`,`address`,`phone`,`email`,`kk_katolik`,`status`,
   `school`,`grade`,`major`,`year_in`,`year_out`,
   `father_name`,`father_job`,`mother_name`,`mother_job`,
   `parent_phone`,`family_income`,`notes`)
VALUES
  ('Maria Benedikta Safitri','Jonggol','2007-03-14',
   'Kp. Cibangkong RT 02/03, Jonggol','0812-3456-7890','maria.safitri@gmail.com',
   'KK-2024-001','Aktif','SMAN 1 Jonggol','Kelas XI','IPA',2023,2026,
   'Yohanes Safitri','Petani','Theresia Wati','Ibu Rumah Tangga',
   '0878-1234-5678','Rp 1.500.000','Siswa berprestasi, juara olimpiade matematika kabupaten.'),

  ('Antonius Rendi Pratama','Bekasi','2008-07-22',
   'Jl. Mekar No. 5, Klapanunggal','0823-4567-8901','rendi.pratama@mail.com',
   'KK-2024-002','Aktif','SMPN 2 Jonggol','Kelas IX','Reguler',2022,2025,
   'Petrus Pratama','Buruh Pabrik','Katarina Sari','Pedagang',
   '0857-9876-5432','Rp 2.000.000','Aktif di kegiatan misdinar paroki.'),

  ('Fransisca Dewi Lestari','Bogor','2005-11-05',
   'Kp. Batu Nunggul RT 04/01, Cileungsi','0836-5678-9012','fransisca.dewi@gmail.com',
   'KK-2024-003','Aktif','SMK Negeri 1 Cileungsi','Kelas XII','Akuntansi',2022,2025,
   'Blasius Lestari','Ojek Online','Veronika Heni','Ibu Rumah Tangga',
   '0819-2345-6789','Rp 1.800.000','Kandidat beasiswa perguruan tinggi.');

-- ─────────────────────────────────────────
--  TABLE: donors (Orangtua Asuh)
-- ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `donors` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`           VARCHAR(150) NOT NULL,
  `address`        TEXT         DEFAULT NULL,
  `phone`          VARCHAR(30)  NOT NULL,
  `email`          VARCHAR(120) NOT NULL,
  `kk_katolik`     VARCHAR(60)  NOT NULL,
  `status`         ENUM('Aktif','Tidak Aktif') NOT NULL DEFAULT 'Aktif',
  `company`        VARCHAR(150) DEFAULT NULL,
  `position`       VARCHAR(100) DEFAULT NULL,
  `field`          VARCHAR(100) DEFAULT NULL,
  `job_desc`       TEXT         DEFAULT NULL,
  `monthly_amount` DECIMAL(14,2) NOT NULL DEFAULT 0,
  `payment_method` ENUM('Transfer Bank','Tunai','QRIS') NOT NULL DEFAULT 'Transfer Bank',
  `joined_date`    DATE          DEFAULT NULL,
  `scholar_ref`    VARCHAR(150)  DEFAULT NULL COMMENT 'Nama anak asuh yang diasuh',
  `notes`          TEXT          DEFAULT NULL,
  `created_at`     TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_status` (`status`),
  INDEX `idx_name`   (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `donors`
  (`name`,`address`,`phone`,`email`,`kk_katolik`,`status`,
   `company`,`position`,`field`,`job_desc`,
   `monthly_amount`,`payment_method`,`joined_date`,`scholar_ref`,`notes`)
VALUES
  ('Stefanus Hendra Wijaya','Jl. Kamboja No. 12, Jonggol',
   '0811-2233-4455','hendra.wijaya@gmail.com','KK-2024-101','Aktif',
   'PT. Mitra Sejahtera','Direktur Utama','Kontraktor',
   'Kontraktor bangunan & infrastruktur wilayah Bogor Timur',
   1000000,'Transfer Bank','2023-01-15','Maria Benedikta Safitri',
   'Donatur tetap sejak program dimulai.'),

  ('Agustina Meliani Susanto','Kompleks Villa Jonggol Blok B No. 7',
   '0821-3344-5566','agustina.mel@yahoo.com','KK-2024-102','Aktif',
   'Apotek Bunda Sehat','Apoteker / Pemilik','Farmasi & Kesehatan',
   'Pemilik apotek dan praktisi kesehatan masyarakat',
   750000,'QRIS','2023-03-01','Antonius Rendi Pratama',
   'Aktif dalam kegiatan kesehatan paroki.'),

  ('Yohanes Budi Santoso','Jl. Pahlawan RT 03/05, Cileungsi',
   '0817-4455-6677','budi.santoso@gmail.com','KK-2024-103','Aktif',
   'UD Maju Bersama','Pemilik','Perdagangan',
   'Usaha perdagangan sembako dan barang kebutuhan pokok',
   500000,'Tunai','2023-06-10','Fransisca Dewi Lestari',
   'Pembayaran dilakukan setiap tanggal 10.');

-- ─────────────────────────────────────────
--  TABLE: invoices (Tagihan Bulanan)
-- ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `invoices` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `donor_id`    INT UNSIGNED NOT NULL,
  `month`       TINYINT      NOT NULL COMMENT '1-12',
  `year`        SMALLINT     NOT NULL,
  `amount`      DECIMAL(14,2) NOT NULL,
  `status`      ENUM('Lunas','Belum Lunas') NOT NULL DEFAULT 'Belum Lunas',
  `paid_date`   DATE          DEFAULT NULL,
  `method`      ENUM('Transfer Bank','Tunai','QRIS') NOT NULL DEFAULT 'Transfer Bank',
  `notes`       TEXT          DEFAULT NULL,
  `created_at`  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`donor_id`) REFERENCES `donors`(`id`) ON DELETE CASCADE,
  INDEX `idx_donor`  (`donor_id`),
  INDEX `idx_period` (`year`,`month`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `invoices` (`donor_id`,`month`,`year`,`amount`,`status`,`paid_date`,`method`,`notes`) VALUES
  (1,1,2025,1000000,'Lunas','2025-01-10','Transfer Bank','Pembayaran tepat waktu'),
  (2,1,2025,750000,'Lunas','2025-01-12','QRIS',''),
  (3,1,2025,500000,'Lunas','2025-01-10','Tunai',''),
  (1,2,2025,1000000,'Lunas','2025-02-08','Transfer Bank',''),
  (2,2,2025,750000,'Belum Lunas',NULL,'QRIS','Belum konfirmasi'),
  (3,2,2025,500000,'Lunas','2025-02-10','Tunai','');

-- ─────────────────────────────────────────
--  VIEW: invoice_detail (untuk laporan)
-- ─────────────────────────────────────────
CREATE OR REPLACE VIEW `v_invoice_detail` AS
SELECT
  i.id,
  i.donor_id,
  d.name          AS donor_name,
  d.kk_katolik    AS donor_kk,
  d.phone         AS donor_phone,
  d.email         AS donor_email,
  d.address       AS donor_address,
  d.company       AS donor_company,
  d.scholar_ref,
  i.month,
  i.year,
  i.amount,
  i.status,
  i.paid_date,
  i.method,
  i.notes,
  i.created_at
FROM `invoices` i
LEFT JOIN `donors` d ON d.id = i.donor_id;
