<?php

declare(strict_types=1);

/*
 * This file is part of the Ferienpass package.
 *
 * (c) Richard Henkenjohann <richard@ferienpass.online>
 *
 * For more information visit the project website <https://ferienpass.online>
 * or the documentation under <https://docs.ferienpass.online>.
 */

namespace Ferienpass\AdminBundle\Statistics;

use Doctrine\ORM\EntityManagerInterface;
use Ferienpass\CoreBundle\Entity\Attendance;
use Ferienpass\CoreBundle\Entity\Edition;
use Ferienpass\CoreBundle\Entity\EditionTask;
use Ferienpass\CoreBundle\Entity\ParticipantLog;
use Ferienpass\CoreBundle\Entity\Payment;
use Ferienpass\CoreBundle\Entity\PaymentItem;
use Ferienpass\CoreBundle\Enum\AttendanceStatus;
use Ferienpass\CoreBundle\Enum\PaymentStatus;
use Ferienpass\CoreBundle\Repository\AttendanceRepository;
use Ferienpass\CoreBundle\Repository\EditionRepository;
use Ferienpass\CoreBundle\Repository\OfferRepositoryInterface;
use Ferienpass\CoreBundle\Settings\PageConfigManager;

/**
 * Builds one EditionStats snapshot from the database.
 *
 * Each chart is a self-contained `build…()` method so query changes stay
 * local. Aggregations live here rather than on individual repositories
 * because the stats page is the only consumer and grouping them keeps
 * reasoning easy.
 *
 * Implementation note: we always go through `repository::createQueryBuilder()`
 * — never `$em->createQueryBuilder()->from(Entity::class, …)` — because Offer
 * is registered with Doctrine via the OfferInterface ResolveTargetEntities
 * mapping. Referencing the concrete `Offer\Base` directly produces
 * "Table 'base' not found".
 */
final class EditionStatsBuilder
{
    public function __construct(
        private readonly AttendanceRepository $attendances,
        private readonly OfferRepositoryInterface $offers,
        private readonly EditionRepository $editions,
        private readonly PageConfigManager $pageConfigManager,
        private readonly EntityManagerInterface $em,
    ) {
    }

    public function build(Edition $edition, ?Edition $compareTo = null): EditionStats
    {
        $headline = $this->headlineFigures($edition);
        $compareHeadline = $compareTo ? $this->headlineFigures($compareTo) : null;

        $kpis = $this->buildKpis($headline);

        $compareDeltas = [];
        if (null !== $compareHeadline) {
            // Keys match the labels in buildKpis() so the template can do
            // stats.compareDeltas[k.label] to look up each tile's delta.
            $compareDeltas = [
                'stats.kpi.uniqueChildren' => $headline['uniqueChildren'] - $compareHeadline['uniqueChildren'],
                'stats.kpi.attendances' => $headline['attendances'] - $compareHeadline['attendances'],
                'stats.kpi.offers' => $headline['offers'] - $compareHeadline['offers'],
                'stats.kpi.utilization' => round($headline['utilization'] - $compareHeadline['utilization'], 1),
                'stats.kpi.hostsActive' => $headline['hostsActive'] - $compareHeadline['hostsActive'],
            ];
        }

        return new EditionStats(
            edition: $edition,
            kpis: $kpis,
            appWindow: $this->applicationWindow($edition),
            eventWindow: $this->eventWindow($edition),
            daily: $this->buildDailySignups($edition),
            applicationStatus: $this->buildApplicationStatus($edition, $headline['attendances']),
            funnel: $this->buildFunnel($edition),
            confirmedPerKid: $this->buildConfirmedPerKid($edition),
            appliedPerKid: $this->buildAppliedPerKid($edition),
            registrationHours: $this->buildRegistrationHours($edition),
            ages: $this->buildAgeHistogram($edition),
            categories: $this->buildCategories($edition),
            newVsReturning: $this->buildNewVsReturning($edition),
            plzDistribution: $this->buildPlzDistribution($edition),
            topOffers: $this->buildTopOffers($edition, 8),
            flopOffers: $this->buildFlopOffers($edition, 8),
            utilization: $this->buildUtilizationBuckets($edition),
            topVendors: $this->buildTopVendors($edition, 5),
            priceDistribution: $this->buildPriceDistribution($edition),
            paymentTiming: $this->buildPaymentTiming($edition),
            cancellationTiming: $this->buildCancellationTiming($edition),
            comparableEditions: $this->comparableEditions($edition),
            compareEdition: $compareTo,
            compareDeltas: $compareDeltas,
        );
    }

    /**
     * Whether the concrete Offer entity (resolved via ResolveTargetEntities)
     * maps the given association. Tenants run different Offer subclasses with
     * different traits; not every installation has `categories`, for example.
     */
    private function offerHasAssociation(string $name): bool
    {
        return $this->em->getClassMetadata($this->offers->getClassName())->hasAssociation($name);
    }

