<?php
/**
 * Schema migrations, activation and deactivation.
 *
 * @package HandyMCP
 */

namespace HandyMCP\Database;

use HandyMCP\Settings;

defined( 'ABSPATH' ) || exit;

/**
 * Versioned migrations using dbDelta.
 */
final class Installer {

	/**
	 * Table name helper.
	 *
	 * @param string $name Short name.
	 */
	public static function table( string $name ): string {
		global $wpdb;
		return $wpdb->prefix . 'handymcp_' . $name;
	}

	/**
	 * Activation hook.
	 */
	public static function activate(): void {
		self::migrate();
		if ( false === get_option( Settings::OPTION ) ) {
			add_option( Settings::OPTION, Settings::defaults(), '', false );
		}
		if ( ! wp_next_scheduled( 'handymcp_daily_maintenance' ) ) {
			wp_schedule_event( time() + HOUR_IN_SECONDS, 'daily', 'handymcp_daily_maintenance' );
		}
		if ( ! get_option( 'handymcp_activated_at' ) ) {
			update_option( 'handymcp_do_redirect', 1, false );
		}
		update_option( 'handymcp_activated_at', time(), false );
		delete_transient( 'handymcp_discovery' );
		delete_option( 'handymcp_probe_version' ); // Probe /.well-known/ again on the next load.
	}

	/**
	 * Deactivation hook. Data is kept; uninstall.php removes it.
	 */
	public static function deactivate(): void {
		wp_clear_scheduled_hook( 'handymcp_daily_maintenance' );
		wp_clear_scheduled_hook( 'handymcp_diag_daily' );
		wp_clear_scheduled_hook( 'handymcp_discovery_probe' );
		delete_transient( 'handymcp_discovery' );
	}

	/**
	 * Run migrations when the stored version is behind.
	 */
	public static function maybe_migrate(): void {
		if ( get_option( 'handymcp_db_version' ) !== HANDYMCP_DB_VERSION ) {
			self::migrate();
		}
	}

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

		$sql = array();

