-- Face Recognition System Database Schema
-- MySQL Database Structure

CREATE DATABASE IF NOT EXISTS face_recognition_db;
USE face_recognition_db;

-- Companies table (for multi-tenant support)
CREATE TABLE IF NOT EXISTS companies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    aws_collection_id VARCHAR(255) NOT NULL UNIQUE,
    office_latitude DECIMAL(10, 8) DEFAULT NULL,
    office_longitude DECIMAL(11, 8) DEFAULT NULL,
    geofence_radius INT DEFAULT 100, -- in meters
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Employee faces table (stores AWS Rekognition FaceIds)
CREATE TABLE IF NOT EXISTS employee_faces (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    face_id VARCHAR(255) NOT NULL, -- AWS Rekognition FaceId
    image_path VARCHAR(500), -- Optional: store enrollment image path
    confidence DECIMAL(5, 2), -- Face detection confidence
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    INDEX idx_employee_id (employee_id),
    INDEX idx_face_id (face_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Attendance events table
CREATE TABLE IF NOT EXISTS attendance_events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    event_type ENUM('IN', 'OUT') NOT NULL,
    device_id VARCHAR(255),
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    confidence_score DECIMAL(5, 2), -- Face match confidence
    image_path VARCHAR(500), -- Optional: store check-in selfie
    ip_address VARCHAR(45),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    INDEX idx_employee_id (employee_id),
    INDEX idx_created_at (created_at),
    INDEX idx_event_type (event_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Devices table (for device binding anti-fraud)
CREATE TABLE IF NOT EXISTS devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    device_id VARCHAR(255) NOT NULL,
    device_name VARCHAR(255),
    device_model VARCHAR(255),
    os_version VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    first_seen_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_seen_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    UNIQUE KEY unique_employee_device (employee_id, device_id),
    INDEX idx_device_id (device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default company (required for employees)
INSERT INTO companies (id, name, aws_collection_id, office_latitude, office_longitude, geofence_radius) 
VALUES (1, 'Default Company', 'attendance_company_1', 28.6139, 77.2090, 100)
ON DUPLICATE KEY UPDATE name=name;

