<?php
/**
 * Read-only aggregates for the Reports page. Customer identities are never selected.
 *
 * A scope is array{start_utc:string,end_utc:string,currency:string,staff_id:int|string,location_id:int}.
 *
 * @package Abientot
 */

namespace Abientot\Repositories;

use Abientot\Support\Db;
use Abientot\Support\StaffFilter;

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

final class ReportInsightsRepository {
	/** Online sources are bookings customers made themselves. */
	public const ONLINE_SOURCES = array( 'frontend', 'group_enrollment', 'waitlist_offer', 'portal' );
	public const TEAM_SOURCES   = array( 'admin', 'admin_recurrence' );

	/**
	 * Scope values in the order of the shared filter every query writes out:
	 * start/end of the UTC period, currency, staff filter (StaffFilter::arg, used twice) and
	 * location (0 = all, used twice).
	 *
	 * @return array{0:string,1:string,2:string,3:int,4:int}
	 */
	private function scope( array $scope ): array {
		return array( (string) $scope['start_utc'], (string) $scope['end_utc'], (string) $scope['currency'], StaffFilter::arg( $scope['staff_id'] ?? 0 ), max( 0, (int) ( $scope['location_id'] ?? 0 ) ) );
	}

	/** @return array<string,int> */
	public function summary( array $scope ): array {
		global $wpdb;
		list( $start, $end, $currency, $staff, $location ) = $this->scope( $scope );
		$rows = Db::rows(
			$wpdb->suppress_errors( true ),
			$wpdb->get_results(
				$wpdb->prepare(
					"SELECT COUNT(*) AS booking_count,
						COALESCE(SUM(CASE WHEN COALESCE(st.counts_as_revenue, 0) = 1 THEN a.price_cents ELSE 0 END), 0) AS revenue_cents,
						COALESCE(SUM(CASE WHEN COALESCE(st.counts_as_revenue, 0) = 1 THEN 1 ELSE 0 END), 0) AS revenue_booking_count,
						COALESCE(SUM(CASE WHEN st.slug = 'pending' AND NOT (COALESCE(st.slug, '') = 'cancelled' OR a.cancelled_at IS NOT NULL) THEN 1 ELSE 0 END), 0) AS pending_count,
						COALESCE(SUM(CASE WHEN st.slug = 'completed' AND NOT (COALESCE(st.slug, '') = 'cancelled' OR a.cancelled_at IS NOT NULL) THEN 1 ELSE 0 END), 0) AS completed_count,
						COALESCE(SUM(CASE WHEN (COALESCE(st.slug, '') = 'cancelled' OR a.cancelled_at IS NOT NULL) THEN 1 ELSE 0 END), 0) AS cancelled_count,
						COALESCE(SUM(CASE WHEN st.slug = 'no_show' AND NOT (COALESCE(st.slug, '') = 'cancelled' OR a.cancelled_at IS NOT NULL) THEN 1 ELSE 0 END), 0) AS no_show_count,
						COALESCE(SUM(CASE WHEN (COALESCE(st.slug, '') = 'cancelled' OR a.cancelled_at IS NOT NULL) THEN 0 ELSE a.duration_minutes END), 0) AS booked_minutes,
						COUNT(DISTINCT a.customer_id) AS customer_count
					FROM {$wpdb->prefix}abientot_appointments a LEFT JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id WHERE a.start_utc >= %s AND a.start_utc < %s AND a.currency = %s AND ( %d = 0 OR a.staff_id = GREATEST( %d, 0 ) ) AND ( %d = 0 OR a.location_id = %d )",
					$start,
					$end,
					$currency,
					$staff,
					$staff,
					$location,
					$location
				),
				ARRAY_A
			),
			'report_query_failed'
		);
		return array_map( 'intval', $rows[0] ?? array() ) + array_fill_keys( array( 'booking_count', 'revenue_cents', 'revenue_booking_count', 'pending_count', 'completed_count', 'cancelled_count', 'no_show_count', 'booked_minutes', 'customer_count' ), 0 );
	}