    /**
     * The "view" timezone for time-of-day charts. The DB stores all timestamps
     * in UTC; admin templates render in the configured admin timezone (set on
     * the Twig CoreExtension by RequestConfigListener). Match that here so
     * hour-of-day buckets correspond to wall-clock hours an admin recognises.
     */
    private function adminTimezone(): \DateTimeZone
    {
        $tz = $this->pageConfigManager->getPageConfig()->getTimezone();

        return new \DateTimeZone('' !== $tz ? $tz : date_default_timezone_get());
    }

    /**
     * @return array{uniqueChildren:int, attendances:int, offers:int, utilization:float, hostsActive:int}
     */
    private function headlineFigures(Edition $edition): array
    {
        $editionId = $edition->getId();

        $uniqueChildren = (int) $this->attendances->createQueryBuilder('a')
            ->select('COUNT(DISTINCT COALESCE(IDENTITY(a.participant), a.participantPseudonym))')
            ->innerJoin('a.offer', 'o')
            ->where('o.edition = :edition')
            ->andWhere('a.status <> :status')
            ->setParameter('edition', $editionId)
            ->setParameter('status', AttendanceStatus::WITHDRAWN)
            ->getQuery()->getSingleScalarResult();

        $attendances = (int) $this->attendances->createQueryBuilder('a')
            ->select('COUNT(a.id)')
            ->innerJoin('a.offer', 'o')
            ->where('o.edition = :edition')
            ->andWhere('a.status <> :status')
            ->setParameter('edition', $editionId)
            ->setParameter('status', AttendanceStatus::WITHDRAWN)
            ->getQuery()->getSingleScalarResult();

        $offers = (int) $this->offers->createQueryBuilder('o')
            ->select('COUNT(o.id)')
            ->where('o.edition = :edition')
            ->andWhere('o.variantBase IS NULL')
            ->setParameter('edition', $editionId)
            ->getQuery()->getSingleScalarResult();

        $hostsActive = (int) $this->offers->createQueryBuilder('o')
            ->select('COUNT(DISTINCT h.id)')
            ->innerJoin('o.hosts', 'h')
            ->where('o.edition = :edition')
            ->setParameter('edition', $editionId)
            ->getQuery()->getSingleScalarResult();

        $capacity = (int) $this->offers->createQueryBuilder('o')
            ->select('COALESCE(SUM(o.maxParticipants), 0)')
            ->where('o.edition = :edition')
            ->andWhere('o.variantBase IS NULL')
            ->setParameter('edition', $editionId)
            ->getQuery()->getSingleScalarResult();

        $confirmed = (int) $this->attendances->createQueryBuilder('a')
            ->select('COUNT(a.id)')
            ->innerJoin('a.offer', 'o')
            ->where('o.edition = :edition')
            ->andWhere('o.variantBase IS NULL')
            ->andWhere('a.status = :status')
            ->setParameter('edition', $editionId)
            ->setParameter('status', AttendanceStatus::CONFIRMED)
            ->getQuery()->getSingleScalarResult();

        $utilization = $capacity > 0 ? round(($confirmed / $capacity) * 100, 1) : 0.0;

        return [
            'uniqueChildren' => $uniqueChildren,
            'attendances' => $attendances,
            'offers' => $offers,
            'utilization' => $utilization,
            'hostsActive' => $hostsActive,
        ];
    }

    /**
     * @param array{uniqueChildren:int, attendances:int, offers:int, utilization:float, hostsActive:int} $h
     *
     * @return list<array{label:string, value:int|float, suffix:string}>
     */
    private function buildKpis(array $h): array
    {
        // Labels are full translation keys (the YAML nests them under stats:).
        return [
            ['label' => 'stats.kpi.uniqueChildren', 'value' => $h['uniqueChildren'], 'suffix' => ''],
            ['label' => 'stats.kpi.attendances',    'value' => $h['attendances'],    'suffix' => ''],
            ['label' => 'stats.kpi.offers',         'value' => $h['offers'],         'suffix' => ''],
            ['label' => 'stats.kpi.utilization',    'value' => $h['utilization'],    'suffix' => '%'],
            ['label' => 'stats.kpi.hostsActive',    'value' => $h['hostsActive'],    'suffix' => ''],
        ];
    }

    /** @return array{start:string, end:string}|null  ISO Y-m-d */
    private function applicationWindow(Edition $edition): ?array
    {
        $begin = null;
        $end = null;
        foreach ($edition->getTasksOfType('application_system') as $task) {
            /** @var EditionTask $task */
            $b = $task->getPeriodBegin();
            $e = $task->getPeriodEnd();
            if (null !== $b && (null === $begin || $b < $begin)) {
                $begin = $b;
            }
            if (null !== $e && (null === $end || $e > $end)) {
                $end = $e;
            }
        }

        if (null === $begin || null === $end) {
            return null;
        }

        return ['start' => $begin->format('Y-m-d'), 'end' => $end->format('Y-m-d')];
    }

    /** @return array{start:string, end:string}|null */
    private function eventWindow(Edition $edition): ?array
    {
        $holiday = $edition->getHoliday();
        if (!$holiday instanceof EditionTask || null === $holiday->getPeriodBegin() || null === $holiday->getPeriodEnd()) {
            return null;
        }

        return [
            'start' => $holiday->getPeriodBegin()->format('Y-m-d'),
            'end' => $holiday->getPeriodEnd()->format('Y-m-d'),
        ];
    }

