-- Table: users
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nama VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255),
    google_id VARCHAR(100),
    no_hp VARCHAR(20),
    role ENUM('tamu','petugas','admin_utama','admin_pelaporan','petugas_verifikasi') NOT NULL,
    instansi VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Table: reservasi
CREATE TABLE reservasi (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    asal_instansi VARCHAR(100),
    penanggung_jawab VARCHAR(100),
    kontak_email VARCHAR(100),
    kontak_hp VARCHAR(20),
    perihal VARCHAR(255),
    jenis_kunjungan ENUM('audiensi','rapat','studi banding','lainnya'),
    jumlah_tamu INT,
    tanggal_kunjungan DATE,
    waktu_mulai TIME,
    waktu_selesai TIME,
    status ENUM('draft','menunggu','diterima','ditolak','revisi','cancel'),
    catatan_admin TEXT,
    lampiran VARCHAR(255),
    catatan_tambahan TEXT,
    qr_code VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_arsip BOOLEAN DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Table: reservasi_log
CREATE TABLE reservasi_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    reservasi_id INT NOT NULL,
    status ENUM('menunggu','diterima','ditolak','revisi','cancel'),
    catatan TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (reservasi_id) REFERENCES reservasi(id)
);

-- Table: notifikasi
CREATE TABLE notifikasi (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    reservasi_id INT,
    pesan TEXT,
    status_baca BOOLEAN DEFAULT FALSE,
    waktu_kirim DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (reservasi_id) REFERENCES reservasi(id)
);

-- Table: kehadiran
CREATE TABLE kehadiran (
    id INT PRIMARY KEY AUTO_INCREMENT,
    reservasi_id INT NOT NULL,
    waktu_checkin DATETIME,
    waktu_checkout DATETIME,
    durasi INT,
    petugas_id INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (reservasi_id) REFERENCES reservasi(id),
    FOREIGN KEY (petugas_id) REFERENCES users(id)
);

-- Table: konsumsi
CREATE TABLE konsumsi (
    id INT PRIMARY KEY AUTO_INCREMENT,
    reservasi_id INT NOT NULL,
    snack_asal VARCHAR(100),
    snack_qty INT,
    aqua_qty_dus INT,
    uang_representatif INT,
    input_by INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (reservasi_id) REFERENCES reservasi(id),
    FOREIGN KEY (input_by) REFERENCES users(id)
);

-- Table: daftar_hadir
CREATE TABLE daftar_hadir (
    id INT PRIMARY KEY AUTO_INCREMENT,
    reservasi_id INT NOT NULL,
    nama_tamu VARCHAR(100),
    instansi VARCHAR(100),
    signature VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (reservasi_id) REFERENCES reservasi(id)
);

-- Table: notulen
CREATE TABLE notulen (
    id INT PRIMARY KEY AUTO_INCREMENT,
    reservasi_id INT NOT NULL,
    ringkasan TEXT,
    tindak_lanjut TEXT,
    input_by INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (reservasi_id) REFERENCES reservasi(id),
    FOREIGN KEY (input_by) REFERENCES users(id)
);

-- Table: audit_log
CREATE TABLE audit_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    aksi VARCHAR(50),
    tabel VARCHAR(50),
    data_lama TEXT,
    data_baru TEXT,
    waktu DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Table: arsip_data (opsional, jika ingin tracking arsip detail)
