<?php
/**
 * Email log storage.
 *
 * @package Adminkeep
 */

namespace Adminkeep\Service;

defined( 'ABSPATH' ) || exit;

/*
 * The only thing interpolated into SQL in this file is self::table(), which is $wpdb->prefix
 * followed by a literal — no caller can influence it. Table names cannot be passed as prepare()
 * placeholders, so there is no version of these queries that satisfies the sniff. Every value
 * that does come from outside is placeholdered.
 *
 * phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
 *
 * The schema-change warning is disabled for the same reason: creating and dropping this table is
 * this class's job, and uninstall depends on drop() actually dropping it.
 *
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange
 */

/**
 * One table holding what wp_mail() was asked to send.
 *
 * WHY A TABLE AND NOT A POST TYPE OR A FILE. A log file under wp-content is web-readable on a
 * misconfigured host, and that is how easy-wp-smtp leaked reset links in 2020. A post type would
 * put email into wp_posts, where every plugin that walks posts would meet it. A table nothing
 * else knows about is the smallest surface.
 *
 * Arrays are stored as JSON. Nothing here is ever unserialized: the log holds text that strangers
 * typed into contact forms, and two rival plugins shipped object injection through exactly that.
 */
class EmailLogStore {

	/**
	 * Schema version, bumped when the table changes.
	 */
	const SCHEMA_VERSION = 1;

	/**
	 * Option holding the installed schema version.
	 */
	const VERSION_OPTION = 'adminkeep_email_log_schema';

	/**
	 * The statuses a row can carry. Anything else is coerced to 'unconfirmed'.
	 */
	const STATUSES = array( 'unconfirmed', 'sent', 'failed' );

	/**
	 * Columns query() is allowed to sort by.
	 */
	const SORTABLE = array( 'created_at', 'id' );

	/**
	 * Every column but the body, for lists that show one line per email. A page of twenty
	 * 100 KB bodies is 2 MB pulled from MySQL only to be thrown away. body_head is the body's
	 * first 100 characters, enough for EmailLogPreview::is_html() to recognise a document that
	 * arrived without a Content-Type header; it is read, never printed.
	 */
	const LIST_COLUMNS = 'id, created_at, status, to_email, from_email, subject, headers, attachments, error, LEFT(body, 100) AS body_head';

	/**
	 * Row cap enforced by trim_to().
	 */
	const MAX_ROWS = 10000;

	/**
	 * Default batch size for purge_older_than() and trim_to().
	 */
	const BATCH = 500;

	/**
	 * Column length caps, applied before insert so a hostile form cannot bloat the table.
	 */
	const CAPS = array(
		'to_email'   => 2000,
		'from_email' => 255,
		'subject'    => 255,
		'error'      => 2000,
	);

	/**
	 * Fully-qualified table name.
	 *
	 * @return string
	 */
	public static function table() {
		global $wpdb;

		return $wpdb->prefix . 'adminkeep_email_log';
	}

	/**
	 * Whether the table is installed at the current schema version.
	 *
	 * @return bool
	 */
	public static function installed() {
		return (int) get_option( self::VERSION_OPTION ) === self::SCHEMA_VERSION;
	}

	/**
	 * Create the table if it is missing or out of date.
	 *
	 * @return void
	 */
	public static function maybe_install() {
		if ( self::installed() ) {
			return;
		}

		global $wpdb;

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$table   = self::table();
		$charset = $wpdb->get_charset_collate();

		dbDelta(
			"CREATE TABLE {$table} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				created_at datetime NOT NULL,
				status varchar(20) NOT NULL DEFAULT 'unconfirmed',
				to_email text NOT NULL,
				from_email varchar(255) NOT NULL DEFAULT '',
				subject varchar(255) NOT NULL DEFAULT '',
				body mediumtext NOT NULL,
				headers text NOT NULL,
				attachments text NOT NULL,
				error text NOT NULL,
				PRIMARY KEY  (id),
				KEY created_status (created_at, status)
			) {$charset};"
		);

