<?php

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

class AIEO_Content_Vitals {

	const ROLLUP_TABLE = 'aieo_content_engagement_daily';
	const VITALS_TABLE = 'aieo_core_content_vitals';

	
	public static function enabled() {
		return (bool) apply_filters( 'aieo_content_vitals_enabled', true );
	}

	private static function table_exists( $t ) {
		global $wpdb;
		return (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $t ) ) === $t;
	}

	
	public static function covered_types() {
		if ( ! class_exists( 'AIEO_Content_Types' ) ) {
			require_once AIEO_DIR . 'includes/class-aieo-content-types.php';
		}
		$types = array_keys( AIEO_Content_Types::post_types() );
		return array_values( array_diff( $types, array( 'product' ) ) );
	}

	
	
	

	
	private static function charset() {
		global $wpdb;
		static $line = null;
		if ( null !== $line ) {
			return $line;
		}
		$coll = (string) $wpdb->get_var( $wpdb->prepare(
			'SELECT TABLE_COLLATION FROM information_schema.TABLES WHERE table_schema = DATABASE() AND table_name = %s',
			$wpdb->posts
		) );
		if ( '' !== $coll && false !== strpos( $coll, '_' ) ) {
			$cs   = substr( $coll, 0, strpos( $coll, '_' ) );
			$line = "ENGINE=InnoDB DEFAULT CHARSET={$cs} COLLATE={$coll}";
		} else {
			$line = 'ENGINE=InnoDB ' . $wpdb->get_charset_collate();
		}
		return $line;
	}

	public static function ensure_tables() {
		global $wpdb;
		$charset = self::charset();
		$rollup  = $wpdb->prefix . self::ROLLUP_TABLE;
		$vitals  = $wpdb->prefix . self::VITALS_TABLE;

		
		$wpdb->query( "CREATE TABLE IF NOT EXISTS `{$rollup}` (
			`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			`object_type` VARCHAR(32) NOT NULL,
			`object_id` BIGINT UNSIGNED NOT NULL,
			`date` DATE NOT NULL,
			`views` INT UNSIGNED NOT NULL DEFAULT 0,
			`visitors` INT UNSIGNED NOT NULL DEFAULT 0,
			`avg_time_sec` DECIMAL(10,2) NOT NULL DEFAULT 0,
			`avg_scroll_pct` DECIMAL(5,2) NOT NULL DEFAULT 0,
			`views_s0` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			`views_s1` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			`views_s2` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			`views_s3` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			`views_s4` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			`views_s5` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			`views_s6` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			`views_s7` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			`views_earlybird` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			`views_morning` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			`views_afternoon` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			`views_nightbird` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			`goals` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			PRIMARY KEY (`id`),
			UNIQUE KEY `obj_date` (`object_type`,`object_id`,`date`),
			KEY `date` (`date`),
			KEY `obj` (`object_type`,`object_id`)
		) {$charset}" );

		
		
		$has_goals = $wpdb->get_var( $wpdb->prepare(
			'SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE table_schema = DATABASE() AND table_name = %s AND column_name = %s',
			$rollup,
			'goals'
		) );
		if ( null === $has_goals ) {
			
			$wpdb->query( "ALTER TABLE `{$rollup}` ADD COLUMN `goals` SMALLINT UNSIGNED NOT NULL DEFAULT 0 AFTER `views_nightbird`" );
		}

		
		$wpdb->query( self::vitals_ddl( $vitals, $charset ) );

		
		self::ensure_view();
	}

	
	public static function ensure_view() {
		global $wpdb;
		$rollup = $wpdb->prefix . self::ROLLUP_TABLE;
		$vitals = $wpdb->prefix . self::VITALS_TABLE;
		$view   = $wpdb->prefix . 'aieo_content_consumption_facts';
		
		$wpdb->query( "CREATE OR REPLACE SQL SECURITY INVOKER VIEW `{$view}` AS
			SELECT
				r.object_type,
				r.object_id,
				COALESCE(v.title, CONCAT('#', r.object_id)) AS title,
				v.content_url,
				r.`date`,
				DAYOFWEEK(r.`date`)  AS day_of_week,
				DAYOFMONTH(r.`date`) AS day_of_month,
				r.views,
				r.visitors,
				r.goals,
				r.avg_time_sec,
				r.avg_scroll_pct,
				CASE
					WHEN r.views_afternoon >= r.views_morning AND r.views_afternoon >= r.views_earlybird AND r.views_afternoon >= r.views_nightbird THEN 'Afternoon'
					WHEN r.views_morning   >= r.views_earlybird AND r.views_morning >= r.views_nightbird THEN 'Morning'
					WHEN r.views_earlybird >= r.views_nightbird THEN 'Early Bird'
					ELSE 'Night Bird'
				END AS readerstime,
				CASE
					WHEN DAYOFWEEK(r.`date`) = 1 OR DAYOFWEEK(r.`date`) = 7 THEN 'Weekend'
					WHEN DAYOFWEEK(r.`date`) > 1 AND DAYOFWEEK(r.`date`) < 5 THEN 'Beginning of Week'
					WHEN DAYOFWEEK(r.`date`) > 4 AND DAYOFWEEK(r.`date`) < 6 THEN 'Mid Week'
					ELSE 'Friday'
				END AS readersday,
				CASE
					WHEN DAYOFMONTH(r.`date`) > 0  AND DAYOFMONTH(r.`date`) < 11 THEN 'Conservative'
					WHEN DAYOFMONTH(r.`date`) > 10 AND DAYOFMONTH(r.`date`) < 21 THEN 'Sound'
					WHEN DAYOFMONTH(r.`date`) > 20 AND DAYOFMONTH(r.`date`) < 28 THEN 'Optimist'
					ELSE 'End of Month'
				END AS readerspayrol,
				r.views_earlybird,
				r.views_morning,
				r.views_afternoon,
				r.views_nightbird,
				r.views_s0, r.views_s1, r.views_s2, r.views_s3,
				r.views_s4, r.views_s5, r.views_s6, r.views_s7
			FROM `{$rollup}` r
			LEFT JOIN `{$vitals}` v ON v.post_type = r.object_type AND v.content_id = r.object_id" );
	}

	
	private static function vitals_ddl( $table, $charset ) {
		return "CREATE TABLE IF NOT EXISTS `{$table}` (
			`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			`content_id` BIGINT UNSIGNED NOT NULL,
			`post_type` VARCHAR(32) NOT NULL,
			`title` VARCHAR(255) DEFAULT NULL,
			`content_url` VARCHAR(300) DEFAULT NULL,
			`author_id` BIGINT UNSIGNED DEFAULT NULL,
			`word_count` INT UNSIGNED NOT NULL DEFAULT 0,
			`comment_count` INT UNSIGNED NOT NULL DEFAULT 0,
			`content_created` DATETIME DEFAULT NULL,
			`content_last_updated` DATETIME DEFAULT NULL,
			`content_age` INT DEFAULT NULL,
			`first_viewed_date` DATE DEFAULT NULL,
			`last_viewed_date` DATE DEFAULT NULL,
			`views_7d` INT UNSIGNED NOT NULL DEFAULT 0,
			`views_30d` INT UNSIGNED NOT NULL DEFAULT 0,
			`views_lifetime` INT UNSIGNED NOT NULL DEFAULT 0,
			`visitors_30d` INT UNSIGNED NOT NULL DEFAULT 0,
			`avg_time_30d` DECIMAL(10,2) NOT NULL DEFAULT 0,
			`avg_scroll_30d` DECIMAL(5,2) NOT NULL DEFAULT 0,
			`views_per_day_30d` DECIMAL(10,3) NOT NULL DEFAULT 0,
			`seg_views_30d` VARCHAR(120) DEFAULT NULL,
			`weekend_views_30d` INT UNSIGNED NOT NULL DEFAULT 0,
			`goals_30d` INT UNSIGNED NOT NULL DEFAULT 0,
			`goal_rate_30d` DECIMAL(8,4) NOT NULL DEFAULT 0,
			`engagement_score` DECIMAL(5,2) NOT NULL DEFAULT 0,
			`indexable` TINYINT(1) NOT NULL DEFAULT 1,
			`seo_score` TINYINT UNSIGNED DEFAULT NULL,
			`is_pillar` TINYINT(1) NOT NULL DEFAULT 0,
			`content_demand` TINYINT UNSIGNED NOT NULL DEFAULT 0,
			`gsc_demand_90d` DECIMAL(12,2) NOT NULL DEFAULT 0,
			PRIMARY KEY (`id`),
			UNIQUE KEY `obj` (`post_type`,`content_id`),
			KEY `content_id` (`content_id`),
			KEY `type_demand` (`post_type`,`content_demand`),
			KEY `last_viewed` (`last_viewed_date`)
		) {$charset}";
	}

	
	
	

	
	public static function compute_daily( $date ) {
		global $wpdb;
		if ( ! self::enabled() ) {
			return 0;
		}
		$ses    = $wpdb->prefix . 'aieo_sessions';
		$target = $wpdb->prefix . self::ROLLUP_TABLE;
		if ( ! self::table_exists( $ses ) ) {
			return 0;
		}
		self::ensure_tables();

		
		$start_ms = strtotime( $date . ' 00:00:00 UTC' ) * 1000;
		$end_ms   = strtotime( $date . ' 23:59:59 UTC' ) * 1000 + 999;

		
		
		$offset_sec = (int) ( wp_timezone()->getOffset( new DateTime( $date . ' 12:00:00', new DateTimeZone( 'UTC' ) ) ) );

		
		
		$urls = $wpdb->get_results( $wpdb->prepare(
			"SELECT page_url,
					COUNT(*) AS views,
					COUNT(DISTINCT session_id) AS visitors,
					AVG(time_on_page) AS avg_time,
					AVG(scroll_depth) AS avg_scroll
			   FROM {$ses}
			  WHERE event_type = 'page_view'
				AND timestamp BETWEEN %d AND %d
				AND ( product_id IS NULL OR product_id = 0 )
				AND ( category_id IS NULL OR category_id = 0 )
				AND page_url IS NOT NULL AND page_url <> ''
			  GROUP BY page_url",
			$start_ms,
			$end_ms
		), ARRAY_A );
		
		
		

		
		
		
		
		
		$hour_rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT page_url,
					-- Epoch arithmetic, NOT FROM_UNIXTIME: unix 0 is 1970-01-01 00:00:00
					-- UTC by definition, so this yields the site-local hour on any host.
					-- FROM_UNIXTIME() applies the SESSION timezone as well, which
					-- double-counted the offset on a host whose clock was not UTC.
					HOUR( DATE_ADD('1970-01-01 00:00:00', INTERVAL ( (timestamp/1000) + %d ) SECOND) ) AS hr,
					COUNT(*) AS views
			   FROM {$ses}
			  WHERE event_type = 'page_view'
				AND timestamp BETWEEN %d AND %d
				AND ( product_id IS NULL OR product_id = 0 )
				AND ( category_id IS NULL OR category_id = 0 )
				AND page_url IS NOT NULL AND page_url <> ''
			  GROUP BY page_url, hr",
			$offset_sec,
			$start_ms,
			$end_ms
		), ARRAY_A );
		$segs_by_url  = array();
		$bands_by_url = array();
		foreach ( (array) $hour_rows as $hr_row ) {
			$url = (string) $hr_row['page_url'];
			$hr  = max( 0, min( 23, (int) $hr_row['hr'] ) );
			$v   = (int) $hr_row['views'];
			$seg = (int) floor( $hr / 3 );
			if ( ! isset( $segs_by_url[ $url ] ) ) {
				$segs_by_url[ $url ] = array_fill( 0, 8, 0 );
			}
			$segs_by_url[ $url ][ $seg ] += $v;
			if ( $hr > 4 && $hr < 11 ) {
				$band = 'earlybird';
			} elseif ( $hr > 10 && $hr < 14 ) {
				$band = 'morning';
			} elseif ( $hr > 13 && $hr < 21 ) {
				$band = 'afternoon';
			} else {
				$band = 'nightbird';
			}
			if ( ! isset( $bands_by_url[ $url ] ) ) {
				$bands_by_url[ $url ] = array( 'earlybird' => 0, 'morning' => 0, 'afternoon' => 0, 'nightbird' => 0 );
			}
			$bands_by_url[ $url ][ $band ] += $v;
		}

		
		
		$goal_rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT page_url, COUNT(*) AS goals
			   FROM {$ses}
			  WHERE event_type = 'goal'
				AND timestamp BETWEEN %d AND %d
				AND page_url IS NOT NULL AND page_url <> ''
			  GROUP BY page_url",
			$start_ms,
			$end_ms
		), ARRAY_A );
		$goals_by_url = array();
		foreach ( (array) $goal_rows as $gr ) {
			$goals_by_url[ (string) $gr['page_url'] ] = (int) $gr['goals'];
		}

		if ( empty( $urls ) && empty( $goals_by_url ) ) {
			return 0;
		}

		$covered = array_fill_keys( self::covered_types(), true );

		
		$by_obj = array();
		foreach ( $urls as $u ) {
			$obj = self::resolve_url( (string) $u['page_url'] );
			if ( null === $obj || ! isset( $covered[ $obj[0] ] ) ) {
				continue; 
			}
			$key = $obj[0] . ':' . $obj[1];
			if ( ! isset( $by_obj[ $key ] ) ) {
				$by_obj[ $key ] = array(
					'type' => $obj[0], 'id' => $obj[1],
					'views' => 0, 'visitors' => 0, 'time_sum' => 0.0, 'scroll_sum' => 0.0,
					'segs'  => array_fill( 0, 8, 0 ),
					'bands' => array( 'earlybird' => 0, 'morning' => 0, 'afternoon' => 0, 'nightbird' => 0 ),
					'goals' => 0,
				);
			}
			$views = (int) $u['views'];
			$by_obj[ $key ]['views']      += $views;
			$by_obj[ $key ]['visitors']   += (int) $u['visitors'];
			$by_obj[ $key ]['time_sum']   += ( (float) $u['avg_time'] ) * $views;
			$by_obj[ $key ]['scroll_sum'] += ( (float) $u['avg_scroll'] ) * $views;
			if ( isset( $segs_by_url[ (string) $u['page_url'] ] ) ) {
				foreach ( $segs_by_url[ (string) $u['page_url'] ] as $s => $v ) {
					$by_obj[ $key ]['segs'][ $s ] += $v;
				}
			}
			if ( isset( $bands_by_url[ (string) $u['page_url'] ] ) ) {
				foreach ( $bands_by_url[ (string) $u['page_url'] ] as $b => $v ) {
					$by_obj[ $key ]['bands'][ $b ] += $v;
				}
			}
			if ( isset( $goals_by_url[ (string) $u['page_url'] ] ) ) {
				$by_obj[ $key ]['goals'] += $goals_by_url[ (string) $u['page_url'] ];
				unset( $goals_by_url[ (string) $u['page_url'] ] );
			}
		}
		
		
		foreach ( $goals_by_url as $gurl => $gcount ) {
			$obj = self::resolve_url( (string) $gurl );
			if ( null === $obj || ! isset( $covered[ $obj[0] ] ) ) {
				continue;
			}
			$key = $obj[0] . ':' . $obj[1];
			if ( ! isset( $by_obj[ $key ] ) ) {
				$by_obj[ $key ] = array(
					'type' => $obj[0], 'id' => $obj[1],
					'views' => 0, 'visitors' => 0, 'time_sum' => 0.0, 'scroll_sum' => 0.0,
					'segs'  => array_fill( 0, 8, 0 ),
					'bands' => array( 'earlybird' => 0, 'morning' => 0, 'afternoon' => 0, 'nightbird' => 0 ),
					'goals' => 0,
				);
			}
			$by_obj[ $key ]['goals'] += (int) $gcount;
		}
		if ( empty( $by_obj ) ) {
			return 0;
		}

		$written = 0;
		foreach ( $by_obj as $o ) {
			$views = max( 1, (int) $o['views'] );
			
			$ok = $wpdb->query( $wpdb->prepare(
				"INSERT INTO `{$target}`
					(object_type, object_id, `date`, views, visitors, avg_time_sec, avg_scroll_pct,
					 views_s0, views_s1, views_s2, views_s3, views_s4, views_s5, views_s6, views_s7,
					 views_earlybird, views_morning, views_afternoon, views_nightbird, goals)
				 VALUES (%s, %d, %s, %d, %d, %f, %f, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d)
				 ON DUPLICATE KEY UPDATE
					views = VALUES(views), visitors = VALUES(visitors),
					avg_time_sec = VALUES(avg_time_sec), avg_scroll_pct = VALUES(avg_scroll_pct),
					views_s0 = VALUES(views_s0), views_s1 = VALUES(views_s1), views_s2 = VALUES(views_s2),
					views_s3 = VALUES(views_s3), views_s4 = VALUES(views_s4), views_s5 = VALUES(views_s5),
					views_s6 = VALUES(views_s6), views_s7 = VALUES(views_s7),
					views_earlybird = VALUES(views_earlybird), views_morning = VALUES(views_morning),
					views_afternoon = VALUES(views_afternoon), views_nightbird = VALUES(views_nightbird),
					goals = VALUES(goals)",
				$o['type'], $o['id'], $date,
				(int) $o['views'], (int) $o['visitors'],
				$o['time_sum'] / $views, $o['scroll_sum'] / $views,
				$o['segs'][0], $o['segs'][1], $o['segs'][2], $o['segs'][3],
				$o['segs'][4], $o['segs'][5], $o['segs'][6], $o['segs'][7],
				$o['bands']['earlybird'], $o['bands']['morning'], $o['bands']['afternoon'], $o['bands']['nightbird'],
				(int) $o['goals']
			) );
			if ( false !== $ok ) {
				$written++;
			}
		}
		return $written;
	}

	
	private static function resolve_url( $url ) {
		static $memo = array();
		$path = (string) wp_parse_url( $url, PHP_URL_PATH );
		if ( '' === $path ) {
			return null;
		}
		$path = '/' . ltrim( rawurldecode( $path ), '/' );
		if ( '/' !== $path ) {
			$path = rtrim( $path, '/' );
		}
		if ( isset( $memo[ $path ] ) ) {
			return $memo[ $path ];
		}
		global $wpdb;
		$out = null;
		$sp  = $wpdb->prefix . 'aieo_seo_posts';
		if ( self::table_exists( $sp ) ) {
			
			
			
			$row = $wpdb->get_row( $wpdb->prepare(
				"SELECT object_type, object_id FROM `{$sp}` WHERE canonical_url IN (%s, %s) LIMIT 1",
				$path,
				$path . '/'
			), ARRAY_A );
			if ( $row ) {
				$out = array( (string) $row['object_type'], (int) $row['object_id'] );
			}
		}
		if ( null === $out ) {
			$pid = url_to_postid( home_url( $path ) );
			if ( $pid > 0 ) {
				$out = array( (string) get_post_type( $pid ), (int) $pid );
			}
		}
		$memo[ $path ] = $out;
		return $out;
	}

	
	
	

	
	public static function build_vitals() {
		global $wpdb;
		$out = array( 'rows' => 0 );
		if ( ! self::enabled() ) {
			return $out;
		}
		self::ensure_tables();

		$vitals = $wpdb->prefix . self::VITALS_TABLE;
		$rollup = $wpdb->prefix . self::ROLLUP_TABLE;
		$sp     = $wpdb->prefix . 'aieo_seo_posts';
		$sc     = $wpdb->prefix . 'aieo_seo_scores';
		$new    = $vitals . '_new';
		$old    = $vitals . '_old';
		$charset = self::charset();

		$types = self::covered_types();
		if ( empty( $types ) ) {
			return $out;
		}
		$type_in = "'" . implode( "','", array_map( 'esc_sql', $types ) ) . "'";

		
		$wpdb->query( "DROP TABLE IF EXISTS `{$new}`" );
		
		$wpdb->query( self::vitals_ddl( $new, $charset ) );

		$has_sp = self::table_exists( $sp );
		$has_sc = self::table_exists( $sc );
		$sp_join = $has_sp ? "LEFT JOIN `{$sp}` sp ON sp.object_type = p.post_type AND sp.object_id = p.ID" : '';
		$sc_join = $has_sc ? "LEFT JOIN `{$sc}` sc ON sc.object_type = p.post_type AND sc.object_id = p.ID" : '';
		$sp_url  = $has_sp ? 'sp.canonical_url' : 'NULL';
		$sp_idx  = $has_sp ? 'COALESCE(sp.indexable, 1)' : '1';
		$sc_scr  = $has_sc ? 'sc.score' : 'NULL';
		$sc_pil  = $has_sc ? 'COALESCE(sc.is_pillar, 0)' : '0';

		
		
		
		
		
		
		$gsc_tbl  = $wpdb->prefix . 'aieo_gsc_daily';
		$has_gsc  = self::table_exists( $gsc_tbl );
		if ( $has_gsc ) {
			
			$has_gsc = (bool) $wpdb->get_var(
				"SELECT 1 FROM `{$gsc_tbl}` WHERE dim_type = 'page' AND object_id > 0 LIMIT 1"
			);
		}
		$gsc_join = $has_gsc ? "LEFT JOIN (
			SELECT object_type, object_id,
			       SUM(clicks) + 0.02 * SUM(impressions) AS demand_raw
			FROM `{$gsc_tbl}`
			WHERE dim_type = 'page' AND object_id > 0
			  AND `date` >= UTC_DATE() - INTERVAL 90 DAY
			GROUP BY object_type, object_id
		) gsc ON gsc.object_type = p.post_type AND gsc.object_id = p.ID" : '';
		$gsc_raw  = $has_gsc ? 'COALESCE(gsc.demand_raw, 0)' : '0';
		
		
		$gsc_c    = $has_gsc ? "(100 * (1 - EXP( -COALESCE(gsc.demand_raw,0) / 150 )))" : '0';

		
		
		
		
		
		
		
		
		
		
		
		
		
		$eng_base = 'LEAST(1, 0.625 * (COALESCE(w30.avg_scroll,0)/100) + 0.375 * LEAST(1, COALESCE(w30.avg_time,0)/180))';
		$eng      = "CASE WHEN COALESCE(w30.goals,0) > 0
				THEN LEAST(1, 0.5 * LEAST(1, (COALESCE(w30.goals,0) / GREATEST(COALESCE(w30.views,0),1)) * 20) + 0.5 * {$eng_base})
				ELSE {$eng_base} END";
		$views_demand = "CASE WHEN life.last_viewed IS NULL THEN 0 ELSE
				LEAST(100, ROUND(
					(100 * (1 - EXP( -COALESCE(w30.visitors,0) / 50 )))
					* (0.5 + 0.5 * {$eng})
					* GREATEST(0.35, POW(0.5, DATEDIFF(UTC_DATE(), life.last_viewed) / 30))
					* IF(p.post_modified >= UTC_TIMESTAMP() - INTERVAL 365 DAY, 1.0, 0.9)
				))
			END";
		
		$sql = "INSERT INTO `{$new}`
			(content_id, post_type, title, content_url, author_id, word_count, comment_count,
			 content_created, content_last_updated, content_age,
			 first_viewed_date, last_viewed_date, views_7d, views_30d, views_lifetime,
			 visitors_30d, avg_time_30d, avg_scroll_30d, views_per_day_30d,
			 seg_views_30d, weekend_views_30d, goals_30d, goal_rate_30d, engagement_score,
			 indexable, seo_score, is_pillar, content_demand, gsc_demand_90d)
		SELECT
			p.ID, p.post_type, p.post_title, {$sp_url}, p.post_author,
			GREATEST(0, LENGTH(TRIM(p.post_content)) - LENGTH(REPLACE(TRIM(p.post_content), ' ', '')) + 1),
			p.comment_count, p.post_date, p.post_modified,
			DATEDIFF(UTC_DATE(), p.post_date),
			life.first_viewed, life.last_viewed,
			COALESCE(w7.views, 0), COALESCE(w30.views, 0), COALESCE(life.views, 0),
			COALESCE(w30.visitors, 0), COALESCE(w30.avg_time, 0), COALESCE(w30.avg_scroll, 0),
			ROUND(COALESCE(w30.views, 0) / 30, 3),
			CONCAT_WS(',', COALESCE(w30.s0,0), COALESCE(w30.s1,0), COALESCE(w30.s2,0), COALESCE(w30.s3,0),
			               COALESCE(w30.s4,0), COALESCE(w30.s5,0), COALESCE(w30.s6,0), COALESCE(w30.s7,0)),
			COALESCE(w30.weekend_views, 0),
			COALESCE(w30.goals, 0),
			ROUND(COALESCE(w30.goals, 0) / GREATEST(COALESCE(w30.views, 0), 1), 4),
			ROUND( 100 * {$eng}, 2),
			{$sp_idx}, {$sc_scr}, {$sc_pil},
			LEAST(100, ROUND(GREATEST(
				{$views_demand},
				0.6 * ({$views_demand}) + 0.4 * {$gsc_c}
			))),
			{$gsc_raw}
		FROM {$wpdb->posts} p
		{$sp_join}
		{$sc_join}
		LEFT JOIN (
			SELECT object_type, object_id, MIN(`date`) AS first_viewed, MAX(`date`) AS last_viewed, SUM(views) AS views
			FROM `{$rollup}` GROUP BY object_type, object_id
		) life ON life.object_type = p.post_type AND life.object_id = p.ID
		LEFT JOIN (
			SELECT object_type, object_id, SUM(views) AS views, SUM(visitors) AS visitors,
			       SUM(views * avg_time_sec) / NULLIF(SUM(views),0) AS avg_time,
			       SUM(views * avg_scroll_pct) / NULLIF(SUM(views),0) AS avg_scroll,
			       SUM(views_s0) AS s0, SUM(views_s1) AS s1, SUM(views_s2) AS s2, SUM(views_s3) AS s3,
			       SUM(views_s4) AS s4, SUM(views_s5) AS s5, SUM(views_s6) AS s6, SUM(views_s7) AS s7,
			       SUM(CASE WHEN DAYOFWEEK(`date`) IN (1,7) THEN views ELSE 0 END) AS weekend_views,
			       SUM(goals) AS goals
			FROM `{$rollup}` WHERE `date` >= UTC_DATE() - INTERVAL 30 DAY
			GROUP BY object_type, object_id
		) w30 ON w30.object_type = p.post_type AND w30.object_id = p.ID
		LEFT JOIN (
			SELECT object_type, object_id, SUM(views) AS views
			FROM `{$rollup}` WHERE `date` >= UTC_DATE() - INTERVAL 7 DAY
			GROUP BY object_type, object_id
		) w7 ON w7.object_type = p.post_type AND w7.object_id = p.ID
		{$gsc_join}
		WHERE p.post_status = 'publish' AND p.post_type IN ({$type_in})";

		
		$ins = $wpdb->query( $sql );
		if ( false === $ins ) {
			
			
			$out['error'] = (string) $wpdb->last_error;
			if ( function_exists( 'aieo_debug_log' ) ) {
				aieo_debug_log( 'AIEO_Content_Vitals build failed: ' . $out['error'] );
			}
			
			$wpdb->query( "DROP TABLE IF EXISTS `{$new}`" );
			return $out;
		}
		
		$out['rows'] = (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$new}`" );

		
		
		$wpdb->query( "DROP TABLE IF EXISTS `{$old}`" );
		
		$wpdb->query( "RENAME TABLE `{$vitals}` TO `{$old}`, `{$new}` TO `{$vitals}`" );
		
		$wpdb->query( "DROP TABLE IF EXISTS `{$old}`" );

		update_option( 'aieo_content_vitals_built_at', time(), false );
		return $out;
	}

	
	
	

	
	public static function demand( $object_type, $object_id ) {
		global $wpdb;
		$t = $wpdb->prefix . self::VITALS_TABLE;
		static $has = null;
		if ( null === $has ) {
			$has = self::table_exists( $t );
		}
		if ( ! $has ) {
			return 0;
		}
		
		return (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT content_demand FROM `{$t}` WHERE post_type = %s AND content_id = %d",
			(string) $object_type,
			(int) $object_id
		) );
	}
}