<?php

declare(strict_types=1);

namespace ProCalendarWP\Events;

use DateTimeImmutable;
use DateTimeZone;
use ProCalendarWP\Query\EventQuerySpec;

final class WpEventRepository implements EventRepositoryInterface
{
    /** @var array<int,EventData> */
    private static array $eventCache = [];

    public static function clearEventCache(int $eventId): void
    {
        unset(self::$eventCache[$eventId]);
    }
    /** @var array<int,array{venue:list<int>,organizer:list<int>}> */
    private static array $entityCache = [];

    public static function clearEntityCache(int $eventId): void
    {
        unset(self::$entityCache[$eventId]);
        self::clearEventCache($eventId);
    }

    public function find(int $eventId): ?EventData
    {
        if (isset(self::$eventCache[$eventId])) {
            return self::$eventCache[$eventId];
        }
        $post = get_post($eventId);
        if (! $post instanceof \WP_Post || $post->post_type !== 'pcwp_event') {
            return null;
        }

        $timezone = EventMeta::sanitizeTimezone(get_post_meta($eventId, '_pcwp_timezone', true));
        $zone = new DateTimeZone($timezone);
        $start = $this->date((string) get_post_meta($eventId, '_pcwp_start', true), $zone);
        $end = $this->date((string) get_post_meta($eventId, '_pcwp_end', true), $zone);
        if ($start === null || $end === null) {
            return null;
        }
        try {
            $time = EventTime::fromStored($eventId, (string) get_post_meta($eventId, '_pcwp_start', true), (string) get_post_meta($eventId, '_pcwp_end', true), $timezone);
            $start = $time->start;
            $end = $time->end;
        } catch (\InvalidArgumentException) {
            return null;
        }

        $entities = $this->entities($eventId);
        return self::$eventCache[$eventId] = new EventData(
            $eventId,
            get_the_title($post),
            (string) $post->post_content,
            (string) $post->post_excerpt,
            $start,
            $end,
            $timezone,
            (bool) get_post_meta($eventId, '_pcwp_all_day', true),
            EventMeta::sanitizeStatus(get_post_meta($eventId, '_pcwp_status', true)),
            EventMeta::sanitizeMode(get_post_meta($eventId, '_pcwp_mode', true)),
            (bool) get_post_meta($eventId, '_pcwp_featured', true),
            (string) get_post_meta($eventId, '_pcwp_cost', true),
            (string) get_post_meta($eventId, '_pcwp_currency', true),
            (string) get_post_meta($eventId, '_pcwp_website', true),
            (string) get_post_meta($eventId, '_pcwp_virtual_url', true),
            $entities['venue'],
            $entities['organizer'],
            is_array($schedule = get_post_meta($eventId, '_pcwp_schedule', true)) ? $schedule : ['type' => 'continuous'],
        );
    }

