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

declare(strict_types=1);

namespace BookiFlex\Extensions\Payment\Repository;

use BookiFlex\Core\ORM\Schema\CustomerSchema;
use BookiFlex\Extensions\Payment\ORM\Schema\PaymentSchema;
use Cycle\Database\Injection\Fragment;
use Cycle\ORM\EntityManagerInterface;
use Cycle\ORM\Select\Repository;

defined( 'ABSPATH' ) || exit;

/**
 * Payment Repository
 *
 * Provides data access methods for Payment entities.
 * Uses Cycle ORM for simple queries and query builder for complex operations.
 *
 * @package BookiFlex\Extensions\Payment\Repository
 */
class PaymentRepository extends Repository
{
	private EntityManagerInterface $entityManager;

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

    /**
     * Find payments with pagination and filtering.
     *
     * Returns Payment entities with client relation loaded.
     * Uses query builder for complex queries (with filters), ORM for simple queries.
     *
     * @param PaymentQueryFilter $filter Filter parameters
     * @return array Array of Payment entities
     */
    public function findPaginated(PaymentQueryFilter $filter): array
    {
        // Use query builder if filters are present OR sorting by customer (requires JOIN)
        if ($filter->hasFilters() || $filter->orderBy === 'customer') {
            return $this->findPaginatedWithQueryBuilder($filter);
        }

        // Use ORM for simple queries (no filters, no customer sorting)
        $select = $this->select()->load('client');

        // Apply sorting
        $this->applySorting($select, $filter);

        // Apply pagination
        if ($filter->limit > 0) {
            $select->limit($filter->limit)->offset($filter->offset);
        }

        return $select->fetchAll();
    }

    /**
     * Find payments using query builder (for complex queries with JOINs).
     *
     * @param PaymentQueryFilter $filter Filter parameters
     * @return array Array of Payment entities
     */
    private function findPaginatedWithQueryBuilder(PaymentQueryFilter $filter): array
    {
        // Build query with JOINs
        $query = $this->select()->buildQuery()
            ->from(PaymentSchema::TABLE_NAME . ' AS p')
            ->leftJoin(CustomerSchema::TABLE_NAME, 'c')
            ->on('p.client_id', 'c.id')
            ->columns(['p.id']);

        // Apply WHERE conditions
        $this->applyQueryFilters($query, $filter);

        // Apply sorting
        $query->orderBy($this->mapOrderByField($filter->orderBy), $filter->order);

        // Apply pagination
        if ($filter->limit > 0) {
            $query->limit($filter->limit)->offset($filter->offset);
        }

        // Execute query to get IDs
        $results = $query->run()->fetchAll();
        $paymentIds = array_column($results, 'id');

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

        // Load entities with relations (preserves order)
        return $this->select()
            ->where('id', 'in', $paymentIds)
            ->load('client')
            ->fetchAll();
    }

    /**
     * Count payments matching filter (ignoring pagination).
     *
     * @param PaymentQueryFilter $filter Filter parameters
     * @return int Total count
     */
    public function countFiltered(PaymentQueryFilter $filter): int
    {
        // Use query builder if filters are present
        if ($filter->hasFilters()) {
            $query = $this->select()->buildQuery()
                ->from(PaymentSchema::TABLE_NAME . ' AS p')
                ->leftJoin(CustomerSchema::TABLE_NAME, 'c')
                ->on('p.client_id', 'c.id');

            // Apply WHERE conditions
            $this->applyQueryFilters($query, $filter);

            // Count distinct payments
            $result = $query->columns(new Fragment('COUNT(DISTINCT p.id) as count'))
                ->run()
                ->fetch();

            return (int) ($result['count'] ?? 0);
        }

        // Use ORM for simple count
        return $this->select()->count();
    }

