<?php

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

namespace AmeliaBooking\Infrastructure\Repository\Coupon;

use AmeliaBooking\Domain\Collection\Collection;
use AmeliaBooking\Domain\Common\Exceptions\InvalidArgumentException;
use AmeliaBooking\Domain\Entity\Coupon\Coupon;
use AmeliaBooking\Domain\Entity\Entities;
use AmeliaBooking\Domain\Factory\Coupon\CouponFactory;
use AmeliaBooking\Domain\Services\DateTime\DateTimeService;
use AmeliaBooking\Domain\ValueObjects\Number\Integer\WholeNumber;
use AmeliaBooking\Infrastructure\Common\Exceptions\NotFoundException;
use AmeliaBooking\Infrastructure\Connection;
use AmeliaBooking\Infrastructure\Repository\AbstractRepository;
use AmeliaBooking\Domain\Repository\Coupon\CouponRepositoryInterface;
use AmeliaBooking\Infrastructure\Common\Exceptions\QueryExecutionException;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\EventsPeriodsTable;

/**
 * Class CouponRepository
 *
 * @package AmeliaBooking\Infrastructure\Repository\Coupon
 */
class CouponRepository extends AbstractRepository implements CouponRepositoryInterface
{
    public const FACTORY = CouponFactory::class;

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

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

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

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

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

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

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

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

    /**
     * @param Connection $connection
     * @param string     $table
     * @param string     $servicesTable
     * @param string     $couponToServicesTable
     * @param string     $eventsTable
     * @param string     $couponToEventsTable
     * @param string     $packagesTable
     * @param string     $couponToPackagesTable
     * @param string     $bookingsTable
     */
    public function __construct(
        Connection $connection,
        $table,
        $servicesTable,
        $couponToServicesTable,
        $eventsTable,
        $couponToEventsTable,
        $packagesTable,
        $couponToPackagesTable,
        $bookingsTable,
        $eventsPeriodsTable
    ) {
        parent::__construct($connection, $table);

        $this->servicesTable         = $servicesTable;
        $this->couponToServicesTable = $couponToServicesTable;
        $this->eventsTable           = $eventsTable;
        $this->couponToEventsTable   = $couponToEventsTable;
        $this->packagesTable         = $packagesTable;
        $this->couponToPackagesTable = $couponToPackagesTable;
        $this->bookingsTable         = $bookingsTable;
        $this->eventsPeriodsTable    = $eventsPeriodsTable;
    }

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

        $params = [
            ':code'                  => $data['code'],
            ':discount'              => $data['discount'],
            ':deduction'             => $data['deduction'],
            ':limit'                 => (int)$data['limit'],
            ':customerLimit'         => (int)$data['customerLimit'],
            ':status'                => $data['status'],
            ':notificationInterval'  => $data['notificationInterval'],
            ':notificationRecurring' => $data['notificationRecurring'] ? 1 : 0,
            ':expirationDate'        => $data['expirationDate'],
            ':startDate'             => $data['startDate'],
            ':allServices'           => $data['allServices'] ? 1 : 0,
            ':allEvents'             => $data['allEvents'] ? 1 : 0,
            ':allPackages'           => $data['allPackages'] ? 1 : 0
        ];