	/**
	 * UTC 15-minute buckets (exact for half- and quarter-hour timezones); callers convert them
	 * to local days, weeks and weekday/hour cells.
	 *
	 * @return array<int,array{bucket:string,booking_count:int,revenue_cents:int,active_count:int}>
	 */
	public function hourly( array $scope ): array {
		global $wpdb;
		list( $start, $end, $currency, $staff, $location ) = $this->scope( $scope );
		$rows = Db::rows(
			$wpdb->suppress_errors( true ),
			$wpdb->get_results(
				$wpdb->prepare(
					"SELECT CONCAT(DATE_FORMAT(a.start_utc, '%%Y-%%m-%%d %%H:'), LPAD(FLOOR(MINUTE(a.start_utc) / 15) * 15, 2, '0'), ':00') AS bucket, COUNT(*) AS booking_count,
						COALESCE(SUM(CASE WHEN COALESCE(st.counts_as_revenue, 0) = 1 THEN a.price_cents ELSE 0 END), 0) AS revenue_cents,
						COALESCE(SUM(CASE WHEN (COALESCE(st.slug, '') = 'cancelled' OR a.cancelled_at IS NOT NULL) THEN 0 ELSE 1 END), 0) AS active_count
					FROM {$wpdb->prefix}abientot_appointments a LEFT JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id WHERE a.start_utc >= %s AND a.start_utc < %s AND a.currency = %s AND ( %d = 0 OR a.staff_id = GREATEST( %d, 0 ) ) AND ( %d = 0 OR a.location_id = %d ) GROUP BY bucket ORDER BY bucket LIMIT 40000",
					$start,
					$end,
					$currency,
					$staff,
					$staff,
					$location,
					$location
				),
				ARRAY_A
			),
			'report_query_failed'
		);
		return array_map(
			static fn( array $row ): array => array(
				'bucket'        => (string) $row['bucket'],
				'booking_count' => (int) $row['booking_count'],
				'revenue_cents' => (int) $row['revenue_cents'],
				'active_count'  => (int) $row['active_count'],
			),
			$rows
		);
	}

	/** @return array<int,array<string,mixed>> */
	public function statuses( array $scope ): array {
		global $wpdb;
		list( $start, $end, $currency, $staff, $location ) = $this->scope( $scope );
		return Db::rows(
			$wpdb->suppress_errors( true ),
			$wpdb->get_results(
				$wpdb->prepare(
					"SELECT st.id, st.slug, st.label, st.color, COUNT(a.id) AS booking_count
					FROM {$wpdb->prefix}abientot_appointments a LEFT JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id WHERE a.start_utc >= %s AND a.start_utc < %s AND a.currency = %s AND ( %d = 0 OR a.staff_id = GREATEST( %d, 0 ) ) AND ( %d = 0 OR a.location_id = %d )
					GROUP BY st.id, st.slug, st.label, st.color, st.sort_order ORDER BY st.sort_order ASC, st.label ASC LIMIT 60",
					$start,
					$end,
					$currency,
					$staff,
					$staff,
					$location,
					$location
				),
				ARRAY_A
			),
			'report_query_failed'
		);
	}

	/** @return array<int,array<string,mixed>> */
	public function services( array $scope ): array {
		global $wpdb;
		list( $start, $end, $currency, $staff, $location ) = $this->scope( $scope );
		return Db::rows(
			$wpdb->suppress_errors( true ),
			$wpdb->get_results(
				$wpdb->prepare(
					"SELECT sv.id, sv.name, sv.color, COUNT(a.id) AS booking_count, COALESCE(SUM(CASE WHEN COALESCE(st.counts_as_revenue, 0) = 1 THEN a.price_cents ELSE 0 END), 0) AS revenue_cents,
						COALESCE(SUM(CASE WHEN (COALESCE(st.slug, '') = 'cancelled' OR a.cancelled_at IS NOT NULL) THEN 0 ELSE a.duration_minutes END), 0) AS booked_minutes
					FROM {$wpdb->prefix}abientot_appointments a LEFT JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id LEFT JOIN {$wpdb->prefix}abientot_services sv ON sv.id = a.service_id WHERE a.start_utc >= %s AND a.start_utc < %s AND a.currency = %s AND ( %d = 0 OR a.staff_id = GREATEST( %d, 0 ) ) AND ( %d = 0 OR a.location_id = %d )
					GROUP BY sv.id, sv.name, sv.color ORDER BY revenue_cents DESC, booking_count DESC, sv.name ASC LIMIT 50",
					$start,
					$end,
					$currency,
					$staff,
					$staff,
					$location,
					$location
				),
				ARRAY_A
			),
			'report_query_failed'
		);
	}

