<?php
/**
 * @package Hafenstudios\Kurato
 */

namespace Hafenstudios\Kurato\Db;

defined( 'ABSPATH' ) || exit;

/**
 * Versand-Warteschlange (hs_mail_queue, K4-Konzept Abschnitt 6).
 *
 * Snapshot der Empfaenger einer Kampagne. Der Prozessor claimt Batches, sendet
 * und markiert. Da nur ein Worker laeuft (Action Scheduler bzw. WP-Cron),
 * dient `claimed_at` vor allem der Crash-Recovery: haengengebliebene
 * "sending"-Zeilen fallen nach einer Weile auf "pending" zurueck.
 *
 * Nicht final, damit Tests die DB-Methoden per Test-Double ueberschreiben koennen.
 */
class QueueRepository {

	public const MAX_ATTEMPTS = 3;

	/**
	 * Fuegt Empfaenger als pending in die Queue ein (Materialisierung, K4c).
	 * INSERT IGNORE dank UNIQUE(campaign_id, contact_id) - keine Doppelsendung.
	 *
	 * @param int                                   $campaign_id Kampagne.
	 * @param array<int, array{contact_id:int,to_email:string}> $recipients  Empfaenger.
	 * @param string                                $scheduled_at Startzeit (mysql, UTC).
	 * @return int Anzahl eingefuegter Zeilen.
	 */
	public function enqueue( int $campaign_id, array $recipients, string $scheduled_at ): int {
		if ( array() === $recipients ) {
			return 0;
		}
		global $wpdb;
		$table = Schema::table( 'mail_queue' );

		$values = array();
		$params = array();
		foreach ( $recipients as $r ) {
			$values[] = '(%d, %d, %s, %s, %s)';
			$params[] = $campaign_id;
			$params[] = absint( $r['contact_id'] ?? 0 );
			$params[] = sanitize_email( (string) ( $r['to_email'] ?? '' ) );
			$params[] = 'pending';
			$params[] = $scheduled_at;
		}
		$sql = "INSERT IGNORE INTO {$table} (campaign_id, contact_id, to_email, status, scheduled_at) VALUES " . implode( ', ', $values );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
		return (int) $wpdb->query( $wpdb->prepare( $sql, ...$params ) );
	}

