<?php

declare(strict_types=1);

namespace SeoScope\Repositories;

use PDO;

/**
 * Persisted “tracked” GSC queries (keywords) per user and Search Console property.
 */
final class TrackedQueryRepository
{
    /** Same max length as `gsc_daily_query.query`. */
    public const MAX_QUERY_LENGTH = 1024;

    /** Maximum tracked queries per user and property (abuse / UX guard). */
    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_queries') . '`';
    }

    /**
     * @param null|int $categoryIdFilter null = all tracked; 0 = uncategorized only; >0 = that category id
     * @return list<string> Distinct queries, ordered alphabetically.
     */
    public function listQueries(int $userId, int $propertyId, ?int $categoryIdFilter = null): array
    {
        if ($userId < 1 || $propertyId < 1) {
            return [];
        }

        $t = $this->table();
        $sql = "SELECT `query` AS q 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 `query` 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;
            }
            $q = (string) ($r['q'] ?? '');
            if ($q !== '') {
                $out[] = $q;
            }
        }

        return $out;
    }

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

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

        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 $query): string
    {
        if ($userId < 1 || $propertyId < 1) {
            return 'invalid';
        }
        $query = trim($query);
        if ($query === '' || mb_strlen($query) > self::MAX_QUERY_LENGTH) {
            return 'invalid';
        }

        if (!$this->isTracked($userId, $propertyId, $query)) {
            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, `query`, created_at)
             VALUES (?, ?, ?, ?)"
        );
        $stmt->execute([$userId, $propertyId, $query, $now]);

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

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

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

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

    /**
     * @return array<string, int|null> query string => category id or null (uncategorized)
     */
    public function queryCategoryMap(int $userId, int $propertyId): array
    {
        if ($userId < 1 || $propertyId < 1) {
            return [];
        }
        $t = $this->table();
        $stmt = $this->pdo->prepare(
            "SELECT `query` AS q, 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;
            }
            $q = (string) ($r['q'] ?? '');
            if ($q === '') {
                continue;
            }
            $cid = $r['category_id'] ?? null;
            $out[$q] = $cid === null || $cid === '' ? null : (int) $cid;
        }

        return $out;
    }

    /**
     * Set or clear the category for one tracked query row.
     *
     * @return bool True if a row was updated
     */
    public function setCategory(int $userId, int $propertyId, string $query, ?int $categoryId): bool
    {
        if ($userId < 1 || $propertyId < 1) {
            return false;
        }
        $query = trim($query);
        if ($query === '' || mb_strlen($query) > self::MAX_QUERY_LENGTH) {
            return false;
        }
        $t = $this->table();
        $stmt = $this->pdo->prepare(
            "UPDATE {$t} SET category_id = ?
             WHERE user_id = ? AND property_id = ? AND `query` = ?"
        );
        $stmt->execute([$categoryId, $userId, $propertyId, $query]);

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