CREATE TABLE arsip_data (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tabel VARCHAR(50),
    data TEXT,
    waktu_arsip DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- =========================
-- REVISI PERANCANGAN DATABASE eTAMU DPRD KOTA KEDIRI
-- =========================
--
-- 1. users
--    - id, nama, email, password, google_id, no_hp, role (tamu, petugas, admin_utama, admin_pelaporan, petugas_verifikasi), instansi, created_at, updated_at
-- 2. reservasi
--    - id, user_id, asal_instansi, penanggung_jawab, kontak_email, kontak_hp, perihal, jenis_kunjungan, jumlah_tamu, tanggal_kunjungan, waktu_mulai, waktu_selesai, status, catatan_admin, lampiran, catatan_tambahan, qr_code, is_arsip, created_at, updated_at
-- 3. reservasi_log
--    - id, reservasi_id, status, catatan, created_at
-- 4. notifikasi
--    - id, user_id, reservasi_id, pesan, status_baca, waktu_kirim
-- 5. kehadiran
--    - id, reservasi_id, waktu_checkin, waktu_checkout, durasi, petugas_id, created_at
-- 6. konsumsi
--    - id, reservasi_id, snack_asal, snack_qty, aqua_qty_dus, uang_representatif, input_by, created_at
-- 7. daftar_hadir
--    - id, reservasi_id, nama_tamu, instansi, signature, created_at
-- 8. notulen
--    - id, reservasi_id, ringkasan, tindak_lanjut, input_by, created_at
-- 9. audit_log
--    - id, user_id, aksi, tabel, data_lama, data_baru, waktu
-- 10. arsip_data (opsional)
--    - id, tabel, data, waktu_arsip
--
-- Fitur backup/restore dilakukan di level database (misal: mysqldump, cron job, dsb)

-- =========================
-- SEED DATA
-- =========================

-- Users
-- Password: 'admin123' (MD5: 0192023a7bbd73250516f069df18b500)
INSERT INTO users (nama, email, password, google_id, no_hp, role, instansi) VALUES
('Admin DPRD', 'admin@dprd.go.id', '0192023a7bbd73250516f069df18b500', NULL, '081234567890', 'admin', NULL),
('Petugas 1', 'petugas1@dprd.go.id', '0192023a7bbd73250516f069df18b500', NULL, '081234567891', 'petugas', NULL),
('Tamu A', 'tamuA@instansi.com', '0192023a7bbd73250516f069df18b500', 'google-uid-123', '081234567892', 'tamu', 'Instansi A');

-- Reservasi
INSERT INTO reservasi (user_id, asal_instansi, penanggung_jawab, kontak_email, kontak_hp, perihal, jenis_kunjungan, jumlah_tamu, tanggal_kunjungan, waktu_mulai, waktu_selesai, status, catatan_admin, lampiran, catatan_tambahan, qr_code)
VALUES
(3, 'Instansi A', 'Tamu A', 'tamuA@instansi.com', '081234567892', 'Audiensi Program', 'audiensi', 5, '2026-04-10', '09:00:00', '10:00:00', 'menunggu', NULL, NULL, NULL, 'QR123A');

-- Reservasi Log
INSERT INTO reservasi_log (reservasi_id, status, catatan) VALUES
(1, 'menunggu', 'Menunggu verifikasi admin');
 admin');

-- Notifikasi
INSERT INTO notifikasi (user_id, reservasi_id, pesan, status_baca) VALUES
(3, 1, 'Reservasi Anda telah diajukan dan menunggu verifikasi.', 0);

-- Kehadiran
INSERT INTO kehadiran (reservasi_id, waktu_checkin, waktu_checkout, durasi, petugas_id) VALUES
(1, NULL, NULL, NULL, 2);

-- Konsumsi
INSERT INTO konsumsi (reservasi_id, snack_asal, snack_qty, aqua_qty_dus, uang_representatif, input_by) VALUES
(1, 'Koperasi DPRD', 5, 1, 100000, 1);

-- Daftar Hadir
INSERT INTO daftar_hadir (reservasi_id, nama_tamu, instansi, signature) VALUES
(1, 'Tamu A', 'Instansi A', NULL);

-- Notulen
INSERT INTO notulen (reservasi_id, ringkasan, tindak_lanjut, input_by) VALUES
(1, 'Audiensi berjalan lancar.', 'Tindak lanjut: follow up via email.', 1);

-- =========================
-- QUERY CONTOH
-- =========================

-- 1. Daftar reservasi beserta status dan nama tamu
SELECT r.id, u.nama AS nama_tamu, r.asal_instansi, r.perihal, r.jenis_kunjungan, r.tanggal_kunjungan, r.status
FROM reservasi r
JOIN users u ON r.user_id = u.id;

-- 2. Daftar tamu harian (untuk petugas)
SELECT r.id, u.nama AS nama_tamu, r.asal_instansi, r.perihal, r.jumlah_tamu, r.tanggal_kunjungan, r.waktu_mulai
FROM reservasi r
JOIN users u ON r.user_id = u.id
WHERE r.tanggal_kunjungan = CURDATE();

-- 3. Rekap bulanan jumlah tamu hadir
SELECT MONTH(r.tanggal_kunjungan) AS bulan, COUNT(*) AS jumlah_reservasi, SUM(r.jumlah_tamu) AS total_tamu
FROM reservasi r
WHERE r.status = 'diterima'
GROUP BY MONTH(r.tanggal_kunjungan);

-- 4. Data konsumsi per reservasi
SELECT r.id, k.snack_asal, k.snack_qty, k.aqua_qty_dus, k.uang_representatif
FROM reservasi r
JOIN konsumsi k ON r.id = k.reservasi_id;

-- 5. Daftar hadir siap cetak untuk reservasi tertentu
SELECT dh.nama_tamu, dh.instansi
FROM daftar_hadir dh
WHERE dh.reservasi_id = 1;