<?php
/**
 * Database schema and table names.
 *
 * @package RestaurantCosting
 */

declare(strict_types=1);

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

final class RC_Database {

	public static function table( string $suffix ): string {
		global $wpdb;
		return $wpdb->prefix . 'rc_' . $suffix;
	}

	/**
	 * @return array<int, string>
	 */
	public static function all_table_suffixes(): array {
		return array(
			'ingredients', 'recipes', 'recipe_lines', 'recipe_labor',
			'menu_items', 'menu_item_aliases', 'pos_imports',
			'pos_sales', 'pos_labor', 'food_types', 'suppliers',
			'areas', 'inventories', 'inventory_items', 'restaurant_info',
			'menu_types',
		);
	}

	/**
	 * Drops all rc_* tables. Called before restoring a backup.
	 */
	public static function drop_all_tables(): void {
		global $wpdb;
		foreach ( self::all_table_suffixes() as $suffix ) {
			$table = self::table( $suffix );
			$wpdb->query( "DROP TABLE IF EXISTS `{$table}`" );
		}
	}

	public static function maybe_upgrade(): void {
		self::ensure_v7_columns();
		self::ensure_v8_columns();
		self::ensure_v9_columns();

		$installed = get_option( 'rc_db_version', '1' );

		if ( RC_DB_VERSION !== $installed ) {
			self::create_tables();
			self::seed_food_types();
			self::seed_suppliers();

			// DB v6: drop UNIQUE KEY on name_normalized (recipes) and add is_active if missing
			if ( (int) $installed < 6 ) {
				global $wpdb;
				$recipes = self::table( 'recipes' );

				$index_exists = $wpdb->get_row(
					$wpdb->prepare(
						"SELECT 1 FROM information_schema.STATISTICS WHERE table_schema = DATABASE() AND table_name = %s AND index_name = %s LIMIT 1",
						$recipes,
						'name_normalized'
					)
				);
				if ( $index_exists ) {
					$wpdb->query( "ALTER TABLE `{$recipes}` DROP INDEX `name_normalized`" );
					$wpdb->query( "ALTER TABLE `{$recipes}` ADD INDEX `name_normalized` (`name_normalized`)" );
				}

				$col_exists = $wpdb->get_row(
					$wpdb->prepare(
						"SELECT 1 FROM information_schema.COLUMNS WHERE table_schema = DATABASE() AND table_name = %s AND column_name = %s LIMIT 1",
						$recipes,
						'is_active'
					)
				);
				if ( ! $col_exists ) {
					$wpdb->query( "ALTER TABLE `{$recipes}` ADD COLUMN `is_active` tinyint(1) NOT NULL DEFAULT 1 AFTER `print_image`" );
				}
			}

			// DB v7: add min_level + area to ingredients, email_order to suppliers
			if ( (int) $installed < 7 ) {
				global $wpdb;
				$ingredients = self::table( 'ingredients' );
				$suppliers   = self::table( 'suppliers' );

				$col = $wpdb->get_row(
					$wpdb->prepare(
						"SELECT 1 FROM information_schema.COLUMNS WHERE table_schema = DATABASE() AND table_name = %s AND column_name = %s LIMIT 1",
						$ingredients,
						'min_level'
					)
				);
				if ( ! $col ) {
					$wpdb->query( "ALTER TABLE `{$ingredients}` ADD COLUMN `min_level` int unsigned NOT NULL DEFAULT 0 AFTER `food_cost`" );
				}

				$col = $wpdb->get_row(
					$wpdb->prepare(
						"SELECT 1 FROM information_schema.COLUMNS WHERE table_schema = DATABASE() AND table_name = %s AND column_name = %s LIMIT 1",
						$ingredients,
						'area'
					)
				);
				if ( ! $col ) {
					$wpdb->query( "ALTER TABLE `{$ingredients}` ADD COLUMN `area` varchar(100) NOT NULL DEFAULT '' AFTER `food_type`" );
				}

				$col = $wpdb->get_row(
					$wpdb->prepare(
						"SELECT 1 FROM information_schema.COLUMNS WHERE table_schema = DATABASE() AND table_name = %s AND column_name = %s LIMIT 1",
						$suppliers,
						'email_order'
					)
				);
				if ( ! $col ) {
					$wpdb->query( "ALTER TABLE `{$suppliers}` ADD COLUMN `email_order` tinyint(1) NOT NULL DEFAULT 0 AFTER `contact_name`" );
				}
			}

			// DB v8: rename ordered_flag to emailed in inventory_items
			if ( (int) $installed < 8 ) {
				global $wpdb;
				$inv_items = self::table( 'inventory_items' );
				$col = $wpdb->get_row(
					$wpdb->prepare(
						"SELECT 1 FROM information_schema.COLUMNS WHERE table_schema = DATABASE() AND table_name = %s AND column_name = %s LIMIT 1",
						$inv_items,
						'ordered_flag'
					)
				);
				if ( $col ) {
					$wpdb->query( "ALTER TABLE `{$inv_items}` CHANGE COLUMN `ordered_flag` `emailed` tinyint(1) NOT NULL DEFAULT 0" );
					$wpdb->query( "ALTER TABLE `{$inv_items}` DROP INDEX `ordered_flag`" );
					$wpdb->query( "ALTER TABLE `{$inv_items}` ADD INDEX `emailed` (`emailed`)" );
				}
			}

			// DB v9: add is_ingredient column to recipes table
			if ( (int) $installed < 9 ) {
				global $wpdb;
				$recipes   = self::table( 'recipes' );
				$ingredients = self::table( 'ingredients' );

				$col = $wpdb->get_row(
					$wpdb->prepare(
						"SELECT 1 FROM information_schema.COLUMNS WHERE table_schema = DATABASE() AND table_name = %s AND column_name = %s LIMIT 1",
						$recipes,
						'is_ingredient'
					)
				);
				if ( ! $col ) {
					$wpdb->query( "ALTER TABLE `{$recipes}` ADD COLUMN `is_ingredient` tinyint(1) NOT NULL DEFAULT 0 AFTER `print_image`" );
					// Backfill: set is_ingredient = 1 for recipes that have a matching ingredient with is_recipe = 1
					$wpdb->query(
						"UPDATE {$recipes} r
						 INNER JOIN {$ingredients} i ON i.name = r.name AND i.is_recipe = 1
						 SET r.is_ingredient = 1"
					);
				}
			}

			// DB v10: create restaurant_info table
			if ( (int) $installed < 10 ) {
				self::create_tables();
			}

			// DB v11: menu_types table + menu_type_id on recipes
			if ( (int) $installed < 11 ) {
				global $wpdb;
				$mt = self::table( 'menu_types' );
				$wpdb->query(
					"CREATE TABLE IF NOT EXISTS `{$mt}` (
						id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
						name varchar(100) NOT NULL,
						sort_order smallint unsigned NOT NULL DEFAULT 0,
						created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
						PRIMARY KEY  (id),
						UNIQUE KEY name (name)
					) {$wpdb->get_charset_collate()}"
				);
				$defaults = array( 'Liquor', 'Appetizers', 'Main Courses', 'Beverages', 'Desserts', 'Open Food' );
				$order = 0;
				foreach ( $defaults as $name ) {
					$count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$mt} WHERE name = %s", $name ) );
					if ( (int) $count === 0 ) {
						$wpdb->insert( $mt, array( 'name' => $name, 'sort_order' => $order++ ), array( '%s', '%d' ) );
					}
				}
				$recipes = self::table( 'recipes' );
				$col = $wpdb->get_row(
					$wpdb->prepare(
						"SELECT 1 FROM information_schema.COLUMNS WHERE table_schema = DATABASE() AND table_name = %s AND column_name = %s LIMIT 1",
						$recipes,
						'menu_type_id'
					)
				);
				if ( ! $col ) {
					$wpdb->query( "ALTER TABLE `{$recipes}` ADD COLUMN `menu_type_id` bigint(20) unsigned DEFAULT NULL AFTER `is_active`" );
				}
			}

