<?php

namespace AmeliaBooking\Infrastructure\Repository\Location;

use AmeliaBooking\Domain\Collection\Collection;
use AmeliaBooking\Domain\Common\Exceptions\InvalidArgumentException;
use AmeliaBooking\Domain\Entity\Location\Location;
use AmeliaBooking\Domain\Factory\Bookable\Service\ServiceFactory;
use AmeliaBooking\Domain\Repository\Location\LocationRepositoryInterface;
use AmeliaBooking\Domain\Services\DateTime\DateTimeService;
use AmeliaBooking\Domain\ValueObjects\String\Status;
use AmeliaBooking\Infrastructure\Common\Exceptions\QueryExecutionException;
use AmeliaBooking\Infrastructure\Connection;
use AmeliaBooking\Infrastructure\Repository\AbstractRepository;
use AmeliaBooking\Domain\Factory\Location\LocationFactory;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Bookable\CategoriesTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Bookable\ServicesTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\AppointmentsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\EventsPeriodsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\EventsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\User\Provider\ProvidersLocationTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\User\Provider\ProvidersPeriodLocationTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\User\Provider\ProvidersPeriodTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\User\Provider\ProvidersServiceTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\User\UsersTable;

/**
 * Class LocationRepositoryInterface
 *
 * @package AmeliaBooking\Infrastructure\Repository
 */
class LocationRepository extends AbstractRepository implements LocationRepositoryInterface
{
    public const FACTORY         = LocationFactory::class;
    public const SERVICE_FACTORY = ServiceFactory::class;

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

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

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

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

    /**
     * @param Connection $connection
     * @param string     $table
     * @param string     $providerLocationTable
     * @param string     $providerServicesTable
     * @param string     $servicesTable
     * @param            $locationViewsTable
     */
    public function __construct(
        Connection $connection,
        $table,
        $providerLocationTable,
        $providerServicesTable,
        $servicesTable,
        $locationViewsTable
    ) {
        parent::__construct($connection, $table);

        $this->providerServicesTable = $providerServicesTable;
        $this->providerLocationTable = $providerLocationTable;
        $this->servicesTable         = $servicesTable;
        $this->locationViewsTable    = $locationViewsTable;
    }

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

        $params = [
            ':status'           => $data['status'],
            ':name'             => $data['name'],
            ':description'      => $data['description'],
            ':address'          => $data['address'],
            ':phone'            => $data['phone'],
            ':latitude'         => $data['latitude'],
            ':longitude'        => $data['longitude'],
            ':pictureFullPath'  => $data['pictureFullPath'],
            ':pictureThumbPath' => $data['pictureThumbPath'],
            ':pin'              => $data['pin'],
            ':translations'     => $data['translations'],
            ':countryPhoneIso'  => isset($data['countryPhoneIso']) ? $data['countryPhoneIso'] : null
        ];

        try {
            $statement = $this->connection->prepare(
                "INSERT INTO {$this->table}
                (
                `status`,
                `name`,
                `description`,
                `address`,
                `phone`,
                `latitude`,
                `longitude`,
                `pictureFullPath`,
                `pictureThumbPath`,
                `pin`,
                `translations`,
                `countryPhoneIso`
                )
                 VALUES (
                 :status,
                 :name,
                 :description,
                 :address,
                 :phone,
                 :latitude,
                 :longitude,
                 :pictureFullPath,
                 :pictureThumbPath,
                 :pin,
                 :translations,
                 :countryPhoneIso
                 )"
            );

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

        $params = [
            ':status'           => $data['status'],
            ':name'             => $data['name'],
            ':description'      => $data['description'],
            ':address'          => $data['address'],
            ':phone'            => $data['phone'],
            ':latitude'         => $data['latitude'],
            ':longitude'        => $data['longitude'],
            ':pictureFullPath'  => $data['pictureFullPath'],
            ':pictureThumbPath' => $data['pictureThumbPath'],
            ':pin'              => $data['pin'],
            ':translations'     => $data['translations'],
            ':id'               => $id,
            ':countryPhoneIso'  => isset($data['countryPhoneIso']) ? $data['countryPhoneIso'] : null
        ];

        try {
            $statement = $this->connection->prepare(
                "UPDATE {$this->table}
                SET `status` = :status, `name` = :name, `description` = :description, `address` = :address,
                `phone` = :phone, `latitude` = :latitude, `longitude` = :longitude,
                `pictureFullPath` = :pictureFullPath, `pictureThumbPath` = :pictureThumbPath,
                `pin` = :pin, `translations` = :translations, `countryPhoneIso` = :countryPhoneIso
                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);
        }
    }

