<?php

declare( strict_types=1 );

if ( ! defined( 'ABSPATH' ) ) exit;

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

/**
 * Aggregates reporting data for the admin dashboard and future report pages.
 */
class Playard_Report {

	// ── Dashboard ─────────────────────────────────────────────────────────────────

	/**
	 * Returns stat buckets for today, this week, and this month.
	 * today also includes fields_occupied, total_fields, and new_customers.
	 *
	 * @return array{ today: array, week: array, month: array }
	 */
	public function get_dashboard_stats(): array {
		global $wpdb;

		$tz             = playard_facility_tz();
		$now_dt         = new DateTime( 'now', $tz );
		$today          = $now_dt->format( 'Y-m-d' );
		$week_start_dt  = ( clone $now_dt )->modify( 'monday this week' );
		$week_start     = $week_start_dt->format( 'Y-m-d' );
		$week_end       = ( clone $week_start_dt )->modify( '+6 days' )->format( 'Y-m-d' );
		$month_start    = $now_dt->format( 'Y-m-01' );
		$month_end      = $now_dt->format( 'Y-m-t' );

		$stats = [];

		// Each period: [ from, to ] — both inclusive.
		$periods = [
			'today' => [ $today,       $today       ],
			'week'  => [ $week_start,  $week_end    ],
			'month' => [ $month_start, $month_end   ],
		];

		foreach ( $periods as $key => [ $from, $to ] ) {
			$stats[ $key ] = [
				'bookings_count' => (int) $wpdb->get_var(
					$wpdb->prepare(
						"SELECT COUNT(*) FROM {$wpdb->prefix}playard_bookings
						 WHERE booking_date >= %s AND booking_date <= %s
						   AND booking_status IN ('confirmed','pending','completed')",
						$from,
						$to
					)
				),
				'revenue' => (float) $wpdb->get_var(
					$wpdb->prepare(
						"SELECT COALESCE(SUM(amount_paid), 0) FROM {$wpdb->prefix}playard_bookings
						 WHERE booking_date >= %s AND booking_date <= %s
						   AND payment_status = 'paid'
						   AND booking_status != 'cancelled'",
						$from,
						$to
					)
				),
			];
		}

		// Fields occupied right now — compare against facility local time.
		$now_time = playard_now( 'H:i:s' );

		$stats['today']['fields_occupied'] = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(DISTINCT field_id) FROM {$wpdb->prefix}playard_bookings
				 WHERE booking_date = %s
				   AND start_time <= %s AND end_time > %s
				   AND booking_status IN ('confirmed','pending')",
				$today,
				$now_time,
				$now_time
			)
		);

		// Total active fields for the facility
		$facility_id = playard_get_facility_id();

		$stats['today']['total_fields'] = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$wpdb->prefix}playard_fields
				 WHERE facility_id = %d AND status = 'active'",
				$facility_id
			)
		);

		// New customers registered today
		$stats['today']['new_customers'] = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$wpdb->prefix}playard_customers
				 WHERE DATE(created_at) = %s",
				$today
			)
		);

		// Upcoming pending bookings that need attention (today or future, not yet confirmed)
		$stats['today']['pending_bookings'] = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$wpdb->prefix}playard_bookings
				 WHERE booking_status = 'pending' AND booking_date >= %s",
				$today
			)
		);

		return $stats;
	}

	/**
	 * Returns today's upcoming bookings sorted by start_time.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function get_upcoming_bookings(): array {
		global $wpdb;

		$today = playard_now( 'Y-m-d' );

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT b.booking_ref, b.start_time, b.end_time, b.sport, b.booking_status,
				        f.name AS field_name,
				        c.name AS customer_name
				 FROM {$wpdb->prefix}playard_bookings b
				 LEFT JOIN {$wpdb->prefix}playard_fields f    ON b.field_id    = f.id
				 LEFT JOIN {$wpdb->prefix}playard_customers c ON b.customer_id = c.id
				 WHERE b.booking_date = %s
				   AND b.booking_status IN ('confirmed','pending','completed')
				 ORDER BY b.start_time ASC",
				$today
			)
		);

		return array_map( static function ( object $row ): array {
			return [
				'booking_ref'    => $row->booking_ref,
				'field_name'     => (string) ( $row->field_name    ?? '' ),
				'customer_name'  => (string) ( $row->customer_name ?? '' ),
				'start_time'     => $row->start_time,
				'end_time'       => $row->end_time,
				'sport'          => $row->sport,
				'booking_status' => $row->booking_status,
			];
		}, $rows );
	}

	/**
	 * Returns the most recent bookings, newest first.
	 *
	 * @param int $limit Defaults to 5.
	 * @return array<int,array<string,mixed>>
	 */
	// ── Calendar ─────────────────────────────────────────────────────────────────

	/**
	 * Returns bookings grouped by date for a given calendar month.
	 * Cancelled bookings are excluded.
	 *
	 * @return array<string, list<array<string,mixed>>>  Keys are YYYY-MM-DD dates.
	 */
	public function get_month_calendar( int $year, int $month ): array {
		global $wpdb;

		$from = sprintf( '%04d-%02d-01', $year, $month );
		$to   = gmdate( 'Y-m-t', strtotime( $from ) );

		$bookings = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT b.booking_ref, b.booking_date, b.start_time, b.end_time,
				        b.booked_slots, b.sport, b.booking_status, b.field_id,
				        f.name AS field_name, f.slot_duration,
				        c.name AS customer_name
				 FROM {$wpdb->prefix}playard_bookings b
				 LEFT JOIN {$wpdb->prefix}playard_fields f    ON b.field_id    = f.id
				 LEFT JOIN {$wpdb->prefix}playard_customers c ON b.customer_id = c.id
				 WHERE b.booking_date >= %s AND b.booking_date <= %s
				   AND b.booking_status != 'cancelled'
				 ORDER BY b.start_time ASC",
				$from,
				$to
			)
		);

		$calendar = [];
		foreach ( $bookings as $b ) {
			$booked_slots = ! empty( $b->booked_slots )
				? json_decode( (string) $b->booked_slots, true )
				: null;

			$calendar[ $b->booking_date ][] = [
				'booking_ref'    => $b->booking_ref,
				'field_id'       => (int) $b->field_id,
				'field_name'     => (string) ( $b->field_name    ?? '' ),
				'start_time'     => $b->start_time,
				'end_time'       => $b->end_time,
				'slot_duration'  => (int) ( $b->slot_duration ?? 60 ),
				'booked_slots'   => is_array( $booked_slots ) ? $booked_slots : null,
				'sport'          => $b->sport,
				'customer_name'  => (string) ( $b->customer_name ?? '' ),
				'booking_status' => $b->booking_status,
			];
		}

		return $calendar;
	}

	/**
	 * Returns bookings grouped by date for the 7-day week starting on $week_start.
	 * All 7 days are returned (even if empty). Cancelled bookings are excluded.
	 *
	 * @param string $week_start  YYYY-MM-DD of the first day (Monday).
	 * @return array<string, list<array<string,mixed>>>
	 */
	public function get_week_calendar( string $week_start ): array {
		global $wpdb;

		$from     = $week_start;
		$base     = new \DateTime( $from );
		$to       = ( clone $base )->modify( '+6 days' )->format( 'Y-m-d' );

		$bookings = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT b.booking_ref, b.booking_date, b.start_time, b.end_time,
				        b.booked_slots, b.sport, b.booking_status, b.field_id,
				        f.name AS field_name, f.slot_duration,
				        c.name AS customer_name
				 FROM {$wpdb->prefix}playard_bookings b
				 LEFT JOIN {$wpdb->prefix}playard_fields f    ON b.field_id    = f.id
				 LEFT JOIN {$wpdb->prefix}playard_customers c ON b.customer_id = c.id
				 WHERE b.booking_date >= %s AND b.booking_date <= %s
				   AND b.booking_status != 'cancelled'
				 ORDER BY b.start_time ASC",
				$from,
				$to
			)
		);

		// Pre-populate all 7 days so empty days are explicit in the response.
		$calendar = [];
		for ( $i = 0; $i < 7; $i++ ) {
			$day              = ( clone $base )->modify( "+$i days" )->format( 'Y-m-d' );
			$calendar[ $day ] = [];
		}

		foreach ( $bookings as $b ) {
			$booked_slots = ! empty( $b->booked_slots )
				? json_decode( (string) $b->booked_slots, true )
				: null;

			$calendar[ $b->booking_date ][] = [
				'booking_ref'    => $b->booking_ref,
				'field_id'       => (int) $b->field_id,
				'field_name'     => (string) ( $b->field_name    ?? '' ),
				'start_time'     => $b->start_time,
				'end_time'       => $b->end_time,
				'slot_duration'  => (int) ( $b->slot_duration ?? 60 ),
				'booked_slots'   => is_array( $booked_slots ) ? $booked_slots : null,
				'sport'          => $b->sport,
				'customer_name'  => (string) ( $b->customer_name ?? '' ),
				'booking_status' => $b->booking_status,
			];
		}

		return $calendar;
	}

	public function get_recent_bookings( int $limit = 5 ): array {
		global $wpdb;

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT b.booking_ref, b.booking_date, b.amount_total, b.booking_status,
				        f.name AS field_name,
				        c.name AS customer_name
				 FROM {$wpdb->prefix}playard_bookings b
				 LEFT JOIN {$wpdb->prefix}playard_fields f    ON b.field_id    = f.id
				 LEFT JOIN {$wpdb->prefix}playard_customers c ON b.customer_id = c.id
				 ORDER BY b.created_at DESC
				 LIMIT %d",
				$limit
			)
		);

		return array_map( static function ( object $row ): array {
			return [
				'booking_ref'    => $row->booking_ref,
				'field_name'     => (string) ( $row->field_name    ?? '' ),
				'customer_name'  => (string) ( $row->customer_name ?? '' ),
				'booking_date'   => playard_format_date( (string) $row->booking_date ),
				'amount_total'   => (float) $row->amount_total,
				'booking_status' => $row->booking_status,
			];
		}, $rows );
	}

	/**
	 * Revenue report: totals per field and per day within a date range.
	 * Only paid, non-cancelled bookings are counted.
	 *
	 * @param  string $from  Y-m-d start date (sanitized by caller).
	 * @param  string $to    Y-m-d end date   (sanitized by caller).
	 * @return array<string,mixed>
	 */
	public function get_revenue_report( string $from, string $to ): array {
		global $wpdb;

		$from = sanitize_text_field( $from );
		$to   = sanitize_text_field( $to );

		$by_field = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT b.field_id, f.name AS field_name,
				        COUNT(*) AS bookings,
				        COALESCE( SUM( b.amount_paid ), 0 ) AS revenue
				 FROM {$wpdb->prefix}playard_bookings b
				 LEFT JOIN {$wpdb->prefix}playard_fields f ON b.field_id = f.id
				 WHERE b.booking_date >= %s AND b.booking_date <= %s
				   AND b.payment_status = 'paid'
				   AND b.booking_status != 'cancelled'
				 GROUP BY b.field_id
				 ORDER BY revenue DESC",
				$from,
				$to
			)
		);

		$by_day = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT booking_date AS date,
				        COUNT(*) AS bookings,
				        COALESCE( SUM( amount_paid ), 0 ) AS revenue
				 FROM {$wpdb->prefix}playard_bookings
				 WHERE booking_date >= %s AND booking_date <= %s
				   AND payment_status = 'paid'
				   AND booking_status != 'cancelled'
				 GROUP BY booking_date
				 ORDER BY booking_date ASC",
				$from,
				$to
			)
		);

		$by_field_arr = array_map( static function ( object $row ): array {
			return [
				'field_id'   => (int)    $row->field_id,
				'field_name' => (string) ( $row->field_name ?? '' ),
				'bookings'   => (int)    $row->bookings,
				'revenue'    => (float)  $row->revenue,
			];
		}, $by_field ?: [] );

		$by_day_arr = array_map( static function ( object $row ): array {
			return [
				'date'     => (string) $row->date,
				'bookings' => (int)    $row->bookings,
				'revenue'  => (float)  $row->revenue,
			];
		}, $by_day ?: [] );

		$total_revenue  = (float) array_sum( array_column( $by_field_arr, 'revenue' ) );
		$total_bookings = (int)   array_sum( array_column( $by_field_arr, 'bookings' ) );

		return [
			'total_revenue'  => $total_revenue,
			'total_bookings' => $total_bookings,
			'by_field'       => $by_field_arr,
			'by_day'         => $by_day_arr,
		];
	}

	/**
	 * Sport breakdown report: bookings and revenue per sport within a date range.
	 * Only confirmed/completed (non-cancelled) bookings are counted.
	 *
	 * @param  string $from  Y-m-d start date (sanitized by caller).
	 * @param  string $to    Y-m-d end date   (sanitized by caller).
	 * @return array<string,mixed>
	 */
	public function get_sport_report( string $from, string $to ): array {
		global $wpdb;

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT sport,
				        COUNT(*) AS bookings,
				        COALESCE( SUM( amount_paid ), 0 ) AS revenue
				 FROM {$wpdb->prefix}playard_bookings
				 WHERE booking_date >= %s AND booking_date <= %s
				   AND booking_status IN ('confirmed','completed')
				 GROUP BY sport
				 ORDER BY bookings DESC",
				sanitize_text_field( $from ),
				sanitize_text_field( $to )
			)
		);

		$sports_list = playard_get_default_sports();
		$total       = (int) array_sum( array_column( $rows ?: [], 'bookings' ) );

		$by_sport = array_map( static function ( object $row ) use ( $total, $sports_list ): array {
			$key        = (string) $row->sport;
			$sport_meta = null;
			foreach ( $sports_list as $s ) {
				if ( isset( $s['key'] ) && $s['key'] === $key ) {
					$sport_meta = $s;
					break;
				}
			}
			return [
				'sport'      => $key,
				'label'      => (string) ( $sport_meta['label'] ?? ucfirst( $key ) ),
				'icon'       => (string) ( $sport_meta['icon']  ?? '🏅' ),
				'bookings'   => (int)    $row->bookings,
				'revenue'    => (float)  $row->revenue,
				'percentage' => $total > 0 ? (int) round( ( (int) $row->bookings / $total ) * 100 ) : 0,
			];
		}, $rows ?: [] );

		return [ 'by_sport' => $by_sport ];
	}

	/**
	 * Booking trends report: confirmed/completed/cancelled counts per day.
	 *
	 * @param  string $from  Y-m-d start date.
	 * @param  string $to    Y-m-d end date.
	 * @return array<string,mixed>
	 */
	public function get_booking_trends( string $from, string $to ): array {
		global $wpdb;

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT booking_date AS date,
				        SUM( CASE WHEN booking_status IN ('confirmed','pending') THEN 1 ELSE 0 END ) AS booked,
				        SUM( CASE WHEN booking_status = 'completed'             THEN 1 ELSE 0 END ) AS completed,
				        SUM( CASE WHEN booking_status = 'cancelled'             THEN 1 ELSE 0 END ) AS cancelled
				 FROM {$wpdb->prefix}playard_bookings
				 WHERE booking_date >= %s AND booking_date <= %s
				 GROUP BY booking_date
				 ORDER BY booking_date ASC",
				$from,
				$to
			)
		);

		// Fill every date in range so chart has no gaps.
		$map   = [];
		foreach ( $rows ?: [] as $row ) {
			$map[ $row->date ] = [
				'date'      => $row->date,
				'booked'    => (int) $row->booked,
				'completed' => (int) $row->completed,
				'cancelled' => (int) $row->cancelled,
			];
		}

		$result = [];
		$cursor = new DateTime( $from );
		$end    = new DateTime( $to );
		while ( $cursor <= $end ) {
			$key      = $cursor->format( 'Y-m-d' );
			$result[] = $map[ $key ] ?? [ 'date' => $key, 'booked' => 0, 'completed' => 0, 'cancelled' => 0 ];
			$cursor->modify( '+1 day' );
		}

		$total_booked    = array_sum( array_column( $result, 'booked' ) );
		$total_completed = array_sum( array_column( $result, 'completed' ) );
		$total_cancelled = array_sum( array_column( $result, 'cancelled' ) );

		return [
			'by_day'          => $result,
			'total_booked'    => $total_booked,
			'total_completed' => $total_completed,
			'total_cancelled' => $total_cancelled,
		];
	}

	// ── Occupancy ─────────────────────────────────────────────────────────────────

	public function get_occupancy_report( string $from, string $to ): array {
		global $wpdb;

		$from = sanitize_text_field( $from );
		$to   = sanitize_text_field( $to );

		$facility    = playard_get_facility();
		$facility_id = $facility ? (int) $facility->id : 0;

		$open_time  = $facility ? substr( (string) ( $facility->open_time  ?? '06:00' ), 0, 5 ) : '06:00';
		$close_time = $facility ? substr( (string) ( $facility->close_time ?? '22:00' ), 0, 5 ) : '22:00';
		$open_days  = is_array( json_decode( (string) ( $facility->open_days ?? '[]' ), true ) )
			? (array) json_decode( (string) ( $facility->open_days ?? '[]' ), true )
			: [];

		$operating_days = $this->count_operating_days( $from, $to, $open_days );

		$open_mins   = $this->time_to_minutes( $open_time );
		$close_mins  = $this->time_to_minutes( $close_time );
		$window_mins = max( 0, $close_mins - $open_mins );

		$fields = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, name, slot_duration
				 FROM {$wpdb->prefix}playard_fields
				 WHERE facility_id = %d AND status = 'active'
				 ORDER BY sort_order ASC",
				$facility_id
			)
		);

		$booked_rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT field_id, COALESCE( SUM( slots_count ), 0 ) AS booked_slots
				 FROM {$wpdb->prefix}playard_bookings
				 WHERE booking_date >= %s AND booking_date <= %s
				   AND booking_status IN ('confirmed','completed')
				 GROUP BY field_id",
				$from,
				$to
			)
		);

		$booked_map = [];
		foreach ( $booked_rows as $row ) {
			$booked_map[ (int) $row->field_id ] = (int) $row->booked_slots;
		}

		$by_field = [];
		foreach ( $fields as $f ) {
			$slot_dur        = max( 1, (int) $f->slot_duration );
			$daily_slots     = $window_mins > 0 ? (int) floor( $window_mins / $slot_dur ) : 0;
			$available_slots = $daily_slots * $operating_days;
			$booked_slots    = $booked_map[ (int) $f->id ] ?? 0;

			$pct = null;
			if ( $available_slots > 0 ) {
				$pct = min( 100.0, round( ( $booked_slots / $available_slots ) * 100, 1 ) );
			}

			$by_field[] = [
				'field_id'        => (int) $f->id,
				'field_name'      => (string) $f->name,
				'available_slots' => $available_slots,
				'booked_slots'    => $booked_slots,
				'daily_slots'     => $daily_slots,
				'operating_days'  => $operating_days,
				'occupancy_pct'   => $pct,
			];
		}

		$total_available = array_sum( array_column( $by_field, 'available_slots' ) );
		$total_booked    = array_sum( array_column( $by_field, 'booked_slots' ) );
		$overall_pct     = $total_available > 0
			? min( 100.0, round( ( $total_booked / $total_available ) * 100, 1 ) )
			: null;

		$valid = array_values( array_filter( $by_field, fn( $f ) => $f['occupancy_pct'] !== null ) );
		usort( $valid, fn( $a, $b ) => $b['occupancy_pct'] <=> $a['occupancy_pct'] );

		return [
			'overall_pct'     => $overall_pct,
			'total_available' => $total_available,
			'total_booked'    => $total_booked,
			'most_occupied'   => $valid[0]                     ?? null,
			'least_occupied'  => ! empty( $valid ) ? end( $valid ) : null,
			'by_field'        => $by_field,
			'operating_days'  => $operating_days,
		];
	}

	private function count_operating_days( string $from, string $to, array $open_days ): int {
		if ( empty( $open_days ) ) return 0;

		// open_days are 3-letter lowercase strings: mon, tue, wed, thu, fri, sat, sun
		// gmdate('D') returns Mon, Tue … — strtolower gives mon, tue …
		$count  = 0;
		$cursor = new DateTime( $from );
		$end    = new DateTime( $to );

		while ( $cursor <= $end ) {
			$day_key = strtolower( (string) $cursor->format( 'D' ) );
			if ( in_array( $day_key, $open_days, true ) ) {
				$count++;
			}
			$cursor->modify( '+1 day' );
		}

		return $count;
	}

	private function time_to_minutes( string $hhmm ): int {
		$parts = explode( ':', $hhmm . ':0' );
		return (int) $parts[0] * 60 + (int) $parts[1];
	}
}
