<?php
/**
 * Results storage: two custom tables (not wp_options, which autoloads).
 *
 *   {prefix}miintg_runs        — one row per scan run (parent)
 *   {prefix}miintg_run_checks  — one row per check within a run (child)
 *
 * Each background check writes only its own detail row (unique on
 * run_id + check_name), so concurrent WP-Cron events never race on a
 * shared read-modify-write. Retention keeps the last N runs (see
 * miintg_runs_retention()); pruning deletes detail rows first,
 * then the parent row.
 *
 * @package MIINTG
 */

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

define( 'MIINTG_DB_VERSION', '1.0.0' );

/**
 * How many completed runs to keep. Oldest beyond this are pruned after
 * every run that finishes. Not filterable from the admin UI (yet) — a
 * fixed, simple default.
 *
 * @return int
 */
function miintg_runs_retention() {
	return (int) apply_filters( 'miintg_runs_retention', 10 );
}

/**
 * @return string
 */
function miintg_runs_table() {
	global $wpdb;
	return $wpdb->prefix . 'miintg_runs';
}

/**
 * @return string
 */
function miintg_run_checks_table() {
	global $wpdb;
	return $wpdb->prefix . 'miintg_run_checks';
}

/**
 * Creates or upgrades both tables via dbDelta. Safe to call repeatedly
 * (activation, and a version-mismatch check on admin_init).
 */
function miintg_db_install() {
	global $wpdb;

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

	$charset_collate = $wpdb->get_charset_collate();
	$runs_table      = miintg_runs_table();
	$checks_table    = miintg_run_checks_table();

	$sql = "CREATE TABLE {$runs_table} (
		id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
		created_at DATETIME NOT NULL,
		updated_at DATETIME NOT NULL,
		status VARCHAR(20) NOT NULL DEFAULT 'queued',
		checks_requested TEXT NOT NULL,
		trigger_source VARCHAR(20) NOT NULL DEFAULT 'manual',
		triggered_by BIGINT UNSIGNED NULL,
		duration_seconds FLOAT NULL,
		severity_critical INT UNSIGNED NOT NULL DEFAULT 0,
		severity_review INT UNSIGNED NOT NULL DEFAULT 0,
		PRIMARY KEY  (id),
		KEY status (status),
		KEY created_at (created_at)
	) {$charset_collate};

	CREATE TABLE {$checks_table} (
		id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
		run_id BIGINT UNSIGNED NOT NULL,
		check_name VARCHAR(20) NOT NULL,
		status VARCHAR(20) NOT NULL DEFAULT 'queued',
		severity_critical INT UNSIGNED NOT NULL DEFAULT 0,
		severity_review INT UNSIGNED NOT NULL DEFAULT 0,
		result LONGTEXT NULL,
		completed_at DATETIME NULL,
		PRIMARY KEY  (id),
		UNIQUE KEY run_check (run_id, check_name),
		KEY run_id (run_id)
	) {$charset_collate};";

	dbDelta( $sql );

	update_option( 'miintg_db_version', MIINTG_DB_VERSION, false );
}

/**
 * Called on admin_init: creates/upgrades tables once per version bump,
 * so an upload-and-reactivate isn't the only way to pick up a schema
 * change.
 */
function miintg_db_maybe_upgrade() {
	if ( get_option( 'miintg_db_version' ) !== MIINTG_DB_VERSION ) {
		miintg_db_install();
	}
}
add_action( 'admin_init', 'miintg_db_maybe_upgrade' );

/**
 * Creates a new run row plus one queued detail row per requested check.
 *
 * @param array  $checks  Check names.
 * @param string $trigger 'manual' | 'cron' | 'wp_cli' | 'rest'.
 * @param int|null $user_id Triggering user, if any.
 * @return int Run id.
 */
