<?php

namespace RadiusTheme\RadiusBooking\Repositories;

if ( ! defined( 'ABSPATH' ) ) {
	exit; // Exit if accessed directly
}

use RadiusTheme\RadiusBooking\Abstracts\BaseRepository;
use RadiusTheme\RadiusBooking\Models\Appointment;
use RadiusTheme\RadiusBooking\Models\CustomerBooking;
use RadiusTheme\RadiusBooking\Models\EmployeeService;
use RadiusTheme\RadiusBooking\Models\Payment;
use RadiusTheme\RadiusBooking\Models\User;

/**
 * Class EmployeeRepository
 *
 * Handles data operations for the Employee model.
 */
class EmployeeRepository extends BaseRepository {

	/**
	 * The model class managed by this repository.
	 *
	 * @var string
	 */
	protected string $model = User::class;

	// You can add custom repository methods here

	/**
	 * Finds and returns an EmployeeService record based on the provided user ID and service ID.
	 *
	 * @param int $userId The ID of the user.
	 * @param int $serviceId The ID of the service.
	 *
	 * @return EmployeeService|null The matching EmployeeService record, or null if no matching record is found.
	 */
	public function findByUserAndService( int $userId, int $serviceId ) {
		return EmployeeService::where( 'user_id', $userId )->where( 'service_id', $serviceId )->first();
	}

	/**
	 * Retrieves the price for a specific service provided by a given employee.
	 *
	 * @param int $employeeId The unique identifier of the employee.
	 * @param int $serviceId The unique identifier of the service.
	 *
	 * @return float|null The price of the service provided by the employee, or null if not found.
	 */
	public function getEmployeePrice( $employeeId, $serviceId ) {
		// Assuming you have a method to get the employee's price for a service
		$empService = $this->findByUserAndService( $employeeId, $serviceId );

		return $empService->price ?? null;
		// return EmployeeService::where( 'user_id', $userId )->where( 'service_id', $serviceId )->first();
	}

	public function appointmentCount( $employeeId, $status ) {
		$user = $this->find( $employeeId );
		if ( ! $user ) {
			return 0;
		}
		if ( $status !== '' ) {
			return Appointment::where( 'employee_id', $employeeId )->where( 'status', $status )->count();
		}

		return Appointment::where( 'employee_id', $employeeId )->count();

	}

	/**
	 * Get count of today's appointments for an employee.
	 *
	 * @param int $employee_id Employee ID.
	 * @param string $date Date in Y-m-d format.
	 *
	 * @return int Today's appointment count.
	 */
	public function getTodayAppointmentCount( $employee_id, $date ) {
		return Appointment::whereDate( 'appointment_date', '=', $date )
		                  ->where( 'employee_id', $employee_id )
		                  ->count();
	}

	/**
	 * Get today's appointments with full details.
	 *
	 * @param int $employee_id Employee ID.
	 * @param string $date Date in Y-m-d format.
	 *
	 * @return array Today's appointments with customer and service info.
	 */
	public function getTodayAppointments( $employee_id, $date ) {
		$prefix = radius_table_prefix();

		return Appointment::query()
		                  ->leftJoin(
			                  'radius_booking_customer_bookings',
			                  'radius_booking_customer_bookings.appointment_id',
			                  '=',
			                  'radius_booking_appointments.id'
		                  )
		                  ->leftJoin(
			                  'radius_booking_services',
			                  'radius_booking_services.id',
			                  '=',
			                  'radius_booking_appointments.service_id'
		                  )
		                  ->leftJoin(
			                  'radius_booking_meeting_types',
			                  'radius_booking_meeting_types.id',
			                  '=',
			                  'radius_booking_appointments.meeting_type_id'
		                  )
		                  ->leftJoin(
			                  'radius_booking_events',
			                  'radius_booking_events.id',
			                  '=',
			                  'radius_booking_appointments.event_id'
		                  )
		                  ->leftJoin(
			                  'radius_booking_event_registrations',
			                  'radius_booking_event_registrations.appointment_id',
			                  '=',
			                  'radius_booking_appointments.id'
		                  )
		                  ->whereDate( $prefix . 'appointments.appointment_date', '=', $date )
		                  ->where( $prefix . 'appointments.employee_id', $employee_id )
		                  ->select(
			                  $prefix . 'appointments.id as appointment_id',
			                  $prefix . 'appointments.status as appointment_status',
			                  $prefix . 'appointments.start_time',
			                  $prefix . 'appointments.end_time',
			                  $prefix . 'appointments.booking_type',
			                  $prefix . 'services.name as service_name',
			                  $prefix . 'services.duration',
			                  $prefix . 'meeting_types.name as meeting_type_name',
			                  $prefix . 'meeting_types.duration as meeting_duration',
			                  $prefix . 'meeting_types.price as meeting_price',
			                  $prefix . 'events.name as event_name',
			                  $prefix . 'event_registrations.registration_number',
		                  )
		                  ->orderBy( $prefix . 'appointments.start_time', 'ASC' )
		                  ->get();
	}

