<?php
/**
 * Reads and writes stored entries.
 *
 * @package TFlexForms
 */

declare( strict_types = 1 );

namespace TFlexForms;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * All queries use $wpdb->insert()/update()/delete() or $wpdb->prepare(),
 * with %i for table names, so no value or identifier is ever concatenated
 * into SQL.
 *
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom tables; results are request-specific.
 */
final class Entry_Store {

	public const STATUSES = array( 'unread', 'read', 'spam' );

	/** Suspected spam: kept for review, hidden from the default list, never mailed. */
	public const SPAM = 'spam';

	/**
	 * Saves an entry with its values (files are added with add_files()).
	 *
	 * @param Form                 $form   Form.
	 * @param array<string,string> $values Display values, field name => text.
	 * @param array<string,mixed>  $meta   ip, user_id, page_url, mail_sent, status (unread|spam).
	 * @return int Entry ID, or 0 on failure.
	 */
	public static function create( Form $form, array $values, array $meta ): int {
		global $wpdb;

		$ok = $wpdb->insert(
			Database::table( 'entries' ),
			array(
				'form_id'    => $form->id,
				'status'     => self::SPAM === ( $meta['status'] ?? '' ) ? self::SPAM : 'unread',
				'mail_sent'  => ! empty( $meta['mail_sent'] ) ? 1 : 0,
				'user_id'    => (int) ( $meta['user_id'] ?? 0 ),
				'ip'         => substr( (string) ( $meta['ip'] ?? '' ), 0, 45 ),
				'page_url'   => substr( (string) ( $meta['page_url'] ?? '' ), 0, 2048 ),
				'created_at' => current_time( 'mysql', true ),
			),
			array( '%d', '%s', '%d', '%d', '%s', '%s', '%s' )
		);
		if ( false === $ok ) {
			return 0;
		}
		$entry_id = (int) $wpdb->insert_id;

		foreach ( $values as $name => $value ) {
			$wpdb->insert(
				Database::table( 'entry_values' ),
				array(
					'entry_id'    => $entry_id,
					'field_name'  => substr( (string) $name, 0, 64 ),
					'field_value' => (string) $value,
				),
				array( '%d', '%s', '%s' )
			);
		}
		return $entry_id;
	}

	/**
	 * Records the outcome of the notification email. A message that could not
	 * be sent is kept (without attachments, which live with the entry) so it
	 * can be resent later; a sent one is cleared.
	 *
	 * @param int                      $entry_id Entry ID.
	 * @param bool                     $sent     Whether the notification was sent.
	 * @param array<string,mixed>|null $message  Notification from Mailer::notification(), or null.
	 */
	public static function set_mail_result( int $entry_id, bool $sent, ?array $message = null ): void {
		global $wpdb;
		if ( null !== $message ) {
			unset( $message['attachments'] );
		}
		$wpdb->update(
			Database::table( 'entries' ),
			array(
				'mail_sent'    => $sent ? 1 : 0,
				'mail_message' => $sent || null === $message ? null : (string) wp_json_encode( $message ),
			),
			array( 'id' => $entry_id ),
			array( '%d', '%s' ),
			array( '%d' )
		);
	}

	/** Attempts kept in an entry's mail log. */
	public const MAIL_LOG_SIZE = 20;

	/**
	 * Records one attempt to send an entry's notification.
	 *
	 * @param int    $entry_id Entry ID.
	 * @param bool   $sent     Whether the mail server accepted it.
	 * @param string $error    Error reported by the mailer ('' when unknown or sent).
	 * @param string $source   submit (with the submission), retry (automatic) or resend (by a user).
	 */
	public static function log_mail_attempt( int $entry_id, bool $sent, string $error, string $source ): void {
		global $wpdb;
		$entry = $wpdb->get_row( $wpdb->prepare( 'SELECT mail_log FROM %i WHERE id = %d', Database::table( 'entries' ), $entry_id ) );
		if ( ! is_object( $entry ) ) {
			return;
		}
		$log   = self::mail_log( $entry );
		$log[] = array(
			'time'   => time(),
			'sent'   => $sent,
			'error'  => $sent ? '' : $error,
			'source' => $source,
		);
		$log   = array_slice( $log, -self::MAIL_LOG_SIZE );
		$wpdb->update( Database::table( 'entries' ), array( 'mail_log' => (string) wp_json_encode( $log ) ), array( 'id' => $entry_id ), array( '%s' ), array( '%d' ) );
	}

