<?php
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

class ANFOWO_Order_Query {

	public static function get_report( $period, $start_date = '', $end_date = '' ) {
		global $wpdb;
		$dates = ANFOWO_Date_Resolver::resolve( $period, $start_date, $end_date );
		
		// 1. Fetch accurate status summary and overall totals from wc_order_stats
		$where = "WHERE 1=1";
		$where = ANFOWO_Database::append_date_where( $where, 'date_created', $dates['start'], $dates['end'] );
		
		$summary_query = "SELECT 
			status,
			COUNT(order_id) as order_count,
			SUM(total_sales) as total_amount
			FROM {$wpdb->prefix}wc_order_stats 
			$where
			GROUP BY status";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$summary_results = $wpdb->get_results( $summary_query, ARRAY_A );
		
		$status_summary = array();
		$total_orders = 0;
		$grand_total = 0.0;
		
		if ( ! function_exists( 'wc_get_order_status_name' ) ) {
			include_once WC_ABSPATH . 'includes/wc-order-functions.php';
		}
		
		foreach ( $summary_results as $row ) {
			$original_slug = str_replace( 'wc-', '', $row['status'] );
			$sc = ANFOWO_Helper::normalize_status_slug( $original_slug );
			$count = (int) $row['order_count'];
			$total = (float) $row['total_amount'];
			
			// Try to get a valid display label
			$label = wc_get_order_status_name( $original_slug );
			if ( $label === $original_slug ) {
				// Fall back to normalized if original is just a slug
				$label = wc_get_order_status_name( $sc );
			}
			
			if ( isset( $status_summary[ $sc ] ) ) {
				$status_summary[ $sc ]['count'] += $count;
				$status_summary[ $sc ]['total'] += $total;
				if ( $status_summary[ $sc ]['label'] === $sc && $label !== $sc ) {
					$status_summary[ $sc ]['label'] = $label;
				}
			} else {
				$status_summary[ $sc ] = array(
					'label' => $label,
					'count' => $count,
					'total' => $total,
				);
			}
			
			$total_orders += $count;
			$grand_total += $total;
		}

		// 2. Fetch the actual orders list from wc_order_stats to ensure 100% consistency with the summary counts
		$order_ids_query = "SELECT order_id 
			FROM {$wpdb->prefix}wc_order_stats 
			$where 
			ORDER BY date_created DESC";
		
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$order_ids = $wpdb->get_col( $order_ids_query );
		
		$report = array();
		$countries = ( function_exists( 'WC' ) && WC()->countries ) ? WC()->countries->get_countries() : array();
		
		foreach ( $order_ids as $order_id ) {
			$order = wc_get_order( $order_id );
			if ( ! is_a( $order, 'WC_Order' ) ) {
				continue;
			}
			$items = array();
			$items_count = 0;
			foreach ( $order->get_items() as $item_id => $item ) {
				if ( ! is_object( $item ) ) {
					continue;
				}
				$product = method_exists( $item, 'get_product' ) ? $item->get_product() : null;
				$quantity = method_exists( $item, 'get_quantity' ) ? $item->get_quantity() : 0;
				$item_total = method_exists( $item, 'get_total' ) ? $item->get_total() : 0;
				$item_name = method_exists( $item, 'get_name' ) ? $item->get_name() : '';

				if ( ( $product && (float) $product->get_price() === 0.0 ) || ( ! $product && (float) $item_total === 0.0 ) ) {
					$quantity = 0;
				}
				$items[] = array(
					'name'     => $item_name,
					'quantity' => $quantity,
					'total'    => $item_total,
				);
				$items_count += $quantity;
			}
			
			$country_code = $order->get_billing_country();
			$country_name = ( ! empty( $countries ) && isset( $countries[ $country_code ] ) ) ? $countries[ $country_code ] : $country_code;
			
			if ( $country_name && $country_name !== $country_code ) {
				$formatted_country = sprintf( '%s (%s)', $country_name, $country_code );
			} else {
				$formatted_country = $country_code ? $country_code : __( 'N/A', '360-analytics-for-woocommerce' );
			}
			
			$first_name = $order->get_billing_first_name();
			$last_name  = $order->get_billing_last_name();
			$customer_name = trim( $first_name . ' ' . $last_name );
			if ( empty( $customer_name ) ) {
				$customer_name = $order->get_billing_email() ?: __( 'Guest', '360-analytics-for-woocommerce' );
			}
			
			$report[] = array(
				'id'             => $order->get_id(),
				'status'         => wc_get_order_status_name( $order->get_status() ),
				'status_class'   => ANFOWO_Helper::normalize_status_slug( $order->get_status() ),
				'billing_country'=> $formatted_country,
				'customer_name'  => $customer_name,
				'customer_email' => $order->get_billing_email(),
				'date'           => $order->get_date_created() ? $order->get_date_created()->date_i18n( get_option( 'date_format' ) . ' ' . get_option( 'time_format' ) ) : '',
				'total'          => $order->get_total(),
				'items_count'    => $items_count,
				'items'          => $items,
				'edit_url'       => $order->get_edit_order_url(),
			);
		}

		return array(
			'orders'       => $report,
			'summary'      => $status_summary,
			'total_orders' => $total_orders,
			'grand_total'  => $grand_total,
		);
	}

	/**
	 * Get order volume chart data.
	 *
	 * @param array $dates Resolved dates.
	 * @return array
	 */
	public static function get_chart_data( $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'] );

		$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';
		}

		$query = "SELECT {$group_expr} as label_date, COUNT(order_id) as 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
		$results = $wpdb->get_results( $query, ARRAY_A );

		$labels = array();
		$counts = array();

		foreach ( $results as $row ) {
			$ts = strtotime( $row['label_date'] );
			if ( 'monthly' === $group_mode ) {
				$labels[] = date_i18n( 'M Y', $ts );
			} elseif ( 'weekly' === $group_mode ) {
				$labels[] = date_i18n( 'M j', $ts );
			} else {
				$labels[] = date_i18n( 'M j', $ts );
			}
			$counts[] = (int) $row['order_count'];
		}

		return array(
			'labels' => $labels,
			'data'   => $counts,
		);
	}
}