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

namespace Hafenstudios\Kurato\Db;

defined( 'ABSPATH' ) || exit;

/**
 * Repository fuer Kontakte (hs_contacts) samt Stufenwechsel-Verlauf.
 *
 * Kontakte sind der einzige Kontaktspeicher der Flotte (BAUPLAN Abschnitt 7):
 * Leadlotse-Anfragen werden hier zu Kontakten. Dedup erfolgt ueber die E-Mail.
 *
 * Nicht final, damit Tests die DB-Methoden per Test-Double ueberschreiben und die
 * Intake-Logik isoliert pruefen koennen.
 */
class ContactRepository {

	/**
	 * Legt einen Kontakt an.
	 *
	 * @param array<string, mixed> $data Kontaktdaten.
	 * @return int Kontakt-ID (0 bei Fehler).
	 */
	public function insert( array $data ): int {
		global $wpdb;

		$email = sanitize_email( (string) ( $data['email'] ?? '' ) );

		$row = array(
			// Leere E-Mail als NULL speichern: erlaubt mehrere namenlose Kontakte
			// trotz UNIQUE-Index, verhindert aber Doppelte bei echter Adresse.
			'email'        => '' !== $email ? $email : null,
			'first_name'   => substr( sanitize_text_field( (string) ( $data['first_name'] ?? '' ) ), 0, 100 ),
			'last_name'    => substr( sanitize_text_field( (string) ( $data['last_name'] ?? '' ) ), 0, 100 ),
			'display_name' => substr( sanitize_text_field( (string) ( $data['display_name'] ?? '' ) ), 0, 200 ),
			'phone'        => substr( sanitize_text_field( (string) ( $data['phone'] ?? '' ) ), 0, 50 ),
			'stage_id'     => isset( $data['stage_id'] ) ? absint( $data['stage_id'] ) : null,
			'source'       => substr( sanitize_text_field( (string) ( $data['source'] ?? '' ) ), 0, 100 ),
			'utm_source'   => substr( sanitize_text_field( (string) ( $data['utm_source'] ?? '' ) ), 0, 100 ),
			'utm_medium'   => substr( sanitize_text_field( (string) ( $data['utm_medium'] ?? '' ) ), 0, 100 ),
			'utm_campaign' => substr( sanitize_text_field( (string) ( $data['utm_campaign'] ?? '' ) ), 0, 100 ),
			'consent_id'   => ! empty( $data['consent_id'] ) ? absint( $data['consent_id'] ) : null,
			'created_at'   => current_time( 'mysql', true ),
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$ok = $wpdb->insert( Schema::table( 'contacts' ), $row );
		return $ok ? (int) $wpdb->insert_id : 0;
	}

	/**
	 * Aktualisiert einen Kontakt (nur uebergebene Felder).
	 *
	 * @param int                  $id   Kontakt-ID.
	 * @param array<string, mixed> $data Zu aendernde Felder.
	 * @return bool
	 */
	public function update( int $id, array $data ): bool {
		global $wpdb;

		$allowed = array( 'email', 'first_name', 'last_name', 'display_name', 'phone', 'source', 'utm_source', 'utm_medium', 'utm_campaign', 'consent_id' );
		$row     = array();
		foreach ( $allowed as $key ) {
			if ( ! array_key_exists( $key, $data ) ) {
				continue;
			}
			if ( 'email' === $key ) {
				$email       = sanitize_email( (string) $data[ $key ] );
				$row[ $key ] = '' !== $email ? $email : null;
			} elseif ( 'consent_id' === $key ) {
				$row[ $key ] = ! empty( $data[ $key ] ) ? absint( $data[ $key ] ) : null;
			} else {
				$row[ $key ] = sanitize_text_field( (string) $data[ $key ] );
			}
		}
		if ( array() === $row ) {
			return false;
		}
		$row['updated_at'] = current_time( 'mysql', true );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return false !== $wpdb->update( Schema::table( 'contacts' ), $row, array( 'id' => $id ) );
	}

	/**
	 * Setzt die Tracking-Einwilligung eines Kontakts (hs_contacts.tracking_consent).
	 *
	 * Getrennte, einwilligungspflichtige Einwilligung fuer Open-/Click-Messung
	 * (K4-Konzept Abschnitt 7, Default aus). Das Preference Center schreibt hier;
	 * der Consent-Nachweis wird dort zusaetzlich ins Core-Consent-Log geschrieben.
	 *
	 * @param int  $id      Kontakt-ID.
	 * @param bool $consent true = messen erlaubt, false = widerrufen.
	 * @return bool true, wenn die Zeile aktualisiert wurde.
	 */
	public function set_tracking_consent( int $id, bool $consent ): bool {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$affected = $wpdb->update(
			Schema::table( 'contacts' ),
			array(
				'tracking_consent' => $consent ? 1 : 0,
				'updated_at'       => current_time( 'mysql', true ),
			),
			array( 'id' => $id )
		);
		return is_int( $affected ) && $affected > 0;
	}

	/**
	 * Setzt den Zustellstatus eines Kontakts (hs_contacts.email_status).
	 *
	 * Wird von der Suppression-Logik gesetzt (K4e-1): `suppressed_bounce` bei
	 * einem harten Bounce, `suppressed_complaint` bei einer Beschwerde. Nur diese
	 * bekannten Werte sind zulaessig; die Queue ueberspringt Kontakte, deren
	 * Status nicht `active` ist.
	 *
	 * @param int    $id     Kontakt-ID.
	 * @param string $status active | suppressed_bounce | suppressed_complaint.
	 * @return bool true, wenn die Zeile aktualisiert wurde.
	 */
	public function set_email_status( int $id, string $status ): bool {
		if ( ! in_array( $status, array( 'active', 'suppressed_bounce', 'suppressed_complaint' ), true ) ) {
			return false;
		}
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$affected = $wpdb->update(
			Schema::table( 'contacts' ),
			array(
				'email_status' => $status,
				'updated_at'   => current_time( 'mysql', true ),
			),
			array( 'id' => $id )
		);
		return is_int( $affected ) && $affected > 0;
	}

	/**
	 * Liefert einen Kontakt.
	 *
	 * @param int $id Kontakt-ID.
	 * @return array<string, mixed>|null
	 */
	public function get( int $id ): ?array {
		global $wpdb;
		$table = Schema::table( 'contacts' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $id ), ARRAY_A );
		return $row ? $this->hydrate( $row ) : null;
	}