	/**
	 * Claimt einen Batch faelliger Zeilen (setzt sie auf "sending").
	 *
	 * @param int $limit Batchgroesse.
	 * @return array<int, array<string, mixed>>
	 */
	public function claim_batch( int $limit ): array {
		global $wpdb;
		$table = Schema::table( 'mail_queue' );
		$now   = current_time( 'mysql', true );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT id FROM {$table} WHERE status = 'pending' AND ( scheduled_at IS NULL OR scheduled_at <= %s ) ORDER BY id ASC LIMIT %d",
				$now,
				max( 1, $limit )
			)
		);
		$ids = array_map( 'intval', $ids ?: array() );
		if ( array() === $ids ) {
			return array();
		}

		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
		$wpdb->query( $wpdb->prepare( "UPDATE {$table} SET status = 'sending', claimed_at = %s WHERE id IN ({$placeholders})", $now, ...$ids ) );

		$rows = $wpdb->get_results( "SELECT * FROM {$table} WHERE id IN (" . implode( ',', $ids ) . ')', ARRAY_A );
		// phpcs:enable
		return $rows ?: array();
	}

	/**
	 * Markiert eine Zeile als gesendet.
	 *
	 * @param int    $id         Zeilen-ID.
	 * @param string $message_id Provider-Message-ID.
	 * @return void
	 */
	public function mark_sent( int $id, string $message_id ): void {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->update(
			Schema::table( 'mail_queue' ),
			array(
				'status'              => 'sent',
				'sent_at'             => current_time( 'mysql', true ),
				'provider_message_id' => substr( $message_id, 0, 190 ),
			),
			array( 'id' => $id )
		);
	}

	/**
	 * Behandelt einen Fehlversuch: permanenter Fehler oder erschoepfte Versuche
	 * -> failed; sonst zurueck auf pending mit spaeterer Startzeit (Backoff).
	 *
	 * @param int    $id        Zeilen-ID.
	 * @param int    $attempts  Bisherige Versuche.
	 * @param bool   $permanent Permanenter Fehler?
	 * @param string $error     Fehlermeldung.
	 * @return void
	 */
	public function mark_failed( int $id, int $attempts, bool $permanent, string $error ): void {
		global $wpdb;
		$attempts = $attempts + 1;

		if ( $permanent || $attempts >= self::MAX_ATTEMPTS ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->update(
				Schema::table( 'mail_queue' ),
				array( 'status' => 'failed', 'attempts' => $attempts, 'last_error' => substr( $error, 0, 255 ) ),
				array( 'id' => $id )
			);
			return;
		}

		$retry_at = gmdate( 'Y-m-d H:i:s', time() + self::backoff_seconds( $attempts ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->update(
			Schema::table( 'mail_queue' ),
			array( 'status' => 'pending', 'attempts' => $attempts, 'claimed_at' => null, 'scheduled_at' => $retry_at, 'last_error' => substr( $error, 0, 255 ) ),
			array( 'id' => $id )
		);
	}

	/**
	 * Holt haengengebliebene "sending"-Zeilen zurueck (Crash-Recovery).
	 *
	 * @param int $older_than_minutes Alter der Claim-Zeit.
	 * @return int Anzahl zurueckgeholter Zeilen.
	 */
	public function reclaim_stale( int $older_than_minutes = 15 ): int {
		global $wpdb;
		$table  = Schema::table( 'mail_queue' );
		$cutoff = gmdate( 'Y-m-d H:i:s', time() - ( max( 1, $older_than_minutes ) * MINUTE_IN_SECONDS ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return (int) $wpdb->query( $wpdb->prepare( "UPDATE {$table} SET status = 'pending', claimed_at = NULL WHERE status = 'sending' AND claimed_at < %s", $cutoff ) );
	}

	/**
	 * Zaehlt Zeilen je Status fuer eine Kampagne.
	 *
	 * @param int $campaign_id Kampagne.
	 * @return array<string, int>
	 */
	public function counts_for_campaign( int $campaign_id ): array {
		global $wpdb;
		$table = Schema::table( 'mail_queue' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT status, COUNT(*) AS total FROM {$table} WHERE campaign_id = %d GROUP BY status", $campaign_id ), ARRAY_A );
		$out  = array( 'pending' => 0, 'sending' => 0, 'sent' => 0, 'failed' => 0, 'skipped' => 0 );
		foreach ( (array) $rows as $row ) {
			$out[ (string) $row['status'] ] = (int) $row['total'];
		}
		return $out;
	}

	/**
	 * Zahl der Queue-Empfaenger einer Kampagne mit Tracking-Einwilligung.
	 *
	 * Ehrliche Bezugsgroesse fuer die Open-/Klick-Statistik (K4-Konzept
	 * Abschnitt 7): Oeffnungen werden nur mit N = "Empfaenger mit
	 * Tracking-Einwilligung" ausgewiesen, nicht auf die Gesamtempfaengerzahl
	 * schoengerechnet. Live-Join auf den aktuellen Einwilligungsstand: Wer nach
	 * dem Versand widerrufen hat, wird nicht mehr gemessen und faellt hier
	 * konsistent heraus.
	 *
	 * @param int $campaign_id Kampagne.
	 * @return int
	 */
	public function tracking_consented_count( int $campaign_id ): int {
		global $wpdb;
		$queue    = Schema::table( 'mail_queue' );
		$contacts = Schema::table( 'contacts' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$queue} q INNER JOIN {$contacts} c ON c.id = q.contact_id WHERE q.campaign_id = %d AND c.tracking_consent = 1",
				$campaign_id
			)
		);
	}

	/**
	 * Gesamtzahl noch offener Zeilen (pending oder sending).
	 *
	 * @return int
	 */
	public function open_total(): int {
		global $wpdb;
		$table = Schema::table( 'mail_queue' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table} WHERE status IN ('pending','sending')" );
	}

	/**
	 * Markiert eine Zeile als uebersprungen (z.B. Adresse supprimiert).
	 *
	 * @param int    $id     Zeilen-ID.
	 * @param string $reason Grund.
	 * @return void
	 */
	public function mark_skipped( int $id, string $reason ): void {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->update(
			Schema::table( 'mail_queue' ),
			array( 'status' => 'skipped', 'last_error' => substr( $reason, 0, 255 ) ),
			array( 'id' => $id )
		);
	}

	/**
	 * Queue-Zeilen eines Kontakts (fuer den Privacy-Export): welche Kampagne,
	 * welcher Zustand.
	 *
	 * @param int $contact_id Kontakt-ID.
	 * @return array<int, array<string, mixed>>
	 */
	public function for_contact( int $contact_id ): array {
		global $wpdb;
		$table = Schema::table( 'mail_queue' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT campaign_id, status, to_email, sent_at FROM {$table} WHERE contact_id = %d ORDER BY id ASC", $contact_id ), ARRAY_A );
		return $rows ?: array();
	}

	/**
	 * Die letzten fehlgeschlagenen Queue-Zeilen (fuer das Zustell-Protokoll der
	 * Versand-Einstellungen). Liefert nur Zustell-Metadaten (Empfaenger, Fehlertext,
	 * Versuche, Kampagne) - keine Mail-Inhalte. Die Maskierung des Empfaengers
	 * uebernimmt die Anzeige.
	 *
	 * @param int $limit Maximale Anzahl.
	 * @return array<int, array<string, mixed>>
	 */
	public function recent_failures( int $limit = 20 ): array {
		global $wpdb;
		$table = Schema::table( 'mail_queue' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT campaign_id, to_email, attempts, last_error, scheduled_at FROM {$table} WHERE status = 'failed' ORDER BY id DESC LIMIT %d",
				max( 1, $limit )
			),
			ARRAY_A
		);
		return $rows ?: array();
	}

	/**
	 * Backoff-Dauer in Sekunden fuer einen Wiederholungsversuch.
	 *
	 * @param int $attempt Versuchsnummer (1-basiert).
	 * @return int
	 */
	public static function backoff_seconds( int $attempt ): int {
		// 5 min, 15 min, ... (exponentiell begrenzt).
		return min( 3600, 300 * ( 2 ** max( 0, $attempt - 1 ) ) );
	}

	/**
	 * Batchgroesse pro Minuten-Lauf aus der Stundenrate.
	 *
	 * @param int $rate_per_hour Mails pro Stunde.
	 * @return int
	 */
	public static function batch_size( int $rate_per_hour ): int {
		return max( 1, (int) floor( $rate_per_hour / 60 ) );
	}
}
