-- =====================================================================
-- SKParking – Migration v2: Upgrade Tabel Member
-- Jalankan sekali di database MySQL pusat
-- =====================================================================

SET NAMES utf8mb4;

-- ---------------------------------------------------------------------
-- 1. Tambah kolom baru ke tabel member
-- ---------------------------------------------------------------------
ALTER TABLE member
    ADD COLUMN IF NOT EXISTS kategori        ENUM('individu','perusahaan') NOT NULL DEFAULT 'individu'  AFTER status,
    ADD COLUMN IF NOT EXISTS nama_pt         VARCHAR(150) NULL                                          AFTER kategori,
    ADD COLUMN IF NOT EXISTS no_blok         VARCHAR(50)  NULL                                          AFTER nama_pt,
    ADD COLUMN IF NOT EXISTS gratis          TINYINT(1) NOT NULL DEFAULT 0                              AFTER no_blok,
    ADD COLUMN IF NOT EXISTS tarif_tahunan   INT NOT NULL DEFAULT 0                                     AFTER gratis,
    ADD COLUMN IF NOT EXISTS status_bayar    ENUM('lunas','belum_bayar','grace_period') NOT NULL DEFAULT 'lunas' AFTER tarif_tahunan,
    ADD COLUMN IF NOT EXISTS tahun_berlaku   YEAR NOT NULL DEFAULT (YEAR(CURDATE()))                    AFTER status_bayar,
    ADD COLUMN IF NOT EXISTS tgl_peringatan  DATE NULL                                                  AFTER tahun_berlaku,
    ADD COLUMN IF NOT EXISTS catatan         TEXT NULL                                                  AFTER tgl_peringatan;

-- Index untuk filter yang sering dipakai
ALTER TABLE member
    ADD INDEX IF NOT EXISTS idx_kategori     (kategori),
    ADD INDEX IF NOT EXISTS idx_status_bayar (status_bayar),
    ADD INDEX IF NOT EXISTS idx_tahun        (tahun_berlaku),
    ADD INDEX IF NOT EXISTS idx_no_blok      (no_blok);

-- ---------------------------------------------------------------------
-- 2. Seed pengaturan tarif & periode member ke pengaturan_global
-- ---------------------------------------------------------------------
INSERT INTO pengaturan_global (kategori, kunci, nilai, tipe_data, keterangan) VALUES
('tarif',  'tarif_member_mobil_tahunan',          '75000', 'number',  'Tarif member mobil per tahun (Rp)'),
('tarif',  'tarif_member_motor_tahunan',           '50000', 'number',  'Tarif member motor per tahun (Rp)'),
('member', 'kuota_gratis_mobil_per_perusahaan',   '2',     'number',  'Kuota gratis mobil per perusahaan'),
('member', 'kuota_gratis_motor_per_perusahaan',   '1',     'number',  'Kuota gratis motor per perusahaan'),
('member', 'tgl_expire_hari',                     '31',    'number',  'Tanggal expire member (hari dalam bulan)'),
('member', 'tgl_expire_bulan',                    '12',    'number',  'Bulan expire member (12 = Desember)'),
('member', 'grace_period_hari',                   '5',     'number',  'Grace period hari setelah expire sebelum nonaktif'),
('member', 'tgl_peringatan_hari_sebelum_expire',  '11',    'number',  'H-N sebelum expire mulai beri peringatan (default 11 = 20 Des)')
ON DUPLICATE KEY UPDATE nilai = VALUES(nilai), keterangan = VALUES(keterangan);

-- ---------------------------------------------------------------------
-- 3. Backfill data lama: hitung tarif_tahunan berdasarkan jenis_kendaraan
--    Member lama yang belum punya kategori → individu, status_bayar = lunas
-- ---------------------------------------------------------------------
UPDATE member
SET
    tarif_tahunan = CASE
        WHEN jenis_kendaraan = 'mobil' THEN 75000
        WHEN jenis_kendaraan = 'motor' THEN 50000
        ELSE 0
    END,
    tahun_berlaku = YEAR(tanggal_mulai),
    status_bayar  = 'lunas'
WHERE kategori = 'individu' AND tarif_tahunan = 0 AND gratis = 0;

-- ---------------------------------------------------------------------
-- 4. View berguna: ringkasan per perusahaan
-- ---------------------------------------------------------------------
CREATE OR REPLACE VIEW v_ringkasan_perusahaan AS
SELECT
    no_blok,
    nama_pt,
    tahun_berlaku,
    COUNT(*)                                              AS total_member,
    SUM(CASE WHEN jenis_kendaraan = 'mobil' THEN 1 ELSE 0 END) AS total_mobil,
    SUM(CASE WHEN jenis_kendaraan = 'motor' THEN 1 ELSE 0 END) AS total_motor,
    SUM(CASE WHEN status = 'aktif' THEN 1 ELSE 0 END)    AS total_aktif,
    SUM(CASE WHEN status_bayar = 'lunas' THEN 1 ELSE 0 END)    AS sudah_bayar,
    SUM(CASE WHEN status_bayar IN ('belum_bayar','grace_period') THEN 1 ELSE 0 END) AS belum_bayar,
    SUM(CASE WHEN status_bayar = 'lunas' THEN tarif_tahunan ELSE 0 END) AS pendapatan_lunas,
    SUM(CASE WHEN status_bayar IN ('belum_bayar','grace_period') THEN tarif_tahunan ELSE 0 END) AS potensi_pending
FROM member
WHERE kategori = 'perusahaan'
GROUP BY no_blok, nama_pt, tahun_berlaku;

-- ---------------------------------------------------------------------
-- 5. View: estimasi pendapatan member per kategori
-- ---------------------------------------------------------------------
CREATE OR REPLACE VIEW v_estimasi_pendapatan_member AS
SELECT
    tahun_berlaku,
    kategori,
    COUNT(*)                                                    AS total_member,
    SUM(tarif_tahunan)                                          AS total_potensi,
    SUM(CASE WHEN status_bayar = 'lunas' THEN tarif_tahunan ELSE 0 END) AS total_lunas,
    SUM(CASE WHEN status_bayar IN ('belum_bayar','grace_period') THEN tarif_tahunan ELSE 0 END) AS total_pending
FROM member
GROUP BY tahun_berlaku, kategori;