<?php
namespace Indeed\Uap\Db;

class Products
{

    private $limit          = 30;
    private $offset         = 0;
    private $searchPhrase   = '';
    private $type           = '';
    private $affiliateId    = 0;
    private $category       = 0;
    private $orderBy        = '';
    private $wooPriceFormat = '';

    public function __construct(){}

    public function setSearchPhrase( $searchPhrase='' )
    {
        $this->searchPhrase = $searchPhrase;
        return $this;
    }

    public function setLimit( $limit=0 )
    {
        $this->limit = $limit;
        return $this;
    }

    public function setOffset( $offset=0 )
    {
        $this->offset = $offset;
        return $this;
    }

    public function setType( $type=0 )
    {
        $this->type = $type;
        return $this;
    }

    public function setAffiliateId( $affiliateId=0 )
    {
        $this->affiliateId = $affiliateId;
        return $this;
    }

    public function setProductCategory( $category=0 )
    {
        $this->category = $category;
        return $this;
    }

    public function setOrderBy( $orderBy='' )
    {
        $this->orderBy = $orderBy;
        return $this;
    }

    public function getResults( $search='', $limit=0, $offset=0 )
    {
        switch ( $this->type ){
            case 'woo':
              return $this->searchIntoWoo();
              break;
            case 'edd':
              return $this->searchIntoEdd();
              break;
            case 'ulp':
              return $this->searchIntoUlp();
              break;
        }
        return [];
    }

    public function getCount()
    {
        switch ( $this->type ){
            case 'woo':
              return $this->countsForWoo();
              break;
            case 'edd':
              return $this->countsForEdd();
              break;
            case 'ulp':
              return $this->countsForUlp();
              break;
        }

    }

