<?php
if ( ! defined( 'ABSPATH' ) ) {
	exit;
} // Exit if accessed directly

if ( ! defined( 'ATKP_VERSION_PRODUCTPRICEHISTORYTABLE' ) ) {
	define( 'ATKP_VERSION_PRODUCTPRICEHISTORYTABLE', 1 );
}

class atkp_pricehistorytable_helper {
	public function __construct() {
		$this->check_table_structure();

	}

	function get_pricehistory_tablename() {
		global $wpdb;

		return strtolower( $wpdb->prefix . ATKP_PLUGIN_PREFIX . '_pricehistory' );;
	}

	function check_table_structure() {
		global $wpdb;

		require_once( ABSPATH . 'wp-admin/includes/upgrade.php' ); //required for dbDelta

		$current_version_db = get_option( ATKP_PLUGIN_PREFIX . '_version_pricehistorytable' );
		//$current_version_db = 0;
		$table_name = $this->get_pricehistory_tablename();

		//older or not even set
		if ( $current_version_db < ATKP_VERSION_PRODUCTPRICEHISTORYTABLE || $current_version_db == false ) {

			$sql = "CREATE TABLE $table_name (
					`ID` int AUTO_INCREMENT,
					
    				`ProductId` int( 50 ) NOT NULL,    				
    				`ShopId` int (50) NULL,
    				`ProductPrice` varchar( 60 ) NULL ,
    				`ProductPriceFloat` varchar( 60 ) NULL ,
    				`CreatedOn` datetime NULL ,
    				PRIMARY KEY (`ID`)
    				) ENGINE = MYISAM DEFAULT CHARSET = utf8";

			$result = dbDelta( $sql );

			$wpdb->query ("ALTER TABLE $table_name ADD INDEX idx_atkp_listid_productnumber ( `ProductId`, `ShopId`);") ;
			$wpdb->query ("ALTER TABLE $table_name ADD INDEX idx_atkp_productid ( `ProductId`);") ;
			$wpdb->query ("ALTER TABLE $table_name ADD INDEX idx_atkp_productid2 ( `ProductId`,`ProductPriceFloat`);") ;

			update_option( ATKP_PLUGIN_PREFIX . '_version_pricehistorytable', ATKP_VERSION_PRODUCTPRICEHISTORYTABLE );
		}
	}

	function create_history_entry($productid, $shopid, $saleprice, $salepricefloat) {
		global $wpdb;

		if($salepricefloat == 0)
			return;

		$table_name = $this->get_pricehistory_tablename();

		$results = $wpdb->get_results(
         "SELECT ProductPriceFloat FROM $table_name WHERE ProductId=$productid " .($shopid <= 0 ? " and ShopId is null" : " and ShopId=$shopid"). " ORDER BY id DESC LIMIT 1"
         );

		if(count($results) > 0) {
			if($results[0]->ProductPriceFloat == $salepricefloat)
				return;
		}

		$data = array(
			'ProductId'         => intval($productid),
			'ShopId'            => intval($shopid),

			'ProductPrice'      => $saleprice,
			'ProductPriceFloat' => $salepricefloat,

			'CreatedOn' =>  date( "Y-m-d H:i:s" ),
		);

		$data = apply_filters( 'atkp_modify_pricehistory_before_db_write', $data );

		//insert oder update row
		$inserted = $wpdb->insert(
			$table_name,
			$data
		);

		return $inserted;

	}

	public function get_pricehistory_by_productid($productid) {
		global $wpdb;

		$table_name = $this->get_pricehistory_tablename();


		$result = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM $table_name WHERE productid = %s	", $productid ), ARRAY_A );


		return $this->build_history_array($result);
	}

	public function get_pricehistory_by_productid_lastxdays($productid, $minifier_shops, $lastxdays) {
		global $wpdb;

		$table_name = $this->get_pricehistory_tablename();

		$shopgroup = 'ShopId, ';
		if($minifier_shops) {
			$shopgroup = '';

		}

		$date = date('Y-m-d', strtotime("-$lastxdays days"));

		$result = $wpdb->get_results( $wpdb->prepare( "SELECT Id, ProductId, ShopId,CreatedOn, min(ProductPrice) as ProductPrice, avg(ProductPriceFloat) as ProductPriceFloat, concat(year(CreatedOn), '-', month(CreatedOn),'-',day(createdon)) as groupname FROM $table_name b WHERE productid = %s and createdon >= %s group by $shopgroup year(createdon), month(createdon), day(createdon)	", $productid, $date ), ARRAY_A );


		return $this->build_history_array($result);

	}

	public function get_pricehistory_by_productid_monthly($productid, $minifier_shops) {
		global $wpdb;

		$table_name = $this->get_pricehistory_tablename();

$shopgroup = 'ShopId, ';
if($minifier_shops) {
	$shopgroup = '';

}

		$result = $wpdb->get_results( $wpdb->prepare( "SELECT Id, ProductId, ShopId,CreatedOn, min(ProductPrice) as ProductPrice, avg(ProductPriceFloat) as ProductPriceFloat, concat(year(CreatedOn), '-', month(CreatedOn)) as groupname FROM $table_name b WHERE productid = %s group by $shopgroup year(createdon), month(createdon)	", $productid ), ARRAY_A );


		return $this->build_history_array($result);
	}



	private function build_history_array($result) {

		$products = array();

		if ( $result ) {
			foreach ( $result as $row ) {

				$product = new atkp_product_pricehistory();

				$product->id    = $row['Id'];
				$product->productid    = $row['ProductId'];
				$product->shopid       = $row['ShopId'];
				$product->price        = $row['ProductPrice'];
				$product->pricefloat   = $row['ProductPriceFloat'];
				$product->createdon      = $row['CreatedOn'];
				$product->groupname      = $row['groupname'];

				array_push( $products, $product );
			}
		}

		return $products;

	}

}