<?php
/**
 * Install or fix admission applications table structure
 */
session_start();
require_once 'config/database.php';

try {
    $pdo = new PDO("mysql:host=$db_host;dbname=$db_name", $db_user, $db_pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "<h2>Checking Admission Applications Table</h2>";
    
    // Check if table exists
    $stmt = $pdo->query("SHOW TABLES LIKE 'admission_applications'");
    $tableExists = $stmt->rowCount() > 0;
    
    if (!$tableExists) {
        echo "<p>Table 'admission_applications' does not exist. Creating it...</p>";
        
        // Create the table
        $createTableSql = "
        CREATE TABLE admission_applications (
            id INT AUTO_INCREMENT PRIMARY KEY,
            application_no VARCHAR(50) UNIQUE NOT NULL,
            first_name VARCHAR(100) NOT NULL,
            last_name VARCHAR(100),
            father_name VARCHAR(100) NOT NULL,
            mother_name VARCHAR(100) NOT NULL,
            date_of_birth DATE,
            gender ENUM('male', 'female', 'other'),
            religion ENUM('islam', 'hinduism', 'buddhism', 'christianity', 'other'),
            blood_group VARCHAR(5),
            phone VARCHAR(20) NOT NULL,
            father_phone VARCHAR(20),
            email VARCHAR(100),
            present_address TEXT,
            permanent_address TEXT,
            applying_for_class_id INT,
            previous_school VARCHAR(200),
            previous_class VARCHAR(100),
            application_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            password VARCHAR(255),
            status ENUM('pending', 'approved', 'rejected', 'cancelled') DEFAULT 'pending',
            photo_path VARCHAR(500),
            applying_for_session VARCHAR(20),
            applying_for_group VARCHAR(50),
            selected_subjects JSON,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
        
        $pdo->exec($createTableSql);
        echo "<p style='color: green;'>✓ Table 'admission_applications' created successfully!</p>";
    } else {
        echo "<p>Table 'admission_applications' already exists.</p>";
        
        // Check if photo_path column exists
        $columns = $pdo->query("DESCRIBE admission_applications");
        $hasPhotoPath = false;
        $columnList = [];
        
        while ($row = $columns->fetch()) {
            $columnList[] = $row['Field'];
            if ($row['Field'] === 'photo_path') {
                $hasPhotoPath = true;
            }
        }
        
        if (!$hasPhotoPath) {
            echo "<p>Adding 'photo_path' column to the table...</p>";
            $pdo->exec("ALTER TABLE admission_applications ADD COLUMN photo_path VARCHAR(500) AFTER status");
            echo "<p style='color: green;'>✓ 'photo_path' column added successfully!</p>";
        } else {
            echo "<p>✓ 'photo_path' column already exists.</p>";
        }
        
        // Check if all required columns exist
        $requiredColumns = [
            'application_no', 'first_name', 'last_name', 'father_name', 'mother_name',
            'date_of_birth', 'gender', 'religion', 'blood_group', 'phone', 'father_phone',
            'email', 'present_address', 'permanent_address', 'applying_for_class_id',
            'previous_school', 'previous_class', 'password', 'status', 'photo_path',
            'applying_for_session', 'applying_for_group', 'selected_subjects', 'created_at'
        ];
        
        $missingColumns = array_diff($requiredColumns, $columnList);
        
        if (!empty($missingColumns)) {
            echo "<p>Missing columns: " . implode(', ', $missingColumns) . "</p>";
            echo "<p style='color: red;'>Warning: Some required columns are missing!</p>";
        } else {
            echo "<p>✓ All required columns are present.</p>";
        }
    }
    
    // Check if uploads directory exists
    $uploadDir = __DIR__ . '/uploads/students/';
    if (!is_dir($uploadDir)) {
        $result = mkdir($uploadDir, 0755, true);
        if ($result) {
            echo "<p style='color: green;'>✓ Upload directory created: $uploadDir</p>";
        } else {
            echo "<p style='color: red;'>✗ Failed to create upload directory: $uploadDir</p>";
        }
    } else {
        echo "<p>✓ Upload directory exists: $uploadDir</p>";
    }
    
    // Test if image_utils.php exists and functions are available
    if (file_exists(__DIR__ . '/includes/image_utils.php')) {
        require_once 'includes/image_utils.php';
        if (function_exists('validate_and_compress_image')) {
            echo "<p>✓ Image utilities are properly installed.</p>";
        } else {
            echo "<p style='color: orange;'>⚠ Image utilities file exists but function not available.</p>";
        }
    } else {
        echo "<p style='color: red;'>✗ Image utilities file does not exist!</p>";
    }
    
    echo "<h3>Table Structure:</h3>";
    echo "<pre>";
    $describe = $pdo->query("DESCRIBE admission_applications");
    while ($row = $describe->fetch()) {
        echo sprintf("%-20s %-20s %-10s %-10s %-10s %s\n", 
            $row['Field'], $row['Type'], $row['Null'], $row['Key'], $row['Default'], $row['Extra']);
    }
    echo "</pre>";
    
    echo "<h3>Next Steps:</h3>";
    echo "<ol>";
    echo "<li>Verify that your form is working correctly</li>";
    echo "<li>Visit <a href='admission_application.php'>Admission Application Form</a> to test</li>";
    echo "<li>Check <a href='debug_upload.php'>Debug Upload Script</a> for any issues</li>";
    echo "</ol>";
    
} catch (PDOException $e) {
    echo "<p style='color: red;'>Database Error: " . $e->getMessage() . "</p>";
} catch (Exception $e) {
    echo "<p style='color: red;'>Error: " . $e->getMessage() . "</p>";
}
?>