<?php
namespace Pay4All\Pro\Domain;

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

/**
 * Repository over wp_p4all_newsletter_send. One row per (campaign,
 * customer) dispatch — the UNIQUE index guarantees a customer gets each
 * campaign at most once even if the spooler restarts mid-run.
 *
 * Since Pro 2.6.0 the row also carries per-send tracking fields
 * (track_token, opened_at, open_count, clicked_at, click_count,
 * unsubscribed_at) consumed by the stats page. The tracking endpoints
 * look up the row by its 40-hex track_token (unique + indexed).
 */
final class NewsletterSendLog {

	public const STATUS_SENT   = 'sent';
	public const STATUS_FAILED = 'failed';
	public const STATUS_TEST   = 'test';

	public static function table() : string {
		global $wpdb;
		return $wpdb->prefix . 'p4all_newsletter_send';
	}

	/**
	 * Fresh 40-hex token used as the tracking identifier attached to
	 * every open pixel + click redirect embedded in the newsletter body.
	 * Falls back to a sha1 of a random seed if random_bytes throws (never
	 * happens in practice, kept as a defensive net).
	 */
	public static function new_track_token() : string {
		try {
			return bin2hex( random_bytes( 20 ) );
		} catch ( \Throwable $e ) {
			return substr( sha1( wp_generate_password( 40, false ) . microtime( true ) ), 0, 40 );
		}
	}

	/**
	 * INSERT IGNORE with tracking token : re-recording the same (campaign,
	 * customer) pair is a no-op thanks to the UNIQUE index — matches the
	 * spooler's « idempotent retry » contract. Returns [ inserted?, send_id,
	 * token ] so the spooler can plug the token into the body BEFORE the
	 * mail flies out.
	 *
	 * @return array{0:bool,1:int,2:string}  [ was_inserted, send_id, token ]
	 */
	public static function record( int $campaign_id, int $customer_id, string $email, string $lang, string $status, string $error = '' ) : array {
		global $wpdb;
		$table = self::table();
		$token = self::new_track_token();
		$sql = 'INSERT IGNORE INTO ' . $table . ' (campaign_id, customer_id, email, lang, status, error, sent_at, track_token) VALUES (%d, %d, %s, %s, %s, %s, %s, %s)';
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table name safe (prefix + literal) ; every value passes through prepare().
		$ok = $wpdb->query( $wpdb->prepare( $sql, $campaign_id, $customer_id, $email, $lang, $status, $error, gmdate( 'Y-m-d H:i:s' ), $token ) );
		$inserted = ( $ok !== false && (int) $wpdb->rows_affected > 0 );
		if ( $inserted ) {
			return [ true, (int) $wpdb->insert_id, $token ];
		}
		// Duplicate (campaign,customer) — fetch the existing row so the caller
		// can reuse its already-persisted token instead of the freshly-minted
		// one we ended up throwing away.
		$existing = self::find_by_campaign_customer( $campaign_id, $customer_id );
		if ( $existing ) {
			return [ false, (int) $existing['id'], (string) $existing['track_token'] ];
		}
		return [ false, 0, '' ];
	}

	public static function count_sent( int $campaign_id ) : int {
		return self::count_where( $campaign_id, 'status = %s', [ self::STATUS_SENT ] );
	}

	public static function count_failed( int $campaign_id ) : int {
		return self::count_where( $campaign_id, 'status = %s', [ self::STATUS_FAILED ] );
	}

	public static function count_opened( int $campaign_id ) : int {
		return self::count_where( $campaign_id, 'opened_at IS NOT NULL', [] );
	}

	public static function count_clicked( int $campaign_id ) : int {
		return self::count_where( $campaign_id, 'clicked_at IS NOT NULL', [] );
	}

	public static function count_unsubscribed( int $campaign_id ) : int {
		return self::count_where( $campaign_id, 'unsubscribed_at IS NOT NULL', [] );
	}

