<?php

namespace AmeliaBooking\Infrastructure\Repository\Booking\Event;

use AmeliaBooking\Domain\Collection\Collection;
use AmeliaBooking\Domain\Common\Exceptions\InvalidArgumentException;
use AmeliaBooking\Domain\Entity\Booking\Event\Event;
use AmeliaBooking\Domain\Factory\Booking\Appointment\CustomerBookingFactory;
use AmeliaBooking\Domain\Factory\Booking\Event\EventFactory;
use AmeliaBooking\Domain\Repository\Booking\Event\EventRepositoryInterface;
use AmeliaBooking\Domain\Services\DateTime\DateTimeService;
use AmeliaBooking\Infrastructure\Common\Exceptions\QueryExecutionException;
use AmeliaBooking\Infrastructure\Licence;
use AmeliaBooking\Infrastructure\Repository\AbstractRepository;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\CustomerBookingsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\CustomerBookingsToEventsPeriodsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\CustomerBookingToEventsTicketsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\EventsPeriodsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\EventsProvidersTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\EventsTagsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\EventsTicketsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Coupon\CouponsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Gallery\GalleriesTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Location\LocationsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Payment\PaymentsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\User\UsersTable;

/**
 * Class EventRepository
 *
 * @package AmeliaBooking\Infrastructure\Repository\Booking\Event
 */
class EventRepository extends AbstractRepository implements EventRepositoryInterface
{
    public const FACTORY = EventFactory::class;

    /**
     * @param Event $entity
     *
     * @return int
     * @throws QueryExecutionException
     */
    public function add($entity)
    {
        $data = $entity->toArray();

        $params = [
            ':bookingOpens'         => $data['bookingOpens'] ? DateTimeService::getCustomDateTimeInUtc($data['bookingOpens']) : null,
            ':bookingCloses'        => $data['bookingCloses'] ? DateTimeService::getCustomDateTimeInUtc($data['bookingCloses']) : null,
            ':bookingOpensRec'      => $data['bookingOpensRec'],
            ':bookingClosesRec'     => $data['bookingClosesRec'],
            ':status'               => $data['status'],
            ':name'                 => $data['name'],
            ':description'          => $data['description'],
            ':color'                => $data['color'],
            ':price'                => $data['price'],
            ':bringingAnyone'       => $data['bringingAnyone'] ? 1 : 0,
            ':bookMultipleTimes'    => $data['bookMultipleTimes'] ? 1 : 0,
            ':maxCapacity'          => $data['maxCapacity'],
            ':maxCustomCapacity'    => $data['maxCustomCapacity'],
            ':maxExtraPeople'       => $data['maxExtraPeople'],
            ':show'                 => $data['show'] ? 1 : 0,
            ':notifyParticipants'   => $data['notifyParticipants'],
            ':customLocation'       => $data['customLocation'],
            ':parentId'             => $data['parentId'],
            ':created'              => $data['created'],
            ':closeAfterMin'        => $data['closeAfterMin'],
            ':closeAfterMinBookings'  => $data['closeAfterMinBookings'] ? 1 : 0,
            ':aggregatedPrice'      => $data['aggregatedPrice'] ? 1 : 0,
            ':pictureFullPath'      => $data['pictureFullPath'],
            ':pictureThumbPath'     => $data['pictureThumbPath'],
            ':error'                => '',
        ];

        $additionalData = Licence\DataModifier::getEventRepositoryData($data);

        $params = array_merge($params, $additionalData['values'], $additionalData['addValues']);

        try {
            $statement = $this->connection->prepare(
                "INSERT INTO {$this->table} 
                (
                {$additionalData['columns']}
                `bookingOpens`,
                `bookingCloses`,
                `bookingOpensRec`,
                `bookingClosesRec`,
                `status`,
                `name`,
                `description`,
                `color`,
                `price`,
                `bringingAnyone`,
                `bookMultipleTimes`,
                `maxCapacity`,
                `maxCustomCapacity`,
                `maxExtraPeople`,
                `show`,
                `notifyParticipants`,
                `customLocation`,
                `parentId`,
                `created`,
                `closeAfterMin`,
                `closeAfterMinBookings`,
                `aggregatedPrice`,
                `pictureFullPath`,
                `pictureThumbPath`,
                `error`
                 )
                VALUES (
                {$additionalData['placeholders']}
                :bookingOpens,
                :bookingCloses,
                :bookingOpensRec,
                :bookingClosesRec,
                :status,
                :name,
                :description,
                :color,
                :price,
                :bringingAnyone,
                :bookMultipleTimes,
                :maxCapacity,
                :maxCustomCapacity,
                :maxExtraPeople,
                :show,
                :notifyParticipants,
                :customLocation,
                :parentId,
                :created,
                :closeAfterMin,
                :closeAfterMinBookings,
                :aggregatedPrice,
                :pictureFullPath,
                :pictureThumbPath,
                :error
                )"
            );

            $statement->execute($params);

            return $this->connection->lastInsertId();
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to add data in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }
    }

    /**
     * @param int         $id
     * @param Event $entity
     *
     * @return mixed
     * @throws QueryExecutionException
     */
    public function update($id, $entity)
    {
        $data = $entity->toArray();

        $params = [
            ':id'                   => $id,
            ':bookingOpens'         => $data['bookingOpens'] ? DateTimeService::getCustomDateTimeInUtc($data['bookingOpens']) : null,
            ':bookingCloses'        => $data['bookingCloses'] ? DateTimeService::getCustomDateTimeInUtc($data['bookingCloses']) : null,
            ':bookingOpensRec'      => $data['bookingOpensRec'],
            ':bookingClosesRec'     => $data['bookingClosesRec'],
            ':status'               => $data['status'],
            ':name'                 => $data['name'],
            ':description'          => $data['description'],
            ':color'                => $data['color'],
            ':price'                => $data['price'],
            ':bringingAnyone'       => $data['bringingAnyone'] ? 1 : 0,
            ':bookMultipleTimes'    => $data['bookMultipleTimes'] ? 1 : 0,
            ':maxCapacity'          => $data['maxCapacity'],
            ':maxCustomCapacity'    => $data['maxCustomCapacity'],
            ':maxExtraPeople'       => $data['maxExtraPeople'],
            ':show'                 => $data['show'] ? 1 : 0,
            ':notifyParticipants'   => $data['notifyParticipants'] ? 1 : 0,
            ':customLocation'       => $data['customLocation'],
            ':parentId'             => $data['parentId'],
            ':closeAfterMin'        => $data['closeAfterMin'],
            ':closeAfterMinBookings'  => $data['closeAfterMinBookings'] ? 1 : 0,
            ':aggregatedPrice'      => $data['aggregatedPrice'] ? 1 : 0,
            ':pictureFullPath'      => $data['pictureFullPath'],
            ':pictureThumbPath'     => $data['pictureThumbPath'],
        ];

        $additionalData = Licence\DataModifier::getEventRepositoryData($data);

        $params = array_merge($params, $additionalData['values']);

        try {
            $statement = $this->connection->prepare(
                "UPDATE {$this->table}
                SET
                {$additionalData['columnsPlaceholders']}
                `bookingOpens` = :bookingOpens,
                `bookingCloses` = :bookingCloses, 
                `bookingOpensRec` = :bookingOpensRec,
                `bookingClosesRec` = :bookingClosesRec, 
                `status` = :status,
                `name` = :name,
                `description` = :description,
                `color` = :color,
                `price` = :price,
                `bringingAnyone` = :bringingAnyone,
                `bookMultipleTimes` = :bookMultipleTimes,
                `maxCapacity` = :maxCapacity,
                `maxCustomCapacity` = :maxCustomCapacity, 
                `maxExtraPeople` = :maxExtraPeople,    
                `show` = :show,
                `notifyParticipants` = :notifyParticipants,
                `customLocation` = :customLocation,
                `parentId` = :parentId,
                `closeAfterMin` = :closeAfterMin,
                `closeAfterMinBookings` = :closeAfterMinBookings,
                `aggregatedPrice` = :aggregatedPrice,
                `pictureFullPath`   = :pictureFullPath,
                `pictureThumbPath`  = :pictureThumbPath
                WHERE id = :id"
            );

            $statement->execute($params);

            return true;
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to save data in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }
    }

    /**
     * @param int      $id
     * @param int|null $parentId
     *
     * @return mixed
     * @throws QueryExecutionException
     */
    public function updateParentId($id, $parentId)
    {
        $params = [
            ':id'             => $id,
            ':parentId'       => $parentId,
        ];

        try {
            $statement = $this->connection->prepare(
                "UPDATE {$this->table}
                SET
                `parentId` = :parentId
                WHERE id = :id"
            );

            $statement->execute($params);

            return true;
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to save data in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }
    }

