<?php

namespace FleekDash\Services\SiteGrowthMeter;

use FleekDash\Models\SiteGrowthEvent;

/**
 * Growth Calculator
 *
 * Handles all growth calculations and metrics
 *
 * All methods gracefully handle missing tables during plugin activation
 *
 * @package FleekDash\Services\SiteGrowthMeter
 */
class GrowthCalculator
{
    /**
     * Check if snapshots table exists
     *
     * @return bool
     */
    private function snapshotsTableExists(): bool
    {
        global $wpdb;
        $snapshots_table = $wpdb->prefix . 'fleekdash_site_growth_monthly_snapshots';

        $result = $wpdb->get_var($wpdb->prepare(
            "SHOW TABLES LIKE %s",
            $snapshots_table
        ));

        return $result === $snapshots_table;
    }

    /**
     * Calculate initial metrics
     *
     * @return array Initial metrics
     */
    public function calculateInitialMetrics()
    {
        // Exclude technical post types (elementor_library, attachment, etc)
        $excluded_types = ['page', 'profile', 'elementor_library', 'attachment', 'e-floating-buttons'];
        $post_types = \get_post_types(['public' => true], 'names');
        $allowed_post_types = array_values(array_diff($post_types, $excluded_types));

        $total_posts = 0;
        foreach ($allowed_post_types as $post_type) {
            $total_posts += wp_count_posts($post_type)->publish;
        }

        $metrics = [
            'total_posts' => $total_posts,
            'total_users' => count_users()['total_users'],
            'total_comments' => wp_count_comments()->approved,
            'total_sales' => 0,
            'total_orders' => 0
        ];

        if (class_exists('WooCommerce')) {
            $woo = $this->getWooOrdersMetrics(null, null);
            $metrics['total_sales'] = (float) $woo['total_sales'];
            $metrics['total_orders'] = (int) $woo['total_orders'];
        }

        return $metrics;
    }

    /**
     * Calculate current value for a goal
     *
     * @param object $goal Goal object
     * @return int|float Current value
     */
    public function calculateCurrentValue($goal)
    {
        global $wpdb;
        $events_table = $wpdb->prefix . 'fleekdash_site_growth_events';
        $start = isset($goal->start_date) ? $goal->start_date : null;
        $end = isset($goal->end_date) ? $goal->end_date : null;

        switch ($goal->goal_type) {
            case 'posts':
                $posts_table = $wpdb->posts;
                $excluded_types = ['page', 'profile'];
                $post_types = \get_post_types(['public' => true], 'names');
                $allowed_post_types = array_values(array_diff($post_types, $excluded_types));

                if ($start && $end) {
                    $placeholders = implode(',', array_fill(0, count($allowed_post_types), '%s'));
                    $query = "SELECT COUNT(*) FROM {$posts_table} WHERE post_type IN ({$placeholders}) AND post_status = 'publish' AND post_date >= %s AND post_date <= %s";
                    $params = array_merge($allowed_post_types, [$start, $end]);
                    $count = $wpdb->prepare($query, $params);
                    $count = $wpdb->get_var($count);
                    return (int) $count;
                }

                $total_posts = 0;
                foreach ($allowed_post_types as $post_type) {
                    $total_posts += wp_count_posts($post_type)->publish;
                }
                return $total_posts;

            case 'users':
                $users_table = $wpdb->users;
                if ($start && $end) {
                    $count = $wpdb->get_var($wpdb->prepare(
                        "SELECT COUNT(*) FROM {$users_table} WHERE user_registered >= %s AND user_registered <= %s",
                        $start,
                        $end
                    ));
                    return (int) $count;
                }
                $totals = count_users();
                return isset($totals['total_users']) ? (int) $totals['total_users'] : 0;

            case 'sales':
                if (class_exists('WooCommerce')) {
                    $metrics = $this->getWooOrdersMetrics($start, $end);
                    return (float) $metrics['total_sales'];
                }
                return 0.0;

            case 'custom':
                if ($goal->related_event_id) {
                    $event = $wpdb->get_row($wpdb->prepare(
                        "SELECT total_occurrences FROM {$events_table} WHERE id = %d",
                        $goal->related_event_id
                    ));
                    return $event ? $event->total_occurrences : 0;
                }
                return 0;

            default:
                return 0;
        }
    }