	public static function sum_open_count( int $campaign_id ) : int {
		global $wpdb;
		$table = self::table();
		$sql = 'SELECT COALESCE(SUM(open_count), 0) FROM ' . $table . ' WHERE campaign_id = %d';
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table name safe ; value via prepare().
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, $campaign_id ) );
	}

	public static function sum_click_count( int $campaign_id ) : int {
		global $wpdb;
		$table = self::table();
		$sql = 'SELECT COALESCE(SUM(click_count), 0) FROM ' . $table . ' WHERE campaign_id = %d';
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table name safe ; value via prepare().
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, $campaign_id ) );
	}

	/**
	 * @param string[] $args
	 */
	private static function count_where( int $campaign_id, string $where_clause, array $args ) : int {
		global $wpdb;
		$table = self::table();
		$sql = 'SELECT COUNT(*) FROM ' . $table . ' WHERE campaign_id = %d AND ' . $where_clause;
		$params = array_merge( [ $campaign_id ], $args );
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table name safe ; where_clause built from literal callers.
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, $params ) );
	}

	/**
	 * Aggregate a « unique events per day » series for the stats chart.
	 * Uses gmdate('Y-m-d') of the FIRST event (opened_at / clicked_at /
	 * unsubscribed_at) so a single recipient appears once per event kind.
	 *
	 * @return array<string,array{opens:int,clicks:int,unsubs:int}>  keyed by ISO date
	 */
	public static function stats_by_day( int $campaign_id ) : array {
		global $wpdb;
		$table = self::table();
		$out = [];
		$fetch = static function ( string $col ) use ( $wpdb, $table, $campaign_id ) {
			$sql = 'SELECT DATE(' . $col . ') AS d, COUNT(*) AS n FROM ' . $table
				. ' WHERE campaign_id = %d AND ' . $col . ' IS NOT NULL GROUP BY DATE(' . $col . ')';
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table + column names literal.
			return $wpdb->get_results( $wpdb->prepare( $sql, $campaign_id ), ARRAY_A );
		};
		foreach ( [ 'opened_at' => 'opens', 'clicked_at' => 'clicks', 'unsubscribed_at' => 'unsubs' ] as $col => $key ) {
			$rows = $fetch( $col );
			foreach ( (array) $rows as $r ) {
				$d = (string) $r['d'];
				if ( $d === '' ) { continue; }
				if ( ! isset( $out[ $d ] ) ) { $out[ $d ] = [ 'opens' => 0, 'clicks' => 0, 'unsubs' => 0 ]; }
				$out[ $d ][ $key ] = (int) $r['n'];
			}
		}
		ksort( $out );
		return $out;
	}

	/**
	 * Paginated recipients for the stats table. `$filter` :
	 *   - '' / 'all' — everything
	 *   - 'opened'    — opened_at IS NOT NULL
	 *   - 'clicked'   — clicked_at IS NOT NULL
	 *   - 'unsubs'    — unsubscribed_at IS NOT NULL
	 *   - 'none'      — opened_at IS NULL (never opened)
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public static function recipients( int $campaign_id, string $filter = '', string $search = '', int $limit = 50, int $offset = 0 ) : array {
		global $wpdb;
		$table   = self::table();
		$where   = 'campaign_id = %d';
		$params  = [ $campaign_id ];
		switch ( $filter ) {
			case 'opened':  $where .= ' AND opened_at IS NOT NULL';       break;
			case 'clicked': $where .= ' AND clicked_at IS NOT NULL';      break;
			case 'unsubs':  $where .= ' AND unsubscribed_at IS NOT NULL'; break;
			case 'none':    $where .= ' AND opened_at IS NULL';           break;
		}
		$search = trim( $search );
		if ( $search !== '' ) {
			$where   .= ' AND email LIKE %s';
			$params[] = '%' . $wpdb->esc_like( $search ) . '%';
		}
		$sql = 'SELECT * FROM ' . $table . ' WHERE ' . $where . ' ORDER BY sent_at DESC LIMIT %d OFFSET %d';
		$params[] = $limit;
		$params[] = $offset;
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table safe ; filter built from literals ; values via prepare().
		return (array) $wpdb->get_results( $wpdb->prepare( $sql, $params ), ARRAY_A );
	}

	/**
	 * Count matching the same filter set — used to render pagination.
	 */
	public static function recipients_count( int $campaign_id, string $filter = '', string $search = '' ) : int {
		global $wpdb;
		$table   = self::table();
		$where   = 'campaign_id = %d';
		$params  = [ $campaign_id ];
		switch ( $filter ) {
			case 'opened':  $where .= ' AND opened_at IS NOT NULL';       break;
			case 'clicked': $where .= ' AND clicked_at IS NOT NULL';      break;
			case 'unsubs':  $where .= ' AND unsubscribed_at IS NOT NULL'; break;
			case 'none':    $where .= ' AND opened_at IS NULL';           break;
		}
		$search = trim( $search );
		if ( $search !== '' ) {
			$where   .= ' AND email LIKE %s';
			$params[] = '%' . $wpdb->esc_like( $search ) . '%';
		}
		$sql = 'SELECT COUNT(*) FROM ' . $table . ' WHERE ' . $where;
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table safe ; filter built from literals ; values via prepare().
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, $params ) );
	}

	public static function find_by_track_token( string $token ) : ?array {
		global $wpdb;
		$token = preg_replace( '/[^a-f0-9]/i', '', trim( $token ) );
		if ( strlen( (string) $token ) !== 40 ) { return null; }
		$table = self::table();
		$sql = 'SELECT * FROM ' . $table . ' WHERE track_token = %s LIMIT 1';
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table safe ; value via prepare().
		$row = $wpdb->get_row( $wpdb->prepare( $sql, strtolower( (string) $token ) ), ARRAY_A );
		return $row ?: null;
	}

	public static function find_by_campaign_customer( int $campaign_id, int $customer_id ) : ?array {
		global $wpdb;
		$table = self::table();
		$sql = 'SELECT * FROM ' . $table . ' WHERE campaign_id = %d AND customer_id = %d LIMIT 1';
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table safe ; values via prepare().
		$row = $wpdb->get_row( $wpdb->prepare( $sql, $campaign_id, $customer_id ), ARRAY_A );
		return $row ?: null;
	}

	/**
	 * Records an open event on the send row : bumps open_count and sets
	 * opened_at when this is the first open. No-op on send rows we don't
	 * own (foreign token or purged campaign).
	 */
	public static function record_open( int $send_id ) : void {
		if ( $send_id <= 0 ) { return; }
		global $wpdb;
		$table = self::table();
		$sql = 'UPDATE ' . $table . ' SET open_count = open_count + 1, opened_at = COALESCE(opened_at, %s) WHERE id = %d';
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table safe ; values via prepare().
		$wpdb->query( $wpdb->prepare( $sql, gmdate( 'Y-m-d H:i:s' ), $send_id ) );
	}

	/**
	 * Records a click event. Click implies open, so opened_at is also
	 * touched when it's still NULL (some clients block images but not
	 * link clicks).
	 */
	public static function record_click( int $send_id ) : void {
		if ( $send_id <= 0 ) { return; }
		global $wpdb;
		$table = self::table();
		$now   = gmdate( 'Y-m-d H:i:s' );
		$sql = 'UPDATE ' . $table
			. ' SET click_count = click_count + 1, clicked_at = COALESCE(clicked_at, %s),'
			. ' opened_at = COALESCE(opened_at, %s), open_count = CASE WHEN opened_at IS NULL THEN open_count + 1 ELSE open_count END'
			. ' WHERE id = %d';
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table safe ; values via prepare().
		$wpdb->query( $wpdb->prepare( $sql, $now, $now, $send_id ) );
	}

	/**
	 * Records an unsubscribe event AND treats it as an open (per user
	 * spec : clicking the unsub link implies they saw the email).
	 */
	public static function record_unsub( int $send_id ) : void {
		if ( $send_id <= 0 ) { return; }
		global $wpdb;
		$table = self::table();
		$now   = gmdate( 'Y-m-d H:i:s' );
		$sql = 'UPDATE ' . $table
			. ' SET unsubscribed_at = COALESCE(unsubscribed_at, %s),'
			. ' opened_at = COALESCE(opened_at, %s), open_count = CASE WHEN opened_at IS NULL THEN open_count + 1 ELSE open_count END'
			. ' WHERE id = %d';
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table safe ; values via prepare().
		$wpdb->query( $wpdb->prepare( $sql, $now, $now, $send_id ) );
	}

	/**
	 * Deletes every log row for the given campaign — called from the
	 * before_delete_post hook so a trashed / hard-deleted newsletter
	 * doesn't leave orphan send rows behind.
	 */
	public static function delete_for_campaign( int $campaign_id ) : void {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->delete( self::table(), [ 'campaign_id' => $campaign_id ], [ '%d' ] );
	}
}
