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

namespace Hafenstudios\Kurato\Db;

defined( 'ABSPATH' ) || exit;

/**
 * Zentrale Schema-Definition fuer Kurato (BAUPLAN Abschnitt 25).
 *
 * Custom Tables statt CPT (Massendaten, Performance, Indizes). Alle Tabellen
 * tragen den WP-Praefix. Die CRM-Kern-Tabellen entstehen in Version 1.0.0;
 * Newsletter/Versand und Termine kommen in spaeteren Versionen als eigene
 * Migrationen (der Core-Migrator versioniert inkrementell).
 *
 * Tabellen 1.0.0:
 * - hs_contacts        Kontakte (Stammdaten, aktuelle Pipeline-Stufe, Quelle)
 * - hs_contact_meta    flexible Zusatzfelder je Kontakt
 * - hs_tags            Tags (frei definierbar)
 * - hs_tag_rel         Zuordnung Tag zu Kontakt
 * - hs_stages          Pipeline-Stufen (konfigurierbar)
 * - hs_stage_history   Verlauf der Stufenwechsel je Kontakt
 * - hs_tasks           Wiedervorlagen (Nachfass-Aufgaben)
 */
final class Schema {

	public const DB_VERSION = '1.3.0';

	/**
	 * Voller Tabellenname fuer einen Kurznamen (z.B. "contacts").
	 *
	 * @param string $name Kurzname ohne Praefix und ohne hs_.
	 * @return string
	 */
	public static function table( string $name ): string {
		global $wpdb;
		return $wpdb->prefix . 'hs_' . $name;
	}

	/**
	 * Registriert alle Migrationen am Core-Migrator.
	 *
	 * @param \Hafenstudios\Core\Db\Migrator $migrator Core-Migrator (Namespace kurato).
	 * @return void
	 */
	public static function register_migrations( $migrator ): void {
		$migrator->register( '1.0.0', array( self::class, 'migrate_1_0_0' ) );
		$migrator->register( '1.1.0', array( self::class, 'migrate_1_1_0' ) );
		$migrator->register( '1.2.0', array( self::class, 'migrate_1_2_0' ) );
		$migrator->register( '1.3.0', array( self::class, 'migrate_1_3_0' ) );
	}

	/**
	 * Legt die CRM-Kern-Tabellen an.
	 *
	 * @param \wpdb  $wpdb            WordPress-Datenbank.
	 * @param string $charset_collate Charset/Collation-Klausel.
	 * @return void
	 */
	public static function migrate_1_0_0( $wpdb, string $charset_collate ): void {
		$contacts      = self::table( 'contacts' );
		$contact_meta  = self::table( 'contact_meta' );
		$tags          = self::table( 'tags' );
		$tag_rel       = self::table( 'tag_rel' );
		$stages        = self::table( 'stages' );
		$stage_history = self::table( 'stage_history' );
		$tasks         = self::table( 'tasks' );

		dbDelta(
			"CREATE TABLE {$contacts} (
				id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				email VARCHAR(190) NULL DEFAULT NULL,
				first_name VARCHAR(100) NOT NULL DEFAULT '',
				last_name VARCHAR(100) NOT NULL DEFAULT '',
				display_name VARCHAR(200) NOT NULL DEFAULT '',
				phone VARCHAR(50) NOT NULL DEFAULT '',
				stage_id BIGINT UNSIGNED NULL,
				source VARCHAR(100) 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,
				created_at DATETIME NOT NULL,
				updated_at DATETIME NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY email (email),
				KEY stage_id (stage_id),
				KEY created_at (created_at)
			) {$charset_collate};"
		);