		$sql[] = 'CREATE TABLE ' . self::table( 'tokens' ) . " (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			token_hash char(64) NOT NULL,
			token_prefix varchar(24) NOT NULL DEFAULT '',
			kind varchar(16) NOT NULL DEFAULT 'pat',
			name varchar(190) NOT NULL DEFAULT '',
			user_id bigint(20) unsigned NOT NULL DEFAULT 0,
			client_id varchar(64) NOT NULL DEFAULT '',
			family varchar(64) NOT NULL DEFAULT '',
			scopes text NOT NULL,
			created_at datetime NOT NULL,
			expires_at datetime NULL DEFAULT NULL,
			last_used_at datetime NULL DEFAULT NULL,
			last_ip_hash char(16) NOT NULL DEFAULT '',
			use_count bigint(20) unsigned NOT NULL DEFAULT 0,
			revoked tinyint(1) NOT NULL DEFAULT 0,
			revoked_reason varchar(64) NOT NULL DEFAULT '',
			revoked_at datetime NULL DEFAULT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY token_hash (token_hash),
			KEY user_id (user_id),
			KEY family (family),
			KEY kind (kind)
		) $c;";

		$sql[] = 'CREATE TABLE ' . self::table( 'oauth_clients' ) . " (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			client_id varchar(64) NOT NULL,
			client_name varchar(190) NOT NULL DEFAULT '',
			redirect_uris text NOT NULL,
			client_uri varchar(255) NOT NULL DEFAULT '',
			created_at datetime NOT NULL,
			created_via varchar(16) NOT NULL DEFAULT 'dcr',
			disabled tinyint(1) NOT NULL DEFAULT 0,
			PRIMARY KEY  (id),
			UNIQUE KEY client_id (client_id)
		) $c;";

		$sql[] = 'CREATE TABLE ' . self::table( 'oauth_codes' ) . " (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			code_hash char(64) NOT NULL,
			client_id varchar(64) NOT NULL,
			user_id bigint(20) unsigned NOT NULL,
			redirect_uri text NOT NULL,
			code_challenge varchar(128) NOT NULL,
			scopes text NOT NULL,
			resource varchar(255) NOT NULL DEFAULT '',
			created_at datetime NOT NULL,
			expires_at datetime NOT NULL,
			used tinyint(1) NOT NULL DEFAULT 0,
			PRIMARY KEY  (id),
			UNIQUE KEY code_hash (code_hash)
		) $c;";

		$sql[] = 'CREATE TABLE ' . self::table( 'sessions' ) . " (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			session_hash char(64) NOT NULL,
			token_id bigint(20) unsigned NOT NULL DEFAULT 0,
			user_id bigint(20) unsigned NOT NULL DEFAULT 0,
			auth_method varchar(24) NOT NULL DEFAULT '',
			client_name varchar(190) NOT NULL DEFAULT '',
			client_version varchar(64) NOT NULL DEFAULT '',
			protocol_version varchar(24) NOT NULL DEFAULT '',
			created_at datetime NOT NULL,
			last_seen_at datetime NOT NULL,
			request_count bigint(20) unsigned NOT NULL DEFAULT 0,
			PRIMARY KEY  (id),
			UNIQUE KEY session_hash (session_hash),
			KEY last_seen_at (last_seen_at)
		) $c;";

		$sql[] = 'CREATE TABLE ' . self::table( 'log' ) . " (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			created_at datetime NOT NULL,
			user_id bigint(20) unsigned NOT NULL DEFAULT 0,
			client varchar(190) NOT NULL DEFAULT '',
			auth_method varchar(24) NOT NULL DEFAULT '',
			tool varchar(120) NOT NULL DEFAULT '',
			tool_group varchar(40) NOT NULL DEFAULT '',
			is_write tinyint(1) NOT NULL DEFAULT 0,
			status varchar(20) NOT NULL DEFAULT '',
			object_ref varchar(190) NOT NULL DEFAULT '',
			summary text NULL,
			args text NULL,
			duration_ms int(11) unsigned NOT NULL DEFAULT 0,
			PRIMARY KEY  (id),
			KEY created_at (created_at),
			KEY status (status),
			KEY tool (tool)
		) $c;";

		foreach ( $sql as $statement ) {
			dbDelta( $statement );
		}
		$sql_history = 'CREATE TABLE ' . self::table( 'history' ) . " (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			op_id varchar(40) NOT NULL,
			created_at datetime NOT NULL,
			user_id bigint(20) unsigned NOT NULL DEFAULT 0,
			tool varchar(120) NOT NULL DEFAULT '',
			task varchar(40) NOT NULL DEFAULT '',
			kind varchar(24) NOT NULL DEFAULT '',
			object_ref varchar(190) NOT NULL DEFAULT '',
			label varchar(190) NOT NULL DEFAULT '',
			data longtext NOT NULL,
			reverted_at datetime NULL DEFAULT NULL,
			PRIMARY KEY  (id),
			KEY op_id (op_id),
			KEY object_ref (object_ref),
			KEY task (task),
			KEY created_at (created_at)
		) $c;";
		dbDelta( $sql_history );
		// The three small options read on every request are autoloaded, so they cost no extra query.
		// Switched in place: deleting and re-adding would leave a moment with no settings.
		if ( false !== get_option( 'handymcp_settings' ) ) {
			if ( function_exists( 'wp_set_option_autoload' ) ) {
				wp_set_option_autoload( 'handymcp_settings', true );
			} else {
				global $wpdb;
				$wpdb->update( $wpdb->options, array( 'autoload' => 'yes' ), array( 'option_name' => 'handymcp_settings' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- WordPress before 6.4 has no API to change autoload in place.
				wp_cache_delete( 'alloptions', 'options' );
			}
		}
		if ( false === get_option( 'handymcp_hide_from_admins' ) ) {
			add_option( 'handymcp_hide_from_admins', 0, '', true );
		}
		update_option( 'handymcp_db_version', HANDYMCP_DB_VERSION, true );
	}

	/*
	 * Existing connections keep exactly the scopes they were granted. New scopes
	 * are only ever added when the site owner approves them again (OAuth consent
	 * or a new access token), never by a migration.
	 */

	/**
	 * Daily cleanup.
	 *
	 * @param Settings $settings Settings.
	 */
	public static function maintenance( Settings $settings ): void {
		global $wpdb;
		$days = (int) $settings->get( 'log_retention_days', 90 );
		$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE created_at < %s', self::table( 'log' ), gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table cleanup, nothing to cache.
		$keep = (int) $settings->get( 'history_days', 30 );
		$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE created_at < %s', self::table( 'history' ), gmdate( 'Y-m-d H:i:s', time() - $keep * DAY_IN_SECONDS ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table cleanup, nothing to cache.
		$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE expires_at < %s', self::table( 'oauth_codes' ), gmdate( 'Y-m-d H:i:s', time() - HOUR_IN_SECONDS ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table cleanup, nothing to cache.
		$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE expires_at IS NOT NULL AND expires_at < %s', self::table( 'tokens' ), gmdate( 'Y-m-d H:i:s', time() - 30 * DAY_IN_SECONDS ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table cleanup, nothing to cache.
		$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE last_seen_at < %s', self::table( 'sessions' ), gmdate( 'Y-m-d H:i:s', time() - 30 * DAY_IN_SECONDS ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table cleanup, nothing to cache.
		// Revoked tokens are kept 30 days for the activity log, then removed.
		$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE revoked = 1 AND COALESCE( revoked_at, last_used_at, created_at ) < %s', self::table( 'tokens' ), gmdate( 'Y-m-d H:i:s', time() - 30 * DAY_IN_SECONDS ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table cleanup, nothing to cache.
	}

}