<?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\ORM\Database\Connection;
use CliqThemes\StoreLocator\I18n\ContentTranslator;
use CliqThemes\StoreLocator\Schema\SchemaInstaller;
use CliqThemes\StoreLocator\Models\Location;
use CliqThemes\StoreLocator\Models\Store;
use CliqThemes\StoreLocator\Support\FrontendAssetRegistry;
use CliqThemes\StoreLocator\Support\StoredValue;
use CliqThemes\StoreLocator\Support\ColumnInput;

/**
 * Read/write access to stores and all their related data (locations, hours,
 * category/brand assignments and meta). This is the single write path the admin
 * REST layer uses so relational integrity stays in one place.
 */
final class StoreRepository
{
    /** Map of HoursField day keys to numeric day-of-week (0=Sun..6=Sat). */
    private const DAY_MAP = ['sun' => 0, 'mon' => 1, 'tue' => 2, 'wed' => 3, 'thu' => 4, 'fri' => 5, 'sat' => 6];

    /**
     * Shared connection used for transactional multi-table writes and for the
     * raw relation writes (locations/hours/meta/pivots). Going through the ORM
     * connection means every write is error-checked and enrolled in the active
     * transaction (it wraps the same global $wpdb the ORM models use). Resolved
     * lazily so the repository can be constructed without a live $wpdb.
     */
    private ?Connection $db;

    public function __construct(?Connection $db = null)
    {
        $this->db = $db;
    }

    private function db(): Connection
    {
        return $this->db ??= new Connection();
    }

    /**
     * Paginated, filtered list of stores with their primary location and
     * category ids attached.
     *
     * @param array<string, mixed> $args
     *
     * @return array{items: array<int, array<string, mixed>>, total: int, page: int, per_page: int}
     */
    public function list(array $args = []): array
    {
        $perPage = max(1, min(200, (int) ($args['per_page'] ?? 20)));
        $page = max(1, (int) ($args['page'] ?? 1));

        $query = Store::query();
        // Only canonical originals are listed; translated text is overlaid below.
        ContentTranslator::scopeOriginals($query);

        if (! empty($args['status'])) {
            $query->where('status', '=', (string) $args['status']);
        }
        if (! empty($args['country'])) {
            $query->where('country', '=', (string) $args['country']);
        }
        if (isset($args['featured']) && $args['featured'] !== '') {
            $query->where('featured', '=', (int) (bool) $args['featured']);
        }
        if (! empty($args['q'])) {
            // Simple name match (Builder lacks LIKE; use raw helper).
            $query->whereNotNull('name');
        }

        $all = $query->orderBy('ordering', 'ASC')->orderBy('id', 'DESC')->get();

        // Secondary in-PHP filters (term/category/brand) — acceptable at admin scale.
        $term = isset($args['q']) ? strtolower(trim((string) $args['q'])) : '';
        $categoryId = (int) ($args['category'] ?? 0);
        $brandId = (int) ($args['brand'] ?? 0);

        $categoryStoreIds = $categoryId > 0 ? $this->storeIdsForPivot('cliq_sl_store_category', 'category_id', $categoryId) : null;
        $brandStoreIds = $brandId > 0 ? $this->storeIdsForPivot('cliq_sl_brand_store', 'brand_id', $brandId) : null;

        $filtered = [];
        foreach ($all as $store) {
            $id = (int) $store->getKey();
            if ($term !== '' && ! str_contains(strtolower((string) $store->getAttribute('name')), $term)) {
                continue;
            }
            if ($categoryStoreIds !== null && ! isset($categoryStoreIds[$id])) {
                continue;
            }
            if ($brandStoreIds !== null && ! isset($brandStoreIds[$id])) {
                continue;
            }
            $filtered[] = $store;
        }

        $total = count($filtered);
        $pageItems = array_slice($filtered, ($page - 1) * $perPage, $perPage);

        $items = array_map(fn (Store $store): array => $this->summarize($store), $pageItems);
        $items = ContentTranslator::overlay($items, 'cliq_sl_stores', ['name', 'description'], 'id');

        return [
            'items' => $items,
            'total' => $total,
            'page' => $page,
            'per_page' => $perPage,
        ];
    }

    /**
     * Full store record with relations.
     *
     * @return array<string, mixed>|null
     */
    public function find(int $id): ?array
    {
        $store = Store::find($id);
        if (! $store) {
            return null;
        }

        $data = $store->toArray();
        $data['categories'] = $this->pivotIds('cliq_sl_store_category', 'store_id', $id, 'category_id');
        $data['brands'] = $this->pivotIds('cliq_sl_brand_store', 'store_id', $id, 'brand_id');
        $data['locations'] = $this->locationsFor($id);
        $data['meta'] = $this->metaFor($id);

        return $data;
    }

