<?php
/**
 * Create Integration System Tables Migration
 *
 * Creates the unified integration system database tables with
 * security and performance optimizations built-in.
 *
 * @package FleekDash\Database\Migrations
 * @since 2.2.0
 * @version 1.0.0
 *
 * Security Features:
 * - Sanitized text fields (VARCHAR with limits)
 * - Validated ENUM fields
 * - Foreign key constraints
 * - Proper indexes for performance
 * - UTF8MB4 charset for emoji support
 */

namespace FleekDash\Database\Migrations;

use FleekDash\Database\Core\BaseMigration;

defined('ABSPATH') || exit;

$fullyQualifiedClassName = __NAMESPACE__ . '\\CreateIntegrationSystemTables';

if (!class_exists($fullyQualifiedClassName, false)) {
    class CreateIntegrationSystemTables extends BaseMigration
    {
        /**
         * Migration version.
         *
         * @var string
         */
        public const VERSION = '2.3.1';

        /**
         * Registry columns added for older installations (order + definitions must stay aligned with needsMigration() checks).
         */
        private const REGISTRY_UPGRADE_COLUMN_DEFINITIONS = [
            'category' => "VARCHAR(50) DEFAULT 'plugin'",
            'source' => "VARCHAR(50) DEFAULT 'external'",
            'source_label' => 'VARCHAR(100)',
            'settings_schema' => 'LONGTEXT',
            'allowed_roles' => 'TEXT',
            'feature_key' => 'VARCHAR(150)',
            'license_required' => 'TINYINT(1) DEFAULT 0',
            'required_tier' => 'VARCHAR(100)',
            'is_available' => 'TINYINT(1) DEFAULT 1',
        ];

        /**
         * Run migration.
         */
        public function up(): void // NOSONAR php:S138 - dbDelta table definitions are kept together for migration atomicity.
        {
            $charsetCollate = $this->wpdb->get_charset_collate();

            $registryTable = $this->prefix . 'fleekdash_integration_registry';
            $routesTable = $this->prefix . 'fleekdash_integration_routes';
            $settingsTable = $this->prefix . 'fleekdash_integration_settings';

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

            // =====================================================================
            // TABLE 1: Integration Registry
            // =====================================================================

            $sqlRegistry = "CREATE TABLE IF NOT EXISTS `{$registryTable}` (
                `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

                -- Identity (sanitized on insert)
                `integration_id` VARCHAR(100) NOT NULL,
                `name` VARCHAR(100) NOT NULL,
                `description` TEXT,
                `version` VARCHAR(20) NOT NULL,
                `author` VARCHAR(100),
                `author_uri` VARCHAR(255),

                -- Type & Configuration
                `integration_type` ENUM('native', 'standalone', 'hybrid', 'internal', 'mu-plugin', 'premium-catalog') NOT NULL,
                `plugin_file` VARCHAR(255) NOT NULL,
                `icon` VARCHAR(50),
                `category` VARCHAR(50) DEFAULT 'plugin',
                `source` VARCHAR(50) DEFAULT 'external',
                `source_label` VARCHAR(100),

                -- Manifest (JSON, validated on insert)
                `manifest` LONGTEXT NOT NULL,
                `manifest_hash` VARCHAR(64),
                `settings_schema` LONGTEXT,

                -- WordPress Integration
                `wp_menu_slug` VARCHAR(100),
                `has_wp_menu` TINYINT(1) DEFAULT 0,
                `has_integration_routes` TINYINT(1) DEFAULT 0,
                `allowed_roles` TEXT,

                -- Licensing and access
                `feature_key` VARCHAR(150),
                `license_required` TINYINT(1) DEFAULT 0,
                `required_tier` VARCHAR(100),

                -- Status
                `is_active` TINYINT(1) DEFAULT 1,
                `is_available` TINYINT(1) DEFAULT 1,

                -- Metadata
                `installed_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
                `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                `last_error` TEXT,

                PRIMARY KEY (`id`),
                UNIQUE KEY `unique_integration_id` (`integration_id`),
                KEY `integration_type` (`integration_type`),
                KEY `source` (`source`),
                KEY `feature_key` (`feature_key`),
                KEY `is_active` (`is_active`),
                KEY `is_available` (`is_available`),
                KEY `manifest_hash` (`manifest_hash`)
            ) {$charsetCollate};";

            // =====================================================================
            // TABLE 2: Integration Routes
            // =====================================================================

            $sqlRoutes = "CREATE TABLE IF NOT EXISTS `{$routesTable}` (
                `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

                -- Integration Reference
                `integration_id` VARCHAR(100) NOT NULL,

                -- Route Definition (sanitized on insert)
                `route_path` VARCHAR(255) NOT NULL,
                `route_title` VARCHAR(100) NOT NULL,
                `component_url` VARCHAR(500) NOT NULL,

                -- Menu Configuration
                `icon` VARCHAR(50),
                `menu_parent` VARCHAR(255),
                `menu_order` INT(11) DEFAULT 0,
                `menu_badge` TEXT,
                `menu_config` TEXT,

                -- Security Configuration (JSON, validated)
                `permissions` TEXT,
                `capabilities` TEXT,
                `security_config` TEXT,
                `sandbox_mode` ENUM('strict', 'moderate', 'none') DEFAULT 'strict',

                -- CSP & CORS
                `allowed_origins` TEXT,
                `csp_policy` TEXT,

                -- Status
                `is_active` TINYINT(1) DEFAULT 1,

                -- Metadata
                `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
                `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

                PRIMARY KEY (`id`),
                UNIQUE KEY `unique_route` (`integration_id`, `route_path`),
                KEY `integration_id` (`integration_id`),
                KEY `is_active` (`is_active`),
                KEY `menu_order` (`menu_order`),

                CONSTRAINT `fk_{$routesTable}_registry`
                    FOREIGN KEY (`integration_id`)
                    REFERENCES `{$registryTable}` (`integration_id`)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE
            ) {$charsetCollate};";

            // =====================================================================
            // TABLE 3: Integration Settings
            // =====================================================================

            $sqlSettings = "CREATE TABLE IF NOT EXISTS `{$settingsTable}` (
                `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

                -- Integration Reference
                `integration_id` VARCHAR(100) NOT NULL,

                -- Setting (sanitized on insert)
                `setting_key` VARCHAR(100) NOT NULL,
                `setting_value` LONGTEXT,
                `setting_type` VARCHAR(50) DEFAULT 'string',

                -- Metadata
                `is_encrypted` TINYINT(1) DEFAULT 0,
                `is_required` TINYINT(1) DEFAULT 0,
                `is_sensitive` TINYINT(1) DEFAULT 0,

                -- Validation
                `validation_rules` TEXT,
                `default_value` TEXT,

                -- Timestamps
                `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
                `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

                PRIMARY KEY (`id`),
                UNIQUE KEY `unique_setting` (`integration_id`, `setting_key`),
                KEY `integration_id` (`integration_id`),
                KEY `is_sensitive` (`is_sensitive`),

                CONSTRAINT `fk_{$settingsTable}_registry`
                    FOREIGN KEY (`integration_id`)
                    REFERENCES `{$registryTable}` (`integration_id`)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE
            ) {$charsetCollate};";

            dbDelta($sqlRegistry);
            dbDelta($sqlRoutes);
            dbDelta($sqlSettings);
            $this->ensureRegistryColumns();

            update_option('fleekdash_integration_db_version', self::VERSION);
            update_option('fleekdash_integration_migration_date', current_time('mysql'));

            error_log(sprintf(
                '[FleekDash Integration] Database tables created. Migration version: %s (Plugin: %s)',
                self::VERSION,
                defined('FLEEKDASH_VERSION') ? FLEEKDASH_VERSION : 'unknown'
            ));
        }

        /**
         * Ensure newly required registry columns exist on upgraded installations.
         */
        private function ensureRegistryColumns(): void
        {
            // Unprefixed table name is required by BaseMigration::addColumn.
            $table = 'fleekdash_integration_registry';
            foreach (self::REGISTRY_UPGRADE_COLUMN_DEFINITIONS as $column => $definition) {
                $this->addColumn($table, $column, $definition);
            }
        }

        /**
         * Rollback migration.
         */
        public function down(): void
        {
            $tables = [
                $this->prefix . 'fleekdash_integration_settings',
                $this->prefix . 'fleekdash_integration_routes',
                $this->prefix . 'fleekdash_integration_registry',
            ];

            foreach ($tables as $table) {
                $this->wpdb->query("DROP TABLE IF EXISTS `{$table}`");
            }

            delete_option('fleekdash_integration_db_version');
            delete_option('fleekdash_integration_migration_date');

            error_log('[FleekDash Integration] Database tables dropped (rollback)');
        }

        /**
         * Determine if migration needs to run.
         */
        public static function needsMigration(): bool
        {
            $currentVersion = get_option('fleekdash_integration_db_version', '0.0.0');
            $needsMigration = false;

            if (version_compare($currentVersion, self::VERSION, '<')) {
                $needsMigration = true;
            }

            if (!$needsMigration) {
                $status = self::verifyTables();
                foreach ($status as $exists) {
                    if ($exists === false) {
                        $needsMigration = true;
                        break;
                    }
                }
            }

            if (!$needsMigration) {
                foreach (array_keys(self::REGISTRY_UPGRADE_COLUMN_DEFINITIONS) as $columnName) {
                    if (!self::registryColumnExists($columnName)) {
                        $needsMigration = true;
                        break;
                    }
                }
            }

            return $needsMigration;
        }

        private static function registryColumnExists(string $column): bool
        {
            global $wpdb;
            $table = $wpdb->prefix . 'fleekdash_integration_registry';
            $count = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM information_schema.columns
                 WHERE table_schema = DATABASE()
                 AND table_name = %s
                 AND column_name = %s",
                $table,
                $column
            ));

            return (int) $count > 0;
        }

        /**
         * Verify tables exist.
         *
         * @return array{registry:bool,routes:bool,settings:bool}
         */
        public static function verifyTables(): array
        {
            global $wpdb;

            $tablePrefix = $wpdb->prefix;
            $tables = [
                'registry' => $tablePrefix . 'fleekdash_integration_registry',
                'routes' => $tablePrefix . 'fleekdash_integration_routes',
                'settings' => $tablePrefix . 'fleekdash_integration_settings',
            ];

            $status = [];

            foreach ($tables as $key => $table) {
                $status[$key] = $wpdb->get_var($wpdb->prepare(
                    "SHOW TABLES LIKE %s",
                    $table
                )) === $table;
            }

            return $status;
        }

        /**
         * Convenience wrapper to run migration outside manager.
         */
        public static function migrate(): void
        {
            (new self())->up();
        }

        /**
         * Convenience wrapper to rollback migration outside manager.
         */
        public static function rollbackMigration(): void
        {
            (new self())->down();
        }
    }
}

return new CreateIntegrationSystemTables();
