<?php

declare(strict_types=1);

namespace FormConnector\Features\Attribution;

/**
 * Attribution Query
 *
 * Provides read-only query methods for attribution analytics.
 * Supports summary aggregation, time-series data, and drill-down.
 */
class AttributionQuery {

	/**
	 * Sentinel label used in summary keys when a dimension value is empty/NULL.
	 * Drill-down translates this back into an empty-or-NULL match.
	 */
	public const NOT_SET = '(not set)';

	/**
	 * Valid group-by dimensions
	 */
	private const VALID_GROUP_BY = ['campaign', 'source', 'medium', 'landing_page', 'form', 'location'];

	/**
	 * Valid metrics for time-series
	 */
	private const VALID_METRICS = ['submissions', 'leads', 'value'];

	/**
	 * Get the table name.
	 */
	private static function table(): string {
		return AttributionTracker::get_instance()->get_table_name();
	}

	/**
	 * Get attribution summary grouped by a dimension.
	 *
	 * @param string $from     Start date (Y-m-d)
	 * @param string $to       End date (Y-m-d)
	 * @param string $group_by Dimension to group by
	 * @return array Rows of summary data
	 */
	public static function summary(string $from, string $to, string $group_by = 'campaign'): array {
		global $wpdb;

		if (!in_array($group_by, self::VALID_GROUP_BY, true)) {
			$group_by = 'campaign';
		}

		$table  = self::table();
		$column = self::dimension_column($group_by);

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$sql = $wpdb->prepare(
			"SELECT
				{$column} AS group_key,
				COUNT(*) AS submissions,
				SUM(CASE WHEN lead_status = 'qualified' THEN 1 ELSE 0 END) AS qualified,
				SUM(CASE WHEN lead_status = 'won' THEN 1 ELSE 0 END) AS won,
				SUM(CASE WHEN lead_status = 'lost' THEN 1 ELSE 0 END) AS lost,
				SUM(CASE WHEN lead_value IS NOT NULL THEN lead_value ELSE 0 END) AS total_value
			FROM {$table}
			WHERE submitted_at >= %s AND submitted_at < %s
			GROUP BY {$column}
			ORDER BY submissions DESC
			LIMIT 100",
			$from . ' 00:00:00',
			$to . ' 23:59:59'
		);

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results($sql, ARRAY_A);

		if (!is_array($rows)) {
			return [];
		}

		return array_map(function ($row) {
			$submissions = (int) $row['submissions'];
			$won         = (int) $row['won'];

			return [
				'key'             => $row['group_key'] ?: self::NOT_SET,
				'submissions'     => $submissions,
				'qualified'       => (int) $row['qualified'],
				'won'             => $won,
				'lost'            => (int) $row['lost'],
				'total_value'     => round((float) $row['total_value'], 2),
				'conversion_rate' => $submissions > 0 ? round($won / $submissions * 100, 1) : 0,
			];
		}, $rows);
	}

