<?php
/**
 * Email queue and log repository.
 *
 * @package HostKhata
 */

namespace HostKhata\Database\Repositories;

defined( 'ABSPATH' ) || exit;

use HostKhata\Database\Tables;
use HostKhata\Support\Date;

/**
 * Reads and writes queued emails and the log of what was sent.
 *
 * @since 0.1.0
 */
final class EmailRepository extends AbstractRepository {

	/**
	 * A "sending" email older than this many minutes is assumed to have died with its request.
	 */
	const STUCK_MINUTES = 10;

	/**
	 * Columns of the queue table and their formats.
	 */
	const COLUMNS = array(
		'to_email'       => '%s',
		'to_name'        => '%s',
		'subject'        => '%s',
		'body_html'      => '%s',
		'body_text'      => '%s',
		'template_key'   => '%s',
		'related_type'   => '%s',
		'related_id'     => '%d',
		'status'         => '%s',
		'attempts'       => '%d',
		'last_error'     => '%s',
		'send_after_gmt' => '%s',
		'created_at_gmt' => '%s',
	);

	/**
	 * Adds an email to the queue.
	 *
	 * @since 0.1.0
	 *
	 * @param array<string, mixed> $data Queue columns; `to_email` is required.
	 * @return int New id, or 0 on failure.
	 */
	public function enqueue( array $data ): int {
		$now  = Date::now_gmt();
		$row  = array_intersect_key( $data, self::COLUMNS );
		$row += array(
			'status'         => 'pending',
			'send_after_gmt' => $now,
			'created_at_gmt' => $now,
		);

		return $this->insert_row( Tables::name( 'email_queue' ), $row, $this->formats( self::COLUMNS, $row ) );
	}

