<?php
/**
 * @package Hafenstudios\Kurato
 */

namespace Hafenstudios\Kurato\Insight;

use Hafenstudios\Kurato\Db\Schema;

defined( 'ABSPATH' ) || exit;

/**
 * Next-Best-Action (HANS-KI-KONZEPT Schicht 2, Regeln + Hans, kein LLM). Wertet
 * den v1-Regelkatalog aus lokalen Kurato-Daten aus und erzeugt Empfehlungs-
 * Kandidaten (keine Ausfuehrung, kein Versand - Leitplanke 1):
 *
 * - R1 Haenger: >= min_hanging Kontakte laenger als X Tage ohne Aktivitaet in
 *   einer Stufe -> Nachfassmail-Kampagne vorschlagen.
 * - R2 Warme Leads: >= min_warm Kontakte mit Oeffnung/Klick (NUR mit Tracking-
 *   Einwilligung, Consent-Linie) ohne Folgeaktion -> Termin-Link anbieten.
 * - R3 Termin ohne Nachfassen: >= min_overdue ueberfaellige Nachfass-Aufgaben nach
 *   einer Buchung -> an das Nachfassen erinnern.
 * - R4 Listenhygiene: >= min_hygiene unterdrueckte/inaktive Kontakte -> aufraeumen.
 * - R5 Formular-Auffaelligkeit: bewusst ZURUECKGESTELLT (braucht Leadlotse-Daten,
 *   die in dieser Etappe nicht angefasst werden). Siehe DECISIONS-ADDENDUM-H2.
 *
 * Jeder Kandidat traegt einen deterministischen context_hash aus Regel + der
 * betroffenen Kontaktmenge (sortierte, eindeutige IDs). Gleiche Menge => gleicher
 * Hash: so protokolliert die Kompass-Seite eine Empfehlung nur einmal (kein Spam
 * bei jedem Seitenaufruf). Es werden NIE Kontaktdaten in den Hash oder in
 * hs_recommendations geschrieben, nur der Hash.
 *
 * Aktionen sind reine Absichten (intent + label); die konkrete Admin-URL loest die
 * InsightPage auf. So bleibt der Engine ohne WordPress-URL-Kontext pruefbar.
 *
 * Die Roh-Zaehlungen kommen aus geschuetzten fetch_*-Methoden (SQL-Seams); Tests
 * ueberschreiben sie mit Fake-Quellen. Nicht final.
 */
class RecommendationEngine {

	public const RULE_HANGER   = 'R1_haenger';
	public const RULE_WARM     = 'R2_warme_leads';
	public const RULE_OVERDUE  = 'R3_nachfassen';
	public const RULE_HYGIENE  = 'R4_listenhygiene';

	/**
	 * Die Rangfolge der Reede (CP6), von dringend nach ruhig. Sie ist bewusst
	 * eine **Reihenfolge von Gruenden**, keine Punktzahl: was oben steht, steht
	 * oben, weil sein Grund staerker ist - und genau dieser Grund steht auf der
	 * Karte. Wer die Liste liest, kann die Sortierung nachvollziehen, ohne eine
	 * Zahl zu glauben, die niemand erklaeren kann.
	 *
	 * 1 Wiedervorlage ueberfaellig (R3)  - Sie haben es sich selbst vorgenommen.
	 * 2 Mail geoeffnet oder geklickt (R2) - Kaufsignal, und es kuehlt ab.
	 * 3 Seit langem still (R1)            - der Haenger, um den es dem Inhaber geht.
	 * 4 Termin steht an                   - vorbereiten, aber der Kalender erinnert schon.
	 * 5 Kein naechster Schritt geplant    - niemand hat etwas vor.
	 * 6 Wiedervorlage geplant             - alles in Ordnung, nur der Vollstaendigkeit halber.
	 *
	 * @var array<int, string>
	 */
	public const REASONS = array(
		1 => 'task_overdue',
		2 => 'mail_engaged',
		3 => 'silent',
		4 => 'booking_soon',
		5 => 'no_next_step',
		6 => 'on_track',
	);

