<?php
/**
 * Data access for entries.
 *
 * @package ALPEG\Forms
 */

namespace ALPEG\Forms;

defined( 'ABSPATH' ) || exit;

/**
 * All database access in one place, exclusively via $wpdb->prepare().
 */
class Entries {

	/**
	 * Column formats for inserts.
	 *
	 * @var array
	 */
	private static $formats = array(
		'form_id'     => '%s',
		'form_title'  => '%s',
		'post_id'     => '%d',
		'page_url'    => '%s',
		'data'        => '%s',
		'email'       => '%s',
		'ip'          => '%s',
		'user_agent'  => '%s',
		'user_id'     => '%d',
		'consent'     => '%d',
		'status'      => '%s',
		'mail_status' => '%s',
		'mail_error'  => '%s',
		'import_ref'  => '%s',
		'created_at'  => '%s',
	);

	/**
	 * Store a new entry.
	 *
	 * @param array $row Column values.
	 * @return int|false New ID or false.
	 */
	public static function insert( array $row ) {
		global $wpdb;
		$defaults = array(
			'form_id'     => '',
			'form_title'  => '',
			'post_id'     => 0,
			'page_url'    => '',
			'data'        => array(),
			'email'       => '',
			'ip'          => '',
			'user_agent'  => '',
			'user_id'     => 0,
			'consent'     => 0,
			'status'      => 'unread',
			'mail_status' => '',
			'mail_error'  => '',
			'import_ref'  => '',
			'created_at'  => current_time( 'mysql', true ),
		);
		$row         = array_intersect_key( wp_parse_args( $row, $defaults ), self::$formats );
		$row['data'] = wp_json_encode( $row['data'], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES );

		$formats = array();
		foreach ( array_keys( $row ) as $col ) {
			$formats[] = self::$formats[ $col ];
		}

		$ok = $wpdb->insert( Installer::table(), $row, $formats ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom entries table, written through wpdb with explicit formats.
		return $ok ? (int) $wpdb->insert_id : false;
	}

	/**
	 * Load one entry.
	 *
	 * @param int $id ID.
	 * @return object|null
	 */
	public static function get( $id ) {
		global $wpdb;
		$table = Installer::table();
		$row   = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $table, $id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Custom entries table; always read fresh for deletion/export permissions.
		return $row ? self::hydrate( $row ) : null;
	}

	/**
	 * Query entries.
	 *
	 * @param array $args form_id, status, search, orderby, order, per_page, page, before, email, ids, mail_status, after.
	 * @return object[]
	 */
	public static function query( array $args = array() ) {
		global $wpdb;
		$args = wp_parse_args(
			$args,
			array(
				'per_page' => 20,
				'page'     => 1,
				'orderby'  => 'created_at',
				'order'    => 'DESC',
			)
		);

		list( $where, $params ) = self::where( $args );

		$orderby = in_array( $args['orderby'], array( 'created_at', 'form_title', 'status', 'id' ), true ) ? $args['orderby'] : 'created_at';
		$order   = 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC';
		$table   = Installer::table();
		$sql     = "SELECT * FROM %i {$where} ORDER BY {$orderby} {$order}, id {$order}";
		array_unshift( $params, $table );

		if ( $args['per_page'] > 0 ) {
			$sql     .= ' LIMIT %d OFFSET %d';
			$params[] = (int) $args['per_page'];
			$params[] = max( 0, ( (int) $args['page'] - 1 ) * (int) $args['per_page'] );
		}

		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table; where() binds values, ordering is allowlisted, identifier uses %i.
		return array_map( array( __CLASS__, 'hydrate' ), (array) $rows );
	}

	/**
	 * Count matching entries.
	 *
	 * @param array $args See query().
	 * @return int
	 */
	public static function count( array $args = array() ) {
		global $wpdb;
		list( $where, $params ) = self::where( $args );
		$table = Installer::table();
		$sql   = "SELECT COUNT(*) FROM %i {$where}";
		array_unshift( $params, $table );
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table; where() binds all values and table identifier uses %i.
	}

	/**
	 * Build the WHERE clause.
	 *
	 * @param array $args Filters.
	 * @return array [string $where, array $params]
	 */
	private static function where( array $args ) {
		global $wpdb;
		$clauses = array();
		$params  = array();

		foreach ( array( 'form_id', 'status', 'email', 'mail_status' ) as $col ) {
			if ( ! empty( $args[ $col ] ) ) {
				$clauses[] = "{$col} = %s";
				$params[]  = $args[ $col ];
			}
		}
		if ( ! empty( $args['before'] ) ) {
			$clauses[] = 'created_at < %s';
			$params[]  = $args['before'];
		}
		if ( ! empty( $args['after'] ) ) {
			$clauses[] = 'created_at >= %s';
			$params[]  = $args['after'];
		}
		if ( ! empty( $args['ids'] ) ) {
			$ids       = array_map( 'absint', (array) $args['ids'] );
			$clauses[] = 'id IN (' . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ')';
			$params    = array_merge( $params, $ids );
		}
		if ( isset( $args['search'] ) && '' !== $args['search'] ) {
			$like      = '%' . $wpdb->esc_like( $args['search'] ) . '%';
			$clauses[] = '(data LIKE %s OR email LIKE %s OR form_title LIKE %s)';
			array_push( $params, $like, $like, $like );
		}

		return array( $clauses ? 'WHERE ' . implode( ' AND ', $clauses ) : '', $params );
	}

	/**
	 * All forms that already have entries (for the filter).
	 *
	 * @return array form_id => form_title
	 */
	public static function forms() {
		global $wpdb;
		$table = Installer::table();
		$rows  = $wpdb->get_results( $wpdb->prepare( 'SELECT form_id, MAX(form_title) AS form_title, MAX(created_at) AS last FROM %i GROUP BY form_id ORDER BY last DESC', $table ) ); // phpcs:ignore WordPress.DB -- Custom table; uncached to reflect imports and deletions immediately.
		$out   = array();
		foreach ( (array) $rows as $r ) {
			$out[ $r->form_id ] = '' !== $r->form_title ? $r->form_title : $r->form_id;
		}
		return $out;
	}

	/**
	 * Set read status.
	 *
	 * @param int[]  $ids    IDs.
	 * @param string $status unread | read.
	 */
	public static function set_status( array $ids, $status ) {
		global $wpdb;
		$ids = array_filter( array_map( 'absint', $ids ) );
		if ( ! $ids || ! in_array( $status, array( 'unread', 'read' ), true ) ) {
			return;
		}
		$table        = Installer::table();
		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
		$wpdb->query( $wpdb->prepare( "UPDATE %i SET status = %s WHERE id IN ({$placeholders})", array_merge( array( $table, $status ), $ids ) ) ); // phpcs:ignore WordPress.DB -- Custom table; generated placeholders bind integer IDs.
	}

	/**
	 * Store the delivery status of the notification mail.
	 *
	 * @param int    $id     Entry ID.
	 * @param string $status sent | failed | none.
	 * @param string $error  Error message.
	 */
	public static function set_mail_status( $id, $status, $error = '' ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Custom entries table; wpdb binds update and where values, no cache is maintained.
		$wpdb->update(
			Installer::table(),
			array(
				'mail_status' => $status,
				'mail_error'  => substr( (string) $error, 0, 255 ),
			),
			array( 'id' => (int) $id ),
			array( '%s', '%s' ),
			array( '%d' )
		); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Custom table; wpdb binds every update value and where clause.
	}

	/**
	 * Delete entries including their uploaded files.
	 *
	 * @param int[] $ids IDs.
	 * @return int Number of deleted rows.
	 */
	public static function delete( array $ids ) {
		global $wpdb;
		$ids = array_values( array_filter( array_map( 'absint', $ids ) ) );
		if ( ! $ids ) {
			return 0;
		}
		foreach ( self::query( array( 'ids' => $ids, 'per_page' => 0 ) ) as $row ) {
			Files::delete_for_entry( $row );
		}
		$table        = Installer::table();
		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
		return (int) $wpdb->query( $wpdb->prepare( "DELETE FROM %i WHERE id IN ({$placeholders})", array_merge( array( $table ), $ids ) ) ); // phpcs:ignore WordPress.DB -- Custom table; generated placeholders bind integer IDs.
	}

	/**
	 * Delete everything older than X days (retention period).
	 *
	 * @param int $days Days.
	 * @return int Number of deleted rows.
	 */
	public static function delete_older_than( $days ) {
		global $wpdb;
		$days = (int) $days;
		if ( $days <= 0 ) {
			return 0;
		}
		$cutoff = gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS );
		$table  = Installer::table();
		$total  = 0;
		do {
			$ids    = $wpdb->get_col( $wpdb->prepare( 'SELECT id FROM %i WHERE created_at < %s LIMIT 200', $table, $cutoff ) ); // phpcs:ignore WordPress.DB -- Custom table; current rows required while deleting in batches.
			$total += $ids ? self::delete( $ids ) : 0;
		} while ( count( $ids ) === 200 );
		return $total;
	}

	/**
	 * Import references already stored (to skip duplicates when importing old submissions).
	 *
	 * @param string $prefix Reference prefix, e.g. "flamingo:".
	 * @return array<string,bool>
	 */
	public static function import_refs( $prefix ) {
		global $wpdb;
		$table = Installer::table();
		$refs  = $wpdb->get_col( $wpdb->prepare( 'SELECT import_ref FROM %i WHERE import_ref LIKE %s', $table, $wpdb->esc_like( $prefix ) . '%' ) ); // phpcs:ignore WordPress.DB -- Custom table; fresh references prevent reimporting existing records.
		return array_fill_keys( (array) $refs, true );
	}

	/**
	 * Decode JSON data.
	 *
	 * @param object $row Database row.
	 * @return object
	 */
	private static function hydrate( $row ) {
		$data      = json_decode( (string) $row->data, true );
		$row->data = is_array( $data ) ? $data : array();
		$row->id   = (int) $row->id;
		if ( ! isset( $row->mail_status ) ) {
			$row->mail_status = '';
			$row->mail_error  = '';
		}
		return $row;
	}
}