<?php
/**
 * Schema installation and table-name registry.
 *
 * @package CommentMate
 */

declare(strict_types=1);

namespace CommentMate;

defined('ABSPATH') || exit;

final class Database {
	public const SCHEMA_VERSION = '5';

	/**
	 * @var list<string>
	 */
	private const TABLE_SUFFIXES = array(
		'commentmate_reviews',
		'commentmate_share_tokens',
		'commentmate_guest_sessions',
		'commentmate_pages',
		'commentmate_comments',
		'commentmate_comment_messages',
		'commentmate_attachments',
		'commentmate_audit_events',
		'commentmate_rate_limits',
		'commentmate_idempotency',
	);

	public static function table(string $suffix): string {
		global $wpdb;

		if (!in_array($suffix, self::TABLE_SUFFIXES, true) || !preg_match('/\A[A-Za-z0-9_]+\z/', $wpdb->prefix)) {
			throw new \InvalidArgumentException('Invalid CommentMate table identity.');
		}
		return $wpdb->prefix . $suffix;
	}

	/**
	 * @return list<string>
	 */
	public static function tables(): array {
		$tables = array();
		foreach (self::TABLE_SUFFIXES as $suffix) {
			$tables[] = self::table($suffix);
		}

		return $tables;
	}

	public static function maybe_upgrade(): bool {
		if (self::SCHEMA_VERSION === (string) get_option('commentmate_schema_version', '')) {
			return true;
		}

		return self::install();
	}

