<?php
/**
 * BookiFlex Core
 *
 * @package    BookiFlex
 * @subpackage Core
 * @author     BookiFlex
 * @copyright  Copyright (c) 2025 BookiFlex
 * @license    GPL-2.0-or-later
 *
 * @link       https://bookiflex.com
 */

declare(strict_types=1);

namespace BookiFlex\Core\Repository;

use BookiFlex\Core\Contract\Reservation\ReservationRepositoryInterface;
use BookiFlex\Core\Model\Reservation\Reservation;
use BookiFlex\Core\Model\Reservation\ReservationStatus;
use BookiFlex\Core\ORM\Schema\CustomerSchema;
use BookiFlex\Core\ORM\Schema\ReservationAccommodationSchema;
use BookiFlex\Core\ORM\Schema\ReservationSchema;
use BookiFlex\Core\Util\DateTimeUtils;
use Cycle\Database\Injection\Fragment;
use Cycle\Database\Injection\Parameter;
use Cycle\Database\Query\SelectQuery;
use Cycle\ORM\EntityManagerInterface;
use Cycle\ORM\Select;
use Cycle\ORM\Select\Repository;

defined( 'ABSPATH' ) || exit;

class ReservationRepository extends Repository implements ReservationRepositoryInterface
{
	private EntityManagerInterface $entityManager;

	public function setEntityManager(EntityManagerInterface $em): void
	{
		$this->entityManager = $em;
	}

    /**
     * Find reservations with filters, sorting, and pagination.
     *
     * Returns hydrated Reservation objects with eagerly loaded relations:
     * - customer
     * - paymentType
     * - accommodationItems
     *
     * Uses two-step approach to avoid LIMIT issues with HAS_MANY relations:
     * 1. Find reservation IDs with pagination (no eager loading JOINs)
     * 2. Load entities by IDs with eager loading (no LIMIT)
     *
     * @param ReservationQueryFilter $filter Query filters and pagination
     * @return Reservation[] Array of Reservation entities
     */
    public function findPaginated(ReservationQueryFilter $filter): array
    {
        // Step 1: Find IDs with proper pagination (no eager loading JOINs)
        // This prevents LIMIT from counting duplicated rows from HAS_MANY JOINs
        if ($filter->searchTerm !== null) {
            // For search queries, use Query Builder with customer JOIN
            $reservationIds = $this->findReservationIdsBySearchQuery($filter);
        } else {
            // For simple queries, use ORM without eager loading
            $reservationIds = $this->findReservationIdsSimple($filter);
        }

        if (empty($reservationIds)) {
            return [];
        }

        // Step 2: Load entities by IDs with eager loading (no LIMIT)
        // Note: ->with('group') is not used due to Cycle ORM issue with NULLABLE relations
        return $this->loadReservationsByIds($reservationIds, $filter);
    }

    /**
     * Count total reservations matching filters.
     *
     * @param ReservationQueryFilter $filter Query filters (pagination ignored)
     * @return int Total count of matching reservations
     */
    public function countFiltered(ReservationQueryFilter $filter): int
    {
        // If search term is present, use query builder
        if ($filter->searchTerm !== null) {
            $query = $this->buildSearchQuery($filter)
                ->columns([new Fragment('COUNT(DISTINCT r.id) as total')]);

            $result = $query->run()->fetch();
            return (int) ($result['total'] ?? 0);
        }

        // For simple queries, use existing ORM approach
        $select = $this->select();
        $this->applyFilters($select, $filter);

        return $select->count();
    }

