-- ============================================
-- FaceLogX - Face Recognition Migration
-- Run this on an existing facelogx database
-- to add Raspberry Pi face recognition support
-- ============================================

USE facelogx;

-- Add event_id to attendance_sessions table
ALTER TABLE attendance_sessions
  ADD COLUMN IF NOT EXISTS event_id INT NULL AFTER is_active;

-- Add foreign key for event_id
ALTER TABLE attendance_sessions
  ADD CONSTRAINT fk_session_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE SET NULL;

-- Add new columns to cameras table
ALTER TABLE cameras
  ADD COLUMN IF NOT EXISTS device_index INT NOT NULL DEFAULT 0 AFTER name,
  ADD COLUMN IF NOT EXISTS location VARCHAR(200) DEFAULT '' AFTER device_index,
  ADD COLUMN IF NOT EXISTS lcd_address VARCHAR(10) DEFAULT '0x27' AFTER location,
  ADD COLUMN IF NOT EXISTS last_heartbeat TIMESTAMP NULL AFTER is_active,
  ADD COLUMN IF NOT EXISTS status ENUM('online', 'offline', 'error') NOT NULL DEFAULT 'offline' AFTER last_heartbeat,
  ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- Add stream_url to cameras for live MJPEG feeds
ALTER TABLE cameras
  ADD COLUMN IF NOT EXISTS stream_url VARCHAR(255) DEFAULT '' AFTER status;

-- Update existing camera records
UPDATE cameras SET device_index = 0, location = 'Main Entrance', lcd_address = '0x27', is_active = TRUE WHERE id = 1;
UPDATE cameras SET device_index = 2, location = 'Laboratory', lcd_address = '0x26', is_active = TRUE WHERE id = 2;
UPDATE cameras SET device_index = 4, location = 'Hall', lcd_address = '0x25', is_active = TRUE WHERE id = 3;

-- Face Encodings table (128-dimensional vectors)
CREATE TABLE IF NOT EXISTS 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,
  UNIQUE KEY unique_encoding (student_id),
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
);

-- Face Recognition Logs
CREATE TABLE IF NOT EXISTS 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
);

-- Pi Devices registration
CREATE TABLE IF NOT EXISTS pi_devices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  device_name VARCHAR(100) NOT NULL,
  ip_address VARCHAR(45) NOT NULL,
  api_key VARCHAR(255) NOT NULL UNIQUE,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  last_seen TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Add indexes for faster queries
CREATE INDEX IF NOT EXISTS idx_face_logs_timestamp ON face_recognition_logs(timestamp);
CREATE INDEX IF NOT EXISTS idx_face_logs_student ON face_recognition_logs(student_id);
CREATE INDEX IF NOT EXISTS idx_face_logs_camera ON face_recognition_logs(camera_id);
CREATE INDEX IF NOT EXISTS idx_face_encodings_student ON face_encodings(student_id);
CREATE INDEX IF NOT EXISTS idx_attendance_date ON attendance_records(date);
CREATE INDEX IF NOT EXISTS idx_attendance_student ON attendance_records(student_id);
