<?php
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

class ANFOWO_Taxonomy_Query {

	public static function get_report( $period, $start_date = '', $end_date = '', $taxonomy_type = 'all' ) {
		// Adjust parameters if called with ($dates, $taxonomy_type)
		if ( is_array( $period ) ) {
			$dates = $period;
			$taxonomy_type = $start_date ? $start_date : 'all';
		} else {
			$dates = ANFOWO_Date_Resolver::resolve( $period, $start_date, $end_date );
		}
		global $wpdb;
		$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'] );

		if ( $taxonomy_type === 'all' ) {
			$query = "SELECT 
				CASE 
					WHEN tt.taxonomy IN ('pwb-brand', 'product_brand', 'brand') THEN 'Brands'
					WHEN tt.taxonomy = 'product_cat' THEN 'Categories'
					WHEN tt.taxonomy = 'product_tag' THEN 'Tags'
					WHEN tt.taxonomy LIKE '%author%' OR tt.taxonomy LIKE '%writer%' THEN 'Authors'
					WHEN tt.taxonomy LIKE '%language%' THEN 'Languages'
					WHEN tt.taxonomy LIKE '%publisher%' THEN 'Publishers'
					WHEN tt.taxonomy LIKE '%translator%' THEN 'Translator'
				END 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 IN ('pwb-brand', 'product_brand', 'brand', 'product_cat', 'product_tag') OR
					tt.taxonomy LIKE '%author%' OR tt.taxonomy LIKE '%writer%' OR
					tt.taxonomy LIKE '%language%' OR 
					tt.taxonomy LIKE '%publisher%' OR 
					tt.taxonomy LIKE '%translator%'
				)
				$where
				GROUP BY name
				ORDER BY revenue DESC";
		} else {
			// Determine taxonomy filter
			$tax_condition = "tt.taxonomy IN ('pwb-brand', 'product_brand', 'brand')";
			if ( $taxonomy_type === 'categories' ) {
				$tax_condition = "tt.taxonomy = 'product_cat'";
			} elseif ( $taxonomy_type === 'tags' ) {
				$tax_condition = "tt.taxonomy = 'product_tag'";
			} elseif ( $taxonomy_type === 'authors' ) {
				$tax_condition = "(tt.taxonomy LIKE '%author%' OR tt.taxonomy LIKE '%writer%')";
			} elseif ( $taxonomy_type === 'languages' ) {
				$tax_condition = "tt.taxonomy LIKE '%language%'";
			} elseif ( $taxonomy_type === 'publishers' ) {
				$tax_condition = "tt.taxonomy LIKE '%publisher%'";
			} elseif ( $taxonomy_type === 'translator' ) {
				$tax_condition = "tt.taxonomy LIKE '%translator%'";
			}

			// Custom Taxonomies (excluding default ones if not specifically requested)
			$query = "SELECT 
				MAX(t.term_id) as term_id,
				GROUP_CONCAT(DISTINCT t.term_id) as term_ids,
				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 $tax_condition
				JOIN {$wpdb->terms} t ON t.term_id = tt.term_id
				$where
				GROUP BY t.name
				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 );

		if ( $taxonomy_type === 'publishers' ) {
			foreach ( $results as &$row ) {
				$term_ids = isset( $row['term_ids'] ) ? $row['term_ids'] : (isset( $row['term_id'] ) ? $row['term_id'] : 0);
				if ( $term_ids ) {
					$term_ids_array = array_map( 'intval', explode( ',', $term_ids ) );
					$term_ids_in = implode( ',', $term_ids_array );
					$stock_query = "
						SELECT SUM(pml.stock_quantity) 
						FROM {$wpdb->prefix}wc_product_meta_lookup pml
						JOIN {$wpdb->term_relationships} tr ON pml.product_id = tr.object_id
						JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
						WHERE tt.term_id IN ($term_ids_in)
					";
					
					// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
					$term_stock = $wpdb->get_var( $stock_query );
					$row['stock'] = $term_stock ? (int) $term_stock : 0;
				} else {
					$row['stock'] = 0;
				}
			}
			unset($row);
		}

		$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,
			)
		);
	}
}