<?php
require_once("includes/config.inc.php");
//echo API_CONNECTION;
//die();
/**
 * @var \Fusio\Engine\ConnectorInterface $connector
 * @var \Fusio\Engine\ContextInterface $context
 * @var \Fusio\Engine\RequestInterface $request
 * @var \Fusio\Engine\Response\FactoryInterface $response
 * @var \Fusio\Engine\ProcessorInterface $processor
 * @var \Psr\Log\LoggerInterface $logger
 * @var \Psr\SimpleCache\CacheInterface $cache
 */

use PSX\Http\Exception as StatusCode;

/** @var \Doctrine\DBAL\Connection $connection */
$connection = $connector->getConnection( API_CONNECTION );

// ---- Parameter sauber aufbereiten -------------------------------------------
// strtotime + festes Format normalisiert die Eingabe auf YYYY-MM-DD => injection-sicher
$dateParam = $request->getParameter('date');
$limit     = (int) $request->getParameter('limit');

$startDate     = $dateParam ? date('Y-m-d', strtotime($dateParam)) : null;
$year          = $startDate ? (int) date('Y', strtotime($startDate)) : (int) date('Y');
$nextYearStart = ($year + 1) . '-01-01';

$lower = $startDate ? '"' . $startDate . '"' : 'CURDATE()';

// sargbare Range-Bedingung (kein DATE_FORMAT auf der Spalte -> Index nutzbar)
$cond_b  = 'b.booking_begin >= '  . $lower . ' AND b.booking_begin < "'  . $nextYearStart . '"';
$cond_bs = 'bs.booking_begin >= ' . $lower . ' AND bs.booking_begin < "' . $nextYearStart . '"';


// ---- Result-Cache -----------------------------------------------------------
// Das Dashboard wird häufig gelesen, ändert sich aber nur im Sekundentakt (Check-ins).
// Die komplette Antwort wird kurz gecacht -> auf einen Cache-Hit folgen 0 DB-Queries.
// TTL nach Geschmack anpassen; auf 0 setzen deaktiviert das Caching komplett.
$cacheTtl = 10; // Sekunden
$cacheKey = 'pacim_getevents_' . md5(($startDate ?: 'today') . '|' . $limit);

if ($cacheTtl > 0) {
    $cached = $cache->get($cacheKey);
    if ($cached !== null) {
        return $response->build(200, [], $cached);
    }
}


