<?php
/**
 * Custom tables (PLAN.md §5.3, §12.1). Created on activation and upgraded
 * in place when AgentAdapt_Schema::VERSION moves.
 *
 * Nothing secret is stored in the clear: tokens and replay identifiers are
 * SHA-256 hashes, and an agent's per-company User ID (`sub`) is kept only as
 * a hash inside `subject_key`.
 *
 * @package AgentAdapt
 */

defined( 'ABSPATH' ) || exit;

/**
 * Schema install / upgrade.
 */
final class AgentAdapt_Schema {

	public const VERSION = 4;
	private const OPTION = 'agentadapt_schema_version';

	/**
	 * Table name with the site prefix.
	 *
	 * @param string $short agents|subjects|sessions|tokens|grants|codes|replay|rate|events|feedback.
	 */
	public static function table( string $short ): string {
		global $wpdb;
		return $wpdb->prefix . 'agentadapt_' . $short;
	}

	/**
	 * Run install when the stored version is behind. One autoloaded option read
	 * per request; dbDelta only when something actually changed.
	 */
	public static function maybe_upgrade(): void {
		if ( (int) get_option( self::OPTION, 0 ) < self::VERSION ) {
			self::install();
		}
	}

	/**
	 * Create or update every table.
	 */
	public static function install(): void {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		$c = $wpdb->get_charset_collate();

		// dbDelta's parser is strict: one column per line, two spaces after
		// PRIMARY KEY, and KEY name (col) for indexes.
		$sql = array();

		$sql[] = 'CREATE TABLE ' . self::table( 'agents' ) . " (
  client_hash char(64) NOT NULL,
  client_id varchar(512) NOT NULL,
  name varchar(191) NOT NULL DEFAULT '',
  logo_uri varchar(512) NOT NULL DEFAULT '',
  metadata longtext NULL,
  jwks longtext NULL,
  status varchar(16) NOT NULL DEFAULT 'ok',
  fetched_at bigint(20) unsigned NOT NULL DEFAULT 0,
  jwks_fetched_at bigint(20) unsigned NOT NULL DEFAULT 0,
  expires_at bigint(20) unsigned NOT NULL DEFAULT 0,
  first_seen bigint(20) unsigned NOT NULL DEFAULT 0,
  last_seen bigint(20) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY  (client_hash),
  KEY status (status)
) $c;";

