<?php

namespace FleekDash\Controllers\Analytics;

use FleekDash\Services\ResponseService;
use FleekDash\Controllers\Analytics\Traits\HasDateRangeHelpers;
use WP_REST_Request;

/**
 * Comments Stats Controller (V2.2)
 * 
 * Analytics endpoints for comments with chart data, summaries, and period comparisons.
 * Migrated from includes/API/real/Stats/CommentsController.php
 * 
 * @package FleekDash\Controllers\Analytics
 * @since 2.2.0
 */
class CommentsStatsController
{
    use HasDateRangeHelpers;
    /**
     * Get comments stats with date range filtering and pagination
     * 
     * @param WP_REST_Request $request
     * @return \WP_REST_Response
     */
    public function index($request)
    {
        $range_info = $this->get_date_range_info($request);
        $limit = \absint($request->get_param('limit')) ?: 50;
        $page = \max(1, \absint($request->get_param('page')) ?: 1);
        $per_page = \min(100, \max(1, \absint($request->get_param('per_page')) ?: 20));

        $data = $this->get_real_comments_data($range_info, $limit, $page, $per_page);

        return ResponseService::success($data['comments'], [
            'total_comments' => $data['total_comments'],
            'total_pages' => $data['total_pages'],
            'current_page' => $page,
            'per_page' => $per_page,
            'date_range' => $range_info,
            'endpoint' => 'comments_full'
        ]);
    }

    /**
     * Get chart data (time series)
     * 
     * @param WP_REST_Request $request
     * @return \WP_REST_Response
     */
    public function chart($request)
    {
        $range_info = $this->get_date_range_info($request);
        $data = $this->get_real_comments_chart($range_info);

        return ResponseService::success($data, [
            'date_range' => $range_info,
            'endpoint' => 'comments_chart'
        ]);
    }

    /**
     * Get summary KPIs
     * 
     * @param WP_REST_Request $request
     * @return \WP_REST_Response
     */
    public function summary($request)
    {
        $range_info = $this->get_date_range_info($request);
        $summary = $this->get_real_comments_summary($range_info);

        return ResponseService::success($summary, [
            'date_range' => $range_info,
            'endpoint' => 'comments_summary'
        ]);
    }

    /**
     * Get period comparison data
     * 
     * @param WP_REST_Request $request
     * @return \WP_REST_Response
     */
    public function comparison($request)
    {
        $range_info = $this->get_date_range_info($request);
        $comparison_type = $request->get_param('comparison_type') ?: 'previous_period';

        $comparison_data = $this->get_real_comments_comparison($range_info, $comparison_type);

        return ResponseService::success($comparison_data, [
            'date_range' => $range_info,
            'comparison_type' => $comparison_type,
            'endpoint' => 'comments_comparison'
        ]);
    }

