<?php
/**
 * The network log's table (#8).
 *
 * @package ActivityLog404
 */

namespace ActivityLog404\Network;

defined( 'ABSPATH' ) || exit;

/**
 * One table for the whole network, named from `$wpdb->base_prefix`, beside
 * each site's own `{prefix}ops_activity_log` -- which it does not replace.
 *
 * **The storage decision** (docs/network-log.md): every site keeps its own
 * table exactly as before, so a site's screen, REST route, CSV export, WP-CLI,
 * personal-data tools, importer and the Pro add-on's `ProServices::$table`
 * read and write what they always did. The network table holds two kinds of
 * row, told apart by `site_event_id`:
 *
 * - a **copy** of each site row (`site_event_id` = the site row's id,
 *   `blog_id` = the site), written by `NetworkLog::mirror_site_row()` right
 *   after the site insert, and kept in step by the eraser and the purge;
 * - a **network row** (`site_event_id` NULL): what happens in the network
 *   admin and belongs to no site's log -- a site created, a super admin
 *   granted, a network activation. `blog_id` names the site it concerns, or
 *   0 for the network itself.
 *
 * The alternative that keeps per-site tables and adds no copy is a `UNION
 * ALL` over every site's table on every page load: one sub-select per site,
 * each sorted, and a COUNT per site for the pager. That is N queries' work
 * per screen, growing with the network. One table with `(blog_id,
 * timestamp)` and `(timestamp)` indexes answers a page -- one site or all --
 * with one index range read, whatever the network's size. The cost is a
 * second INSERT per event, paid once at write time.
 *
 * `network_id` scopes a multi-network install: base_prefix tables are shared
 * by every network, and a network's super admins are its own
 * (`site_admins` is a per-network option), so each network reads only its
 * own rows.
 */
final class NetworkDatabase {

	/** Unprefixed table name; prefixed with `$wpdb->base_prefix`. */
	public const TABLE_NAME = 'ops_activity_log_network';

	/**
	 * 1: the site table's columns (schema 3 of `Database`) plus
	 * `network_id`, `blog_id` and `site_event_id`.
	 *
	 * Indexes, each for a named reader: `network_time` for the all-sites
	 * page; `blog_time` for one site's page and for a site's retention purge;
	 * `site_row` (unique) makes a copy idempotent and finds it again for the
	 * eraser; `import_ref` (unique, per site) keeps an import's identity
	 * rule from the site table (two sites' Simple History tables reuse the
	 * same ids); `country` lets the country filter's DISTINCT read the index
	 * instead of the table; `user_id` and `event_type` as the site table has.
	 */
	public const SCHEMA_VERSION = '1';

	/** Network (site) option holding the installed schema version. */
	public const SCHEMA_OPTION = 'ops_activity_log_network_schema_version';

	/**
	 * Create or update the table when the plugin is network-active and the
	 * schema is behind. Called from `Plugin::boot()` on every request, so it
	 * costs one cached `get_site_option()` when there is nothing to do.
	 */
	public static function maybe_upgrade(): void {
		if ( ! NetworkLog::is_active() ) {
			return;
		}

		if ( get_site_option( self::SCHEMA_OPTION ) === self::SCHEMA_VERSION ) {
			return;
		}

		self::create_table();
		update_site_option( self::SCHEMA_OPTION, self::SCHEMA_VERSION );
	}

	/**
	 * Create or update the table via dbDelta. Column types are written as
	 * MySQL reports them, or dbDelta ALTERs on every call.
	 */
	public static function create_table(): void {
		global $wpdb;
		$table_name      = self::get_table_name();
		$charset_collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE $table_name (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			network_id bigint(20) unsigned NOT NULL DEFAULT 1,
			blog_id bigint(20) unsigned NOT NULL DEFAULT 0,
			site_event_id bigint(20) unsigned DEFAULT NULL,
			timestamp datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			user_id bigint(20) unsigned DEFAULT NULL,
			user_email varchar(100) DEFAULT NULL,
			ip_address varchar(45) DEFAULT NULL,
			event_type varchar(50) NOT NULL,
			object_type varchar(50) DEFAULT NULL,
			object_id bigint(20) unsigned DEFAULT NULL,
			summary text,
			details longtext,
			import_source varchar(20) DEFAULT NULL,
			import_id bigint(20) unsigned DEFAULT NULL,
			country char(2) DEFAULT NULL,
			city varchar(80) DEFAULT NULL,
			device_class varchar(10) DEFAULT NULL,
			browser varchar(40) DEFAULT NULL,
			os varchar(40) DEFAULT NULL,
			PRIMARY KEY  (id),
			KEY network_time (network_id,timestamp),
			KEY blog_time (blog_id,timestamp),
			KEY user_id (user_id),
			KEY event_type (event_type),
			KEY country (country),
			UNIQUE KEY site_row (blog_id,site_event_id),
			UNIQUE KEY import_ref (blog_id,import_source,import_id,timestamp)
		) $charset_collate;";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );
	}

	/**
	 * Drop the table and forget its schema version.
	 */
	public static function drop_table(): void {
		global $wpdb;
		$table_name = self::get_table_name();
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery -- the plugin's own table name; no input.
		$wpdb->query( "DROP TABLE IF EXISTS $table_name;" );
		delete_site_option( self::SCHEMA_OPTION );
	}

	/**
	 * The table's full name: network-wide, so `base_prefix`, never the
	 * current site's `prefix`.
	 */
	public static function get_table_name(): string {
		global $wpdb;
		return $wpdb->base_prefix . self::TABLE_NAME;
	}
}