    /** @return list<array{date:string, n:int}> */
    private function buildDailySignups(Edition $edition): array
    {
        $rows = $this->attendances->createQueryBuilder('a')
            ->select("DATE_FORMAT(a.createdAt, '%Y-%m-%d') AS day", 'COUNT(a.id) AS n')
            ->innerJoin('a.offer', 'o')
            ->where('o.edition = :edition')
            ->andWhere('a.status <> :status')
            ->setParameter('edition', $edition->getId())
            ->setParameter('status', AttendanceStatus::WITHDRAWN)
            ->groupBy('day')
            ->orderBy('day', 'ASC')
            ->getQuery()->getScalarResult();

        if ([] === $rows) {
            return [];
        }

        $byDay = [];
        foreach ($rows as $r) {
            $byDay[$r['day']] = (int) $r['n'];
        }

        $window = $this->applicationWindow($edition);
        $start = $window['start'] ?? (string) array_key_first($byDay);
        $end = $window['end'] ?? (string) array_key_last($byDay);

        try {
            $begin = new \DateTimeImmutable($start);
            $stop = new \DateTimeImmutable($end);
        } catch (\Exception) {
            return [];
        }

        $out = [];
        $cursor = $begin;
        while ($cursor <= $stop) {
            $d = $cursor->format('Y-m-d');
            $out[] = ['date' => $d, 'n' => $byDay[$d] ?? 0];
            $cursor = $cursor->modify('+1 day');
        }

        return $out;
    }

    /**
     * Stacked share of all Anmeldungen: confirmed / waitlisted / unfulfilled.
     *
     * @return array{total:int, segments:list<array{label:string, n:int, tone:string, desc:string}>}
     */
    private function buildApplicationStatus(Edition $edition, int $total): array
    {
        $counts = $this->countByStatus($edition);
        $hasPlace = ($counts[AttendanceStatus::CONFIRMED] ?? 0)
            + ($counts[AttendanceStatus::PARTICIPATED] ?? 0)
            + ($counts[AttendanceStatus::NO_SHOW] ?? 0);
        $onWaitlist = ($counts[AttendanceStatus::WAITLISTED] ?? 0)
            + ($counts[AttendanceStatus::WAITING] ?? 0);
        $noPlace = ($counts[AttendanceStatus::REJECTED] ?? 0)
            + ($counts[AttendanceStatus::UNFULFILLED] ?? 0);

        return [
            'total' => $total,
            'segments' => [
                ['label' => 'status.share.confirmed',   'n' => $hasPlace,   'tone' => 'emerald', 'desc' => 'status.share.confirmed.desc'],
                ['label' => 'status.share.waitlisted',  'n' => $onWaitlist, 'tone' => 'amber',   'desc' => 'status.share.waitlisted.desc'],
                ['label' => 'status.share.unfulfilled', 'n' => $noPlace,    'tone' => 'rose',    'desc' => 'status.share.unfulfilled.desc'],
            ],
        ];
    }

    /** @return list<array{label:string, count:int, tone:string}> */
    private function buildFunnel(Edition $edition): array
    {
        $counts = $this->countByStatus($edition);
        $total = array_sum($counts);
        $confirmed = ($counts[AttendanceStatus::CONFIRMED] ?? 0)
            + ($counts[AttendanceStatus::PARTICIPATED] ?? 0)
            + ($counts[AttendanceStatus::NO_SHOW] ?? 0);
        $participated = $counts[AttendanceStatus::PARTICIPATED] ?? 0;
        $withdrawn = $counts[AttendanceStatus::WITHDRAWN] ?? 0;

        return [
            ['label' => 'funnel.attendances',  'count' => $total,        'tone' => 'gray'],
            ['label' => 'funnel.confirmed',    'count' => $confirmed,    'tone' => 'indigo'],
            ['label' => 'funnel.participated', 'count' => $participated, 'tone' => 'emerald'],
            ['label' => 'funnel.withdrawn',    'count' => $withdrawn,    'tone' => 'rose'],
        ];
    }

    /** @return array<string,int> status => count (raw, includes WITHDRAWN) */
    private function countByStatus(Edition $edition): array
    {
        $rows = $this->attendances->createQueryBuilder('a')
            ->select('a.status', 'COUNT(a.id) AS n')
            ->innerJoin('a.offer', 'o')
            ->where('o.edition = :edition')
            ->setParameter('edition', $edition->getId())
            ->groupBy('a.status')
            ->getQuery()->getScalarResult();

        $out = [];
        foreach ($rows as $r) {
            $out[$r['status']] = (int) $r['n'];
        }

        return $out;
    }