    /**
     * Calculate month over month growth
     *
     * Returns default data if table doesn't exist
     *
     * @return array Growth data
     */
    public function calculateMonthOverMonthGrowth()
    {
        $current_month = date('Y-m');
        $previous_month = date('Y-m', strtotime('-1 month'));

        // Return safe defaults if table doesn't exist
        if (!$this->snapshotsTableExists()) {
            return [
                'current_month' => $current_month,
                'current_points' => 0,
                'previous_month' => $previous_month,
                'previous_points' => 0,
                'absolute_change' => 0,
                'percentage_change' => 0,
                'is_growth' => false
            ];
        }

        global $wpdb;
        $snapshots_table = $wpdb->prefix . 'fleekdash_site_growth_monthly_snapshots';

        $current = $wpdb->get_row($wpdb->prepare(
            "SELECT total_points FROM {$snapshots_table} WHERE month_year = %s",
            $current_month
        ));

        $previous = $wpdb->get_row($wpdb->prepare(
            "SELECT total_points FROM {$snapshots_table} WHERE month_year = %s",
            $previous_month
        ));

        $current_points = $current ? $current->total_points : 0;
        $previous_points = $previous ? $previous->total_points : 0;

        $change = $current_points - $previous_points;
        $percentage = $previous_points > 0 ? ($change / $previous_points) * 100 : 0;

        return [
            'current_month' => $current_month,
            'current_points' => $current_points,
            'previous_month' => $previous_month,
            'previous_points' => $previous_points,
            'absolute_change' => $change,
            'percentage_change' => round($percentage, 2),
            'is_growth' => $change > 0
        ];
    }

    /**
     * Calculate total accumulated points (all events, all time)
     *
     * Returns 0 if events table doesn't exist
     */
    public function calculateTotalAccumulatedPoints(): int
    {
        // Return 0 if events table doesn't exist
        if (!SiteGrowthEvent::tableExists()) {
            return 0;
        }

        $events = SiteGrowthEvent::getActive();
        $total = 0;

        foreach ($events as $event) {
            $points = GrowthHelpers::computeEventTotalPoints(
                $event,
                $event['total_occurrences'] ?? 0
            );
            $total += $points;
        }

        return $total;
    }

    /**
     * Calculate current month points (difference between accumulated baseline snapshots)
     *
     * Proper logic for calculating current month points
     * Returns 0 if tables don't exist
     *
     * Logic:
     * 1. If previous month snapshot exists: current_total - previous_snapshot
     * 2. If no previous snapshot: return current_total (all points are "new")
     *
     * Note: Current month snapshot is NOT used for calculation, only for historical record
     */
    public function calculateCurrentMonthPoints(): int
    {
        // Check if tables exist
        if (!SiteGrowthEvent::tableExists() || !$this->snapshotsTableExists()) {
            return 0;
        }

        global $wpdb;
        $snapshots_table = $wpdb->prefix . 'fleekdash_site_growth_monthly_snapshots';

        $total_accumulated = $this->calculateTotalAccumulatedPoints();

        // Bug #3 Fix: Calculate current month points using previous month snapshot
        $previous_month = date('Y-m', strtotime('-1 month'));

        // Get snapshot from previous month (baseline)
        $previous_snapshot = $wpdb->get_row($wpdb->prepare(
            "SELECT total_points FROM {$snapshots_table} WHERE month_year = %s",
            $previous_month
        ));

        if ($previous_snapshot) {
            // Calculate difference: current total - previous month total = current month points
            // Use max(0, ...) to handle edge case where total decreased (e.g., events deleted)
            $current_month_points = max(0, $total_accumulated - $previous_snapshot->total_points);
            return $current_month_points;
        }

        // Fallback: If no previous snapshot exists, all current points are "new" this month
        // This happens on first month or fresh installations
        return $total_accumulated;
    }