    /**
     * @param array<string,mixed> $query Query arguments.
     * @return list<OccurrenceData>
     */
    public function occurrences(array $query = []): array
    {
        $offset = max(0, absint($query['offset'] ?? 0));
        // Calendar grids group daily slots by event-local date, not the site's UTC boundaries.
        $calendarRange = array_intersect_key($query, array_flip(['schedule_local_start', 'schedule_local_end']));
        $query = EventQuerySpec::from($query, true)->toRepositoryQuery() + $calendarRange;
        // Let multilingual plugins apply their native query filters and fallback rules.
        // Limit this extra selection to the requested page, rather than loading every translated ID.
        $language = function_exists('pll_current_language') ? pll_current_language() : apply_filters('wpml_current_language', null);
        if (is_string($language) && $language !== '' && $language !== 'all') {
            $args = EventQuerySpec::from($query, true)->toWpQueryArgs() + $calendarRange;
            $args['lang'] = $language;
            $args['suppress_filters'] = false;
            $args['no_found_rows'] = true;
            $args['fields'] = 'ids';
            $selection = new \WP_Query($args);
            if ($selection->posts === []) {
                return [];
            }
            $query['include_events'] = array_map('absint', $selection->posts);
        }
        global $wpdb;
        $table = $wpdb->prefix . 'pcwp_occurrences';
        $posts = $wpdb->posts;
        $where = ["p.post_status = 'publish'", "p.post_type = 'pcwp_event'", "p.post_password = ''"];
        $where[] = "NOT EXISTS (SELECT 1 FROM {$posts} parent WHERE parent.ID=o.series_id AND (parent.post_status<>'publish' OR parent.post_password<>''))";
        $params = [];

        if (! empty($query['start'])) {
            $where[] = "(o.schedule_type='daily' OR o.end_utc >= %s)";
            $params[] = $this->utcSql((string) $query['start']);
        }
        if (! empty($query['end'])) {
            $where[] = "(o.schedule_type='daily' OR o.start_utc <= %s)";
            $params[] = $this->utcSql((string) $query['end']);
        }
        if (isset($query['past_events']) && ! filter_var($query['past_events'], FILTER_VALIDATE_BOOLEAN)) {
            $where[] = "(o.schedule_type='daily' OR o.end_utc >= %s)";
            $params[] = gmdate('Y-m-d H:i:s');
        }
        if (! empty($query['status'])) {
            $where[] = 'o.event_status = %s';
            $params[] = EventMeta::sanitizeStatus($query['status']);
        }
        if ($query['include_events'] !== []) {
            $placeholders = implode(',', array_fill(0, count($query['include_events']), '%d'));
            $where[] = "o.event_id IN ({$placeholders})";
            array_push($params, ...$query['include_events']);
        }
        if ($query['exclude_events'] !== []) {
            $placeholders = implode(',', array_fill(0, count($query['exclude_events']), '%d'));
            $where[] = "o.event_id NOT IN ({$placeholders})";
            array_push($params, ...$query['exclude_events']);
        }
        if (! empty($query['mode'])) {
            $where[] = 'o.event_mode = %s';
            $params[] = $query['mode'];
        }
        if (! empty($query['series'])) {
            $where[] = 'o.series_id = %d';
            $params[] = $query['series'];
        }
        if (! empty($query['related_to'])) {
            $where[] = 'o.event_id <> %d';
            $params[] = $query['related_to'];
            $where[] = "EXISTS (SELECT 1 FROM {$wpdb->term_relationships} r1 INNER JOIN {$wpdb->term_taxonomy} rt1 ON rt1.term_taxonomy_id=r1.term_taxonomy_id INNER JOIN {$wpdb->term_relationships} r2 ON r2.term_taxonomy_id=r1.term_taxonomy_id WHERE r1.object_id=%d AND r2.object_id=o.event_id AND rt1.taxonomy IN ('pcwp_event_category','pcwp_event_tag'))";
            $params[] = $query['related_to'];
        }
        if (! empty($query['featured'])) {
            $where[] = 'o.featured = 1';
        }
        if ($query['min_price'] !== null) {
            $where[] = 'o.cost_decimal >= %f';
            $params[] = $query['min_price'];
        }
        if ($query['max_price'] !== null) {
            $where[] = 'o.cost_decimal <= %f';
            $params[] = $query['max_price'];
        }
        if ($query['weekday'] >= 0) {
            $where[] = "(o.schedule_type='daily' OR WEEKDAY(o.start_local) = %d)";
            $params[] = $query['weekday'];
        }
        if ($query['time_from'] !== '') {
            $where[] = "(o.schedule_type='daily' OR TIME(o.start_local) >= %s)";
            $params[] = $query['time_from'] . ':00';
        }
        if ($query['time_to'] !== '') {
            $where[] = "(o.schedule_type='daily' OR TIME(o.start_local) <= %s)";
            $params[] = $query['time_to'] . ':59';
        }
        if (is_numeric($query['latitude']) && is_numeric($query['longitude']) && is_numeric($query['radius']) && (float) $query['radius'] > 0) {
            $latDelta = (float) $query['radius'] / 111.0;
            $lonDelta = (float) $query['radius'] / (111.0 * max(0.01, cos(deg2rad((float) $query['latitude']))));
            $where[] = 'o.latitude BETWEEN %f AND %f';
            $params[] = (float) $query['latitude'] - $latDelta;
            $params[] = (float) $query['latitude'] + $latDelta;
            $where[] = 'o.longitude BETWEEN %f AND %f';
            $params[] = (float) $query['longitude'] - $lonDelta;
            $params[] = (float) $query['longitude'] + $lonDelta;
            $where[] = '(6371 * 2 * ASIN(SQRT(POWER(SIN(RADIANS(o.latitude - %f) / 2), 2) + COS(RADIANS(%f)) * COS(RADIANS(o.latitude)) * POWER(SIN(RADIANS(o.longitude - %f) / 2), 2)))) <= %f';
            $params[] = (float) $query['latitude'];
            $params[] = (float) $query['latitude'];
            $params[] = (float) $query['longitude'];
            $params[] = (float) $query['radius'];
        }
        if (! empty($query['keyword'])) {
            $where[] = '(p.post_title LIKE %s OR p.post_content LIKE %s)';
            $needle = '%' . $wpdb->esc_like(sanitize_text_field((string) $query['keyword'])) . '%';
            $params[] = $needle;
            $params[] = $needle;
        }

        foreach (['venue' => 'venue', 'organizer' => 'organizer'] as $key => $entityType) {
            if (! empty($query[$key])) {
                $where[] = "EXISTS (SELECT 1 FROM {$wpdb->prefix}pcwp_event_entities ee WHERE ee.event_id=o.event_id AND ee.entity_type=%s AND ee.entity_id=%d)";
                $params[] = $entityType;
                $params[] = absint($query[$key]);
            }
        }

        $taxonomyClauses = $query['required_taxonomies'];
        foreach ($query['taxonomies'] as $taxonomy => $filter) {
            $taxonomyClauses[] = ['taxonomy' => $taxonomy] + $filter;
        }
        foreach ($taxonomyClauses as $filter) {
            $taxonomy = $filter['taxonomy'];
            if (! taxonomy_exists($taxonomy) || ! is_object_in_taxonomy('pcwp_event', $taxonomy) || $filter['terms'] === []) {
                continue;
            }
            if ($filter['operator'] === 'AND') {
                foreach ($filter['terms'] as $term) {
                    $where[] = "EXISTS (SELECT 1 FROM {$wpdb->term_relationships} tr JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id=tr.term_taxonomy_id JOIN {$wpdb->terms} t ON t.term_id=tt.term_id WHERE tr.object_id=o.event_id AND tt.taxonomy=%s AND t.slug=%s)";
                    $params[] = $taxonomy;
                    $params[] = $term;
                }
                continue;
            }
            $placeholders = implode(',', array_fill(0, count($filter['terms']), '%s'));
            $where[] = "EXISTS (SELECT 1 FROM {$wpdb->term_relationships} tr JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id=tr.term_taxonomy_id JOIN {$wpdb->terms} t ON t.term_id=tt.term_id WHERE tr.object_id=o.event_id AND tt.taxonomy=%s AND t.slug IN ({$placeholders}))";
            $params[] = $taxonomy;
            array_push($params, ...$filter['terms']);
        }
        $allowedFields = apply_filters('pcwp_public_custom_fields', [], 0);
        if (is_array($allowedFields)) {
            foreach ($query['custom_fields'] as $key => $value) {
                if (in_array($key, $allowedFields, true)) {
                    $where[] = "EXISTS (SELECT 1 FROM {$wpdb->postmeta} pcwp_pm WHERE pcwp_pm.post_id=o.event_id AND pcwp_pm.meta_key=%s AND pcwp_pm.meta_value=%s)";
                    $params[] = $key;
                    $params[] = (string) $value;
                }
            }
        }

        $limit = min(500, max(1, (int) ($query['limit'] ?? 100)));
        $order = 'o.start_utc';
        // Avoid a computed sort over the whole event table on sites without daily intervals.
        $hasDaily = (bool) $wpdb->get_var("SELECT 1 FROM {$wpdb->prefix}pcwp_daily_intervals LIMIT 1");
        if ($hasDaily) {
            $matchingStart = str_replace('%', '%%', ScheduleQuery::matchingStart('o', $query));
            $where[] = "(o.schedule_type<>'daily' OR {$matchingStart} IS NOT NULL)";
            $order = "CASE WHEN o.schedule_type='daily' THEN {$matchingStart} ELSE o.start_utc END";
        }
        $sql = "SELECT o.* FROM {$table} o INNER JOIN {$posts} p ON p.ID=o.event_id WHERE " . implode(' AND ', $where) . " ORDER BY {$order} ASC,o.event_id ASC LIMIT %d OFFSET %d";
        $params[] = $limit;
        $params[] = $offset;
        $rows = $wpdb->get_results($wpdb->prepare($sql, ...$params)); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query fragments contain generated identifiers/placeholders; every external value is in the prepared parameter list.
        $ids = array_map(static fn (object $row): int => (int) $row->event_id, is_array($rows) ? $rows : []);
        // Calendar templates read titles, metadata and relationships for every result.
        _prime_post_caches($ids, true, true);
        self::primeEntities($ids);
        return array_values(array_map([$this, 'hydrateOccurrence'], is_array($rows) ? $rows : []));
    }