    /** @return list<array{spots:int, n:int}> */
    private function buildConfirmedPerKid(Edition $edition): array
    {
        $rows = $this->attendances->createQueryBuilder('a')
            ->select('COALESCE(IDENTITY(a.participant), a.participantPseudonym) AS kid', 'COUNT(a.id) AS n')
            ->innerJoin('a.offer', 'o')
            ->where('o.edition = :edition')
            ->andWhere('a.status IN (:statuses)')
            ->setParameter('edition', $edition->getId())
            ->setParameter('statuses', [AttendanceStatus::CONFIRMED, AttendanceStatus::PARTICIPATED, AttendanceStatus::NO_SHOW])
            ->groupBy('kid')
            ->getQuery()->getScalarResult();

        $perKid = [];
        foreach ($rows as $r) {
            $perKid[$r['kid']] = (int) $r['n'];
        }

        // Include "applied but got nothing" kids in the 0-bucket.
        $allKids = $this->attendances->createQueryBuilder('a')
            ->select('DISTINCT COALESCE(IDENTITY(a.participant), a.participantPseudonym) AS kid')
            ->innerJoin('a.offer', 'o')
            ->where('o.edition = :edition')
            ->andWhere('a.status <> :status')
            ->setParameter('edition', $edition->getId())
            ->setParameter('status', AttendanceStatus::WITHDRAWN)
            ->getQuery()->getScalarResult();

        foreach ($allKids as $r) {
            if (!isset($perKid[$r['kid']])) {
                $perKid[$r['kid']] = 0;
            }
        }

        $hist = [];
        foreach ($perKid as $n) {
            $hist[$n] = ($hist[$n] ?? 0) + 1;
        }

        if ([] === $hist) {
            return [];
        }

        ksort($hist);
        $max = max(array_keys($hist));
        $out = [];
        for ($i = 0; $i <= $max; ++$i) {
            $out[] = ['spots' => $i, 'n' => $hist[$i] ?? 0];
        }

        return $out;
    }

    /** @return list<array{count:int, n:int}> */
    private function buildAppliedPerKid(Edition $edition): array
    {
        $rows = $this->attendances->createQueryBuilder('a')
            ->select('COALESCE(IDENTITY(a.participant), a.participantPseudonym) AS kid', 'COUNT(a.id) AS n')
            ->innerJoin('a.offer', 'o')
            ->where('o.edition = :edition')
            ->andWhere('a.status <> :status')
            ->setParameter('edition', $edition->getId())
            ->setParameter('status', AttendanceStatus::WITHDRAWN)
            ->groupBy('kid')
            ->getQuery()->getScalarResult();

        $hist = [];
        foreach ($rows as $r) {
            $hist[(int) $r['n']] = ($hist[(int) $r['n']] ?? 0) + 1;
        }

        if ([] === $hist) {
            return [];
        }

        ksort($hist);
        $max = max(array_keys($hist));
        $out = [];
        for ($i = 1; $i <= $max; ++$i) {
            $out[] = ['count' => $i, 'n' => $hist[$i] ?? 0];
        }

        return $out;
    }

    /**
     * Hour-of-day histogram. The DB stores createdAt in UTC, but the chart
     * should read in the admin's wall-clock timezone (Europe/Berlin in the
     * common case). We can't bucket on the DB side without CONVERT_TZ() (which
     * needs MySQL timezone tables loaded) — instead fetch raw timestamps and
     * convert each one in PHP. Per-edition volume is in the low thousands, so
     * this is cheap.
     *
     * @return list<array{h:int, n:int}>
     */
    private function buildRegistrationHours(Edition $edition): array
    {
        $rows = $this->attendances->createQueryBuilder('a')
            ->select('a.createdAt AS createdAt')
            ->innerJoin('a.offer', 'o')
            ->where('o.edition = :edition')
            ->andWhere('a.status <> :status')
            ->setParameter('edition', $edition->getId())
            ->setParameter('status', AttendanceStatus::WITHDRAWN)
            ->getQuery()->getScalarResult();

        $tz = $this->adminTimezone();
        $byHour = array_fill(0, 24, 0);
        foreach ($rows as $r) {
            $createdAt = $r['createdAt'] ?? null;
            if (!$createdAt instanceof \DateTimeInterface) {
                try {
                    $createdAt = new \DateTimeImmutable((string) $createdAt, new \DateTimeZone('UTC'));
                } catch (\Exception) {
                    continue;
                }
            }
            $h = (int) (clone $createdAt)->setTimezone($tz)->format('G');
            ++$byHour[$h];
        }

        $out = [];
        for ($h = 0; $h < 24; ++$h) {
            $out[] = ['h' => $h, 'n' => $byHour[$h]];
        }

        return $out;
    }

    /** @return list<array{age:string, n:int}> */
    private function buildAgeHistogram(Edition $edition): array
    {
        $rows = $this->attendances->createQueryBuilder('a')
            ->select(
                <<<'SQL'
                    (
                        CASE
                            WHEN (p.dateOfBirth IS NULL AND a.age IS NOT NULL) THEN a.age
                            WHEN (p.dateOfBirth IS NOT NULL AND d.begin IS NOT NULL) THEN TIMESTAMPDIFF(YEAR, p.dateOfBirth, MIN(d.begin))
                            ELSE 'N/A' END
                    ) AS age
                    SQL
            )
            ->innerJoin('a.offer', 'o')
            ->leftJoin('a.participant', 'p')
            ->leftJoin('o.dates', 'd')
            ->where('o.edition = :edition')
            ->andWhere('a.status <> :status')
            ->setParameter('edition', $edition->getId())
            ->setParameter('status', AttendanceStatus::WITHDRAWN)
            ->groupBy('a.id')
            ->getQuery()->getScalarResult();

        $counts = [];
        foreach ($rows as $r) {
            $counts[(string) $r['age']] = ($counts[(string) $r['age']] ?? 0) + 1;
        }

        if ([] === $counts) {
            return [];
        }

        ksort($counts, \SORT_NATURAL);

        // Suffix numeric ages with "J." (Jahre) for the chart's X-axis labels.
        // Leave "N/A" untouched.
        $out = [];
        foreach ($counts as $age => $n) {
            $label = is_numeric((string) $age) ? $age.' J.' : (string) $age;
            $out[] = ['age' => $label, 'n' => $n];
        }

        return $out;
    }