    public function searchIntoWoo()
    {
        global $wpdb;
        $search = uap_sanitize_array( $this->searchPhrase );
        $query = "SELECT a.ID, a.post_title,
                          CAST(c.meta_value AS DECIMAL(10,2)) as price,
                          CAST(d.meta_value AS UNSIGNED) as total_sales,
                          c.meta_key as c_meta_key,
                          c.meta_value as c_meta_value
                          FROM {$wpdb->posts} a ";
        if ( $this->category ){
            $query .= " INNER JOIN {$wpdb->term_relationships} b ON a.ID=b.object_id  ";
        }
        $query .= " INNER JOIN {$wpdb->postmeta} c ON a.ID=c.post_id ";
        $query .= " INNER JOIN {$wpdb->postmeta} d ON a.ID=d.post_id ";
        $query .= " WHERE 1=1 ";
        if ( $this->category ){
            $query .= $wpdb->prepare( " AND b.term_taxonomy_id=%d ", $this->category );
        }
        if ( $search != '' ){
            $query .= " AND a.post_title LIKE '%$search%' ";
        }
        $query .= " AND a.post_type='product' AND a.post_status='publish' ";
        $query .= " AND (c.meta_key='_regular_price' OR c.meta_key='_price') ";
        $query .= " AND d.meta_key='total_sales' ";
        $query .= " GROUP BY a.ID ";
        if ( $this->orderBy ){
            switch ( $this->orderBy ){
                case 'popularity':
                  $query .= " ORDER BY total_sales DESC ";
                  break;
                case 'date':
                  $query .= " ORDER BY a.post_date DESC ";
                  break;
                case 'price':
                  $query .= " ORDER BY price ASC ";
                  break;
                case 'price-desc':
                  $query .= " ORDER BY price DESC ";
                  break;
            }
        }
        $query .= $wpdb->prepare( " LIMIT %d OFFSET %d;", $this->limit, $this->offset);

        $data = $wpdb->get_results( $query );
        if ( !$data ){
            return [];
        }
        $currency = get_option('woocommerce_currency');
        if ( function_exists( 'get_woocommerce_currency_symbol' ) ){
            $currency = get_woocommerce_currency_symbol( $currency );
        }
        $return = [];

        require_once UAP_PATH . 'public/Affiliate_Referral_Amount.class.php';
        $do_math = new \Affiliate_Referral_Amount( $this->affiliateId, 'woo');

        $excludeTax = get_option( 'uap_exclude_tax' );

        foreach ( $data as $productData){
            $prices = [];
            $regularPrices = [];
            $product = wc_get_product( $productData->ID );


            if ( $excludeTax ){
              $price = wc_get_price_excluding_tax( $product, [] );
            } else {
              $price = wc_get_price_including_tax( $product, [] );
            }

      			$sale_price = $product->get_sale_price();
      			$regular_price = $product->get_regular_price();

      			if(!empty($sale_price)) {
      				$price = $sale_price;
      				if($regular_price == $sale_price){
                 $regular_price = FALSE;
              }
      			}

            $priceHtml = $this->formatWooPrice( $price, $currency );
            $referralAmount = $do_math->get_result( $price, $productData->ID );// input price, product id
            $referralPrice = $this->formatWooPrice( $referralAmount, $currency );

            if ( $product instanceof \WC_Product_Variable ){
                  $variablePrices = $product->get_available_variations();
                  foreach ( $variablePrices as $variablePrice ){
                  		$variableProductObject = wc_get_product( $variablePrice['variation_id'] );
                      if ( $excludeTax ){
                        $prices[] = wc_get_price_excluding_tax( $variableProductObject, [] );
                      } else {
                        $prices[] = wc_get_price_including_tax( $variableProductObject, [] );
                      }
                      /*
                      $prices[] = $variablePrice['display_price'];
                      if ( isset( $variablePrice['display_regular_price'] ) ){
                          $regularPrices[] = $variablePrice['display_regular_price'];
                      }
                      */
                  }
                  // referral amount
                  $temporaryMinReferralAmount = $do_math->get_result( min( $prices ), $productData->ID );
                  $temporaryMaxReferralAmount = $do_math->get_result( max( $prices ), $productData->ID );
                  $referralPrice = $this->formatWooPrice( $temporaryMinReferralAmount, $currency );
                  $referralPrice .= ' - ';
                  $referralPrice .= $this->formatWooPrice( $temporaryMaxReferralAmount, $currency );

                  // base price
                  $priceHtml = $this->formatWooPrice( min( $prices ), $currency );
                  $priceHtml .= ' - ';
                  $priceHtml .= $this->formatWooPrice( max( $prices ), $currency );

                  // regular price
                  if ( !empty( $regularPrices ) ){
                      $regularPriceHtml = $this->formatWooPrice( min( $regularPrices ), $currency );
                      $regularPriceHtml .= ' - ';
                      $regularPriceHtml .= $this->formatWooPrice( max( $regularPrices ), $currency );
                  }
            }

            $return[$productData->ID] = [
                  'price'                   => $priceHtml,
                  'numeric_price'           => $price,
				          'regular_price'           => isset( $regularPriceHtml ) ? $regularPriceHtml : $regular_price,
                  'numeric_regular_price'   => $regular_price,
                  'label'                   => $productData->post_title,
                  'featureImage'            => get_the_post_thumbnail_url( $productData->ID ),
                  'id'                      => $productData->ID,
                  'product_type'            => 'woo',
                  'referral_amount'         => $referralPrice,
                  'permalink'               => get_permalink( $productData->ID ),
                  'categories'              => get_the_terms( $productData->ID, 'product_cat' ),
            ];
            if ( isset( $regularPriceHtml ) ){
                unset( $regularPriceHtml );
            }
        }
        return $return;
    }

    private function formatWooPrice( $price='', $currency='' )
    {
        if ($price === FALSE){
           return FALSE;
        }

		    if ( !$this->wooPriceFormat ){
            $this->wooPriceFormat = get_option( 'woocommerce_currency_pos' );
        }
        $string = $currency . $price;
        switch ( $this->wooPriceFormat ) {
          case 'left':
            $string = $currency . $price;
            break;
          case 'right':
            $string = $price . $currency;
            break;
          case 'left_space':
            $string = $currency . ' ' . $price;
            break;
          case 'right_space':
            $string = $price . ' ' . $currency;
            break;
        }
        return $string;
    }

