<?php
/**
 * @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('PaCIM-PAS');


$count = $connection->fetchColumn('SELECT
    COUNT(*)
FROM pacim_booking b
    LEFT JOIN pacim_customer c ON c.customer_id = b.booking_customer
    LEFT JOIN pacim_invoice d ON d.invoice_id = b.booking_invoice
WHERE b.booking_begin = :date
ORDER by b.booking_serial ASC', [
        'date' => $request->getUriFragment('date')
]);


$arr_bookings = $connection->fetchAssoc('
SELECT
     JSON_OBJECT(
    		JSON_OBJEC("id", c.customer_id),
    		JSON_OBJECT("firstname", c.customer_firstname),
    		JSON_OBJECT(
    ) AS customer,
    b.booking_no,
    UPPER(b.booking_serial) AS booking_serial,
    UPPER(REGEXP_REPLACE(TRIM(b.booking_serial), "(^[a-z|0-9]{1,3})[-| ]([a-z|0-9]{1,2})", "\\\1 - \\\2 \\\3")) AS license_plate,
    b.booking_id,
    b.booking_checkin,
    DATE_FORMAT(b.booking_checkin, "%H:%i") AS checkin_zeit,
    DATE_FORMAT(b.booking_checkin, "%d.%m.%Y") AS checkin_datum,
    b.booking_type,
    REPLACE(REPLACE(REPLACE(REPLACE(b.booking_type, "Hallenstellplatz", "Halle"), "Außenstellplatz", "Außen"), "Hallenparkplatz", "Halle"), "Außenparkplatz", "Außen") AS booking_parkplatz,
    (SELECT product_id FROM pacim_invoice_pos WHERE (product_id=8 OR product_id=10) AND invoice_id=b.booking_invoice LIMIT 1) AS booking_special,
    (IF(b.booking_event = "", (
        SELECT
        e.event_calendar_title AS booking_event
        FROM pacim_events e
        WHERE DATE_FORMAT(e.event_calendar_begin, "%Y-%m-%d") = b.booking_begin
        AND DATE_FORMAT(e.event_calendar_end, "%Y-%m-%d") = b.booking_end
        LIMIT 1), b.booking_event)) AS booking_event,
    b.booking_begin,
    b.booking_end,
    DATE_FORMAT(b.booking_begin, "%d.%m.") AS booking_von,
    DATE_FORMAT(b.booking_end, "%d.%m.%Y") AS booking_bis,
    DATE_FORMAT(b.booking_begin, "%Y/%m/%d") AS link_von,
    DATE_FORMAT(b.booking_end, "%Y/%m/%d") AS link_bis,
    DATEDIFF(b.booking_end, b.booking_begin) AS booking_duration,
    d.invoice_status,
    d.invoice_no,
    d.invoice_id,
    d.invoice_price,
    d.invoice_payment,
    d.invoice_coupon,
    (SELECT
     FORMAT(SUM(
         IF((product_id <= 2 AND (d.invoice_coupon = 2 OR invoice_pos_discount_type = 2)),
            (invoice_pos_price*0.9*invoice_pos_quantity*1.19),
            IF((product_id <= 2 AND (d.invoice_coupon = 1 OR invoice_pos_discount_type = 1)),
               ((invoice_pos_price*invoice_pos_quantity*1.19)-5),
               (invoice_pos_price*invoice_pos_quantity*1.19)
              ))
     ), 2) AS price
     FROM pacim_invoice_pos
     WHERE invoice_id=b.booking_invoice) AS price
FROM pacim_booking b
    LEFT JOIN pacim_customer c ON c.customer_id = b.booking_customer
    LEFT JOIN pacim_invoice d ON d.invoice_id = b.booking_invoice
WHERE b.booking_begin = :date
ORDER by b.booking_serial ASC', [
	'date' => $request->getUriFragment('date')
]);

/*if (empty($arr_bookings)) {
    throw new StatusCode\NotFoundException('Entry not available');
}*/

return $response->build(200, [], array(
	'count' => $count,
	'entries' => $arr_bookings
));