<?php
/**
 * Traffic rollup and retention worker.
 *
 * @package Niwa_AI
 */

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

class Niwa_AI_Traffic_Rollup_Worker {

	const ROLLUP_HOOK  = 'niwa_ai_traffic_rollup';
	const CLEANUP_HOOK = 'niwa_ai_traffic_cleanup';

	public function register_hooks(): void {
		add_action( self::ROLLUP_HOOK, array( $this, 'run_rollup' ) );
		add_action( self::CLEANUP_HOOK, array( $this, 'cleanup' ) );
	}

	public static function schedule(): void {
		if ( class_exists( 'Niwa_AI_Cron_Maintenance' ) ) {
			Niwa_AI_Cron_Maintenance::ensure_recurring_event( self::ROLLUP_HOOK, 'niwa_ai_fifteen_minutes', 300 );
			Niwa_AI_Cron_Maintenance::ensure_recurring_event( self::CLEANUP_HOOK, 'daily', HOUR_IN_SECONDS );
			return;
		}

		if ( ! wp_next_scheduled( self::ROLLUP_HOOK ) ) {
			wp_schedule_event( time() + 300, 'niwa_ai_fifteen_minutes', self::ROLLUP_HOOK );
		}
		if ( ! wp_next_scheduled( self::CLEANUP_HOOK ) ) {
			wp_schedule_event( time() + HOUR_IN_SECONDS, 'daily', self::CLEANUP_HOOK );
		}
	}

	public function run_rollup(): void {
		global $wpdb;
		Niwa_AI_Traffic_DB::ensure_schema();
		$lock_name = 'niwa_ai_traffic_rollup';
		$acquired  = intval( $wpdb->get_var( $wpdb->prepare( 'SELECT GET_LOCK(%s, 0)', $lock_name ) ) );
		if ( 1 !== $acquired ) {
			return;
		}

		$today = gmdate( 'Y-m-d' );
		$wpdb->query( 'START TRANSACTION' );
		try {
			$this->rollup_date( gmdate( 'Y-m-d', strtotime( '-1 day' ) ) );
			$this->rollup_date( $today );
			$wpdb->query( 'COMMIT' );
		} catch ( Throwable $e ) {
			$wpdb->query( 'ROLLBACK' );
			throw $e;
		} finally {
			$wpdb->get_var( $wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', $lock_name ) );
		}
	}

	public function cleanup(): void {
		global $wpdb;
		$retention_days = (int) apply_filters( 'niwa_ai_traffic_raw_retention_days', 180 );
		$retention_days = max( 30, min( 730, $retention_days ) );
		$cutoff         = gmdate( 'Y-m-d H:i:s', time() - ( $retention_days * DAY_IN_SECONDS ) );

		foreach ( array( Niwa_AI_Traffic_DB::EVENTS, Niwa_AI_Traffic_DB::TOUCHPOINTS ) as $slug ) {
			$table = Niwa_AI_Traffic_DB::table( $slug );
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE occurred_at < %s LIMIT 5000", $cutoff ) );
		}
	}

	private function rollup_date( $date ): void {
		global $wpdb;
		$date = preg_match( '/^\d{4}-\d{2}-\d{2}$/', (string) $date ) ? (string) $date : gmdate( 'Y-m-d' );

		$daily = Niwa_AI_Traffic_DB::table( Niwa_AI_Traffic_DB::DAILY );
		$events = Niwa_AI_Traffic_DB::table( Niwa_AI_Traffic_DB::EVENTS );
		$conversions = Niwa_AI_Traffic_DB::table( Niwa_AI_Traffic_DB::CONVERSIONS );

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query( $wpdb->prepare( "DELETE FROM {$daily} WHERE rollup_date = %s", $date ) );

		$this->insert_event_rollup( $date, 'channel', 'channel', $events, $daily );
		$this->insert_event_rollup( $date, 'campaign', 'campaign', $events, $daily );
		$this->insert_event_rollup( $date, 'page', 'page_path', $events, $daily );

		if ( $this->table_exists( Niwa_AI_Traffic_DB::CONVERSIONS ) ) {
			$this->apply_conversion_rollup( $date, 'channel', 'channel', $conversions, $daily );
			$this->apply_conversion_rollup( $date, 'campaign', 'campaign', $conversions, $daily );
			$this->apply_conversion_rollup( $date, 'page', 'landing_page_path', $conversions, $daily );
		}
	}

	private function insert_event_rollup( $date, $dimension_type, $field, $events, $daily ): void {
		global $wpdb;
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$sql = $wpdb->prepare(
			"INSERT INTO {$daily}
			(rollup_date,dimension_type,dimension_value,channel,source,medium,campaign,page_path,sessions,visitors,pageviews,events,engaged_events,created_at,updated_at)
			SELECT DATE(occurred_at), %s, {$field}, MAX(channel), MAX(source), MAX(medium), MAX(campaign), MAX(page_path),
				COUNT(DISTINCT session_id), COUNT(DISTINCT visitor_id),
				SUM(CASE WHEN event_type='pageview' THEN 1 ELSE 0 END),
				COUNT(*),
				SUM(CASE WHEN event_type IN ('engaged_view','scroll_depth','blog_read','lead','chat_message') THEN 1 ELSE 0 END),
				%s, %s
			FROM {$events}
			WHERE DATE(occurred_at) = %s AND {$field} <> ''
			GROUP BY {$field}",
			$dimension_type,
			current_time( 'mysql' ),
			current_time( 'mysql' ),
			$date
		);
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$wpdb->query( $sql );
	}

	private function apply_conversion_rollup( $date, $dimension_type, $field, $conversions, $daily ): void {
		global $wpdb;
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT {$field} AS dimension_value, COUNT(*) AS conversions, COUNT(DISTINCT order_id) AS orders, COALESCE(SUM(revenue),0) AS revenue
				FROM {$conversions}
				WHERE DATE(occurred_at) = %s
					AND {$field} <> ''
					AND status IN ('processing','completed','on-hold')
				GROUP BY {$field}",
				$date
			),
			ARRAY_A
		);

		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query(
				$wpdb->prepare(
					"UPDATE {$daily}
					SET conversions=%d, orders=%d, revenue=%f, updated_at=%s
					WHERE rollup_date=%s AND dimension_type=%s AND dimension_value=%s",
					(int) ( $row['conversions'] ?? 0 ),
					(int) ( $row['orders'] ?? 0 ),
					(float) ( $row['revenue'] ?? 0 ),
					current_time( 'mysql' ),
					$date,
					$dimension_type,
					(string) ( $row['dimension_value'] ?? '' )
				)
			);
		}
	}

	private function table_exists( $slug ): bool {
		global $wpdb;
		$table = Niwa_AI_Traffic_DB::table( $slug );
		return $table === $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );
	}
}
