<?php

declare(strict_types=1);

namespace CliqThemes\StoreLocator\Support;

defined('ABSPATH') || exit;

// Batched reads target plugin-owned custom tables and avoid per-store queries.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

use CliqThemes\StoreLocator\Frontend\FrontendModule;
use CliqThemes\StoreLocator\I18n\ContentTranslator;
use CliqThemes\StoreLocator\Markers\Markers;
use CliqThemes\StoreLocator\Models\Location;
use CliqThemes\StoreLocator\Models\Store;

/**
 * Turns Store models (+ their relations) into the array shape the public REST
 * surface returns. Extracted from PublicController so serialization is a single
 * responsibility, independent of routing.
 *
 * For a result page, call {@see prefetch()} once with all store ids before
 * {@see format()}-ing each store: it loads meta, categories and hours in a
 * fixed number of queries and the format helpers read those caches instead of
 * querying per store (eliminating the N+1). For a single store, skip prefetch
 * and the helpers fall back to per-store queries.
 */
final class StorePayloadFormatter
{
    /**
     * Per-store relation prefetch caches. Null means "not prefetched" — the
     * format helpers then query per store (the single-store payload path).
     *
     * @var array<int, array<int, array<string, mixed>>>|null meta rows by store
     */
    private ?array $metaCache = null;
    /** @var array<int, array<int, array<string, mixed>>>|null category rows by store */
    private ?array $catCache = null;
    /** @var array<int, array<int, array<string, mixed>>>|null primary-location hours by store */
    private ?array $hoursCache = null;
    /** @var array<int, array<string, mixed>|null>|null primary brand by store */
    private ?array $brandCache = null;

    /** @var array<string, bool>|null custom-field keys this site publishes */
    private ?array $publicMetaKeys = null;

    /**
     * Pre-load every per-store relation a result page needs — meta, categories
     * (with the columns marker inheritance needs) — in a fixed number of
     * queries, eliminating the ~5-per-store N+1 in the result loop.
     * Populates the *Cache properties consumed by the format helpers.
     *
     * @param int[] $storeIds
     */
    public function prefetch(array $storeIds): void
    {
        global $wpdb;

        $storeIds = array_values(array_unique(array_map('intval', $storeIds)));

        // Initialise the caches (non-null marks the format helpers as "batched").
        $this->metaCache = array_fill_keys($storeIds, []);
        $this->catCache = array_fill_keys($storeIds, []);

        // The marker source batches whatever answering these stores needs.
        Markers::source()->prefetch($storeIds);

        if ($storeIds === []) {
            return;
        }

        $placeholders = implode(',', array_fill(0, count($storeIds), '%d'));

        // Meta rows grouped by store.
        $metaTable = $wpdb->prefix . 'cliq_sl_store_meta';
        $query = "SELECT store_id, meta_key, meta_value FROM %i WHERE store_id IN ({$placeholders})";
        // The IN placeholders are generated above and every id is bound.
        // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQL.NotPrepared
        $rows = $wpdb->get_results($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Generated placeholders are bound immediately below.
            $query,
            ...array_merge([$metaTable], $storeIds)
        ), ARRAY_A) ?: [];
        foreach ($rows as $row) {
            $this->metaCache[(int) $row['store_id']][] = $row;
        }