	/**
	 * Get count of completed appointments for an employee.
	 *
	 * @param int $employee_id Employee ID.
	 *
	 * @return int Completed appointment count.
	 */
	public function getCompletedAppointmentCount( $employee_id ) {
		return Appointment::where( 'employee_id', $employee_id )
		                  ->where( 'status', 'completed' )
		                  ->count();
	}

	/**
	 * Get count of pending appointments for an employee.
	 *
	 * @param int $employee_id Employee ID.
	 *
	 * @return int Pending appointment count.
	 */
	public function getPendingAppointmentCount( $employee_id ) {
		return Appointment::where( 'employee_id', $employee_id )
		                  ->where( 'status', 'pending' )
		                  ->count();
	}

	/**
	 * Get count of cancelled appointments for an employee.
	 *
	 * @param int $employee_id Employee ID.
	 *
	 * @return int Cancelled appointment count.
	 */
	public function getCancelledAppointmentCount( $employee_id ) {
		return Appointment::where( 'employee_id', $employee_id )
		                  ->where( 'status', 'cancelled' )
		                  ->count();
	}

	/**
	 * Get count of Confirmed appointments for an employee.
	 *
	 * @param int $employee_id Employee ID.
	 *
	 * @return int Cancelled appointment count.
	 */
	public function getConfirmedAppointmentCount( $employee_id ) {
		return Appointment::where( 'employee_id', $employee_id )
		                  ->where( 'status', 'confirmed' )
		                  ->count();
	}

	/**
	 * Get count of unique active clients for an employee.
	 *
	 * @param int $employee_id Employee ID.
	 *
	 * //     * @return int Active client count.
	 */
	public function getActiveClientCount( $employee_id ) {
		$prefix = radius_table_prefix();

		$customer = CustomerBooking::query()
		                           ->leftJoin(
			                           'radius_booking_appointments',
			                           'radius_booking_appointments.id',
			                           '=',
			                           'radius_booking_customer_bookings.appointment_id'
		                           )
		                           ->where( $prefix . 'appointments.employee_id', $employee_id )
		                           ->whereIn( $prefix . 'appointments.status', array(
			                           'pending',
			                           'confirmed',
			                           'completed'
		                           ) )
		                           ->distinct()
		                           ->groupBy( $prefix . 'customer_bookings.customer_id' )
		                           ->get();

		return count( $customer );

	}

	/**
	 * Get total earnings for an employee from completed appointments.
	 *
	 * @param int $employee_id Employee ID.
	 *
	 * @return float Total earnings amount.
	 */
	public function getTotalEarnings( $employee_id ) {
		$prefix = radius_table_prefix();

		$result = Payment::query()
		                 ->leftJoin(
			                 'radius_booking_customer_bookings',
			                 'radius_booking_customer_bookings.id',
			                 '=',
			                 'radius_booking_payments.booking_id'
		                 )
		                 ->leftJoin(
			                 'radius_booking_appointments',
			                 'radius_booking_appointments.id',
			                 '=',
			                 'radius_booking_customer_bookings.appointment_id'
		                 )
		                 ->where( $prefix . 'appointments.employee_id', $employee_id )
		                 ->whereIn( $prefix . 'payments.status', [ 'paid', 'partial_payment' ] )
		                 ->sum( $prefix . 'payments.amount' );

		return (float) $result;
	}