	/**
	 * Get time-series data for a metric.
	 *
	 * @param string $from     Start date (Y-m-d)
	 * @param string $to       End date (Y-m-d)
	 * @param string $metric   Metric to aggregate
	 * @param string $group_by Optional dimension to group by (null = totals only)
	 * @return array Daily buckets
	 */
	public static function timeseries(string $from, string $to, string $metric = 'submissions', string $group_by = ''): array {
		global $wpdb;

		if (!in_array($metric, self::VALID_METRICS, true)) {
			$metric = 'submissions';
		}

		$table = self::table();

		$value_expr = match ($metric) {
			'submissions' => 'COUNT(*)',
			'leads'       => "SUM(CASE WHEN lead_status IN ('qualified','won') THEN 1 ELSE 0 END)",
			'value'       => 'COALESCE(SUM(lead_value), 0)',
		};

		$group_column = '';
		$select_group = '';
		$group_clause = '';

		if (!empty($group_by) && in_array($group_by, self::VALID_GROUP_BY, true)) {
			$col           = self::dimension_column($group_by);
			$select_group  = ", {$col} AS series";
			$group_clause  = ", {$col}";
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$sql = $wpdb->prepare(
			"SELECT
				DATE(submitted_at) AS date,
				{$value_expr} AS value
				{$select_group}
			FROM {$table}
			WHERE submitted_at >= %s AND submitted_at < %s
			GROUP BY DATE(submitted_at){$group_clause}
			ORDER BY date ASC",
			$from . ' 00:00:00',
			$to . ' 23:59:59'
		);

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results($sql, ARRAY_A);

		return is_array($rows) ? $rows : [];
	}

	/**
	 * Get paginated drill-down list of submissions.
	 *
	 * @param string $from    Start date (Y-m-d)
	 * @param string $to      End date (Y-m-d)
	 * @param array  $filters Filters: campaign, source, medium, form_plugin, lead_status
	 * @param int    $page    Page number (1-indexed)
	 * @param int    $per_page Results per page
	 * @return array ['items' => array, 'total' => int, 'pages' => int]
	 */
	public static function submissions(string $from, string $to, array $filters = [], int $page = 1, int $per_page = 25): array {
		global $wpdb;

		$table  = self::table();
		$where  = ['submitted_at >= %s', 'submitted_at < %s'];
		$params = [$from . ' 00:00:00', $to . ' 23:59:59'];

		foreach (['utm_campaign', 'utm_source', 'utm_medium', 'form_plugin', 'lead_status', 'location_id'] as $key) {
			if (empty($filters[$key])) {
				continue;
			}

			// Summary surfaces empty/NULL dimension values under the "(not set)"
			// sentinel; translate it back so drilling into a "(not set)" row
			// returns those submissions instead of matching the literal string.
			if ($filters[$key] === self::NOT_SET) {
				$where[] = "({$key} = '' OR {$key} IS NULL)";
				continue;
			}

			$where[]  = "{$key} = %s";
			$params[] = sanitize_text_field($filters[$key]);
		}

		$where_sql = implode(' AND ', $where);
		$offset    = max(0, ($page - 1) * $per_page);

		// Count
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$count_sql = $wpdb->prepare(
			"SELECT COUNT(*) FROM {$table} WHERE {$where_sql}",
			...$params
		);
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$total = (int) $wpdb->get_var($count_sql);

		// Fetch
		$params[] = $per_page;
		$params[] = $offset;

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$sql = $wpdb->prepare(
			"SELECT * FROM {$table} WHERE {$where_sql} ORDER BY submitted_at DESC LIMIT %d OFFSET %d",
			...$params
		);
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$items = $wpdb->get_results($sql, ARRAY_A);

		return [
			'items' => is_array($items) ? $items : [],
			'total' => $total,
			'pages' => (int) ceil($total / $per_page),
			'page'  => $page,
		];
	}

	/**
	 * Get KPI totals for a date range.
	 *
	 * @param string $from Start date (Y-m-d)
	 * @param string $to   End date (Y-m-d)
	 * @return array KPI data
	 */
	public static function kpis(string $from, string $to): array {
		global $wpdb;

		$table = self::table();

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$sql = $wpdb->prepare(
			"SELECT
				COUNT(*) AS total_submissions,
				SUM(CASE WHEN lead_status IN ('qualified','won','lost') THEN 1 ELSE 0 END) AS total_leads,
				SUM(CASE WHEN lead_status = 'qualified' THEN 1 ELSE 0 END) AS qualified,
				SUM(CASE WHEN lead_status = 'won' THEN 1 ELSE 0 END) AS won,
				SUM(CASE WHEN lead_status = 'lost' THEN 1 ELSE 0 END) AS lost,
				COALESCE(SUM(lead_value), 0) AS total_value,
				AVG(CASE WHEN lead_value IS NOT NULL AND lead_value > 0 THEN lead_value END) AS avg_value
			FROM {$table}
			WHERE submitted_at >= %s AND submitted_at < %s",
			$from . ' 00:00:00',
			$to . ' 23:59:59'
		);

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$row = $wpdb->get_row($sql, ARRAY_A);

		if (!is_array($row)) {
			return [
				'total_submissions' => 0,
				'total_leads'       => 0,
				'qualified'         => 0,
				'won'               => 0,
				'lost'              => 0,
				'total_value'       => 0,
				'avg_value'         => 0,
				'qualified_rate'    => 0,
				'won_rate'          => 0,
			];
		}

		$total = (int) $row['total_submissions'];

		return [
			'total_submissions' => $total,
			'total_leads'       => (int) $row['total_leads'],
			'qualified'         => (int) $row['qualified'],
			'won'               => (int) $row['won'],
			'lost'              => (int) $row['lost'],
			'total_value'       => round((float) $row['total_value'], 2),
			'avg_value'         => round((float) ($row['avg_value'] ?? 0), 2),
			'qualified_rate'    => $total > 0 ? round((int) $row['qualified'] / $total * 100, 1) : 0,
			'won_rate'          => $total > 0 ? round((int) $row['won'] / $total * 100, 1) : 0,
		];
	}

	/**
	 * Map group-by dimension to SQL column name.
	 */
	private static function dimension_column(string $group_by): string {
		return match ($group_by) {
			'campaign'     => 'utm_campaign',
			'source'       => 'utm_source',
			'medium'       => 'utm_medium',
			'landing_page' => 'landing_page',
			'form'         => 'form_name',
			'location'     => 'location_name',
			default        => 'utm_campaign',
		};
	}
}
