<?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;

function money_num(int $minor)
{
    $dec = (int)cfg('currency_decimals', 0);
    return $dec === 0 ? $minor : round($minor / (10 ** $dec), $dec);
}

/** Headline numbers. $eventId null = all events. Dates filter on order creation. */
function report_summary(?int $eventId, ?string $from = null, ?string $to = null): array
{
    [$w, $a] = report_where($eventId, $from, $to);
    $r = q1("SELECT
        COALESCE(SUM(CASE WHEN o.status = 'paid' THEN o.total END),0) AS revenue,
        COALESCE(SUM(CASE WHEN o.status = 'paid' THEN o.subtotal END),0) AS gross,
        COALESCE(SUM(CASE WHEN o.status = 'paid' THEN o.discount_amount END),0) AS discounts,
        COALESCE(SUM(CASE WHEN o.status = 'paid' THEN o.tax_amount END),0) AS tax,
        COALESCE(SUM(CASE WHEN o.status = 'refunded' THEN o.total END),0) AS refunded,
        SUM(o.status = 'paid') AS paid_orders,
        SUM(o.status = 'awaiting_approval') AS awaiting_approval,
        SUM(o.status = 'pending_payment' AND o.expires_at > ?) AS pending_payment,
        SUM(o.status IN ('cancelled','rejected','expired','refunded')) AS closed_orders,
        COUNT(*) AS all_orders
        FROM {orders} o WHERE $w", array_merge([now()], $a));
    $t = q1("SELECT COUNT(*) AS sold, SUM(tk.checked_in_at IS NOT NULL) AS checked_in, SUM(o.payment_method = 'comp') AS comp, SUM(o.payment_method IN ('cash','door_card')) AS door FROM {tickets} tk JOIN {orders} o ON o.id = tk.order_id
             WHERE tk.status = 'valid' AND o.status = 'paid' AND $w", $a);
    $r = array_map('intval', $r);
    $r['tickets'] = (int)$t['sold'];
    $r['checked_in'] = (int)$t['checked_in'];
    $r['comp_tickets'] = (int)$t['comp'];
    $r['door_tickets'] = (int)$t['door'];
    $r['avg_order'] = $r['paid_orders'] ? intdiv($r['revenue'], $r['paid_orders']) : 0;
    return $r;
}

function report_where(?int $eventId, ?string $from, ?string $to, string $alias = 'o'): array
{
    $w = ['1=1']; $a = [];
    if ($eventId) { $w[] = "$alias.event_id = ?"; $a[] = $eventId; }
    if ($from) { $w[] = "$alias.created_at >= ?"; $a[] = $from . ' 00:00:00'; }
    if ($to) { $w[] = "$alias.created_at <= ?"; $a[] = $to . ' 23:59:59'; }
    return [implode(' AND ', $w), $a];
}

/** Sold, revenue and capacity by category and tier for one event. */
function report_by_ticket_type(int $eventId): array
{
    return q("SELECT c.id AS category_id, c.name AS category, c.capacity,
              (SELECT t.price FROM {ticket_tiers} t WHERE t.category_id = c.id ORDER BY t.is_active DESC, t.sort_order, t.id LIMIT 1) AS price,
              COALESCE(SUM(CASE WHEN o.status = 'paid' THEN oi.qty END),0) AS sold,
              COALESCE(SUM(CASE WHEN o.status = 'paid' THEN oi.qty * oi.unit_price END),0) AS gross,
              COALESCE(SUM(CASE WHEN o.status IN ('awaiting_approval','pending_payment') AND o.expires_at > ? THEN oi.qty END),0) AS held
              FROM {ticket_categories} c
              LEFT JOIN {order_items} oi ON oi.category_id = c.id LEFT JOIN {orders} o ON o.id = oi.order_id
              WHERE c.event_id = ? GROUP BY c.id ORDER BY c.sort_order, c.id", [now(), $eventId])->fetchAll();
}

/** Daily paid tickets and revenue for the last $days days. */
function report_daily(?int $eventId, int $days = 30): array
{
    $start = ldate('Y-m-d', lstrtotime('-' . ($days - 1) . ' days'));
    $args = [$start . ' 00:00:00'];
    $ev = '';
    if ($eventId) { $ev = ' AND o.event_id = ?'; $args[] = $eventId; }
    $rows = q("SELECT DATE(o.paid_at) AS d, SUM(o.total) AS revenue, SUM((SELECT SUM(qty) FROM {order_items} WHERE order_id = o.id)) AS tickets
               FROM {orders} o WHERE o.status = 'paid' AND o.paid_at >= ?$ev GROUP BY DATE(o.paid_at)", $args)->fetchAll();
    $map = [];
    foreach ($rows as $r) $map[$r['d']] = ['revenue' => (int)$r['revenue'], 'tickets' => (int)$r['tickets']];
    $out = [];
    for ($i = 0; $i < $days; $i++) {
        $d = ldate('Y-m-d', lstrtotime($start . " +$i days"));
        $out[] = ['date' => $d] + ($map[$d] ?? ['revenue' => 0, 'tickets' => 0]);
    }
    return $out;
}

function report_discounts(?int $eventId): array
{
    $args = [];
    $w = "o.status = 'paid'";
    if ($eventId) { $w .= ' AND o.event_id = ?'; $args[] = $eventId; }
    return q("SELECT d.id, d.name, d.code, d.auto_apply, COUNT(o.id) AS uses, COALESCE(SUM(o.discount_amount),0) AS amount
              FROM {discounts} d JOIN {orders} o ON o.discount_id = d.id WHERE $w GROUP BY d.id ORDER BY amount DESC", $args)->fetchAll();
}

/**
 * Accessible SVG bar chart. Horizontal positions are percentages and vertical ones pixels, so the chart fills any
 * width without stretching its text. Values are drawn with presentation attributes so the strict CSP stays intact.
 */
function bar_chart_svg(array $points, string $valueKey, string $title, callable $fmt): string
{
    $h = 220; $top = 22; $bottom = 26; $plot = $h - $top - $bottom;
    $max = max(1, ...array_map(fn($p) => (int)$p[$valueKey], $points));
    $n = max(1, count($points));
    $slot = 100 / $n; $gap = min(0.6, $slot * 0.2);
    $pc = fn(float $v) => round($v, 3) . '%';
    $out = '';
    $real = max(0, ...array_map(fn($p) => (int)$p[$valueKey], $points));
    if ($real === 0) $out .= '<text x="50%" y="' . ($top + $plot / 2) . '" text-anchor="middle" class="axis">Nothing in this period yet</text>';
    foreach ($real === 0 ? [] : ($max >= 2 ? [1, 0.5] : [1]) as $f) {
        $y = $top + $plot - (int)round($plot * $f);
        $out .= '<line x1="0" x2="100%" y1="' . $y . '" y2="' . $y . '" class="grid grid-soft"/>';
        $out .= '<text x="0" y="' . ($y - 6) . '" class="axis">' . e($fmt((int)round($max * $f))) . '</text>';
    }
    $out .= '<line x1="0" x2="100%" y1="' . ($top + $plot) . '" y2="' . ($top + $plot) . '" class="grid"/>';
    foreach (array_values($points) as $i => $p) {
        $v = (int)$p[$valueKey];
        $bh = $v ? max(3, (int)round($plot * $v / $max)) : 2;
        $x = $i * $slot + $gap / 2;
        $label = ldate_h('j M Y', lstrtotime($p['date'])) . ': ' . $fmt($v);
        $out .= '<rect x="' . $pc($x) . '" y="' . ($top + $plot - $bh) . '" width="' . $pc($slot - $gap) . '" height="' . $bh . '" rx="3" class="bar' . ($v ? '' : ' bar-empty') . '"><title>' . e($label) . '</title></rect>';
        $last = $i === $n - 1;
        if ($n <= 31 ? ($last || ($i % 5 === 0 && $n - 1 - $i >= 3)) : ($last || $i % (int)ceil($n / 6) === 0)) {
            $anchor = $i === 0 ? 'start' : ($last ? 'end' : 'middle');
            $tx = $i === 0 ? $x : ($last ? $x + $slot - $gap : $x + ($slot - $gap) / 2);
            $out .= '<text x="' . $pc($tx) . '" y="' . ($h - 6) . '" text-anchor="' . $anchor . '" class="axis' . (!$last && $i % 10 === 5 ? ' axis-alt' : '') . '">' . e(ldate_h('j M', lstrtotime($p['date']))) . '</text>';
        }
    }
    return '<svg class="chart" width="100%" height="' . $h . '" role="img" aria-label="' . e($title) . '">' . $out . '</svg>';
}

/** Rows for the attendee export (one per ticket). */
function export_attendees(int $eventId): array
{
    $questions = q('SELECT label FROM {event_questions} WHERE event_id = ? ORDER BY sort_order, id', [$eventId])->fetchAll(Result::COLUMN);
    $rows = [array_merge(['Ticket code', 'Category', 'Attendee', 'Email', 'Phone', 'Booking', 'Paid at', 'Checked in at', 'Ticket status'], $questions)];
    $st = q("SELECT tk.code, tk.status AS tstatus, tk.checked_in_at, c.name AS category, t.name AS tier, o.buyer_name, o.buyer_email, o.buyer_phone, o.reference, o.paid_at, o.answers
             FROM {tickets} tk JOIN {orders} o ON o.id = tk.order_id JOIN {ticket_categories} c ON c.id = tk.category_id JOIN {ticket_tiers} t ON t.id = tk.tier_id
             WHERE o.event_id = ? ORDER BY o.id, tk.id", [$eventId]);
    foreach ($st as $r) {
        $ans = [];
        foreach (answers_list($r['answers']) as $x) $ans[$x['q']] = $x['a'];
        $rows[] = array_merge([format_code($r['code']), $r['category'], $r['buyer_name'], $r['buyer_email'], (string)$r['buyer_phone'], $r['reference'],
            (string)$r['paid_at'], (string)$r['checked_in_at'], $r['tstatus']], array_map(fn($q) => $ans[$q] ?? '', $questions));
    }
    return $rows;
}

function export_orders(array $filters): array
{
    [$w, $a] = orders_filter_sql($filters);
    $rows = [['Reference', 'Event', 'Buyer', 'Email', 'Phone', 'Status', 'Tickets', 'Subtotal', 'Discount', 'Tax', 'Total', 'Discount code', 'Payment', 'Created', 'Paid', 'Invoice', 'Company', 'Tax number']];
    $st = q("SELECT o.*, e.title, d.name AS dname, d.code AS dcode, (SELECT SUM(qty) FROM {order_items} WHERE order_id = o.id) AS qty
             FROM {orders} o JOIN {events} e ON e.id = o.event_id LEFT JOIN {discounts} d ON d.id = o.discount_id WHERE $w ORDER BY o.id DESC", $a);
    foreach ($st as $r) {
        $rows[] = [$r['reference'], $r['title'], $r['buyer_name'], $r['buyer_email'], (string)$r['buyer_phone'], status_label($r['status']), (int)$r['qty'],
            money_num((int)$r['subtotal']), money_num((int)$r['discount_amount']), money_num((int)$r['tax_amount']), money_num((int)$r['total']), (string)($r['dcode'] ?: $r['dname']),
            (string)$r['payment_method'], $r['created_at'], (string)$r['paid_at'], (string)$r['invoice_number'], (string)$r['buyer_company'], (string)$r['buyer_tax_id']];
    }
    return $rows;
}

function export_sales_workbook(?int $eventId, ?string $from, ?string $to): array
{
    $s = report_summary($eventId, $from, $to);
    $cur = cfg('currency');
    $summary = [['Metric', 'Value'],
        ['Revenue (' . $cur . ')', money_num($s['revenue'])], ['Gross before discounts (' . $cur . ')', money_num($s['gross'])],
        ['Discounts given (' . $cur . ')', money_num($s['discounts'])], ['Tax collected (' . $cur . ')', money_num($s['tax'])], ['Refunded (' . $cur . ')', money_num($s['refunded'])],
        ['Confirmed bookings', $s['paid_orders']], ['Tickets issued', $s['tickets']], ['Checked in', $s['checked_in']],
        ['Awaiting approval', $s['awaiting_approval']], ['Awaiting payment', $s['pending_payment']], ['Average booking (' . $cur . ')', money_num($s['avg_order'])]];
    $sheets = ['Summary' => $summary];
    if ($eventId) {
        $types = [['Category', 'Price', 'Sold', 'Held', 'Capacity', 'Gross']];
        foreach (report_by_ticket_type($eventId) as $r) {
            $types[] = [$r['category'], money_num((int)$r['price']), (int)$r['sold'], (int)$r['held'], (int)$r['capacity'], money_num((int)$r['gross'])];
        }
        $sheets['By category'] = $types;
    }
    $daily = [['Date', 'Tickets', 'Revenue']];
    foreach (report_daily($eventId, 90) as $d) $daily[] = [$d['date'], $d['tickets'], money_num($d['revenue'])];
    $sheets['Daily (90 days)'] = $daily;
    $disc = [['Discount', 'Code', 'Automatic', 'Uses', 'Amount']];
    foreach (report_discounts($eventId) as $d) $disc[] = [$d['name'], (string)$d['code'], $d['auto_apply'] ? 'Yes' : 'No', (int)$d['uses'], money_num((int)$d['amount'])];
    $sheets['Discounts'] = $disc;
    return $sheets;
}

/** Shared filter logic for the orders list and export. */
function orders_filter_sql(array $f): array
{
    $w = ['1=1']; $a = [];
    if (!empty($f['event'])) { $w[] = 'o.event_id = ?'; $a[] = (int)$f['event']; }
    if (!empty($f['status']) && in_array($f['status'], ['awaiting_approval', 'pending_payment', 'paid', 'rejected', 'cancelled', 'expired', 'refunded'], true)) {
        $w[] = 'o.status = ?'; $a[] = $f['status'];
    }
    if (!empty($f['type'])) {
        $types = ['online' => "o.payment_method IN ('stripe','offline','free')", 'comp' => "o.payment_method = 'comp'", 'door' => "o.payment_method IN ('cash','door_card')", 'imported' => "o.payment_method = 'imported'"];
        if (isset($types[$f['type']])) $w[] = $types[$f['type']];
    }
    if (!empty($f['q'])) {
        $w[] = '(o.reference = ? OR o.buyer_email LIKE ? OR o.buyer_name LIKE ?)';
        $like = '%' . addcslashes((string)$f['q'], '%_\\') . '%';
        array_push($a, strtoupper((string)$f['q']), $like, $like);
    }
    if (!empty($f['from'])) { $w[] = 'o.created_at >= ?'; $a[] = $f['from'] . ' 00:00:00'; }
    if (!empty($f['to'])) { $w[] = 'o.created_at <= ?'; $a[] = $f['to'] . ' 23:59:59'; }
    return [implode(' AND ', $w), $a];
}