	/**
	 * An entry's notification attempts, oldest first.
	 *
	 * @param object $entry Entry row.
	 * @return array<int,array{time:int,sent:bool,error:string,source:string}>
	 */
	public static function mail_log( object $entry ): array {
		$log = json_decode( (string) ( $entry->mail_log ?? '' ), true );
		$out = array();
		foreach ( is_array( $log ) ? $log : array() as $item ) {
			if ( is_array( $item ) ) {
				$out[] = array(
					'time'   => (int) ( $item['time'] ?? 0 ),
					'sent'   => ! empty( $item['sent'] ),
					'error'  => (string) ( $item['error'] ?? '' ),
					'source' => (string) ( $item['source'] ?? '' ),
				);
			}
		}
		return $out;
	}

	/**
	 * The unsent notification kept for an entry, or null.
	 *
	 * @param object $entry Entry row.
	 * @return array{to:string[],subject:string,body:string,headers:string[]}|null
	 */
	public static function pending_mail( object $entry ): ?array {
		$message = json_decode( (string) ( $entry->mail_message ?? '' ), true );
		if ( ! is_array( $message ) || ! isset( $message['to'], $message['subject'], $message['body'], $message['headers'] ) ) {
			return null;
		}
		return array(
			'to'      => array_map( 'strval', (array) $message['to'] ),
			'subject' => (string) $message['subject'],
			'body'    => (string) $message['body'],
			'headers' => array_map( 'strval', (array) $message['headers'] ),
		);
	}

	/**
	 * Adds file rows to an entry.
	 *
	 * @param int                                 $entry_id Entry ID.
	 * @param array<int,array<string,string|int>> $files    Rows from Uploads::keep_for_entry().
	 */
	public static function add_files( int $entry_id, array $files ): void {
		global $wpdb;
		foreach ( $files as $file ) {
			$wpdb->insert(
				Database::table( 'entry_files' ),
				array(
					'entry_id'      => $entry_id,
					'field_name'    => substr( (string) $file['field'], 0, 64 ),
					'original_name' => substr( (string) $file['original_name'], 0, 255 ),
					'stored_name'   => substr( (string) $file['stored_name'], 0, 255 ),
					'mime'          => substr( (string) $file['mime'], 0, 100 ),
					'size'          => (int) $file['size'],
				),
				array( '%d', '%s', '%s', '%s', '%s', '%d' )
			);
		}
	}

	/**
	 * Human-readable values to store: labels (not piped values), "Other: …"
	 * text, acceptance as Accepted/Not accepted. Quiz answers and fields hidden
	 * by conditions are left out.
	 *
	 * @param Form                $form     Form.
	 * @param array<string,mixed> $values   Validated values.
	 * @param string[]            $inactive Fields hidden by conditions.
	 * @return array<string,string>
	 */
	public static function display_values( Form $form, array $values, array $inactive = array() ): array {
		$out = array();
		foreach ( $form->input_fields() as $name => $field ) {
			if ( 'quiz' === $field->type || in_array( $name, $inactive, true ) ) {
				continue;
			}
			$value = $values[ $name ] ?? '';
			if ( 'acceptance' === $field->type ) {
				$out[ $name ] = '1' === $value ? __( 'Accepted', 'flexsolutions-form-builder' ) : __( 'Not accepted', 'flexsolutions-form-builder' );
				continue;
			}
			$items = array();
			$free  = (string) ( $values[ Validator::free_text_key( $name ) ] ?? '' );
			foreach ( array_map( 'strval', (array) $value ) as $label ) {
				$items[] = '' !== $free && $label === $field->free_text_choice() ? $label . ': ' . $free : $label;
			}
			$out[ $name ] = implode( ', ', $items );
		}
		return $out;
	}