		dbDelta(
			"CREATE TABLE {$contact_meta} (
				id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				contact_id BIGINT UNSIGNED NOT NULL,
				meta_key VARCHAR(190) NOT NULL DEFAULT '',
				meta_value LONGTEXT NULL,
				PRIMARY KEY  (id),
				KEY contact_id (contact_id),
				KEY meta_key (meta_key)
			) {$charset_collate};"
		);

		dbDelta(
			"CREATE TABLE {$tags} (
				id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				slug VARCHAR(100) NOT NULL DEFAULT '',
				label VARCHAR(190) NOT NULL DEFAULT '',
				created_at DATETIME NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY slug (slug)
			) {$charset_collate};"
		);

		dbDelta(
			"CREATE TABLE {$tag_rel} (
				tag_id BIGINT UNSIGNED NOT NULL,
				contact_id BIGINT UNSIGNED NOT NULL,
				PRIMARY KEY  (tag_id, contact_id),
				KEY contact_id (contact_id)
			) {$charset_collate};"
		);

		dbDelta(
			"CREATE TABLE {$stages} (
				id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				slug VARCHAR(100) NOT NULL DEFAULT '',
				label VARCHAR(190) NOT NULL DEFAULT '',
				sort_order INT NOT NULL DEFAULT 0,
				is_won TINYINT(1) NOT NULL DEFAULT 0,
				is_lost TINYINT(1) NOT NULL DEFAULT 0,
				PRIMARY KEY  (id),
				KEY sort_order (sort_order)
			) {$charset_collate};"
		);

		dbDelta(
			"CREATE TABLE {$stage_history} (
				id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				contact_id BIGINT UNSIGNED NOT NULL,
				from_stage_id BIGINT UNSIGNED NULL,
				to_stage_id BIGINT UNSIGNED NULL,
				changed_at DATETIME NOT NULL,
				user_id BIGINT UNSIGNED NULL,
				PRIMARY KEY  (id),
				KEY contact_id (contact_id)
			) {$charset_collate};"
		);

		dbDelta(
			"CREATE TABLE {$tasks} (
				id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				contact_id BIGINT UNSIGNED NOT NULL,
				title VARCHAR(255) NOT NULL DEFAULT '',
				due_date DATE NULL,
				done TINYINT(1) NOT NULL DEFAULT 0,
				created_at DATETIME NOT NULL,
				PRIMARY KEY  (id),
				KEY contact_id (contact_id),
				KEY due_date (due_date)
			) {$charset_collate};"
		);
	}

	/**
	 * Newsletter/Versand-Tabellen (K4a) und Kontakt-Erweiterungen.
	 *
	 * @param \wpdb  $wpdb            WordPress-Datenbank.
	 * @param string $charset_collate Charset/Collation-Klausel.
	 * @return void
	 */
	public static function migrate_1_1_0( $wpdb, string $charset_collate ): void {
		$lists         = self::table( 'lists' );
		$subscriptions = self::table( 'subscriptions' );
		$segments      = self::table( 'segments' );
		$campaigns     = self::table( 'campaigns' );
		$queue         = self::table( 'mail_queue' );
		$events        = self::table( 'mail_events' );

		dbDelta(
			"CREATE TABLE {$lists} (
				id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				slug VARCHAR(100) NOT NULL DEFAULT '',
				label VARCHAR(190) NOT NULL DEFAULT '',
				description TEXT NULL,
				doi_required TINYINT(1) NOT NULL DEFAULT 1,
				created_at DATETIME NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY slug (slug)
			) {$charset_collate};"
		);

		dbDelta(
			"CREATE TABLE {$subscriptions} (
				id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				list_id BIGINT UNSIGNED NOT NULL,
				contact_id BIGINT UNSIGNED NOT NULL,
				status VARCHAR(20) NOT NULL DEFAULT 'pending',
				doi_token_hash VARCHAR(64) NOT NULL DEFAULT '',
				consent_id BIGINT UNSIGNED NULL,
				source VARCHAR(100) NOT NULL DEFAULT '',
				subscribed_at DATETIME NOT NULL,
				confirmed_at DATETIME NULL,
				unsubscribed_at DATETIME NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY list_contact (list_id, contact_id),
				KEY status (status),
				KEY contact_id (contact_id)
			) {$charset_collate};"
		);

		dbDelta(
			"CREATE TABLE {$segments} (
				id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				label VARCHAR(190) NOT NULL DEFAULT '',
				definition LONGTEXT NULL,
				created_at DATETIME NOT NULL,
				updated_at DATETIME NULL,
				PRIMARY KEY  (id)
			) {$charset_collate};"
		);

		dbDelta(
			"CREATE TABLE {$campaigns} (
				id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				post_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
				status VARCHAR(20) NOT NULL DEFAULT 'draft',
				audience LONGTEXT NULL,
				scheduled_at DATETIME NULL,
				started_at DATETIME NULL,
				finished_at DATETIME NULL,
				total_recipients BIGINT UNSIGNED NOT NULL DEFAULT 0,
				sent_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
				failed_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
				created_at DATETIME NOT NULL,
				PRIMARY KEY  (id),
				KEY post_id (post_id),
				KEY status (status)
			) {$charset_collate};"
		);

		dbDelta(
			"CREATE TABLE {$queue} (
				id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				campaign_id BIGINT UNSIGNED NULL,
				contact_id BIGINT UNSIGNED NOT NULL,
				to_email VARCHAR(190) NOT NULL DEFAULT '',
				status VARCHAR(20) NOT NULL DEFAULT 'pending',
				attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
				claimed_at DATETIME NULL,
				scheduled_at DATETIME NULL,
				sent_at DATETIME NULL,
				provider_message_id VARCHAR(190) NOT NULL DEFAULT '',
				last_error VARCHAR(255) NOT NULL DEFAULT '',
				PRIMARY KEY  (id),
				UNIQUE KEY campaign_contact (campaign_id, contact_id),
				KEY status_scheduled (status, scheduled_at)
			) {$charset_collate};"
		);

		dbDelta(
			"CREATE TABLE {$events} (
				id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				campaign_id BIGINT UNSIGNED NULL,
				contact_id BIGINT UNSIGNED NOT NULL,
				queue_id BIGINT UNSIGNED NULL,
				type VARCHAR(30) NOT NULL DEFAULT '',
				detail VARCHAR(255) NOT NULL DEFAULT '',
				created_at DATETIME NOT NULL,
				PRIMARY KEY  (id),
				KEY campaign_type (campaign_id, type),
				KEY contact_id (contact_id)
			) {$charset_collate};"
		);

		// Kontakt-Erweiterungen (idempotent per Spalten-Existenzpruefung).
		self::add_column( 'contacts', 'email_status', "VARCHAR(30) NOT NULL DEFAULT 'active'" );
		self::add_column( 'contacts', 'tracking_consent', 'TINYINT(1) NOT NULL DEFAULT 0' );
		self::add_column( 'contacts', 'last_activity_at', 'DATETIME NULL' );
	}

	/**
	 * Termine-Buchungsmodul (K5a). Vier Tabellen fuer das gekapselte Booking-
	 * Modul (auskoppelbar, BAUPLAN 13; K5-KONZEPT Abschnitt 2). Alle Zeitspalten
	 * halten UTC (start_utc/end_utc DATETIME, cancelled_at DATETIME NULL); die
	 * Wochenraster-Zeiten (hs_availability) sind Site-Zeitzonen-Wanduhrzeiten und
	 * werden erst vom SlotCalculator nach UTC umgerechnet.
	 *
	 * Doppelbuchungs-Schutz auf DB-Ebene: UNIQUE(host_id, start_utc, status). Ein
	 * stornierter Slot bleibt wieder buchbar, weil der Status Teil des Schluessels
	 * ist (eine confirmed- UND eine cancelled-Zeile duerfen je Slot koexistieren).
	 * Die confirmed-Eindeutigkeit ist die eigentliche Garantie; der Insert-Race
	 * wird wie beim K3c-Muster abgefangen (BookingRepository::create).
	 *
	 * @param \wpdb  $wpdb            WordPress-Datenbank.
	 * @param string $charset_collate Charset/Collation-Klausel.
	 * @return void
	 */
	public static function migrate_1_2_0( $wpdb, string $charset_collate ): void {
		$hosts         = self::table( 'hosts' );
		$booking_types = self::table( 'booking_types' );
		$availability  = self::table( 'availability' );
		$bookings      = self::table( 'bookings' );

		dbDelta(
			"CREATE TABLE {$hosts} (
				id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				label VARCHAR(190) NOT NULL DEFAULT '',
				slug VARCHAR(100) NOT NULL DEFAULT '',
				role VARCHAR(190) NOT NULL DEFAULT '',
				photo_id BIGINT UNSIGNED NULL,
				user_id BIGINT UNSIGNED NULL,
				active TINYINT(1) NOT NULL DEFAULT 1,
				created_at DATETIME NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY slug (slug)
			) {$charset_collate};"
		);

		dbDelta(
			"CREATE TABLE {$booking_types} (
				id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				host_id BIGINT UNSIGNED NOT NULL,
				label VARCHAR(190) NOT NULL DEFAULT '',
				slug VARCHAR(100) NOT NULL DEFAULT '',
				duration_min SMALLINT UNSIGNED NOT NULL DEFAULT 30,
				location_type VARCHAR(20) NOT NULL DEFAULT 'phone',
				location_detail VARCHAR(255) NOT NULL DEFAULT '',
				buffer_before_min SMALLINT UNSIGNED NOT NULL DEFAULT 0,
				buffer_after_min SMALLINT UNSIGNED NOT NULL DEFAULT 0,
				lead_time_hours SMALLINT UNSIGNED NOT NULL DEFAULT 0,
				horizon_days SMALLINT UNSIGNED NOT NULL DEFAULT 60,
				active TINYINT(1) NOT NULL DEFAULT 1,
				PRIMARY KEY  (id),
				KEY host_id (host_id),
				UNIQUE KEY host_slug (host_id, slug)
			) {$charset_collate};"
		);

		dbDelta(
			"CREATE TABLE {$availability} (
				id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				host_id BIGINT UNSIGNED NOT NULL,
				booking_type_id BIGINT UNSIGNED NULL,
				kind VARCHAR(20) NOT NULL DEFAULT 'weekly',
				weekday TINYINT UNSIGNED NULL,
				start_time TIME NULL,
				end_time TIME NULL,
				date_from DATE NULL,
				date_to DATE NULL,
				PRIMARY KEY  (id),
				KEY host_kind (host_id, kind)
			) {$charset_collate};"
		);

		dbDelta(
			"CREATE TABLE {$bookings} (
				id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				contact_id BIGINT UNSIGNED NULL,
				host_id BIGINT UNSIGNED NOT NULL,
				type_id BIGINT UNSIGNED NOT NULL,
				start_utc DATETIME NOT NULL,
				end_utc DATETIME NOT NULL,
				status VARCHAR(20) NOT NULL DEFAULT 'confirmed',
				note TEXT NULL,
				reminder_sent TINYINT(1) NOT NULL DEFAULT 0,
				created_at DATETIME NOT NULL,
				cancelled_at DATETIME NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY host_start_status (host_id, start_utc, status),
				KEY host_start (host_id, start_utc),
				KEY contact_id (contact_id),
				KEY status_start (status, start_utc)
			) {$charset_collate};"
		);
	}

	/**
	 * Empfehlungs-Lernschleife (H1, HANS-KI-KONZEPT Schicht 4). Eine Tabelle
	 * hs_recommendations protokolliert je erzeugter Hans-Empfehlung die
	 * Entscheidung des Betreibers (accepted/dismissed/expired) und das spaeter
	 * gemessene Ergebnis (advanced/converted/none). Grundlage der Regel-
	 * Gewichtung, die in H2/H4 ausgewertet wird; H1 legt nur Tabelle und
	 * Schreib-/Lese-Wege an.
	 *
	 * Spalten exakt nach Konzept Abschnitt 2/Schicht 4: rule_key (welche Regel),
	 * context_hash (Dedup je Kontext, keine Klardaten), created_at, decision +
	 * decided_at, outcome + outcome_at.
	 *
	 * @param \wpdb  $wpdb            WordPress-Datenbank.
	 * @param string $charset_collate Charset/Collation-Klausel.
	 * @return void
	 */
	public static function migrate_1_3_0( $wpdb, string $charset_collate ): void {
		$recommendations = self::table( 'recommendations' );

		dbDelta(
			"CREATE TABLE {$recommendations} (
				id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				rule_key VARCHAR(60) NOT NULL DEFAULT '',
				context_hash CHAR(64) NOT NULL DEFAULT '',
				created_at DATETIME NOT NULL,
				decision VARCHAR(20) NOT NULL DEFAULT '',
				decided_at DATETIME NULL,
				outcome VARCHAR(20) NOT NULL DEFAULT '',
				outcome_at DATETIME NULL,
				PRIMARY KEY  (id),
				KEY rule_key (rule_key),
				KEY rule_context (rule_key, context_hash),
				KEY decision (decision),
				KEY created_at (created_at)
			) {$charset_collate};"
		);
	}

	/**
	 * Fuegt eine Spalte hinzu, falls sie noch nicht existiert (fuer Migrationen
	 * auf Bestandstabellen; dbDelta braucht sonst die volle Tabellendefinition).
	 *
	 * @param string $table_short Kurzname der Tabelle.
	 * @param string $column      Spaltenname.
	 * @param string $definition  SQL-Typdefinition.
	 * @return void
	 */
	private static function add_column( string $table_short, string $column, string $definition ): void {
		global $wpdb;
		$table = self::table( $table_short );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$exists = $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM {$table} LIKE %s", $column ) );
		if ( $exists ) {
			return;
		}
		// Spalten- und Typname stammen aus Konstanten dieser Klasse, nicht aus Eingaben.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query( "ALTER TABLE {$table} ADD COLUMN {$column} {$definition}" );
	}

	/**
	 * Entfernt alle Kurato-Tabellen (nur Deinstallation mit Datenloeschung).
	 *
	 * @return void
	 */
	public static function drop(): void {
		global $wpdb;
		foreach ( array( 'recommendations', 'bookings', 'availability', 'booking_types', 'hosts', 'mail_events', 'mail_queue', 'campaigns', 'segments', 'subscriptions', 'lists', 'tasks', 'stage_history', 'stages', 'tag_rel', 'tags', 'contact_meta', 'contacts' ) as $name ) {
			$table = self::table( $name );
			// 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}" );
		}
	}
}
