<?php
require_once __DIR__ . '/../../includes/app.php';

try {
    $pdo = db();
    
    // Create roles table
    $pdo->exec("CREATE TABLE IF NOT EXISTS `roles` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `name` VARCHAR(255) NOT NULL,
        `description` TEXT,
        `is_system` TINYINT(1) DEFAULT 0,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");

    // Create role_permissions table
    $pdo->exec("CREATE TABLE IF NOT EXISTS `role_permissions` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `role_id` INT NOT NULL,
        `page` VARCHAR(50) NOT NULL,
        `can_view` TINYINT(1) DEFAULT 0,
        `can_add` TINYINT(1) DEFAULT 0,
        `can_edit` TINYINT(1) DEFAULT 0,
        `can_delete` TINYINT(1) DEFAULT 0,
        UNIQUE KEY `role_page` (`role_id`, `page`),
        FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");

    // Alter users table to add role_id
    // Check if column exists
    $stmt = $pdo->prepare("SHOW COLUMNS FROM `users` LIKE 'role_id'");
    $stmt->execute();
    if ($stmt->rowCount() == 0) {
        $pdo->exec("ALTER TABLE `users` ADD COLUMN `role_id` INT NULL DEFAULT NULL");
        $pdo->exec("ALTER TABLE `users` ADD CONSTRAINT `fk_user_role` FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE SET NULL");
    }

    // Insert 'Super Admin' role if it doesn't exist
    $stmt = $pdo->prepare("SELECT id FROM roles WHERE name = 'Super Admin'");
    $stmt->execute();
    $adminRole = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$adminRole) {
        $pdo->exec("INSERT INTO roles (name, description, is_system) VALUES ('Super Admin', 'Full access to all pages and actions', 1)");
        $adminRoleId = $pdo->lastInsertId();
    } else {
        $adminRoleId = $adminRole['id'];
    }

    // Assign 'Super Admin' role to all existing admins
    $stmt = $pdo->prepare("UPDATE users SET role_id = ? WHERE role = 'admin' AND role_id IS NULL");
    $stmt->execute([$adminRoleId]);

    echo "Migration completed successfully.";
} catch (PDOException $e) {
    echo "Migration failed: " . $e->getMessage();
}