<?php
/**
 * Storage of form entries (F-FORMS-03).
 *
 * @package ForensixDigital
 */

declare(strict_types=1);

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Our own tables; entries are read right after being written.

namespace ForensixDigital\Apps\Forms;

defined( 'ABSPATH' ) || exit;

use ForensixDigital\Core\Schema;

/**
 * Entries of all forms. Each entry keeps the label next to the value, so renaming or deleting a
 * field later does not change what was received.
 */
final class EntryStore {

	/**
	 * Saves an entry.
	 *
	 * @param int            $form_id    Form id.
	 * @param FormDefinition $definition Definition the values were checked against.
	 * @param array          $values     Clean values by field id.
	 * @phpstan-param array<string, string> $values
	 * @return int Entry id.
	 */
	public function add( int $form_id, FormDefinition $definition, array $values ): int {
		global $wpdb;
		$rows  = array();
		$email = '';
		foreach ( $definition->fields as $field ) {
			if ( FormDefinition::is_layout( $field['type'] ) ) {
				continue;
			}
			$value  = $values[ $field['id'] ] ?? '';
			$rows[] = array(
				'id'    => $field['id'],
				'label' => $field['label'],
				'value' => $value,
			);
			if ( '' === $email && 'email' === $field['type'] && '' !== $value ) {
				$email = mb_substr( $value, 0, 190 );
			}
		}
		$wpdb->insert(
			Schema::table( FormStore::ENTRIES ),
			array(
				'form_id'     => $form_id,
				'data'        => (string) wp_json_encode( $rows ),
				'mail_status' => FormStore::MAIL_NONE,
				'email'       => $email,
				'created_at'  => current_time( 'mysql', true ),
			)
		);
		return (int) $wpdb->insert_id;
	}

	/**
	 * Records the result of sending the notification.
	 *
	 * @param int    $id     Entry id.
	 * @param string $status FormStore::MAIL_SENT or MAIL_FAILED.
	 * @param string $error  Error message, if any.
	 */
	public function set_mail( int $id, string $status, string $error = '' ): void {
		global $wpdb;
		$wpdb->update(
			Schema::table( FormStore::ENTRIES ),
			array(
				'mail_status' => $status,
				'mail_error'  => mb_substr( $error, 0, 500 ),
			),
			array( 'id' => $id )
		);
	}

	/**
	 * Marks an entry read or unread.
	 *
	 * @param int  $id   Entry id.
	 * @param bool $read Whether it is read.
	 */
	public function set_read( int $id, bool $read ): void {
		global $wpdb;
		$wpdb->update( Schema::table( FormStore::ENTRIES ), array( 'is_read' => $read ? 1 : 0 ), array( 'id' => $id ) );
	}

	/**
	 * Deletes an entry.
	 *
	 * @param int $id Entry id.
	 */
	public function delete( int $id ): void {
		global $wpdb;
		$wpdb->delete( Schema::table( FormStore::ENTRIES ), array( 'id' => $id ) );

		/**
		 * Fires after an entry was deleted, whatever the reason (manual, retention, privacy erase).
		 * Add-ons that keep data for an entry, such as uploaded files, remove it here.
		 *
		 * @param int $id Entry id.
		 */
		do_action( 'forensix_form_entry_deleted', $id );
	}

	/**
	 * One entry.
	 *
	 * @param int $id Entry id.
	 * @return array<string, mixed>|null
	 */
	public function find( int $id ): ?array {
		global $wpdb;
		$row = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', Schema::table( FormStore::ENTRIES ), $id ), ARRAY_A );
		return is_array( $row ) ? self::shape( $row ) : null;
	}