    /** @return list<array{label:string, signups:int, slots:int, offers:int}> */
    private function buildCategories(Edition $edition): array
    {
        // Categories are opt-in per tenant (via CategoriesTrait on the Offer
        // subclass). If the current Offer mapping has no `categories`
        // association, skip the query — there's nothing meaningful to chart.
        if (!$this->offerHasAssociation('categories')) {
            return [];
        }

        $rows = $this->attendances->createQueryBuilder('a')
            ->select('c.name AS category', 'COUNT(DISTINCT a.id) AS signups', 'COUNT(DISTINCT o.id) AS offers', 'SUM(DISTINCT o.maxParticipants) AS slots')
            ->innerJoin('a.offer', 'o')
            ->innerJoin('o.categories', 'c')
            ->where('o.edition = :edition')
            ->andWhere('a.status <> :status')
            ->setParameter('edition', $edition->getId())
            ->setParameter('status', AttendanceStatus::WITHDRAWN)
            ->groupBy('c.id')
            ->orderBy('signups', 'DESC')
            ->getQuery()->getScalarResult();

        $out = [];
        foreach ($rows as $r) {
            $out[] = [
                'label' => (string) $r['category'],
                'signups' => (int) $r['signups'],
                'slots' => (int) $r['slots'],
                'offers' => (int) $r['offers'],
            ];
        }

        return $out;
    }

    /**
     * Bucket the unique-children list by number of prior editions they
     * appeared in (0 / 1 / 2-3 / 4+).
     *
     * @return array{total:int, segments:list<array{label:string, n:int, tone:string, desc:string}>}
     */
    private function buildNewVsReturning(Edition $edition): array
    {
        $editionId = $edition->getId();

        // For each participant in this edition, count distinct OTHER editions
        // they've appeared in.
        $rows = $this->attendances->createQueryBuilder('a')
            ->select('IDENTITY(a.participant) AS kid', 'COUNT(DISTINCT IDENTITY(o2.edition)) AS prior_editions')
            ->innerJoin('a.offer', 'o')
            ->innerJoin(Attendance::class, 'a2', 'WITH', 'a2.participant = a.participant')
            ->innerJoin('a2.offer', 'o2', 'WITH', 'o2.edition <> :edition')
            ->where('o.edition = :edition')
            ->andWhere('a.participant IS NOT NULL')
            ->andWhere('a.status <> :status')
            ->setParameter('edition', $editionId)
            ->setParameter('status', AttendanceStatus::WITHDRAWN)
            ->groupBy('kid')
            ->getQuery()->getScalarResult();

        $allKids = (int) $this->attendances->createQueryBuilder('a')
            ->select('COUNT(DISTINCT IDENTITY(a.participant))')
            ->innerJoin('a.offer', 'o')
            ->where('o.edition = :edition')
            ->andWhere('a.participant IS NOT NULL')
            ->andWhere('a.status <> :status')
            ->setParameter('edition', $editionId)
            ->setParameter('status', AttendanceStatus::WITHDRAWN)
            ->getQuery()->getSingleScalarResult();

        $buckets = [0 => 0, 1 => 0, 2 => 0, 3 => 0];
        $countedKids = 0;
        foreach ($rows as $r) {
            $p = (int) $r['prior_editions'];
            ++$countedKids;
            if (0 === $p) {
                ++$buckets[0];
            } elseif (1 === $p) {
                ++$buckets[1];
            } elseif ($p <= 3) {
                ++$buckets[2];
            } else {
                ++$buckets[3];
            }
        }
        // Kids who don't appear in the joined query at all are first-timers.
        $buckets[0] += max(0, $allKids - $countedKids);

        return [
            'total' => $allKids,
            'segments' => [
                ['label' => 'family.first',     'n' => $buckets[0], 'tone' => 'cyan',    'desc' => 'family.first.desc'],
                ['label' => 'family.returning', 'n' => $buckets[1], 'tone' => 'sky',     'desc' => 'family.returning.desc'],
                ['label' => 'family.regular',   'n' => $buckets[2], 'tone' => 'indigo',  'desc' => 'family.regular.desc'],
                ['label' => 'family.veteran',   'n' => $buckets[3], 'tone' => 'emerald', 'desc' => 'family.veteran.desc'],
            ],
        ];
    }

