<?php
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Handles the custom findings table and the fix-audit log table.
 *
 * Every query below that references a table name uses $wpdb->prepare()'s
 * %i identifier placeholder (available since WP 6.2 — see "Requires at
 * least" in the plugin header/readme) instead of interpolating the table
 * name directly into the SQL string. The table names themselves are never
 * user input (they're built from internal constants), but %i is the
 * correct, guideline-compliant way to say that rather than leaving a raw
 * interpolated identifier for a linter (or a future contributor) to worry
 * about. Custom-table SELECT/UPDATE/DELETE queries here are necessarily
 * direct $wpdb calls — WordPress core has no higher-level API for a
 * plugin's own tables — which is expected and standard for this pattern.
 */
class CGARD_Db {

	/**
	 * Create (or upgrade) both custom tables using dbDelta.
	 */
	public static function create_tables() {
		global $wpdb;

		$findings_table  = $wpdb->prefix . CGARD_TABLE_FINDINGS;
		$fix_log_table   = $wpdb->prefix . CGARD_TABLE_FIX_LOG;
		$charset_collate = $wpdb->get_charset_collate();

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

		// dbDelta() requires a literal CREATE TABLE statement — %i isn't
		// applicable to DDL passed to dbDelta(), and the table names here
		// are built from internal constants, not user input.
		// check_key identifies which check produced a finding, so re-running
		// scans can update existing rows instead of duplicating them.
		// fix_type/fix_meta describe an available one-click fix (if any) —
		// set by the check itself, so CGARD_Remediator never has to guess what
		// a finding means from its check_key.
		$sql = "CREATE TABLE {$findings_table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			check_key VARCHAR(191) NOT NULL,
			severity VARCHAR(20) NOT NULL,
			title VARCHAR(255) NOT NULL,
			description TEXT NOT NULL,
			fix_steps TEXT NOT NULL,
			fix_type VARCHAR(30) NOT NULL DEFAULT '',
			fix_meta TEXT NOT NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'open',
			first_seen DATETIME NOT NULL,
			last_seen DATETIME NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY check_key (check_key),
			KEY severity (severity),
			KEY status (status)
		) {$charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- dbDelta() requires a literal string; table name is an internal constant, not user input.

		dbDelta( $sql );

		// Audit trail for every auto-fix that's actually applied. before_meta
		// is what makes a chmod fix revertible; delete/update fixes are
		// logged the same way for transparency even though those two aren't
		// revertible from here.
		$sql = "CREATE TABLE {$fix_log_table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			check_key VARCHAR(191) NOT NULL,
			fix_type VARCHAR(30) NOT NULL,
			before_meta TEXT NOT NULL,
			after_meta TEXT NOT NULL,
			reverted TINYINT(1) NOT NULL DEFAULT 0,
			applied_by BIGINT UNSIGNED NOT NULL,
			applied_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY check_key (check_key)
		) {$charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- dbDelta() requires a literal string; table name is an internal constant, not user input.

		dbDelta( $sql );
	}

	/**
	 * Upsert a batch of findings from a scan run. Any existing "open" finding
	 * whose check_key was NOT seen in this run is marked resolved.
	 *
	 * @param array $findings Array of finding arrays (see CGARD_Scanner::run()).
	 */
	public static function sync_findings( array $findings ) {
		global $wpdb;

		$table_name = $wpdb->prefix . CGARD_TABLE_FINDINGS;
		$now        = current_time( 'mysql' );
		$seen_keys  = array();

		foreach ( $findings as $finding ) {
			$seen_keys[] = $finding['check_key'];

			$fix_type = isset( $finding['fix_type'] ) ? $finding['fix_type'] : '';
			$fix_meta = isset( $finding['fix_meta'] ) ? wp_json_encode( $finding['fix_meta'] ) : '';

			$existing_id = $wpdb->get_var(
				$wpdb->prepare(
					'SELECT id FROM %i WHERE check_key = %s',
					$table_name,
					$finding['check_key']
				)
			);

			if ( $existing_id ) {
				$wpdb->update(
					$table_name,
					array(
						'severity'    => $finding['severity'],
						'title'       => $finding['title'],
						'description' => $finding['description'],
						'fix_steps'   => $finding['fix_steps'],
						'fix_type'    => $fix_type,
						'fix_meta'    => $fix_meta,
						'status'      => 'open',
						'last_seen'   => $now,
					),
					array( 'id' => $existing_id )
				);
			} else {
				$wpdb->insert(
					$table_name,
					array(
						'check_key'   => $finding['check_key'],
						'severity'    => $finding['severity'],
						'title'       => $finding['title'],
						'description' => $finding['description'],
						'fix_steps'   => $finding['fix_steps'],
						'fix_type'    => $fix_type,
						'fix_meta'    => $fix_meta,
						'status'      => 'open',
						'first_seen'  => $now,
						'last_seen'   => $now,
					)
				);
			}
		}

		// Anything previously open that wasn't produced by this scan run is fixed.
		if ( ! empty( $seen_keys ) ) {
			$placeholders = implode( ',', array_fill( 0, count( $seen_keys ), '%s' ) );
			$query        = "UPDATE %i SET status = 'resolved' WHERE status = 'open' AND check_key NOT IN ({$placeholders})"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- {$placeholders} is a run of literal '%s' placeholders, not a value; the real values are bound via prepare() below.
			$wpdb->query( $wpdb->prepare( $query, array_merge( array( $table_name ), $seen_keys ) ) );
		} else {
			$wpdb->query( $wpdb->prepare( "UPDATE %i SET status = 'resolved' WHERE status = 'open'", $table_name ) );
		}

		update_option( 'cgard_last_scan_time', $now );
	}

	/**
	 * Fetch a single finding by its check_key (used by the "Explain with AI"
	 * and "Auto-Fix" AJAX handlers, which only have the key from the report
	 * page's markup).
	 *
	 * @param string $check_key
	 * @return array|null
	 */
	public static function get_finding_by_key( $check_key ) {
		global $wpdb;

		$table_name = $wpdb->prefix . CGARD_TABLE_FINDINGS;

		$row = $wpdb->get_row(
			$wpdb->prepare( 'SELECT * FROM %i WHERE check_key = %s', $table_name, $check_key ),
			ARRAY_A
		);

		return $row ? $row : null;
	}

	/**
	 * Get findings, optionally filtered by status.
	 *
	 * @param string $status 'open', 'resolved', or 'all'.
	 * @return array
	 */
	public static function get_findings( $status = 'open' ) {
		global $wpdb;

		$table_name = $wpdb->prefix . CGARD_TABLE_FINDINGS;

		if ( 'all' === $status ) {
			$sql = $wpdb->prepare(
				"SELECT * FROM %i ORDER BY FIELD(severity,'critical','high','medium','low'), last_seen DESC",
				$table_name
			);
			return $wpdb->get_results( $sql, ARRAY_A );
		}

		$sql = $wpdb->prepare(
			"SELECT * FROM %i WHERE status = %s ORDER BY FIELD(severity,'critical','high','medium','low'), last_seen DESC",
			$table_name,
			$status
		);

		return $wpdb->get_results( $sql, ARRAY_A );
	}

	/**
	 * Record an applied (or reverted) fix in the audit log.
	 *
	 * @param string $check_key
	 * @param string $fix_type
	 * @param array  $before_meta State captured before the fix, for revert.
	 * @param array  $after_meta  State after the fix was applied.
	 * @return int Insert ID.
	 */
	public static function log_fix( $check_key, $fix_type, array $before_meta, array $after_meta ) {
		global $wpdb;

		$wpdb->insert(
			$wpdb->prefix . CGARD_TABLE_FIX_LOG,
			array(
				'check_key'   => $check_key,
				'fix_type'    => $fix_type,
				'before_meta' => wp_json_encode( $before_meta ),
				'after_meta'  => wp_json_encode( $after_meta ),
				'reverted'    => 0,
				'applied_by'  => get_current_user_id(),
				'applied_at'  => current_time( 'mysql' ),
			)
		);

		return (int) $wpdb->insert_id;
	}

	/**
	 * @param int $log_id
	 * @return array|null
	 */
	public static function get_fix_log_entry( $log_id ) {
		global $wpdb;

		$row = $wpdb->get_row(
			$wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $wpdb->prefix . CGARD_TABLE_FIX_LOG, $log_id ),
			ARRAY_A
		);

		return $row ? $row : null;
	}

	public static function mark_fix_reverted( $log_id ) {
		global $wpdb;

		$wpdb->update(
			$wpdb->prefix . CGARD_TABLE_FIX_LOG,
			array( 'reverted' => 1 ),
			array( 'id' => $log_id )
		);
	}

	/**
	 * Most recent chmod fixes that haven't been reverted yet — the only fix
	 * type this version can safely undo — for the report page's "Recent
	 * fixes" list.
	 *
	 * @param int $limit
	 * @return array
	 */
	public static function get_revertible_fixes( $limit = 10 ) {
		global $wpdb;

		$table = $wpdb->prefix . CGARD_TABLE_FIX_LOG;

		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM %i WHERE fix_type = 'chmod' AND reverted = 0 ORDER BY applied_at DESC LIMIT %d",
				$table,
				$limit
			),
			ARRAY_A
		);
	}
}