<?php
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

class BingoCR_DB {

	// ── CARS ─────────────────────────────────────────────────────────────

	public static function get_cars( array $args = array() ): array {
		global $wpdb;
		$table  = esc_sql( $wpdb->prefix . 'bingocr_cars' );
		$where  = array( '1=1' );
		$values = array();

		if ( ! empty( $args['status'] ) ) {
			$where[]  = 'status = %s';
			$values[] = $args['status'];
		}
		if ( ! empty( $args['category'] ) ) {
			$where[]  = 'category = %s';
			$values[] = $args['category'];
		}

		$where_sql = implode( ' AND ', $where );
		$order     = 'ORDER BY sort_order ASC, id ASC';

		if ( empty( $values ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			return $wpdb->get_results( "SELECT * FROM `$table` WHERE $where_sql $order", ARRAY_A ) ?: array();
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
		return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM `$table` WHERE $where_sql $order", $values ), ARRAY_A ) ?: array();
	}

	public static function get_car( int $id ): ?array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$row = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM `{$wpdb->prefix}bingocr_cars` WHERE id = %d", $id ),
			ARRAY_A
		);
		return $row ?: null;
	}

	public static function get_available_cars( string $pickup_date, string $return_date, string $category = '' ): array {
		global $wpdb;
		$cars_table = esc_sql( $wpdb->prefix . 'bingocr_cars' );
		$res_table  = esc_sql( $wpdb->prefix . 'bingocr_reservations' );

		if ( $category !== '' ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			return $wpdb->get_results( $wpdb->prepare( "SELECT c.*, ( c.quantity - COALESCE( ( SELECT COUNT(*) FROM `$res_table` r WHERE r.car_id = c.id AND r.status IN ('pending','confirmed','active') AND r.pickup_date < %s AND r.return_date > %s ), 0 ) ) AS available_units FROM `$cars_table` c WHERE c.status = 'available' AND c.category = %s HAVING available_units > 0 ORDER BY c.sort_order ASC, c.price_per_day ASC", $return_date, $pickup_date, $category ), ARRAY_A ) ?: array();
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return $wpdb->get_results( $wpdb->prepare( "SELECT c.*, ( c.quantity - COALESCE( ( SELECT COUNT(*) FROM `$res_table` r WHERE r.car_id = c.id AND r.status IN ('pending','confirmed','active') AND r.pickup_date < %s AND r.return_date > %s ), 0 ) ) AS available_units FROM `$cars_table` c WHERE c.status = 'available' HAVING available_units > 0 ORDER BY c.sort_order ASC, c.price_per_day ASC", $return_date, $pickup_date ), ARRAY_A ) ?: array();
	}

	public static function get_car_categories(): array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_results( "SELECT category, COUNT(*) as count FROM `{$wpdb->prefix}bingocr_cars` WHERE status = 'available' GROUP BY category ORDER BY category ASC", ARRAY_A ) ?: array();
	}