    /** @return list<array{plz:string, label:string, n:int}> */
    private function buildPlzDistribution(Edition $edition): array
    {
        $rows = $this->attendances->createQueryBuilder('a')
            ->select('pa.postalCode AS plz', 'COUNT(DISTINCT p.id) AS n')
            ->innerJoin('a.offer', 'o')
            ->innerJoin('a.participant', 'p')
            ->leftJoin('p.postalAddress', 'pa')
            ->where('o.edition = :edition')
            ->andWhere('a.status <> :status')
            ->setParameter('edition', $edition->getId())
            ->setParameter('status', AttendanceStatus::WITHDRAWN)
            ->groupBy('plz')
            ->orderBy('n', 'DESC')
            ->setMaxResults(8)
            ->getQuery()->getScalarResult();

        $out = [];
        foreach ($rows as $r) {
            $plz = (string) ($r['plz'] ?? '');
            $out[] = [
                'plz' => '' === $plz ? '—' : $plz,
                'label' => '',
                'n' => (int) $r['n'],
            ];
        }

        return $out;
    }

    /** @return list<array{label:string, sub:?string, signups:int, slots:int}> */
    private function buildTopOffers(Edition $edition, int $limit): array
    {
        return $this->offersByDemand($edition, 'DESC', $limit);
    }

    /** @return list<array{label:string, sub:?string, signups:int, slots:int}> */
    private function buildFlopOffers(Edition $edition, int $limit): array
    {
        return $this->offersByDemand($edition, 'ASC', $limit, includeZero: true);
    }

    /** @return list<array{label:string, sub:?string, signups:int, slots:int}> */
    private function offersByDemand(Edition $edition, string $dir, int $limit, bool $includeZero = false): array
    {
        $qb = $this->offers->createQueryBuilder('o')
            ->select(
                'o.id AS oid',
                'o.name AS name',
                'o.maxParticipants AS slots',
                'COUNT(a.id) AS signups',
            )
            ->leftJoin('o.attendances', 'a', 'WITH', 'a.status <> :withdrawn')
            ->where('o.edition = :edition')
            ->andWhere('o.maxParticipants > 0')
            ->setParameter('edition', $edition->getId())
            ->setParameter('withdrawn', AttendanceStatus::WITHDRAWN)
            ->groupBy('o.id')
            ->orderBy('signups', $dir)
            ->setMaxResults($limit);

        if (!$includeZero) {
            $qb->having('signups > 0');
        }

        $rows = $qb->getQuery()->getScalarResult();

        $offerIds = array_map(static fn ($r) => (int) $r['oid'], $rows);
        $hostsByOffer = $this->loadHostsForOffers($offerIds);

        $out = [];
        foreach ($rows as $r) {
            $oid = (int) $r['oid'];
            $out[] = [
                'label' => (string) $r['name'],
                'sub' => $hostsByOffer[$oid] ?? null,
                'signups' => (int) $r['signups'],
                'slots' => (int) $r['slots'],
            ];
        }

        return $out;
    }

    /**
     * @param list<int> $offerIds
     *
     * @return array<int,string>
     */
    private function loadHostsForOffers(array $offerIds): array
    {
        if ([] === $offerIds) {
            return [];
        }
        $rows = $this->offers->createQueryBuilder('o')
            ->select('o.id AS oid', 'h.name AS host_name')
            ->innerJoin('o.hosts', 'h')
            ->where('o.id IN (:ids)')
            ->setParameter('ids', $offerIds)
            ->getQuery()->getScalarResult();

        $out = [];
        foreach ($rows as $r) {
            $oid = (int) $r['oid'];
            $out[$oid] = isset($out[$oid]) ? $out[$oid].', '.$r['host_name'] : (string) $r['host_name'];
        }

        return $out;
    }

    /** @return list<array{bucket:string, n:int, tone:string}> */
    private function buildUtilizationBuckets(Edition $edition): array
    {
        $rows = $this->offers->createQueryBuilder('o')
            ->select(
                'o.id AS oid',
                'o.maxParticipants AS slots',
                'COUNT(a.id) AS confirmed',
            )
            ->leftJoin('o.attendances', 'a', 'WITH', 'a.status = :status')
            ->where('o.edition = :edition')
            ->andWhere('o.variantBase IS NULL')
            ->andWhere('o.maxParticipants > 0')
            ->setParameter('edition', $edition->getId())
            ->setParameter('status', AttendanceStatus::CONFIRMED)
            ->groupBy('o.id')
            ->getQuery()->getScalarResult();

        $buckets = [
            '0' => 0, '1-24' => 0, '25-49' => 0, '50-74' => 0,
            '75-99' => 0, '100' => 0, '>100' => 0,
        ];
        foreach ($rows as $r) {
            $slots = (int) $r['slots'];
            if ($slots <= 0) {
                continue;
            }
            $u = ((int) $r['confirmed'] / $slots) * 100;
            if (0.0 === $u) {
                ++$buckets['0'];
            } elseif ($u < 25) {
                ++$buckets['1-24'];
            } elseif ($u < 50) {
                ++$buckets['25-49'];
            } elseif ($u < 75) {
                ++$buckets['50-74'];
            } elseif ($u < 100) {
                ++$buckets['75-99'];
            } elseif (100.0 === $u) {
                ++$buckets['100'];
            } else {
                ++$buckets['>100'];
            }
        }

        return [
            ['bucket' => '0%',     'n' => $buckets['0'],     'tone' => 'rose'],
            ['bucket' => '1–24%',  'n' => $buckets['1-24'],  'tone' => 'rose'],
            ['bucket' => '25–49%', 'n' => $buckets['25-49'], 'tone' => 'amber'],
            ['bucket' => '50–74%', 'n' => $buckets['50-74'], 'tone' => 'amber'],
            ['bucket' => '75–99%', 'n' => $buckets['75-99'], 'tone' => 'emerald'],
            ['bucket' => '100%',   'n' => $buckets['100'],   'tone' => 'emerald'],
            // Over-booking is an operational problem, not a success metric —
            // paint it red.
            ['bucket' => '> 100%', 'n' => $buckets['>100'],  'tone' => 'rose'],
        ];
    }

