<?php

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

if ( ! class_exists( 'AIEO_DataForSEO' ) ) {
	require_once AIEO_DIR . 'includes/seo/class-aieo-dataforseo.php';
}

class AIEO_DFS_Rankings {

	const TABLE      = 'aieo_dfs_rankings';
	const LAST_OPT   = 'aieo_dfs_rankings_pulled_at';
	const META_OPT   = 'aieo_dfs_rankings_last';   
	const SPEND_OPT  = 'aieo_dfs_spend';           
	const LOCALE_OPT = 'aieo_dataforseo_locale';   
	const INTERVAL   = 561600;                     
	const PAGE_SIZE  = 1000;
	const MAX_PAGES  = 20;                         

	
	
	

	
	public static function locale() {
		$raw = get_option( self::LOCALE_OPT, array() );
		$loc = is_array( $raw ) ? $raw : array();
		return array(
			'location_code' => isset( $loc['location_code'] ) && (int) $loc['location_code'] > 0 ? (int) $loc['location_code'] : 2840, 
			'language_code' => isset( $loc['language_code'] ) && '' !== (string) $loc['language_code'] ? (string) $loc['language_code'] : 'en',
		);
	}

	public static function save_locale( $location_code, $language_code ) {
		update_option( self::LOCALE_OPT, array(
			'location_code' => max( 1, (int) $location_code ),
			'language_code' => sanitize_key( substr( (string) $language_code, 0, 8 ) ),
		), false );
	}

	
	public static function budget() {
		$b = class_exists( 'AIEO_DMM_Settings' ) ? AIEO_DMM_Settings::get( 'seo.dfs_budget_usd', 5.0 ) : 5.0;
		return max( 0.0, (float) $b );
	}

	
	public static function spend_this_month() {
		$s = get_option( self::SPEND_OPT, array() );
		if ( ! is_array( $s ) || ! isset( $s['month'] ) || gmdate( 'Y-m' ) !== $s['month'] ) {
			return 0.0;
		}
		return (float) $s['usd'];
	}

