<?php
/**
 * Database handler for BoxCart custom tables.
 *
 * @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.


/**
 * Database class.
 *
 * Handles creation and management of custom database tables.
 */
class BoxCart_Database {

	/**
	 * Current database version.
	 *
	 * @var string
	 */
	const DB_VERSION = '1.0.4';

	/**
	 * Create all custom database tables.
	 */
	public static function create_tables() {
		global $wpdb;

		$charset_collate = $wpdb->get_charset_collate();

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		// Before dbDelta runs: note whether an older create_tables() already
		// turned the weighed quantity columns into whole numbers.
		self::flag_rounded_quantities();

		self::create_locations_table( $charset_collate );
		self::create_location_schedules_table( $charset_collate );
		self::create_location_slots_table( $charset_collate );
		self::create_orders_table( $charset_collate );
		self::create_order_items_table( $charset_collate );
		self::create_order_status_log_table( $charset_collate );
		self::create_baskets_table( $charset_collate );
		self::create_basket_items_table( $charset_collate );
		self::create_product_quantities_table( $charset_collate );

		update_option( 'boxcart_db_version', self::DB_VERSION );
	}

	/**
	 * Create locations table.
	 *
	 * @param string $charset_collate Database charset and collation.
	 */
	private static function create_locations_table( $charset_collate ) {
		global $wpdb;

		$table_name = $wpdb->prefix . 'boxcart_locations';

		$sql = "CREATE TABLE {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			name varchar(255) NOT NULL,
			address_line_1 varchar(255) DEFAULT NULL,
			address_line_2 varchar(255) DEFAULT NULL,
			city varchar(100) DEFAULT NULL,
			county varchar(100) DEFAULT NULL,
			postcode varchar(20) DEFAULT NULL,
			country varchar(2) DEFAULT 'GB',
			phone varchar(32) DEFAULT NULL,
			email varchar(100) DEFAULT NULL,
			instructions text DEFAULT NULL,
			is_active tinyint(1) NOT NULL DEFAULT 1,
			sort_order int(11) NOT NULL DEFAULT 0,
			created_at datetime NOT NULL,
			updated_at datetime NOT NULL,
			PRIMARY KEY  (id),
			KEY is_active (is_active),
			KEY sort_order (sort_order)
		) $charset_collate;";