	/**
	 * Takes up to `$limit` emails that are due and marks them as being sent.
	 *
	 * Each email is claimed with a conditional update, so two overlapping runs
	 * never send the same one.
	 *
	 * @since 0.1.0
	 *
	 * @param int $limit Maximum number of emails.
	 * @return array<int, array<string, mixed>>
	 */
	public function claim_due( int $limit ): array {
		$table = Tables::name( 'email_queue' );
		$now   = Date::now_gmt();

		// Give emails back that were claimed by a run that never finished.
		$this->wpdb->query( $this->prepare( "UPDATE {$table} SET status = 'pending' WHERE status = 'sending' AND send_after_gmt < %s", array( gmdate( 'Y-m-d H:i:s', time() - self::STUCK_MINUTES * MINUTE_IN_SECONDS ) ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter

		$rows    = $this->rows( "SELECT * FROM {$table} WHERE status = 'pending' AND send_after_gmt <= %s ORDER BY id ASC LIMIT %d", array( $now, max( 1, $limit ) ) );
		$claimed = array();

		foreach ( $rows as $row ) {
			$changed = $this->wpdb->query( $this->prepare( "UPDATE {$table} SET status = 'sending', attempts = attempts + 1, send_after_gmt = %s WHERE id = %d AND status = 'pending'", array( $now, (int) $row['id'] ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter

			if ( 1 === $changed ) {
				$row['attempts'] = (int) $row['attempts'] + 1;
				$claimed[]       = $this->hydrate( $row );
			}
		}

		return $claimed;
	}

	/**
	 * Finds a queued email.
	 *
	 * @since 0.1.0
	 *
	 * @param int $id Queue id.
	 * @return array<string, mixed>|null
	 */
	public function find( int $id ): ?array {
		$table = Tables::name( 'email_queue' );
		$row   = $this->row( "SELECT * FROM {$table} WHERE id = %d", array( $id ) );

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

	/**
	 * Updates a queued email.
	 *
	 * @since 0.1.0
	 *
	 * @param int                  $id   Queue id.
	 * @param array<string, mixed> $data Changed columns.
	 * @return bool
	 */
	public function update( int $id, array $data ): bool {
		$row = array_intersect_key( $data, self::COLUMNS );

		return false !== $this->wpdb->update( Tables::name( 'email_queue' ), $row, array( 'id' => $id ), $this->formats( self::COLUMNS, $row ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * Removes a queued email.
	 *
	 * @since 0.1.0
	 *
	 * @param int $id Queue id.
	 * @return bool
	 */
	public function delete( int $id ): bool {
		return false !== $this->wpdb->delete( Tables::name( 'email_queue' ), array( 'id' => $id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * Writes a line to the email log.
	 *
	 * @since 0.1.0
	 *
	 * @param array<string, mixed> $email  The email (queue row).
	 * @param string               $status `sent` or `failed`.
	 * @param string               $error  Error message, if any.
	 * @return void
	 */
	public function log( array $email, string $status, string $error = '' ): void {
		$this->wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			Tables::name( 'email_log' ),
			array(
				'to_email'       => $email['to_email'],
				'subject'        => $email['subject'],
				'template_key'   => $email['template_key'],
				'related_type'   => $email['related_type'],
				'related_id'     => $email['related_id'],
				'status'         => $status,
				'error'          => $error,
				'created_at_gmt' => Date::now_gmt(),
			),
			array( '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s' )
		);
	}

	/**
	 * Lists queued emails (pending, sending and failed).
	 *
	 * @since 0.1.0
	 *
	 * @param array<string, mixed> $args `status`, `page`, `per_page`.
	 * @return array{items: array<int, array<string, mixed>>, total: int}
	 */
	public function queue( array $args ): array {
		$table  = Tables::name( 'email_queue' );
		$where  = '1 = 1';
		$params = array();

		if ( ! empty( $args['status'] ) ) {
			$where    = 'status = %s';
			$params[] = (string) $args['status'];
		}

		$per_page = max( 1, min( 100, (int) ( $args['per_page'] ?? 20 ) ) );
		$offset   = max( 0, ( (int) ( $args['page'] ?? 1 ) - 1 ) * $per_page );
		$total    = (int) $this->value( "SELECT COUNT(*) FROM {$table} WHERE {$where}", $params );
		$rows     = $this->rows( "SELECT * FROM {$table} WHERE {$where} ORDER BY id DESC LIMIT %d OFFSET %d", array_merge( $params, array( $per_page, $offset ) ) );

		return array(
			'items' => array_map( array( $this, 'hydrate' ), $rows ),
			'total' => $total,
		);
	}

	/**
	 * Lists the email log.
	 *
	 * @since 0.1.0
	 *
	 * @param array<string, mixed> $args `search`, `status`, `page`, `per_page`.
	 * @return array{items: array<int, array<string, mixed>>, total: int}
	 */
	public function log_entries( array $args ): array {
		$table  = Tables::name( 'email_log' );
		$where  = array( '1 = 1' );
		$params = array();

		$search = trim( (string) ( $args['search'] ?? '' ) );

		if ( '' !== $search ) {
			$like     = '%' . $this->wpdb->esc_like( $search ) . '%';
			$where[]  = '(to_email LIKE %s OR subject LIKE %s)';
			$params[] = $like;
			$params[] = $like;
		}

		if ( ! empty( $args['status'] ) ) {
			$where[]  = 'status = %s';
			$params[] = (string) $args['status'];
		}

		$where_sql = implode( ' AND ', $where );
		$per_page  = max( 1, min( 100, (int) ( $args['per_page'] ?? 20 ) ) );
		$offset    = max( 0, ( (int) ( $args['page'] ?? 1 ) - 1 ) * $per_page );
		$total     = (int) $this->value( "SELECT COUNT(*) FROM {$table} WHERE {$where_sql}", $params );
		$rows      = $this->rows( "SELECT * FROM {$table} WHERE {$where_sql} ORDER BY id DESC LIMIT %d OFFSET %d", array_merge( $params, array( $per_page, $offset ) ) );

		return array(
			'items' => array_map(
				static function ( array $row ) {
					$row['id']         = (int) $row['id'];
					$row['related_id'] = (int) $row['related_id'];
					$row['error']      = (string) $row['error'];

					return $row;
				},
				$rows
			),
			'total' => $total,
		);
	}

	/**
	 * Number of queued emails per status.
	 *
	 * @since 0.1.0
	 *
	 * @return array<string, int>
	 */
	public function queue_counts(): array {
		$table  = Tables::name( 'email_queue' );
		$counts = array();

		foreach ( $this->rows( "SELECT status, COUNT(*) AS total FROM {$table} GROUP BY status" ) as $row ) {
			$counts[ (string) $row['status'] ] = (int) $row['total'];
		}

		return $counts;
	}

	/**
	 * Deletes log lines older than a number of days.
	 *
	 * @since 0.1.0
	 *
	 * @param int $days Age in days.
	 * @return void
	 */
	public function purge_log( int $days ): void {
		$table = Tables::name( 'email_log' );

		$this->wpdb->query( $this->prepare( "DELETE FROM {$table} WHERE created_at_gmt < %s", array( gmdate( 'Y-m-d H:i:s', time() - max( 1, $days ) * DAY_IN_SECONDS ) ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Casts numeric columns.
	 *
	 * @param array<string, mixed> $row Database row.
	 * @return array<string, mixed>
	 */
	private function hydrate( array $row ): array {
		foreach ( array( 'id', 'related_id', 'attempts' ) as $column ) {
			$row[ $column ] = (int) $row[ $column ];
		}

		foreach ( array( 'body_html', 'body_text', 'last_error', 'sent_at_gmt', 'to_name' ) as $column ) {
			$row[ $column ] = (string) ( $row[ $column ] ?? '' );
		}

		return $row;
	}
}