    /**
     * @return Collection
     * @throws InvalidArgumentException
     * @throws QueryExecutionException
     */
    public function getAllOrderedByName()
    {
        try {
            $statement = $this->connection->query(
                "SELECT * FROM {$this->table} ORDER BY name"
            );

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

        $items = new Collection();
        foreach ($rows as $row) {
            $items->addItem(call_user_func([static::FACTORY, 'create'], $row), $row['id']);
        }

        return $items;
    }

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

        $order = '';
        if (!empty($criteria['sort'])) {
            $orderColumn = $criteria['sort']['field'];
            $orderDirection = $criteria['sort']['order'];
            $order = "ORDER BY {$orderColumn} {$orderDirection}";
        }

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

            foreach ($terms as $term) {
                $param = ":search{$termIndex}";
                $params[$param] = "%{$term}%";

                $where[] = "(
                        l.name LIKE {$param}
                        OR l.address LIKE {$param}
                        OR l.id LIKE {$param}
                    )";

                $termIndex++;
            }
            $search = ' AND (' . implode(' AND ', $where) . ')';
        }

        $services = '';
        if (!empty($criteria['services'])) {
            foreach ((array)$criteria['services'] as $index => $value) {
                ++$index;
                $services .= ':service' . $index . ', ';
                $params[':service' . $index] = $value;
            }

            $services = ' AND s.id IN (' . rtrim($services, ', ') . ')';
        }

        $status = '';
        if (isset($criteria['status'])) {
            $status = ' AND l.status = :status';
            $params[':status'] = $criteria['status'];
        }

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

        try {
            $statement = $this->connection->prepare(
                "SELECT 
                  l.id,
                  l.status,
                  l.name,
                  l.description,
                  l.address,
                  l.phone,
                  l.latitude,
                  l.longitude,
                  l.pictureFullPath,
                  l.pictureThumbPath,
                  l.pin,
                  l.translations,
                  l.countryPhoneIso
                FROM {$this->table} l
                LEFT JOIN {$this->providerLocationTable} pl ON pl.locationId = l.id
                LEFT JOIN {$this->providerServicesTable}  ps ON ps.userId = pl.userId
                LEFT JOIN {$this->servicesTable} s ON s.id = ps.serviceId
                WHERE 1 = 1 $search $services $status
                GROUP BY l.id
                {$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);
        }

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

        return new Collection($items);
    }

    /**
     * @param $criteria
     *
     * @return array
     * @throws InvalidArgumentException
     * @throws QueryExecutionException
     */
    public function getCount($criteria)
    {
        $providerLocationTable = ProvidersLocationTable::getTableName();
        $providerServicesTable = ProvidersServiceTable::getTableName();
        $servicesTable         = ServicesTable::getTableName();

        $params = [];

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

            foreach ($terms as $term) {
                $param = ":search{$termIndex}";
                $params[$param] = "%{$term}%";

                $where[] = "(
                        l.name LIKE {$param}
                        OR l.address LIKE {$param}
                        OR l.id LIKE {$param}
                    )";

                $termIndex++;
            }
            $search = ' AND (' . implode(' AND ', $where) . ')';
        }

        $services = '';
        if (!empty($criteria['services'])) {
            foreach ((array)$criteria['services'] as $index => $value) {
                ++$index;
                $services .= ':service' . $index . ', ';
                $params[':service' . $index] = $value;
            }

            $services = ' AND s.id IN (' . rtrim($services, ', ') . ')';
        }


        try {
            $statement = $this->connection->prepare(
                "SELECT COUNT(*) as count
                FROM (
                    SELECT l.id 
                    FROM {$this->table} l
                    LEFT JOIN {$providerLocationTable} pl ON pl.locationId = l.id
                    LEFT JOIN {$providerServicesTable}  ps ON ps.userId = pl.userId
                    LEFT JOIN {$servicesTable} s ON s.id = ps.serviceId
                    WHERE l.status IN (:visibleStatus, :hiddenStatus) $search $services
                    GROUP BY l.id
                ) as t"
            );

            $params[':visibleStatus'] = Status::VISIBLE;
            $params[':hiddenStatus']  = Status::HIDDEN;

            $statement->execute($params);

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

        return $rows;
    }

    /**
     * @param $id
     *
     * @return Collection
     * @throws InvalidArgumentException
     * @throws QueryExecutionException
     */
    public function getServicesById($id)
    {
        $params = [
            ':id' => $id
        ];

        try {
            $statement = $this->connection->prepare(
                "
              SELECT s.*
              FROM {$this->table} l
              INNER JOIN {$this->providerLocationTable} pl ON pl.locationId = l.id
              INNER JOIN {$this->providerServicesTable} ps ON ps.userId = pl.userId
              INNER JOIN {$this->servicesTable} s ON s.id = ps.serviceId
              WHERE l.id = :id
              GROUP BY s.id"
            );

            $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::SERVICE_FACTORY, 'create'], $row);
        }

        return new Collection($items);
    }

    /**
     * Return an array of locations with the number of appointments for the given date period.
     * Keys of the array are Locations IDs.
     *
     * @param $criteria
     *
     * @return array
     * @throws QueryExecutionException
     * @throws \AmeliaBooking\Domain\Common\Exceptions\InvalidArgumentException
     */
    public function getAllNumberOfAppointments($criteria)
    {
        $userTable        = UsersTable::getTableName();
        $appointmentTable = AppointmentsTable::getTableName();

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

        if ($criteria['dates']) {
            $where[] = "(a.bookingStart BETWEEN :bookingFrom AND :bookingTo)";
            $params[':bookingFrom'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
            $params[':bookingTo']   = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
        }

        if (isset($criteria['status'])) {
            $where[]           = 'l.status = :status';
            $params[':status'] = $criteria['status'];
        }

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

        try {
            $statement = $this->connection->prepare(
                "SELECT
                l.id,
                l.name,
                COUNT(l.id) AS appointments
            FROM {$this->table} l
            INNER JOIN {$this->providerLocationTable} pl ON pl.locationId = l.id
            INNER JOIN {$userTable} u ON u.id = pl.userId
            INNER JOIN {$appointmentTable} a ON u.id = a.providerId
            $where
            GROUP BY l.id"
            );

            $statement->execute($params);

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

        $result = [];

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

        return $result;
    }

    /**
     * Return an array of locations with the number of views for the given date period.
     * Keys of the array are Locations IDs.
     *
     * @param $criteria
     *
     * @return array
     * @throws QueryExecutionException
     */
    public function getAllNumberOfViews($criteria)
    {
        $params = [];

        $where = [];

        if ($criteria['dates']) {
            $where[] = "(lv.date BETWEEN :bookingFrom AND :bookingTo)";

            $params[':bookingFrom'] = explode(' ', $criteria['dates'][0])[0];

            $params[':bookingTo'] = explode(' ', $criteria['dates'][1])[0];
        }

        if (isset($criteria['status'])) {
            $where[] = 'l.status = :status';

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

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

        try {
            $statement = $this->connection->prepare(
                "SELECT
            l.id,
            l.name,
            SUM(lv.views) AS views
            FROM {$this->table} l
            INNER JOIN {$this->locationViewsTable} lv ON lv.locationId = l.id 
            $where
            GROUP BY l.id"
            );

            $statement->execute($params);

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

        $result = [];

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

        return $result;
    }

    /**
     * @param $locationId
     *
     * @return boolean
     * @throws QueryExecutionException
     */
    public function addViewStats($locationId)
    {
        $date = DateTimeService::getNowDate();

        $params = [
            ':locationId' => $locationId,
            ':date'       => $date,
            ':views'      => 1
        ];

        try {
            // Check if there is already data for this provider for this date
            $statement = $this->connection->prepare(
                "SELECT COUNT(*) AS count 
                FROM {$this->locationViewsTable} AS pv 
                WHERE pv.locationId = :locationId 
                AND pv.date = :date"
            );

            $statement->bindParam(':locationId', $locationId);
            $statement->bindParam(':date', $date);
            $statement->execute();
            $count = $statement->fetch()['count'];

            if (!$count) {
                $statement = $this->connection->prepare(
                    "INSERT INTO {$this->locationViewsTable}
                    (`locationId`, `date`, `views`)
                    VALUES 
                    (:locationId, :date, :views)"
                );
            } else {
                $statement = $this->connection->prepare(
                    "UPDATE {$this->locationViewsTable} pv SET pv.views = pv.views + :views
                    WHERE pv.locationId = :locationId
                    AND pv.date = :date"
                );
            }

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

        return true;
    }

    /**
     * @param int $locationId
     *
     * @return mixed
     * @throws QueryExecutionException
     */
    public function deleteViewStats($locationId)
    {
        $params = [
            ':locationId'  => $locationId,
        ];

        try {
            $statement = $this->connection->prepare(
                "DELETE FROM {$this->locationViewsTable} WHERE locationId = :locationId"
            );

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


    /**
     * @param $id
     *
     * @return Collection
     * @throws InvalidArgumentException
     * @throws QueryExecutionException
     */
    public function getByIdWithEntities($id)
    {
        $usersTable = UsersTable::getTableName();
        $providerPeriodsTable = ProvidersPeriodTable::getTableName();
        $providerPeriodsLocations = ProvidersPeriodLocationTable::getTableName();
        $eventsTable = EventsTable::getTableName();
        $categoriesTable = CategoriesTable::getTableName();
        $eventPeriodsTable = EventsPeriodsTable::getTableName();

        $params = [
            ':id' => $id,
            ':eventLocationId' => $id
        ];

        try {
            $statement = $this->connection->prepare("
              SELECT 
                  l.id AS location_id,
                  l.status AS location_status,
                  l.name AS location_name,
                  l.description AS location_description,
                  l.address AS location_address,
                  l.phone AS location_phone,
                  l.latitude AS location_latitude,
                  l.longitude AS location_longitude,
                  l.pictureFullPath AS location_pictureFullPath,
                  l.pictureThumbPath AS location_pictureThumbPath,
                  l.pin AS location_pin,
                  l.translations AS location_translations,
                  
                  pu.id AS provider_id,
                  pu.firstName AS provider_firstName,
                  pu.lastName AS provider_lastName,
                  pu.email AS provider_email,
                  pu.phone AS provider_phone,
                  pu.pictureThumbPath AS provider_pictureThumbPath,
                  pu.pictureFullPath AS provider_pictureFullPath,

                  s.id AS service_id,
                  s.name AS service_name,
                  s.color AS service_color,
                  
                  c.id AS category_id,
                  c.name AS category_name,
                  
                  e.id AS event_id,
                  e.name AS event_name,
                  e.color AS event_color,
                  
                  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.googleMeetUrl AS event_googleMeetUrl
            
              FROM {$this->table} l
              LEFT JOIN {$this->providerLocationTable} pl ON pl.locationId = l.id
              LEFT JOIN {$providerPeriodsTable} pp ON pp.locationId = l.id
              LEFT JOIN {$providerPeriodsLocations} ppl ON ppl.locationId = l.id
              LEFT JOIN {$usersTable} pu ON pu.id = pl.userId
              LEFT JOIN {$this->providerServicesTable} ps ON ps.userId = pl.userId
              LEFT JOIN (SELECT * FROM {$this->servicesTable} LIMIT 11) s ON s.id = ps.serviceId
              LEFT JOIN {$categoriesTable} c ON c.id = s.categoryId
              LEFT JOIN (SELECT * FROM {$eventsTable} WHERE locationId = :eventLocationId LIMIT 11 ) e ON e.locationId = l.id
              LEFT JOIN {$eventPeriodsTable} ep ON ep.eventId = e.id
              WHERE l.id = :id 
              ");

            $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);
    }
}
