<?php
/**
 * The plugin's own tables.
 *
 * @package WorkMoreCookieCrumb
 */

namespace WorkMore\CookieCrumb\Database;

/**
 * Two tables per site: the consent log (one row per decision a visitor
 * makes) and the daily counters the dashboard reads (which hold no personal
 * data, so they outlive the log rows they were counted from).
 *
 * Created on activation and brought up to date by maybeUpgrade() whenever
 * the code is newer than the stored schema version. Not frozen before
 * 1.0.0: no site has these tables yet, so sql() may still change freely.
 */
final class Schema {

	/**
	 * Bump when sql() changes; every site then runs dbDelta once, on its
	 * next request.
	 */
	public const VERSION = 3;

	public const VERSION_OPTION = 'wmcc_db_version';

	public const CONSENT_LOG = 'wmcc_consent_log';
	public const DAILY_STATS = 'wmcc_daily_stats';

	/**
	 * Table names, without the site's prefix.
	 *
	 * @return array<int, string>
	 */
	public static function names(): array {
		return array( self::CONSENT_LOG, self::DAILY_STATS );
	}

	/**
	 * A table's full name on the current site.
	 *
	 * @param string $name One of the table constants.
	 * @return string
	 */
	public static function table( string $name ): string {
		global $wpdb;

		return $wpdb->prefix . $name;
	}

	/**
	 * A network deleting one of its sites drops the tables WordPress knows;
	 * these go with them (the `wpmu_drop_tables` filter).
	 *
	 * @param mixed $tables  Tables about to be dropped.
	 * @param mixed $site_id The site being deleted.
	 * @return mixed
	 */
	public static function withSite( $tables, $site_id = 0 ) {
		global $wpdb;

		if ( ! is_array( $tables ) || (int) $site_id < 1 ) {
			return $tables;
		}

		$prefix = $wpdb->get_blog_prefix( (int) $site_id );

		foreach ( self::names() as $name ) {
			$tables[] = $prefix . $name;
		}

		return array_values( array_unique( $tables ) );
	}

	/**
	 * Create or update the current site's tables.
	 *
	 * @return void
	 */
	public static function install(): void {
		global $wpdb;

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		dbDelta( self::sql( $wpdb->get_charset_collate() ) );

		// Autoloaded: maybeUpgrade() reads it on every request.
		update_option( self::VERSION_OPTION, self::VERSION, true );
	}

	/**
	 * Bring the current site's tables up to the code's schema version.
	 *
	 * @return void
	 */
	public static function maybeUpgrade(): void {
		if ( (int) get_option( self::VERSION_OPTION, 0 ) < self::VERSION ) {
			self::install();
		}
	}

	/**
	 * Which tables exist on the current site — asked of the database each
	 * time, so two calls can differ.
	 *
	 * @phpstan-impure
	 *
	 * @return array<string, bool> Table name (unprefixed) => exists.
	 */
	public static function status(): array {
		global $wpdb;

		$status = array();

		foreach ( self::names() as $name ) {
			$table = self::table( $name );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- A live look at the schema for the System card; a cached answer would defeat it.
			$status[ $name ] = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) ) === $table;
		}

		return $status;
	}

	/**
	 * The schema, in the shape dbDelta() parses: one column per line, two
	 * spaces after PRIMARY KEY, KEY (never INDEX), no backticks.
	 *
	 * @param string $collate The site's charset/collation clause.
	 * @return array<int, string>
	 */
	private static function sql( string $collate ): array {
		$log   = self::table( self::CONSENT_LOG );
		$stats = self::table( self::DAILY_STATS );

		return array(
			// event_uuid makes a retried request harmless (one decision, one
			// row); consent_id is the visitor's own Consent ID, shared by
			// every decision made under the same consent cookie. categories
			// holds every category id a site can have (12 of 32 characters).
			// The second key serves both "the last 90 days" and "how many
			// from this network in the last minutes" without reading a row.
			"CREATE TABLE {$log} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				event_uuid char(36) NOT NULL,
				consent_id char(36) NOT NULL,
				created_at datetime NOT NULL,
				action varchar(20) NOT NULL,
				categories varchar(400) NOT NULL DEFAULT '',
				banner_version int(10) unsigned NOT NULL DEFAULT 0,
				language varchar(24) NOT NULL DEFAULT '',
				ip varchar(45) NOT NULL DEFAULT '',
				browser varchar(60) NOT NULL DEFAULT '',
				url varchar(255) NOT NULL DEFAULT '',
				PRIMARY KEY  (id),
				UNIQUE KEY event_uuid (event_uuid),
				KEY consent_id (consent_id),
				KEY created_ip (created_at,ip)
			) {$collate};",
			"CREATE TABLE {$stats} (
				stat_day date NOT NULL,
				metric varchar(40) NOT NULL,
				hits bigint(20) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY  (stat_day,metric)
			) {$collate};",
		);
	}
}