<?php
declare(strict_types=1);

namespace App\Admin;

use App\Questions\AnswerService;
use App\Questions\QuestionService;
use App\Support\TranslatableException;
use App\Support\Hooks;
use App\Support\CoreHooks;
use App\Security\HtmlSanitizer;
use App\Settings\SettingsRepository;
use App\Slug\Slugger;
use App\Widgets\WidgetRegistry;
use InvalidArgumentException;
use PDO;
use RuntimeException;
use Throwable;

final class AdminRepository
{
    private readonly Slugger $slugger;

    public function __construct(
        private readonly PDO $pdo,
    ) {
        $this->slugger = new Slugger();
    }

    /** @return array<string, int> */
    public function stats(): array
    {
        $stats = [];
        foreach (['users', 'questions', 'answers', 'topics', 'categories', 'tags', 'reports'] as $table) {
            $stats[$table] = (int) $this->pdo->query('SELECT COUNT(*) FROM ' . $table)->fetchColumn();
        }

        foreach (['pending_questions' => "SELECT COUNT(*) FROM questions WHERE status = 'pending'", 'open_reports' => "SELECT COUNT(*) FROM reports WHERE status = 'open'", 'published_questions' => "SELECT COUNT(*) FROM questions WHERE status = 'published'"] as $key => $sql) {
            $stats[$key] = (int) $this->pdo->query($sql)->fetchColumn();
        }

        return $stats;
    }

    public function countRows(string $table, ?string $status = null, string $search = ''): int
    {
        $allowed = ['questions', 'topics', 'answers', 'comments', 'categories', 'tags', 'users', 'reports', 'contact_messages', 'notifications', 'audit_log', 'moderation_actions', 'widgets', 'layouts', 'layout_sections', 'languages'];
        if (!in_array($table, $allowed, true)) throw new InvalidArgumentException('Invalid count table.');
        $where = '';
        $params = [];
        if ($status !== null && in_array($table, ['questions', 'reports', 'contact_messages', 'users'], true)) { $where = ' WHERE status=:status'; $params['status'] = $status; }
        if ($search !== '' && in_array($table, ['questions', 'users'], true)) {
            $column = $table === 'questions' ? "(COALESCE(title_normalized,title) LIKE :search_title ESCAPE '|' OR body LIKE :search_body ESCAPE '|')" : "(COALESCE(name,'') LIKE :search_name ESCAPE '|' OR email LIKE :search_email ESCAPE '|')";
            $where .= $where === '' ? ' WHERE ' : ' AND ';
            $where .= $column;
            $needle = '%' . str_replace(['|', '%', '_'], ['||', '|%', '|_'], $search) . '%';
            if ($table === 'questions') { $params['search_title'] = $needle; $params['search_body'] = $needle; }
            else { $params['search_name'] = $needle; $params['search_email'] = $needle; }
        }
        $statement = $this->pdo->prepare('SELECT COUNT(*) FROM ' . $table . $where); $statement->execute($params);
        return (int) $statement->fetchColumn();
    }

    public function countNotificationsForUser(int $userId, ?string $status = null): int
    {
        $where = 'user_id=:user_id';
        $params = ['user_id' => $userId];
        if ($status === 'unread') $where .= ' AND read_at IS NULL';
        if ($status === 'read') $where .= ' AND read_at IS NOT NULL';
        $statement = $this->pdo->prepare('SELECT COUNT(*) FROM notifications WHERE ' . $where);
        $statement->execute($params);
        return (int) $statement->fetchColumn();
    }

    /** @return list<array<string, mixed>> */
    public function questions(?string $status = null, string $search = ''): array
    {
        return $this->paginatedQuestions([
            'status' => $status,
            'search' => $search,
            'sort' => 'newest',
        ], 1, 200);
    }

    /** @param array<string, mixed> $filters */
    public function countQuestions(array $filters = []): int
    {
        [$where, $params] = $this->questionFilterSql($filters);
        $statement = $this->pdo->prepare('SELECT COUNT(*) FROM questions q ' . $where);
        $statement->execute($params);
        return (int) $statement->fetchColumn();
    }

