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

echo "Updating attendance table structure for teacher attendance support...\n";

try {
    // Check if teacher_id column exists
    $columns = $pdo->query("DESCRIBE attendance")->fetchAll(PDO::FETCH_COLUMN);
    
    if (!in_array('teacher_id', $columns)) {
        // Add teacher_id column
        $pdo->exec("ALTER TABLE attendance ADD COLUMN teacher_id INT NULL AFTER student_id");
        echo "✓ Added teacher_id column to attendance table\n";
    } else {
        echo "- teacher_id column already exists in attendance table\n";
    }
    
    if (!in_array('class_id', $columns) || $pdo->query("SHOW FIELDS FROM attendance WHERE Field = 'class_id' AND Null = 'NO'")->rowCount() > 0) {
        // Modify class_id to allow NULL
        $pdo->exec("ALTER TABLE attendance MODIFY COLUMN class_id INT NULL");
        echo "✓ Modified class_id column to allow NULL values\n";
    } else {
        echo "- class_id column already allows NULL values\n";
    }
    
    // Modify student_id to allow NULL
    if ($pdo->query("SHOW FIELDS FROM attendance WHERE Field = 'student_id' AND Null = 'NO'")->rowCount() > 0) {
        $pdo->exec("ALTER TABLE attendance MODIFY COLUMN student_id INT NULL");
        echo "✓ Modified student_id column to allow NULL values\n";
    } else {
        echo "- student_id column already allows NULL values\n";
    }
    
    // Add foreign key constraint for teacher_id
    try {
        $pdo->exec("ALTER TABLE attendance ADD FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE SET NULL");
        echo "✓ Added foreign key constraint for teacher_id\n";
    } catch (PDOException $e) {
        if (strpos($e->getMessage(), 'Duplicate key name') !== false || strpos($e->getMessage(), 'already exists') !== false) {
            echo "- Foreign key constraint for teacher_id already exists\n";
        } else {
            echo "! Warning: Could not add foreign key constraint for teacher_id: " . $e->getMessage() . "\n";
        }
    }
    
    // Add unique index for teacher attendance
    try {
        $pdo->exec("ALTER TABLE attendance ADD UNIQUE KEY unique_teacher_date (teacher_id, date)");
        echo "✓ Added unique index for teacher attendance (teacher_id, date)\n";
    } catch (PDOException $e) {
        if (strpos($e->getMessage(), 'Duplicate key name') !== false) {
            echo "- Unique index for teacher attendance already exists\n";
        } else {
            echo "! Warning: Could not add unique index for teacher attendance: " . $e->getMessage() . "\n";
        }
    }
    
    echo "\nAttendance table structure updated successfully!\n";
    echo "Teacher attendance functionality is now supported.\n";
    
} catch (Exception $e) {
    echo "Error updating attendance table: " . $e->getMessage() . "\n";
}
?>