<?php
namespace FleekDash\Database\Migrations;

use FleekDash\Database\Core\BaseMigration;

/**
 * Create Site Growth Achievements Table
 *
 * Enterprise-grade achievements system (complex table with many indexes)
 * Converted to new format
 */
return new class extends BaseMigration {

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

        $sql = "CREATE TABLE IF NOT EXISTS `{$table}` (
            `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            `achievement_type` enum('event_record', 'monthly_record', 'weekly_record', 'daily_record') NOT NULL COMMENT 'Type of achievement',
            `achievement_name` varchar(255) NOT NULL COMMENT 'Display name',
            `achievement_slug` varchar(100) NOT NULL COMMENT 'Unique slug',
            `description` text NULL COMMENT 'Description',
            `category` varchar(50) NOT NULL DEFAULT 'general' COMMENT 'Category',
            `event_type` varchar(100) NULL COMMENT 'Related event type',
            `event_config` longtext NULL COMMENT 'JSON config',
            `event_config_hash` varchar(32) NULL COMMENT 'MD5 hash',
            `target_value` bigint(20) unsigned NOT NULL COMMENT 'Target to achieve',
            `current_record` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT 'Current best record',
            `previous_record` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT 'Previous record',
            `record_period` date NULL COMMENT 'When record was achieved',
            `is_achieved` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Achievement unlocked',
            `achieved_at` datetime NULL COMMENT 'When unlocked',
            `times_achieved` int(11) unsigned NOT NULL DEFAULT 0 COMMENT 'Times unlocked',
            `last_achieved_at` datetime NULL COMMENT 'Most recent unlock',
            `auto_scale` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Auto-increase target',
            `scale_factor` decimal(3,2) NOT NULL DEFAULT 1.20 COMMENT 'Multiplier',
            `min_target` int(11) unsigned NOT NULL DEFAULT 20 COMMENT 'Min target',
            `max_target` int(11) unsigned NOT NULL DEFAULT 0 COMMENT 'Max target',
            `last_calculated_at` datetime NULL COMMENT 'Cache timestamp',
            `calculation_hash` varchar(32) NULL COMMENT 'Cache hash',
            `is_active` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Active status',
            `achievement_icon` varchar(50) NOT NULL DEFAULT 'trophy' COMMENT 'Icon',
            `achievement_color` varchar(7) NOT NULL DEFAULT '#22C55E' COMMENT 'Color',
            `display_order` int(11) NOT NULL DEFAULT 0 COMMENT 'Sort order',
            `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`),
            UNIQUE KEY `uniq_achievement_identity` (`achievement_type`, `event_type`, `event_config_hash`),
            KEY `idx_achievement_type` (`achievement_type`),
            KEY `idx_category` (`category`),
            KEY `idx_event_type` (`event_type`),
            KEY `idx_is_active` (`is_active`),
            KEY `idx_is_achieved` (`is_achieved`),
            KEY `idx_current_record` (`current_record`),
            KEY `idx_display_order` (`display_order`, `category`)
        ) {$charset_collate} COMMENT='Site Growth Achievements';";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);

        update_option('fleekdash_site_growth_achievements_version', '2.0.0');
        update_option('fleekdash_site_growth_achievements_installed_at', current_time('mysql'));
    }

    public function down(): void
    {
        $this->dropTable('fleekdash_site_growth_achievements');
        delete_option('fleekdash_site_growth_achievements_version');
        delete_option('fleekdash_site_growth_achievements_installed_at');
    }
};