    /** @param array<string, mixed> $filters @return list<array<string, mixed>> */
    public function paginatedQuestions(array $filters = [], int $page = 1, int $perPage = 25): array
    {
        $page = max(1, $page);
        $perPage = max(1, min(100, $perPage));
        [$where, $params] = $this->questionFilterSql($filters);
        $sort = $this->questionSort((string) ($filters['sort'] ?? 'newest'));
        $statement = $this->pdo->prepare(
            'SELECT q.id, q.title, q.body, q.slug, q.status, q.content_locale, q.category_id,
                    q.answers_count, q.votes_count, q.views_count, q.created_at, q.published_at,
                    q.rejected_reason, q.duplicate_of_id, q.meta_description, q.meta_title,
                    q.canonical_url, q.seo_index,
                    COALESCE(u.name, u.email, \'Anonymous\') AS author_name,
                    COALESCE(c.name, \'\') AS category_name
             FROM questions q
             LEFT JOIN users u ON u.id = q.user_id
             LEFT JOIN categories c ON c.id = q.category_id
             ' . $where . '
             ORDER BY ' . $sort . '
             LIMIT :limit OFFSET :offset'
        );
        foreach ($params as $key => $value) $statement->bindValue(':' . $key, $value);
        $statement->bindValue(':limit', $perPage, PDO::PARAM_INT);
        $statement->bindValue(':offset', ($page - 1) * $perPage, PDO::PARAM_INT);
        $statement->execute();
        return $statement->fetchAll(PDO::FETCH_ASSOC);
    }

    /** @param array<string, mixed> $filters @return array{0:string,1:array<string,mixed>} */
    private function questionFilterSql(array $filters): array
    {
        $clauses = [];
        $params = [];
        $status = (string) ($filters['status'] ?? '');
        if (in_array($status, ['pending', 'published', 'rejected', 'closed', 'duplicate'], true)) {
            $clauses[] = 'q.status = :question_status';
            $params['question_status'] = $status;
        }
        $search = trim((string) ($filters['search'] ?? ''));
        if ($search !== '') {
            $clauses[] = "(COALESCE(q.title_normalized,q.title) LIKE :question_search_title ESCAPE '|' OR COALESCE(q.body_normalized,q.body) LIKE :question_search_body ESCAPE '|')";
            $needle = '%' . str_replace(['|', '%', '_'], ['||', '|%', '|_'], $search) . '%';
            $params['question_search_title'] = $needle;
            $params['question_search_body'] = $needle;
        }
        $categoryId = max(0, (int) ($filters['category_id'] ?? 0));
        if ($categoryId > 0) {
            $clauses[] = 'q.category_id = :question_category_id';
            $params['question_category_id'] = $categoryId;
        }
        return [$clauses === [] ? '' : 'WHERE ' . implode(' AND ', $clauses), $params];
    }

    private function questionSort(string $sort): string
    {
        return match ($sort) {
            'oldest' => 'q.created_at ASC, q.id ASC',
            'answers' => 'q.answers_count DESC, q.created_at DESC, q.id DESC',
            'votes' => 'q.votes_count DESC, q.created_at DESC, q.id DESC',
            'views' => 'q.views_count DESC, q.created_at DESC, q.id DESC',
            'title' => 'q.title_normalized ASC, q.title ASC, q.id ASC',
            default => 'FIELD(q.status, \'pending\', \'published\', \'rejected\', \'closed\', \'duplicate\'), q.created_at DESC, q.id DESC',
        };
    }

    public function moderateQuestion(int $actorId, string $action, int $questionId, array $input = []): bool
    {
        $service = new QuestionService($this->pdo);

        return match ($action) {
            'publish' => $service->publish($actorId, $questionId, trim((string) ($input['slug'] ?? '')) ?: null, trim((string) ($input['meta_description'] ?? '')) ?: null),
            'reject' => $service->reject($actorId, $questionId, (string) ($input['reason'] ?? '')),
            'close' => $service->close($actorId, $questionId),
            'duplicate' => $service->markDuplicate($actorId, $questionId, (int) ($input['duplicate_of_id'] ?? 0)),
            'reopen' => $this->restoreQuestion($questionId),
            default => throw new TranslatableException('admin.moderation_action_invalid'),
        };
    }

    public function restoreQuestion(int $questionId): bool
    {
        $statement = $this->pdo->prepare("UPDATE questions SET status='pending', duplicate_of_id=NULL, rejected_reason=NULL, updated_at=NOW() WHERE id=:id AND status IN ('rejected','closed','duplicate')");
        $statement->execute(['id' => $questionId]);
        return $statement->rowCount() === 1;
    }

    public function deleteQuestion(int $questionId): bool
    {
        $transactionOwner = $this->beginTransactionScope('admin_delete_question');
        try {
            $answerIds = $this->pdo->prepare('SELECT id FROM answers WHERE question_id=:id');
            $answerIds->execute(['id' => $questionId]);
            foreach (array_map('intval', $answerIds->fetchAll(PDO::FETCH_COLUMN)) as $answerId) {
                foreach (['votes' => "votable_type='answer' AND votable_id=:id", 'comments' => "commentable_type='answer' AND commentable_id=:id", 'reports' => 'reportable_type=:type AND reportable_id=:id'] as $table => $condition) {
                    $statement = $this->pdo->prepare('DELETE FROM ' . $table . ' WHERE ' . $condition);
                    $statement->execute($table === 'reports' ? ['type' => 'answer', 'id' => $answerId] : ['id' => $answerId]);
                }
                $this->pdo->prepare('DELETE FROM answers WHERE id=:id')->execute(['id' => $answerId]);
            }
            foreach (['votes' => "votable_type='question' AND votable_id=:id", 'comments' => "commentable_type='question' AND commentable_id=:id", 'reports' => 'reportable_type=:type AND reportable_id=:id', 'question_tags' => 'question_id=:id', 'question_follows' => 'question_id=:id'] as $table => $condition) {
                $statement = $this->pdo->prepare('DELETE FROM ' . $table . ' WHERE ' . $condition);
                $statement->execute($table === 'reports' ? ['type' => 'question', 'id' => $questionId] : ['id' => $questionId]);
            }
            $statement = $this->pdo->prepare('DELETE FROM questions WHERE id=:id');
            $statement->execute(['id' => $questionId]);
            $this->finishTransactionScope('admin_delete_question', $transactionOwner);
            return $statement->rowCount() === 1;
        } catch (Throwable $throwable) { $this->failTransactionScope('admin_delete_question', $transactionOwner); throw $throwable; }
    }

    public function saveQuestionSeo(array $input): bool
    {
        $id = (int) ($input['question_id'] ?? 0);
        $metaTitle = trim((string) ($input['meta_title'] ?? ''));
        $metaDescription = trim((string) ($input['meta_description'] ?? ''));
        $canonicalUrl = trim((string) ($input['canonical_url'] ?? ''));
        $seoIndex = strtolower(trim((string) ($input['seo_index'] ?? 'inherit')));

        if ($id <= 0) {
            throw new TranslatableException('admin.question_required');
        }

        if (function_exists('mb_strlen')) {
            if (mb_strlen($metaTitle, 'UTF-8') > 255 || mb_strlen($metaDescription, 'UTF-8') > 191) {
                throw new TranslatableException('admin.validation_seo_too_long');
            }
        } elseif (strlen($metaTitle) > 255 || strlen($metaDescription) > 191) {
            throw new TranslatableException('admin.validation_seo_too_long');
        }

        if (strlen($canonicalUrl) > 500) {
            throw new TranslatableException('admin.validation_canonical_url_too_long');
        }

        if ($canonicalUrl !== '' && !str_starts_with($canonicalUrl, '/') && filter_var($canonicalUrl, FILTER_VALIDATE_URL) === false) {
            throw new TranslatableException('admin.validation_canonical_url_invalid');
        }

        if (!in_array($seoIndex, ['inherit', 'index', 'noindex'], true)) {
            throw new TranslatableException('admin.validation_seo_index_invalid');
        }

        $statement = $this->pdo->prepare(
            'UPDATE questions
             SET meta_title = :meta_title,
                 meta_description = :meta_description,
                 canonical_url = :canonical_url,
                 seo_index = :seo_index,
                 updated_at = NOW()
             WHERE id = :id'
        );
        $statement->execute([
            'meta_title' => $metaTitle !== '' ? $metaTitle : null,
            'meta_description' => $metaDescription !== '' ? $metaDescription : null,
            'canonical_url' => $canonicalUrl !== '' ? $canonicalUrl : null,
            'seo_index' => $seoIndex,
            'id' => $id,
        ]);

        return $statement->rowCount() >= 0;
    }

    /** @return list<array<string, mixed>> */
    public function answers(): array
    {
        $statement = $this->pdo->query(
            'SELECT a.id, a.question_id, a.body, a.status, a.votes_count, a.is_best, a.created_at,
                    q.title AS question_title, q.slug AS question_slug,
                    COALESCE(u.name, u.email, \'Anonymous\') AS author_name
             FROM answers a
             INNER JOIN questions q ON q.id = a.question_id
             LEFT JOIN users u ON u.id = a.user_id
             ORDER BY a.created_at DESC
             LIMIT 200'
        );

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

    public function setBestAnswer(int $answerId): bool
    {
        $transactionOwner = $this->beginTransactionScope('admin_set_best_answer');
        try {
            $answer = $this->lockRow('answers', $answerId);
            if ((string) ($answer['status'] ?? 'published') !== 'published') throw new TranslatableException('admin.answer_not_published');
            $questionId = (int) ($answer['question_id'] ?? 0);
            if ($questionId <= 0) {
                throw new TranslatableException('admin.answer_question_not_found');
            }

            $this->lockRow('questions', $questionId);
            $clear = $this->pdo->prepare('UPDATE answers SET is_best = 0, updated_at = NOW() WHERE question_id = :question_id');
            $clear->execute(['question_id' => $questionId]);
            $mark = $this->pdo->prepare('UPDATE answers SET is_best = 1, updated_at = NOW() WHERE id = :id');
            $mark->execute(['id' => $answerId]);
            $update = $this->pdo->prepare('UPDATE questions SET best_answer_id = :answer_id, updated_at = NOW() WHERE id = :id');
            $update->execute(['answer_id' => $answerId, 'id' => $questionId]);
            $this->finishTransactionScope('admin_set_best_answer', $transactionOwner);
            Hooks::emit(CoreHooks::ANSWER_ACCEPTED, [
                'answer_id' => $answerId,
                'question_id' => $questionId,
                'source' => 'admin',
            ]);

            return true;
        } catch (Throwable $throwable) {
            $this->failTransactionScope('admin_set_best_answer', $transactionOwner);
            throw $throwable;
        }
    }

    public function unsetBestAnswer(int $answerId): bool
    {
        $statement = $this->pdo->prepare('SELECT question_id FROM answers WHERE id=:id LIMIT 1'); $statement->execute(['id' => $answerId]); $questionId = (int) $statement->fetchColumn();
        if ($questionId <= 0) return false;
        $transactionOwner = $this->beginTransactionScope('admin_unset_best_answer');
        try {
            $this->pdo->prepare('UPDATE answers SET is_best=0, updated_at=NOW() WHERE id=:id')->execute(['id' => $answerId]);
            $this->pdo->prepare('UPDATE questions SET best_answer_id=NULL, updated_at=NOW() WHERE id=:id AND best_answer_id=:answer_id')->execute(['id' => $questionId, 'answer_id' => $answerId]);
            $this->finishTransactionScope('admin_unset_best_answer', $transactionOwner); return true;
        } catch (Throwable $throwable) { $this->failTransactionScope('admin_unset_best_answer', $transactionOwner); throw $throwable; }
    }

    public function setAnswerStatus(int $answerId, string $status): bool
    {
        if (!in_array($status, ['published','pending'], true)) throw new TranslatableException('admin.answer_status_invalid');
        $transactionOwner = $this->beginTransactionScope('admin_set_answer_status');
        try {
            $answer = $this->lockRow('answers', $answerId);
            $old = (string) ($answer['status'] ?? 'published');
            $questionId = (int) ($answer['question_id'] ?? 0);
            if ($questionId <= 0) throw new TranslatableException('admin.answer_question_not_found');
            if ($old === $status) { $this->finishTransactionScope('admin_set_answer_status', $transactionOwner); return true; }
            $this->pdo->prepare('UPDATE answers SET status=:status,is_best=CASE WHEN :pending=1 THEN 0 ELSE is_best END,updated_at=NOW() WHERE id=:id')->execute(['status'=>$status,'pending'=>$status==='pending'?1:0,'id'=>$answerId]);
            $delta = $status === 'published' ? 1 : -1;
            $this->pdo->prepare('UPDATE questions SET answers_count=GREATEST(answers_count+:delta,0),best_answer_id=CASE WHEN :pending=1 AND best_answer_id=:answer_id THEN NULL ELSE best_answer_id END,updated_at=NOW() WHERE id=:id')->execute(['delta'=>$delta,'pending'=>$status==='pending'?1:0,'answer_id'=>$answerId,'id'=>$questionId]);
            $this->finishTransactionScope('admin_set_answer_status', $transactionOwner);
            if ($status === 'published') Hooks::emit(CoreHooks::ANSWER_PUBLISHED, ['answer_id'=>$answerId,'question_id'=>$questionId,'source'=>'admin']);
            return true;
        } catch (Throwable $throwable) { $this->failTransactionScope('admin_set_answer_status', $transactionOwner); throw $throwable; }
    }

    public function deleteAnswer(int $answerId): bool
    {
        $transactionOwner = $this->beginTransactionScope('admin_delete_answer');
        try {
            $answer = $this->lockRow('answers', $answerId);
            $questionId = (int) $answer['question_id'];
            $this->lockRow('questions', $questionId);
            foreach (['votes' => 'votable_type = \'answer\' AND votable_id = :id', 'comments' => 'commentable_type = \'answer\' AND commentable_id = :id', 'reports' => 'reportable_type = \'answer\' AND reportable_id = :id'] as $table => $condition) {
                $statement = $this->pdo->prepare('DELETE FROM ' . $table . ' WHERE ' . $condition);
                $statement->execute(['id' => $answerId]);
            }
            $delete = $this->pdo->prepare('DELETE FROM answers WHERE id = :id');
            $delete->execute(['id' => $answerId]);
            if ((string) ($answer['status'] ?? 'published') === 'published') {
                $update = $this->pdo->prepare('UPDATE questions SET answers_count = GREATEST(answers_count - 1, 0), best_answer_id = CASE WHEN best_answer_id = :answer_id THEN NULL ELSE best_answer_id END, updated_at = NOW() WHERE id = :id');
                $update->execute(['answer_id' => $answerId, 'id' => $questionId]);
            }
            $this->finishTransactionScope('admin_delete_answer', $transactionOwner);

            return true;
        } catch (Throwable $throwable) {
            $this->failTransactionScope('admin_delete_answer', $transactionOwner);
            throw $throwable;
        }
    }

    /** @return list<array<string, mixed>> */
    public function categories(): array
    {
        $statement = $this->pdo->query(
            'SELECT c.id, c.name, c.description, c.content_locale, c.slug, c.parent_id, c.sort_order,
                    c.seo_title, c.seo_description, c.canonical_url, c.seo_index,
                    COALESCE(p.name, \'\') AS parent_name, COUNT(q.id) AS questions_count
             FROM categories c
             LEFT JOIN categories p ON p.id = c.parent_id
             LEFT JOIN questions q ON q.category_id = c.id
             GROUP BY c.id
             ORDER BY c.sort_order ASC, c.name ASC'
        );

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

    public function saveCategory(array $input): int
    {
        $id = (int) ($input['id'] ?? 0);
        $name = trim((string) ($input['name'] ?? ''));
        if ($name === '') {
            throw new TranslatableException('admin.category_name_required');
        }
        $slug = $this->uniqueSlug('categories', (string) ($input['slug'] ?? '') ?: $name, $id);
        $parentId = (int) ($input['parent_id'] ?? 0);
        if ($parentId > 0 && $parentId === $id) {
            throw new TranslatableException('admin.category_self_parent');
        }
        $locale = $this->normalizeEnabledLocale((string) ($input['content_locale'] ?? 'ar'));
        $sort = (int) ($input['sort_order'] ?? 0);
        $this->assertValidCategoryParent($id, $parentId);
        if ($parentId > 0) {
            $parent = $this->pdo->prepare('SELECT content_locale FROM categories WHERE id=:id LIMIT 1');
            $parent->execute(['id' => $parentId]);
            if ((string) $parent->fetchColumn() !== $locale) {
                throw new TranslatableException('admin.category_parent_locale_mismatch');
            }
        }
        if ($id > 0) {
            $children = $this->pdo->prepare('SELECT COUNT(*) FROM categories WHERE parent_id=:id AND content_locale<>:locale');
            $children->execute(['id' => $id, 'locale' => $locale]);
            if ((int) $children->fetchColumn() > 0) {
                throw new TranslatableException('admin.category_child_locale_conflict');
            }
        }
        $seo = $this->normalizeSeoInput($input);
        $params = [
            'name' => $name,
            'description' => trim((string) ($input['description'] ?? '')) ?: null,
            'locale' => $locale,
            'slug' => $slug,
            'parent_id' => $parentId > 0 ? $parentId : null,
            'sort' => $sort,
            'seo_title' => $seo['title'],
            'seo_description' => $seo['description'],
            'canonical_url' => $seo['canonical'],
            'seo_index' => $seo['index'],
        ];

        if ($id > 0) {
            $statement = $this->pdo->prepare(
                'UPDATE categories SET name=:name,description=:description,content_locale=:locale,slug=:slug,parent_id=:parent_id,sort_order=:sort,'
                . 'seo_title=:seo_title,seo_description=:seo_description,canonical_url=:canonical_url,seo_index=:seo_index,updated_at=NOW() WHERE id=:id'
            );
            $statement->execute($params + ['id' => $id]);
            return $id;
        }

        $statement = $this->pdo->prepare(
            'INSERT INTO categories (name,description,content_locale,slug,parent_id,sort_order,seo_title,seo_description,canonical_url,seo_index,created_at,updated_at) '
            . 'VALUES (:name,:description,:locale,:slug,:parent_id,:sort,:seo_title,:seo_description,:canonical_url,:seo_index,NOW(),NOW())'
        );
        $statement->execute($params);

        return (int) $this->pdo->lastInsertId();
    }

    public function deleteCategory(int $id): void
    {
        $transactionOwner = $this->beginTransactionScope('admin_delete_category');
        try {
            $this->pdo->prepare('UPDATE categories SET parent_id=NULL, updated_at=NOW() WHERE parent_id=:id')->execute(['id' => $id]);
            $this->pdo->prepare('UPDATE questions SET category_id=NULL, updated_at=NOW() WHERE category_id=:id')->execute(['id' => $id]);
            $statement = $this->pdo->prepare('DELETE FROM categories WHERE id=:id'); $statement->execute(['id' => $id]);
            $this->finishTransactionScope('admin_delete_category', $transactionOwner);
        } catch (Throwable $throwable) { $this->failTransactionScope('admin_delete_category', $transactionOwner); throw $throwable; }
    }

    /** @return list<array<string, mixed>> */
    public function tags(): array
    {
        $statement = $this->pdo->query(
            'SELECT t.id,t.name,t.description,t.slug,t.content_locale,t.seo_title,t.seo_description,t.canonical_url,t.seo_index,t.created_at,'
            . 'COUNT(qt.question_id) AS questions_count,'
            . "COALESCE((SELECT GROUP_CONCAT(ts.synonym ORDER BY ts.synonym SEPARATOR ', ') FROM tag_synonyms ts WHERE ts.tag_id=t.id),'') AS synonyms "
            . 'FROM tags t LEFT JOIN question_tags qt ON qt.tag_id=t.id GROUP BY t.id ORDER BY t.name ASC'
        );

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

    public function saveTag(array $input): int
    {
        $id = (int) ($input['id'] ?? 0);
        $name = trim((string) ($input['name'] ?? ''));
        if ($name === '') {
            throw new TranslatableException('admin.tag_name_required');
        }
        $slug = $this->uniqueSlug('tags', (string) ($input['slug'] ?? '') ?: $name, $id);
        $locale = $this->normalizeEnabledLocale((string) ($input['content_locale'] ?? 'ar'));
        $seo = $this->normalizeSeoInput($input);
        $description = trim((string) ($input['description'] ?? '')) ?: null;

        $transactionOwner = $this->beginTransactionScope('admin_save_tag');
        try {
            if ($id > 0) {
                $statement = $this->pdo->prepare(
                    'UPDATE tags SET name=:name,description=:description,slug=:slug,content_locale=:locale,seo_title=:seo_title,'
                    . 'seo_description=:seo_description,canonical_url=:canonical_url,seo_index=:seo_index,updated_at=NOW() WHERE id=:id'
                );
                $statement->execute([
                    'name'=>$name,'description'=>$description,'slug'=>$slug,'locale'=>$locale,'seo_title'=>$seo['title'],
                    'seo_description'=>$seo['description'],'canonical_url'=>$seo['canonical'],'seo_index'=>$seo['index'],'id'=>$id,
                ]);
            } else {
                $statement = $this->pdo->prepare(
                    'INSERT INTO tags (name,description,slug,content_locale,seo_title,seo_description,canonical_url,seo_index,created_at,updated_at) '
                    . 'VALUES (:name,:description,:slug,:locale,:seo_title,:seo_description,:canonical_url,:seo_index,NOW(),NOW())'
                );
                $statement->execute([
                    'name'=>$name,'description'=>$description,'slug'=>$slug,'locale'=>$locale,'seo_title'=>$seo['title'],
                    'seo_description'=>$seo['description'],'canonical_url'=>$seo['canonical'],'seo_index'=>$seo['index'],
                ]);
                $id = (int) $this->pdo->lastInsertId();
            }
            $this->syncTagSynonyms($id, (string) ($input['synonyms'] ?? ''), $name);
            $this->finishTransactionScope('admin_save_tag', $transactionOwner);
            return $id;
        } catch (Throwable $throwable) {
            $this->failTransactionScope('admin_save_tag', $transactionOwner);
            throw $throwable;
        }
    }

    public function mergeTags(int $sourceTagId, int $targetTagId, bool $preserveSourceName = true): void
    {
        if ($sourceTagId <= 0 || $targetTagId <= 0 || $sourceTagId === $targetTagId) {
            throw new TranslatableException('admin.tag_merge_distinct');
        }
        $transactionOwner = $this->beginTransactionScope('admin_merge_tags');
        try {
            $find = $this->pdo->prepare('SELECT id,name,content_locale FROM tags WHERE id IN (?, ?) FOR UPDATE');
            $find->execute([$sourceTagId, $targetTagId]);
            $rows = [];
            foreach ($find->fetchAll(PDO::FETCH_ASSOC) as $row) $rows[(int) $row['id']] = $row;
            if (!isset($rows[$sourceTagId], $rows[$targetTagId])) throw new TranslatableException('admin.tag_merge_not_found');
            if ((string) $rows[$sourceTagId]['content_locale'] !== (string) $rows[$targetTagId]['content_locale']) {
                throw new TranslatableException('admin.tag_merge_locale_mismatch');
            }

            $moveQuestions = $this->pdo->prepare('INSERT IGNORE INTO question_tags (question_id,tag_id,created_at) SELECT question_id,:target,created_at FROM question_tags WHERE tag_id=:source');
            $moveQuestions->execute(['target'=>$targetTagId,'source'=>$sourceTagId]);
            $moveFollows = $this->pdo->prepare('INSERT IGNORE INTO tag_follows (user_id,tag_id,created_at) SELECT user_id,:target,created_at FROM tag_follows WHERE tag_id=:source');
            $moveFollows->execute(['target'=>$targetTagId,'source'=>$sourceTagId]);
            $moveSynonyms = $this->pdo->prepare('INSERT IGNORE INTO tag_synonyms (tag_id,synonym,created_at) SELECT :target,synonym,created_at FROM tag_synonyms WHERE tag_id=:source');
            $moveSynonyms->execute(['target'=>$targetTagId,'source'=>$sourceTagId]);
            if ($preserveSourceName) {
                $synonym = trim((string) $rows[$sourceTagId]['name']);
                if ($synonym !== '') {
                    $insertSynonym = $this->pdo->prepare('INSERT IGNORE INTO tag_synonyms (tag_id,synonym,created_at) VALUES (:tag_id,:synonym,NOW())');
                    $insertSynonym->execute(['tag_id'=>$targetTagId,'synonym'=>$synonym]);
                }
            }
            $delete = $this->pdo->prepare('DELETE FROM tags WHERE id=:id');
            $delete->execute(['id'=>$sourceTagId]);
            $this->finishTransactionScope('admin_merge_tags', $transactionOwner);
        } catch (Throwable $throwable) {
            $this->failTransactionScope('admin_merge_tags', $transactionOwner);
            throw $throwable;
        }
    }

    public function deleteTag(int $id): void
    {
        $transactionOwner = $this->beginTransactionScope('admin_delete_tag');
        try {
            $this->pdo->prepare('DELETE FROM question_tags WHERE tag_id=:id')->execute(['id' => $id]);
            $this->pdo->prepare('DELETE FROM tag_follows WHERE tag_id=:id')->execute(['id' => $id]);
            $this->pdo->prepare('DELETE FROM tag_synonyms WHERE tag_id=:id')->execute(['id' => $id]);
            $this->pdo->prepare('DELETE FROM tags WHERE id=:id')->execute(['id' => $id]);
            $this->finishTransactionScope('admin_delete_tag', $transactionOwner);
        } catch (Throwable $throwable) { $this->failTransactionScope('admin_delete_tag', $transactionOwner); throw $throwable; }
    }

    /** @return list<array<string, mixed>> */
    public function users(string $search = ''): array
    {
        $where = '';
        $params = [];
        if ($search !== '') {
            $where = " WHERE (COALESCE(name,'') LIKE :search_name ESCAPE '|' OR email LIKE :search_email ESCAPE '|')";
            $needle = '%' . str_replace(['|', '%', '_'], ['||', '|%', '|_'], $search) . '%';
            $params['search_name'] = $needle;
            $params['search_email'] = $needle;
        }
        $statement = $this->pdo->prepare('SELECT id, name, email, role, account_type, trust_level, login_disabled, created_by_admin_id, status, points, preferred_locale, email_verified_at, created_at FROM users' . $where . ' ORDER BY created_at DESC LIMIT 200');
        $statement->execute($params);
        return $statement->fetchAll(PDO::FETCH_ASSOC);
    }

    public function createUser(string $name, string $email, string $password, string $role, string $status, string $locale): int
    {
        if (!filter_var($email, FILTER_VALIDATE_EMAIL) || strlen($password) < 8) throw new TranslatableException('admin.user_credentials_invalid');
        $statement = $this->pdo->prepare('INSERT INTO users (name,email,password_hash,role,status,preferred_locale,email_verified_at,created_at,updated_at) VALUES (:name,:email,:password_hash,:role,:status,:locale,NOW(),NOW(),NOW())');
        $statement->execute(['name' => trim($name), 'email' => strtolower(trim($email)), 'password_hash' => password_hash($password, PASSWORD_DEFAULT), 'role' => $role, 'status' => $status, 'locale' => $this->normalizeEnabledLocale($locale)]);
        return (int) $this->pdo->lastInsertId();
    }

    public function createEditorialPersona(string $name, string $email, string $locale, int $createdByAdminId): int
    {
        $name = trim($name);
        $email = strtolower(trim($email));
        if ($name === '' || !filter_var($email, FILTER_VALIDATE_EMAIL) || $createdByAdminId <= 0) {
            throw new TranslatableException('admin.user_credentials_invalid');
        }
        $passwordHash = password_hash(bin2hex(random_bytes(32)), PASSWORD_DEFAULT);
        $statement = $this->pdo->prepare("INSERT INTO users (name,email,password_hash,role,account_type,trust_level,login_disabled,created_by_admin_id,status,preferred_locale,email_verified_at,created_at,updated_at) VALUES (:name,:email,:password_hash,'member','editorial',2,1,:created_by_admin_id,'active',:locale,NOW(),NOW(),NOW())");
        $statement->execute(['name'=>$name,'email'=>$email,'password_hash'=>$passwordHash,'created_by_admin_id'=>$createdByAdminId,'locale'=>$this->normalizeEnabledLocale($locale)]);
        return (int) $this->pdo->lastInsertId();
    }

    public function updateUserTrust(int $id, int $trustLevel, bool $loginDisabled): void
    {
        $trustLevel = max(0, min(5, $trustLevel));
        $statement = $this->pdo->prepare('UPDATE users SET trust_level=:trust_level,login_disabled=:login_disabled,auth_version=auth_version+1,updated_at=NOW() WHERE id=:id');
        $statement->execute(['trust_level'=>$trustLevel,'login_disabled'=>$loginDisabled?1:0,'id'=>$id]);
    }

    public function updateUser(int $id, string $role, string $status, string $locale, ?string $name = null, ?string $email = null, ?string $password = null, ?bool $verified = null): void
    {
        if (!in_array($role, ['member', 'moderator', 'admin'], true) || !in_array($status, ['active', 'banned', 'suspended'], true)) {
            throw new TranslatableException('admin.user_role_status_invalid');
        }
        $locale = $this->normalizeEnabledLocale($locale);
        $this->assertLastActiveAdminPreserved($id, $role, $status);
        $fields = 'role=:role,status=:status,preferred_locale=:locale,auth_version=auth_version+1,updated_at=NOW()'; $params = ['role' => $role, 'status' => $status, 'locale' => $locale, 'id' => $id];
        if ($name !== null) { $fields .= ',name=:name'; $params['name'] = trim($name); }
        if ($email !== null && filter_var($email, FILTER_VALIDATE_EMAIL)) { $fields .= ',email=:email'; $params['email'] = strtolower(trim($email)); }
        if ($password !== null && $password !== '') { if (strlen($password) < 8) throw new TranslatableException('admin.password_min'); $fields .= ',password_hash=:password_hash'; $params['password_hash'] = password_hash($password, PASSWORD_DEFAULT); }
        if ($verified !== null) { $fields .= ',email_verified_at=:verified'; $params['verified'] = $verified ? gmdate('Y-m-d H:i:s') : null; }
        $statement = $this->pdo->prepare('UPDATE users SET ' . $fields . ' WHERE id=:id'); $statement->execute($params);
    }

    public function setUserStatus(int $id, string $status): void
    {
        if (!in_array($status, ['active', 'banned', 'suspended'], true)) {
            throw new TranslatableException('admin.user_status_invalid');
        }
        $statement = $this->pdo->prepare('SELECT role FROM users WHERE id = :id LIMIT 1');
        $statement->execute(['id' => $id]);
        $role = $statement->fetchColumn();
        if (!is_string($role)) {
            throw new TranslatableException('admin.user_not_found');
        }
        $this->assertLastActiveAdminPreserved($id, $role, $status);
        $update = $this->pdo->prepare('UPDATE users SET status = :status, auth_version = auth_version + 1, updated_at = NOW() WHERE id = :id');
        $update->execute(['status' => $status, 'id' => $id]);
    }

    public function deleteUser(int $id): void
    {
        $statement = $this->pdo->prepare('SELECT role, status FROM users WHERE id = :id LIMIT 1');
        $statement->execute(['id' => $id]);
        $row = $statement->fetch(PDO::FETCH_ASSOC);
        if (!is_array($row)) {
            throw new TranslatableException('admin.user_not_found');
        }
        if ((string) ($row['role'] ?? '') === 'admin' && (string) ($row['status'] ?? '') === 'active') {
            $this->assertLastActiveAdminPreserved($id, 'member', 'banned');
        }
        $delete = $this->pdo->prepare('DELETE FROM users WHERE id = :id');
        $delete->execute(['id' => $id]);
    }

    /** @return list<array<string, mixed>> */
    public function comments(): array
    {
        $statement = $this->pdo->query(
            "SELECT c.id,c.commentable_type,c.commentable_id,c.body,c.status,c.created_at,c.updated_at,
                    COALESCE(u.name,u.email,'Anonymous') AS author_name,
                    CASE WHEN c.commentable_type='question' THEN q.title WHEN c.commentable_type='answer' THEN aq.title ELSE t.title END AS question_title,
                    CASE WHEN c.commentable_type='question' THEN q.slug WHEN c.commentable_type='answer' THEN aq.slug ELSE t.slug END AS question_slug
             FROM comments c
             LEFT JOIN users u ON u.id=c.user_id
             LEFT JOIN questions q ON c.commentable_type='question' AND q.id=c.commentable_id
             LEFT JOIN answers a ON c.commentable_type='answer' AND a.id=c.commentable_id
             LEFT JOIN questions aq ON aq.id=a.question_id
             LEFT JOIN topics t ON c.commentable_type='topic' AND t.id=c.commentable_id
             ORDER BY FIELD(c.status,'pending','hidden','published'),c.created_at DESC,c.id DESC LIMIT 300"
        );
        return $statement->fetchAll(PDO::FETCH_ASSOC);
    }

    public function updateCommentStatus(int $id, string $status): void
    {
        if (!in_array($status, ['published','pending','hidden'], true)) throw new TranslatableException('admin.comment_status_invalid');
        $transactionOwner = $this->beginTransactionScope('admin_comment_status');
        try {
            $find = $this->pdo->prepare("SELECT c.status,c.commentable_type,c.commentable_id,CASE WHEN c.commentable_type='question' THEN c.commentable_id WHEN c.commentable_type='answer' THEN a.question_id ELSE NULL END AS question_id FROM comments c LEFT JOIN answers a ON c.commentable_type='answer' AND a.id=c.commentable_id WHERE c.id=:id FOR UPDATE");
            $find->execute(['id'=>$id]);
            $row=$find->fetch(PDO::FETCH_ASSOC);
            if (!is_array($row)) { $this->finishTransactionScope('admin_comment_status', $transactionOwner); return; }
            $old=(string)($row['status']??'published');
            if ($old === $status) { $this->finishTransactionScope('admin_comment_status', $transactionOwner); return; }
            $this->pdo->prepare('UPDATE comments SET status=:status,updated_at=NOW() WHERE id=:id')->execute(['status'=>$status,'id'=>$id]);
            $wasPublished=$old==='published'; $isPublished=$status==='published';
            if ($wasPublished !== $isPublished) {
                $delta=$isPublished?1:-1;
                if (($row['commentable_type']??'') === 'topic') {
                    $this->pdo->prepare('UPDATE topics SET comments_count=GREATEST(comments_count+:delta,0),last_activity_at=CASE WHEN :inc=1 THEN NOW() ELSE last_activity_at END,updated_at=NOW() WHERE id=:id')->execute(['delta'=>$delta,'inc'=>$isPublished?1:0,'id'=>(int)($row['commentable_id']??0)]);
                } elseif ((int)($row['question_id']??0)>0) {
                    $this->pdo->prepare('UPDATE questions SET comments_count=GREATEST(comments_count+:delta,0),updated_at=NOW() WHERE id=:id')->execute(['delta'=>$delta,'id'=>(int)$row['question_id']]);
                }
            }
            $this->finishTransactionScope('admin_comment_status', $transactionOwner);
        } catch (Throwable $throwable) { $this->failTransactionScope('admin_comment_status', $transactionOwner); throw $throwable; }
    }

    public function deleteComment(int $id): void
    {
        $transactionOwner = $this->beginTransactionScope('admin_delete_comment');
        try {
            $find = $this->pdo->prepare("SELECT c.status,c.commentable_type,c.commentable_id,CASE WHEN c.commentable_type='question' THEN c.commentable_id ELSE a.question_id END AS question_id FROM comments c LEFT JOIN answers a ON c.commentable_type='answer' AND a.id=c.commentable_id WHERE c.id=:id FOR UPDATE");
            $find->execute(['id'=>$id]);
            $row=$find->fetch(PDO::FETCH_ASSOC);
            if (!is_array($row)) { $this->failTransactionScope('admin_delete_comment', $transactionOwner); return; }
            $delete=$this->pdo->prepare('DELETE FROM comments WHERE id=:id');
            $delete->execute(['id'=>$id]);
            $questionId=(int)($row['question_id']??0);
            if ((string)($row['status'] ?? 'published') === 'published' && ($row['commentable_type'] ?? '') === 'topic') {
                $count=$this->pdo->prepare('UPDATE topics SET comments_count=GREATEST(comments_count-1,0),updated_at=NOW() WHERE id=:id');
                $count->execute(['id'=>(int)($row['commentable_id']??0)]);
            } elseif((string)($row['status'] ?? 'published') === 'published' && $questionId>0){
                $count=$this->pdo->prepare('UPDATE questions SET comments_count=GREATEST(comments_count-1,0),updated_at=NOW() WHERE id=:id');
                $count->execute(['id'=>$questionId]);
            }
            $this->finishTransactionScope('admin_delete_comment', $transactionOwner);
        } catch (\Throwable $e) {
            $this->failTransactionScope('admin_delete_comment', $transactionOwner);
            throw $e;
        }
    }

    /** @return list<array<string, mixed>> */
    public function reports(?string $status = null): array
    {
        $where = '';
        $params = [];
        if ($status !== null && in_array($status, ['open', 'reviewing', 'resolved', 'dismissed'], true)) {
            $where = ' WHERE r.status = :status';
            $params['status'] = $status;
        }
        $statement = $this->pdo->prepare(
            "SELECT r.id,r.reportable_type,r.reportable_id,r.reason,r.status,r.notes,r.moderator_notes,r.created_at,r.updated_at,
                    COALESCE(u.name,u.email,'Anonymous') AS reporter_name,
                    COALESCE(q.title,aq.title,cq.title,caq.title,t.title,'') AS question_title,
                    COALESCE(q.slug,aq.slug,cq.slug,caq.slug,t.slug,'') AS question_slug,
                    CASE
                        WHEN r.reportable_type='question' THEN COALESCE(q.body,'')
                        WHEN r.reportable_type='answer' THEN COALESCE(a.body,'')
                        WHEN r.reportable_type='comment' THEN COALESCE(c.body,'')
                        WHEN r.reportable_type='topic' THEN COALESCE(t.body,'')
                        ELSE ''
                    END AS target_content,
                    CASE
                        WHEN r.reportable_type='answer' THEN a.id
                        WHEN r.reportable_type='comment' AND c.commentable_type='answer' THEN ca.id
                        ELSE NULL
                    END AS target_answer_id,
                    CASE WHEN r.reportable_type='comment' THEN c.id ELSE NULL END AS target_comment_id,
                    CASE WHEN r.reportable_type='comment' THEN c.commentable_type ELSE NULL END AS target_commentable_type
             FROM reports r
             LEFT JOIN users u ON u.id=r.user_id
             LEFT JOIN questions q ON r.reportable_type='question' AND q.id=r.reportable_id
             LEFT JOIN answers a ON r.reportable_type='answer' AND a.id=r.reportable_id
             LEFT JOIN questions aq ON aq.id=a.question_id
             LEFT JOIN comments c ON r.reportable_type='comment' AND c.id=r.reportable_id
             LEFT JOIN questions cq ON c.commentable_type='question' AND cq.id=c.commentable_id
             LEFT JOIN answers ca ON c.commentable_type='answer' AND ca.id=c.commentable_id
              LEFT JOIN questions caq ON caq.id=ca.question_id
             LEFT JOIN topics t ON r.reportable_type='topic' AND t.id=r.reportable_id"
            . $where
            . " ORDER BY FIELD(r.status,'open','reviewing','resolved','dismissed'),r.created_at DESC
              LIMIT 200"
        );
        $statement->execute($params);
        return $statement->fetchAll(PDO::FETCH_ASSOC);
    }

    public function updateReport(int $id, string $status, string $notes = ''): void
    {
        if (!in_array($status, ['open', 'reviewing', 'resolved', 'dismissed'], true)) {
            throw new TranslatableException('admin.report_status_invalid');
        }
        // `reports.notes` belongs to the reporter. Moderation notes have their own column.
        $statement = $this->pdo->prepare('UPDATE reports SET status = :status, moderator_notes = :moderator_notes, updated_at = NOW() WHERE id = :id');
        $statement->execute(['status' => $status, 'moderator_notes' => trim($notes) !== '' ? trim($notes) : null, 'id' => $id]);
    }

    /** @return array{layouts: list<array<string, mixed>>, sections: list<array<string, mixed>>, widgets: list<array<string, mixed>>} */
    public function widgets(): array
    {
        $layouts = $this->pdo->query('SELECT l.*, COUNT(DISTINCT ls.id) AS sections_count FROM layouts l LEFT JOIN layout_sections ls ON ls.layout_id = l.id GROUP BY l.id ORDER BY l.name ASC')->fetchAll(PDO::FETCH_ASSOC);
        $sections = $this->pdo->query('SELECT ls.*, l.name AS layout_name FROM layout_sections ls INNER JOIN layouts l ON l.id = ls.layout_id ORDER BY l.name ASC, ls.sort_order ASC')->fetchAll(PDO::FETCH_ASSOC);
        $widgets = $this->pdo->query('SELECT w.*, ls.section_key, l.name AS layout_name FROM widgets w INNER JOIN layout_sections ls ON ls.id = w.layout_section_id INNER JOIN layouts l ON l.id = ls.layout_id ORDER BY l.name ASC, ls.sort_order ASC, w.sort_order ASC')->fetchAll(PDO::FETCH_ASSOC);

        return ['layouts' => $layouts, 'sections' => $sections, 'widgets' => $widgets];
    }

    public function saveLayout(array $input): int
    {
        $name = trim((string) ($input['name'] ?? ''));
        if ($name === '') {
            throw new TranslatableException('admin.layout_name_required');
        }
        $id = (int) ($input['id'] ?? 0);
        $slug = $this->uniqueSlug('layouts', (string) ($input['slug'] ?? '') ?: $name, $id);
        $status = in_array((string) ($input['status'] ?? 'draft'), ['draft', 'published'], true) ? (string) $input['status'] : 'draft';
        if ($id > 0) {
            $statement = $this->pdo->prepare('UPDATE layouts SET name = :name, slug = :slug, description = :description, status = :status, updated_at = NOW() WHERE id = :id');
            $statement->execute(['name' => $name, 'slug' => $slug, 'description' => trim((string) ($input['description'] ?? '')) ?: null, 'status' => $status, 'id' => $id]);
            if ($status === 'published') $this->setOnlyPublishedLayout($id);
            return $id;
        }
        $statement = $this->pdo->prepare('INSERT INTO layouts (name, slug, description, status, created_at, updated_at) VALUES (:name, :slug, :description, :status, NOW(), NOW())');
        $statement->execute(['name' => $name, 'slug' => $slug, 'description' => trim((string) ($input['description'] ?? '')) ?: null, 'status' => $status]);
        $id=(int) $this->pdo->lastInsertId();
        if ($status === 'published') $this->setOnlyPublishedLayout($id);
        return $id;
    }

    public function deleteLayout(int $id): void
    {
        $statement = $this->pdo->prepare('DELETE FROM layouts WHERE id = :id');
        $statement->execute(['id' => $id]);
    }

    public function saveSection(array $input): int
    {
        $id = (int) ($input['id'] ?? 0);
        $layoutId = (int) ($input['layout_id'] ?? 0);
        $key = trim((string) ($input['section_key'] ?? ''));
        $name = trim((string) ($input['name'] ?? ''));
        if ($layoutId <= 0 || $key === '' || preg_match('/^[a-z0-9_-]{2,64}$/', $key) !== 1 || $name === '') {
            throw new TranslatableException('admin.section_fields_required');
        }
        $params = ['layout_id' => $layoutId, 'section_key' => $key, 'name' => $name, 'sort_order' => (int) ($input['sort_order'] ?? 0)];
        if ($id > 0) {
            $statement = $this->pdo->prepare('UPDATE layout_sections SET layout_id = :layout_id, section_key = :section_key, name = :name, sort_order = :sort_order, updated_at = NOW() WHERE id = :id');
            $statement->execute($params + ['id' => $id]);
            return $id;
        }
        $statement = $this->pdo->prepare('INSERT INTO layout_sections (layout_id, section_key, name, sort_order, created_at, updated_at) VALUES (:layout_id, :section_key, :name, :sort_order, NOW(), NOW())');
        $statement->execute($params);
        return (int) $this->pdo->lastInsertId();
    }

    public function deleteSection(int $id): void
    {
        $statement = $this->pdo->prepare('DELETE FROM layout_sections WHERE id = :id');
        $statement->execute(['id' => $id]);
    }

    public function saveWidget(array $input): int
    {
        $id = (int) ($input['id'] ?? 0);
        $sectionId = (int) ($input['layout_section_id'] ?? 0);
        $type = trim((string) ($input['type'] ?? ''));
        $config = $input['config'] ?? [];
        if (is_string($config)) {
            $config = json_decode($config, true, 512, JSON_THROW_ON_ERROR);
        }
        $registry = new WidgetRegistry(new HtmlSanitizer());
        $configJson = $registry->validateForPersistence($type, is_array($config) ? $config : []);
        if ($sectionId <= 0) {
            throw new TranslatableException('admin.section_required');
        }
        $enabled = !array_key_exists('is_enabled', $input) || !empty($input['is_enabled']);
        $params = ['section_id' => $sectionId, 'type' => $type, 'title' => trim((string) ($input['title'] ?? '')) ?: null, 'config_json' => $configJson, 'sort_order' => (int) ($input['sort_order'] ?? 0), 'enabled' => $enabled ? 1 : 0];
        if ($id > 0) {
            $statement = $this->pdo->prepare('UPDATE widgets SET layout_section_id = :section_id, type = :type, title = :title, config_json = :config_json, sort_order = :sort_order, is_enabled = :enabled, updated_at = NOW() WHERE id = :id');
            $statement->execute($params + ['id' => $id]);
            return $id;
        }
        $statement = $this->pdo->prepare('INSERT INTO widgets (layout_section_id, type, title, config_json, sort_order, is_enabled, created_at, updated_at) VALUES (:section_id, :type, :title, :config_json, :sort_order, :enabled, NOW(), NOW())');
        $statement->execute($params);
        return (int) $this->pdo->lastInsertId();
    }

    /** @return array<string,mixed>|null */
    public function widget(int $id): ?array
    {
        $statement = $this->pdo->prepare('SELECT id,layout_section_id,type,title,config_json,sort_order,is_enabled FROM widgets WHERE id=:id LIMIT 1');
        $statement->execute(['id' => $id]);
        $row = $statement->fetch(PDO::FETCH_ASSOC);
        return is_array($row) ? $row : null;
    }

    /** @return array<string,mixed>|null */
    public function widgetTranslation(int $widgetId, string $locale): ?array
    {
        $statement = $this->pdo->prepare('SELECT widget_id,locale,title,content_json FROM widget_translations WHERE widget_id=:widget_id AND locale=:locale LIMIT 1');
        $statement->execute(['widget_id' => $widgetId, 'locale' => strtolower(trim($locale))]);
        $row = $statement->fetch(PDO::FETCH_ASSOC);
        return is_array($row) ? $row : null;
    }

    /** @return array{before:?array<string,mixed>,after:array<string,mixed>} */
    public function saveWidgetTranslation(int $widgetId, string $locale, ?string $title, array $content): array
    {
        $locale = strtolower(str_replace('_', '-', trim($locale)));
        $language = $this->pdo->prepare('SELECT 1 FROM languages WHERE code=:locale AND is_enabled=1 LIMIT 1');
        $language->execute(['locale' => $locale]);
        if ($language->fetchColumn() === false) throw new TranslatableException('admin.widget_translation_locale_disabled');
        $widget = $this->pdo->prepare('SELECT type FROM widgets WHERE id=:id LIMIT 1');
        $widget->execute(['id' => $widgetId]);
        $type = $widget->fetchColumn();
        if (!is_string($type)) throw new TranslatableException('admin.widget_not_found');
        $before = $this->widgetTranslation($widgetId, $locale);
        $registry = new WidgetRegistry(new HtmlSanitizer());
        $contentJson = json_encode($registry->validateTranslation($type, $content), JSON_THROW_ON_ERROR | JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        $title = trim((string) $title);
        $statement = $this->pdo->prepare(
            'INSERT INTO widget_translations (widget_id,locale,title,content_json,created_at,updated_at)
             VALUES (:widget_id,:locale,:title,:content_json,NOW(),NOW())
             ON DUPLICATE KEY UPDATE title=VALUES(title),content_json=VALUES(content_json),updated_at=NOW()'
        );
        $statement->execute(['widget_id' => $widgetId, 'locale' => $locale, 'title' => $title !== '' ? $title : null, 'content_json' => $contentJson]);
        return ['before' => $before, 'after' => $this->widgetTranslation($widgetId, $locale) ?? []];
    }

    public function deleteWidget(int $id): void
    {
        $statement = $this->pdo->prepare('DELETE FROM widgets WHERE id = :id');
        $statement->execute(['id' => $id]);
    }

    public function toggleWidget(int $id): void
    {
        $statement = $this->pdo->prepare('UPDATE widgets SET is_enabled = CASE WHEN is_enabled = 1 THEN 0 ELSE 1 END, updated_at = NOW() WHERE id = :id');
        $statement->execute(['id' => $id]);
    }

    private function setOnlyPublishedLayout(int $id): void
    {
        $statement=$this->pdo->prepare("UPDATE layouts SET status='draft',updated_at=NOW() WHERE id<>:id AND status='published'");
        $statement->execute(['id'=>$id]);
    }

    /** @return array{title:?string,description:?string,canonical:?string,index:string} */
    private function normalizeSeoInput(array $input): array
    {
        $title = trim((string) ($input['seo_title'] ?? ''));
        $description = trim((string) ($input['seo_description'] ?? ''));
        $canonical = trim((string) ($input['canonical_url'] ?? ''));
        $index = strtolower(trim((string) ($input['seo_index'] ?? 'inherit')));
        $length = static fn(string $value): int => function_exists('mb_strlen') ? mb_strlen($value, 'UTF-8') : strlen($value);
        if ($length($title) > 255 || $length($description) > 191 || strlen($canonical) > 500) {
            throw new TranslatableException('admin.taxonomy_seo_too_long');
        }
        if ($canonical !== '' && !str_starts_with($canonical, '/') && filter_var($canonical, FILTER_VALIDATE_URL) === false) {
            throw new TranslatableException('admin.validation_canonical_url_invalid');
        }
        if (!in_array($index, ['inherit','index','noindex'], true)) {
            throw new TranslatableException('admin.validation_seo_index_invalid');
        }
        return ['title'=>$title !== '' ? $title : null,'description'=>$description !== '' ? $description : null,'canonical'=>$canonical !== '' ? $canonical : null,'index'=>$index];
    }

    private function syncTagSynonyms(int $tagId, string $raw, string $tagName): void
    {
        $parts = preg_split('/[\r\n,]+/u', $raw) ?: [];
        $synonyms = [];
        $tagNameKey = function_exists('mb_strtolower') ? mb_strtolower(trim($tagName), 'UTF-8') : strtolower(trim($tagName));
        foreach ($parts as $part) {
            $value = trim((string) $part);
            if ($value === '') continue;
            $key = function_exists('mb_strtolower') ? mb_strtolower($value, 'UTF-8') : strtolower($value);
            if ($key === $tagNameKey) continue;
            $length = function_exists('mb_strlen') ? mb_strlen($value, 'UTF-8') : strlen($value);
            if ($length > 120) throw new TranslatableException('admin.tag_synonym_too_long');
            $synonyms[$key] = $value;
        }
        if (count($synonyms) > 30) throw new TranslatableException('admin.tag_synonyms_max', ['count' => 30]);
        $delete = $this->pdo->prepare('DELETE FROM tag_synonyms WHERE tag_id=:tag_id');
        $delete->execute(['tag_id'=>$tagId]);
        if ($synonyms === []) return;
        $insert = $this->pdo->prepare('INSERT INTO tag_synonyms (tag_id,synonym,created_at) VALUES (:tag_id,:synonym,NOW())');
        foreach ($synonyms as $synonym) {
            try { $insert->execute(['tag_id'=>$tagId,'synonym'=>$synonym]); }
            catch (Throwable $throwable) { throw new TranslatableException('admin.tag_synonym_duplicate', [], '', $throwable); }
        }
    }

    private function assertLastActiveAdminPreserved(int $userId, string $newRole, string $newStatus): void
    {
        $statement = $this->pdo->prepare('SELECT role, status FROM users WHERE id = :id LIMIT 1');
        $statement->execute(['id' => $userId]);
        $current = $statement->fetch(PDO::FETCH_ASSOC);
        if (!is_array($current)) {
            throw new TranslatableException('admin.user_not_found');
        }
        $currentlyActiveAdmin = (string) ($current['role'] ?? '') === 'admin' && (string) ($current['status'] ?? '') === 'active';
        $willRemainActiveAdmin = $newRole === 'admin' && $newStatus === 'active';
        if (!$currentlyActiveAdmin || $willRemainActiveAdmin) {
            return;
        }
        $count = (int) $this->pdo->query("SELECT COUNT(*) FROM users WHERE role = 'admin' AND status = 'active'")->fetchColumn();
        if ($count <= 1) {
            throw new TranslatableException('admin.final_admin_protected');
        }
    }

    private function normalizeEnabledLocale(string $locale): string
    {
        $locale = strtolower(str_replace('_', '-', trim($locale)));
        if ($locale === '' || preg_match('/^[a-z]{2,3}(?:-[a-z0-9]{2,8})*$/', $locale) !== 1) {
            throw new TranslatableException('admin.locale_invalid');
        }

        try {
            $statement = $this->pdo->prepare('SELECT 1 FROM languages WHERE code = :code AND is_enabled = 1 LIMIT 1');
            $statement->execute(['code' => $locale]);
            if ($statement->fetchColumn()) {
                return $locale;
            }
        } catch (\Throwable) {
            if (in_array($locale, ['ar', 'en'], true)) {
                return $locale;
            }
        }

        throw new TranslatableException('admin.locale_not_enabled');
    }

    private function assertValidCategoryParent(int $categoryId, int $parentId): void
    {
        if ($parentId <= 0) {
            return;
        }
        if ($categoryId > 0 && $parentId === $categoryId) {
            throw new TranslatableException('admin.category_self_parent');
        }

        $visited = [];
        $currentId = $parentId;
        while ($currentId > 0) {
            if (isset($visited[$currentId])) {
                throw new TranslatableException('admin.category_cycle_existing');
            }
            if ($categoryId > 0 && $currentId === $categoryId) {
                throw new TranslatableException('admin.category_cycle_selected');
            }
            $visited[$currentId] = true;

            $statement = $this->pdo->prepare('SELECT parent_id FROM categories WHERE id = :id LIMIT 1');
            $statement->execute(['id' => $currentId]);
            $row = $statement->fetch(PDO::FETCH_ASSOC);
            if (!is_array($row)) {
                throw new TranslatableException('admin.category_parent_not_found');
            }
            $currentId = (int) ($row['parent_id'] ?? 0);
        }
    }

    /** @return list<array<string, mixed>> */
    public function contactMessages(): array
    {
        $statement = $this->pdo->query(
            'SELECT id, name, email, subject, message, locale, status, admin_notes, created_at, updated_at
             FROM contact_messages ORDER BY created_at DESC LIMIT 300'
        );
        return $statement->fetchAll(PDO::FETCH_ASSOC);
    }

    public function updateContactMessage(int $id, string $status, string $notes): void
    {
        if (!in_array($status, ['open', 'reviewing', 'resolved', 'closed'], true)) {
            throw new TranslatableException('admin.contact_status_invalid');
        }
        $statement = $this->pdo->prepare(
            'UPDATE contact_messages SET status = :status, admin_notes = :notes, updated_at = NOW() WHERE id = :id'
        );
        $statement->execute(['status' => $status, 'notes' => trim($notes) === '' ? null : trim($notes), 'id' => $id]);
    }

    /** @return list<array<string, mixed>> */
    public function notificationsForUser(int $userId, ?string $status = null): array
    {
        $where = 'user_id = :user_id';
        $params = ['user_id' => $userId];
        if ($status === 'unread') $where .= ' AND read_at IS NULL';
        if ($status === 'read') $where .= ' AND read_at IS NOT NULL';
        $statement = $this->pdo->prepare(
            'SELECT id, type, title, body, url, read_at, created_at FROM notifications WHERE ' . $where . ' ORDER BY created_at DESC LIMIT 200'
        );
        $statement->execute($params);
        return $statement->fetchAll(PDO::FETCH_ASSOC);
    }

    public function markNotificationRead(int $userId, int $notificationId): void
    {
        $statement = $this->pdo->prepare('UPDATE notifications SET read_at = COALESCE(read_at, NOW()) WHERE id = :id AND user_id = :user_id');
        $statement->execute(['id' => $notificationId, 'user_id' => $userId]);
    }

    public function markAllNotificationsRead(int $userId): void
    {
        $statement = $this->pdo->prepare('UPDATE notifications SET read_at=COALESCE(read_at,NOW()) WHERE user_id=:user_id AND read_at IS NULL');
        $statement->execute(['user_id' => $userId]);
    }

    public function unreadNotificationsCount(int $userId): int
    {
        $statement = $this->pdo->prepare('SELECT COUNT(*) FROM notifications WHERE user_id = :user_id AND read_at IS NULL');
        $statement->execute(['user_id' => $userId]);
        return (int) $statement->fetchColumn();
    }

    /**
     * Close the admin notifications a moderation action has just answered.
     *
     * store() writes one row per recipient, so one new question leaves an unread
     * row for every administrator and moderator. Publishing it cleared none of
     * them and the badge counted handled work for ever.
     */
    public function markNotificationsResolvedForUrl(string $urlNeedle): int
    {
        $statement = $this->pdo->prepare(
            'UPDATE notifications SET read_at = NOW() WHERE read_at IS NULL AND url LIKE :needle'
        );
        $statement->execute(['needle' => '%' . $urlNeedle . '%']);

        return $statement->rowCount();
    }

    /**
     * Action queue for administration dashboard: pending questions, open reports, new contact messages.
     *
     * @return array{questions: list<array<string, mixed>>, reports: list<array<string, mixed>>, messages: list<array<string, mixed>>}
     */
    public function actionQueue(int $limit = 5): array
    {
        $limit = max(1, min(20, $limit));

        // The keys and column aliases below are read directly by the overview
        // card in admin/index.php. They were named differently on each side --
        // questions/pending_questions, reportable_type/target_type, and no
        // category at all -- so every column resolved to null and the admin
        // home page died on count(null). Nothing rendered the page in a test,
        // so a full green suite said the panel was fine.
        $questions = $this->pdo->query(
            "SELECT q.id, q.title, q.created_at,
                    COALESCE(u.name, u.email, 'Anonymous') AS author_name,
                    COALESCE(c.name, '') AS category_name
             FROM questions q
             LEFT JOIN users u ON u.id = q.user_id
             LEFT JOIN categories c ON c.id = q.category_id
             WHERE q.status = 'pending'
             ORDER BY q.created_at ASC, q.id ASC
             LIMIT " . $limit
        )->fetchAll(PDO::FETCH_ASSOC);

        $reports = $this->pdo->query(
            "SELECT r.id, r.reportable_type AS target_type, r.reportable_id, r.reason, r.created_at,
                    COALESCE(u.name, u.email, 'Anonymous') AS reporter_name
             FROM reports r
             LEFT JOIN users u ON u.id = r.user_id
             WHERE r.status = 'open'
             ORDER BY r.created_at ASC, r.id ASC
             LIMIT " . $limit
        )->fetchAll(PDO::FETCH_ASSOC);

        $messages = $this->pdo->query(
            "SELECT m.id, m.name, m.email, m.subject, m.created_at
             FROM contact_messages m
             WHERE m.status = 'open'
             ORDER BY m.created_at ASC, m.id ASC
             LIMIT " . $limit
        )->fetchAll(PDO::FETCH_ASSOC);

        return [
            'pending_questions' => $questions,
            'open_reports' => $reports,
            'open_messages' => $messages,
        ];
    }

    /** @return list<array<string,mixed>> */
    public function recentUnreadNotifications(int $userId, int $limit = 5): array
    {
        $limit = max(1, min(10, $limit));
        $statement = $this->pdo->prepare(
            'SELECT id,type,title,body,url,read_at,created_at FROM notifications WHERE user_id=:user_id AND read_at IS NULL ORDER BY created_at DESC,id DESC LIMIT ' . $limit
        );
        $statement->execute(['user_id'=>$userId]);
        return $statement->fetchAll(PDO::FETCH_ASSOC);
    }

    /** @return list<array<string,mixed>> */
    public function auditLogs(int $limit = 250): array
    {
        $limit = max(1, min(500, $limit));
        $statement = $this->pdo->query(
            "SELECT a.id,a.action,a.resource_type,a.resource_id,a.after_json,a.ip_hash,a.created_at,COALESCE(u.name,u.email,'System') AS actor_name "
            . 'FROM audit_log a LEFT JOIN users u ON u.id=a.actor_user_id ORDER BY a.created_at DESC,a.id DESC LIMIT ' . $limit
        );
        return $statement->fetchAll(PDO::FETCH_ASSOC);
    }

    /** @return list<array<string,mixed>> */
    public function moderationActions(int $limit = 250): array
    {
        $limit = max(1, min(500, $limit));
        $statement = $this->pdo->query(
            "SELECT m.id,m.action,m.resource_type,m.resource_id,m.reason,m.created_at,COALESCE(u.name,u.email,'System') AS actor_name "
            . 'FROM moderation_actions m LEFT JOIN users u ON u.id=m.actor_user_id ORDER BY m.created_at DESC,m.id DESC LIMIT ' . $limit
        );
        return $statement->fetchAll(PDO::FETCH_ASSOC);
    }

    public function settings(): SettingsRepository
    {
        return new SettingsRepository($this->pdo);
    }

    /** @return array<string, mixed> */
    private function lockRow(string $table, int $id): array
    {
        if (!in_array($table, ['answers', 'questions'], true)) {
            throw new InvalidArgumentException('Invalid table.');
        }
        $statement = $this->pdo->prepare('SELECT * FROM ' . $table . ' WHERE id = :id LIMIT 1 FOR UPDATE');
        $statement->execute(['id' => $id]);
        $row = $statement->fetch(PDO::FETCH_ASSOC);
        if (!is_array($row)) {
            throw new RuntimeException(ucfirst(substr($table, 0, -1)) . ' not found.');
        }
        return $row;
    }

    private function uniqueSlug(string $table, string $source, int $ignoreId): string
    {
        if (!in_array($table, ['categories', 'tags', 'layouts'], true)) {
            throw new InvalidArgumentException('Invalid slug table.');
        }
        return $this->slugger->unique($source, function (string $candidate) use ($table, $ignoreId): bool {
            $statement = $this->pdo->prepare('SELECT id FROM ' . $table . ' WHERE slug = :slug AND id <> :id LIMIT 1');
            $statement->execute(['slug' => $candidate, 'id' => $ignoreId]);
            return $statement->fetchColumn() !== false;
        });
    }

    private function beginTransactionScope(string $name): bool
    {
        if ($this->pdo->inTransaction()) {
            $this->pdo->exec('SAVEPOINT ' . $name);
            return false;
        }

        $this->pdo->beginTransaction();
        return true;
    }

    private function finishTransactionScope(string $name, bool $owner): void
    {
        if ($owner) {
            $this->pdo->commit();
            return;
        }

        $this->pdo->exec('RELEASE SAVEPOINT ' . $name);
    }

    private function failTransactionScope(string $name, bool $owner): void
    {
        if ($owner) {
            if ($this->pdo->inTransaction()) $this->pdo->rollBack();
            return;
        }

        if (!$this->pdo->inTransaction()) return;
        $this->pdo->exec('ROLLBACK TO SAVEPOINT ' . $name);
        try {
            $this->pdo->exec('RELEASE SAVEPOINT ' . $name);
        } catch (Throwable) {
            // The outer transaction remains the caller's responsibility.
        }
    }
}
