<?php
/**
 * Scan persistence.
 *
 * @package CertaCart_EU_Product_Readiness
 */

namespace CertaCart\EU_Product_Readiness;

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Scan reports use dedicated tables and must always return current progress.
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifiers come only from Schema; all runtime values are passed through wpdb placeholders.

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

class Scan_Repository {
	public function create( int $user_id, array $scope = array() ): int {
		global $wpdb;
		$inserted = $wpdb->insert(
			Schema::scans_table(),
			array(
				'status'         => 'queued',
				'total_products' => $this->count_products( $scope ),
				'scope_json'     => wp_json_encode( $scope ),
				'created_by'     => $user_id,
				'created_at'     => current_time( 'mysql', true ),
			),
			array( '%s', '%d', '%s', '%d', '%s' )
		);
		return false === $inserted ? 0 : (int) $wpdb->insert_id;
	}

	public function get( int $scan_id ): ?array {
		global $wpdb;
		$table = Schema::scans_table();
		$row   = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $scan_id ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return is_array( $row ) ? $row : null;
	}

	public function active(): ?array {
		global $wpdb;
		$table = Schema::scans_table();
		$row   = $wpdb->get_row( "SELECT * FROM {$table} WHERE status IN ('queued','running') ORDER BY id DESC LIMIT 1", ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return is_array( $row ) ? $row : null;
	}

	public function latest(): ?array {
		global $wpdb;
		$table = Schema::scans_table();
		$row   = $wpdb->get_row( "SELECT * FROM {$table} ORDER BY id DESC LIMIT 1", ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return is_array( $row ) ? $row : null;
	}

	/**
	 * Return recent scans for the History screen.
	 *
	 * @param int $limit Maximum number of scans.
	 * @return array<int,array<string,mixed>>
	 */
	public function recent( int $limit = 20 ): array {
		global $wpdb;
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT * FROM %i ORDER BY id DESC LIMIT %d',
				Schema::scans_table(),
				max( 1, min( 50, $limit ) )
			),
			ARRAY_A
		);
		return is_array( $rows ) ? $rows : array();
	}

	public function mark_running( int $scan_id ): void {
		global $wpdb;
		$wpdb->update(
			Schema::scans_table(),
			array( 'status' => 'running', 'started_at' => current_time( 'mysql', true ) ),
			array( 'id' => $scan_id ),
			array( '%s', '%s' ),
			array( '%d' )
		);
	}

	public function save_result( int $scan_id, int $product_id, array $evaluation ): bool {
		return $this->save_results( $scan_id, array( $product_id => $evaluation ) );
	}

