-- ============================================================
-- Smart Laboratory IoT - Database Schema
-- ============================================================

CREATE DATABASE IF NOT EXISTS smart_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE smart_lab;

-- Status Laboratorium (single row, id='main')
CREATE TABLE IF NOT EXISTS laboratory_status (
    id           VARCHAR(10)  PRIMARY KEY DEFAULT 'main',
    status       ENUM('occupied','idle') DEFAULT 'idle',
    occupied_by  VARCHAR(100) DEFAULT '',
    last_activity VARCHAR(200) DEFAULT '',
    updated_at   DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
INSERT IGNORE INTO laboratory_status (id) VALUES ('main');

-- Status Perangkat / Devices (single row, id='main')
CREATE TABLE IF NOT EXISTS devices (
    id          VARCHAR(10)  PRIMARY KEY DEFAULT 'main',
    master_power TINYINT(1)  DEFAULT 0,
    lamp        TINYINT(1)   DEFAULT 0,
    computer    TINYINT(1)   DEFAULT 0,
    ac          TINYINT(1)   DEFAULT 0,
    ac_mode     ENUM('auto','manual') DEFAULT 'auto',
    updated_at  DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
INSERT IGNORE INTO devices (id) VALUES ('main');

-- Log Suhu & Kelembaban
CREATE TABLE IF NOT EXISTS temperature_logs (
    id          INT          AUTO_INCREMENT PRIMARY KEY,
    temperature DECIMAL(5,2) NOT NULL,
    humidity    DECIMAL(5,2) NOT NULL,
    timestamp   DATETIME     DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_timestamp (timestamp)
);

-- Log Energi Listrik
CREATE TABLE IF NOT EXISTS energy_logs (
    id             INT          AUTO_INCREMENT PRIMARY KEY,
    energy_today   DECIMAL(8,4) DEFAULT 0,
    energy_week    DECIMAL(8,4) DEFAULT 0,
    energy_month   DECIMAL(8,4) DEFAULT 0,
    estimated_kwh  DECIMAL(8,4) DEFAULT 0,
    voltage        DECIMAL(6,2) DEFAULT 220,
    timestamp      DATETIME     DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_timestamp (timestamp)
);

-- Log Aktivitas Akses (RFID Petugas)
CREATE TABLE IF NOT EXISTS activity_logs (
    id           INT          AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) DEFAULT '-',
    nim          VARCHAR(20)  DEFAULT '-',
    rfid_uid     VARCHAR(50)  NOT NULL,
    activity     VARCHAR(50)  DEFAULT 'check_in',
    check_in     DATETIME     DEFAULT NULL,
    check_out    DATETIME     DEFAULT NULL,
    duration     VARCHAR(20)  DEFAULT '-',
    timestamp    DATETIME     DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_timestamp (timestamp)
);

-- Scan RFID (raw log dari ESP32)
CREATE TABLE IF NOT EXISTS rfid_scans (
    id            INT          AUTO_INCREMENT PRIMARY KEY,
    rfid_uid      VARCHAR(50)  NOT NULL,
    staff_name    VARCHAR(100) DEFAULT 'Unknown',
    access_status ENUM('granted','denied') DEFAULT 'denied',
    scan_time     DATETIME     DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_scan_time (scan_time)
);

-- Notifikasi
CREATE TABLE IF NOT EXISTS notifications (
    id        INT          AUTO_INCREMENT PRIMARY KEY,
    title     VARCHAR(100) NOT NULL,
    message   TEXT,
    type      VARCHAR(20)  DEFAULT 'info',
    status    ENUM('read','unread') DEFAULT 'unread',
    timestamp DATETIME     DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_timestamp (timestamp)
);

-- Pengaturan Sistem (single row, id='main')
CREATE TABLE IF NOT EXISTS settings (
    id                     VARCHAR(10)  PRIMARY KEY DEFAULT 'main',
    temperature_on         DECIMAL(5,2) DEFAULT 27,
    temperature_off        DECIMAL(5,2) DEFAULT 24,
    auto_mode              TINYINT(1)   DEFAULT 1,
    lab_name               VARCHAR(100) DEFAULT 'Smart Laboratory',
    working_hour_start     VARCHAR(5)   DEFAULT '08:00',
    working_hour_end       VARCHAR(5)   DEFAULT '17:00',
    update_interval_minutes INT         DEFAULT 5
);
INSERT IGNORE INTO settings (id) VALUES ('main');