    /**
     * Get payment counts grouped by status.
     *
     * Excludes status filter to count across all statuses.
     * Applies other filters (customerId, searchTerm).
     *
     * @param PaymentQueryFilter $filter Filter parameters (status will be ignored)
     * @return array Associative array: ['status' => count]
     */
    public function getStatusCounts(PaymentQueryFilter $filter): array
    {
        $query = $this->select()->buildQuery()
            ->from(PaymentSchema::TABLE_NAME . ' AS p')
            ->leftJoin(CustomerSchema::TABLE_NAME, 'c')
            ->on('p.client_id', 'c.id')
            ->columns([
                'p.status',
                new Fragment('COUNT(DISTINCT p.id) as count')
            ])
            ->groupBy('p.status');

        // Apply non-status filters
        if ($filter->customerId !== null) {
            $query->where('p.client_id', '=', $filter->customerId);
        }

        if ($filter->searchTerm !== null) {
            $escapedTerm = '%' . $filter->searchTerm . '%';
            $query->where(function ($q) use ($escapedTerm) {
                $q->orWhere('c.first_name', 'LIKE', $escapedTerm)
                    ->orWhere('c.last_name', 'LIKE', $escapedTerm)
                    ->orWhere('c.email', 'LIKE', $escapedTerm);
            });
        }

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

        // Convert to associative array
        $counts = [];
        foreach ($results as $row) {
            $counts[$row['status']] = (int) $row['count'];
        }

        return $counts;
    }

    /**
     * Delete payments by IDs.
     *
     * Uses ORM to preserve model lifecycle and cascade deletions.
     *
     * @param array $paymentIds Array of payment IDs to delete
     * @return int Number of deleted payments
     */
    public function deleteByIds(array $paymentIds): int
    {
        if (empty($paymentIds)) {
            return 0;
        }

        $deleted = 0;
        $payments = $this->select()->where('id', 'in', $paymentIds)->fetchAll();

        foreach ($payments as $payment) {
			$this->entityManager->delete($payment);
            $deleted++;
        }

		$this->entityManager->run();

        return $deleted;
    }

    /**
     * Find payments by IDs with all relations loaded.
     *
     * Used for audit logging before deletion.
     *
     * @param array $paymentIds Array of payment IDs
     * @return array Array of Payment entities with relations
     */
    public function findByIdsWithRelations(array $paymentIds): array
    {
        if (empty($paymentIds)) {
            return [];
        }

        return $this->select()
            ->where('id', 'in', $paymentIds)
            ->load('client')
            ->load('paymentAllocations.reservation')
            ->fetchAll();
    }

    /**
     * Apply WHERE conditions to query based on filter.
     *
     * @param mixed $query Query builder instance
     * @param PaymentQueryFilter $filter Filter parameters
     * @return void
     */
    private function applyQueryFilters($query, PaymentQueryFilter $filter): void
    {
        // Status filter
        if ($filter->status !== null) {
            $query->where('p.status', '=', $filter->status);
        }

        // Customer ID filter
        if ($filter->customerId !== null) {
            $query->where('p.client_id', '=', $filter->customerId);
        }

        // Search term filter (customer name or email)
        if ($filter->searchTerm !== null) {
            $escapedTerm = '%' . $filter->searchTerm . '%';
            $query->where(function ($q) use ($escapedTerm) {
                $q->orWhere('c.first_name', 'LIKE', $escapedTerm)
                    ->orWhere('c.last_name', 'LIKE', $escapedTerm)
                    ->orWhere('c.email', 'LIKE', $escapedTerm);
            });
        }
    }

    /**
     * Apply sorting to ORM select.
     *
     * Maps filter orderBy fields to actual entity property names (camelCase).
     *
     * @param mixed $select ORM select instance
     * @param PaymentQueryFilter $filter Filter parameters
     * @return void
     */
    private function applySorting(mixed $select, PaymentQueryFilter $filter): void
    {
        // Map orderBy field to entity property name (snake_case to camelCase)
        $orderByField = match ($filter->orderBy) {
            'total_amount' => 'totalAmount',
            'gateway_name' => 'gatewayName',
            'created_at' => 'createdAt',
            'updated_at' => 'updatedAt',
            default => $filter->orderBy, // id, status already match entity properties
        };

        $select->orderBy($orderByField, $filter->order);
    }

    /**
     * Map orderBy field to database column name for query builder.
     *
     * Used for query builder queries with table aliases (p. prefix for payment table).
     *
     * @param string $orderBy Field name from filter
     * @return string Database column name with table alias
     */
    private function mapOrderByField(string $orderBy): string
    {
        return match ($orderBy) {
            'customer' => 'c.first_name',
            'total_amount' => 'p.total_amount',
            'gateway_name' => 'p.gateway_name',
            'created_at' => 'p.created_at',
            'updated_at' => 'p.updated_at',
            default => 'p.' . $orderBy, // id, status map directly with p. prefix
        };
    }
}
