<?php

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

function aieo_social_posts_table() {
	global $wpdb;
	return $wpdb->prefix . 'aieo_social_posts';
}

function aieo_social_post_metrics_table() {
	global $wpdb;
	return $wpdb->prefix . 'aieo_social_post_metrics';
}

function aieo_boost_candidates_table() {
	global $wpdb;
	return $wpdb->prefix . 'aieo_boost_candidates';
}

function aieo_boost_weights_table() {
	global $wpdb;
	return $wpdb->prefix . 'aieo_boost_weights';
}

function aieo_campaign_forecasts_table() {
	global $wpdb;
	return $wpdb->prefix . 'aieo_campaign_forecasts';
}

function aieo_vector_priors_table() {
	global $wpdb;
	return $wpdb->prefix . 'aieo_vector_priors';
}

function aieo_social_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 );
		}
	};

	$posts    = aieo_social_posts_table();
	$metrics  = aieo_social_post_metrics_table();

	$boost_candidates   = aieo_boost_candidates_table();
	$boost_weights      = aieo_boost_weights_table();
	$campaign_forecasts = aieo_campaign_forecasts_table();

	
	$run( "CREATE TABLE IF NOT EXISTS `{$posts}` (
		`id` bigint unsigned NOT NULL AUTO_INCREMENT,
		`wf_id` bigint unsigned NOT NULL DEFAULT 0,
		`step_idx` int NOT NULL DEFAULT 0,
		`launch_batch` varchar(30) NOT NULL DEFAULT '',
		`platform` varchar(20) NOT NULL DEFAULT '',
		`account_id` bigint unsigned NOT NULL DEFAULT 0,
		`object_type` varchar(20) NOT NULL DEFAULT '',
		`object_id` bigint unsigned NOT NULL DEFAULT 0,
		`variant_label` varchar(40) DEFAULT NULL,
		`platform_post_id` varchar(191) DEFAULT NULL,
		`permalink` varchar(255) DEFAULT NULL,
		`media_json` longtext,
		`status` varchar(20) NOT NULL DEFAULT 'draft',
		`error` text,
		`published_at` datetime DEFAULT NULL,
		`created_at` datetime DEFAULT NULL,
		PRIMARY KEY (`id`),
		KEY `object` (`object_type`,`object_id`),
		KEY `platform` (`platform`),
		UNIQUE KEY `dedupe` (`wf_id`,`step_idx`,`platform`,`account_id`,`object_type`,`object_id`,`launch_batch`)
	) {$charset_collate};" );

	
	$run( "CREATE TABLE IF NOT EXISTS `{$metrics}` (
		`id` bigint unsigned NOT NULL AUTO_INCREMENT,
		`social_post_id` bigint unsigned NOT NULL,
		`stat_date` date NOT NULL,
		`impressions` bigint unsigned NOT NULL DEFAULT 0,
		`reach` bigint unsigned NOT NULL DEFAULT 0,
		`likes` bigint unsigned NOT NULL DEFAULT 0,
		`comments` bigint unsigned NOT NULL DEFAULT 0,
		`shares` bigint unsigned NOT NULL DEFAULT 0,
		`saves` bigint unsigned NOT NULL DEFAULT 0,
		`video_views` bigint unsigned NOT NULL DEFAULT 0,
		`link_clicks` bigint unsigned NOT NULL DEFAULT 0,
		`follower_delta` bigint NOT NULL DEFAULT 0,
		PRIMARY KEY (`id`),
		UNIQUE KEY `post_day` (`social_post_id`,`stat_date`),
		KEY `stat_date` (`stat_date`)
	) {$charset_collate};" );

	
	$run( "CREATE TABLE IF NOT EXISTS `{$boost_candidates}` (
		`id` bigint unsigned NOT NULL AUTO_INCREMENT,
		`object_type` varchar(20) NOT NULL DEFAULT '',
		`object_id` bigint unsigned NOT NULL DEFAULT 0,
		`seo_quadrant` varchar(20) NOT NULL DEFAULT '',
		`components_json` longtext,
		`boost_score` decimal(6,2) NOT NULL DEFAULT 0,
		`weights_version` varchar(20) NOT NULL DEFAULT 'seed_v1',
		`eligible_vectors` varchar(120) NOT NULL DEFAULT '',
		`veto_reasons` varchar(255) NOT NULL DEFAULT '',
		`computed_at` datetime DEFAULT NULL,
		PRIMARY KEY (`id`),
		UNIQUE KEY `object` (`object_type`,`object_id`),
		KEY `score` (`boost_score`)
	) {$charset_collate};" );

	
	$run( "CREATE TABLE IF NOT EXISTS `{$boost_weights}` (
		`id` bigint unsigned NOT NULL AUTO_INCREMENT,
		`weights_version` varchar(20) NOT NULL DEFAULT '',
		`weights_json` longtext,
		`source` varchar(20) NOT NULL DEFAULT 'seed_v1',
		`training_rows` int NOT NULL DEFAULT 0,
		`fitted_at` datetime DEFAULT NULL,
		`notes` varchar(255) NOT NULL DEFAULT '',
		PRIMARY KEY (`id`),
		UNIQUE KEY `version` (`weights_version`)
	) {$charset_collate};" );

	
	$run( "CREATE TABLE IF NOT EXISTS `{$campaign_forecasts}` (
		`id` bigint unsigned NOT NULL AUTO_INCREMENT,
		`wf_id` bigint unsigned NOT NULL DEFAULT 0,
		`proposal_id` bigint unsigned NOT NULL DEFAULT 0,
		`vector_slug` varchar(20) NOT NULL DEFAULT '',
		`object_type` varchar(20) NOT NULL DEFAULT '',
		`object_id` bigint unsigned NOT NULL DEFAULT 0,
		`components_json` longtext,
		`weights_version` varchar(20) NOT NULL DEFAULT '',
		`predicted_reach` bigint unsigned NOT NULL DEFAULT 0,
		`predicted_ctr` decimal(6,4) NOT NULL DEFAULT 0,
		`predicted_sessions` bigint unsigned NOT NULL DEFAULT 0,
		`predicted_orders` bigint unsigned NOT NULL DEFAULT 0,
		`predicted_revenue` decimal(12,2) NOT NULL DEFAULT 0,
		`predicted_cost` decimal(12,2) NOT NULL DEFAULT 0,
		`predicted_roi` decimal(8,2) NOT NULL DEFAULT 0,
		`confidence` varchar(10) NOT NULL DEFAULT '',
		`model_version` varchar(20) NOT NULL DEFAULT 'v1',
		`actual_posts` bigint unsigned DEFAULT NULL,
		`actual_reach` bigint unsigned DEFAULT NULL,
		`actual_sessions` bigint unsigned DEFAULT NULL,
		`actual_orders` bigint unsigned DEFAULT NULL,
		`actual_revenue` decimal(12,2) DEFAULT NULL,
		`error_ratio` decimal(8,3) DEFAULT NULL,
		`reconciled_at` datetime DEFAULT NULL,
		`created_at` datetime DEFAULT NULL,
		PRIMARY KEY (`id`),
		KEY `wf` (`wf_id`),
		KEY `object` (`object_type`,`object_id`)
	) {$charset_collate};" );

	

	
	
	if ( function_exists( 'aieo_dmm_ensure_column' ) ) {
		aieo_dmm_ensure_column( $posts, 'variant_label', 'varchar(40) DEFAULT NULL' );
		aieo_dmm_ensure_column( $posts, 'permalink', 'varchar(255) DEFAULT NULL' );
		aieo_dmm_ensure_column( $posts, 'media_json', 'longtext' );
		aieo_dmm_ensure_column( $posts, 'error', 'text' );
		
		aieo_dmm_ensure_column( $posts, 'step_idx', 'int NOT NULL DEFAULT 0' );
		aieo_dmm_ensure_column( $posts, 'launch_batch', "varchar(30) NOT NULL DEFAULT ''" );

		
		
		
		
		
		
		
		$idx_cols = $wpdb->get_col( "SHOW INDEX FROM `{$posts}` WHERE Key_name = 'dedupe'", 4 ); 
		if ( is_array( $idx_cols ) && $idx_cols && ! in_array( 'launch_batch', $idx_cols, true ) ) {
			
			$wpdb->query( "ALTER TABLE `{$posts}` DROP INDEX `dedupe`, ADD UNIQUE KEY `dedupe` (`wf_id`,`step_idx`,`platform`,`account_id`,`object_type`,`object_id`,`launch_batch`)" );
		}

		
		
		
		$wf_stats = $wpdb->prefix . 'aieo_workflow_stats';
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wf_stats ) ) === $wf_stats ) {
			aieo_dmm_ensure_column( $wf_stats, 'posts_published', 'int NOT NULL DEFAULT 0' );
			aieo_dmm_ensure_column( $wf_stats, 'social_impressions', 'bigint unsigned NOT NULL DEFAULT 0' );
			aieo_dmm_ensure_column( $wf_stats, 'social_clicks', 'bigint unsigned NOT NULL DEFAULT 0' );
			aieo_dmm_ensure_column( $wf_stats, 'social_engagements', 'bigint unsigned NOT NULL DEFAULT 0' );
		}

		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		$seo_posts = $wpdb->prefix . 'aieo_seo_posts';
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $seo_posts ) ) === $seo_posts ) {
			aieo_dmm_ensure_column( $seo_posts, 'social_video_id', 'bigint unsigned NOT NULL DEFAULT 0' );
			aieo_dmm_ensure_column( $seo_posts, 'social_video_url', "varchar(255) NOT NULL DEFAULT ''" );
		}
	}

	
	
	
	$seed_weights = wp_json_encode( array(
		'seo_quadrant'   => 0.15,
		'search_demand'  => 0.15,
		'commerce_demand' => 0.15,
		'forecast_units' => 0.15,
		'content_demand' => 0.10,
		'intent_mass'    => 0.10,
		'gradient'       => 0.10,
		'push_appetite'  => 0.10,
	) );
	$wpdb->query( $wpdb->prepare(
		"INSERT IGNORE INTO `{$boost_weights}`
			(`weights_version`,`weights_json`,`source`,`training_rows`,`fitted_at`,`notes`)
		 VALUES (%s, %s, %s, 0, NULL, %s)",
		'seed_v1',
		$seed_weights,
		'seed_v1',
		'Heuristic seed weights for the fused boost scorer.'
	) );
}

if ( ! defined( 'AIEO_SOCIAL_SCHEMA_VERSION' ) ) {
	define( 'AIEO_SOCIAL_SCHEMA_VERSION', '1.3.0' );
}

function aieo_social_maybe_install() {
	if ( get_option( 'aieo_social_schema_version' ) === AIEO_SOCIAL_SCHEMA_VERSION ) {
		return;
	}
	if ( ! AIEO_Schema_Lock::acquire( 'aieo_social_schema_version' ) ) {
		return;
	}
	aieo_social_install_schema();
	update_option( 'aieo_social_schema_version', AIEO_SOCIAL_SCHEMA_VERSION, false );
}