	/**
	 * Persist one scan batch in a single database query.
	 *
	 * @param int   $scan_id Scan ID.
	 * @param array $evaluations Product-ID keyed evaluations.
	 */
	public function save_results( int $scan_id, array $evaluations ): bool {
		global $wpdb;
		if ( empty( $evaluations ) ) {
			return true;
		}

		$rows = array();
		$values = array( Schema::results_table() );
		$created_at = current_time( 'mysql', true );
		foreach ( $evaluations as $product_id => $evaluation ) {
			$rows[] = '(%d,%d,%f,%d,%d,%d,%d,%s,%s)';
			array_push(
				$values,
				$scan_id,
				absint( $product_id ),
				(float) $evaluation['score'],
				! empty( $evaluation['in_scope'] ) ? 1 : 0,
				(int) $evaluation['critical'],
				(int) $evaluation['warning'],
				(int) $evaluation['info'],
				wp_json_encode( $evaluation['findings'], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES ),
				$created_at
			);
		}
		$sql = 'INSERT INTO %i (scan_id,product_id,score,in_scope,critical_count,warning_count,info_count,findings_json,created_at) VALUES ' . implode( ',', $rows ) . ' ON DUPLICATE KEY UPDATE score=VALUES(score),in_scope=VALUES(in_scope),critical_count=VALUES(critical_count),warning_count=VALUES(warning_count),info_count=VALUES(info_count),findings_json=VALUES(findings_json),created_at=VALUES(created_at)';
		$saved = false !== $wpdb->query( $wpdb->prepare( $sql, ...$values ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		if ( $saved ) {
			delete_transient( 'ccpr_scan_tasks_' . $scan_id );
		}
		return $saved;
	}

	public function advance( int $scan_id, int $processed_delta, int $last_product_id ): void {
		global $wpdb;
		$table = Schema::scans_table();
		$wpdb->query(
			$wpdb->prepare(
				'UPDATE %i SET processed_products = processed_products + %d, last_product_id = %d WHERE id = %d',
				$table,
				$processed_delta,
				$last_product_id,
				$scan_id
			)
		);
	}

	public function complete( int $scan_id ): void {
		global $wpdb;
		$results = Schema::results_table();
		$summary = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT COALESCE(AVG(CASE WHEN in_scope = 1 THEN score END),0) AS score, COALESCE(SUM(CASE WHEN in_scope = 1 THEN critical_count ELSE 0 END),0) AS critical, COALESCE(SUM(CASE WHEN in_scope = 1 THEN warning_count ELSE 0 END),0) AS warning, COALESCE(SUM(CASE WHEN in_scope = 1 THEN info_count ELSE 0 END),0) AS info, COALESCE(SUM(in_scope),0) AS in_scope_products FROM %i WHERE scan_id = %d',
				$results,
				$scan_id
			),
			ARRAY_A
		);

		$wpdb->update(
			Schema::scans_table(),
			array(
				'status'         => 'completed',
				'completed_at'   => current_time( 'mysql', true ),
				'overall_score'  => (float) ( $summary['score'] ?? 0 ),
				'critical_count' => (int) ( $summary['critical'] ?? 0 ),
				'warning_count'  => (int) ( $summary['warning'] ?? 0 ),
				'info_count'     => (int) ( $summary['info'] ?? 0 ),
				'in_scope_products' => (int) ( $summary['in_scope_products'] ?? 0 ),
			),
			array( 'id' => $scan_id ),
			array( '%s', '%s', '%f', '%d', '%d', '%d', '%d' ),
			array( '%d' )
		);
	}

	public function fail( int $scan_id, string $message ): void {
		global $wpdb;
		$wpdb->update(
			Schema::scans_table(),
			array( 'status' => 'failed', 'error_message' => sanitize_text_field( $message ), 'completed_at' => current_time( 'mysql', true ) ),
			array( 'id' => $scan_id ),
			array( '%s', '%s', '%s' ),
			array( '%d' )
		);
	}

	public function cancel( int $scan_id ): void {
		global $wpdb;
		$wpdb->update(
			Schema::scans_table(),
			array( 'status' => 'cancelled', 'completed_at' => current_time( 'mysql', true ) ),
			array( 'id' => $scan_id ),
			array( '%s', '%s' ),
			array( '%d' )
		);
	}

