<?php

namespace BwWinner;

function get_winners_all_sites ( $args ) {
	$options = \BwWinner\Options\get_options();
	global $wpdb;

	$sites = get_sites();

	$queries = array();

	foreach ( $sites as $site ) {
		$prefix = $wpdb->base_prefix . ($site->blog_id == 1 ? '' : "{$site->blog_id}_");

		$details = get_blog_details( $site->blog_id );

		$query = $wpdb->prepare(
			"SELECT
				entry.id AS entry_id,
				entry.year AS entry_year,
				entry.score AS entry_score,
				product.id AS product_id,
				product.name AS product_name,
				product.url AS product_url,
				product.type AS product_type,
				product.price AS product_price,
				product.country AS product_country,
				brand.id AS brand_id,
				brand.name AS brand_name,
				brand.url AS brand_url,
				company.id AS company_id,
				company.name AS company_name,
				company.url AS company_url,
				post_product.ID AS post_product_id,
				post_product.post_title AS post_product_title,
				post_product.post_status AS post_product_status,
				post_brand.ID AS post_brand_id,
				post_brand.post_title AS post_brand_title,
				post_brand.post_status AS post_brand_status,
				post_company.ID AS post_company_id,
				post_company.post_title AS post_company_title,
				post_company.post_status AS post_company_status,
				%d AS blog_id,
				%s AS domain,
				%s AS blogname
			FROM {$prefix}bw_winners_entries AS entry
			INNER JOIN  {$prefix}bw_winners_products AS product ON entry.product_id = product.id
			INNER JOIN  {$prefix}bw_winners_brands AS brand ON product.brand_id = brand.id
			INNER JOIN  {$prefix}bw_winners_companies AS company ON brand.company_id = company.id
			INNER JOIN  {$prefix}posts AS post_product ON product.id = post_product.ID
			INNER JOIN  {$prefix}posts AS post_brand ON brand.id = post_brand.ID
			INNER JOIN  {$prefix}posts AS post_company ON company.id = post_company.ID",
			array(
				$site->blog_id,
				$site->domain,
				$details->blogname
			)
		);

		$count_query = "SELECT COUNT(*)
			FROM {$prefix}bw_winners_entries AS entry
			INNER JOIN  {$prefix}bw_winners_products AS product ON entry.product_id = product.id
			INNER JOIN  {$prefix}bw_winners_brands AS brand ON product.brand_id = brand.id
			INNER JOIN  {$prefix}bw_winners_companies AS company ON brand.company_id = company.id
			INNER JOIN  {$prefix}posts AS post_product ON product.id = post_product.ID
			INNER JOIN  {$prefix}posts AS post_brand ON brand.id = post_brand.ID
			INNER JOIN  {$prefix}posts AS post_company ON company.id = post_company.ID";

		$where = array();

		if ( ! empty( $args['s'] ) ) {
			$like = '%' . $wpdb->esc_like( $args['s'] ) . '%';
			$where_or = array();
			// post title
			$where_or[] = $wpdb->prepare(
				"post_product.post_title LIKE %s",
				$like
			);
			$where_or[] = $wpdb->prepare(
				"post_brand.post_title LIKE %s",
				$like
			);
			$where_or[] = $wpdb->prepare(
				"post_company.post_title LIKE %s",
				$like
			);
			// post excerpt
			$where_or[] = $wpdb->prepare(
				"post_product.post_excerpt LIKE %s",
				$like
			);
			$where_or[] = $wpdb->prepare(
				"post_brand.post_excerpt LIKE %s",
				$like
			);
			$where_or[] = $wpdb->prepare(
				"post_company.post_excerpt LIKE %s",
				$like
			);
			// entity names
			$where_or[] = $wpdb->prepare(
				"product.name LIKE %s",
				$like
			);
			$where_or[] = $wpdb->prepare(
				"brand.name LIKE %s",
				$like
			);
			$where_or[] = $wpdb->prepare(
				"company.name LIKE %s",
				$like
			);
			// product type
			$where_or[] = $wpdb->prepare(
				"product.type LIKE %s",
				$like
			);
			// product price
			$where_or[] = $wpdb->prepare(
				"product.price LIKE %s",
				$like
			);
			// product country
			$where_or[] = $wpdb->prepare(
				"product.country LIKE %s",
				$like
			);
			// entry year
			$year = intval( $args['s'] );
			if ( $year ) {
				$where_or[] = $wpdb->prepare(
					"entry.year = %d",
					intval( $args['s'] )
				);
			}
			// product category
			$where_or[] = $wpdb->prepare(
				"post_product.ID IN (
					SELECT object_id
					FROM {$wpdb->term_relationships} as rel
					INNER JOIN {$wpdb->term_taxonomy} as tax ON rel.term_taxonomy_id = tax.term_taxonomy_id
					INNER JOIN {$wpdb->terms} as term  ON tax.term_id = term.term_id
					WHERE tax.taxonomy = 'product-category' AND term.name LIKE %s
				)",
				$like
			);
			// post content
			$where_or[] = $wpdb->prepare(
				"post_product.post_content LIKE %s",
				$like
			);
			$where_or[] = $wpdb->prepare(
				"post_brand.post_content LIKE %s",
				$like
			);
			$where_or[] = $wpdb->prepare(
				"post_company.post_content LIKE %s",
				$like
			);
			$where[] = '( ' . implode( " OR\n", $where_or ) . ' )';
		}


		// Year
		if ( ! empty( $args['filters']['year'] ) ) {
			$where_or = array();
			foreach ( $args['filters']['year'] as $year ) {
				$where_or[] = $wpdb->prepare(
					"entry.year = %d",
					$year
				);
			}
			$where[] = '( ' . implode( " OR\n", $where_or ) . ' )';
		}
		// Award name (double gold, gold, silver, bronze)
		if ( ! empty( $args['filters']['award'] ) ) {
			$score_as = $options['displayScoreAs'];
			$min_max = array(
				'doubleGold' => array($score_as['doubleGold'], 100),
				'gold' => array($score_as['gold'], $score_as['doubleGold'] - 1),
				'silver' => array($score_as['silver'], $score_as['gold'] - 1),
				'bronze' => array($score_as['bronze'], $score_as['silver'] - 1)
			);
			$where_or = array();
			foreach ( $args['filters']['award'] as $award ) {
				$where_or[] = $wpdb->prepare(
					"%d <= entry.score AND entry.score <= %d",
					$min_max[$award][0],
					$min_max[$award][1]
				);
			}
			$where[] = '( ' . implode( " OR\n", $where_or ) . ' )';
		}
		// Company name
		if ( ! empty( $args['filters']['company_name'] ) ) {
			$where_or = array();
			foreach ( $args['filters']['company_name'] as $name ) {
				$where_or[] = $wpdb->prepare(
					"company.name = %s",
					$name
				);
			}
			$where[] = '( ' . implode( " OR\n", $where_or ) . ' )';
		}
		// Brand name
		if ( ! empty( $args['filters']['brand_name'] ) ) {
			$where_or = array();
			foreach ( $args['filters']['brand_name'] as $name ) {
				$where_or[] = $wpdb->prepare(
					"brand.name = %s",
					$name
				);
			}
			$where[] = '( ' . implode( " OR\n", $where_or ) . ' )';
		}
		// Product name
		if ( ! empty( $args['filters']['product_name'] ) ) {
			$where_or = array();
			foreach ( $args['filters']['product_name'] as $name ) {
				$where_or[] = $wpdb->prepare(
					"product.name = %s",
					$name
				);
			}
			$where[] = '( ' . implode( " OR\n", $where_or ) . ' )';
		}
		// Product Category
		if ( ! empty( $args['filters']['product_category'] ) ) {
			$where_or = array();
			foreach ( $args['filters']['product_category'] as $name ) {
				$where_or[] = $wpdb->prepare(
					"post_product.ID IN (
						SELECT object_id
						FROM {$wpdb->term_relationships} as rel
						INNER JOIN {$wpdb->term_taxonomy} as tax ON rel.term_taxonomy_id = tax.term_taxonomy_id
						INNER JOIN {$wpdb->terms} as term  ON tax.term_id = term.term_id
						WHERE tax.taxonomy = 'product-category' AND term.name = %s
					)",
					$name
				);
			}
			$where[] = '( ' . implode( " OR\n", $where_or ) . ' )';
		}
		// Tag
		if ( ! empty( $args['filters']['tag'] ) ) {
			$where_or = array();
			foreach ( $args['filters']['tag'] as $name ) {
				$where_or[] = $wpdb->prepare(
					"post_company.ID IN (
						SELECT object_id
						FROM {$wpdb->term_relationships} as rel
						INNER JOIN {$wpdb->term_taxonomy} as tax ON rel.term_taxonomy_id = tax.term_taxonomy_id
						INNER JOIN {$wpdb->terms} as term  ON tax.term_id = term.term_id
						WHERE tax.taxonomy = 'post_tag' AND term.name = %s
					)",
					$name
				);
				$where_or[] = $wpdb->prepare(
					"post_brand.ID IN (
						SELECT object_id
						FROM {$wpdb->term_relationships} as rel
						INNER JOIN {$wpdb->term_taxonomy} as tax ON rel.term_taxonomy_id = tax.term_taxonomy_id
						INNER JOIN {$wpdb->terms} as term  ON tax.term_id = term.term_id
						WHERE tax.taxonomy = 'post_tag' AND term.name = %s
					)",
					$name
				);
				$where_or[] = $wpdb->prepare(
					"post_product.ID IN (
						SELECT object_id
						FROM {$wpdb->term_relationships} as rel
						INNER JOIN {$wpdb->term_taxonomy} as tax ON rel.term_taxonomy_id = tax.term_taxonomy_id
						INNER JOIN {$wpdb->terms} as term  ON tax.term_id = term.term_id
						WHERE tax.taxonomy = 'post_tag' AND term.name = %s
					)",
					$name
				);
			}
			$where[] = '( ' . implode( " OR\n", $where_or ) . ' )';
		}
		// Price Category
		if ( ! empty( $args['filters']['price_category'] ) ) {
			$where_or = array();
			foreach ( $args['filters']['price_category'] as $price ) {
				$where_or[] = $wpdb->prepare(
					"product.price = %s",
					$price
				);
			}
			$where[] = '( ' . implode( " OR\n", $where_or ) . ' )';
		}

		if ( count( $where ) ) {
			$query .= ' WHERE ' . implode( " AND\n", $where );
			$count_query .= ' WHERE ' . implode( " AND\n", $where );
		}

		$queries[] = $query;
	}

	$query = implode( " UNION\n", $queries ) . ' ';
	

	// Order
	$order_by_allowed = array(
		'company_name' => 'company_name',
		'brand_name' => 'brand_name',
		'product_name' => 'product_name',
		'product_type' => 'product_type',
		'product_price' => 'product_price',
		'product_country' => 'product_country',
		'entry_year' => 'entry_year',
		'entry_score' => 'entry_score',
	);
	$order_allowed = array(
		'ASC' => 'ASC',
		'DESC' => 'DESC'
	);
	if ( isset( $order_by_allowed[$args['sort_field']] ) ) {
		$order_by = $order_by_allowed[$args['sort_field']];
		if ( isset( $order_allowed[$args['sort_dir']] ) ) {
			$order = $order_allowed[$args['sort_dir']];
		} else {
			$order = 'ASC';
		}
		$query .= "\n\t\tORDER BY {$order_by} {$order}";
	}

	// Limit / Offset
	$page = isset( $args['page'] ) ? max( intval( $args['page'] ), 1 ) : 1;
	$per_page = isset( $args['per_page'] ) ? intval( $args['per_page'] ) : 20;

	$total_pages = 1;
	
	if ( $per_page > 0 ) {
		$limit = intval( $per_page );
		$offset = ( $page - 1 ) * $limit;
		$query .= $wpdb->prepare(
			" LIMIT %d, %d",
			$offset,
			$limit
		);
		$count = intval($wpdb->get_var($count_query));
		$total_pages = ceil( $count / $limit );
	} else if ( $per_page === 0 ) {
		return array(
			'entries' => array(),
			'page' => $page,
			'totalPages' => 0
		);
	} else if ( $page > 1 ) {
		return array(
			'entries' => array(),
			'page' => $page,
			'totalPages' => 1
		);
	}

	$results = $wpdb->get_results( $query, ARRAY_A );

	if ( $wpdb->last_error ) {
		return new \WP_Error( 'winners_error', $wpdb->last_error, array('status' => 500) );
	}

	foreach ( $results as $index => $winner ) {
		$results[$index]['product_category'] = wp_get_post_terms($winner['post_product_id'], 'product-category');
		$results[$index]['post_product_url'] = get_permalink( $results[$index]['post_product_id'] );
	}

	return array(
		'entries' => $results,
		'page' => $page,
		'totalPages' => $total_pages,
		'query' => $query
	);
}