<?php


namespace Elex\MultiVendor\Model;

if ( ! defined( 'ABSPATH' ) ) {
exit;
}

class WoocommerceReport {


	public function get_order_report_on_sales( $store_id, $from_date, $to_date, $product_id ) {
	
		global $wpdb;

		new \WpFluent\Connection( $wpdb, array(), 'DB' );

		$table_prefix = $wpdb->prefix;

		$product_meta_lookup = "INNER JOIN {$wpdb->prefix}wc_order_product_lookup as prod_lookup ON orders.order_id = prod_lookup.order_id AND prod_lookup.product_id = " . $product_id   ;

		if ( ! empty( $product_id ) ) {

			$results_com = $wpdb->get_results(
				$wpdb->prepare(
					"
				SELECT  orders.order_id as ord_id, DATE(orders.date_created_gmt) as order_date, prod_lookup.product_net_revenue as total_sales_product
				FROM {$wpdb->prefix}wc_order_stats as orders
				INNER JOIN {$wpdb->prefix}wc_orders_meta as vendor_id ON orders.order_id = vendor_id.order_id AND vendor_id.meta_key = 'elex_mv_store_id' AND vendor_id.meta_value = %s
				INNER JOIN {$wpdb->prefix}wc_order_product_lookup as prod_lookup ON orders.order_id = prod_lookup.order_id AND prod_lookup.product_id = %s
				WHERE orders.status IN ('wc-processing', 'wc-completed')
				AND DATE(orders.date_created_gmt) BETWEEN %s AND %s
				GROUP BY DATE(orders.date_created)
				",
					$store_id,
					$product_id,
					$from_date,
					$to_date
				) 
			); 
	
			$date_range = array();
			$from_date  = new \DateTime( $from_date );
			$end_date   = new \DateTime( $to_date );
	
			while ( $from_date <= $end_date ) {
				$date                = $from_date->format( 'Y-m-d' );
				$date_range[ $date ] = 0; // Initialize total_admin_commission to 0
				$from_date->modify( '+1 day' );
			}
	
	
			foreach ( $results_com as $item ) {
				$date_range[ $item->order_date ] = $item;
			}

			$result = array();

			foreach ( $date_range as $key => $item ) {
				if ( is_object( $item ) ) {
					$order_date = $key;

					$order_id                 = $item->ord_id;
					$order                    = wc_get_order( $order_id );
					$order_product_commission = $order->get_meta( 'elex_mv_commission_cut_after_refund_for_product_id_' . $product_id );

					$combinedItem = (object) array(
						'order_date'             => $order_date,
						'total_admin_commission' => $order_product_commission ? $order_product_commission : 0,
						'total_sales_product'    => $item->total_sales_product ? $item->total_sales_product : 0,
						'total_vendor_earning'   => (float) $item->total_sales_product - (float) $order_product_commission ? (float) $item->total_sales_product - (float) $order_product_commission : 0,

					);
					$result[] = $combinedItem;
				} else {
					$order_date = $key;

					$combinedItem = (object) array(
						'order_date'             => $order_date,
						'total_admin_commission' => 0,
						'total_sales_product'    => 0,
						'total_vendor_earning'   => 0,

					);
					$result[] = $combinedItem;
				}           
			}
			return $result;
	

		} else {
			
			
			$results_com = $wpdb->get_results(
				$wpdb->prepare(
					"
				SELECT orders.order_id as ord_id, DATE(orders.date_created_gmt) as order_date, SUM(admin_commission.meta_value) as total_admin_commission
				FROM {$wpdb->prefix}wc_order_stats as orders
				INNER JOIN {$wpdb->prefix}wc_orders_meta as admin_commission ON orders.order_id = admin_commission.order_id AND admin_commission.meta_key = 'elex_mv_admin_commission_from_product'
				INNER JOIN {$wpdb->prefix}wc_orders_meta as vendor_id ON orders.order_id = vendor_id.order_id AND vendor_id.meta_key = 'elex_mv_store_id' AND vendor_id.meta_value = %s
				WHERE orders.status IN ('wc-processing', 'wc-completed')
				AND DATE(orders.date_created_gmt) BETWEEN %s AND %s
				GROUP BY DATE(orders.date_created)
				",
					$store_id,
					$from_date,
					$to_date
				) 
			);
	
			$results_sales = $wpdb->get_results(
				$wpdb->prepare(
					"
				SELECT  orders.order_id as ord_id, DATE(orders.date_created_gmt) as order_date, SUM(orders.total_sales) as total_sales_product
				FROM {$wpdb->prefix}wc_order_stats as orders
				INNER JOIN {$wpdb->prefix}wc_orders_meta as vendor_id ON orders.order_id = vendor_id.order_id AND vendor_id.meta_key = 'elex_mv_store_id' AND vendor_id.meta_value = %s
				WHERE orders.status IN ('wc-processing', 'wc-completed')
				AND DATE(orders.date_created_gmt) BETWEEN %s AND %s
				GROUP BY DATE(orders.date_created)
				",
					$store_id,
					$from_date,
					$to_date
				) 
			);
	
	
			$date_range       = array();
			$date_range_sales = array();
			$from_date        = new \DateTime( $from_date );
			$end_date         = new \DateTime( $to_date );
	
			while ( $from_date <= $end_date ) {
				$date                      = $from_date->format( 'Y-m-d' );
				$date_range[ $date ]       = 0; // Initialize total_admin_commission to 0
				$date_range_sales[ $date ] = 0;
				$from_date->modify( '+1 day' );
			}
	
	
			foreach ( $results_com as $item ) {
				$date_range[ $item->order_date ] = $item;
			}
	
			foreach ( $results_sales as $item ) {
				$date_range_sales[ $item->order_date ] = $item;
			}
	
	
			// Join arrays a and b based on order_date
			$result = array();
			foreach ( $date_range_sales as $key => $item ) {
				$order_date = $key;
				if ( is_object( $item ) ) {

					if ( isset( $date_range[ $order_date ] ) ) {

						$admin_commission = 0;
						if ( is_object( $date_range[ $order_date ] ) && isset( $date_range[ $order_date ]->total_admin_commission ) ){
							$admin_commission =  $date_range[ $order_date ]->total_admin_commission;
						}
						
						$sales_product    = isset( $item->total_sales_product ) ? $item->total_sales_product : 0;

						$combinedItem = (object) array(
							'order_date'             => $order_date,
							'total_admin_commission' => $admin_commission,
							'total_sales_product'    => $sales_product,
							'total_vendor_earning'   => $sales_product - $admin_commission,
		
						);
						$result[] = $combinedItem;
					}
				} elseif ( isset( $date_range[ $order_date ] ) ) {
						$combinedItem = (object) array(
							'order_date'             => $order_date,
							'total_admin_commission' => 0,
							'total_sales_product'    => 0,
							'total_vendor_earning'   => 0,
		
						);
						$result[] = $combinedItem;
				}           
			}
	
	
	
			foreach ( $result as &$item ) {
				$item = (array) $item;
			}
		}

	
		return $result;
	}

