<?php
/**
 * Campaign Sources List Table.
 *
 * @package     EDD\Pro\CampaignTracker\Admin\Reports\Data
 * @copyright   Copyright (c) 2025, Sandhills Development, LLC
 * @license     https://opensource.org/licenses/gpl-2.0.php GNU Public License
 * @since       3.6.3
 */

namespace EDD\Pro\CampaignTracker\Admin\Reports\Data;

// Exit if accessed directly.
defined( 'ABSPATH' ) || exit; // @codeCoverageIgnore

use EDD\Reports;
use EDD\Admin\List_Table;

/**
 * Campaign Sources List Table Class.
 *
 * @since 3.6.3
 */
class CampaignSourcesListTable extends List_Table {

	/**
	 * Comparison data from the previous period.
	 *
	 * @since 3.6.3
	 * @var array
	 */
	protected $comparison_data = array();

	/**
	 * Query the database and fetch campaign source data.
	 *
	 * Retrieves aggregated campaign performance metrics by querying order meta for UTM parameters.
	 * Groups orders by campaign name, source, and medium combinations, calculating order count,
	 * gross sales, and average order value for each unique campaign source. Results are filtered
	 * by date range and optional campaign filters, sorted by the requested column, and limited
	 * to 100 rows. Also populates comparison data from the previous period for trend analysis.
	 *
	 * @since 3.6.3
	 * @return array Array of campaign source objects containing campaign_name, source, medium,
	 *               order_count, gross_sales, and average_order properties.
	 */
	public function get_data() {
		global $wpdb;

		$dates = Reports\get_filter_value( 'dates' );

		// Check if campaign filter is explicitly set in URL.
		// phpcs:ignore WordPress.Security.NonceVerification.Recommended
		$has_campaign_filter = isset( $_GET['campaign_tracker'] );
		$campaign            = Reports\get_filter_value( 'campaign_tracker' );

		// Get comparison data for the previous period.
		$this->comparison_data = $this->get_comparison_data( $dates, $campaign, $has_campaign_filter );

		// Parse dates into DateTime objects for the main query.
		$parsed   = Reports\parse_dates_for_range( $dates['range'] );
		$date_sql = '';
		if ( ! empty( $parsed['start'] ) && ! empty( $parsed['end'] ) ) {
			$date_sql = $wpdb->prepare(
				'AND o.date_created >= %s AND o.date_created <= %s',
				$parsed['start']->format( 'Y-m-d H:i:s' ),
				$parsed['end']->format( 'Y-m-d H:i:s' )
			);
		}

		// Build campaign filter SQL.
		$campaign_sql = '';
		if ( $has_campaign_filter && 'all' !== $campaign ) {
			if ( '' === $campaign ) {
				// Filter for empty/unknown campaigns.
				$campaign_sql = 'AND (campaign.meta_value IS NULL OR campaign.meta_value = \'\')';
			} else {
				$campaign_sql = $wpdb->prepare(
					'AND campaign.meta_value = %s',
					$campaign
				);
			}
		}

		// Get sorting parameters.
		// phpcs:ignore WordPress.Security.NonceVerification.Recommended
		$orderby = isset( $_GET['orderby'] ) ? sanitize_text_field( $_GET['orderby'] ) : 'gross_sales';
		// phpcs:ignore WordPress.Security.NonceVerification.Recommended
		$order = isset( $_GET['order'] ) ? sanitize_text_field( $_GET['order'] ) : 'DESC';

		// Validate orderby against allowed columns.
		$allowed_orderby = array( 'campaign_name', 'source', 'medium', 'content', 'term', 'order_count', 'gross_sales', 'average_order' );
		if ( ! in_array( $orderby, $allowed_orderby, true ) ) {
			$orderby = 'gross_sales';
		}

		// Validate order.
		$order = strtoupper( $order );
		if ( ! in_array( $order, array( 'ASC', 'DESC' ), true ) ) {
			$order = 'DESC';
		}

		// Build ORDER BY clause - handle NULL values for text columns.
		$order_clause = 'ORDER BY ';
		if ( in_array( $orderby, array( 'campaign_name', 'source', 'medium', 'content', 'term' ), true ) ) {
			// For text columns, sort NULLs last and use case-insensitive sorting.
			$order_clause .= "CASE WHEN {$orderby} IS NULL OR {$orderby} = '' THEN 1 ELSE 0 END, {$orderby} COLLATE utf8mb4_unicode_ci {$order}";
		} else {
			// For numeric columns, standard sorting.
			$order_clause .= "{$orderby} {$order}";
		}

		// Query for campaign sources using a subquery to prevent row multiplication from LEFT JOINs.
		// The subquery first gets unique order data with campaign metadata, then we aggregate.
		$query = "SELECT
				campaign_name,
				source,
				medium,
				content,
				term,
				COUNT(*) as order_count,
				SUM(total) as gross_sales,
				AVG(total) as average_order
			FROM (
				SELECT DISTINCT
					o.id,
					o.total,
					campaign.meta_value as campaign_name,
					source.meta_value as source,
					medium.meta_value as medium,
					content.meta_value as content,
					term.meta_value as term
				FROM {$wpdb->edd_orders} o
				LEFT JOIN {$wpdb->edd_ordermeta} campaign ON (o.id = campaign.edd_order_id AND campaign.meta_key = '_eddct_campaign_name')
				LEFT JOIN {$wpdb->edd_ordermeta} source ON (o.id = source.edd_order_id AND source.meta_key = '_eddct_campaign_source')
				LEFT JOIN {$wpdb->edd_ordermeta} medium ON (o.id = medium.edd_order_id AND medium.meta_key = '_eddct_campaign_medium')
				LEFT JOIN {$wpdb->edd_ordermeta} content ON (o.id = content.edd_order_id AND content.meta_key = '_eddct_campaign_content')
				LEFT JOIN {$wpdb->edd_ordermeta} term ON (o.id = term.edd_order_id AND term.meta_key = '_eddct_campaign_term')
				WHERE o.type = 'sale'
				AND o.status IN('complete', 'revoked', 'partially_refunded')
				AND (campaign.meta_value IS NOT NULL OR source.meta_value IS NOT NULL OR medium.meta_value IS NOT NULL)
				{$date_sql}
				{$campaign_sql}
			) AS unique_orders
			GROUP BY campaign_name, source, medium, content, term
			{$order_clause}
			LIMIT 100";

		return $wpdb->get_results( $query );
	}

