<?php
/**
 * ============================================
 * FLOWBOT DCI - ANALYTICS CONTROLLER v1.0
 * ============================================
 * API controller for crawler analytics.
 *
 * Endpoints:
 * - GET /api/v1/analytics/overview      - Dashboard overview
 * - GET /api/v1/analytics/domains       - Domain statistics
 * - GET /api/v1/analytics/performance   - Performance metrics
 * - GET /api/v1/analytics/timeline      - Timeline data
 * - GET /api/v1/analytics/errors        - Error analysis
 * - GET /api/v1/analytics/trends        - Trend analysis
 * ============================================
 */

declare(strict_types=1);

namespace FlowbotDCI\Api\v1;

use FlowbotDCI\Core\Database;
use PDO;

class AnalyticsController
{
    const VERSION = '1.0';

    private array $config;
    private ?PDO $pdo = null;

    /**
     * Constructor
     */
    public function __construct(array $config, array $dbConfig)
    {
        $this->config = $config;

        // Initialize database connection
        try {
            $dsn = sprintf(
                'mysql:host=%s;port=%s;dbname=%s;charset=utf8mb4',
                $dbConfig['host'] ?? 'localhost',
                $dbConfig['port'] ?? 3306,
                $dbConfig['database'] ?? ''
            );
            $this->pdo = new PDO($dsn, $dbConfig['username'] ?? '', $dbConfig['password'] ?? '', [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
            ]);
        } catch (\PDOException $e) {
            throw new \Exception("Database connection failed: " . $e->getMessage());
        }
    }

    /**
     * Get dashboard overview statistics
     */
    public function getOverview(array $params = []): array
    {
        try {
            $period = $params['period'] ?? '7d';
            $dateRange = $this->getDateRange($period);

            // Get job statistics
            $jobStats = $this->getJobStats($dateRange);

            // Get URL statistics
            $urlStats = $this->getUrlStats($dateRange);

            // Get domain statistics
            $domainStats = $this->getDomainOverview($dateRange);

            // Get performance metrics
            $perfMetrics = $this->getPerformanceMetrics($dateRange);

            // Get error summary
            $errorSummary = $this->getErrorSummary($dateRange);

            return [
                'success' => true,
                'data' => [
                    'period' => $period,
                    'date_range' => $dateRange,
                    'jobs' => $jobStats,
                    'urls' => $urlStats,
                    'domains' => $domainStats,
                    'performance' => $perfMetrics,
                    'errors' => $errorSummary,
                    'generated_at' => date('Y-m-d H:i:s'),
                ],
            ];
        } catch (\Exception $e) {
            return [
                'success' => false,
                'error' => 'Failed to get overview: ' . $e->getMessage(),
                'http_code' => 500,
            ];
        }
    }

    /**
     * Get domain statistics
     */
    public function getDomains(array $params = []): array
    {
        try {
            $page = max(1, (int)($params['page'] ?? 1));
            $limit = min(100, max(10, (int)($params['limit'] ?? 20)));
            $offset = ($page - 1) * $limit;
            $sortBy = $params['sort'] ?? 'total_visits';
            $sortDir = strtoupper($params['dir'] ?? 'DESC') === 'ASC' ? 'ASC' : 'DESC';

            // Validate sort column
            $allowedSorts = ['domain', 'total_visits', 'successful_visits', 'failed_visits', 'avg_response_time', 'last_visit'];
            if (!in_array($sortBy, $allowedSorts)) {
                $sortBy = 'total_visits';
            }

            // Get total count
            $countStmt = $this->pdo->query("SELECT COUNT(*) FROM crawler_domain_stats");
            $totalCount = (int)$countStmt->fetchColumn();

            // Get domain data
            $sql = "SELECT
                        domain,
                        total_visits,
                        successful_visits,
                        failed_visits,
                        avg_response_time,
                        last_visit,
                        last_http_code,
                        crawl_delay,
                        is_blocked,
                        block_reason,
                        CASE WHEN total_visits > 0
                            THEN ROUND((successful_visits / total_visits) * 100, 2)
                            ELSE 0
                        END as success_rate
                    FROM crawler_domain_stats
                    ORDER BY {$sortBy} {$sortDir}
                    LIMIT :limit OFFSET :offset";

            $stmt = $this->pdo->prepare($sql);
            $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
            $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
            $stmt->execute();

            $domains = $stmt->fetchAll();

            return [
                'success' => true,
                'data' => [
                    'domains' => $domains,
                    'pagination' => [
                        'page' => $page,
                        'limit' => $limit,
                        'total' => $totalCount,
                        'pages' => ceil($totalCount / $limit),
                    ],
                ],
            ];
        } catch (\Exception $e) {
            return [
                'success' => false,
                'error' => 'Failed to get domain stats: ' . $e->getMessage(),
                'http_code' => 500,
            ];
        }
    }