	/**
	 * A page of entries, newest first.
	 *
	 * @param int    $form_id  Form id, 0 for all forms.
	 * @param string $search   Text to look for in the values and the email, empty for none.
	 * @param string $mail     Only entries with this mail status, empty for all.
	 * @param int    $page     Page, from 1.
	 * @param int    $per_page Entries per page.
	 * @return array{items: array<int, array<string, mixed>>, total: int}
	 */
	public function query( int $form_id = 0, string $search = '', string $mail = '', int $page = 1, int $per_page = 20 ): array {
		global $wpdb;
		$table = Schema::table( FormStore::ENTRIES );
		$where = array( '1=1' );
		$args  = array();
		if ( $form_id > 0 ) {
			$where[] = 'form_id = %d';
			$args[]  = $form_id;
		}
		if ( '' !== $search ) {
			$like    = '%' . $wpdb->esc_like( $search ) . '%';
			$where[] = '(data LIKE %s OR email LIKE %s)';
			$args[]  = $like;
			$args[]  = $like;
		}
		if ( '' !== $mail ) {
			$where[] = 'mail_status = %s';
			$args[]  = $mail;
		}
		$clause = implode( ' AND ', $where );

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- The clause only contains fixed fragments with placeholders.
		$total = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE {$clause}", array_merge( array( $table ), $args ) ) );
		$rows  = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM %i WHERE {$clause} ORDER BY id DESC LIMIT %d OFFSET %d",
				array_merge( array( $table ), $args, array( max( 1, $per_page ), max( 0, ( $page - 1 ) * $per_page ) ) )
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber

		return array(
			'items' => array_map( array( self::class, 'shape' ), is_array( $rows ) ? $rows : array() ),
			'total' => $total,
		);
	}

	/**
	 * Number of unread entries.
	 */
	public function unread(): int {
		global $wpdb;
		return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE is_read = 0', Schema::table( FormStore::ENTRIES ) ) );
	}

	/**
	 * Deletes entries older than a number of days.
	 *
	 * @param int $form_id Form id.
	 * @param int $days    Age in days; 0 deletes nothing.
	 * @return int Number of deleted entries.
	 */
	public function delete_older_than( int $form_id, int $days ): int {
		global $wpdb;
		if ( $days < 1 ) {
			return 0;
		}
		$cutoff = gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS );
		$ids    = $wpdb->get_col( $wpdb->prepare( 'SELECT id FROM %i WHERE form_id = %d AND created_at < %s ORDER BY id LIMIT 1000', Schema::table( FormStore::ENTRIES ), $form_id, $cutoff ) );
		foreach ( is_array( $ids ) ? $ids : array() as $id ) {
			$this->delete( (int) $id );
		}
		return count( is_array( $ids ) ? $ids : array() );
	}

	/**
	 * Ids of the entries that carry an email address (for the WordPress privacy tools).
	 *
	 * @param string $email  Address.
	 * @param int    $page   Page, from 1.
	 * @param int    $number Entries per page.
	 * @return int[]
	 */
	public function ids_by_email( string $email, int $page = 1, int $number = 50 ): array {
		global $wpdb;
		$ids = $wpdb->get_col( $wpdb->prepare( 'SELECT id FROM %i WHERE email = %s ORDER BY id LIMIT %d OFFSET %d', Schema::table( FormStore::ENTRIES ), $email, $number, max( 0, ( $page - 1 ) * $number ) ) );
		return array_map( 'intval', is_array( $ids ) ? $ids : array() );
	}

	/**
	 * Entries whose mail failed and are still waiting for attention.
	 */
	public function failed_mail(): int {
		global $wpdb;
		return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE mail_status = %s', Schema::table( FormStore::ENTRIES ), FormStore::MAIL_FAILED ) );
	}

	/**
	 * Row into a result with the data decoded.
	 *
	 * @param array $row Table row.
	 * @phpstan-param array<string, mixed> $row
	 * @return array<string, mixed>
	 */
	private static function shape( array $row ): array {
		$data = json_decode( (string) $row['data'], true );
		return array(
			'id'          => (int) $row['id'],
			'form_id'     => (int) $row['form_id'],
			'data'        => is_array( $data ) ? $data : array(),
			'is_read'     => (bool) $row['is_read'],
			'mail_status' => (string) $row['mail_status'],
			'mail_error'  => (string) ( $row['mail_error'] ?? '' ),
			'email'       => (string) $row['email'],
			'created_at'  => (string) $row['created_at'],
		);
	}
}