<?php

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

/** Persistent operator decisions and delayed evidence snapshots. */
class Content_Miner_Decision_Ledger {
	const SCHEMA_OPTION = 'content_miner_decision_schema';
	const SCHEMA_VERSION = 1;
	const OUTCOME_HOOK = 'content_miner_capture_decision_outcomes';
	const OUTCOME_AGE_DAYS = 90;

	/** Register the weekly outcome worker. */
	public function __construct() {
		if ( $this->outcomes_enabled() ) {
			add_action( self::OUTCOME_HOOK, array( $this, 'capture_outcomes' ) );
		}
	}

	/** Create or upgrade the decision ledger. */
	public static function install() {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		$table = self::table_name();
		$charset_collate = $wpdb->get_charset_collate();
		$sql = "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			post_id bigint(20) unsigned NOT NULL,
			pair_post_id bigint(20) unsigned NULL,
			pair_key bigint(20) unsigned NOT NULL DEFAULT 0,
			decision varchar(20) NOT NULL,
			note text NOT NULL,
			user_id bigint(20) unsigned NOT NULL,
			decided_at datetime NOT NULL,
			metrics_before longtext NOT NULL,
			metrics_after longtext NULL,
			outcome_at datetime NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY content_pair (post_id,pair_key),
			KEY outcome_due (outcome_at,decided_at)
		) {$charset_collate};";
		dbDelta( $sql );
		update_option( self::SCHEMA_OPTION, self::SCHEMA_VERSION, false );
		if ( self::outcomes_enabled_static() && ! wp_next_scheduled( self::OUTCOME_HOOK ) ) {
			wp_schedule_event( time() + WEEK_IN_SECONDS, 'weekly', self::OUTCOME_HOOK );
		}
	}

	/** Ensure upgrades receive the table without reactivation. */
	public static function maybe_install() {
		if ( self::SCHEMA_VERSION !== (int) get_option( self::SCHEMA_OPTION, 0 ) ) {
			self::install();
		}
	}

	/** @return string */
	public static function table_name() {
		global $wpdb;
		return $wpdb->prefix . 'content_miner_decisions';
	}

	/** @return string[] */
	public static function allowed_decisions() {
		return array( 'keep', 'refresh', 'consolidate', 'differentiate', 'localize', 'dismiss' );
	}

	/** Record or update one canonical page/pair decision. @return int|WP_Error */
	public function record( $post_id, $pair_post_id, $decision, $note, $user_id ) {
		global $wpdb;
		$post_id = absint( $post_id );
		$pair_post_id = absint( $pair_post_id );
		if ( $pair_post_id && $pair_post_id < $post_id ) {
			$swap = $post_id;
			$post_id = $pair_post_id;
			$pair_post_id = $swap;
		}
		$decision = sanitize_key( $decision );
		if ( ! $post_id || ! in_array( $decision, self::allowed_decisions(), true ) ) {
			return new WP_Error( 'content_miner_invalid_decision', __( 'Choose a valid content decision.', 'content-miner' ) );
		}
		$pair_key = $pair_post_id ? $pair_post_id : 0;
		$existing_id = (int) $wpdb->get_var(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The table name is generated by this class; values use placeholders.
			$wpdb->prepare( 'SELECT id FROM ' . self::table_name() . ' WHERE post_id = %d AND pair_key = %d', $post_id, $pair_key )
		);
		$data = array(
			'post_id'        => $post_id,
			'pair_post_id'   => $pair_post_id ? $pair_post_id : null,
			'pair_key'       => $pair_key,
			'decision'       => $decision,
			'note'           => sanitize_textarea_field( $note ),
			'user_id'        => absint( $user_id ),
			'decided_at'     => current_time( 'mysql', true ),
			'metrics_before' => wp_json_encode( $this->outcomes_enabled() ? $this->snapshot_metrics( $post_id, $pair_post_id ) : array() ),
			'metrics_after'  => null,
			'outcome_at'     => null,
		);
		if ( $existing_id ) {
			$result = $wpdb->update( self::table_name(), $data, array( 'id' => $existing_id ) );
			return false === $result ? new WP_Error( 'content_miner_decision_save_failed', __( 'The decision could not be saved.', 'content-miner' ) ) : $existing_id;
		}
		$result = $wpdb->insert( self::table_name(), $data );
		return false === $result ? new WP_Error( 'content_miner_decision_save_failed', __( 'The decision could not be saved.', 'content-miner' ) ) : (int) $wpdb->insert_id;
	}

	/** @param int[] $post_ids Post IDs. @return array<int,array<string,mixed>> */
	public function get_page_decisions( array $post_ids ) {
		global $wpdb;
		$post_ids = array_values( array_unique( array_filter( array_map( 'absint', $post_ids ) ) ) );
		if ( empty( $post_ids ) ) {
			return array();
		}
		$placeholders = implode( ', ', array_fill( 0, count( $post_ids ), '%d' ) );
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM ' . self::table_name() . " WHERE pair_key = 0 AND post_id IN ({$placeholders})", ...$post_ids ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$decisions = array();
		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			$decisions[ (int) $row['post_id'] ] = $row;
		}
		return $decisions;
	}

	/** @return array<string,array<string,mixed>> */
	public function get_pair_decisions( array $pairs ) {
		global $wpdb;
		$ids = array();
		foreach ( $pairs as $pair ) {
			$ids[] = (int) $pair['left_id'];
			$ids[] = (int) $pair['right_id'];
		}
		$ids = array_values( array_unique( array_filter( $ids ) ) );
		if ( empty( $ids ) ) {
			return array();
		}
		$placeholders = implode( ', ', array_fill( 0, count( $ids ), '%d' ) );
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM ' . self::table_name() . " WHERE pair_key > 0 AND post_id IN ({$placeholders})", ...$ids ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$decisions = array();
		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			$decisions[ (int) $row['post_id'] . ':' . (int) $row['pair_key'] ] = $row;
		}
		return $decisions;
	}

	/** Return one paginated ledger page. @return array<string,mixed> */
	public function get_page( $page = 1, $per_page = 25 ) {
		global $wpdb;
		$page = max( 1, absint( $page ) );
		$per_page = min( 100, max( 1, absint( $per_page ) ) );
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The generated table name is the complete query's only dynamic value.
		$total = (int) $wpdb->get_var( 'SELECT COUNT(*) FROM ' . self::table_name() );
		$rows = $wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The table name is generated by this class; values use placeholders.
			$wpdb->prepare( 'SELECT * FROM ' . self::table_name() . ' ORDER BY decided_at DESC, id DESC LIMIT %d OFFSET %d', $per_page, ( $page - 1 ) * $per_page ),
			ARRAY_A
		);
		return array( 'items' => is_array( $rows ) ? $rows : array(), 'total' => $total, 'page' => $page, 'per_page' => $per_page );
	}

	/** Capture current evidence once for decisions at least 90 days old. @return int */
	public function capture_outcomes() {
		if ( ! $this->outcomes_enabled() ) {
			return 0;
		}
		global $wpdb;
		$cutoff = gmdate( 'Y-m-d H:i:s', time() - self::OUTCOME_AGE_DAYS * DAY_IN_SECONDS );
		$rows = $wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The table name is generated by this class; the date uses a placeholder.
			$wpdb->prepare( 'SELECT id, post_id, pair_post_id FROM ' . self::table_name() . ' WHERE outcome_at IS NULL AND decided_at <= %s ORDER BY id ASC LIMIT 100', $cutoff ),
			ARRAY_A
		);
		$captured = 0;
		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			$metrics = $this->snapshot_metrics( (int) $row['post_id'], (int) $row['pair_post_id'] );
			if ( ! $this->has_metrics( $metrics ) ) {
				continue;
			}
			$result = $wpdb->update(
				self::table_name(),
				array( 'metrics_after' => wp_json_encode( $metrics ), 'outcome_at' => current_time( 'mysql', true ) ),
				array( 'id' => (int) $row['id'], 'outcome_at' => null )
			);
			if ( false !== $result && $result > 0 ) {
				++$captured;
			}
		}
		return $captured;
	}

	/** @return array<int,array<string,mixed>> */
	public function get_completed_outcomes( $limit = 25 ) {
		global $wpdb;
		$rows = $wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The table name is generated by this class; the limit uses a placeholder.
			$wpdb->prepare( 'SELECT * FROM ' . self::table_name() . ' WHERE outcome_at IS NOT NULL ORDER BY outcome_at DESC LIMIT %d', max( 1, absint( $limit ) ) ),
			ARRAY_A
		);
		return is_array( $rows ) ? $rows : array();
	}

	/** @return int */
	public function count_since( $date, $outcomes = false ) {
		global $wpdb;
		$column = $outcomes ? 'outcome_at' : 'decided_at';
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The table and column are selected internally; the date uses a placeholder.
		return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM ' . self::table_name() . " WHERE {$column} >= %s", $date ) );
	}

	/** Snapshot GSC and GA4 evidence for one page or pair. @return array<string,mixed> */
	private function snapshot_metrics( $post_id, $pair_post_id = 0 ) {
		$pages = array();
		foreach ( array_filter( array( absint( $post_id ), absint( $pair_post_id ) ) ) as $id ) {
			$search = get_post_meta( $id, '_content_miner_search_console', true );
			$analytics = get_post_meta( $id, '_content_miner_google_analytics', true );
			$pages[ $id ] = array(
				'search_console' => is_array( $search ) ? $search : array(),
				'analytics'      => is_array( $analytics ) ? $analytics : array(),
			);
		}
		return array( 'pages' => $pages, 'captured_at' => gmdate( 'c' ) );
	}

	/** @param array<string,mixed> $metrics Metrics. */
	private function has_metrics( array $metrics ) {
		foreach ( isset( $metrics['pages'] ) ? $metrics['pages'] : array() as $page ) {
			if ( ! empty( $page['search_console'] ) || ! empty( $page['analytics'] ) ) {
				return true;
			}
		}
		return false;
	}

	/** Return whether delayed outcome metrics are licensed. */
	private function outcomes_enabled() {
		return self::outcomes_enabled_static();
	}

	/** Static variant used during schema installation. */
	private static function outcomes_enabled_static() {
		return ! function_exists( 'content_miner_has_feature' ) || content_miner_has_feature( Content_Miner_License::FEATURE_OUTCOMES );
	}
}