	/**
	 * Default-Schwellwerte. Per Filter kurato_recommendation_thresholds anpassbar;
	 * einzelne Werte lassen sich zusaetzlich per Konstruktor ueberschreiben (Tests).
	 *
	 * @var array<string, int>
	 */
	public const DEFAULT_THRESHOLDS = array(
		'hanging_days'       => 14, // R1: Tage ohne Aktivitaet in der Stufe.
		'min_hanging'        => 3,  // R1: Mindestgruppe je Stufe.
		'warm_lookback_days' => 30, // R2: Zeitfenster fuer Oeffnung/Klick.
		'min_warm'           => 3,  // R2: Mindestzahl warmer Leads.
		'min_overdue'        => 1,  // R3: Mindestzahl ueberfaelliger Aufgaben.
		'inactive_days'      => 180, // R4: Tage ohne Aktivitaet fuer "inaktiv".
		'min_hygiene'        => 5,  // R4: Mindestzahl fuer den Aufraeum-Vorschlag.
	);

	/**
	 * @var array<string, int>
	 */
	private array $overrides;

	/**
	 * @param array<string, int> $overrides Optionale Schwellwert-Ueberschreibungen (Tests).
	 */
	public function __construct( array $overrides = array() ) {
		$this->overrides = $overrides;
	}

	/**
	 * Effektive Schwellwerte: Default, dann Filter, dann Konstruktor-Override.
	 *
	 * @return array<string, int>
	 */
	public function thresholds(): array {
		$filtered = apply_filters( 'kurato_recommendation_thresholds', self::DEFAULT_THRESHOLDS );
		$filtered = is_array( $filtered ) ? $filtered : array();
		$merged   = array_merge( self::DEFAULT_THRESHOLDS, $filtered, $this->overrides );
		$out      = array();
		foreach ( self::DEFAULT_THRESHOLDS as $key => $default ) {
			$out[ $key ] = isset( $merged[ $key ] ) ? (int) $merged[ $key ] : (int) $default;
		}
		return $out;
	}

	/**
	 * Wertet alle Regeln aus und liefert die feuernden Empfehlungs-Kandidaten.
	 *
	 * @return array<int, array{
	 *   rule_key:string, context_hash:string, title:string, body:string,
	 *   action:array{intent:string,label:string}, count:int, ids:array<int,int>
	 * }>
	 */
	public function evaluate(): array {
		$t   = $this->thresholds();
		$now = gmdate( 'Y-m-d H:i:s' );
		$out = array();

		// R1 Haenger je Stufe.
		foreach ( $this->fetch_hanging_by_stage( (int) $t['hanging_days'], $now ) as $stage ) {
			$ids = self::clean_ids( $stage['ids'] ?? array() );
			if ( count( $ids ) >= (int) $t['min_hanging'] ) {
				$out[] = $this->candidate_r1( (string) ( $stage['label'] ?? '' ), $ids );
			}
		}

		// R2 Warme Leads (Consent-Gate im Engine erzwungen).
		$warm_ids = array();
		foreach ( $this->fetch_warm_leads( (int) $t['warm_lookback_days'], $now ) as $lead ) {
			if ( ! empty( $lead['tracking_consent'] ) ) {
				$warm_ids[] = (int) ( $lead['contact_id'] ?? 0 );
			}
		}
		$warm_ids = self::clean_ids( $warm_ids );
		if ( count( $warm_ids ) >= (int) $t['min_warm'] ) {
			$out[] = $this->candidate_r2( $warm_ids );
		}

		// R3 Ueberfaellige Nachfass-Aufgaben nach einer Buchung.
		$overdue_ids = array();
		foreach ( $this->fetch_overdue_followups( gmdate( 'Y-m-d' ) ) as $task ) {
			$overdue_ids[] = (int) ( $task['contact_id'] ?? 0 );
		}
		$overdue_ids = self::clean_ids( $overdue_ids );
		if ( count( $overdue_ids ) >= (int) $t['min_overdue'] ) {
			$out[] = $this->candidate_r3( $overdue_ids );
		}

		// R4 Listenhygiene (unterdrueckt + inaktiv).
		$inactive_cutoff = gmdate( 'Y-m-d H:i:s', time() - (int) $t['inactive_days'] * DAY_IN_SECONDS );
		$hygiene         = $this->fetch_list_hygiene( $inactive_cutoff );
		$suppressed      = (int) ( $hygiene['suppressed'] ?? 0 );
		$inactive        = (int) ( $hygiene['inactive'] ?? 0 );
		if ( $suppressed + $inactive >= (int) $t['min_hygiene'] ) {
			$out[] = $this->candidate_r4( self::clean_ids( $hygiene['ids'] ?? array() ), $suppressed, $inactive );
		}

		return $out;
	}