function miintg_db_create_run( $checks, $trigger = 'manual', $user_id = null ) {
	global $wpdb;

	$now = current_time( 'mysql', true );

	// Custom scan-history tables have no WordPress CRUD API; all values use
	// $wpdb formats or prepared statements.
	$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		miintg_runs_table(),
		array(
			'created_at'       => $now,
			'updated_at'       => $now,
			'status'           => 'queued',
			'checks_requested' => implode( ',', $checks ),
			'trigger_source'   => $trigger,
			'triggered_by'     => $user_id ? (int) $user_id : null,
		),
		array( '%s', '%s', '%s', '%s', '%s', '%d' )
	);

	$run_id = (int) $wpdb->insert_id;

	foreach ( $checks as $check ) {
		$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			miintg_run_checks_table(),
			array(
				'run_id'     => $run_id,
				'check_name' => $check,
				'status'     => 'queued',
			),
			array( '%d', '%s', '%s' )
		);
	}

	return $run_id;
}

/**
 * Marks a run's status (used to flip queued -> running on first pickup).
 *
 * @param int    $run_id Run id.
 * @param string $status New status.
 */
function miintg_db_set_run_status( $run_id, $status ) {
	global $wpdb;

	$wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		miintg_runs_table(),
		array(
			'status'     => $status,
			'updated_at' => current_time( 'mysql', true ),
		),
		array( 'id' => (int) $run_id ),
		array( '%s', '%s' ),
		array( '%d' )
	);
}

/**
 * Records one check's completed (or errored) result against its
 * dedicated detail row, then re-evaluates the parent run: if every
 * requested check now has a terminal status, the run is finalized
 * (status, duration, summed severities) and retention is enforced.
 * Idempotent — safe if two checks finish close together and both
 * attempt finalization.
 *
 * @param int    $run_id Run id.
 * @param string $check  Check name.
 * @param array|null $result Enveloped tool result, or null on hard failure.
 * @param string $status 'complete' | 'error'.
 */
function miintg_db_record_check( $run_id, $check, $result, $status = 'complete' ) {
	global $wpdb;

	$severity = array(
		'critical' => 0,
		'review'   => 0,
	);
	if ( is_array( $result ) ) {
		$severity = miintg_check_severity( $check, $result );
	}

	$wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		miintg_run_checks_table(),
		array(
			'status'            => $status,
			'severity_critical' => $severity['critical'],
			'severity_review'   => $severity['review'],
			'result'            => null === $result ? null : wp_json_encode( $result ),
			'completed_at'      => current_time( 'mysql', true ),
		),
		array(
			'run_id'     => (int) $run_id,
			'check_name' => $check,
		),
		array( '%s', '%d', '%d', '%s', '%s' ),
		array( '%d', '%s' )
	);

	miintg_db_set_run_status( $run_id, 'running' );
	miintg_db_maybe_finalize_run( $run_id );
}

/**
 * If every detail row for a run is terminal (complete or error),
 * finalizes the parent row and prunes retention. No-op otherwise.
 *
 * @param int $run_id Run id.
 */
function miintg_db_maybe_finalize_run( $run_id ) {
	global $wpdb;

	$run = miintg_db_get_run( $run_id );
	if ( ! $run ) {
		return;
	}

	$checks = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->prepare(
			'SELECT status, severity_critical, severity_review FROM %i WHERE run_id = %d',
			miintg_run_checks_table(),
			$run_id
		),
		ARRAY_A
	);

	if ( empty( $checks ) ) {
		return;
	}

	$pending = 0;
	$critical = 0;
	$review   = 0;
	$any_error = false;

	foreach ( $checks as $row ) {
		if ( ! in_array( $row['status'], array( 'complete', 'error' ), true ) ) {
			++$pending;
			continue;
		}
		if ( 'error' === $row['status'] ) {
			$any_error = true;
		}
		$critical += (int) $row['severity_critical'];
		$review   += (int) $row['severity_review'];
	}

	if ( $pending > 0 ) {
		return;
	}

	$created = strtotime( $run['created_at'] . ' UTC' );
	$duration = $created ? round( microtime( true ) - $created, 2 ) : null;

	$wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		miintg_runs_table(),
		array(
			'status'             => $any_error ? 'error' : 'complete',
			'updated_at'         => current_time( 'mysql', true ),
			'duration_seconds'   => $duration,
			'severity_critical'  => $critical,
			'severity_review'    => $review,
		),
		array( 'id' => (int) $run_id ),
		array( '%s', '%s', '%f', '%d', '%d' ),
		array( '%d' )
	);

	miintg_db_prune_retention();
}

