<?php
/**
 * Migration: Create notifications tables
 * Run once: http://localhost/shopnest/shopnest/php-api-server/migrate_notifications.php
 */

require_once __DIR__ . '/config/database.php';

$queries = [
    // Admin-created notifications
    "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",

    // Per-user read tracking
    "CREATE TABLE IF NOT EXISTS `user_notifications` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `user_id` INT NOT NULL,
        `notification_id` INT NOT NULL,
        `is_read` TINYINT(1) DEFAULT 0,
        `read_at` TIMESTAMP NULL DEFAULT NULL,
        UNIQUE KEY `user_notif_unique` (`user_id`, `notification_id`),
        FOREIGN KEY (`notification_id`) REFERENCES `notifications`(`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
];

echo "<h2>Notification Tables Migration</h2>";
foreach ($queries as $sql) {
    if (mysqli_query($conn, $sql)) {
        echo "<p style='color:green'>✅ OK: " . substr($sql, 0, 60) . "...</p>";
    } else {
        echo "<p style='color:red'>❌ Error: " . mysqli_error($conn) . "</p>";
    }
}
echo "<p><strong>Done!</strong></p>";