    /**
     * Get status distribution for matching reservations.
     *
     * Returns count of reservations grouped by status.
     * Status filter is typically excluded to get counts across all statuses.
     *
     * @param ReservationQueryFilter $filter Query filters (status typically null)
     * @return array<string, int> Map of status => count
     */
    public function getStatusCounts(ReservationQueryFilter $filter): array
    {
        // If search term is present, use query builder
        if ($filter->searchTerm !== null) {
            $query = $this->buildSearchQuery($filter)
                ->columns(['r.status', new Fragment('COUNT(DISTINCT r.id) as count')])
                ->where('r.status', '!=', new Parameter(null))
                ->groupBy('r.status');

            $results = $query->run()->fetchAll();

            $counts = [];
            foreach ($results as $row) {
                $counts[$row['status']] = (int) $row['count'];
            }

            return $counts;
        }

        // For simple queries, use existing PHP-based counting
        $select = $this->select();
        $this->applyFilters($select, $filter);

        /** @var Reservation[] $reservations */
        $reservations = $select->fetchAll();

        // Group by status and count in PHP
        $counts = [];
        foreach ($reservations as $reservation) {
            $status = (string) $reservation->getStatus();
            $counts[$status] = ($counts[$status] ?? 0) + 1;
        }

        return $counts;
    }

    /**
     * Find reservations by IDs with full relations loaded.
     *
     * Used for deletion audit trail - loads all related data before deletion.
     *
     * @param int[] $reservationIds Array of reservation IDs
     * @return Reservation[] Array of Reservation entities
     */
    public function findByIdsWithRelations(array $reservationIds): array
    {
        return $this->loadReservationsByIds($reservationIds, new ReservationQueryFilter());
    }

    /**
     * Delete reservations by IDs.
     *
     * @param int[] $reservationIds Array of reservation IDs to delete
     * @return int Number of deleted reservations
     */
    public function deleteByIds(array $reservationIds): int
    {
        if (empty($reservationIds)) {
            return 0;
        }

        $deleted = 0;

        foreach ($reservationIds as $id) {
            $reservation = $this->findByPK($id);
            if ($reservation) {
				$this->entityManager->delete($reservation);
                $deleted++;
            }
        }

		$this->entityManager->run();

        return $deleted;
    }

    /**
     * Find reservation IDs using search query with customer JOIN.
     *
     * Used for complex queries with search term that require JOINs.
     * Uses Query Builder to find IDs, then entities are loaded separately.
     *
     * @param ReservationQueryFilter $filter Query filters
     * @return int[] Array of reservation IDs
     */
    private function findReservationIdsBySearchQuery(ReservationQueryFilter $filter): array
    {
        $query = $this->buildSearchQuery($filter)
            ->columns(['r.id'])
            ->groupBy('r.id');

        // Apply sorting (inline because applySorting() expects Select object)
        $orderByMap = [
            'created_at' => 'r.created_at',
            'updated_at' => 'r.updated_at',
            'status' => 'r.status',
            'check_in_date' => 'r.check_in_date',
            'check_out_date' => 'r.check_out_date',
            'total_amount' => 'r.total_amount',
            'number' => 'r.number',
        ];

        $field = $orderByMap[$filter->orderBy] ?? 'r.created_at';
        $direction = $filter->order === 'ASC' ? 'ASC' : 'DESC';

        $query->orderBy($field, $direction);

        if ($filter->limit > 0) {
            $query->limit($filter->limit);
        }

        if ($filter->offset > 0) {
            $query->offset($filter->offset);
        }

        $results = $query->run()->fetchAll();

        return array_map(fn($row) => (int) $row['id'], $results);
    }

    /**
     * Find reservation IDs using simple ORM query (no eager loading).
     *
     * Used for queries without search term. Gets IDs first to avoid
     * LIMIT issues when loading entities with HAS_MANY relations.
     *
     * @param ReservationQueryFilter $filter Query filters
     * @return int[] Array of reservation IDs
     */
    private function findReservationIdsSimple(ReservationQueryFilter $filter): array
    {
        $select = $this->select();

        $this->applyFilters($select, $filter);
        $this->applySorting($select, $filter);

        if ($filter->limit > 0) {
            $select->limit($filter->limit);
        }

        if ($filter->offset > 0) {
            $select->offset($filter->offset);
        }

        /** @var Reservation[] $reservations */
        $reservations = $select->fetchAll();

        return array_map(fn(Reservation $r) => $r->getId(), $reservations);
    }

