-- =============================================
-- LICS 2026 — Database Schema
-- =============================================
SET NAMES utf8mb4;
SET time_zone = '+00:00';

DROP TABLE IF EXISTS activity_logs;
DROP TABLE IF EXISTS tamu;
DROP TABLE IF EXISTS events;
DROP TABLE IF EXISTS admin_users;

CREATE TABLE admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(150),
    role ENUM('superadmin','admin','staff') DEFAULT 'admin',
    last_login DATETIME,
    created_at DATETIME DEFAULT NOW()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama_acara VARCHAR(255) NOT NULL,
    tanggal DATE,
    waktu_mulai TIME,
    waktu_selesai TIME,
    lokasi VARCHAR(255),
    deskripsi TEXT,
    status ENUM('draft','active','completed') DEFAULT 'active',
    created_at DATETIME DEFAULT NOW()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE tamu (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_id INT NOT NULL,
    nama VARCHAR(250) NOT NULL,
    nim VARCHAR(50),
    status_kehadiran ENUM('belum_checkin','hadir','tidak_hadir') DEFAULT 'belum_checkin',
    waktu_checkin DATETIME,
    created_at DATETIME DEFAULT NOW(),
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT,
    event_id INT,
    action VARCHAR(100),
    description TEXT,
    ip_address VARCHAR(50),
    created_at DATETIME DEFAULT NOW()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Admin: password = admin123
INSERT INTO admin_users (username, password, full_name, role) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Administrator LICS 2026', 'superadmin');

-- Event LICS 2026
INSERT INTO events (nama_acara, tanggal, waktu_mulai, waktu_selesai, lokasi, status) VALUES
('LEGAL INTERNATIONAL CONFERENCE & STUDIES 9TH (LICS 2026) AND CALL FOR PAPERS-PROCEEDINGS',
 '2026-01-01', '08:00:00', '17:00:00', 'UNISSULA', 'active');

-- Data Tamu (92 peserta)
INSERT INTO tamu (event_id, nama, nim, status_kehadiran) VALUES
(1, 'Dwita Srihapsari', '10302300186', 'belum_checkin'),
(1, 'Yekti Mahardika', '10302300224', 'belum_checkin'),
(1, 'Fausi', '10302300249', 'belum_checkin'),
(1, 'Silvia Desty R', '10302300287', 'belum_checkin'),
(1, 'Dorprawati Siburian', '10302300265', 'belum_checkin'),
(1, 'Sukamto', '10302300291', 'belum_checkin'),
(1, 'Faisal Amri Nasution', '10302300400', 'belum_checkin'),
(1, 'Muhammad Ridha', '10302300373', 'belum_checkin'),
(1, 'Ibrahim Nasrul Fourida', '10302300193', 'belum_checkin'),
(1, 'Desi Kumalasari', '', 'belum_checkin'),
(1, 'Ridwan', '10302300208', 'belum_checkin'),
(1, 'Ade Alfriyan Rumrijono', '10302300173', 'belum_checkin'),
(1, 'Mujianto', '10302500013', 'belum_checkin'),
(1, 'Diyani Indrawati', '10302300262', 'belum_checkin'),
(1, 'Febie Dwi Hartanto', '10302300269', 'belum_checkin'),
(1, 'Pudiastuti Citra Adi', '10302300283', 'belum_checkin'),
(1, 'Carrel Ticualu', '10302300253', 'belum_checkin'),
(1, 'Yayan Supiani', '10302300338', 'belum_checkin'),
(1, 'Rudy Febrianto Wibowo', '10302300352', 'belum_checkin'),
(1, 'Rizal Edison', '10302300284', 'belum_checkin'),
(1, 'Herwansyah', '10302300272', 'belum_checkin'),
(1, 'Munzaroh', '10302300280', 'belum_checkin'),
(1, 'Ahmadianto Saputro', '10302300252', 'belum_checkin'),
(1, 'Robi Gotama', '10302300210', 'belum_checkin'),
(1, 'Irvan Suhardika Putra', '10302300274', 'belum_checkin'),
(1, 'Faresh Emha', '10302300267', 'belum_checkin'),
(1, 'Beny Samodra Triambodo', '10302300182', 'belum_checkin'),
(1, 'Teguh Setiawan', '10302300292', 'belum_checkin'),
(1, 'Adi Akbar', '', 'belum_checkin'),
(1, 'Slamet Riyono', '10302300288', 'belum_checkin'),
(1, 'Sono Prabowo', '10302300214', 'belum_checkin'),
(1, 'Sukriyanto', '10302300307', 'belum_checkin'),
(1, 'Mujiono', '', 'belum_checkin'),
(1, 'Ganesh Deo Gangga', '10302300311', 'belum_checkin'),
(1, 'Sugito', '10302300290', 'belum_checkin'),
(1, 'Christian Tonato', '10302300258', 'belum_checkin'),
(1, 'Agus Taufikurrahman', '10302300177', 'belum_checkin'),
(1, 'Mahpudin', '10302300351', 'belum_checkin'),
(1, 'Bagus Zuhri', '10302300181', 'belum_checkin'),
(1, 'Irham Virdi', '10302300198', 'belum_checkin'),
(1, 'Fajar Muttaqien', '10302300190', 'belum_checkin'),
(1, 'Liza Priandini', '10302300200', 'belum_checkin'),
(1, 'Lorina', '10302300372', 'belum_checkin'),
(1, 'Zuhdi Arman', '10302300299', 'belum_checkin'),
(1, 'Wachyu Setiawan', '10302300294', 'belum_checkin'),
(1, 'Muhammad Tawil', '10302300206', 'belum_checkin'),
(1, 'Sulisyadi', '10302300216', 'belum_checkin'),
(1, 'I Gusti Ngurah Yulio Mahendra P', '10302300273', 'belum_checkin'),
(1, 'Angga Dhielayaksya', '10302300179', 'belum_checkin'),
(1, 'Laode Muhammad Ilwan', '10302300275', 'belum_checkin'),
(1, 'Dodik Hermawan', '10302300263', 'belum_checkin'),
(1, 'Agus Riyanto', '10302300251', 'belum_checkin'),
(1, 'Slamet Riyadi', '', 'belum_checkin'),
(1, 'R. Hendral, S.H. M.H', '10302400141', 'belum_checkin'),
(1, 'Muh Mansur', '10302400047', 'belum_checkin'),
(1, 'Muhammad Khutub', '10302400173', 'belum_checkin'),
(1, 'Yessiarie Silvanny Sibot', '10302400114', 'belum_checkin'),
(1, 'Muhammad Rizky Pratama', '10302400103', 'belum_checkin'),
(1, 'Dyah Ayu Sulistyarini', '10302400162', 'belum_checkin'),
(1, 'Izdilaf Lailal Muna Hadi', '', 'belum_checkin'),
(1, 'Edi Purwanto', '10302400160', 'belum_checkin'),
(1, 'Salihin Ardiansyah', '', 'belum_checkin'),
(1, 'Liena', '10302400101', 'belum_checkin'),
(1, 'Muhammad Rizki Maulana', '10302400102', 'belum_checkin'),
(1, 'Raymund Hasdianto Sihotang', '', 'belum_checkin'),
(1, 'Al Mas''udah', '10302400068', 'belum_checkin'),
(1, 'Efrianto', '10302500248', 'belum_checkin'),
(1, 'Dina Noviani', '10302400005', 'belum_checkin'),
(1, 'Kholid Mawardi', '10302400038', 'belum_checkin'),
(1, 'Abdul Muis Ali', '', 'belum_checkin'),
(1, 'Fifi Lonardy', '10302400229', 'belum_checkin'),
(1, 'Shodiq Munawar,S.ST.,MH', '10302400314', 'belum_checkin'),
(1, 'Nabih Amer S.In, MH', '10302400240', 'belum_checkin'),
(1, 'Harwan Muldidarmawan', '10302400263', 'belum_checkin'),
(1, 'Gannis', '10302400262', 'belum_checkin'),
(1, 'Tutik Nurul Janah', '10302400249', 'belum_checkin'),
(1, 'Agung Cahyo Putro, S.In., M.M.', '10302400223', 'belum_checkin'),
(1, 'Ryan Eka Cahya', '10302400244', 'belum_checkin'),
(1, 'Henu Sistha Aditya', '10302400275', 'belum_checkin'),
(1, 'Indera Dewi', '10302400233', 'belum_checkin'),
(1, 'Ricky Rezamourandy', '10302400274', 'belum_checkin'),
(1, 'Norita', '', 'belum_checkin'),
(1, 'Henny Susilowati', '10302400308', 'belum_checkin'),
(1, 'Romulus SH.,MH.', '10302400243', 'belum_checkin'),
(1, 'Selviany', '10302400276', 'belum_checkin'),
(1, 'Andrian Al Mas’udi, SH., MH', '10302400311', 'belum_checkin'),
(1, 'Faisal Tamin', '10302400315', 'belum_checkin'),
(1, 'Rachmat Ade Putra', '10302400313', 'belum_checkin'),
(1, 'Emil FL', '10302400261', 'belum_checkin'),
(1, 'Akhdiyat SP', '10302400305', 'belum_checkin'),
(1, 'Abdul Holid', '10302400283', 'belum_checkin'),
(1, 'Priyo Nugroho', '10302400273', 'belum_checkin');

-- ALTER untuk existing DB (jalankan jika DB sudah ada sebelumnya)
-- ALTER TABLE tamu ADD COLUMN nim VARCHAR(50) AFTER nama;

-- Jika database lama masih punya kolom foto_selfie, hapus dengan:
-- ALTER TABLE tamu DROP COLUMN foto_selfie;