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

namespace Hafenstudios\Leadlotse\Db;

defined( 'ABSPATH' ) || exit;

/**
 * Repository fuer die Custom Table `hs_entries` (D5).
 *
 * Massendaten liegen bewusst NICHT als CPT vor: JSON-Spalte fuer die Feldwerte
 * plus separate, indizierte Spalten fuer E-Mail, Status, Datum, form_id und
 * contact_id (nullable, Kurato-Anbindung Phase 2) sowie Quelle/UTM.
 */
final class EntryRepository {

	public const DB_VERSION = '1.1.0';

	/**
	 * Erlaubte Entry-Status (Mini-Inbox laut BAUPLAN 12).
	 *
	 * @var array<int, string>
	 */
	public const STATUSES = array( 'new', 'in_progress', 'done' );

	/**
	 * Vollstaendiger Tabellenname.
	 *
	 * @return string
	 */
	public static function table(): string {
		global $wpdb;
		return $wpdb->prefix . 'hs_entries';
	}

	/**
	 * Registriert die Migration(en) am uebergebenen Core-Migrator.
	 *
	 * @param \Hafenstudios\Core\Db\Migrator $migrator Core-Migrator (Namespace leadlotse).
	 * @return void
	 */
	public static function register_migrations( $migrator ): void {
		$table = self::table();

		$migrator->register(
			'1.0.0',
			static function ( $wpdb, $charset_collate ) use ( $table ) {
				$sql = "CREATE TABLE {$table} (
					id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
					form_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
					contact_id BIGINT UNSIGNED NULL,
					email VARCHAR(190) NOT NULL DEFAULT '',
					status VARCHAR(20) NOT NULL DEFAULT 'new',
					purpose VARCHAR(60) NOT NULL DEFAULT '',
					field_values LONGTEXT NULL,
					source_url VARCHAR(255) NOT NULL DEFAULT '',
					utm_source VARCHAR(100) NOT NULL DEFAULT '',
					utm_medium VARCHAR(100) NOT NULL DEFAULT '',
					utm_campaign VARCHAR(100) NOT NULL DEFAULT '',
					consent_id BIGINT UNSIGNED NULL,
					doi_token_hash VARCHAR(64) NOT NULL DEFAULT '',
					doi_confirmed_at DATETIME NULL,
					created_at DATETIME NOT NULL,
					updated_at DATETIME NULL,
					PRIMARY KEY  (id),
					KEY form_id (form_id),
					KEY contact_id (contact_id),
					KEY email (email),
					KEY status (status),
					KEY created_at (created_at),
					KEY utm_source (utm_source)
				) {$charset_collate};";
				dbDelta( $sql );
			}
		);