	// --- Kandidaten-Bau ----------------------------------------------------

	/**
	 * @param string          $stage_label Stufen-Label.
	 * @param array<int, int> $ids         Betroffene Kontakt-IDs.
	 * @return array<string, mixed>
	 */
	private function candidate_r1( string $stage_label, array $ids ): array {
		$count = count( $ids );
		return array(
			'rule_key'     => self::RULE_HANGER,
			'context_hash' => self::context_hash( self::RULE_HANGER, $ids ),
			'title'        => __( 'Haengende Kontakte nachfassen', 'kurato' ),
			'body'         => sprintf(
				/* translators: 1: Anzahl Kontakte, 2: Stufen-Label */
				_n(
					'%1$d Kontakt in der Stufe "%2$s" wartet seit laengerem ohne Reaktion. Ein kurzes Nachfassen bringt Bewegung.',
					'%1$d Kontakte in der Stufe "%2$s" warten seit laengerem ohne Reaktion. Ein kurzes Nachfassen bringt Bewegung.',
					$count,
					'kurato'
				),
				$count,
				$stage_label
			),
			'action'       => array(
				'intent' => 'create_campaign',
				'label'  => __( 'Nachfassmail entwerfen', 'kurato' ),
			),
			'count'        => $count,
			'ids'          => $ids,
		);
	}

	/**
	 * @param array<int, int> $ids Betroffene Kontakt-IDs (mit Consent).
	 * @return array<string, mixed>
	 */
	private function candidate_r2( array $ids ): array {
		$count = count( $ids );
		return array(
			'rule_key'     => self::RULE_WARM,
			'context_hash' => self::context_hash( self::RULE_WARM, $ids ),
			'title'        => __( 'Warme Leads ansprechen', 'kurato' ),
			'body'         => sprintf(
				/* translators: %d: Anzahl warmer Leads */
				_n(
					'%d Kontakt hat zuletzt Ihre Mail geoeffnet oder geklickt, aber noch keinen naechsten Schritt gemacht. Bieten Sie einen Termin an.',
					'%d Kontakte haben zuletzt Ihre Mail geoeffnet oder geklickt, aber noch keinen naechsten Schritt gemacht. Bieten Sie einen Termin an.',
					$count,
					'kurato'
				),
				$count
			),
			'action'       => array(
				'intent' => 'view_contacts',
				'label'  => __( 'Kontakte ansehen', 'kurato' ),
			),
			'count'        => $count,
			'ids'          => $ids,
		);
	}

