<?php
if ( ! defined( 'ABSPATH' ) ) {
		exit;
} // Exit if accessed directly

if ( ! defined( 'ATKP_VERSION_PRODUCTOFFERSTABLE' ) ) {
		define( 'ATKP_VERSION_PRODUCTOFFERSTABLE', 2 );
}

class atkp_offertable_helper {
		public function __construct() {
				$this->check_table_structure();

		}

		/**
		 * Gibt den internen Tabellenname inkl. Prefix für _offertable zurück
		 * @return string Der Tabellenname
		 */
		function get_offertable_tablename() {
				global $wpdb;

				return strtolower( $wpdb->prefix . ATKP_PLUGIN_PREFIX . '_additionaloffers' );;
		}

		function get_old_offertable_tablename() {
				global $wpdb;

				return strtolower( $wpdb->prefix . ATKP_PLUGIN_PREFIX . '_offertable' );;
		}

		/**
		 * Aktualisiert oder hängt die übergebenen atkp_product_offer Angebote an der Tabelle an
		 * @param int $listid Die Listen-ID für die angehängten Angebote
		 * @param string $asin Die eindeutige Produkt-ID in der Liste
		 * @param Array $alloffers Ein Array von atkp_product_offer welche gespeichert werden soll
		 */
		function update_offers_by_listid( $listid, $asin, $alloffers) {
				global $wpdb;

				foreach ( $alloffers as $offer ) {
						$offer->productid = 0;
						$offer->listid = $listid;
						$offer->asin = $asin;

						$this->update_offer( $offer , true);
				}

		}


		/**
		 * Löscht alle Angebote zu diesem Produkt und hängt die übergebenen atkp_product_offer Angebote an
		 * @param int $productid Die Produkt-ID für die angehängten Angebote
		 * @param Array $alloffers Ein Array von atkp_product_offer welche gespeichert werden soll
		 * @param bool $clearoffers Gibt an ob die existierenden Angebote vor dem Speichern gelöscht werden sollen
		 */
		function update_offers_by_productid( $productid, $alloffers, $clearoffers = true) {
				global $wpdb;

				if($clearoffers)
					$this->clear_offers_internal( $productid, 0 );

				foreach ( $alloffers as $offer ) {
						$offer->productid = $productid;
						$offer->listid = 0;
						$offer->asin = '';

						$this->update_offer(  $offer, true );
				}

		}

		/**
		 * Löscht alle Angebote zu der angegeben List-ID aus der Tabelle
		 * @param int $listid Die Listen-ID
		 */
		function clear_offers_by_listid( $listid ) {
				$this->clear_offers_internal( 0, $listid );
		}

		/**
		 * Lädt alle Angebote welche zu der Liste + eindeutige Product-ID gehören
		 *
		 * @param int $listid Die Listen-ID
		 * @param string $asin Die eindeutige ID vom Listeneintrag
		 *
		 * @return array Gibt ein Array von atkp_product_offer zurück
		 */
		function get_offers_by_listid( $listid, $asin ) {
				return $this->get_offers_internal( '', $listid, $asin );
		}


		/**
		 * Lädt alle Angebote welche zu dieser AT-Produkt-ID gehören
		 *
		 * @param int $productid Die AT-Produkt-ID
		 *
		 * @return array Gibt ein Array von atkp_product_offer zurück
		 */
		function get_offers_by_productid( $productid ) {

				return $this->get_offers_internal( $productid, '', '' );
		}

		/**
		 * Prüft ob die Tabelle atkp_offertable vorhanden ist, und legt diese ggf. an.
		 */
		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_additionalofferstable' );
				//$current_version_db = 1;
				$table_name = $this->get_offertable_tablename();