    /** @return array{venue:list<int>,organizer:list<int>} */
    private function entities(int $eventId): array
    {
        if (isset(self::$entityCache[$eventId])) {
            return self::$entityCache[$eventId];
        }
        self::primeEntities([$eventId]);
        return self::$entityCache[$eventId] ?? ['venue' => [], 'organizer' => []];
    }

    /** @return list<int> */
    public static function venueIds(int $eventId): array
    {
        self::primeEntities([$eventId]);
        return self::$entityCache[$eventId]['venue'] ?? [];
    }

    /** @param list<int> $eventIds */
    public static function primeEntities(array $eventIds): void
    {
        $eventIds = array_values(array_diff(array_unique(array_map('absint', $eventIds)), array_keys(self::$entityCache)));
        if ($eventIds === []) {
            return;
        }
        global $wpdb;
        $table = $wpdb->prefix . 'pcwp_event_entities';
        foreach ($eventIds as $eventId) {
            self::$entityCache[$eventId] = ['venue' => [], 'organizer' => []];
        }
        $placeholders = implode(',', array_fill(0, count($eventIds), '%d'));
        $rows = $wpdb->get_results($wpdb->prepare("SELECT event_id,entity_id,entity_type FROM {$table} WHERE event_id IN ({$placeholders}) ORDER BY event_id,sort_order ASC", ...$eventIds)); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        foreach (is_array($rows) ? $rows : [] as $row) {
            $eventId = (int) $row->event_id;
            if (isset(self::$entityCache[$eventId][$row->entity_type])) {
                self::$entityCache[$eventId][$row->entity_type][] = (int) $row->entity_id;
            }
        }
    }

