<?php
/**
 * Persistence for citation queries and check results.
 *
 * @package WPLens
 */

namespace WPLens\Citations;

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
// phpcs:disable WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter
//
// Justification: this file is a data-access layer. Table names are built
// from $wpdb->prefix . a static suffix, which is safe by construction; the linter
// cannot prove that. All user input flows through $wpdb->prepare() placeholders.


final class CitationRepository {

	private function queries_table(): string {
		global $wpdb;
		return $wpdb->prefix . 'wplens_citation_queries';
	}

	private function checks_table(): string {
		global $wpdb;
		return $wpdb->prefix . 'wplens_citation_checks';
	}

	public function add_query( string $query ): int {
		global $wpdb;
		$query = trim( $query );
		if ( '' === $query ) {
			return 0;
		}

		$ok = $wpdb->insert(
			$this->queries_table(),
			array(
				'query'      => substr( $query, 0, 512 ),
				'is_active'  => 1,
				'created_at' => current_time( 'mysql', true ),
			)
		);
		return false === $ok ? 0 : (int) $wpdb->insert_id;
	}

	public function delete_query( int $id ): bool {
		global $wpdb;
		$wpdb->delete( $this->checks_table(), array( 'query_id' => $id ), array( '%d' ) );
		return false !== $wpdb->delete( $this->queries_table(), array( 'id' => $id ), array( '%d' ) );
	}

	public function set_active( int $id, bool $active ): void {
		global $wpdb;
		$wpdb->update(
			$this->queries_table(),
			array( 'is_active' => $active ? 1 : 0 ),
			array( 'id' => $id ),
			array( '%d' ),
			array( '%d' )
		);
	}

	public function active_queries(): array {
		global $wpdb;
		$table = $this->queries_table();
		$rows = $wpdb->get_results( "SELECT id, query FROM {$table} WHERE is_active = 1 ORDER BY id ASC", ARRAY_A );
		return is_array( $rows ) ? $rows : array();
	}

	public function all_queries(): array {
		global $wpdb;
		$table = $this->queries_table();
		$rows = $wpdb->get_results( "SELECT id, query, is_active, created_at FROM {$table} ORDER BY created_at DESC", ARRAY_A );
		return is_array( $rows ) ? $rows : array();
	}

	public function record_check( int $query_id, string $provider, \WPLens\AI\CitationResult $result ): int {
		global $wpdb;

		$ok = $wpdb->insert(
			$this->checks_table(),
			array(
				'query_id'             => $query_id,
				'ai_provider'          => substr( $provider, 0, 32 ),
				'cited'                => $result->cited ? 1 : 0,
				'position'             => $result->position,
				'cited_url'            => $result->cited_url ? substr( $result->cited_url, 0, 2048 ) : null,
				'raw_response_excerpt' => $result->raw_excerpt,
				'checked_at'           => current_time( 'mysql', true ),
			)
		);
		return false === $ok ? 0 : (int) $wpdb->insert_id;
	}

	public function recent_checks( int $limit = 100 ): array {
		global $wpdb;
		$table_c = $this->checks_table();
		$table_q = $this->queries_table();
		$limit   = max( 1, min( 500, $limit ) );

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT c.id, c.query_id, c.ai_provider, c.cited, c.position, c.cited_url, c.checked_at, q.query
				 FROM {$table_c} c
				 LEFT JOIN {$table_q} q ON q.id = c.query_id
				 ORDER BY c.checked_at DESC
				 LIMIT %d",
				$limit
			),
			ARRAY_A
		);
		return is_array( $rows ) ? $rows : array();
	}

	public function citation_rate( int $days = 30 ): float {
		global $wpdb;
		$table = $this->checks_table();
		$since = gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS );

		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT SUM(cited) AS cited, COUNT(*) AS total FROM {$table} WHERE checked_at >= %s",
				$since
			),
			ARRAY_A
		);

		if ( ! $row || (int) $row['total'] === 0 ) {
			return 0.0;
		}
		return round( (int) $row['cited'] / (int) $row['total'] * 100, 1 );
	}
}