    /** @return list<array{name:string, offers:int, signups:int, util:int}> */
    private function buildTopVendors(Edition $edition, int $limit): array
    {
        $rows = $this->offers->createQueryBuilder('o')
            ->select(
                'h.id AS hid',
                'h.name AS name',
                'COUNT(DISTINCT o.id) AS offers',
                'COUNT(a.id) AS signups',
                'SUM(o.maxParticipants) AS slots',
            )
            ->innerJoin('o.hosts', 'h')
            ->leftJoin('o.attendances', 'a', 'WITH', 'a.status <> :withdrawn')
            ->where('o.edition = :edition')
            ->setParameter('edition', $edition->getId())
            ->setParameter('withdrawn', AttendanceStatus::WITHDRAWN)
            ->groupBy('h.id')
            ->orderBy('signups', 'DESC')
            ->setMaxResults($limit)
            ->getQuery()->getScalarResult();

        $out = [];
        foreach ($rows as $r) {
            $slots = (int) $r['slots'];
            $signups = (int) $r['signups'];
            $util = $slots > 0 ? (int) round(($signups / $slots) * 100) : 0;
            $out[] = [
                'name' => (string) $r['name'],
                'offers' => (int) $r['offers'],
                'signups' => $signups,
                'util' => $util,
            ];
        }

        return $out;
    }

    /** @return list<array{label:string, n:int, tone:string, axisLabel:string, highlight:bool}> */
    private function buildPriceDistribution(Edition $edition): array
    {
        $rows = $this->offers->createQueryBuilder('o')
            ->select('o.fee AS fee')
            ->where('o.edition = :edition')
            ->andWhere('o.variantBase IS NULL')
            ->setParameter('edition', $edition->getId())
            ->getQuery()->getScalarResult();

        // fee is stored in cents; null = no fee → treat as free.
        $counts = array_fill(0, 27, 0);
        foreach ($rows as $r) {
            $cents = (int) ($r['fee'] ?? 0);
            $euro = (int) floor($cents / 100);
            if ($euro <= 0) {
                ++$counts[0];
            } elseif ($euro > 25) {
                ++$counts[26];
            } else {
                ++$counts[$euro];
            }
        }

        // Price isn't a quality signal — a cheap offer isn't "better" than an
        // expensive one. Keep all bars neutral gray so the chart reads as a
        // pure distribution.
        $out = [];
        for ($i = 0; $i <= 26; ++$i) {
            $out[] = [
                'label' => 0 === $i ? 'price.free' : (26 === $i ? 'price.gt25' : $i.' €'),
                'n' => $counts[$i],
                'tone' => 'gray',
                'axisLabel' => 26 === $i ? '>25 €' : (0 === $i ? '0 €' : (0 === $i % 5 ? $i.' €' : '')),
                'highlight' => false,
            ];
        }

        return $out;
    }