	public static function record_spend( $usd ) {
		$usd = (float) $usd;
		if ( $usd <= 0 ) {
			return;
		}
		update_option( self::SPEND_OPT, array(
			'month' => gmdate( 'Y-m' ),
			'usd'   => self::spend_this_month() + $usd,
		), false );
	}

	
	
	

	
	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;
	}

	private static function ddl( $table ) {
		$charset = self::charset();
		return "CREATE TABLE IF NOT EXISTS `{$table}` (
			`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			`keyword` VARCHAR(190) NOT NULL,
			`url_path` VARCHAR(300) DEFAULT NULL,
			`object_type` VARCHAR(32) DEFAULT NULL,
			`object_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
			`position` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			`volume` INT UNSIGNED NOT NULL DEFAULT 0,
			`etv` DECIMAL(12,2) NOT NULL DEFAULT 0,
			`intent` VARCHAR(20) DEFAULT NULL,
			`difficulty` TINYINT UNSIGNED DEFAULT NULL,
			`pulled_at` DATETIME NOT NULL,
			PRIMARY KEY (`id`),
			KEY `obj` (`object_type`,`object_id`),
			KEY `keyword` (`keyword`(64)),
			KEY `striking` (`position`,`volume`)
		) {$charset}";
	}

	public static function table_exists() {
		global $wpdb;
		$t = $wpdb->prefix . self::TABLE;
		return (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $t ) ) === $t;
	}

	
	
	

	public static function maybe_pull() {
		if ( ! AIEO_DataForSEO::is_connected() ) {
			return array( 'skipped' => 'not_connected' );
		}
		$last = (int) get_option( self::LAST_OPT, 0 );
		if ( $last && ( time() - $last ) < self::INTERVAL ) {
			return array( 'skipped' => 'throttled' );
		}
		return self::pull();
	}

	
	public static function pull() {
		global $wpdb;
		if ( ! AIEO_DataForSEO::is_connected() ) {
			return array( 'skipped' => 'not_connected' );
		}
		if ( self::spend_this_month() >= self::budget() ) {
			return array( 'skipped' => 'budget_exhausted' );
		}

		$host = strtolower( (string) wp_parse_url( home_url(), PHP_URL_HOST ) );
		$host = preg_replace( '/^www\./', '', $host );
		if ( '' === $host ) {
			return array( 'error' => 'no host' );
		}
		$locale = self::locale();

		$items = array();
		$cost  = 0.0;
		for ( $page = 0; $page < self::MAX_PAGES; $page++ ) {
			$res = AIEO_DataForSEO::post( '/v3/dataforseo_labs/google/ranked_keywords/live', array(
				'target'        => $host,
				'location_code' => $locale['location_code'],
				'language_code' => $locale['language_code'],
				'limit'         => self::PAGE_SIZE,
				'offset'        => $page * self::PAGE_SIZE,
			) );
			if ( is_wp_error( $res ) ) {
				self::record_spend( $cost );
				return array( 'error' => $res->get_error_message(), 'cost' => $cost );
			}
			$task  = isset( $res['tasks'][0] ) ? $res['tasks'][0] : array();
			$cost += isset( $task['cost'] ) ? (float) $task['cost'] : 0.0;
			$r     = isset( $task['result'][0] ) ? $task['result'][0] : array();
			$batch = isset( $r['items'] ) && is_array( $r['items'] ) ? $r['items'] : array();
			$items = array_merge( $items, $batch );
			$total = isset( $r['total_count'] ) ? (int) $r['total_count'] : 0;
			if ( count( $batch ) < self::PAGE_SIZE || count( $items ) >= $total ) {
				break;
			}
			if ( self::spend_this_month() + $cost >= self::budget() ) {
				break; 
			}
		}
		self::record_spend( $cost );

		
		$table = $wpdb->prefix . self::TABLE;
		$new   = $table . '_new';
		$old   = $table . '_old';
		
		$wpdb->query( "DROP TABLE IF EXISTS `{$new}`" );
		
		$wpdb->query( str_replace( "`{$table}`", "`{$new}`", self::ddl( $table ) ) );

		$now  = current_time( 'mysql', true );
		$rows = 0;
		foreach ( $items as $it ) {
			$kd  = isset( $it['keyword_data'] ) ? $it['keyword_data'] : array();
			$se  = isset( $it['ranked_serp_element']['serp_item'] ) ? $it['ranked_serp_element']['serp_item'] : array();
			$kw  = isset( $kd['keyword'] ) ? substr( (string) $kd['keyword'], 0, 190 ) : '';
			if ( '' === $kw ) {
				continue;
			}
			$path = isset( $se['relative_url'] ) ? substr( (string) $se['relative_url'], 0, 300 ) : '';
			$obj  = self::resolve_path( $path );
			$wpdb->insert( $new, array(
				'keyword'     => $kw,
				'url_path'    => $path,
				'object_type' => $obj ? $obj[0] : null,
				'object_id'   => $obj ? $obj[1] : 0,
				'position'    => isset( $se['rank_absolute'] ) ? min( 65535, max( 0, (int) $se['rank_absolute'] ) ) : 0,
				'volume'      => isset( $kd['keyword_info']['search_volume'] ) ? max( 0, (int) $kd['keyword_info']['search_volume'] ) : 0,
				'etv'         => isset( $se['etv'] ) ? (float) $se['etv'] : 0.0,
				'intent'      => isset( $kd['search_intent_info']['main_intent'] ) ? substr( (string) $kd['search_intent_info']['main_intent'], 0, 20 ) : null,
				'difficulty'  => isset( $kd['keyword_properties']['keyword_difficulty'] ) ? min( 100, max( 0, (int) $kd['keyword_properties']['keyword_difficulty'] ) ) : null,
				'pulled_at'   => $now,
			) );
			$rows++;
		}

		
		$wpdb->query( "DROP TABLE IF EXISTS `{$old}`" );
		if ( self::table_exists() ) {
			
			$wpdb->query( "RENAME TABLE `{$table}` TO `{$old}`, `{$new}` TO `{$table}`" );
			
			$wpdb->query( "DROP TABLE IF EXISTS `{$old}`" );
		} else {
			
			$wpdb->query( "RENAME TABLE `{$new}` TO `{$table}`" );
		}

		update_option( self::LAST_OPT, time(), false );
		update_option( self::META_OPT, array( 'rows' => $rows, 'cost' => round( $cost, 4 ), 'finished_at' => time() ), false );

		self::materialise_aggregates();

		return array( 'rows' => $rows, 'cost' => round( $cost, 4 ) );
	}

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

	
	
	

	
	public static function materialise_aggregates() {
		global $wpdb;
		$sp = $wpdb->prefix . 'aieo_seo_posts';
		$rk = $wpdb->prefix . self::TABLE;
		if ( ! self::table_exists() ) {
			return;
		}
		
		$col = $wpdb->get_var( $wpdb->prepare(
			'SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE table_schema = DATABASE() AND table_name = %s AND column_name = %s',
			$sp,
			'search_volume_total'
		) );
		if ( null === $col ) {
			return;
		}
		
		$wpdb->query( "UPDATE `{$sp}` SET search_volume_total = 0, best_position = NULL, striking_count = 0, search_demand_score = 0
			WHERE search_volume_total <> 0 OR best_position IS NOT NULL OR striking_count <> 0 OR search_demand_score <> 0" );
		
		$wpdb->query( "UPDATE `{$sp}` sp
			INNER JOIN (
				SELECT object_type, object_id,
				       SUM(volume) AS sv,
				       MIN(NULLIF(position,0)) AS bp,
				       SUM(position BETWEEN 8 AND 20) AS sc,
				       ROUND(100 * PERCENT_RANK() OVER (ORDER BY SUM(volume))) AS sds
				  FROM `{$rk}`
				 WHERE object_id > 0
				 GROUP BY object_type, object_id
			) r ON r.object_type = sp.object_type AND r.object_id = sp.object_id
			SET sp.search_volume_total = r.sv,
			    sp.best_position       = r.bp,
			    sp.striking_count      = r.sc,
			    sp.search_demand_score = r.sds" );
	}

	
	
	

	
	public static function striking_distance( $limit = 20 ) {
		global $wpdb;
		if ( ! self::table_exists() ) {
			return array();
		}
		$rk = $wpdb->prefix . self::TABLE;
		
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT keyword, url_path, object_type, object_id, position, volume, etv, intent
			   FROM `{$rk}`
			  WHERE position BETWEEN 8 AND 20
			  ORDER BY volume DESC, position ASC
			  LIMIT %d",
			(int) $limit
		), ARRAY_A );
		return is_array( $rows ) ? $rows : array();
	}

	
	public static function serp_context( $keyword ) {
		if ( ! AIEO_DataForSEO::is_connected() ) {
			return new WP_Error( 'aieo_dfs_unconfigured', 'DataForSEO not connected.' );
		}
		if ( self::spend_this_month() >= self::budget() ) {
			return new WP_Error( 'aieo_dfs_budget', 'DataForSEO monthly budget exhausted.' );
		}
		$locale = self::locale();
		$res    = AIEO_DataForSEO::post( '/v3/serp/google/organic/live/advanced', array(
			'keyword'       => (string) $keyword,
			'location_code' => $locale['location_code'],
			'language_code' => $locale['language_code'],
			'depth'         => 10,
		) );
		if ( is_wp_error( $res ) ) {
			return $res;
		}
		$task = isset( $res['tasks'][0] ) ? $res['tasks'][0] : array();
		self::record_spend( isset( $task['cost'] ) ? (float) $task['cost'] : 0.0 );
		$items = isset( $task['result'][0]['items'] ) && is_array( $task['result'][0]['items'] ) ? $task['result'][0]['items'] : array();

		$out = array( 'competitors' => array(), 'paa' => array() );
		foreach ( $items as $it ) {
			$t = isset( $it['type'] ) ? (string) $it['type'] : '';
			if ( 'organic' === $t && count( $out['competitors'] ) < 10 ) {
				$out['competitors'][] = array(
					'title'       => isset( $it['title'] ) ? (string) $it['title'] : '',
					'description' => isset( $it['description'] ) ? (string) $it['description'] : '',
					'url'         => isset( $it['url'] ) ? (string) $it['url'] : '',
				);
			} elseif ( 'people_also_ask' === $t && isset( $it['items'] ) && is_array( $it['items'] ) ) {
				foreach ( $it['items'] as $q ) {
					if ( isset( $q['title'] ) && count( $out['paa'] ) < 6 ) {
						$out['paa'][] = (string) $q['title'];
					}
				}
			}
		}
		return $out;
	}

	
	public static function keywords_for( $object_type, $object_id, $limit = 8 ) {
		global $wpdb;
		if ( ! self::table_exists() ) {
			return array();
		}
		$rk = $wpdb->prefix . self::TABLE;
		
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT keyword, position, volume FROM `{$rk}`
			  WHERE object_type = %s AND object_id = %d
			  ORDER BY volume DESC, position ASC LIMIT %d",
			(string) $object_type,
			(int) $object_id,
			(int) $limit
		), ARRAY_A );
		return is_array( $rows ) ? $rows : array();
	}
}
