<?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\Dashboard;
use RadiusTheme\RadiusBooking\Models\Payment;
use RadiusTheme\RadiusBooking\Models\Service;
use RadiusTheme\RadiusBooking\Models\User;

/**
 * Class DashboardRepository
 *
 * Handles data operations for the Dashboard model.
 */
class DashboardRepository extends BaseRepository {

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


	/**
	 * Retrieves the latest appointment based on the highest ID.
	 *
	 * @return Appointment|null The latest appointment record, or null if no appointments exist.
	 */
	public function latestAppointment() {
		return Appointment::query()->orderBy( 'id', 'DESC' )->first();
	}

	/**
	 * Retrieves the most recently added customer.
	 *
	 * @return User|null The latest customer record or null if no customers are found.
	 */
	public function latestCustomer() {
		return User::query()->where( 'user_type', 'customer' )->orderBy( 'id', 'DESC' )->first();
	}

	/**
	 * Retrieves the most recent payment record based on the updated timestamp.
	 *
	 * @return Payment|null The latest payment record or null if no records exist.
	 */
	public function latestPayment() {
		return Payment::query()->orderBy( 'updated_at', 'DESC' )->first();
	}

	/**
	 * Retrieves the most recently added employee record.
	 *
	 * @return User|null The latest employee record or null if no employee exists.
	 */
	public function latestEmployee() {
		return User::query()->where( 'user_type', 'employee' )->orderBy( 'id', 'DESC' )->first();
	}

	/**
	 * Retrieves the most recently updated service.
	 *
	 * @return Service|null The most recently updated service, or null if no services exist.
	 */
	public function latestService() {
		return Service::query()->orderBy( 'updated_at', 'DESC' )->first();
	}


	/**
	 * Get yearly statistics grouped by month.
	 *
	 * @param int $year The year for which statistics are retrieved.
	 */
	public function getYearlyStatistics( int $year ) {
		return Appointment::query()
							->selectRaw( 'MONTH(appointment_date) as month, COUNT(*) as total' )
							->whereYear( 'appointment_date', $year )
							->groupByRaw( 'MONTH(appointment_date)' )
							->orderBy( 'month', 'ASC' )
							->get();
	}

	/**
	 * Retrieves monthly appointment statistics based on the specified year and month.
	 *
	 * @param int $year The year for which the statistics need to be fetched.
	 * @param int $month The month for which the statistics need to be fetched.
	 */
	public function getMonthlyStatistics( int $year, int $month ) {
		return Appointment::query()
							->selectRaw( 'DAY(appointment_date) as day, COUNT(*) as total' )
							->whereYear( 'appointment_date', $year )
							->whereMonth( 'appointment_date', $month )
							->groupByRaw( 'DAY(appointment_date)' )
							->orderBy( 'day', 'ASC' )
							->get();
	}

	/**
	 * Retrieves the weekly statistics for appointments within the given date range.
	 *
	 * @param string $startDate The start date for the range, in format 'Y-m-d'.
	 * @param string $endDate The end date for the range, in format 'Y-m-d'.
	 */
	public function getWeeklyStatistics( string $startDate, string $endDate ) {
		return Appointment::query()
							->selectRaw( 'DAYOFWEEK(appointment_date) as weekday, COUNT(*) as total' )
							->whereBetween( 'appointment_date', array( $startDate, $endDate ) )
							->groupByRaw( 'DAYOFWEEK(appointment_date)' )
							->orderBy( 'weekday', 'ASC' )
							->get();
	}


	/**
	 * Retrieves the yearly revenue by calculating the total payment amounts for each month of the specified year.
	 *
	 * @param int $year The year for which the revenue is to be retrieved.
	 */
	public function getYearlyRevenue( int $year ) {
		return Payment::query()
						->selectRaw( 'MONTH(booking_date_time) as month, SUM(amount) as total' )
						->whereYear( 'booking_date_time', $year )
						->whereIn( 'status', array( 'paid', 'completed', 'partial_payment' ) )
						->groupByRaw( 'MONTH(booking_date_time)' )
						->orderBy( 'month', 'ASC' )
						->get();
	}

	/**
	 * Retrieves the monthly revenue breakdown by day for a specified year and month.
	 *
	 * @param int $year The year for which the monthly revenue is to be retrieved.
	 * @param int $month The month for which the monthly revenue is to be retrieved.
	 */
	public function getMonthlyRevenue( int $year, int $month ) {
		return Payment::query()
						->selectRaw( 'DAY(booking_date_time) as day, SUM(amount) as total' )
						->whereYear( 'booking_date_time', $year )
						->whereMonth( 'booking_date_time', $month )
						->whereIn( 'status', array( 'paid', 'completed', 'partial_payment' ) )
						->groupByRaw( 'DAY(booking_date_time)' )
						->orderBy( 'day', 'ASC' )
						->get();
	}

	/**
	 * Retrieves the weekly revenue grouped by the day of the week within a specified date range.
	 *
	 * @param string $startDate The start date of the range in the format 'YYYY-MM-DD'.
	 * @param string $endDate The end date of the range in the format 'YYYY-MM-DD'.
	 */
	public function getWeeklyRevenue( string $startDate, string $endDate ) {
		return Payment::query()
						->selectRaw( 'DAYOFWEEK(booking_date_time) as weekday, SUM(amount) as total' )
						->whereBetween( 'booking_date_time', array( $startDate, $endDate ) )
						->whereIn( 'status', array( 'paid', 'completed', 'partial_payment' ) )
						->groupByRaw( 'DAYOFWEEK(booking_date_time)' )
						->orderBy( 'weekday', 'ASC' )
						->get();
	}
}