	/** Lead time between booking creation and appointment start, in fixed buckets. */
	public function lead_times( array $scope ): array {
		global $wpdb;
		list( $start, $end, $currency, $staff, $location ) = $this->scope( $scope );
		$rows = Db::rows(
			$wpdb->suppress_errors( true ),
			$wpdb->get_results(
				$wpdb->prepare(
					"SELECT COALESCE(SUM(CASE WHEN TIMESTAMPDIFF(MINUTE, a.created_at, a.start_utc) < 1440 THEN 1 ELSE 0 END), 0) AS same_day,
						COALESCE(SUM(CASE WHEN TIMESTAMPDIFF(MINUTE, a.created_at, a.start_utc) >= 1440 AND TIMESTAMPDIFF(MINUTE, a.created_at, a.start_utc) < 4320 THEN 1 ELSE 0 END), 0) AS days_1_3,
						COALESCE(SUM(CASE WHEN TIMESTAMPDIFF(MINUTE, a.created_at, a.start_utc) >= 4320 AND TIMESTAMPDIFF(MINUTE, a.created_at, a.start_utc) < 10080 THEN 1 ELSE 0 END), 0) AS days_3_7,
						COALESCE(SUM(CASE WHEN TIMESTAMPDIFF(MINUTE, a.created_at, a.start_utc) >= 10080 AND TIMESTAMPDIFF(MINUTE, a.created_at, a.start_utc) < 20160 THEN 1 ELSE 0 END), 0) AS days_7_14,
						COALESCE(SUM(CASE WHEN TIMESTAMPDIFF(MINUTE, a.created_at, a.start_utc) >= 20160 AND TIMESTAMPDIFF(MINUTE, a.created_at, a.start_utc) < 43200 THEN 1 ELSE 0 END), 0) AS days_14_30,
						COALESCE(SUM(CASE WHEN TIMESTAMPDIFF(MINUTE, a.created_at, a.start_utc) >= 43200 THEN 1 ELSE 0 END), 0) AS days_30_plus,
						COALESCE(AVG(GREATEST(0, TIMESTAMPDIFF(MINUTE, a.created_at, a.start_utc))), 0) AS average_minutes
					FROM {$wpdb->prefix}abientot_appointments a LEFT JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id WHERE a.start_utc >= %s AND a.start_utc < %s AND a.currency = %s AND ( %d = 0 OR a.staff_id = GREATEST( %d, 0 ) ) AND ( %d = 0 OR a.location_id = %d ) AND a.source <> 'import'",
					$start,
					$end,
					$currency,
					$staff,
					$staff,
					$location,
					$location
				),
				ARRAY_A
			),
			'report_query_failed'
		);
		return array_map( 'intval', $rows[0] ?? array() );
	}

	/** @return array<string,int> online|team|import|other => bookings */
	public function sources( array $scope ): array {
		global $wpdb;
		list( $start, $end, $currency, $staff, $location ) = $this->scope( $scope );
		$out  = array(
			'online' => 0,
			'team'   => 0,
			'import' => 0,
			'other'  => 0
		);
		$rows = Db::rows(
			$wpdb->suppress_errors( true ),
			$wpdb->get_results(
				$wpdb->prepare(
					"SELECT a.source, COUNT(*) AS booking_count FROM {$wpdb->prefix}abientot_appointments a LEFT JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id WHERE a.start_utc >= %s AND a.start_utc < %s AND a.currency = %s AND ( %d = 0 OR a.staff_id = GREATEST( %d, 0 ) ) AND ( %d = 0 OR a.location_id = %d ) GROUP BY a.source LIMIT 50",
					$start,
					$end,
					$currency,
					$staff,
					$staff,
					$location,
					$location
				),
				ARRAY_A
			),
			'report_query_failed'
		);
		foreach ( $rows as $row ) {
			$source       = (string) $row['source'];
			$key          = in_array( $source, self::ONLINE_SOURCES, true ) ? 'online' : ( in_array( $source, self::TEAM_SOURCES, true ) ? 'team' : ( 'import' === $source ? 'import' : 'other' ) );
			$out[ $key ] += (int) $row['booking_count'];
		}
		return $out;
	}

