<?php

declare(strict_types=1);

namespace CliqThemes\StoreLocator\Repository;

defined('ABSPATH') || exit;

use CliqThemes\StoreLocator\Support\StorePayloadFormatter;
use CliqThemes\StoreLocator\Schema\SchemaInstaller;

// Reads target plugin-owned custom tables; WordPress has no higher-level API
// for these queries and request results must reflect current mutable records.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
// `meta_key` is indexed in the plugin table; value comparisons are intentional filters.
// phpcs:disable WordPress.DB.SlowDBQuery.slow_db_query_meta_key, WordPress.DB.SlowDBQuery.slow_db_query_meta_value

/**
 * Read-side queries for the public locator: the store-id filter sets a search
 * intersects (category / brand / name / custom-field) and category store counts.
 * Extracted from PublicController so the controller orchestrates rather than
 * builds SQL. Each method returns plain data; no per-request state is held.
 */
final class StoreReadRepository
{
    /**
     * Return the supplied store ids ordered by display name, with id as a
     * deterministic tie-breaker. Used by the public sort control before paging.
     *
     * @param int[] $storeIds
     * @return int[]
     */
    public function orderStoreIdsByName(array $storeIds, int $limit = 0, int $offset = 0): array
    {
        global $wpdb;
        $ids = array_values(array_unique(array_filter(array_map('intval', $storeIds), static fn (int $id): bool => $id > 0)));
        if ($ids === []) {
            return [];
        }

        $table = $wpdb->prefix . 'cliq_sl_stores';
        $placeholders = implode(',', array_fill(0, count($ids), '%d'));
        // Ordering AND slicing in SQL: the caller wants one page, so returning
        // every id would make the database sort a set the request then throws
        // away. `$limit` of 0 keeps the previous "give me all of them" contract.
        // Two literal statements rather than one assembled from a variable —
        // only the generated placeholder list is interpolated, and every value
        // is bound.
        $args = array_merge([$table], $ids);
        if ($limit > 0) {
            $args[] = $limit;
            $args[] = max(0, $offset);
            // Only the generated placeholder list is interpolated; every value is bound.
            // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $sql = "SELECT id FROM %i WHERE id IN ({$placeholders}) ORDER BY name ASC, id ASC LIMIT %d OFFSET %d";
        } else {
            // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $sql = "SELECT id FROM %i WHERE id IN ({$placeholders}) ORDER BY name ASC, id ASC";
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $ordered = $wpdb->get_col($wpdb->prepare($sql, ...$args));

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

    /**
     * Store ids linked to a pivot row (e.g. store_category, brand_store). A
     * single id keeps the original `= %d` query; an array of ids (the
     * frontend's optional multi-select category filter) OR-matches any of
     * them via `IN (...)`.
     *
     * @param int|array<int, int> $value
     *
     * @return array<int, true> store_id => true
     */
    public function storeIdsForPivot(string $table, string $column, $value): array
    {
        global $wpdb;
        $prefixed = SchemaInstaller::qualify($table);

        if (is_array($value)) {
            $ids = array_values(array_unique(array_filter(array_map('intval', $value), static fn (int $id): bool => $id > 0)));
            if ($ids === []) {
                return [];
            }
            $placeholders = implode(',', array_fill(0, count($ids), '%d'));
            $query = "SELECT store_id FROM %i WHERE %i IN ({$placeholders})";
            // The IN placeholders are generated above and every value is bound.
            // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQL.NotPrepared
            $rows = $wpdb->get_col($wpdb->prepare($query, ...array_merge([$prefixed, $column], $ids)));

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

        $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);
    }

    /**
     * Store ids whose name matches a search term.
     *
     * @return array<int, true> store_id => true
     */
    public function storeIdsForTerm(string $term): array
    {
        global $wpdb;
        $stores = $wpdb->prefix . 'cliq_sl_stores';
        $like = '%' . $wpdb->esc_like($term) . '%';
        $ids = $wpdb->get_col($wpdb->prepare('SELECT id FROM %i WHERE name LIKE %s AND deleted_at IS NULL', $stores, $like));

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

    /**
     * Stores whose custom field (store_meta key) matches the given value.
     *
     * @return array<int, true> store_id => true
     */
    public function storeIdsForMeta(string $key, string $value, string $op = 'LIKE'): array
    {
        global $wpdb;
        $table = $wpdb->prefix . 'cliq_sl_store_meta';

        if ($op === 'IN') {
            $values = array_values(array_filter(array_map('trim', explode(',', $value)), static fn ($v) => $v !== ''));
            if ($values === []) {
                return [];
            }
            $placeholders = implode(',', array_fill(0, count($values), '%s'));
            $sql = "SELECT store_id FROM %i WHERE meta_key = %s AND meta_value IN ({$placeholders})";
            // The IN placeholders are generated above and every value is bound.
            // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQL.NotPrepared
            $ids = $wpdb->get_col($wpdb->prepare($sql, array_merge([$table, $key], $values)));
        } elseif ($op === '=') {
            $ids = $wpdb->get_col($wpdb->prepare(
                'SELECT store_id FROM %i WHERE meta_key = %s AND meta_value = %s',
                $table,
                $key,
                $value
            ));
        } elseif ($op === '>=' || $op === '<=') {
            // Numeric comparison (rating/price/capacity-style custom fields) —
            // meta_value is stored as text, so a plain string >=/<= would sort
            // "9" after "10" lexicographically; cast both sides to compare as
            // numbers instead. Keep separate static statements so no SQL
            // operator is interpolated, even though $op is already allowlisted.
            $sql = $op === '>='
                ? 'SELECT store_id FROM %i WHERE meta_key = %s AND CAST(meta_value AS DECIMAL(20,4)) >= %f'
                : 'SELECT store_id FROM %i WHERE meta_key = %s AND CAST(meta_value AS DECIMAL(20,4)) <= %f';
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Both possible statements are static and fully prepared.
            $ids = $wpdb->get_col($wpdb->prepare($sql, $table, $key, (float) $value));
        } else { // LIKE (default)
            $ids = $wpdb->get_col($wpdb->prepare(
                'SELECT store_id FROM %i WHERE meta_key = %s AND meta_value LIKE %s',
                $table,
                $key,
                '%' . $wpdb->esc_like($value) . '%'
            ));
        }

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

    /**
     * Store ids open RIGHT NOW — primary location, today's hours, current time in
     * the SITE timezone (wp_date). Handles overnight ranges (e.g. 18:00–02:00).
     * Powers the search "open now" filter; mirrors StorePayloadFormatter::isOpenNow
     * so the filter and the per-card badge agree.
     *
     * @return array<int, true> store_id => true
     */
    public function storeIdsOpenNow(): array
    {
        global $wpdb;
        $hours = $wpdb->prefix . 'cliq_sl_store_hours';
        $locations = $wpdb->prefix . 'cliq_sl_store_locations';
        $dow = (int) wp_date('w');
        $now = (string) wp_date('H:i');

        $ids = array_map('intval', $wpdb->get_col($wpdb->prepare(
            "SELECT DISTINCT l.store_id
             FROM %i h INNER JOIN %i l ON l.id = h.location_id
             WHERE l.is_primary = 1 AND h.day_of_week = %d AND h.is_closed = 0
               AND h.open_time IS NOT NULL AND h.close_time IS NOT NULL
               AND h.open_time <> '' AND h.close_time <> ''
               AND (
                 (h.open_time <= h.close_time AND %s >= h.open_time AND %s <= h.close_time)
                 OR (h.open_time > h.close_time AND (%s >= h.open_time OR %s <= h.close_time))
               )",
            $hours,
            $locations,
            $dow,
            $now,
            $now,
            $now,
            $now
        )));

        // Current admin Hours fields live in store_meta. Prefer them over a
        // possibly stale store_hours rows so filtering and badges
        // agree with exactly what the editor shows.
        $metaTable = $wpdb->prefix . 'cliq_sl_store_meta';
        $metaRows = $wpdb->get_results($wpdb->prepare(
            'SELECT store_id, meta_value FROM %i WHERE meta_key = %s',
            $metaTable,
            'opening_hours'
        ), ARRAY_A) ?: [];
        $metaStores = [];
        $metaOpen = [];
        foreach ($metaRows as $row) {
            $schedule = StorePayloadFormatter::decodeOpeningHours($row['meta_value'] ?? null);
            if ($schedule === []) {
                continue;
            }
            $storeId = (int) $row['store_id'];
            $metaStores[$storeId] = true;
            if (StorePayloadFormatter::isOpenNow(StorePayloadFormatter::openingHoursRows($schedule)) === true) {
                $metaOpen[$storeId] = true;
            }
        }

        $relationalOpen = array_fill_keys($ids, true);
        foreach ($metaStores as $storeId => $_present) {
            unset($relationalOpen[$storeId]);
        }

        return $relationalOpen + $metaOpen;
    }

    /**
     * Store ids in the given language (Multilingual::current()), excluding
     * translation-sibling rows (source_id IS NOT NULL — a sibling holds only
     * translated name/description text, never its own locations/hours/etc.,
     * so it must never be listed as its own search result; it's meant to
     * supply overlay text onto its ORIGINAL when a visitor is in that
     * language — see ContentTranslator::overlay(), Multilingual.php's
     * docblock, and StoreRepository::saveTranslation()). Applied
     * unconditionally (not opt-in) by search()/browseResponse() — a search
     * or browse should never mix languages, unlike category/brand/etc,
     * which are only filters when the visitor picks one.
     *
     * @return array<int, true> store_id => true
     */
    public function storeIdsForLanguage(string $lang): array
    {
        global $wpdb;
        $table = $wpdb->prefix . 'cliq_sl_stores';

        $ids = $wpdb->get_col($wpdb->prepare(
            'SELECT id FROM %i WHERE lang = %s AND source_id IS NULL AND deleted_at IS NULL',
            $table,
            $lang
        ));

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

    /**
     * Store ids whose status is one of the given values — powers the
     * Results widget's "Visible statuses" setting (default: Active only;
     * see StoreProviderDefinition's status field, whose own help text says
     * "Only Active stores appear on the public locator" — true only once
     * this filter exists).
     *
     * @param string[] $statuses
     *
     * @return array<int, true> store_id => true
     */
    public function storeIdsForStatus(array $statuses): array
    {
        global $wpdb;
        $table = $wpdb->prefix . 'cliq_sl_stores';
        $statuses = array_values(array_filter(array_map('trim', $statuses), static fn (string $s): bool => $s !== ''));
        if ($statuses === []) {
            return [];
        }
        $placeholders = implode(',', array_fill(0, count($statuses), '%s'));
        $sql = "SELECT id FROM %i WHERE status IN ({$placeholders}) AND deleted_at IS NULL";
        // The IN placeholders are generated above and every value is bound.
        // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQL.NotPrepared
        $ids = $wpdb->get_col($wpdb->prepare($sql, array_merge([$table], $statuses)));

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

    /**
     * Store count per category (non-deleted stores), for category-browser cards.
     *
     * @return array<int, int>
     */
    public function categoryStoreCounts(): array
    {
        global $wpdb;
        $pivot = $wpdb->prefix . 'cliq_sl_store_category';
        $stores = $wpdb->prefix . 'cliq_sl_stores';
        // Only count stores a search would actually surface — active + not
        // deleted. Counting other statuses (e.g. pending) made a category tile
        // advertise a store that clicking through then filtered out ("no
        // results"), so count and search must apply the same status floor.
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT p.category_id AS cid, COUNT(*) AS n
             FROM %i p
             INNER JOIN %i s ON s.id = p.store_id AND s.deleted_at IS NULL AND s.status = 'active'
             GROUP BY p.category_id",
                $pivot,
                $stores
            ),
            ARRAY_A
        ) ?: [];

        $counts = [];
        foreach ($rows as $row) {
            $counts[(int) $row['cid']] = (int) $row['n'];
        }

        return $counts;
    }

    /**
     * Per-category counts restricted to a given set of store ids — the search
     * category facet (counts scoped to the current result set). Same active +
     * not-deleted floor as categoryStoreCounts() so tile counts and the store
     * list agree.
     *
     * @param array<int, int> $storeIds
     * @return array<int, int> category_id => count
     */
    public function categoryCountsForStores(array $storeIds): array
    {
        $ids = array_values(array_unique(array_filter(array_map('intval', $storeIds), static fn (int $id): bool => $id > 0)));
        if ($ids === []) {
            return [];
        }

        global $wpdb;
        $pivot = $wpdb->prefix . 'cliq_sl_store_category';
        $stores = $wpdb->prefix . 'cliq_sl_stores';
        $placeholders = implode(',', array_fill(0, count($ids), '%d'));
        $query = "SELECT p.category_id AS cid, COUNT(*) AS n
             FROM %i p
             INNER JOIN %i s ON s.id = p.store_id AND s.deleted_at IS NULL AND s.status = 'active'
             WHERE p.store_id IN ({$placeholders})
             GROUP BY p.category_id";
        // The IN placeholders are generated above and every value 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([$pivot, $stores], $ids)
        ), ARRAY_A) ?: [];

        $counts = [];
        foreach ($rows as $row) {
            $counts[(int) $row['cid']] = (int) $row['n'];
        }

        return $counts;
    }
}
