<?php
/**
 * Notification log persistence via wpdb.
 *
 * @package Bookline
 */

declare( strict_types=1 );

namespace Bookline\Infrastructure\Persistence;

use Bookline\Domain\Notification\NotificationLogEntry;
use Bookline\Domain\Notification\NotificationLogRepository;
use Bookline\Domain\Notification\NotificationStatus;
use Bookline\Domain\Shared\Clock;
use Bookline\Domain\Shared\Uuid;

/**
 * Persists notification delivery log rows.
 *
 * @since 0.1.0
 */
final class WpdbNotificationLogRepository extends AbstractWpdbRepository implements NotificationLogRepository {

	/**
	 * Constructor.
	 *
	 * @param Clock      $clock Clock.
	 * @param \wpdb|null $db    DB.
	 */
	public function __construct(
		private readonly Clock $clock,
		?\wpdb $db = null,
	) {
		parent::__construct( $db );
	}

	/**
	 * {@inheritDoc}
	 *
	 * @param int $id Id.
	 */
	public function find_by_id( int $id ): ?NotificationLogEntry {
		$sql = $this->db->prepare(
			'SELECT * FROM %i WHERE `id` = %d LIMIT 1',
			$this->table( 'notification_log' ),
			$id
		);
		$row = is_string( $sql ) ? $this->db->get_row( $sql, ARRAY_A ) : null;
		return is_array( $row ) ? $this->hydrate( $row ) : null;
	}

	/**
	 * {@inheritDoc}
	 *
	 * @param string $key Key.
	 */
	public function find_by_idempotency_key( string $key ): ?NotificationLogEntry {
		$sql = $this->db->prepare(
			'SELECT * FROM %i WHERE `idempotency_key` = %s LIMIT 1',
			$this->table( 'notification_log' ),
			$key
		);
		$row = is_string( $sql ) ? $this->db->get_row( $sql, ARRAY_A ) : null;
		return is_array( $row ) ? $this->hydrate( $row ) : null;
	}

	/**
	 * {@inheritDoc}
	 *
	 * @param NotificationLogEntry $entry Entry.
	 */
	public function save( NotificationLogEntry $entry ): void {
		$data = [
			'uuid'                => $entry->uuid()->to_string(),
			'booking_id'          => $entry->booking_id(),
			'customer_id'         => $entry->customer_id(),
			'template_key'        => $entry->template_key(),
			'channel'             => $entry->channel(),
			'recipient'           => $entry->recipient(),
			'subject'             => $entry->subject(),
			'body'                => $entry->body(),
			'status'              => $entry->status()->value,
			'scheduled_for'       => null === $entry->scheduled_for() ? null : $this->format_datetime( $entry->scheduled_for() ),
			'sent_at'             => null === $entry->sent_at() ? null : $this->format_datetime( $entry->sent_at() ),
			'attempts'            => $entry->attempts(),
			'last_error'          => $entry->last_error(),
			'action_scheduler_id' => $entry->action_scheduler_id(),
			'idempotency_key'     => $entry->idempotency_key(),
			'reminder_id'         => $entry->reminder_id(),
			'created_at'          => $this->format_datetime( $entry->created_at() ),
			'updated_at'          => $this->format_datetime( $entry->updated_at() ),
		];
		if ( null === $entry->id() ) {
			$this->db->insert( $this->table( 'notification_log' ), $data );
			$entry->assign_id( $this->last_insert_id() );
			return;
		}
		$this->db->update( $this->table( 'notification_log' ), $data, [ 'id' => $entry->id() ] );
	}

	/**
	 * {@inheritDoc}
	 *
	 * @param int $booking_id Booking id.
	 *
	 * @return list<NotificationLogEntry>
	 */
	public function cancel_pending_for_booking( int $booking_id ): array {
		$pending = $this->pending_for_booking( $booking_id );
		$now     = $this->clock->now();
		foreach ( $pending as $entry ) {
			$entry->mark_cancelled( $now );
			$this->save( $entry );
		}
		return $pending;
	}

	/**
	 * {@inheritDoc}
	 *
	 * @param int $booking_id Booking id.
	 *
	 * @return list<NotificationLogEntry>
	 */
	public function pending_for_booking( int $booking_id ): array {
		$sql = $this->db->prepare(
			'SELECT * FROM %i WHERE `booking_id` = %d AND `status` IN (%s, %s)',
			$this->table( 'notification_log' ),
			$booking_id,
			NotificationStatus::Queued->value,
			NotificationStatus::Drafted->value
		);
		$rows = is_string( $sql ) ? $this->db->get_results( $sql, ARRAY_A ) : [];
		if ( ! is_array( $rows ) ) {
			return [];
		}
		$out = [];
		foreach ( $rows as $row ) {
			if ( is_array( $row ) ) {
				$out[] = $this->hydrate( $row );
			}
		}
		return $out;
	}

