<?php
/**
 * Truebe seeds: table schema and storage for the keyword-matching model.
 *
 * Storage only. Matching lives in Truebe_Matcher (reads via get_ready_seeds()).
 * List fields are JSON; filterable fields (intent, seed_type, risk_category) are flat.
 * Context fields hold the source paragraph plus the ones above and below, and a locator
 * (section, heading, page) so the review screen can show where the answer came from.
 */

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

if ( class_exists( 'Truebe_Seeds' ) ) {
	return;
}

class Truebe_Seeds {

	const SCHEMA_VERSION = '2.4.0';

	const STATUS_PENDING = 'pending';
	const STATUS_READY   = 'ready';
	const STATUS_HELD    = 'held';

	public static function table() {
		global $wpdb;
		return $wpdb->prefix . 'truebe_seeds';
	}

	public static function install() {
		global $wpdb;

		$table           = self::table();
		$charset_collate = $wpdb->get_charset_collate();

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$sql = "CREATE TABLE {$table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			document_id BIGINT UNSIGNED NULL,
			label VARCHAR(255) NOT NULL,
			entity VARCHAR(100) NULL,
			intent VARCHAR(40) NULL,
			seed_type VARCHAR(40) NULL,
			priority VARCHAR(20) NOT NULL DEFAULT 'normal',
			risk_category VARCHAR(40) NULL,
			thesis LONGTEXT NOT NULL,
			anchors LONGTEXT NULL,
			keywords LONGTEXT NULL,
			phrasings LONGTEXT NULL,
			citations LONGTEXT NULL,
			followups LONGTEXT NULL,
			handoff LONGTEXT NULL,
			context_before LONGTEXT NULL,
			context_main LONGTEXT NULL,
			context_after LONGTEXT NULL,
			source_doc VARCHAR(255) NULL,
			source_section VARCHAR(255) NULL,
			source_heading VARCHAR(255) NULL,
			source_page INT UNSIGNED NOT NULL DEFAULT 0,
			status VARCHAR(20) NOT NULL DEFAULT 'pending',
			reviewer_id BIGINT UNSIGNED NULL,
			last_validated DATETIME NULL,
			confidence_tier TINYINT UNSIGNED NULL,
			published TINYINT UNSIGNED NOT NULL DEFAULT 1,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			KEY status (status),
			KEY document_id (document_id),
			KEY risk_category (risk_category),
			KEY intent (intent),
			KEY seed_type (seed_type)
		) {$charset_collate};";

		dbDelta( $sql );

		update_option( 'truebe_seeds_schema_version', self::SCHEMA_VERSION );
	}

	private static function encode_list( $value ) {
		if ( empty( $value ) || ! is_array( $value ) ) {
			return null;
		}
		$encoded = wp_json_encode( array_values( $value ) );
		return ( false === $encoded ) ? null : $encoded;
	}

public static function decode_list( $value ) {
		if ( empty( $value ) ) {
			return array();
		}
		$decoded = json_decode( $value, true );
		return is_array( $decoded ) ? $decoded : array();
	}

	private static function encode_handoff( $value ) {
		if ( empty( $value ) || ! is_array( $value ) ) {
			return null;
		}
		$label = isset( $value['label'] ) ? sanitize_text_field( $value['label'] ) : '';
		$url   = isset( $value['url'] ) ? esc_url_raw( $value['url'] ) : '';
		if ( '' === $label || '' === $url ) {
			return null;
		}
		$encoded = wp_json_encode( array( 'label' => $label, 'url' => $url ) );
		return ( false === $encoded ) ? null : $encoded;
	}

