<?php

final class TelemetryTimeSeriesBuilder
{
    /**
     * Erzeugt aus kategoriebasierten Telemetriedaten eine gemeinsame Zeitreihe.
     *
     * Erwartetes Tabellenschema:
     * - Zeit
     * - category
     * - wert
     *
     * @param PDO               $pdo
     * @param string            $table
     * @param string[]          $categories
     * @param DateTimeInterface $from
     * @param int               $toleranceSeconds
     *
     * @return array<int, array<string, mixed>>
     */
    public static function buildCategorySeries(
        PDO $pdo,
        string $table,
        array $categories,
        DateTimeInterface $from,
        int $toleranceSeconds = 2
    ): array {
        $table = self::validateTableName($table);
        $categories = self::validateCategories($categories);
        self::validateTolerance($toleranceSeconds);

        self::assertCategoriesExist(
            $pdo,
            $table,
            $categories
        );

        $measurements = self::loadMeasurements(
            $pdo,
            $table,
            $categories,
            $from
        );

        return self::mergeByTolerance(
            $measurements,
            $categories,
            $toleranceSeconds
        );
    }

    private static function validateTableName(string $table): string
    {
        $table = trim($table);

        if ($table === '') {
            throw new InvalidArgumentException(
                'Der Tabellenname darf nicht leer sein.'
            );
        }

        if (!preg_match('/^[A-Za-z0-9_]+$/', $table)) {
            throw new InvalidArgumentException(
                'Ungültiger Tabellenname: ' . $table
            );
        }

        return $table;
    }

    /**
     * @param mixed[] $categories
     *
     * @return string[]
     */
    private static function validateCategories(array $categories): array
    {
        if ($categories === []) {
            throw new InvalidArgumentException(
                'Es muss mindestens eine Kategorie angegeben werden.'
            );
        }

        $validated = [];

        foreach ($categories as $category) {
            if (!is_string($category)) {
                throw new InvalidArgumentException(
                    'Kategorien müssen als Zeichenketten angegeben werden.'
                );
            }

            $category = trim($category);

            if ($category === '') {
                throw new InvalidArgumentException(
                    'Eine Kategorie darf nicht leer sein.'
                );
            }

            if (!preg_match('/^[A-Za-z0-9_]+$/', $category)) {
                throw new InvalidArgumentException(
                    'Ungültiger Kategoriename: ' . $category
                );
            }

            $validated[] = $category;
        }

        $validated = array_values(array_unique($validated));

        if ($validated === []) {
            throw new InvalidArgumentException(
                'Es muss mindestens eine gültige Kategorie angegeben werden.'
            );
        }

        return $validated;
    }

    private static function validateTolerance(int $toleranceSeconds): void
    {
        if ($toleranceSeconds < 0) {
            throw new InvalidArgumentException(
                'Die Toleranz darf nicht negativ sein.'
            );
        }
    }

    /**
     * Prüft die Kategorien unabhängig vom gewählten Zeitraum.
     *
     * So wird eine unbekannte oder falsch geschriebene Kategorie nicht mit
     * einer im Zeitraum lediglich messwertlosen Kategorie verwechselt.
     *
     * @param string[] $categories
     */
    private static function assertCategoriesExist(
        PDO $pdo,
        string $table,
        array $categories
    ): void {
        $placeholders = self::buildPlaceholders(
            count($categories)
        );

        $sql = sprintf(
            'SELECT DISTINCT category
             FROM `%s`
             WHERE category IN (%s)',
            $table,
            $placeholders
        );

        $stmt = $pdo->prepare($sql);
        $stmt->execute($categories);

        $existingCategories = $stmt->fetchAll(
            PDO::FETCH_COLUMN
        );

        $existingLookup = array_fill_keys(
            array_map('strval', $existingCategories),
            true
        );

        $missingCategories = [];

        foreach ($categories as $category) {
            if (!isset($existingLookup[$category])) {
                $missingCategories[] = $category;
            }
        }

        if ($missingCategories !== []) {
            throw new RuntimeException(
                sprintf(
                    'Unbekannte Kategorie(n) in Tabelle "%s": %s',
                    $table,
                    implode(', ', $missingCategories)
                )
            );
        }
    }