        // Category rows grouped by store, pre-ordered so a consumer can take the
        // first marker-bearing category without re-sorting.
        $pivot = $wpdb->prefix . 'cliq_sl_store_category';
        $categories = $wpdb->prefix . 'cliq_sl_categories';
        $query = "SELECT p.store_id, c.id, c.name, c.slug, c.color, c.marker_id, c.marker_design_id, c.ordering
             FROM %i c INNER JOIN %i p ON p.category_id = c.id
             WHERE p.store_id IN ({$placeholders})
             ORDER BY c.ordering ASC, c.id ASC";
        // The IN placeholders are generated above and every id is bound.
        // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQL.NotPrepared
        $rows = $wpdb->get_results($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Generated placeholders are bound immediately below.
            $query,
            ...array_merge([$categories, $pivot], $storeIds)
        ), ARRAY_A) ?: [];
        foreach ($rows as $row) {
            $this->catCache[(int) $row['store_id']][] = $row;
        }


        // Opening hours for each store's PRIMARY location — so result cards / map
        // infoboxes can show an accurate Open/Closed badge (single query, batched).
        $this->hoursCache = array_fill_keys($storeIds, []);
        $locations = $wpdb->prefix . 'cliq_sl_store_locations';
        $hoursTable = $wpdb->prefix . 'cliq_sl_store_hours';
        $query = "SELECT l.store_id, h.day_of_week, h.open_time, h.close_time, h.is_closed
             FROM %i h INNER JOIN %i l ON l.id = h.location_id
             WHERE l.store_id IN ({$placeholders}) AND l.is_primary = 1
             ORDER BY h.day_of_week ASC";
        // The IN placeholders are generated above and every id is bound.
        // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQL.NotPrepared
        $rows = $wpdb->get_results($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Generated placeholders are bound immediately below.
            $query,
            ...array_merge([$hoursTable, $locations], $storeIds)
        ), ARRAY_A) ?: [];
        foreach ($rows as $row) {
            $this->hoursCache[(int) $row['store_id']][] = [
                'day' => (int) $row['day_of_week'],
                'open' => $row['open_time'],
                'close' => $row['close_time'],
                'closed' => (bool) $row['is_closed'],
            ];
        }

        // Primary brand per store (lowest brand id — brand_store has no ordering
        // column, so a store's "card brand" among several is deterministic but
        // otherwise arbitrary; keep only the first row seen per store below).
        $this->brandCache = array_fill_keys($storeIds, null);
        $brands = $wpdb->prefix . 'cliq_sl_brands';
        $brandPivot = $wpdb->prefix . 'cliq_sl_brand_store';
        $query = "SELECT p.store_id, b.id, b.name, b.slug, b.logo
             FROM %i b INNER JOIN %i p ON p.brand_id = b.id
             WHERE p.store_id IN ({$placeholders})
             ORDER BY b.id ASC";
        // The IN placeholders are generated above and every id is bound.
        // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQL.NotPrepared
        $rows = $wpdb->get_results($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Generated placeholders are bound immediately below.
            $query,
            ...array_merge([$brands, $brandPivot], $storeIds)
        ), ARRAY_A) ?: [];
        foreach ($rows as $row) {
            $sid = (int) $row['store_id'];
            if ($this->brandCache[$sid] === null) {
                $this->brandCache[$sid] = [
                    'id' => (int) $row['id'],
                    'name' => $row['name'],
                    'slug' => $row['slug'],
                    'logo' => $row['logo'] ? esc_url_raw((string) $row['logo']) : null,
                ];
            }
        }
    }

    /**
     * @param array<string, mixed>|null $location
     *
     * @return array<string, mixed>
     */
    public function format(Store $store, ?array $location, ?float $distance): array
    {
        $meta = $this->metaFor((int) $store->getKey());
        $routeSlug = StoreDetailUrl::slug(
            [
                'name' => $store->getAttribute('name'),
                'slug' => $store->getAttribute('slug'),
                'city' => $store->getAttribute('city'),
                'country' => $store->getAttribute('country'),
            ],
            $location
        );

        $formatted = [
            'id' => (int) $store->getKey(),
            'name' => $store->getAttribute('name'),
            'slug' => $store->getAttribute('slug'),
            // Permalink to the single-store page (empty when the feature is off) so
            // a "Details" button can link straight there.
            'detail_url' => FrontendModule::storePermalink($routeSlug),
            'description' => $store->getAttribute('description'),
            'phone' => $store->getAttribute('phone'),
            'email' => $store->getAttribute('email'),
            'website' => $store->getAttribute('website'),
            'rating' => $this->numOrNull($store->getAttribute('rating')),
            'rating_count' => $this->intOrNull($store->getAttribute('rating_count')),
            'featured' => (bool) $store->getAttribute('featured'),
            'status' => $store->getAttribute('status'),
            'city' => $store->getAttribute('city'),
            'country' => $store->getAttribute('country'),
            'distance' => $distance,
            'hero_media' => DemoHero::resolve(
                $this->heroMediaUrl((int) $store->getKey()),
                $meta
            ),
            'marker' => Markers::source()->imageFor($store),
            'marker_design_id' => ($designId = Markers::source()->designIdFor($store)) > 0 ? $designId : null,
            'location' => $location ? $this->formatLocation($location) : null,
            'categories' => $this->categoriesForStore((int) $store->getKey()),
            'brand' => $this->brandFor((int) $store->getKey()),
            // Only the custom fields the site's layouts display: this payload
            // is unauthenticated and not every field is meant for visitors.
            // `$meta` itself stays complete — the schedule and hero lookups
            // below read keys no layout has to reference.
            'meta' => $this->publicMeta($meta),
        ];

        // The admin Hours field is stored in store_meta. It is the current source
        // of truth; relational store_hours remains a fallback for imports and
        // older records. Previously the empty relation overwrote valid saved meta.
        $schedule = self::decodeOpeningHours($meta['opening_hours'] ?? null);
        $relationalHours = $this->hoursForStore((int) $store->getKey());
        $hours = $schedule !== [] ? self::openingHoursRows($schedule) : $relationalHours;
        $formatted['hours'] = $hours;
        $formatted['opening_hours'] = $schedule !== [] ? $schedule : $relationalHours;
        $formatted['open_now'] = self::isOpenNow($hours);

        // The demo marker exists only to select the bundled hero fallback; it
        // is not a public custom field or a frontend merge tag.
        unset($formatted['meta'][DemoHero::META_KEY]);

        $formatted = ContentTranslator::overlayOne($formatted, 'cliq_sl_stores', ['name', 'description'], 'id');

        /**
         * Filter a single store's serialized payload (search results + map markers).
         * Add or reshape fields an integration needs on the frontend.
         *
         * @param array<string, mixed> $formatted The serialized store.
         * @param Store                $store     The store model.
         * @param float|null           $distance  Distance from the search origin, if any.
         */
        return apply_filters('cliq_sl_store_payload', $formatted, $store, $distance);
    }

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

        return $rows[0] ?? null;
    }

    /** Cast a stored decimal rating to a float, or null when empty. */
    private function numOrNull($value): ?float
    {
        return ($value === null || $value === '') ? null : (float) $value;
    }

    /** Cast a stored integer to int, or null when empty. */
    private function intOrNull($value): ?int
    {
        return ($value === null || $value === '') ? null : (int) $value;
    }

    /**
     * Primary-location hours for a store — from the batched cache on a result page,
     * else a per-store query (single-store path).
     *
     * @return array<int, array<string, mixed>>
     */
    public function hoursForStore(int $storeId): array
    {
        if ($this->hoursCache !== null) {
            return $this->hoursCache[$storeId] ?? [];
        }
        $loc = $this->primaryLocation($storeId);

        return $loc ? $this->hoursForLocation((int) $loc['id']) : [];
    }

    /**
     * Is the store open right now, given its hours rows? Uses the SITE timezone
     * (wp_date) and handles overnight ranges (e.g. 18:00–02:00). Returns null when
     * there are no hours for today (unknown — neither "open" nor "closed").
     *
     * @param array<int, array<string, mixed>> $hours
     */
    public static function isOpenNow(array $hours): ?bool
    {
        if ($hours === []) {
            return null;
        }
        $dow = (int) wp_date('w');
        $now = (string) wp_date('H:i');

        $todayRows = array_filter($hours, static fn (array $h): bool => (int) ($h['day'] ?? -1) === $dow);
        if ($todayRows === []) {
            return null;
        }
        foreach ($todayRows as $row) {
            if (! empty($row['closed'])) {
                continue;
            }
            $open = substr((string) ($row['open'] ?? ''), 0, 5);
            $close = substr((string) ($row['close'] ?? ''), 0, 5);
            if ($open === '' || $close === '') {
                continue;
            }
            $isOpen = $open <= $close
                ? ($now >= $open && $now <= $close)              // same-day range
                : ($now >= $open || $now <= $close);             // overnight range
            if ($isOpen) {
                return true;
            }
        }

        return false;
    }

    /** Decode the Hours field's serialized/JSON/keyed value into a day map. */
    public static function decodeOpeningHours($value): array
    {
        if (is_string($value)) {
            $value = StoredValue::decode($value);
            if (is_string($value)) {
                $decoded = json_decode($value, true);
                $value = is_array($decoded) ? $decoded : [];
            }
        }

        return is_array($value) ? $value : [];
    }

    /** Convert a keyed Hours field schedule to the row shape open-state logic uses. */
    public static function openingHoursRows(array $schedule): array
    {
        $days = ['sun' => 0, 'mon' => 1, 'tue' => 2, 'wed' => 3, 'thu' => 4, 'fri' => 5, 'sat' => 6];
        $rows = [];
        foreach ($days as $key => $day) {
            $entry = $schedule[$key] ?? null;
            if (! is_array($entry)) {
                continue;
            }
            if (! empty($entry['closed'])) {
                $rows[] = ['day' => $day, 'open' => null, 'close' => null, 'closed' => true];
                continue;
            }
            if (! empty($entry['open24'])) {
                $rows[] = ['day' => $day, 'open' => '00:00', 'close' => '23:59', 'closed' => false];
                continue;
            }
            foreach ((is_array($entry['slots'] ?? null) ? $entry['slots'] : []) as $slot) {
                if (! is_array($slot)) {
                    continue;
                }
                $open = substr((string) ($slot['from'] ?? ''), 0, 5);
                $close = substr((string) ($slot['to'] ?? ''), 0, 5);
                if ($open !== '' && $close !== '') {
                    $rows[] = ['day' => $day, 'open' => $open, 'close' => $close, 'closed' => false];
                }
            }
        }

        return $rows;
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    public function hoursForLocation(int $locationId): array
    {
        global $wpdb;
        $table = $wpdb->prefix . 'cliq_sl_store_hours';
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT day_of_week, open_time, close_time, is_closed FROM %i WHERE location_id = %d ORDER BY day_of_week ASC',
                $table,
                $locationId
            ),
            ARRAY_A
        ) ?: [];

        return array_map(static function (array $row): array {
            return [
                'day' => (int) $row['day_of_week'],
                'open' => $row['open_time'],
                'close' => $row['close_time'],
                'closed' => (bool) $row['is_closed'],
            ];
        }, $rows);
    }

    /**
     * @param array<string, mixed> $row
     *
     * @return array<string, mixed>
     */
    public function formatLocation(array $row): array
    {
        return [
            'id' => (int) $row['id'],
            'label' => $row['label'] ?? null,
            'address_line1' => $row['address_line1'] ?? null,
            'address_line2' => $row['address_line2'] ?? null,
            'city' => $row['city'] ?? null,
            'state' => $row['state'] ?? null,
            'postal_code' => $row['postal_code'] ?? null,
            'country' => $row['country'] ?? null,
            'latitude' => isset($row['latitude']) ? (float) $row['latitude'] : null,
            'longitude' => isset($row['longitude']) ? (float) $row['longitude'] : null,
            'is_primary' => (bool) ($row['is_primary'] ?? false),
        ];
    }

    /**
     * The subset of a store's custom fields that may leave the server.
     *
     * {@see FrontendAssetRegistry::metaKeys()} resolves the allowlist from the
     * saved designs. Resolved once per request.
     *
     * @param array<string, mixed> $meta
     *
     * @return array<string, mixed>
     */
    private function publicMeta(array $meta): array
    {
        $this->publicMetaKeys ??= FrontendAssetRegistry::metaKeys();

        return array_intersect_key($meta, $this->publicMetaKeys);
    }

    /**
     * All custom (store_meta) values for a store, so layout designs can render
     * custom-field tokens on the frontend.
     *
     * @return array<string, string>
     */
    private function metaFor(int $storeId): array
    {
        if ($this->metaCache !== null) {
            $rows = $this->metaCache[$storeId] ?? [];
        } else {
            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. Group
        // first so those can be collected into a list instead of the last row
        // silently winning.
        $grouped = [];
        foreach ($rows as $row) {
            $grouped[$row['meta_key']][] = $row['meta_value'];
        }

        $out = [];
        foreach ($grouped as $metaKey => $rawValues) {
            if (count($rawValues) > 1) {
                // Emitted as a JSON list — byte-identical to what a serialised
                // multi-value row already produced, so the token renderer and its
                // designRender.js twin need no knowledge of the storage change.
                $out[$metaKey] = wp_json_encode(array_values(array_map(
                    static function ($raw) {
                        $value = StoredValue::decode($raw);

                        return is_scalar($value) ? (string) $value : $value;
                    },
                    $rawValues
                )));
                continue;
            }

            $value = StoredValue::decode($rawValues[0]);
            if (is_scalar($value)) {
                $out[$metaKey] = (string) $value;
            } elseif (is_array($value) && array_is_list($value)) {
                // A list of items (e.g. gallery images, events) — keep the full
                // structure as JSON so designs that iterate it (the gallery
                // token) receive every item, not a flattened url. Single media
                // objects fall through to the url branch below.
                $out[$metaKey] = wp_json_encode($value);
            } elseif (is_array($value)) {
                // A media object resolves to its url; any other structured value
                // (e.g. an Opening Hours schedule) is preserved as JSON so the token
                // renderer can format it (it is NOT a media object).
                $out[$metaKey] = (isset($value['url']) || isset($value['source_url']))
                    ? (string) ($value['url'] ?? $value['source_url'] ?? '')
                    : wp_json_encode($value);
            } else {
                $out[$metaKey] = '';
            }
        }

        return $out;
    }






    /**
     * Resolve a store's hero image to a plain URL from store_meta. The value is a
     * maybe_serialized media object ({id,url,…}) or a bare URL string.
     */
    private function heroMediaUrl(int $storeId): ?string
    {
        if ($this->metaCache !== null) {
            $raw = null;
            foreach ($this->metaCache[$storeId] ?? [] as $row) {
                if ($row['meta_key'] === 'hero_media') {
                    $raw = $row['meta_value'];
                    break;
                }
            }
        } else {
            global $wpdb;
            $raw = $wpdb->get_var($wpdb->prepare(
                "SELECT meta_value FROM {$wpdb->prefix}cliq_sl_store_meta WHERE store_id = %d AND meta_key = 'hero_media' LIMIT 1",
                $storeId
            ));
        }

        if ($raw === null || $raw === '') {
            return null;
        }

        $value = StoredValue::decode($raw);

        if (is_array($value)) {
            $url = $value['url'] ?? ($value['source_url'] ?? '');
            return $url !== '' ? esc_url_raw((string) $url) : null;
        }

        return is_string($value) && $value !== '' ? esc_url_raw($value) : null;
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    private function categoriesForStore(int $storeId): array
    {
        if ($this->catCache !== null) {
            $rows = $this->catCache[$storeId] ?? [];
        } else {
            global $wpdb;
            $pivot = $wpdb->prefix . 'cliq_sl_store_category';
            $categories = $wpdb->prefix . 'cliq_sl_categories';
            $rows = $wpdb->get_results(
                $wpdb->prepare(
                    'SELECT c.id, c.name, c.slug, c.color FROM %i c INNER JOIN %i p ON p.category_id = c.id WHERE p.store_id = %d',
                    $categories,
                    $pivot,
                    $storeId
                ),
                ARRAY_A
            ) ?: [];
        }

        $cats = array_map(static function (array $row): array {
            return [
                'id' => (int) $row['id'],
                'name' => $row['name'],
                'slug' => $row['slug'],
                'color' => $row['color'] ?? null,
            ];
        }, $rows);

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

    /**
     * A store's primary brand ({id, name, slug, logo}) — the lowest-id brand
     * among any it's linked to via brand_store — or null when it has none. From
     * the batched cache on a result page, else a per-store query.
     *
     * @return array<string, mixed>|null
     */
    private function brandFor(int $storeId): ?array
    {
        if ($this->brandCache !== null) {
            return $this->brandCache[$storeId] ?? null;
        }

        global $wpdb;
        $row = $wpdb->get_row($wpdb->prepare(
            "SELECT b.id, b.name, b.slug, b.logo
             FROM {$wpdb->prefix}cliq_sl_brands b
             INNER JOIN {$wpdb->prefix}cliq_sl_brand_store p ON p.brand_id = b.id
             WHERE p.store_id = %d ORDER BY b.id ASC LIMIT 1",
            $storeId
        ), ARRAY_A);

        if (! $row) {
            return null;
        }

        return [
            'id' => (int) $row['id'],
            'name' => $row['name'],
            'slug' => $row['slug'],
            'logo' => $row['logo'] ? esc_url_raw((string) $row['logo']) : null,
        ];
    }
}
