<?php
/**
 * Activation, schema, defaults, and upgrade routines.
 *
 * MULTISITE
 * ---------
 * Upgrade Pilot's tables are created once per NETWORK, not once per subsite
 * (see UPilot_Store for the full argument). The rows describe plugin files,
 * which a network shares; per-subsite tables would hold N identical copies.
 *
 * The one genuinely per-subsite fact — which plugins are active there, and so
 * what that subsite's readiness verdict is — lives in a single network table
 * keyed by blog_id (upilot_site_status). New subsites get their row from
 * wp_initialize_site; deleted subsites have it removed by wp_delete_site.
 *
 * @package Upgrade_Pilot
 */

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

/**
 * Class UPilot_Install
 */
class UPilot_Install {

	/**
	 * Register the subsite lifecycle hooks.
	 *
	 * Called on every request (not just activation) so that a network which
	 * gains or loses a subsite stays consistent.
	 */
	public static function hooks() {
		if ( ! is_multisite() ) {
			return;
		}
		add_action( 'wp_initialize_site', array( __CLASS__, 'on_new_site' ), 100 );
		add_action( 'wp_delete_site', array( __CLASS__, 'on_delete_site' ) );
	}

	/**
	 * Activation hook.
	 *
	 * @param bool $network_wide True when network-activated on multisite.
	 */
	public static function activate( $network_wide = false ) {
		// The schema is network-wide either way, so $network_wide does not change
		// WHAT we create — but it does tell us whether every existing subsite needs
		// a status row seeded now, or just this one.
		self::create_tables();
		self::set_defaults();
		UPilot_Store::update_option( 'upilot_db_version', UPILOT_DB_VERSION );

		if ( is_multisite() && $network_wide ) {
			self::seed_all_sites();
		} else {
			self::seed_site( get_current_blog_id() );
		}
	}

	/**
	 * Deactivation hook: clear scheduled events. Data stays; uninstall removes it.
	 *
	 * @param bool $network_wide True when network-deactivated.
	 */
	public static function deactivate( $network_wide = false ) {
		self::clear_schedules();

		// WP-Cron is per-site. Jobs are only ever scheduled on the main site
		// (UPilot_Store::is_cron_site), but a network that was upgraded from an
		// older build may still carry stray events on subsites — sweep them so a
		// deactivate genuinely leaves no scheduled work behind anywhere.
		if ( is_multisite() && $network_wide ) {
			foreach ( self::site_ids() as $blog_id ) {
				switch_to_blog( $blog_id );
				self::clear_schedules();
				restore_current_blog();
			}
		}
	}

	/**
	 * Remove every scheduled event this plugin owns, on the current site.
	 */
	public static function clear_schedules() {
		foreach ( array( 'upilot_daily_snapshot', 'upilot_weekly_rescan', 'upilot_queue_tick', 'upilot_daily_digest', 'upilot_network_rollup' ) as $hook ) {
			wp_clear_scheduled_hook( $hook );
		}
	}

	/**
	 * Run schema upgrades when the plugin updates.
	 */
	public static function maybe_upgrade() {
		if ( (string) UPilot_Store::get_option( 'upilot_db_version' ) !== (string) UPILOT_DB_VERSION ) {
			self::create_tables();
			UPilot_Store::update_option( 'upilot_db_version', UPILOT_DB_VERSION );
		}
	}

	/**
	 * A new subsite appeared: give it a status row.
	 *
	 * @param WP_Site|int $site New site.
	 */
	public static function on_new_site( $site ) {
		$blog_id = is_object( $site ) ? (int) $site->blog_id : (int) $site;
		if ( $blog_id > 0 ) {
			self::seed_site( $blog_id );
		}
	}