    private function countsForWoo()
    {
        global $wpdb;
        $search = uap_sanitize_array( $this->searchPhrase );
        $query = "SELECT COUNT( a.ID ) FROM {$wpdb->posts} a ";
        if ( $this->category ){
            $query .= " INNER JOIN {$wpdb->term_relationships} b ON a.ID=b.object_id ";
        }
        $query .= " WHERE 1=1 ";
        if ( $this->category ){
            $query .= $wpdb->prepare( " AND b.term_taxonomy_id=%d ", $this->category );
        }
        if ( $search != '' ){
            $query .= " AND a.post_title LIKE '%$search%' ";
        }
        $query .= " AND a.post_type='product' AND a.post_status='publish' ";
        return $wpdb->get_var( $query );
    }

    private function searchIntoEdd()
    {
        global $wpdb;
        $search = uap_sanitize_array( $this->searchPhrase );
        $query = "SELECT a.ID, a.post_title, CAST(c.meta_value AS DECIMAL(10,2)) as price, CAST(d.meta_value AS UNSIGNED) as total_sales FROM {$wpdb->posts} a ";
        if ( $this->category ){
            $query .= " INNER JOIN {$wpdb->term_relationships} b ON a.ID=b.object_id  ";
        }
        $query .= " INNER JOIN {$wpdb->postmeta} c ON a.ID=c.post_id ";
        $query .= " INNER JOIN {$wpdb->postmeta} d ON a.ID=d.post_id ";
        $query .= " WHERE 1=1 ";
        if ( $this->category ){
            $query .= $wpdb->prepare( " AND b.term_taxonomy_id=%d ", $this->category );
        }
        if ( $search != '' ){
            $query .= " AND a.post_title LIKE '%$search%' ";
        }
        $query .= " AND a.post_type='download' AND a.post_status='publish' ";
        $query .= " AND c.meta_key='edd_price' ";
        $query .= " AND d.meta_key='_edd_download_sales' ";
        if ( $this->orderBy ){
            switch ( $this->orderBy ){
                case 'popularity':
                  $query .= " ORDER BY total_sales DESC ";
                  break;
                case 'date':
                  $query .= " ORDER BY a.post_date DESC ";
                  break;
                case 'price':
                  $query .= " ORDER BY price ASC ";
                  break;
                case 'price-desc':
                  $query .= " ORDER BY price DESC ";
                  break;
            }
        }
        $query .= $wpdb->prepare( " LIMIT %d OFFSET %d;", $this->limit, $this->offset);

        $data = $wpdb->get_results( $query );
        if ( !$data ){
            return [];
        }
        $return = [];

        $currency = edd_get_currency();

        require_once UAP_PATH . 'public/Affiliate_Referral_Amount.class.php';
        $do_math = new \Affiliate_Referral_Amount( $this->affiliateId, 'edd');

        foreach ( $data as $productData){
            $price = edd_price( $productData->ID, false );
            $referralAmount = $do_math->get_result( $price, $productData->ID );// input price, product id

            $return[$productData->ID] = [
                  'price'             => $price,
                  'label'             => $productData->post_title,
                  'featureImage'      => get_the_post_thumbnail_url( $productData->ID ),
                  'id'                => $productData->ID,
                  'product_type'      => 'edd',
                  'referral_amount'   => edd_currency_filter( $referralAmount, $currency ),
                  'permalink'         => get_permalink( $productData->ID ),
                  'categories'        => get_the_terms( $productData->ID, 'download_category' ),
            ];
        }
        return $return;
    }

    private function countsForEdd()
    {
        global $wpdb;
        $search = uap_sanitize_array( $this->searchPhrase );
        $query = "SELECT COUNT(a.ID) FROM {$wpdb->posts} a ";
        if ( $this->category ){
            $query .= " INNER JOIN {$wpdb->term_relationships} b ON a.ID=b.object_id ";
        }
        $query .= " WHERE 1=1 ";
        if ( $this->category ){
            $query .= $wpdb->prepare( " AND b.term_taxonomy_id=%d ", $this->category );
        }
        if ( $search != '' ){
            $query .= " AND a.post_title LIKE '%$search%' ";
        }
        $query .= " AND a.post_type='download' AND a.post_status='publish' ";
        return $wpdb->get_var( $query );
    }