        try {
            $statement = $this->connection->prepare(
                "INSERT INTO
                {$this->table} 
                (
                 `code`,
                 `discount`,
                 `deduction`,
                 `limit`,
                 `customerLimit`,
                 `status`,
                 `notificationInterval`,
                 `notificationRecurring`,
                 `expirationDate`,
                 `startDate`,
                 `allServices`,
                 `allEvents`,
                 `allPackages`  
                ) VALUES (
                  :code, 
                  :discount, 
                  :deduction,
                  :limit,
                  :customerLimit,
                  :status,
                  :notificationInterval,
                  :notificationRecurring,
                  :expirationDate,
                  :startDate,
                  :allServices,
                  :allEvents,
                  :allPackages
                )"
            );


            $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 Coupon $entity
     *
     * @return bool
     * @throws QueryExecutionException
     */
    public function update($id, $entity)
    {
        $data = $entity->toArray();

        $params = [
            ':code'                  => $data['code'],
            ':discount'              => $data['discount'],
            ':deduction'             => $data['deduction'],
            ':limit'                 => (int)$data['limit'],
            ':customerLimit'         => (int)$data['customerLimit'],
            ':status'                => $data['status'],
            ':notificationInterval'  => $data['notificationInterval'],
            ':notificationRecurring' => $data['notificationRecurring'] ? 1 : 0,
            ':id'                    => $id,
            ':expirationDate'        => $data['expirationDate'],
            ':startDate'             => $data['startDate'],
            ':allServices'           => $data['allServices'] ? 1 : 0,
            ':allEvents'             => $data['allEvents'] ? 1 : 0,
            ':allPackages'           => $data['allPackages'] ? 1 : 0
        ];

        try {
            $statement = $this->connection->prepare(
                "UPDATE {$this->table}
                SET
                `code`                  = :code,
                `discount`              = :discount,
                `deduction`             = :deduction,
                `limit`                 = :limit,
                `customerLimit`         = :customerLimit,
                `status`                = :status,
                `notificationInterval`  = :notificationInterval,
                `notificationRecurring` = :notificationRecurring,
                `expirationDate`        = :expirationDate,
                `startDate`             = :startDate,
                `allServices`           = :allServices,
                `allEvents`             = :allEvents,
                `allPackages`           = :allPackages
                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 int $id
     *
     * @return Coupon
     * @throws QueryExecutionException
     * @throws NotFoundException
     * @throws InvalidArgumentException
     */
    public function getById($id)
    {
        try {
            $statement = $this->connection->prepare(
                "SELECT
                    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.notificationInterval AS coupon_notificationInterval,
                    c.notificationRecurring AS coupon_notificationRecurring,
                    c.status AS coupon_status,
                    c.expirationDate AS coupon_expirationDate,
                    c.startDate AS coupon_startDate,
                    c.allServices AS coupon_allServices,
                    c.allEvents AS coupon_allEvents,
                    c.allPackages AS coupon_allPackages,
                    s.id AS service_id,
                    s.price AS service_price,
                    s.minCapacity AS service_minCapacity,
                    s.maxCapacity AS service_maxCapacity,
                    s.name AS service_name,
                    s.description AS service_description,
                    s.color AS service_color,
                    s.status AS service_status,
                    s.categoryId AS service_categoryId,
                    s.duration AS service_duration,
                    e.id AS event_id,
                    e.price AS event_price,
                    e.name AS event_name,
                    ep.id AS event_periodId,
                    ep.periodStart AS event_periodStart,
                    ep.periodEnd AS event_periodEnd,
                    p.id AS package_id,
                    p.price AS package_price,
                    p.name AS package_name
                FROM {$this->table} c
                LEFT JOIN {$this->couponToServicesTable} cs ON cs.couponId = c.id
                LEFT JOIN {$this->couponToEventsTable} ce ON ce.couponId = c.id
                LEFT JOIN {$this->couponToPackagesTable} cp ON cp.couponId = c.id
                LEFT JOIN {$this->servicesTable} s ON cs.serviceId = s.id
                LEFT JOIN {$this->eventsTable} e ON ce.eventId = e.id
                LEFT JOIN {$this->eventsPeriodsTable} ep ON ep.eventId = e.id
                LEFT JOIN {$this->packagesTable} p ON cp.packageId = p.id
                WHERE c.id = :couponId"
            );

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

            $statement->execute();

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

        if (!$rows) {
            throw new NotFoundException('Data not found in ' . __CLASS__);
        }

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

        $this->populateCouponsUsed($coupons);

        return $coupons->getItem($id);
    }

    /**
     * @param array $criteria
     * @param int   $itemsPerPage
     *
     * @return Collection
     * @throws QueryExecutionException
     */
    public function getFiltered($criteria, $itemsPerPage)
    {
        try {
            $params = [];

            $where = [];

            if (!empty($criteria['search'])) {
                $params[':search'] = "%{$criteria['search']}%";

                $where[] = 'UPPER(c.code) LIKE UPPER(:search)';
            }

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

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

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

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

                foreach ((array)$criteria['services'] as $index => $value) {
                    $param           = ':service' . $index;
                    $queryServices[] = $param;
                    $params[$param]  = $value;
                }

                $where[] = "(c.id IN (
                    SELECT couponId FROM {$this->couponToServicesTable} 
                    WHERE serviceId IN (" . implode(', ', $queryServices) . ')
                ) OR c.allServices = 1)';
            }

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

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

                $where[] = "(c.id IN (
                    SELECT couponId FROM {$this->couponToEventsTable} 
                    WHERE eventId IN (" . implode(', ', $queryEvents) . ')
                ) OR c.allEvents = 1)';
            }

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

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

                $where[] = "(c.id IN (
                    SELECT couponId FROM {$this->couponToPackagesTable} 
                    WHERE packageId IN (" . implode(', ', $queryPackages) . ')
                ) OR c.allPackages = 1)';
            }


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

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

            $allowedSortFields = [
                'id', 'code', 'discount', 'deduction', 'limit', 'customerLimit',
                'status', 'startDate', 'expirationDate', 'times_used',
            ];
            $field     = 'id';
            $direction = 'ASC';
            if (!empty($criteria['sort'])) {
                $candidateField     = (string)($criteria['sort']['field'] ?? '');
                $candidateDirection = strtoupper((string)($criteria['sort']['order'] ?? 'ASC'));
                $field              = in_array($candidateField, $allowedSortFields, true) ? $candidateField : 'id';
                $direction          = $candidateDirection === 'DESC' ? 'DESC' : 'ASC';
            }
            $order = $field === 'times_used'
                ? "ORDER BY times_used {$direction}, c.id ASC"
                : "ORDER BY c.`{$field}` {$direction}";

            $statement = $this->connection->prepare(
                "SELECT
                    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.notificationInterval AS coupon_notificationInterval,
                    c.notificationRecurring AS coupon_notificationRecurring,
                    c.status AS coupon_status,
                    c.expirationDate AS coupon_expirationDate,
                    c.startDate AS coupon_startDate,
                    c.allServices AS coupon_allServices,
                    c.allEvents AS coupon_allEvents,
                    c.allPackages AS coupon_allPackages,
                    COALESCE((SELECT COUNT(*) FROM {$this->bookingsTable} cb WHERE cb.couponId = c.id), 0) AS times_used
                FROM {$this->table} c
                {$where}
                {$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);
        }

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

    /**
     * @param array $criteria
     *
     * @return mixed
     * @throws QueryExecutionException
     */
    public function getCount($criteria)
    {
        try {
            $params = [];

            $where = [];

            if (!empty($criteria['search'])) {
                $params[':search'] = "%{$criteria['search']}%";

                $where[] = 'UPPER(c.code) LIKE UPPER(:search)';
            }

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

                foreach ((array)$criteria['services'] as $index => $value) {
                    $param           = ':service' . $index;
                    $queryServices[] = $param;
                    $params[$param]  = $value;
                }

                $where[] = "(c.id IN (SELECT couponId FROM {$this->couponToServicesTable}
                WHERE serviceId IN (" . implode(', ', $queryServices) . ')) OR c.allServices = 1)';
            }

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

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

                $where[] = "(c.id IN (
                    SELECT couponId FROM {$this->couponToEventsTable} 
                    WHERE eventId IN (" . implode(', ', $queryEvents) . ')
                ) OR c.allEvents = 1)';
            }

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

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

                $where[] = "(c.id IN (
                    SELECT couponId FROM {$this->couponToPackagesTable} 
                    WHERE packageId IN (" . implode(', ', $queryPackages) . ')
                ) OR c.allPackages = 1)';
            }

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

            $statement = $this->connection->prepare(
                "SELECT COUNT(*) AS count
                FROM {$this->table} c
                $where"
            );

            $statement->execute($params);

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

        return $row;
    }

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

            $where = [];

            if (isset($criteria['code'])) {
                $where[] = $criteria['couponsCaseInsensitive'] ? 'LOWER(c.code) = LOWER(:code)' : 'c.code = :code';

                $params[':code'] = $criteria['code'];
            }

            if (!empty($criteria['notificationInterval'])) {
                $where[] = 'c.notificationInterval != 0';
            }

            if (!empty($criteria['notExpired'])) {
                $currentDateTime = "STR_TO_DATE('" . DateTimeService::getNowDateTimeInUtc() . "', '%Y-%m-%d %H:%i:%s')";

                $where[] = "(c.expirationDate IS NULL OR c.expirationDate >= {$currentDateTime})";
            }

            if (!empty($criteria['notStarted'])) {
                $currentDateTime = "STR_TO_DATE('" . DateTimeService::getNowDateTimeInUtc() . "', '%Y-%m-%d %H:%i:%s')";

                $where[] = "(c.startDate IS NULL OR c.startDate <= {$currentDateTime})";
            }


            if (!empty($criteria['couponIds'])) {
                $couponIdsParams = [];

                foreach ((array)$criteria['couponIds'] as $key => $id) {
                    $couponIdsParams[":id$key"] = $id;
                }

                if ($couponIdsParams) {
                    $where[] = '(c.id IN ( ' . implode(', ', array_keys($couponIdsParams)) . '))';

                    $params = array_merge($params, $couponIdsParams);
                }
            }

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

            $statement = $this->connection->prepare(
                "SELECT
                    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.notificationInterval AS coupon_notificationInterval,
                    c.notificationRecurring AS coupon_notificationRecurring,
                    c.status AS coupon_status,
                    c.expirationDate AS coupon_expirationDate,
                    c.startDate AS coupon_startDate,
                    c.allServices AS coupon_allServices,
                    c.allEvents AS coupon_allEvents,
                    c.allPackages AS coupon_allPackages
                FROM {$this->table} c
                {$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);
        }

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

        if (!$coupons->length()) {
            return $coupons;
        }

        $this->populateCouponsUsed($coupons);

        return $coupons;
    }

    /**
     * Populate used counts for provided coupons from bookings table.
     *
     * @throws QueryExecutionException
     * @throws InvalidArgumentException
     */
    private function populateCouponsUsed(Collection $coupons): void
    {
        if (!$coupons->length()) {
            return;
        }

        $params = [];

        foreach ($coupons->keys() as $key => $id) {
            $params[":id$key"] = $id;
        }

        $where = 'WHERE cb.couponId IN ( ' . implode(', ', array_keys($params)) . ')';

        try {
            $statement = $this->connection->prepare(
                "SELECT
                    DISTINCT(cb.couponId) AS couponId,
                    COUNT(*) AS used
                FROM {$this->bookingsTable} cb
                {$where}
                GROUP BY cb.couponId"
            );

            $statement->execute($params);

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

        foreach ($rows as $row) {
            if ($coupons->keyExists($row['couponId'])) {
                /** @var Coupon $coupon */
                $coupon = $coupons->getItem($row['couponId']);

                $coupon->setUsed(new WholeNumber($row['used']));
            }
        }
    }

    /**
     * @param array $couponIds
     * @param array $serviceIds
     *
     * @return array
     *
     * @throws QueryExecutionException
     */
    public function getCouponsServicesIds($couponIds, $serviceIds = [])
    {
        $couponsParams = [];

        $servicesParams = [];

        $where = [];

        if ($couponIds) {
            foreach ($couponIds as $key => $couponId) {
                $couponsParams[":id$key"] = $couponId;
            }

            $where[] = '(couponId IN (' . implode(', ', array_keys($couponsParams)) . '))';
        }

        if ($serviceIds) {
            foreach ($serviceIds as $key => $serviceId) {
                $servicesParams[":serviceId$key"] = $serviceId;
            }

            $where[] = '(serviceId IN (' . implode(', ', array_keys($servicesParams)) . '))';
        }

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

        try {
            $statement = $this->connection->prepare(
                "SELECT serviceId, couponId FROM {$this->couponToServicesTable} {$where} GROUP BY serviceId, couponId"
            );

            $statement->execute(array_merge($couponsParams, $servicesParams));

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

    /**
     * @param array $couponIds
     * @param array $eventIds
     *
     * @return array
     *
     * @throws QueryExecutionException
     */
    public function getCouponsEventsIds($couponIds, $eventIds = [])
    {
        $couponsParams = [];

        $eventsParams = [];

        $where = [];

        if ($couponIds) {
            foreach ($couponIds as $key => $couponId) {
                $couponsParams[":id$key"] = $couponId;
            }

            $where[] = '(couponId IN (' . implode(', ', array_keys($couponsParams)) . '))';
        }

        if ($eventIds) {
            foreach ($eventIds as $key => $eventId) {
                $eventsParams[":eventId$key"] = $eventId;
            }

            $where[] = '(eventId IN (' . implode(', ', array_keys($eventsParams)) . '))';
        }

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

        try {
            $statement = $this->connection->prepare(
                "SELECT eventId, couponId FROM {$this->couponToEventsTable} {$where} GROUP BY eventId, couponId"
            );

            $statement->execute(array_merge($couponsParams, $eventsParams));

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

    /**
     * @param array $couponIds
     * @param array $packageIds
     *
     * @return array
     *
     * @throws QueryExecutionException
     */
    public function getCouponsPackagesIds($couponIds, $packageIds = [])
    {
        $couponsParams = [];

        $packagesParams = [];

        $where = [];

        if ($couponIds) {
            foreach ($couponIds as $key => $couponId) {
                $couponsParams[":id$key"] = $couponId;
            }

            $where[] = '(couponId IN (' . implode(', ', array_keys($couponsParams)) . '))';
        }

        if ($packageIds) {
            foreach ($packageIds as $key => $packageId) {
                $packagesParams[":packageId$key"] = $packageId;
            }

            $where[] = '(packageId IN (' . implode(', ', array_keys($packagesParams)) . '))';
        }

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

        try {
            $statement = $this->connection->prepare(
                "SELECT packageId, couponId FROM {$this->couponToPackagesTable} {$where} GROUP BY packageId, couponId"
            );

            $statement->execute(array_merge($couponsParams, $packagesParams));

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