<?php

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

if ( ! function_exists( 'aieo_subjects_table' ) ) {
	function aieo_subjects_table() {
		global $wpdb;
		return $wpdb->prefix . 'aieo_subjects';
	}
}

if ( ! function_exists( 'aieo_session_identity_table' ) ) {
	function aieo_session_identity_table() {
		global $wpdb;
		return $wpdb->prefix . 'aieo_session_identity';
	}
}

if ( ! function_exists( 'aieo_interaction_summary_table' ) ) {
	function aieo_interaction_summary_table() {
		global $wpdb;
		return $wpdb->prefix . 'aieo_interaction_summary';
	}
}

if ( ! function_exists( 'aieo_subject_recos_table' ) ) {
	function aieo_subject_recos_table() {
		global $wpdb;
		return $wpdb->prefix . 'aieo_subject_recos';
	}
}

if ( ! function_exists( 'aieo_audiences_table' ) ) {
	function aieo_audiences_table() {
		global $wpdb;
		return $wpdb->prefix . 'aieo_audiences';
	}
}

if ( ! function_exists( 'aieo_workflows_table' ) ) {
	function aieo_workflows_table() {
		global $wpdb;
		return $wpdb->prefix . 'aieo_workflows';
	}
}

if ( ! function_exists( 'aieo_workflow_runs_table' ) ) {
	function aieo_workflow_runs_table() {
		global $wpdb;
		return $wpdb->prefix . 'aieo_workflow_runs';
	}
}

if ( ! function_exists( 'aieo_coupon_grants_table' ) ) {
	function aieo_coupon_grants_table() {
		global $wpdb;
		return $wpdb->prefix . 'aieo_coupon_grants';
	}
}

if ( ! function_exists( 'aieo_proposals_table' ) ) {
	function aieo_proposals_table() {
		global $wpdb;
		return $wpdb->prefix . 'aieo_proposals';
	}
}

if ( ! function_exists( 'aieo_customer_purchases_table' ) ) {
	function aieo_customer_purchases_table() {
		global $wpdb;
		return $wpdb->prefix . 'aieo_customer_purchases';
	}
}

if ( ! function_exists( 'aieo_visitor_lifetime_table' ) ) {
	function aieo_visitor_lifetime_table() {
		global $wpdb;
		return $wpdb->prefix . 'aieo_visitor_lifetime';
	}
}

