<?php
namespace BwWinnersNetwork\Query;

if ( ! class_exists( __NAMESPACE__ . 'Entries_Query' ) ) {

	class Entries_Query extends Abstract_Query {

		protected function build_select( $args ) {

			$this->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.categories AS product_categories',
				'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',

				'competition.id AS competition_id',
				'competition.name AS competition_name',
				'competition.type AS competition_type',

				'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',
			];
		}

		protected function build_from( $args ) {

			$prefix = $this->wpdb->get_blog_prefix( $this->site_id );

			$this->from = "
				{$prefix}bw_winners_v2_entries AS entry
				INNER JOIN {$prefix}bw_winners_v2_products AS product ON entry.product_id = product.id
				INNER JOIN {$prefix}bw_winners_v2_brands AS brand ON product.brand_id = brand.id
				INNER JOIN {$prefix}bw_winners_v2_competitions AS competition ON entry.competition_id = competition.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
			";
		}

		protected function apply_filters( $args ) {

			$filters = $args['filters'] ?? [];
			$search  = $args['s'] ?? null;

			$prefix  = $this->wpdb->get_blog_prefix( $this->site_id );
			$options = get_blog_option( $this->site_id, 'bw_winners_v2_options_site' );

			/* --------------------------------------------------
			 * SEARCH
			 * -------------------------------------------------- */
			if ( $search ) {

				$like = '%' . $this->wpdb->esc_like( $search ) . '%';
				$where_or = [];

				$where_or[] = $this->wpdb->prepare( 'post_product.post_title LIKE %s', $like );
				$where_or[] = $this->wpdb->prepare( 'post_product.post_excerpt LIKE %s', $like );
				$where_or[] = $this->wpdb->prepare( 'post_product.post_content LIKE %s', $like );

				$where_or[] = $this->wpdb->prepare( 'post_brand.post_title LIKE %s', $like );
				$where_or[] = $this->wpdb->prepare( 'post_brand.post_excerpt LIKE %s', $like );
				$where_or[] = $this->wpdb->prepare( 'post_brand.post_content LIKE %s', $like );

				$where_or[] = $this->wpdb->prepare( 'product.name LIKE %s', $like );
				$where_or[] = $this->wpdb->prepare( 'brand.name LIKE %s', $like );
				$where_or[] = $this->wpdb->prepare( 'product.type LIKE %s', $like );
				$where_or[] = $this->wpdb->prepare( 'product.price LIKE %s', $like );
				$where_or[] = $this->wpdb->prepare( 'product.country LIKE %s', $like );

				$year = intval( $search );
				if ( $year ) {
					$where_or[] = $this->wpdb->prepare( 'entry.year = %d', $year );
				}

				// Product category name (LIKE)
				$where_or[] = $this->wpdb->prepare(
					"post_product.ID IN (
						SELECT object_id
						FROM {$prefix}term_relationships rel
						INNER JOIN {$prefix}term_taxonomy tax ON rel.term_taxonomy_id = tax.term_taxonomy_id
						INNER JOIN {$prefix}terms term ON tax.term_id = term.term_id
						WHERE tax.taxonomy = 'product-category' AND term.name LIKE %s
					)",
					$like
				);

				$this->add_where( '( ' . implode( " OR\n", $where_or ) . ' )' );
			}

			/* --------------------------------------------------
			 * YEAR FILTER
			 * -------------------------------------------------- */
			if ( ! empty( $filters['year'] ) ) {
				$where_or = [];
				foreach ( $filters['year'] as $year ) {
					$where_or[] = $this->wpdb->prepare( 'entry.year = %d', $year );
				}
				$this->add_where( '( ' . implode( ' OR ', $where_or ) . ' )' );
			}

			/* --------------------------------------------------
			 * AWARD FILTER (score ranges)
			 * -------------------------------------------------- */
			if ( ! empty( $filters['award'] ) ) {

				$display_score_as = $options['display_score_as'];

				$min_max = [
					'double_gold' => [ $display_score_as['double_gold'], 100 ],
					'gold'        => [ $display_score_as['gold'], $display_score_as['double_gold'] - 1 ],
					'silver'      => [ $display_score_as['silver'], $display_score_as['gold'] - 1 ],
					'bronze'      => [ $display_score_as['bronze'], $display_score_as['silver'] - 1 ],
				];

				$where_or = [];

				foreach ( $filters['award'] as $award ) {
					if ( isset( $min_max[ $award ] ) ) {
						$where_or[] = $this->wpdb->prepare(
							'%d <= entry.score AND entry.score <= %d',
							$min_max[ $award ][0],
							$min_max[ $award ][1]
						);
					}
				}

				if ( $where_or ) {
					$this->add_where( '( ' . implode( ' OR ', $where_or ) . ' )' );
				}
			}

			/* --------------------------------------------------
			 * COMPETITION NAME
			 * -------------------------------------------------- */
			if ( ! empty( $filters['competition_name'] ) ) {
				$where_or = [];
				foreach ( $filters['competition_name'] as $name ) {
					$where_or[] = $this->wpdb->prepare( 'competition.name = %s', $name );
				}
				$this->add_where( '( ' . implode( ' OR ', $where_or ) . ' )' );
			}

			/* --------------------------------------------------
			 * BRAND
			 * -------------------------------------------------- */
			if ( ! empty( $filters['brand_id'] ) ) {
				$this->add_where(
					$this->wpdb->prepare( 'brand.id = %d', $filters['brand_id'] )
				);
			}

			if ( ! empty( $filters['brand_name'] ) ) {
				$where_or = [];
				foreach ( $filters['brand_name'] as $name ) {
					$where_or[] = $this->wpdb->prepare( 'brand.name = %s', $name );
				}
				$this->add_where( '( ' . implode( ' OR ', $where_or ) . ' )' );
			}

			/* --------------------------------------------------
			 * PRODUCT
			 * -------------------------------------------------- */
			if ( ! empty( $filters['product_id'] ) ) {
				$this->add_where(
					$this->wpdb->prepare( 'product.id = %d', $filters['product_id'] )
				);
			}

			if ( ! empty( $filters['product_name'] ) ) {
				$where_or = [];
				foreach ( $filters['product_name'] as $name ) {
					$where_or[] = $this->wpdb->prepare( 'product.name = %s', $name );
				}
				$this->add_where( '( ' . implode( ' OR ', $where_or ) . ' )' );
			}

			/* --------------------------------------------------
			 * PRODUCT CATEGORY (exact)
			 * -------------------------------------------------- */
			if ( ! empty( $filters['product_category'] ) ) {
				$where_or = [];

				foreach ( $filters['product_category'] as $name ) {
					$where_or[] = $this->wpdb->prepare(
						"post_product.ID IN (
							SELECT object_id
							FROM {$prefix}term_relationships rel
							INNER JOIN {$prefix}term_taxonomy tax ON rel.term_taxonomy_id = tax.term_taxonomy_id
							INNER JOIN {$prefix}terms term ON tax.term_id = term.term_id
							WHERE tax.taxonomy = 'product-category'
							AND term.name = %s
						)",
						$name
					);
				}

				$this->add_where( '( ' . implode( ' OR ', $where_or ) . ' )' );
			}

			/* --------------------------------------------------
			 * POST TAGS (brand OR product)
			 * -------------------------------------------------- */
			if ( ! empty( $filters['post_tag'] ) ) {

				$where_or = [];

				foreach ( $filters['post_tag'] as $name ) {

					$where_or[] = $this->wpdb->prepare(
						"post_brand.ID IN (
							SELECT object_id
							FROM {$prefix}term_relationships rel
							INNER JOIN {$prefix}term_taxonomy tax ON rel.term_taxonomy_id = tax.term_taxonomy_id
							INNER JOIN {$prefix}terms term ON tax.term_id = term.term_id
							WHERE tax.taxonomy = 'post_tag'
							AND term.name = %s
						)",
						$name
					);

					$where_or[] = $this->wpdb->prepare(
						"post_product.ID IN (
							SELECT object_id
							FROM {$prefix}term_relationships rel
							INNER JOIN {$prefix}term_taxonomy tax ON rel.term_taxonomy_id = tax.term_taxonomy_id
							INNER JOIN {$prefix}terms term ON tax.term_id = term.term_id
							WHERE tax.taxonomy = 'post_tag'
							AND term.name = %s
						)",
						$name
					);
				}

				$this->add_where( '( ' . implode( ' OR ', $where_or ) . ' )' );
			}

			/* --------------------------------------------------
			 * PRICE CATEGORY
			 * -------------------------------------------------- */
			if ( ! empty( $filters['price_category'] ) ) {
				$where_or = [];
				foreach ( $filters['price_category'] as $price ) {
					$where_or[] = $this->wpdb->prepare( 'product.price = %s', $price );
				}
				$this->add_where( '( ' . implode( ' OR ', $where_or ) . ' )' );
			}
		}

		protected function apply_ordering( $args ) {

			$allowed = [
				'competition_name',
				'brand_name',
				'product_name',
				'product_type',
				'product_price',
				'product_country',
				'entry_year',
				'entry_score',
			];

			$order_by = in_array( $args['order_by'] ?? '', $allowed, true )
				? $args['order_by']
				: 'entry_score';

			$order = in_array( $args['order'] ?? '', [ 'ASC', 'DESC' ], true )
				? $args['order']
				: 'DESC';

			$this->order_by = "{$order_by} {$order}";
		}
	}
}