		dbDelta( $sql );
	}

	/**
	 * Create location schedules table.
	 *
	 * @param string $charset_collate Database charset and collation.
	 */
	private static function create_location_schedules_table( $charset_collate ) {
		global $wpdb;

		$table_name = $wpdb->prefix . 'boxcart_location_schedules';

		$sql = "CREATE TABLE {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			location_id bigint(20) unsigned NOT NULL,
			day_of_week tinyint(1) NOT NULL,
			is_open tinyint(1) NOT NULL DEFAULT 0,
			PRIMARY KEY  (id),
			UNIQUE KEY location_day (location_id,day_of_week),
			KEY location_id (location_id)
		) $charset_collate;";

		dbDelta( $sql );
	}

	/**
	 * Create location slots table.
	 *
	 * @param string $charset_collate Database charset and collation.
	 */
	private static function create_location_slots_table( $charset_collate ) {
		global $wpdb;

		$table_name = $wpdb->prefix . 'boxcart_location_slots';

		$sql = "CREATE TABLE {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			location_id bigint(20) unsigned NOT NULL,
			day_of_week tinyint(1) NOT NULL,
			time_start time NOT NULL,
			time_end time NOT NULL,
			capacity int(11) NOT NULL DEFAULT 10,
			is_active tinyint(1) NOT NULL DEFAULT 1,
			PRIMARY KEY  (id),
			KEY location_day (location_id,day_of_week),
			KEY is_active (is_active)
		) $charset_collate;";

		dbDelta( $sql );
	}

	/**
	 * Create orders table.
	 *
	 * @param string $charset_collate Database charset and collation.
	 */
	private static function create_orders_table( $charset_collate ) {
		global $wpdb;

		$table_name = $wpdb->prefix . 'boxcart_orders';

		$sql = "CREATE TABLE {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			order_number varchar(32) NOT NULL,
			customer_id bigint(20) unsigned DEFAULT NULL,
			customer_email varchar(100) NOT NULL,
			customer_first_name varchar(100) NOT NULL,
			customer_last_name varchar(100) NOT NULL,
			customer_phone varchar(32) DEFAULT NULL,
			delivery_type varchar(20) NOT NULL DEFAULT 'collection',
			delivery_address_1 varchar(255) DEFAULT NULL,
			delivery_address_2 varchar(255) DEFAULT NULL,
			delivery_city varchar(100) DEFAULT NULL,
			delivery_postcode varchar(20) DEFAULT NULL,
			delivery_instructions text DEFAULT NULL,
			delivery_fee decimal(10,2) DEFAULT NULL,
			location_id bigint(20) unsigned DEFAULT NULL,
			slot_date date DEFAULT NULL,
			slot_time_start time DEFAULT NULL,
			slot_time_end time DEFAULT NULL,
			status varchar(32) NOT NULL DEFAULT 'pending',
			item_count int(11) NOT NULL DEFAULT 0,
			subtotal decimal(10,2) DEFAULT NULL,
			discount_amount decimal(10,2) DEFAULT NULL,
			tax_amount decimal(10,2) DEFAULT NULL,
			total decimal(10,2) DEFAULT NULL,
			payment_method varchar(32) DEFAULT NULL,
			payment_status varchar(32) DEFAULT 'not_required',
			transaction_id varchar(255) DEFAULT NULL,
			amount_paid decimal(10,2) DEFAULT NULL,
			amount_refunded decimal(10,2) DEFAULT NULL,
			amount_credited decimal(10,2) DEFAULT NULL,
			customer_notes text DEFAULT NULL,
			admin_notes text DEFAULT NULL,
			ip_address varchar(45) DEFAULT NULL,
			user_agent varchar(255) DEFAULT NULL,
			view_token varchar(64) DEFAULT NULL,
			created_at datetime NOT NULL,
			updated_at datetime NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY order_number (order_number),
			KEY customer_id (customer_id),
			KEY customer_email (customer_email),
			KEY location_id (location_id),
			KEY slot_date (slot_date),
			KEY status (status),
			KEY payment_status (payment_status),
			KEY created_at (created_at),
			KEY delivery_type (delivery_type),
			KEY view_token (view_token)
		) $charset_collate;";

		dbDelta( $sql );
	}

	/**
	 * Create order items table.
	 *
	 * @param string $charset_collate Database charset and collation.
	 */
	private static function create_order_items_table( $charset_collate ) {
		global $wpdb;

		$table_name = $wpdb->prefix . 'boxcart_order_items';

		$sql = "CREATE TABLE {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			order_id bigint(20) unsigned NOT NULL,
			product_id bigint(20) unsigned NOT NULL,
			product_name varchar(255) NOT NULL,
			product_sku varchar(100) DEFAULT NULL,
			quantity_type_id bigint(20) unsigned DEFAULT NULL,
			unit_label varchar(50) DEFAULT NULL,
			price_per_unit decimal(10,2) DEFAULT NULL,
			quantity decimal(10,3) NOT NULL DEFAULT 1,
			price decimal(10,2) DEFAULT NULL,
			line_total decimal(10,2) DEFAULT NULL,
			PRIMARY KEY  (id),
			KEY order_id (order_id),
			KEY product_id (product_id)
		) $charset_collate;";

		dbDelta( $sql );
	}

	/**
	 * Create order status log table.
	 *
	 * @param string $charset_collate Database charset and collation.
	 */
	private static function create_order_status_log_table( $charset_collate ) {
		global $wpdb;

		$table_name = $wpdb->prefix . 'boxcart_order_status_log';

		$sql = "CREATE TABLE {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			order_id bigint(20) unsigned NOT NULL,
			old_status varchar(32) DEFAULT NULL,
			new_status varchar(32) NOT NULL,
			changed_by bigint(20) unsigned DEFAULT NULL,
			note text DEFAULT NULL,
			created_at datetime NOT NULL,
			PRIMARY KEY  (id),
			KEY order_id (order_id),
			KEY created_at (created_at)
		) $charset_collate;";

		dbDelta( $sql );
	}

	/**
	 * Create baskets table.
	 *
	 * @param string $charset_collate Database charset and collation.
	 */
	private static function create_baskets_table( $charset_collate ) {
		global $wpdb;

		$table_name = $wpdb->prefix . 'boxcart_baskets';

		$sql = "CREATE TABLE {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			session_id varchar(64) NOT NULL,
			user_id bigint(20) unsigned DEFAULT NULL,
			selected_location_id bigint(20) unsigned DEFAULT NULL,
			selected_slot_date date DEFAULT NULL,
			selected_slot_time_start time DEFAULT NULL,
			selected_slot_time_end time DEFAULT NULL,
			slot_selected_at datetime DEFAULT NULL,
			slot_reservation_expires_at datetime DEFAULT NULL,
			created_at datetime NOT NULL,
			updated_at datetime NOT NULL,
			expires_at datetime NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY session_id (session_id),
			KEY user_id (user_id),
			KEY expires_at (expires_at),
			KEY selected_location_id (selected_location_id)
		) $charset_collate;";

		dbDelta( $sql );
	}

	/**
	 * Create basket items table.
	 *
	 * @param string $charset_collate Database charset and collation.
	 */
	private static function create_basket_items_table( $charset_collate ) {
		global $wpdb;

		$table_name = $wpdb->prefix . 'boxcart_basket_items';

		$sql = "CREATE TABLE {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			basket_id bigint(20) unsigned NOT NULL,
			product_id bigint(20) unsigned NOT NULL,
			quantity_type_id bigint(20) unsigned DEFAULT NULL,
			quantity decimal(10,3) NOT NULL DEFAULT 1,
			added_at datetime NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY basket_product_qty_type (basket_id,product_id,quantity_type_id),
			KEY basket_id (basket_id),
			KEY product_id (product_id)
		) $charset_collate;";

		dbDelta( $sql );
	}

	/**
	 * Create product quantities table.
	 *
	 * Stores custom quantity types for products (e.g., by kg, by bunch, etc.).
	 *
	 * @param string $charset_collate Database charset and collation.
	 */
	private static function create_product_quantities_table( $charset_collate ) {
		global $wpdb;

		$table_name = $wpdb->prefix . 'boxcart_product_quantities';

		$sql = "CREATE TABLE {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			product_id bigint(20) unsigned NOT NULL,
			unit_label varchar(50) NOT NULL,
			unit_label_plural varchar(50) DEFAULT NULL,
			display_label varchar(20) DEFAULT NULL,
			price_prefix varchar(20) DEFAULT 'per',
			price_per_unit decimal(10,2) NOT NULL,
			allow_decimals tinyint(1) NOT NULL DEFAULT 0,
			step_increment decimal(10,3) NOT NULL DEFAULT 1,
			min_quantity decimal(10,3) NOT NULL DEFAULT 1,
			max_quantity decimal(10,3) DEFAULT NULL,
			display_order int(11) NOT NULL DEFAULT 0,
			created_at datetime NOT NULL,
			PRIMARY KEY  (id),
			KEY product_id (product_id),
			KEY display_order (display_order)
		) $charset_collate;";

		dbDelta( $sql );
	}

	/**
	 * Drop all custom tables.
	 *
	 * Used during uninstall when user opts to remove all data.
	 */
	public static function drop_tables() {
		global $wpdb;

		$tables = array(
			$wpdb->prefix . 'boxcart_product_quantities',
			$wpdb->prefix . 'boxcart_basket_items',
			$wpdb->prefix . 'boxcart_baskets',
			$wpdb->prefix . 'boxcart_order_status_log',
			$wpdb->prefix . 'boxcart_order_items',
			$wpdb->prefix . 'boxcart_orders',
			$wpdb->prefix . 'boxcart_location_slots',
			$wpdb->prefix . 'boxcart_location_schedules',
			$wpdb->prefix . 'boxcart_locations',
		);

		/**
		 * Allow extensions (e.g. BoxCart Pro) to drop their own custom tables
		 * during a full-data uninstall. Pro owns the location closures,
		 * capacity overrides, and order-messages tables, so it cleans them up
		 * here rather than the Free build referencing tables it never creates.
		 */
		do_action( 'boxcart_drop_tables' );

		foreach ( $tables as $table ) {
			$wpdb->query( "DROP TABLE IF EXISTS {$table}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		}

		delete_option( 'boxcart_db_version' );
	}

	/**
	 * Get the current database version.
	 *
	 * @return string Database version.
	 */
	public static function get_db_version() {
		return get_option( 'boxcart_db_version', '0.0.0' );
	}

	/**
	 * Check if database needs updating.
	 *
	 * @return bool True if update needed.
	 */
	public static function needs_update() {
		return version_compare( self::get_db_version(), self::DB_VERSION, '<' );
	}

	/**
	 * Ensure delivery and discount columns exist in orders table.
	 *
	 * dbDelta doesn't reliably add new columns to existing tables,
	 * so we need to check and add them explicitly.
	 */
	public static function ensure_order_columns() {
		global $wpdb;

		$table_name = $wpdb->prefix . 'boxcart_orders';

		// Check if the table exists first.
		$table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) );
		if ( ! $table_exists ) {
			return;
		}

		// Get existing columns.
		$columns = $wpdb->get_col( "DESCRIBE {$table_name}", 0 ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		// Columns to add if missing.
		$new_columns = array(
			'delivery_type'         => "varchar(20) NOT NULL DEFAULT 'collection' AFTER customer_phone",
			'delivery_address_1'    => 'varchar(255) DEFAULT NULL AFTER delivery_type',
			'delivery_address_2'    => 'varchar(255) DEFAULT NULL AFTER delivery_address_1',
			'delivery_city'         => 'varchar(100) DEFAULT NULL AFTER delivery_address_2',
			'delivery_postcode'     => 'varchar(20) DEFAULT NULL AFTER delivery_city',
			'delivery_instructions' => 'text DEFAULT NULL AFTER delivery_postcode',
			'delivery_fee'          => 'decimal(10,2) DEFAULT NULL AFTER delivery_instructions',
			'discount_amount'       => 'decimal(10,2) DEFAULT NULL AFTER subtotal',
			'tax_amount'            => 'decimal(10,2) DEFAULT NULL AFTER discount_amount',
		);

		foreach ( $new_columns as $column => $definition ) {
			if ( ! in_array( $column, $columns, true ) ) {
				$wpdb->query( "ALTER TABLE {$table_name} ADD COLUMN {$column} {$definition}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			}
		}

		// Make location_id and slot fields nullable for delivery orders.
		$wpdb->query( "ALTER TABLE {$table_name} MODIFY location_id bigint(20) unsigned DEFAULT NULL" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query( "ALTER TABLE {$table_name} MODIFY slot_date date DEFAULT NULL" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query( "ALTER TABLE {$table_name} MODIFY slot_time_start time DEFAULT NULL" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query( "ALTER TABLE {$table_name} MODIFY slot_time_end time DEFAULT NULL" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	/**
	 * Ensure the view_token column exists in the orders table.
	 *
	 * Adds the per-order view token used by the magic-link order-view flow
	 * (so guest customers can return to view their order via the link in
	 * their confirmation email).
	 */
	public static function ensure_view_token_column() {
		global $wpdb;

		$table_name = $wpdb->prefix . 'boxcart_orders';

		// Check if the table exists first.
		$table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) );
		if ( ! $table_exists ) {
			return;
		}

		$columns = $wpdb->get_col( "DESCRIBE {$table_name}", 0 ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		if ( ! in_array( 'view_token', $columns, true ) ) {
			$wpdb->query( "ALTER TABLE {$table_name} ADD COLUMN view_token varchar(64) DEFAULT NULL AFTER user_agent" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( "ALTER TABLE {$table_name} ADD KEY view_token (view_token)" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		}
	}

	/**
	 * Ensure payment columns exist in orders table.
	 *
	 * Adds payment_method, payment_status, and transaction_id columns
	 * for the payment system introduced in v1.8.0.
	 */
	public static function ensure_payment_columns() {
		global $wpdb;

		$table_name = $wpdb->prefix . 'boxcart_orders';

		// Check if the table exists first.
		$table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) );
		if ( ! $table_exists ) {
			return;
		}

		// Get existing columns.
		$columns = $wpdb->get_col( "DESCRIBE {$table_name}", 0 ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		// Columns to add if missing.
		$new_columns = array(
			'payment_method'  => 'varchar(32) DEFAULT NULL AFTER total',
			'payment_status'  => "varchar(32) DEFAULT 'not_required' AFTER payment_method",
			'transaction_id'  => 'varchar(255) DEFAULT NULL AFTER payment_status',
			// 1.5.0: what has actually been paid and refunded, so a paid order
			// can change (balance to pay at collection, or a refund).
			'amount_paid'     => 'decimal(10,2) DEFAULT NULL AFTER transaction_id',
			'amount_refunded' => 'decimal(10,2) DEFAULT NULL AFTER amount_paid',
			'amount_credited' => 'decimal(10,2) DEFAULT NULL AFTER amount_refunded',
		);

		foreach ( $new_columns as $column => $definition ) {
			if ( ! in_array( $column, $columns, true ) ) {
				$wpdb->query( "ALTER TABLE {$table_name} ADD COLUMN {$column} {$definition}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			}
		}

		// Orders paid before 1.5.0 were paid in full: store that, so a later
		// change to the order is measured against what was actually paid.
		$wpdb->query(
			$wpdb->prepare(
				"UPDATE {$table_name} SET amount_paid = total WHERE amount_paid IS NULL AND total IS NOT NULL AND payment_status IN (%s, %s)", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				'paid',
				'refunded'
			)
		);

		// Add index for payment_status if it doesn't exist.
		$indexes = $wpdb->get_results( "SHOW INDEX FROM {$table_name} WHERE Key_name = 'payment_status'", ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		if ( empty( $indexes ) ) {
			$wpdb->query( "ALTER TABLE {$table_name} ADD KEY payment_status (payment_status)" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		}
	}

	/**
	 * Ensure slot selection columns exist in baskets table.
	 *
	 * dbDelta doesn't reliably add new columns to existing tables,
	 * so we need to check and add them explicitly.
	 */
	public static function ensure_slot_columns() {
		global $wpdb;

		$table_name = $wpdb->prefix . 'boxcart_baskets';

		// Check if the table exists first.
		$table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) );
		if ( ! $table_exists ) {
			return;
		}

		// Get existing columns.
		$columns = $wpdb->get_col( "DESCRIBE {$table_name}", 0 ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		// Columns to add if missing.
		$slot_columns = array(
			'selected_location_id'        => 'bigint(20) unsigned DEFAULT NULL',
			'selected_slot_date'          => 'date DEFAULT NULL',
			'selected_slot_time_start'    => 'time DEFAULT NULL',
			'selected_slot_time_end'      => 'time DEFAULT NULL',
			'slot_selected_at'            => 'datetime DEFAULT NULL',
			'slot_reservation_expires_at' => 'datetime DEFAULT NULL',
		);

		foreach ( $slot_columns as $column => $definition ) {
			if ( ! in_array( $column, $columns, true ) ) {
				$wpdb->query( "ALTER TABLE {$table_name} ADD COLUMN {$column} {$definition}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			}
		}
	}

	/**
	 * Ensure quantity type columns exist in basket_items table.
	 *
	 * Adds support for custom quantity types (by weight, by bunch, etc.).
	 */
	public static function ensure_quantity_type_columns() {
		global $wpdb;

		// Update basket_items table.
		$basket_items_table = $wpdb->prefix . 'boxcart_basket_items';

		$table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $basket_items_table ) );
		if ( $table_exists ) {
			$columns = $wpdb->get_col( "DESCRIBE {$basket_items_table}", 0 ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

			// Add quantity_type_id column.
			if ( ! in_array( 'quantity_type_id', $columns, true ) ) {
				$wpdb->query( "ALTER TABLE {$basket_items_table} ADD COLUMN quantity_type_id bigint(20) unsigned DEFAULT NULL AFTER product_id" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			}

			// Modify quantity column to support decimals.
			$wpdb->query( "ALTER TABLE {$basket_items_table} MODIFY quantity decimal(10,3) NOT NULL DEFAULT 1" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

			// Update the unique key to include quantity_type_id (drop old, add new).
			// First check if the old key exists.
			$keys = $wpdb->get_results( "SHOW INDEX FROM {$basket_items_table} WHERE Key_name = 'basket_product'", ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			if ( ! empty( $keys ) ) {
				$wpdb->query( "ALTER TABLE {$basket_items_table} DROP INDEX basket_product" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			}

			// Check if new key exists before adding.
			$new_keys = $wpdb->get_results( "SHOW INDEX FROM {$basket_items_table} WHERE Key_name = 'basket_product_qty_type'", ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			if ( empty( $new_keys ) ) {
				$wpdb->query( "ALTER TABLE {$basket_items_table} ADD UNIQUE KEY basket_product_qty_type (basket_id, product_id, quantity_type_id)" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			}
		}

		// Update order_items table.
		$order_items_table = $wpdb->prefix . 'boxcart_order_items';

		$table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $order_items_table ) );
		if ( $table_exists ) {
			$columns = $wpdb->get_col( "DESCRIBE {$order_items_table}", 0 ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

			$new_columns = array(
				'quantity_type_id' => 'bigint(20) unsigned DEFAULT NULL AFTER product_sku',
				'unit_label'       => 'varchar(50) DEFAULT NULL AFTER quantity_type_id',
				'price_per_unit'   => 'decimal(10,2) DEFAULT NULL AFTER unit_label',
			);

			foreach ( $new_columns as $column => $definition ) {
				if ( ! in_array( $column, $columns, true ) ) {
					$wpdb->query( "ALTER TABLE {$order_items_table} ADD COLUMN {$column} {$definition}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				}
			}

			// Modify quantity column to support decimals.
			$wpdb->query( "ALTER TABLE {$order_items_table} MODIFY quantity decimal(10,3) NOT NULL DEFAULT 1" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		}
	}

	/**
	 * Ensure display_label column exists in product_quantities table.
	 *
	 * Adds support for short display labels in table view unit buttons.
	 */
	public static function ensure_display_label_column() {
		global $wpdb;

		$table_name = $wpdb->prefix . 'boxcart_product_quantities';

		$table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) );
		if ( $table_exists ) {
			$columns = $wpdb->get_col( "DESCRIBE {$table_name}", 0 ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

			if ( ! in_array( 'display_label', $columns, true ) ) {
				$wpdb->query( "ALTER TABLE {$table_name} ADD COLUMN display_label varchar(20) DEFAULT NULL AFTER unit_label_plural" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			}
		}
	}

	/**
	 * Ensure price_prefix column exists in product_quantities table.
	 *
	 * Adds support for customizable word between price and unit label (e.g., "per", blank).
	 */
	public static function ensure_price_prefix_column() {
		global $wpdb;

		$table_name = $wpdb->prefix . 'boxcart_product_quantities';

		$table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) );
		if ( $table_exists ) {
			$columns = $wpdb->get_col( "DESCRIBE {$table_name}", 0 ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

			if ( ! in_array( 'price_prefix', $columns, true ) ) {
				$wpdb->query( "ALTER TABLE {$table_name} ADD COLUMN price_prefix varchar(20) DEFAULT 'per' AFTER display_label" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			}
		}
	}

	/**
	 * Flag sites whose quantity columns were rounded to whole numbers.
	 *
	 * Before 1.5.0, create_tables() still declared order and basket item
	 * quantities as int(11). Reactivating the plugin re-ran it, and dbDelta
	 * changed the decimal(10,3) columns back to INT, so MySQL rounded every
	 * stored weight (1.5 kg became 2). If that happened here, flag it so
	 * recover_rounded_quantities() can repair the order lines afterwards.
	 */
	private static function flag_rounded_quantities() {
		global $wpdb;

		$table_name = $wpdb->prefix . 'boxcart_order_items';

		$table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) );
		if ( ! $table_exists ) {
			return;
		}

		$column = $wpdb->get_row( "SHOW COLUMNS FROM {$table_name} LIKE 'quantity'", ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		// Only an upgraded table can have been damaged: one that already has the
		// quantity type columns but whose quantity is back to a whole number.
		$has_types = $wpdb->get_var( "SHOW COLUMNS FROM {$table_name} LIKE 'quantity_type_id'" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		if ( $column && $has_types && 0 === strpos( strtolower( $column['Type'] ), 'int' ) ) {
			update_option( 'boxcart_recover_quantities', 1, false );
		}
	}

	/**
	 * Repair weighed order lines rounded by the old int(11) column.
	 *
	 * Line totals were calculated from the real quantity before the row was
	 * saved, so where a line has a price, line_total / price gives back the
	 * quantity that was ordered. It is snapped to the quantity type's step to
	 * undo the rounding of the line total to pence. Lines without a price
	 * (shops that don't show prices) can't be recovered.
	 *
	 * @return int Number of order lines repaired.
	 */
	public static function recover_rounded_quantities() {
		global $wpdb;

		delete_option( 'boxcart_recover_quantities' );

		$items_table = $wpdb->prefix . 'boxcart_order_items';
		$types_table = $wpdb->prefix . 'boxcart_product_quantities';

		$rows = $wpdb->get_results(
			"SELECT i.id, i.quantity, i.price, i.line_total, q.step_increment
			FROM {$items_table} i
			LEFT JOIN {$types_table} q ON q.id = i.quantity_type_id
			WHERE i.quantity_type_id IS NOT NULL
			AND i.price > 0
			AND i.line_total IS NOT NULL",
			ARRAY_A
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		$repaired = 0;

		foreach ( (array) $rows as $row ) {
			$ordered = (float) $row['line_total'] / (float) $row['price'];
			$step    = (float) $row['step_increment'];

			if ( $step > 0 && $step < 1 ) {
				$ordered = round( $ordered / $step ) * $step;
			}

			$ordered = round( $ordered, 3 );

			if ( $ordered <= 0 || abs( $ordered - (float) $row['quantity'] ) < 0.001 ) {
				continue;
			}

			$wpdb->update( $items_table, array( 'quantity' => $ordered ), array( 'id' => (int) $row['id'] ), array( '%f' ), array( '%d' ) );
			++$repaired;
		}

		if ( $repaired ) {
			update_option( 'boxcart_recovered_quantities', $repaired, false );
		}

		return $repaired;
	}

	/**
	 * Fix inconsistent quantity type settings.
	 *
	 * Products with allow_decimals=0 but decimal step/min values should be updated
	 * to use whole numbers. This fixes legacy data from before the preset fix.
	 */
	public static function fix_inconsistent_quantity_settings() {
		global $wpdb;

		$table_name = $wpdb->prefix . 'boxcart_product_quantities';

		$table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) );
		if ( ! $table_exists ) {
			return;
		}

		// Check if migration has already been run.
		if ( get_option( 'boxcart_qty_settings_fixed', false ) ) {
			return;
		}

		// Fix products where allow_decimals=0 but step/min are decimals less than 1.
		// This includes the default Unit type that had step=0.1, min=0.1.
		$wpdb->query(
			"UPDATE {$table_name}
			SET step_increment = 1, min_quantity = 1
			WHERE allow_decimals = 0
			AND (step_increment < 1 OR min_quantity < 1)"
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		// Mark migration as complete.
		update_option( 'boxcart_qty_settings_fixed', true );
	}
}