<?php
namespace DayTidy\DisplayViews\Query;

use DayTidy\Db;
use DayTidy\Modules\Offering\Core\OfferingVisibility;
use DayTidy\Posts\Offering;
use DayTidy\DisplayViews\Config as ViewConfig;
use DayTidy\DisplayViews\DateWindow;
use DayTidy\DisplayViews\OfferingDataIndexer;
use DayTidy\DisplayViews\SessionCoverage;
use DateTime;
use DateTimeZone;

class OfferingSessionQuery
{
    /**
     * @var mixed[]
     */
    private array $config;

    private string $now;

    /**
     * @param mixed[] $config
     */
    public function __construct(array $config)
    {
        $this->config = ViewConfig::normalize($config);
        $this->now = gmdate('Y-m-d H:i:s');
    }

    /**
     * @return array{items: mixed[], next_cursor: string, has_more: bool}
     */
    public function page(string $cursor = ''): array
    {
        $limit = max(1, min(100, (int) ($this->config['pagination']['per_page'] ?? 12)));
        (new SessionCoverage())->ensure($this->config, (new DateWindow($this->config))->coverageTarget());

        $records = $this->records([
            'limit' => $limit + 1,
            'cursor' => $cursor,
        ]);

        $has_more = count($records) > $limit;
        if ($has_more) array_pop($records);

        $next_cursor = '';
        if ($has_more && count($records))
        {
            $last = $records[count($records) - 1];
            $next_cursor = $this->encodeCursor($last);
        }

        return [
            'items' => $records,
            'next_cursor' => $next_cursor,
            'has_more' => $has_more,
        ];
    }

    /**
     * @return mixed[]
     */
    public function calendar(DateTime $start, DateTime $end): array
    {
        (new SessionCoverage())->ensure($this->config, $end);

        return $this->records([
            'range_start' => $start->setTimezone(new DateTimeZone('UTC'))->format('Y-m-d H:i:s'),
            'range_end' => $end->setTimezone(new DateTimeZone('UTC'))->format('Y-m-d H:i:s'),
            'limit' => 1000,
        ]);
    }

    /**
     * @param mixed[] $args
     * @return mixed[]
     */
    private function records(array $args): array
    {
        $db = new Db();

        $args = $this->withDateWindow($args);
        $sessions = $db->table('daytidy_sessions');
        $joins = ["INNER JOIN {$db->table('posts')} p ON p.ID = o.offering_id"];
        $where = [
            $db->prepare('p.post_type = %s', Offering::PT),
            $db->prepare('p.post_status = %s', 'publish'),
            'o.visible = 1',
        ];

        $this->statusWhere($where, $db);
        $this->rangeWhere($where, $args, $db);
        $this->visibilityWhere($joins, $where, $db);
        $this->filterWhere($where, $db);

        if ($this->oneSession())
        {
            $this->oneSessionJoin($joins, $args, $db);
        }

        $this->cursorWhere($where, (string) ($args['cursor'] ?? ''), $db);

        $order = $this->expiredOnly() ? 'o.ends_at_utc DESC, o.id DESC' : 'o.starts_at_utc ASC, o.id ASC';
        $limit = max(1, min(1000, (int) ($args['limit'] ?? 20)));

        $sql = "SELECT o.id, o.offering_id, o.starts_at, o.ends_at, o.starts_at_utc, o.ends_at_utc,
                p.post_title, p.post_excerpt, p.post_content
            FROM {$sessions} o
            " . implode("\n", $joins) . "
            WHERE " . implode(' AND ', $where) . "
            ORDER BY {$order}
            LIMIT {$limit}";

        return $db->getResults($sql);
    }

