<?php
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

class ANFOWO_Sales_Query {

	/**
	 * Get sales report data for the given period.
	 *
	 * @param array|string $period
	 * @param string       $start_date
	 * @param string       $end_date
	 * @return array
	 */
	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 status IN ($paid_statuses)";
		$where = ANFOWO_Database::append_date_where( $where, 'date_created', $dates['start'], $dates['end'] );

		// 1. KPI Summary
		$kpi_query = "SELECT 
			SUM(total_sales) as gross_sales,
			SUM(net_total) as net_sales,
			SUM(shipping_total) as shipping,
			SUM(tax_total) as tax,
			COUNT(order_id) as order_count
			FROM {$wpdb->prefix}wc_order_stats 
			$where";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$kpi = $wpdb->get_row( $kpi_query, ARRAY_A );

		$gross_sales = (float) ($kpi['gross_sales'] ?? 0.0);
		$net_sales   = (float) ($kpi['net_sales'] ?? 0.0);
		$shipping    = (float) ($kpi['shipping'] ?? 0.0);
		$tax         = (float) ($kpi['tax'] ?? 0.0);
		$order_count = (int) ($kpi['order_count'] ?? 0);
		$avg_order   = $order_count > 0 ? ($net_sales / $order_count) : 0.0;

		// 2. Determine grouping mode based on date range duration
		$start_ts   = strtotime( $dates['start'] );
		$end_ts     = strtotime( $dates['end'] );
		$day_diff   = (int) ceil( ( $end_ts - $start_ts ) / DAY_IN_SECONDS );

		if ( $day_diff <= 31 ) {
			$group_expr = 'DATE(date_created)';
			$group_mode = 'daily';
		} elseif ( $day_diff <= 90 ) {
			$group_expr = "DATE_FORMAT(DATE_SUB(date_created, INTERVAL WEEKDAY(date_created) DAY), '%Y-%m-%d')";
			$group_mode = 'weekly';
		} else {
			$group_expr = "DATE_FORMAT(date_created, '%Y-%m-01')";
			$group_mode = 'monthly';
		}

		// 3. Periodic breakdown
		$breakdown_query = "SELECT 
			{$group_expr} as label_date, 
			SUM(total_sales) as period_gross_sales,
			SUM(net_total) as period_net_sales,
			SUM(shipping_total) as period_shipping,
			SUM(tax_total) as period_tax,
			COUNT(order_id) as period_order_count
			FROM {$wpdb->prefix}wc_order_stats
			$where
			GROUP BY label_date
			ORDER BY label_date ASC";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$breakdown_results = $wpdb->get_results( $breakdown_query, ARRAY_A );

		$labels     = array();
		$net_data   = array();
		$table_data = array();

		foreach ( $breakdown_results as $row ) {
			$ts = strtotime( $row['label_date'] );
			if ( 'monthly' === $group_mode ) {
				$label = date_i18n( 'M Y', $ts );
			} elseif ( 'weekly' === $group_mode ) {
				$label = date_i18n( 'M j', $ts );
			} else {
				$label = date_i18n( 'M j', $ts );
			}
			$labels[]   = $label;
			$net_data[] = (float) $row['period_net_sales'];

			$p_orders = (int) $row['period_order_count'];
			$p_net    = (float) $row['period_net_sales'];

			$table_data[] = array(
				'date'        => $label,
				'orders'      => $p_orders,
				'gross_sales' => (float) $row['period_gross_sales'],
				'net_sales'   => $p_net,
				'shipping'    => (float) $row['period_shipping'],
				'tax'         => (float) $row['period_tax'],
				'avg_order'   => $p_orders > 0 ? ($p_net / $p_orders) : 0.0,
			);
		}

		// Reverse table data so newest dates are first in the list
		$table_data = array_reverse( $table_data );

		return array(
			'kpi'   => array(
				'gross_sales' => $gross_sales,
				'net_sales'   => $net_sales,
				'shipping'    => $shipping,
				'tax'         => $tax,
				'orders'      => $order_count,
				'avg_order'   => $avg_order,
			),
			'chart' => array(
				'labels' => $labels,
				'data'   => $net_data,
			),
			'table' => $table_data,
		);
	}
}