<?php
/**
 * Reminder rule repository.
 *
 * @package HostKhata
 */

namespace HostKhata\Database\Repositories;

defined( 'ABSPATH' ) || exit;

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

/**
 * Reads and writes reminder rules and the log that keeps a reminder from being sent twice.
 *
 * @since 0.1.0
 */
final class ReminderRepository extends AbstractRepository {

	/**
	 * Columns of the rules table and their formats.
	 */
	const COLUMNS = array(
		'name'            => '%s',
		'rule_type'       => '%s',
		'days_offset'     => '%d',
		'channel'         => '%s',
		'template_key'    => '%s',
		'send_admin_copy' => '%d',
		'status'          => '%s',
		'sort_order'      => '%d',
	);

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

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

	/**
	 * Lists rules.
	 *
	 * @since 0.1.0
	 *
	 * @param string $status Only this status, or an empty string for all.
	 * @return array<int, array<string, mixed>>
	 */
	public function all( string $status = '' ): array {
		$table = Tables::name( 'reminder_rules' );
		$sql   = "SELECT * FROM {$table}" . ( '' !== $status ? ' WHERE status = %s' : '' ) . ' ORDER BY rule_type ASC, days_offset ASC, id ASC LIMIT 200';

		return array_map( array( $this, 'hydrate' ), $this->rows( $sql, '' !== $status ? array( $status ) : array() ) );
	}

	/**
	 * Number of rules.
	 *
	 * @since 0.1.0
	 *
	 * @return int
	 */
	public function count(): int {
		$table = Tables::name( 'reminder_rules' );

		return (int) $this->value( "SELECT COUNT(*) FROM {$table}" );
	}

	/**
	 * Inserts a rule.
	 *
	 * @since 0.1.0
	 *
	 * @param array<string, mixed> $data Rule columns.
	 * @return int New id, or 0.
	 */
	public function insert( array $data ): int {
		$now  = Date::now_gmt();
		$row  = array_intersect_key( $data, self::COLUMNS );
		$row += array(
			'created_at_gmt' => $now,
			'updated_at_gmt' => $now,
		);

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

	/**
	 * Updates a rule.
	 *
	 * @since 0.1.0
	 *
	 * @param int                  $id   Rule 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 );
		$row['updated_at_gmt'] = Date::now_gmt();

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

	/**
	 * Deletes a rule and its log.
	 *
	 * @since 0.1.0
	 *
	 * @param int $id Rule id.
	 * @return bool
	 */
	public function delete( int $id ): bool {
		$this->wpdb->delete( Tables::name( 'reminder_log' ), array( 'rule_id' => $id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

		return false !== $this->wpdb->delete( Tables::name( 'reminder_rules' ), array( 'id' => $id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * Claims a reminder: succeeds once per rule, target and day.
	 *
	 * Uses the unique key of the log table, so two overlapping runs cannot both
	 * send the same reminder.
	 *
	 * @since 0.1.0
	 *
	 * @param int    $rule_id     Rule id.
	 * @param string $target_type `invoice`, `domain` or `service`.
	 * @param int    $target_id   Target id.
	 * @param string $day         Day the reminder is for, `YYYY-MM-DD`.
	 * @return bool True when this call claimed it.
	 */
	public function claim( int $rule_id, string $target_type, int $target_id, string $day ): bool {
		$table = Tables::name( 'reminder_log' );

		$changed = $this->wpdb->query( $this->prepare( "INSERT IGNORE INTO {$table} (rule_id, target_type, target_id, scheduled_for, status, sent_at_gmt) VALUES (%d, %s, %d, %s, 'queued', %s)", array( $rule_id, $target_type, $target_id, $day, Date::now_gmt() ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter

		return 1 === $changed;
	}

	/**
	 * Number of reminders queued for a rule.
	 *
	 * @since 0.1.0
	 *
	 * @param int $rule_id Rule id.
	 * @return int
	 */
	public function sent_count( int $rule_id ): int {
		$table = Tables::name( 'reminder_log' );

		return (int) $this->value( "SELECT COUNT(*) FROM {$table} WHERE rule_id = %d", array( $rule_id ) );
	}

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

		$row['send_admin_copy'] = (bool) $row['send_admin_copy'];

		return $row;
	}
}