<?php
/**
 * Mail event repository.
 *
 * @package SubmissionInbox
 */

namespace SUBIN\MailLog;

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Repository methods read/write Submission Inbox custom mail event tables directly; WordPress has no native API for these records.
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table names are plugin-owned $wpdb->prefix values, and dynamic clauses are built from sanitized allowlists/placeholders in this repository.

/**
 * Reads and writes mail event records.
 */
class MailEventRepository {
	const STATUS_SENT    = 'sent';
	const STATUS_FAILED  = 'failed';
	const STATUS_PENDING = 'pending';
	const STATUS_UNKNOWN = 'unknown';
	const STATUS_NO_EMAIL_GENERATED = 'no_email_generated';

	/**
	 * Create pending mail event.
	 *
	 * @param array<string,mixed> $data Event data.
	 * @return int
	 */
	public function create_pending( array $data ) {
		global $wpdb;

		$table = $wpdb->prefix . 'subin_mail_events';
		$now   = current_time( 'mysql' );

		$inserted = $wpdb->insert(
			$table,
			array(
				'lead_id'       => ! empty( $data['lead_id'] ) ? absint( $data['lead_id'] ) : null,
				'source'        => sanitize_text_field( $data['source'] ?? 'unknown' ),
				'to_email'      => sanitize_textarea_field( $data['to_email'] ?? '' ),
				'from_email'    => sanitize_email( $data['from_email'] ?? '' ),
				'subject'       => sanitize_text_field( $data['subject'] ?? '' ),
				'message_hash'  => sanitize_text_field( $data['message_hash'] ?? '' ),
				'status'        => 'pending',
				'error_message' => null,
				'created_at'    => $now,
				'updated_at'    => $now,
			),
			array( '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )
		);

		return $inserted ? absint( $wpdb->insert_id ) : 0;
	}

	/**
	 * Create an event for a message that was not generated.
	 *
	 * @param array<string,mixed> $data Event data.
	 * @return int
	 */
	public function create_no_email_generated( array $data ) {
		global $wpdb;

		$table = $wpdb->prefix . 'subin_mail_events';
		$now   = current_time( 'mysql' );

		$inserted = $wpdb->insert(
			$table,
			array(
				'lead_id'        => ! empty( $data['lead_id'] ) ? absint( $data['lead_id'] ) : null,
				'source'         => sanitize_text_field( $data['source'] ?? 'unknown' ),
				'to_email'       => sanitize_textarea_field( $data['to_email'] ?? '' ),
				'from_email'     => '',
				'subject'        => sanitize_text_field( $data['subject'] ?? '' ),
				'message_hash'   => '',
				'status'         => self::STATUS_NO_EMAIL_GENERATED,
				'error_message'  => sanitize_textarea_field( $data['error_message'] ?? '' ),
				'created_at'     => $now,
				'updated_at'     => $now,
			),
			array( '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )
		);

		return $inserted ? absint( $wpdb->insert_id ) : 0;
	}

	/**
	 * Mark event sent.
	 *
	 * @param int $event_id Event id.
	 * @return bool
	 */
	public function mark_sent( $event_id ) {
		return $this->update_status( $event_id, 'sent', '' );
	}

	/**
	 * Mark event failed.
	 *
	 * @param int    $event_id Event id.
	 * @param string $error Error message.
	 * @return bool
	 */
	public function mark_failed( $event_id, $error ) {
		return $this->update_status( $event_id, 'failed', $error );
	}

	/**
	 * Get event by id.
	 *
	 * @param int $event_id Event id.
	 * @return object|null
	 */
	public function get( $event_id ) {
		global $wpdb;

		$table = $wpdb->prefix . 'subin_mail_events';

		return $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", absint( $event_id ) ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);
	}

	/**
	 * Get events for lead.
	 *
	 * @param int $lead_id Lead id.
	 * @return array<int,object>
	 */
	public function get_by_lead_id( $lead_id ) {
		global $wpdb;

		$table = $wpdb->prefix . 'subin_mail_events';

		return $wpdb->get_results(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE lead_id = %d ORDER BY created_at DESC, id DESC", absint( $lead_id ) ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);
	}

	/**
	 * Get paginated email events.
	 *
	 * @param array<string,mixed> $args Query args.
	 * @return array<int,object>
	 */
	public function get_paginated_events( array $args ) {
		global $wpdb;

		$table      = $wpdb->prefix . 'subin_mail_events';
		$lead_table = $wpdb->prefix . 'subin_leads';
		$where      = $this->build_where( $args );
		$limit      = max( 1, absint( $args['per_page'] ?? 20 ) );
		$offset     = max( 0, absint( $args['offset'] ?? 0 ) );
		$sql        = "SELECT {$table}.*, leads.id AS linked_lead_id, leads.provider AS lead_provider, leads.form_name AS lead_form_name
			FROM {$table}
			LEFT JOIN {$lead_table} leads ON leads.id = {$table}.lead_id
			{$where['sql']}
			ORDER BY {$table}.created_at DESC, {$table}.id DESC
			LIMIT %d OFFSET %d";
		$params  = array_merge( $where['params'], array( $limit, $offset ) );

		return $wpdb->get_results( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * Count email events.
	 *
	 * @param array<string,mixed> $filters Filters.
	 * @return int
	 */
	public function count_events( array $filters = array() ) {
		global $wpdb;

		$table      = $wpdb->prefix . 'subin_mail_events';
		$lead_table = $wpdb->prefix . 'subin_leads';
		$where      = $this->build_where( $filters );
		$sql        = "SELECT COUNT(*)
			FROM {$table}
			LEFT JOIN {$lead_table} leads ON leads.id = {$table}.lead_id
			{$where['sql']}";

		if ( ! empty( $where['params'] ) ) {
			return absint( $wpdb->get_var( $wpdb->prepare( $sql, $where['params'] ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		}

		return absint( $wpdb->get_var( $sql ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * Get distinct sources.
	 *
	 * @return array<int,string>
	 */
	public function get_distinct_sources() {
		global $wpdb;

		$table = $wpdb->prefix . 'subin_mail_events';

		return array_map(
			'sanitize_text_field',
			$wpdb->get_col( "SELECT DISTINCT source FROM {$table} WHERE source IS NOT NULL AND source != '' ORDER BY source ASC" ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);
	}

	/**
	 * Get email events for CSV export.
	 *
	 * @param array<string,mixed> $filters Filters.
	 * @param int                 $limit Export limit.
	 * @return array<int,object>
	 */
	public function get_events_for_export( array $filters, $limit = 5000 ) {
		global $wpdb;

		$table      = $wpdb->prefix . 'subin_mail_events';
		$lead_table = $wpdb->prefix . 'subin_leads';
		$where      = $this->build_where( $filters );
		$limit      = max( 1, absint( $limit ) );
		$sql        = "SELECT {$table}.*, leads.id AS linked_lead_id, leads.provider AS lead_provider, leads.form_name AS lead_form_name
			FROM {$table}
			LEFT JOIN {$lead_table} leads ON leads.id = {$table}.lead_id
			{$where['sql']}
			ORDER BY {$table}.created_at DESC, {$table}.id DESC
			LIMIT %d";
		$params  = array_merge( $where['params'], array( $limit ) );

		return $wpdb->get_results( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * Count events for lead.
	 *
	 * @param int $lead_id Lead id.
	 * @return int
	 */
	public function count_by_lead_id( $lead_id ) {
		global $wpdb;

		$table = $wpdb->prefix . 'subin_mail_events';

		return absint(
			$wpdb->get_var(
				$wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE lead_id = %d", absint( $lead_id ) ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			)
		);
	}

	/**
	 * Get related events for lead.
	 *
	 * @param int $lead_id Lead id.
	 * @return array<int,object>
	 */
	public function get_related_events_for_lead( $lead_id ) {
		return $this->get_by_lead_id( $lead_id );
	}

	/**
	 * Attach recent unlinked events for a source to a lead.
	 *
	 * @param int               $lead_id Lead id.
	 * @param string            $source Event source.
	 * @param string            $created_after Earliest event datetime.
	 * @param array<int,string> $recipients Optional recipient emails.
	 * @return int|false
	 */
	public function attach_recent_unlinked_events( $lead_id, $source, $created_after = '', array $recipients = array() ) {
		global $wpdb;

		$lead_id = absint( $lead_id );
		$source  = sanitize_key( $source );
		if ( $lead_id <= 0 || '' === $source ) {
			return false;
		}

		$table  = $wpdb->prefix . 'subin_mail_events';
		$where  = array( '(lead_id IS NULL OR lead_id = 0)', 'source = %s' );
		$params = array( $source );

		if ( '' !== $created_after ) {
			$where[]  = 'created_at >= %s';
			$params[] = sanitize_text_field( $created_after );
		}

		$recipients = array_values( array_filter( array_map( 'sanitize_email', $recipients ) ) );
		if ( ! empty( $recipients ) ) {
			$recipient_where = array();
			foreach ( $recipients as $recipient ) {
				$recipient_where[] = 'to_email LIKE %s';
				$params[]          = '%' . $wpdb->esc_like( $recipient ) . '%';
			}
			$where[] = '(' . implode( ' OR ', $recipient_where ) . ')';
		}

		$params    = array_merge( array( current_time( 'mysql' ), $lead_id ), $params );
		$where_sql = implode( ' AND ', $where );
		$query     = "UPDATE {$table}
			SET updated_at = %s, lead_id = %d
			WHERE {$where_sql}";

		return $wpdb->query(
			$wpdb->prepare( $query, ...$params ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared
		);
	}

	/**
	 * Detach mail events from a lead while preserving email log history.
	 *
	 * @param int $lead_id Lead id.
	 * @return bool
	 */
	public function detach_lead( $lead_id ) {
		global $wpdb;

		$table = $wpdb->prefix . 'subin_mail_events';

		return false !== $wpdb->query(
			$wpdb->prepare(
				"UPDATE {$table} SET lead_id = NULL, updated_at = %s WHERE lead_id = %d",
				current_time( 'mysql' ),
				absint( $lead_id )
			) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);
	}

	/**
	 * Update event status.
	 *
	 * @param int    $event_id Event id.
	 * @param string $status Status.
	 * @param string $error Error message.
	 * @return bool
	 */
	private function update_status( $event_id, $status, $error ) {
		global $wpdb;

		$table = $wpdb->prefix . 'subin_mail_events';

		return false !== $wpdb->update(
			$table,
			array(
				'status'        => sanitize_text_field( $status ),
				'error_message' => '' !== $error ? sanitize_textarea_field( $error ) : null,
				'updated_at'    => current_time( 'mysql' ),
			),
			array( 'id' => absint( $event_id ) ),
			array( '%s', '%s', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Build WHERE clause.
	 *
	 * @param array<string,mixed> $args Query args.
	 * @return array{sql:string,params:array<int,mixed>}
	 */
	private function build_where( array $args ) {
		global $wpdb;

		$table  = $wpdb->prefix . 'subin_mail_events';
		$where  = array();
		$params = array();

		if ( ! empty( $args['status'] ) && in_array( $args['status'], $this->get_statuses(), true ) ) {
			$where[]  = "{$table}.status = %s";
			$params[] = $args['status'];
		}

		if ( ! empty( $args['source'] ) ) {
			$source = sanitize_key( $args['source'] );
			if ( in_array( $source, array( 'submission_inbox_form', 'contact_form_7', 'wpforms', 'fluent_forms', 'forminator', 'ninja_forms' ), true ) ) {
				$where[]  = "({$table}.source = %s OR leads.provider = %s)";
				$params[] = $source;
				$params[] = $source;
			} else {
				$where[]  = "{$table}.source = %s";
				$params[] = $source;
			}
		}

		if ( ! empty( $args['date_from'] ) ) {
			$where[]  = "{$table}.created_at >= %s";
			$params[] = sanitize_text_field( $args['date_from'] ) . ' 00:00:00';
		}

		if ( ! empty( $args['date_to'] ) ) {
			$where[]  = "{$table}.created_at <= %s";
			$params[] = sanitize_text_field( $args['date_to'] ) . ' 23:59:59';
		}

		if ( empty( $args['date_from'] ) && empty( $args['date_to'] ) && ! empty( $args['date_range'] ) && in_array( (string) $args['date_range'], array( '7', '30' ), true ) ) {
			$where[]  = "{$table}.created_at >= %s";
			$params[] = gmdate( 'Y-m-d 00:00:00', strtotime( '-' . absint( $args['date_range'] ) . ' days' ) );
		}

		if ( ! empty( $args['search'] ) ) {
			$search   = sanitize_text_field( $args['search'] );
			$like     = '%' . $wpdb->esc_like( $search ) . '%';
			$where[]  = "({$table}.to_email LIKE %s OR {$table}.from_email LIKE %s OR {$table}.subject LIKE %s OR {$table}.error_message LIKE %s OR CAST({$table}.id AS CHAR) = %s OR CAST({$table}.lead_id AS CHAR) = %s)";
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
			$params[] = $search;
			$params[] = $search;
		}

		return array(
			'sql'    => ! empty( $where ) ? 'WHERE ' . implode( ' AND ', $where ) : '',
			'params' => $params,
		);
	}

	/**
	 * Get allowed email event statuses.
	 *
	 * @return array<int,string>
	 */
	private function get_statuses() {
		return array( self::STATUS_SENT, self::STATUS_FAILED, self::STATUS_PENDING, self::STATUS_NO_EMAIL_GENERATED, self::STATUS_UNKNOWN );
	}
}
