-- ============================================================
-- Visitor SIPERI Plugin - Install Script (Fixed & Safe Edition)
-- ============================================================

-- ------------------------------------------------------------
-- 1. Tabel visitor_users (Login Khusus Operator/Pustakawan)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `visitor_users` (
    `user_id` int(11) NOT NULL AUTO_INCREMENT,
    `username` varchar(50) NOT NULL,
    `password` varchar(255) NOT NULL,
    `fullname` varchar(100) NOT NULL,
    `email` varchar(100) NULL,
    `role` enum('superadmin','pustakawan','operator') DEFAULT 'operator',
    `library_id` int(11) NULL COMMENT 'Untuk pembatasan hak akses per perpustakaan',
    `is_active` tinyint(1) DEFAULT 1,
    `last_login` datetime NULL,
    `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
    `created_by` varchar(50) NULL,
    PRIMARY KEY (`user_id`),
    UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- 2. Tabel visitor_attendance (Data Log Absensi Kunjungan)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `visitor_attendance` (
    `attendance_id` int(11) NOT NULL AUTO_INCREMENT,
    
    -- Data Perpustakaan (Copy snapshot dari mst_library)
    `library_id` int(11) NOT NULL,
    `library_name` varchar(100) NOT NULL,
    `library_code` varchar(20) NOT NULL,
    
    -- Data Ruangan (Copy snapshot dari mst_visitor_room)
    `room_id` int(11) NOT NULL,
    `room_name` varchar(100) NOT NULL,
    `room_code` varchar(20) NOT NULL,
    
    -- Data Member (Jika jalur member, copy snapshot dari tabel member)
    `member_id` varchar(20) NULL,
    `member_name` varchar(255) NULL,
    `member_institution` varchar(100) NULL,
    `member_email` varchar(100) NULL,
    
    -- Data Guest (Jika jalur pengunjung umum)
    `is_guest` tinyint(1) DEFAULT 0,
    `guest_name` varchar(100) NULL,
    `guest_institution` varchar(100) NULL,
    `guest_phone` varchar(20) NULL,
    `guest_purpose` text NULL,
    
    -- Waktu Kunjungan
    `checkin_date` datetime NOT NULL,
    
    -- Status & Metadata
    `status` enum('active','cancelled') DEFAULT 'active',
    `created_by` varchar(50) NULL,
    `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (`attendance_id`),
    INDEX `library_id` (`library_id`),
    INDEX `room_id` (`room_id`),
    INDEX `member_id` (`member_id`),
    INDEX `checkin_date` (`checkin_date`),
    INDEX `is_guest` (`is_guest`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- 3. Tabel visitor_settings (Konfigurasi Sistem)
--    Wajib menggunakan UNIQUE KEY agar fitur REPLACE INTO di SLiMS berjalan normal
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `visitor_settings` (
    `setting_id` int(11) NOT NULL AUTO_INCREMENT,
    `library_id` int(11) NOT NULL,
    `setting_key` varchar(50) NOT NULL,
    `setting_value` text,
    `description` varchar(255),
    `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`setting_id`),
    UNIQUE KEY `uk_library_key` (`library_id`, `setting_key`),
    KEY `library_id` (`library_id`),
    KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- 4. Inisialisasi Data Pengaturan Default Berbasis Multi-Library
-- ------------------------------------------------------------
INSERT IGNORE INTO `visitor_settings` (`library_id`, `setting_key`, `setting_value`, `description`)
SELECT library_id, 'session_timeout', '3600', 'Session timeout in seconds' FROM mst_library;

INSERT IGNORE INTO `visitor_settings` (`library_id`, `setting_key`, `setting_value`, `description`)
SELECT library_id, 'allow_guest', '1', 'Allow guest/non-member check-in (1=Yes, 0=No)' FROM mst_library;

INSERT IGNORE INTO `visitor_settings` (`library_id`, `setting_key`, `setting_value`, `description`)
SELECT library_id, 'checkin_limit', '1', 'Max check-in per day per member' FROM mst_library;