	/**
	 * Lists entries.
	 *
	 * @param array<string,mixed> $args form_id, status (spam is only listed when asked for), search, tag, order (ASC|DESC), per_page, page.
	 * @return array{items:array<int,object>,total:int}
	 */
	public static function query( array $args = array() ): array {
		global $wpdb;
		$entries = Database::table( 'entries' );
		$values  = Database::table( 'entry_values' );

		$where  = array( '1=1' );
		$params = array( $entries );

		if ( ! empty( $args['form_id'] ) ) {
			$where[]  = 'e.form_id = %d';
			$params[] = (int) $args['form_id'];
		}
		if ( ! empty( $args['status'] ) && in_array( $args['status'], self::STATUSES, true ) ) {
			$where[]  = 'e.status = %s';
			$params[] = $args['status'];
		} else {
			$where[]  = 'e.status <> %s';
			$params[] = self::SPAM;
		}
		if ( isset( $args['search'] ) && '' !== $args['search'] ) {
			$where[]  = 'EXISTS ( SELECT 1 FROM %i v WHERE v.entry_id = e.id AND v.field_value LIKE %s )';
			$params[] = $values;
			$params[] = '%' . $wpdb->esc_like( (string) $args['search'] ) . '%';
		}
		if ( isset( $args['tag'] ) && '' !== $args['tag'] ) {
			$where[]  = 'EXISTS ( SELECT 1 FROM %i t WHERE t.entry_id = e.id AND t.tag = %s )';
			$params[] = Database::table( 'entry_tags' );
			$params[] = (string) $args['tag'];
		}
		$where_sql = implode( ' AND ', $where );
		$order     = 'ASC' === strtoupper( (string) ( $args['order'] ?? 'DESC' ) ) ? 'ASC' : 'DESC';
		$per_page  = max( 1, min( 500, (int) ( $args['per_page'] ?? 20 ) ) );
		$page      = max( 1, (int) ( $args['page'] ?? 1 ) );

		// $where_sql only contains the fixed fragments above; every value is a placeholder.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$total = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i e WHERE {$where_sql}", $params ) );

		$params[] = $per_page;
		$params[] = ( $page - 1 ) * $per_page;
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- See above; $order is one of two literals; $params is the full argument list.
		$items = $wpdb->get_results( $wpdb->prepare( "SELECT e.* FROM %i e WHERE {$where_sql} ORDER BY e.created_at {$order}, e.id {$order} LIMIT %d OFFSET %d", $params ) );

		return array(
			'items' => is_array( $items ) ? $items : array(),
			'total' => $total,
		);
	}

	/**
	 * One entry with values and files, or null.
	 *
	 * @param int $id Entry ID.
	 */
	public static function get( int $id ): ?object {
		global $wpdb;
		$entry = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', Database::table( 'entries' ), $id ) );
		if ( ! is_object( $entry ) ) {
			return null;
		}
		$entry->values = self::values_for( array( $id ) )[ $id ] ?? array();
		$entry->files  = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE entry_id = %d ORDER BY id', Database::table( 'entry_files' ), $id ) );
		return $entry;
	}

	/**
	 * Values for several entries: entry ID => [ field => value ].
	 *
	 * @param int[] $ids Entry IDs.
	 * @return array<int,array<string,string>>
	 */
	public static function values_for( array $ids ): array {
		global $wpdb;
		$ids = array_values( array_filter( array_map( 'intval', $ids ) ) );
		if ( array() === $ids ) {
			return array();
		}
		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
		// $placeholders is a list of %d markers; all values are bound by prepare().
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT entry_id, field_name, field_value FROM %i WHERE entry_id IN ({$placeholders}) ORDER BY id", array_merge( array( Database::table( 'entry_values' ) ), $ids ) ) );
		$out  = array();
		foreach ( (array) $rows as $row ) {
			$out[ (int) $row->entry_id ][ (string) $row->field_name ] = (string) $row->field_value;
		}
		return $out;
	}