	/**
	 * @param array<int, int> $ids Betroffene Kontakt-IDs.
	 * @return array<string, mixed>
	 */
	private function candidate_r3( array $ids ): array {
		$count = count( $ids );
		return array(
			'rule_key'     => self::RULE_OVERDUE,
			'context_hash' => self::context_hash( self::RULE_OVERDUE, $ids ),
			'title'        => __( 'Ueberfaellige Nachfass-Aufgaben', 'kurato' ),
			'body'         => sprintf(
				/* translators: %d: Anzahl Aufgaben */
				_n(
					'Nach einem Termin ist %d Nachfass-Aufgabe ueberfaellig. Jetzt erledigen, damit der Faden nicht abreisst.',
					'Nach Terminen sind %d Nachfass-Aufgaben ueberfaellig. Jetzt erledigen, damit der Faden nicht abreisst.',
					$count,
					'kurato'
				),
				$count
			),
			'action'       => array(
				'intent' => 'view_contacts',
				'label'  => __( 'Zu den Kontakten', 'kurato' ),
			),
			'count'        => $count,
			'ids'          => $ids,
		);
	}

	/**
	 * @param array<int, int> $ids        Betroffene Kontakt-IDs.
	 * @param int             $suppressed Unterdrueckte.
	 * @param int             $inactive   Inaktive.
	 * @return array<string, mixed>
	 */
	private function candidate_r4( array $ids, int $suppressed, int $inactive ): array {
		$total = $suppressed + $inactive;
		return array(
			'rule_key'     => self::RULE_HYGIENE,
			'context_hash' => self::context_hash( self::RULE_HYGIENE, $ids ),
			'title'        => __( 'Liste aufraeumen', 'kurato' ),
			'body'         => sprintf(
				/* translators: 1: Gesamtzahl, 2: unzustellbar, 3: inaktiv */
				__( '%1$d Kontakte belasten Ihre Zustellbarkeit: %2$d unzustellbar/abgemeldet, %3$d lange inaktiv. Eine Re-Permission oder Bereinigung haelt die Liste sauber.', 'kurato' ),
				$total,
				$suppressed,
				$inactive
			),
			'action'       => array(
				'intent' => 'view_lists',
				'label'  => __( 'Listen pruefen', 'kurato' ),
			),
			'count'        => $total,
			'ids'          => $ids,
		);
	}

	// --- Reede: dieselben Signale, aber je Kontakt (CP6) ---------------------

	/**
	 * Die dringendsten Kontakte einer Stufe, mit ihrem Grund im Klartext.
	 *
	 * Dies ist **keine zweite Regelwelt**: es sind dieselben Signale, aus denen
	 * evaluate() seine Gruppen-Empfehlungen baut (ueberfaellige Nachfass-Aufgabe
	 * = R3, Oeffnung/Klick mit Einwilligung = R2, lange still = R1), nur je
	 * Kontakt statt je Stufe. Auch die Schwellen kommen unveraendert aus
	 * {@see thresholds()}; es gibt keinen zweiten Satz Stellschrauben.
	 *
	 * Rangfolge und Sortierung macht die Datenbank ({@see REASONS}); PHP bekommt
	 * nur die angeforderten Zeilen. Bei 10.000 Kontakten in einer Stufe sind das
	 * fuenf - die Antwortgroesse haengt an $limit, nicht am Bestand.
	 *
	 * Das Consent-Gate von R2 gilt hier genauso: Oeffnungen und Klicks zaehlen
	 * nur bei Kontakten mit tracking_consent.
	 *
	 * @param int               $stage_id Stufe.
	 * @param FunnelFilter|null $filter   Filter (null = alle Kontakte der Stufe).
	 * @param int               $limit    Hoechstzahl Zeilen.
	 * @param int               $offset   Versatz (Hafenliste, Zoom 3).
	 * @param string            $sort     Sortierung aus {@see FunnelFilter::SORTS}.
	 * @param string            $now      UTC-"Jetzt" (leer = jetzt).
	 * @return array<int, array{id:int,name:string,email:string,stage_id:int,silent_days:int,reason:array{key:string,days:int,count:int}}>
	 */
	public function urgent_in_stage( int $stage_id, ?FunnelFilter $filter, int $limit, int $offset = 0, string $sort = 'rang', string $now = '' ): array {
		$now   = '' !== $now ? $now : gmdate( 'Y-m-d H:i:s' );
		$rows  = $this->fetch_urgent_in_stage(
			max( 0, $stage_id ),
			$filter ?? FunnelFilter::none(),
			max( 1, $limit ),
			max( 0, $offset ),
			FunnelFilter::normalize_sort( $sort ),
			$now
		);

		$out = array();
		foreach ( $rows as $row ) {
			$out[] = array(
				'id'          => (int) ( $row['contact_id'] ?? 0 ),
				'name'        => (string) ( $row['name'] ?? '' ),
				'email'       => (string) ( $row['email'] ?? '' ),
				'stage_id'    => (int) ( $row['stage_id'] ?? 0 ),
				'silent_days' => (int) ( $row['silent_days'] ?? 0 ),
				'reason'      => self::reason( (int) ( $row['rang'] ?? 6 ), (int) ( $row['wert'] ?? 0 ) ),
			);
		}
		return $out;
	}