function aieo_subjects_install_schema() {
	global $wpdb;

	$charset_collate = function_exists( 'aieo_get_charset_collate' )
		? aieo_get_charset_collate()
		: $wpdb->get_charset_collate();

	$run = function ( $sql ) {
		if ( function_exists( 'aieo_dbdelta' ) ) {
			aieo_dbdelta( $sql );
		} else {
			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
			dbDelta( $sql );
		}
	};

	$subjects = aieo_subjects_table();
	$sessions = aieo_session_identity_table();

	
	$run( "CREATE TABLE IF NOT EXISTS `{$subjects}` (
		`subject_id` bigint unsigned NOT NULL AUTO_INCREMENT,
		`visitor_key` varchar(64) NOT NULL DEFAULT '',
		`wp_user_id` bigint unsigned NOT NULL DEFAULT 0,
		`email_hash` char(32) NOT NULL DEFAULT '',
		`eponymous_id` bigint unsigned NOT NULL DEFAULT 0,
		`merged_into` bigint unsigned NOT NULL DEFAULT 0,
		`first_seen` datetime DEFAULT NULL,
		`last_seen` datetime DEFAULT NULL,
		`created` datetime DEFAULT NULL,
		`updated` datetime DEFAULT NULL,
		PRIMARY KEY (`subject_id`),
		UNIQUE KEY `visitor` (`visitor_key`),
		KEY `wp_user` (`wp_user_id`),
		KEY `email_hash` (`email_hash`),
		KEY `eponymous` (`eponymous_id`),
		KEY `merged_into` (`merged_into`)
	) {$charset_collate};" );

	
	$run( "CREATE TABLE IF NOT EXISTS `{$sessions}` (
		`id` bigint unsigned NOT NULL AUTO_INCREMENT,
		`session_id` varchar(64) NOT NULL,
		`visitor_key` varchar(64) NOT NULL DEFAULT '',
		`user_id` bigint unsigned NOT NULL DEFAULT 0,
		`subject_id` bigint unsigned NOT NULL DEFAULT 0,
		`resolved_via` varchar(16) NOT NULL DEFAULT 'pending',
		`created` datetime DEFAULT NULL,
		`updated` datetime DEFAULT NULL,
		PRIMARY KEY (`id`),
		UNIQUE KEY `session` (`session_id`),
		KEY `subject` (`subject_id`),
		KEY `resolved_via` (`resolved_via`),
		KEY `visitor` (`visitor_key`)
	) {$charset_collate};" );

	
	$interactions = aieo_interaction_summary_table();
	$run( "CREATE TABLE IF NOT EXISTS `{$interactions}` (
		`subject_id` bigint unsigned NOT NULL,
		`item_id` bigint unsigned NOT NULL,
		`item_type` varchar(20) NOT NULL DEFAULT 'product',
		`views` smallint unsigned NOT NULL DEFAULT 0,
		`carts` smallint unsigned NOT NULL DEFAULT 0,
		`removes` smallint unsigned NOT NULL DEFAULT 0,
		`wishlists` smallint unsigned NOT NULL DEFAULT 0,
		`image_clicks` smallint unsigned NOT NULL DEFAULT 0,
		`conversions` smallint unsigned NOT NULL DEFAULT 0,
		`sale_ctx` smallint unsigned NOT NULL DEFAULT 0,
		`weight` decimal(12,4) NOT NULL DEFAULT 0,
		`first_at` datetime DEFAULT NULL,
		`last_at` datetime DEFAULT NULL,
		PRIMARY KEY (`subject_id`,`item_id`,`item_type`),
		KEY `subject_weight` (`subject_id`,`weight`),
		KEY `item` (`item_type`,`item_id`),
		KEY `last_at` (`last_at`)
	) {$charset_collate};" );

	
	$lifetime = aieo_visitor_lifetime_table();
	$run( "CREATE TABLE IF NOT EXISTS `{$lifetime}` (
		`subject_id` bigint unsigned NOT NULL,
		`first_seen` datetime NOT NULL,
		`last_seen` datetime NOT NULL,
		`visits` int unsigned NOT NULL DEFAULT 0,
		`pages` int unsigned NOT NULL DEFAULT 0,
		`product_views` int unsigned NOT NULL DEFAULT 0,
		`carts` int unsigned NOT NULL DEFAULT 0,
		`session_seconds` bigint unsigned NOT NULL DEFAULT 0,
		`first_channel` varchar(40) NOT NULL DEFAULT '',
		`first_landing` varchar(255) NOT NULL DEFAULT '',
		`first_referrer` varchar(190) NOT NULL DEFAULT '',
		`first_device` varchar(50) NOT NULL DEFAULT '',
		`last_channel` varchar(40) NOT NULL DEFAULT '',
		`last_landing` varchar(255) NOT NULL DEFAULT '',
		`last_referrer` varchar(190) NOT NULL DEFAULT '',
		`last_device` varchar(50) NOT NULL DEFAULT '',
		`updated` datetime DEFAULT NULL,
		PRIMARY KEY (`subject_id`),
		KEY `first_seen` (`first_seen`),
		KEY `last_seen` (`last_seen`)
	) ENGINE=InnoDB {$charset_collate};" );

	
	$recos = aieo_subject_recos_table();
	$run( "CREATE TABLE IF NOT EXISTS `{$recos}` (
		`subject_id` bigint unsigned NOT NULL,
		`reco_rank` smallint unsigned NOT NULL,
		`item_id` bigint unsigned NOT NULL,
		`item_type` varchar(20) NOT NULL DEFAULT 'product',
		`score` float NOT NULL DEFAULT 0,
		`computed_at` datetime DEFAULT NULL,
		PRIMARY KEY (`subject_id`,`reco_rank`),
		KEY `subject` (`subject_id`),
		KEY `item` (`item_type`,`item_id`)
	) {$charset_collate};" );

	
	$aud = aieo_audiences_table();
	$run( "CREATE TABLE IF NOT EXISTS `{$aud}` (
		`id` bigint unsigned NOT NULL AUTO_INCREMENT,
		`name` varchar(190) NOT NULL DEFAULT '',
		`rule_json` longtext NULL,
		`count_cached` int NOT NULL DEFAULT -1,
		`count_at` datetime DEFAULT NULL,
		`created` datetime DEFAULT NULL,
		`updated` datetime DEFAULT NULL,
		PRIMARY KEY (`id`),
		KEY `name` (`name`)
	) {$charset_collate};" );

	
	$wf = aieo_workflows_table();
	$run( "CREATE TABLE IF NOT EXISTS `{$wf}` (
		`id` bigint unsigned NOT NULL AUTO_INCREMENT,
		`name` varchar(190) NOT NULL DEFAULT '',
		`status` varchar(12) NOT NULL DEFAULT 'draft',
		`trigger_type` varchar(40) NOT NULL DEFAULT '',
		`trigger_config` longtext NULL,
		`audience_id` bigint unsigned NOT NULL DEFAULT 0,
		`steps_json` longtext NULL,
		`runs_count` int NOT NULL DEFAULT 0,
		`last_run_at` datetime DEFAULT NULL,
		`created` datetime DEFAULT NULL,
		`updated` datetime DEFAULT NULL,
		PRIMARY KEY (`id`),
		KEY `status_trigger` (`status`,`trigger_type`)
	) {$charset_collate};" );

	
	$wfr = aieo_workflow_runs_table();
	$run( "CREATE TABLE IF NOT EXISTS `{$wfr}` (
		`id` bigint unsigned NOT NULL AUTO_INCREMENT,
		`workflow_id` bigint unsigned NOT NULL,
		`subject_id` bigint unsigned NOT NULL DEFAULT 0,
		`wp_user_id` bigint unsigned NOT NULL DEFAULT 0,
		`order_id` bigint unsigned NOT NULL DEFAULT 0,
		`email_hash` char(32) NOT NULL DEFAULT '',
		`step_index` smallint unsigned NOT NULL DEFAULT 0,
		`state` varchar(12) NOT NULL DEFAULT 'pending',
		`context_json` longtext NULL,
		`run_at` datetime DEFAULT NULL,
		`created` datetime DEFAULT NULL,
		`updated` datetime DEFAULT NULL,
		PRIMARY KEY (`id`),
		KEY `due` (`state`,`run_at`),
		KEY `workflow` (`workflow_id`,`subject_id`),
		KEY `wf_order` (`workflow_id`,`order_id`),
		KEY `wf_email` (`workflow_id`,`email_hash`)
	) {$charset_collate};" );
	
	
	
	
	
	
	if ( (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $interactions ) ) === $interactions ) {
		$has_ic = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = 'image_clicks'",
			$interactions
		) );
		if ( ! $has_ic ) {
			
			$wpdb->query( "ALTER TABLE `{$interactions}` ADD COLUMN `image_clicks` smallint unsigned NOT NULL DEFAULT 0 AFTER `wishlists`" );
		}
	}

	
	if ( (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wfr ) ) === $wfr ) {
		$has_oid = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = 'order_id'",
			$wfr
		) );
		if ( ! $has_oid ) {
			
			$wpdb->query( "ALTER TABLE `{$wfr}` ADD COLUMN `order_id` bigint unsigned NOT NULL DEFAULT 0 AFTER `wp_user_id`" );
		}
		$has_idx = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND INDEX_NAME = 'wf_order'",
			$wfr
		) );
		if ( ! $has_idx ) {
			
			$wpdb->query( "ALTER TABLE `{$wfr}` ADD KEY `wf_order` (`workflow_id`,`order_id`)" );
		}
		
		$has_eh = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = 'email_hash'",
			$wfr
		) );
		if ( ! $has_eh ) {
			
			$wpdb->query( "ALTER TABLE `{$wfr}` ADD COLUMN `email_hash` char(32) NOT NULL DEFAULT '' AFTER `order_id`" );
		}
		$has_eidx = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND INDEX_NAME = 'wf_email'",
			$wfr
		) );
		if ( ! $has_eidx ) {
			
			$wpdb->query( "ALTER TABLE `{$wfr}` ADD KEY `wf_email` (`workflow_id`,`email_hash`)" );
		}
	}

	
	$cg = aieo_coupon_grants_table();
	$run( "CREATE TABLE IF NOT EXISTS `{$cg}` (
		`id` bigint unsigned NOT NULL AUTO_INCREMENT,
		`code` varchar(64) NOT NULL DEFAULT '',
		`coupon_id` bigint unsigned NOT NULL DEFAULT 0,
		`source` varchar(64) NOT NULL DEFAULT '',
		`subject_id` bigint unsigned NOT NULL DEFAULT 0,
		`wp_user_id` bigint unsigned NOT NULL DEFAULT 0,
		`email` varchar(190) NOT NULL DEFAULT '',
		`channel` varchar(10) NOT NULL DEFAULT '',
		`offer_type` varchar(10) NOT NULL DEFAULT 'percent',
		`offer_value` decimal(12,2) NOT NULL DEFAULT 0,
		`status` varchar(12) NOT NULL DEFAULT 'granted',
		`redeemed_order_id` bigint unsigned NOT NULL DEFAULT 0,
		`minted_at` datetime DEFAULT NULL,
		`sent_at` datetime DEFAULT NULL,
		`clicked_at` datetime DEFAULT NULL,
		`redeemed_at` datetime DEFAULT NULL,
		`expires_at` datetime DEFAULT NULL,
		PRIMARY KEY (`id`),
		UNIQUE KEY `code` (`code`),
		KEY `source_user` (`source`,`wp_user_id`),
		KEY `status_expires` (`status`,`expires_at`),
		KEY `coupon` (`coupon_id`),
		KEY `email` (`email`)
	) {$charset_collate};" );

	
	$prop = aieo_proposals_table();
	$run( "CREATE TABLE IF NOT EXISTS `{$prop}` (
		`id` bigint unsigned NOT NULL AUTO_INCREMENT,
		`signature` char(40) NOT NULL DEFAULT '',
		`kind` varchar(30) NOT NULL DEFAULT '',
		`title` varchar(190) NOT NULL DEFAULT '',
		`narrative` text NULL,
		`rule_json` longtext NULL,
		`offer_type` varchar(10) NOT NULL DEFAULT 'none',
		`offer_value` decimal(12,2) NOT NULL DEFAULT 0,
		`timing` varchar(20) NOT NULL DEFAULT 'weekly',
		`channel` varchar(10) NOT NULL DEFAULT 'cascade',
		`audience_count` int unsigned NOT NULL DEFAULT 0,
		`predicted_ev` decimal(14,2) NOT NULL DEFAULT 0,
		`evidence_json` longtext NULL,
		`status` varchar(12) NOT NULL DEFAULT 'new',
		`created_audience_id` bigint unsigned NOT NULL DEFAULT 0,
		`created_workflow_id` bigint unsigned NOT NULL DEFAULT 0,
		`created` datetime DEFAULT NULL,
		`updated` datetime DEFAULT NULL,
		PRIMARY KEY (`id`),
		UNIQUE KEY `signature` (`signature`),
		KEY `status_ev` (`status`,`predicted_ev`)
	) {$charset_collate};" );

	
	$purchases = aieo_customer_purchases_table();
	
	$purchases_exists = (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $purchases ) ) === $purchases;
	if ( $purchases_exists ) {
		$cols = (array) $wpdb->get_col( $wpdb->prepare(
			'SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s',
			$purchases
		) );
		$cols     = array_map( 'strtolower', $cols );
		$has_hash = in_array( 'email_hash', $cols, true );

		if ( ! $has_hash ) {
			
			$aside = $purchases . '_pre_rekey';
			
			
			
			$wpdb->query( "DROP TABLE IF EXISTS `{$aside}`" );
			
			$renamed = false !== $wpdb->query( "RENAME TABLE `{$purchases}` TO `{$aside}`" );
			if ( $renamed ) {
				
				
				
				
				delete_option( 'aieo_customer_purchases_kinds' );
				if ( function_exists( 'aieo_debug_log' ) ) {
					aieo_debug_log( 'AIEO: re-keyed customer_purchases to email_hash; previous rows preserved as ' . $aside . '.' );
				}
				
				
				
				
				
				if ( function_exists( 'as_schedule_single_action' ) ) {
					as_schedule_single_action( time() + 60, 'aieo_customer_purchases_rollup', array(), 'aieo' );
				} elseif ( ! wp_next_scheduled( 'aieo_customer_purchases_rollup' ) ) {
					wp_schedule_single_event( time() + 60, 'aieo_customer_purchases_rollup' );
				}
			}
		}
	}
	$run( "CREATE TABLE IF NOT EXISTS `{$purchases}` (
		`email_hash` char(32) NOT NULL,
		`email` varchar(190) NOT NULL DEFAULT '',
		`kind` varchar(10) NOT NULL DEFAULT 'product',
		`ref_id` bigint unsigned NOT NULL,
		`first_at` datetime DEFAULT NULL,
		`last_at` datetime DEFAULT NULL,
		`times` int unsigned NOT NULL DEFAULT 0,
		PRIMARY KEY (`email_hash`,`kind`,`ref_id`),
		KEY `kind_ref` (`kind`,`ref_id`)
	) {$charset_collate};" );
}

if ( ! defined( 'AIEO_SUBJECTS_SCHEMA_VERSION' ) ) {
	define( 'AIEO_SUBJECTS_SCHEMA_VERSION', '2026-11-04-n' ); 
}

function aieo_subjects_maybe_install() {
	if ( get_option( 'aieo_subjects_schema_version' ) === AIEO_SUBJECTS_SCHEMA_VERSION ) {
		return;
	}
	if ( ! AIEO_Schema_Lock::acquire( 'aieo_subjects_schema_version' ) ) {
		return;
	}
	aieo_subjects_install_schema();
	
	
	
	global $wpdb;
	$wfr    = aieo_workflow_runs_table();
	$healed = (int) $wpdb->get_var( $wpdb->prepare(
		"SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = 'email_hash'",
		$wfr
	) );
	$lifetime = aieo_visitor_lifetime_table();
	if ( $healed > 0 && $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $lifetime ) ) === $lifetime ) {
		update_option( 'aieo_subjects_schema_version', AIEO_SUBJECTS_SCHEMA_VERSION, false );
	}
}