public static function decode_handoff( $value ) {
		if ( empty( $value ) ) {
			return null;
		}
		$decoded = json_decode( $value, true );
		if ( ! is_array( $decoded ) ) {
			return null;
		}
		$label = isset( $decoded['label'] ) ? (string) $decoded['label'] : '';
		$url   = isset( $decoded['url'] ) ? (string) $decoded['url'] : '';
		if ( '' === $label && '' === $url ) {
			return null;
		}
		return array( 'label' => $label, 'url' => $url );
	}

	public static function insert( $data ) {
		global $wpdb;

		$status = isset( $data['status'] ) ? sanitize_key( $data['status'] ) : self::STATUS_PENDING;

		$row = array(
			'document_id'    => isset( $data['document_id'] ) ? absint( $data['document_id'] ) : null,
			'label'          => isset( $data['label'] ) ? sanitize_text_field( $data['label'] ) : '',
			'entity'         => isset( $data['entity'] ) ? sanitize_key( $data['entity'] ) : null,
			'intent'         => isset( $data['intent'] ) ? sanitize_key( $data['intent'] ) : null,
			'seed_type'      => isset( $data['seed_type'] ) ? sanitize_key( $data['seed_type'] ) : null,
			'priority'       => isset( $data['priority'] ) ? sanitize_key( $data['priority'] ) : 'normal',
			'risk_category'  => isset( $data['risk_category'] ) ? sanitize_key( $data['risk_category'] ) : null,
			'thesis'         => isset( $data['thesis'] ) ? wp_kses_post( $data['thesis'] ) : '',
			'anchors'        => self::encode_list( isset( $data['anchors'] ) ? $data['anchors'] : null ),
			'keywords'       => self::encode_list( isset( $data['keywords'] ) ? $data['keywords'] : null ),
			'phrasings'      => self::encode_list( isset( $data['phrasings'] ) ? $data['phrasings'] : null ),
			'citations'      => self::encode_list( isset( $data['citations'] ) ? $data['citations'] : null ),
			'followups'      => self::encode_list( isset( $data['followups'] ) ? $data['followups'] : null ),
			'context_before' => isset( $data['context_before'] ) ? wp_kses_post( $data['context_before'] ) : null,
			'context_main'   => isset( $data['context_main'] ) ? wp_kses_post( $data['context_main'] ) : null,
			'context_after'  => isset( $data['context_after'] ) ? wp_kses_post( $data['context_after'] ) : null,
			'source_doc'     => isset( $data['source_doc'] ) ? sanitize_text_field( $data['source_doc'] ) : null,
			'source_section' => isset( $data['source_section'] ) ? sanitize_text_field( $data['source_section'] ) : null,
			'source_heading' => isset( $data['source_heading'] ) ? sanitize_text_field( $data['source_heading'] ) : null,
			'source_page'    => isset( $data['source_page'] ) ? absint( $data['source_page'] ) : 0,
			'status'         => $status,
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table; $wpdb->insert() is the supported API and there is no object cache for this row.
		$ok = $wpdb->insert( self::table(), $row );
		return $ok ? (int) $wpdb->insert_id : false;
	}

	public static function set_status( $seed_id, $status, $reviewer_id = 0 ) {
		global $wpdb;
		$update_data = array(
			'status'     => sanitize_key( $status ),
			'updated_at' => current_time( 'mysql' ),
		);
		if ( $reviewer_id > 0 ) {
			$update_data['reviewer_id'] = absint( $reviewer_id );
		}
		$formats = array( '%s', '%s' );
		if ( $reviewer_id > 0 ) {
			$formats[] = '%d';
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table; $wpdb->update() is the supported API and there is no object cache for this row.
		$updated = $wpdb->update(
			self::table(),
			$update_data,
			array( 'id' => absint( $seed_id ) ),
			$formats,
			array( '%d' )
		);
		return ( false !== $updated );
	}

/** Distinct documents among published, ready seeds, for the internal chat scope dropdown. */
	public static function list_documents() {
		global $wpdb;
		$table = self::table();
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- {$table} is prefix + fixed string; custom plugin table.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT source_doc, MAX(created_at) AS uploaded_at
				 FROM {$table}
				 WHERE status = %s AND source_doc <> ''
				 GROUP BY source_doc
				 ORDER BY uploaded_at DESC",
				self::STATUS_READY
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return is_array( $rows ) ? $rows : array();
	}

public static function get_ready_seeds() {
	global $wpdb;

		$table = self::table();
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- {$table} is built from $wpdb->prefix plus a fixed string (no user input); SQL identifiers cannot be passed as prepare() parameters. Custom plugin table, no core API or object cache.
		$rows  = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, label, entity, intent, seed_type, priority, risk_category,
				        thesis, anchors, keywords, phrasings, citations, followups, handoff,
				        context_before, context_main, context_after,
				        source_doc, source_section, source_heading, source_page,
				        status, reviewer_id, confidence_tier, published, created_at, updated_at
				 FROM {$table} WHERE status = %s AND published = 1",
				self::STATUS_READY
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( empty( $rows ) ) {
			return array();
		}

foreach ( $rows as &$row ) {
			$row['anchors']   = self::decode_list( $row['anchors'] );
			$row['keywords']  = self::decode_list( $row['keywords'] );
			$row['phrasings'] = self::decode_list( $row['phrasings'] );
			$row['citations'] = self::decode_list( $row['citations'] );
			$row['followups'] = self::decode_list( $row['followups'] );
			$row['handoff']   = self::decode_handoff( $row['handoff'] );
		}
		unset( $row );

		return $rows;
	}

public static function get_by_status( $status, $limit = 500 ) {
		global $wpdb;

		$table = self::table();
		$limit = max( 1, (int) $limit );
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- {$table} is built from $wpdb->prefix plus a fixed string (no user input); SQL identifiers cannot be passed as prepare() parameters. Custom plugin table, no core API or object cache.
		$rows  = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, label, entity, intent, seed_type, priority, risk_category,
				        thesis, anchors, keywords, phrasings, citations, followups, handoff,
				        context_before, context_main, context_after,
				        source_doc, source_section, source_heading, source_page,
				        status, reviewer_id, confidence_tier, published, created_at, updated_at
				 FROM {$table}
				 WHERE status = %s
				 ORDER BY id ASC
				 LIMIT %d",
				$status,
				$limit
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( empty( $rows ) ) {
			return array();
		}

foreach ( $rows as &$row ) {
			$row['anchors']   = self::decode_list( $row['anchors'] );
			$row['keywords']  = self::decode_list( $row['keywords'] );
			$row['phrasings'] = self::decode_list( $row['phrasings'] );
			$row['citations'] = self::decode_list( $row['citations'] );
			$row['followups'] = self::decode_list( $row['followups'] );
			$row['handoff']   = self::decode_handoff( $row['handoff'] );
		}
		unset( $row );

		return $rows;
	}
	/**
	 * Every seed (pending and ready). Ordered by id so a seed keeps its slot
	 * when its status changes, so the review screen stays put after approve.
	 */
	public static function get_all( $limit = 500 ) {
		global $wpdb;

		$table = self::table();
		$limit = max( 1, (int) $limit );
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- {$table} is built from $wpdb->prefix plus a fixed string (no user input); SQL identifiers cannot be passed as prepare() parameters. Custom plugin table, no core API or object cache.
		$rows  = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, label, entity, intent, seed_type, priority, risk_category,
				        thesis, anchors, keywords, phrasings, citations, followups,
				        context_before, context_main, context_after,
				        source_doc, source_section, source_heading, source_page,
				        status, reviewer_id, confidence_tier, published, created_at, updated_at
				 FROM {$table}
				 ORDER BY id ASC
				 LIMIT %d",
				$limit
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( empty( $rows ) ) {
			return array();
		}

		foreach ( $rows as &$row ) {
			$row['anchors']   = self::decode_list( $row['anchors'] );
			$row['keywords']  = self::decode_list( $row['keywords'] );
			$row['phrasings'] = self::decode_list( $row['phrasings'] );
			$row['citations'] = self::decode_list( $row['citations'] );
			$row['followups'] = self::decode_list( $row['followups'] );
		}
		unset( $row );

		return $rows;
	}

	/** Add the published column to existing installs that predate schema 2.3.0. */
	public static function maybe_upgrade() {
		if ( get_option( 'truebe_seeds_schema_version' ) === self::SCHEMA_VERSION ) {
			return;
		}
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- {$table} is prefix + fixed string; checking a column's existence.
$has = $wpdb->get_results( "SHOW COLUMNS FROM {$table} LIKE 'published'" );
		if ( empty( $has ) ) {
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter -- one-time schema migration adding the published column on a custom plugin table; no user input.
			$wpdb->query( "ALTER TABLE {$table} ADD COLUMN published TINYINT UNSIGNED NOT NULL DEFAULT 1 AFTER confidence_tier" );
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- {$table} is prefix + fixed string; checking a column's existence.
		$has_handoff = $wpdb->get_results( "SHOW COLUMNS FROM {$table} LIKE 'handoff'" );
		if ( empty( $has_handoff ) ) {
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter -- one-time schema migration adding the handoff column on a custom plugin table; no user input.
			$wpdb->query( "ALTER TABLE {$table} ADD COLUMN handoff LONGTEXT NULL AFTER followups" );
		}
		update_option( 'truebe_seeds_schema_version', self::SCHEMA_VERSION );
	}

	/** Toggle chatbot visibility for a seed without touching its approval. */
	public static function set_published( $seed_id, $published ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table; $wpdb->update() is the supported API and there is no object cache for this row.
		return false !== $wpdb->update(
			self::table(),
			array( 'published' => $published ? 1 : 0 ),
			array( 'id' => absint( $seed_id ) ),
			array( '%d' ),
			array( '%d' )
		);
	}

	public static function count( $status = null ) {
		global $wpdb;
		$table = self::table();

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- {$table} is built from $wpdb->prefix plus a fixed string (no user input); SQL identifiers cannot be passed as prepare() parameters. Custom plugin table, no core API or object cache.
		if ( null === $status ) {
			return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
		}
		return (int) $wpdb->get_var(
			$wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE status = %s", $status )
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}
}