	/** Customers seen in the scope, split by whether their first ever booking falls inside it. */
	public function customers( array $scope ): array {
		global $wpdb;
		list( $start, $end, $currency, $staff, $location ) = $this->scope( $scope );
		$rows = Db::rows(
			$wpdb->suppress_errors( true ),
			$wpdb->get_results(
				$wpdb->prepare(
					"SELECT COUNT(*) AS customer_count,
						COALESCE(SUM(CASE WHEN first_seen.first_start >= %s THEN 1 ELSE 0 END), 0) AS new_count,
						COALESCE(SUM(CASE WHEN period.visits > 1 THEN 1 ELSE 0 END), 0) AS repeat_count,
						COALESCE(SUM(period.visits), 0) AS booking_count
					FROM (SELECT a.customer_id, COUNT(*) AS visits FROM {$wpdb->prefix}abientot_appointments a LEFT JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id WHERE a.start_utc >= %s AND a.start_utc < %s AND a.currency = %s AND ( %d = 0 OR a.staff_id = GREATEST( %d, 0 ) ) AND ( %d = 0 OR a.location_id = %d ) AND NOT (COALESCE(st.slug, '') = 'cancelled' OR a.cancelled_at IS NOT NULL) GROUP BY a.customer_id) period
					INNER JOIN (SELECT customer_id, MIN(start_utc) AS first_start FROM {$wpdb->prefix}abientot_appointments WHERE cancelled_at IS NULL GROUP BY customer_id) first_seen ON first_seen.customer_id = period.customer_id",
					$start,
					$start,
					$end,
					$currency,
					$staff,
					$staff,
					$location,
					$location
				),
				ARRAY_A
			),
			'report_query_failed'
		);
		return array_map( 'intval', $rows[0] ?? array() ) + array(
			'customer_count' => 0,
			'new_count'      => 0,
			'repeat_count'   => 0,
			'booking_count'  => 0
		);
	}

	/** @return array<int,array<string,mixed>> */
	public function staff( array $scope ): array {
		global $wpdb;
		list( $start, $end, $currency, $staff, $location ) = $this->scope( $scope );
		return Db::rows(
			$wpdb->suppress_errors( true ),
			$wpdb->get_results(
				$wpdb->prepare(
					"SELECT a.staff_id AS staff_id, MAX(s.display_name) AS name, MAX(s.photo_id) AS photo_id, COUNT(*) AS booking_count,
						COALESCE(SUM(CASE WHEN COALESCE(st.counts_as_revenue, 0) = 1 THEN a.price_cents ELSE 0 END), 0) AS revenue_cents,
						COALESCE(SUM(CASE WHEN st.slug = 'completed' AND NOT (COALESCE(st.slug, '') = 'cancelled' OR a.cancelled_at IS NOT NULL) THEN 1 ELSE 0 END), 0) AS completed_count,
						COALESCE(SUM(CASE WHEN st.slug = 'no_show' AND NOT (COALESCE(st.slug, '') = 'cancelled' OR a.cancelled_at IS NOT NULL) THEN 1 ELSE 0 END), 0) AS no_show_count,
						COALESCE(SUM(CASE WHEN (COALESCE(st.slug, '') = 'cancelled' OR a.cancelled_at IS NOT NULL) THEN 1 ELSE 0 END), 0) AS cancelled_count,
						COALESCE(SUM(CASE WHEN (COALESCE(st.slug, '') = 'cancelled' OR a.cancelled_at IS NOT NULL) THEN 0 ELSE a.duration_minutes END), 0) AS booked_minutes
					FROM {$wpdb->prefix}abientot_appointments a LEFT JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id LEFT JOIN {$wpdb->prefix}abientot_staff s ON s.id = a.staff_id WHERE a.start_utc >= %s AND a.start_utc < %s AND a.currency = %s AND ( %d = 0 OR a.staff_id = GREATEST( %d, 0 ) ) AND ( %d = 0 OR a.location_id = %d )
					GROUP BY a.staff_id ORDER BY revenue_cents DESC, booking_count DESC LIMIT 100",
					$start,
					$end,
					$currency,
					$staff,
					$staff,
					$location,
					$location
				),
				ARRAY_A
			),
			'report_query_failed'
		);
	}

