<?php
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

class ANFOWO_Product_Query {

	public static function get_summary( $dates ) {
		global $wpdb;
		$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'] );

		$kpi_query = "SELECT 
			SUM(net_total) as total_sales,
			COUNT(order_id) as order_count,
			SUM(num_items_sold) as items_sold
			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
		$summary = $wpdb->get_row( $kpi_query, ARRAY_A );

		// Calculate items sold of zero-priced products
		$zero_price_where = "WHERE os.status IN ($paid_statuses)";
		$zero_price_where = ANFOWO_Database::append_date_where( $zero_price_where, 'os.date_created', $dates['start'], $dates['end'] );
		$zero_price_query = "SELECT SUM(opl.product_qty)
		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'
		$zero_price_where
		AND COALESCE(CAST(NULLIF(pm.meta_value, '') AS DECIMAL(10,2)), 0.00) = 0";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$zero_price_qty = (int) $wpdb->get_var( $zero_price_query );

		$summary['items_sold'] = max( 0, (int) $summary['items_sold'] - $zero_price_qty );

		// Lifetime Sales
		$lifetime_where = "WHERE status IN ($paid_statuses)";
		$lifetime_query = "SELECT SUM(net_total) FROM {$wpdb->prefix}wc_order_stats $lifetime_where";
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$summary['lifetime_sales'] = $wpdb->get_var( $lifetime_query );

		return $summary;
	}

	public static function get_overview( $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'] );

		// KPI Revenue, Orders, Items, Customers
		$kpi_query = "SELECT 
			SUM(net_total) as revenue,
			COUNT(order_id) as orders,
			SUM(num_items_sold) as items,
			COUNT(DISTINCT customer_id) as new_customers
			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 );

		// Calculate items sold of zero-priced products
		$zero_price_where = "WHERE os.status IN ($paid_statuses)";
		$zero_price_where = ANFOWO_Database::append_date_where( $zero_price_where, 'os.date_created', $dates['start'], $dates['end'] );
		$zero_price_query = "SELECT SUM(opl.product_qty)
		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'
		$zero_price_where
		AND COALESCE(CAST(NULLIF(pm.meta_value, '') AS DECIMAL(10,2)), 0.00) = 0";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$zero_price_qty = (int) $wpdb->get_var( $zero_price_query );

		$kpi['items'] = max( 0, (int) $kpi['items'] - $zero_price_qty );

		// 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 ) {
			// Daily grouping
			$group_expr = 'DATE(date_created)';
			$group_mode = 'daily';
		} elseif ( $day_diff <= 90 ) {
			// Weekly grouping: group by Monday of each week
			$group_expr = "DATE_FORMAT(DATE_SUB(date_created, INTERVAL WEEKDAY(date_created) DAY), '%Y-%m-%d')";
			$group_mode = 'weekly';
		} else {
			// Monthly grouping
			$group_expr = "DATE_FORMAT(date_created, '%Y-%m-01')";
			$group_mode = 'monthly';
		}

		$chart_query = "SELECT {$group_expr} as label_date, SUM(net_total) as period_revenue
			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
		$chart_results = $wpdb->get_results( $chart_query, ARRAY_A );

		$labels  = array();
		$revenue = array();

		foreach ( $chart_results as $row ) {
			$ts = strtotime( $row['label_date'] );
			if ( 'monthly' === $group_mode ) {
				$labels[] = date_i18n( 'M Y', $ts );       // e.g. "Jan 2025"
			} elseif ( 'weekly' === $group_mode ) {
				$labels[] = date_i18n( 'M j', $ts );        // e.g. "Jan 6" (start of week)
			} else {
				$labels[] = date_i18n( 'M j', $ts );        // e.g. "Jan 15"
			}
			$revenue[] = (float) $row['period_revenue'];
		}

		return array(
			'kpi' => array(
				'revenue'       => (float) $kpi['revenue'],
				'orders'        => (int) $kpi['orders'],
				'items'         => (int) $kpi['items'],
				'new_customers' => (int) $kpi['new_customers'],
			),
			'chart' => array(
				'labels'  => $labels,
				'revenue' => $revenue,
			),
		);
	}

	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 
			p.ID as product_id,
			p.post_title 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,
			COUNT(DISTINCT opl.order_id) as orders_count
			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->posts} p ON p.ID = opl.product_id
			$where
			GROUP BY opl.product_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();

		$chart_limit = 15;
		$chart_count = 0;

		foreach ( $results as &$row ) {
			if ( $chart_count < $chart_limit ) {
				$labels[] = $row['name'];
				$revenue[] = (float) $row['revenue'];
				$quantity[] = (int) $row['items_sold'];
				$chart_count++;
			}
			$row['categories'] = wc_get_product_category_list( $row['product_id'] );
		}
		unset( $row );

		return array(
			'table' => $results,
			'chart' => array(
				'labels'   => $labels,
				'revenue'  => $revenue,
				'quantity' => $quantity,
			)
		);
	}
}