	/**
	 * Uebersetzt Rang und Kennwert einer Zeile in den Grund, der auf der Karte
	 * steht. Reine Zuordnung, ohne Text: die Sprache waehlt die Oberflaeche
	 * (DE/EN im Cockpit-Katalog), hier steht nur, WAS gesagt wird.
	 *
	 * `days` und `count` sind die Zahl im Satz ("3 Tage ueberfaellig", "zweimal
	 * geoeffnet"). Beim Termin liefert die Datenbank Stunden, damit "heute" und
	 * "morgen" unterscheidbar bleiben.
	 *
	 * @param int $rang Rang 1..6.
	 * @param int $wert Kennwert der Rangklasse.
	 * @return array{key:string,days:int,count:int}
	 */
	public static function reason( int $rang, int $wert ): array {
		$key   = self::REASONS[ $rang ] ?? self::REASONS[6];
		$wert  = max( 0, $wert );
		$days  = 'booking_soon' === $key ? intdiv( $wert, 24 ) : $wert;
		$count = 'mail_engaged' === $key ? $wert : 0;

		return array(
			'key'   => $key,
			'days'  => 'mail_engaged' === $key ? 0 : $days,
			'count' => $count,
		);
	}

	/**
	 * SQL-Seam der Reede: liefert die Rohzeilen (contact_id, stage_id, name,
	 * email, silent_days, rang, wert) bereits sortiert und begrenzt.
	 *
	 * Aufbau: eine Unterabfrage sammelt je Kontakt der Stufe seine Signale, die
	 * aeussere Abfrage leitet daraus Rang und Kennwert ab und sortiert. Alle
	 * Werte sind Platzhalter; Tabellennamen kommen aus dem Schema.
	 *
	 * @param int          $stage_id Stufe (0 = ohne Stufen-Einschraenkung).
	 * @param FunnelFilter $filter   Filter.
	 * @param int          $limit    Hoechstzahl.
	 * @param int          $offset   Versatz.
	 * @param string       $sort     Normalisierte Sortierung.
	 * @param string       $now      UTC-"Jetzt".
	 * @return array<int, array<string, mixed>>
	 */
	protected function fetch_urgent_in_stage( int $stage_id, FunnelFilter $filter, int $limit, int $offset, string $sort, string $now ): array {
		global $wpdb;

		$t        = $this->thresholds();
		$contacts = Schema::table( 'contacts' );
		$tasks    = Schema::table( 'tasks' );
		$bookings = Schema::table( 'bookings' );
		$events   = Schema::table( 'mail_events' );
		$history  = Schema::table( 'stage_history' );

		$today = substr( $now, 0, 10 );
		$warm  = gmdate( 'Y-m-d H:i:s', strtotime( $now ) - (int) $t['warm_lookback_days'] * DAY_IN_SECONDS );
		$still = (int) $t['hanging_days'];
		$where = $filter->where( 'c', $now );

		// Letzte Aktivitaet: dieselbe Definition wie in der Haenger-Zaehlung.
		$activity = "GREATEST(
			COALESCE( c.updated_at, c.created_at ),
			COALESCE( ( SELECT MAX(h_a.changed_at) FROM {$history} h_a WHERE h_a.contact_id = c.id ), c.created_at ),
			COALESCE( ( SELECT MAX(e_a.created_at) FROM {$events} e_a WHERE e_a.contact_id = c.id ), c.created_at ),
			COALESCE( ( SELECT MAX(b_a.created_at) FROM {$bookings} b_a WHERE b_a.contact_id = c.id ), c.created_at )
		)";