		$sql[] = 'CREATE TABLE ' . self::table( 'subjects' ) . " (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  subject_key char(64) NOT NULL,
  client_hash char(64) NOT NULL,
  created bigint(20) unsigned NOT NULL DEFAULT 0,
  last_seen bigint(20) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY  (id),
  UNIQUE KEY subject_key (subject_key),
  KEY client_hash (client_hash)
) $c;";

		$sql[] = 'CREATE TABLE ' . self::table( 'sessions' ) . " (
  session_id varchar(40) NOT NULL,
  client_hash char(64) NOT NULL,
  subject_id bigint(20) unsigned NOT NULL,
  user_id bigint(20) unsigned NOT NULL DEFAULT 0,
  bound_user_id bigint(20) unsigned NOT NULL DEFAULT 0,
  grant_id varchar(40) NOT NULL DEFAULT '',
  wc_session_key char(32) NOT NULL DEFAULT '',
  created bigint(20) unsigned NOT NULL DEFAULT 0,
  last_used bigint(20) unsigned NOT NULL DEFAULT 0,
  expires bigint(20) unsigned NOT NULL DEFAULT 0,
  ended tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY  (session_id),
  KEY subject_id (subject_id),
  KEY client_hash (client_hash)
) $c;";

		$sql[] = 'CREATE TABLE ' . self::table( 'tokens' ) . " (
  token_hash char(64) NOT NULL,
  session_id varchar(40) NOT NULL,
  kind varchar(16) NOT NULL DEFAULT 'session',
  scope varchar(255) NOT NULL DEFAULT '',
  user_id bigint(20) unsigned NOT NULL DEFAULT 0,
  jkt varchar(64) NULL,
  resource varchar(512) NULL,
  expires bigint(20) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY  (token_hash),
  KEY session_id (session_id),
  KEY expires (expires)
) $c;";

		// Account Tokens (spec 4.8): one row per sign-in approval. Backs the
		// customer's Connected agents list.
		$sql[] = 'CREATE TABLE ' . self::table( 'grants' ) . " (
  grant_id varchar(40) NOT NULL,
  token_hash char(64) NOT NULL,
  client_hash char(64) NOT NULL,
  subject_id bigint(20) unsigned NOT NULL DEFAULT 0,
  user_id bigint(20) unsigned NOT NULL,
  scope varchar(255) NOT NULL DEFAULT '',
  created bigint(20) unsigned NOT NULL DEFAULT 0,
  expires bigint(20) unsigned NOT NULL DEFAULT 0,
  last_used bigint(20) unsigned NOT NULL DEFAULT 0,
  revoked tinyint(1) NOT NULL DEFAULT 0,
  revoked_at bigint(20) unsigned NOT NULL DEFAULT 0,
  revoked_reason varchar(40) NOT NULL DEFAULT '',
  PRIMARY KEY  (grant_id),
  UNIQUE KEY token_hash (token_hash),
  KEY user_id (user_id),
  KEY client_hash (client_hash)
) $c;";

		// Authorization codes (Direct) and device codes (Device), hashed.
		$sql[] = 'CREATE TABLE ' . self::table( 'codes' ) . " (
  code_hash char(64) NOT NULL,
  kind varchar(16) NOT NULL,
  client_hash char(64) NOT NULL,
  user_id bigint(20) unsigned NOT NULL DEFAULT 0,
  scope varchar(255) NOT NULL DEFAULT '',
  granted_scope varchar(255) NOT NULL DEFAULT '',
  redirect_uri varchar(512) NOT NULL DEFAULT '',
  code_challenge varchar(128) NOT NULL DEFAULT '',
  resource varchar(512) NOT NULL DEFAULT '',
  user_code varchar(16) NULL,
  status varchar(16) NOT NULL DEFAULT 'pending',
  grant_id varchar(40) NOT NULL DEFAULT '',
  created bigint(20) unsigned NOT NULL DEFAULT 0,
  expires bigint(20) unsigned NOT NULL DEFAULT 0,
  last_poll bigint(20) unsigned NOT NULL DEFAULT 0,
  poll_interval int(10) unsigned NOT NULL DEFAULT 5,
  PRIMARY KEY  (code_hash),
  KEY user_code (user_code),
  KEY expires (expires)
) $c;";

		$sql[] = 'CREATE TABLE ' . self::table( 'replay' ) . " (
  jti_hash char(64) NOT NULL,
  expires bigint(20) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY  (jti_hash),
  KEY expires (expires)
) $c;";

		$sql[] = 'CREATE TABLE ' . self::table( 'rate' ) . " (
  bucket char(64) NOT NULL,
  window_start bigint(20) unsigned NOT NULL,
  hits int(10) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY  (bucket,window_start)
) $c;";

		$sql[] = 'CREATE TABLE ' . self::table( 'events' ) . " (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  created bigint(20) unsigned NOT NULL DEFAULT 0,
  client_hash char(64) NOT NULL DEFAULT '',
  subject_id bigint(20) unsigned NOT NULL DEFAULT 0,
  session_id varchar(40) NOT NULL DEFAULT '',
  user_id bigint(20) unsigned NOT NULL DEFAULT 0,
  channel varchar(16) NOT NULL DEFAULT '',
  event varchar(40) NOT NULL,
  object_id bigint(20) unsigned NOT NULL DEFAULT 0,
  value decimal(19,4) NULL,
  outcome varchar(40) NOT NULL DEFAULT 'ok',
  meta longtext NULL,
  PRIMARY KEY  (id),
  KEY created (created),
  KEY event (event),
  KEY session_id (session_id)
) $c;";

		// Feedback reports (spec §8). Written only by AgentAdapt_Feedback_Store.
		// subject_id is the store's own subject, never the agent's raw `sub`;
		// user_id is set only when the Session was signed in.
		$sql[] = 'CREATE TABLE ' . self::table( 'feedback' ) . " (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  created bigint(20) unsigned NOT NULL DEFAULT 0,
  client_hash char(64) NOT NULL DEFAULT '',
  subject_id bigint(20) unsigned NOT NULL DEFAULT 0,
  session_id varchar(40) NOT NULL DEFAULT '',
  user_id bigint(20) unsigned NOT NULL DEFAULT 0,
  kind varchar(64) NOT NULL DEFAULT '',
  task varchar(200) NOT NULL DEFAULT '',
  task_key char(64) NOT NULL DEFAULT '',
  description text NULL,
  PRIMARY KEY  (id),
  KEY created (created),
  KEY task_key (task_key),
  KEY user_id (user_id)
) $c;";

		dbDelta( $sql );
		update_option( self::OPTION, self::VERSION, true );
		// New My Account endpoints need a rewrite flush, done once on the next
		// init after they are registered (AgentAdapt_My_Account).
		update_option( 'agentadapt_flush_rewrites', 1, false );
	}

	/**
	 * Every table this plugin owns (uninstall + privacy tooling).
	 *
	 * @return string[]
	 */
	public static function tables(): array {
		return array_map( array( __CLASS__, 'table' ), array( 'agents', 'subjects', 'sessions', 'tokens', 'grants', 'codes', 'replay', 'rate', 'events', 'feedback' ) );
	}
}