	/**
	 * Findet einen Kontakt anhand der E-Mail (Dedup beim Intake).
	 *
	 * @param string $email E-Mail.
	 * @return array<string, mixed>|null
	 */
	public function find_by_email( string $email ): ?array {
		$email = sanitize_email( $email );
		if ( '' === $email ) {
			return null;
		}
		global $wpdb;
		$table = Schema::table( 'contacts' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE email = %s ORDER BY id ASC LIMIT 1", $email ), ARRAY_A );
		return $row ? $this->hydrate( $row ) : null;
	}

	/**
	 * Listet Kontakte mit Pagination, Suche und Stufen-Filter.
	 *
	 * @param array{page?:int,per_page?:int,search?:string,stage_id?:int} $args Filter.
	 * @return array{items:array<int,array<string,mixed>>,total:int,page:int,per_page:int}
	 */
	public function query( array $args = array() ): array {
		global $wpdb;
		$table = Schema::table( 'contacts' );

		$page     = max( 1, absint( $args['page'] ?? 1 ) );
		$per_page = min( 100, max( 1, absint( $args['per_page'] ?? 25 ) ) );
		$offset   = ( $page - 1 ) * $per_page;

		$where  = array( '1=1' );
		$params = array();

		if ( ! empty( $args['stage_id'] ) ) {
			$where[]  = 'stage_id = %d';
			$params[] = absint( $args['stage_id'] );
		}
		if ( ! empty( $args['search'] ) ) {
			$like     = '%' . $wpdb->esc_like( sanitize_text_field( (string) $args['search'] ) ) . '%';
			$where[]  = '(email LIKE %s OR display_name LIKE %s OR first_name LIKE %s OR last_name LIKE %s)';
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
		}

		$where_sql = implode( ' AND ', $where );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		if ( $params ) {
			$total = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE {$where_sql}", ...$params ) );
			$rows  = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT * FROM {$table} WHERE {$where_sql} ORDER BY created_at DESC, id DESC LIMIT %d OFFSET %d",
					...array_merge( $params, array( $per_page, $offset ) )
				),
				ARRAY_A
			);
		} else {
			$total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
			$rows  = $wpdb->get_results(
				$wpdb->prepare( "SELECT * FROM {$table} ORDER BY created_at DESC, id DESC LIMIT %d OFFSET %d", $per_page, $offset ),
				ARRAY_A
			);
		}
		// phpcs:enable

