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

namespace SmartWP\AdminPowerups\Modules\EmailLog;

use SmartWP\AdminPowerups\Support\Paging;

defined( 'ABSPATH' ) || exit;

/**
 * One row per WooCommerce email attempt in `{prefix}apwc_email_log`: indexed by date, email
 * type, status and related object, so the log screen and an order's history are single indexed
 * queries. Old rows are pruned in batches.
 *
 * @since 1.0.0
 */
final class Store {

	/**
	 * Schema version, stored in the VERSION option once the table exists. 2 adds `sent_by` (who
	 * sent a test email).
	 */
	const DB      = 2;
	const VERSION = 'apwc_email_log_db';

	/**
	 * Table name for this site.
	 *
	 * @since 1.0.0
	 * @return string
	 */
	public static function table() {
		global $wpdb;
		return $wpdb->prefix . 'apwc_email_log';
	}

	/**
	 * Whether the table is ready (checked from an autoloaded option, no query).
	 *
	 * @since 1.0.0
	 * @return bool
	 */
	public static function ready() {
		return (int) get_option( self::VERSION, 0 ) >= self::DB;
	}

	/**
	 * Whether a log table exists, read from the autoloaded options only (never a query, so it's
	 * safe on every request). The version option stays autoloaded while the table exists, with
	 * the power-up on or off.
	 *
	 * @since 1.0.0
	 * @return bool
	 */
	public static function kept() {
		$options = wp_load_alloptions();
		return isset( $options[ self::VERSION ] ) && (int) $options[ self::VERSION ] > 0;
	}

