<?php

declare( strict_types=1 );

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

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

/**
 * Handles all CRUD operations for the playard_fields table.
 */
class Playard_Field {

	/**
	 * Always returns true — no field limit on free or Pro.
	 *
	 * @param int $facility_id  Unused, kept for signature compatibility.
	 */
	public function can_add_field( int $facility_id = 0 ): bool {
		return true;
	}

	/**
	 * Updates sort_order for multiple fields in one pass.
	 * $id_order is an array of field IDs in the desired order; index becomes sort_order.
	 * Only updates fields that belong to the given facility.
	 *
	 * @param int   $facility_id
	 * @param int[] $id_order
	 */
	public function batch_sort( int $facility_id, array $id_order ): void {
		global $wpdb;

		foreach ( $id_order as $position => $field_id ) {
			$wpdb->update(
				$wpdb->prefix . 'playard_fields',
				[ 'sort_order' => $position ],
				[ 'id' => (int) $field_id, 'facility_id' => $facility_id ],
				[ '%d' ],
				[ '%d', '%d' ]
			);
		}
	}

	/**
	 * Returns the next sort_order value for a new field in a facility.
	 */
	public function get_next_sort_order( int $facility_id ): int {
		global $wpdb;

		$max = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COALESCE( MAX(sort_order), 0 ) FROM {$wpdb->prefix}playard_fields
				 WHERE facility_id = %d",
				$facility_id
			)
		);

		return $max + 1;
	}

	/**
	 * Returns all fields for a facility ordered by sort_order.
	 * Pass $status to filter; empty string returns all statuses.
	 *
	 * @return array<object>
	 */
	public function get_all( int $facility_id, string $status = '' ): array {
		global $wpdb;

		if ( $status !== '' ) {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT * FROM {$wpdb->prefix}playard_fields
					 WHERE facility_id = %d AND status = %s
					 ORDER BY sort_order ASC, id ASC",
					$facility_id,
					$status
				)
			);
		} else {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT * FROM {$wpdb->prefix}playard_fields
					 WHERE facility_id = %d
					 ORDER BY sort_order ASC, id ASC",
					$facility_id
				)
			);
		}

		return $rows ?: [];
	}

	// ── Blocked slots ─────────────────────────────────────────────────────────

	/**
	 * Returns blocked slots for a field, optionally filtered by date.
	 *
	 * @return array<object>
	 */
	public function get_blocks( int $field_id, string $date = '' ): array {
		global $wpdb;

		if ( $date !== '' ) {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT * FROM {$wpdb->prefix}playard_blocked_slots
					 WHERE field_id = %d AND blocked_date = %s
					 ORDER BY start_time ASC",
					$field_id, $date
				)
			);
		} else {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT * FROM {$wpdb->prefix}playard_blocked_slots
					 WHERE field_id = %d
					 ORDER BY blocked_date ASC, start_time ASC",
					$field_id
				)
			);
		}

		return $rows ?: [];
	}

	/**
	 * Inserts a blocked slot record.
	 *
	 * @param  array<string,mixed> $data  Keys: date, start_time, end_time, reason
	 * @return int|false  Inserted ID or false on DB error.
	 */
	public function add_block( int $field_id, array $data ): int|false {
		global $wpdb;

		$result = $wpdb->insert(
			$wpdb->prefix . 'playard_blocked_slots',
			[
				'field_id'     => $field_id,
				'blocked_date' => sanitize_text_field( (string) $data['date'] ),
				'start_time'   => sanitize_text_field( (string) $data['start_time'] ),
				'end_time'     => sanitize_text_field( (string) $data['end_time'] ),
				'reason'       => sanitize_text_field( (string) ( $data['reason'] ?? '' ) ),
			],
			[ '%d', '%s', '%s', '%s', '%s' ]
		);

		if ( false === $result ) {
			return false;
		}

		$block_id = (int) $wpdb->insert_id;
		do_action( 'playard_slot_blocked', $block_id, $field_id, $data );
		return $block_id;
	}

	/**
	 * Deletes a blocked slot. Verifies field ownership before deleting.
	 * Returns 'not_found', 'forbidden', false, or true.
	 *
	 * @return true|false|string
	 */
	public function remove_block( int $block_id, int $field_id ): bool|string {
		global $wpdb;

		$block = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}playard_blocked_slots WHERE id = %d LIMIT 1",
				$block_id
			)
		);

		if ( ! $block ) {
			return 'not_found';
		}

		if ( (int) $block->field_id !== $field_id ) {
			return 'forbidden';
		}

		$result = $wpdb->delete(
			$wpdb->prefix . 'playard_blocked_slots',
			[ 'id' => $block_id ],
			[ '%d' ]
		);

		if ( false === $result ) {
			return false;
		}

		do_action( 'playard_slot_unblocked', $block_id, $field_id );
		return true;
	}

	/**
	 * Returns true if the given time range overlaps any block for the field on $date.
	 * Overlap condition: block.start_time < slot_end AND block.end_time > slot_start.
	 */
	public function is_slot_blocked( int $field_id, string $date, string $start, string $end ): bool {
		global $wpdb;

		$count = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$wpdb->prefix}playard_blocked_slots
				 WHERE field_id = %d AND blocked_date = %s
				 AND start_time < %s AND end_time > %s",
				$field_id, $date, $end, $start
			)
		);

		return $count > 0;
	}

	// ──────────────────────────────────────────────────────────────────────────

	/**
	 * @return object|null
	 */
	public function get( int $id ): ?object {
		global $wpdb;

		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}playard_fields WHERE id = %d LIMIT 1",
				$id
			)
		);

		return $row ?: null;
	}

	/**
	 * @param array<string,mixed> $data
	 * @return int|false
	 */
	public function create( array $data ): int|false {
		global $wpdb;

		$facility_id = absint( $data['facility_id'] );

		$result = $wpdb->insert(
			$wpdb->prefix . 'playard_fields',
			[
				'facility_id'   => $facility_id,
				'name'          => sanitize_text_field( $data['name'] ),
				'description'   => sanitize_textarea_field( $data['description'] ?? '' ),
				'sports'        => wp_json_encode( $this->filter_sports( (array) ( $data['sports'] ?? [] ) ) ),
				'photos'        => wp_json_encode( [] ),
				'peak_hours'    => wp_json_encode( [] ),
				'capacity'      => absint( $data['capacity'] ?? 0 ),
				'slot_duration' => $this->valid_slot_duration( $data['slot_duration'] ?? 60 ),
				'buffer_time'   => $this->valid_buffer_time( $data['buffer_time'] ?? 0 ),
				'min_advance'   => absint( $data['min_advance'] ?? 0 ),
				'max_advance'   => absint( $data['max_advance'] ?? 30 ),
				'base_price'    => round( (float) $data['base_price'], 2 ),
				'payment_mode'  => $this->default_payment_mode(),
				'status'        => 'active',
				'sort_order'    => $this->get_next_sort_order( $facility_id ),
			],
			[ '%d', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%d', '%d', '%d', '%f', '%s', '%s', '%d' ]
		);

		if ( false === $result ) {
			return false;
		}

		$field_id = (int) $wpdb->insert_id;

		do_action( 'playard_field_created', $field_id, $data );

		return $field_id;
	}

	/**
	 * Updates name, description, and photos for a field.
	 * Called from the basic info section of the field form.
	 *
	 * @param int                 $id
	 * @param array<string,mixed> $data  Keys: name, description, photos
	 */
	public function update_basic( int $id, array $data ): bool {
		global $wpdb;

		$update_data   = [];
		$update_format = [];

		if ( isset( $data['name'] ) ) {
			$update_data['name'] = sanitize_text_field( (string) $data['name'] );
			$update_format[]     = '%s';
		}

		if ( isset( $data['description'] ) ) {
			$update_data['description'] = sanitize_textarea_field( (string) $data['description'] );
			$update_format[]            = '%s';
		}

		if ( isset( $data['photos'] ) ) {
			$photos                = array_map( 'esc_url_raw', array_values( array_filter( (array) $data['photos'] ) ) );
			$update_data['photos'] = wp_json_encode( $photos );
			$update_format[]       = '%s';
		}

		if ( empty( $update_data ) ) {
			return false;
		}

		$result = $wpdb->update(
			$wpdb->prefix . 'playard_fields',
			$update_data,
			[ 'id' => $id ],
			$update_format,
			[ '%d' ]
		);

		if ( false !== $result ) {
			do_action( 'playard_field_updated', $id, $data );
		}

		return false !== $result;
	}

	/**
	 * Partial update — only updates keys present in $data.
	 *
	 * @param int                 $id
	 * @param array<string,mixed> $data
	 */
	public function update( int $id, array $data ): bool {
		global $wpdb;

		$update_data   = [];
		$update_format = [];

		$allowed = [
			'name'          => '%s',
			'description'   => '%s',
			'capacity'      => '%d',
			'slot_duration' => '%d',
			'buffer_time'   => '%d',
			'min_advance'   => '%d',
			'max_advance'   => '%d',
			'base_price'    => '%f',
			'payment_mode'  => '%s',
			'status'        => '%s',
			'sort_order'    => '%d',
		];

		foreach ( $allowed as $key => $format ) {
			if ( ! isset( $data[ $key ] ) ) {
				continue;
			}

			$update_data[ $key ] = match ( true ) {
				$key === 'description'    => sanitize_textarea_field( (string) $data[ $key ] ),
				$key === 'slot_duration'  => $this->valid_slot_duration( $data[ $key ] ),
				$key === 'buffer_time'    => $this->valid_buffer_time( $data[ $key ] ),
				$key === 'payment_mode'   => $this->valid_payment_mode( $data[ $key ] ),
				$format === '%s'          => sanitize_text_field( (string) $data[ $key ] ),
				$format === '%d'          => absint( $data[ $key ] ),
				$format === '%f'          => round( (float) $data[ $key ], 2 ),
			};

			$update_format[] = $format;
		}

		if ( isset( $data['sports'] ) ) {
			$update_data['sports'] = wp_json_encode( $this->filter_sports( (array) $data['sports'] ) );
			$update_format[]       = '%s';
		}

		if ( empty( $update_data ) ) {
			return false;
		}

		$result = $wpdb->update(
			$wpdb->prefix . 'playard_fields',
			$update_data,
			[ 'id' => $id ],
			$update_format,
			[ '%d' ]
		);

		if ( false !== $result ) {
			do_action( 'playard_field_updated', $id, $data );
		}

		return false !== $result;
	}

	/**
	 * Filters an array of sport keys against the whitelist from playard_get_default_sports().
	 *
	 * @param  array<mixed> $sports
	 * @return array<string>
	 */
	/**
	 * Returns the slot duration if it is a valid value, otherwise falls back to 60.
	 */
	private function valid_slot_duration( mixed $value ): int {
		$v = absint( $value );
		return in_array( $v, [ 30, 60, 90, 120 ], true ) ? $v : 60;
	}

	private function valid_payment_mode( mixed $value ): string {
		$v = sanitize_text_field( (string) $value );
		return in_array( $v, [ 'online', 'cash', 'both' ], true ) ? $v : 'online';
	}

	/**
	 * Derives the default payment_mode for a new field from the global payment
	 * settings, so new fields immediately match the facility's configured gateways.
	 */
	private function default_payment_mode(): string {
		$settings  = (array) get_option( 'playard_settings', [] );
		$stripe_on = ! empty( $settings['payments']['stripe']['enabled'] );
		$cash_on   = ! empty( $settings['payments']['cash']['enabled'] );

		if ( $stripe_on && $cash_on ) {
			return 'both';
		}
		if ( $stripe_on ) {
			return 'online';
		}
		return 'cash';
	}

	/**
	 * Returns the buffer time if it is a valid value, otherwise falls back to 0.
	 */
	private function valid_buffer_time( mixed $value ): int {
		$v = absint( $value );
		return in_array( $v, [ 0, 5, 10, 15, 30 ], true ) ? $v : 0;
	}

	private function filter_sports( array $sports ): array {
		$valid = array_column( playard_get_default_sports(), 'key' );
		return array_values( array_filter(
			array_map( 'sanitize_text_field', $sports ),
			fn( string $s ) => in_array( $s, $valid, true )
		) );
	}

	/**
	 * Sets a field's status to 'active' or 'inactive'.
	 * Blocks deactivation if this is the last active field.
	 * Returns 'not_found', 'invalid_status', 'last_field', false, or true.
	 *
	 * @return true|false|string
	 */
	public function set_status( int $id, string $status ): bool|string {
		if ( ! in_array( $status, [ 'active', 'inactive' ], true ) ) {
			return 'invalid_status';
		}

		global $wpdb;

		$field = $this->get( $id );

		if ( ! $field ) {
			return 'not_found';
		}

		if ( $field->status === $status ) {
			return true; // Already in requested state — idempotent
		}

		if ( $status === 'inactive' ) {
			$active_count = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM {$wpdb->prefix}playard_fields
					 WHERE facility_id = %d AND status = 'active'",
					$field->facility_id
				)
			);

			if ( $active_count <= 1 ) {
				return 'last_field';
			}
		}

		$result = $wpdb->update(
			$wpdb->prefix . 'playard_fields',
			[ 'status' => $status ],
			[ 'id'     => $id ],
			[ '%s' ],
			[ '%d' ]
		);

		if ( false !== $result ) {
			do_action( 'playard_field_status_changed', $id, $status );
		}

		return false !== $result;
	}

	/**
	 * Soft-deletes a field by setting its status to 'inactive'.
	 * Returns 'not_found' or 'last_field' for business-rule failures,
	 * false for DB errors, true on success.
	 *
	 * @return true|false|string
	 */
	public function deactivate( int $id ): bool|string {
		global $wpdb;

		$field = $this->get( $id );

		if ( ! $field ) {
			return 'not_found';
		}

		if ( $field->status !== 'active' ) {
			return true; // Already inactive — idempotent
		}

		// Cannot deactivate the last remaining active field
		$active_count = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$wpdb->prefix}playard_fields
				 WHERE facility_id = %d AND status = 'active'",
				$field->facility_id
			)
		);

		if ( $active_count <= 1 ) {
			return 'last_field';
		}

		$result = $wpdb->update(
			$wpdb->prefix . 'playard_fields',
			[ 'status' => 'inactive' ],
			[ 'id'     => $id ],
			[ '%s' ],
			[ '%d' ]
		);

		if ( false === $result ) {
			return false;
		}

		do_action( 'playard_field_deactivated', $id );

		return true;
	}
}
