<?php

declare(strict_types=1);

namespace CliqThemes\StoreLocator\Repository;

defined('ABSPATH') || exit;

// This repository is the data-access layer for plugin-owned custom tables.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

use CliqThemes\StoreLocator\I18n\ContentTranslator;
use CliqThemes\StoreLocator\I18n\Multilingual;
use CliqThemes\StoreLocator\Models\Category;
use CliqThemes\StoreLocator\Support\FrontendAssetRegistry;
use CliqThemes\StoreLocator\Support\ColumnInput;

/**
 * CRUD + hierarchy helpers for store categories.
 */
final class CategoryRepository
{
    /**
     * @return array<int, array<string, mixed>>
     */
    public function all(bool $onlyActive = false): array
    {
        $query = Category::query()->orderBy('ordering', 'ASC')->orderBy('name', 'ASC');
        ContentTranslator::scopeOriginals($query);
        if ($onlyActive) {
            $query->where('is_active', '=', 1);
        }

        $items = array_map([$this, 'normalize'], $query->raw());

        return ContentTranslator::overlay($items, 'cliq_sl_categories', ['name'], 'id');
    }

    /**
     * Categories as a nested tree (children under each parent).
     *
     * @return array<int, array<string, mixed>>
     */
    public function tree(bool $onlyActive = false): array
    {
        $flat = $this->all($onlyActive);
        $byParent = [];
        foreach ($flat as $cat) {
            $byParent[$cat['parent_id']][] = $cat;
        }

        // Same guard as the public aggregator: a parent cycle in stored data
        // must not recurse forever.
        $build = static function (int $parentId, array $seen = []) use (&$build, $byParent): array {
            if (isset($seen[$parentId])) {
                return [];
            }
            $seen[$parentId] = true;
            $branch = [];
            foreach ($byParent[$parentId] ?? [] as $cat) {
                $cat['children'] = $build((int) $cat['id'], $seen);
                $branch[] = $cat;
            }

            return $branch;
        };

        return $build(0);
    }

    /**
     * @return array<string, mixed>|null
     */
    public function find(int $id): ?array
    {
        $cat = Category::find($id);

        return $cat ? $this->normalize($cat->toArray()) : null;
    }

    /**
     * @param array<string, mixed> $data
     *
     * @return array<string, mixed>
     */
    public function create(array $data): array
    {
        $data['slug'] = $this->uniqueSlug($data['slug'] ?? sanitize_title((string) ($data['name'] ?? 'category')));
        $writable = $this->writable($data);
        if (array_key_exists('parent_id', $writable)) {
            // A new row has no id yet, so it cannot be its own ancestor — only
            // the parent's existence is in question.
            $writable['parent_id'] = self::safeParentId(0, (int) $writable['parent_id']);
        }
        $cat = Category::create($writable);
        $id = (int) $cat->getKey();
        ContentTranslator::stampOriginalTrid('cliq_sl_categories', $id);
        FrontendAssetRegistry::invalidate();

        return $this->find($id) ?? [];
    }

    /**
     * Create/update a category's translation for one language (text = name).
     *
     * @param array<string, mixed> $data
     *
     * @return array<string, mixed>|null
     */
    public function saveTranslation(int $sourceId, string $lang, array $data): ?array
    {
        $lang = Multilingual::normalize($lang);
        if ($lang === '') {
            return null;
        }
        $source = Category::find($sourceId);
        if (! $source) {
            return null;
        }

        $existing = ContentTranslator::translationsOf('cliq_sl_categories', $sourceId);
        $fields = [
            'name' => ColumnInput::sanitize('name', (string) ($data['name'] ?? $source->getAttribute('name'))),
            'slug' => (string) $source->getAttribute('slug'),
            'parent_id' => (int) $source->getAttribute('parent_id'),
            'is_active' => (int) $source->getAttribute('is_active'),
            'lang' => $lang,
            'trid' => ContentTranslator::tridFor('cliq_sl_categories', $sourceId),
            'source_id' => $sourceId,
        ];

        if (isset($existing[$lang])) {
            $row = Category::find($existing[$lang]);
            $row->fill($fields);
            $row->save();
            $id = $existing[$lang];
        } else {
            $row = Category::create($fields);
            $id = (int) $row->getKey();
        }

        return ['id' => $id, 'source_id' => $sourceId] + $fields;
    }

    /**
     * @return array<string, array<string, mixed>>
     */
    public function translations(int $sourceId): array
    {
        $out = [];
        foreach (ContentTranslator::translationsOf('cliq_sl_categories', $sourceId) as $lang => $id) {
            $row = Category::find($id);
            if ($row) {
                $out[$lang] = ['id' => $id, 'name' => $row->getAttribute('name')];
            }
        }

        return $out;
    }

