-- ========================================
-- SMART DOOR LOCK - DATABASE SCHEMA
-- ========================================
-- Database: senggolpingmy_smart_door
-- Author: IoT Engineer Team
-- Version: 1.0
-- ========================================

-- Buat database
CREATE DATABASE IF NOT EXISTS senggolpingmy_smart_door CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE senggolpingmy_smart_door;

-- ========================================
-- TABEL 1: rfid_users
-- Menyimpan data pengguna RFID yang terdaftar
-- ========================================
CREATE TABLE `rfid_users` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `rfid_uid` VARCHAR(50) NOT NULL UNIQUE COMMENT 'UID kartu RFID (hashed untuk keamanan)',
  `rfid_uid_raw` VARCHAR(50) NOT NULL COMMENT 'UID asli untuk debugging (optional)',
  `user_name` VARCHAR(100) NOT NULL COMMENT 'Nama pengguna',
  `category` ENUM('Admin', 'Lansia') NOT NULL DEFAULT 'Lansia',
  `phone_number` VARCHAR(20) DEFAULT NULL,
  `email` VARCHAR(100) DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=aktif, 0=nonaktif',
  `special_hours` VARCHAR(50) DEFAULT NULL COMMENT 'Jam akses khusus (misal: 08:00-17:00)',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_rfid_uid` (`rfid_uid`),
  KEY `idx_category` (`category`),
  KEY `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Tabel pengguna RFID yang terdaftar';

