<?php
/**
 * Delivery-log reads, retry bookkeeping and retention purges.
 *
 * @package RewindBrevoFormBridge
 */

namespace RewindBrevoFormBridge\Logs;

defined( 'ABSPATH' ) || exit;

/*
 * Every query below reads or writes this plugin's own custom table. There is
 * no WordPress API for these rows, so a direct $wpdb call is the only option,
 * and an object cache would have to be invalidated on every submission to stay
 * correct — the delivery log is written far more often than it is read. Table
 * names are passed to $wpdb->prepare() as %i identifier placeholders (WP 6.2+;
 * this plugin requires 6.6), so every query — table names included — goes
 * through prepare(). The WHERE fragment interpolated in some queries is built
 * by where() from a fixed set of clauses, each carrying its own value
 * placeholder; the bound values ride in the same prepare() call.
 */
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

/**
 * All database access for the rbfb_log table.
 *
 * The Processor writes rows; nothing read them until now. Every query goes
 * through $wpdb->prepare(); the table name is interpolated from
 * $wpdb->prefix only, never from caller input.
 *
 * @since 0.4.0
 */
class LogRepository {

	/**
	 * Statuses the Processor and queue can write.
	 *
	 * @var string[]
	 */
	const STATUSES = array( 'queued', 'success', 'failed', 'retrying', 'skipped', 'pending_doi' );

	/**
	 * Fully-prefixed log table name.
	 *
	 * @since 0.4.0
	 * @return string
	 */
	private function table(): string {
		global $wpdb;

		return $wpdb->prefix . 'rbfb_log';
	}

	/**
	 * Fully-prefixed feeds table name, for joining in the feed title.
	 *
	 * @since 0.4.0
	 * @return string
	 */
	private function feeds_table(): string {
		global $wpdb;

		return $wpdb->prefix . 'rbfb_feeds';
	}

	/**
	 * Ordered bind values for the literal WHERE fragment used by the list
	 * queries.
	 *
	 * The fragment itself is written out verbatim in each query so the SQL
	 * string handed to $wpdb->prepare() contains no interpolation at all:
	 *
	 *   ( %s = '' OR l.status = %s )
	 *   AND ( %d = 0 OR l.feed_id = %d )
	 *   AND ( %s = '' OR l.email LIKE %s )
	 *   AND ( %s = '' OR l.created_at >= %s )
	 *   AND ( %s = '' OR l.created_at <= %s )
	 *
	 * Each filter contributes a guard value and a match value; when the
	 * filter is unset both are empty and the guard collapses the condition
	 * to TRUE, so unset filters cost nothing and set filters bind normally.
	 *
	 * @since 0.4.0
	 * @param array $filters status|feed_id|search|date_from|date_to.
	 * @return array Ten ordered bind values.
	 */
	private function where_values( array $filters ): array {
		global $wpdb;

		$status = ( ! empty( $filters['status'] ) && in_array( $filters['status'], self::STATUSES, true ) )
			? (string) $filters['status']
			: '';

		$feed_id = ! empty( $filters['feed_id'] ) ? (int) $filters['feed_id'] : 0;

		$search = ! empty( $filters['search'] ) ? (string) $filters['search'] : '';
		$like   = '' !== $search ? '%' . $wpdb->esc_like( $search ) . '%' : '';

		$from    = ! empty( $filters['date_from'] ) ? (string) $filters['date_from'] : '';
		$from_ts = '' !== $from ? $from . ' 00:00:00' : '';

		$to    = ! empty( $filters['date_to'] ) ? (string) $filters['date_to'] : '';
		$to_ts = '' !== $to ? $to . ' 23:59:59' : '';

		return array( $status, $status, $feed_id, $feed_id, $search, $like, $from, $from_ts, $to, $to_ts );
	}

	/**
	 * One page of log rows, newest first, with the feed title joined in.
	 *
	 * @since 0.4.0
	 * @param array $filters  See where().
	 * @param int   $page     1-based page number.
	 * @param int   $per_page Rows per page.
	 * @return array{items:array,total:int,pages:int}
	 */
	public function paginate( array $filters, int $page = 1, int $per_page = 20 ): array {
		global $wpdb;

		$page     = max( 1, $page );
		$per_page = max( 1, min( 100, $per_page ) );
		$offset   = ( $page - 1 ) * $per_page;

		$values = $this->where_values( $filters );

		$log   = $this->table();
		$feeds = $this->feeds_table();

		$total = (int) $wpdb->get_var(
			$wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- values are passed as one ordered array, a documented prepare() form the sniff cannot count.
				"SELECT COUNT(*) FROM %i l
				WHERE ( %s = '' OR l.status = %s )
				AND ( %d = 0 OR l.feed_id = %d )
				AND ( %s = '' OR l.email LIKE %s )
				AND ( %s = '' OR l.created_at >= %s )
				AND ( %s = '' OR l.created_at <= %s )",
				array_merge( array( $log ), $values )
			)
		);