	/**
	 * Create or update the table. Runs when the power-up is switched on, never mid-request on a
	 * send (schema changes commit open transactions).
	 *
	 * @since 1.0.0
	 * @return void
	 */
	public static function install() {
		global $wpdb;
		if ( self::ready() ) {
			return;
		}
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta(
			'CREATE TABLE ' . self::table() . " (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				created_gmt datetime NOT NULL,
				email_id varchar(100) NOT NULL DEFAULT '',
				object_type varchar(20) NOT NULL DEFAULT '',
				object_id bigint(20) unsigned NOT NULL DEFAULT 0,
				recipients text NOT NULL,
				cc text NOT NULL,
				bcc text NOT NULL,
				subject text NOT NULL,
				status varchar(20) NOT NULL DEFAULT 'sent',
				error text NOT NULL,
				attachments text NOT NULL,
				body longtext NULL,
				is_test tinyint(1) NOT NULL DEFAULT 0,
				sent_by bigint(20) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY  (id),
				KEY created_gmt (created_gmt),
				KEY email_id (email_id),
				KEY status (status),
				KEY object (object_type,object_id)
			) {$wpdb->get_charset_collate()};"
		);
		update_option( self::VERSION, self::DB, true );
	}

	/**
	 * Drop the table (Data section, uninstall).
	 *
	 * @since 1.0.0
	 * @return void
	 */
	public static function drop() {
		global $wpdb;
		$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', self::table() ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- our own table, dropped on request (Data section, uninstall).
		delete_option( self::VERSION );
	}

	/**
	 * Add an entry.
	 *
	 * @since 1.0.0
	 * @param array<string, mixed> $row Columns.
	 * @return int Entry ID (0 when not stored).
	 */
	public static function insert( array $row ) {
		global $wpdb;
		if ( ! self::ready() ) {
			return 0;
		}
		$defaults = array(
			'created_gmt' => gmdate( 'Y-m-d H:i:s' ),
			'email_id'    => '',
			'object_type' => '',
			'object_id'   => 0,
			'recipients'  => '',
			'cc'          => '',
			'bcc'         => '',
			'subject'     => '',
			'status'      => 'sent',
			'error'       => '',
			'attachments' => '',
			'body'        => null,
			'is_test'     => 0,
			'sent_by'     => 0,
		);
		$row      = array_intersect_key( array_merge( $defaults, $row ), $defaults );
		$ok       = $wpdb->insert( self::table(), $row ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- custom log table, no WordPress API.
		return $ok ? (int) $wpdb->insert_id : 0;
	}

	/**
	 * One entry.
	 *
	 * @since 1.0.0
	 * @param int $id Entry ID.
	 * @return object|null
	 */
	public static function get( $id ) {
		global $wpdb;
		if ( ! self::ready() ) {
			return null;
		}
		$row = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', self::table(), $id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom log table; always fresh.
		return is_object( $row ) ? $row : null;
	}

	/**
	 * Entries matching filters, newest first, without bodies.
	 *
	 * @since 1.0.0
	 * @param array<string, mixed> $args `search`, `email_id`, `status`, `object_type`, `object_id`, `page`, `per_page`.
	 * @return array{rows: object[], total: int}
	 */
	public static function query( array $args ) {
		global $wpdb;
		if ( ! self::ready() ) {
			return array(
				'rows'  => array(),
				'total' => 0,
			);
		}
		// Conditions hold only literal SQL with placeholders; values go in $params.
		$where  = array( '1=1' );
		$params = array( self::table() );
		if ( ! empty( $args['email_id'] ) ) {
			$where[]  = 'email_id = %s';
			$params[] = (string) $args['email_id'];
		}
		if ( ! empty( $args['status'] ) ) {
			if ( 'test' === $args['status'] ) {
				$where[] = 'is_test = 1';
			} else {
				$where[]  = 'status = %s AND is_test = 0';
				$params[] = (string) $args['status'];
			}
		}
		if ( ! empty( $args['object_type'] ) ) {
			$where[]  = 'object_type = %s AND object_id = %d';
			$params[] = (string) $args['object_type'];
			$params[] = (int) ( $args['object_id'] ?? 0 );
		}
		$search = trim( (string) ( $args['search'] ?? '' ) );
		if ( '' !== $search ) {
			$like   = '%' . $wpdb->esc_like( $search ) . '%';
			$parts  = 'recipients LIKE %s OR cc LIKE %s OR bcc LIKE %s OR subject LIKE %s';
			$params = array_merge( $params, array( $like, $like, $like, $like ) );
			if ( ctype_digit( ltrim( $search, '#' ) ) ) {
				$parts   .= " OR ( object_type = 'order' AND object_id = %d )";
				$params[] = (int) ltrim( $search, '#' );
			}
			$where[] = '( ' . $parts . ' )';
		}
		$per   = max( 1, min( 200, (int) ( $args['per_page'] ?? 50 ) ) );
		$page  = Paging::page( $args['page'] ?? 1, $per );
		$sql   = implode( ' AND ', $where );
		$count = "SELECT COUNT(*) FROM %i WHERE {$sql}";
		$list  = "SELECT id, created_gmt, email_id, object_type, object_id, recipients, cc, bcc, subject, status, error, attachments, is_test FROM %i WHERE {$sql} ORDER BY id DESC LIMIT %d OFFSET %d";
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- custom log table; $count and $list hold only literal SQL and placeholders (values are in $params), prepared here.
		$rows  = $wpdb->get_results( $wpdb->prepare( $list, array_merge( $params, array( $per, ( $page - 1 ) * $per ) ) ) );
		$total = (int) $wpdb->get_var( $wpdb->prepare( $count, $params ) );
		// phpcs:enable
		return array(
			'rows'  => (array) $rows,
			'total' => $total,
		);
	}

	/**
	 * Count all entries.
	 *
	 * @since 1.0.0
	 * @return int
	 */
	public static function count() {
		global $wpdb;
		return self::ready() ? (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', self::table() ) ) : 0; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom log table; always fresh.
	}

	/**
	 * Delete entries older than N days, oldest first, one batch.
	 *
	 * Walks the created_gmt index (no sort of the expired rows and their bodies), so each batch
	 * stays fast however big the backlog is.
	 *
	 * @since 1.0.0
	 * @param int $days  Keep this many days.
	 * @param int $batch Rows per delete.
	 * @return int Rows deleted.
	 */
	public static function prune( $days, $batch = 1000 ) {
		global $wpdb;
		if ( ! self::ready() ) {
			return 0;
		}
		$cutoff = gmdate( 'Y-m-d H:i:s', time() - max( 1, (int) $days ) * DAY_IN_SECONDS );
		$table  = self::table();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom log table.
		$deleted = $wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE created_gmt < %s ORDER BY created_gmt LIMIT %d', $table, $cutoff, max( 1, (int) $batch ) ) );
		return max( 0, (int) $deleted );
		// phpcs:enable
	}

	/**
	 * Entries about a person (privacy export and erase), newest first: sent to their address, or
	 * linked to one of their orders or their account (admin emails such as "New order" carry the
	 * customer's details but go to the store).
	 *
	 * LIKE only narrows the rows; the exact address check keeps jimbob@x.com out of bob@x.com's
	 * export and erase. The page is counted after that check (reading candidates in id-ordered
	 * chunks), so look-alike addresses never shorten or end a page early.
	 *
	 * @since 1.0.0
	 * @param string               $email   Address.
	 * @param int                  $limit   Most entries to return.
	 * @param int                  $offset  Matches to skip first.
	 * @param array<string, int[]> $objects Linked objects that count as theirs: type (`order`,
	 *                                      `user`) => IDs.
	 * @return object[]
	 */
	public static function for_address( $email, $limit = 100, $offset = 0, array $objects = array() ) {
		global $wpdb;
		$limit  = max( 1, (int) $limit );
		$offset = max( 0, (int) $offset );
		if ( ! self::ready() || ! is_email( $email ) ) {
			return array();
		}
		$like   = '%' . $wpdb->esc_like( $email ) . '%';
		$wanted = strtolower( trim( $email ) );
		$linked = array();
		$where  = 'recipients LIKE %s OR cc LIKE %s OR bcc LIKE %s';
		$params = array( self::table(), $like, $like, $like );
		foreach ( $objects as $type => $ids ) {
			$ids = array_values( array_unique( array_filter( array_map( 'intval', (array) $ids ) ) ) );
			if ( ! $ids ) {
				continue;
			}
			$linked[ (string) $type ] = array_flip( $ids );
			$where                   .= ' OR ( object_type = %s AND object_id IN ( ' . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ' ) )';
			$params                   = array_merge( $params, array( (string) $type ), $ids );
		}
		$chunk  = 500;
		$before = PHP_INT_MAX;
		$found  = array();
		do {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- custom log table; privacy requests need fresh rows. $where holds only literal SQL and placeholders; every value goes through prepare().
			$rows = (array) $wpdb->get_results( $wpdb->prepare( "SELECT id, created_gmt, email_id, object_type, object_id, subject, recipients, cc, bcc FROM %i WHERE ( {$where} ) AND id < %d ORDER BY id DESC LIMIT %d", array_merge( $params, array( $before, $chunk ) ) ) );
			foreach ( $rows as $row ) {
				$before = (int) $row->id;
				if ( ! isset( $linked[ (string) $row->object_type ][ (int) $row->object_id ] ) && ! self::sent_to( $row, $wanted ) ) {
					continue;
				}
				if ( $offset > 0 ) {
					--$offset;
					continue;
				}
				$found[] = $row;
				if ( count( $found ) >= $limit ) {
					return $found;
				}
			}
			$more = count( $rows ) === $chunk;
		} while ( $more );
		return $found;
	}

	/**
	 * Kept content of some entries (privacy export), in one query.
	 *
	 * @since 1.0.0
	 * @param int[] $ids Entry IDs.
	 * @return array<int, string> Entry ID => content (entries without content left out).
	 */
	public static function bodies( array $ids ) {
		global $wpdb;
		$ids = array_values( array_filter( array_map( 'intval', $ids ) ) );
		if ( ! $ids || ! self::ready() ) {
			return array();
		}
		$in = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- custom log table; $in is only %d placeholders.
		$rows = (array) $wpdb->get_results( $wpdb->prepare( "SELECT id, body FROM %i WHERE id IN ( {$in} ) AND body IS NOT NULL", array_merge( array( self::table() ), $ids ) ) );
		$out  = array();
		foreach ( $rows as $row ) {
			$out[ (int) $row->id ] = (string) $row->body;
		}
		return $out;
	}

	/**
	 * Whether an entry was sent to this address (exactly, in To, Cc or Bcc).
	 *
	 * @since 1.0.0
	 * @param object $row   Entry with recipients, cc, bcc.
	 * @param string $email Address.
	 * @return bool
	 */
	public static function received( $row, $email ) {
		return self::sent_to( $row, strtolower( trim( (string) $email ) ) );
	}

	/**
	 * Whether an entry's To, Cc or Bcc holds exactly this address.
	 *
	 * @param object $row    Entry with recipients, cc, bcc.
	 * @param string $wanted Lower-case address.
	 * @return bool
	 */
	private static function sent_to( $row, $wanted ) {
		foreach ( (array) preg_split( '/[,;]/', $row->recipients . ',' . $row->cc . ',' . $row->bcc ) as $address ) {
			$address = (string) $address;
			// Addresses may be "Name <a@b.c>".
			if ( preg_match( '/<([^>]+)>/', $address, $m ) ) {
				$address = $m[1];
			}
			if ( strtolower( trim( $address ) ) === $wanted ) {
				return true;
			}
		}
		return false;
	}

	/**
	 * Delete entries by ID.
	 *
	 * @since 1.0.0
	 * @param int[] $ids IDs.
	 * @return void
	 */
	public static function delete( array $ids ) {
		global $wpdb;
		$ids = array_filter( array_map( 'intval', $ids ) );
		if ( $ids && self::ready() ) {
			$in = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- custom log table; $in is only %d placeholders.
			$wpdb->query( $wpdb->prepare( "DELETE FROM %i WHERE id IN ( {$in} )", array_merge( array( self::table() ), array_values( $ids ) ) ) );
		}
	}
}