<?php
/**
 * Create User Permission Override Tables
 *
 * Creates the main overrides table and mandatory audit history table
 * for the User Override Permission System.
 *
 * @package FleekDash\Database\Migrations
 * @since 2.3.0
 */

namespace FleekDash\Database\Migrations;

use FleekDash\Database\Core\BaseMigration;

defined('ABSPATH') || exit;

return new class extends BaseMigration {

    public function up(): void
    {
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();
        $prefix = $wpdb->prefix;

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        // Table 1: Main overrides table
        $sqlOverrides = "CREATE TABLE IF NOT EXISTS `{$prefix}fleekdash_user_permission_overrides` (
            `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            `user_id` BIGINT(20) UNSIGNED NOT NULL,
            `permission_id` VARCHAR(100) NOT NULL,
            `override_type` ENUM('ALLOW', 'DENY', 'TEMP_ALLOW', 'TEMP_DENY') NOT NULL,
            `reason` TEXT DEFAULT NULL,
            `created_by` BIGINT(20) UNSIGNED NOT NULL,
            `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            `expires_at` DATETIME DEFAULT NULL,
            `is_active` TINYINT(1) NOT NULL DEFAULT 1,

            PRIMARY KEY (`id`),
            KEY `idx_user` (`user_id`),
            KEY `idx_permission` (`permission_id`),
            KEY `idx_user_permission` (`user_id`, `permission_id`),
            KEY `idx_expires` (`expires_at`),
            KEY `idx_active` (`is_active`),
            UNIQUE KEY `uk_user_permission_active` (`user_id`, `permission_id`, `is_active`)
        ) {$charset_collate};";

        // Table 2: Audit history (MANDATORY for compliance)
        $sqlHistory = "CREATE TABLE IF NOT EXISTS `{$prefix}fleekdash_permission_override_history` (
            `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            `override_id` BIGINT(20) UNSIGNED NOT NULL,
            `action` ENUM('CREATE', 'UPDATE', 'DELETE', 'EXPIRE') NOT NULL,
            `old_value` LONGTEXT DEFAULT NULL,
            `new_value` LONGTEXT DEFAULT NULL,
            `performed_by` BIGINT(20) UNSIGNED NOT NULL,
            `performed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            `ip_address` VARCHAR(45) DEFAULT NULL,

            PRIMARY KEY (`id`),
            KEY `idx_override` (`override_id`),
            KEY `idx_performed_at` (`performed_at`),
            KEY `idx_performed_by` (`performed_by`)
        ) {$charset_collate};";

        dbDelta($sqlOverrides);
        dbDelta($sqlHistory);

        update_option('fleekdash_permission_override_version', '1.0.0');
    }

    public function down(): void
    {
        $this->dropTable('fleekdash_permission_override_history');
        $this->dropTable('fleekdash_user_permission_overrides');
        delete_option('fleekdash_permission_override_version');
    }
};