/**
 * Deletes runs beyond the retention count, oldest first, detail rows
 * before parent rows. Only ever removes finalized runs (complete/error)
 * so an in-flight run is never pruned mid-scan.
 */
function miintg_db_prune_retention() {
	global $wpdb;

	$keep = miintg_runs_retention();

	$stale_ids = $wpdb->get_col( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->prepare(
			"SELECT id FROM %i WHERE status IN ('complete','error') ORDER BY created_at DESC LIMIT 1000 OFFSET %d",
			miintg_runs_table(),
			max( 0, $keep )
		)
	);

	if ( empty( $stale_ids ) ) {
		return;
	}

	$table        = esc_sql( miintg_runs_table() );
	$checks_table = esc_sql( miintg_run_checks_table() );
	$ids_sql      = implode( ',', array_map( 'absint', $stale_ids ) );

	$wpdb->query( "DELETE FROM {$checks_table} WHERE run_id IN ({$ids_sql})" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	$wpdb->query( "DELETE FROM {$table} WHERE id IN ({$ids_sql})" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
}

/**
 * @param int $run_id Run id.
 * @return array|null
 */
function miintg_db_get_run( $run_id ) {
	global $wpdb;

	$row = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', miintg_runs_table(), $run_id ),
		ARRAY_A
	);

	return $row ?: null;
}

/**
 * @param int $run_id Run id.
 * @return array Detail rows, decoded.
 */
function miintg_db_get_run_checks( $run_id ) {
	global $wpdb;

	$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->prepare(
			'SELECT check_name, status, severity_critical, severity_review, result, completed_at FROM %i WHERE run_id = %d ORDER BY id ASC',
			miintg_run_checks_table(),
			$run_id
		),
		ARRAY_A
	);

	foreach ( $rows as &$row ) {
		$row['result'] = $row['result'] ? json_decode( $row['result'], true ) : null;
	}

	return $rows;
}

/**
 * A run plus its checks, in one call — what both the admin UI and the
 * REST /runs/{id} route need.
 *
 * @param int $run_id Run id.
 * @return array|null
 */
function miintg_db_get_run_with_checks( $run_id ) {
	$run = miintg_db_get_run( $run_id );
	if ( ! $run ) {
		return null;
	}

	$run['checks'] = miintg_db_get_run_checks( $run_id );
	return $run;
}

/**
 * Deletes every run and check row — a full reset of scan history, used by
 * the Logs tab's "Clear Scan History" action. Unlike prune_retention, this
 * removes in-flight runs too; only called on an explicit admin action.
 */
function miintg_db_clear_all_runs() {
	global $wpdb;

	$wpdb->query( $wpdb->prepare( 'TRUNCATE TABLE %i', miintg_run_checks_table() ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	$wpdb->query( $wpdb->prepare( 'TRUNCATE TABLE %i', miintg_runs_table() ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
}

/**
 * @param int $limit Max runs to return.
 * @return array Run rows (no check detail — use get_run_with_checks for that).
 */
function miintg_db_list_runs( $limit = 10 ) {
	global $wpdb;

	return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->prepare(
			'SELECT * FROM %i ORDER BY created_at DESC LIMIT %d',
			miintg_runs_table(),
			max( 1, (int) $limit )
		),
		ARRAY_A
	);
}