<?php
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

class ANFOWO_Tax_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();

		$sql_where = "WHERE os.status IN ($paid_statuses)";
		$sql_where = ANFOWO_Database::append_date_where( $sql_where, 'os.date_created', $dates['start'], $dates['end'] );

		$query = "SELECT 
			tr.tax_rate_name as name,
			tr.tax_rate as rate,
			COUNT(DISTINCT otl.order_id) as orders_count,
			SUM(otl.total_tax) as tax_amount
			FROM {$wpdb->prefix}wc_order_tax_lookup otl
			JOIN {$wpdb->prefix}wc_order_stats os ON os.order_id = otl.order_id
			JOIN {$wpdb->prefix}woocommerce_tax_rates tr ON tr.tax_rate_id = otl.tax_rate_id
			$sql_where
			GROUP BY otl.tax_rate_id
			ORDER BY tax_amount 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 );

		return array(
			'table' => $results,
			'chart' => array(
				'labels' => array(), // Tax optionally doesn't need charts, just list
				'revenue' => array(),
			)
		);
	}
}