    /**
     * Get performance metrics
     */
    public function getPerformance(array $params = []): array
    {
        try {
            $period = $params['period'] ?? '7d';
            $dateRange = $this->getDateRange($period);

            // Get hourly metrics for the period
            $sql = "SELECT
                        metric_date,
                        metric_hour,
                        total_jobs,
                        completed_jobs,
                        failed_jobs,
                        total_urls_crawled,
                        total_urls_processed,
                        total_errors,
                        avg_job_duration,
                        avg_urls_per_job,
                        avg_response_time
                    FROM crawler_metrics
                    WHERE metric_date BETWEEN :start AND :end
                    ORDER BY metric_date ASC, metric_hour ASC";

            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([
                ':start' => $dateRange['start'],
                ':end' => $dateRange['end'],
            ]);

            $metrics = $stmt->fetchAll();

            // Calculate aggregates
            $totals = [
                'total_jobs' => 0,
                'completed_jobs' => 0,
                'failed_jobs' => 0,
                'total_urls_crawled' => 0,
                'total_urls_processed' => 0,
                'total_errors' => 0,
                'avg_job_duration' => 0,
                'avg_response_time' => 0,
            ];

            $durationSum = 0;
            $responseTimeSum = 0;
            $durationCount = 0;
            $responseTimeCount = 0;

            foreach ($metrics as $m) {
                $totals['total_jobs'] += $m['total_jobs'];
                $totals['completed_jobs'] += $m['completed_jobs'];
                $totals['failed_jobs'] += $m['failed_jobs'];
                $totals['total_urls_crawled'] += $m['total_urls_crawled'];
                $totals['total_urls_processed'] += $m['total_urls_processed'];
                $totals['total_errors'] += $m['total_errors'];

                if ($m['avg_job_duration'] > 0) {
                    $durationSum += $m['avg_job_duration'];
                    $durationCount++;
                }
                if ($m['avg_response_time'] > 0) {
                    $responseTimeSum += $m['avg_response_time'];
                    $responseTimeCount++;
                }
            }

            $totals['avg_job_duration'] = $durationCount > 0 ? round($durationSum / $durationCount, 2) : 0;
            $totals['avg_response_time'] = $responseTimeCount > 0 ? round($responseTimeSum / $responseTimeCount, 2) : 0;

            // Calculate success rate
            $totals['success_rate'] = $totals['total_jobs'] > 0
                ? round(($totals['completed_jobs'] / $totals['total_jobs']) * 100, 2)
                : 0;

            return [
                'success' => true,
                'data' => [
                    'period' => $period,
                    'date_range' => $dateRange,
                    'totals' => $totals,
                    'timeline' => $metrics,
                ],
            ];
        } catch (\Exception $e) {
            return [
                'success' => false,
                'error' => 'Failed to get performance metrics: ' . $e->getMessage(),
                'http_code' => 500,
            ];
        }
    }