	/**
	 * Get paginated appointments for an employee.
	 *
	 * @param int $employee_id Employee ID.
	 * @param int $page Current page number.
	 * @param int $per_page Items per page.
	 *
	 * @return array Paginated appointments with metadata.
	 */
	public function getPaginatedAppointments( $employee_id, $per_page, $offset = 0, $search = '', $status = '' ) {
		$prefix = radius_table_prefix();
		$query  = Appointment::query()
		                     ->leftJoin(
			                     'radius_booking_customer_bookings',
			                     'radius_booking_customer_bookings.appointment_id',
			                     '=',
			                     'radius_booking_appointments.id'
		                     )
		                     ->leftJoin(
			                     'radius_booking_services',
			                     'radius_booking_services.id',
			                     '=',
			                     'radius_booking_appointments.service_id'
		                     )
		                     ->leftJoin(
			                     'radius_booking_users',
			                     'radius_booking_users.id',
			                     '=',
			                     'radius_booking_appointments.employee_id'
		                     )
		                     ->leftJoin(
			                     'radius_booking_user_profiles',
			                     'radius_booking_user_profiles.user_id',
			                     '=',
			                     'radius_booking_users.id'
		                     )
		                     ->leftJoin(
			                     'radius_booking_payments',
			                     'radius_booking_payments.booking_id',
			                     '=',
			                     'radius_booking_customer_bookings.id'
		                     )
		                     ->leftJoin(
			                     'radius_booking_meeting_types',
			                     'radius_booking_meeting_types.id',
			                     '=',
			                     'radius_booking_appointments.meeting_type_id'
		                     )
		                     ->leftJoin(
			                     'radius_booking_events',
			                     'radius_booking_events.id',
			                     '=',
			                     'radius_booking_appointments.event_id'
		                     )
		                     ->leftJoin(
			                     'radius_booking_event_registrations',
			                     'radius_booking_event_registrations.appointment_id',
			                     '=',
			                     'radius_booking_appointments.id'
		                     )
		                     ->where( $prefix . 'appointments.employee_id', $employee_id )
		                     ->select(
			                     $prefix . 'appointments.id as appointment_id',
			                     $prefix . 'appointments.status as appointment_status',
			                     $prefix . 'appointments.appointment_date',
			                     $prefix . 'appointments.start_time',
			                     $prefix . 'appointments.end_time',
			                     $prefix . 'appointments.service_id',
			                     $prefix . 'appointments.employee_id',
			                     $prefix . 'appointments.booking_type',
			                     $prefix . 'services.name as service_name',
			                     $prefix . 'services.duration as service_duration',
			                     $prefix . 'services.price as service_price',
			                     $prefix . 'meeting_types.name as meeting_type_name',
			                     $prefix . 'meeting_types.duration as meeting_duration',
			                     $prefix . 'meeting_types.price as meeting_price',
			                     $prefix . 'events.name as event_name',
			                     $prefix . 'event_registrations.registration_number',
			                     $prefix . 'user_profiles.first_name as employee_first_name',
			                     $prefix . 'user_profiles.last_name as employee_last_name',
			                     $prefix . 'users.user_type as user_type',
			                     $prefix . 'payments.id as payment_id',
			                     $prefix . 'payments.transaction_id',
			                     $prefix . 'payments.payment_method',
			                     $prefix . 'payments.amount as paid_amount',
			                     $prefix . 'payments.total_amount',
			                     $prefix . 'payments.due_amount',
			                     $prefix . 'payments.status as payment_status',
			                     $prefix . 'payments.paid_at',
			                     $prefix . 'payments.coupon_code',
			                     $prefix . 'payments.discount_amount',
			                     $prefix . 'payments.tax_rate',
			                     $prefix . 'payments.tax_amount',
			                     $prefix . 'customer_bookings.*'
		                     )
		                     ->orderByRaw(
			                     $prefix . "appointments.appointment_date >= CURDATE() DESC"
		                     )
		                     ->orderByRaw(
			                     'ABS(DATEDIFF(' . $prefix . 'appointments.appointment_date, CURDATE())) ASC'
		                     )
		                     ->orderBy( $prefix . 'appointments.start_time', 'ASC' );

		if ( $status != '' ) {
			$query->where( $prefix . 'appointments.status', $status );
		}

		if ( '' !== $search ) {
			$search_term = '%' . $search . '%';
			$query->where(
				function ( $q ) use ( $prefix, $search_term ) {
					$q->where( $prefix . 'services.name', 'LIKE', $search_term )
					  ->orWhere( $prefix . 'meeting_types.name', 'LIKE', $search_term )
					  ->orWhere( $prefix . 'events.name', 'LIKE', $search_term )
					  ->orWhere( $prefix . 'payments.payment_method', 'LIKE', $search_term )
					  ->orWhere( $prefix . 'user_profiles.first_name', 'LIKE', $search_term )
					  ->orWhere( $prefix . 'user_profiles.last_name', 'LIKE', $search_term )
					  ->orWhere( $prefix . 'users.email', 'LIKE', $search_term );
				}
			);
		}


		$result = $query->limit( $per_page )
		                ->offset( $offset )
		                ->get();

		return $result;
	}

