<?php

require_once __DIR__ . '/db.php';

function sparrow_analytics_request_page_url(): string
{
    return substr($_SERVER['REQUEST_URI'] ?? '', 0, 255);
}

function sparrow_analytics_request_referrer(): string
{
    return substr($_SERVER['HTTP_REFERER'] ?? '', 0, 255);
}

function sparrow_analytics_ip_hash(): string
{
    $ip = $_SERVER['HTTP_X_FORWARDED_FOR'] ?? $_SERVER['REMOTE_ADDR'] ?? '';

    if (str_contains($ip, ',')) {
        $parts = explode(',', $ip);
        $ip = trim($parts[0]);
    }

    return $ip !== '' ? hash('sha256', $ip . '|sparrow-seed') : '';
}

function sparrow_record_analytics_event(
    string $event_type,
    string $entity_type = '',
    ?int $entity_id = null,
    string $entity_title = '',
    string $entity_category = '',
    string $page_url = '',
    array $metadata = []
): void {
    $pdo = sparrow_db();

    $event_type = trim($event_type);
    $entity_type = trim($entity_type);
    $entity_title = trim($entity_title);
    $entity_category = trim($entity_category);

    if ($event_type === '') {
        return;
    }

    $metadata_json = $metadata
        ? json_encode($metadata, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES)
        : null;

    $stmt = $pdo->prepare("
        INSERT INTO analytics_events (
            event_type,
            entity_type,
            entity_id,
            entity_title,
            entity_category,
            page_url,
            referrer,
            ip_hash,
            user_agent,
            metadata
        ) VALUES (
            :event_type,
            :entity_type,
            :entity_id,
            :entity_title,
            :entity_category,
            :page_url,
            :referrer,
            :ip_hash,
            :user_agent,
            :metadata
        )
    ");

    $stmt->execute([
        ':event_type' => substr($event_type, 0, 60),
        ':entity_type' => $entity_type !== '' ? substr($entity_type, 0, 60) : null,
        ':entity_id' => $entity_id,
        ':entity_title' => $entity_title !== '' ? substr($entity_title, 0, 180) : null,
        ':entity_category' => $entity_category !== '' ? substr($entity_category, 0, 80) : null,
        ':page_url' => substr($page_url ?: sparrow_analytics_request_page_url(), 0, 255),
        ':referrer' => sparrow_analytics_request_referrer(),
        ':ip_hash' => sparrow_analytics_ip_hash() ?: null,
        ':user_agent' => substr($_SERVER['HTTP_USER_AGENT'] ?? '', 0, 500),
        ':metadata' => $metadata_json,
    ]);
}

function sparrow_analytics_total_events(): int
{
    $pdo = sparrow_db();

    $stmt = $pdo->query("
        SELECT COUNT(*)
        FROM analytics_events
    ");

    return (int) $stmt->fetchColumn();
}

function sparrow_analytics_count_event_type(string $event_type): int
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        SELECT COUNT(*)
        FROM analytics_events
        WHERE event_type = :event_type
    ");

    $stmt->execute([
        ':event_type' => $event_type,
    ]);

    return (int) $stmt->fetchColumn();
}

function sparrow_analytics_most_opened_gallery_item(): ?array
{
    $pdo = sparrow_db();

    $stmt = $pdo->query("
        SELECT
            entity_id,
            entity_title,
            entity_category,
            COUNT(*) AS total
        FROM analytics_events
        WHERE event_type = 'gallery_lightbox_open'
        AND entity_type = 'gallery_item'
        GROUP BY entity_id, entity_title, entity_category
        ORDER BY total DESC, entity_title ASC
        LIMIT 1
    ");

    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    return $row ?: null;
}

function sparrow_analytics_most_viewed_blog_post(): ?array
{
    $pdo = sparrow_db();

    $stmt = $pdo->query("
        SELECT
            entity_id,
            entity_title,
            COUNT(*) AS total
        FROM analytics_events
        WHERE event_type = 'blog_post_view'
        AND entity_type = 'marketing_post'
        GROUP BY entity_id, entity_title
        ORDER BY total DESC, entity_title ASC
        LIMIT 1
    ");

    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    return $row ?: null;
}

function sparrow_analytics_top_category(): ?array
{
    $pdo = sparrow_db();

    $stmt = $pdo->query("
        SELECT
            entity_category,
            COUNT(*) AS total
        FROM analytics_events
        WHERE entity_category IS NOT NULL
        AND entity_category <> ''
        GROUP BY entity_category
        ORDER BY total DESC, entity_category ASC
        LIMIT 1
    ");

    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    return $row ?: null;
}

function sparrow_analytics_recent_events(int $limit = 25): array
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        SELECT *
        FROM analytics_events
        ORDER BY created_at DESC, id DESC
        LIMIT :limit
    ");

    $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
    $stmt->execute();

    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}