    /**
     * @param string[] $categories
     *
     * @return array<int, array{
     *     time: string,
     *     timestamp: int,
     *     category: string,
     *     value: mixed
     * }>
     */
    private static function loadMeasurements(
        PDO $pdo,
        string $table,
        array $categories,
        DateTimeInterface $from
    ): array {
        $placeholders = self::buildPlaceholders(
            count($categories)
        );

        $sql = sprintf(
            'SELECT
                Zeit,
                category,
                wert
             FROM `%s`
             WHERE category IN (%s)
               AND Zeit >= ?
             ORDER BY Zeit ASC',
            $table,
            $placeholders
        );

        $parameters = $categories;
        $parameters[] = $from->format('Y-m-d H:i:s');

        $stmt = $pdo->prepare($sql);
        $stmt->execute($parameters);

        $measurements = [];

        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $time = (string)$row['Zeit'];
            $timestamp = strtotime($time);

            if ($timestamp === false) {
                throw new RuntimeException(
                    'Ungültiger Zeitstempel in Tabelle "' .
                    $table .
                    '": ' .
                    $time
                );
            }

            $value = $row['wert'];

            if (is_numeric($value)) {
                $value = (float)$value;
            }

            $measurements[] = [
                'time' => $time,
                'timestamp' => $timestamp,
                'category' => (string)$row['category'],
                'value' => $value,
            ];
        }

        return $measurements;
    }

    /**
     * Eine Messung wird nur dann zum aktuellen Punkt hinzugefügt, wenn
     *
     * 1. sie innerhalb der Toleranz liegt und
     * 2. ihre Kategorie in diesem Punkt noch nicht belegt ist.
     *
     * Dadurch werden wiederholte Werte derselben Kategorie nicht
     * stillschweigend überschrieben.
     *
     * @param array<int, array{
     *     time: string,
     *     timestamp: int,
     *     category: string,
     *     value: mixed
     * }> $measurements
     * @param string[] $categories
     *
     * @return array<int, array<string, mixed>>
     */
    private static function mergeByTolerance(
        array $measurements,
        array $categories,
        int $toleranceSeconds
    ): array {
        if ($measurements === []) {
            return [];
        }

        $points = [];
        $currentPoint = null;
        $currentTimestamp = null;

        foreach ($measurements as $measurement) {
            $category = $measurement['category'];
            $measurementTimestamp = $measurement['timestamp'];

            $withinTolerance =
                $currentTimestamp !== null
                && ($measurementTimestamp - $currentTimestamp)
                    <= $toleranceSeconds;

            $categoryIsFree =
                $currentPoint !== null
                && array_key_exists($category, $currentPoint)
                && $currentPoint[$category] === null;

            if (
                $currentPoint === null
                || !$withinTolerance
                || !$categoryIsFree
            ) {
                if ($currentPoint !== null) {
                    $points[] = $currentPoint;
                }

                $currentPoint = self::createEmptyPoint(
                    $measurement['time'],
                    $categories
                );

                $currentTimestamp = $measurementTimestamp;
            }

            $currentPoint[$category] = $measurement['value'];
        }

        if ($currentPoint !== null) {
            $points[] = $currentPoint;
        }

        return $points;
    }

    /**
     * @param string[] $categories
     *
     * @return array<string, mixed>
     */
    private static function createEmptyPoint(
        string $time,
        array $categories
    ): array {
        $point = [
            'time' => $time,
        ];

        foreach ($categories as $category) {
            $point[$category] = null;
        }

        return $point;
    }

    private static function buildPlaceholders(int $count): string
    {
        if ($count < 1) {
            throw new InvalidArgumentException(
                'Für die SQL-Abfrage wird mindestens ein Platzhalter benötigt.'
            );
        }

        return implode(
            ', ',
            array_fill(0, $count, '?')
        );
    }
}