<?php
namespace FleekDash\Database\Migrations;

use FleekDash\Database\Core\BaseMigration;
use FleekDash\Database\Core\TableBuilder;

/**
 * Create Site Growth Monthly Snapshots Table
 *
 * Stores monthly snapshots of growth metrics for historical comparison
 * Used for month-over-month growth calculations and record tracking
 */
return new class extends BaseMigration {

    public function up(): void
    {
        $this->createTable('fleekdash_site_growth_monthly_snapshots', function (TableBuilder $table) {
            $table->id();
            $table->string('month_year', 7)->comment('Format: YYYY-MM');
            $table->unsignedBigInteger('total_points')->default(0)->comment('Total growth points for the month');
            $table->unsignedBigInteger('total_posts')->default(0)->comment('Total posts in the month');
            $table->unsignedBigInteger('total_users')->default(0)->comment('Total users in the month');
            $table->unsignedBigInteger('total_comments')->default(0)->comment('Total comments in the month');
            $table->decimal('total_sales', 15, 2)->default(0.00)->comment('Total sales in the month');
            $table->unsignedBigInteger('total_orders')->default(0)->comment('Total orders in the month');
            $table->unsignedBigInteger('total_traffic')->default(0)->comment('Total traffic in the month');
            $table->integer('goals_achieved')->default(0)->comment('Goals achieved (legacy, kept for compatibility)');
            $table->decimal('growth_percentage', 10, 2)->nullable()->comment('Growth percentage vs previous month');
            $table->timestamps();

            $table->unique('month_year', 'unique_month_year');
            $table->index('total_points', 'idx_total_points');
            $table->index('month_year', 'idx_month_year');
        });

        update_option('fleekdash_site_growth_monthly_snapshots_version', '1.0.0');
        update_option('fleekdash_site_growth_monthly_snapshots_installed_at', current_time('mysql'));
    }

    public function down(): void
    {
        $this->dropTable('fleekdash_site_growth_monthly_snapshots');
        delete_option('fleekdash_site_growth_monthly_snapshots_version');
        delete_option('fleekdash_site_growth_monthly_snapshots_installed_at');
    }
};
