<?php

declare(strict_types=1);

final class MyronDashboardTipsFeedRepository
{
    public function __construct(private readonly PDO $pdo)
    {
    }

    /**
     * @return array<string, string>
     */
    public function cacheState(string $feedUrl): array
    {
        $statement = $this->pdo->prepare('SELECT * FROM dashboard_tip_feed_cache WHERE id = 1 AND feed_url = :feed_url LIMIT 1');
        $statement->execute([':feed_url' => $feedUrl]);
        $row = $statement->fetch(PDO::FETCH_ASSOC);
        if (!is_array($row)) {
            return [];
        }

        return array_map('strval', $row);
    }

    public function saveCacheState(string $feedUrl, string $etag, string $lastModified): void
    {
        $now = myronNow();
        $statement = $this->pdo->prepare(
            'REPLACE INTO dashboard_tip_feed_cache (
                id, feed_url, etag, last_modified, last_checked_at, updated_at
             ) VALUES (
                1, :feed_url, :etag, :last_modified, :last_checked_at, :updated_at
             )'
        );
        $statement->execute([
            ':feed_url' => $feedUrl,
            ':etag' => $etag,
            ':last_modified' => $lastModified,
            ':last_checked_at' => $now,
            ':updated_at' => $now,
        ]);
    }

    /**
     * @param list<array{title:string,url:string,summary:string,categories:list<string>,publishedAt:string}> $items
     */
    public function replaceItems(array $items): void
    {
        $this->pdo->exec('DELETE FROM dashboard_tips');
        $statement = $this->pdo->prepare(
            'INSERT INTO dashboard_tips (
                id, title, url, summary, categories_json, published_at, sort_order, updated_at
             ) VALUES (
                :id, :title, :url, :summary, :categories_json, :published_at, :sort_order, :updated_at
             )'
        );

        $now = myronNow();
        foreach (array_values($items) as $index => $item) {
            $statement->execute([
                ':id' => myronGenerateId('tip'),
                ':title' => substr($item['title'], 0, 255),
                ':url' => substr($item['url'], 0, 500),
                ':summary' => $item['summary'],
                ':categories_json' => json_encode(array_values($item['categories']), JSON_UNESCAPED_SLASHES),
                ':published_at' => substr($item['publishedAt'], 0, 40),
                ':sort_order' => $index,
                ':updated_at' => $now,
            ]);
        }
    }

    /**
     * @return list<array<string, mixed>>
     */
    public function listTipsForContext(string $context, int $limit = 3): array
    {
        $context = strtolower(trim($context));
        $statement = $this->pdo->prepare(
            'SELECT * FROM dashboard_tips ORDER BY sort_order ASC, published_at DESC, title ASC LIMIT 50'
        );
        $statement->execute();

        $tips = [];
        foreach ($statement->fetchAll(PDO::FETCH_ASSOC) ?: [] as $row) {
            $categories = json_decode((string) ($row['categories_json'] ?? '[]'), true);
            $categories = is_array($categories) ? array_values(array_map('strval', $categories)) : [];
            $normalized = array_map(static fn (string $category): string => strtolower(trim($category)), $categories);
            $contextCategories = array_values(array_filter($normalized, static fn (string $category): bool => str_starts_with($category, 'context:')));
            $broadcast = $contextCategories === [];
            $matches = $context !== '' && in_array('context:' . $context, $normalized, true);
            if (!$broadcast && !$matches) {
                continue;
            }

            $tips[] = [
                'title' => (string) ($row['title'] ?? ''),
                'url' => (string) ($row['url'] ?? ''),
                'summary' => (string) ($row['summary'] ?? ''),
                'categories' => $categories,
                'publishedAt' => (string) ($row['published_at'] ?? ''),
                'isSecurity' => in_array('type:security', $normalized, true),
            ];
            if (count($tips) >= max(1, $limit)) {
                break;
            }
        }

        return $tips;
    }
}
