<?php
require_once '../config/database.php';

echo "Setting up biometric attendance tables...\n";

try {
    // Create biometric_templates table for storing fingerprint/face templates
    $sql = "
    CREATE TABLE IF NOT EXISTS `biometric_templates` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `user_type` enum('student','teacher') NOT NULL,
        `user_id` int(11) NOT NULL,
        `template_data` longtext DEFAULT NULL,
        `template_type` enum('fingerprint','face','iris') NOT NULL,
        `status` enum('active','inactive') DEFAULT 'active',
        `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
        `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
        PRIMARY KEY (`id`),
        UNIQUE KEY `unique_user_template` (`user_type`,`user_id`,`template_type`),
        INDEX `idx_user_type_id` (`user_type`,`user_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
    
    $pdo->exec($sql);
    echo "✓ Created biometric_templates table\n";

    // Create attendance_log table for detailed attendance logs
    $sql = "
    CREATE TABLE IF NOT EXISTS `attendance_log` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `student_id` int(11) DEFAULT NULL,
        `teacher_id` int(11) DEFAULT NULL,
        `class_id` int(11) DEFAULT NULL,
        `date` date NOT NULL,
        `time` time NOT NULL,
        `status` enum('present','absent','late','excused') NOT NULL DEFAULT 'present',
        `mode` enum('manual','camera','fingerprint','face_recognition') NOT NULL,
        `biometric_ref` varchar(255) DEFAULT NULL,
        `device_id` varchar(100) DEFAULT NULL,
        `remarks` text DEFAULT NULL,
        `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
        PRIMARY KEY (`id`),
        KEY `idx_date_student` (`date`,`student_id`),
        KEY `idx_date_teacher` (`date`,`teacher_id`),
        KEY `idx_class_date` (`class_id`,`date`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
    
    $pdo->exec($sql);
    echo "✓ Created attendance_log table\n";

    // Create biometric_devices table for managing biometric devices
    $sql = "
    CREATE TABLE IF NOT EXISTS `biometric_devices` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `device_name` varchar(255) NOT NULL,
        `device_type` enum('fingerprint','face_recognition','iris_scanner') NOT NULL,
        `location` varchar(255) DEFAULT NULL,
        `ip_address` varchar(45) DEFAULT NULL,
        `status` enum('active','inactive','maintenance') DEFAULT 'active',
        `last_sync` timestamp NULL DEFAULT NULL,
        `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
        `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
    
    $pdo->exec($sql);
    echo "✓ Created biometric_devices table\n";

    // Create attendance_settings table for system configuration
    $sql = "
    CREATE TABLE IF NOT EXISTS `attendance_settings` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `setting_key` varchar(255) NOT NULL,
        `setting_value` text DEFAULT NULL,
        `description` text DEFAULT NULL,
        `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
        `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
        PRIMARY KEY (`id`),
        UNIQUE KEY `unique_setting` (`setting_key`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
    
    $pdo->exec($sql);
    echo "✓ Created attendance_settings table\n";

    // Insert default settings
    $settings = [
        ['camera_enabled', '1', 'Enable camera-based attendance'],
        ['fingerprint_enabled', '1', 'Enable fingerprint-based attendance'],
        ['face_recognition_enabled', '1', 'Enable face recognition-based attendance'],
        ['auto_sync_enabled', '0', 'Enable automatic sync with biometric devices'],
        ['attendance_timeout', '300', 'Attendance timeout in seconds'],
        ['notification_enabled', '1', 'Enable attendance notifications'],
        ['attendance_threshold', '80', 'Attendance percentage threshold for alerts']
    ];

    $stmt = $pdo->prepare("INSERT IGNORE INTO attendance_settings (setting_key, setting_value, description) VALUES (?, ?, ?)");
    foreach ($settings as $setting) {
        $stmt->execute($setting);
    }
    echo "✓ Inserted default attendance settings\n";

    echo "\nBiometric attendance system setup completed successfully!\n";
    
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>