    /**
     * @param array<string, mixed> $data
     *
     * @return array<string, mixed>|null
     */
    public function update(int $id, array $data): ?array
    {
        $cat = Category::find($id);
        if (! $cat) {
            return null;
        }
        $writable = $this->writable($data);
        if (array_key_exists('parent_id', $writable)) {
            $writable['parent_id'] = self::safeParentId($id, (int) $writable['parent_id']);
        }
        $cat->fill($writable);
        $cat->save();
        FrontendAssetRegistry::invalidate();

        return $this->find($id);
    }

    public function delete(int $id): bool
    {
        global $wpdb;
        // Re-parent children to root and detach from stores.
        $wpdb->update($wpdb->prefix . 'cliq_sl_categories', ['parent_id' => 0], ['parent_id' => $id], ['%d'], ['%d']);
        $wpdb->delete($wpdb->prefix . 'cliq_sl_store_category', ['category_id' => $id], ['%d']);
        // Remove this category's translations along with the original.
        $wpdb->delete($wpdb->prefix . 'cliq_sl_categories', ['source_id' => $id], ['%d']);

        $cat = Category::find($id);

        $deleted = $cat ? $cat->delete() : false;
        if ($deleted) {
            FrontendAssetRegistry::invalidate();
        }

        return $deleted;
    }

    /**
     * @param array<string, mixed> $row
     *
     * @return array<string, mixed>
     */
    private function normalize(array $row): array
    {
        $row['id'] = (int) $row['id'];
        $row['parent_id'] = (int) ($row['parent_id'] ?? 0);
        $row['ordering'] = (int) ($row['ordering'] ?? 0);
        $row['is_active'] = (bool) ($row['is_active'] ?? true);

        return $row;
    }

    /**
     * @param array<string, mixed> $data
     *
     * @return array<string, mixed>
     */
    /**
     * A parent this category may actually have, or 0.
     *
     * Refuses three things: being its own parent, naming a parent that does not
     * exist, and joining a chain that leads back to itself. Without the last
     * one, A → B → A is reachable in two ordinary edits, and the public
     * /categories endpoint then recurses until the request dies.
     */
    public static function safeParentId(int $categoryId, int $parentId): int
    {
        if ($parentId <= 0 || ($categoryId > 0 && $parentId === $categoryId)) {
            return 0;
        }

        global $wpdb;

        // Walk up from the proposed parent. Reaching this category means the
        // edit would close a loop; `$seen` stops a loop that already exists
        // from trapping the walk itself.
        $seen = [];
        $cursor = $parentId;
        while ($cursor > 0 && ! isset($seen[$cursor])) {
            if ($categoryId > 0 && $cursor === $categoryId) {
                return 0;
            }
            $seen[$cursor] = true;
            $cursor = (int) $wpdb->get_var(
                $wpdb->prepare("SELECT parent_id FROM {$wpdb->prefix}cliq_sl_categories WHERE id = %d", $cursor)
            );
        }

        // `$cursor` only reaches 0 by walking off the top of a real chain, so a
        // parent that does not exist has already returned null → 0 above.
        $exists = (int) $wpdb->get_var(
            $wpdb->prepare("SELECT COUNT(*) FROM {$wpdb->prefix}cliq_sl_categories WHERE id = %d", $parentId)
        );

        return $exists > 0 ? $parentId : 0;
    }

    private function writable(array $data): array
    {
        $out = [];
        // NB: marker_id was previously missing here too (same class of bug as
        // StoreRepository::storeColumns()'s missing marker_source) — this path
        // (CSV import / the taxonomy REST route, not the main OrmTableProvider
        // admin form) silently dropped any marker assignment.
        foreach (['name', 'slug', 'parent_id', 'icon', 'color', 'marker_id', 'marker_design_id', 'ordering', 'is_active', 'lang'] as $key) {
            if (array_key_exists($key, $data)) {
                $out[$key] = ColumnInput::sanitize($key, $data[$key]);
            }
        }

        return $out;
    }

    private function uniqueSlug(string $base): string
    {
        global $wpdb;
        $base = $base !== '' ? $base : 'category';
        $slug = $base;
        $i = 2;
        while ((int) $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM {$wpdb->prefix}cliq_sl_categories WHERE slug = %s AND source_id IS NULL", $slug)) > 0) {
            $slug = $base . '-' . $i++;
        }

        return $slug;
    }
}