    /**
     * Get timeline data for charts
     */
    public function getTimeline(array $params = []): array
    {
        try {
            $period = $params['period'] ?? '7d';
            $granularity = $params['granularity'] ?? 'hourly'; // hourly, daily
            $dateRange = $this->getDateRange($period);

            if ($granularity === 'daily') {
                $sql = "SELECT
                            metric_date as date,
                            SUM(total_jobs) as jobs,
                            SUM(completed_jobs) as completed,
                            SUM(failed_jobs) as failed,
                            SUM(total_urls_crawled) as urls_crawled,
                            SUM(total_urls_processed) as urls_processed,
                            SUM(total_errors) as errors,
                            AVG(avg_response_time) as avg_response_time
                        FROM crawler_metrics
                        WHERE metric_date BETWEEN :start AND :end
                        GROUP BY metric_date
                        ORDER BY metric_date ASC";
            } else {
                $sql = "SELECT
                            CONCAT(metric_date, ' ', LPAD(metric_hour, 2, '0'), ':00') as datetime,
                            total_jobs as jobs,
                            completed_jobs as completed,
                            failed_jobs as failed,
                            total_urls_crawled as urls_crawled,
                            total_urls_processed as urls_processed,
                            total_errors as errors,
                            avg_response_time
                        FROM crawler_metrics
                        WHERE metric_date BETWEEN :start AND :end
                        ORDER BY metric_date ASC, metric_hour ASC";
            }

            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([
                ':start' => $dateRange['start'],
                ':end' => $dateRange['end'],
            ]);

            $timeline = $stmt->fetchAll();

            return [
                'success' => true,
                'data' => [
                    'period' => $period,
                    'granularity' => $granularity,
                    'date_range' => $dateRange,
                    'timeline' => $timeline,
                ],
            ];
        } catch (\Exception $e) {
            return [
                'success' => false,
                'error' => 'Failed to get timeline: ' . $e->getMessage(),
                'http_code' => 500,
            ];
        }
    }

    /**
     * Get error analysis
     */
    public function getErrors(array $params = []): array
    {
        try {
            $period = $params['period'] ?? '7d';
            $dateRange = $this->getDateRange($period);

            // Get error counts by domain
            $sql = "SELECT
                        domain,
                        failed_visits as error_count,
                        last_http_code,
                        CASE WHEN total_visits > 0
                            THEN ROUND((failed_visits / total_visits) * 100, 2)
                            ELSE 0
                        END as error_rate
                    FROM crawler_domain_stats
                    WHERE failed_visits > 0
                    ORDER BY failed_visits DESC
                    LIMIT 20";

            $stmt = $this->pdo->prepare($sql);
            $stmt->execute();
            $byDomain = $stmt->fetchAll();

            // Get HTTP code distribution
            $sql = "SELECT
                        last_http_code as http_code,
                        COUNT(*) as count
                    FROM crawler_domain_stats
                    WHERE last_http_code IS NOT NULL
                    GROUP BY last_http_code
                    ORDER BY count DESC";

            $stmt = $this->pdo->query($sql);
            $byHttpCode = $stmt->fetchAll();

            // Get blocked domains
            $sql = "SELECT
                        domain,
                        block_reason,
                        updated_at as blocked_at
                    FROM crawler_domain_stats
                    WHERE is_blocked = 1
                    ORDER BY updated_at DESC
                    LIMIT 10";

            $stmt = $this->pdo->query($sql);
            $blockedDomains = $stmt->fetchAll();

            return [
                'success' => true,
                'data' => [
                    'period' => $period,
                    'date_range' => $dateRange,
                    'by_domain' => $byDomain,
                    'by_http_code' => $byHttpCode,
                    'blocked_domains' => $blockedDomains,
                ],
            ];
        } catch (\Exception $e) {
            return [
                'success' => false,
                'error' => 'Failed to get error analysis: ' . $e->getMessage(),
                'http_code' => 500,
            ];
        }
    }