	/**
	 * Get paginated client list for an employee with booking count.
	 *
	 * @param int $employee_id Employee ID.
	 * @param int $per_page Items per page.
	 * @param int $offset Offset for pagination.
	 *
	 * @return array Paginated clients with total booking count.
	 */
	public function getPaginatedClients( $employee_id, $per_page, $offset = 0 ) {
		$prefix  = radius_table_prefix();
		$clients = Appointment::query()
		                      ->leftJoin(
			                      'radius_booking_customer_bookings',
			                      'radius_booking_customer_bookings.appointment_id',
			                      '=',
			                      'radius_booking_appointments.id'
		                      )
		                      ->leftJoin(
			                      'radius_booking_payments',
			                      'radius_booking_payments.booking_id',
			                      '=',
			                      'radius_booking_customer_bookings.id'
		                      )
		                      ->leftJoin(
			                      'radius_booking_users',
			                      'radius_booking_users.id',
			                      '=',
			                      'radius_booking_customer_bookings.customer_id'
		                      )
		                      ->leftJoin(
			                      'radius_booking_user_profiles',
			                      'radius_booking_user_profiles.user_id',
			                      '=',
			                      'radius_booking_users.id'
		                      )
		                      ->where( $prefix . 'appointments.employee_id', $employee_id )
		                      ->selectRaw(
			                      $prefix . 'users.email, ' .
			                      $prefix . 'user_profiles.first_name, ' .
			                      $prefix . 'user_profiles.last_name, ' .
			                      $prefix . 'customer_bookings.customer_id,' .
			                      'COUNT(' . $prefix . 'appointments.id) as total_bookings,' .
			                      'SUM(CASE WHEN ' . $prefix . 'payments.status IN ("paid", "partial_payment") THEN ' . $prefix . 'payments.amount ELSE 0 END) as total_spends'

		                      )
		                      ->groupBy( $prefix . 'customer_bookings.customer_id' )
		                      ->limit( $per_page )
		                      ->offset( $offset )
		                      ->get();
		// Step 2: Fetch bookings for each customer.
		foreach ( $clients as &$client ) {
			$client->bookings = $this->getCustomerBookings( $employee_id, $client->customer_id );
		}

		return $clients;
	}

	/**
	 * Retrieves all bookings for a specific customer under an employee.
	 *
	 * @param int $employee_id The employee ID.
	 * @param int $customer_id The customer ID.
	 *
	 * @return array Array of booking details.
	 */
	private function getCustomerBookings( $employee_id, $customer_id ) {
		$prefix = radius_table_prefix();

		$bookings = Appointment::query()
		                       ->leftJoin(
			                       'radius_booking_customer_bookings',
			                       'radius_booking_customer_bookings.appointment_id',
			                       '=',
			                       'radius_booking_appointments.id'
		                       )
		                       ->leftJoin(
			                       'radius_booking_services',
			                       'radius_booking_services.id',
			                       '=',
			                       'radius_booking_appointments.service_id'
		                       )
		                       ->where( $prefix . 'appointments.employee_id', $employee_id )
		                       ->where( $prefix . 'customer_bookings.customer_id', $customer_id )
		                       ->select(
			                       $prefix . 'appointments.id',
			                       $prefix . 'appointments.appointment_date',
			                       $prefix . 'appointments.start_time',
			                       $prefix . 'appointments.end_time',
			                       $prefix . 'appointments.status',
			                       $prefix . 'services.name as service_name',
			                       $prefix . 'services.duration as service_duration'

		                       )
		                       ->orderBy( $prefix . 'appointments.appointment_date', 'desc' )
		                       ->get();

		return $bookings;
	}