		$inner = "SELECT c.id AS contact_id, c.stage_id AS stage_id,
				TRIM( COALESCE( NULLIF( c.display_name, '' ), c.email, '' ) ) AS name,
				COALESCE( c.email, '' ) AS email,
				( SELECT MIN(t_o.due_date) FROM {$tasks} t_o WHERE t_o.contact_id = c.id AND t_o.done = 0 AND t_o.due_date IS NOT NULL AND t_o.due_date < %s ) AS overdue_due,
				( SELECT MIN(t_n.due_date) FROM {$tasks} t_n WHERE t_n.contact_id = c.id AND t_n.done = 0 AND t_n.due_date IS NOT NULL AND t_n.due_date >= %s ) AS next_due,
				( SELECT COUNT(*) FROM {$tasks} t_c WHERE t_c.contact_id = c.id AND t_c.done = 0 ) AS open_tasks,
				( SELECT MIN(b_n.start_utc) FROM {$bookings} b_n WHERE b_n.contact_id = c.id AND b_n.status = 'confirmed' AND b_n.start_utc >= %s ) AS next_booking,
				CASE WHEN c.tracking_consent = 1 THEN ( SELECT COUNT(*) FROM {$events} e_w WHERE e_w.contact_id = c.id AND e_w.type IN ('open','click') AND e_w.created_at >= %s ) ELSE 0 END AS engaged,
				FLOOR( GREATEST( 0, TIMESTAMPDIFF( SECOND, {$activity}, %s ) ) / 86400 ) AS silent_days
			FROM {$contacts} c
			WHERE c.stage_id = %d{$where['sql']}";

		$order = "r.rang ASC, CASE WHEN r.rang IN (4,6) THEN -r.wert ELSE r.wert END DESC, r.contact_id ASC";
		if ( 'name' === $sort ) {
			$order = 'r.name ASC, r.contact_id ASC';
		} elseif ( 'still' === $sort ) {
			$order = 'r.silent_days DESC, r.contact_id ASC';
		}