    /**
     * Load reservations by IDs with eager loading.
     *
     * @param int[] $reservationIds Array of reservation IDs
     * @return Reservation[] Array of Reservation entities
     */
    private function loadReservationsByIds(array $reservationIds, ReservationQueryFilter $filter): array
    {
        if (empty($reservationIds)) {
            return [];
        }

		$select = $this->select();

	    $this->applySorting($select, $filter);

        // Note: ->with('group') is not used due to Cycle ORM issue with NULLABLE relations
        return $select
            ->where('id', 'in', $reservationIds)
            ->with('customer')
            ->with('paymentType')
            ->with('accommodationItems')
            ->fetchAll();
    }

    /**
     * Build query with JOIN for complex search queries.
     *
     * Creates query with reservations table as base and JOIN with customers.
     * Applies all filters including search term with OR conditions.
     *
     * @param ReservationQueryFilter $filter Query filters
     * @return SelectQuery Query builder instance
     */
    private function buildSearchQuery(ReservationQueryFilter $filter): SelectQuery
    {
        // Start with query builder and add JOIN with customers
        $query = $this->select()
            ->buildQuery()
            ->from(ReservationSchema::TABLE_NAME . ' AS r')
            ->leftJoin(CustomerSchema::TABLE_NAME, 'c')->on('c.id', 'r.customer_id');

        // Apply statuses filter
        if ($filter->statuses !== null) {
            $query->where('r.status', 'in', new Parameter($filter->statuses));
        }

        // Apply customer ID filter
        if ($filter->customerId !== null) {
            $query->where('r.customer_id', new Parameter($filter->customerId));
        }

        // Apply group ID filter
        if ($filter->groupId !== null) {
            $query->where('r.group_id', new Parameter($filter->groupId));
        }

        // Apply reservation number filter
        if ($filter->reservationNumber !== null) {
            $term = '%' . $filter->reservationNumber . '%';
            $query->where('r.number', 'like', new Parameter($term));
        }

        // Apply search filter across multiple fields with OR logic
        if ($filter->searchTerm !== null) {
            $term = '%' . $filter->searchTerm . '%';
            $query->where(function ($select) use ($term) {
                $select
                    ->where('r.number', 'like', new Parameter($term))
                    ->orWhere('c.first_name', 'like', new Parameter($term))
                    ->orWhere('c.last_name', 'like', new Parameter($term))
                    ->orWhere('c.email', 'like', new Parameter($term));
            });
        }

        return $query;
    }

    /**
     * Apply filters to query selector.
     *
     * @param Select $select Cycle ORM selector
     * @param ReservationQueryFilter $filter Query filters
     */
    private function applyFilters(Select $select, ReservationQueryFilter $filter): void
    {
        // Filter by statuses
        if ($filter->statuses !== null) {
            $select->where('status', 'in', new Parameter($filter->statuses));
        }

        // Filter by customer ID
        if ($filter->customerId !== null) {
            $select->where('customer_id', $filter->customerId);
        }

        // Filter by group ID
        if ($filter->groupId !== null) {
            $select->where('group_id', $filter->groupId);
        }

        // Filter by reservation number
        if ($filter->reservationNumber !== null) {
            $select->where('number', 'like', '%' . $filter->reservationNumber . '%');
        }

        // Note: searchTerm is not applied here because it's handled
        // in findPaginated() via the two-path approach using Query Builder
    }

    /**
     * Apply sorting to query selector.
     *
     * Maps filter orderBy fields to actual entity fields.
     *
     * @param Select $select Cycle ORM selector
     * @param ReservationQueryFilter $filter Query filters
     */
    private function applySorting(Select $select, ReservationQueryFilter $filter): void
    {
        // Map orderBy to actual field names
        $orderByMap = [
            'created_at' => 'created_at',
            'updated_at' => 'updated_at',
            'status' => 'status',
            'check_in_date' => 'check_in_date',
            'check_out_date' => 'check_out_date',
            'total_amount' => 'total_amount',
            'number' => 'number',
        ];

        $field = $orderByMap[$filter->orderBy] ?? 'created_at';
        $direction = $filter->order === 'ASC' ? 'ASC' : 'DESC';

        $select->orderBy($field, $direction);
    }