	/**
	 * Retrieve the table columns.
	 *
	 * @since 3.6.3
	 * @return array Array of all the list table columns.
	 */
	public function get_columns() {
		// Check if a specific campaign is being filtered.
		// phpcs:ignore WordPress.Security.NonceVerification.Recommended
		$has_campaign_filter = isset( $_GET['campaign_tracker'] );
		$campaign            = Reports\get_filter_value( 'campaign_tracker' );

		// When filtering by a specific campaign, show content/term instead of campaign.
		if ( $has_campaign_filter && 'all' !== $campaign ) {
			return array(
				'source'        => __( 'UTM Source', 'easy-digital-downloads' ),
				'medium'        => __( 'UTM Medium', 'easy-digital-downloads' ),
				'content'       => __( 'UTM Content', 'easy-digital-downloads' ),
				'term'          => __( 'UTM Term', 'easy-digital-downloads' ),
				'order_count'   => __( 'Orders', 'easy-digital-downloads' ),
				'gross_sales'   => __( 'Gross Sales', 'easy-digital-downloads' ),
				'average_order' => __( 'Average Order', 'easy-digital-downloads' ),
			);
		}

		// Default columns for overview.
		return array(
			'campaign_name' => __( 'UTM Campaign', 'easy-digital-downloads' ),
			'source'        => __( 'UTM Source', 'easy-digital-downloads' ),
			'medium'        => __( 'UTM Medium', 'easy-digital-downloads' ),
			'order_count'   => __( 'Orders', 'easy-digital-downloads' ),
			'gross_sales'   => __( 'Gross Sales', 'easy-digital-downloads' ),
			'average_order' => __( 'Average Order', 'easy-digital-downloads' ),
		);
	}

