<?php
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

class ANFOWO_Customer_Query {

	public static function get_report( $period, $start_date = '', $end_date = '' ) {
		global $wpdb;
		$dates = ANFOWO_Date_Resolver::resolve( $period, $start_date, $end_date );
		// For customer queries, we largely rely on `wc_customer_lookup`. 
		// We filter by date using `date_last_active` or orders within the period.
		
		$paid_statuses = ANFOWO_Database::get_paid_statuses_sql();

		// We will join order_stats to see which customers ordered in this period
		$where = "WHERE os.status IN ($paid_statuses)";
		$where = ANFOWO_Database::append_date_where( $where, 'os.date_created', $dates['start'], $dates['end'] );

		// Active customers in the period, their overall LTV using lookup
		$query = "SELECT 
			cl.email as email,
			CONCAT(cl.first_name, ' ', cl.last_name) as name,
			cl.city as city,
			cl.country as country,
			COUNT(DISTINCT os.order_id) as orders_count,
			cl.date_last_active as last_order,
			SUM(os.net_total) as ltv
			FROM {$wpdb->prefix}wc_order_stats os
			JOIN {$wpdb->prefix}wc_customer_lookup cl ON cl.customer_id = os.customer_id
			$where
			GROUP BY cl.customer_id
			ORDER BY ltv DESC";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$results = $wpdb->get_results( $query, ARRAY_A );

		$labels = array();
		$ltv = array();
		$quantity = array();

		$chart_limit = 15;
		$chart_count = 0;

		foreach ( $results as $row ) {
			if ( $chart_count < $chart_limit ) {
				$labels[] = trim( $row['name'] ) !== '' ? trim( $row['name'] ) : 'Guest';
				$ltv[] = (float) $row['ltv'];
				$quantity[] = (int) $row['orders_count'];
				$chart_count++;
			}
		}

		return array(
			'table' => $results,
			'chart' => array(
				'labels' => $labels,
				'ltv' => $ltv,
				'quantity' => $quantity,
			)
		);
	}
}