    /**
     * Create monthly snapshot
     *
     * Saves current month's metrics to snapshots table for historical comparison
     *
     * Returns false if tables don't exist
     *
     * @param string|null $month_year Month in format YYYY-MM (defaults to current month)
     * @return bool Success status
     */
    public function createMonthlySnapshot($month_year = null)
    {
        // Check if tables exist before creating snapshot
        if (!SiteGrowthEvent::tableExists() || !$this->snapshotsTableExists()) {
            if (function_exists('fleekdash_log_debug')) {
                fleekdash_log_debug('GrowthCalculator::createMonthlySnapshot - Tables do not exist yet');
            }
            return false;
        }

        global $wpdb;
        $snapshots_table = $wpdb->prefix . 'fleekdash_site_growth_monthly_snapshots';

        if (!$month_year) {
            $month_year = date('Y-m');
        }

        $metrics = $this->calculateInitialMetrics();
        $total_points = $this->calculateTotalAccumulatedPoints();

        // Calculate growth percentage vs previous month
        $previous_month = date('Y-m', strtotime($month_year . '-01 -1 month'));
        $previous_snapshot = $wpdb->get_row($wpdb->prepare(
            "SELECT total_points FROM {$snapshots_table} WHERE month_year = %s",
            $previous_month
        ));

        $previous_points = $previous_snapshot ? $previous_snapshot->total_points : 0;

        $current_month_points = $previous_points > 0
            ? max(0, $total_points - $previous_points)
            : $total_points;

        // Need previous month points (difference between previous snapshot and month before)
        $month_before_previous = date('Y-m', strtotime($previous_month . '-01 -1 month'));
        $snapshot_before_previous = $wpdb->get_row($wpdb->prepare(
            "SELECT total_points FROM {$snapshots_table} WHERE month_year = %s",
            $month_before_previous
        ));
        $month_before_points = $snapshot_before_previous ? $snapshot_before_previous->total_points : 0;
        $previous_month_points = $previous_snapshot
            ? max(0, $previous_points - $month_before_points)
            : null;

        $growth_percentage = ($previous_month_points && $previous_month_points > 0)
            ? (($current_month_points - $previous_month_points) / $previous_month_points) * 100
            : null;

        // Insert or update snapshot
        $existing = $wpdb->get_row($wpdb->prepare(
            "SELECT id FROM {$snapshots_table} WHERE month_year = %s",
            $month_year
        ));

        $snapshot_data = [
            'month_year' => $month_year,
            '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, // TODO: Implement traffic tracking if needed
            'goals_achieved' => 0, // Legacy field, kept for compatibility
            'growth_percentage' => $growth_percentage !== null ? round($growth_percentage, 2) : null,
            'updated_at' => \current_time('mysql')
        ];

        if ($existing) {
            // Update existing snapshot
            $result = $wpdb->update(
                $snapshots_table,
                $snapshot_data,
                ['id' => $existing->id],
                ['%s', '%d', '%d', '%d', '%d', '%f', '%d', '%d', '%d', '%f', '%s'],
                ['%d']
            );
        } else {
            // Insert new snapshot
            $snapshot_data['created_at'] = \current_time('mysql');
            $result = $wpdb->insert(
                $snapshots_table,
                $snapshot_data,
                ['%s', '%d', '%d', '%d', '%d', '%f', '%d', '%d', '%d', '%f', '%s', '%s']
            );
        }

        return $result !== false;
    }

    /**
     * Get WooCommerce orders metrics
     *
     * @param string|null $start Start date
     * @param string|null $end End date
     * @return array Metrics
     */
    public function getWooOrdersMetrics($start = null, $end = null)
    {
        if (!class_exists('WooCommerce')) {
            return [
                'total_orders' => 0,
                'total_sales' => 0.0
            ];
        }

        global $wpdb;
        $orders_table = $wpdb->prefix . 'wc_orders';

        $query = "SELECT COUNT(*) as total_orders, SUM(total_amount) as total_sales FROM {$orders_table} WHERE status = 'wc-completed'";

        if ($start && $end) {
            $query .= $wpdb->prepare(" AND date_created_gmt >= %s AND date_created_gmt <= %s", $start, $end);
        }

        $result = $wpdb->get_row($query);

        return [
            'total_orders' => (int) ($result->total_orders ?? 0),
            'total_sales' => (float) ($result->total_sales ?? 0.0)
        ];
    }
}
