<?php

namespace Core\App\Models;

use PDO;
use Exception;

/**
 * ============================================================
 * PageviewsModel
 * ------------------------------------------------------------
 * - تسجيل الزيارات
 * - إحصائيات Dashboard
 * - تصفية الملفات (images / api)
 * - معالجة فروقات التوقيت
 * ============================================================
 */
class PageviewsModel
{
    /**
     * اتصال قاعدة البيانات
     */
    protected PDO $db;

    /**
     * امتدادات الملفات التي يجب تجاهلها
     */
    protected array $ignoredExtensions = [
        '.jpg', '.jpeg', '.png', '.webp', '.gif', '.svg',
        '.css', '.js', '.ico', '.woff', '.woff2', '.ttf'
    ];

    /**
     * مسارات يجب تجاهلها
     */
    protected array $ignoredPaths = [
        '/api/',
        '/admin/',
    ];

    /**
     * ============================================================
     * Constructor
     * ============================================================
     */
    public function __construct()
    {
        $this->db = db();
        // Table is created by the installer SQL schema.
        // DDL removed from constructor to avoid per-request schema lock overhead.
    }

    /**
     * ============================================================
     * فحص هل الرابط قابل للتسجيل
     * ============================================================
     */
protected function isTrackableUrl(string $url): bool
{
    // ✅ خذ فقط المسار بدون Query string
    $path = parse_url($url, PHP_URL_PATH) ?? '';
    $path = strtolower($path);

    if ($path === '') {
        return false;
    }

    // ✅ تجاهل مسارات محددة (بالضبط)
    if ($path === '/robots.txt' || $path === '/sitemap.xml') {
        return false;
    }

    // ✅ تجاهل مسارات تبدأ بـ
    foreach ($this->ignoredPaths as $p) {
        $p = strtolower($p);
        if (str_starts_with($path, $p)) {
            return false;
        }
    }

    // ✅ تجاهل cron
    if (str_starts_with($path, '/cron/')) {
        return false;
    }

    // ✅ تجاهل امتدادات الملفات
    foreach ($this->ignoredExtensions as $ext) {
        if (str_ends_with($path, $ext)) {
            return false;
        }
    }

    return true;
}

