<?php

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

function aieo_seo_posts_table() {
	global $wpdb;
	return $wpdb->prefix . 'aieo_seo_posts';
}

function aieo_seo_head_cache_table() {
	global $wpdb;
	return $wpdb->prefix . 'aieo_seo_head_cache';
}

function aieo_seo_scores_table() {
	global $wpdb;
	return $wpdb->prefix . 'aieo_seo_scores';
}

function aieo_seo_redirects_table() {
	global $wpdb;
	return $wpdb->prefix . 'aieo_seo_redirects';
}

function aieo_seo_404_table() {
	global $wpdb;
	return $wpdb->prefix . 'aieo_seo_404_log';
}

function aieo_seo_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_seo_posts_table();
	$head_cache = aieo_seo_head_cache_table();
	$scores     = aieo_seo_scores_table();
	$redirects  = aieo_seo_redirects_table();
	$fourohfour = aieo_seo_404_table();

	
	$run( "CREATE TABLE IF NOT EXISTS `{$posts}` (
		`id` bigint unsigned NOT NULL AUTO_INCREMENT,
		`object_type` varchar(32) NOT NULL DEFAULT 'post',
		`object_id` bigint unsigned NOT NULL,
		`title` text,
		`description` text,
		`keyphrases` longtext,
		`focus_keyphrase` varchar(191) DEFAULT NULL,
		`focus_keyphrase_source` varchar(20) DEFAULT NULL,
		`canonical_url` text,
		`canonical_override` text,
		`primary_cat_id` bigint unsigned DEFAULT NULL,
		`primary_term` longtext,
		`robots_default` tinyint(1) NOT NULL DEFAULT 1,
		`robots_noindex` tinyint(1) NOT NULL DEFAULT 0,
		`robots_nofollow` tinyint(1) NOT NULL DEFAULT 0,
		`robots_noarchive` tinyint(1) NOT NULL DEFAULT 0,
		`robots_nosnippet` tinyint(1) NOT NULL DEFAULT 0,
		`robots_noimageindex` tinyint(1) NOT NULL DEFAULT 0,
		`robots_noodp` tinyint(1) NOT NULL DEFAULT 0,
		`robots_notranslate` tinyint(1) NOT NULL DEFAULT 0,
		`robots_max_snippet` int DEFAULT NULL,
		`robots_max_videopreview` int DEFAULT NULL,
		`robots_max_imagepreview` varchar(20) DEFAULT NULL,
		`og_title` text,
		`og_description` text,
		`og_object_type` varchar(64) DEFAULT NULL,
		`og_image_type` varchar(64) DEFAULT NULL,
		`og_image_custom_url` text,
		`og_image_url` text,
		`og_image_width` int DEFAULT NULL,
		`og_image_height` int DEFAULT NULL,
		`og_article_section` text,
		`og_article_tags` text,
		`twitter_use_og` tinyint(1) NOT NULL DEFAULT 0,
		`twitter_card` varchar(64) DEFAULT NULL,
		`twitter_image_type` varchar(64) DEFAULT NULL,
		`twitter_image_custom_url` text,
		`twitter_image_url` text,
		`twitter_title` text,
		`twitter_description` text,
		`schema_type` varchar(32) DEFAULT NULL,
		`schema_type_options` longtext,
		`pillar_content` tinyint(1) NOT NULL DEFAULT 0,
		`og_image_id` bigint unsigned DEFAULT NULL,
		`image_alt` text,
		`demand_score` double DEFAULT NULL,
		`sitemap_priority` float DEFAULT NULL,
		`sitemap_frequency` varchar(20) DEFAULT NULL,
		`indexable` tinyint(1) NOT NULL DEFAULT 1,
		`sitemap` tinyint(1) NOT NULL DEFAULT 1,
		`priority_score` smallint unsigned NOT NULL DEFAULT 0,
		`sitemap_cut` tinyint(1) NOT NULL DEFAULT 0,
		`ai_state` varchar(20) NOT NULL DEFAULT 'none',
		`ai_locked` tinyint(1) NOT NULL DEFAULT 0,
		`created` datetime DEFAULT NULL,
		`updated` datetime DEFAULT NULL,
		PRIMARY KEY (`id`),
		UNIQUE KEY `object` (`object_type`,`object_id`),
		KEY `pillar_content` (`pillar_content`),
		KEY `demand_score` (`demand_score`),
		KEY `focus_keyphrase` (`focus_keyphrase`),
		KEY `ai_state` (`ai_state`),
		KEY `canonical_url` (`canonical_url`(191)),
		KEY `sitemap_order` (`priority_score`,`id`)
	) {$charset_collate};" );

	
	$run( "CREATE TABLE IF NOT EXISTS `{$head_cache}` (
		`id` bigint unsigned NOT NULL AUTO_INCREMENT,
		`object_type` varchar(32) NOT NULL DEFAULT 'post',
		`object_id` bigint unsigned NOT NULL,
		`lang` varchar(10) NOT NULL DEFAULT '',
		`title` text,
		`canonical` text,
		`robots` varchar(191) DEFAULT NULL,
		`head_html` longtext,
		`content_hash` varchar(40) DEFAULT NULL,
		`updated` datetime DEFAULT NULL,
		PRIMARY KEY (`id`),
		UNIQUE KEY `object_lang` (`object_type`,`object_id`,`lang`),
		KEY `updated` (`updated`)
	) {$charset_collate};" );

	
	$run( "CREATE TABLE IF NOT EXISTS `{$scores}` (
		`id` bigint unsigned NOT NULL AUTO_INCREMENT,
		`object_type` varchar(32) NOT NULL DEFAULT 'post',
		`object_id` bigint unsigned NOT NULL,
		`score` tinyint unsigned NOT NULL DEFAULT 0,
		`breakdown` longtext,
		`pillar_score` double DEFAULT NULL,
		`is_pillar` tinyint(1) NOT NULL DEFAULT 0,
		`fix_count` smallint unsigned NOT NULL DEFAULT 0,
		`last_calculated` datetime DEFAULT NULL,
		PRIMARY KEY (`id`),
		UNIQUE KEY `object` (`object_type`,`object_id`),
		KEY `score` (`score`),
		KEY `is_pillar` (`is_pillar`)
	) {$charset_collate};" );

	
	$run( "CREATE TABLE IF NOT EXISTS `{$redirects}` (
		`id` bigint unsigned NOT NULL AUTO_INCREMENT,
		`source_path` varchar(190) NOT NULL,
		`target` text NOT NULL,
		`status_code` smallint unsigned NOT NULL DEFAULT 301,
		`is_regex` tinyint(1) NOT NULL DEFAULT 0,
		`enabled` tinyint(1) NOT NULL DEFAULT 1,
		`hits` bigint unsigned NOT NULL DEFAULT 0,
		`source` varchar(20) NOT NULL DEFAULT 'manual',
		`last_hit` datetime DEFAULT NULL,
		`created_at` datetime DEFAULT NULL,
		PRIMARY KEY (`id`),
		UNIQUE KEY `source_path` (`source_path`),
		KEY `enabled` (`enabled`),
		KEY `is_regex` (`is_regex`)
	) {$charset_collate};" );

	
	$run( "CREATE TABLE IF NOT EXISTS `{$fourohfour}` (
		`id` bigint unsigned NOT NULL AUTO_INCREMENT,
		`path` varchar(190) NOT NULL,
		`hits` bigint unsigned NOT NULL DEFAULT 1,
		`referrer` text,
		`first_seen` datetime DEFAULT NULL,
		`last_seen` datetime DEFAULT NULL,
		`redirected` tinyint(1) NOT NULL DEFAULT 0,
		PRIMARY KEY (`id`),
		UNIQUE KEY `path` (`path`),
		KEY `hits` (`hits`),
		KEY `last_seen` (`last_seen`)
	) {$charset_collate};" );

	
	
	if ( function_exists( 'aieo_dmm_ensure_column' ) ) {
		
		
		aieo_dmm_ensure_column( $posts, 'canonical_override', 'text' );
		aieo_dmm_ensure_column( $posts, 'primary_cat_id', 'bigint unsigned DEFAULT NULL' );
		
		
		
		aieo_dmm_ensure_column( $posts, 'indexable', 'tinyint(1) NOT NULL DEFAULT 1' );
		
		aieo_dmm_ensure_column( $posts, 'sitemap', 'tinyint(1) NOT NULL DEFAULT 1' );
		
		
		aieo_dmm_ensure_column( $posts, 'priority_score', 'smallint unsigned NOT NULL DEFAULT 0' );
		aieo_dmm_ensure_column( $posts, 'sitemap_cut', 'tinyint(1) NOT NULL DEFAULT 0' );
		
		
		aieo_dmm_ensure_column( $posts, 'search_volume_total', 'int unsigned NOT NULL DEFAULT 0' );
		aieo_dmm_ensure_column( $posts, 'best_position', 'smallint unsigned DEFAULT NULL' );
		aieo_dmm_ensure_column( $posts, 'striking_count', 'int unsigned NOT NULL DEFAULT 0' );
		aieo_dmm_ensure_column( $posts, 'search_demand_score', 'tinyint unsigned NOT NULL DEFAULT 0' );
		
		
		aieo_dmm_ensure_column( $posts, 'suggested_title', 'text' );
		aieo_dmm_ensure_column( $posts, 'suggested_description', 'text' );
		aieo_dmm_ensure_column( $posts, 'suggested_keyphrase', 'varchar(191) DEFAULT NULL' );
		aieo_dmm_ensure_column( $posts, 'suggested_at', 'datetime DEFAULT NULL' );
		
		
		
		aieo_dmm_ensure_column( $posts, 'suggested_body', 'longtext' );
		aieo_dmm_ensure_column( $posts, 'suggested_body_at', 'datetime DEFAULT NULL' );
	}

	
	
	
	
	
	$has_idx = $wpdb->get_var( "SHOW INDEX FROM `{$posts}` WHERE Key_name = 'canonical_url'" );
	if ( null === $has_idx ) {
		
		$wpdb->query( "ALTER TABLE `{$posts}` ADD KEY `canonical_url` (`canonical_url`(191))" );
	}
	
	
	$has_order = $wpdb->get_var( "SHOW INDEX FROM `{$posts}` WHERE Key_name = 'sitemap_order'" );
	
	if ( null === $has_order && $wpdb->get_var( "SHOW COLUMNS FROM `{$posts}` LIKE 'priority_score'" ) ) {
		
		$wpdb->query( "ALTER TABLE `{$posts}` ADD KEY `sitemap_order` (`priority_score`,`id`)" );
	}
}

if ( ! defined( 'AIEO_SEO_SCHEMA_VERSION' ) ) {
	define( 'AIEO_SEO_SCHEMA_VERSION', '2026-11-06-sitemap-ranking' );
}

function aieo_seo_maybe_install() {
	if ( get_option( 'aieo_seo_schema_version' ) === AIEO_SEO_SCHEMA_VERSION ) {
		return;
	}
	if ( ! AIEO_Schema_Lock::acquire( 'aieo_seo_schema_version' ) ) {
		return;
	}
	aieo_seo_install_schema();
	update_option( 'aieo_seo_schema_version', AIEO_SEO_SCHEMA_VERSION, false );
}