    /**
     * @param array $criteria
     *
     * @return Collection
     * @throws QueryExecutionException
     * @throws InvalidArgumentException
     */
    public function getProvidersEvents($criteria)
    {
        $eventsPeriodsTable   = EventsPeriodsTable::getTableName();
        $eventsProvidersTable = EventsProvidersTable::getTableName();
        $usersTable           = UsersTable::getTableName();

        $params = [];
        $where  = [];

        if (!empty($criteria['dates'])) {
            if (isset($criteria['dates'][0], $criteria['dates'][1])) {
                $whereStart           = "(ep.periodStart BETWEEN :eventFrom AND :eventTo)";
                $params[':eventFrom'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
                $params[':eventTo']   = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);

                $whereEnd = "(ep.periodEnd BETWEEN :bookingFrom2 AND :bookingTo2)";
                $params[':bookingFrom2'] = $params[':eventFrom'];
                $params[':bookingTo2']   = $params[':eventTo'];

                $where[] = "({$whereStart} OR {$whereEnd})";
            } elseif (isset($criteria['dates'][0])) {
                $where[] = "(ep.periodStart >= :eventFrom)";
                $params[':eventFrom'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
            } elseif (isset($criteria['dates'][1])) {
                $where[]            = "(ep.periodStart <= :eventTo)";
                $params[':eventTo'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
            } else {
                $where[] = "(ep.periodStart > :eventFrom)";
                $params[':eventFrom'] = DateTimeService::getNowDateTimeInUtc();
            }
        }

        if (!empty($criteria['providers'])) {
            $queryProviders = [];

            foreach ((array)$criteria['providers'] as $index => $value) {
                $param            = ':provider' . $index;
                $queryProviders[] = $param;
                $params[$param]   = $value;
            }

            $where[] = 'epr.userId IN (' . implode(', ', $queryProviders) . ')';
        }

        if (!empty($criteria['status'])) {
            $params[':status'] = $criteria['status'];

            $where[] = 'e.status = :status';
        }

        $where = $where ? 'WHERE ' . implode(' AND ', $where) : '';

        try {
            $statement = $this->connection->prepare(
                "SELECT
                    e.id AS event_id,
                    e.name AS event_name,
                    e.status AS event_status,
                    e.bookingOpens AS event_bookingOpens,
                    e.bookingCloses AS event_bookingCloses,
                    e.recurringCycle AS event_recurringCycle,
                    e.recurringOrder AS event_recurringOrder,
                    e.recurringInterval AS event_recurringInterval,
                    e.recurringUntil AS event_recurringUntil,
                    e.recurringMonthly AS event_recurringMonthly, 
                    e.monthlyDate AS event_monthlyDate,
                    e.monthlyOnRepeat AS event_monthlyOnRepeat,
                    e.monthlyOnDay AS event_monthlyOnDay,
                    e.bringingAnyone AS event_bringingAnyone,
                    e.bookMultipleTimes AS event_bookMultipleTimes,
                    e.maxCapacity AS event_maxCapacity,
                    e.maxCustomCapacity AS event_maxCustomCapacity,
                    e.maxExtraPeople AS event_maxExtraPeople,
                    e.price AS event_price,
                    e.description AS event_description,
                    e.color AS event_color,
                    e.show AS event_show,
                    e.locationId AS event_locationId,
                    e.customLocation AS event_customLocation,
                    e.parentId AS event_parentId,
                    e.created AS event_created,
                    e.notifyParticipants AS event_notifyParticipants,
                    e.translations AS event_translations,
                    e.deposit AS event_deposit,
                    e.depositPayment AS event_depositPayment,
                    e.depositPerPerson AS event_depositPerPerson,
                    e.fullPayment AS event_fullPayment,
                    e.customPricing AS event_customPricing,
                    e.aggregatedPrice AS event_aggregatedPrice,
                    
                    ep.id AS event_periodId,
                    ep.periodStart AS event_periodStart,
                    ep.periodEnd AS event_periodEnd,
                    
                    pu.id AS provider_id,
                    pu.firstName AS provider_firstName,
                    pu.lastName AS provider_lastName,
                    pu.email AS provider_email,
                    pu.note AS provider_note,
                    pu.description AS provider_description,
                    pu.phone AS provider_phone,
                    pu.countryPhoneIso AS provider_countryPhoneIso,
                    pu.gender AS provider_gender,
                    pu.pictureFullPath AS provider_pictureFullPath,
                    pu.pictureThumbPath AS provider_pictureThumbPath,
                    pu.translations AS provider_translations
                FROM {$this->table} e
                INNER JOIN {$eventsPeriodsTable} ep ON ep.eventId = e.id
                INNER JOIN {$eventsProvidersTable} epr ON epr.eventId = e.id
                INNER JOIN {$usersTable} pu ON pu.id = epr.userId
                {$where}
                ORDER BY ep.periodStart"
            );

            $statement->execute($params);

            $rows = $statement->fetchAll();
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }

        return call_user_func([static::FACTORY, 'createCollection'], $rows);
    }

    /**
     * @param array $criteria
     * @param int|null   $itemsPerPage
     *
     * @return array
     * @throws QueryExecutionException
     * @throws InvalidArgumentException
     */
    public function getFilteredIds($criteria, $itemsPerPage)
    {
        $eventsPeriodsTable    = EventsPeriodsTable::getTableName();
        $eventsTagsTable       = EventsTagsTable::getTableName();
        $customerBookingsTable = CustomerBookingsTable::getTableName();
        $customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();
        $eventsProvidersTable          = EventsProvidersTable::getTableName();
        $usersTable = UsersTable::getTableName();

        $params = [];

        $where = [];

        $joins = '';

        $groupBy = 'GROUP BY e.id';

        if (isset($criteria['parentId'])) {
            $params[':parentId'] = $criteria['parentId'];

            $params[':originParentId'] = $criteria['parentId'];

            $where[] = '(e.parentId = :parentId OR e.id = :originParentId)';
        }

        if (!empty($criteria['events'])) {
            $queryExcludeIds = [];

            foreach ($criteria['events'] as $index => $value) {
                $param = ':id' . $index;

                $queryExcludeIds[] = $param;

                $params[$param] = $value;
            }

            $where[] = 'e.id IN (' . implode(', ', $queryExcludeIds) . ')';
        }

        if (!empty($criteria['excludeIds'])) {
            $queryExcludeIds = [];

            foreach ($criteria['excludeIds'] as $index => $value) {
                $param = ':excludeId' . $index;

                $queryExcludeIds[] = $param;

                $params[$param] = $value;
            }

            $where[] = 'e.id NOT IN (' . implode(', ', $queryExcludeIds) . ')';
        }

        if (!empty($criteria['search'])) {
            $terms = preg_split('/\s+/', trim($criteria['search']));
            $termIndex = 0;

            foreach ($terms as $term) {
                $p1 = ":search{$termIndex}_1";
                $p2 = ":search{$termIndex}_2";
                $p3 = ":search{$termIndex}_3";
                $p4 = ":search{$termIndex}_4";
                $p5 = ":search{$termIndex}_5";

                $params[$p1] = "%{$term}%";
                $params[$p2] = "{\"name\":{%{$term}%\"description\":{%";
                $params[$p3] = "{\"description\":{%\"name\":{%{$term}%";
                $params[$p4] = "{\"name\":{%{$term}%";
                $params[$p5] = "%{$term}%";

                $where[] = "(
                    e.name LIKE {$p1}
                    OR e.translations LIKE {$p2}
                    OR e.translations LIKE {$p3}
                    OR (
                        e.translations LIKE {$p4}
                        AND e.translations NOT LIKE '%\"description\":{%'
                    )
                    OR e.id LIKE {$p5}
                )";

                $termIndex++;
            }
        }

        if (!empty($criteria['status'])) {
            $params[':status'] = $criteria['status'];
            $where[] = "e.status = :status";
        }

        if (isset($criteria['show'])) {
            $where[] = 'e.show = 1';
        }

        if (!empty($criteria['dates'])) {
            if (!empty($criteria['dates'][0]) && !empty($criteria['dates'][1])) {
                $where[] = "((ep.periodStart BETWEEN :eventFrom1 AND :eventTo1)
                OR (ep.periodEnd BETWEEN :eventFrom2 AND :eventTo2)
                OR (:eventFrom3 BETWEEN ep.periodStart AND ep.periodEnd)
                OR (:eventTo3  BETWEEN ep.periodStart AND ep.periodEnd))";

                $params[':eventFrom1'] = $params[':eventFrom2'] = $params[':eventFrom3'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
                $params[':eventTo1']   = $params[':eventTo2']   = $params[':eventTo3']   = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
            } elseif (!empty($criteria['dates'][0])) {
                $where[] = "(ep.periodStart >= :eventFrom OR (ep.periodEnd >= :eventTo))";
                $params[':eventFrom'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
                $params[':eventTo']   = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
            } elseif (!empty($criteria['dates'][1])) {
                $where[] = "(ep.periodStart <= :eventTo)";

                $params[':eventTo'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
            } else {
                $where[] = "(ep.periodStart > :eventFrom)";
                $params[':eventFrom'] = DateTimeService::getNowDateTimeInUtc();
            }
        }

        if (!empty($criteria['tag'])) {
            $queryTags = [];

            $tags = $criteria['tag'];
            foreach ((array)$tags as $index => $value) {
                $param = ':tag' . $index;

                $queryTags[] = $param;

                $params[$param] = $value;
            }

            $where[] = 'et.name IN (' . implode(', ', $queryTags) . ')';

            $joins .= "
                INNER JOIN {$eventsTagsTable} et ON et.eventId = e.id
            ";
        }

        if (!empty($criteria['skipRecurring'])) {
            // When skipRecurring is true, only return the first upcoming event from each recurring series
            $params[':recurringEventFrom'] = !empty($criteria['dates'][0]) ?
                DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]) :
                DateTimeService::getNowDateTimeInUtc();
            $where[] = "e.id IN (
                SELECT e_sub.id
                FROM {$this->table} e_sub
                INNER JOIN {$eventsPeriodsTable} ep_sub ON ep_sub.eventId = e_sub.id
                INNER JOIN (
                    SELECT COALESCE(e_inner.parentId, e_inner.id) as series_id, MIN(ep_inner.periodStart) as min_start
                    FROM {$this->table} e_inner
                    INNER JOIN {$eventsPeriodsTable} ep_inner ON ep_inner.eventId = e_inner.id
                    WHERE ep_inner.periodStart >= :recurringEventFrom
                    GROUP BY COALESCE(e_inner.parentId, e_inner.id)
                ) upcoming ON COALESCE(e_sub.parentId, e_sub.id) = upcoming.series_id 
                    AND ep_sub.periodStart = upcoming.min_start
            )";
        }

        if (!empty($criteria['id'])) {
            if (!empty($criteria['recurring'])) {
                $whereOr = [];
                foreach ((array)$criteria['id'] as $index => $value) {
                    $param = 'id' . $index;

                    $params[':rec1' . $param] = (int)$value;
                    $params[':rec2' . $param] = (int)$value;
                    $params[':rec3' . $param] = (int)$value;
                    $params[':rec4' . $param] = (int)$value;

                    $whereOr[] = "((e.id = :rec1id" . $index . " AND e.parentId IS NULL) OR 
                    (e.parentId IN (SELECT parentId FROM {$this->table} WHERE parentId IS NOT NULL AND parentId = :rec2id" . $index . ")) OR
                    (e.id >= :rec3id" . $index . "  AND e.parentId IN (SELECT parentId FROM {$this->table} WHERE id = :rec4id" . $index . ")))";
                }
                $where[] = '(' . implode(' OR ', $whereOr) . ')';
            } else {
                $queryIds = [];

                foreach ((array)$criteria['id'] as $index => $value) {
                    $param = ':id' . $index;

                    $queryIds[] = $param;

                    $params[$param] = (int)$value;
                }

                $where[] = 'e.id IN (' . implode(', ', $queryIds) . ')';
            }
        }

