<?php
/**
 * Database schema definitions (dbDelta).
 *
 * @package Kayvandra\MarginBeacon
 */

declare(strict_types=1);

namespace Kayvandra\MarginBeacon\Migrations;

/**
 * Returns CREATE TABLE SQL for MarginBeacon custom tables.
 */
final class Schema {

	/**
	 * All table DDL keyed by logical name.
	 *
	 * @return array<string,string>
	 */
	public static function tables() {
		global $wpdb;
		$charset = $wpdb->get_charset_collate();
		$p       = $wpdb->prefix;

		return array(
			'scans'              => "CREATE TABLE {$p}marginbeacon_scans (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				type varchar(32) NOT NULL,
				status varchar(32) NOT NULL,
				cursor_json longtext NULL,
				input_revision varchar(64) NOT NULL DEFAULT '',
				evaluation_scope varchar(32) NOT NULL DEFAULT '',
				execution_key varchar(128) DEFAULT NULL,
				counts_json longtext NULL,
				scheduled_at_gmt datetime NULL,
				started_at_gmt datetime NULL,
				completed_at_gmt datetime NULL,
				error_code varchar(64) NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY uk_scan_execution_key (execution_key),
				KEY status (status),
				KEY started_at_gmt (started_at_gmt),
				KEY type_status (type, status)
			) {$charset};",

			'findings'           => "CREATE TABLE {$p}marginbeacon_findings (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				scan_id bigint(20) unsigned NOT NULL,
				entity_type varchar(32) NOT NULL,
				entity_id bigint(20) unsigned NOT NULL,
				rule_id varchar(64) NOT NULL,
				rule_version varchar(32) NOT NULL DEFAULT '',
				severity varchar(16) NOT NULL,
				freshness varchar(32) NOT NULL DEFAULT 'fresh',
				revenue decimal(19,6) NULL,
				cogs decimal(19,6) NULL,
				gross_profit decimal(19,6) NULL,
				margin decimal(19,8) NULL,
				money_scale tinyint(3) unsigned NOT NULL DEFAULT 6,
				severity_basis_json longtext NULL,
				currency char(3) NOT NULL DEFAULT '',
				threshold_snapshot_json longtext NULL,
				cost_source varchar(64) NULL,
				confidence varchar(32) NULL,
				source_presence varchar(32) NULL,
				coverage_json longtext NULL,
				evaluation_scope varchar(32) NOT NULL DEFAULT '',
				input_hash varchar(64) NOT NULL DEFAULT '',
				details_json longtext NULL,
				created_at_gmt datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY scan_input_hash (scan_id, input_hash),
				KEY scan_id (scan_id),
				KEY entity (entity_type, entity_id),
				KEY severity (severity),
				KEY freshness (freshness),
				KEY created_at_gmt (created_at_gmt)
			) {$charset};",

