<?php
require_once __DIR__ . '/../config/db.php';
$conn = getDbConnection();

echo "<h2>Fixing Notification Tables (Version 2)</h2>";

// 1. Create notifications table
$createNotifications = "CREATE TABLE IF NOT EXISTS `notifications` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(255) NOT NULL,
    `message` TEXT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $createNotifications)) {
    echo "<p style='color:green'>✅ SUCCESS: notifications table verified.</p>";
} else {
    echo "<p style='color:red'>❌ ERROR: " . mysqli_error($conn) . "</p>";
}

// 2. Fix user_notifications table
$checkTable = mysqli_query($conn, "SHOW COLUMNS FROM `user_notifications` LIKE 'notification_id'");
if (mysqli_num_rows($checkTable) == 0) {
    // Check if table exists at all
    $tableExists = mysqli_query($conn, "SHOW TABLES LIKE 'user_notifications'");
    if (mysqli_num_rows($tableExists) == 0) {
        // Create it
        $sql = "CREATE TABLE `user_notifications` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `user_id` INT NOT NULL,
            `notification_id` INT DEFAULT NULL,
            `title` VARCHAR(255) DEFAULT NULL,
            `message` TEXT DEFAULT NULL,
            `type` VARCHAR(50) DEFAULT 'admin',
            `is_read` TINYINT(1) DEFAULT 0,
            `read_at` TIMESTAMP NULL DEFAULT NULL,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
        mysqli_query($conn, $sql);
        echo "<p style='color:green'>✅ SUCCESS: user_notifications table created from scratch.</p>";
    } else {
        // Alter it
        mysqli_query($conn, "ALTER TABLE `user_notifications` ADD COLUMN `notification_id` INT DEFAULT NULL AFTER `user_id` ");
        
        // Check for read_at separately
        $checkReadAt = mysqli_query($conn, "SHOW COLUMNS FROM `user_notifications` LIKE 'read_at'");
        if (mysqli_num_rows($checkReadAt) == 0) {
             mysqli_query($conn, "ALTER TABLE `user_notifications` ADD COLUMN `read_at` TIMESTAMP NULL DEFAULT NULL AFTER `is_read` ");
        }
        
        echo "<p style='color:green'>✅ SUCCESS: user_notifications table altered with new columns.</p>";
    }
} else {
    echo "<p style='color:green'>✅ SUCCESS: user_notifications columns verified (notification_id already exists).</p>";
}

echo "<p><a href='notifications.php'>Go back to Notifications</a></p>";
?>
