<?php
defined( 'ABSPATH' ) || exit;

/**
 * Schema + Datenzugriff.
 *
 * events: Roh-Events (kurzlebig, nach 30 Tagen geprunt). Keine IP, keine
 *         persistente Besucher-ID — session_key stirbt mit dem Browser-Tab.
 * stats:  Aggregat je Section × Kontext-Bucket. Grundlage des Bandit-Modells.
 * daily:  Tagessummen je Seite × Gerät × Gruppe, 36 Monate (ADSEC_Daily).
 */
class ADSEC_Database {

	public static function events_table() {
		global $wpdb;
		return $wpdb->prefix . 'adsec_events';
	}

	public static function stats_table() {
		global $wpdb;
		return $wpdb->prefix . 'adsec_stats';
	}

	public static function daily_table() {
		global $wpdb;
		return $wpdb->prefix . 'adsec_daily';
	}

	public static function sections_table() {
		global $wpdb;
		return $wpdb->prefix . 'adsec_sections';
	}

	public static function install() {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$charset = $wpdb->get_charset_collate();
		$events  = self::events_table();
		$stats   = self::stats_table();

		dbDelta( "CREATE TABLE {$events} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			session_key CHAR(16) NOT NULL,
			page_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			section_id VARCHAR(32) NOT NULL,
			event_type VARCHAR(16) NOT NULL,
			value FLOAT NOT NULL DEFAULT 1,
			ctx_bucket VARCHAR(96) NOT NULL,
			variant CHAR(1) NOT NULL DEFAULT 'b',
			created_at DATETIME NOT NULL,
			processed TINYINT(1) NOT NULL DEFAULT 0,
			PRIMARY KEY  (id),
			KEY processed_created (processed, created_at),
			KEY created_at (created_at)
		) {$charset};" );

		$sections = self::sections_table();
		dbDelta( "CREATE TABLE {$sections} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			uid VARCHAR(32) NOT NULL,
			page_id BIGINT UNSIGNED NOT NULL,
			position SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			heading VARCHAR(160) NOT NULL DEFAULT '',
			classes VARCHAR(160) NOT NULL DEFAULT '',
			excerpt VARCHAR(200) NOT NULL DEFAULT '',
			features TEXT NULL,
			ai_label VARCHAR(80) NOT NULL DEFAULT '',
			topic VARCHAR(48) NOT NULL DEFAULT 'unbekannt',
			topic_source VARCHAR(8) NOT NULL DEFAULT 'auto',
			pinned TINYINT(1) NOT NULL DEFAULT 0,
			excluded TINYINT(1) NOT NULL DEFAULT 0,
			reusable TINYINT(1) NOT NULL DEFAULT 0,
			content_score TINYINT UNSIGNED NOT NULL DEFAULT 0,
			lead_in TINYINT(1) NOT NULL DEFAULT 0,
			lead_ai TINYINT(1) NOT NULL DEFAULT 2,
			link_next TINYINT(1) NOT NULL DEFAULT 2,
			html LONGTEXT NULL,
			first_seen DATETIME NOT NULL,
			last_seen DATETIME NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY page_uid (page_id, uid),
			KEY topic (topic),
			KEY reusable (reusable)
		) {$charset};" );

		dbDelta( "CREATE TABLE {$stats} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			section_id VARCHAR(32) NOT NULL,
			ctx_bucket VARCHAR(32) NOT NULL,
			trials BIGINT UNSIGNED NOT NULL DEFAULT 0,
			successes FLOAT NOT NULL DEFAULT 0,
			updated_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY section_ctx (section_id, ctx_bucket)
		) {$charset};" );

		// 0.40.0: Tageszusammenfassungen (36 Monate), siehe ADSEC_Daily.
		$daily = self::daily_table();
		dbDelta( "CREATE TABLE {$daily} (
			day DATE NOT NULL,
			scope CHAR(1) NOT NULL DEFAULT 'p',
			page_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			device VARCHAR(8) NOT NULL DEFAULT 'other',
			variant CHAR(1) NOT NULL DEFAULT 'c',
			sessions INT UNSIGNED NOT NULL DEFAULT 0,
			goal_sessions INT UNSIGNED NOT NULL DEFAULT 0,
			dwell FLOAT NOT NULL DEFAULT 0,
			pageviews INT UNSIGNED NOT NULL DEFAULT 0,
			PRIMARY KEY  (day,scope,page_id,device,variant),
			KEY scope_page (scope,page_id)
		) {$charset};" );
	}

	/**
	 * Event-Batch aus dem Tracker speichern. Eingaben sind bereits in der
	 * REST-Schicht validiert/whitelisted.
	 */
	public static function insert_events( array $events ) {
		global $wpdb;
		$table = self::events_table();
		$now   = current_time( 'mysql', true );

		foreach ( $events as $e ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table.
			$wpdb->insert( $table, array(
				'session_key' => $e['session_key'],
				'page_id'     => $e['page_id'],
				'section_id'  => $e['section_id'],
				'event_type'  => $e['event_type'],
				'value'       => $e['value'],
				'ctx_bucket'  => $e['ctx_bucket'],
				'variant'     => $e['variant'],
				'created_at'  => $now,
			), array( '%s', '%d', '%s', '%s', '%f', '%s', '%s', '%s' ) );
		}
	}

	public static function prune_events( $days ) {
		global $wpdb;
		$table = self::events_table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table.
		$wpdb->query( $wpdb->prepare(
			'DELETE FROM %i WHERE created_at < %s',
			$table,
			gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS )
		) );
	}

	/**
	 * Setzt alle Mess- und Lerndaten auf null zurück: Roh-Events, aggregierte
	 * Statistik und das gelernte Modell inkl. Champions/Verbesserungs-Log. Das
	 * Inhalts-Inventar (erkannte Abschnitte/Themen) und die Einstellungen bleiben
	 * unangetastet — das Dashboard startet danach bei 0 und zählt nur noch echte
	 * Besucher. Genutzt vom „Daten zurücksetzen"-Knopf (z. B. nach Test/Simulation).
	 */
	public static function reset_stats() {
		global $wpdb;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- empties the plugin's own tables.
		$wpdb->query( $wpdb->prepare( 'TRUNCATE TABLE %i', self::events_table() ) );
		$wpdb->query( $wpdb->prepare( 'TRUNCATE TABLE %i', self::stats_table() ) );
		$wpdb->query( $wpdb->prepare( 'TRUNCATE TABLE %i', self::daily_table() ) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		// Aggregat-/Lern-Optionen (vgl. ADSEC_Bandit-Konstanten).
		foreach ( array( 'adsec_model', 'adsec_totals', 'adsec_champions', 'adsec_promotions', 'adsec_eval_last', 'adsec_daily_last' ) as $opt ) {
			delete_option( $opt );
		}
	}
}