<?php

namespace Bookingor\Frontend\Controller\Service;

use Bookingor\Integrations\WooCommerce\WooCommercePaymentService;

class ServiceControlFront
{
	private static $bookingorDb;

	public static $f_service_all;
	public static $f_service_woocommcerce;
	public static $dp_prefix = 'bookingor_';

	/**
	 * Retrieves the list of services including their details like ID, name, icon, duration, cost, capacity, tax, padding before, recurring status, recurring type, and padding after.
	 *
	 * @param int $service_get_id  The ID of the service to retrieve if provided.
	 * @param int $category_get_id The ID of the category to retrieve if provided.
	 *
	 * @return array|null An array of services or null if no data is found.
	 */
	public static function bp_front_services_view($service_get_id = null, $category_get_id = null)
	{
		global $wpdb;
		self::$bookingorDb = $wpdb;
		$category = $wpdb->prefix . self::$dp_prefix . 'categories';
		$service = $wpdb->prefix . self::$dp_prefix . 'services';
		$location = $wpdb->prefix . self::$dp_prefix . 'location';
		$subcategory = $wpdb->prefix . self::$dp_prefix . 'subcategory';

		$location_assign = $wpdb->prefix . self::$dp_prefix . 'location_assign_service';
		$staff_assign = $wpdb->prefix . self::$dp_prefix . 'staff_assign_service';
		$posts = $wpdb->prefix . 'posts';
		$service_id = $service_get_id;
		$category_id = $category_get_id;

		if (class_exists('Bookingor\Frontend\Controller\ServiceSubcategory\ServiceSubcategoryControl')) {
			self::$f_service_all =  \Bookingor\Frontend\Controller\ServiceSubcategory\ServiceSubcategoryControl::subcategory_service_front();
			return self::$f_service_all;
		} else {
			if (!empty($service_get_id)) {
				if (!empty($location_assign)) {
					return self::$f_service_all = self::$bookingorDb->get_results(
						$wpdb->prepare(
							"SELECT 
					DISTINCT s.service_id, 
					la.service_get_id AS service_get_id_location, 
					s.service_description,
                    s.service_duration_type, 
					s.service_duration_count, 
					la.location_get_id, 
					l.location_address, 
					l.location_city, 
					l.location_country, 
					l.location_latitude, 
					l.location_longitude, 
					s.service_icon, 
					sa.staff_get_id, 
					s.service_name, 
					s.category_get_id, 
					s.service_cost, 
					s.service_duration,
					s.service_capacity_max,
					GROUP_CONCAT(DISTINCT la.location_get_id) AS location_get_id, 
					s.service_tax, 
					s.padding_before, 
					s.recurring_status, 
					s.recurring_type, 
					s.padding_after, 
					s.recurring_payment 
				FROM 
					%1\$s s 
				LEFT JOIN 
					%2\$s la ON s.service_id = la.service_get_id 
				LEFT JOIN 
					%3\$s sa ON s.service_id = sa.service_get_id 
				LEFT JOIN 
					%4\$s l ON l.location_id = la.location_get_id 
 
				WHERE 
					s.status = 1 and s.service_id = $service_id
				GROUP BY 
					s.service_id = $service_id",
							$service,
							$location_assign,
							$staff_assign,
							$location
						)
					);
				}
			}
			if (!empty($category_get_id && get_option('bookingor_settings_staff_active') === 'off') && get_option('bookingor_settings_location_active') === 'off') {
				if (!empty($location_assign)) {
					return self::$f_service_all = self::$bookingorDb->get_results(
						$wpdb->prepare(
							"SELECT 
					DISTINCT s.category_get_id, 
					s.service_description, 
 
					s.service_icon, 
					sa.staff_get_id, 
					s.service_name, 
                    s.service_duration_type,
					s.service_duration_count, 
					s.category_get_id, 
					s.service_cost, 
					s.service_duration,
					s.service_capacity_max,
					s.service_tax, 
					s.padding_before, 
					s.recurring_status, 
					s.recurring_type, 
					s.padding_after, 
					s.recurring_payment 
				FROM 
					%1\$s s 
				LEFT JOIN 
					%3\$s sa ON s.service_id = sa.service_get_id
 
				WHERE 
					s.status = 1 and s.category_get_id = $category_id
				GROUP BY 
					s.service_id = $category_id",
							$staff_assign,
							$service,
							$service
						)
					);
				}
			}
			if (!empty($category_get_id)) {
				// print_r($category_id);
				if (!empty($location_assign)) {
					return self::$f_service_all = self::$bookingorDb->get_results(
						$wpdb->prepare(
							"SELECT 
					DISTINCT s.category_get_id, 
					la.service_get_id AS service_get_id_location, 
					s.service_description, 
                    s.service_duration_type, 
 					s.service_duration_count,
					la.location_get_id, 
					l.location_address, 
					l.location_city, 
					l.location_country, 
					l.location_latitude, 
					l.location_longitude,
                    s.service_id, 
					s.service_icon, 
					sa.staff_get_id, 
					s.service_name, 
					s.category_get_id, 
					s.service_cost, 
					s.service_duration,
					s.service_capacity_max,
					s.service_tax, 
					s.padding_before, 
					s.recurring_status, 
					s.recurring_type, 
					s.padding_after, 
					s.recurring_payment 
				FROM 
					%1\$s s 
				LEFT JOIN 
					%2\$s la ON s.service_id = la.service_get_id 
				LEFT JOIN 
					%3\$s sa ON s.service_id = sa.service_get_id 
				LEFT JOIN 
					%4\$s l ON l.location_id = la.location_get_id 
 
				WHERE 
					s.status = 1 and s.category_get_id = $category_id 
				GROUP BY 
					s.service_id",
							$service,
							$location_assign,
							$staff_assign,
							$location
						)
					);
				}
			}
			if (get_option('bookingor_settings_woocommcerce_payment_active') === 'on') {
				self::$f_service_all = [];

				$wc_products = WooCommercePaymentService::get_all_wooCommerce_products();
				$results = self::$bookingorDb->get_results(
					$wpdb->prepare("SELECT DISTINCT 
						s.service_id, 
						la.service_get_id AS service_get_id_location, 
						s.service_description,
                        s.service_duration_type, 
 s.service_duration_count,
						la.location_get_id, 
						l.location_address, 
						l.location_city, 
						l.location_country, 
						l.location_latitude, 
						l.location_longitude, 
						s.service_icon, 
						sa.staff_get_id, 
						s.service_name, 
						s.category_get_id, 
						c.category_name, 
						s.service_cost,
						s.service_capacity_max, 
						s.wc_id, 
						s.service_duration, 
						GROUP_CONCAT(DISTINCT la.location_get_id) AS location_get_id, 
						s.service_tax, 
						s.padding_before, 
						s.recurring_status, 
						s.recurring_type, 
						s.padding_after, 
						s.recurring_payment 
					FROM 
						{$wpdb->prefix}bookingor_services s 
					LEFT JOIN 
					  {$wpdb->prefix}bookingor_location_assign_service la ON s.service_id = la.service_get_id 
					LEFT JOIN 
						{$wpdb->prefix}bookingor_staff_assign_service sa ON s.service_id = sa.service_get_id 
					LEFT JOIN 
						{$wpdb->prefix}bookingor_location l ON l.location_id = la.location_get_id 
 
					LEFT JOIN 
						{$wpdb->prefix}bookingor_categories c ON s.category_get_id = c.category_id 
					WHERE 
						s.status = 1 
						And s.wc_id > 0
					GROUP BY
						s.wc_id
				")
				);

				self::$f_service_all[] = array_merge((array) $results, $wc_products);
				$flattenedData = array_map(function ($item) {
					return (array) $item;
				}, self::$f_service_all[0]);
				$mergedData = [];

				foreach ($flattenedData as $item) {
					if (isset($item['wc_id'])) {
						foreach ($flattenedData as $product) {
							if (isset($product['id']) && $item['wc_id'] == $product['id']) {
								$mergedData[] = array_merge($item, $product);
							}
						}
					}
				}

				return $mergedData;
			}
			if (!empty($location_assign)) {
				return self::$f_service_all = self::$bookingorDb->get_results(
					$wpdb->prepare(
						'SELECT DISTINCT 
					  s.service_id, 
					  la.location_get_id, 
					  s.service_description, 
                      s.service_duration_type, 
 s.service_duration_count,
					  la.location_get_id, 
					  l.location_address, 
					  l.location_city, 
					  l.location_country, 
					  l.location_latitude, 
					  l.location_longitude, 
					  s.service_icon, 
					  sa.staff_get_id, 
					  s.service_name, 
					  s.category_get_id, 
					  c.category_name, 
					  s.service_cost, 
					  s.service_duration,
					  s.service_capacity_max,
					  GROUP_CONCAT(DISTINCT la.location_get_id) AS location_get_id, 
					  s.service_tax, 
					  s.padding_before, 
					  s.recurring_status, 
					  s.recurring_type, 
					  s.padding_after, 
					  s.recurring_payment 
					FROM 
					  %1$s s 
					LEFT JOIN 
					  %2$s la ON s.service_id = la.service_get_id 
					LEFT JOIN 
					  %3$s sa ON s.service_id = sa.service_get_id 
					LEFT JOIN 
					  %4$s l ON l.location_id = la.location_get_id 
 
					LEFT JOIN 
					  %5$s c ON s.category_get_id = c.category_id 
					WHERE 
					  s.status = 1 
					GROUP BY 
					  s.service_id',
						$service,
						$location_assign,
						$staff_assign,
						$location,

						$category
					)
				);
			} else {
				return self::$f_service_all = self::$bookingorDb->get_results(
					$wpdb->prepare(
						'SELECT 
							  s.service_id, 
							  s.service_icon, 
							  s.service_description, 
                              s.service_duration_type, 
							  s.service_duration_cont,
							  s.service_name, 
							  s.category_get_id, 
							  s.service_cost, 
							  s.service_duration,
							  s.service_capacity_max, 
							  la.location_get_id, 
							  s.service_tax, 
							  s.padding_before, 
							  s.recurring_status, 
							  s.recurring_type, 
							  s.padding_after 
							FROM 
							  %1$s s 
							LEFT JOIN 
							  %2$s la ON s.service_id = la.service_get_id 
							WHERE 
							  s.status = 1',
						$service,
						$location_assign
					)
				);
			}
		}
		$serviceAll = [];
		foreach (self::$f_service_all as $key => $value) {
			$serviceAll[] = [
				'service_id' => $value->service_id,
				'service_name' => $value->service_name,
				'service_icon' => $value->service_icon,
				'service_duration' => $value->service_duration,
				'service_tax' => $value->service_tax,
			];
		}
	}

	/**
	 * Get all services to display on frontend with their details.
	 *
	 * @return void
	 */
	public static function bp_front_services_data()
	{
		global $wpdb;
		self::$bookingorDb = $wpdb;
		$service = $wpdb->prefix . self::$dp_prefix . 'services';
		$default_img = plugin_dir_url(dirname(dirname(__FILE__))) . 'assets/images/serv-image.jpg';
		$service_get_id = isset($_REQUEST['service_get_id']) ? intval($_REQUEST['service_get_id']) : 0;
		if (isset($_POST['nonce'])) {
			if (!wp_verify_nonce(sanitize_text_field(wp_unslash($_POST['nonce'])), 'bookingor_ajax_nonce')) {
				$response = [
					'status' => htmlspecialchars('error'),
					'message' => htmlspecialchars('Nonce verification failed.')
				];
				echo wp_json_encode($response);
			}
		}

		self::$f_service_all = self::$bookingorDb->get_results(
			$wpdb->prepare(
				"SELECT 
			  service_id, 
			  service_icon, 
			  service_name, 
			  category_get_id, 
			  service_cost,
			  service_duration_type,
			  service_duration_count, 
			  service_deposit, 
			  service_deposit_type, 
			  service_duration, 
			  service_tax, 
			  service_capacity_min, 
			  service_capacity_max, 
			  padding_before, 
			  recurring_status, 
			  recurring_type, 
			  wc_id, 
			  recurring_payment, 
			  padding_after 
			FROM 
			  {$wpdb->prefix}bookingor_services 
			WHERE 
			  status = 1 
			  AND service_id = %d",
				$service_get_id
			)
		);
		$serviceAll = [];
		foreach (self::$f_service_all as $key => $value) {
			$icon = $value->service_icon ? wp_get_attachment_url($value->service_icon) : $default_img;
			$serviceAll[] = [
				'category_get_id' => $value->category_get_id,
				'service_id' => $value->service_id,
				'service_name' => $value->service_name,
				'service_icon' => $icon,
				'service_duration_type' => $value->service_duration_type,
				'service_duration_count' => $value->service_duration_count,
				'service_cost' => $value->service_cost,
				'service_deposit' => $value->service_deposit,
				'service_deposit_type' => $value->service_deposit_type,
				'service_duration' => $value->service_duration,
				'service_tax' => $value->service_tax,
				'recurring_status' => $value->recurring_status,
				'recurring_type' => $value->recurring_type,
				'recurring_payment' => $value->recurring_payment,
				'wc_id' => $value->wc_id,
				'service_capacity_min' => $value->service_capacity_min,
				'service_capacity_max' => $value->service_capacity_max,
			];
		}
		echo wp_json_encode($serviceAll);
		die();
	}

	/**
	 * bp_front_services_data_by_id_shortcode.
	 *
	 * Get services data by id to use in shortcode
	 *
	 * @since 1.0.0
	 */
	public static function bp_front_services_data_by_id_shortcode()
	{
		global $wpdb;
		self::$bookingorDb = $wpdb;
		$service = $wpdb->prefix . self::$dp_prefix . 'services';
		$default_img = plugin_dir_url(dirname(dirname(__FILE__))) . 'assets/images/serv-image.jpg';
		$service_get_id = isset($_REQUEST['shortcode_service_id']) ? intval($_REQUEST['shortcode_service_id']) : 0;
		if (isset($_POST['nonce'])) {
			if (!wp_verify_nonce(sanitize_text_field(wp_unslash($_POST['nonce'])), 'bookingor_ajax_nonce')) {
				$response = [
					'status' => htmlspecialchars('error'),
					'message' => htmlspecialchars('Nonce verification failed.')
				];
				echo wp_json_encode($response);
			}
		}

		self::$f_service_all = self::$bookingorDb->get_results(
			$wpdb->prepare(
				"SELECT 
			  service_id, 
			  service_icon, 
			  service_name, 
			  category_get_id, 
			  service_cost, 
			  service_deposit, 
			  service_deposit_type, 
			  service_duration, 
			  service_tax, 
			  service_capacity_min, 
			  service_capacity_max, 
			  padding_before, 
			  recurring_status, 
			  recurring_type, 
			  recurring_payment, 
			  padding_after 
			FROM 
			  {$wpdb->prefix}bookingor_services 
			WHERE 
			  status = 1 
			  AND service_id = %d",
				$service_get_id
			)
		);
		$serviceAll = [];
		foreach (self::$f_service_all as $key => $value) {
			$icon = $value->service_icon ? wp_get_attachment_url($value->service_icon) : $default_img;
			$serviceAll[] = [
				'category_get_id' => $value->category_get_id,
				'service_id' => $value->service_id,
				'service_name' => $value->service_name,
				'service_icon' => $icon,
				'service_cost' => $value->service_cost,
				'service_deposit' => $value->service_deposit,
				'service_deposit_type' => $value->service_deposit_type,
				'service_duration' => $value->service_duration,
				'service_tax' => $value->service_tax,
				'recurring_status' => $value->recurring_status,
				'recurring_type' => $value->recurring_type,
				'recurring_payment' => $value->recurring_payment,
				'service_capacity_min' => $value->service_capacity_min,
				'service_capacity_max' => $value->service_capacity_max,
			];
		}
		echo wp_json_encode($serviceAll);
		die();
	}

	/**
	 * Check if there is any service with status 1 and empty wc_id
	 *
	 * @return bool
	 */
	public static function check_wc_id_empty_db()
	{
		global $wpdb;
		$service = $wpdb->prefix . self::$dp_prefix . 'services';

		// First check if there is ANY service with a valid wc_id
		$connected_count = $wpdb->get_var("SELECT COUNT(*) FROM $service WHERE status = 1 AND wc_id > 0");

		if ($connected_count > 0) {
			return false;
		}

		// If no connected services found, check if there are any unconnected ones
		$result = $wpdb->get_var("SELECT COUNT(*) FROM $service WHERE status = 1 AND (wc_id IS NULL OR wc_id = 0 OR wc_id = '')");
		return $result > 0;
	}
}