	/**
	 * {@inheritDoc}
	 *
	 * @param NotificationStatus $status Status.
	 */
	public function count_by_status( NotificationStatus $status ): int {
		$sql = $this->db->prepare(
			'SELECT COUNT(*) FROM %i WHERE `status` = %s',
			$this->table( 'notification_log' ),
			$status->value
		);
		return is_string( $sql ) ? (int) $this->db->get_var( $sql ) : 0;
	}

	/**
	 * {@inheritDoc}
	 *
	 * @param array<string, mixed> $filters Filters.
	 * @param int                  $page    Page.
	 * @param int                  $per_page Per page.
	 *
	 * @return array{items: list<NotificationLogEntry>, total: int}
	 */
	public function list_for_admin( array $filters, int $page, int $per_page ): array {
		$where  = [ '1=1' ];
		$params = [];

		$status = isset( $filters['status'] ) && is_string( $filters['status'] ) ? trim( $filters['status'] ) : '';
		if ( '' !== $status ) {
			$where[]  = '`status` = %s';
			$params[] = $status;
		}

		$channel = isset( $filters['channel'] ) && is_string( $filters['channel'] ) ? trim( $filters['channel'] ) : '';
		if ( '' !== $channel ) {
			$where[]  = '`channel` = %s';
			$params[] = $channel;
		}

		$booking_id = isset( $filters['booking_id'] ) ? (int) $filters['booking_id'] : 0;
		if ( $booking_id > 0 ) {
			$where[]  = '`booking_id` = %d';
			$params[] = $booking_id;
		}

		$search = isset( $filters['search'] ) && is_string( $filters['search'] ) ? trim( $filters['search'] ) : '';
		if ( '' !== $search ) {
			$like     = '%' . $this->db->esc_like( $search ) . '%';
			$where[]  = '(`recipient` LIKE %s OR `subject` LIKE %s OR `template_key` LIKE %s)';
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
		}

		$where_sql = implode( ' AND ', $where );
		$offset    = max( 0, ( $page - 1 ) * $per_page );

		$count_sql = $this->db->prepare(
			'SELECT COUNT(*) FROM %i WHERE ' . $where_sql,
			$this->table( 'notification_log' ),
			...$params
		);
		$total = is_string( $count_sql ) ? (int) $this->db->get_var( $count_sql ) : 0;

		$list_params   = $params;
		$list_params[] = $per_page;
		$list_params[] = $offset;
		$list_sql      = $this->db->prepare(
			'SELECT * FROM %i WHERE ' . $where_sql . ' ORDER BY `created_at` DESC LIMIT %d OFFSET %d',
			$this->table( 'notification_log' ),
			...$list_params
		);
		$rows = is_string( $list_sql ) ? $this->db->get_results( $list_sql, ARRAY_A ) : [];
		if ( ! is_array( $rows ) ) {
			return [ 'items' => [], 'total' => $total ];
		}

		$items = [];
		foreach ( $rows as $row ) {
			if ( is_array( $row ) ) {
				$items[] = $this->hydrate( $row );
			}
		}

		return [ 'items' => $items, 'total' => $total ];
	}

	/**
	 * Hydrate.
	 *
	 * @param array<string, mixed> $row Row.
	 */
	private function hydrate( array $row ): NotificationLogEntry {
		return new NotificationLogEntry(
			(int) $row['id'],
			new Uuid( (string) $row['uuid'] ),
			is_numeric( $row['booking_id'] ?? null ) ? (int) $row['booking_id'] : null,
			is_numeric( $row['customer_id'] ?? null ) ? (int) $row['customer_id'] : null,
			(string) $row['template_key'],
			(string) $row['channel'],
			(string) $row['recipient'],
			( is_string( $row['subject'] ?? null ) ? (string) $row['subject'] : null ),
			( is_string( $row['body'] ?? null ) ? (string) $row['body'] : null ),
			NotificationStatus::from( (string) $row['status'] ),
			is_string( $row['scheduled_for'] ?? null ) && '' !== $row['scheduled_for'] ? $this->parse_datetime( (string) $row['scheduled_for'] ) : null,
			is_string( $row['sent_at'] ?? null ) && '' !== $row['sent_at'] ? $this->parse_datetime( (string) $row['sent_at'] ) : null,
			(int) $row['attempts'],
			( is_string( $row['last_error'] ?? null ) ? (string) $row['last_error'] : null ),
			is_numeric( $row['action_scheduler_id'] ?? null ) ? (int) $row['action_scheduler_id'] : null,
			( is_string( $row['idempotency_key'] ?? null ) ? (string) $row['idempotency_key'] : null ),
			is_numeric( $row['reminder_id'] ?? null ) ? (int) $row['reminder_id'] : null,
			$this->parse_datetime( (string) $row['created_at'] ),
			$this->parse_datetime( (string) $row['updated_at'] ),
		);
	}
}