<?php
declare(strict_types=1);

namespace Gatecraft;

use WP_User, WP_Error, WP_Session_Tokens, WP_CLI, RuntimeException, Throwable, Exception, DateTime, DateTimeImmutable, ZipArchive;

if (!defined('ABSPATH')) exit;

// Stock is "held" by paid orders and by unexpired orders awaiting approval or payment.
const HELD_SQL = "(o.status = 'paid' OR (o.status IN ('awaiting_approval','pending_payment') AND o.expires_at > ?))";

/** Held quantities for an event: ['cat' => [id => n], 'tier' => [id => n], 'total' => n]. */
function held_counts(int $eventId): array
{
    $rows = q("SELECT oi.category_id, oi.tier_id, SUM(oi.qty) AS n, SUM(CASE WHEN o.payment_method = 'imported' THEN 0 ELSE oi.qty END) AS live
               FROM {order_items} oi JOIN {orders} o ON o.id = oi.order_id
               WHERE o.event_id = ? AND " . HELD_SQL . " GROUP BY oi.category_id, oi.tier_id", [$eventId, now()])->fetchAll();
    $out = ['cat' => [], 'tier' => [], 'total' => 0];
    foreach ($rows as $r) {
        $out['cat'][$r['category_id']] = ($out['cat'][$r['category_id']] ?? 0) + (int)$r['n'];
        $out['tier'][$r['tier_id']] = ($out['tier'][$r['tier_id']] ?? 0) + (int)$r['n'];
        // Imported bookings sit in their own switched-off categories, whose capacity grew to fit them, and never take places
        // from the event or venue limit.
        $out['total'] += (int)$r['live'];
    }
    return $out;
}

/** Hard cap for an event: event capacity, venue capacity, whichever is lower. Null means no cap beyond categories. */
function event_cap(array $event): ?int
{
    $caps = [];
    if ($event['capacity'] !== null && (int)$event['capacity'] > 0) $caps[] = (int)$event['capacity'];
    if (!empty($event['venue_id'])) {
        $vc = (int)qv('SELECT capacity FROM {venues} WHERE id = ?', [$event['venue_id']]);
        if ($vc > 0) $caps[] = $vc;
    }
    return $caps ? min($caps) : null;
}

function categories_with_tiers(int $eventId, bool $activeOnly = false): array
{
    $cats = q('SELECT * FROM {ticket_categories} WHERE event_id = ?' . ($activeOnly ? ' AND is_active = 1' : '') . ' ORDER BY sort_order, id', [$eventId])->fetchAll();
    if (!$cats) return [];
    $ids = array_column($cats, 'id');
    $in = implode(',', array_fill(0, count($ids), '?'));
    $tiers = q("SELECT * FROM {ticket_tiers} WHERE category_id IN ($in) ORDER BY sort_order, id", $ids)->fetchAll();
    $by = [];
    foreach ($tiers as $t) $by[$t['category_id']][] = $t;
    foreach ($cats as &$c) $c['tiers'] = $by[$c['id']] ?? [];
    return $cats;
}

/**
 * What is on sale right now, per category. Each category gets:
 * state (on_sale, sold_out, upcoming, ended, unavailable), tier (the active tier), available, next_tier.
 */
function event_offer(array $event, ?array $held = null): array
{
    $held ??= held_counts((int)$event['id']);
    $cap = event_cap($event);
    $eventLeft = $cap === null ? PHP_INT_MAX : max(0, $cap - $held['total']);
    $n = now();
    $out = [];
    foreach (categories_with_tiers((int)$event['id'], true) as $c) {
        $catLeft = max(0, (int)$c['capacity'] - ($held['cat'][$c['id']] ?? 0));
        $c['state'] = 'unavailable';
        $c['tier'] = null;
        $c['next_tier'] = null;
        $c['available'] = 0;
        $anyFuture = false;
        foreach ($c['tiers'] as $t) {
            if (!$t['is_active']) continue;
            $tierLeft = $t['quantity'] === null ? PHP_INT_MAX : max(0, (int)$t['quantity'] - ($held['tier'][$t['id']] ?? 0));
            if ($t['sale_end'] !== null && $t['sale_end'] < $n) continue;
            if ($tierLeft <= 0) continue;
            if ($t['sale_start'] !== null && $t['sale_start'] > $n) {
                $anyFuture = true;
                $c['next_tier'] ??= $t;
                continue;
            }
            $c['tier'] = $t;
            $c['available'] = min($catLeft, $tierLeft, $eventLeft);
            break;
        }
        if ($c['tier'] && $c['available'] > 0) $c['state'] = 'on_sale';
        elseif ($c['tier'] || $catLeft <= 0 || $eventLeft <= 0) $c['state'] = 'sold_out';
        elseif ($anyFuture) $c['state'] = 'upcoming';
        elseif ($c['tiers']) $c['state'] = 'ended';
        $out[] = $c;
    }
    return $out;
}

/** Lowest current price for listing cards. */
function event_from_price(array $offer): ?int
{
    $prices = [];
    foreach ($offer as $c) if ($c['state'] === 'on_sale') $prices[] = (int)$c['tier']['price'];
    return $prices ? min($prices) : null;
}

function event_with_venue(int $id): ?array
{
    return q1('SELECT e.*, v.name AS venue_name, v.address AS venue_address, v.city AS venue_city, v.country AS venue_country,
               v.map_url AS venue_map, v.accessibility AS venue_accessibility, v.transport AS venue_transport, v.postal_code AS venue_postal
               FROM {events} e LEFT JOIN {venues} v ON v.id = e.venue_id WHERE e.id = ?', [$id]);
}

function venue_line(array $ev): string
{
    return implode(', ', array_filter([$ev['venue_name'] ?? '', $ev['venue_city'] ?? '']));
}

/**
 * One price per category. Internally the price lives on the category's first tier, which is always on, has no
 * quantity limit and no sale dates. Any older extra tiers are switched off (kept for past orders).
 */
function set_category_price(int $categoryId, int $price): void
{
    $first = qv('SELECT id FROM {ticket_tiers} WHERE category_id = ? ORDER BY sort_order, id LIMIT 1', [$categoryId]);
    if ($first) {
        q('UPDATE {ticket_tiers} SET price = ?, quantity = NULL, sale_start = NULL, sale_end = NULL, is_active = 1 WHERE id = ?', [$price, $first]);
        q('UPDATE {ticket_tiers} SET is_active = 0 WHERE category_id = ? AND id <> ?', [$categoryId, $first]);
    } else {
        q('INSERT INTO {ticket_tiers} (category_id, name, price, quantity, is_active, sort_order) VALUES (?,?,?,NULL,1,0)', [$categoryId, 'Standard', $price]);
    }
}