<?php
namespace FleekDash\Database\Migrations;

use FleekDash\Database\Core\BaseMigration;

/**
 * Create Initial Previous Month Snapshot
 *
 * This migration:
 * 1. Checks if snapshot table exists
 * 2. Checks if snapshot for previous month exists
 * 3. If not, creates snapshot of previous month using current data as baseline
 * 4. This ensures there's a baseline for growth percentage calculations
 *
 * Why: Without a previous month snapshot, growth percentage will be null/zero
 * This migration creates that baseline so current month can show growth
 *
 * @package FleekDash\Database\Migrations
 */
return new class extends BaseMigration {

    public function up(): void
    {
        // Check if snapshots table exists
        if (!$this->tableExists('fleekdash_site_growth_monthly_snapshots')) {
            return; // Snapshots table doesn't exist yet
        }

        // Check if events table exists
        if (!$this->tableExists('fleekdash_site_growth_events')) {
            return; // Events table doesn't exist yet
        }

        $snapshots_table = $this->prefix . 'fleekdash_site_growth_monthly_snapshots';
        $events_table = $this->prefix . 'fleekdash_site_growth_events';

        // Get previous month
        $previous_month = date('Y-m', strtotime('-1 month'));
        $current_month = date('Y-m');

        // Check if snapshot for previous month already exists
        $existing = $this->wpdb->get_var($this->wpdb->prepare(
            "SELECT COUNT(*) FROM {$snapshots_table} WHERE month_year = %s",
            $previous_month
        ));

        if ($existing) {
            return; // Snapshot already exists, no need to create
        }

        // Verify column points_per_event exists (from migration #1)
        // NOTE: information_schema.columns.table_name stores FULL table name with prefix
        $column_exists = $this->wpdb->get_var($this->wpdb->prepare(
            "SELECT COUNT(*) FROM information_schema.columns
             WHERE table_schema = DATABASE()
             AND table_name = %s
             AND column_name = 'points_per_event'",
            $events_table
        ));

        if (!$column_exists) {
            return; // Column doesn't exist yet, migration #1 needs to run first
        }

        // Calculate current total points (this will be used as baseline for previous month)
        $total_points = 0;
        $events = $this->wpdb->get_results(
            "SELECT points_per_event, total_occurrences FROM {$events_table} WHERE is_active = 1",
            ARRAY_A
        );

        foreach ($events as $event) {
            $points_per_event = isset($event['points_per_event']) ? (int) $event['points_per_event'] : 10;
            $occurrences = (int) ($event['total_occurrences'] ?? 0);
            $total_points += $points_per_event * $occurrences;
        }

        // Get basic metrics (posts, users, comments, etc.)
        $metrics = $this->calculateBasicMetrics();

        // Create snapshot for previous month using current data as baseline
        // This gives a starting point for growth calculations
        $snapshot_data = [
            'month_year' => $previous_month,
            'total_points' => $total_points,
            'total_posts' => $metrics['total_posts'],
            'total_users' => $metrics['total_users'],
            'total_comments' => $metrics['total_comments'],
            'total_sales' => $metrics['total_sales'],
            'total_orders' => $metrics['total_orders'],
            'total_traffic' => 0,
            'goals_achieved' => 0,
            'growth_percentage' => null, // No previous month to compare
            'created_at' => current_time('mysql'),
            'updated_at' => current_time('mysql')
        ];

        // Double-check snapshot doesn't exist (race condition protection)
        $double_check = $this->wpdb->get_var($this->wpdb->prepare(
            "SELECT COUNT(*) FROM {$snapshots_table} WHERE month_year = %s",
            $previous_month
        ));

        if ($double_check) {
            return; // Snapshot was created by another process, skip
        }

        // Insert snapshot (table has UNIQUE constraint on month_year, so safe)
        $result = $this->wpdb->insert(
            $snapshots_table,
            $snapshot_data,
            ['%s', '%d', '%d', '%d', '%d', '%f', '%d', '%d', '%d', '%f', '%s', '%s']
        );

        if ($result) {
            // Log success (if logging is available)
            if (function_exists('fleekdash_log_info')) {
                fleekdash_log_info("Growth Score: Created initial snapshot for {$previous_month} with {$total_points} points");
            }
        } else {
            // Log error if insert failed (might be duplicate key error)
            if (function_exists('fleekdash_log_error')) {
                $error = $this->wpdb->last_error;
                fleekdash_log_error("Growth Score: Failed to create snapshot for {$previous_month}: {$error}");
            }
        }
    }

    public function down(): void
    {
        // Remove snapshot of previous month if it was created by this migration
        // Note: We can't safely determine if it was created by this migration,
        // so we'll leave it (it's useful data anyway)
    }

    /**
     * Calculate basic metrics for snapshot
     *
     * @return array Basic metrics
     */
    private function calculateBasicMetrics(): array
    {
        global $wpdb;

        $metrics = [
            'total_posts' => 0,
            'total_users' => 0,
            'total_comments' => 0,
            'total_sales' => 0.0,
            'total_orders' => 0,
        ];

        // Get total posts
        $metrics['total_posts'] = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'publish' AND post_type = 'post'"
        );

        // Get total users
        $metrics['total_users'] = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->users}"
        );

        // Get total comments
        $metrics['total_comments'] = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_approved = '1'"
        );

        // Get WooCommerce metrics if available
        if (class_exists('WooCommerce')) {
            $orders_table = $wpdb->prefix . 'wc_orders';
            if ($wpdb->get_var("SHOW TABLES LIKE '{$orders_table}'") === $orders_table) {
                $woo_metrics = $wpdb->get_row(
                    "SELECT COUNT(*) as total_orders, SUM(total_amount) as total_sales
                     FROM {$orders_table}
                     WHERE status = 'wc-completed'",
                    ARRAY_A
                );

                if ($woo_metrics) {
                    $metrics['total_orders'] = (int) ($woo_metrics['total_orders'] ?? 0);
                    $metrics['total_sales'] = (float) ($woo_metrics['total_sales'] ?? 0.0);
                }
            }
        }

        return $metrics;
    }
};