    private function hydrateOccurrence(object $row): OccurrenceData
    {
        return new OccurrenceData(
            (int) $row->id,
            (int) $row->event_id,
            $row->series_id === null ? null : (int) $row->series_id,
            new DateTimeImmutable($row->start_utc, new DateTimeZone('UTC')),
            new DateTimeImmutable($row->end_utc, new DateTimeZone('UTC')),
            (new DateTimeImmutable($row->start_utc, new DateTimeZone('UTC')))->setTimezone(new DateTimeZone($row->timezone)),
            (new DateTimeImmutable($row->end_utc, new DateTimeZone('UTC')))->setTimezone(new DateTimeZone($row->timezone)),
            (string) $row->timezone,
            (bool) $row->all_day,
            (string) $row->event_status,
        );
    }

    private function date(string $value, DateTimeZone $zone): ?DateTimeImmutable
    {
        if ($value === '') {
            return null;
        }
        try {
            return new DateTimeImmutable($value, $zone);
        } catch (\Exception) {
            return null;
        }
    }

    private function utcSql(string $value): string
    {
        try {
            return (new DateTimeImmutable($value))->setTimezone(new DateTimeZone('UTC'))->format('Y-m-d H:i:s');
        } catch (\Exception) {
            return gmdate('Y-m-d H:i:s');
        }
    }
}