<?php
/**
 * Admin orders management.
 *
 * @package BoxCart
 */

// If this file is called directly, abort.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- BoxCart operates on its own custom tables (orders, baskets, locations, slots, etc.); WordPress core APIs (get_posts(), meta_query, etc.) do not apply. User-supplied values are always passed via $wpdb->prepare() placeholders.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching -- Caching is applied at the caller level for hot paths (admin dashboard widgets, frontend product listings); per-query wp_cache_get/set would add complexity for one-off admin queries.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange -- Schema changes are intentional and run only during plugin activation via dbDelta() or controlled migrations.
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Only internal, plugin-owned table names are interpolated into queries (e.g. {$wpdb->prefix}boxcart_orders); user-supplied values always go through prepare() placeholders.
// phpcs:disable WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- $wpdb->prepare() is still valid with zero placeholders when we only need its auto-escaping of an array.
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- Interpolated values are internal table-name constants, not user input.


/**
 * Admin Orders class.
 *
 * Handles order management in the admin area.
 */
class BoxCart_Admin_Orders {

	/**
	 * Constructor.
	 */
	public function __construct() {
		add_action( 'admin_init', array( $this, 'handle_form_submissions' ) );
	}

	/**
	 * Handle form submissions for order operations.
	 */
	public function handle_form_submissions() {
		if ( ! current_user_can( 'manage_options' ) ) {
			return;
		}

		// Delete a cancelled order (e.g. a test order).
		if ( isset( $_POST['boxcart_delete_order'], $_POST['order_id'], $_POST['_wpnonce'] ) ) {
			$order_id = absint( $_POST['order_id'] );
			if ( wp_verify_nonce( sanitize_key( $_POST['_wpnonce'] ), 'boxcart_delete_order_' . $order_id ) ) {
				$order  = BoxCart_Order::get( $order_id );
				$result = $order ? $order->delete() : new WP_Error( 'not_found', __( 'Order not found.', 'boxcart' ) );
				if ( is_wp_error( $result ) ) {
					set_transient( 'boxcart_order_status_error_' . get_current_user_id(), $result->get_error_message(), 120 );
					wp_safe_redirect( admin_url( 'admin.php?page=boxcart-orders&action=view&id=' . $order_id ) );
					exit;
				}
				wp_safe_redirect( admin_url( 'admin.php?page=boxcart-orders&deleted=1' ) );
				exit;
			}
		}

		// Handle status update form submission.
		if ( isset( $_POST['boxcart_update_order_status'] ) && isset( $_POST['_wpnonce'] ) ) {
			if ( wp_verify_nonce( sanitize_key( $_POST['_wpnonce'] ), 'boxcart_update_order_status' ) ) {
				$order_id   = isset( $_POST['order_id'] ) ? absint( $_POST['order_id'] ) : 0;
				$new_status = isset( $_POST['order_status'] ) ? sanitize_text_field( wp_unslash( $_POST['order_status'] ) ) : '';
				$note       = isset( $_POST['status_note'] ) ? sanitize_textarea_field( wp_unslash( $_POST['status_note'] ) ) : '';

				if ( $order_id && $new_status ) {
					/**
					 * Last check before an admin changes an order's status. Return a
					 * WP_Error to stop the change and show its message. Pro refunds
					 * the card payment here when cancelling with "refund" ticked.
					 *
					 * @param null|WP_Error      $error      Null to go ahead.
					 * @param BoxCart_Order|false $order      Order.
					 * @param string             $new_status New status.
					 */
					$error = apply_filters( 'boxcart_before_admin_status_update', null, BoxCart_Order::get( $order_id ), $new_status );
					if ( is_wp_error( $error ) ) {
						set_transient( 'boxcart_order_status_error_' . get_current_user_id(), $error->get_error_message(), 120 );
						wp_safe_redirect( admin_url( 'admin.php?page=boxcart-orders&action=view&id=' . $order_id ) );
						exit;
					}

					$this->update_status( $order_id, $new_status, $note );
				}

				// Redirect to avoid form resubmission.
				wp_safe_redirect( admin_url( 'admin.php?page=boxcart-orders&action=view&id=' . $order_id . '&status_updated=1' ) );
				exit;
			}
		}
	}