	/**
	 * Retrieve the sortable columns.
	 *
	 * @since 3.6.3
	 * @return array Array of all the sortable columns.
	 */
	public function get_sortable_columns() {
		// Check if a specific campaign is being filtered.
		// phpcs:ignore WordPress.Security.NonceVerification.Recommended
		$has_campaign_filter = isset( $_GET['campaign_tracker'] );
		$campaign            = Reports\get_filter_value( 'campaign_tracker' );

		// When filtering by a specific campaign, include content/term instead of campaign.
		if ( $has_campaign_filter && 'all' !== $campaign ) {
			return array(
				'source'        => array( 'source', false ),
				'medium'        => array( 'medium', false ),
				'content'       => array( 'content', false ),
				'term'          => array( 'term', false ),
				'order_count'   => array( 'order_count', false ),
				'gross_sales'   => array( 'gross_sales', true ), // Default sort.
				'average_order' => array( 'average_order', false ),
			);
		}

		// Default sortable columns for overview.
		return array(
			'campaign_name' => array( 'campaign_name', false ),
			'source'        => array( 'source', false ),
			'medium'        => array( 'medium', false ),
			'order_count'   => array( 'order_count', false ),
			'gross_sales'   => array( 'gross_sales', true ), // Default sort.
			'average_order' => array( 'average_order', false ),
		);
	}

	/**
	 * Render the Campaign Name Column.
	 *
	 * @since 3.6.3
	 * @param \stdClass $item Campaign data object.
	 * @return string Data shown in the Campaign column.
	 */
	public function column_campaign_name( $item ) {
		return ! empty( $item->campaign_name ) ? esc_html( $item->campaign_name ) : '<em>' . __( 'Unknown', 'easy-digital-downloads' ) . '</em>';
	}

	/**
	 * Render the Source Column.
	 *
	 * @since 3.6.3
	 * @param \stdClass $item Campaign data object.
	 * @return string Data shown in the Source column.
	 */
	public function column_source( $item ) {
		return ! empty( $item->source ) ? esc_html( $item->source ) : '<em>' . __( 'Unknown', 'easy-digital-downloads' ) . '</em>';
	}

	/**
	 * Render the Medium Column.
	 *
	 * @since 3.6.3
	 * @param \stdClass $item Campaign data object.
	 * @return string Data shown in the Medium column.
	 */
	public function column_medium( $item ) {
		return ! empty( $item->medium ) ? esc_html( $item->medium ) : '<em>' . __( 'Unknown', 'easy-digital-downloads' ) . '</em>';
	}

	/**
	 * Render the Content Column.
	 *
	 * @since 3.6.3
	 * @param \stdClass $item Campaign data object.
	 * @return string Data shown in the Content column.
	 */
	public function column_content( $item ) {
		return ! empty( $item->content ) ? esc_html( $item->content ) : '<em>' . __( 'Unknown', 'easy-digital-downloads' ) . '</em>';
	}

	/**
	 * Render the Term Column.
	 *
	 * @since 3.6.3
	 * @param \stdClass $item Campaign data object.
	 * @return string Data shown in the Term column.
	 */
	public function column_term( $item ) {
		return ! empty( $item->term ) ? esc_html( $item->term ) : '<em>' . __( 'Unknown', 'easy-digital-downloads' ) . '</em>';
	}

	/**
	 * Render the Order Count Column.
	 *
	 * @since 3.6.3
	 * @param \stdClass $item Campaign data object.
	 * @return string Data shown in the Orders column.
	 */
	public function column_order_count( $item ) {
		$output = number_format_i18n( absint( $item->order_count ) );

		// Add percentage change if comparison data exists.
		$key = $this->get_campaign_key( $item );
		if ( isset( $this->comparison_data[ $key ] ) ) {
			$prev       = $this->comparison_data[ $key ]->order_count;
			$percentage = $this->calculate_percentage_change( $item->order_count, $prev );
			$output    .= $this->render_percentage_badge( $percentage );
		}

		return $output;
	}

	/**
	 * Render the Gross Sales Column.
	 *
	 * @since 3.6.3
	 * @param \stdClass $item Campaign data object.
	 * @return string Data shown in the Gross Sales column.
	 */
	public function column_gross_sales( $item ) {
		$output = edd_currency_filter( edd_format_amount( $item->gross_sales ) );

		// Add percentage change if comparison data exists.
		$key = $this->get_campaign_key( $item );
		if ( isset( $this->comparison_data[ $key ] ) ) {
			$prev       = $this->comparison_data[ $key ]->gross_sales;
			$percentage = $this->calculate_percentage_change( $item->gross_sales, $prev );
			$output    .= $this->render_percentage_badge( $percentage );
		}

		return $output;
	}