	/** @return array<int,array<string,mixed>> */
	public function locations( array $scope ): array {
		global $wpdb;
		list( $start, $end, $currency, $staff, $location ) = $this->scope( $scope );
		return Db::rows(
			$wpdb->suppress_errors( true ),
			$wpdb->get_results(
				$wpdb->prepare(
					"SELECT a.location_id, MAX(l.name) AS name, COUNT(*) AS booking_count, COALESCE(SUM(CASE WHEN COALESCE(st.counts_as_revenue, 0) = 1 THEN a.price_cents ELSE 0 END), 0) AS revenue_cents,
						COALESCE(SUM(CASE WHEN (COALESCE(st.slug, '') = 'cancelled' OR a.cancelled_at IS NOT NULL) THEN 0 ELSE a.duration_minutes END), 0) AS booked_minutes,
						COUNT(DISTINCT a.staff_id) AS staff_count
					FROM {$wpdb->prefix}abientot_appointments a LEFT JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id LEFT JOIN {$wpdb->prefix}abientot_locations l ON l.id = a.location_id WHERE a.start_utc >= %s AND a.start_utc < %s AND a.currency = %s AND ( %d = 0 OR a.staff_id = GREATEST( %d, 0 ) ) AND ( %d = 0 OR a.location_id = %d )
					GROUP BY a.location_id ORDER BY revenue_cents DESC, booking_count DESC LIMIT 100",
					$start,
					$end,
					$currency,
					$staff,
					$staff,
					$location,
					$location
				),
				ARRAY_A
			),
			'report_query_failed'
		);
	}

