<?php

namespace BwWinner;

function get_winners ( $args ) {
	$options = \BwWinner\Options\get_options();
	global $wpdb;
	$query = "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
		FROM {$wpdb->prefix}bw_winners_entries AS entry
		INNER JOIN  {$wpdb->prefix}bw_winners_products AS product ON entry.product_id = product.id
		INNER JOIN  {$wpdb->prefix}bw_winners_brands AS brand ON product.brand_id = brand.id
		INNER JOIN  {$wpdb->prefix}bw_winners_companies AS company ON brand.company_id = company.id
		INNER JOIN  {$wpdb->posts} AS post_product ON product.id = post_product.ID
		INNER JOIN  {$wpdb->posts} AS post_brand ON brand.id = post_brand.ID
		INNER JOIN  {$wpdb->posts} AS post_company ON company.id = post_company.ID";

	$count_query = "SELECT COUNT(*)
		FROM {$wpdb->prefix}bw_winners_entries AS entry
		INNER JOIN  {$wpdb->prefix}bw_winners_products AS product ON entry.product_id = product.id
		INNER JOIN  {$wpdb->prefix}bw_winners_brands AS brand ON product.brand_id = brand.id
		INNER JOIN  {$wpdb->prefix}bw_winners_companies AS company ON brand.company_id = company.id
		INNER JOIN  {$wpdb->posts} AS post_product ON product.id = post_product.ID
		INNER JOIN  {$wpdb->posts} AS post_brand ON brand.id = post_brand.ID
		INNER JOIN  {$wpdb->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 );
	}

	// 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( $args['sort_field'] ) && 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
	);
}