    private function searchIntoUlp()
    {
        global $wpdb;
        $search = uap_sanitize_array( $this->searchPhrase );
        $query = "SELECT a.ID, a.post_title, CAST(c.meta_value AS DECIMAL(10,2)) as price, COUNT(d.id) as total_sales FROM {$wpdb->posts} a ";
        if ( $this->category ){
            $query .= " INNER JOIN {$wpdb->term_relationships} b ON a.ID=b.object_id  ";
        }
        $query .= " LEFT JOIN {$wpdb->postmeta} c ON a.ID=c.post_id ";
        $query .= " LEFT JOIN {$wpdb->prefix}ulp_user_entities_relations d ON a.ID=d.entity_id ";
        $query .= " WHERE 1=1 ";
        if ( $this->category ){
            $query .= $wpdb->prepare( " AND b.term_taxonomy_id=%d ", $this->category );
        }
        if ( $search != '' ){
            $query .= " AND a.post_title LIKE '%$search%' ";
        }
        $query .= " AND a.post_type='ulp_course' AND a.post_status='publish' ";
        $query .= " AND c.meta_key='ulp_course_price' ";
        $query .= " GROUP BY a.ID ";
        if ( $this->orderBy ){
            switch ( $this->orderBy ){
                case 'popularity':
                  $query .= " ORDER BY total_sales DESC ";
                  break;
                case 'date':
                  $query .= " ORDER BY a.post_date DESC ";
                  break;
                case 'price':
                  $query .= " ORDER BY price ASC ";
                  break;
                case 'price-desc':
                  $query .= " ORDER BY price DESC ";
                  break;
            }
        }
        $query .= $wpdb->prepare( " LIMIT %d OFFSET %d;", $this->limit, $this->offset);

        $data = $wpdb->get_results( $query );
        if ( !$data ){
            return [];
        }
        $currency = get_option('ulp_currency');
        $return = [];

        require_once UAP_PATH . 'public/Affiliate_Referral_Amount.class.php';
        $do_math = new \Affiliate_Referral_Amount( $this->affiliateId, 'ulp');

        foreach ( $data as $productData){
            $price = get_post_meta( $productData->ID, 'ulp_course_price', true );
            if ( is_numeric($price) ){
                $price .= $currency;
            }
            $referralAmount = $do_math->get_result( $price, $productData->ID );// input price, product id

            $return[$productData->ID] = [
                  'price'             => $this->formatUlpPrice( $price, $currency ),
                  'label'             => $productData->post_title,
                  'featureImage'      => get_the_post_thumbnail_url( $productData->ID ),
                  'id'                => $productData->ID,
                  'product_type'      => 'ulp',
                  'referral_amount'   => $this->formatUlpPrice( $referralAmount, $currency ),
                  'permalink'         => get_permalink( $productData->ID ),
                  'categories'        => get_the_terms( $productData->ID, 'ulp_course_categories' ),
            ];
        }
        return $return;
    }


    private function formatUlpPrice( $price='', $currency='' )
    {
        if ( function_exists('ulp_format_price') ){
            return ulp_format_price( $price );
        }
        return $price . $currency;
    }

    private function countsForUlp()
    {
        global $wpdb;
        $search = uap_sanitize_array( $this->searchPhrase );
        $query = "SELECT COUNT(a.ID) FROM {$wpdb->posts} a ";
        if ( $this->category ){
            $query .= " INNER JOIN {$wpdb->term_relationships} b ON a.ID=b.object_id ";
        }
        $query .= " WHERE 1=1 ";
        if ( $this->category ){
            $query .= $wpdb->prepare( " AND b.term_taxonomy_id=%d ", $this->category );
        }
        if ( $search != '' ){
            $query .= " AND a.post_title LIKE '%$search%' ";
        }
        $query .= " AND a.post_type='ulp_course' AND a.post_status='publish' ";
        return $wpdb->get_var( $query );
    }

}
