<?php
namespace FleekDash\Database\Migrations;

use FleekDash\Database\Core\BaseMigration;

/**
 * Add points_per_event Column to Growth Events Table
 *
 * This migration:
 * 1. Adds points_per_event column to fleekdash_site_growth_events
 * 2. Migrates existing data from tracking_config.points_override to points_per_event
 * 3. Sets default value of 10 for new records
 * 4. Creates initial snapshot of previous month (if snapshots table exists and no snapshot exists)
 *
 * This fixes Bug #2: Points stored in JSON instead of dedicated column
 * Also ensures there's a baseline for growth percentage calculations
 *
 * @package FleekDash\Database\Migrations
 */
return new class extends BaseMigration {

    public function up(): void
    {
        // Check if table exists
        if (!$this->tableExists('fleekdash_site_growth_events')) {
            return; // Table doesn't exist yet, will be created by initial migration
        }

        // Add column if it doesn't exist (addColumn checks internally)
        $this->addColumn(
            'fleekdash_site_growth_events',
            'points_per_event',
            'INT(11) UNSIGNED NOT NULL DEFAULT 10 COMMENT \'Points awarded per event occurrence\''
        );

        // Migrate existing data from tracking_config
        $this->migrateExistingPoints();

        // Create initial snapshot of previous month if snapshots table exists
        $this->createInitialPreviousMonthSnapshot();
    }

    public function down(): void
    {
        // Remove column (data will remain in tracking_config)
        $this->dropColumn('fleekdash_site_growth_events', 'points_per_event');
    }

    /**
     * Migrate existing points from tracking_config to points_per_event
     */
    private function migrateExistingPoints(): void
    {
        $table = $this->prefix . 'fleekdash_site_growth_events';

        // Get all events with tracking_config
        $events = $this->wpdb->get_results(
            "SELECT id, tracking_config FROM {$table} WHERE tracking_config IS NOT NULL AND tracking_config != ''",
            ARRAY_A
        );

        foreach ($events as $event) {
            $points = 10; // Default value

            // Try to extract points_override from tracking_config
            if (!empty($event['tracking_config'])) {
                $config = json_decode($event['tracking_config'], true);

                // Handle double-encoded JSON
                if (is_string($config)) {
                    $config = json_decode($config, true);
                }

                // Extract points_override or points
                if (is_array($config)) {
                    if (isset($config['points_override'])) {
                        $points = max(1, (int) $config['points_override']);
                    } elseif (isset($config['points'])) {
                        $points = max(1, (int) $config['points']);
                    }
                } elseif (is_object($config)) {
                    if (isset($config->points_override)) {
                        $points = max(1, (int) $config->points_override);
                    } elseif (isset($config->points)) {
                        $points = max(1, (int) $config->points);
                    }
                }
            }

            // Update event with extracted points
            $this->wpdb->update(
                $table,
                ['points_per_event' => $points],
                ['id' => $event['id']],
                ['%d'],
                ['%d']
            );
        }
    }

    /**
     * Create initial snapshot of previous month
     *
     * This ensures there's a baseline for growth percentage calculations.
     * If no snapshot exists for the previous month, creates one using current data
     * as the baseline (so current month can show growth from that baseline).
     */
    private function createInitialPreviousMonthSnapshot(): void
    {
        $snapshots_table = $this->prefix . 'fleekdash_site_growth_monthly_snapshots';
        $events_table = $this->prefix . 'fleekdash_site_growth_events';

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

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

        // 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
        }

        // Check if snapshot for current month exists (if yes, we don't need to create previous)
        $current_exists = $this->wpdb->get_var($this->wpdb->prepare(
            "SELECT COUNT(*) FROM {$snapshots_table} WHERE month_year = %s",
            $current_month
        ));

        // Verify column points_per_event exists (should exist since we just added it)
        // 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
        ));

        // Calculate current total points (this will be used as baseline for previous month)
        $total_points = 0;

        // Use points_per_event if column exists, otherwise fallback to default
        if ($column_exists) {
            $events = $this->wpdb->get_results(
                "SELECT points_per_event, total_occurrences FROM {$events_table} WHERE is_active = 1",
                ARRAY_A
            );
        } else {
            // Fallback: use tracking_config if column doesn't exist yet
            $events = $this->wpdb->get_results(
                "SELECT tracking_config, total_occurrences FROM {$events_table} WHERE is_active = 1",
                ARRAY_A
            );
        }

        foreach ($events as $event) {
            if ($column_exists && isset($event['points_per_event'])) {
                $points_per_event = (int) $event['points_per_event'];
            } else {
                // Fallback: extract from tracking_config
                $points_per_event = 10; // Default
                if (!empty($event['tracking_config'])) {
                    $config = json_decode($event['tracking_config'], true);
                    if (is_string($config)) {
                        $config = json_decode($config, true);
                    }
                    if (is_array($config) && isset($config['points_override'])) {
                        $points_per_event = max(1, (int) $config['points_override']);
                    }
                }
            }
            $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 && function_exists('fleekdash_log_error')) {
            $error = $this->wpdb->last_error;
            fleekdash_log_error("Growth Score: Failed to create snapshot for {$previous_month}: {$error}");
        }
    }

    /**
     * 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;
    }
};