	/**
	 * A subsite is being deleted: drop its status row so nothing is orphaned.
	 *
	 * @param WP_Site|int $site Site being deleted.
	 */
	public static function on_delete_site( $site ) {
		global $wpdb;
		$blog_id = is_object( $site ) ? (int) $site->blog_id : (int) $site;
		if ( $blog_id > 0 ) {
			$wpdb->delete( UPilot_Store::table( 'site_status' ), array( 'blog_id' => $blog_id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table; lifecycle cleanup when a subsite is deleted.
		}
	}

	/**
	 * Seed a status row for one subsite (idempotent).
	 *
	 * @param int $blog_id Blog id.
	 */
	public static function seed_site( $blog_id ) {
		global $wpdb;
		$blog_id = (int) $blog_id;
		$table   = UPilot_Store::table( 'site_status' );

		$exists = $wpdb->get_var( $wpdb->prepare( "SELECT blog_id FROM {$table} WHERE blog_id = %d", $blog_id ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery -- own table name; no cache layer for schema bookkeeping.
		if ( null !== $exists ) {
			return;
		}

		$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- own table; seeds one status row per subsite.
			$table,
			array(
				'blog_id'     => $blog_id,
				'verdict'     => 'unknown',
				'headline'    => '',
				'facts'       => 0,
				'advisories'  => 0,
				'criticals'   => 0,
				'active_plugins' => 0,
				'computed_at' => '0000-00-00 00:00:00',
			),
			array( '%d', '%s', '%s', '%d', '%d', '%d', '%d', '%s' )
		);
	}

	/**
	 * Seed a status row for every subsite on the network.
	 */
	public static function seed_all_sites() {
		foreach ( self::site_ids() as $blog_id ) {
			self::seed_site( $blog_id );
		}
	}

	/**
	 * All blog ids on the network (or just this one on single site).
	 *
	 * Paged so a very large network does not build a huge array of site objects.
	 *
	 * @return int[]
	 */
	public static function site_ids() {
		if ( ! is_multisite() ) {
			return array( get_current_blog_id() );
		}

		$ids    = array();
		$offset = 0;
		do {
			$batch = get_sites(
				array(
					'fields'                 => 'ids',
					'number'                 => 200,
					'offset'                 => $offset,
					'update_site_cache'      => false,
					'update_site_meta_cache' => false,
					'orderby'                => 'id',
				)
			);
			foreach ( (array) $batch as $id ) {
				$ids[] = (int) $id;
			}
			$offset += 200;
		} while ( count( $batch ) === 200 );

		return $ids;
	}

	/**
	 * Create/upgrade custom tables via dbDelta.
	 */
	public static function create_tables() {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$charset = $wpdb->get_charset_collate();
		$p       = UPilot_Store::table_prefix() . 'upilot_';

		dbDelta(
			"CREATE TABLE {$p}snapshots (
				id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
				item_type VARCHAR(6) NOT NULL DEFAULT 'plugin',
				slug VARCHAR(191) NOT NULL,
				captured_at DATETIME NOT NULL,
				state VARCHAR(12) NOT NULL DEFAULT 'ok',
				payload_hash CHAR(32) NOT NULL DEFAULT '',
				payload LONGTEXT NULL,
				PRIMARY KEY  (id),
				KEY item_slug (item_type, slug, captured_at)
			) $charset;"
		);

		dbDelta(
			"CREATE TABLE {$p}events (
				id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
				created_at DATETIME NOT NULL,
				item_type VARCHAR(6) NOT NULL DEFAULT 'plugin',
				slug VARCHAR(191) NOT NULL,
				event_type VARCHAR(32) NOT NULL,
				severity VARCHAR(8) NOT NULL DEFAULT 'info',
				details LONGTEXT NULL,
				acknowledged TINYINT(1) NOT NULL DEFAULT 0,
				PRIMARY KEY  (id),
				KEY created_at (created_at),
				KEY slug (slug)
			) $charset;"
		);

		dbDelta(
			"CREATE TABLE {$p}scan_queue (
				id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
				scan_id BIGINT(20) UNSIGNED NOT NULL,
				slug VARCHAR(191) NOT NULL,
				file VARCHAR(512) NOT NULL,
				status VARCHAR(8) NOT NULL DEFAULT 'pending',
				claimed_at DATETIME NULL,
				PRIMARY KEY  (id),
				KEY scan_status (scan_id, status)
			) $charset;"
		);

		dbDelta(
			"CREATE TABLE {$p}findings (
				id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
				scan_id BIGINT(20) UNSIGNED NOT NULL,
				slug VARCHAR(191) NOT NULL,
				item_version VARCHAR(32) NOT NULL DEFAULT '',
				file VARCHAR(512) NOT NULL,
				file_hash CHAR(32) NOT NULL DEFAULT '',
				line INT(11) UNSIGNED NOT NULL DEFAULT 0,
				rule_id VARCHAR(64) NOT NULL,
				severity VARCHAR(8) NOT NULL DEFAULT 'warning',
				php_version VARCHAR(8) NOT NULL DEFAULT '',
				message TEXT NULL,
				suppressed TINYINT(1) NOT NULL DEFAULT 0,
				PRIMARY KEY  (id),
				KEY scan_slug (scan_id, slug),
				KEY cache_lookup (slug, file_hash)
			) $charset;"
		);

		// Per-subsite rollup cache. On single site this holds exactly one row.
		// It is the one table keyed by blog_id, because "which plugins are active
		// here" is the one fact that genuinely differs between subsites.
		dbDelta(
			"CREATE TABLE {$p}site_status (
				blog_id BIGINT(20) UNSIGNED NOT NULL,
				verdict VARCHAR(8) NOT NULL DEFAULT 'unknown',
				headline VARCHAR(191) NOT NULL DEFAULT '',
				facts INT(11) UNSIGNED NOT NULL DEFAULT 0,
				advisories INT(11) UNSIGNED NOT NULL DEFAULT 0,
				criticals INT(11) UNSIGNED NOT NULL DEFAULT 0,
				active_plugins INT(11) UNSIGNED NOT NULL DEFAULT 0,
				computed_at DATETIME NOT NULL,
				PRIMARY KEY  (blog_id)
			) $charset;"
		);
	}

	/**
	 * Default settings (only set when absent).
	 */
	public static function set_defaults() {
		if ( false !== UPilot_Store::get_option( 'upilot_settings', false ) ) {
			return;
		}
		UPilot_Store::update_option(
			'upilot_settings',
			array(
				'target_php'          => '8.4',
				'target_wp'           => 'latest',
				'alert_email'         => UPilot_Store::default_admin_email(),
				'abandoned_months'    => 24,
				'stale_tested_majors' => 3,
				'weekly_rescan'       => 1,
				'email_critical'      => 1,
				'email_digest'        => 1,
			)
		);
	}
}