	public function next_product_ids( int $after_id, int $limit, array $scope = array() ): array {
		global $wpdb;
		$posts = $wpdb->posts;
		$sql   = "SELECT p.ID FROM {$posts} p WHERE p.post_type = 'product' AND p.post_status = 'publish' AND p.ID > %d";
		$args  = array( $after_id );

		if ( ! empty( $scope['category_id'] ) ) {
			$relationships = $wpdb->term_relationships;
			$taxonomies    = $wpdb->term_taxonomy;
			$sql .= " AND p.ID IN (SELECT tr.object_id FROM {$relationships} tr INNER JOIN {$taxonomies} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy = 'product_cat' AND tt.term_id = %d)";
			$args[] = absint( $scope['category_id'] );
		}

		$sql   .= ' ORDER BY p.ID ASC LIMIT %d';
		$args[] = max( 1, min( 2000, $limit ) );
		$query  = $wpdb->prepare( $sql, ...$args ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$ids    = $wpdb->get_col( $query ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		return array_map( 'absint', is_array( $ids ) ? $ids : array() );
	}

	public function results( int $scan_id, array $args = array() ): array {
		global $wpdb;
		$table    = Schema::results_table();
		$args     = wp_parse_args( $args, array( 'page' => 1, 'per_page' => 50, 'severity' => '', 'search' => '', 'rule' => '', 'rules' => array() ) );
		$where    = array( 'r.scan_id = %d' );
		$params   = array( $scan_id );

		$rule = sanitize_key( (string) $args['rule'] );
		$rules = array_values( array_unique( array_filter( array_map( 'sanitize_key', (array) $args['rules'] ) ) ) );
		$has_rule_filter = '' !== $rule || ! empty( $rules );

		if ( 'critical' === $args['severity'] && ! $has_rule_filter ) {
			$where[] = 'r.critical_count > 0';
		} elseif ( 'warning' === $args['severity'] && ! $has_rule_filter ) {
			$where[] = 'r.warning_count > 0';
		} elseif ( 'ready' === $args['severity'] ) {
			$where[] = 'r.in_scope = 1 AND r.critical_count = 0 AND r.warning_count = 0';
		} elseif ( 'out_of_scope' === $args['severity'] ) {
			$where[] = 'r.in_scope = 0';
		}

		if ( '' !== trim( (string) $args['search'] ) ) {
			$where[]  = 'p.post_title LIKE %s';
			$params[] = '%' . $wpdb->esc_like( trim( (string) $args['search'] ) ) . '%';
		}

		if ( '' !== $rule ) {
			$where[]  = $this->finding_condition( (string) $args['severity'] );
			$params[] = $this->finding_candidate( $rule, (string) $args['severity'] );
		}
		if ( $rules ) {
			$rule_where = array();
			foreach ( $rules as $raw_rule ) {
				$rule_where[] = $this->finding_condition( (string) $args['severity'] );
				$params[]     = $this->finding_candidate( $raw_rule, (string) $args['severity'] );
			}
			$where[] = '(' . implode( ' OR ', $rule_where ) . ')';
		}

		$limit    = min( 200, max( 1, absint( $args['per_page'] ) ) );
		$offset   = ( max( 1, absint( $args['page'] ) ) - 1 ) * $limit;
		$params[] = $limit;
		$params[] = $offset;
		$where_sql = implode( ' AND ', $where );
		$sql       = "SELECT r.*, p.post_title FROM {$table} r INNER JOIN {$wpdb->posts} p ON p.ID = r.product_id WHERE {$where_sql} ORDER BY r.score ASC, r.product_id ASC LIMIT %d OFFSET %d";
		$query     = $wpdb->prepare( $sql, ...$params ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$rows      = $wpdb->get_results( $query, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		return is_array( $rows ) ? $rows : array();
	}

	public function count_results( int $scan_id, string $severity = '', string $search = '', string $rule = '', array $rules = array() ): int {
		global $wpdb;
		$table  = Schema::results_table();
		$where  = array( 'r.scan_id = %d' );
		$params = array( $scan_id );

		$rule = sanitize_key( $rule );
		$rules = array_values( array_unique( array_filter( array_map( 'sanitize_key', $rules ) ) ) );
		$has_rule_filter = '' !== $rule || ! empty( $rules );

		if ( 'critical' === $severity && ! $has_rule_filter ) {
			$where[] = 'r.critical_count > 0';
		} elseif ( 'warning' === $severity && ! $has_rule_filter ) {
			$where[] = 'r.warning_count > 0';
		} elseif ( 'ready' === $severity ) {
			$where[] = 'r.in_scope = 1 AND r.critical_count = 0 AND r.warning_count = 0';
		} elseif ( 'out_of_scope' === $severity ) {
			$where[] = 'r.in_scope = 0';
		}
		if ( '' !== trim( $search ) ) {
			$where[]  = 'p.post_title LIKE %s';
			$params[] = '%' . $wpdb->esc_like( trim( $search ) ) . '%';
		}
		if ( '' !== $rule ) {
			$where[]  = $this->finding_condition( $severity );
			$params[] = $this->finding_candidate( $rule, $severity );
		}
		if ( $rules ) {
			$rule_where = array();
			foreach ( $rules as $raw_rule ) {
				$rule_where[] = $this->finding_condition( $severity );
				$params[]     = $this->finding_candidate( $raw_rule, $severity );
			}
			$where[] = '(' . implode( ' OR ', $rule_where ) . ')';
		}

		$sql = "SELECT COUNT(*) FROM {$table} r INNER JOIN {$wpdb->posts} p ON p.ID = r.product_id WHERE " . implode( ' AND ', $where );
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, ...$params ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}

	private function finding_candidate( string $rule, string $severity = '' ): string {
		$rule = sanitize_key( $rule );
		if ( in_array( $severity, array( 'critical', 'warning' ), true ) ) {
			return '[{][^}]*"rule":"' . $rule . '"[^}]*"severity":"' . $severity . '"|[{][^}]*"severity":"' . $severity . '"[^}]*"rule":"' . $rule . '"';
		}
		global $wpdb;
		return '%' . $wpdb->esc_like( '"rule":"' . $rule . '"' ) . '%';
	}

	private function finding_condition( string $severity ): string {
		return in_array( $severity, array( 'critical', 'warning' ), true ) ? 'r.findings_json REGEXP %s' : 'r.findings_json LIKE %s';
	}

	public function affected_product_count( int $scan_id ): int {
		global $wpdb;
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM %i WHERE scan_id = %d AND in_scope = 1 AND (critical_count > 0 OR warning_count > 0)',
				Schema::results_table(),
				$scan_id
			)
		);
	}

	public function issue_summary( int $scan_id, int $limit = 5 ): array {
		$cache_key = 'ccpr_scan_issues_' . $scan_id;
		$cached = get_transient( $cache_key );
		if ( is_array( $cached ) ) {
			return array_slice( $cached, 0, $limit );
		}

		global $wpdb;
		$table = Schema::results_table();
		$issues = array();
		$after_id = 0;
		do {
			$rows = $wpdb->get_results(
				$wpdb->prepare( 'SELECT id,findings_json FROM %i WHERE scan_id = %d AND in_scope = 1 AND id > %d ORDER BY id ASC LIMIT 500', $table, $scan_id, $after_id ),
				ARRAY_A
			);
			$rows = is_array( $rows ) ? $rows : array();
			foreach ( $rows as $row ) {
				$after_id = (int) $row['id'];
				$findings = json_decode( (string) $row['findings_json'], true );
				foreach ( is_array( $findings ) ? $findings : array() as $finding ) {
					$rule = sanitize_key( $finding['rule'] ?? '' );
					if ( '' === $rule || 'info' === ( $finding['severity'] ?? '' ) ) {
						continue;
					}
					if ( ! isset( $issues[ $rule ] ) ) {
						$issues[ $rule ] = array(
							'rule' => $rule,
							'severity' => 'critical' === ( $finding['severity'] ?? '' ) ? 'critical' : 'warning',
							'message' => sanitize_text_field( $finding['message'] ?? '' ),
							'count' => 0,
						);
					}
					++$issues[ $rule ]['count'];
				}
			}
		} while ( 500 === count( $rows ) );
		$issues = array_values( $issues );
		usort(
			$issues,
			static function ( array $left, array $right ): int {
				$severity = array( 'critical' => 0, 'warning' => 1 );
				return ( $severity[ $left['severity'] ] ?? 2 ) <=> ( $severity[ $right['severity'] ] ?? 2 ) ?: $right['count'] <=> $left['count'];
			}
		);
		set_transient( $cache_key, $issues, DAY_IN_SECONDS );
		return array_slice( $issues, 0, $limit );
	}

	public function delete_scan( int $scan_id ): void {
		global $wpdb;
		$wpdb->delete( Schema::results_table(), array( 'scan_id' => $scan_id ), array( '%d' ) );
		$wpdb->delete( Schema::scans_table(), array( 'id' => $scan_id ), array( '%d' ) );
		delete_transient( 'ccpr_scan_issues_' . $scan_id );
		delete_transient( 'ccpr_scan_tasks_' . $scan_id );
	}

	private function count_products( array $scope ): int {
		global $wpdb;
		$posts = $wpdb->posts;
		if ( ! empty( $scope['category_id'] ) ) {
			$relationships = $wpdb->term_relationships;
			$taxonomies    = $wpdb->term_taxonomy;
			return (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(DISTINCT p.ID) FROM %i p INNER JOIN %i tr ON p.ID = tr.object_id INNER JOIN %i tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_type = 'product' AND p.post_status = 'publish' AND tt.taxonomy = 'product_cat' AND tt.term_id = %d",
					$posts,
					$relationships,
					$taxonomies,
					absint( $scope['category_id'] )
				)
			);
		}
		return (int) $wpdb->get_var( "SELECT COUNT(ID) FROM {$posts} WHERE post_type = 'product' AND post_status = 'publish'" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}
}