		update_option( self::VERSION_OPTION, self::SCHEMA_VERSION, false );
	}

	/**
	 * Drop the table entirely. Used by uninstall.
	 *
	 * @return void
	 */
	public static function drop() {
		global $wpdb;

		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$wpdb->query( "DROP TABLE IF EXISTS {$table}" );
		delete_option( self::VERSION_OPTION );
	}

	/**
	 * Record one attempted email.
	 *
	 * @param array $row Keys: status, to_email, from_email, subject, body, headers (array),
	 *                   attachments (array), error, and optionally created_at — a UTC
	 *                   'Y-m-d H:i:s', for an email imported from another log. Anything else
	 *                   there is ignored and the row is stamped now.
	 * @return int New row id, or 0 on failure.
	 */
	public static function insert( array $row ) {
		global $wpdb;

		self::maybe_install();

		$status  = isset( $row['status'] ) && in_array( $row['status'], self::STATUSES, true ) ? $row['status'] : 'unconfirmed';
		$created = isset( $row['created_at'] ) && is_string( $row['created_at'] ) && preg_match( '/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', $row['created_at'] ) ? $row['created_at'] : gmdate( 'Y-m-d H:i:s' );

		$data = array(
			'created_at'  => $created,
			'status'      => $status,
			'to_email'    => self::cap( 'to_email', $row ),
			'from_email'  => self::cap( 'from_email', $row ),
			'subject'     => self::cap( 'subject', $row ),
			'body'        => self::scrub( isset( $row['body'] ) ? $row['body'] : '' ),
			'headers'     => (string) wp_json_encode( self::scrub_all( isset( $row['headers'] ) && is_array( $row['headers'] ) ? $row['headers'] : array() ) ),
			'attachments' => (string) wp_json_encode( array_values( self::scrub_all( isset( $row['attachments'] ) && is_array( $row['attachments'] ) ? $row['attachments'] : array() ) ) ),
			'error'       => self::cap( 'error', $row ),
		);

		$ok = $wpdb->insert( self::table(), $data, array_fill( 0, count( $data ), '%s' ) );

		if ( ! $ok && '' !== $data['body'] ) {
			// Second line of defence. The body is the one value big and strange enough to be
			// refused for a reason scrub() cannot see — a column that is still latin1, say, or an
			// emoji on a table that never made it to utf8mb4 — and the envelope is worth keeping
			// on its own, so try once more without it rather than lose the email from the log.
			$data['body'] = '';
			$ok           = $wpdb->insert( self::table(), $data, array_fill( 0, count( $data ), '%s' ) );
		}

		return $ok ? (int) $wpdb->insert_id : 0;
	}

	/**
	 * Drop bytes that are not valid UTF-8.
	 *
	 * WHY THIS EXISTS. $wpdb->insert() runs every value through strip_invalid_text() and refuses
	 * the whole row if any value changes (wp-includes/class-wpdb.php). One ISO-8859-1 byte from a
	 * contact form on a latin1 page therefore meant the email was not logged at all — no row, not
	 * even the envelope — which is the one outcome a log must not produce quietly.
	 *
	 * wp_check_invalid_utf8() is the scrubber chosen over mb_scrub(): it is WordPress's own, so it
	 * is present wherever the plugin runs, while mbstring is optional. It has two gaps of its own —
	 * it returns the string untouched when blog_charset is not UTF-8, and '' when iconv is missing
	 * — so the result is checked and a plain byte filter finishes the job. A mangled subject is
	 * worth more than no row.
	 *
	 * @param mixed $value Value to store.
	 * @return string
	 */
	private static function scrub( $value ) {
		$value = (string) $value;

		// A /u pattern fails to match at all on the first invalid byte, which makes this a test of
		// the whole string, not just its first character.
		if ( '' === $value || 1 === preg_match( '/^./us', $value ) ) {
			return $value;
		}

		$clean = wp_check_invalid_utf8( $value, true );

		if ( '' === $clean || 1 !== preg_match( '/^./us', $clean ) ) {
			$clean = (string) preg_replace( '/[\x80-\xFF]/', '', $value );
		}

		return $clean;
	}

	/**
	 * Scrub every scalar in a flat array, keeping its keys.
	 *
	 * The headers and attachments columns are JSON, and wp_json_encode() returns false on invalid
	 * UTF-8, which would store an empty array and lose the Cc the privacy tools search.
	 *
	 * @param array $values Header name => value, or attachment names.
	 * @return array
	 */
	private static function scrub_all( array $values ) {
		foreach ( $values as $key => $value ) {
			$values[ $key ] = is_scalar( $value ) ? self::scrub( $value ) : '';
		}

		return $values;
	}

	/**
	 * Scrub a field, then truncate it to its column cap.
	 *
	 * @param string $key Column name, a key of self::CAPS.
	 * @param array  $row Row data being written.
	 * @return string
	 */
	private static function cap( $key, array $row ) {
		$value = self::scrub( isset( $row[ $key ] ) ? $row[ $key ] : '' );

		// mb_strcut keeps a multi-byte character whole; a column cut mid-character is rejected
		// by MySQL in strict mode and the whole row would be lost. It is given valid UTF-8 because
		// scrub() ran first: on invalid input mb_strcut can itself produce a broken sequence.
		return function_exists( 'mb_strcut' ) ? mb_strcut( $value, 0, self::CAPS[ $key ], 'UTF-8' ) : substr( $value, 0, self::CAPS[ $key ] );
	}

	/**
	 * Update the mutable fields of a row: status, from_email, error, and headers.
	 *
	 * headers is deliberately narrower than it looks: the only caller that ever passes it is
	 * EmailLog::record_sender(), backfilling a content-type header the wp_mail_content_type
	 * filter set after the row was already written by insert() -- nothing else may rewrite a
	 * row's headers, and record_sender() itself only fills a gap, never replaces what is there.
	 *
	 * @param int   $id     Row id.
	 * @param array $fields Any of status, from_email, error, headers (array); everything else is
	 *                      ignored.
	 * @return void
	 */
	public static function update( $id, array $fields ) {
		global $wpdb;

		$data = array();

		if ( isset( $fields['status'] ) && in_array( $fields['status'], self::STATUSES, true ) ) {
			$data['status'] = $fields['status'];
		}

		foreach ( array( 'from_email', 'error' ) as $key ) {
			if ( isset( $fields[ $key ] ) ) {
				$data[ $key ] = self::cap( $key, $fields );
			}
		}

		if ( isset( $fields['headers'] ) && is_array( $fields['headers'] ) ) {
			// Scrubbed through the same path insert() uses: this column is JSON, and
			// wp_json_encode() returns false on invalid UTF-8, which would store an empty array.
			$data['headers'] = (string) wp_json_encode( self::scrub_all( $fields['headers'] ) );
		}

		if ( array() === $data || ! self::installed() ) {
			return;
		}

		$wpdb->update( self::table(), $data, array( 'id' => (int) $id ), array_fill( 0, count( $data ), '%s' ), array( '%d' ) );
	}

	/**
	 * Fetch one row by id, with headers and attachments decoded to arrays.
	 *
	 * @param int $id Row id.
	 * @return array|null
	 */
	public static function get( $id ) {
		global $wpdb;

		if ( ! self::installed() ) {
			return null;
		}

		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", (int) $id ), ARRAY_A );

		return is_array( $row ) ? self::decode( $row ) : null;
	}

	/**
	 * Decode a raw row's JSON columns to arrays.
	 *
	 * @param array $row Raw row as returned by $wpdb.
	 * @return array
	 */
	private static function decode( array $row ) {
		foreach ( array( 'headers', 'attachments' ) as $key ) {
			$decoded     = json_decode( (string) $row[ $key ], true );
			$row[ $key ] = is_array( $decoded ) ? $decoded : array();
		}

		return $row;
	}

	/**
	 * WHERE clause and its values for the shared filter arguments.
	 *
	 * @param array $args status, search, since, until.
	 * @return array{0: string, 1: array}
	 */
	private static function where( array $args ) {
		global $wpdb;

		$sql    = array( '1=1' );
		$values = array();

		if ( ! empty( $args['status'] ) && in_array( $args['status'], self::STATUSES, true ) ) {
			$sql[]    = 'status = %s';
			$values[] = $args['status'];
		}

		if ( isset( $args['search'] ) && '' !== (string) $args['search'] ) {
			$like  = '%' . $wpdb->esc_like( (string) $args['search'] ) . '%';
			$sql[] = '( to_email LIKE %s OR from_email LIKE %s OR subject LIKE %s )';
			array_push( $values, $like, $like, $like );
		}

		foreach ( array( 'since' => '>=', 'until' => '<=' ) as $key => $operator ) {
			$time = empty( $args[ $key ] ) ? false : strtotime( (string) $args[ $key ] );

			if ( false !== $time ) {
				$sql[]    = "created_at {$operator} %s";
				$values[] = gmdate( 'Y-m-d H:i:s', $time );
			}
		}

		return array( implode( ' AND ', $sql ), $values );
	}

	/**
	 * Filtered, sorted, paginated rows, with headers and attachments decoded to arrays.
	 *
	 * @param array $args status, search, since, until, orderby, order, per_page, page, with_body (default true).
	 * @return array[]
	 */
	public static function query( array $args = array() ) {
		global $wpdb;

		if ( ! self::installed() ) {
			return array();
		}

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

		$orderby  = isset( $args['orderby'] ) && in_array( $args['orderby'], self::SORTABLE, true ) ? $args['orderby'] : 'id';
		$order    = isset( $args['order'] ) && 'asc' === strtolower( (string) $args['order'] ) ? 'ASC' : 'DESC';
		$per_page = isset( $args['per_page'] ) ? max( 1, min( 500, (int) $args['per_page'] ) ) : 20;
		$page     = isset( $args['page'] ) ? max( 1, (int) $args['page'] ) : 1;

		$values[] = $per_page;
		$values[] = ( $page - 1 ) * $per_page;

		$columns = isset( $args['with_body'] ) && false === $args['with_body'] ? self::LIST_COLUMNS : '*';

		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $where already carries its own %s/%d placeholders from self::where(); the sniff sees only the two literal %d appended here and undercounts against the full $values array, which does hold one value per placeholder.
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT {$columns} FROM {$table} WHERE {$where} ORDER BY {$orderby} {$order}, id {$order} LIMIT %d OFFSET %d", $values ), ARRAY_A );

		return array_map( array( __CLASS__, 'decode' ), is_array( $rows ) ? $rows : array() );
	}

	/**
	 * Count of rows matching the shared filter arguments.
	 *
	 * @param array $args status, search, since, until.
	 * @return int
	 */
	public static function count( array $args = array() ) {
		global $wpdb;

		if ( ! self::installed() ) {
			return 0;
		}

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

		$table = self::table();
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$sql = "SELECT COUNT(*) FROM {$table} WHERE {$where}";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return (int) $wpdb->get_var( array() === $values ? $sql : $wpdb->prepare( $sql, $values ) );
	}

	/**
	 * Delete specific rows by id.
	 *
	 * @param array $ids Row ids; non-numeric and non-existent values are ignored.
	 * @return int Rows actually deleted.
	 */
	public static function delete( array $ids ) {
		global $wpdb;

		$ids = array_values( array_filter( array_map( 'absint', $ids ) ) );

		if ( array() === $ids || ! self::installed() ) {
			return 0;
		}

		$table        = self::table();
		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- $placeholders is built from count( $ids ) '%d' tokens and interpolated before prepare() sees the string, so the placeholders are real; the sniff only reads the source text, where {$placeholders} is an opaque variable, not literal %d characters.
		return (int) $wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE id IN ({$placeholders})", $ids ) );
	}

	/**
	 * Delete every row.
	 *
	 * @return int Rows deleted.
	 */
	public static function delete_all() {
		global $wpdb;

		if ( ! self::installed() ) {
			return 0;
		}

		$table = self::table();

		// DELETE, not TRUNCATE: TRUNCATE is DDL, it commits implicitly and it cannot run against
		// the temporary table the test suite substitutes.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return (int) $wpdb->query( "DELETE FROM {$table}" );
	}

	/**
	 * Rows holding one address, and how far the search got.
	 *
	 * WHERE THE ADDRESS CAN BE. The recipient, the sender, and the Cc, Bcc and Reply-To headers.
	 * On a contact form the stranger is the Reply-To — the To is the site owner — so an erasure
	 * request that only read to_email and from_email answered "nothing found" while the person's
	 * address and the message they typed stayed in the table.
	 *
	 * LIKE narrows, PHP decides: "jo@example.com" is a substring of "banjo@example.com", and an
	 * erasure request must not delete a stranger's email.
	 *
	 * WHAT A CALLER MUST JUDGE "DONE" ON. 'examined', never the number of ids. A page can hold
	 * near misses and real matches together, so a short list of ids says nothing about whether
	 * more rows lie beyond the page. 'last_id' lets a caller that deletes as it goes carry on past
	 * the near misses instead of meeting them again at offset 0 for ever.
	 *
	 * @param string $email    Address.
	 * @param int    $limit    Rows to examine.
	 * @param int    $offset   Rows to skip; for paging that does not delete as it goes.
	 * @param int    $after_id Only rows past this id; for paging that does.
	 * @return array{ids: int[], examined: int, last_id: int}
	 */
	public static function find_by_address( $email, $limit, $offset = 0, $after_id = 0 ) {
		global $wpdb;

		$email = strtolower( trim( (string) $email ) );
		$found = array(
			'ids'      => array(),
			'examined' => 0,
			'last_id'  => (int) $after_id,
		);

		if ( '' === $email || ! self::installed() ) {
			return $found;
		}

		$table = self::table();
		$like  = '%' . $wpdb->esc_like( $email ) . '%';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT id, to_email, from_email, headers FROM {$table} WHERE id > %d AND ( to_email LIKE %s OR from_email LIKE %s OR headers LIKE %s ) ORDER BY id ASC LIMIT %d OFFSET %d", (int) $after_id, $like, $like, $like, (int) $limit, (int) $offset ), ARRAY_A );

		foreach ( (array) $rows as $row ) {
			++$found['examined'];
			$found['last_id'] = (int) $row['id'];

			if ( in_array( $email, self::addresses_in( $row ), true ) ) {
				$found['ids'][] = (int) $row['id'];
			}
		}

		return $found;
	}

	/**
	 * Every address one row holds, lower-cased.
	 *
	 * @param array $row Raw row with to_email, from_email and the headers JSON.
	 * @return string[]
	 */
	private static function addresses_in( array $row ) {
		$text    = $row['to_email'] . ',' . $row['from_email'];
		$headers = json_decode( (string) $row['headers'], true );

		foreach ( array( 'cc', 'bcc', 'reply-to' ) as $name ) {
			if ( is_array( $headers ) && isset( $headers[ $name ] ) && is_scalar( $headers[ $name ] ) ) {
				$text .= ',' . $headers[ $name ];
			}
		}

		return (array) preg_split( '/[\s,;<>]+/', strtolower( $text ) );
	}

	/**
	 * Delete rows older than a cutoff, one batch at a time.
	 *
	 * @param int $days  Age cutoff in days.
	 * @param int $limit Maximum rows to delete in this call.
	 * @return int Rows deleted.
	 */
	public static function purge_older_than( $days, $limit ) {
		global $wpdb;

		if ( ! self::installed() ) {
			return 0;
		}

		$table  = self::table();
		$cutoff = gmdate( 'Y-m-d H:i:s', time() - max( 1, (int) $days ) * DAY_IN_SECONDS );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return (int) $wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE created_at < %s ORDER BY id ASC LIMIT %d", $cutoff, max( 1, (int) $limit ) ) );
	}

	/**
	 * Delete the oldest rows down to a maximum row count, one batch at a time.
	 *
	 * @param int $max_rows Rows to keep.
	 * @param int $limit    Maximum rows to delete in this call.
	 * @return int Rows deleted.
	 */
	public static function trim_to( $max_rows, $limit ) {
		global $wpdb;

		$excess = self::count() - max( 0, (int) $max_rows );

		if ( $excess <= 0 ) {
			return 0;
		}

		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return (int) $wpdb->query( $wpdb->prepare( "DELETE FROM {$table} ORDER BY id ASC LIMIT %d", min( $excess, max( 1, (int) $limit ) ) ) );
	}
}