	public function get_order_report_data( $args = array() ) {
		global $wpdb;

		$default_args = array(
			'data'                => array(),
			'where'               => array(),
			'where_meta'          => array(),
			'query_type'          => 'get_row',
			'group_by'            => '',
			'order_by'            => '',
			'limit'               => '',
			'filter_range'        => false,
			'nocache'             => false,
			'debug'               => false,
			'order_types'         => wc_get_order_types( 'reports' ),
			'order_status'        => array( 'completed', 'processing', 'on-hold' ),
			'parent_order_status' => false,
		);

		/**
		* Filters the arguments used to retrieve order report data in WooCommerce reports.
		*
		* This filter allows modification or customization of the arguments used to fetch
		* order report data before the data is retrieved.
		*
		* @since 1.0.0
		*
		* @param array $args The arguments array used to retrieve order report data.
		* @return array      Modified or unmodified arguments array based on filter implementation.
		*/

		$args = apply_filters( 'elex_mv_reports_get_order_report_data_args', $args );
		$args = wp_parse_args( $args, $default_args );

		// phpcs:ignore WordPress.PHP.DontExtract.extract_extract
		extract( $args );

		if ( empty( $data ) ) {
			return '';
		}
		/**
		 * Filter the order statuses included in WooCommerce reports.
		 *
		 * The 'woocommerce_reports_order_statuses' filter allows modifying the order statuses
		 * that are considered in various reports generated by WooCommerce, such as sales reports,
		 * order count reports, and other statistical reports.
		 *
		 * @since 1.0.0
		 * 
		 * @param array $order_status Array of order statuses used in reports.
		 * @return array Modified array of order statuses.
		 */
		$order_status = apply_filters( 'elex_mv_reports_order_statuses', $order_status );

		$query  = array();
		$select = array();

		foreach ( $data as $raw_key => $value ) {
			$key      = sanitize_key( $raw_key );
			$distinct = '';

			if ( isset( $value['distinct'] ) ) {
				$distinct = 'DISTINCT';
			}

			switch ( $value['type'] ) {
				case 'meta':
					$get_key = "meta_{$key}.meta_value";
					break;
				case 'parent_meta':
					$get_key = "parent_meta_{$key}.meta_value";
					break;
				case 'post_data':
					$get_key = "posts.{$key}";
					break;
				case 'order_item_meta':
					$get_key = "order_item_meta_{$key}.meta_value";
					break;
				case 'order_item':
					$get_key = "order_items.{$key}";
					break;
			}

			if ( empty( $get_key ) ) {
				// Skip to the next foreach iteration else the query will be invalid.
				continue;
			}

			if ( $value['function'] ) {
				$get = "{$value['function']}({$distinct} {$get_key})";
			} else {
				$get = "{$distinct} {$get_key}";
			}

			$select[] = "{$get} as {$value['name']}";
		}

		// $select items are built from sanitize_key() and trusted constants — no user data.
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$query['select'] = 'SELECT ' . implode( ',', $select );
		$query['from']   = "FROM {$wpdb->prefix}wc_orders AS posts";

		// Joins.
		$joins = array();

		foreach ( ( $data + $where ) as $raw_key => $value ) {
			$join_type = isset( $value['join_type'] ) ? $value['join_type'] : 'INNER';
			$type      = isset( $value['type'] ) ? $value['type'] : false;
			$key       = sanitize_key( $raw_key );

			switch ( $type ) {
				case 'meta':
					$joins[ "meta_{$key}" ] = "{$join_type} JOIN {$wpdb->prefix}wc_orders_meta AS meta_{$key} ON ( posts.ID = meta_{$key}.id AND meta_{$key}.meta_key = '{$raw_key}' )";
					break;
				case 'parent_meta':
					$joins[ "parent_meta_{$key}" ] = "{$join_type} JOIN {$wpdb->prefix}wc_orders_meta AS parent_meta_{$key} ON (posts.post_parent = parent_meta_{$key}.id) AND (parent_meta_{$key}.meta_key = '{$raw_key}')";
					break;
				case 'order_item_meta':
					$joins['order_items'] = "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON (posts.ID = order_items.order_id)";

					if ( ! empty( $value['order_item_type'] ) ) {
						$joins['order_items'] .= " AND (order_items.order_item_type = '{$value['order_item_type']}')";
					}

					$joins[ "order_item_meta_{$key}" ] = "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS order_item_meta_{$key} ON " .
														"(order_items.order_item_id = order_item_meta_{$key}.order_item_id) " .
														" AND (order_item_meta_{$key}.meta_key = '{$raw_key}')";
					break;
				case 'order_item':
					$joins['order_items'] = "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON posts.ID = order_items.order_id";
					break;
			}
		}

		if ( ! empty( $where_meta ) ) {
			foreach ( $where_meta as $value ) {
				if ( ! is_array( $value ) ) {
					continue;
				}
				$join_type = isset( $value['join_type'] ) ? $value['join_type'] : 'INNER';
				$type      = isset( $value['type'] ) ? $value['type'] : false;
				$key       = sanitize_key( is_array( $value['meta_key'] ) ? $value['meta_key'][0] . '_array' : $value['meta_key'] );

				if ( 'order_item_meta' === $type ) {

					$joins['order_items']              = "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON posts.ID = order_items.order_id";
					$joins[ "order_item_meta_{$key}" ] = "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS order_item_meta_{$key} ON order_items.order_item_id = order_item_meta_{$key}.order_item_id";

				} else {
					// If we have a where clause for meta, join the postmeta table.
					$joins[ "meta_{$key}" ] = "{$join_type} JOIN {$wpdb->prefix}wc_orders_meta AS meta_{$key} ON posts.ID = meta_{$key}.post_id";
				}
			}
		}

		if ( ! empty( $parent_order_status ) ) {
			$joins['parent'] = "LEFT JOIN {$wpdb->prefix}wc_orders AS parent ON posts.parent_order_id = parent.ID";
		}

		$query['join'] = implode( ' ', $joins );

		$query_params = array();

		$type_placeholders = implode( ',', array_fill( 0, count( $order_types ), '%s' ) );
		$query_params      = array_merge( $query_params, array_values( $order_types ) );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$query['where'] = "WHERE posts.type IN ($type_placeholders)";

		if ( ! empty( $order_status ) ) {
			$prefixed_statuses   = array_map(
				function ( $s ) {
				return 'wc-' . $s;
				},
				$order_status 
			);
			$status_placeholders = implode( ',', array_fill( 0, count( $prefixed_statuses ), '%s' ) );
			$query_params        = array_merge( $query_params, array_values( $prefixed_statuses ) );
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$query['where'] .= " AND posts.status IN ($status_placeholders)";
		}

		if ( ! empty( $parent_order_status ) ) {
			$prefixed_parent     = array_map(
				function ( $s ) {
				return 'wc-' . $s;
				},
				$parent_order_status 
			);
			$parent_placeholders = implode( ',', array_fill( 0, count( $prefixed_parent ), '%s' ) );
			$query_params        = array_merge( $query_params, array_values( $prefixed_parent ) );
			if ( ! empty( $order_status ) ) {
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$query['where'] .= " AND ( parent.status IN ($parent_placeholders) OR parent.ID IS NULL )";
			} else {
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$query['where'] .= " AND parent.status IN ($parent_placeholders)";
			}
		}

		// phpcs:disable WordPress.DateTime.RestrictedFunctions.date_date
		if ( $filter_range ) {
			$query_params[]  = gmdate( 'Y-m-d H:i:s', $this->start_date );
			$query_params[]  = gmdate( 'Y-m-d H:i:s', strtotime( '+1 DAY', $this->end_date ) );
			$query['where'] .= ' AND posts.date_created_gmt >= %s AND posts.date_created_gmt < %s';
		}
		// phpcs:enable WordPress.DateTime.RestrictedFunctions.date_date

		if ( ! empty( $where_meta ) ) {

			$relation = isset( $where_meta['relation'] ) ? $where_meta['relation'] : 'AND';

			$query['where'] .= ' AND (';

			foreach ( $where_meta as $index => $value ) {

				if ( ! is_array( $value ) ) {
					continue;
				}

				$key         = sanitize_key( is_array( $value['meta_key'] ) ? $value['meta_key'][0] . '_array' : $value['meta_key'] );
				$where_value = '';
				$meta_params = array();

				if ( strtolower( $value['operator'] ) === 'in' || strtolower( $value['operator'] ) === 'not in' ) {

					// phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_value
					$meta_values = is_array( $value['meta_value'] ) ? array_values( $value['meta_value'] ) : array( $value['meta_value'] );

					if ( ! empty( $meta_values ) ) {
						$meta_placeholders = implode( ',', array_fill( 0, count( $meta_values ), '%s' ) );
						$meta_params       = $meta_values;
						// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
						$where_value = "{$value['operator']} ($meta_placeholders)";
					}
				} else {
					$meta_params = array( $value['meta_value'] );
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					$where_value = "{$value['operator']} %s";
				}

				if ( ! empty( $where_value ) ) {
					if ( $index > 0 ) {
						$query['where'] .= ' ' . $relation;
					}

					if ( isset( $value['type'] ) && 'order_item_meta' === $value['type'] ) {

						if ( is_array( $value['meta_key'] ) ) {
							$key_placeholders = implode( ',', array_fill( 0, count( $value['meta_key'] ), '%s' ) );
							$query_params     = array_merge( $query_params, array_values( $value['meta_key'] ) );
							// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
							$query['where'] .= " ( order_item_meta_{$key}.meta_key IN ($key_placeholders)";
						} else {
							$query_params[] = $value['meta_key'];
							// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
							$query['where'] .= " ( order_item_meta_{$key}.meta_key = %s";
						}

						$query_params = array_merge( $query_params, $meta_params );
						// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
						$query['where'] .= " AND order_item_meta_{$key}.meta_value {$where_value} )";
					} else {

						if ( is_array( $value['meta_key'] ) ) {
							$key_placeholders = implode( ',', array_fill( 0, count( $value['meta_key'] ), '%s' ) );
							$query_params     = array_merge( $query_params, array_values( $value['meta_key'] ) );
							// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
							$query['where'] .= " ( meta_{$key}.meta_key IN ($key_placeholders)";
						} else {
							$query_params[] = $value['meta_key'];
							// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
							$query['where'] .= " ( meta_{$key}.meta_key = %s";
						}

						$query_params = array_merge( $query_params, $meta_params );
						// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
						$query['where'] .= " AND meta_{$key}.meta_value {$where_value} )";
					}
				}
			}

			$query['where'] .= ')';
		}

		if ( ! empty( $where ) ) {

			foreach ( $where as $value ) {

				$where_value  = '';
				$where_params = array();

				if ( strtolower( $value['operator'] ) === 'in' || strtolower( $value['operator'] ) === 'not in' ) {

					$where_values = is_array( $value['value'] ) ? array_values( $value['value'] ) : array( $value['value'] );

					if ( ! empty( $where_values ) ) {
						$val_placeholders = implode( ',', array_fill( 0, count( $where_values ), '%s' ) );
						$where_params     = $where_values;
						// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
						$where_value = "{$value['operator']} ($val_placeholders)";
					}
				} else {
					$where_params = array( $value['value'] );
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					$where_value = "{$value['operator']} %s";
				}

				if ( ! empty( $where_value ) ) {
					$query_params = array_merge( $query_params, $where_params );
					// $value['key'] is a column name from internal callers — not user input.
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					$query['where'] .= " AND {$value['key']} {$where_value}";
				}
			}
		}

		if ( $group_by ) {
			$query['group_by'] = "GROUP BY {$group_by}";
		}

		if ( $order_by ) {
			$query['order_by'] = "ORDER BY {$order_by}";
		}

		if ( $limit ) {
			$query['limit'] = "LIMIT {$limit}";
		}

		/**
		* Filters the SQL query used to retrieve order report data in WooCommerce reports.
		*
		* This filter allows modification or customization of the SQL query used to fetch
		* order report data before it is executed.
		*
		* @since 1.0.0
		*
		* @param string $query The SQL query string used to retrieve order report data.
		* @return string       Modified or unmodified SQL query based on filter implementation.
		*/
		$query = apply_filters( 'elex_mv_reports_get_order_report_query', $query );
		$query = implode( ' ', $query );

		if ( ! empty( $query_params ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$query = $wpdb->prepare( $query, ...$query_params );
		}

		/**
		* Filters the order report data obtained from WooCommerce reports.
		*
		* This filter allows manipulation or customization of the order report data retrieved
		* before it is processed further or returned.
		*
		* @since 1.0.0
		*
		* @param mixed $data       The order report data obtained from the database query.
		* @param string $query     The SQL query string used to fetch the data.
		* @return mixed            Modified or unmodified order report data based on filter implementation.
		*/
		$result = apply_filters( 'elex_mv_reports_get_order_report_data', $wpdb->$query_type( $query ), $data ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

		return $result;
	}
}