        if (!empty($criteria['customers']) || !empty($criteria['customerId']) || !empty($criteria['customerBookingsIds'])) {
            $joins .= "
                LEFT JOIN {$customerBookingsEventsPeriods} cbe ON cbe.eventPeriodId = ep.id
                LEFT JOIN {$customerBookingsTable} cb ON cb.id = cbe.customerBookingId
            ";

            if (!empty($criteria['customerId'])) {
                $params[':customerId'] = $criteria['customerId'];

                $where[] = 'cb.customerId = :customerId';
            }

            if (!empty($criteria['customers'])) {
                $queryCustomerIds = [];

                foreach ($criteria['customers'] as $index => $value) {
                    $param = ':customerId' . $index;

                    $queryCustomerIds[] = $param;

                    $params[$param] = $value;
                }

                $where[] = 'cb.customerId IN (' . implode(', ', $queryCustomerIds) . ')';
            }

            if (!empty($criteria['customerBookingsIds'])) {
                $queryBookingsIds = [];

                foreach ($criteria['customerBookingsIds'] as $index => $value) {
                    $param = ':customerBookingId' . $index;

                    $queryBookingsIds[] = $param;

                    $params[$param] = $value;
                }

                $where[] = 'cb.id IN (' . implode(', ', $queryBookingsIds) . ')';
            }

            if (!empty($criteria['customerBookingStatus'])) {
                $params[':customerBookingStatus'] = $criteria['customerBookingStatus'];

                $where[] = 'cb.status = :customerBookingStatus';
            }

            if (!empty($criteria['customerBookingCouponId'])) {
                $params[':customerBookingCouponId'] = $criteria['customerBookingCouponId'];

                $where[] = 'cb.couponId = :customerBookingCouponId';
            }
        }

        if (!empty($criteria['locationId'])) {
            $params[':locationId'] = $criteria['locationId'];

            $where[] = 'e.locationId = :locationId';
        }

        if (!empty($criteria['locations'])) {
            $queryLocations = [];

            foreach ((array)$criteria['locations'] as $index => $value) {
                $param            = ':location' . $index;
                $queryLocations[] = $param;
                $params[$param]   = $value;
            }

            $where3 = 'e.locationId IN (' . implode(', ', $queryLocations) . ')';

            $where[] = '(' . $where3 . ')';
        }

        if (!empty($criteria['providers'])) {
            $joins .= "
                LEFT JOIN {$eventsProvidersTable} epr ON epr.eventId = e.id
                INNER JOIN {$usersTable} pu ON pu.id = epr.userId OR pu.id = e.organizerId
            ";

            $queryProviders = [];

            foreach ((array)$criteria['providers'] as $index => $value) {
                $param            = ':provider' . $index;
                $queryProviders[] = $param;
                $params[$param]   = $value;
            }

            $where1 = 'epr.userId IN (' . implode(', ', $queryProviders) . ')';

            $queryProviders = [];
            foreach ((array)$criteria['providers'] as $index => $value) {
                $param            = ':organizer' . $index;
                $queryProviders[] = $param;
                $params[$param]   = $value;
            }

            $where2 = 'e.organizerId IN (' . implode(', ', $queryProviders) . ')';

            $where[] = '(' . $where1 . ' OR ' . $where2 . ')';
        }

        $where = $where ? 'WHERE ' . implode(' AND ', $where) : '';

        $limit = $this->getLimit(
            !empty($criteria['page']) ? (int)$criteria['page'] : 0,
            (int)$itemsPerPage
        );

        try {
            $statement = $this->connection->prepare(
                "SELECT
                     e.id
                FROM {$this->table} e
                INNER JOIN {$eventsPeriodsTable} ep ON ep.eventId = e.id
                {$joins}
                {$where}
                {$groupBy}
                {$this->getOrderBy(!empty($criteria['sort']) ? $criteria['sort'] : null)}
                {$limit}"
            );

            $statement->execute($params);

            $rows = $statement->fetchAll();
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }

        return array_column($rows, 'id');
    }

    /**
     * @param int $id
     *
     * @return Event
     * @throws QueryExecutionException
     * @throws InvalidArgumentException
     */
    public function getById($id, $criteria = [])
    {
        $eventsPeriodsTable = EventsPeriodsTable::getTableName();
        $eventsTagsTable    = EventsTagsTable::getTableName();
        $eventsTicketTable  = EventsTicketsTable::getTableName();

        $customerBookingsTable = CustomerBookingsTable::getTableName();
        $paymentsTable         = PaymentsTable::getTableName();
        $usersTable            = UsersTable::getTableName();
        $customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();
        $galleriesTable       = GalleriesTable::getTableName();
        $eventsProvidersTable = EventsProvidersTable::getTableName();
        $couponsTable         = CouponsTable::getTableName();

        $fields = '';

        $joins = "INNER JOIN {$eventsPeriodsTable} ep ON ep.eventId = e.id
                LEFT JOIN {$eventsTagsTable} et ON et.eventId = e.id
                LEFT JOIN {$customerBookingsEventsPeriods} cbe ON cbe.eventPeriodId = ep.id
                LEFT JOIN {$customerBookingsTable} cb ON cb.id = cbe.customerBookingId
                LEFT JOIN {$usersTable} cu ON cu.id = cb.customerId
                LEFT JOIN {$eventsProvidersTable} epr ON epr.eventId = e.id
                LEFT JOIN {$usersTable} pu ON pu.id = epr.userId
                LEFT JOIN {$paymentsTable} p ON p.customerBookingId = cb.id
                LEFT JOIN {$galleriesTable} g ON g.entityId = e.id AND g.entityType = 'event'
                LEFT JOIN {$couponsTable} c ON c.id = cb.couponId
                LEFT JOIN {$eventsTicketTable} t ON t.eventId = e.id";

        if (!empty($criteria['fetchBookingsTickets'])) {
            $bookingsTicketsTable = CustomerBookingToEventsTicketsTable::getTableName();

            $fields .= '
                cbt.id AS booking_ticket_id,
                cbt.eventTicketId AS booking_ticket_eventTicketId,
                cbt.price AS booking_ticket_price,
                cbt.persons AS booking_ticket_persons,
            ';

            $joins .= "
                LEFT JOIN {$bookingsTicketsTable} cbt ON cbt.customerBookingId = cb.id
            ";
        }

        $fields .= 'e.id AS event_id,
                    e.name AS event_name,
                    e.status AS event_status,
                    e.bookingOpens AS event_bookingOpens,
                    e.bookingCloses AS event_bookingCloses, 
                    e.bookingOpensRec AS event_bookingOpensRec,
                    e.bookingClosesRec AS event_bookingClosesRec,
                    e.ticketRangeRec AS event_ticketRangeRec,
                    e.recurringCycle AS event_recurringCycle,
                    e.recurringOrder AS event_recurringOrder,
                    e.recurringInterval AS event_recurringInterval,
                    e.recurringMonthly AS event_recurringMonthly, 
                    e.monthlyDate AS event_monthlyDate,
                    e.monthlyOnRepeat AS event_monthlyOnRepeat,
                    e.monthlyOnDay AS event_monthlyOnDay,
                    e.recurringUntil AS event_recurringUntil,
                    e.bringingAnyone AS event_bringingAnyone,
                    e.bookMultipleTimes AS event_bookMultipleTimes,
                    e.maxCapacity AS event_maxCapacity,
                    e.maxCustomCapacity AS event_maxCustomCapacity,
                    e.maxExtraPeople AS event_maxExtraPeople,
                    e.price AS event_price,
                    e.description AS event_description,
                    e.color AS event_color,
                    e.show AS event_show,
                    e.notifyParticipants AS event_notifyParticipants,
                    e.locationId AS event_locationId,
                    e.customLocation AS event_customLocation,
                    e.parentId AS event_parentId,
                    e.created AS event_created,
                    e.settings AS event_settings,
                    e.zoomUserId AS event_zoomUserId,
                    e.organizerId AS event_organizerId,
                    e.translations AS event_translations,
                    e.deposit AS event_deposit,
                    e.depositPayment AS event_depositPayment,
                    e.depositPerPerson AS event_depositPerPerson,
                    e.fullPayment AS event_fullPayment,
                    e.customPricing AS event_customPricing,
                    e.aggregatedPrice AS event_aggregatedPrice,
                    
                    ep.id AS event_periodId,
                    ep.periodStart AS event_periodStart,
                    ep.periodEnd AS event_periodEnd,
                    ep.zoomMeeting AS event_periodZoomMeeting,
                    ep.lessonSpace AS event_periodLessonSpace,
                    ep.googleCalendarEventId AS event_googleCalendarEventId,
                    ep.googleMeetUrl AS event_googleMeetUrl,
                    ep.outlookCalendarEventId AS event_outlookCalendarEventId,
                    ep.microsoftTeamsUrl AS event_microsoftTeamsUrl,
                    ep.appleCalendarEventId AS event_appleCalendarEventId,
                    
                    et.id AS event_tagId,
                    et.name AS event_tagName,
                                        
                    cb.id AS booking_id,
                    cb.customerId AS booking_customerId,
                    cb.status AS booking_status,
                    cb.price AS booking_price,
                    cb.persons AS booking_persons,
                    cb.customFields AS booking_customFields,
                    cb.info AS booking_info,
                    cb.aggregatedPrice AS booking_aggregatedPrice,
                    cb.token AS booking_token,
                    cb.utcOffset AS booking_utcOffset,
                    cb.couponId AS booking_couponId,
                    
                    cu.id AS customer_id,
                    cu.firstName AS customer_firstName,
                    cu.lastName AS customer_lastName,
                    cu.email AS customer_email,
                    cu.note AS customer_note,
                    cu.phone AS customer_phone,
                    cu.countryPhoneIso AS customer_countryPhoneIso,
                    cu.gender AS customer_gender,
                    cu.birthday AS customer_birthday,
                    
                    p.id AS payment_id,
                    p.amount AS payment_amount,
                    p.dateTime AS payment_dateTime,
                    p.status AS payment_status,
                    p.gateway AS payment_gateway,
                    p.gatewayTitle AS payment_gatewayTitle,
                    p.transactionId AS payment_transactionId,
                    p.data AS payment_data,
                    p.wcOrderId AS payment_wcOrderId,
                    p.wcOrderItemId AS payment_wcOrderItemId,
                    p.invoiceNumber AS payment_invoiceNumber,
                    
                    pu.id AS provider_id,
                    pu.firstName AS provider_firstName,
                    pu.lastName AS provider_lastName,
                    pu.email AS provider_email,
                    pu.note AS provider_note,
                    pu.description AS provider_description,
                    pu.phone AS provider_phone,
                    pu.countryPhoneIso AS provider_countryPhoneIso,
                    pu.gender AS provider_gender,
                    pu.translations AS provider_translations,
                    pu.timeZone AS provider_timeZone,
       
                    g.id AS gallery_id,
                    g.pictureFullPath AS gallery_picture_full,
                    g.pictureThumbPath AS gallery_picture_thumb,
                    g.position AS gallery_position,
                    
                    c.id AS coupon_id,
                    c.code AS coupon_code,
                    c.discount AS coupon_discount,
                    c.deduction AS coupon_deduction,
                    c.limit AS coupon_limit,
                    c.customerLimit AS coupon_customerLimit,
                    c.status AS coupon_status,
       
                    t.id AS ticket_id,
                    t.name AS ticket_name,
                    t.enabled AS ticket_enabled,
                    t.price AS ticket_price,
                    t.spots AS ticket_spots,
                    t.waitingListSpots AS ticket_waiting_list_spots,
                    t.dateRanges AS ticket_dateRanges,
                    t.translations AS ticket_translations';

        try {
            $statement = $this->connection->prepare(
                "SELECT
                    {$fields}
                FROM {$this->table} e
                {$joins}
                
                WHERE e.id = :eventId"
            );

            $statement->bindParam(':eventId', $id);

            $statement->execute();

            $rows = $statement->fetchAll();
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to find event by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }

        return call_user_func([static::FACTORY, 'createCollection'], $rows)->getItem($id);
    }


    /**
     * @param int $id
     *
     * @return mixed
     * @throws QueryExecutionException
     */
    public function isRecurring($id)
    {
        try {
            $statement = $this->connection->prepare(
                "SELECT
                  e.recurringOrder AS event_recurringOrder, 
                  e.parentId AS event_parentId 
                FROM {$this->table} e 
                WHERE e.id = :eventId"
            );

            $statement->bindParam(':eventId', $id);

            $statement->execute();

            return $statement->fetch();
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to find event by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }
    }


    /**
     * @param int $id
     * @param int $parentId
     *
     * @return mixed
     * @throws QueryExecutionException
     */
    public function getRecurringIds($id, $parentId)
    {
        $whereParent = empty($parentId) ? '' : ' OR e.parentId = :parentId';
        try {
            $statement = $this->connection->prepare(
                "SELECT
                  e.id AS eventId 
                FROM {$this->table} e 
                WHERE e.parentId = :eventId" . $whereParent
            );

            $statement->bindParam(':eventId', $id);
            if ($parentId) {
                $statement->bindParam(':parentId', $parentId);
            }

            $statement->execute();

            $events = $statement->fetchAll();

            return array_column($events, 'eventId');
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to find event by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }
    }

    /**
     * @param int   $bookingId
     * @param array $criteria
     *
     * @return ?Event
     * @throws QueryExecutionException
     * @throws InvalidArgumentException
     */
    public function getByBookingId($bookingId, $criteria = []): ?Event
    {
        $eventsPeriodsTable = EventsPeriodsTable::getTableName();

        $customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();

        $fields = '';

        $joins = '';

        if (!empty($criteria['fetchEventsCoupons'])) {
            $couponsTable = CouponsTable::getTableName();

            $fields .= '
                ec.id AS coupon_id,
                ec.code AS coupon_code,
                ec.discount AS coupon_discount,
                ec.deduction AS coupon_deduction,
                ec.limit AS coupon_limit,
                ec.customerLimit AS coupon_customerLimit,
                ec.status AS coupon_status,
            ';

            $joins .= "
                LEFT JOIN {$couponsTable} ec ON ec.id = cb.couponId
            ";
        }

        if (!empty($criteria['fetchEventsTickets'])) {
            $ticketsTable = EventsTicketsTable::getTableName();

            $fields .= '
                eti.id AS ticket_id,
                eti.name AS ticket_name,
                eti.enabled AS ticket_enabled,
                eti.price AS ticket_price,
                eti.spots AS ticket_spots,
                eti.waitingListSpots AS ticket_waiting_list_spots,
                eti.dateRanges AS ticket_dateRanges,
                eti.translations AS ticket_translations,
            ';

            $joins .= "
                LEFT JOIN {$ticketsTable} eti ON eti.eventId = e.id
            ";
        }

        if (!empty($criteria['fetchEventsTags'])) {
            $tagsTable = EventsTagsTable::getTableName();

            $fields .= '
                eta.id AS event_tagId,
                eta.name AS event_tagName,
            ';

            $joins .= "
                LEFT JOIN {$tagsTable} eta ON eta.eventId = e.id
            ";
        }

        if (!empty($criteria['fetchEventsImages'])) {
            $galleriesTable = GalleriesTable::getTableName();

            $fields .= '
                eg.id AS gallery_id,
                eg.pictureFullPath AS gallery_picture_full,
                eg.pictureThumbPath AS gallery_picture_thumb,
                eg.position AS gallery_position,
            ';

            $joins .= "
                LEFT JOIN {$galleriesTable} eg ON eg.entityId = e.id AND eg.entityType = 'event'
            ";
        }

        if (!empty($criteria['fetchEventsProviders'])) {
            $eventsProvidersTable = EventsProvidersTable::getTableName();

            $usersTable = UsersTable::getTableName();

            $joins .= "
                LEFT JOIN {$eventsProvidersTable} epr ON epr.eventId = e.id
                LEFT JOIN {$usersTable} pu ON pu.id = epr.userId
            ";

            $fields .= '
                pu.id AS provider_id,
                pu.firstName AS provider_firstName,
                pu.lastName AS provider_lastName,
                pu.email AS provider_email,
                pu.note AS provider_note,
                pu.description AS provider_description,
                pu.phone AS provider_phone,
                pu.countryPhoneIso AS provider_countryPhoneIso,                
                pu.gender AS provider_gender,
                pu.pictureFullPath AS provider_pictureFullPath,
                pu.pictureThumbPath AS provider_pictureThumbPath,
                pu.translations AS provider_translations,
                pu.timeZone AS provider_timeZone,
            ';
        }

        $fields .= "
            e.id AS event_id,
            e.name AS event_name,
            e.status AS event_status,
            e.bookingOpens AS event_bookingOpens,
            e.bookingCloses AS event_bookingCloses,
            e.recurringCycle AS event_recurringCycle,
            e.recurringOrder AS event_recurringOrder,
            e.recurringInterval AS event_recurringInterval,
            e.recurringUntil AS event_recurringUntil,
            e.bringingAnyone AS event_bringingAnyone,
            e.bookMultipleTimes AS event_bookMultipleTimes,
            e.maxCapacity AS event_maxCapacity,
            e.maxCustomCapacity AS event_maxCustomCapacity,
            e.maxExtraPeople AS event_maxExtraPeople,
            e.price AS event_price,
            e.description AS event_description,
            e.color AS event_color,
            e.show AS event_show,
            e.notifyParticipants AS event_notifyParticipants,
            e.locationId AS event_locationId,
            e.customLocation AS event_customLocation,
            e.customPricing AS event_customPricing,
            e.parentId AS event_parentId,
            e.created AS event_created,
            e.settings AS event_settings,
            e.zoomUserId AS event_zoomUserId,
            e.translations AS event_translations,
            e.deposit AS event_deposit,
            e.depositPayment AS event_depositPayment,
            e.depositPerPerson AS event_depositPerPerson,
            e.fullPayment AS event_fullPayment,
            e.organizerId AS event_organizerId,
            e.aggregatedPrice AS event_aggregatedPrice,
            
            ep.id AS event_periodId,
            ep.periodStart AS event_periodStart,
            ep.periodEnd AS event_periodEnd,
            ep.zoomMeeting AS event_periodZoomMeeting,
            ep.lessonSpace AS event_periodLessonSpace,
            ep.googleCalendarEventId AS event_googleCalendarEventId,
            ep.googleMeetUrl AS event_googleMeetUrl,
            ep.outlookCalendarEventId AS event_outlookCalendarEventId,
            ep.microsoftTeamsUrl AS event_microsoftTeamsUrl,
            ep.appleCalendarEventId AS event_appleCalendarEventId
        ";

        $params = [
            ':customerBookingId' => $bookingId,
        ];

        try {
            $statement = $this->connection->prepare(
                "SELECT
                {$fields}
                FROM {$customerBookingsEventsPeriods} cbe
                INNER JOIN {$eventsPeriodsTable} ep ON ep.id = cbe.eventPeriodId
                INNER JOIN {$this->table} e ON e.id = ep.eventId
                {$joins}
                WHERE cbe.customerBookingId = :customerBookingId"
            );

            $statement->execute($params);

            $rows = $statement->fetchAll();
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to find event by booking id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }

        /** @var Collection $events */
        $events = call_user_func([static::FACTORY, 'createCollection'], $rows);

        return $events->length() ? $events->getItem($events->keys()[0]) : null;
    }

    /**
     * @param array $ids
     * @param array $criteria
     *
     * @return Collection
     * @throws QueryExecutionException
     * @throws InvalidArgumentException
     */
    public function getByIdsWithEntities($ids, $criteria = [], $sort = null)
    {
        $params = [];

        $where = [];

        $fields = '';

        $joins = '';

        $orderBy = '';

        if (!empty($criteria['fetchEventsPeriods'])) {
            $eventsPeriodsTable = EventsPeriodsTable::getTableName();

            $fields .= '
                ep.id AS event_periodId,
                ep.periodStart AS event_periodStart,
                ep.periodEnd AS event_periodEnd,
                ep.zoomMeeting AS event_periodZoomMeeting,
                ep.lessonSpace AS event_periodLessonSpace,
                ep.googleCalendarEventId AS event_googleCalendarEventId,
                ep.googleMeetUrl AS event_googleMeetUrl,
                ep.outlookCalendarEventId AS event_outlookCalendarEventId,
                ep.microsoftTeamsUrl AS event_microsoftTeamsUrl,
                ep.appleCalendarEventId AS event_appleCalendarEventId,
            ';

            $joins .= "
                INNER JOIN {$eventsPeriodsTable} ep ON ep.eventId = e.id
            ";

            $orderBy = $this->getOrderBy($sort);
        }

        if (!empty($criteria['fetchEventsLocation'])) {
            $locationsTable = LocationsTable::getTableName();

            $fields .= '
                l.id AS location_id,
                l.name AS location_name,
            ';

            $joins .= "
                LEFT JOIN {$locationsTable} l ON l.id = e.locationId
            ";
        }

        if (!empty($criteria['fetchEventsCoupons'])) {
            $couponsTable = CouponsTable::getTableName();

            $fields .= '
                ec.id AS coupon_id,
                ec.code AS coupon_code,
                ec.discount AS coupon_discount,
                ec.deduction AS coupon_deduction,
                ec.limit AS coupon_limit,
                ec.customerLimit AS coupon_customerLimit,
                ec.status AS coupon_status,
            ';

            $joins .= "
                LEFT JOIN {$couponsTable} ec ON ec.id = cb.couponId
            ";
        }

        if (!empty($criteria['fetchEventsTickets'])) {
            $ticketsTable = EventsTicketsTable::getTableName();

            $fields .= '
                eti.id AS ticket_id,
                eti.name AS ticket_name,
                eti.enabled AS ticket_enabled,
                eti.price AS ticket_price,
                eti.spots AS ticket_spots,
                eti.waitingListSpots AS ticket_waiting_list_spots,
                eti.dateRanges AS ticket_dateRanges,
                eti.translations AS ticket_translations,
            ';

            $joins .= "
                LEFT JOIN {$ticketsTable} eti ON eti.eventId = e.id
            ";

            $orderBy .= (!empty($orderBy) ? ',' : 'ORDER BY') . ' eti.id ASC';
        }

        if (!empty($criteria['fetchEventsTags'])) {
            $tagsTable = EventsTagsTable::getTableName();

            $fields .= '
                eta.id AS event_tagId,
                eta.name AS event_tagName,
            ';

            $joins .= "
                LEFT JOIN {$tagsTable} eta ON eta.eventId = e.id
            ";
        }

        if (!empty($criteria['fetchEventsImages'])) {
            $galleriesTable = GalleriesTable::getTableName();

            $fields .= '
                eg.id AS gallery_id,
                eg.pictureFullPath AS gallery_picture_full,
                eg.pictureThumbPath AS gallery_picture_thumb,
                eg.position AS gallery_position,
            ';

            $joins .= "
                LEFT JOIN {$galleriesTable} eg ON eg.entityId = e.id AND eg.entityType = 'event'
            ";
        }

        if (!empty($criteria['fetchEventsOrganizer'])) {
            $usersTable = UsersTable::getTableName();

            $joins .= "
                LEFT JOIN {$usersTable} ou ON ou.id = e.organizerId
            ";

            $fields .= '
                ou.id AS organizer_id,
                ou.firstName AS organizer_firstName,
                ou.lastName AS organizer_lastName,
                ou.email AS organizer_email,
                ou.badgeId AS organizer_badgeId,
                ou.pictureThumbPath AS organizer_pictureThumbPath,
                ou.pictureFullPath AS organizer_pictureFullPath,
            ';
        }

        if (!empty($criteria['fetchEventsProviders'])) {
            $eventsProvidersTable = EventsProvidersTable::getTableName();

            $usersTable = UsersTable::getTableName();

            $joins .= "
                LEFT JOIN {$eventsProvidersTable} epr ON epr.eventId = e.id
                LEFT JOIN {$usersTable} pu ON pu.id = epr.userId
            ";

            $fields .= '
                pu.id AS provider_id,
                pu.firstName AS provider_firstName,
                pu.lastName AS provider_lastName,
                pu.email AS provider_email,
                pu.note AS provider_note,
                pu.description AS provider_description,
                pu.phone AS provider_phone,
                pu.countryPhoneIso AS provider_countryPhoneIso,
                pu.gender AS provider_gender,
                pu.pictureFullPath AS provider_pictureFullPath,
                pu.pictureThumbPath AS provider_pictureThumbPath,
                pu.translations AS provider_translations,
                pu.timeZone AS provider_timeZone,
                pu.badgeId AS provider_badgeId,
            ';
        }

        $fields .= "
            e.id AS event_id,
            e.name AS event_name,
            e.status AS event_status,
            e.bookingOpens AS event_bookingOpens,
            e.bookingCloses AS event_bookingCloses, 
            e.bookingOpensRec AS event_bookingOpensRec,
            e.bookingClosesRec AS event_bookingClosesRec,
            e.ticketRangeRec AS event_ticketRangeRec,
            e.recurringCycle AS event_recurringCycle,
            e.recurringOrder AS event_recurringOrder,
            e.recurringInterval AS event_recurringInterval,
            e.recurringMonthly AS event_recurringMonthly, 
            e.monthlyDate AS event_monthlyDate,
            e.monthlyOnRepeat AS event_monthlyOnRepeat,
            e.monthlyOnDay AS event_monthlyOnDay,
            e.recurringUntil AS event_recurringUntil,
            e.bringingAnyone AS event_bringingAnyone,
            e.bookMultipleTimes AS event_bookMultipleTimes,
            e.maxCapacity AS event_maxCapacity,
            e.maxCustomCapacity AS event_maxCustomCapacity,
            e.maxExtraPeople AS event_maxExtraPeople,
            e.price AS event_price,
            e.description AS event_description,
            e.color AS event_color,
            e.show AS event_show,
            e.notifyParticipants AS event_notifyParticipants,
            e.locationId AS event_locationId,
            e.customLocation AS event_customLocation,
            e.parentId AS event_parentId,
            e.created AS event_created,
            e.settings AS event_settings,
            e.zoomUserId AS event_zoomUserId,
            e.organizerId AS event_organizerId,
            e.translations AS event_translations,
            e.deposit AS event_deposit,
            e.depositPayment AS event_depositPayment,
            e.depositPerPerson AS event_depositPerPerson,
            e.fullPayment AS event_fullPayment,
            e.customPricing AS event_customPricing,
            e.closeAfterMin AS event_closeAfterMin,
            e.closeAfterMinBookings AS event_closeAfterMinBookings,
            e.aggregatedPrice AS event_aggregatedPrice,
            e.pictureFullPath AS event_pictureFullPath,
            e.pictureThumbPath AS event_pictureThumbPath
        ";

        if (!empty($ids)) {
            $queryIds = [];

            foreach ($ids as $index => $value) {
                $param = ':id' . $index;

                $queryIds[] = $param;

                $params[$param] = $value;
            }

            $where[] = 'e.id IN (' . implode(', ', $queryIds) . ')';
        }

        $where = $where ? 'WHERE ' . implode(' AND ', $where) : '';

        try {
            $statement = $this->connection->prepare(
                "SELECT
                {$fields}
                FROM {$this->table} e
                {$joins}
                {$where}
                {$orderBy}"
            );

            $statement->execute($params);

            $rows = $statement->fetchAll();
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to find event by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }

        return call_user_func([static::FACTORY, 'createCollection'], $rows);
    }

    /**
     * @param array $criteria
     *
     * @return Collection
     * @throws QueryExecutionException
     * @throws InvalidArgumentException
     */
    public function getBookingsByCriteria($criteria = [])
    {
        $params = [];

        $where = [];

        $fields = '';

        $joins = '';

        $eventsPeriodsTable = EventsPeriodsTable::getTableName();

        $customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();

        $customerBookingsTable = CustomerBookingsTable::getTableName();

        if (!empty($criteria['fetchApprovedBookings'])) {
            $where[] = "cb.status = 'approved'";
        }

        if (!empty($criteria['customerId'])) {
            $params[':customerId'] = $criteria['customerId'];

            $where[] = 'cb.customerId = :customerId';
        }

        if (!empty($criteria['customerBookingStatus'])) {
            $params[':customerBookingStatus'] = $criteria['customerBookingStatus'];

            $where[] = 'cb.status = :customerBookingStatus';
        }

        if (!empty($criteria['customerBookingId'])) {
            $params[':customerBookingId'] = $criteria['customerBookingId'];

            $where[] = 'cb.id = :customerBookingId';
        }

        if (!empty($criteria['fetchBookingsPayments'])) {
            $paymentsTable = PaymentsTable::getTableName();

            $fields .= '
                p.id AS payment_id,
                p.amount AS payment_amount,
                p.dateTime AS payment_dateTime,
                p.created AS payment_created,
                p.status AS payment_status,
                p.gateway AS payment_gateway,
                p.gatewayTitle AS payment_gatewayTitle,
                p.transactionId AS payment_transactionId,
                p.data AS payment_data,
                p.wcOrderId AS payment_wcOrderId,
                p.wcOrderItemId AS payment_wcOrderItemId,
                p.invoiceNumber AS payment_invoiceNumber,
            ';

            $joins .= "
                LEFT JOIN {$paymentsTable} p ON p.customerBookingId = cb.id
            ";
        }

        if (!empty($criteria['fetchBookingsCoupons'])) {
            $couponsTable = CouponsTable::getTableName();

            $fields .= '
                c.id AS coupon_id,
                c.code AS coupon_code,
                c.discount AS coupon_discount,
                c.deduction AS coupon_deduction,
                c.limit AS coupon_limit,
                c.customerLimit AS coupon_customerLimit,
                c.status AS coupon_status,
            ';

            $joins .= "
                LEFT JOIN {$couponsTable} c ON c.id = cb.couponId
            ";
        }

        if (!empty($criteria['fetchBookingsUsers'])) {
            $usersTable = UsersTable::getTableName();

            $fields .= '
                cu.id AS customer_id,
                cu.type AS customer_type,
                cu.firstName AS customer_firstName,
                cu.lastName AS customer_lastName,
                cu.email AS customer_email,
                cu.note AS customer_note,
                cu.phone AS customer_phone,
                cu.countryPhoneIso AS customer_countryPhoneIso,
                cu.gender AS customer_gender,
                cu.birthday AS customer_birthday,
                cu.customFields AS customer_customFields,
            ';

            $joins .= "
                INNER JOIN {$usersTable} cu ON cu.id = cb.customerId
            ";
        }

        if (!empty($criteria['fetchBookingsTickets'])) {
            $bookingsTicketsTable = CustomerBookingToEventsTicketsTable::getTableName();

            $fields .= '
                cbt.id AS booking_ticket_id,
                cbt.eventTicketId AS booking_ticket_eventTicketId,
                cbt.price AS booking_ticket_price,
                cbt.persons AS booking_ticket_persons,
            ';

            $joins .= "
                LEFT JOIN {$bookingsTicketsTable} cbt ON cbt.customerBookingId = cb.id
            ";
        }

        $fields .= '
            ep.eventId AS eventId,
            cb.id AS booking_id,
            cb.appointmentId AS booking_appointmentId,
            cb.customerId AS booking_customerId,
            cb.status AS booking_status,
            cb.price AS booking_price,
            cb.tax AS booking_tax,
            cb.persons AS booking_persons,
            cb.couponId AS booking_couponId,
            cb.customFields AS booking_customFields,
            cb.info AS booking_info,
            cb.utcOffset AS booking_utcOffset,
            cb.token AS booking_token,
            cb.aggregatedPrice AS booking_aggregatedPrice,
            cb.tax AS booking_tax,
            cb.qrCodes AS booking_qrCodes
        ';

        if (!empty($criteria['ids'])) {
            $queryIds = [];

            foreach ($criteria['ids'] as $index => $value) {
                $param = ':id' . $index;

                $queryIds[] = $param;

                $params[$param] = $value;
            }

            $where[] = 'ep.eventId IN (' . implode(', ', $queryIds) . ')';
        }

        $where = $where ? 'WHERE ' . implode(' AND ', $where) : '';

        try {
            $statement = $this->connection->prepare(
                "SELECT
                {$fields}
                FROM {$eventsPeriodsTable} ep
                INNER JOIN {$customerBookingsEventsPeriods} cbe ON cbe.eventPeriodId = ep.id
                INNER JOIN {$customerBookingsTable} cb ON cb.id = cbe.customerBookingId
                {$joins}
                {$where}
                ORDER BY cb.id"
            );

            $statement->execute($params);

            $rows = $statement->fetchAll();
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to find event by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }

        $reformattedData = [];

        foreach ($rows as $row) {
            if (empty($reformattedData[$row['eventId']])) {
                $reformattedData[$row['eventId']] = [];
            }

            $reformattedData[$row['eventId']][] = $row;
        }

        $result = new Collection();

        foreach ($reformattedData as $eventId => $bookingsData) {
            $reformattedBookingsData = CustomerBookingFactory::reformat($bookingsData);

            $eventBookings = new Collection();

            foreach ($reformattedBookingsData as $bookingId => $data) {
                $eventBookings->addItem(CustomerBookingFactory::create($data), $bookingId);
            }

            $result->addItem($eventBookings, $eventId);
        }

        return $result;
    }


    /**
     * @param Event $event
     * @param array $booking
     * @param array $limitPerCustomer
     * @return int
     * @throws QueryExecutionException
     * @throws InvalidArgumentException
     */
    public function getRelevantBookingsCount($event, $booking, $limitPerCustomer)
    {
        $eventsPeriodsTable = EventsPeriodsTable::getTableName();

        $customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();

        $customerBookingsTable = CustomerBookingsTable::getTableName();

        $params = [
            ':customerId' => $booking['customerId']
        ];

        $paymentTableJoin = '';
        $compareToDate    = 'ep.periodStart';

        if ($limitPerCustomer['from'] === 'bookingDate') {
            $eventStartDate =
                (clone $event->getPeriods()->getItems()[0]->getPeriodStart()->getValue())->setTimezone(new \DateTimeZone('UTC'))->format('Y-m-d H:i');
        } else {
            $paymentTableJoin = 'INNER JOIN ' . PaymentsTable::getTableName() . ' p ON p.customerBookingId = cb.id';
            $eventStartDate   = DateTimeService::getNowDateTimeObject()->setTimezone(new \DateTimeZone('UTC'))->format('Y-m-d H:i');
            $compareToDate    = 'p.created';
        }

        $intervalString = "interval " . $limitPerCustomer['period'] . " " . $limitPerCustomer['timeFrame'];

        $where = "(STR_TO_DATE('" . $eventStartDate . "', '%Y-%m-%d %H:%i:%s') BETWEEN " .
            "(" . $compareToDate . " - " . $intervalString . " + interval 1 second)" .
            " AND (" .
            $compareToDate . " + " . $intervalString . " - interval 1 second))";

        try {
            $statement = $this->connection->prepare(
                "SELECT COUNT(DISTINCT cb.id) AS count FROM 
                    {$this->table} e 
                    INNER JOIN {$eventsPeriodsTable} ep ON ep.eventId = e.id 
                    INNER JOIN {$customerBookingsEventsPeriods} cbep ON cbep.eventPeriodId = ep.id 
                    INNER JOIN {$customerBookingsTable} cb ON cb.id = cbep.customerBookingId  
                    {$paymentTableJoin}
                    WHERE cb.customerId = :customerId AND {$where} AND e.status = 'approved' AND cb.status = 'approved'
                "
            );

            $statement->execute($params);

            $rows = $statement->fetch()['count'];
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }

        return $rows;
    }

    /**
     * @param array $ids
     * @return array
     * @throws QueryExecutionException
     * @throws InvalidArgumentException
     */
    public function getEventsSpotsCount($ids)
    {
        $eventsPeriodsTable = EventsPeriodsTable::getTableName();

        $customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();

        $customerBookingsTable = CustomerBookingsTable::getTableName();

        $where = "WHERE ep.eventId IN (" . implode(', ', $ids) . ") AND cb.status IN ('approved', 'pending', 'waiting')";

        try {
            $statement = $this->connection->prepare(
                "SELECT
                t.eventId, t.status, SUM(t.persons) as places
                FROM (
                    SELECT
                    ep.eventId,
                    cb.id,
                    cb.status,
                    cb.persons
                    FROM {$eventsPeriodsTable} ep
                    INNER JOIN {$customerBookingsEventsPeriods} cbep ON cbep.eventPeriodId = ep.id
                    INNER JOIN {$customerBookingsTable} cb ON cb.id = cbep.customerBookingId
                    {$where}
                    GROUP BY ep.eventId, cb.id, cb.status
                ) t
                GROUP BY t.eventId, t.status"
            );

            $statement->execute();

            $rows = $statement->fetchAll();

            $result = [];

            foreach ($rows as $row) {
                $result[$row['eventId']][$row['status']] =
                    $row['places'] +
                    (!empty($result[$row['eventId']][$row['status']]) ? $result[$row['eventId']][$row['status']] : 0);
            }
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to find by id in ' . __CLASS__, $e->getCode(), $e);
        }

        return $result;
    }

    /**
     * @param array $ids
     * @return array
     * @throws QueryExecutionException
     * @throws InvalidArgumentException
     */
    public function getEventsTicketsCount($ids)
    {
        $eventsPeriodsTable = EventsPeriodsTable::getTableName();

        $customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();

        $customerBookingsTable = CustomerBookingsTable::getTableName();

        $customerBookingsTicketsTable = CustomerBookingToEventsTicketsTable::getTableName();

        $where = "WHERE ep.eventId IN (" . implode(', ', $ids) . ") AND cb.status IN ('approved', 'pending', 'waiting')";

        try {
            $statement = $this->connection->prepare(
                "SELECT
                t.eventId, t.status, t.eventTicketId, SUM(t.persons) as places
                FROM (
                    SELECT
                    ep.eventId,
                    cb.id,
                    cb.status,
                    cbt.eventTicketId,
                    cbt.persons
                    FROM {$eventsPeriodsTable} ep
                    INNER JOIN {$customerBookingsEventsPeriods} cbep ON cbep.eventPeriodId = ep.id
                    INNER JOIN {$customerBookingsTable} cb ON cb.id = cbep.customerBookingId
                    INNER JOIN {$customerBookingsTicketsTable} cbt ON cbt.customerBookingId = cb.id
                    {$where}
                    GROUP BY ep.eventId, cb.id, cb.status, cbt.eventTicketId, cbt.persons
                ) t
                GROUP BY t.eventId, t.status, t.eventTicketId"
            );

            $statement->execute();

            $rows = $statement->fetchAll();

            $result = [];

            foreach ($rows as $row) {
                $result[$row['eventId']][$row['eventTicketId']][$row['status']] =
                    $row['places'] +
                    (
                        !empty($result[$row['eventId']][$row['eventTicketId']][$row['status']])
                            ? $result[$row['eventId']][$row['eventTicketId']][$row['status']]
                            : 0
                    );
            }
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to find by id in ' . __CLASS__, $e->getCode(), $e);
        }

        return $result;
    }

    /**
     * @param int $id
     * @return array
     * @throws QueryExecutionException
     * @throws InvalidArgumentException
     */
    public function getEventsPaymentsSummary($id)
    {
        $eventsPeriodsTable = EventsPeriodsTable::getTableName();

        $customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();

        $customerBookingsTable = CustomerBookingsTable::getTableName();

        $paymentsTable = PaymentsTable::getTableName();

        try {
            $statementAmount = $this->connection->prepare(
                "SELECT
                SUM(t.amount) as amount
                FROM (
                    SELECT
                    cb.id,
                    p.amount
                    FROM {$eventsPeriodsTable} ep
                    INNER JOIN {$customerBookingsEventsPeriods} cbep ON cbep.eventPeriodId = ep.id
                    INNER JOIN {$customerBookingsTable} cb ON cb.id = cbep.customerBookingId
                    INNER JOIN {$paymentsTable} p ON p.customerBookingId = cb.id
                    WHERE ep.eventId = :eventId AND cb.status IN ('approved', 'pending', 'waiting')
                    GROUP BY cb.id, p.amount
                ) t"
            );

            $statementAmount->execute([':eventId' => $id]);

            $rowAmount = $statementAmount->fetch();

            $statementStatus = $this->connection->prepare(
                "SELECT
                t.status, COUNT(t.status) as counter
                FROM (
                    SELECT
                    cb.id,
                    p.status
                    FROM {$eventsPeriodsTable} ep
                    INNER JOIN {$customerBookingsEventsPeriods} cbep ON cbep.eventPeriodId = ep.id
                    INNER JOIN {$customerBookingsTable} cb ON cb.id = cbep.customerBookingId
                    INNER JOIN {$paymentsTable} p ON p.customerBookingId = cb.id
                    INNER JOIN (
                        SELECT customerBookingId, MAX(id) as maxId
                        FROM {$paymentsTable}
                        GROUP BY customerBookingId
                    ) pm ON p.customerBookingId = pm.customerBookingId AND p.id = pm.maxId
                    WHERE ep.eventId = :eventId AND cb.status IN ('approved', 'pending', 'waiting')
                    GROUP BY cb.id, p.status
                ) t
                GROUP BY t.status"
            );

            $statementStatus->execute([':eventId' => $id]);

            $rowsStatus = $statementStatus->fetchAll();

            $statementGateway = $this->connection->prepare(
                "SELECT
                t.gateway, COUNT(t.gateway) as counter
                FROM (
                    SELECT
                    p.id,
                    p.gateway
                    FROM {$eventsPeriodsTable} ep
                    INNER JOIN {$customerBookingsEventsPeriods} cbep ON cbep.eventPeriodId = ep.id
                    INNER JOIN {$customerBookingsTable} cb ON cb.id = cbep.customerBookingId
                    INNER JOIN {$paymentsTable} p ON p.customerBookingId = cb.id
                    WHERE ep.eventId = :eventId AND cb.status IN ('approved', 'pending', 'waiting')
                    GROUP BY p.id, p.gateway
                ) t
                GROUP BY t.gateway"
            );

            $statementGateway->execute([':eventId' => $id]);

            $rowsGateway = $statementGateway->fetchAll();
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to find by id in ' . __CLASS__, $e->getCode(), $e);
        }

        return [
            'amount' => $rowAmount['amount'] ? floatval($rowAmount['amount']) : 0,
            'method' => array_map(
                'intval',
                array_column($rowsGateway, 'counter', 'gateway')
            ),
            'status'  => array_map(
                'intval',
                array_column($rowsStatus, 'counter', 'status')
            ),
        ];
    }

    /**
     * @param array $ids
     * @return array
     * @throws QueryExecutionException
     * @throws InvalidArgumentException
     */
    public function getEventsBookingsStatusesCount($ids)
    {
        $eventsPeriodsTable = EventsPeriodsTable::getTableName();

        $customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();

        $customerBookingsTable = CustomerBookingsTable::getTableName();

        $where = "WHERE ep.eventId IN (" . implode(', ', $ids) . ")";

        try {
            $statement = $this->connection->prepare(
                "SELECT
                t.eventId, t.status, COUNT(t.status) as counter
                FROM (
                    SELECT
                    ep.eventId,
                    cb.id,
                    cb.status
                    FROM {$eventsPeriodsTable} ep
                    INNER JOIN {$customerBookingsEventsPeriods} cbep ON cbep.eventPeriodId = ep.id
                    INNER JOIN {$customerBookingsTable} cb ON cb.id = cbep.customerBookingId
                    {$where}
                    GROUP BY ep.eventId, cb.id, cb.status
                ) t
                GROUP BY t.eventId, t.status"
            );

            $statement->execute();

            $rows = $statement->fetchAll();

            $result = [];

            foreach ($rows as $row) {
                $result[$row['eventId']][$row['status']] =
                    $row['counter'] +
                    (!empty($result[$row['eventId']][$row['status']]) ? $result[$row['eventId']][$row['status']] : 0);
            }
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to find by id in ' . __CLASS__, $e->getCode(), $e);
        }

        return $result;
    }

    /**
     * @param string $sort
     * @return string
     */
    private function getOrderBy($sort)
    {
        $order = "ORDER BY ep.periodStart";
        if ($sort) {
            $column = $sort[0] === '-' ? substr($sort, 1) : $sort;
            $orderColumn = 'ep.periodStart';
            switch ($column) {
                case 'name':
                    $orderColumn = 'e.name';
                    break;
                case 'id':
                    $orderColumn = 'e.id';
                    break;
                case 'bookingOpens':
                    $orderColumn = 'COALESCE(e.bookingOpens, e.created)';
                    break;
                case 'bookingCloses':
                    $orderColumn = 'COALESCE(e.bookingCloses, ep.periodStart)';
                    break;
            }

            $orderDirection = $sort[0] === '-' ? 'DESC' : 'ASC';

            $order = "ORDER BY {$orderColumn} {$orderDirection}";
        }

        return $order;
    }


    /**
     * @param Event $event
     * @param int $visible
     * @param boolean $applyGlobally
     * @throws QueryExecutionException
     * @throws InvalidArgumentException
     */
    public function toggleEventVisibility($event, $visible, $applyGlobally)
    {
        $params = [
            ':show' => $visible
        ];

        if ($applyGlobally) {
            $params[':id1'] = $event->getId()->getValue();
            $params[':id2'] = $event->getId()->getValue();

            $where = "WHERE id = :id1 OR parentId = :id2";
            if ($event->getParentId()) {
                $params[':id3'] = $event->getId()->getValue();
                $params[':parentId'] = $event->getParentId()->getValue();
                $where .= ' OR (id > :id3 AND parentId = :parentId)';
            }
        } else {
            $params[':id'] = $event->getId()->getValue();
            $where = 'WHERE id = :id';
        }

        try {
            $statement = $this->connection->prepare(
                "UPDATE {$this->table}
                SET
                `show` = :show
                $where"
            );

            $statement->execute($params);

            return true;
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to update visibility in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }
    }

    /**
     * @param int $organizerId
     */
    public function removeOrganizerFromEvents($organizerId)
    {
        $params = [
            ':organizerId' => $organizerId
        ];


        try {
            $statement = $this->connection->prepare(
                "UPDATE {$this->table}
                SET
                organizerId = NULL
                WHERE organizerId = :organizerId"
            );

            $statement->execute($params);

            return true;
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to remove organizer in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }
    }
}
