<?php
/**
 * Bookings and booking items.
 *
 * @package Rentbaik
 */

namespace Rentbaik\Booking;

use Rentbaik\Database\Repository;
use Rentbaik\Database\Schema;

defined( 'ABSPATH' ) || exit;

/**
 * Reads and writes the rentbaik_bookings and rentbaik_booking_items tables.
 */
final class BookingRepository extends Repository {

	/**
	 * Short table name.
	 *
	 * @return string
	 */
	protected static function table_name(): string {
		return 'bookings';
	}

	/**
	 * Booking by its public number.
	 *
	 * @param string $number Booking number.
	 * @return array<string, mixed>|null
	 */
	public static function find_by_number( string $number ): ?array {
		$table = self::table();
		$row   = self::remember(
			'number:' . $number,
			static function () use ( $table, $number ) {
				global $wpdb;
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; cached by remember().
				return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE booking_number = %s', $table, $number ), ARRAY_A );
			}
		);
		return is_array( $row ) ? $row : null;
	}

	/**
	 * Items of a booking.
	 *
	 * @param int $booking_id Booking ID.
	 * @return array<int, array<string, mixed>>
	 */
	public static function items( int $booking_id ): array {
		$items = Schema::table( 'booking_items' );
		return (array) self::remember(
			'items:' . $booking_id,
			static function () use ( $items, $booking_id ) {
				global $wpdb;
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; cached by remember().
				return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE booking_id = %d ORDER BY id ASC', $items, $booking_id ), ARRAY_A );
			}
		);
	}

	/**
	 * Add an item to a booking.
	 *
	 * @param array<string, mixed> $item Item data.
	 * @return int
	 */
	public static function add_item( array $item ): int {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table write; cache invalidated by touch().
		$wpdb->insert(
			Schema::table( 'booking_items' ),
			array(
				'booking_id'    => (int) $item['booking_id'],
				'item_type'     => (string) $item['item_type'],
				'vehicle_id'    => (int) ( $item['vehicle_id'] ?? 0 ),
				'unit_id'       => (int) ( $item['unit_id'] ?? 0 ),
				'extra_id'      => (int) ( $item['extra_id'] ?? 0 ),
				'label'         => (string) $item['label'],
				'quantity'      => (int) ( $item['quantity'] ?? 1 ),
				'duration'      => (float) ( $item['duration'] ?? 0 ),
				'duration_unit' => (string) ( $item['duration_unit'] ?? '' ),
				'unit_price'    => (int) ( $item['unit_price'] ?? 0 ),
				'total'         => (int) ( $item['total'] ?? 0 ),
				'meta'          => wp_json_encode( $item['meta'] ?? array() ),
			),
			array( '%d', '%s', '%d', '%d', '%d', '%s', '%d', '%f', '%s', '%d', '%d', '%s' )
		);
		// Read the ID before touch(): touch() writes an option, which changes $wpdb->insert_id.
		$id = (int) $wpdb->insert_id;
		self::touch();
		return $id;
	}

	/**
	 * The vehicle item of a booking (unit, vehicle).
	 *
	 * @param int $booking_id Booking ID.
	 * @return array<string, mixed>|null
	 */
	public static function vehicle_item( int $booking_id ): ?array {
		foreach ( self::items( $booking_id ) as $item ) {
			if ( 'vehicle' === $item['item_type'] ) {
				return $item;
			}
		}
		return null;
	}