// ---- COUNT ------------------------------------------------------------------
// Gegenüber dem Original:
//  * der LEFT JOIN auf pacim_invoice (ON i.invoice_id = b.booking_id) ist entfernt:
//    LEFT JOIN auf einen eindeutigen PK -> beeinflusst COUNT(*) nachweislich nicht.
//  * pacim_events_info entfernt: ebenfalls LEFT JOIN auf eindeutigen Key -> ohne Einfluss.
//  * der Event-Join ist sargbar (Range statt DATE_FORMAT auf der Spalte) -> Index nutzbar.
// Der zurückgegebene Wert bleibt damit identisch (Buchung × Event-Treffer).
$count = $connection->fetchColumn('
    SELECT COUNT(*)
    FROM pacim_booking b
        LEFT JOIN pacim_events e
            ON e.event_calendar_begin >= b.booking_begin
            AND e.event_calendar_begin < b.booking_begin + INTERVAL 1 DAY
            AND (e.event_calendar_title = b.booking_event
                 OR (e.event_calendar_end >= b.booking_end
                     AND e.event_calendar_end < b.booking_end + INTERVAL 1 DAY))
    WHERE ' . $cond_b . '
');


// ---- STATS EINMALIG pro (begin,end) -----------------------------------------
// Vorher wurde diese Aggregation zweimal über pacim_booking gefahren: einmal in der
// Tages-Query (GROUP BY begin) und einmal in der st-Derived-Table der Schiffe-Query
// (GROUP BY begin,end). Da SUM additiv ist, reicht EIN Scan auf (begin,end)-Ebene;
// die Tageswerte werden unten in PHP aufsummiert (mathematisch identisch).
$stats_be = $connection->fetchAll('
    SELECT
        b.booking_begin AS s_begin,
        b.booking_end   AS s_end,
        SUM(IF(b.booking_type = 1 AND i.invoice_status != 3, 1, 0)) AS halle,
        SUM(IF(b.booking_type = 1 AND i.invoice_status != 3 AND b.booking_checkin IS NOT NULL, 1, 0)) AS halle_checkin,
        SUM(IF(b.booking_type = 2 AND i.invoice_status != 3, 1, 0)) AS aussen,
        SUM(IF(b.booking_type = 2 AND i.invoice_status != 3 AND b.booking_checkin IS NOT NULL, 1, 0)) AS aussen_checkin,
        SUM(IF(i.invoice_status != 3 AND vp.has_valet = 1, 1, 0)) AS valet,
        SUM(IF(i.invoice_status != 3 AND vp.has_valet = 1 AND b.booking_checkin IS NOT NULL, 1, 0)) AS valet_checkin,
        SUM(IF(i.invoice_status != 3, 1, 0)) AS fahrzeuge,
        SUM(IF(i.invoice_status != 3 AND b.booking_checkin IS NULL, 1, 0)) AS wait_checkin,
        SUM(IF(i.invoice_status = 3, 1, 0)) AS storno
    FROM pacim_booking b
        LEFT JOIN pacim_invoice i ON i.invoice_id = b.booking_invoice
        LEFT JOIN (
            SELECT invoice_id, 1 AS has_valet
            FROM pacim_invoice_pos
            WHERE product_id = 8
            GROUP BY invoice_id
        ) vp ON vp.invoice_id = b.booking_invoice
    WHERE ' . $cond_b . '
    GROUP BY b.booking_begin, b.booking_end
    ORDER BY b.booking_begin ASC, b.booking_end ASC
');

// Stats für O(1)-Zugriff nach [begin][end] indexieren
$STAT_FIELDS = array('halle','halle_checkin','aussen','aussen_checkin','valet','valet_checkin','fahrzeuge','wait_checkin','storno');
$statsByBE = array();
foreach ($stats_be as $r) {
    $statsByBE[$r['s_begin']][$r['s_end']] = $r;
}


// ---- TAGES-ÜBERSICHT: (begin,end)-Stats in PHP auf begin aufsummieren --------
$today  = date('Y-m-d');
$dayAgg = array(); // begin => summierte Stats (Reihenfolge bleibt aufsteigend, da stats_be sortiert ist)

foreach ($stats_be as $r) {
    $bg = $r['s_begin'];
    if (!isset($dayAgg[$bg])) {
        $dayAgg[$bg] = array_fill_keys($STAT_FIELDS, 0);
    }
    foreach ($STAT_FIELDS as $f) {
        $dayAgg[$bg][$f] += (int) $r[$f];
    }
}

$arr_events = array();
foreach ($dayAgg as $bg => $stats) {
    $ts  = strtotime($bg);
    $row = array(
        'year'     => date('Y', $ts),
        'month'    => date('m', $ts),
        'day'      => date('d', $ts),
        'date'     => $bg,
        'is_today' => ($bg === $today ? 1 : 0),
    );
    // als String belassen -> identisch zur bisherigen SQL-Ausgabe (SUM liefert String)
    foreach ($STAT_FIELDS as $f) {
        $row[$f] = (string) $stats[$f];
    }
    $arr_events[] = $row;

    if ($limit > 0 && count($arr_events) >= $limit) {
        break;
    }
}


// ---- SCHIFFE: nur noch Meta (Titel/Logo/last_booking) je (begin,end,title) ---
// Die Statistik-Spalten (halle, aussen, ...) kommen nicht mehr aus einer zweiten
// Aggregation, sondern werden unten aus $statsByBE gemerged.
$ships_raw = $connection->fetchAll('
    SELECT
        b.booking_begin AS _key_date,
        b.booking_begin AS s_begin,
        b.booking_end   AS s_end,
        DATE_FORMAT(b.booking_begin, "%Y-%m-%d") AS date,
        DATE_FORMAT(b.booking_begin, "%Y/%m/%d") AS date_link,
        IF(ei.event_title IS NOT NULL, ei.event_title, b.booking_event) AS title,
        MAX(IF(ei.event_logo IS NOT NULL, ei.event_logo, "img/seaside/parken-warnemuende.png")) AS logo,
        MAX(lb.last_booking) AS last_booking,
        IF(b.booking_begin = CURDATE(), 1, 0) AS is_today
    FROM pacim_booking b
        LEFT JOIN pacim_events e
            ON e.event_calendar_begin >= b.booking_begin
            AND e.event_calendar_begin < b.booking_begin + INTERVAL 1 DAY
            AND (e.event_calendar_title = b.booking_event
                 OR (e.event_calendar_end >= b.booking_end
                     AND e.event_calendar_end < b.booking_end + INTERVAL 1 DAY))
        LEFT JOIN pacim_events_info ei ON ei.event_id = e.event_calendar_info_id
        LEFT JOIN (
            SELECT lb_begin, lb_end, last_booking FROM (
                SELECT
                    bs.booking_begin AS lb_begin,
                    bs.booking_end   AS lb_end,
                    DATE_FORMAT(isu.invoice_date, "%d.%m.%Y") AS last_booking,
                    ROW_NUMBER() OVER (
                        PARTITION BY bs.booking_begin, bs.booking_end
                        ORDER BY isu.invoice_id DESC
                    ) AS rn
                FROM pacim_booking bs
                    JOIN pacim_invoice isu ON isu.invoice_id = bs.booking_invoice
                    JOIN pacim_customer csu ON csu.customer_id = bs.booking_customer
                WHERE isu.invoice_status != 3
                    AND csu.customer_source = "Parken-am-Schiff.de"
                    AND ' . $cond_bs . '
            ) z WHERE rn = 1
        ) lb ON lb.lb_begin = b.booking_begin AND lb.lb_end = b.booking_end
    WHERE ' . $cond_b . '
    GROUP BY b.booking_begin, b.booking_end, title
    ORDER BY b.booking_begin ASC, title ASC
');

// Schiffe nach Tag gruppieren + Stats aus $statsByBE mergen
$shipsByDate = array();
foreach ($ships_raw as $row) {
    $st = isset($statsByBE[$row['s_begin']][$row['s_end']])
        ? $statsByBE[$row['s_begin']][$row['s_end']]
        : null;
    foreach ($STAT_FIELDS as $f) {
        $row[$f] = $st !== null ? $st[$f] : '0';
    }
    $shipsByDate[$row['_key_date']][] = $row;
}


$arr_items = array();
$arr_ships = array();
if($count > 0) {

	foreach($arr_events as $eventKey => $event) {

		// statt eigener Query pro Tag: vorgruppierte Schiffe holen
		$arr_ships[ $eventKey ] = isset($shipsByDate[ $event['date'] ])
			? $shipsByDate[ $event['date'] ]
			: array();

		foreach($arr_ships[ $eventKey ] as $key => $ship) {

			$arr_ships[ $eventKey ][ $key ] = array(
				'date' => $ship['date'],
				'date_link' => $ship['date_link'],
				'title' => $ship['title'],
				'logo' => $ship['logo'],
				'is_today' => $ship['is_today'],
				'last_booking' => $ship['last_booking'],
				'stats' => array(
					'fahrzeuge' => array(
						'all' => (int)$ship['fahrzeuge'],
						'wait' => (int)$ship['wait_checkin'],
						'checkin' => (int)$ship['fahrzeuge']-$ship['wait_checkin'],
						'percent' => array(
							'status' => $ship['fahrzeuge'] > 0 ? number_format( ( ( $ship['fahrzeuge']-$ship['wait_checkin'] ) / $ship['fahrzeuge'] * 100 ), 2, '.', false ) : 0,
							'rest' => $ship['fahrzeuge'] > 0 ? number_format( ( 100-( ( $ship['fahrzeuge']-$ship['wait_checkin'] ) / $ship['fahrzeuge'] * 100 ) ), 2, '.', false ) : 100
						)
					),
					'halle' => array(
						'all' => (int)$ship['halle'],
						'wait' => (int)$ship['halle']-$ship['halle_checkin'],
						'checkin' => (int)$ship['halle_checkin'],
						'percent' => array(
							'status' => $ship['halle_checkin'] > 0 && $ship['halle'] > 0 ? number_format( ( $ship['halle_checkin'] / $ship['halle'] * 100 ), 2, '.', false ) : 0,
							'rest' => $ship['halle_checkin'] > 0 && $ship['halle'] > 0 ? number_format( ( 100-$ship['halle_checkin'] / $ship['halle'] * 100 ), 2, '.', false ) : 100
						)
					),
					'aussen' => array(
						'all' => (int)$ship['aussen'],
						'wait' => (int)$ship['aussen']-$ship['aussen_checkin'],
						'checkin' => (int)$ship['aussen_checkin'],
						'percent' => array(
							'status' => $ship['aussen_checkin'] > 0 && $ship['aussen'] > 0 ? number_format( ( $ship['aussen_checkin'] / $ship['aussen'] * 100 ), 2, '.', false ) : 0,
							'rest' => $ship['aussen_checkin'] > 0 && $ship['aussen'] > 0 ? number_format( ( 100-$ship['aussen_checkin'] / $ship['aussen'] * 100 ), 2, '.', false ) : 100
						)
					),
					'valet' => array(
						'all' => (int)$ship['valet'],
						'wait' => (int)$ship['valet']-$event['valet_checkin'],
						'checkin' => (int)$ship['valet_checkin'],
						'percent' => array(
							'status' => $ship['valet_checkin'] > 0 && $ship['valet'] > 0 ? number_format( ( $ship['valet_checkin'] / $ship['valet'] * 100 ), 2, '.', false ) : 0,
							'rest' => $ship['valet_checkin'] > 0 && $ship['valet'] > 0 ? number_format( ( 100-$ship['valet_checkin'] / $ship['valet'] * 100 ), 2, '.', false ) : 100
						)
					),
					'storno' => $ship['storno']
				),
			);

			if($ship['fahrzeuge'] > 0) {
				$arr_ships[ $eventKey ][ $key ]['percent'] = number_format(($arr_ships[ $eventKey ][ $key ]['stats']['fahrzeuge']['checkin']/$ship['fahrzeuge']*100), 2, '.', '');
				$arr_ships[ $eventKey ][ $key ]['percent_rest'] = 100-$arr_ships[ $eventKey ][ $key ]['percent'];
			} else {
				$arr_ships[ $eventKey ][ $key ]['percent'] = 0;
				$arr_ships[ $eventKey ][ $key ]['percent_rest'] = 0;
			}
		}

		$arr_items[ $event['date'] ] = array(

			'calendar' => array(
				'day' => $event['day'],
				'month' => $event['month'],
				'year' => $event['year'],
				'date' => $event['date'],
				'today' => $event['is_today']
			),
			'stats' => array(
				'fahrzeuge' => array(
					'all' => $event['fahrzeuge'],
					'wait' => $event['wait_checkin'],
					'checkin' => $event['fahrzeuge']-$event['wait_checkin'],
					'percent' => array(
						'status' => $event['fahrzeuge'] > 0 ? number_format( ( ( $event['fahrzeuge']-$event['wait_checkin'] ) / $event['fahrzeuge'] * 100 ), 2, '.', false ) : 0,
						'rest' => $event['fahrzeuge'] > 0 ? number_format( ( 100-( ( $event['fahrzeuge']-$event['wait_checkin'] ) / $event['fahrzeuge'] * 100 ) ), 2, '.', false ) : 100
					)
				),
				'halle' => array(
					'all' => $event['halle'],
					'wait' => $event['halle']-$event['halle_checkin'],
					'checkin' => $event['halle_checkin'],
					'percent' => array(
						'status' => $event['halle_checkin'] > 0 && $event['halle'] > 0 ? number_format( ( $event['halle_checkin'] / $event['halle'] * 100 ), 2, '.', false ) : 0,
						'rest' => $event['halle_checkin'] > 0 && $event['halle'] > 0 ? number_format( ( 100-$event['halle_checkin'] / $event['halle'] * 100 ), 2, '.', false ) : 100
					)
				),
				'aussen' => array(
					'all' => $event['aussen'],
					'wait' => $event['aussen']-$event['aussen_checkin'],
					'checkin' => $event['aussen_checkin'],
					'percent' => array(
						'status' => $event['aussen_checkin'] > 0 && $event['aussen'] > 0 ? number_format( ( $event['aussen_checkin'] / $event['aussen'] * 100 ), 2, '.', false ) : 0,
						'rest' => $event['aussen_checkin'] > 0 && $event['aussen'] > 0 ? number_format( ( 100-$event['aussen_checkin'] / $event['aussen'] * 100 ), 2, '.', false ) : 100
					)
				),
				'valet' => array(
					'all' => $event['valet'],
					'wait' => $event['valet']-$event['valet_checkin'],
					'checkin' => $event['valet_checkin'],
					'percent' => array(
						'status' => $event['valet_checkin'] > 0 && $event['valet'] > 0 ? number_format( ( $event['valet_checkin'] / $event['valet'] * 100 ), 2, '.', false ) : 0,
						'rest' => $event['valet_checkin'] > 0 && $event['valet'] > 0 ? number_format( ( 100-$event['valet_checkin'] / $event['valet'] * 100 ), 2, '.', false ) : 100
					)
				),
				'storno' => $event['storno']
			),
			'ships' => $arr_ships[ $eventKey ]
		);

	}

}

$payload = array(
	'status' => 'success',
	'count' => $count,
	'connection' => API_CONNECTION,
	'entries' => $arr_items
);

if ($cacheTtl > 0) {
	$cache->set($cacheKey, $payload, $cacheTtl);
}

return $response->build(200, [], $payload);