<?php
/**
 * Create application_media table if it doesn't exist
 */

require_once 'config/database.php';

echo "<h2>Creating application_media Table</h2>";

try {
    // Check if table exists
    $tableExists = $conn->query("SHOW TABLES LIKE 'application_media'");
    
    if ($tableExists->num_rows > 0) {
        echo "<p style='color: green;'>✅ application_media table already exists</p>";
    } else {
        echo "<p style='color: orange;'>⚠️ application_media table does not exist. Creating...</p>";
        
        // Create the table
        $createTable = "
        CREATE TABLE `application_media` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `application_id` int(11) NOT NULL,
          `media_type` enum('image','video','document','portfolio') NOT NULL,
          `file_path` varchar(255) NOT NULL,
          `file_name` varchar(255) NOT NULL,
          `file_size` int(11) NOT NULL,
          `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
          PRIMARY KEY (`id`),
          KEY `application_id` (`application_id`),
          CONSTRAINT `application_media_ibfk_1` FOREIGN KEY (`application_id`) REFERENCES `job_applications` (`id`) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        ";
        
        if ($conn->query($createTable)) {
            echo "<p style='color: green;'>✅ application_media table created successfully</p>";
        } else {
            echo "<p style='color: red;'>❌ Error creating table: " . $conn->error . "</p>";
        }
    }
    
    // Show current structure
    $structure = $conn->query("DESCRIBE application_media");
    echo "<h3>Table Structure:</h3>";
    echo "<table border='1' style='border-collapse: collapse;'>";
    echo "<tr><th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th></tr>";
    while ($row = $structure->fetch_assoc()) {
        echo "<tr>";
        echo "<td>{$row['Field']}</td>";
        echo "<td>{$row['Type']}</td>";
        echo "<td>{$row['Null']}</td>";
        echo "<td>{$row['Key']}</td>";
        echo "<td>{$row['Default']}</td>";
        echo "</tr>";
    }
    echo "</table>";
    
    // Count records
    $count = $conn->query("SELECT COUNT(*) as total FROM application_media");
    $total = $count->fetch_assoc()['total'];
    echo "<p><strong>Total records:</strong> $total</p>";
    
    if ($total == 0) {
        echo "<p style='color: blue;'>ℹ️ No attachments have been uploaded yet. This is normal for a new system.</p>";
        echo "<p>The attachments functionality will work once users start uploading files with their job applications.</p>";
    }
    
} catch (Exception $e) {
    echo "<p style='color: red;'>❌ Error: " . $e->getMessage() . "</p>";
}

echo "<p><a href='jobs/job-applications.php?job_id=5'>← Back to Job Applications</a></p>";
?>
