<?php
/**
 * Database Manager — Custom tables via dbDelta, migrations, analytics, cleanup.
 *
 * Tables managed:
 *   Core: capiflow_events (queue), capiflow_analytics (dashboard)
 *   Pro:  capiflow_customer_reputation, capiflow_courier_logs, capiflow_abandoned_carts
 *   (Pro tables created by Pro plugin, dropped by Pro uninstall.php)
 *
 * @package CapiFlow
 */

namespace CapiFlow\Core;

defined( 'ABSPATH' ) || exit;

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter
// Reason: All queries target plugin's own custom tables (capiflow_events, capiflow_analytics).
// Table names come from self::events_table() / self::analytics_table() which return $wpdb->prefix + constant.
// WordPress object cache and WP_Query abstraction do not apply to custom tables.
class DB_Manager {

	/**
	 * Current DB schema version.
	 * Start at 1.0.0 — first public release. Using 2.0.0 implies prior schema existed.
	 *
	 * @var string
	 */
	const DB_VERSION = '1.0.0';

	// ─────────────────────────────────────────────────────────────────
	// Table helpers
	// ─────────────────────────────────────────────────────────────────

	/**
	 * Get the events queue table name.
	 *
	 * @return string
	 */
	public static function events_table(): string {
		global $wpdb;
		return $wpdb->prefix . 'capiflow_events';
	}

	/**
	 * Get the analytics aggregate table name.
	 *
	 * @return string
	 */
	public static function analytics_table(): string {
		global $wpdb;
		return $wpdb->prefix . 'capiflow_analytics';
	}

	// ─────────────────────────────────────────────────────────────────
	// Table creation & migration
	// ─────────────────────────────────────────────────────────────────

	/**
	 * Create or update all Core plugin tables using dbDelta.
	 *
	 * Note on capiflow_events: the table was previously named woo_capi_events.
	 * Phase 0 renames it via SQL (managed externally). No re-creation needed here
	 * on fresh installs — dbDelta creates it fresh.
	 */
	public static function create_tables(): void {
		global $wpdb;

		$charset_collate = $wpdb->get_charset_collate();
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		// ── Events Queue Table ──────────────────────────────────────────────
		$events_table = self::events_table();
		$sql_events   = "CREATE TABLE {$events_table} (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			event_name VARCHAR(50) NOT NULL,
			event_id VARCHAR(80) NOT NULL,
			event_time INT(11) UNSIGNED NOT NULL,
			pixel_id VARCHAR(30) NOT NULL DEFAULT '',
			wc_order_id BIGINT(20) UNSIGNED DEFAULT NULL,
			payload LONGTEXT NOT NULL,
			status ENUM('pending','processing','sent','failed') NOT NULL DEFAULT 'pending',
			retry_count TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
			next_retry_at DATETIME DEFAULT NULL,
			api_response TEXT DEFAULT NULL,
			http_code SMALLINT(5) UNSIGNED DEFAULT NULL,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY event_id (event_id),
			KEY status_retry (status, next_retry_at),
			KEY order_id (wc_order_id),
			KEY event_name_status (event_name, status)
		) {$charset_collate};";

		dbDelta( $sql_events );