				//older or not even set
				if ( $current_version_db < ATKP_VERSION_PRODUCTOFFERSTABLE || $current_version_db == false ) {

						$sql = "CREATE TABLE $table_name (
    		        `OfferId` int (50) NOT NULL,
    				`ShopId` int (50) NOT NULL,
    				`ProductId` int( 50 ) NOT NULL,
    				`ListId` int( 50 ) NOT NULL,
    				`ProductNumber` varchar( 100 ) NOT NULL,
    				`ProductTitle` varchar(700) NULL ,
    				`ProductEAN` varchar( 20 ) NULL ,
    				`Shipping` varchar( 150 ) NULL ,
    				`Availability` varchar( 150 ) NULL ,
    				`ProductPrice` varchar( 60 ) NULL ,
    				`ProductPriceFloat` varchar( 60 ) NULL ,
    				`OfferType` varchar(40) NULL ,
    				`ProductLink` varchar(700) NULL ,
    				`HideOffer` int(1) NULL ,
    				`HoldOnTop` int(1) NULL ,
    				`Message` varchar(700) NULL ,
    				`UpdatedOn` datetime NULL ,
    				PRIMARY KEY (`OfferId`,`ProductId`,`ListId`)
    				) ENGINE = MYISAM DEFAULT CHARSET = utf8";

						dbDelta( $sql );

						$wpdb->query ("ALTER TABLE $table_name ADD INDEX idx_atkp_listid_productnumber ( `ListId`, `ProductNumber`);") ;
						$wpdb->query ("ALTER TABLE $table_name ADD INDEX idx_atkp_productid ( `ProductId`);") ;
						$wpdb->query ("ALTER TABLE $table_name ADD INDEX idx_atkp_productid2 ( `shopid`,`offertype`,`UpdatedOn`);") ;

						$wpdb->query ("ALTER TABLE $table_name MODIFY `ProductEAN` VARCHAR(60);") ;


						update_option( ATKP_PLUGIN_PREFIX . '_version_additionalofferstable', ATKP_VERSION_PRODUCTOFFERSTABLE );

