-- FaceLogX Database Schema

CREATE DATABASE IF NOT EXISTS facelogx;
USE facelogx;

-- ============================================
-- System Settings
-- ============================================
CREATE TABLE system_settings (
  id INT PRIMARY KEY DEFAULT 1,
  fine_per_absence DECIMAL(10,2) NOT NULL DEFAULT 50.00,
  morning_start_time TIME NOT NULL DEFAULT '08:00:00',
  afternoon_start_time TIME NOT NULL DEFAULT '13:00:00',
  organization_name VARCHAR(100) NOT NULL DEFAULT 'IT Society',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO system_settings (id) VALUES (1);

-- ============================================
-- Student Folders
-- ============================================
CREATE TABLE student_folders (
  id VARCHAR(50) PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  color VARCHAR(100) NOT NULL DEFAULT 'from-cyan-500 to-blue-500',
  is_default BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO student_folders (id, name, color, is_default) VALUES
('1st-year', '1st Year', 'from-cyan-500 to-blue-500', TRUE),
('2nd-year', '2nd Year', 'from-blue-500 to-indigo-500', TRUE),
('3rd-year', '3rd Year', 'from-indigo-500 to-purple-500', TRUE),
('4th-year', '4th Year', 'from-purple-500 to-violet-500', TRUE);

-- ============================================
-- Attendance Sessions
-- ============================================
CREATE TABLE attendance_sessions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  session_type ENUM('morning', 'afternoon') NOT NULL,
  date DATE NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT FALSE,
  started_at TIMESTAMP NULL,
  ended_at TIMESTAMP NULL,
  event_id INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_session (session_type, date)
);

-- ============================================
-- Attendance Records
-- ============================================
CREATE TABLE attendance_records (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id VARCHAR(20) NOT NULL,
  date DATE NOT NULL,
  morning ENUM('Present', 'Absent') NOT NULL DEFAULT 'Absent',
  afternoon ENUM('Present', 'Absent') NOT NULL DEFAULT 'Absent',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  UNIQUE KEY unique_attendance (student_id, date)
);

-- ============================================
-- Events
-- ============================================
CREATE TABLE events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  date DATE NOT NULL,
  time VARCHAR(50) NOT NULL,
  location VARCHAR(200) NOT NULL,
  description TEXT,
  status ENUM('Upcoming', 'Ongoing', 'Completed') NOT NULL DEFAULT 'Upcoming',
  poster VARCHAR(500) DEFAULT '',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO events (title, date, time, location, description, status) VALUES
('Tech Summit 2026', '2026-05-15', '9:00 AM - 5:00 PM', 'Main Auditorium', 'Annual technology conference featuring industry speakers', 'Upcoming'),
('Hackathon 2026', '2026-06-01', '8:00 AM - 8:00 PM', 'Computer Laboratory', '24-hour coding competition', 'Upcoming'),
('IT Society Orientation', '2026-04-20', '1:00 PM - 4:00 PM', 'Room 301', 'Welcome orientation for new IT Society members', 'Upcoming');

-- ============================================
-- Payments
-- ============================================
CREATE TABLE payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id VARCHAR(20) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  date DATE NOT NULL,
  recorded_by VARCHAR(100) NOT NULL DEFAULT 'Admin',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
);

-- ============================================
-- Cameras (Raspberry Pi connected)
-- ============================================
CREATE TABLE cameras (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  device_index INT NOT NULL DEFAULT 0,
  location VARCHAR(200) DEFAULT '',
  lcd_address VARCHAR(10) DEFAULT '0x27',
  is_active BOOLEAN NOT NULL DEFAULT FALSE,
  last_heartbeat TIMESTAMP NULL,
  status ENUM('online', 'offline', 'error') NOT NULL DEFAULT 'offline',
  stream_url VARCHAR(255) DEFAULT '',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO cameras (name, device_index, location, lcd_address, is_active) VALUES
('Camera 1 - Main Entrance', 0, 'Main Entrance', '0x27', TRUE),
('Camera 2 - Laboratory', 2, 'Laboratory', '0x26', TRUE),
('Camera 3 - Hall', 4, 'Hall', '0x25', TRUE);

-- ============================================
-- Face Encodings (128-d vectors from face_recognition lib)
-- ============================================
CREATE TABLE face_encodings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id VARCHAR(20) NOT NULL,
  encoding TEXT NOT NULL,
  image_path VARCHAR(500) DEFAULT '',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
);

-- ============================================
-- Face Recognition Logs (every recognition event from Pi)
-- ============================================
CREATE TABLE face_recognition_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id VARCHAR(20) NULL,
  camera_id INT NOT NULL,
  recognized BOOLEAN NOT NULL DEFAULT FALSE,
  confidence DECIMAL(5,4) DEFAULT 0,
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE SET NULL,
  FOREIGN KEY (camera_id) REFERENCES cameras(id) ON DELETE CASCADE
);

-- ============================================
-- System Logs
-- ============================================
CREATE TABLE system_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  action VARCHAR(50) NOT NULL,
  user VARCHAR(100) NOT NULL,
  details TEXT NOT NULL
);

INSERT INTO system_logs (action, user, details) VALUES
('Login', 'Admin', 'Admin logged in successfully'),
('Attendance', 'System', 'Morning attendance session started'),
('Payment', 'Admin', 'Payment recorded for Student 2024-00001'),
('Student Added', 'Admin', 'New student Juan Dela Cruz added');

-- ============================================
-- User Settings (per student)
-- ============================================
CREATE TABLE user_settings (
  student_id VARCHAR(20) PRIMARY KEY,
  email_notifications BOOLEAN NOT NULL DEFAULT TRUE,
  push_notifications BOOLEAN NOT NULL DEFAULT TRUE,
  face_recognition BOOLEAN NOT NULL DEFAULT TRUE,
  dark_mode BOOLEAN NOT NULL DEFAULT TRUE,
  language VARCHAR(20) NOT NULL DEFAULT 'English',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
);