		// 1.1.0 (L-R1): Check-in-Zeitstempel fuer Veranstaltungs-Tickets.
		// dbDelta ergaenzt die neue Spalte in der bestehenden Tabelle.
		$migrator->register(
			'1.1.0',
			static function ( $wpdb, $charset_collate ) use ( $table ) {
				$sql = "CREATE TABLE {$table} (
					id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
					form_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
					contact_id BIGINT UNSIGNED NULL,
					email VARCHAR(190) NOT NULL DEFAULT '',
					status VARCHAR(20) NOT NULL DEFAULT 'new',
					purpose VARCHAR(60) NOT NULL DEFAULT '',
					field_values LONGTEXT NULL,
					source_url VARCHAR(255) NOT NULL DEFAULT '',
					utm_source VARCHAR(100) NOT NULL DEFAULT '',
					utm_medium VARCHAR(100) NOT NULL DEFAULT '',
					utm_campaign VARCHAR(100) NOT NULL DEFAULT '',
					consent_id BIGINT UNSIGNED NULL,
					doi_token_hash VARCHAR(64) NOT NULL DEFAULT '',
					doi_confirmed_at DATETIME NULL,
					checked_in_at DATETIME NULL,
					created_at DATETIME NOT NULL,
					updated_at DATETIME NULL,
					PRIMARY KEY  (id),
					KEY form_id (form_id),
					KEY contact_id (contact_id),
					KEY email (email),
					KEY status (status),
					KEY created_at (created_at),
					KEY utm_source (utm_source)
				) {$charset_collate};";
				dbDelta( $sql );
			}
		);
	}

	/**
	 * Speichert einen neuen Entry.
	 *
	 * @param array<string, mixed> $data Entry-Daten.
	 * @return int Entry-ID (0 bei Fehler).
	 */
	public function insert( array $data ): int {
		global $wpdb;

		$values = $data['field_values'] ?? array();

		$row = array(
			'form_id'        => absint( $data['form_id'] ?? 0 ),
			'contact_id'     => isset( $data['contact_id'] ) ? absint( $data['contact_id'] ) : null,
			'email'          => sanitize_email( (string) ( $data['email'] ?? '' ) ),
			'status'         => $this->sanitize_status( (string) ( $data['status'] ?? 'new' ) ),
			'purpose'        => substr( sanitize_key( (string) ( $data['purpose'] ?? '' ) ), 0, 60 ),
			'field_values'   => wp_json_encode( is_array( $values ) ? $values : array() ),
			'source_url'     => substr( esc_url_raw( (string) ( $data['source_url'] ?? '' ) ), 0, 255 ),
			'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'     => isset( $data['consent_id'] ) ? absint( $data['consent_id'] ) : null,
			'doi_token_hash' => substr( (string) ( $data['doi_token_hash'] ?? '' ), 0, 64 ),
			'created_at'     => current_time( 'mysql', true ),
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$ok = $wpdb->insert( self::table(), $row );

		return $ok ? (int) $wpdb->insert_id : 0;
	}

	/**
	 * Liefert einen Entry (Feldwerte als Array dekodiert).
	 *
	 * @param int $id Entry-ID.
	 * @return array<string, mixed>|null
	 */
	public function get( int $id ): ?array {
		global $wpdb;
		$table = self::table();
		// 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;
	}

	/**
	 * Listet Entries mit Pagination, Status-Filter, Formular-Filter und Suche.
	 *
	 * @param array{
	 *   page?:int, per_page?:int, status?:string, form_id?:int, search?:string
	 * } $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 = self::table();

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

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

		if ( ! empty( $args['status'] ) && in_array( $args['status'], self::STATUSES, true ) ) {
			$where[]  = 'status = %s';
			$params[] = $args['status'];
		}
		if ( ! empty( $args['form_id'] ) ) {
			$where[]  = 'form_id = %d';
			$params[] = absint( $args['form_id'] );
		}
		if ( ! empty( $args['search'] ) ) {
			$like     = '%' . $wpdb->esc_like( sanitize_text_field( (string) $args['search'] ) ) . '%';
			$where[]  = '(email LIKE %s OR field_values LIKE %s)';
			$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,
		);
	}

	/**
	 * Aktualisiert den Status eines Entries.
	 *
	 * @param int    $id     Entry-ID.
	 * @param string $status Neuer Status.
	 * @return bool
	 */
	public function update_status( int $id, string $status ): bool {
		global $wpdb;

		$status = $this->sanitize_status( $status );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$updated = $wpdb->update(
			self::table(),
			array(
				'status'     => $status,
				'updated_at' => current_time( 'mysql', true ),
			),
			array( 'id' => $id )
		);

		return false !== $updated;
	}

	/**
	 * Markiert einen Entry als DOI-bestaetigt und entwertet den Token.
	 *
	 * @param int $id Entry-ID.
	 * @return bool
	 */
	public function mark_doi_confirmed( int $id ): bool {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$updated = $wpdb->update(
			self::table(),
			array(
				'doi_confirmed_at' => current_time( 'mysql', true ),
				'doi_token_hash'   => '',
				'updated_at'       => current_time( 'mysql', true ),
			),
			array( 'id' => $id )
		);

		return false !== $updated;
	}

	/**
	 * Markiert einen Entry als eingecheckt (L-R1, Zeitstempel einmalig).
	 *
	 * @param int $id Entry-ID.
	 * @return bool
	 */
	public function mark_checked_in( int $id ): bool {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$updated = $wpdb->update(
			self::table(),
			array(
				'checked_in_at' => current_time( 'mysql', true ),
				'updated_at'    => current_time( 'mysql', true ),
			),
			array( 'id' => $id )
		);

		return false !== $updated;
	}

	/**
	 * Alle Entries eines Formulars, neueste zuerst (Teilnehmer-Sicht und
	 * Kapazitaetszaehlung, L-R1; bewusst ohne Pagination - Veranstaltungs-
	 * Anmeldelisten sind ueberschaubar).
	 *
	 * @param int $form_id Formular-ID.
	 * @return array<int, array<string, mixed>>
	 */
	public function all_for_form( int $form_id ): array {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE form_id = %d ORDER BY created_at DESC, id DESC", absint( $form_id ) ), ARRAY_A );
		return array_map( array( $this, 'hydrate' ), $rows ?: array() );
	}

	/**
	 * Findet Entries zu einer E-Mail (Privacy-Export).
	 *
	 * @param string $email E-Mail.
	 * @return array<int, array<string, mixed>>
	 */
	public function find_by_email( string $email ): array {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE email = %s ORDER BY created_at DESC", sanitize_email( $email ) ), ARRAY_A );
		return array_map( array( $this, 'hydrate' ), $rows ?: array() );
	}

	/**
	 * Loescht alle Entries zu einer E-Mail (Recht auf Vergessen).
	 *
	 * @param string $email E-Mail.
	 * @return int Anzahl geloeschter Zeilen.
	 */
	public function delete_by_email( string $email ): int {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (int) $wpdb->delete( self::table(), array( 'email' => sanitize_email( $email ) ) );
	}

	/**
	 * Loescht einen Entry.
	 *
	 * @param int $id Entry-ID.
	 * @return bool
	 */
	public function delete( int $id ): bool {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (bool) $wpdb->delete( self::table(), array( 'id' => $id ) );
	}

	/**
	 * Loescht unbestaetigte DOI-Anmeldungen, deren Token laengst abgelaufen ist
	 * (Datenminimierung). Aufruf aus dem taeglichen Wartungs-Cron.
	 *
	 * @param int $older_than_days Alter in Tagen.
	 * @return int Anzahl geloeschter Zeilen.
	 */
	public function delete_expired_doi( int $older_than_days ): int {
		global $wpdb;
		$table  = self::table();
		$cutoff = gmdate( 'Y-m-d H:i:s', time() - ( max( 1, $older_than_days ) * DAY_IN_SECONDS ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return (int) $wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE doi_token_hash <> '' AND doi_confirmed_at IS NULL AND created_at < %s", $cutoff ) );
	}

	/**
	 * Loescht Entries, die aelter als die konfigurierte Aufbewahrungsfrist sind.
	 *
	 * @param int $months Frist in Monaten (0 = keine Loeschung).
	 * @return int Anzahl geloeschter Zeilen.
	 */
	public function delete_older_than_months( int $months ): int {
		if ( $months <= 0 ) {
			return 0;
		}
		global $wpdb;
		$table  = self::table();
		$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
		return (int) $wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE created_at < %s", $cutoff ) );
	}

	/**
	 * Gesamtzahl aller Einsendungen (fuer die Einrichtungs-Checkliste, Etappe 5).
	 *
	 * @return int
	 */
	public function count_all(): int {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
	}

	/**
	 * Anzahl unbeantworteter Anfragen (Status "new"), die aelter als N Tage sind
	 * (Hans-Hinweis in der Inbox, UX-Ausloeser "3+ unbeantwortete Anfragen").
	 *
	 * @param int $days Mindestalter in Tagen.
	 * @return int
	 */
	public function count_stale_new( int $days ): int {
		global $wpdb;
		$table  = self::table();
		$cutoff = gmdate( 'Y-m-d H:i:s', time() - ( max( 0, $days ) * DAY_IN_SECONDS ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE status = 'new' AND created_at < %s", $cutoff ) );
	}

	/**
	 * Anzahl Einsendungen je Formular (fuer die Formularliste, Etappe 3).
	 * Ein einzelnes GROUP-BY statt N Einzelabfragen (Performance-Ziel UX 8.4).
	 *
	 * @return array<int, int> form_id => Anzahl.
	 */
	public function count_by_form(): array {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results( "SELECT form_id, COUNT(*) AS total FROM {$table} GROUP BY form_id", ARRAY_A );

		$counts = array();
		foreach ( (array) $rows as $row ) {
			$counts[ (int) $row['form_id'] ] = (int) $row['total'];
		}
		return $counts;
	}

	/**
	 * Entfernt die Tabelle (nur Deinstallation mit aktivierter Datenloeschung).
	 *
	 * @return void
	 */
	public static function drop(): void {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query( "DROP TABLE IF EXISTS {$table}" );
	}

	/**
	 * Dekodiert eine Datenbankzeile.
	 *
	 * @param array<string, mixed> $row Rohzeile.
	 * @return array<string, mixed>
	 */
	private function hydrate( array $row ): array {
		$decoded             = json_decode( (string) ( $row['field_values'] ?? '' ), true );
		$row['field_values'] = is_array( $decoded ) ? $decoded : array();
		$row['id']           = (int) $row['id'];
		$row['form_id']      = (int) $row['form_id'];
		$row['contact_id']   = null !== $row['contact_id'] ? (int) $row['contact_id'] : null;
		$row['consent_id']   = null !== $row['consent_id'] ? (int) $row['consent_id'] : null;
		return $row;
	}

	/**
	 * Validiert einen Status gegen die erlaubte Liste.
	 *
	 * @param string $status Status.
	 * @return string
	 */
	private function sanitize_status( string $status ): string {
		return in_array( $status, self::STATUSES, true ) ? $status : 'new';
	}
}