    /**
     * Get trend analysis
     */
    public function getTrends(array $params = []): array
    {
        try {
            $period = $params['period'] ?? '30d';
            $dateRange = $this->getDateRange($period);

            // Get daily trends
            $sql = "SELECT
                        metric_date as date,
                        SUM(total_jobs) as jobs,
                        SUM(total_urls_crawled) as urls,
                        SUM(total_errors) as errors,
                        AVG(avg_response_time) as avg_response_time,
                        SUM(unique_domains) as domains
                    FROM crawler_metrics
                    WHERE metric_date BETWEEN :start AND :end
                    GROUP BY metric_date
                    ORDER BY metric_date ASC";

            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([
                ':start' => $dateRange['start'],
                ':end' => $dateRange['end'],
            ]);

            $dailyTrends = $stmt->fetchAll();

            // Calculate week-over-week changes
            $weeklyChanges = [];
            if (count($dailyTrends) >= 14) {
                $lastWeek = array_slice($dailyTrends, -7);
                $prevWeek = array_slice($dailyTrends, -14, 7);

                $lastWeekJobs = array_sum(array_column($lastWeek, 'jobs'));
                $prevWeekJobs = array_sum(array_column($prevWeek, 'jobs'));

                $lastWeekUrls = array_sum(array_column($lastWeek, 'urls'));
                $prevWeekUrls = array_sum(array_column($prevWeek, 'urls'));

                $lastWeekErrors = array_sum(array_column($lastWeek, 'errors'));
                $prevWeekErrors = array_sum(array_column($prevWeek, 'errors'));

                $weeklyChanges = [
                    'jobs' => [
                        'current' => $lastWeekJobs,
                        'previous' => $prevWeekJobs,
                        'change' => $prevWeekJobs > 0 ? round((($lastWeekJobs - $prevWeekJobs) / $prevWeekJobs) * 100, 2) : 0,
                    ],
                    'urls' => [
                        'current' => $lastWeekUrls,
                        'previous' => $prevWeekUrls,
                        'change' => $prevWeekUrls > 0 ? round((($lastWeekUrls - $prevWeekUrls) / $prevWeekUrls) * 100, 2) : 0,
                    ],
                    'errors' => [
                        'current' => $lastWeekErrors,
                        'previous' => $prevWeekErrors,
                        'change' => $prevWeekErrors > 0 ? round((($lastWeekErrors - $prevWeekErrors) / $prevWeekErrors) * 100, 2) : 0,
                    ],
                ];
            }

            // Get top growing domains
            $sql = "SELECT
                        domain,
                        total_visits,
                        successful_visits,
                        CASE WHEN total_visits > 0
                            THEN ROUND((successful_visits / total_visits) * 100, 2)
                            ELSE 0
                        END as success_rate
                    FROM crawler_domain_stats
                    WHERE last_visit >= DATE_SUB(NOW(), INTERVAL 7 DAY)
                    ORDER BY total_visits DESC
                    LIMIT 10";

            $stmt = $this->pdo->query($sql);
            $topGrowingDomains = $stmt->fetchAll();

            return [
                'success' => true,
                'data' => [
                    'period' => $period,
                    'date_range' => $dateRange,
                    'daily_trends' => $dailyTrends,
                    'weekly_changes' => $weeklyChanges,
                    'top_growing_domains' => $topGrowingDomains,
                ],
            ];
        } catch (\Exception $e) {
            return [
                'success' => false,
                'error' => 'Failed to get trends: ' . $e->getMessage(),
                'http_code' => 500,
            ];
        }
    }

    /**
     * Get date range from period string
     */
    private function getDateRange(string $period): array
    {
        $end = date('Y-m-d');

        switch ($period) {
            case '24h':
                $start = date('Y-m-d', strtotime('-1 day'));
                break;
            case '7d':
                $start = date('Y-m-d', strtotime('-7 days'));
                break;
            case '30d':
                $start = date('Y-m-d', strtotime('-30 days'));
                break;
            case '90d':
                $start = date('Y-m-d', strtotime('-90 days'));
                break;
            case '1y':
                $start = date('Y-m-d', strtotime('-1 year'));
                break;
            default:
                $start = date('Y-m-d', strtotime('-7 days'));
        }

        return ['start' => $start, 'end' => $end];
    }

