<?php
/**
 * Core database tables.
 *
 * @package ForensixDigital
 */

declare(strict_types=1);

namespace ForensixDigital\Core;

/**
 * Creates and upgrades the core tables (jobs, activity). Apps manage their own tables.
 */
final class Schema {

	public const VERSION        = '3';
	public const VERSION_OPTION = 'forensix_db_version';

	/**
	 * Core table names without the WordPress prefix.
	 *
	 * @return string[]
	 */
	public static function tables(): array {
		return array( 'forensix_jobs', 'forensix_activity', 'forensix_scans', 'forensix_findings', 'forensix_occurrences', 'forensix_scan_urls', 'forensix_scan_targets' );
	}

	/**
	 * Full table name including the site prefix.
	 *
	 * @param string $name Table name without prefix, e.g. "forensix_jobs".
	 */
	public static function table( string $name ): string {
		global $wpdb;
		return $wpdb->prefix . $name;
	}

	/**
	 * Runs the installer when the stored schema version differs. Cheap when up to date.
	 */
	public static function maybe_upgrade(): void {
		if ( self::VERSION !== get_option( self::VERSION_OPTION ) ) {
			self::install();
		}
	}

	/**
	 * Creates or updates the core tables. Idempotent.
	 */
	public static function install(): void {
		global $wpdb;

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

		$charset = $wpdb->get_charset_collate();
		$jobs    = self::table( 'forensix_jobs' );
		$log     = self::table( 'forensix_activity' );

		dbDelta(
			"CREATE TABLE {$jobs} (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
type varchar(100) NOT NULL,
app varchar(64) NOT NULL,
status varchar(20) NOT NULL DEFAULT 'queued',
args longtext NULL,
state longtext NULL,
progress int(10) unsigned NOT NULL DEFAULT 0,
total int(10) unsigned NOT NULL DEFAULT 0,
attempts smallint(5) unsigned NOT NULL DEFAULT 0,
error text NULL,
lock_until datetime NULL,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (id),
KEY app_status (app,status),
KEY status (status)
) {$charset};"
		);

		dbDelta(
			"CREATE TABLE {$log} (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
user_id bigint(20) unsigned NOT NULL DEFAULT 0,
app varchar(64) NOT NULL,
action varchar(100) NOT NULL,
object_type varchar(50) NOT NULL DEFAULT '',
object_id varchar(191) NOT NULL DEFAULT '',
summary text NOT NULL,
before_data longtext NULL,
after_data longtext NULL,
after_hash char(64) NOT NULL DEFAULT '',
status varchar(20) NOT NULL DEFAULT 'done',
undo_of bigint(20) unsigned NULL,
created_at datetime NOT NULL,
PRIMARY KEY  (id),
KEY app (app),
KEY created_at (created_at),
KEY object (object_type,object_id)
) {$charset};"
		);

		$scans       = self::table( 'forensix_scans' );
		$findings    = self::table( 'forensix_findings' );
		$occurrences = self::table( 'forensix_occurrences' );

		dbDelta(
			"CREATE TABLE {$scans} (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
app varchar(64) NOT NULL,
status varchar(20) NOT NULL DEFAULT 'running',
job_id bigint(20) unsigned NULL,
stats longtext NULL,
started_at datetime NOT NULL,
finished_at datetime NULL,
PRIMARY KEY  (id),
KEY app_status (app,status)
) {$charset};"
		);

		dbDelta(
			"CREATE TABLE {$findings} (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
app varchar(64) NOT NULL,
check_id varchar(64) NOT NULL,
fingerprint char(40) NOT NULL,
severity varchar(10) NOT NULL,
type varchar(20) NOT NULL,
status varchar(20) NOT NULL DEFAULT 'open',
title text NOT NULL,
target text NULL,
details longtext NULL,
source_type varchar(32) NOT NULL DEFAULT 'unknown',
source_ref varchar(191) NOT NULL DEFAULT '',
source_label text NULL,
affected int(10) unsigned NOT NULL DEFAULT 0,
score int(10) unsigned NOT NULL DEFAULT 0,
note text NULL,
first_scan bigint(20) unsigned NOT NULL,
last_scan bigint(20) unsigned NOT NULL,
first_seen datetime NOT NULL,
last_seen datetime NOT NULL,
resolved_at datetime NULL,
PRIMARY KEY  (id),
UNIQUE KEY app_fingerprint (app,fingerprint),
KEY app_status_score (app,status,score),
KEY last_scan (last_scan)
) {$charset};"
		);

		dbDelta(
			"CREATE TABLE {$occurrences} (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
finding_id bigint(20) unsigned NOT NULL,
scan_id bigint(20) unsigned NOT NULL,
url text NOT NULL,
url_hash char(40) NOT NULL,
context text NULL,
importance tinyint(3) unsigned NOT NULL DEFAULT 1,
PRIMARY KEY  (id),
UNIQUE KEY finding_url (finding_id,url_hash),
KEY scan_id (scan_id)
) {$charset};"
		);

		$urls    = self::table( 'forensix_scan_urls' );
		$targets = self::table( 'forensix_scan_targets' );

		dbDelta(
			"CREATE TABLE {$urls} (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
scan_id bigint(20) unsigned NOT NULL,
url text NOT NULL,
url_hash char(40) NOT NULL,
origin varchar(64) NOT NULL DEFAULT '',
importance tinyint(3) unsigned NOT NULL DEFAULT 1,
status varchar(20) NOT NULL DEFAULT 'pending',
reason varchar(64) NOT NULL DEFAULT '',
http_status smallint(5) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (id),
UNIQUE KEY scan_url (scan_id,url_hash),
KEY scan_status (scan_id,status,id)
) {$charset};"
		);

		dbDelta(
			"CREATE TABLE {$targets} (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
scan_id bigint(20) unsigned NOT NULL,
url text NOT NULL,
url_hash char(40) NOT NULL,
verdict varchar(20) NOT NULL,
http_status smallint(5) unsigned NOT NULL DEFAULT 0,
final_url text NULL,
hops tinyint(3) unsigned NOT NULL DEFAULT 0,
error varchar(191) NOT NULL DEFAULT '',
PRIMARY KEY  (id),
UNIQUE KEY scan_url (scan_id,url_hash)
) {$charset};"
		);

		update_option( self::VERSION_OPTION, self::VERSION, true );
	}

	/**
	 * Drops the core tables. Only called from uninstall.
	 */
	public static function drop(): void {
		global $wpdb;
		foreach ( self::tables() as $name ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Uninstall removes our own tables.
			$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', self::table( $name ) ) );
		}
		delete_option( self::VERSION_OPTION );
	}
}