	/**
	 * Changes an entry's submitted values and records what changed (who, when,
	 * old and new value) in its change history. Fields not given are left as
	 * they are; a value only counts as changed when the text differs.
	 *
	 * @param int                  $id      Entry ID.
	 * @param array<string,string> $values  Field name => new value.
	 * @param int                  $user_id Who made the change.
	 * @return array<string,array{from:string,to:string}> The changes made.
	 */
	public static function update_values( int $id, array $values, int $user_id ): array {
		global $wpdb;
		$table   = Database::table( 'entry_values' );
		$current = self::values_for( array( $id ) )[ $id ] ?? array();
		$changes = array();
		foreach ( $values as $name => $value ) {
			$name  = substr( (string) $name, 0, 64 );
			$value = (string) $value;
			$old   = $current[ $name ] ?? null;
			if ( $old === $value || ( null === $old && '' === $value ) ) {
				continue;
			}
			if ( null === $old ) {
				$wpdb->insert(
					$table,
					array(
						'entry_id'    => $id,
						'field_name'  => $name,
						'field_value' => $value,
					),
					array( '%d', '%s', '%s' )
				);
			} else {
				$wpdb->update(
					$table,
					array( 'field_value' => $value ),
					array(
						'entry_id'   => $id,
						'field_name' => $name,
					),
					array( '%s' ),
					array( '%d', '%s' )
				);
			}
			$changes[ $name ] = array(
				'from' => (string) $old,
				'to'   => $value,
			);
		}
		if ( array() !== $changes ) {
			Entry_Notes::add_edit( $id, $changes, $user_id );
			/**
			 * Fires after someone changed a stored entry's values.
			 *
			 * @param int                                         $id      Entry ID.
			 * @param array<string,array{from:string,to:string}> $changes Field name => old and new value.
			 * @param int                                         $user_id Who made the change.
			 */
			do_action( 'tflexforms_entry_updated', $id, $changes, $user_id );
		}
		return $changes;
	}

	/**
	 * Sets the status of entries.
	 *
	 * @param int[]  $ids    Entry IDs.
	 * @param string $status unread|read|spam.
	 */
	public static function set_status( array $ids, string $status ): void {
		global $wpdb;
		if ( ! in_array( $status, self::STATUSES, true ) ) {
			return;
		}
		foreach ( array_filter( array_map( 'intval', $ids ) ) as $id ) {
			$wpdb->update( Database::table( 'entries' ), array( 'status' => $status ), array( 'id' => $id ), array( '%s' ), array( '%d' ) );
		}
	}

	/**
	 * Deletes entries, their values, notes, tags and files (rows and disk).
	 *
	 * @param int[] $ids Entry IDs.
	 * @return int Number deleted.
	 */
	public static function delete( array $ids ): int {
		global $wpdb;
		$count = 0;
		foreach ( array_filter( array_map( 'intval', $ids ) ) as $id ) {
			Uploads::delete_entry_files( $id );
			$wpdb->delete( Database::table( 'entry_files' ), array( 'entry_id' => $id ), array( '%d' ) );
			$wpdb->delete( Database::table( 'entry_values' ), array( 'entry_id' => $id ), array( '%d' ) );
			Entry_Notes::delete_for_entry( $id );
			Entry_Tags::delete_for_entry( $id );
			$count += (int) $wpdb->delete( Database::table( 'entries' ), array( 'id' => $id ), array( '%d' ) );
		}
		return $count;
	}

	/**
	 * Number of unread entries (for the menu badge).
	 */
	public static function count_unread(): int {
		global $wpdb;
		return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE status = %s', Database::table( 'entries' ), 'unread' ) );
	}

	/**
	 * IDs of entries older than a number of days (retention clean-up).
	 *
	 * @param int $days Age in days.
	 * @param int $limit Batch size.
	 * @return int[]
	 */
	public static function ids_older_than( int $days, int $limit = 200 ): array {
		global $wpdb;
		$cutoff = gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS );
		return array_map( 'intval', (array) $wpdb->get_col( $wpdb->prepare( 'SELECT id FROM %i WHERE created_at < %s ORDER BY id LIMIT %d', Database::table( 'entries' ), $cutoff, $limit ) ) );
	}

	/**
	 * IDs of entries containing an exact value (case-insensitive), e.g. an email.
	 *
	 * @param string $value Value.
	 * @param int    $limit Max results.
	 * @param int    $offset Offset.
	 * @return int[]
	 */
	public static function ids_with_value( string $value, int $limit = 50, int $offset = 0 ): array {
		global $wpdb;
		return array_map(
			'intval',
			(array) $wpdb->get_col(
				$wpdb->prepare(
					'SELECT DISTINCT entry_id FROM %i WHERE LOWER(field_value) = LOWER(%s) ORDER BY entry_id LIMIT %d OFFSET %d',
					Database::table( 'entry_values' ),
					$value,
					$limit,
					$offset
				)
			)
		);
	}
}