<?php
/**
 * Database schema.
 *
 * @package LocalGuardScanner
 */

namespace LocalGuardScanner\Infrastructure\Database;

/**
 * Produces network-safe table names and dbDelta statements.
 */
final class Schema {
	const VERSION = '5';

	/**
	 * @param \wpdb $wpdb WordPress database object.
	 * @return array<string,string>
	 */
	public static function table_names( $wpdb ) {
		$prefix = $wpdb->base_prefix;
		return array(
			'scans'        => $prefix . 'lgs_scans',
			'inventory'    => $prefix . 'lgs_inventory',
			'findings'     => $prefix . 'lgs_findings',
			'suppressions' => $prefix . 'lgs_suppressions',
		);
	}

	/** @return array<string,string[]> */
	public static function required_columns() {
		return array(
			'scans'        => array( 'id', 'status', 'phase', 'cursor_json', 'coverage_json', 'stats_json', 'progress_json', 'error_count', 'failure_code', 'failure_stage', 'started_at', 'last_checkpoint_at', 'finished_at', 'created_at' ),
			'inventory'    => array( 'id', 'site_id', 'object_type', 'locator_hash', 'locator', 'last_seen_scan' ),
			'findings'     => array( 'id', 'scan_id', 'site_id', 'severity', 'status', 'locator', 'evidence_json', 'resolved_at' ),
			'suppressions' => array( 'id', 'site_id', 'reason', 'author_id', 'created_at' ),
		);
	}

	/**
	 * @param \wpdb $wpdb WordPress database object.
	 * @return string[]
	 */
	public static function statements( $wpdb ) {
		$tables  = self::table_names( $wpdb );
		$collate = $wpdb->get_charset_collate();

		return array(
			"CREATE TABLE {$tables['scans']} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				site_id bigint(20) unsigned NOT NULL DEFAULT 0,
				trigger_type varchar(16) NOT NULL DEFAULT 'manual',
				status varchar(32) NOT NULL DEFAULT 'queued',
				phase varchar(32) NOT NULL DEFAULT 'queued',
				cursor_json longtext NULL,
				coverage_json longtext NULL,
				stats_json longtext NULL,
				progress_json longtext NULL,
				error_count int(10) unsigned NOT NULL DEFAULT 0,
				failure_code varchar(32) NOT NULL DEFAULT '',
				failure_stage varchar(32) NOT NULL DEFAULT '',
				rules_version varchar(32) NOT NULL DEFAULT '',
				started_at datetime NULL,
				last_checkpoint_at datetime NULL,
				finished_at datetime NULL,
				created_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY status_created (status, created_at),
				KEY site_status (site_id, status)
			) $collate;",
			"CREATE TABLE {$tables['inventory']} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				site_id bigint(20) unsigned NOT NULL DEFAULT 0,
				object_type varchar(32) NOT NULL,
				locator_hash char(64) NOT NULL,
				locator text NOT NULL,
				size_bytes bigint(20) unsigned NOT NULL DEFAULT 0,
				modified_at bigint(20) unsigned NULL,
				sha256 char(64) NULL,
				metadata_json longtext NULL,
				baseline_status varchar(24) NOT NULL DEFAULT 'untrusted',
				first_seen_scan bigint(20) unsigned NOT NULL,
				last_seen_scan bigint(20) unsigned NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY site_object_locator (site_id, object_type, locator_hash),
				KEY last_seen_scan (last_seen_scan)
			) $collate;",
			"CREATE TABLE {$tables['findings']} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				scan_id bigint(20) unsigned NOT NULL,
				site_id bigint(20) unsigned NOT NULL DEFAULT 0,
				fingerprint char(64) NOT NULL,
				detector_id varchar(96) NOT NULL,
				severity varchar(16) NOT NULL,
				score tinyint(3) unsigned NOT NULL,
				confidence tinyint(3) unsigned NOT NULL,
				status varchar(16) NOT NULL DEFAULT 'new',
				object_type varchar(32) NOT NULL,
				locator text NOT NULL,
				evidence_json longtext NOT NULL,
				first_seen_at datetime NOT NULL,
				last_seen_at datetime NOT NULL,
				notified_at datetime NULL,
				resolved_at datetime NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY site_fingerprint (site_id, fingerprint),
				KEY scan_severity (scan_id, severity),
				KEY status_severity (status, severity)
			) $collate;",
			"CREATE TABLE {$tables['suppressions']} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				site_id bigint(20) unsigned NOT NULL DEFAULT 0,
				fingerprint char(64) NULL,
				detector_id varchar(96) NULL,
				locator_hash char(64) NULL,
				object_sha256 char(64) NULL,
				scope_json longtext NULL,
				reason text NOT NULL,
				author_id bigint(20) unsigned NOT NULL,
				created_at datetime NOT NULL,
				expires_at datetime NULL,
				PRIMARY KEY  (id),
				KEY site_fingerprint (site_id, fingerprint),
				KEY site_detector (site_id, detector_id),
				KEY site_locator (site_id, locator_hash),
				KEY site_expiry (site_id, expires_at)
			) $collate;",
		);
	}
}
