<?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\Model\Customer\Customer;
use BookiFlex\Core\ORM\Schema\CustomerSchema;
use BookiFlex\Core\ORM\Schema\ReservationSchema;
use Cycle\Database\Injection\Fragment;
use Cycle\Database\Injection\Parameter;
use Cycle\ORM\EntityManagerInterface;
use Cycle\ORM\Select\Repository;

defined( 'ABSPATH' ) || exit;

class CustomerRepository extends Repository
{
	private EntityManagerInterface $entityManager;

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

    public function findByEmail(string $email): ?Customer
    {
        return $this->select()->where('email', $email)->fetchOne();
    }

    /**
     * Find customers with filters, sorting, and pagination.
     *
     * Returns hydrated Customer objects. For customers with reservation status filter,
     * performs JOIN with reservations table.
     *
     * @param CustomerQueryFilter $filter Query filters and pagination
     * @return Customer[] Array of Customer entities
     */
    public function findPaginated(CustomerQueryFilter $filter): array
    {
        // For complex queries with reservation status filter, we need to use query builder
        // to find matching customer IDs first, then load Customer entities
        if ($filter->status !== null) {
            $customerIds = $this->findCustomerIdsByFilter($filter);

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

            // Load Customer entities by IDs and apply sorting
            $select = $this->select()->where('id', 'in', $customerIds);
            $this->applySorting($select, $filter);

            return $select->fetchAll();
        }

        // For simple queries without status filter, use ORM directly
        $select = $this->select();
        $this->applySimpleFilters($select, $filter);
        $this->applySorting($select, $filter);

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

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

        return $select->fetchAll();
    }

