<?php

/**
 * @copyright © Melograno Ventures. All rights reserved.
 * @licence   See LICENCE.md for license details.
 */

namespace AmeliaBooking\Infrastructure\Repository\Payment;

use AmeliaBooking\Domain\Collection\Collection;
use AmeliaBooking\Domain\Common\Exceptions\InvalidArgumentException;
use AmeliaBooking\Domain\Entity\Payment\Payment;
use AmeliaBooking\Domain\Factory\Payment\PaymentFactory;
use AmeliaBooking\Domain\Services\DateTime\DateTimeService;
use AmeliaBooking\Infrastructure\Repository\AbstractRepository;
use AmeliaBooking\Infrastructure\Common\Exceptions\QueryExecutionException;
use AmeliaBooking\Infrastructure\Connection;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Bookable\PackagesCustomersServicesTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\CustomerBookingsToExtrasTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Coupon\CouponsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Location\LocationsTable;

/**
 * Class PaymentRepository
 *
 * @package AmeliaBooking\Infrastructure\Repository\Payment
 */
class PaymentRepository extends AbstractRepository
{
    /** @var string */
    protected $appointmentsTable;

    /** @var string */
    protected $bookingsTable;

    /** @var string */
    protected $servicesTable;

    /** @var string */
    protected $usersTable;

    /** @var string */
    protected $eventsTable;

    /** @var string */
    protected $eventsProvidersTable;

    /** @var string */
    protected $eventsPeriodsTable;

    /** @var string */
    protected $customerBookingsToEventsPeriodsTable;

    /** @var string */
    protected $packagesTable;

    /** @var string */
    protected $packagesCustomersTable;

    /** @var string */
    protected $packagesCustomersServiceTable;


    /**
     * @param Connection $connection
     * @param string     $table
     * @param string     $appointmentsTable
     * @param string     $bookingsTable
     * @param string     $servicesTable
     * @param string     $usersTable
     * @param string     $eventsTable
     * @param string     $eventsProvidersTable
     * @param string     $eventsPeriodsTable
     * @param string     $customerBookingsToEventsPeriodsTable
     * @param string     $packagesTable
     * @param string     $packagesCustomersTable
     */
    public function __construct(
        Connection $connection,
        $table,
        $appointmentsTable,
        $bookingsTable,
        $servicesTable,
        $usersTable,
        $eventsTable,
        $eventsProvidersTable,
        $eventsPeriodsTable,
        $customerBookingsToEventsPeriodsTable,
        $packagesTable,
        $packagesCustomersTable
    ) {
        parent::__construct($connection, $table);

        $this->appointmentsTable    = $appointmentsTable;
        $this->bookingsTable        = $bookingsTable;
        $this->servicesTable        = $servicesTable;
        $this->usersTable           = $usersTable;
        $this->eventsTable          = $eventsTable;
        $this->eventsProvidersTable = $eventsProvidersTable;
        $this->eventsPeriodsTable   = $eventsPeriodsTable;
        $this->customerBookingsToEventsPeriodsTable = $customerBookingsToEventsPeriodsTable;
        $this->packagesTable          = $packagesTable;
        $this->packagesCustomersTable = $packagesCustomersTable;
        $this->packagesCustomersServiceTable = PackagesCustomersServicesTable::getTableName();
    }

    public const FACTORY = PaymentFactory::class;

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

        $params = [
            ':customerBookingId' => $data['customerBookingId'] ? $data['customerBookingId'] : null,
            ':packageCustomerId' => $data['packageCustomerId'] ? $data['packageCustomerId'] : null,
            ':parentId'          => $data['parentId'] ? $data['parentId'] : null,
            ':amount'            => $data['amount'],
            ':dateTime'          => DateTimeService::getCustomDateTimeInUtc($data['dateTime']),
            ':status'            => $data['status'],
            ':gateway'           => $data['gateway'],
            ':gatewayTitle'      => $data['gatewayTitle'],
            ':data'              => $data['data'],
            ':entity'            => $data['entity'],
            ':created'           => DateTimeService::getNowDateTimeInUtc(),
            ':wcOrderId'         => !empty($data['wcOrderId']) ? $data['wcOrderId'] : null,
            ':transactionId'      => !empty($data['transactionId']) ? $data['transactionId'] : null,
            ':wcOrderItemId'     => !empty($data['wcOrderItemId']) ? $data['wcOrderItemId'] : null,
        ];

        if (!empty($data['invoiceNumber'])) {
            $params[':invoiceNumber'] = $data['invoiceNumber'];

            $invoiceNumberText = ":invoiceNumber";
        } else {
            $invoiceNumberText = "(SELECT COALESCE(MAX(invoiceNumber), 0) + 1 FROM {$this->table} p)";
        }

        if ($data['parentId']) {
            $params[':actionsCompleted'] = null;
        } else {
            $params[':actionsCompleted'] = !empty($data['actionsCompleted']) ? 1 : 0;
        }

        try {
            $statement = $this->connection->prepare(
                "INSERT INTO
                {$this->table} 
                (
                 `customerBookingId`,
                 `packageCustomerId`,
                 `parentId`,
                 `amount`,
                 `dateTime`,
                 `status`,
                 `gateway`,
                 `gatewayTitle`,
                 `data`, `entity`,
                 `actionsCompleted`,
                 `created`,
                 `wcOrderId`,
                 `wcOrderItemId`,
                 `transactionId`,
                 `invoiceNumber`
                ) VALUES (
                  :customerBookingId,
                  :packageCustomerId,
                  :parentId,
                  :amount,
                  :dateTime,
                  :status,
                  :gateway,
                  :gatewayTitle,
                  :data,
                  :entity,
                  :actionsCompleted,
                  :created,
                  :wcOrderId,
                  :wcOrderItemId,
                  :transactionId,
                  {$invoiceNumberText}
                )"
            );

            $statement->execute($params);
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to add data in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }

        return $this->connection->lastInsertId();
    }

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

        $params = [
            ':customerBookingId' => $data['customerBookingId'] ? $data['customerBookingId'] : null,
            ':packageCustomerId' => $data['packageCustomerId'] ? $data['packageCustomerId'] : null,
            ':parentId'          => $data['parentId'] ? $data['parentId'] : null,
            ':amount'            => $data['amount'],
            ':dateTime'          => DateTimeService::getCustomDateTimeInUtc($data['dateTime']),
            ':status'            => $data['status'],
            ':gateway'           => $data['gateway'],
            ':gatewayTitle'      => $data['gatewayTitle'],
            ':data'              => $data['data'],
            ':transactionId'     => $data['transactionId'],
            ':id'                => $id,
        ];

        try {
            $statement = $this->connection->prepare(
                "UPDATE {$this->table}
                SET
                `customerBookingId` = :customerBookingId,
                `packageCustomerId` = :packageCustomerId,
                `parentId`          = :parentId,
                `amount`            = :amount,
                `dateTime`          = :dateTime,
                `status`            = :status,
                `gateway`           = :gateway,
                `gatewayTitle`      = :gatewayTitle,
                `data`              = :data,
                `transactionId`     = :transactionId
                WHERE
                id = :id"
            );

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

        return true;
    }

    /**
     * @param array $criteria
     *
     * @return Collection
     * @throws QueryExecutionException
     */
    public function getByCriteria($criteria)
    {
        $result = new Collection();

        $params = [];

        $where = [];

        if (!empty($criteria['bookingIds'])) {
            $queryBookings = [];

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

                $queryBookings[] = $param;

                $params[$param] = $value;
            }

            $where[] = 'customerBookingId IN (' . implode(', ', $queryBookings) . ')';
        }


        if (!empty($criteria['packageCustomerId'])) {
            $params[':packageCustomerId'] = $criteria['packageCustomerId'];
            $where[] = 'packageCustomerId = :packageCustomerId';
        }

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

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

                $queryIds[] = $param;

                $params[$param] = $value;
            }

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

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

        try {
            $statement = $this->connection->prepare(
                "SELECT
                    id AS id,
                    customerBookingId AS customerBookingId,
                    packageCustomerId AS packageCustomerId,
                    parentId AS parentId,
                    invoiceNumber AS invoiceNumber,
                    amount AS amount,
                    dateTime AS dateTime,
                    status AS status,
                    gateway AS gateway,
                    gatewayTitle AS gatewayTitle,
                    data AS data
                FROM {$this->table}
                {$where}"
            );

            $statement->execute($params);

            while ($row = $statement->fetch()) {
                $result->addItem(call_user_func([static::FACTORY, 'create'], $row), $row['id']);
            }
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }

        return $result;
    }

    /**
     * @param array $criteria
     * @param int   $itemsPerPage
     * @param boolean $invoice
     *
     * @return array
     * @throws QueryExecutionException
     */
    public function getFilteredIds($criteria, $itemsPerPage = null, $invoice = false)
    {
        $params = [];
        $appointmentParams1 = [];
        $appointmentParams2 = [];
        $eventParams        = [];
        $whereAppointment1  = [];
        $whereAppointment2  = [];
        $whereEvent         = [];

        if ($invoice) {
            $whereAppointment1[] = 'p.parentId IS NULL';
            $whereAppointment2[] = 'p.parentId IS NULL';
            $whereEvent[]        = 'p.parentId IS NULL';
        }

        $basedOnDate = $invoice ? 'created' : 'dateTime';

        if (!empty($criteria['ids'])) {
            $queryIds1 = [];
            $queryIds2 = [];
            $queryIds3 = [];

            foreach ($criteria['ids'] as $index => $value) {
                $param1      = ':id0' . $index;
                $param2      = ':id1' . $index;
                $param3      = ':id2' . $index;
                $queryIds1[] = $param1;
                $queryIds2[] = $param2;
                $queryIds3[] = $param3;
                $appointmentParams1[$param1] = $value;
                $appointmentParams2[$param2] = $value;
                $eventParams[$param3]        = $value;
            }

            $whereAppointment1[] = 'p.id IN (' . implode(', ', $queryIds1) . ')';
            $whereAppointment2[] = 'p.id IN (' . implode(', ', $queryIds2) . ')';
            $whereEvent[]        = 'p.id IN (' . implode(', ', $queryIds3) . ')';
        }

        if (!empty($criteria['dates'])) {
            $whereAppointment1[] = "(p.{$basedOnDate} BETWEEN :paymentAppointmentFrom1 AND :paymentAppointmentTo1)";
            $whereAppointment2[] = "(p.{$basedOnDate} BETWEEN :paymentAppointmentFrom2 AND :paymentAppointmentTo2)";
            $appointmentParams1[':paymentAppointmentFrom1'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
            $appointmentParams2[':paymentAppointmentFrom2'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
            $appointmentParams1[':paymentAppointmentTo1']   = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
            $appointmentParams2[':paymentAppointmentTo2']   = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);

            $whereEvent[] = "(p.{$basedOnDate} BETWEEN :paymentEventFrom AND :paymentEventTo)";
            $eventParams[':paymentEventFrom'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
            $eventParams[':paymentEventTo']   = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
        }

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

        if (!empty($criteria['customers'])) {
            $queryCustomers1 = [];
            $queryCustomers2 = [];
            $queryCustomers3 = [];

            foreach ((array)$criteria['customers'] as $index => $value) {
                $param1            = ':customer0' . $index;
                $param2            = ':customer1' . $index;
                $param3            = ':customer2' . $index;
                $queryCustomers1[] = $param1;
                $queryCustomers2[] = $param2;
                $queryCustomers3[] = $param3;
                $appointmentParams1[$param1] = $value;
                $appointmentParams2[$param2] = $value;
                $eventParams[$param3]        = $value;
            }

            $whereAppointment1[] = 'cb.customerId IN (' . implode(', ', $queryCustomers1) . ')';
            $whereAppointment2[] = 'pc.customerId IN (' . implode(', ', $queryCustomers2) . ')';
            $whereEvent[]        = 'cb.customerId IN (' . implode(', ', $queryCustomers3) . ')';
        }

        $eventsProvidersJoin = '';

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

        if (!empty($criteria['providers'])) {
            $queryProviders1 = [];
            $queryProviders2 = [];
            $queryProviders3 = [];

            foreach ((array)$criteria['providers'] as $index => $value) {
                $param1            = ':provider0' . $index;
                $param2            = ':provider1' . $index;
                $param3            = ':provider2' . $index;
                $queryProviders1[] = $param1;
                $queryProviders2[] = $param2;
                $queryProviders3[] = $param3;
                $appointmentParams1[$param1] = $value;
                $appointmentParams2[$param2] = $value;
                $eventParams[$param3]        = $value;
            }

            $whereAppointment1[] = 'a.providerId IN (' . implode(', ', $queryProviders1) . ')';
            $whereAppointment2[] = 'a.providerId IN (' . implode(', ', $queryProviders2) . ')';
            $whereEvent[]        = 'epu.userId IN (' . implode(', ', $queryProviders3) . ')';

            $eventsProvidersJoin = "
                INNER JOIN {$this->eventsPeriodsTable} ep ON ep.id = cbe.eventPeriodId
                INNER JOIN {$this->eventsProvidersTable} epu ON epu.eventId = ep.eventId
            ";
        }

        if (!empty($criteria['services'])) {
            $queryServices1 = [];
            $queryServices2 = [];

            foreach ((array)$criteria['services'] as $index => $value) {
                $param1           = ':service0' . $index;
                $param2           = ':service1' . $index;
                $queryServices1[] = $param1;
                $queryServices2[] = $param2;
                $appointmentParams1[$param1] = $value;
                $appointmentParams2[$param2] = $value;
            }

            $whereAppointment1[] = 'a.serviceId IN (' . implode(', ', $queryServices1) . ')';
            $whereAppointment2[] = 'a.serviceId IN (' . implode(', ', $queryServices2) . ')';
        }

        $appointments2ProvidersServicesJoin = '';

        if (!empty($criteria['providers']) || !empty($criteria['services'])) {
            $appointments2ProvidersServicesJoin = "
                INNER JOIN {$this->packagesCustomersServiceTable} pcs ON pc.id = pcs.packageCustomerId
                INNER JOIN {$this->bookingsTable} cb ON cb.packageCustomerServiceId = pcs.id
                INNER JOIN {$this->appointmentsTable} a ON a.id = cb.appointmentId
            ";
        }

        if (!empty($criteria['status'])) {
            $criteria['statuses'][] = $criteria['status'];
        }

        if (!empty($criteria['statuses'])) {
            $queryStatuses1 = [];
            $queryStatuses2 = [];
            $queryStatuses3 = [];

            foreach ($criteria['statuses'] as $index => $value) {
                $param1           = ':status0' . $index;
                $param2           = ':status1' . $index;
                $param3           = ':status2' . $index;
                $queryStatuses1[] = $param1;
                $queryStatuses2[] = $param2;
                $queryStatuses3[] = $param3;
                $appointmentParams1[$param1] = $value;
                $appointmentParams2[$param2] = $value;
                $eventParams[$param3]        = $value;
            }

            $whereAppointment1[] = 'p.status IN (' . implode(', ', $queryStatuses1) . ')';
            $whereAppointment2[] = 'p.status IN (' . implode(', ', $queryStatuses2) . ')';
            $whereEvent[]        = 'p.status IN (' . implode(', ', $queryStatuses3) . ')';
        }

        if (!empty($criteria['packages'])) {
            $queryPackages = [];

            foreach ((array)$criteria['packages'] as $index => $value) {
                $param           = ':package' . $index;
                $queryPackages[] = $param;
                $appointmentParams2[$param] = $value;
            }

            $whereAppointment2[] = "p.packageCustomerId IN (SELECT pc.id
              FROM {$this->packagesCustomersTable} pc
              WHERE pc.packageId IN (" . implode(', ', $queryPackages) . '))';
        }

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

            foreach ((array)$criteria['events'] as $index => $value) {
                $param         = ':event' . $index;
                $queryEvents[] = $param;
                $eventParams[$param] = $value;
            }

            $whereEvent[] = "p.customerBookingId IN (SELECT cbe.customerBookingId
              FROM {$this->eventsTable} e
              INNER JOIN {$this->eventsPeriodsTable} ep ON ep.eventId = e.id
              INNER JOIN {$this->customerBookingsToEventsPeriodsTable} cbe ON cbe.eventPeriodId = ep.id 
              WHERE e.id IN (" . implode(', ', $queryEvents) . '))';
        }

        $whereAppointment1 = $whereAppointment1 ? ' AND ' . implode(' AND ', $whereAppointment1) : '';
        $whereAppointment2 = $whereAppointment2 ? ' AND ' . implode(' AND ', $whereAppointment2) : '';
        $whereEvent        = $whereEvent ? ' AND ' . implode(' AND ', $whereEvent) : '';

        $groupBy       = '';
        $groupByAppointment1Clause = empty($criteria['separateRows']) ? "GROUP BY p.customerBookingId" : "";
        $groupByAppointment2Clause = empty($criteria['separateRows']) ? "GROUP BY p.packageCustomerId" : "";
        $groupByEventClause = empty($criteria['separateRows']) ? "GROUP BY p.customerBookingId" : "";
        if ($invoice) {
            $groupBy       = 'GROUP BY IFNULL(invoiceNumber, id)';
            $groupByAppointment1Clause = '';
            $groupByAppointment2Clause = '';
            $groupByEventClause = '';
        }

        $appointmentQuery1 = "SELECT
                p.id AS id,
                p.dateTime AS dateTime,
                p.created AS created,
                p.status AS status,
                p.amount AS amount,
                p.invoiceNumber AS invoiceNumber,
                'appointment' AS type
            FROM {$this->table} p
            INNER JOIN {$this->bookingsTable} cb ON cb.id = p.customerBookingId
            INNER JOIN {$this->appointmentsTable} a ON a.id = cb.appointmentId
            WHERE 1=1 {$whereAppointment1} {$groupByAppointment1Clause} ORDER BY p.id ASC";

        $appointmentQuery2 = "SELECT
                p.id AS id,
                p.dateTime AS dateTime,
                p.created AS created,
                p.status AS status,
                p.amount AS amount,
                p.invoiceNumber AS invoiceNumber,
                'package' AS type
            FROM {$this->table} p
            INNER JOIN {$this->packagesCustomersTable} pc ON p.packageCustomerId = pc.id
            {$appointments2ProvidersServicesJoin}
            WHERE 1=1 {$whereAppointment2} {$groupByAppointment2Clause} ORDER BY p.id ASC";

        $eventQuery = "SELECT
                p.id AS id,
                p.dateTime AS dateTime,
                p.created AS created,
                p.status AS status,
                p.amount AS amount,
                p.invoiceNumber AS invoiceNumber,
                'event' AS type
            FROM {$this->table} p
            INNER JOIN {$this->bookingsTable} cb ON cb.id = p.customerBookingId
            INNER JOIN {$this->customerBookingsToEventsPeriodsTable} cbe ON cbe.customerBookingId = cb.id
            {$eventsProvidersJoin}
            WHERE 1=1 {$whereEvent} {$groupByEventClause} ORDER BY p.id ASC";

        $result = [];

        if (isset($criteria['events'], $criteria['services'])) {
            return $result;
        } elseif (isset($criteria['services'])) {
            $paymentQuery = "({$appointmentQuery1}) UNION ALL ({$appointmentQuery2})";
            $params       = array_merge($params, $appointmentParams1, $appointmentParams2);
        } elseif (isset($criteria['events'])) {
            $paymentQuery = "({$eventQuery})";
            $params       = array_merge($params, $eventParams);
        } elseif (isset($criteria['packages'])) {
            $paymentQuery = "({$appointmentQuery2})";
            $params       = array_merge($params, $appointmentParams2);
        } else {
            $paymentQuery = "({$appointmentQuery1}) UNION ALL ({$appointmentQuery2}) UNION ALL ({$eventQuery})";
            $params       = array_merge($params, $appointmentParams1, $appointmentParams2, $eventParams);
        }

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

        $bookingTypeCondition = '';
        if (!empty($criteria['bookingTypes'])) {
            $bookingTypeCondition = 'WHERE type IN ("' . implode('", "', $criteria['bookingTypes']) . '")';
        }

        try {
            $order = "ORDER BY id, {$basedOnDate}";
            if (!empty($criteria['sort'])) {
                $order = "ORDER BY {$criteria['sort']['field']} {$criteria['sort']['order']}";
            }

            $statement = $this->connection->prepare(
                "SELECT * FROM ({$paymentQuery}) payments
                {$bookingTypeCondition}
                {$groupBy}
                {$order}
                {$limit}"
            );

            $statement->execute($params);

            $rows = $statement->fetchAll();
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }

        foreach ($rows as $row) {
            $result[(int)$row['id']] = $row['type'];
        }

        return $result;
    }

    /**
     * @param array $criteria
     * @param boolean $invoice
     *
     * @return int
     * @throws QueryExecutionException
     */
    public function getFilteredIdsCount($criteria, $invoice = false)
    {
        $params = [];
        $appointmentParams1 = [];
        $appointmentParams2 = [];
        $eventParams        = [];
        $whereAppointment1  = [];
        $whereAppointment2  = [];
        $whereEvent         = [];

        if ($invoice) {
            $whereAppointment1[] = 'p.parentId IS NULL';
            $whereAppointment2[] = 'p.parentId IS NULL';
            $whereEvent[]        = 'p.parentId IS NULL';
        }

        $basedOnDate = $invoice ? 'created' : 'dateTime';

        if (!empty($criteria['ids'])) {
            $queryIds1 = [];
            $queryIds2 = [];
            $queryIds3 = [];

            foreach ($criteria['ids'] as $index => $value) {
                $param1      = ':id0' . $index;
                $param2      = ':id1' . $index;
                $param3      = ':id2' . $index;
                $queryIds1[] = $param1;
                $queryIds2[] = $param2;
                $queryIds3[] = $param3;
                $appointmentParams1[$param1] = $value;
                $appointmentParams2[$param2] = $value;
                $eventParams[$param3]        = $value;
            }

            $whereAppointment1[] = 'p.id IN (' . implode(', ', $queryIds1) . ')';
            $whereAppointment2[] = 'p.id IN (' . implode(', ', $queryIds2) . ')';
            $whereEvent[]        = 'p.id IN (' . implode(', ', $queryIds3) . ')';
        }

        if (!empty($criteria['dates'])) {
            $whereAppointment1[] = "(p.{$basedOnDate} BETWEEN :paymentAppointmentFrom1 AND :paymentAppointmentTo1)";
            $whereAppointment2[] = "(p.{$basedOnDate} BETWEEN :paymentAppointmentFrom2 AND :paymentAppointmentTo2)";
            $appointmentParams1[':paymentAppointmentFrom1'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
            $appointmentParams2[':paymentAppointmentFrom2'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
            $appointmentParams1[':paymentAppointmentTo1']   = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
            $appointmentParams2[':paymentAppointmentTo2']   = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);

            $whereEvent[] = "(p.{$basedOnDate} BETWEEN :paymentEventFrom AND :paymentEventTo)";
            $eventParams[':paymentEventFrom'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
            $eventParams[':paymentEventTo']   = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
        }

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

        if (!empty($criteria['customers'])) {
            $queryCustomers1 = [];
            $queryCustomers2 = [];
            $queryCustomers3 = [];

            foreach ((array)$criteria['customers'] as $index => $value) {
                $param1            = ':customer0' . $index;
                $param2            = ':customer1' . $index;
                $param3            = ':customer2' . $index;
                $queryCustomers1[] = $param1;
                $queryCustomers2[] = $param2;
                $queryCustomers3[] = $param3;
                $appointmentParams1[$param1] = $value;
                $appointmentParams2[$param2] = $value;
                $eventParams[$param3]        = $value;
            }

            $whereAppointment1[] = 'cb.customerId IN (' . implode(', ', $queryCustomers1) . ')';
            $whereAppointment2[] = 'pc.customerId IN (' . implode(', ', $queryCustomers2) . ')';
            $whereEvent[]        = 'cb.customerId IN (' . implode(', ', $queryCustomers3) . ')';
        }

        $eventsProvidersJoin = '';

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

        if (!empty($criteria['providers'])) {
            $queryProviders1 = [];
            $queryProviders2 = [];
            $queryProviders3 = [];

            foreach ((array)$criteria['providers'] as $index => $value) {
                $param1            = ':provider0' . $index;
                $param2            = ':provider1' . $index;
                $param3            = ':provider2' . $index;
                $queryProviders1[] = $param1;
                $queryProviders2[] = $param2;
                $queryProviders3[] = $param3;
                $appointmentParams1[$param1] = $value;
                $appointmentParams2[$param2] = $value;
                $eventParams[$param3]        = $value;
            }

            $whereAppointment1[] = 'a.providerId IN (' . implode(', ', $queryProviders1) . ')';
            $whereAppointment2[] = 'a.providerId IN (' . implode(', ', $queryProviders2) . ')';
            $whereEvent[]        = 'epu.userId IN (' . implode(', ', $queryProviders3) . ')';

            $eventsProvidersJoin = "
                INNER JOIN {$this->eventsPeriodsTable} ep ON ep.id = cbe.eventPeriodId
                INNER JOIN {$this->eventsProvidersTable} epu ON epu.eventId = ep.eventId
            ";
        }

        if (!empty($criteria['services'])) {
            $queryServices1 = [];
            $queryServices2 = [];

            foreach ((array)$criteria['services'] as $index => $value) {
                $param1           = ':service0' . $index;
                $param2           = ':service1' . $index;
                $queryServices1[] = $param1;
                $queryServices2[] = $param2;
                $appointmentParams1[$param1] = $value;
                $appointmentParams2[$param2] = $value;
            }

            $whereAppointment1[] = 'a.serviceId IN (' . implode(', ', $queryServices1) . ')';
            $whereAppointment2[] = 'a.serviceId IN (' . implode(', ', $queryServices2) . ')';
        }

        $appointments2ProvidersServicesJoin = '';

        if (!empty($criteria['providers']) || !empty($criteria['services'])) {
            $appointments2ProvidersServicesJoin = "
                INNER JOIN {$this->packagesCustomersServiceTable} pcs ON pc.id = pcs.packageCustomerId
                INNER JOIN {$this->bookingsTable} cb ON cb.packageCustomerServiceId = pcs.id
                INNER JOIN {$this->appointmentsTable} a ON a.id = cb.appointmentId
            ";
        }

        if (!empty($criteria['status'])) {
            $criteria['statuses'][] = $criteria['status'];
        }

        if (!empty($criteria['statuses'])) {
            $queryStatuses1 = [];
            $queryStatuses2 = [];
            $queryStatuses3 = [];

            foreach ($criteria['statuses'] as $index => $value) {
                $param1           = ':status0' . $index;
                $param2           = ':status1' . $index;
                $param3           = ':status2' . $index;
                $queryStatuses1[] = $param1;
                $queryStatuses2[] = $param2;
                $queryStatuses3[] = $param3;
                $appointmentParams1[$param1] = $value;
                $appointmentParams2[$param2] = $value;
                $eventParams[$param3]        = $value;
            }

            $whereAppointment1[] = 'p.status IN (' . implode(', ', $queryStatuses1) . ')';
            $whereAppointment2[] = 'p.status IN (' . implode(', ', $queryStatuses2) . ')';
            $whereEvent[]        = 'p.status IN (' . implode(', ', $queryStatuses3) . ')';
        }

        if (!empty($criteria['packages'])) {
            $queryPackages = [];

            foreach ((array)$criteria['packages'] as $index => $value) {
                $param           = ':package' . $index;
                $queryPackages[] = $param;
                $appointmentParams2[$param] = $value;
            }

            $whereAppointment2[] = "p.packageCustomerId IN (SELECT pc.id
              FROM {$this->packagesCustomersTable} pc
              WHERE pc.packageId IN (" . implode(', ', $queryPackages) . '))';
        }

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

            foreach ((array)$criteria['events'] as $index => $value) {
                $param         = ':event' . $index;
                $queryEvents[] = $param;
                $eventParams[$param] = $value;
            }

            $whereEvent[] = "p.customerBookingId IN (SELECT cbe.customerBookingId
              FROM {$this->eventsTable} e
              INNER JOIN {$this->eventsPeriodsTable} ep ON ep.eventId = e.id
              INNER JOIN {$this->customerBookingsToEventsPeriodsTable} cbe ON cbe.eventPeriodId = ep.id 
              WHERE e.id IN (" . implode(', ', $queryEvents) . '))';
        }

        $whereAppointment1 = $whereAppointment1 ? ' AND ' . implode(' AND ', $whereAppointment1) : '';
        $whereAppointment2 = $whereAppointment2 ? ' AND ' . implode(' AND ', $whereAppointment2) : '';
        $whereEvent        = $whereEvent ? ' AND ' . implode(' AND ', $whereEvent) : '';

        $groupByAppointment1Clause = empty($criteria['separateRows']) ? "GROUP BY p.customerBookingId" : "";
        $groupByAppointment2Clause = empty($criteria['separateRows']) ? "GROUP BY p.packageCustomerId" : "";
        $groupByEventClause        = empty($criteria['separateRows']) ? "GROUP BY p.customerBookingId" : "";

        // Build list-style subqueries mirroring getFilteredIds (no ORDER BY / LIMIT here)
        $listAppointment1 = "SELECT
                p.id AS id,
                p.dateTime AS dateTime,
                p.created AS created,
                p.status AS status,
                p.invoiceNumber AS invoiceNumber,
                'appointment' AS type
            FROM {$this->table} p
            INNER JOIN {$this->bookingsTable} cb ON cb.id = p.customerBookingId
            INNER JOIN {$this->appointmentsTable} a ON a.id = cb.appointmentId
            WHERE 1=1 {$whereAppointment1} {$groupByAppointment1Clause}";

        $listAppointment2 = "SELECT
                p.id AS id,
                p.dateTime AS dateTime,
                p.created AS created,
                p.status AS status,
                p.invoiceNumber AS invoiceNumber,
                'package' AS type
            FROM {$this->table} p
            INNER JOIN {$this->packagesCustomersTable} pc ON p.packageCustomerId = pc.id
            {$appointments2ProvidersServicesJoin}
            WHERE 1=1 {$whereAppointment2} {$groupByAppointment2Clause}";

        $listEvent = "SELECT
                p.id AS id,
                p.dateTime AS dateTime,
                p.created AS created,
                p.status AS status,
                p.invoiceNumber AS invoiceNumber,
                'event' AS type
            FROM {$this->table} p
            INNER JOIN {$this->bookingsTable} cb ON cb.id = p.customerBookingId
            INNER JOIN {$this->customerBookingsToEventsPeriodsTable} cbe ON cbe.customerBookingId = cb.id
            {$eventsProvidersJoin}
            WHERE 1=1 {$whereEvent} {$groupByEventClause}";

        if (isset($criteria['events'], $criteria['services'])) {
            return 0;
        }

        // Assemble a single union of the list-style subqueries matching the list endpoint
        if (isset($criteria['services'])) {
            $listPaymentQuery = "({$listAppointment1}) UNION ALL ({$listAppointment2})";
            $params           = array_merge($params, $appointmentParams1, $appointmentParams2);
        } elseif (isset($criteria['events'])) {
            $listPaymentQuery = "({$listEvent})";
            $params           = array_merge($params, $eventParams);
        } elseif (isset($criteria['packages'])) {
            $listPaymentQuery = "({$listAppointment2})";
            $params           = array_merge($params, $appointmentParams2);
        } else {
            $listPaymentQuery = "({$listAppointment1}) UNION ALL ({$listAppointment2}) UNION ALL ({$listEvent})";
            $params           = array_merge($params, $appointmentParams1, $appointmentParams2, $eventParams);
        }

        $bookingTypeCondition = '';
        if (!empty($criteria['bookingTypes'])) {
            $bookingTypeCondition = 'WHERE type IN ("' . implode('", "', $criteria['bookingTypes']) . '")';
        }

        // Invoice page: count distinct invoices across the union to mirror list grouping
        if ($invoice) {
            try {
                $statement = $this->connection->prepare(
                    "SELECT COUNT(DISTINCT IFNULL(invoiceNumber, id)) AS cnt FROM ({$listPaymentQuery}) payments {$bookingTypeCondition}"
                );
                $statement->execute($params);
                $row = $statement->fetch();
                return (int)($row['cnt'] ?? 0);
            } catch (\Exception $e) {
                throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
            }
        }

        // Non-invoice: simply count rows of the grouped list union (matches separateRows logic)
        try {
            $statement = $this->connection->prepare(
                "SELECT COUNT(*) AS cnt FROM ({$listPaymentQuery}) payments {$bookingTypeCondition}"
            );
            $statement->execute($params);
            $row = $statement->fetch();
            return (int)($row['cnt'] ?? 0);
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }
    }

    /**
     * Returns a collection of customers that have birthday on today's date and where notification is not sent
     *
     * @return Collection
     * @throws InvalidArgumentException
     * @throws QueryExecutionException
     * @throws \Exception
     */
    public function getUncompletedActionsForPayments()
    {
        $params = [];

        $currentDateTime = "STR_TO_DATE('" . DateTimeService::getNowDateTimeInUtc() . "', '%Y-%m-%d %H:%i:%s')";

        $pastDateTime =
            "STR_TO_DATE('" .
            DateTimeService::getNowDateTimeObjectInUtc()->modify('-1 day')->format('Y-m-d H:i:s') .
            "', '%Y-%m-%d %H:%i:%s')";

        try {
            $statement = $this->connection->prepare(
                "SELECT * FROM {$this->table} 
                WHERE
                      actionsCompleted = 0 AND
                      {$currentDateTime} > DATE_ADD(created, INTERVAL 300 SECOND) AND
                      {$pastDateTime} < created AND
                      entity IS NOT NULL"
            );

            $statement->execute($params);

            $rows = $statement->fetchAll();
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }

        $items = [];

        foreach ($rows as $row) {
            $items[] = call_user_func([static::FACTORY, 'create'], $row);
        }

        return new Collection($items);
    }

    /**
     * @param array $data
     * @param boolean $invoice
     *
     * @return array
     * @throws QueryExecutionException
     */
    public function getSecondaryPaymentIds($data, $invoice)
    {
        $params = [];

        $where = [];

        $parentIdParam1  = null;
        $parentIdParam2  = null;
        $paymentIdParam2 = null;

        foreach ($data as $index => $item) {
            $paymentIdParam1          = ':paymentId1' . $index;
            $params[$paymentIdParam1] = $item['paymentId'];

            if ($invoice) {
                if (!empty($item['parentId'])) {
                    $parentIdParam1          = ':parentId1' . $index;
                    $params[$parentIdParam1] = $item['parentId'];
                    $parentIdParam2          = ':parentId2' . $index;
                    $params[$parentIdParam2] = $item['parentId'];
                }
                $paymentIdParam2          = ':paymentId2' . $index;
                $params[$paymentIdParam2] = $item['paymentId'];
            }

            $relationParam = ':' . $item['columnName'] . $index;

            $params[$relationParam] = $item['columnId'];

            // change in the future to simply retrieve by invoiceNumber when on invoice page since all the related payments will have the same invoiceNumber
            // cannot be done immediately since invoiceNumber is NULL for existing payments
            if ($invoice) {
                $where[] =
                    "((id <> $paymentIdParam1 AND " .
                    $item['columnName'] . " = $relationParam) OR parentId = $paymentIdParam2" .
                    (!empty($item['parentId']) ? " OR id = $parentIdParam1 OR parentId = $parentIdParam2)" : ")");
            } else {
                $where[] = "(id <> $paymentIdParam1 AND " . $item['columnName'] . " = $relationParam)";
            }
        }

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

        try {
            $statement = $this->connection->prepare(
                "SELECT
                    p.id AS id,
                    p.entity AS entity
                FROM {$this->table} p
                {$where}"
            );

            $statement->execute($params);

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

        $result = [];

        foreach ($rows as $row) {
            $result[(int)$row['id']] = $row['entity'];
        }

        return $result;
    }

    /**
     * @param int $paymentId
     * @param string $transactionId
     *
     * @throws QueryExecutionException
     */
    public function updateTransactionId($paymentId, $transactionId)
    {
        $params = [
            ':transactionId' => $transactionId,
            ':paymentId1'    => $paymentId,
            ':paymentId2'    => $paymentId
        ];

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

            $statement->execute($params);
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to update data in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }

        return true;
    }



    /**
     * @param array $data
     *
     * @return bool
     * @throws QueryExecutionException
     */
    public function setInvoiceNumber($data)
    {
        $params = [
            ':id1' => $data['id'],
            ':id2' => $data['id'],
            ":{$data['columnName']}" => $data['columnValue']
        ];

        $where = "WHERE id = :id1 OR parentId = :id2 OR {$data['columnName']} = :{$data['columnName']}";

        if (!empty($data['parentId'])) {
            $params[':parentId1'] = $params[':parentId2'] = $data['parentId'];
            $where = ' OR id = :parentId1 OR parentId = :parentId2';
        }

        try {
            $statement = $this->connection->prepare(
                "UPDATE {$this->table} 
                SET `invoiceNumber` = (SELECT COALESCE(MAX(invoiceNumber), 0) + 1 FROM (SELECT * FROM {$this->table}) AS p)
                {$where}"
            );

            $statement->execute($params);
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to save invoice number in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }

        return true;
    }

    /**
     * @param array $rows
     *
     * @return array
     */
    private function getEntitiesPaymentsResult($rows)
    {
        $result = [];

        foreach ($rows as &$row) {
            $customerInfo = $row['info'] ? json_decode($row['info'], true) : null;

            if (empty($result[(int)$row['id']])) {
                $result[(int)$row['id']] = [
                    'id' =>  (int)$row['id'],
                    'dateTime' =>  DateTimeService::getCustomDateTimeFromUtc($row['dateTime']),
                    'created'  =>  DateTimeService::getCustomDateTimeFromUtc($row['created']),
                    'bookingStart' =>  $row['bookingStart'] ?
                        DateTimeService::getCustomDateTimeFromUtc($row['bookingStart']) : null,
                    'status' =>  $row['status'],
                    'parentId' =>  $row['parentId'],
                    'wcOrderId' =>  $row['wcOrderId'],
                    'wcOrderItemId' =>  $row['wcOrderItemId'],
                    'gateway' =>  $row['gateway'],
                    'gatewayTitle' =>  $row['gatewayTitle'],
                    'transactionId' =>  $row['transactionId'],
                    'type' => $row['type'],
                    'name' => $row['bookableName'],
                    'customerBookingId' =>  (int)$row['customerBookingId'] ?: null,
                    'packageCustomerId' =>  (int)$row['packageCustomerId'] ?: null,
                    'amount' =>  (float)$row['amount'],
                    'invoiceNumber' => $row['invoiceNumber'],
                    'providers' =>  (int)$row['providerId'] ? [
                        [
                            'id' => (int)$row['providerId'],
                            'fullName' => $row['providerFirstName'] . ' ' . $row['providerLastName'],
                            'email' => $row['providerEmail'],
                            'firstName' => $row['providerFirstName'],
                            'lastName' => $row['providerLastName'],
                            'picture' => $row['providerPictureThumbPath'],
                        ]
                    ] : [],
                    'location' => (int)$row['locationId'] || $row['location_address'] ?
                        [
                            'id' => (int)$row['locationId'],
                            'location_name' => $row['location_name'],
                            'location_address' => $row['location_address'],
                        ]
                        : null,
                    'customerId' =>  (int)$row['customerId'],
                    'serviceId' =>  (int)$row['serviceId'] ?: null,
                    'appointmentId' =>  (int)$row['appointmentId'] ?: null,
                    'packageId' =>  (int)$row['packageId'] ?: null,
                    'bookedPrice' =>  (float)$row['bookedPrice'] ?: 0,
                    'bookedTax' =>  $row['bookedTax'] ?: null,
                    'bookingId' => !empty($row['bookingId']) ? (int)$row['bookingId'] : null,
                    'bookableName' => $row['bookableName'],
                    'customerFirstName' => $customerInfo ? $customerInfo['firstName'] : $row['customerFirstName'],
                    'customerLastName' => $customerInfo ? $customerInfo['lastName'] : $row['customerLastName'],
                    'info' => $row['info'],
                    'customerEmail' => $row['customerEmail'],
                    'customerStatus' => $row['customerStatus'],
                    'coupon' => !empty($row['coupon_id']) ? [
                        'id' => (int)$row['coupon_id'],
                        'discount' => (float)$row['coupon_discount'],
                        'deduction' => (float)$row['coupon_deduction'],
                        'code' => $row['coupon_code']
                    ] : null,
                    'persons' => (int)$row['persons'],
                    'aggregatedPrice' => (bool)$row['aggregatedPrice'],
                    'extras' => [],
                ];
            }

            if ($result[(int)$row['id']] && $row['bookingExtra_id']) {
                $result[(int)$row['id']]['extras'][] = [
                    'id' => (int)$row['bookingExtra_id'],
                    'extraId' => (int)$row['bookingExtra_extraId'],
                    'quantity' => (int)$row['bookingExtra_quantity'],
                    'price' => (float)$row['bookingExtra_price'],
                    'aggregatedPrice' => (bool)$row['bookingExtra_aggregatedPrice'],
                    'tax' => $row['bookingExtra_tax'] ?: null
                ];
            }
        }

        return $result;
    }

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

        $where = [];

        if (!empty($ids)) {
            foreach ($ids as $index => $value) {
                $param = ':sId' . $index;

                $params[$param] = $value;
            }

            $where[] = 'p.id IN (' . implode(', ', array_keys($params)) . ')';
        }

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

        $customerBookingsExtrasTable = CustomerBookingsToExtrasTable::getTableName();

        $couponsTable = CouponsTable::getTableName();

        $locationsTable = LocationsTable::getTableName();

        try {
            $statement = $this->connection->prepare(
                "SELECT
                    p.id AS id,
                    p.customerBookingId AS customerBookingId,
                    NULL AS packageCustomerId,
                    p.amount AS amount,
                    p.invoiceNumber AS invoiceNumber,
                    p.dateTime AS dateTime,
                    p.created AS created,
                    p.status AS status,
                    p.wcOrderId AS wcOrderId,
                    p.wcOrderItemId AS wcOrderItemId,
                    p.gateway AS gateway,
                    p.gatewayTitle AS gatewayTitle,
                    p.transactionId AS transactionId,
                    p.parentId AS parentId,
                    p.entity AS type,
                    
                    NULL AS packageId,
                    cb.id AS bookingId,
                    cb.price AS bookedPrice,
                    cb.tax AS bookedTax,
                    a.providerId AS providerId,
                    cb.customerId AS customerId,
                    cb.persons AS persons,
                    cb.aggregatedPrice AS aggregatedPrice,
                    cb.info AS info,
    
                    cbe.id AS bookingExtra_id,
                    cbe.extraId AS bookingExtra_extraId,
                    cbe.quantity AS bookingExtra_quantity,
                    cbe.price AS bookingExtra_price,
                    cbe.aggregatedPrice AS bookingExtra_aggregatedPrice,
                    cbe.tax AS bookingExtra_tax,
            
                    c.id AS coupon_id,
                    c.discount AS coupon_discount,
                    c.deduction AS coupon_deduction,
                    c.code AS coupon_code,
           
                    a.serviceId AS serviceId,
                    a.id AS appointmentId,
                    a.bookingStart AS bookingStart,
                    s.name AS bookableName,
                    cu.firstName AS customerFirstName,
                    cu.lastName AS customerLastName,
                    cu.email AS customerEmail,
                    cu.status AS customerStatus,
                    pu.firstName AS providerFirstName,
                    pu.lastName AS providerLastName,
                    pu.email AS providerEmail,
                    pu.pictureThumbPath AS providerPictureThumbPath,
                    l.id AS locationId,
                    l.name AS location_name,
                    l.address AS location_address
                FROM {$this->table} p
                INNER JOIN {$this->bookingsTable} cb ON cb.id = p.customerBookingId
                LEFT JOIN {$customerBookingsExtrasTable} cbe ON cbe.customerBookingId = cb.id
                LEFT JOIN {$couponsTable} c ON c.id = cb.couponId
                INNER JOIN {$this->appointmentsTable} a ON a.id = cb.appointmentId
                INNER JOIN {$this->servicesTable} s ON s.id = a.serviceId
                INNER JOIN {$this->usersTable} cu ON cu.id = cb.customerId
                INNER JOIN {$this->usersTable} pu ON pu.id = a.providerId
                LEFT JOIN {$locationsTable} l ON l.id = a.locationId
                WHERE 1=1 {$where}"
            );

            $statement->execute($params);

            $rows = $statement->fetchAll();
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }

        return $this->getEntitiesPaymentsResult($rows);
    }

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

        $where = [];

        if (!empty($ids)) {
            foreach ($ids as $index => $value) {
                $param = ':eId' . $index;

                $params[$param] = $value;
            }

            $where[] = 'p.id IN (' . implode(', ', array_keys($params)) . ')';
        }

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

        $couponsTable = CouponsTable::getTableName();

        $locationsTable = LocationsTable::getTableName();

        try {
            $statement = $this->connection->prepare(
                "SELECT
                    p.id AS id,
                    p.customerBookingId AS customerBookingId,
                    NULL AS packageCustomerId,
                    p.amount AS amount,
                    p.invoiceNumber AS invoiceNumber,
                    p.dateTime AS dateTime,
                    p.created AS created,
                    p.status AS status,
                    p.wcOrderId AS wcOrderId,
                    p.wcOrderItemId AS wcOrderItemId,
                    p.gateway AS gateway,
                    p.gatewayTitle AS gatewayTitle,
                    p.transactionId AS transactionId,
                    p.parentId AS parentId,
                    p.entity AS type,
                    
                    NULL AS packageId,
                    cb.id AS bookingId,
                    cb.price AS bookedPrice,
                    cb.tax AS bookedTax,
                    NULL AS providerId,
                    cb.customerId AS customerId,
                    cb.persons AS persons,
                    cb.aggregatedPrice AS aggregatedPrice,
                    cb.info AS info,
                    
                    NULL AS bookingExtra_id,
                    NULL AS bookingExtra_extraId,
                    NULL AS bookingExtra_quantity,
                    NULL AS bookingExtra_price,
                    NULL AS bookingExtra_aggregatedPrice,
                    NULL AS bookingExtra_tax,
           
                    c.id AS coupon_id,
                    c.discount AS coupon_discount,
                    c.deduction AS coupon_deduction,
                    c.code AS coupon_code,
           
                    NULL AS serviceId,
                    NULL AS appointmentId,
                    NULL AS bookingStart,
                    NULL AS bookableName,
                    cu.firstName AS customerFirstName,
                    cu.lastName AS customerLastName,
                    cu.email AS customerEmail,
                    cu.status AS customerStatus,
                    NULL AS providerFirstName,
                    NULL AS providerLastName,
                    NULL AS providerEmail,
                    l.id AS locationId,
                    l.name AS location_name,
                    (CASE WHEN e.customlocation IS NOT NULL THEN e.customlocation ELSE l.address END) AS location_address
                FROM {$this->table} p
                INNER JOIN {$this->bookingsTable} cb ON cb.id = p.customerBookingId
                LEFT JOIN {$couponsTable} c ON c.id = cb.couponId
                INNER JOIN {$this->usersTable} cu ON cu.id = cb.customerId
                INNER JOIN {$this->customerBookingsToEventsPeriodsTable} cbe ON cbe.customerBookingId = cb.id
                INNER JOIN {$this->eventsPeriodsTable} ep ON ep.id = cbe.eventPeriodId
                INNER JOIN {$this->eventsTable} e ON e.id = ep.eventId
                LEFT JOIN {$this->eventsProvidersTable} epu ON epu.eventId = ep.eventId
                LEFT JOIN {$locationsTable} l ON l.id = e.locationId
                WHERE 1=1 {$where}"
            );

            $statement->execute($params);

            $rows = $statement->fetchAll();
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }

        return $this->getEntitiesPaymentsResult($rows);
    }

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

        $where = [];

        if (!empty($ids)) {
            foreach ($ids as $index => $value) {
                $param = ':pId' . $index;

                $params[$param] = $value;
            }

            $where[] = 'p.id IN (' . implode(', ', array_keys($params)) . ')';
        }

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

        $couponsTable = CouponsTable::getTableName();

        try {
            $statement = $this->connection->prepare(
                "SELECT
                    p.id AS id,
                    NULL AS customerBookingId,
                    p.packageCustomerId AS packageCustomerId,
                    p.amount AS amount,
                    p.invoiceNumber AS invoiceNumber,
                    p.dateTime AS dateTime,
                    p.created AS created,
                    p.status AS status,
                    p.wcOrderId AS wcOrderId,
                    p.wcOrderItemId AS wcOrderItemId,
                    p.gateway AS gateway,
                    p.gatewayTitle AS gatewayTitle,
                    p.transactionId AS transactionId,
                    p.parentId AS parentId,
                    p.entity AS type,
                    
                    pc.packageId AS packageId,
                    pc.id AS bookingId,
                    pc.price AS bookedPrice,
                    pc.tax AS bookedTax,
                    NULL AS providerId,
                    pc.customerId AS customerId,
                    NULL AS persons,
                    NULL AS aggregatedPrice,
                    cb.info AS info,  
                    
                    NULL AS bookingExtra_id,
                    NULL AS bookingExtra_extraId,
                    NULL AS bookingExtra_quantity,
                    NULL AS bookingExtra_price,
                    NULL AS bookingExtra_aggregatedPrice,
                    NULL AS bookingExtra_tax,
           
                    c.id AS coupon_id,
                    c.discount AS coupon_discount,
                    c.deduction AS coupon_deduction,
                    c.code AS coupon_code,
           
                    NULL AS serviceId,
                    NULL AS appointmentId,
                    NULL AS bookingStart,
                    pa.name AS bookableName,
                    cu.firstName AS customerFirstName,
                    cu.lastName AS customerLastName,
                    cu.email AS customerEmail,
                    cu.status AS customerStatus,
                    '' AS providerFirstName,
                    '' AS providerLastName,
                    '' AS providerEmail,
                    '' AS locationId,
                    '' AS location_name,
                    '' AS location_address
                FROM {$this->table} p
                INNER JOIN {$this->packagesCustomersTable} pc ON p.packageCustomerId = pc.id
                INNER JOIN {$this->usersTable} cu ON cu.id = pc.customerId
                LEFT JOIN {$couponsTable} c ON c.id = pc.couponId
                INNER JOIN {$this->packagesTable} pa ON pa.id = pc.packageId
                INNER JOIN {$this->packagesCustomersServiceTable} pcs ON pc.id = pcs.packageCustomerId
                LEFT JOIN {$this->bookingsTable} cb ON cb.packageCustomerServiceId = pcs.id
                LEFT JOIN {$this->appointmentsTable} a ON a.id = cb.appointmentId
                WHERE 1=1 {$where} ORDER BY p.id ASC"
            );

            $statement->execute($params);

            $rows = $statement->fetchAll();
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }

        return $this->getEntitiesPaymentsResult($rows);
    }
}