    /**
     * Find reservations overlapping with a date range.
     *
     * Returns reservations where check-in < dateTo AND check-out > dateFrom,
     * filtered to active statuses (confirmed, in-house, waiting payment/confirmation).
     *
     * @param \DateTimeImmutable $from Start of the range
     * @param \DateTimeImmutable $to End of the range
     * @return Reservation[]
     */
    public function findByDateRange(\DateTimeImmutable $from, \DateTimeImmutable $to): array
    {
        $select = $this->select()
            ->where('check_in_date', '<', $to->format('Y-m-d'))
            ->andWhere('check_out_date', '>', $from->format('Y-m-d'))
            ->where('status', 'IN', [
                ReservationStatus::STATUS_CONFIRMED,
                ReservationStatus::STATUS_IN_HOUSE,
                ReservationStatus::STATUS_WAITING_PAYMENT,
                ReservationStatus::STATUS_WAITING_CONFIRMATION,
            ])
            ->with('customer')
            ->with('paymentType')
            ->with('accommodationItems')
            ->orderBy('check_in_date', 'ASC');

        return $select->fetchAll();
    }

    /**
     * Find all reservations overlapping with a date range (all statuses except CART).
     *
     * @param \DateTimeImmutable $from Start of the range
     * @param \DateTimeImmutable $to End of the range
     * @return Reservation[]
     */
    public function findAllByDateRange(\DateTimeImmutable $from, \DateTimeImmutable $to): array
    {
        return $this->select()
            ->where('check_in_date', '<', $to->format('Y-m-d'))
            ->andWhere('check_out_date', '>', $from->format('Y-m-d'))
            ->where('status', '!=', ReservationStatus::STATUS_CART)
            ->with('customer')
            ->with('paymentType')
            ->with('accommodationItems')
            ->orderBy('check_in_date', 'ASC')
            ->fetchAll();
    }

    public function getOccupancyOnDates(\DateTimeImmutable $checkIn, \DateTimeImmutable $checkOut): array
    {
        $select = $this->select()
            ->buildQuery()
            ->from( ReservationSchema::TABLE_NAME . ' AS r')
            ->columns([
                'rai.accommodation_type_code AS accommodationTypeCode',
                'COUNT(r.id) AS bookedCount'
            ])
            ->leftJoin( ReservationAccommodationSchema::TABLE_NAME, 'rai')->on('rai.reservation_id', 'r.id')
            ->where('r.check_in_date', '<', new Parameter($checkOut->format('Y-m-d')))
            ->andWhere('r.check_out_date', '>', new Parameter($checkIn->format('Y-m-d')))
            ->where(static function (SelectQuery $query) {
                $query
	                ->where('r.status', 'IN', [
						ReservationStatus::STATUS_CONFIRMED,
		                ReservationStatus::STATUS_IN_HOUSE,
		                ReservationStatus::STATUS_WAITING_PAYMENT,
		                ReservationStatus::STATUS_WAITING_CONFIRMATION
	                ]);
//  Block on waiting time only
//                    ->where('r.status', 'IN', [ReservationStatus::STATUS_CONFIRMED, ReservationStatus::STATUS_IN_HOUSE])
//                    ->orWhere(static function (SelectQuery $query) {
//                        $query->where('r.status', 'in', [ReservationStatus::STATUS_WAITING_PAYMENT, ReservationStatus::STATUS_WAITING_CONFIRMATION])
//                            ->andWhere(static function (SelectQuery $query) {
//                                $now = DateTimeUtils::nowUtc();
//                                $query
//                                    ->where('r.waiting_payment_until', 'IS', null)
//                                    ->orWhere('r.waiting_payment_until', '>=', new Parameter($now->format('Y-m-d H:i:s')));
//                            });
//                    });
            })
            ->groupBy('rai.accommodation_type_code');

        return $select->fetchAll();
    }
}