    /**
     * ============================================================
     * إضافة زيارة جديدة
     * ============================================================
     */
    public function addPageview(
        string $sessionId,
        string $ip,
        string $agent,
        string $url
    ): bool {
        try {
            if (!$this->isTrackableUrl($url)) {
                return false;
            }

            $stmt = $this->db->prepare("
                INSERT INTO page_views
                (session_id, ip, user_agent, page_url, visited_at)
                VALUES
                (:session_id, :ip, :user_agent, :page_url, NOW())
            ");

            return $stmt->execute([
                ':session_id' => $sessionId,
                ':ip'         => $ip,
                ':user_agent' => $agent,
                ':page_url'   => $url,
            ]);
        } catch (Exception $e) {
            error_log('PageviewsModel::addPageview ' . $e->getMessage());
            return false;
        }
    }

    /**
     * ============================================================
     * زيارات آخر 24 ساعة
     * ============================================================
     */
    public function countToday(): int
    {
        try {
            $stmt = $this->db->query("
                SELECT COUNT(*)
                FROM page_views
                WHERE visited_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
            ");
            return (int) $stmt->fetchColumn();
        } catch (Exception $e) {
            return 0;
        }
    }

    /**
     * ============================================================
     * زوار فريدون آخر 24 ساعة
     * ============================================================
     */
    public function countUniqueToday(): int
    {
        try {
            $stmt = $this->db->query("
                SELECT COUNT(DISTINCT session_id)
                FROM page_views
                WHERE visited_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
            ");
            return (int) $stmt->fetchColumn();
        } catch (Exception $e) {
            return 0;
        }
    }

    /**
     * ============================================================
     * المستخدمون النشطون الآن (آخر 5 دقائق)
     * ============================================================
     */
    public function countOnline(): int
    {
        try {
            $stmt = $this->db->query("
                SELECT COUNT(DISTINCT session_id)
                FROM page_views
                WHERE visited_at >= DATE_SUB(NOW(), INTERVAL 5 MINUTE)
            ");
            return (int) $stmt->fetchColumn();
        } catch (Exception $e) {
            return 0;
        }
    }

    /**
     * ============================================================
     * إحصائيات آخر 7 أيام
     * ============================================================
     */
    public function getLast7Days(): array
    {
        try {
            $stmt = $this->db->query("
                SELECT
                    DATE(visited_at) AS day,
                    COUNT(*) AS total
                FROM page_views
                WHERE visited_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
                GROUP BY DATE(visited_at)
                ORDER BY day ASC
            ");
            return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (Exception $e) {
            return [];
        }
    }

    /**
     * ============================================================
     * أفضل الصفحات زيارة
     * ============================================================
     */
    public function getTopPages(int $limit = 5): array
    {
        try {
            $stmt = $this->db->prepare("
                SELECT
                    pv.clean_url AS page_url,
                    COUNT(*) AS views,
                    p.title AS product_name
                FROM (
                    SELECT SUBSTRING_INDEX(page_url, '?', 1) AS clean_url
                    FROM page_views
                    WHERE visited_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
                      AND SUBSTRING_INDEX(page_url, '?', 1) REGEXP '^/product/[^/]+/?$'
                ) pv
                LEFT JOIN products p
                    ON p.slug = TRIM(BOTH '/' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(pv.clean_url, '/product/', -1), '?', 1))
                    OR p.id   = CAST(TRIM(BOTH '/' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(pv.clean_url, '/product/', -1), '?', 1)) AS UNSIGNED)
                GROUP BY pv.clean_url, p.title
                ORDER BY views DESC
                LIMIT :limit
            ");
            $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
            $stmt->execute();

            return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (Exception $e) {
            return [];
        }
    }

    /**
     * ============================================================
     * إحصائيات المتصفحات
     * ============================================================
     */
    public function getBrowsersStats(): array
    {
        try {
            $stmt = $this->db->query("
                SELECT user_agent
                FROM page_views
                WHERE visited_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
            ");
            $rows = $stmt->fetchAll(PDO::FETCH_COLUMN);

            $browsers = [
                'Chrome'  => 0,
                'Firefox' => 0,
                'Safari'  => 0,
                'Edge'    => 0,
                'Other'   => 0,
            ];

            foreach ($rows as $ua) {
                $ua = strtolower($ua ?? '');
                if (str_contains($ua, 'chrome') && !str_contains($ua, 'edge')) {
                    $browsers['Chrome']++;
                } elseif (str_contains($ua, 'firefox')) {
                    $browsers['Firefox']++;
                } elseif (str_contains($ua, 'safari')) {
                    $browsers['Safari']++;
                } elseif (str_contains($ua, 'edge')) {
                    $browsers['Edge']++;
                } else {
                    $browsers['Other']++;
                }
            }

            return $browsers;
        } catch (Exception $e) {
            return [];
        }
    }

    /**
     * ============================================================
     * إحصائيات الأجهزة
     * ============================================================
     */
    public function getDevicesStats(): array
    {
        try {
            $stmt = $this->db->query("
                SELECT user_agent
                FROM page_views
                WHERE visited_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
            ");
            $rows = $stmt->fetchAll(PDO::FETCH_COLUMN);

            $devices = [
                'Mobile'  => 0,
                'Desktop' => 0,
            ];

            foreach ($rows as $ua) {
                $ua = strtolower($ua ?? '');
                if (
                    str_contains($ua, 'mobile') ||
                    str_contains($ua, 'android') ||
                    str_contains($ua, 'iphone')
                ) {
                    $devices['Mobile']++;
                } else {
                    $devices['Desktop']++;
                }
            }

            return $devices;
        } catch (Exception $e) {
            return [];
        }
    }

    /**
     * ============================================================
     * معدل التفاعل (صفحات لكل زائر)
     * ============================================================
     */
    public function getEngagementToday(): float
    {
        $views  = $this->countToday();
        $unique = $this->countUniqueToday();
        return $unique > 0 ? round($views / $unique, 2) : 0.0;
    }

    /**
     * ============================================================
     * صفحات ضعيفة الأداء
     * ============================================================
     */
    public function getWeakPages(int $limit = 5): array
    {
        try {
            $stmt = $this->db->prepare("
                SELECT page_url, COUNT(*) AS views
                FROM page_views
                WHERE visited_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
                GROUP BY page_url
                HAVING views >= 10
                ORDER BY views DESC
                LIMIT :limit
            ");
            $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
            $stmt->execute();

            return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (Exception $e) {
            return [];
        }
    }

    /**
     * ============================================================
     * مقارنة اليوم مع أمس (نسبة مئوية)
     * ============================================================
     */
    public function compareWithYesterday(): int
    {
        try {
            $stmt = $this->db->query("
                SELECT
                    SUM(CASE WHEN visited_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR) THEN 1 ELSE 0 END) AS today,
                    SUM(CASE
                        WHEN visited_at BETWEEN DATE_SUB(NOW(), INTERVAL 48 HOUR)
                        AND DATE_SUB(NOW(), INTERVAL 24 HOUR)
                        THEN 1 ELSE 0 END) AS yesterday
                FROM page_views
            ");
            $row = $stmt->fetch(PDO::FETCH_ASSOC);

            if (empty($row['yesterday'])) {
                return 0;
            }

            return (int) round(
                (($row['today'] - $row['yesterday']) / $row['yesterday']) * 100
            );
        } catch (Exception $e) {
            return 0;
        }
    }

    /**
     * ============================================================
     * زوار عائدون خلال آخر 24 ساعة
     * ============================================================
     */
    public function returningVisitorsToday(): int
    {
        try {
            $stmt = $this->db->query("
                SELECT COUNT(DISTINCT v1.session_id)
                FROM page_views v1
                WHERE v1.visited_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
                AND EXISTS (
                    SELECT 1
                    FROM page_views v2
                    WHERE v2.session_id = v1.session_id
                    AND v2.visited_at < DATE_SUB(NOW(), INTERVAL 24 HOUR)
                )
            ");
            return (int) $stmt->fetchColumn();
        } catch (Exception $e) {
            return 0;
        }
    }
}