<?php
/**
 * Idempotent plugin-owned schema installation.
 *
 * @package Px320\SiteAudit
 */

declare(strict_types=1);

namespace Px320\SiteAudit\Storage;

use Px320\SiteAudit\Filesystem\WordPressPathProvider;
use WP_Site;

final class Installer {
	public static function activate( bool $network_wide = false ): void {
		self::install_current_site();

		if ( $network_wide && is_multisite() ) {
			self::remember_site( get_current_blog_id() );
		}
	}

	public static function maybe_upgrade(): void {
		if ( Schema::VERSION !== get_option( Schema::VERSION_OPTION ) ) {
			self::install_current_site();
		}
	}

	public static function initialize_site( WP_Site $site ): void {
		$network_plugins = is_multisite() ? get_site_option( 'active_sitewide_plugins', array() ) : array();
		$network_plugins = is_array( $network_plugins ) ? $network_plugins : array();

		if ( ! isset( $network_plugins[ plugin_basename( PX320_SITE_AUDIT_FILE ) ] ) ) {
			return;
		}

		switch_to_blog( (int) $site->blog_id );
		try {
			self::install_current_site();
		} finally {
			restore_current_blog();
		}
	}

	private static function install_current_site(): void {
		global $wpdb;

		$upgrade_api = ( new WordPressPathProvider() )->core_file( 'wp-admin/includes/upgrade.php' );
		require_once $upgrade_api;

		$tables  = Schema::tables( $wpdb );
		$collate = $wpdb->get_charset_collate();
		self::prepare_decision_migration( $tables['decisions'] );

		$sql = array(
			"CREATE TABLE {$tables['runs']} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				run_uuid char(36) NOT NULL,
				status varchar(24) NOT NULL,
				profile varchar(32) NOT NULL,
				language varchar(12) NOT NULL,
				registry_version varchar(24) NOT NULL,
				budgets longtext NOT NULL,
				coverage longtext NOT NULL,
				event_log longtext NOT NULL,
				created_at datetime NOT NULL,
				updated_at datetime NOT NULL,
				completed_at datetime NULL,
				retention_until datetime NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY run_uuid (run_uuid),
				KEY status_updated (status,updated_at)
			) {$collate};",
			"CREATE TABLE {$tables['tasks']} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				run_id bigint(20) unsigned NOT NULL,
				task_uuid char(36) NOT NULL,
				check_id varchar(96) NOT NULL,
				status varchar(24) NOT NULL,
				cursor_json longtext NOT NULL,
				attempts smallint(5) unsigned NOT NULL DEFAULT 0,
				lock_hash char(64) NULL,
				lock_expires_at datetime NULL,
				error_code varchar(64) NULL,
				updated_at datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY task_uuid (task_uuid),
				KEY run_status (run_id,status),
				KEY lock_expiry (lock_expires_at)
			) {$collate};",
			"CREATE TABLE {$tables['findings']} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				run_id bigint(20) unsigned NOT NULL,
				finding_uuid char(36) NOT NULL,
				check_id varchar(96) NOT NULL,
				category varchar(40) NOT NULL,
				status varchar(24) NOT NULL,
				severity varchar(16) NOT NULL,
				confidence varchar(16) NOT NULL,
				object_hash char(64) NOT NULL,
				contract_json longtext NOT NULL,
				first_seen_at datetime NOT NULL,
				last_seen_at datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY finding_uuid (finding_uuid),
				KEY run_severity (run_id,severity,status),
				KEY check_object (check_id,object_hash)
			) {$collate};",
			"CREATE TABLE {$tables['assertions']} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				run_id bigint(20) unsigned NOT NULL,
				assertion_key varchar(96) NOT NULL,
				state varchar(24) NOT NULL,
				comment_masked text NOT NULL,
				actor_id bigint(20) unsigned NOT NULL,
				asserted_at datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY run_assertion (run_id,assertion_key)
			) {$collate};",
			"CREATE TABLE {$tables['decisions']} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				finding_id bigint(20) unsigned NOT NULL,
				decision varchar(24) NOT NULL,
				reason_masked text NOT NULL,
				actor_id bigint(20) unsigned NOT NULL,
				decided_at datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY finding_current (finding_id),
				KEY finding_decision (finding_id,decision)
			) {$collate};",
			"CREATE TABLE {$tables['operations']} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				operation_uuid char(36) NOT NULL,
				run_id bigint(20) unsigned NULL,
				service varchar(64) NOT NULL,
				disclosure_version varchar(24) NOT NULL,
				data_classes longtext NOT NULL,
				initiator_id bigint(20) unsigned NOT NULL,
				payload_hash char(64) NOT NULL,
				outcome varchar(24) NOT NULL,
				request_id varchar(96) NULL,
				created_at datetime NOT NULL,
				completed_at datetime NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY operation_uuid (operation_uuid),
				KEY service_created (service,created_at)
			) {$collate};",
			"CREATE TABLE {$tables['import_challenges']} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				site_ref char(36) NOT NULL,
				challenge_hash char(64) NOT NULL,
				actor_id bigint(20) unsigned NOT NULL,
				created_at datetime NOT NULL,
				expires_at datetime NOT NULL,
				used_at datetime NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY site_ref (site_ref),
				KEY expiry_used (expires_at,used_at)
			) {$collate};",
			"CREATE TABLE {$tables['crawl_pages']} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				run_id bigint(20) unsigned NOT NULL,
				url_hash char(64) NOT NULL,
				parent_hash char(64) NULL,
				depth smallint(5) unsigned NOT NULL DEFAULT 0,
				redirect_hops tinyint(3) unsigned NOT NULL DEFAULT 0,
				resource_class varchar(16) NOT NULL,
				state varchar(24) NOT NULL,
				runtime_path varbinary(1536) NULL,
				display_path varchar(1536) NULL,
				signals_json longtext NOT NULL,
				http_status smallint(5) unsigned NULL,
				content_class varchar(16) NOT NULL,
				error_code varchar(64) NULL,
				created_at datetime NOT NULL,
				updated_at datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY run_url (run_id,url_hash),
				KEY run_queue (run_id,state,resource_class,id),
				KEY parent_hash (parent_hash)
			) {$collate};",
			"CREATE TABLE {$tables['code_files']} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				run_id bigint(20) unsigned NOT NULL,
				file_hash char(64) NOT NULL,
				scope varchar(32) NOT NULL,
				item_type varchar(16) NOT NULL,
				extension varchar(12) NOT NULL,
				state varchar(24) NOT NULL,
				runtime_path varbinary(2048) NULL,
				masked_path varchar(96) NOT NULL,
				size_bytes bigint(20) unsigned NOT NULL DEFAULT 0,
				signals_json longtext NOT NULL,
				error_code varchar(64) NULL,
				created_at datetime NOT NULL,
				updated_at datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY run_file (run_id,file_hash),
				KEY run_queue (run_id,state,item_type,id),
				KEY run_scope (run_id,scope,item_type)
			) {$collate};",
			"CREATE TABLE {$tables['code_symbols']} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				run_id bigint(20) unsigned NOT NULL,
				file_hash char(64) NOT NULL,
				symbol_hash char(64) NOT NULL,
				symbol_type varchar(16) NOT NULL,
				line_number int(10) unsigned NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY run_file_symbol (run_id,file_hash,symbol_hash,symbol_type),
				KEY run_symbol (run_id,symbol_hash,symbol_type)
			) {$collate};",
		);

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

		if ( false === get_option( Schema::VERSION_OPTION, false ) ) {
			add_option( Schema::VERSION_OPTION, Schema::VERSION, '', false );
		} else {
			update_option( Schema::VERSION_OPTION, Schema::VERSION, false );
		}

		if ( is_multisite() ) {
			self::remember_site( get_current_blog_id() );
		}
	}

	private static function prepare_decision_migration( string $table ): void {
		global $wpdb;

		// The table is selected from the compiled Schema allowlist.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) );
		if ( $table !== $exists ) {
			return;
		}

		// Schema 1.5 introduces one current decision per Finding. Keep the newest
		// plugin-owned row before dbDelta adds the unique key.
		// The identifier is selected from Schema::tables(), never from input.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
		$result = $wpdb->query( "DELETE older FROM {$table} older INNER JOIN {$table} newer ON older.finding_id = newer.finding_id AND older.id < newer.id" );
		if ( false === $result ) {
			throw new StorageException( 'finding_decision_migration_failed' );
		}
	}

	private static function remember_site( int $blog_id ): void {
		$sites = get_site_option( Schema::SITES_OPTION, array() );
		$sites = is_array( $sites ) ? array_map( 'intval', $sites ) : array();

		if ( ! in_array( $blog_id, $sites, true ) ) {
			$sites[] = $blog_id;
			update_site_option( Schema::SITES_OPTION, array_values( array_unique( $sites ) ) );
		}
	}

	private function __construct() {
	}
}