<?php
namespace BwWinnersNetwork\Query;

if ( ! class_exists( __NAMESPACE__ . 'Brands_Query' ) ) {

	class Brands_Query extends Abstract_Query {

		protected function build_select( $args ) {

			$this->select = [

				'brand.id AS brand_id',
				'brand.name AS brand_name',

				'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_brands AS brand
				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_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( 'brand.name LIKE %s', $like );

				$this->add_where( '( ' . implode( " OR\n", $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 ) . ' )' );
			}

			/* --------------------------------------------------
			 * YEAR
			 * -------------------------------------------------- */
			if ( ! empty( $filters['year'] ) ) {
				$where_or = [];

				foreach ( $filters['year'] as $year ) {

					// Award Year
					$where_or[] = $this->wpdb->prepare(
						"brand.id IN (
							SELECT brand_id
							FROM {$prefix}bw_winners_v2_brand_awards
							WHERE year = %d
							GROUP BY brand_id
						)",
						$year
					);
				}

				$this->add_where( '( ' . implode( ' OR ', $where_or ) . ' )' );
			}

			/* --------------------------------------------------
			 * COMPETITION NAME
			 * -------------------------------------------------- */
			if ( ! empty( $filters['competition_name'] ) ) {
				$where_or = [];

				foreach ( $filters['competition_name'] as $name ) {
					
					// Competition Award
					$where_or[] = $this->wpdb->prepare(
						"brand.id IN (
							SELECT award.brand_id
							FROM {$prefix}bw_winners_v2_brand_awards award
							INNER JOIN {$prefix}bw_winners_v2_competitions competition ON award.competition_id = competition.id
							WHERE competition.name = %s
							GROUP BY brand_id
						)",
						$name
					);
				}

				$this->add_where( '( ' . implode( ' OR ', $where_or ) . ' )' );
			}

			/* --------------------------------------------------
			 * COMPETITION TYPE
			 * -------------------------------------------------- */
			if ( ! empty( $filters['competition_type'] ) ) {
				$where_or = [];

				foreach ( $filters['competition_type'] as $type ) {
					
					// Competition Award
					$where_or[] = $this->wpdb->prepare(
						"brand.id IN (
							SELECT award.brand_id
							FROM {$prefix}bw_winners_v2_brand_awards award
							INNER JOIN {$prefix}bw_winners_v2_competitions competition ON award.competition_id = competition.id
							WHERE competition.type = %s
							GROUP BY brand_id
						)",
						$type
					);
				}

				$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
					);
				}

				$this->add_where( '( ' . implode( ' OR ', $where_or ) . ' )' );
			}
		}

		protected function apply_ordering( $args ) {

			$allowed = [
				'brand_name'
			];

			$order_by = in_array( $args['order_by'] ?? '', $allowed, true )
				? $args['order_by']
				: 'brand_name';

			$order = in_array( $args['order'] ?? '', [ 'ASC', 'DESC' ], true )
				? $args['order']
				: 'ASC';

			$this->order_by = "{$order_by} {$order}";
		}
	}
}