<?php

declare(strict_types=1);

namespace SeoScope\Repositories;

use PDO;

/**
 * User-defined labels per GSC property; shared by tracked keywords and tracked pages.
 */
final class TrackedCategoryRepository
{
    public const MAX_NAME_LENGTH = 128;

    public const MAX_CATEGORIES_PER_PROPERTY = 50;

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

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

    /**
     * @return list<array{id:int,name:string}>
     */
    public function listForProperty(int $userId, int $propertyId): array
    {
        if ($userId < 1 || $propertyId < 1) {
            return [];
        }
        $t = $this->table();
        $stmt = $this->pdo->prepare(
            "SELECT id, name FROM {$t}
             WHERE user_id = ? AND property_id = ?
             ORDER BY name ASC"
        );
        $stmt->execute([$userId, $propertyId]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        $out = [];
        foreach ($rows as $r) {
            if (!is_array($r)) {
                continue;
            }
            $id = (int) ($r['id'] ?? 0);
            $name = trim((string) ($r['name'] ?? ''));
            if ($id > 0 && $name !== '') {
                $out[] = ['id' => $id, 'name' => $name];
            }
        }

        return $out;
    }

    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 null|array{id:int,user_id:int,property_id:int,name:string}
     */
    public function findOwned(int $userId, int $propertyId, int $categoryId): ?array
    {
        if ($userId < 1 || $propertyId < 1 || $categoryId < 1) {
            return null;
        }
        $t = $this->table();
        $stmt = $this->pdo->prepare(
            "SELECT id, user_id, property_id, name FROM {$t}
             WHERE id = ? AND user_id = ? AND property_id = ?
             LIMIT 1"
        );
        $stmt->execute([$categoryId, $userId, $propertyId]);
        $r = $stmt->fetch(PDO::FETCH_ASSOC);

        return is_array($r) ? [
            'id' => (int) ($r['id'] ?? 0),
            'user_id' => (int) ($r['user_id'] ?? 0),
            'property_id' => (int) ($r['property_id'] ?? 0),
            'name' => (string) ($r['name'] ?? ''),
        ] : null;
    }

    /**
     * @return 'invalid'|'duplicate'|'at_limit'|'inserted'
     */
    public function create(int $userId, int $propertyId, string $name): string
    {
        if ($userId < 1 || $propertyId < 1) {
            return 'invalid';
        }
        $name = trim($name);
        if ($name === '' || mb_strlen($name) > self::MAX_NAME_LENGTH) {
            return 'invalid';
        }
        if ($this->countForProperty($userId, $propertyId) >= self::MAX_CATEGORIES_PER_PROPERTY) {
            return 'at_limit';
        }
        $t = $this->table();
        $now = date('Y-m-d H:i:s');
        $stmt = $this->pdo->prepare(
            "INSERT INTO {$t} (user_id, property_id, name, created_at) VALUES (?, ?, ?, ?)"
        );
        try {
            $stmt->execute([$userId, $propertyId, $name, $now]);
        } catch (\Throwable) {
            return 'duplicate';
        }

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

    /**
     * @return 'invalid'|'not_found'|'duplicate'|'unchanged'|'ok'
     */
    public function rename(int $userId, int $propertyId, int $categoryId, string $newName): string
    {
        if ($userId < 1 || $propertyId < 1 || $categoryId < 1) {
            return 'invalid';
        }
        $newName = trim($newName);
        if ($newName === '' || mb_strlen($newName) > self::MAX_NAME_LENGTH) {
            return 'invalid';
        }
        $owned = $this->findOwned($userId, $propertyId, $categoryId);
        if ($owned === null) {
            return 'not_found';
        }
        if ($owned['name'] === $newName) {
            return 'unchanged';
        }
        $t = $this->table();
        $stmt = $this->pdo->prepare(
            "UPDATE {$t} SET name = ? WHERE id = ? AND user_id = ? AND property_id = ?"
        );
        try {
            $stmt->execute([$newName, $categoryId, $userId, $propertyId]);
        } catch (\Throwable) {
            return 'duplicate';
        }

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

    public function delete(int $userId, int $propertyId, int $categoryId): bool
    {
        if ($userId < 1 || $propertyId < 1 || $categoryId < 1) {
            return false;
        }
        $t = $this->table();
        $stmt = $this->pdo->prepare(
            "DELETE FROM {$t} WHERE id = ? AND user_id = ? AND property_id = ?"
        );
        $stmt->execute([$categoryId, $userId, $propertyId]);

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