		$rows = $wpdb->get_results(
			$wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- values are passed as one ordered array, a documented prepare() form the sniff cannot count.
				"SELECT l.*, f.title AS feed_title, f.adapter AS feed_adapter
				FROM %i l
				LEFT JOIN %i f ON f.id = l.feed_id
				WHERE ( %s = '' OR l.status = %s )
				AND ( %d = 0 OR l.feed_id = %d )
				AND ( %s = '' OR l.email LIKE %s )
				AND ( %s = '' OR l.created_at >= %s )
				AND ( %s = '' OR l.created_at <= %s )
				ORDER BY l.created_at DESC, l.id DESC
				LIMIT %d OFFSET %d",
				array_merge( array( $log, $feeds ), $values, array( $per_page, $offset ) )
			),
			ARRAY_A
		);

		return array(
			'items' => array_map( array( $this, 'shape' ), $rows ? $rows : array() ),
			'total' => $total,
			'pages' => (int) ceil( $total / $per_page ),
		);
	}

	/**
	 * Every matching row, unpaginated, for a CSV export.
	 *
	 * @since 0.4.0
	 * @param array $filters See where().
	 * @param int   $limit   Hard ceiling so an export cannot exhaust memory.
	 * @return array Shaped rows.
	 */
	public function export( array $filters, int $limit = 5000 ): array {
		global $wpdb;

		$values = $this->where_values( $filters );

		$log   = $this->table();
		$feeds = $this->feeds_table();

		$rows = $wpdb->get_results(
			$wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- values are passed as one ordered array, a documented prepare() form the sniff cannot count.
				"SELECT l.*, f.title AS feed_title, f.adapter AS feed_adapter
				FROM %i l
				LEFT JOIN %i f ON f.id = l.feed_id
				WHERE ( %s = '' OR l.status = %s )
				AND ( %d = 0 OR l.feed_id = %d )
				AND ( %s = '' OR l.email LIKE %s )
				AND ( %s = '' OR l.created_at >= %s )
				AND ( %s = '' OR l.created_at <= %s )
				ORDER BY l.created_at DESC, l.id DESC
				LIMIT %d",
				array_merge( array( $log, $feeds ), $values, array( max( 1, $limit ) ) )
			),
			ARRAY_A
		);

		return array_map( array( $this, 'shape' ), $rows ? $rows : array() );
	}

	/**
	 * One log row by id.
	 *
	 * @since 0.4.0
	 * @param int $id Log row id.
	 * @return array|null Shaped row, or null when absent.
	 */
	public function find( int $id ): ?array {
		global $wpdb;

		$log   = $this->table();
		$feeds = $this->feeds_table();

		$row = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT l.*, f.title AS feed_title, f.adapter AS feed_adapter
				FROM %i l
				LEFT JOIN %i f ON f.id = l.feed_id
				WHERE l.id = %d',
				$log,
				$feeds,
				$id
			),
			ARRAY_A
		);

		return $row ? $this->shape( $row ) : null;
	}

	/**
	 * Ids of every failed row matching the filters, for a bulk retry.
	 *
	 * @since 0.4.0
	 * @param array $filters See where().
	 * @param int   $limit   Hard ceiling on one bulk run.
	 * @return int[]
	 */
	public function failed_ids( array $filters, int $limit = 100 ): array {
		global $wpdb;

		$filters['status'] = 'failed';

		$values = $this->where_values( $filters );

		$log = $this->table();

		$ids = $wpdb->get_col(
			$wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- values are passed as one ordered array, a documented prepare() form the sniff cannot count.
				"SELECT l.id FROM %i l
				WHERE ( %s = '' OR l.status = %s )
				AND ( %d = 0 OR l.feed_id = %d )
				AND ( %s = '' OR l.email LIKE %s )
				AND ( %s = '' OR l.created_at >= %s )
				AND ( %s = '' OR l.created_at <= %s )
				ORDER BY l.created_at DESC LIMIT %d",
				array_merge( array( $log ), $values, array( max( 1, $limit ) ) )
			)
		);

		return array_map( 'intval', $ids ? $ids : array() );
	}

	/**
	 * Record the outcome of a retry attempt against an existing row.
	 *
	 * @since 0.4.0
	 * @param int    $id         Log row id.
	 * @param string $status     New status.
	 * @param int    $http_code  HTTP status of the retry, 0 for transport failure.
	 * @param string $error_code Brevo error code, empty when none.
	 * @param string $message    Human explanation.
	 * @param string $response   Raw response body.
	 * @return bool
	 */
	public function record_attempt( int $id, string $status, int $http_code, string $error_code, string $message, string $response ): bool {
		global $wpdb;

		$log = $this->table();

		$ok = $wpdb->query(
			$wpdb->prepare(
				'UPDATE %i SET status = %s, http_code = %d, error_code = %s, message = %s, response = %s, attempts = attempts + 1, updated_at = %s WHERE id = %d',
				$log,
				$status,
				$http_code,
				'' !== $error_code ? $error_code : null,
				$message,
				$response,
				current_time( 'mysql', true ),
				$id
			)
		);

		return false !== $ok;
	}

	/**
	 * Counts by status over a trailing window, for the dashboard tiles.
	 *
	 * @since 0.4.0
	 * @param int $days Window size in days.
	 * @return array{success:int,failed:int,skipped:int,total:int,created:int,updated:int}
	 */
	public function stats( int $days = 30 ): array {
		global $wpdb;

		$since = gmdate( 'Y-m-d H:i:s', time() - ( max( 1, $days ) * DAY_IN_SECONDS ) );
		$log   = $this->table();

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT status, http_code, COUNT(*) AS n FROM %i WHERE created_at >= %s GROUP BY status, http_code',
				$log,
				$since
			),
			ARRAY_A
		);

		$stats = array(
			'success' => 0,
			'failed'  => 0,
			'skipped' => 0,
			'total'   => 0,
			'created' => 0,
			'updated' => 0,
		);

		foreach ( $rows ? $rows : array() as $row ) {
			$n      = (int) $row['n'];
			$status = (string) $row['status'];

			$stats['total'] += $n;

			if ( isset( $stats[ $status ] ) ) {
				$stats[ $status ] += $n;
			}

			// Brevo answers 201 when it created the contact and 204 when it
			// updated an existing one; that is the only signal we get.
			if ( 'success' === $status ) {
				if ( 201 === (int) $row['http_code'] ) {
					$stats['created'] += $n;
				} elseif ( 204 === (int) $row['http_code'] ) {
					$stats['updated'] += $n;
				}
			}
		}

		return $stats;
	}

	/**
	 * The most recent delivery timestamp for one feed.
	 *
	 * @since 0.4.0
	 * @param int $feed_id Feed id.
	 * @return string|null MySQL UTC datetime, or null when never delivered.
	 */
	public function last_sync_for( int $feed_id ): ?string {
		global $wpdb;

		$log = $this->table();

		$value = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT created_at FROM %i WHERE feed_id = %d ORDER BY created_at DESC LIMIT 1',
				$log,
				$feed_id
			)
		);

		return $value ? (string) $value : null;
	}

	/**
	 * The newest failure, for the system status report.
	 *
	 * @since 0.4.0
	 * @return array|null Shaped row, or null when there are no failures.
	 */
	public function last_failure(): ?array {
		global $wpdb;

		$log = $this->table();

		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM %i WHERE status = 'failed' ORDER BY created_at DESC LIMIT 1",
				$log
			),
			ARRAY_A
		);

		return $row ? $this->shape( $row ) : null;
	}

	/**
	 * Delete rows older than N days.
	 *
	 * @since 0.4.0
	 * @param int $days Retention window.
	 * @return int Rows deleted.
	 */
	public function purge_older_than( int $days ): int {
		global $wpdb;

		$cutoff = gmdate( 'Y-m-d H:i:s', time() - ( max( 1, $days ) * DAY_IN_SECONDS ) );

		$log = $this->table();

		$deleted = $wpdb->query(
			$wpdb->prepare(
				'DELETE FROM %i WHERE created_at < %s',
				$log,
				$cutoff
			)
		);

		return is_numeric( $deleted ) ? (int) $deleted : 0;
	}

	/**
	 * Total row count.
	 *
	 * @since 0.4.0
	 * @return int
	 */
	public function count(): int {
		global $wpdb;

		$log = $this->table();

		return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $log ) );
	}

	/**
	 * Normalise a raw row into the shape the REST layer returns.
	 *
	 * @since 0.4.0
	 * @param array $row Raw associative row.
	 * @return array
	 */
	private function shape( array $row ): array {
		return array(
			'id'         => (int) $row['id'],
			'feed_id'    => (int) $row['feed_id'],
			'feed_title' => isset( $row['feed_title'] ) && null !== $row['feed_title']
				? (string) $row['feed_title']
				: __( '(deleted feed)', 'rewind-form-bridge-for-brevo' ),
			'adapter'    => isset( $row['feed_adapter'] ) ? (string) $row['feed_adapter'] : '',
			'entry_ref'  => isset( $row['entry_ref'] ) ? (string) $row['entry_ref'] : '',
			'email'      => isset( $row['email'] ) ? (string) $row['email'] : '',
			'status'     => (string) $row['status'],
			'http_code'  => null !== $row['http_code'] ? (int) $row['http_code'] : null,
			'error_code' => isset( $row['error_code'] ) ? (string) $row['error_code'] : '',
			'message'    => isset( $row['message'] ) ? (string) $row['message'] : '',
			'request'    => isset( $row['request'] ) ? (string) $row['request'] : '',
			'response'   => isset( $row['response'] ) ? (string) $row['response'] : '',
			'attempts'   => (int) $row['attempts'],
			'created_at' => (string) $row['created_at'],
			'updated_at' => (string) $row['updated_at'],
		);
	}
}

// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