    /**
     * @param array<string, mixed> $data
     *
     * @return array<string, mixed>
     */
    public function create(array $data): array
    {
        // uniqueSlug() is a check-then-insert, so two concurrent creates can
        // pick the same slug; the (slug, lang) unique index then rejects the
        // second insert. Retry a couple of times with a fresh slug to close that
        // race instead of surfacing the collision as an error.
        $attempts = 0;
        while (true) {
            try {
                $created = $this->db()->transaction(function () use ($data): array {
                    $store = Store::create($this->storeColumns($data));
                    $id = (int) $store->getKey();
                    if ($id <= 0) {
                        throw new \RuntimeException('Failed to create store record.');
                    }

                    // Canonical original: its translation-group id is its own id.
                    ContentTranslator::stampOriginalTrid('cliq_sl_stores', $id);

                    $this->persistRelations($id, $data);

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

                return $created;
            } catch (\Throwable $e) {
                if (++$attempts < 3 && $this->isDuplicateEntryError($e)) {
                    $base = sanitize_title((string) ($data['name'] ?? 'store'));
                    $data['slug'] = $this->uniqueSlug($base . '-' . wp_generate_password(5, false));
                    continue;
                }

                throw $e;
            }
        }
    }

    /** Whether a throwable came from a MySQL duplicate-key violation. */
    private function isDuplicateEntryError(\Throwable $e): bool
    {
        return stripos($e->getMessage(), 'Duplicate entry') !== false;
    }

    /**
     * Create or update the translation of an original store for one language.
     * Translation rows carry only translatable text plus their {lang, trid,
     * source_id} identity — all language-neutral data lives on the original.
     *
     * @param array<string, mixed> $data
     *
     * @return array<string, mixed>|null
     */
    public function saveTranslation(int $sourceId, string $lang, array $data): ?array
    {
        $lang = \CliqThemes\StoreLocator\I18n\Multilingual::normalize($lang);
        if ($lang === '') {
            return null;
        }

        $source = Store::find($sourceId);
        if (! $source) {
            return null;
        }

        $existing = ContentTranslator::translationsOf('cliq_sl_stores', $sourceId);
        $fields = [
            'name' => ColumnInput::sanitize('name', (string) ($data['name'] ?? $source->getAttribute('name'))),
            'description' => ColumnInput::sanitize('description', $data['description'] ?? $source->getAttribute('description')),
            'lang' => $lang,
            'trid' => ContentTranslator::tridFor('cliq_sl_stores', $sourceId),
            'source_id' => $sourceId,
            // Slug stays identical to the original (unique is per-slug+lang).
            'slug' => (string) $source->getAttribute('slug'),
            'status' => (string) $source->getAttribute('status'),
        ];

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

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

    /**
     * The translatable text of each existing translation, keyed by language.
     *
     * @return array<string, array<string, mixed>>
     */
    public function translations(int $sourceId): array
    {
        $out = [];
        foreach (ContentTranslator::translationsOf('cliq_sl_stores', $sourceId) as $lang => $id) {
            $row = Store::find($id);
            if ($row) {
                $out[$lang] = [
                    'id' => $id,
                    'name' => $row->getAttribute('name'),
                    'description' => $row->getAttribute('description'),
                ];
            }
        }

        return $out;
    }

    /**
     * @param array<string, mixed> $data
     *
     * @return array<string, mixed>|null
     */
    public function update(int $id, array $data): ?array
    {
        $store = Store::find($id);
        if (! $store) {
            return null;
        }

        $updated = $this->db()->transaction(function () use ($id, $store, $data): ?array {
            $columns = $this->storeColumns($data);
            if ($columns !== []) {
                $store->fill($columns);
                $store->save();
            }

            $this->persistRelations($id, $data, true);

            return $this->find($id);
        });
        FrontendAssetRegistry::invalidate();

        return $updated;
    }

    public function delete(int $id): bool
    {
        $store = Store::find($id);

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

        return $deleted;
    }

    /**
     * Clone a store with its relations under a new slug.
     */
    public function duplicate(int $id): ?array
    {
        $source = $this->find($id);
        if (! $source) {
            return null;
        }

        $copy = $source;
        unset($copy['id'], $copy['created_at'], $copy['updated_at'], $copy['deleted_at']);
        $copy['name'] = ($source['name'] ?? 'Store') . ' (Copy)';
        $copy['slug'] = $this->uniqueSlug(($source['slug'] ?? 'store') . '-copy');
        $copy['status'] = 'draft';

        return $this->create($copy);
    }

    /**
     * @param int[] $ids
     */
    public function bulkDelete(array $ids): int
    {
        $count = 0;
        foreach ($ids as $id) {
            if ($this->delete((int) $id)) {
                $count++;
            }
        }

        return $count;
    }

    /**
     * @param int[] $ids
     */
    public function bulkStatus(array $ids, string $status): int
    {
        $count = 0;
        foreach ($ids as $id) {
            $store = Store::find((int) $id);
            if ($store) {
                $store->setAttribute('status', $status);
                $store->save();
                $count++;
            }
        }

        return $count;
    }

    // ---- Internals -------------------------------------------------------

    /**
     * @param array<string, mixed> $data
     */
    private function persistRelations(int $id, array $data, bool $replace = false): void
    {
        if (array_key_exists('categories', $data)) {
            $this->syncPivot('cliq_sl_store_category', 'store_id', $id, 'category_id', (array) $data['categories']);
        }
        if (array_key_exists('brands', $data)) {
            $this->syncPivot('cliq_sl_brand_store', 'store_id', $id, 'brand_id', (array) $data['brands']);
        }
        if (array_key_exists('locations', $data) && is_array($data['locations'])) {
            $this->syncLocations($id, $data['locations']);
        }
        if (array_key_exists('meta', $data) && is_array($data['meta'])) {
            $this->syncMeta($id, $data['meta']);
        }
    }

    /**
     * @param array<int, array<string, mixed>> $locations
     */
    private function syncLocations(int $storeId, array $locations): void
    {
        global $wpdb;

        // Replace strategy: remove existing locations + their hours, re-insert.
        $existing = $wpdb->get_col($wpdb->prepare(
            "SELECT id FROM {$wpdb->prefix}cliq_sl_store_locations WHERE store_id = %d",
            $storeId
        ));
        foreach ($existing as $locId) {
            $this->db()->delete('cliq_sl_store_hours', ['location_id' => (int) $locId], ['%d']);
        }
        $this->db()->delete('cliq_sl_store_locations', ['store_id' => $storeId], ['%d']);

        $now = current_time('mysql');
        $hasPrimary = false;

        foreach ($locations as $index => $loc) {
            $isPrimary = ! empty($loc['is_primary']) || (! $hasPrimary && $index === 0);
            $hasPrimary = $hasPrimary || $isPrimary;

            $location = Location::create([
                'store_id' => $storeId,
                'label' => ColumnInput::sanitize('label', $loc['label'] ?? 'Main'),
                'address_line1' => ColumnInput::sanitize('address_line1', $loc['address_line1'] ?? ''),
                'address_line2' => ColumnInput::sanitize('address_line2', $loc['address_line2'] ?? ''),
                'city' => ColumnInput::sanitize('city', $loc['city'] ?? ''),
                'state' => ColumnInput::sanitize('state', $loc['state'] ?? ''),
                'postal_code' => ColumnInput::sanitize('postal_code', $loc['postal_code'] ?? ''),
                'country' => ColumnInput::sanitize('country', $loc['country'] ?? ''),
                'latitude' => ColumnInput::sanitize('latitude', $loc['latitude'] ?? 0),
                'longitude' => ColumnInput::sanitize('longitude', $loc['longitude'] ?? 0),
                'is_primary' => $isPrimary ? 1 : 0,
            ]);

            if (! empty($loc['hours']) && is_array($loc['hours'])) {
                $this->insertHours((int) $location->getKey(), $loc['hours'], $now);
            }
        }
    }

    /**
     * Convert a HoursField value object into store_hours rows.
     *
     * @param array<string, mixed> $hours
     */
    private function insertHours(int $locationId, array $hours, string $now): void
    {
        foreach (self::DAY_MAP as $dayKey => $dow) {
            $day = $hours[$dayKey] ?? null;
            if (! is_array($day)) {
                continue;
            }

            if (! empty($day['closed'])) {
                $this->db()->insert('cliq_sl_store_hours', [
                    'location_id' => $locationId,
                    'day_of_week' => $dow,
                    'open_time' => null,
                    'close_time' => null,
                    'is_closed' => 1,
                ], ['%d', '%d', '%s', '%s', '%d']);
                continue;
            }

            if (! empty($day['open24'])) {
                $this->db()->insert('cliq_sl_store_hours', [
                    'location_id' => $locationId,
                    'day_of_week' => $dow,
                    'open_time' => '00:00',
                    'close_time' => '23:59',
                    'is_closed' => 0,
                ], ['%d', '%d', '%s', '%s', '%d']);
                continue;
            }

            $slots = is_array($day['slots'] ?? null) ? $day['slots'] : [];
            foreach ($slots as $slot) {
                $this->db()->insert('cliq_sl_store_hours', [
                    'location_id' => $locationId,
                    'day_of_week' => $dow,
                    'open_time' => substr((string) ($slot['from'] ?? ''), 0, 5),
                    'close_time' => substr((string) ($slot['to'] ?? ''), 0, 5),
                    'is_closed' => 0,
                ], ['%d', '%d', '%s', '%s', '%d']);
            }
        }
    }

    /**
     * @param array<string, mixed> $meta
     */
    /**
     * The registered type of a store custom field, or '' when it has none.
     *
     * Resolved once per request: a store save writes many meta rows and the
     * field registry does not change between them.
     */
    private static function metaFieldType(string $key): string
    {
        static $types = null;
        if (! is_array($types)) {
            $types = [];
            if (class_exists(CustomFieldsRepository::class)) {
                foreach ((new CustomFieldsRepository())->forEntity('store') as $field) {
                    $fieldKey = sanitize_key((string) ($field['field_key'] ?? ''));
                    if ($fieldKey !== '') {
                        $types[$fieldKey] = (string) ($field['type'] ?? '');
                    }
                }
            }
        }

        return $types[$key] ?? '';
    }

    private function syncMeta(int $storeId, array $meta): void
    {
        global $wpdb;
        $table = $wpdb->prefix . 'cliq_sl_store_meta';

        foreach ($meta as $key => $value) {
            $key = sanitize_key((string) $key);
            if ($key === '') {
                continue;
            }

            // A multi-value field (checkboxes) becomes one row per selection so a
            // facet can match a single value in SQL. Everything else keeps its
            // existing shape: scalars as-is, structured arrays serialised whole.
            // A value means whatever its field's type says it means, so that is
            // what decides the sanitising — a URL field must not keep a
            // javascript: value just because it contains no markup.
            $type = self::metaFieldType($key);
            $values = CustomFieldsRepository::isMultiValueKey($key) && is_array($value) && array_is_list($value)
                ? array_values(array_filter(
                    array_map(
                        static fn ($v): string => is_scalar($v) ? (string) ColumnInput::sanitizeByFieldType($type, (string) $v) : '',
                        $value
                    ),
                    static fn (string $v): bool => $v !== ''
                ))
                : [
                    is_array($value) || is_object($value)
                        ? maybe_serialize(ColumnInput::sanitize('meta', $value))
                        : (string) ColumnInput::sanitizeByFieldType($type, (string) $value),
                ];

            // Replace rather than upsert: (store_id, meta_key) is deliberately
            // not unique, so ON DUPLICATE KEY UPDATE has nothing to key on and
            // would append. The caller wraps this in a transaction, which is what
            // makes the delete-then-insert race-safe (as update_post_meta does).
            $this->db()->statement(
                "DELETE FROM {$table} WHERE store_id = %d AND meta_key = %s",
                [$storeId, $key]
            );

            foreach ($values as $single) {
                $this->db()->statement(
                    "INSERT INTO {$table} (store_id, meta_key, meta_value) VALUES (%d, %s, %s)",
                    [$storeId, $key, $single]
                );
            }
        }
    }

    /**
     * @param array<string, mixed> $data
     *
     * @return array<string, mixed>
     */
    private function storeColumns(array $data): array
    {
        $columns = [];
        // NB: marker_source was previously missing here — the "Inherit from
        // category" vs "Custom marker" toggle silently failed to persist.
        foreach (['name', 'slug', 'description', 'status', 'featured', 'ordering', 'lang', 'phone', 'email', 'website', 'rating', 'rating_count', 'city', 'country', 'logo_id', 'marker_id', 'marker_design_id', 'marker_source', 'owner_id'] as $key) {
            if (array_key_exists($key, $data)) {
                $columns[$key] = ColumnInput::sanitize($key, $data[$key]);
            }
        }

        if (! empty($data['name']) && empty($columns['slug']) && empty($data['slug'])) {
            $columns['slug'] = $this->uniqueSlug(sanitize_title((string) $data['name']));
        }

        return $columns;
    }

    private function uniqueSlug(string $base): string
    {
        global $wpdb;
        $base = $base !== '' ? $base : 'store';
        $slug = $base;
        $i = 2;

        while ((int) $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM {$wpdb->prefix}cliq_sl_stores WHERE slug = %s AND source_id IS NULL", $slug)) > 0) {
            $slug = $base . '-' . $i++;
        }

        return $slug;
    }

    /**
     * @return array<string, mixed>
     */
    private function summarize(Store $store): array
    {
        $id = (int) $store->getKey();
        $primary = $this->primaryLocation($id);

        return [
            'id' => $id,
            'name' => $store->getAttribute('name'),
            'slug' => $store->getAttribute('slug'),
            'status' => $store->getAttribute('status'),
            'featured' => (bool) $store->getAttribute('featured'),
            'city' => $store->getAttribute('city'),
            'country' => $store->getAttribute('country'),
            'categories' => $this->pivotIds('cliq_sl_store_category', 'store_id', $id, 'category_id'),
            'location' => $primary,
        ];
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    private function locationsFor(int $storeId): array
    {
        $rows = Location::query()->where('store_id', '=', $storeId)->raw();

        return array_map(function (array $row): array {
            $row['hours'] = $this->hoursFor((int) $row['id']);

            return $row;
        }, $rows);
    }

    /**
     * @return array<string, mixed>|null
     */
    private function primaryLocation(int $storeId): ?array
    {
        $rows = Location::query()->where('store_id', '=', $storeId)->where('is_primary', '=', 1)->raw();

        return $rows[0] ?? null;
    }

    /**
     * Reconstruct a HoursField-style value object from store_hours rows.
     *
     * @return array<string, mixed>
     */
    private function hoursFor(int $locationId): array
    {
        global $wpdb;
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT day_of_week, open_time, close_time, is_closed FROM {$wpdb->prefix}cliq_sl_store_hours WHERE location_id = %d ORDER BY day_of_week, open_time",
            $locationId
        ), ARRAY_A) ?: [];

        $byDow = array_flip(self::DAY_MAP); // 0=>sun, 1=>mon...
        $hours = [];

        foreach ($rows as $row) {
            $dayKey = $byDow[(int) $row['day_of_week']] ?? null;
            if ($dayKey === null) {
                continue;
            }
            if (! isset($hours[$dayKey])) {
                $hours[$dayKey] = ['closed' => true, 'open24' => false, 'slots' => []];
            }
            if ((int) $row['is_closed'] === 1) {
                continue;
            }
            $hours[$dayKey]['closed'] = false;
            $hours[$dayKey]['slots'][] = ['from' => $row['open_time'], 'to' => $row['close_time']];
        }

        return $hours;
    }

    /**
     * @return array<string, mixed>
     */
    private function metaFor(int $storeId): array
    {
        global $wpdb;
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT meta_key, meta_value FROM {$wpdb->prefix}cliq_sl_store_meta WHERE store_id = %d",
            $storeId
        ), ARRAY_A) ?: [];

        // A multi-value field (checkboxes) is several rows sharing one key, so
        // group before unwrapping — assigning straight into $meta[$key] would let
        // the last row win and silently drop the other selections.
        $grouped = [];
        foreach ($rows as $row) {
            $grouped[$row['meta_key']][] = $row['meta_value'];
        }

        $meta = [];
        foreach ($grouped as $key => $values) {
            $meta[$key] = count($values) > 1
                ? array_map([StoredValue::class, 'decode'], $values)
                : StoredValue::decode($values[0]);
        }

        return $meta;
    }