	/**
	 * Paginated, filtered booking list for the admin screen.
	 *
	 * @param array<string, mixed> $args status, from, to (UTC, on pick-up), search, location_id, orderby, order, per_page, page.
	 * @return array{rows: array<int, array<string, mixed>>, total: int}
	 */
	public static function query( array $args ): array {
		global $wpdb;
		$table     = self::table();
		$customers = Schema::table( 'customers' );
		$items     = Schema::table( 'booking_items' );
		$units     = Schema::table( 'units' );
		$status    = (string) ( $args['status'] ?? '' );
		$from      = (string) ( $args['from'] ?? '' );
		$to        = (string) ( $args['to'] ?? '' );
		$location  = (int) ( $args['location_id'] ?? 0 );
		$search    = (string) ( $args['search'] ?? '' );
		$like      = '%' . $wpdb->esc_like( $search ) . '%';
		$orderby   = in_array( $args['orderby'] ?? '', array( 'pickup_at', 'return_at', 'created_at', 'total', 'booking_number' ), true ) ? (string) $args['orderby'] : 'pickup_at';
		$desc      = 'ASC' !== strtoupper( (string) ( $args['order'] ?? 'DESC' ) );
		$per_page  = max( 1, (int) ( $args['per_page'] ?? 20 ) );
		$offset    = max( 0, ( (int) ( $args['page'] ?? 1 ) - 1 ) * $per_page );
		$filter    = array( $status, $status, $from, $from, $to, $to, $location, $location, $search, $like, $customers, $like, $like, $like, $like, $items, $units, $like );

		$rows  = self::remember(
			'query:' . wp_json_encode( array( $filter, $orderby, $desc, $per_page, $offset ) ),
			static function () use ( $table, $status, $from, $to, $location, $search, $like, $customers, $items, $units, $orderby, $desc, $per_page, $offset ) {
				global $wpdb;
				if ( $desc ) {
					// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom tables; cached by remember().
					return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i b WHERE ( %s = '' OR b.status = %s ) AND ( %s = '' OR b.pickup_at >= %s ) AND ( %s = '' OR b.pickup_at < %s ) AND ( %d = 0 OR b.pickup_location_id = %d ) AND ( %s = '' OR b.booking_number LIKE %s OR b.customer_id IN ( SELECT c.id FROM %i c WHERE c.first_name LIKE %s OR c.last_name LIKE %s OR c.email LIKE %s OR c.phone LIKE %s ) OR b.id IN ( SELECT i.booking_id FROM %i i JOIN %i u ON u.id = i.unit_id WHERE u.plate_number LIKE %s ) ) ORDER BY %i DESC, b.id DESC LIMIT %d OFFSET %d", $table, $status, $status, $from, $from, $to, $to, $location, $location, $search, $like, $customers, $like, $like, $like, $like, $items, $units, $like, $orderby, $per_page, $offset ), ARRAY_A );
				}
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom tables; cached by remember().
				return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i b WHERE ( %s = '' OR b.status = %s ) AND ( %s = '' OR b.pickup_at >= %s ) AND ( %s = '' OR b.pickup_at < %s ) AND ( %d = 0 OR b.pickup_location_id = %d ) AND ( %s = '' OR b.booking_number LIKE %s OR b.customer_id IN ( SELECT c.id FROM %i c WHERE c.first_name LIKE %s OR c.last_name LIKE %s OR c.email LIKE %s OR c.phone LIKE %s ) OR b.id IN ( SELECT i.booking_id FROM %i i JOIN %i u ON u.id = i.unit_id WHERE u.plate_number LIKE %s ) ) ORDER BY %i ASC, b.id ASC LIMIT %d OFFSET %d", $table, $status, $status, $from, $from, $to, $to, $location, $location, $search, $like, $customers, $like, $like, $like, $like, $items, $units, $like, $orderby, $per_page, $offset ), ARRAY_A );
			}
		);
		$total = self::remember(
			'count:' . wp_json_encode( $filter ),
			static function () use ( $table, $status, $from, $to, $location, $search, $like, $customers, $items, $units ) {
				global $wpdb;
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom tables; cached by remember().
				return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i b WHERE ( %s = '' OR b.status = %s ) AND ( %s = '' OR b.pickup_at >= %s ) AND ( %s = '' OR b.pickup_at < %s ) AND ( %d = 0 OR b.pickup_location_id = %d ) AND ( %s = '' OR b.booking_number LIKE %s OR b.customer_id IN ( SELECT c.id FROM %i c WHERE c.first_name LIKE %s OR c.last_name LIKE %s OR c.email LIKE %s OR c.phone LIKE %s ) OR b.id IN ( SELECT i.booking_id FROM %i i JOIN %i u ON u.id = i.unit_id WHERE u.plate_number LIKE %s ) )", $table, $status, $status, $from, $from, $to, $to, $location, $location, $search, $like, $customers, $like, $like, $like, $like, $items, $units, $like ) );
			}
		);

		return array(
			'rows'  => (array) $rows,
			'total' => (int) $total,
		);
	}

	/**
	 * Number of bookings per status.
	 *
	 * @return array<string, int>
	 */
	public static function count_by_status(): array {
		$table  = self::table();
		$rows   = self::remember(
			'status_counts',
			static function () use ( $table ) {
				global $wpdb;
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; cached by remember().
				return $wpdb->get_results( $wpdb->prepare( 'SELECT status, COUNT(*) AS total FROM %i GROUP BY status', $table ), ARRAY_A );
			}
		);
		$counts = array();
		foreach ( (array) $rows as $row ) {
			$counts[ $row['status'] ] = (int) $row['total'];
		}
		return $counts;
	}