    private function get_real_comments_data($range_info, $limit, $page, $per_page)
    {
        global $wpdb;

        // Build date query
        $date_condition = '';
        if ($range_info['start_date'] && $range_info['end_date']) {
            $date_condition = $wpdb->prepare(
                "AND DATE(c.comment_date) BETWEEN %s AND %s",
                $range_info['start_date'],
                $range_info['end_date'],
            );
        } else {
            $date_condition = $wpdb->prepare(
                "AND DATE(c.comment_date) >= DATE_SUB(NOW(), INTERVAL %d DAY)",
                $range_info['days'],
            );
        }

        // Get total count
        $total_comments = $wpdb->get_var("
            SELECT COUNT(*) FROM {$wpdb->comments} c
            WHERE 1=1 {$date_condition}
        ");

        // Get paginated comments (per_page and offset sanitized as integers to prevent SQL injection)
        $offset = (int) (($page - 1) * $per_page);
        $per_page = (int) $per_page;
        $comments_query = $wpdb->prepare(
            "SELECT
                c.comment_ID,
                c.comment_post_ID,
                c.comment_author,
                c.comment_author_email,
                c.comment_author_url,
                c.comment_author_IP,
                c.comment_date,
                c.comment_date_gmt,
                c.comment_content,
                c.comment_karma,
                c.comment_approved,
                c.comment_agent,
                c.comment_type,
                c.comment_parent,
                c.user_id,
                p.post_title
            FROM {$wpdb->comments} c
            LEFT JOIN {$wpdb->posts} p ON c.comment_post_ID = p.ID
            WHERE 1=1 {$date_condition}
            ORDER BY c.comment_date DESC
            LIMIT %d OFFSET %d",
            $per_page,
            $offset
        );

        $comments = $wpdb->get_results($comments_query, ARRAY_A);
        $formatted_comments = [];

        foreach ($comments as $comment) {
            // Get comment meta
            $likes = get_comment_meta($comment['comment_ID'], 'likes', true) ?: 0;
            $replies = get_comment_meta($comment['comment_ID'], 'replies', true) ?: 0;
            $is_verified = get_comment_meta($comment['comment_ID'], 'is_verified', true) ?: false;

            // Get user info if available
            $user_info = null;
            if ($comment['user_id']) {
                $user = \get_user_by('ID', $comment['user_id']);
                if ($user) {
                    $user_info = [
                        'display_name' => $user->display_name,
                        'avatar' => \get_avatar_url($comment['user_id'], ['size' => 50])
                    ];
                }
            }

            // Get parent comment info
            $parent_comment = null;
            if ($comment['comment_parent']) {
                $parent = \get_comment($comment['comment_parent']);
                if ($parent) {
                    $parent_comment = [
                        'author' => $parent->comment_author,
                        'content' => wp_trim_words($parent->comment_content, 20)
                    ];
                }
            }

            $formatted_comments[] = [
                'id' => (int) $comment['comment_ID'],
                'author' => $comment['comment_author'],
                'author_email' => $comment['comment_author_email'],
                'author_url' => $comment['comment_author_url'],
                'author_ip' => $comment['comment_author_IP'],
                'comment_date' => date('Y-m-d', strtotime($comment['comment_date'])),
                'comment_date_gmt' => date('Y-m-d', strtotime($comment['comment_date_gmt'])),
                'comment_content' => $comment['comment_content'],
                'comment_karma' => (int) $comment['comment_karma'],
                'comment_approved' => $comment['comment_approved'] == '1' ? 'approved' : ($comment['comment_approved'] == '0' ? 'pending' : 'spam'),
                'comment_agent' => $comment['comment_agent'],
                'comment_type' => $comment['comment_type'],
                'comment_parent' => (int) $comment['comment_parent'],
                'user_id' => (int) $comment['user_id'],
                'post_id' => (int) $comment['comment_post_ID'],
                'post_title' => $comment['post_title'] ?: 'Unknown Post',
                'user_info' => $user_info,
                'parent_comment' => $parent_comment,
                'meta' => [
                    'likes' => (int) $likes,
                    'replies' => (int) $replies,
                    'is_verified' => (bool) $is_verified,
                    'user_agent' => $comment['comment_agent'],
                    'referrer' => get_comment_meta($comment['comment_ID'], 'referrer', true) ?: '',
                    'location' => get_comment_meta($comment['comment_ID'], 'location', true) ?: ''
                ]
            ];
        }

        return [
            'comments' => $formatted_comments,
            'total_comments' => (int) $total_comments,
            'total_pages' => ceil($total_comments / $per_page)
        ];
    }

    private function get_real_comments_chart($range_info)
    {
        global $wpdb;

        $start_date = $range_info['start_date'] ?: date('Y-m-d', strtotime("-{$range_info['days']} days"));
        $end_date = $range_info['end_date'] ?: date('Y-m-d');

        $query = $wpdb->prepare("
            SELECT
                DATE(comment_date) as date,
                COUNT(*) as total_comments,
                COUNT(CASE WHEN comment_approved = '1' THEN 1 END) as approved_comments,
                COUNT(CASE WHEN comment_approved = '0' THEN 1 END) as pending_comments,
                COUNT(CASE WHEN comment_approved = 'spam' THEN 1 END) as spam_comments,
                COUNT(CASE WHEN comment_parent > 0 THEN 1 END) as replies
            FROM {$wpdb->comments}
            WHERE DATE(comment_date) BETWEEN %s AND %s
            GROUP BY DATE(comment_date)
            ORDER BY date ASC
        ", $start_date, $end_date);

        $results = $wpdb->get_results($query, ARRAY_A);

        // Format results
        $chart_data = [];
        foreach ($results as $row) {
            $total_comments = (int) $row['total_comments'];
            $replies = (int) $row['replies'];
            $engagement_rate = $total_comments > 0 ? round(($replies / $total_comments) * 100, 2) : 0;

            $chart_data[] = [
                'date' => $row['date'],
                'total_comments' => $total_comments,
                'approved_comments' => (int) $row['approved_comments'],
                'pending_comments' => (int) $row['pending_comments'],
                'spam_comments' => (int) $row['spam_comments'],
                'replies' => $replies,
                'engagement_rate' => $engagement_rate
            ];
        }

        return $chart_data;
    }

    private function get_real_comments_summary($range_info)
    {
        global $wpdb;

        $start_date = $range_info['start_date'] ?: date('Y-m-d', strtotime("-{$range_info['days']} days"));
        $end_date = $range_info['end_date'] ?: date('Y-m-d');

        // Get total comments
        $total_comments = $wpdb->get_var($wpdb->prepare("
            SELECT COUNT(*) FROM {$wpdb->comments}
            WHERE DATE(comment_date) BETWEEN %s AND %s
        ", $start_date, $end_date));

        // Get approved comments
        $approved_comments = $wpdb->get_var($wpdb->prepare("
            SELECT COUNT(*) FROM {$wpdb->comments}
            WHERE DATE(comment_date) BETWEEN %s AND %s AND comment_approved = '1'
        ", $start_date, $end_date));

        // Get pending comments
        $pending_comments = $wpdb->get_var($wpdb->prepare("
            SELECT COUNT(*) FROM {$wpdb->comments}
            WHERE DATE(comment_date) BETWEEN %s AND %s AND comment_approved = '0'
        ", $start_date, $end_date));

        // Get spam comments
        $spam_comments = $wpdb->get_var($wpdb->prepare("
            SELECT COUNT(*) FROM {$wpdb->comments}
            WHERE DATE(comment_date) BETWEEN %s AND %s AND comment_approved = 'spam'
        ", $start_date, $end_date));

        // Get total replies
        $total_replies = $wpdb->get_var($wpdb->prepare("
            SELECT COUNT(*) FROM {$wpdb->comments}
            WHERE DATE(comment_date) BETWEEN %s AND %s AND comment_parent > 0
        ", $start_date, $end_date));

        // Get unique authors
        $unique_authors = $wpdb->get_var($wpdb->prepare("
            SELECT COUNT(DISTINCT comment_author_email) FROM {$wpdb->comments}
            WHERE DATE(comment_date) BETWEEN %s AND %s
        ", $start_date, $end_date));

        // Get top authors
        $top_authors = $wpdb->get_results($wpdb->prepare("
            SELECT
                comment_author as author,
                COUNT(*) as comments
            FROM {$wpdb->comments}
            WHERE DATE(comment_date) BETWEEN %s AND %s
            GROUP BY comment_author
            ORDER BY comments DESC
            LIMIT 5
        ", $start_date, $end_date), ARRAY_A);

        // Get top posts
        $top_posts = $wpdb->get_results($wpdb->prepare("
            SELECT
                p.post_title,
                COUNT(c.comment_ID) as comments
            FROM {$wpdb->comments} c
            LEFT JOIN {$wpdb->posts} p ON c.comment_post_ID = p.ID
            WHERE DATE(c.comment_date) BETWEEN %s AND %s
            GROUP BY c.comment_post_ID
            ORDER BY comments DESC
            LIMIT 5
        ", $start_date, $end_date), ARRAY_A);

        // Get recent activity
        $last_24h_comments = $wpdb->get_var("
            SELECT COUNT(*) FROM {$wpdb->comments}
            WHERE comment_date >= DATE_SUB(NOW(), INTERVAL 1 DAY)
        ");

        $last_7d_comments = $wpdb->get_var("
            SELECT COUNT(*) FROM {$wpdb->comments}
            WHERE comment_date >= DATE_SUB(NOW(), INTERVAL 7 DAY)
        ");

        $last_30d_comments = $wpdb->get_var("
            SELECT COUNT(*) FROM {$wpdb->comments}
            WHERE comment_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
        ");

        $pending_moderation = $wpdb->get_var("
            SELECT COUNT(*) FROM {$wpdb->comments}
            WHERE comment_approved = '0'
        ");

        $spam_detected = $wpdb->get_var("
            SELECT COUNT(*) FROM {$wpdb->comments}
            WHERE comment_approved = 'spam'
        ");

        $days = $range_info['days'];

        return [
            'total_comments' => (int) $total_comments,
            'approved_comments' => (int) $approved_comments,
            'pending_comments' => (int) $pending_comments,
            'spam_comments' => (int) $spam_comments,
            'total_replies' => (int) $total_replies,
            'unique_authors' => (int) $unique_authors,
            'approval_rate' => $total_comments > 0 ? round(($approved_comments / $total_comments) * 100, 1) : 0,
            'engagement_rate' => $total_comments > 0 ? round(($total_replies / $total_comments) * 100, 1) : 0,
            'comments_per_day' => $total_comments > 0 ? round($total_comments / $days, 1) : 0,
            'replies_per_day' => $total_replies > 0 ? round($total_replies / $days, 1) : 0,
            'top_authors' => $top_authors,
            'top_posts' => $top_posts,
            'recent_activity' => [
                'last_24h_comments' => (int) $last_24h_comments,
                'last_7d_comments' => (int) $last_7d_comments,
                'last_30d_comments' => (int) $last_30d_comments,
                'pending_moderation' => (int) $pending_moderation,
                'spam_detected' => (int) $spam_detected
            ]
        ];
    }

    private function get_real_comments_comparison($range_info, $comparison_type)
    {
        // Get comparison dates
        $comparison_dates = $this->get_comparison_dates($range_info, $comparison_type);

        // Get current period data
        $current_chart_data = $this->get_real_comments_chart($range_info);
        $current_summary = $this->get_real_comments_summary($range_info);

        // Get comparison period data
        $comparison_range_info = [
            'days' => $comparison_dates['days'],
            'start_date' => $comparison_dates['start_date'],
            'end_date' => $comparison_dates['end_date']
        ];
        $comparison_chart_data = $this->get_real_comments_chart($comparison_range_info);
        $comparison_summary = $this->get_real_comments_summary($comparison_range_info);

        // Calculate comparison metrics
        $comparison_metrics = $this->calculate_real_comments_comparison_metrics($current_chart_data, $comparison_chart_data);

        return [
            'current_period' => [
                'data' => $current_chart_data,
                'summary' => $current_summary
            ],
            'comparison_period' => [
                'data' => $comparison_chart_data,
                'summary' => $comparison_summary
            ],
            'comparison_metrics' => $comparison_metrics,
            'comparison_type' => $comparison_type,
            'period_dates' => [
                'current' => [
                    'start_date' => $range_info['start_date'] ?: date('Y-m-d', strtotime("-{$range_info['days']} days")),
                    'end_date' => $range_info['end_date'] ?: date('Y-m-d')
                ],
                'comparison' => [
                    'start_date' => $comparison_dates['start_date'],
                    'end_date' => $comparison_dates['end_date']
                ]
            ]
        ];
    }

    private function calculate_real_comments_comparison_metrics($current_data, $comparison_data)
    {
        $current_total_comments = array_sum(array_column($current_data, 'total_comments'));
        $comparison_total_comments = array_sum(array_column($comparison_data, 'total_comments'));

        $current_total_approved = array_sum(array_column($current_data, 'approved_comments'));
        $comparison_total_approved = array_sum(array_column($comparison_data, 'approved_comments'));

        $current_avg_engagement = array_sum(array_column($current_data, 'engagement_rate')) / max(count($current_data), 1);
        $comparison_avg_engagement = array_sum(array_column($comparison_data, 'engagement_rate')) / max(count($comparison_data), 1);

        $comments_change = $current_total_comments - $comparison_total_comments;
        $comments_change_percentage = $comparison_total_comments > 0 ? ($comments_change / $comparison_total_comments) * 100 : 0;

        $approved_change = $current_total_approved - $comparison_total_approved;
        $approved_change_percentage = $comparison_total_approved > 0 ? ($approved_change / $comparison_total_approved) * 100 : 0;

        $engagement_change = $current_avg_engagement - $comparison_avg_engagement;
        $engagement_change_percentage = $comparison_avg_engagement > 0 ? ($engagement_change / $comparison_avg_engagement) * 100 : 0;

        return [
            'total_comments' => [
                'current' => $current_total_comments,
                'previous' => $comparison_total_comments,
                'change' => $comments_change,
                'change_percentage' => round($comments_change_percentage, 2),
                'trend' => $comments_change > 0 ? 'up' : ($comments_change < 0 ? 'down' : 'stable')
            ],
            'approved_comments' => [
                'current' => $current_total_approved,
                'previous' => $comparison_total_approved,
                'change' => $approved_change,
                'change_percentage' => round($approved_change_percentage, 2),
                'trend' => $approved_change > 0 ? 'up' : ($approved_change < 0 ? 'down' : 'stable')
            ],
            'engagement_rate' => [
                'current' => round($current_avg_engagement, 1),
                'previous' => round($comparison_avg_engagement, 1),
                'change' => round($engagement_change, 1),
                'change_percentage' => round($engagement_change_percentage, 2),
                'trend' => $engagement_change > 0 ? 'up' : ($engagement_change < 0 ? 'down' : 'stable')
            ]
        ];
    }
}