	/**
	 * Non-cancelled appointments that block time, for "right now" views. No customer columns.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function blocking_between( string $start_utc, string $end_utc, array $staff_ids = array() ): array {
		global $wpdb;
		$staff_ids = array_map( 'intval', $staff_ids );
		if ( $staff_ids ) {
			return Db::rows(
				$wpdb->suppress_errors( true ),
				$wpdb->get_results(
					$wpdb->prepare(
						"SELECT a.id, a.staff_id AS staff_id, a.location_id, a.start_utc, a.end_utc, a.duration_minutes, st.slug AS status_slug, sv.name AS service_name
						FROM {$wpdb->prefix}abientot_appointments a LEFT JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id LEFT JOIN {$wpdb->prefix}abientot_services sv ON sv.id = a.service_id
						WHERE a.end_utc > %s AND a.start_utc < %s AND a.staff_id IN (" . implode( ',', array_fill( 0, count( $staff_ids ), '%d' ) ) . ") AND NOT (COALESCE(st.slug, '') = 'cancelled' OR a.cancelled_at IS NOT NULL) AND COALESCE(st.blocks_availability, 1) = 1
						ORDER BY a.start_utc ASC LIMIT 2000",
						array_merge( array( $start_utc, $end_utc ), $staff_ids )
					),
					ARRAY_A
				),
				'report_query_failed'
			);
		}
		return Db::rows(
			$wpdb->suppress_errors( true ),
			$wpdb->get_results(
				$wpdb->prepare(
					"SELECT a.id, a.staff_id AS staff_id, a.location_id, a.start_utc, a.end_utc, a.duration_minutes, st.slug AS status_slug, sv.name AS service_name
					FROM {$wpdb->prefix}abientot_appointments a LEFT JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id LEFT JOIN {$wpdb->prefix}abientot_services sv ON sv.id = a.service_id
					WHERE a.end_utc > %s AND a.start_utc < %s AND NOT (COALESCE(st.slug, '') = 'cancelled' OR a.cancelled_at IS NOT NULL) AND COALESCE(st.blocks_availability, 1) = 1
						ORDER BY a.start_utc ASC LIMIT 2000",
					$start_utc,
					$end_utc
				),
				ARRAY_A
			),
			'report_query_failed'
		);
	}

	/**
	 * Staff occupancy: for each team member, the time their bookings cover inside their working
	 * hours, every minute counted once (group enrollments share one session). Resource bookings
	 * (tables, rooms, equipment) are not staff time and are left out. Never above 100 %.
	 *
	 * @param array<int,array<string,mixed>> $staff Rows with id and timezone.
	 * @return array<int,array{scheduled:int,booked:int,outside:int}> staff_id => minutes
	 */
	public function occupancy( array $scope, array $staff, string $from_date, string $to_date, \DateTimeZone $fallback ): array {
		$ids = array_values( array_filter( array_map( static fn( array $row ): int => (int) $row['id'], $staff ) ) );
		if ( ! $ids ) {
			return array();
		}
		global $wpdb;
		list( $start, $end, $currency, $staff_filter, $location ) = $this->scope( $scope );
		$booked = array();
		$rows   = Db::rows(
			$wpdb->suppress_errors( true ),
			$wpdb->get_results(
				$wpdb->prepare(
					"SELECT a.staff_id AS staff_id, a.start_utc, a.end_utc FROM {$wpdb->prefix}abientot_appointments a LEFT JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
					WHERE a.start_utc >= %s AND a.start_utc < %s AND a.currency = %s AND ( %d = 0 OR a.staff_id = GREATEST( %d, 0 ) ) AND ( %d = 0 OR a.location_id = %d ) AND a.staff_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") AND NOT (COALESCE(st.slug, '') = 'cancelled' OR a.cancelled_at IS NOT NULL) LIMIT 50000",
					array_merge( array( $start, $end, $currency, $staff_filter, $staff_filter, $location, $location ), $ids )
				),
				ARRAY_A
			),
			'report_query_failed'
		);
		foreach ( $rows as $row ) {
			$booked[ (int) $row['staff_id'] ][] = array( strtotime( $row['start_utc'] . ' UTC' ), strtotime( $row['end_utc'] . ' UTC' ) );
		}
		$period  = array( strtotime( $scope['start_utc'] . ' UTC' ), strtotime( $scope['end_utc'] . ' UTC' ) );
		$windows = $this->working_windows( $staff, $from_date, $to_date );
		$out     = array();
		foreach ( $staff as $member ) {
			$id = (int) $member['id'];
			try {
				$zone = new \DateTimeZone( (string) ( $member['timezone'] ?? '' ) ?: $fallback->getName() );
			} catch ( \Throwable $error ) {
				$zone = $fallback;
			}
			$working = array();
			foreach ( $windows[ $id ] ?? array() as $date => $day ) {
				$midnight = new \DateTimeImmutable( $date, $zone );
				$at       = static fn( int $minute ): int => ( $minute >= 1440 ? $midnight->modify( '+1 day' ) : $midnight->setTime( intdiv( $minute, 60 ), $minute % 60 ) )->getTimestamp();
				foreach ( $day as $window ) {
					$working[] = array( $at( $window[0] ), $at( $window[1] ) );
				}
			}
			$covered = array();
			foreach ( self::union( $booked[ $id ] ?? array() ) as $interval ) {
				$interval = array( max( $interval[0], $period[0] ), min( $interval[1], $period[1] ) );
				if ( $interval[1] > $interval[0] ) {
					$covered[] = $interval;
				}
			}
			$working    = self::union( $working );
			$inside     = self::overlap( $covered, $working );
			$out[ $id ] = array(
				'scheduled' => intdiv( self::span( $working ), 60 ),
				'booked'    => intdiv( $inside, 60 ),
				'outside'   => intdiv( self::span( $covered ) - $inside, 60 ),
			);
		}
		return $out;
	}