	/**
	 * Bookings whose pick-up (or return) falls in a UTC period, filtered by statuses.
	 *
	 * @param string   $column   "pickup_at" or "return_at".
	 * @param string   $from     UTC start.
	 * @param string   $to       UTC end.
	 * @param string[] $statuses Statuses to include.
	 * @param int      $location Location filter (0 = all).
	 * @return array<int, array<string, mixed>>
	 */
	public static function between( string $column, string $from, string $to, array $statuses, int $location = 0 ): array {
		$table  = self::table();
		$column = 'return_at' === $column ? 'return_at' : 'pickup_at';
		$list   = implode( ',', array_map( 'sanitize_key', $statuses ) );
		return (array) self::remember(
			'between:' . wp_json_encode( array( $column, $from, $to, $list, $location ) ),
			static function () use ( $table, $column, $from, $to, $list, $location ) {
				global $wpdb;
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; cached by remember().
				return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE %i >= %s AND %i < %s AND FIND_IN_SET( status, %s ) AND ( %d = 0 OR pickup_location_id = %d ) ORDER BY %i ASC', $table, $column, $from, $column, $to, $list, $location, $location, $column ), ARRAY_A );
			}
		);
	}

	/**
	 * Bookings that overlap a UTC period (calendar), excluding cancelled and no-show.
	 *
	 * @param string $from UTC start.
	 * @param string $to   UTC end.
	 * @return array<int, array<string, mixed>>
	 */
	public static function overlapping( string $from, string $to ): array {
		$table = self::table();
		return (array) self::remember(
			'overlap:' . $from . ':' . $to,
			static function () use ( $table, $from, $to ) {
				global $wpdb;
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; cached by remember().
				return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE pickup_at < %s AND return_at > %s AND status NOT IN ( 'cancelled', 'no_show' ) ORDER BY pickup_at ASC, return_at DESC", $table, $to, $from ), ARRAY_A );
			}
		);
	}

	/**
	 * Vehicles that are out and should already be back.
	 *
	 * @param string $now      UTC now.
	 * @param int    $location Location filter.
	 * @return array<int, array<string, mixed>>
	 */
	public static function overdue( string $now, int $location = 0 ): array {
		$table = self::table();
		return (array) self::remember(
			'overdue:' . substr( $now, 0, 16 ) . ':' . $location,
			static function () use ( $table, $now, $location ) {
				global $wpdb;
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; cached by remember().
				return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE status = 'active' AND return_at < %s AND ( %d = 0 OR pickup_location_id = %d ) ORDER BY return_at ASC", $table, $now, $location, $location ), ARRAY_A );
			}
		);
	}

	/**
	 * Bookings that still owe money (confirmed, active or returned).
	 *
	 * @param int $location Location filter.
	 * @return array<int, array<string, mixed>>
	 */
	public static function unpaid( int $location = 0 ): array {
		$table = self::table();
		return (array) self::remember(
			'unpaid:' . $location,
			static function () use ( $table, $location ) {
				global $wpdb;
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; cached by remember().
				return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE status IN ( 'pending', 'confirmed', 'active', 'returned' ) AND paid_total < total AND ( %d = 0 OR pickup_location_id = %d ) ORDER BY pickup_at ASC LIMIT 50", $table, $location, $location ), ARRAY_A );
			}
		);
	}

	/**
	 * Bookings that need a reminder email now.
	 *
	 * @param string $type "pickup" or "return".
	 * @param string $now  UTC now.
	 * @param string $until UTC end of the reminder window.
	 * @return array<int, array<string, mixed>>
	 */
	public static function reminders_due( string $type, string $now, string $until ): array {
		global $wpdb;
		if ( 'return' === $type ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Scheduled job on live data.
			return (array) $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE status = 'active' AND return_reminder_sent = 0 AND return_at > %s AND return_at <= %s LIMIT 100", self::table(), $now, $until ), ARRAY_A );
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Scheduled job on live data.
		return (array) $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE status = 'confirmed' AND pickup_reminder_sent = 0 AND pickup_at > %s AND pickup_at <= %s LIMIT 100", self::table(), $now, $until ), ARRAY_A );
	}

	/**
	 * Quantity of an extra already booked in an overlapping period (live data).
	 *
	 * @param int    $extra_id Extra ID.
	 * @param string $start    UTC start.
	 * @param string $end      UTC end.
	 * @return int
	 */
	public static function extra_quantity_between( int $extra_id, string $start, string $end ): int {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Stock check must read live data.
		return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COALESCE( SUM( i.quantity ), 0 ) FROM %i i JOIN %i b ON b.id = i.booking_id WHERE i.extra_id = %d AND b.status IN ( 'pending', 'confirmed', 'active' ) AND b.pickup_at < %s AND b.return_at > %s", Schema::table( 'booking_items' ), self::table(), $extra_id, $end, $start ) );
	}

	/**
	 * Bookings of a customer.
	 *
	 * @param int $customer_id Customer ID.
	 * @return array<int, array<string, mixed>>
	 */
	public static function for_customer( int $customer_id ): array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Small per-customer read for admin and privacy tools.
		return (array) $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE customer_id = %d ORDER BY pickup_at DESC', self::table(), $customer_id ), ARRAY_A );
	}