    /**
     * Count total customers matching filters.
     *
     * @param CustomerQueryFilter $filter Query filters (pagination ignored)
     * @return int Total count of matching customers
     */
    public function countFiltered(CustomerQueryFilter $filter): int
    {
        if ($filter->status !== null) {
            // Use query builder for complex query with status filter
            $query = $this->buildBaseQuery($filter)
                ->columns([new Fragment('COUNT(DISTINCT c.id) as total')]);

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

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

        return $select->count();
    }

    /**
     * Get status distribution for matching customers.
     *
     * Returns count of customers grouped by reservation status.
     * Status filter is typically excluded to get counts across all statuses.
     *
     * @param CustomerQueryFilter $filter Query filters (status typically null)
     * @return array<string, int> Map of status => count
     */
    public function getStatusCounts(CustomerQueryFilter $filter): array
    {
        $query = $this->buildBaseQuery($filter)
            ->columns(['r.status', new Fragment('COUNT(DISTINCT c.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;
    }

    /**
     * Find customers by IDs with full data.
     *
     * Used for deletion audit trail.
     *
     * @param int[] $customerIds Array of customer IDs
     * @return Customer[] Array of Customer entities
     */
    public function findByIdsWithRelations(array $customerIds): array
    {
        if (empty($customerIds)) {
            return [];
        }

        return $this->select()
            ->where('id', 'in', $customerIds)
            ->fetchAll();
    }

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

        $deleted = 0;

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

		$this->entityManager->run();

        return $deleted;
    }

    /**
     * Get aggregated data for customers (reservations and payments count).
     *
     * Returns array with customer_id as key and counts as value.
     *
     * @param int[] $customerIds Array of customer IDs
     * @return array<int, array{reservations: int, payments: int}>
     */
    public function getCustomerCounts(array $customerIds): array
    {
        if (empty($customerIds)) {
            return [];
        }

        $query = $this->select()
            ->buildQuery()
            ->from(CustomerSchema::TABLE_NAME . ' AS c')
            ->columns([
                'c.id as customer_id',
                new Fragment('COUNT(DISTINCT r.id) as reservations'),
                new Fragment('COUNT(DISTINCT p.id) as payments')
            ])
            ->leftJoin(ReservationSchema::TABLE_NAME, 'r')->on('r.customer_id', 'c.id')
            ->leftJoin('bflex_payments', 'p')->on('p.client_id', 'c.id')
            ->where('c.id', 'in', new Parameter($customerIds))
            ->groupBy('c.id');

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

        $counts = [];
        foreach ($results as $row) {
            $counts[(int) $row['customer_id']] = [
                'reservations' => (int) $row['reservations'],
                'payments' => (int) $row['payments'],
            ];
        }

        return $counts;
    }

    /**
     * Find customer IDs matching filter criteria.
     *
     * Used for complex queries with status filter that require JOINs.
     *
     * @param CustomerQueryFilter $filter Query filters
     * @return int[] Array of customer IDs
     */
    private function findCustomerIdsByFilter(CustomerQueryFilter $filter): array
    {
        $query = $this->buildBaseQuery($filter)
            ->columns(['c.id'])
            ->groupBy('c.id');

        // Apply sorting on aggregated columns
        $this->applyQuerySorting($query, $filter);

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

    /**
     * Build base query with common filters.
     *
     * Creates query with customers table as base and optional JOIN with reservations.
     *
     * @param CustomerQueryFilter $filter Query filters
     * @return \Cycle\Database\Query\SelectQuery
     */
    private function buildBaseQuery(CustomerQueryFilter $filter)
    {
        $query = $this->select()
            ->buildQuery()
            ->from(CustomerSchema::TABLE_NAME . ' AS c')
            ->leftJoin(ReservationSchema::TABLE_NAME, 'r')->on('r.customer_id', 'c.id');

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

        if ($filter->searchTerm !== null) {
            $term = '%' . $filter->searchTerm . '%';
            $query->where(function ($select) use ($term) {
                $select
                    ->where('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 simple filters to ORM selector (without status filter).
     *
     * @param \Cycle\ORM\Select $select ORM selector
     * @param CustomerQueryFilter $filter Query filters
     */
    private function applySimpleFilters($select, CustomerQueryFilter $filter): void
    {
        if ($filter->searchTerm !== null) {
            $term = '%' . $filter->searchTerm . '%';
            $select->where(function ($query) use ($term) {
                $query
                    ->where('first_name', 'like', $term)
                    ->orWhere('last_name', 'like', $term)
                    ->orWhere('email', 'like', $term);
            });
        }
    }

    /**
     * Apply sorting to ORM selector.
     *
     * @param \Cycle\ORM\Select $select ORM selector
     * @param CustomerQueryFilter $filter Query filters
     */
    private function applySorting($select, CustomerQueryFilter $filter): void
    {
        // Map orderBy to actual field names (for non-aggregated fields)
        $orderByMap = [
            'id' => 'id',
            'first_name' => 'first_name',
            'email' => 'email',
            'phone' => 'phone',
            'created_at' => 'created_at',
            'updated_at' => 'updated_at',
        ];

        // Only apply sorting for fields that exist in Customer entity
        if (isset($orderByMap[$filter->orderBy])) {
            $field = $orderByMap[$filter->orderBy];
            $direction = $filter->order === 'ASC' ? 'ASC' : 'DESC';
            $select->orderBy($field, $direction);
        }
    }

    /**
     * Apply sorting to query builder query.
     *
     * Handles sorting on both regular and aggregated columns.
     *
     * @param \Cycle\Database\Query\SelectQuery $query Query builder query
     * @param CustomerQueryFilter $filter Query filters
     */
    private function applyQuerySorting($query, CustomerQueryFilter $filter): void
    {
        // Map orderBy to actual field/expression names
        $orderByMap = [
            'id' => 'c.id',
            'first_name' => 'c.first_name',
            'email' => 'c.email',
            'phone' => 'c.phone',
            'reservations' => 'COUNT(DISTINCT r.id)',
            'payments' => 'COUNT(DISTINCT p.id)',
            'created_at' => 'c.created_at',
            'updated_at' => 'c.updated_at',
        ];

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

        // For aggregated columns, we need to add them to SELECT first
        if (in_array($filter->orderBy, ['reservations', 'payments'])) {
            // These will be added in the calling method if needed
        }

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