	/**
	 * Merge overlapping or touching intervals.
	 *
	 * @param array<int,array{0:int,1:int}> $intervals
	 * @return array<int,array{0:int,1:int}>
	 */
	public static function union( array $intervals ): array {
		return self::merge( array_values( array_filter( $intervals, static fn( array $pair ): bool => $pair[1] > $pair[0] ) ) );
	}

	/** @param array<int,array{0:int,1:int}> $intervals Already merged. */
	public static function span( array $intervals ): int {
		return array_sum( array_map( static fn( array $pair ): int => $pair[1] - $pair[0], $intervals ) );
	}

	/** Length shared by two merged, sorted interval lists. */
	public static function overlap( array $a, array $b ): int {
		$total = 0;
		for ( $i = 0, $j = 0; $i < count( $a ) && $j < count( $b ); ) {
			$total += max( 0, min( $a[ $i ][1], $b[ $j ][1] ) - max( $a[ $i ][0], $b[ $j ][0] ) );
			if ( $a[ $i ][1] < $b[ $j ][1] ) {
				++$i;
			} else {
				++$j;
			}
		}
		return $total;
	}

	/** @return array<int,array<string,mixed>> */
	public function active_locations(): array {
		global $wpdb;
		return Db::rows( $wpdb->suppress_errors( true ), $wpdb->get_results( "SELECT id, name, timezone FROM {$wpdb->prefix}abientot_locations WHERE status = 'active' ORDER BY is_default DESC, name ASC, id ASC LIMIT 200", ARRAY_A ), 'report_query_failed' );
	}

	/** @return array<int,array<string,mixed>> */
	public function active_staff(): array {
		global $wpdb;
		return Db::rows( $wpdb->suppress_errors( true ), $wpdb->get_results( "SELECT id, display_name, photo_id, timezone, is_default FROM {$wpdb->prefix}abientot_staff WHERE status = 'active' ORDER BY is_default DESC, display_name ASC, id ASC LIMIT 100", ARRAY_A ), 'report_query_failed' );
	}

