<?php
// Script to create the teacher leave table if it doesn't exist
require_once 'config/database.php';

echo "<h2>Creating Teacher Leave Table...</h2>";

try {
    // Create the teacher_leaves table
    $pdo->exec("CREATE TABLE IF NOT EXISTS teacher_leaves (
        id INT AUTO_INCREMENT PRIMARY KEY,
        teacher_id INT NOT NULL,
        leave_type VARCHAR(50) NOT NULL,
        start_date DATE NOT NULL,
        end_date DATE NOT NULL,
        reason TEXT NOT NULL,
        status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
        applied_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        approved_by INT NULL,
        approved_date TIMESTAMP NULL,
        remarks TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE,
        FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
    )");
    
    echo "<p style='color: green;'>✓ Teacher leave table created successfully!</p>";
    
    // Verify the table exists
    $stmt = $pdo->query("SHOW TABLES LIKE 'teacher_leaves'");
    if ($stmt->rowCount() > 0) {
        echo "<p style='color: green;'>✓ Table verification: teacher_leaves table exists</p>";
    } else {
        echo "<p style='color: red;'>✗ Table verification: teacher_leaves table NOT found</p>";
    }
    
    // Show table structure
    echo "<h3>Table Structure:</h3>";
    $stmt = $pdo->query("DESCRIBE teacher_leaves");
    $columns = $stmt->fetchAll();
    echo "<ul>";
    foreach ($columns as $column) {
        echo "<li><strong>{$column['Field']}</strong> ({$column['Type']}) - {$column['Null']} - {$column['Key']}</li>";
    }
    echo "</ul>";
    
} catch (Exception $e) {
    echo "<p style='color: red;'>✗ Error creating table: " . htmlspecialchars($e->getMessage()) . "</p>";
}

echo "<h3>System Status:</h3>";
echo "<p>Teacher leave application system is ready!</p>";
echo "<p><a href='teachers/leave_application.php' style='color: blue; text-decoration: underline;'>Access Teacher Leave Application Form</a></p>";
echo "<p><a href='admin/leave_applications.php' style='color: blue; text-decoration: underline;'>Access Admin Leave Approval Panel</a></p>";
?>