-- ========================================
-- TABEL 2: access_logs
-- Menyimpan semua log akses (granted & denied)
-- ========================================
CREATE TABLE `access_logs` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `rfid_uid` VARCHAR(50) NOT NULL COMMENT 'UID kartu yang di-scan',
  `user_name` VARCHAR(100) DEFAULT 'Unknown' COMMENT 'Nama user (jika terdaftar)',
  `status` ENUM('GRANTED', 'DENIED') NOT NULL COMMENT 'Status akses',
  `reason` VARCHAR(200) DEFAULT NULL COMMENT 'Alasan jika ditolak',
  `door_status` ENUM('OPEN', 'LOCKED') NOT NULL DEFAULT 'LOCKED',
  `device_ip` VARCHAR(45) DEFAULT NULL COMMENT 'IP address ESP32/ESP8266',
  `ai_status` ENUM('NORMAL', 'SUSPICIOUS', 'DANGER') DEFAULT 'NORMAL' COMMENT 'Status analisis AI',
  `access_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_rfid_uid` (`rfid_uid`),
  KEY `idx_status` (`status`),
  KEY `idx_access_time` (`access_time`),
  KEY `idx_ai_status` (`ai_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Log semua aktivitas akses pintu';

-- ========================================
-- TABEL 3: telegram_admins
-- Menyimpan admin yang boleh kontrol via Telegram
-- ========================================
CREATE TABLE `telegram_admins` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `telegram_chat_id` VARCHAR(50) NOT NULL UNIQUE COMMENT 'Chat ID Telegram admin',
  `telegram_username` VARCHAR(100) DEFAULT NULL,
  `admin_name` VARCHAR(100) NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `permissions` SET('open_door', 'lock_door', 'view_logs', 'manage_users') DEFAULT 'open_door,lock_door,view_logs',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_chat_id` (`telegram_chat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Admin Telegram yang authorized';

-- ========================================
-- TABEL 4: api_tokens
-- Menyimpan API token untuk keamanan ESP → Server
-- ========================================
CREATE TABLE `api_tokens` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `token` VARCHAR(255) NOT NULL UNIQUE COMMENT 'API Token',
  `device_name` VARCHAR(100) NOT NULL COMMENT 'Nama perangkat',
  `device_ip` VARCHAR(45) DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `last_used` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_token` (`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Token API untuk autentikasi';

-- ========================================
-- TABEL 5: ai_analytics
-- Menyimpan hasil analisis AI
-- ========================================
CREATE TABLE `ai_analytics` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `analysis_type` ENUM('SUSPICIOUS_PATTERN', 'UNUSUAL_HOUR', 'BRUTE_FORCE', 'ANOMALY') NOT NULL,
  `rfid_uid` VARCHAR(50) DEFAULT NULL,
  `details` TEXT COMMENT 'Detail analisis dalam JSON',
  `severity` ENUM('LOW', 'MEDIUM', 'HIGH', 'CRITICAL') NOT NULL DEFAULT 'LOW',
  `action_taken` VARCHAR(200) DEFAULT NULL COMMENT 'Tindakan yang diambil',
  `analyzed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_type` (`analysis_type`),
  KEY `idx_severity` (`severity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Log analisis AI';

-- ========================================
-- INSERT DATA SAMPLE
-- ========================================

-- Sample RFID Users
INSERT INTO `rfid_users` (`rfid_uid`, `rfid_uid_raw`, `user_name`, `category`, `phone_number`, `is_active`) VALUES
('BB1FED48', 'BB1FED48', 'Siti Aminah', 'Lansia', '08198765432', 1);

-- Sample Telegram Admin
INSERT INTO `telegram_admins` (`telegram_chat_id`, `admin_name`, `is_active`, `permissions`) VALUES
('-5028045159', 'Admin Utama', 1, 'open_door,lock_door,view_logs,manage_users');

-- Sample API Token
INSERT INTO `api_tokens` (`token`, `device_name`, `is_active`) VALUES
('Universitas17Agustus1945', 'ESP32-DoorLock-01', 1);

-- ========================================
-- STORED PROCEDURES
-- ========================================

-- Procedure: Validasi RFID
DELIMITER $$
CREATE PROCEDURE sp_validate_rfid(
    IN p_rfid_uid VARCHAR(50),
    IN p_device_ip VARCHAR(45),
    IN p_ai_status VARCHAR(20)
)
BEGIN
    DECLARE v_user_name VARCHAR(100);
    DECLARE v_category VARCHAR(50);
    DECLARE v_is_active TINYINT(1);
    DECLARE v_status VARCHAR(10);
    DECLARE v_door_status VARCHAR(10);
    DECLARE v_reason VARCHAR(200);
    
    -- Cek apakah RFID terdaftar dan aktif
    SELECT user_name, category, is_active
    INTO v_user_name, v_category, v_is_active
    FROM rfid_users
    WHERE rfid_uid = p_rfid_uid
    LIMIT 1;
    
    -- Tentukan status akses
    IF v_user_name IS NOT NULL AND v_is_active = 1 THEN
        SET v_status = 'GRANTED';
        SET v_door_status = 'OPEN';
        SET v_reason = NULL;
    ELSE
        SET v_status = 'DENIED';
        SET v_door_status = 'LOCKED';
        IF v_user_name IS NULL THEN
            SET v_user_name = 'Unknown';
            SET v_reason = 'Unregistered RFID';
        ELSE
            SET v_reason = 'User inactive';
        END IF;
    END IF;
    
    -- Simpan log akses
    INSERT INTO access_logs (rfid_uid, user_name, status, reason, door_status, device_ip, ai_status)
    VALUES (p_rfid_uid, v_user_name, v_status, v_reason, v_door_status, p_device_ip, p_ai_status);
    
    -- Return hasil
    SELECT 
        v_status AS status,
        v_user_name AS user_name,
        v_category AS category,
        v_door_status AS door_status,
        v_reason AS reason;
END$$
DELIMITER ;

-- Procedure: Get AI Analysis
DELIMITER $$
CREATE PROCEDURE sp_get_ai_analysis()
BEGIN
    -- Hitung statistik akses
    SELECT 
        COUNT(*) AS total_access,
        SUM(CASE WHEN status = 'GRANTED' THEN 1 ELSE 0 END) AS granted_count,
        SUM(CASE WHEN status = 'DENIED' THEN 1 ELSE 0 END) AS denied_count,
        SUM(CASE WHEN ai_status = 'SUSPICIOUS' THEN 1 ELSE 0 END) AS suspicious_count,
        SUM(CASE WHEN ai_status = 'DANGER' THEN 1 ELSE 0 END) AS danger_count,
        MAX(access_time) AS last_access_time
    FROM access_logs
    WHERE access_time >= DATE_SUB(NOW(), INTERVAL 24 HOUR);
    
    -- Daftar akses mencurigakan
    SELECT 
        rfid_uid,
        user_name,
        COUNT(*) AS attempt_count,
        MAX(access_time) AS last_attempt
    FROM access_logs
    WHERE status = 'DENIED' 
        AND access_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
    GROUP BY rfid_uid, user_name
    HAVING attempt_count > 3;
END$$
DELIMITER ;

-- ========================================
-- INDEXES untuk performa optimal
-- ========================================
ALTER TABLE access_logs ADD INDEX idx_composite (rfid_uid, access_time, status);

-- ========================================
-- VIEWS untuk reporting
-- ========================================

-- View: Access Summary Today
CREATE OR REPLACE VIEW v_access_summary_today AS
SELECT 
    DATE(access_time) AS access_date,
    status,
    COUNT(*) AS total_count,
    COUNT(DISTINCT rfid_uid) AS unique_users
FROM access_logs
WHERE DATE(access_time) = CURDATE()
GROUP BY DATE(access_time), status;

-- View: Suspicious Activity
CREATE OR REPLACE VIEW v_suspicious_activity AS
SELECT 
    rfid_uid,
    user_name,
    ai_status,
    COUNT(*) AS alert_count,
    MAX(access_time) AS last_alert
FROM access_logs
WHERE ai_status IN ('SUSPICIOUS', 'DANGER')
    AND access_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY rfid_uid, user_name, ai_status
ORDER BY last_alert DESC;

-- ========================================
-- TRIGGERS untuk audit trail
-- ========================================

-- Trigger: Auto cleanup old logs (keep only last 90 days)
DELIMITER $$
CREATE EVENT evt_cleanup_old_logs
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
BEGIN
    DELETE FROM access_logs 
    WHERE access_time < DATE_SUB(NOW(), INTERVAL 90 DAY);
    
    -- Log cleanup action
    INSERT INTO ai_analytics (analysis_type, details, severity)
    VALUES ('ANOMALY', 'Auto cleanup executed', 'LOW');
END$$
DELIMITER ;

-- ========================================
-- SECURITY NOTES
-- ========================================
-- 1. Gunakan prepared statements di PHP untuk mencegah SQL Injection
-- 2. Encrypt/hash rfid_uid sebelum disimpan
-- 3. Gunakan HTTPS untuk semua komunikasi
-- 4. Rotate API token secara berkala
-- 5. Monitor tabel access_logs untuk deteksi anomali
-- 6. Backup database secara regular
-- ========================================