		// ── Analytics Aggregate Table ───────────────────────────────────────
		// Groups daily by event_name + source + currency.
		// UPSERT via ON DUPLICATE KEY UPDATE (deadlock-safe 3-retry in increment_analytics).
		$analytics_table = self::analytics_table();
		$sql_analytics   = "CREATE TABLE {$analytics_table} (
			id BIGINT UNSIGNED AUTO_INCREMENT,
			event_date DATE NOT NULL,
			event_name VARCHAR(50) NOT NULL,
			source VARCHAR(10) NOT NULL DEFAULT 'both',
			event_count INT UNSIGNED DEFAULT 0,
			revenue DECIMAL(18,2) DEFAULT 0.00,
			recovered_revenue DECIMAL(18,2) DEFAULT 0.00,
			currency VARCHAR(3) NOT NULL,
			PRIMARY KEY (id),
			UNIQUE KEY date_event_source_currency (event_date, event_name, source, currency),
			KEY event_date (event_date)
		) {$charset_collate};";

		dbDelta( $sql_analytics );

		// Store schema version.
		update_option( 'capiflow_db_version', self::DB_VERSION );
	}

	/**
	 * Run DB migration if the installed version is behind.
	 * Called from Plugin_Loader::init() on admin context only.
	 */
	public static function maybe_migrate(): void {
		$installed = get_option( 'capiflow_db_version', '0' );

		if ( version_compare( $installed, self::DB_VERSION, '<' ) ) {
			self::create_tables();
		}
	}

	/**
	 * Drop Core tables (called by uninstall.php when keep_data is false).
	 */
	public static function drop_tables(): void {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange
		$wpdb->query( 'DROP TABLE IF EXISTS ' . self::events_table() ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$wpdb->query( 'DROP TABLE IF EXISTS ' . self::analytics_table() ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		// phpcs:enable

		delete_option( 'capiflow_db_version' );
	}

	// ─────────────────────────────────────────────────────────────────
	// Events Queue operations
	// ─────────────────────────────────────────────────────────────────

	/**
	 * Insert an event into the queue.
	 *
	 * @param array $data Event data array.
	 * @return int|false Inserted row ID or false on failure.
	 */
	public static function insert_event( array $data ) {
		global $wpdb;

		$defaults = array(
			'event_name'  => '',
			'event_id'    => '',
			'event_time'  => time(),
			'pixel_id'    => '',
			'wc_order_id' => null,
			'payload'     => '',
			'status'      => 'pending',
			'retry_count' => 0,
		);

		$data = wp_parse_args( $data, $defaults );

		// Encode payload if array.
		if ( is_array( $data['payload'] ) ) {
			$data['payload'] = wp_json_encode( $data['payload'] );
		}

		// Payload size guard (Meta max: 32KB).
		if ( strlen( $data['payload'] ) > 32768 ) {
			Logger::error( $data['event_name'], 'Payload exceeds 32KB limit', array( 'size' => strlen( $data['payload'] ) ) );
			return false;
		}

		$result = $wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			self::events_table(),
			array(
				'event_name'  => sanitize_text_field( $data['event_name'] ),
				'event_id'    => sanitize_text_field( $data['event_id'] ),
				'event_time'  => absint( $data['event_time'] ),
				'pixel_id'    => sanitize_text_field( $data['pixel_id'] ),
				'wc_order_id' => $data['wc_order_id'] ? absint( $data['wc_order_id'] ) : null,
				'payload'     => $data['payload'],
				'status'      => $data['status'],
				'retry_count' => absint( $data['retry_count'] ),
			),
			array( '%s', '%s', '%d', '%s', '%d', '%s', '%s', '%d' )
		);

		if ( false === $result ) {
			Logger::error( $data['event_name'], 'DB insert failed: ' . $wpdb->last_error );
			return false;
		}

		return $wpdb->insert_id;
	}

	/**
	 * Atomic lock: claim an event for processing (prevents concurrent double-send).
	 *
	 * @param int $event_id DB row ID.
	 * @return bool True if lock acquired.
	 */
	public static function claim_event( int $event_id ): bool {
		global $wpdb;

		$table   = self::events_table();
		$affected = $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is from self::events_table(), safe.
			$wpdb->prepare(
				"UPDATE {$table} SET status = 'processing', updated_at = NOW()
				WHERE id = %d
				AND (status = 'pending'
				     OR (status = 'processing' AND updated_at < DATE_SUB(NOW(), INTERVAL 15 MINUTE)))",
				$event_id
			)
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);

		return $affected > 0;
	}

	/**
	 * Mark an event as sent.
	 *
	 * @param int    $event_id     DB row ID.
	 * @param int    $http_code    HTTP response code.
	 * @param string $api_response API response body.
	 */
	public static function mark_sent( int $event_id, int $http_code = 200, string $api_response = '' ): void {
		global $wpdb;

		$wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			self::events_table(),
			array(
				'status'       => 'sent',
				'http_code'    => $http_code,
				'api_response' => mb_substr( $api_response, 0, 500 ),
			),
			array( 'id' => $event_id ),
			array( '%s', '%d', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Mark an event as failed (permanent — no more retries).
	 *
	 * @param int    $event_id  DB row ID.
	 * @param string $reason    Failure reason.
	 * @param int    $http_code HTTP response code.
	 */
	public static function mark_failed( int $event_id, string $reason = '', int $http_code = 0 ): void {
		global $wpdb;

		$wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			self::events_table(),
			array(
				'status'       => 'failed',
				'http_code'    => $http_code,
				'api_response' => mb_substr( $reason, 0, 500 ),
			),
			array( 'id' => $event_id ),
			array( '%s', '%d', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Schedule a retry with exponential backoff.
	 *
	 * @param int $event_id    DB row ID.
	 * @param int $retry_count Current retry count.
	 * @param int $multiplier  Delay multiplier (2 for 429 rate limit).
	 */
	public static function schedule_retry( int $event_id, int $retry_count, int $multiplier = 1 ): void {
		global $wpdb;

		$max_retries = 5;

		if ( $retry_count >= $max_retries ) {
			self::mark_failed( $event_id, 'Max retries exceeded' );
			return;
		}

		// Exponential backoff: 1m, 5m, 15m, 60m, 360m.
		$delays     = array( 60, 300, 900, 3600, 21600 );
		$delay      = isset( $delays[ $retry_count ] ) ? $delays[ $retry_count ] : 21600;
		$delay      = $delay * $multiplier;
		$next_retry = gmdate( 'Y-m-d H:i:s', time() + $delay );

		$wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			self::events_table(),
			array(
				'status'        => 'pending',
				'retry_count'   => $retry_count + 1,
				'next_retry_at' => $next_retry,
			),
			array( 'id' => $event_id ),
			array( '%s', '%d', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Get pending events for queue processing.
	 *
	 * Purchase events prioritized via FIELD() ordering.
	 * Zombie recovery: events stuck as 'processing' for >15 minutes
	 * (PHP crash, memory limit, host kill) are rescued automatically.
	 * Follows WooCommerce Action Scheduler's timeout recovery pattern.
	 *
	 * @param int $batch_size Number of events to fetch.
	 * @return array Array of event row objects.
	 */
	public static function get_pending_events( int $batch_size = 10 ): array {
		global $wpdb;

		$table = self::events_table();

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is from self::events_table().
		return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				"SELECT * FROM {$table}
				WHERE (
				  status = 'pending'
				  OR (status = 'processing' AND updated_at < DATE_SUB(NOW(), INTERVAL 15 MINUTE))
				)
				  AND (next_retry_at IS NULL OR next_retry_at <= NOW())
				ORDER BY
				  FIELD(event_name, 'Purchase','InitiateCheckout','AddPaymentInfo',
				        'AddToCart','ViewContent','PageView','Search') ASC,
				  event_time ASC
				LIMIT %d",
				$batch_size
			)
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	/**
	 * Get queue events for a specific WC order (DB-backed column lookup).
	 *
	 * Used by Order_Columns to show real sync status instead of unreliable meta keys.
	 * The wc_order_id column has an index (KEY order_id), so this query is fast.
	 *
	 * @param int $order_id WC order ID.
	 * @return array Array of event row objects.
	 */
	public static function get_events_by_order( int $order_id ): array {
		global $wpdb;

		$table = self::events_table();

		return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is from self::events_table(), safe.
			$wpdb->prepare(
				"SELECT event_name, status, retry_count, http_code, created_at
				FROM {$table}
				WHERE wc_order_id = %d
				ORDER BY created_at DESC
				LIMIT 10",
				$order_id
			)
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);
	}

	/**
	 * Get recent events for debug console.
	 *
	 * @param int    $count      Number to fetch.
	 * @param string $event_name Optional filter.
	 * @param string $status     Optional status filter.
	 * @return array
	 */
	public static function get_recent_events( int $count = 50, string $event_name = '', string $status = '' ): array {
		global $wpdb;

		$table = self::events_table();
		$where = array( '1=1' );
		$args  = array();

		if ( '' !== $event_name ) {
			$where[] = 'event_name = %s';
			$args[]  = $event_name;
		}

		if ( '' !== $status ) {
			$where[] = 'status = %s';
			$args[]  = $status;
		}

		$where_clause = implode( ' AND ', $where );
		$args[]       = $count;

		return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				"SELECT * FROM {$table} WHERE {$where_clause} ORDER BY created_at DESC LIMIT %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$args
			)
		);
	}

	/**
	 * Get all queue events for a specific order (for Order Metabox display).
	 *
	 * @param int $order_id WooCommerce order ID.
	 * @return array Array of event row objects.
	 */
	public static function get_events_for_order( int $order_id ): array {
		global $wpdb;

		$table = self::events_table();

		return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				"SELECT id, event_name, status, http_code, created_at FROM {$table} WHERE wc_order_id = %d ORDER BY created_at DESC LIMIT 10", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$order_id
			)
		) ?: array();
	}

	/**
	 * Cleanup old sent/failed events (keeps DB lean).
	 * Retention controlled by capiflow_events_retention_days filter.
	 */
	public static function cleanup_old_events(): void {
		global $wpdb;

		$days   = (int) apply_filters( 'capiflow_events_retention_days', 30 );
		$table  = self::events_table();
		$cutoff = gmdate( 'Y-m-d H:i:s', time() - ( $days * DAY_IN_SECONDS ) );

		$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				"DELETE FROM {$table} WHERE status IN ('sent', 'failed') AND updated_at < %s LIMIT 1000", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$cutoff
			)
		);
	}

	/**
	 * Get queue statistics for system health dashboard.
	 *
	 * @return array Counts by status.
	 */
	public static function get_queue_stats(): array {
		global $wpdb;

		$table = self::events_table();

		// Guard: table may not exist on fresh install before activation runs.
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			return array( 'pending' => 0, 'processing' => 0, 'sent' => 0, 'failed' => 0 );
		}

		$results = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
			"SELECT status, COUNT(*) as count FROM {$table} GROUP BY status", // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			ARRAY_A
		);

		$stats = array(
			'pending'    => 0,
			'processing' => 0,
			'sent'       => 0,
			'failed'     => 0,
		);

		if ( is_array( $results ) ) {
			foreach ( $results as $row ) {
				if ( isset( $stats[ $row['status'] ] ) ) {
					$stats[ $row['status'] ] = (int) $row['count'];
				}
			}
		}

		return $stats;
	}

	// ─────────────────────────────────────────────────────────────────
	// Analytics operations
	// ─────────────────────────────────────────────────────────────────

	/**
	 * Atomic UPSERT for daily analytics aggregation.
	 *
	 * Uses MySQL ON DUPLICATE KEY UPDATE for atomicity.
	 * Deadlock-safe: 3 retries with exponential backoff.
	 * wp_date() respects the WooCommerce store timezone (R9 Fix#1).
	 *
	 * @param string $event_name Event name slug (max 50 chars).
	 * @param string $source     'both' (pixel+server) or 'server' (server-only).
	 * @param float  $revenue    Order total. 0.0 for non-Purchase events.
	 * @param float  $recovered  Server-rescued revenue. 0.0 if pixel also fired.
	 * @param string $currency   ISO-4217 code (e.g. 'BDT', 'USD').
	 * @return bool True on success.
	 */
	public static function increment_analytics(
		string $event_name,
		string $source,
		float $revenue,
		float $recovered,
		string $currency = 'USD'
	): bool {
		global $wpdb;

		$table = self::analytics_table();
		// wp_date() = modern WP 5.3+ function, respects store timezone.
		// current_time() works but wp_date() is the recommended replacement.
		$date = wp_date( 'Y-m-d' );

		// Normalize + validate currency.
		$currency = strtoupper( sanitize_text_field( $currency ) );
		if ( 3 !== strlen( $currency ) ) {
			$currency = function_exists( 'get_woocommerce_currency' )
				? get_woocommerce_currency()
				: 'USD';
		}

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is from self::analytics_table().
		$sql = $wpdb->prepare(
			"INSERT INTO {$table}
				(event_date, event_name, source, event_count, revenue, recovered_revenue, currency)
			VALUES (%s, %s, %s, 1, %f, %f, %s)
			ON DUPLICATE KEY UPDATE
				event_count       = event_count + 1,
				revenue           = revenue + VALUES(revenue),
				recovered_revenue = recovered_revenue + VALUES(recovered_revenue)",
			$date,
			$event_name,
			$source,
			$revenue,
			$recovered,
			$currency
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		// Deadlock-safe: 3 retries with exponential backoff.
		for ( $attempt = 1; $attempt <= 3; $attempt++ ) {
			$result = $wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
			if ( false !== $result ) {
				return true;
			}
			if ( $attempt < 3 ) {
				usleep( $attempt * 100000 ); // 100ms → 200ms
			}
		}

		Logger::error( 'Analytics', 'increment_analytics failed after 3 retries: ' . $wpdb->last_error );
		return false;
	}

	/**
	 * Cleanup old analytics rows.
	 *
	 * Retention filterable: Pro overrides to 90/365 days.
	 * Uses wp_date() for timezone consistency with insert (R10 Fix#2).
	 */
	public static function cleanup_old_analytics(): void {
		global $wpdb;

		$days   = (int) apply_filters( 'capiflow_analytics_retention_days', 30 );
		$table  = self::analytics_table();
		$cutoff = wp_date( 'Y-m-d' ); // timezone-aware

		$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				"DELETE FROM {$table} WHERE event_date < DATE_SUB(%s, INTERVAL %d DAY)", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$cutoff,
				$days
			)
		);
	}

	/**
	 * Get dashboard KPI summary.
	 *
	 * @param int $days Period in days (default 30).
	 * @return array{total_events:int, rescued_events:int, rescued_revenue:float, period_days:int}
	 */
	public static function get_dashboard_summary( int $days = 30 ): array {
		global $wpdb;

		$table  = self::analytics_table();
		$cutoff = wp_date( 'Y-m-d', strtotime( "-{$days} days" ) );

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is from self::analytics_table().
		$row = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				"SELECT
					SUM(event_count)       AS total_events,
					SUM(CASE WHEN source = 'server' THEN event_count ELSE 0 END) AS rescued_events,
					SUM(recovered_revenue) AS rescued_revenue
				FROM {$table}
				WHERE event_date >= %s",
				$cutoff
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		return array(
			'total_events'    => (int) ( $row['total_events'] ?? 0 ),
			'rescued_events'  => (int) ( $row['rescued_events'] ?? 0 ),
			'rescued_revenue' => (float) ( $row['rescued_revenue'] ?? 0 ),
			'period_days'     => $days,
		);
	}

	/**
	 * Get funnel data (event counts per type) for dashboard chart.
	 *
	 * @param int $days Period in days.
	 * @return array[] Array of ['event_name'=>string, 'count'=>int, 'rescued'=>int]
	 */
	public static function get_funnel_data( int $days = 30 ): array {
		global $wpdb;

		$table  = self::analytics_table();
		$cutoff = wp_date( 'Y-m-d', strtotime( "-{$days} days" ) );

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is from self::analytics_table().
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				"SELECT
					event_name,
					SUM(event_count) AS count,
					SUM(CASE WHEN source = 'server' THEN event_count ELSE 0 END) AS rescued
				FROM {$table}
				WHERE event_date >= %s
				GROUP BY event_name
				ORDER BY FIELD(event_name,'PageView','ViewContent','Search','AddToCart',
				               'InitiateCheckout','AddPaymentInfo','Purchase') ASC",
				$cutoff
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		if ( ! is_array( $rows ) ) {
			return array();
		}

		return array_map( function ( $row ) {
			return array(
				'event_name' => $row['event_name'],
				'count'      => (int) $row['count'],
				'rescued'    => (int) $row['rescued'],
			);
		}, $rows );
	}

	/**
	 * Get daily breakdown data for Chart.js line graphs.
	 *
	 * @param int $days Period in days.
	 * @return array[] Array of ['date'=>string, 'event_name'=>string, 'source'=>string, 'count'=>int]
	 */
	public static function get_daily_breakdown( int $days = 30 ): array {
		global $wpdb;

		$table  = self::analytics_table();
		$cutoff = wp_date( 'Y-m-d', strtotime( "-{$days} days" ) );

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is from self::analytics_table().
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				"SELECT event_date AS date, event_name, source, event_count AS count
				FROM {$table}
				WHERE event_date >= %s
				ORDER BY event_date ASC, event_name ASC",
				$cutoff
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		if ( ! is_array( $rows ) ) {
			return array();
		}

		return array_map( function ( $row ) {
			return array(
				'date'       => $row['date'],
				'event_name' => $row['event_name'],
				'source'     => $row['source'],
				'count'      => (int) $row['count'],
			);
		}, $rows );
	}

	/**
	 * Get aggregated event summary for dashboard table.
	 *
	 * Returns one row per event_name with:
	 *   today_count, total_count, both_count, server_count
	 *
	 * @param int $days Period in days.
	 * @return array[] Array of aggregated event rows.
	 */
	public static function get_event_table_summary( int $days = 30 ): array {
		global $wpdb;

		$table  = self::analytics_table();
		$cutoff = wp_date( 'Y-m-d', strtotime( "-{$days} days" ) );
		$today  = wp_date( 'Y-m-d' );

		// Guard: table may not exist yet.
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			return array();
		}

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is from self::analytics_table().
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				"SELECT
					event_name,
					SUM(event_count) AS total_count,
					SUM(CASE WHEN event_date = %s THEN event_count ELSE 0 END) AS today_count,
					SUM(CASE WHEN source = 'both' THEN event_count ELSE 0 END) AS both_count,
					SUM(CASE WHEN source = 'server' THEN event_count ELSE 0 END) AS server_count
				FROM {$table}
				WHERE event_date >= %s
				GROUP BY event_name
				ORDER BY FIELD(event_name,'PageView','ViewContent','Search','AddToCart',
				               'InitiateCheckout','AddPaymentInfo','Purchase') ASC",
				$today,
				$cutoff
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		if ( ! is_array( $rows ) ) {
			return array();
		}

		return array_map( function ( $row ) {
			return array(
				'event_name'   => $row['event_name'],
				'today_count'  => (int) $row['today_count'],
				'total_count'  => (int) $row['total_count'],
				'both_count'   => (int) $row['both_count'],
				'server_count' => (int) $row['server_count'],
			);
		}, $rows );
	}

	/**
	 * Get daily chart data for dashboard sparkline.
	 *
	 * Returns one row per day with total sent events.
	 * Used by: admin/views/dashboard-page.php
	 *
	 * @param int $days Number of days to look back.
	 * @return array Array of [ 'date' => 'Y-m-d', 'sent_events' => int ]
	 */
	public static function get_daily_chart_data( int $days = 30 ): array {
		global $wpdb;

		$table  = self::analytics_table();
		$cutoff = wp_date( 'Y-m-d', strtotime( "-{$days} days" ) );

		// Check if table exists first (fresh install — no data yet).
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			return array();
		}

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is from self::analytics_table().
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				"SELECT event_date AS date, SUM(event_count) AS sent_events
				FROM {$table}
				WHERE event_date >= %s
				GROUP BY event_date
				ORDER BY event_date ASC",
				$cutoff
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		if ( ! is_array( $rows ) ) {
			return array();
		}

		return array_map( function ( $row ) {
			return array(
				'date'        => $row['date'],
				'sent_events' => (int) $row['sent_events'],
			);
		}, $rows );
	}

	/**
	 * Get abandoned cart stats (reads option — no SQL).
	 *
	 * @return array{count:int, value:float, products:array}
	 */
	public static function get_abandoned_cart_stats(): array {
		return get_option(
			'capiflow_abandoned_summary',
			array( 'count' => 0, 'value' => 0.0, 'products' => array() )
		);
	}

	/**
	 * Get Pro teaser data for dashboard widget.
	 *
	 * @return array{total_abandoned_value:float, potential_recovery:float}
	 */
	public static function get_teaser_data(): array {
		$summary = self::get_abandoned_cart_stats();
		$value   = (float) ( $summary['value'] ?? 0.0 );

		return array(
			'total_abandoned_value' => $value,
			'potential_recovery'    => round( $value * 0.15, 2 ), // 15% industry average
		);
	}
}