    /**
     * Days between an attendance becoming "confirmed" (per ParticipantLog) and
     * the matching Payment becoming "paid". Last bucket = confirmed but unpaid.
     *
     * @return list<array{label:string, n:int, tone:string, axisLabel:string, highlight:bool}>
     */
    private function buildPaymentTiming(Edition $edition): array
    {
        // Join Payment as a class-level entity rather than the `pi.payment`
        // association, because the association is fetch=EAGER and Doctrine
        // refuses WITH conditions on those. CASE WHEN py.status = :paid …
        // would also work but DQL's CASE rejects a literal NULL in the ELSE
        // arm, which we'd need to express "no paid payment yet".
        $rows = $this->attendances->createQueryBuilder('a')
            ->select(
                'a.id AS aid',
                'MIN(pl.createdAt) AS confirmed_at',
                'MIN(py.createdAt) AS paid_at',
            )
            ->innerJoin('a.offer', 'o')
            ->leftJoin(ParticipantLog::class, 'pl', 'WITH', 'pl.attendance = a AND pl.transitionTo = :confirmed_status')
            ->leftJoin(PaymentItem::class, 'pi', 'WITH', 'pi.attendance = a')
            ->leftJoin(Payment::class, 'py', 'WITH', 'py = pi.payment AND py.status = :paid')
            ->where('o.edition = :edition')
            ->andWhere('a.status IN (:positive)')
            ->setParameter('edition', $edition->getId())
            ->setParameter('confirmed_status', AttendanceStatus::CONFIRMED)
            ->setParameter('paid', PaymentStatus::PAID)
            ->setParameter('positive', [AttendanceStatus::CONFIRMED, AttendanceStatus::PARTICIPATED, AttendanceStatus::NO_SHOW])
            ->groupBy('a.id')
            ->getQuery()->getScalarResult();

        $maxDays = 21;
        $counts = array_fill(0, $maxDays + 2, 0);
        $unpaidIndex = $maxDays + 1;

        foreach ($rows as $r) {
            $confirmedAt = $r['confirmed_at'] ?? null;
            $paidAt = $r['paid_at'] ?? null;

            if (null === $paidAt) {
                ++$counts[$unpaidIndex];
                continue;
            }

            try {
                $c = new \DateTimeImmutable($confirmedAt ?? $paidAt);
                $p = new \DateTimeImmutable($paidAt);
            } catch (\Exception) {
                ++$counts[$unpaidIndex];
                continue;
            }
            $diff = $p < $c ? 0 : (int) $c->diff($p)->days;
            if ($diff > $maxDays) {
                $diff = $maxDays;
            }
            ++$counts[$diff];
        }

        $out = [];
        for ($i = 0; $i <= $maxDays; ++$i) {
            $out[] = [
                'label' => 0 === $i ? 'payment.day.0' : 'payment.day.n',
                'n' => $counts[$i],
                'tone' => $i <= 7 ? 'emerald' : ($i <= 14 ? 'amber' : 'gray'),
                'axisLabel' => 0 === $i ? '0 T.' : (0 === $i % 5 ? $i.' T.' : ''),
                'highlight' => false,
            ];
        }
        $out[] = [
            'label' => 'payment.unpaid',
            'n' => $counts[$unpaidIndex],
            'tone' => 'rose',
            'axisLabel' => 'payment.unpaid.axis',
            'highlight' => true,
        ];

        return $out;
    }

    /**
     * Withdrawals over time: 0 = same day, 28 = "28+ days before event".
     *
     * @return list<array{label:string, n:int, tone:string, axisLabel:string, highlight:bool}>
     */
    private function buildCancellationTiming(Edition $edition): array
    {
        $rows = $this->attendances->createQueryBuilder('a')
            ->select(
                'a.id AS aid',
                'MIN(pl.createdAt) AS withdrawn_at',
                'MIN(d.begin) AS event_at',
            )
            ->innerJoin('a.offer', 'o')
            ->innerJoin('o.dates', 'd')
            ->innerJoin(ParticipantLog::class, 'pl', 'WITH', 'pl.attendance = a AND pl.transitionTo = :withdrawn')
            ->where('o.edition = :edition')
            ->andWhere('a.status = :withdrawn')
            ->setParameter('edition', $edition->getId())
            ->setParameter('withdrawn', AttendanceStatus::WITHDRAWN)
            ->groupBy('a.id')
            ->getQuery()->getScalarResult();

        $maxDays = 28;
        $counts = array_fill(0, $maxDays + 1, 0);
        foreach ($rows as $r) {
            $wAt = $r['withdrawn_at'] ?? null;
            $eAt = $r['event_at'] ?? null;
            if (null === $wAt || null === $eAt) {
                continue;
            }
            try {
                $w = new \DateTimeImmutable($wAt);
                $e = new \DateTimeImmutable($eAt);
            } catch (\Exception) {
                continue;
            }
            if ($w > $e) {
                // withdrew after event — data noise
                continue;
            }
            $diff = (int) $w->diff($e)->days;
            if ($diff > $maxDays) {
                $diff = $maxDays;
            }
            ++$counts[$diff];
        }

        // Bars run right-to-left along a timeline: "28+ Tage vorher" sits at
        // the far left, the disruptive same-day cancellations on the far right.
        // We build the array in reverse-day order so the histogram renderer
        // (which paints left-to-right) produces the desired reading direction.
        $out = [];
        for ($d = $maxDays; $d >= 0; --$d) {
            $out[] = [
                'label' => 0 === $d
                    ? 'cancellation.day.same'
                    : ($maxDays === $d ? 'cancellation.day.28plus' : 'cancellation.day.n'),
                'n' => $counts[$d],
                'tone' => 0 === $d ? 'rose' : ($d <= 7 ? 'amber' : 'gray'),
                'axisLabel' => 0 === $d ? 'cancellation.day.same.axis' : ($maxDays === $d ? '28+' : (0 === $d % 5 ? (string) $d : '')),
                'highlight' => 0 === $d,
            ];
        }

        return $out;
    }

    /**
     * Candidate editions for the ComparePicker: all other editions, ordered
     * by holiday.periodBegin DESC (most recent first), capped at 10.
     *
     * @return list<Edition>
     */
    private function comparableEditions(Edition $edition): array
    {
        return $this->editions->createQueryBuilder('e')
            ->leftJoin('e.tasks', 't', 'WITH', "t.type = 'holiday'")
            ->where('e.id <> :id')
            ->setParameter('id', $edition->getId())
            ->orderBy('t.periodBegin', 'DESC')
            ->setMaxResults(10)
            ->getQuery()
            ->getResult();
    }
}