	public static function insert_car( array $data ): int {
		global $wpdb;
		if ( isset( $data['features'] ) && is_array( $data['features'] ) ) {
			$data['features'] = wp_json_encode( $data['features'] );
		}
		$data['created_at'] = current_time( 'mysql' );
		$data['updated_at'] = current_time( 'mysql' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$wpdb->insert( $wpdb->prefix . 'bingocr_cars', $data );
		return (int) $wpdb->insert_id;
	}

	public static function update_car( int $id, array $data ): bool {
		global $wpdb;
		if ( isset( $data['features'] ) && is_array( $data['features'] ) ) {
			$data['features'] = wp_json_encode( $data['features'] );
		}
		$data['updated_at'] = current_time( 'mysql' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (bool) $wpdb->update( $wpdb->prefix . 'bingocr_cars', $data, array( 'id' => $id ) );
	}

	public static function delete_car( int $id ): bool {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (bool) $wpdb->delete( $wpdb->prefix . 'bingocr_cars', array( 'id' => $id ), array( '%d' ) );
	}

	// ── RESERVATIONS ─────────────────────────────────────────────────────

	public static function get_reservations( array $args = array() ): array {
		global $wpdb;
		$table  = esc_sql( $wpdb->prefix . 'bingocr_reservations' );
		$where  = array( '1=1' );
		$values = array();

		if ( ! empty( $args['status'] ) ) {
			// Must be qualified — the joined cars table also has a status column.
			$where[]  = 'r.status = %s';
			$values[] = $args['status'];
		}
		if ( ! empty( $args['search'] ) ) {
			$where[]  = '(r.customer_name LIKE %s OR r.customer_email LIKE %s OR r.reservation_number LIKE %s)';
			$like     = '%' . $wpdb->esc_like( $args['search'] ) . '%';
			$values[] = $like;
			$values[] = $like;
			$values[] = $like;
		}

		$where_sql = implode( ' AND ', $where );
		$limit     = isset( $args['limit'] ) ? (int) $args['limit'] : 50;
		$offset    = isset( $args['offset'] ) ? (int) $args['offset'] : 0;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
		$bingocr_rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT r.*, c.brand, c.model FROM `$table` r
				 LEFT JOIN `{$wpdb->prefix}bingocr_cars` c ON c.id = r.car_id
				 WHERE $where_sql
				 ORDER BY r.created_at DESC
				 LIMIT %d OFFSET %d",
				array_merge( $values, array( $limit, $offset ) )
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
		return $bingocr_rows ?: array();
	}

	public static function get_reservation( int $id ): ?array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT r.*, c.brand, c.model, c.image_url, c.category, c.transmission, c.fuel_type, c.seats,
					pl.name AS pickup_location_name, dl.name AS dropoff_location_name
				 FROM `{$wpdb->prefix}bingocr_reservations` r
				 LEFT JOIN `{$wpdb->prefix}bingocr_cars` c ON c.id = r.car_id
				 LEFT JOIN `{$wpdb->prefix}bingocr_locations` pl ON pl.id = r.pickup_location_id
				 LEFT JOIN `{$wpdb->prefix}bingocr_locations` dl ON dl.id = r.dropoff_location_id
				 WHERE r.id = %d",
				$id
			),
			ARRAY_A
		);
		return $row ?: null;
	}

	public static function get_reservation_by_number( string $number ): ?array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$row = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM `{$wpdb->prefix}bingocr_reservations` WHERE reservation_number = %s", $number ),
			ARRAY_A
		);
		return $row ?: null;
	}

	public static function insert_reservation( array $data ): int {
		global $wpdb;
		$data['reservation_number'] = self::generate_reservation_number();
		if ( isset( $data['extras'] ) && is_array( $data['extras'] ) ) {
			$data['extras'] = wp_json_encode( $data['extras'] );
		}
		$data['created_at'] = current_time( 'mysql' );
		$data['updated_at'] = current_time( 'mysql' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$wpdb->insert( $wpdb->prefix . 'bingocr_reservations', $data );
		return (int) $wpdb->insert_id;
	}

	public static function update_reservation( int $id, array $data ): bool {
		global $wpdb;
		$data['updated_at'] = current_time( 'mysql' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (bool) $wpdb->update( $wpdb->prefix . 'bingocr_reservations', $data, array( 'id' => $id ) );
	}

	// ── PAYMENTS ─────────────────────────────────────────────────────────

	public static function insert_payment( array $data ): int {
		global $wpdb;
		$data['created_at'] = current_time( 'mysql' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$wpdb->insert( $wpdb->prefix . 'bingocr_payments', $data );
		return (int) $wpdb->insert_id;
	}

	public static function get_reservation_payment( int $reservation_id ): ?array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM `{$wpdb->prefix}bingocr_payments`
				 WHERE reservation_id = %d
				 ORDER BY ( status = 'completed' ) DESC, id DESC
				 LIMIT 1",
				$reservation_id
			),
			ARRAY_A
		) ?: null;
	}

	public static function get_payment_by_order( string $order_id ): ?array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM `{$wpdb->prefix}bingocr_payments` WHERE paypal_order_id = %s", $order_id ),
			ARRAY_A
		) ?: null;
	}

	public static function get_payment_by_capture( string $capture_id ): ?array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM `{$wpdb->prefix}bingocr_payments` WHERE paypal_capture_id = %s", $capture_id ),
			ARRAY_A
		) ?: null;
	}

	public static function update_payment( int $id, array $data ): bool {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (bool) $wpdb->update( $wpdb->prefix . 'bingocr_payments', $data, array( 'id' => $id ) );
	}

	/**
	 * Acquire a per-car advisory lock (MySQL GET_LOCK) so the availability check and
	 * the insert run as one critical section — preventing two concurrent bookings from
	 * both passing the capacity check. Connection-scoped, so it auto-frees at request
	 * end even if release is missed.
	 *
	 * @return bool|null  true = locked; false = timed out (real contention — caller
	 *                    should fail closed); null = lock unavailable on this host
	 *                    (caller proceeds without it rather than blocking all bookings).
	 */
	public static function acquire_booking_lock( int $car_id, int $timeout = 5 ): ?bool {
		global $wpdb;
		$key = $wpdb->prefix . 'bingocr_book_' . $car_id;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$res = $wpdb->get_var( $wpdb->prepare( 'SELECT GET_LOCK(%s, %d)', $key, $timeout ) );
		if ( null === $res ) {
			return null; // error / not supported — degrade gracefully
		}
		return '1' === (string) $res; // '1' = acquired, '0' = timed out (contention)
	}

	public static function release_booking_lock( int $car_id ): void {
		global $wpdb;
		$key = $wpdb->prefix . 'bingocr_book_' . $car_id;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->get_var( $wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', $key ) );
	}

	public static function get_booked_count( int $car_id, string $pickup, string $return_date, int $exclude_id = 0 ): int {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM `{$wpdb->prefix}bingocr_reservations`
				 WHERE car_id = %d
				 AND status IN ('pending','confirmed','active')
				 AND pickup_date < %s
				 AND return_date > %s
				 AND id != %d",
				$car_id, $return_date, $pickup, $exclude_id
			)
		);
	}

	public static function count_reservations( string $status = '' ): int {
		global $wpdb;
		if ( $status ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			return (int) $wpdb->get_var(
				$wpdb->prepare( "SELECT COUNT(*) FROM `{$wpdb->prefix}bingocr_reservations` WHERE status = %s", $status )
			);
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$wpdb->prefix}bingocr_reservations`" );
	}

	public static function get_revenue_total(): float {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (float) $wpdb->get_var( "SELECT COALESCE(SUM(total_price),0) FROM `{$wpdb->prefix}bingocr_reservations` WHERE status IN ('confirmed','active','completed')" );
	}

	// ── LOCATIONS ────────────────────────────────────────────────────────

	public static function get_locations( bool $active_only = true ): array {
		global $wpdb;
		if ( $active_only ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			return $wpdb->get_results(
				$wpdb->prepare( "SELECT * FROM `{$wpdb->prefix}bingocr_locations` WHERE status = %s ORDER BY sort_order ASC, name ASC", 'active' ),
				ARRAY_A
			) ?: array();
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_results( "SELECT * FROM `{$wpdb->prefix}bingocr_locations` ORDER BY sort_order ASC, name ASC", ARRAY_A ) ?: array();
	}

	public static function get_location( int $id ): ?array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM `{$wpdb->prefix}bingocr_locations` WHERE id = %d", $id ),
			ARRAY_A
		) ?: null;
	}

	public static function insert_location( array $data ): int {
		global $wpdb;
		$data['created_at'] = current_time( 'mysql' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$wpdb->insert( $wpdb->prefix . 'bingocr_locations', $data );
		return (int) $wpdb->insert_id;
	}

	public static function update_location( int $id, array $data ): bool {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (bool) $wpdb->update( $wpdb->prefix . 'bingocr_locations', $data, array( 'id' => $id ) );
	}

	public static function delete_location( int $id ): bool {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (bool) $wpdb->delete( $wpdb->prefix . 'bingocr_locations', array( 'id' => $id ), array( '%d' ) );
	}

	// ── EXTRAS ───────────────────────────────────────────────────────────

	public static function get_extras( bool $active_only = true ): array {
		global $wpdb;
		if ( $active_only ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			return $wpdb->get_results(
				$wpdb->prepare( "SELECT * FROM `{$wpdb->prefix}bingocr_extras` WHERE status = %s ORDER BY sort_order ASC, name ASC", 'active' ),
				ARRAY_A
			) ?: array();
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_results( "SELECT * FROM `{$wpdb->prefix}bingocr_extras` ORDER BY sort_order ASC, name ASC", ARRAY_A ) ?: array();
	}

	public static function get_extra( int $id ): ?array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM `{$wpdb->prefix}bingocr_extras` WHERE id = %d", $id ),
			ARRAY_A
		) ?: null;
	}

	public static function insert_extra( array $data ): int {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$wpdb->insert( $wpdb->prefix . 'bingocr_extras', $data );
		return (int) $wpdb->insert_id;
	}

	public static function update_extra( int $id, array $data ): bool {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (bool) $wpdb->update( $wpdb->prefix . 'bingocr_extras', $data, array( 'id' => $id ) );
	}

	public static function delete_extra( int $id ): bool {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (bool) $wpdb->delete( $wpdb->prefix . 'bingocr_extras', array( 'id' => $id ), array( '%d' ) );
	}

	// ── DISCOUNTS ────────────────────────────────────────────────────────

	public static function get_discounts(): array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_results( "SELECT * FROM `{$wpdb->prefix}bingocr_discounts` ORDER BY id DESC", ARRAY_A ) ?: array();
	}

	public static function get_discount( int $id ): ?array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM `{$wpdb->prefix}bingocr_discounts` WHERE id = %d", $id ), ARRAY_A ) ?: null;
	}

	public static function insert_discount( array $data ): int {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$wpdb->insert( $wpdb->prefix . 'bingocr_discounts', $data );
		return (int) $wpdb->insert_id;
	}

	public static function update_discount( int $id, array $data ): bool {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (bool) $wpdb->update( $wpdb->prefix . 'bingocr_discounts', $data, array( 'id' => $id ) );
	}

	public static function delete_discount( int $id ): bool {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (bool) $wpdb->delete( $wpdb->prefix . 'bingocr_discounts', array( 'id' => $id ), array( '%d' ) );
	}

	public static function validate_discount( string $code, int $rental_days ): ?array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM `{$wpdb->prefix}bingocr_discounts`
				 WHERE code = %s
				 AND status = 'active'
				 AND (expires_at IS NULL OR expires_at >= %s)
				 AND (max_uses IS NULL OR used_count < max_uses)
				 AND min_days <= %d",
				$code,
				gmdate( 'Y-m-d' ),
				$rental_days
			),
			ARRAY_A
		);
		return $row ?: null;
	}

	public static function increment_discount_usage( int $id ): void {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->query(
			$wpdb->prepare(
				"UPDATE `{$wpdb->prefix}bingocr_discounts` SET used_count = used_count + 1 WHERE id = %d",
				$id
			)
		);
	}

	// ── SETTINGS ─────────────────────────────────────────────────────────

	public static function get_settings(): array {
		$defaults = array(
			'company_name'         => get_bloginfo( 'name' ),
			'currency'             => 'EUR',
			'currency_symbol'      => '€',
			'currency_position'    => 'before',
			'date_format'          => 'DD/MM/YYYY',
			'min_rental_days'      => 1,
			'max_rental_days'      => 90,
			'advance_booking_days' => 365,
			'default_deposit'      => 0,
			'notify_email'         => get_option( 'admin_email' ),
			'from_name'            => get_bloginfo( 'name' ),
			'from_email'           => get_option( 'admin_email' ),
			'language'             => 'en',
			'auto_detect_language' => false,
			'show_attribution'     => false,
			'theme'                => 'light',
			'primary_color'        => '#e63946',
			'business_hours_start' => '09:00',
			'business_hours_end'   => '18:00',
			'out_of_hours_enabled' => false,
			'out_of_hours_fee'     => 25,
			'terms_conditions'     => '',
			// Payment methods (offline are free; PayPal is Pro and wired in a later phase).
			'payment_cash'         => true,
			'payment_card'         => false,
			'payment_bank'         => false,
			'payment_instructions' => '',
			'paypal_enabled'       => false,
			'paypal_mode'          => 'sandbox',
			'paypal_client_id'     => '',
			'paypal_client_secret' => '',
			'paypal_webhook_id'    => '',
		);
		$saved = get_option( 'bingocr_settings', array() );
		return array_merge( $defaults, $saved );
	}

	public static function update_settings( array $data ): bool {
		$current = self::get_settings();
		$updated = array_merge( $current, $data );
		return update_option( 'bingocr_settings', $updated );
	}

	// ── SEASONAL PRICES ──────────────────────────────────────────────────

	public static function get_seasonal_prices( bool $active_only = false ): array {
		global $wpdb;
		if ( $active_only ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			return $wpdb->get_results(
				$wpdb->prepare( "SELECT * FROM `{$wpdb->prefix}bingocr_seasonal_prices` WHERE status = %s ORDER BY priority DESC, start_date ASC", 'active' ),
				ARRAY_A
			) ?: array();
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_results( "SELECT * FROM `{$wpdb->prefix}bingocr_seasonal_prices` ORDER BY priority DESC, start_date ASC", ARRAY_A ) ?: array();
	}

	public static function get_seasonal_price( int $id ): ?array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM `{$wpdb->prefix}bingocr_seasonal_prices` WHERE id = %d", $id ),
			ARRAY_A
		) ?: null;
	}

	public static function get_seasonal_prices_for_date( string $date ): array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM `{$wpdb->prefix}bingocr_seasonal_prices`
				 WHERE status = 'active' AND start_date <= %s AND end_date >= %s
				 ORDER BY priority DESC",
				$date, $date
			),
			ARRAY_A
		) ?: array();
	}

	public static function insert_seasonal_price( array $data ): int {
		global $wpdb;
		$data['created_at'] = current_time( 'mysql' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$wpdb->insert( $wpdb->prefix . 'bingocr_seasonal_prices', $data );
		return (int) $wpdb->insert_id;
	}

	public static function update_seasonal_price( int $id, array $data ): bool {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (bool) $wpdb->update( $wpdb->prefix . 'bingocr_seasonal_prices', $data, array( 'id' => $id ) );
	}

	public static function delete_seasonal_price( int $id ): bool {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (bool) $wpdb->delete( $wpdb->prefix . 'bingocr_seasonal_prices', array( 'id' => $id ), array( '%d' ) );
	}

	// ── PERIOD PRICING ────────────────────────────────────────────────────

	public static function get_period_pricing( bool $active_only = false ): array {
		global $wpdb;
		if ( $active_only ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			return $wpdb->get_results(
				$wpdb->prepare( "SELECT * FROM `{$wpdb->prefix}bingocr_period_pricing` WHERE status = %s ORDER BY priority DESC, min_days ASC", 'active' ),
				ARRAY_A
			) ?: array();
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_results( "SELECT * FROM `{$wpdb->prefix}bingocr_period_pricing` ORDER BY priority DESC, min_days ASC", ARRAY_A ) ?: array();
	}

	public static function get_period_price( int $id ): ?array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM `{$wpdb->prefix}bingocr_period_pricing` WHERE id = %d", $id ),
			ARRAY_A
		) ?: null;
	}

	public static function get_period_pricing_for_days( int $days ): array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM `{$wpdb->prefix}bingocr_period_pricing`
				 WHERE status = 'active' AND min_days <= %d AND max_days >= %d
				 ORDER BY priority DESC",
				$days, $days
			),
			ARRAY_A
		) ?: array();
	}

	public static function insert_period_price( array $data ): int {
		global $wpdb;
		$data['created_at'] = current_time( 'mysql' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$wpdb->insert( $wpdb->prefix . 'bingocr_period_pricing', $data );
		return (int) $wpdb->insert_id;
	}

	public static function update_period_price( int $id, array $data ): bool {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (bool) $wpdb->update( $wpdb->prefix . 'bingocr_period_pricing', $data, array( 'id' => $id ) );
	}

	public static function delete_period_price( int $id ): bool {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (bool) $wpdb->delete( $wpdb->prefix . 'bingocr_period_pricing', array( 'id' => $id ), array( '%d' ) );
	}

	// ── HELPERS ──────────────────────────────────────────────────────────

	private static function generate_reservation_number(): string {
		$prefix = strtoupper( substr( get_bloginfo( 'name' ), 0, 2 ) );
		do {
			$number = $prefix . strtoupper( wp_generate_password( 6, false ) );
		} while ( self::get_reservation_by_number( $number ) );
		return $number;
	}
}