	/**
	 * Scheduled working minutes per staff and local date, from weekly rules and date exceptions.
	 * An estimate for occupancy: location-specific and service-specific rules are merged.
	 *
	 * @param array<int,array<string,mixed>> $staff Rows with id and timezone.
	 * @return array<int,array<string,array<int,array{0:int,1:int}>>> staff_id => Y-m-d => minute windows
	 */
	public function working_windows( array $staff, string $from_date, string $to_date ): array {
		global $wpdb;
		$ids = array_values( array_filter( array_map( static fn( array $row ): int => (int) $row['id'], $staff ) ) );
		if ( ! $ids || $to_date < $from_date ) {
			return array();
		}
		$rules      = Db::rows(
			$wpdb->suppress_errors( true ),
			$wpdb->get_results(
				$wpdb->prepare(
					"SELECT staff_id, weekday, start_time, end_time, rule_type, effective_from, effective_to FROM {$wpdb->prefix}abientot_availability_rules WHERE is_active = 1 AND staff_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") LIMIT 5000",
					$ids
				),
				ARRAY_A
			),
			'report_query_failed'
		);
		$exceptions = Db::rows(
			$wpdb->suppress_errors( true ),
			$wpdb->get_results(
				$wpdb->prepare(
					"SELECT staff_id, exception_date, start_time, end_time, exception_type FROM {$wpdb->prefix}abientot_availability_exceptions WHERE staff_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") AND exception_date >= %s AND exception_date <= %s LIMIT 20000",
					array_merge( $ids, array( $from_date, $to_date ) )
				),
				ARRAY_A
			),
			'report_query_failed'
		);
		$by_rule    = array();
		foreach ( $rules as $rule ) {
			$by_rule[ (int) $rule['staff_id'] ][ (int) $rule['weekday'] ][] = $rule;
		}
		$by_date = array();
		foreach ( $exceptions as $exception ) {
			$by_date[ (int) $exception['staff_id'] ][ (string) $exception['exception_date'] ][] = $exception;
		}
		$out   = array();
		$first = new \DateTimeImmutable( $from_date, new \DateTimeZone( 'UTC' ) );
		$last  = new \DateTimeImmutable( $to_date, new \DateTimeZone( 'UTC' ) );
		foreach ( $ids as $id ) {
			$carry = array();
			for ( $day = $first->modify( '-1 day' ); $day <= $last; $day = $day->modify( '+1 day' ) ) {
				$date    = $day->format( 'Y-m-d' );
				$working = $carry;
				$carry   = array();
				$blocks  = array();
				foreach ( $by_rule[ $id ][ (int) $day->format( 'w' ) ] ?? array() as $rule ) {
					if ( ( $rule['effective_from'] && $date < $rule['effective_from'] ) || ( $rule['effective_to'] && $date > $rule['effective_to'] ) ) {
						continue;
					}
					$start = self::minutes( (string) $rule['start_time'] );
					$end   = self::minutes( (string) $rule['end_time'] );
					$parts = $end > $start ? array( array( $start, $end ) ) : array( array( $start, 1440 ) );
					if ( $end < $start && $end > 0 && 'working' === $rule['rule_type'] ) {
						$carry[] = array( 0, $end );
					}
					foreach ( $parts as $part ) {
						if ( 'working' === $rule['rule_type'] ) {
							$working[] = $part;
						} elseif ( 'break' === $rule['rule_type'] ) {
							$blocks[] = $part;
						}
					}
				}
				foreach ( $by_date[ $id ][ $date ] ?? array() as $exception ) {
					if ( empty( $exception['start_time'] ) ) {
						if ( 'closed' === $exception['exception_type'] ) {
							$blocks[] = array( 0, 1440 );
						}
						continue;
					}
					$part = array( self::minutes( (string) $exception['start_time'] ), self::minutes( (string) $exception['end_time'] ) ?: 1440 );
					if ( $part[1] <= $part[0] ) {
						continue;
					}
					if ( 'open' === $exception['exception_type'] ) {
						$working[] = $part;
					} elseif ( in_array( $exception['exception_type'], array( 'closed', 'break' ), true ) ) {
						$blocks[] = $part;
					}
				}
				if ( $day < $first ) {
					continue;
				}
				$windows = self::merge( $working );
				foreach ( $blocks as $block ) {
					$windows = self::subtract( $windows, $block );
				}
				if ( $windows ) {
					$out[ $id ][ $date ] = $windows;
				}
			}
		}
		return $out;
	}

	private static function minutes( string $time ): int {
		$parts = array_map( 'intval', explode( ':', $time ) );
		return max( 0, min( 1440, ( $parts[0] ?? 0 ) * 60 + ( $parts[1] ?? 0 ) ) );
	}

	/** @param array<int,array{0:int,1:int}> $windows */
	private static function merge( array $windows ): array {
		usort( $windows, static fn( array $a, array $b ): int => $a[0] <=> $b[0] );
		$out = array();
		foreach ( $windows as $window ) {
			if ( $out && $window[0] <= $out[ count( $out ) - 1 ][1] ) {
				$out[ count( $out ) - 1 ][1] = max( $out[ count( $out ) - 1 ][1], $window[1] );
			} else {
				$out[] = $window;
			}
		}
		return $out;
	}

	/** @param array<int,array{0:int,1:int}> $windows */
	private static function subtract( array $windows, array $block ): array {
		$out = array();
		foreach ( $windows as $window ) {
			if ( $block[1] <= $window[0] || $block[0] >= $window[1] ) {
				$out[] = $window;
				continue;
			}
			if ( $block[0] > $window[0] ) {
				$out[] = array( $window[0], $block[0] );
			}
			if ( $block[1] < $window[1] ) {
				$out[] = array( $block[1], $window[1] );
			}
		}
		return $out;
	}

	/** @param array<int,array{0:int,1:int}> $windows */
	public static function total_minutes( array $windows ): int {
		return array_sum( array_map( static fn( array $window ): int => $window[1] - $window[0], $windows ) );
	}
}