		$sql = "SELECT * FROM (
			SELECT q.contact_id, q.stage_id, q.name, q.email, q.silent_days,
				CASE
					WHEN q.overdue_due IS NOT NULL THEN 1
					WHEN q.engaged > 0 THEN 2
					WHEN q.silent_days > %d THEN 3
					WHEN q.next_booking IS NOT NULL THEN 4
					WHEN q.open_tasks = 0 THEN 5
					ELSE 6
				END AS rang,
				CASE
					WHEN q.overdue_due IS NOT NULL THEN DATEDIFF( %s, q.overdue_due )
					WHEN q.engaged > 0 THEN q.engaged
					WHEN q.silent_days > %d THEN q.silent_days
					WHEN q.next_booking IS NOT NULL THEN GREATEST( 0, TIMESTAMPDIFF( HOUR, %s, q.next_booking ) )
					WHEN q.open_tasks = 0 THEN q.silent_days
					ELSE GREATEST( 0, COALESCE( DATEDIFF( q.next_due, %s ), 0 ) )
				END AS wert
			FROM ( {$inner} ) q
		) r ORDER BY {$order} LIMIT %d OFFSET %d";

		$params = array_merge(
			array( $still, $today, $still, $now, $today ),
			array( $today, $today, $now, $warm, $now, $stage_id ),
			$where['params'],
			array( $limit, $offset )
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, ...$params ), ARRAY_A );
		return is_array( $rows ) ? $rows : array();
	}

	// --- Reine Helfer ------------------------------------------------------

	/**
	 * Deterministischer Kontext-Hash aus Regel und sortierter, eindeutiger
	 * Kontaktmenge. Keine Klardaten - nur ein SHA-256 der IDs.
	 *
	 * @param string          $rule_key Regel.
	 * @param array<int, int> $ids      Kontakt-IDs.
	 * @return string 64 Hex-Zeichen.
	 */
	public static function context_hash( string $rule_key, array $ids ): string {
		$ids = self::clean_ids( $ids );
		return hash( 'sha256', $rule_key . '|' . implode( ',', $ids ) );
	}

	/**
	 * Normalisiert eine ID-Liste: nur positive, eindeutig, aufsteigend sortiert.
	 *
	 * @param array<int, mixed> $ids Roh-IDs.
	 * @return array<int, int>
	 */
	public static function clean_ids( array $ids ): array {
		$out = array();
		foreach ( $ids as $id ) {
			$id = (int) $id;
			if ( $id > 0 ) {
				$out[ $id ] = $id;
			}
		}
		$out = array_values( $out );
		sort( $out, SORT_NUMERIC );
		return $out;
	}

	// --- Datenquellen (SQL-Seams, in Tests ueberschrieben) -----------------

	/**
	 * Haenger je Stufe: Kontakte, die laenger als $days ohne jede Aktivitaet in
	 * ihrer aktuellen Stufe stehen. Liefert je Stufe Label und Kontakt-IDs.
	 *
	 * @param int    $days Schwelle in Tagen.
	 * @param string $now  UTC-"Jetzt".
	 * @return array<int, array{stage_id:int,label:string,ids:array<int,int>}>
	 */
	protected function fetch_hanging_by_stage( int $days, string $now ): array {
		global $wpdb;
		$contacts = Schema::table( 'contacts' );
		$stages   = Schema::table( 'stages' );
		$history  = Schema::table( 'stage_history' );
		$events   = Schema::table( 'mail_events' );
		$bookings = Schema::table( 'bookings' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT c.id AS contact_id, c.stage_id AS stage_id, s.label AS label
				FROM {$contacts} c
				INNER JOIN {$stages} s ON s.id = c.stage_id
				WHERE c.stage_id IS NOT NULL AND s.is_won = 0 AND s.is_lost = 0
				AND TIMESTAMPDIFF( DAY, GREATEST(
					COALESCE( c.updated_at, c.created_at ),
					COALESCE( ( SELECT MAX(h.changed_at) FROM {$history} h WHERE h.contact_id = c.id ), c.created_at ),
					COALESCE( ( SELECT MAX(e.created_at) FROM {$events} e WHERE e.contact_id = c.id ), c.created_at ),
					COALESCE( ( SELECT MAX(b.created_at) FROM {$bookings} b WHERE b.contact_id = c.id ), c.created_at )
				), %s ) > %d",
				$now,
				$days
			),
			ARRAY_A
		);

		$grouped = array();
		foreach ( (array) $rows as $row ) {
			$sid = (int) $row['stage_id'];
			if ( ! isset( $grouped[ $sid ] ) ) {
				$grouped[ $sid ] = array(
					'stage_id' => $sid,
					'label'    => (string) $row['label'],
					'ids'      => array(),
				);
			}
			$grouped[ $sid ]['ids'][] = (int) $row['contact_id'];
		}
		return array_values( $grouped );
	}

	/**
	 * Warme Leads: Kontakte mit Oeffnung/Klick im Zeitfenster ohne spaetere
	 * Folgeaktion (kein spaeterer Stufenwechsel, keine spaetere Buchung). Der
	 * tracking_consent-Zustand wird mitgeliefert; das Consent-Gate erzwingt der
	 * Engine (nur Kontakte mit Einwilligung zaehlen).
	 *
	 * @param int    $lookback_days Zeitfenster in Tagen.
	 * @param string $now           UTC-"Jetzt".
	 * @return array<int, array{contact_id:int,tracking_consent:bool}>
	 */
	protected function fetch_warm_leads( int $lookback_days, string $now ): array {
		global $wpdb;
		$contacts = Schema::table( 'contacts' );
		$events   = Schema::table( 'mail_events' );
		$history  = Schema::table( 'stage_history' );
		$bookings = Schema::table( 'bookings' );
		$since    = gmdate( 'Y-m-d H:i:s', strtotime( $now ) - $lookback_days * DAY_IN_SECONDS );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT DISTINCT c.id AS contact_id, c.tracking_consent AS tracking_consent
				FROM {$contacts} c
				INNER JOIN {$events} ev ON ev.contact_id = c.id AND ev.type IN ('open','click') AND ev.created_at >= %s
				WHERE c.tracking_consent = 1
				AND NOT EXISTS ( SELECT 1 FROM {$history} h WHERE h.contact_id = c.id AND h.changed_at >= ev.created_at )
				AND NOT EXISTS ( SELECT 1 FROM {$bookings} b WHERE b.contact_id = c.id AND b.created_at >= ev.created_at )",
				$since
			),
			ARRAY_A
		);
		$out = array();
		foreach ( (array) $rows as $row ) {
			$out[] = array(
				'contact_id'       => (int) $row['contact_id'],
				'tracking_consent' => (int) $row['tracking_consent'] === 1,
			);
		}
		return $out;
	}

	/**
	 * Ueberfaellige, offene Nachfass-Aufgaben von Kontakten, die bereits eine
	 * Buchung hatten (Termin ohne Nachfassen, R3).
	 *
	 * @param string $today Heutiges Datum (Y-m-d).
	 * @return array<int, array{task_id:int,contact_id:int}>
	 */
	protected function fetch_overdue_followups( string $today ): array {
		global $wpdb;
		$tasks    = Schema::table( 'tasks' );
		$bookings = Schema::table( 'bookings' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT t.id AS task_id, t.contact_id AS contact_id
				FROM {$tasks} t
				WHERE t.done = 0 AND t.due_date IS NOT NULL AND t.due_date < %s
				AND EXISTS ( SELECT 1 FROM {$bookings} b WHERE b.contact_id = t.contact_id )",
				$today
			),
			ARRAY_A
		);
		$out = array();
		foreach ( (array) $rows as $row ) {
			$out[] = array(
				'task_id'    => (int) $row['task_id'],
				'contact_id' => (int) $row['contact_id'],
			);
		}
		return $out;
	}

	/**
	 * Listenhygiene: Zahl der unterdrueckten (email_status != active) und der lange
	 * inaktiven Kontakte plus deren IDs (fuer den Kontext-Hash).
	 *
	 * @param string $inactive_before UTC-Grenze fuer "inaktiv".
	 * @return array{suppressed:int,inactive:int,ids:array<int,int>}
	 */
	protected function fetch_list_hygiene( string $inactive_before ): array {
		global $wpdb;
		$contacts = Schema::table( 'contacts' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id,
					CASE WHEN email_status <> 'active' THEN 1 ELSE 0 END AS suppressed
				FROM {$contacts}
				WHERE email_status <> 'active'
				   OR ( last_activity_at IS NOT NULL AND last_activity_at < %s )
				   OR ( last_activity_at IS NULL AND created_at < %s )",
				$inactive_before,
				$inactive_before
			),
			ARRAY_A
		);
		$suppressed = 0;
		$inactive   = 0;
		$ids        = array();
		foreach ( (array) $rows as $row ) {
			$ids[] = (int) $row['id'];
			if ( 1 === (int) $row['suppressed'] ) {
				$suppressed++;
			} else {
				$inactive++;
			}
		}
		return array(
			'suppressed' => $suppressed,
			'inactive'   => $inactive,
			'ids'        => $ids,
		);
	}
}