			'price_revisions'    => "CREATE TABLE {$p}marginbeacon_price_revisions (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				product_id bigint(20) unsigned NOT NULL,
				variation_id bigint(20) unsigned NOT NULL DEFAULT 0,
				regular_price decimal(19,6) NULL,
				sale_price decimal(19,6) NULL,
				sale_from_gmt datetime NULL,
				sale_to_gmt datetime NULL,
				price_revision_hash varchar(64) NOT NULL,
				valid_from_gmt datetime NOT NULL,
				valid_to_gmt datetime NULL,
				captured_at_gmt datetime NOT NULL,
				capture_source varchar(32) NOT NULL,
				captured_by_user_id bigint(20) unsigned NULL,
				continuity_state varchar(32) NOT NULL DEFAULT 'continuous',
				detector_version varchar(32) NOT NULL DEFAULT '1',
				input_hash varchar(64) NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY input_hash (input_hash),
				KEY entity_validity (product_id, variation_id, valid_from_gmt, valid_to_gmt),
				KEY price_revision_hash (price_revision_hash),
				KEY continuity_state (continuity_state),
				KEY captured_by_user_id (captured_by_user_id)
			) {$charset};",

			'order_risk'         => "CREATE TABLE {$p}marginbeacon_order_risk (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				order_id bigint(20) unsigned NOT NULL,
				order_revision varchar(64) NOT NULL DEFAULT '',
				processing_state varchar(32) NOT NULL DEFAULT 'not_queued',
				eligibility_state varchar(48) NOT NULL DEFAULT '',
				presentation_state varchar(32) NOT NULL DEFAULT 'pending',
				payment_result_state varchar(16) NOT NULL DEFAULT 'confirmed',
				risk_badge varchar(16) NULL,
				recalculation_state varchar(32) NULL,
				recalculation_error_code varchar(64) NULL,
				short_reason_code varchar(64) NULL,
				order_currency char(3) NOT NULL DEFAULT '',
				base_currency char(3) NOT NULL DEFAULT '',
				revenue decimal(19,6) NULL,
				cogs decimal(19,6) NULL,
				gross_profit decimal(19,6) NULL,
				margin decimal(19,8) NULL,
				money_scale tinyint(3) unsigned NOT NULL DEFAULT 6,
				worst_line_item_id bigint(20) unsigned NULL,
				threshold_snapshot_json longtext NULL,
				coverage_json longtext NULL,
				evaluation_scope varchar(32) NOT NULL DEFAULT '',
				promotion_signal varchar(32) NOT NULL DEFAULT 'none',
				promotion_signal_confidence varchar(16) NOT NULL DEFAULT 'none',
				evaluation_origin varchar(32) NOT NULL DEFAULT 'realtime',
				calculation_fidelity varchar(32) NOT NULL DEFAULT 'native',
				acknowledgement_ref bigint(20) unsigned NULL,
				formula_version varchar(32) NOT NULL DEFAULT '',
				input_hash varchar(64) NOT NULL DEFAULT '',
				severity_basis_json longtext NULL,
				current_token tinyint(1) NULL,
				evaluated_at_gmt datetime NULL,
				superseded_at_gmt datetime NULL,
				error_code varchar(64) NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY order_current (order_id, current_token),
				UNIQUE KEY order_input_hash (order_id, input_hash),
				KEY risk_badge (risk_badge),
				KEY presentation_state (presentation_state),
				KEY payment_result_state (payment_result_state),
				KEY processing_state (processing_state),
				KEY evaluated_at_gmt (evaluated_at_gmt)
			) {$charset};",

			'order_risk_lines'   => "CREATE TABLE {$p}marginbeacon_order_risk_lines (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				order_risk_id bigint(20) unsigned NOT NULL,
				order_item_id bigint(20) unsigned NOT NULL,
				product_id bigint(20) unsigned NOT NULL DEFAULT 0,
				variation_id bigint(20) unsigned NOT NULL DEFAULT 0,
				quantity decimal(12,4) NOT NULL DEFAULT 0,
				revenue decimal(19,6) NULL,
				cogs decimal(19,6) NULL,
				gross_profit decimal(19,6) NULL,
				margin decimal(19,8) NULL,
				money_scale tinyint(3) unsigned NOT NULL DEFAULT 6,
				risk_badge varchar(16) NOT NULL,
				cost_source varchar(64) NULL,
				confidence varchar(32) NULL,
				cost_source_presence varchar(32) NULL,
				severity_basis_json longtext NULL,
				threshold_snapshot_json longtext NULL,
				category_refs_json longtext NULL,
				reason_code varchar(64) NULL,
				PRIMARY KEY  (id),
				KEY order_risk_id (order_risk_id),
				KEY order_item_id (order_item_id),
				KEY product_id (product_id),
				KEY risk_badge (risk_badge)
			) {$charset};",

			'acknowledgements'   => "CREATE TABLE {$p}marginbeacon_acknowledgements (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				scope_type varchar(32) NOT NULL,
				scope_ref varchar(128) NOT NULL,
				revision_hash varchar(64) NOT NULL,
				actor_user_id bigint(20) unsigned NOT NULL,
				reason text NULL,
				status varchar(16) NOT NULL DEFAULT 'active',
				active_key varchar(160) NULL,
				revoke_reason text NULL,
				created_at_gmt datetime NOT NULL,
				revoked_at_gmt datetime NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY active_key (active_key),
				KEY scope_ref (scope_type, scope_ref),
				KEY status (status),
				KEY revision_hash (revision_hash)
			) {$charset};",

			'operational_events' => "CREATE TABLE {$p}marginbeacon_operational_events (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				correlation_id varchar(64) NOT NULL DEFAULT '',
				ref_type varchar(32) NOT NULL DEFAULT '',
				ref_id varchar(64) NOT NULL DEFAULT '',
				transition_from varchar(64) NULL,
				transition_to varchar(64) NULL,
				event_at_gmt datetime NOT NULL,
				duration_ms int(11) NULL,
				error_code varchar(64) NULL,
				context_json longtext NULL,
				retain_until_gmt datetime NULL,
				PRIMARY KEY  (id),
				KEY correlation_id (correlation_id),
				KEY ref (ref_type, ref_id),
				KEY event_at_gmt (event_at_gmt),
				KEY retain_until_gmt (retain_until_gmt)
			) {$charset};",
		);
	}
}