	/**
	 * Get orders list with filters.
	 *
	 * @param array $args Query arguments.
	 * @return array Orders data with items and pagination.
	 */
	public function get_orders( $args = array() ) {
		global $wpdb;

		$defaults = array(
			'status'    => '',
			'search'    => '',
			'location'  => 0,
			'date_from' => '',
			'date_to'   => '',
			'orderby'   => 'created_at',
			'order'     => 'DESC',
			'per_page'  => 20,
			'page'      => 1,
		);

		$args  = wp_parse_args( $args, $defaults );
		$table = $wpdb->prefix . 'boxcart_orders';

		// Build WHERE clauses.
		$where   = array( '1=1' );
		$prepare = array();

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

		if ( ! empty( $args['location'] ) ) {
			$where[]   = 'location_id = %d';
			$prepare[] = $args['location'];
		}

		if ( ! empty( $args['search'] ) ) {
			$search    = '%' . $wpdb->esc_like( $args['search'] ) . '%';
			$where[]   = '(order_number LIKE %s OR customer_first_name LIKE %s OR customer_last_name LIKE %s OR customer_email LIKE %s)';
			$prepare[] = $search;
			$prepare[] = $search;
			$prepare[] = $search;
			$prepare[] = $search;
		}

		if ( ! empty( $args['date_from'] ) ) {
			$where[]   = 'slot_date >= %s';
			$prepare[] = $args['date_from'];
		}

		if ( ! empty( $args['date_to'] ) ) {
			$where[]   = 'slot_date <= %s';
			$prepare[] = $args['date_to'];
		}

		$where_clause = implode( ' AND ', $where );

		// Validate orderby and order.
		$valid_orderby = array( 'created_at', 'order_number', 'slot_date', 'total', 'status' );
		$orderby       = in_array( $args['orderby'], $valid_orderby, true ) ? $args['orderby'] : 'created_at';
		$order         = 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC';

		// Get total count.
		$count_query = "SELECT COUNT(*) FROM {$table} WHERE {$where_clause}";
		if ( ! empty( $prepare ) ) {
			$count_query = $wpdb->prepare( $count_query, $prepare ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		}
		$total = (int) $wpdb->get_var( $count_query ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

		// Calculate pagination.
		$per_page    = max( 1, $args['per_page'] );
		$total_pages = ceil( $total / $per_page );
		$current     = max( 1, min( $args['page'], $total_pages ) );
		$offset      = ( $current - 1 ) * $per_page;

		// Get orders.
		$query = "SELECT * FROM {$table} WHERE {$where_clause} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";

		$query_prepare   = $prepare;
		$query_prepare[] = $per_page;
		$query_prepare[] = $offset;

		$orders = $wpdb->get_results( $wpdb->prepare( $query, $query_prepare ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

		return array(
			'items'       => $orders,
			'total'       => $total,
			'total_pages' => $total_pages,
			'current'     => $current,
			'per_page'    => $per_page,
		);
	}

	/**
	 * Get order counts by status.
	 *
	 * @return array Status counts.
	 */
	public function get_status_counts() {
		global $wpdb;

		$table   = $wpdb->prefix . 'boxcart_orders';
		$results = $wpdb->get_results(
			"SELECT status, COUNT(*) as count FROM {$table} GROUP BY status",
			ARRAY_A
		);

		$counts = array(
			'all'        => 0,
			'pending'    => 0,
			'processing' => 0,
			'ready'      => 0,
			'completed'  => 0,
			'cancelled'  => 0,
		);

		foreach ( $results as $row ) {
			$counts[ $row['status'] ] = (int) $row['count'];
			$counts['all']           += (int) $row['count'];
		}

		return $counts;
	}

	/**
	 * Get today's orders grouped by time slot.
	 *
	 * @param int $location_id Optional location ID filter.
	 * @return array Orders grouped by slot.
	 */
	public function get_todays_orders( $location_id = 0 ) {
		global $wpdb;

		$table = $wpdb->prefix . 'boxcart_orders';
		$today = current_time( 'Y-m-d' );

		$where   = array( 'slot_date = %s' );
		$prepare = array( $today );

		if ( $location_id ) {
			$where[]   = 'location_id = %d';
			$prepare[] = $location_id;
		}

		$where[]   = 'status NOT IN (%s, %s)';
		$prepare[] = 'cancelled';
		$prepare[] = 'completed';

		$where_clause = implode( ' AND ', $where );

		$orders = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$table} WHERE {$where_clause} ORDER BY slot_time_start ASC, created_at ASC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$prepare
			),
			ARRAY_A
		);

		// Group by time slot.
		$grouped = array();
		foreach ( $orders as $order ) {
			$slot_key = $order['slot_time_start'] . ' - ' . $order['slot_time_end'];
			if ( ! isset( $grouped[ $slot_key ] ) ) {
				$grouped[ $slot_key ] = array();
			}
			$grouped[ $slot_key ][] = $order;
		}

		return $grouped;
	}

	/**
	 * Update order status.
	 *
	 * @param int    $order_id   Order ID.
	 * @param string $new_status New status.
	 * @param string $note       Optional note.
	 * @return bool True on success.
	 */
	public function update_status( $order_id, $new_status, $note = '' ) {
		$order = BoxCart_Order::get( $order_id );
		if ( ! $order ) {
			return false;
		}

		$current_user = wp_get_current_user();
		$result       = $order->update_status( $new_status, $note, $current_user->display_name );

		return ! is_wp_error( $result );
	}

	/**
	 * Get available locations for filter dropdown.
	 *
	 * @return array Locations.
	 */
	public function get_locations_for_filter() {
		global $wpdb;

		$table = $wpdb->prefix . 'boxcart_locations';
		return $wpdb->get_results(
			"SELECT id, name FROM {$table} WHERE is_active = 1 ORDER BY sort_order ASC, name ASC",
			ARRAY_A
		);
	}

	/**
	 * Get every location's name, including inactive ones (old orders can use them).
	 *
	 * @return array Rows with id and name.
	 */
	private function get_location_names() {
		global $wpdb;

		$table = $wpdb->prefix . 'boxcart_locations';
		return $wpdb->get_results( "SELECT id, name FROM {$table}", ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	/**
	 * Get orders data formatted for export.
	 *
	 * @param array $args Export arguments.
	 * @return array Formatted orders data.
	 */
	public function get_orders_for_export( $args = array() ) {
		global $wpdb;

		$defaults = array(
			'date_from' => '',
			'date_to'   => '',
			'status'    => '',
			'location'  => 0,
		);

		$args  = wp_parse_args( $args, $defaults );
		$table = $wpdb->prefix . 'boxcart_orders';

		// Build WHERE clauses.
		$where   = array( '1=1' );
		$prepare = array();

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

		if ( ! empty( $args['location'] ) ) {
			$where[]   = 'location_id = %d';
			$prepare[] = $args['location'];
		}

		// Ranges on the column itself, so the created_at index can be used.
		if ( ! empty( $args['date_from'] ) ) {
			$where[]   = 'created_at >= %s';
			$prepare[] = $args['date_from'] . ' 00:00:00';
		}

		if ( ! empty( $args['date_to'] ) ) {
			$where[]   = 'created_at < %s';
			$prepare[] = gmdate( 'Y-m-d', strtotime( $args['date_to'] . ' +1 day' ) ) . ' 00:00:00';
		}

		$where_clause = implode( ' AND ', $where );

		// Get all matching orders.
		$query = "SELECT * FROM {$table} WHERE {$where_clause} ORDER BY created_at DESC";

		if ( ! empty( $prepare ) ) {
			$query = $wpdb->prepare( $query, $prepare ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		}

		$orders = $wpdb->get_results( $query, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

		if ( empty( $orders ) ) {
			return array();
		}

		// Get settings.
		$settings        = get_option( 'boxcart_settings', array() );
		$currency_symbol = isset( $settings['currency_symbol'] ) ? $settings['currency_symbol'] : '£';

		// Load every order's items, and the location names, in one query each
		// rather than per order.
		$items_by_order = array();
		$order_ids      = array_map( 'absint', wp_list_pluck( $orders, 'id' ) );
		foreach ( array_chunk( $order_ids, 500 ) as $chunk ) {
			$items_table  = $wpdb->prefix . 'boxcart_order_items';
			$placeholders = implode( ',', array_fill( 0, count( $chunk ), '%d' ) );
			$rows         = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT order_id, product_name, quantity, quantity_type_id, unit_label FROM {$items_table} WHERE order_id IN ({$placeholders}) ORDER BY id ASC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					$chunk
				),
				ARRAY_A
			);
			foreach ( $rows as $row ) {
				$items_by_order[ (int) $row['order_id'] ][] = $row;
			}
		}

		$location_names = array();
		foreach ( $this->get_location_names() as $location_row ) {
			$location_names[ (int) $location_row['id'] ] = $location_row['name'];
		}

		// Format orders for export.
		$export_data = array();

		foreach ( $orders as $order_data ) {
			$order_id = (int) $order_data['id'];

			// Items summary, e.g. "3 × Apples, 1.5 kg × Carrots".
			$items_summary = array();
			foreach ( isset( $items_by_order[ $order_id ] ) ? $items_by_order[ $order_id ] : array() as $item ) {
				$items_summary[] = preg_replace( '/^×/u', '', BoxCart_Helpers::format_order_item_quantity( $item ) ) . ' × ' . $item['product_name'];
			}

			$export_data[] = array(
				'order_number'    => $order_data['order_number'],
				'created_at'      => gmdate( 'Y-m-d H:i:s', strtotime( $order_data['created_at'] ) ),
				'status'          => ucfirst( $order_data['status'] ),
				'customer_name'   => $order_data['customer_first_name'] . ' ' . $order_data['customer_last_name'],
				'customer_email'  => $order_data['customer_email'],
				'customer_phone'  => $order_data['customer_phone'],
				'location_name'   => isset( $location_names[ (int) $order_data['location_id'] ] ) ? $location_names[ (int) $order_data['location_id'] ] : '',
				'slot_date'       => $order_data['slot_date'],
				'slot_time'       => gmdate( 'g:ia', strtotime( $order_data['slot_time_start'] ) ) . ' - ' . gmdate( 'g:ia', strtotime( $order_data['slot_time_end'] ) ),
				'items_summary'   => implode( ', ', $items_summary ),
				'subtotal'        => $order_data['subtotal'] ? BoxCart_Helpers::format_price_plain( (float) $order_data['subtotal'], false ) : '',
				'discount_amount' => $order_data['discount_amount'] ? BoxCart_Helpers::format_price_plain( (float) $order_data['discount_amount'], false ) : '',
				'total'           => $order_data['total'] ? BoxCart_Helpers::format_price_plain( (float) $order_data['total'], false ) : '',
				'customer_notes'  => $order_data['customer_notes'],
			);
		}

		return $export_data;
	}
}