<?php

class ebay_mine_mysql {
	private $sqlConnection;
	/**
	 * 
	 * Array of ebay items cache to store before commiting to the database.
	 * @var ebay_item
	 */
	private $itemsToAdd;
	private $cacheSize = '500';
	
	public function __construct() {
		$this->sqlConnection = mysql_connect('127.0.0.1', 'root', 'jn4qpq8o');
		if (!$this->sqlConnection) {
    		die('Could not connect: ' . mysql_error());
		}
		mysql_select_db('ebay_mine');
		
		$this->itemsToAdd = array();
	}
	
	public function setCacheSize( $cacheSize ) {
		$this->cacheSize = $cacheSize;
	}
	
	public function addItem( $item ) {
		array_push( $this->itemsToAdd, clone $item );
			
		if ( sizeof( $this->itemsToAdd ) > $this->cacheSize ) {
			$this->commitToDb();	
		}
	}
	
	private function makeSQL( $item ) {
		$key_value_pairs = $item->getKeyValuePairs();

		$key_value_pairs['endTime'] = $this->correctDate( $key_value_pairs['endTime'] );
		$key_value_pairs = $this->correctBools( $key_value_pairs );
		$key_value_pairs = $this->quoteAndEscapeKeys( $key_value_pairs );
		
		
		$comma_separated_keys = implode( ",", array_keys( $key_value_pairs ) );
		$comma_separated_values = implode( ",", array_values( $key_value_pairs ) );
		
		return "INSERT INTO items ( " . $comma_separated_keys . " ) VALUES ( " . $comma_separated_values . " );";
	}
	
	private function correctBools( $keyValuePairs ) {
		foreach ( $keyValuePairs as $key => $value ) {
			if ( $value == "t" ) {
				$keyValuePairs[$key] = 1;
			}
			
			if ( $value == "f" ) {
				$keyValuePairs[$key] = 0;
			}
		}
		
		return $keyValuePairs;
	}
	
	private function quoteAndEscapeKeys( $keyValuePairs ) {
		foreach( $keyValuePairs as $key_name => $key_value ) {
			/** Quote and mysql escape all string values (ie non-numeric keys and NULL) **/
			if ( !is_numeric( $key_value ) and $key_value != "NULL" ) {
				$keyValuePairs[$key_name] = '"' . mysql_real_escape_string( $key_value ) . '"';
			}
		}
		
		return $keyValuePairs;
	}
	
	private function correctDate( $ebayDate ) {
		//Ebay date format: Nov-19 08:31
		//NOTE: Timezone hard coded to PST (eBay official time)!
		if ( $ebayDate != "NULL" ) {
			$date_format = DateTime::createFromFormat('M-d H:i', $ebayDate, new DateTimeZone("PST"));
			return $date_format->format('Y:m:d H;i:s');
		} else {
			return "NULL";
		}
	}
	
	private function commitToDb() {
		mysql_query( "START TRANSACTION" );
		mysql_query( "BEGIN" );

		
		foreach( $this->itemsToAdd as $item ) {
			$thisItemQuery = $this->makeSQL( $item );
			
			mysql_query( $thisItemQuery ); 
			if( mysql_error() ) {
				print "SQL ERROR: " . mysql_error() . "\n";
				print "SQL: " . $thisItemQuery . "\n\n";
			}
		}
		
		mysql_query( "COMMIT" );
		
		$this->itemsToAdd = array(); // flush cache
	}
}
?>