	/**
	 * Render the Average Order Column.
	 *
	 * @since 3.6.3
	 * @param \stdClass $item Campaign data object.
	 * @return string Data shown in the Average Order column.
	 */
	public function column_average_order( $item ) {
		$output = edd_currency_filter( edd_format_amount( $item->average_order ) );

		// Add percentage change if comparison data exists.
		$key = $this->get_campaign_key( $item );
		if ( isset( $this->comparison_data[ $key ] ) ) {
			$prev       = $this->comparison_data[ $key ]->average_order;
			$percentage = $this->calculate_percentage_change( $item->average_order, $prev );
			$output    .= $this->render_percentage_badge( $percentage );
		}

		return $output;
	}

	/**
	 * Setup the final data for the table.
	 *
	 * @since 3.6.3
	 */
	public function prepare_items() {
		$columns  = $this->get_columns();
		$hidden   = array();
		$sortable = $this->get_sortable_columns();

		$this->_column_headers = array( $columns, $hidden, $sortable );
		$this->items           = $this->get_data();
	}

	/**
	 * Get the base URL for the list table.
	 *
	 * @since 3.6.3
	 * @return string
	 */
	public function get_base_url() {
		return remove_query_arg( edd_admin_removable_query_args(), edd_get_admin_base_url() );
	}

	/**
	 * Message to be displayed when there are no items.
	 *
	 * @since 3.6.3
	 */
	public function no_items() {
		esc_html_e( 'No campaign data found.', 'easy-digital-downloads' );
	}

	/**
	 * Get comparison data for the previous period.
	 *
	 * @since 3.6.3
	 * @param array  $dates               Date range from filters.
	 * @param string $campaign            Campaign filter value.
	 * @param bool   $has_campaign_filter Whether campaign filter is set in URL.
	 * @return array Comparison data indexed by campaign key.
	 */
	protected function get_comparison_data( $dates, $campaign, $has_campaign_filter ) {
		global $wpdb;

		if ( empty( $dates['from'] ) || empty( $dates['to'] ) ) {
			return array();
		}

		// Parse dates into DateTime objects.
		$parsed = Reports\parse_dates_for_range( $dates['range'] );
		if ( empty( $parsed['start'] ) || empty( $parsed['end'] ) ) {
			return array();
		}

		// Get the relative range type (defaults to previous_period).
		$start          = $parsed['start'];
		$end            = $parsed['end'];
		$relative_range = isset( $dates['relative_range'] ) ? $dates['relative_range'] : 'previous_period';

		// Calculate comparison period based on relative_range.
		switch ( $relative_range ) {
			case 'previous_year':
				// Go back exactly one year.
				$prev_start = $start->copy()->subYear( 1 );
				$prev_end   = $end->copy()->subYear( 1 );
				break;

			case 'previous_quarter':
				// Go back exactly one quarter.
				$prev_start = $start->copy()->subQuarter( 1 );
				$prev_end   = $end->copy()->subQuarter( 1 );
				break;

			case 'previous_month':
				// Go back exactly one month.
				$prev_start = $start->copy()->subMonthNoOverflow( 1 );
				$prev_end   = $end->copy()->subMonthNoOverflow( 1 );
				break;

			case 'previous_period':
			default:
				// Go back the same number of days (current behavior).
				$diff       = $start->diff( $end );
				$days       = $diff->days;
				$prev_end   = $start->copy()->subDay( 1 )->endOfDay();
				$prev_start = $prev_end->copy()->subDays( $days )->startOfDay();
				break;
		}

		// Build date SQL for previous period.
		$prev_date_sql = $wpdb->prepare(
			'AND o.date_created >= %s AND o.date_created <= %s',
			$prev_start->format( 'Y-m-d H:i:s' ),
			$prev_end->format( 'Y-m-d H:i:s' )
		);

		// Build campaign filter SQL.
		$campaign_sql = '';
		if ( $has_campaign_filter && 'all' !== $campaign ) {
			if ( '' === $campaign ) {
				// Filter for empty/unknown campaigns.
				$campaign_sql = 'AND (campaign.meta_value IS NULL OR campaign.meta_value = \'\')';
			} else {
				$campaign_sql = $wpdb->prepare(
					'AND campaign.meta_value = %s',
					$campaign
				);
			}
		}

		// Query for previous period data using a subquery to prevent row multiplication from LEFT JOINs.
		$results = $wpdb->get_results(
			"SELECT
				campaign_name,
				source,
				medium,
				content,
				term,
				COUNT(*) as order_count,
				SUM(total) as gross_sales,
				AVG(total) as average_order
			FROM (
				SELECT DISTINCT
					o.id,
					o.total,
					campaign.meta_value as campaign_name,
					source.meta_value as source,
					medium.meta_value as medium,
					content.meta_value as content,
					term.meta_value as term
				FROM {$wpdb->edd_orders} o
				LEFT JOIN {$wpdb->edd_ordermeta} campaign ON (o.id = campaign.edd_order_id AND campaign.meta_key = '_eddct_campaign_name')
				LEFT JOIN {$wpdb->edd_ordermeta} source ON (o.id = source.edd_order_id AND source.meta_key = '_eddct_campaign_source')
				LEFT JOIN {$wpdb->edd_ordermeta} medium ON (o.id = medium.edd_order_id AND medium.meta_key = '_eddct_campaign_medium')
				LEFT JOIN {$wpdb->edd_ordermeta} content ON (o.id = content.edd_order_id AND content.meta_key = '_eddct_campaign_content')
				LEFT JOIN {$wpdb->edd_ordermeta} term ON (o.id = term.edd_order_id AND term.meta_key = '_eddct_campaign_term')
				WHERE o.type = 'sale'
				AND o.status IN('complete', 'revoked', 'partially_refunded')
				AND (campaign.meta_value IS NOT NULL OR source.meta_value IS NOT NULL OR medium.meta_value IS NOT NULL)
				{$prev_date_sql}
				{$campaign_sql}
			) AS unique_orders
			GROUP BY campaign_name, source, medium, content, term"
		);

		// Index results by campaign key.
		$indexed = array();
		foreach ( $results as $row ) {
			$key             = $this->get_campaign_key( $row );
			$indexed[ $key ] = $row;
		}

		return $indexed;
	}