	/**
	 * Creates tables through WordPress's migration API. JSON is stored in
	 * LONGTEXT for compatibility with the WordPress-supported MySQL/MariaDB
	 * range; all JSON is validated at the application boundary.
	 */
	public static function install(): bool {
		global $wpdb;

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		$charset_collate = $wpdb->get_charset_collate();
		// Comment/idempotency writes use transactions, so every plugin table is
		// explicitly created as InnoDB rather than relying on a host default.
		$table_options = trim($charset_collate . ' ENGINE=InnoDB');

		$reviews = self::table('commentmate_reviews');
		$tokens = self::table('commentmate_share_tokens');
		$sessions = self::table('commentmate_guest_sessions');
		$pages = self::table('commentmate_pages');
		$comments = self::table('commentmate_comments');
		$messages = self::table('commentmate_comment_messages');
		$attachments = self::table('commentmate_attachments');
		$events = self::table('commentmate_audit_events');
		$limits = self::table('commentmate_rate_limits');
		$idempotency = self::table('commentmate_idempotency');

		$sql = array(
			"CREATE TABLE {$reviews} (
				id bigint(20) unsigned NOT NULL auto_increment,
				owner_user_id bigint(20) unsigned NOT NULL,
				state varchar(20) NOT NULL default 'draft',
				active_slot tinyint(3) unsigned NULL default NULL,
				token_version int(10) unsigned NOT NULL default 1,
				metadata longtext NULL,
				created_at datetime NOT NULL,
				updated_at datetime NOT NULL,
				paused_at datetime NULL,
				ended_at datetime NULL,
				cleanup_locked tinyint(1) NOT NULL default 0,
				PRIMARY KEY  (id),
				UNIQUE KEY one_active_review (active_slot),
				KEY state (state),
				KEY owner_user_id (owner_user_id)
			) {$table_options};",
			"CREATE TABLE {$tokens} (
				id bigint(20) unsigned NOT NULL auto_increment,
				review_id bigint(20) unsigned NOT NULL,
				selector varchar(32) NOT NULL,
				secret_hash char(64) NOT NULL,
				secret_ciphertext longtext NULL,
				token_version int(10) unsigned NOT NULL,
				created_at datetime NOT NULL,
				expires_at datetime NULL,
				revoked_at datetime NULL,
				last_used_at datetime NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY selector (selector),
				KEY review_id (review_id),
				KEY active_token (review_id, revoked_at)
			) {$table_options};",
			"CREATE TABLE {$sessions} (
				id bigint(20) unsigned NOT NULL auto_increment,
				review_id bigint(20) unsigned NOT NULL,
				session_hash char(64) NOT NULL,
				csrf_hash char(64) NOT NULL,
				client_hash char(64) NOT NULL,
				token_version int(10) unsigned NOT NULL,
				guest_name varchar(191) NOT NULL default 'Guest',
				created_at datetime NOT NULL,
				last_seen_at datetime NOT NULL,
				expires_at datetime NOT NULL,
				absolute_expires_at datetime NOT NULL,
				revoked_at datetime NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY session_hash (session_hash),
				KEY review_active (review_id, revoked_at),
				KEY expiry (expires_at)
			) {$table_options};",
			"CREATE TABLE {$pages} (
				id bigint(20) unsigned NOT NULL auto_increment,
				review_id bigint(20) unsigned NOT NULL,
				url_hash char(64) NOT NULL,
				canonical_url text NOT NULL,
				page_title varchar(255) NOT NULL default '',
				created_at datetime NOT NULL,
				updated_at datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY review_url (review_id, url_hash),
				KEY review_id (review_id)
			) {$table_options};",
			"CREATE TABLE {$comments} (
				id bigint(20) unsigned NOT NULL auto_increment,
				review_id bigint(20) unsigned NOT NULL,
				comment_number bigint(20) unsigned NOT NULL default 0,
				page_id bigint(20) unsigned NOT NULL,
				author_session_id bigint(20) unsigned NOT NULL,
				author_name varchar(191) NOT NULL default 'Guest',
				status varchar(20) NOT NULL default 'open',
				anchor_json longtext NOT NULL,
				body_html longtext NOT NULL,
				screenshot_state varchar(20) NOT NULL default 'unavailable',
				created_at datetime NOT NULL,
				updated_at datetime NOT NULL,
				resolved_at datetime NULL,
				resolved_by_session_id bigint(20) unsigned NULL,
				deleted_at datetime NULL,
				PRIMARY KEY  (id),
				KEY review_page (review_id, page_id),
				KEY review_comment_number (review_id, comment_number),
				KEY review_status (review_id, status),
				KEY author_session (author_session_id),
				KEY visible_comments (review_id, deleted_at)
			) {$table_options};",
			"CREATE TABLE {$messages} (
				id bigint(20) unsigned NOT NULL auto_increment,
				comment_id bigint(20) unsigned NOT NULL,
				author_session_id bigint(20) unsigned NULL,
				author_user_id bigint(20) unsigned NULL,
				body_html longtext NOT NULL,
				created_at datetime NOT NULL,
				edited_at datetime NULL,
				deleted_at datetime NULL,
				PRIMARY KEY  (id),
				KEY comment_id (comment_id)
			) {$table_options};",
			"CREATE TABLE {$attachments} (
				id bigint(20) unsigned NOT NULL auto_increment,
				review_id bigint(20) unsigned NOT NULL,
				comment_id bigint(20) unsigned NOT NULL,
				message_id bigint(20) unsigned NULL,
				storage_key varchar(191) NOT NULL,
				checksum char(64) NOT NULL,
				mime_type varchar(100) NOT NULL,
				original_name varchar(255) NOT NULL,
				byte_size bigint(20) unsigned NOT NULL,
				disposition varchar(20) NOT NULL default 'transient',
				media_attachment_id bigint(20) unsigned NULL,
				created_at datetime NOT NULL,
				deleted_at datetime NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY storage_key (storage_key),
				KEY review_id (review_id),
				KEY comment_id (comment_id)
			) {$table_options};",
			"CREATE TABLE {$events} (
				id bigint(20) unsigned NOT NULL auto_increment,
				review_id bigint(20) unsigned NOT NULL,
				actor_type varchar(20) NOT NULL,
				actor_id bigint(20) unsigned NULL,
				event_type varchar(100) NOT NULL,
				data_json longtext NULL,
				created_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY review_id (review_id),
				KEY event_type (event_type)
			) {$table_options};",
			"CREATE TABLE {$limits} (
				id bigint(20) unsigned NOT NULL auto_increment,
				review_id bigint(20) unsigned NOT NULL default 0,
				bucket varchar(64) NOT NULL,
				subject_hash char(64) NOT NULL,
				window_started bigint(20) unsigned NOT NULL,
				request_count int(10) unsigned NOT NULL default 0,
				expires_at datetime NOT NULL,
				updated_at datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY review_bucket_subject_window (review_id, bucket, subject_hash, window_started),
				KEY review_id (review_id),
				KEY expires_at (expires_at)
			) {$table_options};",
			"CREATE TABLE {$idempotency} (
				id bigint(20) unsigned NOT NULL auto_increment,
				review_id bigint(20) unsigned NOT NULL,
				session_id bigint(20) unsigned NOT NULL,
				route varchar(100) NOT NULL,
				request_key varchar(128) NOT NULL,
				response_code smallint(5) unsigned NOT NULL,
				response_body longtext NOT NULL,
				created_at datetime NOT NULL,
				expires_at datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY session_route_key (session_id, route, request_key),
				KEY expires_at (expires_at),
				KEY review_id (review_id)
			) {$table_options};",
		);

		foreach ($sql as $statement) {
			dbDelta($statement);
		}

		foreach (self::tables() as $table) {
			$present = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($table))); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin schema installation and migration must inspect the current database; WordPress object APIs cannot manage these tables.
			if ($table !== $present) {
				return false;
			}
			if (!self::ensure_innodb($table)) {
				return false;
			}
		}
		if (!self::migrate_rate_limit_index($limits)) {
			return false;
		}
		if (!self::ensure_share_token_secret_column($tokens)) {
			return false;
		}
		if (!self::migrate_comment_numbers($comments)) {
			return false;
		}

		if (!add_option('commentmate_schema_version', self::SCHEMA_VERSION, '', false)) {
			update_option('commentmate_schema_version', self::SCHEMA_VERSION, false);
		}

		return true;
	}

	private static function ensure_innodb(string $table): bool {
		global $wpdb;

		$status = $wpdb->get_row($wpdb->prepare('SHOW TABLE STATUS LIKE %s', $wpdb->esc_like($table)), ARRAY_A); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin schema installation and migration must inspect the current database; WordPress object APIs cannot manage these tables.
		if (is_array($status) && isset($status['Engine']) && 'innodb' === strtolower((string) $status['Engine'])) {
			return true;
		}

		if (false === $wpdb->query($wpdb->prepare('ALTER TABLE %i ENGINE=InnoDB', $table))) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Versioned plugin schema migration or opted-in uninstall requires this table change; identifiers are bound and core object APIs cannot express it.
			return false;
		}

		$status = $wpdb->get_row($wpdb->prepare('SHOW TABLE STATUS LIKE %s', $wpdb->esc_like($table)), ARRAY_A); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin schema installation and migration must inspect the current database; WordPress object APIs cannot manage these tables.
		return is_array($status) && isset($status['Engine']) && 'innodb' === strtolower((string) $status['Engine']);
	}

	/**
	 * dbDelta adds indexes but does not reliably remove an obsolete unique key.
	 * Rate limits are now review-scoped, so retaining the former global key
	 * would merge counters between different reviews and defeat targeted cleanup.
	 */
	private static function migrate_rate_limit_index(string $table): bool {
		global $wpdb;
		// MySQL/MariaDB allow a WHERE clause in SHOW INDEX, but not LIMIT. The
		// latter made this migration report a false negative, then try to add an
		// index that dbDelta had already created and abort plugin activation.
		$legacy = $wpdb->get_var($wpdb->prepare('SHOW INDEX FROM %i WHERE Key_name = %s', $table, 'bucket_subject_window')); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Versioned plugin schema migration or opted-in uninstall requires this table change; identifiers are bound and core object APIs cannot express it.
		if (null !== $legacy && false === $wpdb->query($wpdb->prepare('ALTER TABLE %i DROP INDEX `bucket_subject_window`', $table))) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Versioned plugin schema migration or opted-in uninstall requires this table change; identifiers are bound and core object APIs cannot express it.
			return false;
		}
		$current = $wpdb->get_var($wpdb->prepare('SHOW INDEX FROM %i WHERE Key_name = %s', $table, 'review_bucket_subject_window')); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Versioned plugin schema migration or opted-in uninstall requires this table change; identifiers are bound and core object APIs cannot express it.
		if (null === $current && false === $wpdb->query($wpdb->prepare('ALTER TABLE %i ADD UNIQUE KEY `review_bucket_subject_window` (`review_id`, `bucket`, `subject_hash`, `window_started`)', $table))) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Versioned plugin schema migration or opted-in uninstall requires this table change; identifiers are bound and core object APIs cannot express it.
			return false;
		}

		return true;
	}

	/**
	 * dbDelta normally adds this nullable column. Verify it explicitly, with a
	 * narrow fallback for older installations whose database abstraction ignored
	 * the change, before any owner screen tries to retrieve an active link.
	 */
	private static function ensure_share_token_secret_column(string $table): bool {
		global $wpdb;
		$column = $wpdb->get_var($wpdb->prepare('SHOW COLUMNS FROM %i LIKE %s', $table, 'secret_ciphertext')); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin schema installation and migration must inspect the current database; WordPress object APIs cannot manage these tables.
		if (null !== $column) {
			return true;
		}

		$updated = $wpdb->query($wpdb->prepare('ALTER TABLE %i ADD COLUMN `secret_ciphertext` longtext NULL AFTER `secret_hash`', $table)); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Versioned plugin schema migration or opted-in uninstall requires this table change; identifiers are bound and core object APIs cannot express it.
		if (false !== $updated) {
			return true;
		}

		// A concurrent activation may have added the same column after the first
		// check. Re-read rather than reporting a false migration failure.
		$column = $wpdb->get_var($wpdb->prepare('SHOW COLUMNS FROM %i LIKE %s', $table, 'secret_ciphertext')); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin schema installation and migration must inspect the current database; WordPress object APIs cannot manage these tables.
		return null !== $column;
	}

	/**
	 * Gives every existing comment a stable review-scoped display number. New
	 * comments receive their number inside the review-row mutation lock; this
	 * migration assigns a chronological baseline only to unnumbered records
	 * created before that field existed. Soft-deleted rows are deliberately
	 * included so their numbers are never reused.
	 */
	private static function migrate_comment_numbers(string $table): bool {
		global $wpdb;
		$column = $wpdb->get_var($wpdb->prepare('SHOW COLUMNS FROM %i LIKE %s', $table, 'comment_number')); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin schema installation and migration must inspect the current database; WordPress object APIs cannot manage these tables.
		if (null === $column) {
			return false;
		}

		$review_ids = $wpdb->get_col($wpdb->prepare('SELECT DISTINCT review_id FROM %i ORDER BY review_id ASC', $table)); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin schema installation and migration must inspect the current database; WordPress object APIs cannot manage these tables.
		if (!is_array($review_ids)) {
			return false;
		}

		foreach ($review_ids as $review_id) {
			$review_id = (int) $review_id;
			if ($review_id < 1) {
				continue;
			}
			$unassigned = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin schema installation and migration must inspect the current database; WordPress object APIs cannot manage these tables.
				$wpdb->prepare(
					'SELECT COUNT(*) FROM %i WHERE review_id = %d AND comment_number = 0',
					$table, $review_id
				)
			);
			if (!is_numeric($unassigned)) {
				return false;
			}
			if (0 === (int) $unassigned) {
				continue;
			}
			$next = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin schema installation and migration must inspect the current database; WordPress object APIs cannot manage these tables.
				$wpdb->prepare(
					'SELECT COALESCE(MAX(comment_number), 0) + 1 FROM %i WHERE review_id = %d',
					$table, $review_id
				)
			);
			if (!is_numeric($next) || (int) $next < 1) {
				return false;
			}
			$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin schema installation and migration must inspect the current database; WordPress object APIs cannot manage these tables.
				$wpdb->prepare(
					'SELECT id FROM %i WHERE review_id = %d AND comment_number = 0 ORDER BY created_at ASC, id ASC',
					$table, $review_id
				),
				ARRAY_A
			);
			if (!is_array($rows)) {
				return false;
			}
			$number = (int) $next;
			foreach ($rows as $row) {
				$updated = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin schema installation and migration must inspect the current database; WordPress object APIs cannot manage these tables.
					$table,
					array('comment_number' => $number),
					array('id' => (int) $row['id'], 'review_id' => $review_id, 'comment_number' => 0),
					array('%d'),
					array('%d', '%d', '%d')
				);
				if (false === $updated) {
					return false;
				}
				$number++;
			}
		}

		return true;
	}
}