<?php

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

class AIEO_SEO_Opportunity_Scorer {

	const LAST_OPT     = 'aieo_seo_opportunities_built_at';
	const MIN_INTERVAL = 72000; 

	
	public static function enabled() {
		return class_exists( 'AIEO_DMM_Settings' ) && AIEO_DMM_Settings::get( 'seo.enabled', false );
	}

	
	public static function default_config() {
		return array(
			'min_views'             => 20,   
			'traffic_threshold'     => 50,   
			'conversion_threshold'  => 50,   
			
			'conv_w_cart'           => 0.60, 
			'conv_w_engagement'     => 0.30, 
			'conv_w_retention'      => 0.10, 
			
			'corner_w_entry'        => 0.40, 
			'corner_w_onward'       => 0.30, 
			'corner_w_engagement'   => 0.30, 
		);
	}

	
	public static function config() {
		$cfg = self::default_config();
		if ( class_exists( 'AIEO_DMM_Settings' ) ) {
			$saved = AIEO_DMM_Settings::get( 'seo.opportunity_config', array() );
			if ( is_array( $saved ) ) {
				foreach ( $cfg as $k => $v ) {
					if ( isset( $saved[ $k ] ) && is_numeric( $saved[ $k ] ) ) {
						$cfg[ $k ] = ( 'min_views' === $k || strpos( $k, 'threshold' ) !== false )
							? (int) $saved[ $k ]
							: (float) $saved[ $k ];
					}
				}
			}
		}
		return $cfg;
	}

	
	private static function ensure_table() {
		global $wpdb;
		$t = $wpdb->prefix . 'aieo_seo_opportunities';
		$has_type = $wpdb->get_var( $wpdb->prepare(
			'SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE table_schema = DATABASE() AND table_name = %s AND column_name = %s',
			$t,
			'object_type'
		) );
		if ( null === $has_type ) {
			
			
			$wpdb->query( "DROP TABLE IF EXISTS `{$t}`" );
		}
		
		$wpdb->query(
			"CREATE TABLE IF NOT EXISTS `{$t}` (
				`object_type`       VARCHAR(32) NOT NULL DEFAULT 'product',
				`object_id`         BIGINT(20) UNSIGNED NOT NULL,
				`quadrant`          VARCHAR(20) NOT NULL DEFAULT '',
				`traffic_score`     TINYINT UNSIGNED NOT NULL DEFAULT 0,
				`conversion_score`  TINYINT UNSIGNED NOT NULL DEFAULT 0,
				`cornerstone_score` TINYINT UNSIGNED NOT NULL DEFAULT 0,
				`priority`          TINYINT UNSIGNED NOT NULL DEFAULT 0,
				`recommendation`    VARCHAR(255) NOT NULL DEFAULT '',
				`signal_mix`        TEXT,
				`updated_at`        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
				PRIMARY KEY (`object_type`, `object_id`),
				KEY `idx_quadrant` (`quadrant`),
				KEY `idx_priority` (`priority`)
			) " . $wpdb->get_charset_collate()
		);
		return $t;
	}

	
	public static function maybe_rebuild( $force = false ) {
		if ( ! self::enabled() ) {
			return array( 'updated' => 0, 'skipped' => 'disabled' );
		}
		$last   = (int) get_option( self::LAST_OPT, 0 );
		$within = $last && ( (int) current_time( 'timestamp', true ) - $last ) < self::MIN_INTERVAL;
		if ( $within && ! $force ) {
			return array( 'updated' => 0, 'skipped' => 'throttled' );
		}
		$res = self::rebuild();
		update_option( self::LAST_OPT, (int) current_time( 'timestamp', true ), false );
		return $res;
	}

	
	public static function rebuild() {
		global $wpdb;
		$out = array( 'updated' => 0 );
		if ( ! self::enabled() ) {
			return $out;
		}
		$pm     = $wpdb->prefix . 'aieo_product_session_metrics';
		$has_pm = (string) $wpdb->get_var( $wpdb->prepare(
			"SELECT table_name FROM information_schema.TABLES WHERE table_schema = DATABASE() AND table_name = %s",
			$pm
		) ) === $pm;

		$t   = self::ensure_table();
		$cfg = self::config();

		
		
		
		
		
		$profile = class_exists( 'AIEO_SEO_Score' ) ? AIEO_SEO_Score::site_profile() : 'shop';
		$w_cart  = (float) $cfg['conv_w_cart'];
		$w_eng   = (float) $cfg['conv_w_engagement'];
		$w_ret   = (float) $cfg['conv_w_retention'];
		if ( 'shop' !== $profile && $w_cart > 0 ) {
			$rest = $w_eng + $w_ret;
			if ( $rest > 0 ) {
				$w_eng += $w_cart * $w_eng / $rest;
				$w_ret += $w_cart * $w_ret / $rest;
			} else {
				$w_eng = 0.75 * $w_cart; 
				$w_ret = 0.25 * $w_cart;
			}
			$w_cart = 0.0;
		}

		$t_thr  = (int) $cfg['traffic_threshold'];
		$c_thr  = (int) $cfg['conversion_threshold'];
		$now    = current_time( 'mysql' );
		$shop   = ( 'shop' === $profile );
		$values = array();
		$args   = array();

		
		
		$classify = static function ( $object_type, $object_id, $traffic, $conv, $corner, $signal, $wording ) use ( &$values, &$args, &$out, $t_thr, $c_thr, $now ) {
			$hi_traffic = $traffic >= $t_thr;
			$hi_conv    = $conv >= $c_thr;
			$shopish    = ( 'shop' === $wording );
			if ( $hi_traffic && $hi_conv ) {
				$quad = 'defend';
				$rec  = __( 'Strong performer — rank-protect and watch for engagement decay.', 'ai-eshop-optimizer' );
				$prio = 40 + (int) round( ( $traffic + $conv ) / 10 );
			} elseif ( $hi_traffic && ! $hi_conv ) {
				$quad = 'intent_mismatch';
				$rec  = $shopish
					? __( 'High traffic but low on-site conversion — the keyword/title likely attracts the wrong intent. Re-target to the query that converts.', 'ai-eshop-optimizer' )
					: __( 'High traffic but low engagement — the title/keyword promises something the page doesn’t deliver. Re-angle it to the intent that keeps visitors reading.', 'ai-eshop-optimizer' );
				$prio = 60 + (int) round( ( $traffic - $conv ) / 5 );
			} elseif ( ! $hi_traffic && $hi_conv ) {
				$quad = 'hidden_gem';
				$rec  = $shopish
					? __( 'Converts well but under-exposed — promote to cornerstone, add internal links, raise sitemap priority, target higher-volume keywords.', 'ai-eshop-optimizer' )
					: __( 'Engages strongly but under-exposed — promote to cornerstone, add internal links, raise sitemap priority, target higher-volume keywords.', 'ai-eshop-optimizer' );
				$prio = 70 + (int) round( ( $conv - $traffic ) / 5 );
			} else {
				$quad = 'prune';
				$rec  = $shopish
					? __( 'Low traffic and low conversion — candidate to merge/noindex and reclaim crawl budget.', 'ai-eshop-optimizer' )
					: __( 'Low traffic and low engagement — candidate to refresh, merge or noindex and reclaim crawl budget.', 'ai-eshop-optimizer' );
				$prio = 10 + (int) round( ( 100 - ( $traffic + $conv ) ) / 20 );
			}
			$values[] = '(%s,%d,%s,%d,%d,%d,%d,%s,%s,%s)';
			array_push( $args, $object_type, $object_id, $quad, $traffic, $conv, $corner, max( 0, min( 100, $prio ) ), $rec, $signal, $now );
			$out['updated']++;
		};

		
		if ( $has_pm ) {
			
			
			
			
			$rows = $wpdb->get_results( $wpdb->prepare(
				"SELECT product_id, total_views, unique_sessions_viewed, view_to_cart_rate,
				        engagement_score, bounce_rate, times_first_page, times_last_page,
				        unique_sessions_added_to_cart,
				        ROUND(100 * PERCENT_RANK() OVER (ORDER BY unique_sessions_viewed)) AS traffic_pct,
				        ROUND(100 * PERCENT_RANK() OVER (
				            ORDER BY ( view_to_cart_rate * %f
				                     + (engagement_score/100) * %f
				                     + (1 - LEAST(1, bounce_rate)) * %f )
				        )) AS conversion_pct,
				        ROUND(100 * PERCENT_RANK() OVER (
				            ORDER BY ( (times_first_page / GREATEST(total_views,1)) * %f
				                     + (1 - (times_last_page / GREATEST(total_views,1))) * %f
				                     + (engagement_score/100) * %f )
				        )) AS cornerstone_pct
				 FROM `{$pm}`
				 WHERE total_views >= %d",
				$w_cart, $w_eng, $w_ret,
				$cfg['corner_w_entry'], $cfg['corner_w_onward'], $cfg['corner_w_engagement'],
				$cfg['min_views']
			), ARRAY_A );
			foreach ( (array) $rows as $r ) {
				$signal = wp_json_encode( array(
					'traffic_pct'     => (int) $r['traffic_pct'],
					'conversion_pct'  => (int) $r['conversion_pct'],
					'cornerstone_pct' => (int) $r['cornerstone_pct'],
					'views'           => (int) $r['unique_sessions_viewed'],
					'cart_rate'       => round( (float) $r['view_to_cart_rate'], 4 ),
					'engagement'      => round( (float) $r['engagement_score'], 1 ),
					'bounce'          => round( (float) $r['bounce_rate'], 4 ),
				) );
				$classify( 'product', (int) $r['product_id'], (int) $r['traffic_pct'], (int) $r['conversion_pct'], (int) $r['cornerstone_pct'], $signal, $shop ? 'shop' : 'content' );
			}
		}

		
		
		
		
		
		
		$cv     = $wpdb->prefix . 'aieo_core_content_vitals';
		$has_cv = (string) $wpdb->get_var( $wpdb->prepare(
			'SELECT table_name FROM information_schema.TABLES WHERE table_schema = DATABASE() AND table_name = %s',
			$cv
		) ) === $cv;
		if ( $has_cv ) {
			
			$crows = $wpdb->get_results( $wpdb->prepare(
				"SELECT post_type, content_id, views_30d, visitors_30d, engagement_score, content_demand,
				        ROUND(100 * PERCENT_RANK() OVER (ORDER BY visitors_30d)) AS traffic_pct,
				        ROUND(100 * PERCENT_RANK() OVER (ORDER BY engagement_score)) AS conversion_pct,
				        ROUND(100 * PERCENT_RANK() OVER (ORDER BY content_demand)) AS cornerstone_pct
				 FROM `{$cv}`
				 WHERE views_30d >= %d",
				$cfg['min_views']
			), ARRAY_A );
			foreach ( (array) $crows as $r ) {
				$signal = wp_json_encode( array(
					'traffic_pct'     => (int) $r['traffic_pct'],
					'conversion_pct'  => (int) $r['conversion_pct'],
					'cornerstone_pct' => (int) $r['cornerstone_pct'],
					'views'           => (int) $r['visitors_30d'],
					'engagement'      => round( (float) $r['engagement_score'], 1 ),
					'content_demand'  => (int) $r['content_demand'],
				) );
				$classify( (string) $r['post_type'], (int) $r['content_id'], (int) $r['traffic_pct'], (int) $r['conversion_pct'], (int) $r['cornerstone_pct'], $signal, 'content' );
			}
		}

		
		
		
		$wpdb->query( "TRUNCATE TABLE `{$t}`" );
		if ( empty( $values ) ) {
			return $out;
		}
		$cols = '(object_type,object_id,quadrant,traffic_score,conversion_score,cornerstone_score,priority,recommendation,signal_mix,updated_at)';
		foreach ( array_chunk( $values, 200 ) as $ci => $chunk ) {
			$slice = array_slice( $args, $ci * 200 * 10, count( $chunk ) * 10 );
			
			$wpdb->query( $wpdb->prepare(
				"REPLACE INTO `{$t}` {$cols} VALUES " . implode( ',', $chunk ),
				...$slice
			) );
		}

		return $out;
	}
}