		return array(
			'items'    => array_map( array( $this, 'hydrate' ), $rows ?: array() ),
			'total'    => $total,
			'page'     => $page,
			'per_page' => $per_page,
		);
	}

	/**
	 * Kontakte einer Pipeline-Stufe (fuer das Kanban-Board).
	 *
	 * @param int $stage_id Stufen-ID.
	 * @param int $limit    Maximale Anzahl je Stufe.
	 * @return array<int, array<string, mixed>>
	 */
	public function by_stage( int $stage_id, int $limit = 50 ): array {
		global $wpdb;
		$table = Schema::table( 'contacts' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE stage_id = %d ORDER BY updated_at DESC, id DESC LIMIT %d", $stage_id, max( 1, $limit ) ), ARRAY_A );
		return array_map( array( $this, 'hydrate' ), $rows ?: array() );
	}

	/**
	 * Setzt die Pipeline-Stufe eines Kontakts und schreibt den Verlauf.
	 *
	 * @param int      $contact_id   Kontakt-ID.
	 * @param int|null $to_stage_id  Ziel-Stufe (null = aus der Pipeline nehmen).
	 * @param int      $user_id      Ausloesende Nutzer-ID (0 = System/Automatik).
	 * @return bool
	 */
	public function set_stage( int $contact_id, ?int $to_stage_id, int $user_id = 0 ): bool {
		global $wpdb;

		$contact = $this->get( $contact_id );
		if ( null === $contact ) {
			return false;
		}
		$from = $contact['stage_id'];

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$updated = $wpdb->update(
			Schema::table( 'contacts' ),
			array(
				'stage_id'   => $to_stage_id,
				'updated_at' => current_time( 'mysql', true ),
			),
			array( 'id' => $contact_id )
		);
		if ( false === $updated ) {
			return false;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->insert(
			Schema::table( 'stage_history' ),
			array(
				'contact_id'    => $contact_id,
				'from_stage_id' => $from,
				'to_stage_id'   => $to_stage_id,
				'changed_at'    => current_time( 'mysql', true ),
				'user_id'       => $user_id > 0 ? $user_id : null,
			)
		);
		return true;
	}

	/**
	 * Verlauf der Stufenwechsel eines Kontakts (Timeline), neueste zuerst.
	 * Stufen-Labels werden per LEFT JOIN mitgeliefert.
	 *
	 * @param int $contact_id Kontakt-ID.
	 * @return array<int, array<string, mixed>>
	 */
	public function stage_history( int $contact_id ): array {
		global $wpdb;
		$history = Schema::table( 'stage_history' );
		$stages  = Schema::table( 'stages' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT h.id, h.from_stage_id, h.to_stage_id, h.changed_at, h.user_id,
					sf.label AS from_label, st.label AS to_label
				FROM {$history} h
				LEFT JOIN {$stages} sf ON sf.id = h.from_stage_id
				LEFT JOIN {$stages} st ON st.id = h.to_stage_id
				WHERE h.contact_id = %d
				ORDER BY h.changed_at DESC, h.id DESC",
				$contact_id
			),
			ARRAY_A
		);

		return array_map(
			static function ( array $row ): array {
				return array(
					'id'         => (int) $row['id'],
					'from_label' => null !== $row['from_label'] ? (string) $row['from_label'] : '',
					'to_label'   => null !== $row['to_label'] ? (string) $row['to_label'] : '',
					'changed_at' => (string) $row['changed_at'],
					'user_id'    => null !== $row['user_id'] ? (int) $row['user_id'] : 0,
				);
			},
			$rows ?: array()
		);
	}

	/** Meta-Schluessel der Kontakt-Notizen (CP5). */
	public const NOTE_META_KEY = 'note';

	/** Laengengrenze einer Notiz (Zeichen). */
	public const NOTE_MAX = 5000;

	/**
	 * Legt eine Notiz am Kontakt an (CP5, Cockpit-Route
	 * `POST kurato/v1/contacts/{id}/notes`). Notizen liegen in der vorhandenen
	 * Zusatzfeld-Tabelle hs_contact_meta - bewusst NICHT in hs_stage_history: dort
	 * rechnen Funnel-Analytik und Empfehlungs-Engine mit jeder Zeile, eine Notiz
	 * wuerde ihre Zahlen still verschieben.
	 *
	 * @param int    $contact_id Kontakt-ID.
	 * @param string $text       Notiztext (wird bereinigt und auf 5000 Zeichen gekappt).
	 * @param int    $user_id    Ausloesende Nutzer-ID (0 = System).
	 * @return int Notiz-ID (0 bei leerem Text oder Fehler).
	 */
	public function add_note( int $contact_id, string $text, int $user_id = 0 ): int {
		global $wpdb;

		$clean = trim( sanitize_textarea_field( $text ) );
		if ( '' === $clean ) {
			return 0;
		}
		$clean = function_exists( 'mb_substr' ) ? mb_substr( $clean, 0, self::NOTE_MAX ) : substr( $clean, 0, self::NOTE_MAX );

		$value = wp_json_encode(
			array(
				'note'       => $clean,
				'created_at' => current_time( 'mysql', true ),
				'user_id'    => $user_id > 0 ? $user_id : 0,
			)
		);
		if ( false === $value ) {
			return 0;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$ok = $wpdb->insert(
			Schema::table( 'contact_meta' ),
			array(
				'contact_id' => $contact_id,
				'meta_key'   => self::NOTE_META_KEY, // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key
				'meta_value' => $value, // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_value
			)
		);
		return $ok ? (int) $wpdb->insert_id : 0;
	}

	/**
	 * Notizen eines Kontakts, neueste zuerst.
	 *
	 * @param int $contact_id Kontakt-ID.
	 * @return array<int, array{id:int, note:string, created_at:string, user_id:int}>
	 */
	public function notes( int $contact_id ): array {
		global $wpdb;
		$table = Schema::table( 'contact_meta' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results(
			$wpdb->prepare( "SELECT id, meta_value FROM {$table} WHERE contact_id = %d AND meta_key = %s ORDER BY id DESC", $contact_id, self::NOTE_META_KEY ),
			ARRAY_A
		);

		$out = array();
		foreach ( (array) $rows as $row ) {
			$data = json_decode( (string) $row['meta_value'], true );
			if ( ! is_array( $data ) || '' === (string) ( $data['note'] ?? '' ) ) {
				continue;
			}
			$out[] = array(
				'id'         => (int) $row['id'],
				'note'       => (string) $data['note'],
				'created_at' => (string) ( $data['created_at'] ?? '' ),
				'user_id'    => (int) ( $data['user_id'] ?? 0 ),
			);
		}
		return $out;
	}

	/**
	 * Gesamtzahl der Kontakte.
	 *
	 * @return int
	 */
	public function count_all(): int {
		global $wpdb;
		$table = Schema::table( 'contacts' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
	}

	/**
	 * Loescht einen Kontakt und seine abhaengigen Zeilen.
	 *
	 * @param int $id Kontakt-ID.
	 * @return bool
	 */
	public function delete( int $id ): bool {
		global $wpdb;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->delete( Schema::table( 'contact_meta' ), array( 'contact_id' => $id ) );
		$wpdb->delete( Schema::table( 'tag_rel' ), array( 'contact_id' => $id ) );
		$wpdb->delete( Schema::table( 'stage_history' ), array( 'contact_id' => $id ) );
		$wpdb->delete( Schema::table( 'tasks' ), array( 'contact_id' => $id ) );
		$wpdb->delete( Schema::table( 'subscriptions' ), array( 'contact_id' => $id ) );
		$wpdb->delete( Schema::table( 'mail_queue' ), array( 'contact_id' => $id ) );
		$wpdb->delete( Schema::table( 'mail_events' ), array( 'contact_id' => $id ) );
		$deleted = $wpdb->delete( Schema::table( 'contacts' ), array( 'id' => $id ) );
		// phpcs:enable
		return (bool) $deleted;
	}

	/**
	 * IDs aller Kontakte zu einer E-Mail (Privacy-Export/-Loeschung).
	 *
	 * @param string $email E-Mail.
	 * @return array<int, int>
	 */
	public function ids_by_email( string $email ): array {
		$email = sanitize_email( $email );
		if ( '' === $email ) {
			return array();
		}
		global $wpdb;
		$table = Schema::table( 'contacts' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$ids = $wpdb->get_col( $wpdb->prepare( "SELECT id FROM {$table} WHERE email = %s", $email ) );
		return array_map( 'intval', $ids ?: array() );
	}

	/**
	 * Loescht alle Kontakte zu einer E-Mail samt abhaengiger Zeilen
	 * (Recht auf Vergessen).
	 *
	 * @param string $email E-Mail.
	 * @return int Anzahl geloeschter Kontakte.
	 */
	public function delete_by_email( string $email ): int {
		$count = 0;
		foreach ( $this->ids_by_email( $email ) as $id ) {
			if ( $this->delete( $id ) ) {
				$count++;
			}
		}
		return $count;
	}

	/**
	 * Loescht Kontakte, die aelter als die Aufbewahrungsfrist sind
	 * (samt abhaengiger Zeilen). 0 = keine Loeschung.
	 *
	 * @param int $months Frist in Monaten.
	 * @return int Anzahl geloeschter Kontakte.
	 */
	public function delete_older_than_months( int $months ): int {
		if ( $months <= 0 ) {
			return 0;
		}
		global $wpdb;
		$table  = Schema::table( 'contacts' );
		$cutoff = gmdate( 'Y-m-d H:i:s', time() - ( $months * 30 * DAY_IN_SECONDS ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$ids = $wpdb->get_col( $wpdb->prepare( "SELECT id FROM {$table} WHERE created_at < %s", $cutoff ) );

		$count = 0;
		foreach ( array_map( 'intval', $ids ?: array() ) as $id ) {
			if ( $this->delete( $id ) ) {
				$count++;
			}
		}
		return $count;
	}

	/**
	 * Normalisiert eine Datenbankzeile.
	 *
	 * @param array<string, mixed> $row Rohzeile.
	 * @return array<string, mixed>
	 */
	private function hydrate( array $row ): array {
		$row['id']         = (int) $row['id'];
		$row['email']      = null !== $row['email'] ? (string) $row['email'] : '';
		$row['stage_id']   = null !== $row['stage_id'] ? (int) $row['stage_id'] : null;
		$row['consent_id'] = null !== $row['consent_id'] ? (int) $row['consent_id'] : null;
		if ( array_key_exists( 'tracking_consent', $row ) ) {
			$row['tracking_consent'] = (int) $row['tracking_consent'];
		}
		return $row;
	}
}
