<?php
namespace FleekDash\Database\Migrations;

use FleekDash\Database\Core\BaseMigration;

/**
 * Add Unique Constraint to event_type
 *
 * Ensures only one event per event_type exists in the system.
 * This prevents duplicate event tracking and data integrity issues.
 *
 * @package FleekDash\Database\Migrations
 * @since 2.2.0
 * @date 26-Nov-2025
 */
return new class extends BaseMigration {

    public function up(): void
    {
        global $wpdb;
        $table = $this->prefix . 'fleekdash_site_growth_events';

        // Check if table exists
        if (!$this->tableExists('fleekdash_site_growth_events')) {
            fleekdash_log_error('Migration: fleekdash_site_growth_events table does not exist');
            return;
        }

        // Check if unique constraint already exists
        // NOTE: INFORMATION_SCHEMA.STATISTICS.table_name stores FULL table name with prefix
        $constraint_exists = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*)
             FROM INFORMATION_SCHEMA.STATISTICS
             WHERE TABLE_SCHEMA = DATABASE()
             AND TABLE_NAME = %s
             AND INDEX_NAME = 'event_type'
             AND NON_UNIQUE = 0",
            $table
        ));

        if ($constraint_exists) {
            fleekdash_log_debug('Migration: Unique constraint on event_type already exists');
            return;
        }

        // Before adding constraint, we need to handle duplicates
        // Find all duplicate event_types
        $duplicates = $wpdb->get_results(
            "SELECT event_type, COUNT(*) as count, GROUP_CONCAT(id ORDER BY id) as ids
             FROM {$table}
             GROUP BY event_type
             HAVING count > 1",
            ARRAY_A
        );

        if (!empty($duplicates)) {
            fleekdash_log_debug('Migration: Found ' . count($duplicates) . ' duplicate event types');

            foreach ($duplicates as $duplicate) {
                $event_type = $duplicate['event_type'];
                $ids = explode(',', $duplicate['ids']);

                // Keep the first ID (oldest), mark others as inactive
                $keep_id = array_shift($ids);
                $deactivate_ids = $ids;

                fleekdash_log_debug("Migration: Keeping event ID {$keep_id} for type '{$event_type}', deactivating: " . implode(', ', $deactivate_ids));

                // Deactivate duplicate events
                foreach ($deactivate_ids as $id) {
                    $wpdb->update(
                        $table,
                        ['is_active' => 0],
                        ['id' => $id],
                        ['%d'],
                        ['%d']
                    );
                }

                // Merge occurrences into the kept event
                // ✅ FIXED: Use proper prepared statement with placeholders for IN clause
                $ids_for_sum = array_map('intval', array_merge([$keep_id], $deactivate_ids));

                // Build placeholders for prepared statement
                $placeholders = implode(',', array_fill(0, count($ids_for_sum), '%d'));

                $total_occurrences = $wpdb->get_var(
                    $wpdb->prepare(
                        "SELECT SUM(total_occurrences) FROM {$table} WHERE id IN ({$placeholders})",
                        ...$ids_for_sum
                    )
                );

                $wpdb->update(
                    $table,
                    ['total_occurrences' => $total_occurrences],
                    ['id' => $keep_id],
                    ['%d'],
                    ['%d']
                );
            }
        }

        // Now add the unique constraint
        // ✅ SECURITY: Use backticks for identifiers to prevent SQL injection
        $table_escaped = "`{$table}`";
        $sql = "ALTER TABLE {$table_escaped} ADD UNIQUE KEY `event_type` (`event_type`)";
        $result = $wpdb->query($sql);

        if ($result === false) {
            fleekdash_log_error('Migration: Failed to add unique constraint to event_type: ' . $wpdb->last_error);
        } else {
            fleekdash_log_debug('Migration: Successfully added unique constraint to event_type');
        }

        // Update migration status
        update_option('fleekdash_migration_event_type_unique_constraint', '1.0.0');
    }

    public function down(): void
    {
        global $wpdb;
        $table = $this->prefix . 'fleekdash_site_growth_events';

        // Check if table exists
        if (!$this->tableExists('fleekdash_site_growth_events')) {
            return;
        }

        // Drop the unique constraint
        // ✅ SECURITY: Use backticks for identifiers to prevent SQL injection
        $table_escaped = "`{$table}`";
        $sql = "ALTER TABLE {$table_escaped} DROP INDEX `event_type`";
        $wpdb->query($sql);

        delete_option('fleekdash_migration_event_type_unique_constraint');
    }
};
