<?php

namespace AmeliaBooking\Infrastructure\Repository\User;

use AmeliaBooking\Domain\Collection\Collection;
use AmeliaBooking\Domain\Common\Exceptions\InvalidArgumentException;
use AmeliaBooking\Domain\Entity\User\AbstractUser;
use AmeliaBooking\Domain\Entity\User\Admin;
use AmeliaBooking\Domain\Entity\User\Customer;
use AmeliaBooking\Domain\Entity\User\Manager;
use AmeliaBooking\Domain\Entity\User\Provider;
use AmeliaBooking\Domain\Factory\User\UserFactory;
use AmeliaBooking\Domain\Repository\User\UserRepositoryInterface;
use AmeliaBooking\Domain\ValueObjects\Json;
use AmeliaBooking\Domain\ValueObjects\String\Password;
use AmeliaBooking\Infrastructure\Licence;
use AmeliaBooking\Infrastructure\Common\Exceptions\QueryExecutionException;
use AmeliaBooking\Infrastructure\DB\WPDB\Statement;
use AmeliaBooking\Infrastructure\Repository\AbstractRepository;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\AppointmentsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\CustomerBookingsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\CustomerBookingsToEventsPeriodsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\EventsPeriodsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\EventsProvidersTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\EventsTable;

/**
 * Class UserRepository
 *
 * @package AmeliaBooking\Infrastructure\Repository
 */
class UserRepository extends AbstractRepository implements UserRepositoryInterface
{
    public const FACTORY = UserFactory::class;

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

        $params = [
            ':type'                  => $data['type'],
            ':status'                => $data['status'] ?: 'visible',
            ':externalId'            => $data['externalId'] ?: null,
            ':firstName'             => $data['firstName'],
            ':lastName'              => $data['lastName'],
            ':email'                 => $data['email'],
            ':note'                  => isset($data['note']) ? $data['note'] : null,
            ':description'           => isset($data['description']) ? $data['description'] : null,
            ':phone'                 => isset($data['phone']) ? $data['phone'] : null,
            ':gender'                => isset($data['gender']) ? $data['gender'] : null,
            ':birthday'              => $data['birthday'] ? $data['birthday']->format('Y-m-d') : null,
            ':pictureFullPath'       => $data['pictureFullPath'],
            ':pictureThumbPath'      => $data['pictureThumbPath'],
            ':password'              => isset($data['password']) ? $data['password'] : null,
            ':usedTokens'            => isset($data['usedTokens']) ? $data['usedTokens'] : null,
            ':countryPhoneIso'       => isset($data['countryPhoneIso']) ? $data['countryPhoneIso'] : null,
            ':stripeConnect'         => !empty($data['stripeConnect']) ? json_encode($data['stripeConnect']) : null,
            ':employeeAppleCalendar' => !empty($data['employeeAppleCalendar']) ? json_encode($data['employeeAppleCalendar']) : null,
            ':error'                 => '',
            ':customFields'          => isset($data['customFields']) ? $data['customFields'] : null,
        ];

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

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

        try {
            $statement = $this->connection->prepare(
                "INSERT INTO {$this->table} (
                {$additionalData['columns']}
                `type`,
                `status`,
                `externalId`,
                `firstName`,
                `lastName`,
                `email`,
                `note`,
                `description`,
                `phone`,
                `gender`,
                `birthday`,
                `pictureFullPath`,
                `pictureThumbPath`,
                `countryPhoneIso`,
                `usedTokens`,
                `stripeConnect`,
                `employeeAppleCalendar`,   
                `password`,
                `error`,
                `customFields`
                ) VALUES (
                {$additionalData['placeholders']}
                :type,
                :status,
                :externalId,
                :firstName,
                :lastName,
                :email,
                :note,
                :description,
                :phone,
                :gender,
                STR_TO_DATE(:birthday, '%Y-%m-%d'),
                :pictureFullPath,
                :pictureThumbPath,
                :countryPhoneIso,
                :usedTokens,
                :stripeConnect,
                :employeeAppleCalendar,
                :password,
                :error,
                :customFields
                )"
            );

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

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