	/**
	 * Save columns of a booking.
	 *
	 * @param int                  $id   Booking ID.
	 * @param array<string, mixed> $data Column => value (strings and integers only).
	 * @return void
	 */
	public static function patch( int $id, array $data ): void {
		$formats = array();
		foreach ( $data as $value ) {
			$formats[] = is_int( $value ) ? '%d' : '%s';
		}
		$data['updated_at'] = self::now();
		$formats[]          = '%s';
		self::update( $id, $data, $formats );
	}

	/**
	 * Remove the checkout hold expiry of a booking (set the column to NULL).
	 *
	 * @param int $id Booking ID.
	 * @return void
	 */
	public static function clear_hold_expiry( int $id ): void {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table write; cache invalidated by touch().
		$wpdb->query( $wpdb->prepare( 'UPDATE %i SET hold_expires_at = NULL, updated_at = %s WHERE id = %d', self::table(), self::now(), $id ) );
		self::touch();
	}

	/**
	 * Delete the items of a booking.
	 *
	 * @param int $booking_id Booking ID.
	 * @return void
	 */
	public static function delete_items( int $booking_id ): void {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table write; cache invalidated by touch().
		$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE booking_id = %d', Schema::table( 'booking_items' ), $booking_id ) );
		self::touch();
	}

	/**
	 * IDs of the bookings that rent a vehicle model.
	 *
	 * @param int $vehicle_id Vehicle post ID.
	 * @return int[]
	 */
	public static function ids_for_vehicle( int $vehicle_id ): array {
		$items = Schema::table( 'booking_items' );
		$ids   = self::remember(
			'vehicle-bookings:' . $vehicle_id,
			static function () use ( $items, $vehicle_id ) {
				global $wpdb;
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; cached by remember().
				return $wpdb->get_col( $wpdb->prepare( 'SELECT DISTINCT booking_id FROM %i WHERE vehicle_id = %d', $items, $vehicle_id ) );
			}
		);
		return array_map( 'intval', (array) $ids );
	}

	/**
	 * Number of upcoming waiting or confirmed bookings that have no unit yet.
	 *
	 * @return int
	 */
	public static function count_unassigned(): int {
		$table = self::table();
		$items = Schema::table( 'booking_items' );
		$now   = self::now();
		return (int) self::remember(
			'unassigned',
			static function () use ( $table, $items, $now ) {
				global $wpdb;
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom tables; cached by remember().
				return $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(DISTINCT b.id) FROM %i b JOIN %i i ON i.booking_id = b.id AND i.item_type = 'vehicle' WHERE i.unit_id = 0 AND b.status IN ( 'pending', 'confirmed' ) AND b.return_at > %s", $table, $items, $now ) );
			}
		);
	}

	/**
	 * The rental under way on a unit (picked up, not returned yet), if any.
	 *
	 * @param int $unit_id Unit ID.
	 * @return array<string, mixed>|null Booking row.
	 */
	public static function active_for_unit( int $unit_id ): ?array {
		$table = self::table();
		$items = Schema::table( 'booking_items' );
		$row   = self::remember(
			'active-unit:' . $unit_id,
			static function () use ( $table, $items, $unit_id ) {
				global $wpdb;
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom tables; cached by remember().
				return $wpdb->get_row( $wpdb->prepare( "SELECT b.* FROM %i b JOIN %i i ON i.booking_id = b.id AND i.item_type = 'vehicle' WHERE i.unit_id = %d AND b.status = 'active' ORDER BY b.return_at ASC LIMIT 1", $table, $items, $unit_id ), ARRAY_A );
			}
		);
		return is_array( $row ) ? $row : null;
	}
}