<?php

declare(strict_types=1);

namespace SeoScope\Repositories;

use PDO;

/**
 * Persisted tracked GSC landing page URLs per user and Search Console property.
 *
 * Limits and uniqueness follow the same policy as {@see TrackedQueryRepository} (keywords).
 */
final class TrackedPageRepository
{
    /** Same max length as `gsc_daily_page.page`. */
    public const MAX_PAGE_LENGTH = 2048;

    /** Maximum tracked pages per user and property (aligned with tracked queries). */
    public const MAX_TRACKED_PER_PROPERTY = 500;

    public function __construct(
        private PDO $pdo,
        private string $tablePrefix
    ) {}

    private function table(): string
    {
        return '`' . str_replace('`', '``', $this->tablePrefix . 'tracked_pages') . '`';
    }

    /**
     * @param null|int $categoryIdFilter null = all; 0 = uncategorized; >0 = category id
     * @return list<string> Distinct full URLs, ordered lexicographically by `page`.
     */
    public function listPages(int $userId, int $propertyId, ?int $categoryIdFilter = null): array
    {
        if ($userId < 1 || $propertyId < 1) {
            return [];
        }

        $t = $this->table();
        $sql = "SELECT `page` AS p FROM {$t}
             WHERE user_id = ? AND property_id = ?";
        $params = [$userId, $propertyId];
        if ($categoryIdFilter === 0) {
            $sql .= ' AND category_id IS NULL';
        } elseif ($categoryIdFilter !== null && $categoryIdFilter > 0) {
            $sql .= ' AND category_id = ?';
            $params[] = $categoryIdFilter;
        }
        $sql .= ' ORDER BY `page` ASC';
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        $out = [];
        foreach ($rows as $r) {
            if (!is_array($r)) {
                continue;
            }
            $p = (string) ($r['p'] ?? '');
            if ($p !== '') {
                $out[] = $p;
            }
        }

        return $out;
    }

    public function isTracked(int $userId, int $propertyId, string $page): bool
    {
        if ($userId < 1 || $propertyId < 1) {
            return false;
        }
        $page = trim($page);
        if ($page === '') {
            return false;
        }

        $t = $this->table();
        $stmt = $this->pdo->prepare(
            "SELECT 1 FROM {$t} WHERE user_id = ? AND property_id = ? AND `page` = ? LIMIT 1"
        );
        $stmt->execute([$userId, $propertyId, $page]);

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

    public function countForProperty(int $userId, int $propertyId): int
    {
        if ($userId < 1 || $propertyId < 1) {
            return 0;
        }
        $t = $this->table();
        $stmt = $this->pdo->prepare(
            "SELECT COUNT(*) FROM {$t} WHERE user_id = ? AND property_id = ?"
        );
        $stmt->execute([$userId, $propertyId]);

        return (int) ($stmt->fetchColumn() ?: 0);
    }

    /**
     * @return 'invalid'|'inserted'|'duplicate'|'at_limit'
     */
    public function add(int $userId, int $propertyId, string $page): string
    {
        if ($userId < 1 || $propertyId < 1) {
            return 'invalid';
        }
        $page = trim($page);
        if ($page === '' || mb_strlen($page) > self::MAX_PAGE_LENGTH) {
            return 'invalid';
        }

        if (!$this->isTracked($userId, $propertyId, $page)) {
            if ($this->countForProperty($userId, $propertyId) >= self::MAX_TRACKED_PER_PROPERTY) {
                return 'at_limit';
            }
        }

        $t = $this->table();
        $now = date('Y-m-d H:i:s');
        $stmt = $this->pdo->prepare(
            "INSERT IGNORE INTO {$t} (user_id, property_id, `page`, created_at)
             VALUES (?, ?, ?, ?)"
        );
        $stmt->execute([$userId, $propertyId, $page, $now]);

        return $stmt->rowCount() > 0 ? 'inserted' : 'duplicate';
    }

    /**
     * @return bool True if a row was deleted.
     */
    public function remove(int $userId, int $propertyId, string $page): bool
    {
        if ($userId < 1 || $propertyId < 1) {
            return false;
        }
        $page = trim($page);
        if ($page === '') {
            return false;
        }

        $t = $this->table();
        $stmt = $this->pdo->prepare(
            "DELETE FROM {$t} WHERE user_id = ? AND property_id = ? AND `page` = ?"
        );
        $stmt->execute([$userId, $propertyId, $page]);

        return $stmt->rowCount() > 0;
    }

    /**
     * @return array<string, int|null> page URL => category id or null
     */
    public function pageCategoryMap(int $userId, int $propertyId): array
    {
        if ($userId < 1 || $propertyId < 1) {
            return [];
        }
        $t = $this->table();
        $stmt = $this->pdo->prepare(
            "SELECT `page` AS p, category_id FROM {$t}
             WHERE user_id = ? AND property_id = ?"
        );
        $stmt->execute([$userId, $propertyId]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        $out = [];
        foreach ($rows as $r) {
            if (!is_array($r)) {
                continue;
            }
            $p = (string) ($r['p'] ?? '');
            if ($p === '') {
                continue;
            }
            $cid = $r['category_id'] ?? null;
            $out[$p] = $cid === null || $cid === '' ? null : (int) $cid;
        }

        return $out;
    }

    public function setCategory(int $userId, int $propertyId, string $page, ?int $categoryId): bool
    {
        if ($userId < 1 || $propertyId < 1) {
            return false;
        }
        $page = trim($page);
        if ($page === '' || mb_strlen($page) > self::MAX_PAGE_LENGTH) {
            return false;
        }
        $t = $this->table();
        $stmt = $this->pdo->prepare(
            "UPDATE {$t} SET category_id = ?
             WHERE user_id = ? AND property_id = ? AND `page` = ?"
        );
        $stmt->execute([$categoryId, $userId, $propertyId, $page]);

        return $stmt->rowCount() > 0;
    }
}