    /**
     * @param string[] $joins
     * @param array<int, string|null> $where
     */
    private function visibilityWhere(array &$joins, array &$where, Db $db): void
    {
        $joins[] = $db->prepare(
            "LEFT JOIN (
                SELECT DISTINCT pm_public.post_id
                FROM {$db->table('postmeta')} pm_public
                WHERE pm_public.meta_key = %s
                AND pm_public.meta_value = %s
            ) hidden_offering_visibility ON hidden_offering_visibility.post_id = p.ID",
            OfferingVisibility::META,
            '0'
        );
        $where[] = 'hidden_offering_visibility.post_id IS NULL';
    }

    /**
     * @param array<int, string|null> $where
     */
    private function statusWhere(array &$where, Db $db, string $alias = 'o'): void
    {
        $statuses = isset($this->config['statuses']) && is_array($this->config['statuses'])
            ? $this->config['statuses']
            : ['upcoming', 'ongoing'];
        $statuses = array_values(array_unique($statuses));
        $includes_upcoming = in_array('upcoming', $statuses, true);
        $includes_ongoing = in_array('ongoing', $statuses, true);
        $includes_expired = in_array('expired', $statuses, true);

        if ($includes_upcoming && $includes_ongoing && !$includes_expired)
        {
            $where[] = $db->prepare("{$alias}.ends_at_utc > %s", $this->now);
            return;
        }

        $parts = [];

        if ($includes_upcoming)
        {
            $parts[] = $db->prepare("{$alias}.starts_at_utc > %s", $this->now);
        }

        if ($includes_ongoing)
        {
            $parts[] = $db->prepare(
                "({$alias}.starts_at_utc <= %s AND {$alias}.ends_at_utc > %s)",
                $this->now,
                $this->now
            );
        }

        if ($includes_expired)
        {
            $parts[] = $db->prepare("{$alias}.ends_at_utc < %s", $this->now);
        }

        if (count($parts)) $where[] = '(' . implode(' OR ', $parts) . ')';
    }

    /**
     * @param array<int, string|null> $where
     * @param mixed[] $args
     */
    private function rangeWhere(array &$where, array $args, Db $db, string $alias = 'o'): void
    {
        if (!empty($args['empty_range']))
        {
            $where[] = '0 = 1';
            return;
        }

        if (!empty($args['range_start']))
        {
            $where[] = $db->prepare("{$alias}.ends_at_utc > %s", (string) $args['range_start']);
        }

        if (!empty($args['range_end']))
        {
            $where[] = $db->prepare("{$alias}.starts_at_utc < %s", (string) $args['range_end']);
        }
    }

    /**
     * @param mixed[] $args
     * @return mixed[]
     */
    private function withDateWindow(array $args): array
    {
        $window = (new DateWindow($this->config))->queryArgs();
        $start = (string) ($window['range_start'] ?? '');
        $end = (string) ($window['range_end'] ?? '');

        if (!empty($window['empty_range'])) $args['empty_range'] = 1;

        if ($start !== '' && (empty($args['range_start']) || $start > (string) $args['range_start']))
        {
            $args['range_start'] = $start;
        }

        if ($end !== '' && (empty($args['range_end']) || $end < (string) $args['range_end']))
        {
            $args['range_end'] = $end;
        }

        if (!empty($args['range_start']) && !empty($args['range_end']) && (string) $args['range_start'] >= (string) $args['range_end'])
        {
            $args['empty_range'] = 1;
        }

        return $args;
    }

    /**
     * @param array<int, string|null> $where
     */
    private function filterWhere(array &$where, Db $db): void
    {
        $filters = isset($this->config['filters']) && is_array($this->config['filters']) ? $this->config['filters'] : [];

        $this->taxonomyWhere($where, $filters['taxonomies'] ?? [], $db);
        $this->authorWhere($where, $filters['users'] ?? [], $db);
        $this->repeatWhere($where, $filters['repeat_types'] ?? [], $db);
        $this->customFieldWhere($where, $filters['custom_fields'] ?? [], $db);
    }

    /**
     * @param array<int, string|null> $where
     * @param mixed $filters
     */
    private function taxonomyWhere(array &$where, $filters, Db $db): void
    {
        if (!is_array($filters)) return;

        $i = 0;
        foreach ($filters as $taxonomy => $filter)
        {
            if (!taxonomy_exists((string) $taxonomy) || !is_array($filter)) continue;

            $include = $this->ids($filter['include'] ?? []);
            $exclude = $this->ids($filter['exclude'] ?? []);

            if (count($include))
            {
                $rel = 'tr_inc_' . $i;
                $tax = 'tt_inc_' . $i;
                $where[] = $db->prepare(
                    "EXISTS (
                        SELECT 1 FROM {$db->table('term_relationships')} {$rel}
                        INNER JOIN {$db->table('term_taxonomy')} {$tax} ON {$tax}.term_taxonomy_id = {$rel}.term_taxonomy_id
                        WHERE {$rel}.object_id = p.ID
                        AND {$tax}.taxonomy = %s
                        AND {$tax}.term_id IN (" . implode(',', $include) . ")
                    )",
                    (string) $taxonomy
                );
            }

            if (count($exclude))
            {
                $where[] = $db->prepare(
                    "NOT EXISTS (
                        SELECT 1 FROM {$db->table('term_relationships')} tr_ex
                        INNER JOIN {$db->table('term_taxonomy')} tt_ex ON tt_ex.term_taxonomy_id = tr_ex.term_taxonomy_id
                        WHERE tr_ex.object_id = p.ID
                        AND tt_ex.taxonomy = %s
                        AND tt_ex.term_id IN (" . implode(',', $exclude) . ")
                    )",
                    (string) $taxonomy
                );
            }

            $i++;
        }
    }

    /**
     * @param array<int, string|null> $where
     * @param mixed $filter
     */
    private function authorWhere(array &$where, $filter, Db $db): void
    {
        if (!is_array($filter)) return;

        $include = $this->ids($filter['include'] ?? []);
        $exclude = $this->ids($filter['exclude'] ?? []);

        if (count($include)) $where[] = 'p.post_author IN (' . implode(',', $include) . ')';
        if (count($exclude)) $where[] = 'p.post_author NOT IN (' . implode(',', $exclude) . ')';
    }

    /**
     * @param array<int, string|null> $where
     * @param mixed $filter
     */
    private function repeatWhere(array &$where, $filter, Db $db): void
    {
        if (!is_array($filter)) return;

        $include = $this->strings($filter['include'] ?? []);
        $exclude = $this->strings($filter['exclude'] ?? []);
        $repeat_value = "COALESCE((SELECT pm_freq.meta_value FROM {$db->table('postmeta')} pm_freq WHERE pm_freq.post_id = p.ID AND pm_freq.meta_key = 'daytidy_freq' LIMIT 1), 'custom')";

        if (count($include)) $where[] = $repeat_value . ' IN (' . $db->quoteList($include) . ')';
        if (count($exclude)) $where[] = $repeat_value . ' NOT IN (' . $db->quoteList($exclude) . ')';
    }

    /**
     * @param array<int, string|null> $where
     * @param mixed $filters
     */
    private function customFieldWhere(array &$where, $filters, Db $db): void
    {
        if (!is_array($filters)) return;

        foreach ($filters as $field_id => $filter)
        {
            if (!is_array($filter)) continue;

            $type = sanitize_key((string) ($filter['type'] ?? 'text'));
            $operator = sanitize_key((string) ($filter['operator'] ?? ''));
            $key = OfferingDataIndexer::key((string) $field_id, $type === 'number');

            if ($type === 'number')
            {
                $min = trim((string) ($filter['min'] ?? ''));
                $max = trim((string) ($filter['max'] ?? ''));
                if ($min === '' && $max === '') continue;

                $parts = [$db->prepare('pm_cf.meta_key = %s', $key)];
                if ($min !== '' && is_numeric($min)) $parts[] = $db->prepare('CAST(pm_cf.meta_value AS DECIMAL(20,6)) >= %f', (float) $min);
                if ($max !== '' && is_numeric($max)) $parts[] = $db->prepare('CAST(pm_cf.meta_value AS DECIMAL(20,6)) <= %f', (float) $max);

                $where[] = "EXISTS (SELECT 1 FROM {$db->table('postmeta')} pm_cf WHERE pm_cf.post_id = p.ID AND " . implode(' AND ', $parts) . ')';
                continue;
            }

            $values = isset($filter['values']) && is_array($filter['values']) ? $this->strings($filter['values']) : [];
            if (in_array($operator, ['in', 'not_in'], true) && count($values))
            {
                $exists = $db->prepare(
                    "EXISTS (SELECT 1 FROM {$db->table('postmeta')} pm_cf WHERE pm_cf.post_id = p.ID AND pm_cf.meta_key = %s AND pm_cf.meta_value IN (" . $db->quoteList($values) . '))',
                    $key
                );
                $where[] = $operator === 'not_in' ? 'NOT ' . $exists : $exists;
                continue;
            }

            $value = trim((string) ($filter['value'] ?? ''));
            if ($value === '') continue;

            if ($operator === 'eq')
            {
                $where[] = $db->prepare(
                    "EXISTS (SELECT 1 FROM {$db->table('postmeta')} pm_cf WHERE pm_cf.post_id = p.ID AND pm_cf.meta_key = %s AND pm_cf.meta_value = %s)",
                    $key,
                    $value
                );
            }
            else
            {
                $where[] = $db->prepare(
                    "EXISTS (SELECT 1 FROM {$db->table('postmeta')} pm_cf WHERE pm_cf.post_id = p.ID AND pm_cf.meta_key = %s AND pm_cf.meta_value LIKE %s)",
                    $key,
                    '%' . $db->escLike($value) . '%'
                );
            }
        }
    }

    /**
     * @param array<int, string|null> $where
     */
    private function cursorWhere(array &$where, string $cursor, Db $db): void
    {
        $cursor = $this->decodeCursor($cursor);
        if (!$cursor) return;

        $dt = (string) ($cursor['dt'] ?? '');
        $id = absint($cursor['id'] ?? 0);
        if ($dt === '' || $id <= 0) return;

        if ($this->expiredOnly())
        {
            $where[] = $db->prepare('(o.ends_at_utc < %s OR (o.ends_at_utc = %s AND o.id < %d))', $dt, $dt, $id);
            return;
        }

        $where[] = $db->prepare('(o.starts_at_utc > %s OR (o.starts_at_utc = %s AND o.id > %d))', $dt, $dt, $id);
    }

    /**
     * @param string[] $joins
     * @param mixed[] $args
     */
    private function oneSessionJoin(array &$joins, array $args, Db $db): void
    {
        $sessions = $db->table('daytidy_sessions');
        $date_alias = 'candidate_date';
        $pick_alias = 'candidate_pick';
        $date_column = $this->expiredOnly() ? 'ends_at_utc' : 'starts_at_utc';
        $date_aggregate = $this->expiredOnly() ? 'MAX' : 'MIN';
        $id_aggregate = $this->expiredOnly() ? 'MAX' : 'MIN';

        $date_where = $this->oneSessionCandidateWhere($args, $db, $date_alias);
        $pick_where = $this->oneSessionCandidateWhere($args, $db, $pick_alias);

        $joins[] = "INNER JOIN (
            SELECT {$pick_alias}.offering_id, {$id_aggregate}({$pick_alias}.id) AS id
            FROM {$sessions} {$pick_alias}
            INNER JOIN (
                SELECT {$date_alias}.offering_id, {$date_aggregate}({$date_alias}.{$date_column}) AS sort_datetime
                FROM {$sessions} {$date_alias}
                WHERE " . implode(' AND ', $date_where) . "
                GROUP BY {$date_alias}.offering_id
            ) session_date ON session_date.offering_id = {$pick_alias}.offering_id
                AND session_date.sort_datetime = {$pick_alias}.{$date_column}
            WHERE " . implode(' AND ', $pick_where) . "
            GROUP BY {$pick_alias}.offering_id
        ) session_pick ON session_pick.id = o.id";
    }

    /**
     * @param mixed[] $args
     * @return string[]
     */
    private function oneSessionCandidateWhere(array $args, Db $db, string $alias): array
    {
        $where = ["{$alias}.visible = 1"];

        $this->statusWhere($where, $db, $alias);
        $this->rangeWhere($where, $args, $db, $alias);

        return $where;
    }

    /**
     * @param mixed $value
     * @return int[]
     */
    private function ids($value): array
    {
        if (!is_array($value)) return [];

        return array_values(array_unique(array_filter(array_map('absint', $value))));
    }

    /**
     * @param mixed $value
     * @return string[]
     */
    private function strings($value): array
    {
        if (!is_array($value)) return [];

        $strings = array_map('sanitize_text_field', $value);

        return array_values(array_unique(array_filter($strings, static function (string $string): bool
        {
            return $string !== '';
        })));
    }

    /**
     * @param mixed $record
     */
    private function encodeCursor($record): string
    {
        $dt = $this->expiredOnly() ? $record->ends_at_utc : $record->starts_at_utc;

        return base64_encode((string) wp_json_encode([
            'dt' => $dt,
            'id' => (int) $record->id,
        ]));
    }

    /**
     * @return mixed[]|null
     */
    private function decodeCursor(string $cursor): ?array
    {
        if ($cursor === '') return null;

        $decoded = json_decode((string) base64_decode($cursor, true), true);

        return is_array($decoded) ? $decoded : null;
    }

    private function expiredOnly(): bool
    {
        $statuses = isset($this->config['statuses']) && is_array($this->config['statuses']) ? $this->config['statuses'] : [];

        return count($statuses) === 1 && in_array('expired', $statuses, true);
    }

    private function oneSession(): bool
    {
        return ($this->config['sessions']['mode'] ?? 'all') === 'one';
    }
}