<?php
/**
 * Logger query and export helpers.
 *
 * @package MailDoctor
 */

namespace MailDoctor;

use MailDoctor\Helpers\Settings;

trait Logger_Query_Trait {


	/**
	 * Fetch payload for specific log.
	 *
	 * @param int $id Log ID.
	 * @return array<string, mixed>
	 */
	public static function get_payload( int $id ): array {
		global $wpdb;

		$table = self::table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$row = $wpdb->get_var(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
				"SELECT payload FROM {$table} WHERE id = %d",
				$id
			)
		);

		$decoded = json_decode( (string) $row, true );

		return is_array( $decoded ) ? $decoded : array();
	}

	/**
	 * Retrieve a raw log entry by identifier.
	 *
	 * @param int $id Log identifier.
	 * @return array<string, mixed>|null
	 */
	public static function get_entry( int $id ): ?array {
		if ( $id <= 0 ) {
			return null;
		}

		global $wpdb;

		$table = self::table_name();

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

		return $row ?: null;
	}

	/**
	 * Retrieve the latest log entry for a given event.
	 *
	 * @param string $event Event slug.
	 * @return array<string, mixed>|null
	 */
	public static function latest_by_event( string $event ): ?array {
		$event = sanitize_text_field( $event );

		if ( '' === $event ) {
			return null;
		}

		global $wpdb;

		$table = self::table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$row = $wpdb->get_row(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
				"SELECT * FROM {$table} WHERE event = %s ORDER BY id DESC LIMIT 1",
				$event
			),
			ARRAY_A
		);

		return $row ?: null;
	}

	/**
	 * Find recent log entry matching idempotency constraints.
	 *
	 * @param string $key     Deterministic idempotency key.
	 * @param int    $bucket  Time bucket identifier.
	 * @return array<string, mixed>|null
	 */
	public static function find_idempotent( string $key, int $bucket ): ?array {
		if ( empty( $key ) ) {
			return null;
		}

		global $wpdb;

		$table = self::table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$row = $wpdb->get_row(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
				"SELECT * FROM {$table} WHERE idempotency_key = %s AND idempotency_bucket = %d AND status IN ('queued','sent','skipped') ORDER BY id DESC LIMIT 1",
				$key,
				$bucket
			),
			ARRAY_A
		);

		return $row ?: null;
	}

	/**
	 * Retrieve logs for listing.
	 *
	 * @param array<string, mixed> $args Query args.
	 * @return array<string, mixed>
	 */
	public static function query( array $args ): array {
		global $wpdb;

		$defaults = array(
			'status'           => null,
			'event'            => null,
			'search'           => null,
			'email_class'      => null,
			'provider_pattern' => null,
			'recipient_domain' => null,
			'has_attachments'  => null,
			'date_from'        => null,
			'date_to'          => null,
			'paged'            => 1,
			'per_page'         => 20,
		);

		$args = wp_parse_args( $args, $defaults );

		$where   = array();
		$prepare = array();

		if ( $args['status'] ) {
			$where[]   = 'status = %s';
			$prepare[] = $args['status'];
		}

		if ( $args['event'] ) {
			$where[]   = 'event = %s';
			$prepare[] = $args['event'];
		}

		if ( $args['search'] ) {
			$search_term = sanitize_text_field( $args['search'] );
			$like        = '%' . $wpdb->esc_like( $search_term ) . '%';
			$conditions  = array( 'to_email LIKE %s', 'subject LIKE %s' );
			$prepare[]   = $like;
			$prepare[]   = $like;

			if ( Settings::hash_recipients_enabled() && is_email( $search_term ) ) {
				$hashed = Settings::hash_email( strtolower( $search_term ) );
				if ( $hashed ) {
					$conditions[] = 'to_email LIKE %s';
					$prepare[]    = '%' . $wpdb->esc_like( $hashed ) . '%';
				}
			}

			$where[] = '(' . implode( ' OR ', $conditions ) . ')';
		}

		if ( ! empty( $args['email_class'] ) ) {
			$email_class = sanitize_text_field( $args['email_class'] );
			$where[]     = 'payload LIKE %s';
			$prepare[]   = '%' . $wpdb->esc_like( '"email_class":"' . $email_class . '"' ) . '%';
		}

		if ( ! empty( $args['provider_pattern'] ) ) {
			$pattern = preg_replace( '/[^0-9A-Za-z\-_.^$*+?\[\]{}|()\\ ]/', '', $args['provider_pattern'] );
			if ( $pattern ) {
				$where[]   = 'provider_code REGEXP %s';
				$prepare[] = $pattern;
			}
		}

		if ( ! empty( $args['recipient_domain'] ) ) {
			$domain    = sanitize_text_field( $args['recipient_domain'] );
			$where[]   = 'to_email LIKE %s';
			$prepare[] = '%' . $wpdb->esc_like( '@' . ltrim( $domain, '@' ) ) . '%';
		}

		if ( null !== $args['has_attachments'] ) {
			if ( $args['has_attachments'] ) {
				$where[] = "payload LIKE '%attachments_meta%'";
			} else {
				$where[] = "payload NOT LIKE '%attachments_meta%'";
			}
		}

		if ( $args['date_from'] ) {
			$date_from = self::normalize_date_argument( (string) $args['date_from'], '00:00:00' );
			if ( $date_from ) {
				$where[]   = 'created_at >= %s';
				$prepare[] = $date_from;
			}
		}

		if ( $args['date_to'] ) {
			$date_to = self::normalize_date_argument( (string) $args['date_to'], '23:59:59' );
			if ( $date_to ) {
				$where[]   = 'created_at <= %s';
				$prepare[] = $date_to;
			}
		}

		$where_sql = '';
		if ( ! empty( $where ) ) {
			$where_sql = 'WHERE ' . implode( ' AND ', $where );
		}

		$allowed_tables = array(
			self::table_name(),
			self::archive_table_name(),
		);

		$table = self::table_name();
		if ( ! empty( $args['table'] ) && in_array( $args['table'], $allowed_tables, true ) ) {
			$table = $args['table'];
		}

		$page     = max( 1, (int) $args['paged'] );
		$per_page = max( 1, (int) $args['per_page'] );
		$offset   = ( $page - 1 ) * $per_page;

		$prepare[] = $per_page;
		$prepare[] = $offset;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$items = $wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Dynamic where clause placeholders.
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
				"SELECT SQL_CALC_FOUND_ROWS * FROM {$table} {$where_sql} ORDER BY id DESC LIMIT %d OFFSET %d",
				$prepare
			),
			ARRAY_A
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$total = (int) $wpdb->get_var( 'SELECT FOUND_ROWS()' );

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

	/**
	 * Stream log export.
	 *
	 * @param array<string, mixed> $args Query args.
	 * @return void
	 */
	public static function export( array $args ): void {
		$format = isset( $args['format'] ) ? strtolower( sanitize_key( (string) $args['format'] ) ) : 'csv';
		if ( ! in_array( $format, array( 'csv', 'json' ), true ) ) {
			$format = 'csv';
		}

		$include_archived = ! empty( $args['include_archived'] );
		$filename         = 'maildoctor-export-' . gmdate( 'Ymd-His' ) . ( 'json' === $format ? '.json' : '.csv' );

		nocache_headers();

		if ( 'json' === $format ) {
			header( 'Content-Type: application/json; charset=utf-8' );
		} else {
			header( 'Content-Type: text/csv; charset=utf-8' );
		}

		header( 'Content-Disposition: attachment; filename=' . $filename );

		$handle = null;

		if ( 'csv' === $format ) {
			$handle = fopen( 'php://output', 'w' );

			if ( ! $handle ) {
				return;
			}

			$columns = array(
				'ID',
				'Created (UTC)',
				'Event',
				'Order ID',
				'Status',
				'Recipient(s)',
				'Subject',
				'Provider Code',
				'Transport',
				'Profile',
				'Error',
				'Duration (ms)',
				'Attempts',
				'Next Attempt (UTC)',
				'Manual Trigger',
				'Email Class',
			);

			fputcsv( $handle, $columns );
		} else {
			echo '[';
		}

		$paged     = 1;
		$per_page  = 500;
		$base_args = wp_parse_args(
			$args,
			array(
				'per_page' => $per_page,
				'paged'    => $paged,
			)
		);

		$sources = array( self::table_name() );

		if ( $include_archived ) {
			$sources[] = self::archive_table_name();
		}

		$first_json_row = true;

		foreach ( $sources as $source_table ) {
			$paged = 1;

			do {
				$batch_args             = $base_args;
				$batch_args['paged']    = $paged;
				$batch_args['per_page'] = $per_page;
				$batch_args['table']    = $source_table;

				unset( $batch_args['format'], $batch_args['include_archived'] );

				$result = self::query( $batch_args );
				$items  = $result['items'];

				if ( empty( $items ) ) {
					break;
				}

				foreach ( $items as $row ) {
					$export_row   = self::prepare_export_row( $row, $source_table === self::archive_table_name() );
					$csv_exported = $export_row;

					if ( 'csv' === $format && $handle ) {
						unset( $csv_exported['archived'] );
						fputcsv( $handle, array_values( $csv_exported ) );
						continue;
					}

					if ( ! $first_json_row ) {
						echo ',';
					}

					echo wp_json_encode( $export_row );
					$first_json_row = false;
				}

				flush();
				++$paged;
			} while ( count( $items ) === $per_page );
		}

		if ( 'csv' === $format && $handle ) {
			// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose
			fclose( $handle );
		} else {
			echo ']';
		}

		exit;
	}

	/**
	 * Normalize a database row for export.
	 *
	 * @param array<string, mixed> $row         Raw row from the database.
	 * @param bool                 $is_archived Whether the row originated from the archive table.
	 * @return array<string, mixed>
	 */
	protected static function prepare_export_row( array $row, bool $is_archived ): array {
		$payload = array();

		if ( isset( $row['payload'] ) && $row['payload'] ) {
			$decoded = json_decode( (string) $row['payload'], true );
			if ( is_array( $decoded ) ) {
				$payload = $decoded;
			}
		} elseif ( ! empty( $row['id'] ) ) {
			$payload = self::get_payload( (int) $row['id'] );
		}

		$retry           = isset( $payload['retry'] ) && is_array( $payload['retry'] ) ? $payload['retry'] : array();
		$meta            = isset( $payload['meta'] ) && is_array( $payload['meta'] ) ? $payload['meta'] : array();
		$attempts        = isset( $retry['attempts'] ) ? (int) $retry['attempts'] : 0;
		$next            = isset( $retry['next_attempt_gmt'] ) ? (string) $retry['next_attempt_gmt'] : '';
		$manual          = ! empty( $retry['manual_trigger'] );
		$email_cls       = isset( $meta['email_class'] ) ? (string) $meta['email_class'] : '';
		$transport_key   = isset( $meta['transport'] ) ? sanitize_key( (string) $meta['transport'] ) : '';
		$transport_label = isset( $meta['transport_label'] ) ? sanitize_text_field( (string) $meta['transport_label'] ) : '';
		$smtp_host       = isset( $meta['smtp_host'] ) ? sanitize_text_field( (string) $meta['smtp_host'] ) : '';
		$profile_slug    = isset( $meta['smtp_profile'] ) ? sanitize_text_field( (string) $meta['smtp_profile'] ) : '';
		$profile_label   = isset( $meta['smtp_profile_label'] ) ? sanitize_text_field( (string) $meta['smtp_profile_label'] ) : '';

		if ( '' === $transport_label && '' !== $transport_key ) {
			$transport_label = Settings::transport_label( $transport_key );
		}

		$transport_display = $transport_label;

		if ( $transport_key && 'smtp' === $transport_key && $smtp_host ) {
			$transport_display = $transport_display
				? sprintf(
					/* translators: 1: delivery method label, 2: SMTP host */
					__( '%1$s via %2$s', 'mail-doctor' ),
					$transport_display,
					$smtp_host
				)
				: $smtp_host;
		}

		if ( '' === $transport_display && '' !== $transport_key ) {
			$transport_display = Settings::transport_label( $transport_key );
		}

		$profile_display = $profile_label;

		if ( '' === $profile_display && '' !== $profile_slug ) {
			$profile_display = $profile_slug;
		}

		return array(
			'id'             => isset( $row['id'] ) ? (int) $row['id'] : 0,
			'created_at'     => isset( $row['created_at'] ) ? (string) $row['created_at'] : '',
			'event'          => isset( $row['event'] ) ? sanitize_text_field( (string) $row['event'] ) : '',
			'order_id'       => isset( $row['order_id'] ) ? (int) $row['order_id'] : 0,
			'status'         => isset( $row['status'] ) ? sanitize_key( (string) $row['status'] ) : '',
			'recipients'     => isset( $row['to_email'] ) ? sanitize_text_field( (string) $row['to_email'] ) : '',
			'subject'        => isset( $row['subject'] ) ? sanitize_text_field( (string) $row['subject'] ) : '',
			'provider_code'  => isset( $row['provider_code'] ) ? sanitize_text_field( (string) $row['provider_code'] ) : '',
			'transport'      => $transport_display,
			'profile'        => $profile_display,
			'error'          => isset( $row['error'] ) ? sanitize_textarea_field( (string) $row['error'] ) : '',
			'duration_ms'    => isset( $row['duration_ms'] ) ? (int) $row['duration_ms'] : 0,
			'attempts'       => $attempts,
			'next_attempt'   => $next,
			'manual_trigger' => $manual ? 'yes' : 'no',
			'email_class'    => sanitize_text_field( $email_cls ),
			'archived'       => $is_archived,
		);
	}

	/**
	 * Locate logs containing a specific recipient (plain or hashed).
	 *
	 * @param string $email Email address.
	 * @param int    $page  Page number.
	 * @param int    $per_page Items per page.
	 * @return array{items: array<int, array<string,mixed>>, total: int}
	 */
	public static function find_logs_by_recipient( string $email, int $page = 1, int $per_page = 50 ): array {
		$email = strtolower( sanitize_email( $email ) );

		if ( '' === $email ) {
			return array(
				'items' => array(),
				'total' => 0,
			);
		}

		global $wpdb;

		$table    = self::table_name();
		$page     = max( 1, $page );
		$per_page = max( 1, $per_page );
		$offset   = ( $page - 1 ) * $per_page;

		$terms = array( '%' . $wpdb->esc_like( $email ) . '%' );
		$hash  = Settings::hash_email( $email );

		if ( $hash ) {
			$terms[] = '%' . $wpdb->esc_like( $hash ) . '%';
		}

		$clauses = array();
		$params  = array();

		foreach ( $terms as $term ) {
			$clauses[] = 'to_email LIKE %s';
			$params[]  = $term;
			$clauses[] = 'payload LIKE %s';
			$params[]  = $term;
		}

		if ( empty( $clauses ) ) {
			return array(
				'items' => array(),
				'total' => 0,
			);
		}

		$where_sql = '(' . implode( ' OR ', $clauses ) . ')';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$items = $wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Dynamic where clause placeholders.
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
				"SELECT SQL_CALC_FOUND_ROWS * FROM {$table} WHERE {$where_sql} ORDER BY created_at DESC LIMIT %d OFFSET %d",
				array_merge( $params, array( $per_page, $offset ) )
			),
			ARRAY_A
		);
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$total = (int) $wpdb->get_var( 'SELECT FOUND_ROWS()' );

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