<?php
/**
 * Finding persistence.
 *
 * @package LocalGuardScanner
 */

namespace LocalGuardScanner\Infrastructure\Database;

use LocalGuardScanner\Domain\Scan\InspectionScope;
use RuntimeException;

/**
 * Stores deduplicated findings.
 */
final class FindingRepository {
	const OBJECT_TYPES = array( 'file', 'plugin', 'db_user', 'db_option', 'db_content' );

	/** @var \wpdb */
	private $wpdb;
	/** @var string */
	private $table;

	/** @param \wpdb $wpdb WordPress database object. */
	public function __construct( $wpdb ) {
		$this->wpdb  = $wpdb;
		$this->table = Schema::table_names( $wpdb )['findings'];
	}

	/**
	 * @param array<string,mixed> $row Finding row.
	 * @return bool
	 */
	public function save( array $row ) {
		$query = $this->wpdb->prepare(
			'INSERT INTO %i
			(scan_id, site_id, fingerprint, detector_id, severity, score, confidence, status, object_type, locator, evidence_json, first_seen_at, last_seen_at, resolved_at)
			VALUES (%d, %d, %s, %s, %s, %d, %d, %s, %s, %s, %s, %s, %s, NULL)
			ON DUPLICATE KEY UPDATE scan_id = VALUES(scan_id), detector_id = VALUES(detector_id), severity = VALUES(severity), score = VALUES(score), confidence = VALUES(confidence), notified_at = IF(status = \'resolved\', NULL, notified_at), resolved_at = NULL, status = VALUES(status), object_type = VALUES(object_type), locator = VALUES(locator), evidence_json = VALUES(evidence_json), last_seen_at = VALUES(last_seen_at)',
			$this->table,
			$row['scan_id'],
			$row['site_id'],
			$row['fingerprint'],
			$row['detector_id'],
			$row['severity'],
			$row['score'],
			$row['confidence'],
			$row['status'],
			$row['object_type'],
			$row['locator'],
			$row['evidence_json'],
			$row['first_seen_at'],
			$row['last_seen_at']
		);
		return false !== $this->wpdb->query( $query ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * @param int    $site_id     Site ID.
	 * @param string $fingerprint Finding fingerprint.
	 * @return string|null
	 */
	public function status_for( $site_id, $fingerprint ) {
		$query  = $this->wpdb->prepare(
			'SELECT status FROM %i WHERE site_id = %d AND fingerprint = %s LIMIT 1',
			$this->table,
			(int) $site_id,
			$fingerprint
		);
		$status = $this->wpdb->get_var( $query ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$this->throw_on_database_error();
		return is_string( $status ) ? $status : null;
	}

	/**
	 * Resolve findings that a sufficient scan no longer observed.
	 *
	 * Findings located inside a directory the administrator excluded were never
	 * re-inspected, so they must keep their previous status instead of being
	 * silently declared resolved.
	 *
	 * @param int      $scan_id           Scan ID.
	 * @param int      $site_id           Site ID.
	 * @param string[] $object_types      Closed object types covered by this scan.
	 * @param string[] $excluded_prefixes Relative directory prefixes that were not inspected.
	 * @param string   $resolved_at       UTC resolution time.
	 * @return int
	 */
	public function mark_unseen_resolved( $scan_id, $site_id, array $object_types, array $excluded_prefixes, $resolved_at ) {
		$allowed_types = array( 'file', 'plugin', 'db_user', 'db_option', 'db_content' );
		$object_types  = array_values( array_unique( array_intersect( $allowed_types, array_filter( $object_types, 'is_string' ) ) ) );
		if ( empty( $object_types ) ) {
			return 0;
		}
		$type_placeholders = implode( ', ', array_fill( 0, count( $object_types ), '%s' ) );
		$sql               = "UPDATE %i SET status = 'resolved', resolved_at = %s WHERE site_id = %d AND scan_id <> %d AND status IN ('new','persistent','ignored') AND object_type IN ($type_placeholders)";
		$args              = array_merge( array( $this->table, (string) $resolved_at, (int) $site_id, (int) $scan_id ), $object_types );
		$skip              = array();
		$scope             = new InspectionScope( $excluded_prefixes );
		if ( in_array( 'file', $object_types, true ) ) {
			foreach ( $scope->exclusions() as $prefix ) {
				$skip[] = '( locator = %s OR locator LIKE %s )';
				$args[] = $prefix;
				$args[] = $this->escape_like( $prefix ) . '/%';
			}
		}
		if ( ! empty( $skip ) ) {
			$sql .= " AND NOT ( object_type = 'file' AND ( " . implode( ' OR ', $skip ) . ' ) )';
		}
		$query  = $this->wpdb->prepare( $sql, ...$args ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$result = $this->wpdb->query( $query ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		if ( false === $result ) {
			throw new RuntimeException( 'Finding resolution state could not be stored.' );
		}
		return (int) $result;
	}

	/**
	 * Return old active file findings for bounded individual reconciliation.
	 *
	 * @param int $scan_id Current scan ID.
	 * @param int $after_id Last candidate ID already checked.
	 * @param int $limit Maximum candidates.
	 * @return array<int,array{id:int,locator:string}>
	 */
	public function stale_file_candidates( $scan_id, $after_id, $limit ) {
		$query = $this->wpdb->prepare(
			"SELECT id, locator FROM %i WHERE id > %d AND scan_id <> %d AND status IN ('new','persistent') AND object_type = 'file' ORDER BY id ASC LIMIT %d",
			$this->table,
			max( 0, (int) $after_id ),
			(int) $scan_id,
			max( 1, min( 100, (int) $limit ) )
		);
		$rows  = $this->wpdb->get_results( $query, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$this->throw_on_database_error();
		$candidates = array();
		foreach ( $rows as $row ) {
			if ( ! isset( $row['id'], $row['locator'] ) || (int) $row['id'] <= 0 || ! is_string( $row['locator'] ) ) {
				continue;
			}
			$candidates[] = array(
				'id'      => (int) $row['id'],
				'locator' => $row['locator'],
			);
		}
		return $candidates;
	}

	/**
	 * Resolve a closed set of confirmed-missing old file findings.
	 *
	 * @param int    $scan_id Current scan ID.
	 * @param int[]  $ids Finding IDs.
	 * @param string $resolved_at UTC resolution time.
	 * @return int
	 */
	public function resolve_missing_file_ids( $scan_id, array $ids, $resolved_at ) {
		$ids = array_values(
			array_unique(
				array_filter(
					array_map( 'intval', $ids ),
					static function ( $id ) {
						return $id > 0;
					}
				)
			)
		);
		if ( empty( $ids ) ) {
			return 0;
		}
		$ids          = array_slice( $ids, 0, 100 );
		$placeholders = implode( ', ', array_fill( 0, count( $ids ), '%d' ) );
		$sql          = "UPDATE %i SET status = 'resolved', resolved_at = %s WHERE id IN ($placeholders) AND scan_id <> %d AND status IN ('new','persistent') AND object_type = 'file'";
		$args         = array_merge( array( $this->table, (string) $resolved_at ), $ids, array( (int) $scan_id ) );
		$query        = $this->wpdb->prepare( $sql, ...$args ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$result       = $this->wpdb->query( $query ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		if ( false === $result ) {
			throw new RuntimeException( 'Missing file reconciliation could not be stored.' );
		}
		return (int) $result;
	}

	/**
	 * @param string $value Literal value.
	 * @return string
	 */
	private function escape_like( $value ) {
		return (string) $this->wpdb->esc_like( $value );
	}

	/**
	 * @param int $scan_id Scan ID.
	 * @return array<int,array<string,mixed>>
	 */
	public function new_high_risk( $scan_id ) {
		$query = $this->wpdb->prepare(
			"SELECT id, fingerprint, severity, score, object_type FROM %i WHERE scan_id = %d AND status = 'new' AND severity IN ('high','critical') AND notified_at IS NULL",
			$this->table,
			(int) $scan_id
		);
		$rows  = $this->wpdb->get_results( $query, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$this->throw_on_database_error();
		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * @param int[]  $ids Finding IDs.
	 * @param string $now UTC MySQL time.
	 * @return void
	 */
	public function mark_notified( array $ids, $now ) {
		foreach ( $ids as $id ) {
			if ( false === $this->wpdb->update( $this->table, array( 'notified_at' => $now ), array( 'id' => (int) $id ), array( '%s' ), array( '%d' ) ) ) {
				throw new RuntimeException( 'Finding notification state could not be stored.' );
			}
		}
	}

	/**
	 * @param int      $limit        Maximum rows.
	 * @param int      $offset       Row offset.
	 * @param string[] $object_types Optional closed object types.
	 * @return array<int,array<string,mixed>>
	 */
	public function latest_findings( $limit = 100, $offset = 0, array $object_types = array() ) {
		$table  = $this->wpdb->prepare( '%i', $this->table );
		$query  = "SELECT id, scan_id, site_id, fingerprint, detector_id, severity, score, confidence, status, object_type, locator, evidence_json, last_seen_at, resolved_at FROM $table WHERE status IN ('new','persistent')";
		$query .= $this->object_type_clause( $object_types );
		$query .= ' ORDER BY score DESC, id DESC LIMIT ' . max( 1, min( 500, (int) $limit ) ) . ' OFFSET ' . max( 0, (int) $offset );
		$rows   = $this->wpdb->get_results( $query, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$this->throw_on_database_error();
		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * @param int      $limit        Maximum rows.
	 * @param int      $offset       Row offset.
	 * @param string[] $object_types Optional closed object types.
	 * @return array<int,array<string,mixed>>
	 */
	public function resolved_findings( $limit = 100, $offset = 0, array $object_types = array() ): array {
		$table  = $this->wpdb->prepare( '%i', $this->table );
		$query  = "SELECT id, scan_id, site_id, fingerprint, detector_id, severity, score, confidence, status, object_type, locator, evidence_json, last_seen_at, resolved_at FROM $table WHERE status = 'resolved'";
		$query .= $this->object_type_clause( $object_types );
		$query .= ' ORDER BY resolved_at DESC, id DESC LIMIT ' . max( 1, min( 500, (int) $limit ) ) . ' OFFSET ' . max( 0, (int) $offset );
		$rows   = $this->wpdb->get_results( $query, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$this->throw_on_database_error();
		return is_array( $rows ) ? $rows : array();
	}

	/** @param string[] $object_types Optional closed object types. @return int */
	public function active_count( array $object_types = array() ): int {
		$table  = $this->wpdb->prepare( '%i', $this->table );
		$query  = "SELECT COUNT(*) FROM $table WHERE status IN ('new','persistent')";
		$query .= $this->object_type_clause( $object_types );
		$count  = $this->wpdb->get_var( $query ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$this->throw_on_database_error();
		return max( 0, (int) $count );
	}

	/** @param string[] $object_types Optional closed object types. @return int */
	public function resolved_count( array $object_types = array() ): int {
		$table  = $this->wpdb->prepare( '%i', $this->table );
		$query  = "SELECT COUNT(*) FROM $table WHERE status = 'resolved'";
		$query .= $this->object_type_clause( $object_types );
		$count  = $this->wpdb->get_var( $query ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$this->throw_on_database_error();
		return max( 0, (int) $count );
	}

	/**
	 * @param string[] $requested Requested object types; an empty list means all.
	 * @return string
	 */
	private function object_type_clause( array $requested ): string {
		if ( empty( $requested ) ) {
			return '';
		}
		$types = array_values( array_unique( array_intersect( self::OBJECT_TYPES, array_filter( $requested, 'is_string' ) ) ) );
		if ( empty( $types ) ) {
			return ' AND 1 = 0';
		}
		$prepared = array();
		foreach ( $types as $type ) {
			$prepared[] = $this->wpdb->prepare( '%s', $type );
		}
		return ' AND object_type IN (' . implode( ', ', $prepared ) . ')';
	}

	/** @return array<string,int> */
	public function active_severity_counts() {
		$query = $this->wpdb->prepare( "SELECT severity, COUNT(*) AS total FROM %i WHERE status IN ('new','persistent') GROUP BY severity", $this->table );
		$rows  = $this->wpdb->get_results( $query, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$this->throw_on_database_error();
		$counts = array(
			'critical' => 0,
			'high'     => 0,
			'medium'   => 0,
			'low'      => 0,
			'info'     => 0,
		);
		foreach ( $rows as $row ) {
			if ( isset( $row['severity'], $row['total'] ) && is_string( $row['severity'] ) && isset( $counts[ $row['severity'] ] ) ) {
				$counts[ $row['severity'] ] = max( 0, (int) $row['total'] );
			}
		}
		return $counts;
	}

	/**
	 * Provisional actionable and informational totals for one scan.
	 *
	 * @param int $scan_id Scan ID.
	 * @return array{review_required:int,informational:int}
	 */
	public function progress_counts( $scan_id ) {
		$query = $this->wpdb->prepare(
			"SELECT severity, COUNT(*) AS total FROM %i WHERE scan_id = %d AND status IN ('new','persistent') GROUP BY severity",
			$this->table,
			(int) $scan_id
		);
		$rows  = $this->wpdb->get_results( $query, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$this->throw_on_database_error();
		$counts = array(
			'review_required' => 0,
			'informational'   => 0,
		);
		foreach ( $rows as $row ) {
			if ( ! isset( $row['severity'], $row['total'] ) || ! is_string( $row['severity'] ) ) {
				continue;
			}
			if ( in_array( $row['severity'], array( 'critical', 'high', 'medium' ), true ) ) {
				$counts['review_required'] += max( 0, (int) $row['total'] );
			} elseif ( in_array( $row['severity'], array( 'low', 'info' ), true ) ) {
				$counts['informational'] += max( 0, (int) $row['total'] );
			}
		}
		return $counts;
	}

	/**
	 * Delete resolved history older than the retention boundary.
	 *
	 * @param string $cutoff UTC MySQL timestamp.
	 * @param int    $limit  Maximum rows.
	 * @return int
	 */
	public function delete_old_resolved( $cutoff, $limit ) {
		$query  = $this->wpdb->prepare(
			"DELETE FROM %i WHERE status = 'resolved' AND last_seen_at < %s ORDER BY id ASC LIMIT %d",
			$this->table,
			$cutoff,
			max( 1, (int) $limit )
		);
		$result = $this->wpdb->query( $query ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		return false === $result ? 0 : (int) $result;
	}

	/** @return void */
	private function throw_on_database_error() {
		if ( '' !== $this->wpdb->last_error ) {
			throw new RuntimeException( 'Security findings could not be read safely.' );
		}
	}
}