	/**
	 * Retrieves paginated payment records for a specific employee with optional date filtering.
	 *
	 * @param int $employee_id The ID of the employee whose payments to retrieve.
	 * @param string $date_filter The date range filter: 'today', 'this_week', 'this_month', or empty for all.
	 * @param int $page The current page number (1-indexed).
	 * @param int $per_page The number of items per page.
	 *
	 * @return array {
	 *     Paginated payment data with metadata.
	 *
	 * @type array $data Array of payment records.
	 * @type array $pagination {
	 * @type int $current_page Current page number.
	 * @type int $per_page Items per page.
	 * @type int $total_items Total number of matching records.
	 * @type int $total_pages Total number of pages.
	 * @type bool $has_previous Whether a previous page exists.
	 * @type bool $has_next Whether a next page exists.
	 *     }
	 * }
	 */
	public function getPaginatedPayments( $employee_id, $date_range, $per_page, $offset, $status = '', $search = '' ) {
		$prefix = radius_table_prefix();
		$query  = Payment::query()
		                 ->leftJoin(
			                 'radius_booking_customer_bookings',
			                 'radius_booking_customer_bookings.id',
			                 '=',
			                 'radius_booking_payments.booking_id'
		                 )
		                 ->leftJoin(
			                 'radius_booking_appointments',
			                 'radius_booking_appointments.id',
			                 '=',
			                 'radius_booking_customer_bookings.appointment_id'
		                 )
		                 ->leftJoin(
			                 'radius_booking_users',
			                 'radius_booking_users.id',
			                 '=',
			                 'radius_booking_customer_bookings.customer_id'
		                 )
		                 ->leftJoin(
			                 'radius_booking_user_profiles',
			                 'radius_booking_user_profiles.user_id',
			                 '=',
			                 'radius_booking_users.id'
		                 )
		                 ->where( $prefix . 'appointments.employee_id', $employee_id )
		                 ->select(
			                 $prefix . 'payments.id',
			                 $prefix . 'payments.transaction_id',
			                 $prefix . 'payments.payment_method',
			                 $prefix . 'payments.amount',
			                 $prefix . 'payments.total_amount',
			                 $prefix . 'payments.due_amount',
			                 $prefix . 'payments.status',
			                 $prefix . 'payments.paid_at',
			                 $prefix . 'payments.coupon_code',
			                 $prefix . 'payments.discount_amount',
			                 $prefix . 'payments.tax_rate',
			                 $prefix . 'payments.tax_amount',
			                 $prefix . 'user_profiles.first_name',
			                 $prefix . 'user_profiles.last_name',
			                 $prefix . 'users.email'
		                 );

		// Apply date filter if a valid range exists.
		if ( $date_range ) {
			$query->whereBetween(
				$prefix . 'payments.paid_at',
				array( $date_range['start'], $date_range['end'] )
			);
		}
		if ( $status != '' ) {
			$query->where( $prefix . 'payments.status', $status );
		}
		// Apply search filter with grouped OR conditions.
		if ( '' !== $search ) {
			$search_term = '%' . $search . '%';
			$query->where(
				function ( $q ) use ( $prefix, $search_term ) {
					$q->where( $prefix . 'payments.transaction_id', 'LIKE', $search_term )
					  ->orWhere( $prefix . 'payments.payment_method', 'LIKE', $search_term )
					  ->orWhere( $prefix . 'user_profiles.first_name', 'LIKE', $search_term )
					  ->orWhere( $prefix . 'user_profiles.last_name', 'LIKE', $search_term )
					  ->orWhere( $prefix . 'users.email', 'LIKE', $search_term );
				}
			);
		}
		$count = $query->count();

		$result = $query
			->orderBy( $prefix . 'payments.id', 'DESC' )
			->limit( $per_page )
			->offset( $offset )
			->get();

		return array(
			'data'  => $result,
			'count' => $count,

		);

	}