						//delete old table:
						$oldtablename = $this->get_old_offertable_tablename();
						if($oldtablename != '')
							$wpdb->query( "DROP TABLE IF EXISTS $oldtablename;" );

				}
		}

		/**
		 * Interne Funktion für das Angebote aktualisieren
		 * @param $productid
		 * @param $listid
		 * @param $productnumber
		 * @param $offer
		 *
		 * @return false|int
		 */
		public function update_offer($offer , $isempty = false) {
				global $wpdb;

				$table_name = $this->get_offertable_tablename();

				$data = array(
						'ProductId'         => intval($offer->productid),
						'ListId'            => intval($offer->listid),
						'ProductTitle'      => $offer->title,
						'ProductNumber'     => $offer->asin,
						'OfferId'           => intval($offer->id),
						'Shipping'          => $offer->shipping,
						'Availability'      => $offer->availability,
						'ProductPrice'      => $offer->price,
						'ProductPriceFloat' => $offer->pricefloat,
						'ShopId'            => intval($offer->shopid),
						'OfferType'         => intval($offer->type),
						'ProductEAN'        => $offer->number,
						'ProductLink'       => $offer->link,
						'HideOffer'         => $offer->hideoffer ? 1 : 0,
						'HoldOnTop'         => $offer->holdontop ? 1 : 0,
						'Message'           => $offer->message == '' ? NULL : $offer->message,
						'UpdatedOn' =>  $isempty ? NULL : date( "Y-m-d H:i:s" ),
				);

				$data = apply_filters( 'atkp_modify_offer_before_db_write', $data );

				//insert oder update row
				$updated = $wpdb->replace(
						$table_name,
						$data
				);

				return $updated;
		}

		/**
		 * Interne Funktion für die beiden externen Funktionen
		 * @param $productid
		 * @param $listid
		 */
		private function clear_offers_internal( $productid, $listid ) {
				global $wpdb;

				$table_name = $this->get_offertable_tablename();

				if ( $listid != '' ) {
						$wpdb->delete( $table_name, array( 'listid' => $listid ), array( '%s' ) );
				} else {
						$wpdb->delete( $table_name, array( 'productid' => $productid ), array( '%s' ) );
				}
		}

		/**
		 * Interne Funktion welche von den beiden externen Funktionen aufgerufen wird.
		 *
		 * @param $productid
		 * @param $listid
		 * @param $asin
		 *
		 * @return array
		 */
		private function get_offers_internal( $productid, $listid, $asin ) {
				global $wpdb;

				$table_name = $this->get_offertable_tablename();

				if ( $listid != '' ) {
						$result = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM $table_name WHERE listid = %s and productnumber = %s	", $listid, $asin ), ARRAY_A );
				} else {
						$result = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM $table_name WHERE productid = %s	", $productid ), ARRAY_A );
				}

				return $this->build_offer_array($result);
		}

		public function get_productids( ) {
				global $wpdb;

				$table_name = $this->get_offertable_tablename();

				$result = $wpdb->get_results( "SELECT productid FROM $table_name group by productid	", ARRAY_A );

				$resultxx = array();

				foreach($result as $xx) {
						array_push($resultxx, $xx['productid']);
				}

				return $resultxx;
		}

		public function delete_productids($productids) {
				global $wpdb;

				$table_name = $this->get_offertable_tablename();

				$ids = implode( ',', array_map( 'absint', $productids ) );
				$wpdb->query( "DELETE FROM $table_name WHERE productid IN($ids)" );
		}

		public function delete_listids($listids) {
				global $wpdb;

				$table_name = $this->get_offertable_tablename();

				$ids = implode( ',', array_map( 'absint', $listids ) );
				$wpdb->query( "DELETE FROM $table_name WHERE listid IN($ids)" );
		}

		public function get_listids( ) {
				global $wpdb;

				$table_name = $this->get_offertable_tablename();

				$result = $wpdb->get_results( "SELECT listid FROM $table_name group by listid	", ARRAY_A );

				$resultxx = array();

				foreach($result as $xx) {
						array_push($resultxx, $xx['listid']);
				}

				return $resultxx;
		}

		public function get_offers_for_update( $shopid, $offertype, $page = 1 ) {
				global $wpdb;

				$items_per_page = 10;
				$offset = ( $page * $items_per_page ) - $items_per_page;

				$table_name = $this->get_offertable_tablename();

				$query = '';
				switch($offertype) {
						case 'ASIN':
								$query = $wpdb->prepare(  "SELECT * FROM $table_name WHERE shopid = %s and offertype = %s and UpdatedOn is null	", $shopid, 2 );
								break;
						case 'EAN':
								$query = $wpdb->prepare( "SELECT * FROM $table_name WHERE shopid = %s and offertype = %s and UpdatedOn is null", $shopid, 1  );
								break;
						default:
								throw new Exception('unknown offertype: '. $offertype);
				}

				$result = $wpdb->get_results( $query . " ORDER BY OfferId LIMIT ${offset}, ${items_per_page}", ARRAY_A );

				return $this->build_offer_array($result);
		}

		private function build_offer_array($result) {

				$products = array();

				if ( $result ) {
						foreach ( $result as $row ) {

								$product = new atkp_product_offer();

								$product->productid    = $row['ProductId'];
								$product->listid       = $row['ListId'];
								$product->asin         = $row['ProductNumber'];
								$product->title        = $row['ProductTitle'];
								$product->id           = $row['OfferId'];
								$product->shipping     = $row['Shipping'];
								$product->availability = $row['Availability'];
								$product->price        = $row['ProductPrice'];
								$product->pricefloat   = $row['ProductPriceFloat'];
								$product->shopid       = $row['ShopId'];
								$product->type         = $row['OfferType'];
								$product->number       = $row['ProductEAN'];
								$product->link         = $row['ProductLink'];
								$product->hideoffer    = $row['HideOffer'] == 1;
								$product->holdontop    = $row['HoldOnTop'] == 1;
								$product->message      = $row['Message'];
								$product->updatedon      = $row['UpdatedOn'];

								array_push( $products, $product );
						}
				}

				return $products;

		}

}