<?php
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

class ANFOWO_Category_Query {

	public static function get_report( $period, $start_date = '', $end_date = '' ) {
		global $wpdb;
		$dates = ANFOWO_Date_Resolver::resolve( $period, $start_date, $end_date );
		$paid_statuses = ANFOWO_Database::get_paid_statuses_sql();

		$where = "WHERE os.status IN ($paid_statuses)";
		$where = ANFOWO_Database::append_date_where( $where, 'os.date_created', $dates['start'], $dates['end'] );

		$query = "SELECT 
			t.term_id,
			t.name as name,
			SUM(
				CASE 
					WHEN COALESCE(CAST(NULLIF(pm.meta_value, '') AS DECIMAL(10,2)), 0.00) = 0 THEN 0
					ELSE opl.product_qty
				END
			) as items_sold,
			SUM(opl.product_net_revenue) as revenue
			FROM {$wpdb->prefix}wc_order_product_lookup opl
			JOIN {$wpdb->prefix}wc_order_stats os ON os.order_id = opl.order_id
			LEFT JOIN {$wpdb->postmeta} pm ON pm.post_id = COALESCE(NULLIF(opl.variation_id, 0), opl.product_id) AND pm.meta_key = '_price'
			JOIN {$wpdb->term_relationships} tr ON tr.object_id = opl.product_id
			JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id AND tt.taxonomy = 'product_cat'
			JOIN {$wpdb->terms} t ON t.term_id = tt.term_id
			$where
			GROUP BY t.term_id
			ORDER BY revenue 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();
		$revenue = array();
		$quantity = array();

		foreach ( $results as $row ) {
			$labels[] = $row['name'];
			$revenue[] = (float) $row['revenue'];
			$quantity[] = (int) $row['items_sold'];
		}

		return array(
			'table' => $results,
			'chart' => array(
				'labels' => $labels,
				'revenue' => $revenue,
				'quantity' => $quantity,
			)
		);
	}
}