	/**
	 * Calculates the date range boundaries based on the specified filter type.
	 *
	 * @param string $filter The filter type: 'today', 'this_week', 'this_month', or empty.
	 *
	 * @return array|null Array with 'start' and 'end' datetime strings, or null if no filter applied.
	 */
	public function getDateRangeForFilter( $filter ) {
		if ( empty( $filter ) || 'all' === $filter ) {
			return null;
		}

		$timezone = wp_timezone();
		$now      = new \DateTime( 'now', $timezone );

		switch ( $filter ) {
			case 'today':
				$start = ( clone $now )->setTime( 0, 0, 0 );
				$end   = ( clone $now )->setTime( 23, 59, 59 );
				break;

			case 'this_week':
				// Get the start of the week based on WordPress settings.
				$week_start_day   = (int) get_option( 'start_of_week', 0 );
				$current_day      = (int) $now->format( 'w' );
				$days_to_subtract = ( $current_day - $week_start_day + 7 ) % 7;

				$start = ( clone $now )
					->modify( "-{$days_to_subtract} days" )
					->setTime( 0, 0, 0 );
				$end   = ( clone $start )
					->modify( '+6 days' )
					->setTime( 23, 59, 59 );
				break;

			case 'this_month':
				$start = ( clone $now )
					->modify( 'first day of this month' )
					->setTime( 0, 0, 0 );
				$end   = ( clone $now )
					->modify( 'last day of this month' )
					->setTime( 23, 59, 59 );
				break;

			default:
				return null;
		}

		return array(
			'start' => $start->format( 'Y-m-d' ),
			'end'   => $end->format( 'Y-m-d' ),
		);
	}


	/**
	 * Retrieves the total count of payments for an employee with optional date filtering.
	 *
	 * @param int $employee_id The ID of the employee.
	 * @param array|null $date_range Optional date range with 'start' and 'end' keys.
	 *
	 * @return array The total number of matching payment records.
	 */
	public function getTotalPaymentCount( $employee_id, $date_range = null, $status = '' ) {
		$prefix       = radius_table_prefix();
		$all_statuses = array( 'pending', 'failed', 'partial_payment', 'paid' );

		// If specific status provided, filter to only that status.
		$filter_statuses = $all_statuses;
		if ( '' !== $status && in_array( $status, $all_statuses, true ) ) {
			$filter_statuses = array( $status );
		}
		$query = Payment::query()
		                ->leftJoin(
			                'radius_booking_customer_bookings',
			                'radius_booking_customer_bookings.id',
			                '=',
			                'radius_booking_payments.booking_id'
		                )
		                ->leftJoin(
			                'radius_booking_appointments',
			                'radius_booking_appointments.id',
			                '=',
			                'radius_booking_customer_bookings.appointment_id'
		                )
		                ->where( $prefix . 'appointments.employee_id', $employee_id )
		                ->whereIn( $prefix . 'payments.status', $filter_statuses )
		                ->selectRaw(
			                $prefix . 'payments.status,
                        COUNT(*) as count,
                        SUM(CASE WHEN ' . $prefix . 'payments.status = \'pending\' THEN ' . $prefix . 'payments.total_amount ELSE ' . $prefix . 'payments.amount END) as total_amount'
		                )
		                ->groupBy( $prefix . 'payments.status' );


		if ( $date_range ) {
			$query->whereBetween(
				$prefix . 'payments.paid_at',
				array( $date_range['start'], $date_range['end'] )
			);
		}

		return $query->toArray();
	}

	public function getPaginatedSchedule( $employee_id, $page, $per_page ) {
		// Implementation for fetching paginated schedule data for the employee.
		return [];

	}

	/**
	 * Get total count by status from items array.
	 *
	 * @param array $items
	 * @param string $status
	 *
	 * @return int
	 */
	public function get_status_total( array $items, ?string $status = null ): int {
		$total  = 0;
		$status = trim( (string) $status );

		foreach ( $items as $item ) {
			if ( ! isset( $item['count'] ) ) {
				continue;
			}

			// If status is empty, count everything
			if ( $status === '' ) {
				$total += (int) $item['count'];
				continue;
			}

			// Count only matching status
			if (
				isset( $item['status'] ) &&
				$item['status'] === $status
			) {
				$total += (int) $item['count'];
			}
		}

		return $total;
	}
}