			update_option( 'rc_db_version', RC_DB_VERSION );
		}
	}

	private static function ensure_v8_columns(): void {
		global $wpdb;

		$checked = get_transient( 'rc_v8_columns' );
		if ( $checked ) {
			return;
		}
		set_transient( 'rc_v8_columns', true, DAY_IN_SECONDS );

		$inv_items = self::table( 'inventory_items' );

		$col = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT 1 FROM information_schema.COLUMNS WHERE table_schema = DATABASE() AND table_name = %s AND column_name = %s LIMIT 1",
				$inv_items,
				'ordered_flag'
			)
		);
		if ( $col ) {
			$wpdb->query( "ALTER TABLE `{$inv_items}` CHANGE COLUMN `ordered_flag` `emailed` tinyint(1) NOT NULL DEFAULT 0" );
			$wpdb->query( "ALTER TABLE `{$inv_items}` DROP INDEX `ordered_flag`" );
			$wpdb->query( "ALTER TABLE `{$inv_items}` ADD INDEX `emailed` (`emailed`)" );
		}
	}

	private static function ensure_v9_columns(): void {
		global $wpdb;

		$checked = get_transient( 'rc_v9_columns' );
		if ( $checked ) {
			return;
		}
		set_transient( 'rc_v9_columns', true, DAY_IN_SECONDS );

		$ingredients = self::table( 'ingredients' );
		$col = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT 1 FROM information_schema.COLUMNS WHERE table_schema = DATABASE() AND table_name = %s AND column_name = %s LIMIT 1",
				$ingredients,
				'is_active'
			)
		);
		if ( ! $col ) {
			$wpdb->query( "ALTER TABLE `{$ingredients}` ADD COLUMN `is_active` tinyint(1) NOT NULL DEFAULT 1 AFTER `is_recipe`" );
		}
	}

	private static function ensure_v7_columns(): void {
		global $wpdb;

		$checked = get_transient( 'rc_v7_columns' );
		if ( $checked ) {
			return;
		}
		set_transient( 'rc_v7_columns', true, DAY_IN_SECONDS );

		$ingredients = self::table( 'ingredients' );
		$suppliers   = self::table( 'suppliers' );

		$col = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT 1 FROM information_schema.COLUMNS WHERE table_schema = DATABASE() AND table_name = %s AND column_name = %s LIMIT 1",
				$ingredients,
				'min_level'
			)
		);
		if ( ! $col ) {
			$wpdb->query( "ALTER TABLE `{$ingredients}` ADD COLUMN `min_level` int unsigned NOT NULL DEFAULT 0 AFTER `food_cost`" );
		}

		$col = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT 1 FROM information_schema.COLUMNS WHERE table_schema = DATABASE() AND table_name = %s AND column_name = %s LIMIT 1",
				$ingredients,
				'area'
			)
		);
		if ( ! $col ) {
			$wpdb->query( "ALTER TABLE `{$ingredients}` ADD COLUMN `area` varchar(100) NOT NULL DEFAULT '' AFTER `food_type`" );
		}

		$col = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT 1 FROM information_schema.COLUMNS WHERE table_schema = DATABASE() AND table_name = %s AND column_name = %s LIMIT 1",
				$suppliers,
				'email_order'
			)
		);
		if ( ! $col ) {
			$wpdb->query( "ALTER TABLE `{$suppliers}` ADD COLUMN `email_order` tinyint(1) NOT NULL DEFAULT 0 AFTER `contact_name`" );
		}
	}

	public static function create_tables(): void {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		$charset = $wpdb->get_charset_collate();

		$ingredients = self::table( 'ingredients' );
		$recipes     = self::table( 'recipes' );
		$lines       = self::table( 'recipe_lines' );
		$labor       = self::table( 'recipe_labor' );
		$menu        = self::table( 'menu_items' );
		$aliases     = self::table( 'menu_item_aliases' );
		$imports     = self::table( 'pos_imports' );
		$sales       = self::table( 'pos_sales' );
		$pos_labor   = self::table( 'pos_labor' );

		$food_types  = self::table( 'food_types' );
		$suppliers   = self::table( 'suppliers' );
		$areas       = self::table( 'areas' );
		$inventories = self::table( 'inventories' );
		$inv_items   = self::table( 'inventory_items' );
		$restaurant  = self::table( 'restaurant_info' );
		$menu_types  = self::table( 'menu_types' );

		$sql = array(
			"CREATE TABLE {$food_types} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				name varchar(100) NOT NULL,
				sort_order smallint unsigned NOT NULL DEFAULT 0,
				created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
				PRIMARY KEY  (id),
				UNIQUE KEY name (name)
			) {$charset};",

			"CREATE TABLE {$suppliers} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				business_name varchar(255) NOT NULL,
				address text DEFAULT NULL,
				phone varchar(50) DEFAULT '',
				email varchar(255) DEFAULT '',
				contact_name varchar(255) DEFAULT '',
				created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
				updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
				PRIMARY KEY  (id),
				UNIQUE KEY business_name (business_name)
			) {$charset};",

			"CREATE TABLE {$ingredients} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				name varchar(255) NOT NULL,
				name_normalized varchar(255) NOT NULL,
				food_type varchar(100) DEFAULT '',
				size_label varchar(255) DEFAULT '',
				weight_grams decimal(12,4) NOT NULL DEFAULT 0,
				calories decimal(12,4) DEFAULT NULL,
				fat_grams decimal(12,4) DEFAULT NULL,
				saturated_fat_grams decimal(12,4) DEFAULT NULL,
				trans_fat_grams decimal(12,4) DEFAULT NULL,
				carb_grams decimal(12,4) DEFAULT NULL,
				fiber_grams decimal(12,4) DEFAULT NULL,
				sugar_grams decimal(12,4) DEFAULT NULL,
				cholesterol_mg decimal(12,4) DEFAULT NULL,
				protein_grams decimal(12,4) DEFAULT NULL,
				sodium_mg decimal(12,4) DEFAULT NULL,
				vit_a decimal(12,4) DEFAULT NULL,
				vit_c_mg decimal(12,4) DEFAULT NULL,
				potassium_mg decimal(12,4) DEFAULT NULL,
				calcium_mg decimal(12,4) DEFAULT NULL,
				iron_mg decimal(12,4) DEFAULT NULL,
				ingredients_text text DEFAULT NULL,
				supplier varchar(255) DEFAULT '',
				food_cost decimal(12,4) NOT NULL DEFAULT 0,
				package_size varchar(100) DEFAULT '',
				package_cost decimal(12,4) DEFAULT NULL,
				is_recipe tinyint(1) NOT NULL DEFAULT 0,
				created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
				updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
				PRIMARY KEY  (id),
				UNIQUE KEY name_normalized (name_normalized),
				KEY food_type (food_type),
				KEY is_recipe (is_recipe)
			) {$charset};",

			"CREATE TABLE {$recipes} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				name varchar(255) NOT NULL,
				name_normalized varchar(255) NOT NULL,
				portions decimal(10,4) NOT NULL DEFAULT 1,
				scale_factor decimal(10,4) NOT NULL DEFAULT 1,
				menu_price decimal(12,4) DEFAULT NULL,
				labor_rate decimal(12,4) DEFAULT NULL,
				method longtext DEFAULT NULL,
				ingredient_id bigint(20) unsigned DEFAULT NULL,
				image_url varchar(500) DEFAULT '',
				print_image tinyint(1) NOT NULL DEFAULT 0,
				is_ingredient tinyint(1) NOT NULL DEFAULT 0,
				is_active tinyint(1) NOT NULL DEFAULT 1,
				created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
				updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
				PRIMARY KEY  (id),
				KEY name_normalized (name_normalized),
				KEY ingredient_id (ingredient_id)
			) {$charset};",

			"CREATE TABLE {$lines} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				recipe_id bigint(20) unsigned NOT NULL,
				ingredient_id bigint(20) unsigned DEFAULT NULL,
				ingredient_name varchar(255) NOT NULL DEFAULT '',
				sort_order smallint unsigned NOT NULL DEFAULT 0,
				quantity decimal(12,4) NOT NULL DEFAULT 0,
				unit enum('g','ml','ea') NOT NULL DEFAULT 'g',
				density_g_per_ml decimal(10,6) DEFAULT NULL,
				method_step smallint unsigned DEFAULT NULL,
				method_text text DEFAULT NULL,
				PRIMARY KEY  (id),
				KEY recipe_id (recipe_id),
				KEY ingredient_id (ingredient_id)
			) {$charset};",

			"CREATE TABLE {$labor} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				recipe_id bigint(20) unsigned NOT NULL,
				item_label varchar(255) DEFAULT '',
				minutes decimal(10,4) NOT NULL DEFAULT 0,
				quantity decimal(10,4) NOT NULL DEFAULT 1,
				sort_order smallint unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY  (id),
				KEY recipe_id (recipe_id)
			) {$charset};",

			"CREATE TABLE {$menu} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				name varchar(255) NOT NULL,
				name_normalized varchar(255) NOT NULL,
				item_type varchar(100) DEFAULT '',
				recipe_id bigint(20) unsigned DEFAULT NULL,
				cost_per_portion decimal(12,4) DEFAULT NULL,
				menu_price decimal(12,4) DEFAULT NULL,
				is_active tinyint(1) NOT NULL DEFAULT 1,
				is_off_menu tinyint(1) NOT NULL DEFAULT 0,
				created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
				updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
				PRIMARY KEY  (id),
				UNIQUE KEY name_normalized (name_normalized),
				KEY recipe_id (recipe_id)
			) {$charset};",

			"CREATE TABLE {$aliases} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				menu_item_id bigint(20) unsigned NOT NULL,
				alias_name varchar(255) NOT NULL,
				name_normalized varchar(255) NOT NULL,
				sales_category varchar(100) DEFAULT NULL,
				menu_group varchar(100) DEFAULT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY alias_scope (name_normalized, sales_category, menu_group),
				KEY menu_item_id (menu_item_id)
			) {$charset};",

			"CREATE TABLE {$imports} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				import_type enum('sales','labor','cogs') NOT NULL,
				period_start date DEFAULT NULL,
				period_end date DEFAULT NULL,
				source_filename varchar(255) DEFAULT '',
				imported_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
				row_count int unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY  (id),
				KEY import_type (import_type),
				KEY period_start (period_start)
			) {$charset};",

			"CREATE TABLE {$sales} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				import_id bigint(20) unsigned NOT NULL,
				menu_item_name varchar(255) NOT NULL,
				name_normalized varchar(255) NOT NULL,
				sales_category varchar(100) DEFAULT '',
				menu_group varchar(100) DEFAULT '',
				quantity decimal(12,4) NOT NULL DEFAULT 0,
				net_sales decimal(14,4) NOT NULL DEFAULT 0,
				gross_sales decimal(14,4) DEFAULT NULL,
				menu_item_id bigint(20) unsigned DEFAULT NULL,
				PRIMARY KEY  (id),
				KEY import_id (import_id),
				KEY name_normalized (name_normalized),
				KEY menu_item_id (menu_item_id)
			) {$charset};",

			"CREATE TABLE {$pos_labor} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				import_id bigint(20) unsigned NOT NULL,
				staff_name varchar(255) NOT NULL DEFAULT '',
				staff_type varchar(100) NOT NULL DEFAULT '',
				payable_hours decimal(10,4) NOT NULL DEFAULT 0,
				regular_rate decimal(10,4) DEFAULT NULL,
				gross_pay decimal(12,4) NOT NULL DEFAULT 0,
				PRIMARY KEY  (id),
				KEY import_id (import_id)
			) {$charset};",

			"CREATE TABLE {$areas} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				name varchar(100) NOT NULL,
				sort_order smallint unsigned NOT NULL DEFAULT 0,
				created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
				PRIMARY KEY  (id),
				UNIQUE KEY name (name)
			) {$charset};",

			"CREATE TABLE {$inventories} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				name varchar(255) NOT NULL,
				created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
				updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
				PRIMARY KEY  (id)
			) {$charset};",

			"CREATE TABLE {$inv_items} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				inventory_id bigint(20) unsigned NOT NULL,
				ingredient_name varchar(255) NOT NULL,
				supplier_name varchar(255) NOT NULL DEFAULT '',
				min_level int unsigned NOT NULL DEFAULT 0,
				order_qty decimal(12,4) NOT NULL DEFAULT 0,
				emailed tinyint(1) NOT NULL DEFAULT 0,
				created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
				PRIMARY KEY  (id),
				KEY inventory_id (inventory_id),
				KEY emailed (emailed)
			) {$charset};",

			"CREATE TABLE {$restaurant} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				restaurant_name varchar(255) NOT NULL DEFAULT '',
				address text DEFAULT NULL,
				phone varchar(50) DEFAULT '',
				logo_url varchar(500) DEFAULT '',
				sender_name varchar(255) DEFAULT '',
				sender_email varchar(255) DEFAULT '',
				created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
				updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
				PRIMARY KEY  (id)
			) {$charset};",

			"CREATE TABLE {$menu_types} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				name varchar(100) NOT NULL,
				sort_order smallint unsigned NOT NULL DEFAULT 0,
				created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
				PRIMARY KEY  (id),
				UNIQUE KEY name (name)
			) {$charset};",
		);

		foreach ( $sql as $statement ) {
			dbDelta( $statement );
		}
	}

	public static function seed_food_types(): void {
		global $wpdb;
		$ft_table = self::table( 'food_types' );
		$ing_table = self::table( 'ingredients' );

		$existing = $wpdb->get_results( "SELECT DISTINCT food_type FROM {$ing_table} WHERE food_type != '' ORDER BY food_type ASC", ARRAY_A );

		$order = 0;
		foreach ( $existing ?: array() as $row ) {
			$name = trim( (string) $row['food_type'] );
			if ( '' === $name ) {
				continue;
			}
			$wpdb->replace( $ft_table, array(
				'name'       => $name,
				'sort_order' => $order++,
			), array( '%s', '%d' ) );
		}

		$defaults = array( 'Produce', 'Meat', 'Dairy', 'Bakery', 'Dry Goods', 'Bar', 'Beverages', 'Frozen', 'Recipe', 'Other' );
		foreach ( $defaults as $name ) {
			$count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$ft_table} WHERE name = %s", $name ) );
			if ( (int) $count === 0 ) {
				$wpdb->insert( $ft_table, array( 'name' => $name, 'sort_order' => $order++ ), array( '%s', '%d' ) );
			}
		}
	}

	public static function seed_suppliers(): void {
		global $wpdb;
		$sup_table = self::table( 'suppliers' );
		$ing_table = self::table( 'ingredients' );

		$existing = $wpdb->get_results( "SELECT DISTINCT supplier FROM {$ing_table} WHERE supplier != '' ORDER BY supplier ASC", ARRAY_A );

		foreach ( $existing ?: array() as $row ) {
			$name = trim( (string) $row['supplier'] );
			if ( '' === $name ) {
				continue;
			}
			$count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$sup_table} WHERE business_name = %s", $name ) );
			if ( (int) $count === 0 ) {
				$wpdb->insert( $sup_table, array( 'business_name' => $name ), array( '%s' ) );
			}
		}
	}
}