    /**
     * Get job statistics for period
     */
    private function getJobStats(array $dateRange): array
    {
        $sql = "SELECT
                    COUNT(*) as total,
                    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed,
                    SUM(CASE WHEN status = 'running' THEN 1 ELSE 0 END) as running,
                    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed,
                    SUM(CASE WHEN status = 'paused' THEN 1 ELSE 0 END) as paused,
                    AVG(TIMESTAMPDIFF(SECOND, started_at, completed_at)) as avg_duration
                FROM crawler_jobs
                WHERE created_at BETWEEN :start AND :end";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':start' => $dateRange['start'] . ' 00:00:00',
            ':end' => $dateRange['end'] . ' 23:59:59',
        ]);

        $result = $stmt->fetch();

        return [
            'total' => (int)($result['total'] ?? 0),
            'completed' => (int)($result['completed'] ?? 0),
            'running' => (int)($result['running'] ?? 0),
            'failed' => (int)($result['failed'] ?? 0),
            'paused' => (int)($result['paused'] ?? 0),
            'avg_duration_seconds' => round($result['avg_duration'] ?? 0, 2),
        ];
    }

    /**
     * Get URL statistics for period
     */
    private function getUrlStats(array $dateRange): array
    {
        $sql = "SELECT
                    SUM(total_urls_crawled) as crawled,
                    SUM(total_urls_processed) as processed,
                    SUM(total_errors) as errors
                FROM crawler_metrics
                WHERE metric_date BETWEEN :start AND :end";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':start' => $dateRange['start'],
            ':end' => $dateRange['end'],
        ]);

        $result = $stmt->fetch();

        return [
            'crawled' => (int)($result['crawled'] ?? 0),
            'processed' => (int)($result['processed'] ?? 0),
            'errors' => (int)($result['errors'] ?? 0),
        ];
    }

    /**
     * Get domain overview statistics
     */
    private function getDomainOverview(array $dateRange): array
    {
        // Total unique domains
        $stmt = $this->pdo->query("SELECT COUNT(DISTINCT domain) FROM crawler_domain_stats");
        $totalDomains = (int)$stmt->fetchColumn();

        // Blocked domains
        $stmt = $this->pdo->query("SELECT COUNT(*) FROM crawler_domain_stats WHERE is_blocked = 1");
        $blockedDomains = (int)$stmt->fetchColumn();

        // Top domains by visits
        $stmt = $this->pdo->query("
            SELECT domain, total_visits, successful_visits
            FROM crawler_domain_stats
            ORDER BY total_visits DESC
            LIMIT 5
        ");
        $topDomains = $stmt->fetchAll();

        return [
            'total' => $totalDomains,
            'blocked' => $blockedDomains,
            'top' => $topDomains,
        ];
    }

    /**
     * Get performance metrics for period
     */
    private function getPerformanceMetrics(array $dateRange): array
    {
        $sql = "SELECT
                    AVG(avg_response_time) as avg_response_time,
                    AVG(avg_job_duration) as avg_job_duration,
                    AVG(avg_urls_per_job) as avg_urls_per_job
                FROM crawler_metrics
                WHERE metric_date BETWEEN :start AND :end";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':start' => $dateRange['start'],
            ':end' => $dateRange['end'],
        ]);

        $result = $stmt->fetch();

        return [
            'avg_response_time_ms' => round(($result['avg_response_time'] ?? 0) * 1000, 2),
            'avg_job_duration_seconds' => round($result['avg_job_duration'] ?? 0, 2),
            'avg_urls_per_job' => round($result['avg_urls_per_job'] ?? 0, 2),
        ];
    }

    /**
     * Get error summary for period
     */
    private function getErrorSummary(array $dateRange): array
    {
        $sql = "SELECT SUM(total_errors) as total
                FROM crawler_metrics
                WHERE metric_date BETWEEN :start AND :end";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':start' => $dateRange['start'],
            ':end' => $dateRange['end'],
        ]);

        $totalErrors = (int)$stmt->fetchColumn();

        // Most common HTTP errors
        $stmt = $this->pdo->query("
            SELECT last_http_code as code, COUNT(*) as count
            FROM crawler_domain_stats
            WHERE last_http_code >= 400
            GROUP BY last_http_code
            ORDER BY count DESC
            LIMIT 5
        ");
        $topErrors = $stmt->fetchAll();

        return [
            'total' => $totalErrors,
            'top_http_errors' => $topErrors,
        ];
    }
}