    /**
     * @return int[]
     */
    private function pivotIds(string $table, string $keyColumn, int $keyValue, string $valueColumn): array
    {
        global $wpdb;
        $prefixed = SchemaInstaller::qualify($table);
        // $table/$keyColumn/$valueColumn are fixed identifiers passed only from
        // internal callers (never request data); the value is bound via prepare().
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $ids = $wpdb->get_col($wpdb->prepare(
            'SELECT %i FROM %i WHERE %i = %d',
            $valueColumn,
            $prefixed,
            $keyColumn,
            $keyValue
        ));

        return array_map('intval', $ids);
    }

    /**
     * @param int[] $values
     */
    private function syncPivot(string $table, string $keyColumn, int $keyValue, string $valueColumn, array $values): void
    {
        $this->db()->delete($table, [$keyColumn => $keyValue], ['%d']);

        foreach (array_unique(array_map('intval', $values)) as $value) {
            if ($value > 0) {
                $this->db()->insert($table, [$keyColumn => $keyValue, $valueColumn => $value], ['%d', '%d']);
            }
        }
    }

    /**
     * @return array<int, true>
     */
    private function storeIdsForPivot(string $table, string $column, int $value): array
    {
        global $wpdb;
        $prefixed = SchemaInstaller::qualify($table);
        $ids = $wpdb->get_col($wpdb->prepare('SELECT store_id FROM %i WHERE %i = %d', $prefixed, $column, $value));

        return array_fill_keys(array_map('intval', $ids), true);
    }
}
