<?php
/**
 * Database schema creation and versioned upgrades.
 *
 * @package EmberPrice
 */

namespace EmberPrice\Core;

defined( 'ABSPATH' ) || exit;

/**
 * Creates the plugin's tables via dbDelta and tracks the installed schema
 * version in the `embp_db_version` option, so upgrades can be detected on
 * any request (not just plugin activation).
 */
final class Migrations {

	const OPTION_DB_VERSION = 'embp_db_version';

	/**
	 * Creates/updates tables if the installed schema is behind EMBP_DB_VERSION.
	 */
	public static function maybe_upgrade(): void {
		$installed_version = (int) get_option( self::OPTION_DB_VERSION, 0 );

		if ( $installed_version >= EMBP_DB_VERSION ) {
			return;
		}

		self::create_tables();

		update_option( self::OPTION_DB_VERSION, EMBP_DB_VERSION );
	}

	/**
	 * Creates (or updates) all plugin tables via dbDelta. Safe to call
	 * repeatedly: dbDelta only applies the schema diff.
	 */
	public static function create_tables(): void {
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		global $wpdb;

		$charset_collate = $wpdb->get_charset_collate();

		foreach ( self::get_schema( $wpdb, $charset_collate ) as $sql ) {
			dbDelta( $sql );
		}
	}

	/**
	 * Returns the CREATE TABLE statements for all plugin tables, per SPEC §7.
	 *
	 * @param \wpdb  $wpdb             WordPress database access object.
	 * @param string $charset_collate  Site charset/collate clause.
	 * @return string[]
	 */
	private static function get_schema( \wpdb $wpdb, string $charset_collate ): array {
		$suppliers = $wpdb->prefix . 'embp_suppliers';
		$jobs      = $wpdb->prefix . 'embp_jobs';
		$rows      = $wpdb->prefix . 'embp_rows';
		$aliases   = $wpdb->prefix . 'embp_aliases';
		$snapshots = $wpdb->prefix . 'embp_snapshots';

		return array(
			"CREATE TABLE {$suppliers} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				name varchar(255) NOT NULL,
				default_rule longtext NULL,
				column_map longtext NULL,
				settings longtext NULL,
				created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
				PRIMARY KEY  (id)
			) {$charset_collate};",

			"CREATE TABLE {$jobs} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				supplier_id bigint(20) unsigned NOT NULL,
				file_name varchar(255) NOT NULL,
				file_hash char(64) NOT NULL,
				status enum('parsing','mapping','matching','review','ready','applying','applied','rolled_back','failed') NOT NULL DEFAULT 'parsing',
				totals longtext NULL,
				created_by bigint(20) unsigned NOT NULL,
				created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
				applied_at datetime NULL,
				PRIMARY KEY  (id),
				KEY supplier_id (supplier_id),
				KEY status_created (status,created_at)
			) {$charset_collate};",

			"CREATE TABLE {$rows} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				job_id bigint(20) unsigned NOT NULL,
				row_index int(10) unsigned NOT NULL,
				raw longtext NULL,
				parsed longtext NULL,
				match_product_id bigint(20) unsigned NULL,
				match_variation_id bigint(20) unsigned NULL,
				match_stage enum('alias','sku','exact_name','fuzzy','llm','manual','none') NULL,
				confidence decimal(4,3) NULL,
				status enum('auto','review','confirmed','rejected','skipped') NOT NULL DEFAULT 'review',
				old_price decimal(12,4) NULL,
				new_price decimal(12,4) NULL,
				flags longtext NULL,
				PRIMARY KEY  (id),
				KEY job_id_status (job_id,status)
			) {$charset_collate};",

			"CREATE TABLE {$aliases} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				supplier_id bigint(20) unsigned NOT NULL,
				alias_normalized varchar(255) NOT NULL,
				product_id bigint(20) unsigned NOT NULL,
				variation_id bigint(20) unsigned NULL,
				hits int(10) unsigned NOT NULL DEFAULT 1,
				last_used_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
				PRIMARY KEY  (id),
				UNIQUE KEY supplier_alias (supplier_id,alias_normalized)
			) {$charset_collate};",

			"CREATE TABLE {$snapshots} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				job_id bigint(20) unsigned NOT NULL,
				product_id bigint(20) unsigned NOT NULL,
				variation_id bigint(20) unsigned NULL,
				old_regular_price decimal(12,4) NULL,
				old_sale_price decimal(12,4) NULL,
				applied_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
				PRIMARY KEY  (id),
				KEY job_id (job_id)
			) {$charset_collate};",
		);
	}
}