	/**
	 * Get a unique key for a campaign row.
	 *
	 * @since 3.6.3
	 * @param \stdClass $item Campaign data object.
	 * @return string Unique key.
	 */
	protected function get_campaign_key( $item ) {
		// Check if a specific campaign is being filtered.
		// phpcs:ignore WordPress.Security.NonceVerification.Recommended
		$has_campaign_filter = isset( $_GET['campaign_tracker'] );
		$campaign_filter     = Reports\get_filter_value( 'campaign_tracker' );

		// Handle null values by converting to empty strings for consistent key generation.
		$campaign_name = $item->campaign_name ?? '';
		$source        = $item->source ?? '';
		$medium        = $item->medium ?? '';

		// When filtering by campaign, include content and term in the key.
		if ( $has_campaign_filter && 'all' !== $campaign_filter ) {
			$content = $item->content ?? '';
			$term    = $item->term ?? '';
			return md5( $campaign_name . '|' . $source . '|' . $medium . '|' . $content . '|' . $term );
		}

		return md5( $campaign_name . '|' . $source . '|' . $medium );
	}

	/**
	 * Calculate percentage change between two values.
	 *
	 * @since 3.6.3
	 * @param float $current  Current value.
	 * @param float $previous Previous value.
	 * @return float|null Percentage change or null if no comparison possible.
	 */
	protected function calculate_percentage_change( $current, $previous ) {
		if ( ! $previous || 0 == $previous ) {
			return $current > 0 ? 100 : null;
		}

		return ( ( $current - $previous ) / $previous ) * 100;
	}

	/**
	 * Render percentage change badge.
	 *
	 * @since 3.6.3
	 * @param float|null $percentage Percentage change.
	 * @return string HTML badge.
	 */
	protected function render_percentage_badge( $percentage ) {
		if ( is_null( $percentage ) ) {
			return '';
		}

		// Round to 1 decimal place to determine display.
		$rounded = round( $percentage, 1 );

		// No change - show neutral indicator.
		if ( 0.0 === $rounded ) {
			return sprintf(
				'<span style="margin-left: 8px; color: #888; font-weight: 600; font-size: 0.85em;">%s</span>',
				esc_html__( '— No change', 'easy-digital-downloads' )
			);
		}

		$arrow = $percentage > 0 ? '▲' : '▼';
		$color = $percentage > 0 ? '#46b450' : '#dc3232';
		$sign  = $percentage > 0 ? '+' : '';

		return sprintf(
			'<span style="margin-left: 8px; color: %s; font-weight: 600; font-size: 0.85em;">%s%s%s%%</span>',
			esc_attr( $color ),
			esc_html( $arrow ),
			esc_html( $sign ),
			esc_html( number_format( abs( $percentage ), 1 ) )
		);
	}
}