    /**
     * @param int          $id
     * @param AbstractUser|array $entity
     *
     * @return bool
     * @throws QueryExecutionException
     */
    public function update($id, $entity)
    {
        if (!is_array($entity)) {
            $data = $entity->toArray();
        } else {
            $data = $entity;
        }

        $params = [
            ':externalId'       => $data['externalId'] ?: null,
            ':firstName'        => $data['firstName'],
            ':lastName'         => $data['lastName'],
            ':email'            => isset($data['email']) ? $data['email'] : null,
            ':note'             => isset($data['note']) ? $data['note'] : null,
            ':description'      => isset($data['description']) ? $data['description'] : null,
            ':phone'            => isset($data['phone']) ? $data['phone'] : null,
            ':gender'           => isset($data['gender']) ? $data['gender'] : null,
            ':birthday'         => isset($data['birthday']) ? $data['birthday']->format('Y-m-d') : null,
            ':pictureFullPath'  => $data['pictureFullPath'],
            ':pictureThumbPath' => $data['pictureThumbPath'],
            ':countryPhoneIso'  => isset($data['countryPhoneIso']) ? $data['countryPhoneIso'] : null,
            ':password'         => isset($data['password']) ? $data['password'] : null,
            ':stripeConnect'    => !empty($data['stripeConnect']) ? json_encode($data['stripeConnect']) : null,
            ':employeeAppleCalendar' => !empty($data['employeeAppleCalendar']) ? json_encode($data['employeeAppleCalendar']) : null,
            ':id'               => $id,
            ':customFields'     => isset($data['customFields']) ? $data['customFields'] : null,
        ];

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

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

        try {
            $statement = $this->connection->prepare(
                "UPDATE {$this->table}
                SET
                {$additionalData['columnsPlaceholders']}
                `externalId` = :externalId,
                `firstName` = :firstName,
                `lastName` = :lastName,
                `email` = :email,
                `note` = :note, 
                `description` = :description,    
                `phone` = :phone,
                `gender` = :gender,
                `birthday` = STR_TO_DATE(:birthday, '%Y-%m-%d'),
                `countryPhoneIso` = :countryPhoneIso,
                `pictureFullPath` = :pictureFullPath,
                `pictureThumbPath` = :pictureThumbPath,
                `stripeConnect` = :stripeConnect,     
                `employeeAppleCalendar` = :employeeAppleCalendar,           
                `customFields` = :customFields,
                `password` = IFNULL(:password, `password`)
                WHERE 
                id = :id"
            );

            $statement->execute($params);

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

    /**
     * @param $externalId
     *
     * @return Admin|Customer|Manager|Provider|bool
     * @throws InvalidArgumentException
     * @throws QueryExecutionException
     */
    public function findByExternalId($externalId)
    {
        try {
            $statement = $this->connection->prepare("SELECT * FROM {$this->table} WHERE externalId = :id");
            $statement->bindParam(':id', $externalId);
            $statement->execute();
            $row = $statement->fetch();
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to find by external id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }

        if (!$row) {
            return false;
        }

        return UserFactory::create($row);
    }

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

        try {
            $statement = $this->connection->prepare($this->selectQuery() . ' WHERE type = :type');

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

    /**
     * Returns Collection of all customers and other users that have at least one booking
     *
     * @return Collection
     * @throws QueryExecutionException
     * @throws InvalidArgumentException
     */
    public function getAllWithAllowedBooking()
    {
        try {
            $bookingsTable = CustomerBookingsTable::getTableName();

            $statement = $this->connection->query(
                "
            SELECT
            u.id AS id,
            u.firstName AS firstName,
            u.lastName AS lastName,
            u.email AS email,
            u.note AS note,
            u.description AS description,
            u.phone AS phone,
            u.countryPhoneIso AS countryPhoneIso,
            u.gender AS gender,
            u.status AS status,
            u.translations AS translations
            FROM {$this->table} u
            LEFT JOIN {$bookingsTable} cb ON cb.customerId = u.id
            WHERE u.type = 'customer' OR (cb.id IS NOT NULL AND u.type IN ('admin', 'provider', 'manager'))
            GROUP BY u.id
            ORDER BY CONCAT(firstName, ' ', lastName) 
            "
            );

            $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[$row['id']] = call_user_func([static::FACTORY, 'create'], $row);
        }

        return new Collection($items);
    }

    /**
     * Returns Collection of all users that have no bookings (neither appointment nor event),
     * or whose bookings belong to appointments/events of the given provider.
     *
     * @param int $providerId
     *
     * @return Collection
     * @throws QueryExecutionException
     * @throws InvalidArgumentException
     */
    public function getProviderAllowedCustomers($providerId)
    {
        $bookingsTable = CustomerBookingsTable::getTableName();

        $appointmentsTable = AppointmentsTable::getTableName();

        $bookingsToEventsTable = CustomerBookingsToEventsPeriodsTable::getTableName();

        $eventsPeriodsTable = EventsPeriodsTable::getTableName();

        $eventsTable = EventsTable::getTableName();

        $eventsProvidersTable = EventsProvidersTable::getTableName();

        try {
            // 1) User IDs that have no bookings at all
            $statement = $this->connection->prepare(
                "
                SELECT u.id
                FROM {$this->table} u
                WHERE u.type = 'customer' AND
                      u.id NOT IN (SELECT DISTINCT cb.customerId FROM {$bookingsTable} cb)
                "
            );

            $statement->execute();

            $userIds = array_map('intval', array_column($statement->fetchAll(), 'id'));

            // 2) User IDs from the provider's appointments
            $statement = $this->connection->prepare(
                "
                    SELECT DISTINCT cb.customerId AS id
                    FROM {$bookingsTable} cb
                    INNER JOIN {$appointmentsTable} a ON a.id = cb.appointmentId
                    WHERE a.providerId = :providerId
                    "
            );

            $statement->execute([':providerId' => $providerId]);

            $userIds = array_unique(
                array_merge(
                    $userIds,
                    array_map('intval', array_column($statement->fetchAll(), 'id'))
                )
            );

            // 3) User IDs from the provider's events (organizer or assigned provider)
            $statement = $this->connection->prepare(
                "
                    SELECT DISTINCT cb.customerId AS id
                    FROM {$bookingsTable} cb
                    INNER JOIN {$bookingsToEventsTable} cbep ON cbep.customerBookingId = cb.id
                    INNER JOIN {$eventsPeriodsTable} ep ON ep.id = cbep.eventPeriodId
                    INNER JOIN {$eventsTable} ev ON ev.id = ep.eventId
                    LEFT JOIN {$eventsProvidersTable} evpr ON evpr.eventId = ev.id
                    WHERE evpr.userId = :providerId OR evpr.userId IS NULL
                    "
            );

            $statement->execute([
                ':providerId' => $providerId,
            ]);

            $userIds = array_unique(
                array_merge(
                    $userIds,
                    array_map('intval', array_column($statement->fetchAll(), 'id'))
                )
            );

            if (empty($userIds)) {
                return new Collection();
            }

            // Fetch full user data for the collected IDs
            $userIdsImploded = implode(', ', array_map('intval', $userIds));

            $statement = $this->connection->prepare(
                "
                SELECT
                    u.id AS id,
                    u.firstName AS firstName,
                    u.lastName AS lastName,
                    u.email AS email,
                    u.note AS note,
                    u.phone AS phone,
                    u.countryPhoneIso AS countryPhoneIso,
                    u.gender AS gender,
                    u.status AS status,
                    u.translations AS translations
                FROM {$this->table} u
                WHERE u.id IN ({$userIdsImploded})
                ORDER BY CONCAT(u.firstName, ' ', u.lastName)
                "
            );

            $statement->execute();

            $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[$row['id']] = call_user_func([static::FACTORY, 'create'], $row);
        }

        return new Collection($items);
    }

    /**
     * @param string  $email
     * @param boolean $setPassword
     * @param boolean $setUsedTokens
     *
     * @return Admin|Customer|Manager|Provider|null
     * @throws InvalidArgumentException
     * @throws QueryExecutionException
     */
    public function getByEmail($email, $setPassword = false, $setUsedTokens = false)
    {
        try {
            $statement = $this->connection->prepare($this->selectQuery() . ' WHERE LOWER(email) = LOWER(:email)');

            $statement->execute(
                array(
                ':email' => $email
                )
            );

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

        if (!$row) {
            return null;
        }

        /** @var Admin|Customer|Manager|Provider $user */
        $user = UserFactory::create($row);

        if ($setPassword) {
            $user->setPassword(Password::createFromHashedPassword($row['password']));
        }

        if ($setUsedTokens) {
            $user->setUsedTokens(new Json($row['usedTokens']));
        }
        return $user;
    }

    /**
     * @param string $phone
     *
     * @return Admin|Customer|Manager|Provider|null
     * @throws InvalidArgumentException
     * @throws QueryExecutionException
     */
    public function getByPhone($phone)
    {
        try {
            $statement = $this->connection->prepare($this->selectQuery() . ' WHERE phone = :phone');

            $statement->execute(
                array(
                    ':phone' => $phone
                )
            );

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

        if (!$row) {
            return null;
        }

        /** @var Admin|Customer|Manager|Provider $user */
        $user = UserFactory::create($row);

        return $user;
    }

    /**
     * @return array
     * @throws QueryExecutionException
     */
    public function getAllEmailsByType($type)
    {
        try {
            $params[':type'] = $type;
            $statement       = $this->connection->prepare(
                "
                SELECT DISTINCT 
                    u.email AS email
                FROM {$this->table} u
                WHERE u.type = :type
                "
            );
            $statement->execute($params);
            $rows = $statement->fetchAll(Statement::FETCH_COLUMN);
        } catch (\Exception $e) {
            throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
        }

        return $rows;
    }

    /**
     * @param string   $email
     * @param array $data
     *
     * @return bool
     * @throws QueryExecutionException
     */
    public function updateFieldsByEmail($email, $data)
    {
        $fields = [];

        $params = [':email' => $email];

        foreach ($data as $key => $item) {
            $params[":$key"] = $item;
            $fields[]        = "`$key` = :$key";
        }

        $fields = implode(', ', $fields);

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

            $statement->execute($params);

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