<?php

require_once(__DIR__ . '/../../../../vendor/autoload.php');
require_once(__DIR__ . '/../../../error.inc');
use Orchard\Mysqli\QueryData;
use Orchard\Mysqli\Wrapper as MysqliWrapper;
use Orchard\QueryWrapper;

class Release_Pricing_Tier{

	public $rpt_info = array();
	private $id;
	private $table_type;

	public function __construct($wrapperObj, $rpt_id, $table_type)
	{
		$this->id = intval($rpt_id);
		$this->table_type = trim(strtolower($table_type));
		$this->setReleasePricingTierInfo($wrapperObj);
	}

	/**
	 * Fills $rpt_info array with data based on current $id
	 */
	public function setReleasePricingTierInfo($wrapperObj) {
		$isPDO = $wrapperObj instanceof QueryWrapper;
		if ($this->rpt_info === array()) {
			$sql = 'SELECT rpt.id, rpt.upc, rpt.release_id, rpt.pricing_tier_id, rcp.custom_price,
			        rpt.start_date, end_date, rpt.active
			    FROM release_pricing_tier rpt
			    INNER JOIN release_custom_pricing rcp ON rcp.release_pricing_tier_id = rpt.id
			    WHERE rpt.id = ?';
			if ($isPDO) {
			    $dataParams = [$this->id];
			} else {
			    $dataParams = new QueryData();
			    $dataParams->addDataParam('i', $this->id);
			}
			$result = $wrapperObj->executeQuery($sql, $dataParams);
			if ($result === false) {
			    $isPDO ? show_pdo_error($sql, $wrapperObj->getLastStatement()) : showierror($sql);
			}
			$this->rpt_info = $result[0];
		}
	}

	/**
	 * Returns current contents of $rpt_info array
	 * @param $key - if $key is provided, it is used to determine returned value.
	 * 				 If it is an array, and array of values is returned
	 * @return $returnValue
	 */
	public function getReleasePricingTierInfo($wrapperObj, $key = null)
	{
		if ($this->rpt_info === array()) { $this->setReleasePricingTierInfo($wrapperObj); }
		if (is_null($key)) {
			return $this->rpt_info;
		} else {
			if (is_array($key)) {
				$arr = array();
				foreach ($key as $k) {
					$arr[] = $this->rpt_info[$k];
				}
				return $arr;
			} else {
				return $this->rpt_info[$key];
			}
		}
	}

	/**
	 * Updates Release_Pricing_Tier record in database with $data
	 * @param array $data - values for Release_Pricing_Tier record
	 * @return int $result - 0 if update fails, number of updated rows if successful
	 */
	public function updateReleasePricingTier($wrapperObj, $data)
	{
		$isPDO = $wrapperObj instanceof QueryWrapper;
		$fields = array('custom_price', 'start_date', 'end_date', 'active');
		$update = '';
		$dataParams = $isPDO ? [] : new QueryData();
		foreach ($fields as $key) {
			if (key_exists($key, $data)) {

				if (in_array($key, array('start_date', 'end_date'))) {
					$update_string = trim($data[$key]) ? '?' : 'null';
					$isPDO ? $dataParams[] = $data[$key] : $dataParams->addDataParam('s', $data[$key]);
				}
				else if ($key == 'active') {
					$update_string = $data[$key];
				}
				else {
					$update_string = '? ';
					$isPDO ? $dataParams[] = $data[$key] : $dataParams->addDataParam('i', $data[$key]);
				}
				$update[] = $key . " = '" .$update_string. "'";
			}
		}

		$update = ($update) ? implode(', ', $update) : '';

		if ($update) {
			$sql = 'UPDATE release_pricing_tier SET ' . $update . ' WHERE id = ? LIMIT 1';
			$isPDO ? $dataParams[] = $this->id : $dataParams->addDataParam('i', $this->id);
			$return_type = $isPDO ? QueryWrapper::RETURN_AFFECTED_ROWS : MysqliWrapper::RETURN_AFFECTED_ROWS;
			$result = $wrapperObj->executeQuery($sql, $dataParams, $return_type);
			if ($result === false) {
			    $isPDO ? show_pdo_error($sql, $wrapperObj->getLastStatement()) : showierror($sql);
			}
			return $result;
		}
		return 0;
	}

	/**
	 * Creates new Release_Pricing_Tier record in database
	 * @param array $data - values for new record
	 * @param string $table_type - 'release' or 'label_copy'
	 * @return int $result - 0 if insert fails, ID of inserted row if successful
	 */
	public static function createReleasePricingTier ($wrapperObj, $data, $table_type) {
		$isPDO = $wrapperObj instanceof QueryWrapper;
		if (isset($data['pricing_tier_id']) and $data['pricing_tier_id'] > 0) {
			$start_date = trim($data['start_date']) ? $data['start_date'] : 'null';
			$end_date = trim($data['end_date']) ? $data['end_date'] : 'null';
			$table_type_data = $data[($table_type == 'label_copy' ? 'release_id' : 'upc')];
			$sql = 'INSERT INTO release_pricing_tier (release_id, upc, pricing_tier_id, start_date, end_date, active)
				SELECT release_id, upc, ?, ?, ?, ?
				FROM releases WHERE ' . ($table_type == 'label_copy' ? 'release_id' : 'upc') . ' = ?';
			if ($isPDO) {
			    $dataParams = [];
			    $dataParams[] = $data['pricing_tier_id'];
			    $dataParams[] = $start_date;
			    $dataParams[] = $end_date;
			    $dataParams[] = $data['active'];
			    $dataParams[] = $table_type_data;
			} else {
			    $dataParams = new QueryData();
			    $dataParams->addDataParam('i', $data['pricing_tier_id']);
			    $dataParams->addDataParam('s', $start_date);
			    $dataParams->addDataParam('s', $end_date);
			    $dataParams->addDataParam('s', $data['active']);
			    $dataParams->addDataParam('i', $table_type_data);
			}
			$return_type = $isPDO ? QueryWrapper::RETURN_INSERT_ID : MysqliWrapper::RETURN_INSERT_ID;
			$pricingTierId = $wrapperObj->executeQuery($sql, $dataParams, $return_type);
			if ($pricingTierId === false) {
			    $isPDO ? show_pdo_error($sql, $wrapperObj->getLastStatement()) : showierror($sql);
			}
			$sql = 'INSERT INTO release_custom_pricing (release_pricing_tier_id, custom_price) VALUES (?, ?)';
			if ($isPDO) {
			    $dataParams = [];
			    $dataParams[] = $pricingTierId;
			    $dataParams[] = $data['custom_price'];
			} else {
			    $dataParams = new QueryData();
			    $dataParams->addDataParam('i', $pricingTierId);
			    $dataParams->addDataParam('i', $data['custom_price']);
			}
			$return_type = $isPDO ? QueryWrapper::RETURN_INSERT_ID : MysqliWrapper::RETURN_INSERT_ID;
			$pricingTierId = $wrapperObj->executeQuery($sql, $dataParams, $return_type);
			if ($pricingTierId === false) {
			    $isPDO ? show_pdo_error($sql, $wrapperObj->getLastStatement()) : showierror($sql);
			}
			return $pricingTierId; // id of new row should be returned
		}
		return 0;
	}

	/**
	 * Gets array of pricing tier ID's from database for a given scheme ID and release ID or UPC
	 *
	 * @param int $scheme_id - scheme id
	 * @param string $table_type - 'release' or 'label_copy'
	 * @param int $id
	 * @return array $ids - returns 0 if there are no relevant id's, array of id's otherwise
	 */
	public static function getRPTIDsBySchemeID ($wrapperObj, $scheme_id, $table_type, $id) {
		$isPDO = $wrapperObj instanceof QueryWrapper;
		$id_field = (trim(strtolower($table_type)) == 'release') ? 'upc' : 'release_id';
		$ids =array();
		$sql = 'SELECT rpt.id FROM release_pricing_tier rpt
		    INNER JOIN dms_pricing_tier dpt ON dpt.pricing_tier_id = rpt.pricing_tier_id
		    AND dpt.pricing_scheme_id = ? AND rpt.' . $id_field . ' = ?';
		if ($isPDO) {
		    $dataParams = [$scheme_id, $id];
		} else {
		    $dataParams = new QueryData();
		    $dataParams->addDataParam('i', $scheme_id);
		    $dataParams->addDataParam('i', $id);
		}
		$result = $wrapperObj->executeQuery($sql, $dataParams);
		if ($result === false){
		    $isPDO ? show_pdo_error($sql, $wrapperObj->getLastStatement()) : showierror($sql);
		    return 0;
		}
		foreach ($result as $row) {
			$ids[] = $row[0];
		}
		return $ids;
	}

	/**
	 * Returns 1 if Release_Pricing_Tier row with values provided already exists
	 * Returns 0 otherwise
	 * @param bigint $data['upc'] - release UPC
	 * @param int $data['pricing_tier_id'] - release pricing tier ID
	 * @param int $data['custom_price'] - custom price value
	 * @param datetime $data['start_date'] - date custom price becomes valid
	 * @param datetime $data['end_date'] - date custom price exprires
	 * @param string $data['active'] - 'Y' or 'N'. Shows if this price is active. We are only concerned with active rows
	 * @param string $table_type - 'release' or 'label_copy'
	 * @return int $return_value
	 */
	public static function releasePricingTierExists ($wrapperObj, $data, $table_type) {
		$isPDO = $wrapperObj instanceof QueryWrapper;
		$dataParams = $isPDO ? [] : new QueryData();
		if (trim(strtolower($table_type)) == 'release') {
			$where_clause = 'WHERE upc = ?';
			$isPDO ? $dataParams[] = $data['upc'] : $dataParams->addDataParam('i', $data['upc']);
		} else if (trim(strtolower($table_type)) == 'label_copy') {
			$where_clause = 'WHERE release_id = ?';
			$isPDO ? $dataParams[] = $data['release_id'] : $dataParams->addDataParam('i', $data['release_id']);
		}

		$sql = 'SELECT rtp.id FROM release_pricing_tier rtp
		    INNER JOIN release_custom_pricing rcp ON rcp.release_pricing_tier_id = rtp.id
		    ' . $where_clause . '
		    AND rtp.pricing_tier_id = ?
		    AND rcp.custom_price = ?
		    AND rtp.active = "Y"';
		$isPDO ? $dataParams[] = $data['pricing_tier_id'] : $dataParams->addDataParam('i', $data['pricing_tier_id']);
		$isPDO ? $dataParams[] = $data['custom_price'] : $dataParams->addDataParam('d', $data['custom_price']);
		$result = $wrapperObj->executeQuery($sql, $dataParams);
		if ($result === false) {
		    $isPDO ? show_pdo_error($sql, $wrapperObj->getLastStatement()) : showierror($sql);
		}
		if (count($result) > 0) {
			return 1;
		}
		return 0;
	}

	/**
	 * Gets table name for a given table type
	 * @param string $table_type - 'release' or 'label_copy'
	 * @return string $table_name
	 */
	public static function getTableName($table_type)
	{
		$table_name = 'release_pricing_tier';
		return $table_name;
	}

	/**
	 * Gets array of custom pricing tier ID's from database
	 *
	 * @return array $ids - array of custom pricing tier id's
	 */
	public static function getCustomPricingTierIDs($wrapperObj)
	{
		$sql = "select pricing_tier_id from dms_pricing_tier where price_code = 'CUSTOM'";
		$result = $wrapperObj->executeQuery($sql, []);
		if ($result === false) {
		    show_pdo_error($sql, $wrapperObj->getLastStatement());
		}
		foreach ($result as $row) {
		    $ids[] = $row["pricing_tier_id"];
		}
		return $ids;
	}

	/**
	 * Gets array of pricing scheme ID's for a given master store ID
	 * @param int $store_id - master store ID
	 * @return array $ids - array of pricing scheme id's
	 */
	public static function getPricingSchemeIDsByMasterStoreID($wrapperObj, $store_id)
	{
		$isPDO = $wrapperObj instanceof QueryWrapper;
		if ($isPDO) {
		    $dataParams = [$store_id];
		} else {
		    $dataParams = new QueryData();
		    $dataParams->addDataParam('i', $store_id);
		}
		$sql = 'SELECT * FROM dms_master_pricing_scheme WHERE customer_master_master_id = ?';
		$result = $wrapperObj->executeQuery($sql, $dataParams);
		if ($result === false) {
		    $isPDO ? show_pdo_error($sql, $wrapperObj->getLastStatement()) : showierror($sql);
		}
		foreach ($result as $row) {
			$ids[] = $row[0];
		}
		return $ids;
	}

	/**
	 * Gets pricing scheme ID for a given pricing scheme name
	 * @param string $pricing_scheme - pricing scheme name
	 * @return int $id - pricing scheme id
	 */
	public static function getPricingSchemeIDByName($wrapperObj, $pricing_scheme)
	{
		$isPDO = $wrapperObj instanceof QueryWrapper;
		if ($isPDO) {
		    $dataParams = [$pricing_scheme];
		} else {
		    $dataParams = new QueryData();
		    $dataParams->addDataParam('s', $pricing_scheme);
		}
		$sql = 'SELECT pricing_scheme_id FROM dms_master_pricing_scheme WHERE pricing_scheme = ?';
		$result = $wrapperObj->executeQuery($sql, $dataParams);

		if ($result === false) {
		    $isPDO ? show_pdo_error($sql, $wrapperObj->getLastStatement()) : showierror($sql);
		}

		$row = $result[0];

		return $row[0];
	}

	/**
	 * Gets pricing tier data for the provided price code, product type and master store ID
	 * @param int $price_code - price code
	 * @param int $product_type_id - product type (3 for Film, 2 for TV Show, etc.)
	 * @param int $customer_master_master_id - master store ID
	 * @param string $scheme_level - 'release' or 'track'
	 * @return array $pricing_tiers
	 */
	public static function getPricingTierData($wrapperObj, $price_code, $product_type_id, $customer_master_master_id, $scheme_level='release')
	{
		$isPDO = $wrapperObj instanceof QueryWrapper;
		$sql = 'SELECT dpt.pricing_tier_id, dpt.pricing_tier, dpt.pricing_scheme_id
				FROM dms_pricing_tier dpt
				INNER JOIN dms_master_pricing_scheme dmps ON dmps.pricing_scheme_id = dpt.pricing_scheme_id
				WHERE price_code = ? AND dmps.product_type_id = ?
				AND dmps.customer_master_master_id = ?
				AND dmps.scheme_level = ?';
		if ($isPDO) {
		    $dataParams = [];
		    $dataParams[] = $price_code;
		    $dataParams[] = $product_type_id;
		    $dataParams[] = $customer_master_master_id;
		    $dataParams[] = $scheme_level;
		} else {
		    $dataParams = new QueryData();
		    $dataParams->addDataParam('i', $price_code);
		    $dataParams->addDataParam('i', $product_type_id);
		    $dataParams->addDataParam('i', $customer_master_master_id);
		    $dataParams->addDataParam('s', $scheme_level);
		}
 		$result = $wrapperObj->executeQuery($sql, $dataParams);
		if ($result === false) {
		    $isPDO ? show_pdo_error($sql, $wrapperObj->getLastStatement()) : showierror($sql);
		}
		$pricing_tiers = array();
		foreach ($result as $row) {
			$pricing_tiers[$row['pricing_tier_id']] = array('pricing_tier'=>$row['pricing_tier'],'pricing_scheme_id'=>$row['pricing_scheme_id']);
		}
		return $pricing_tiers;
	}

	public static function insert_pricing_tiers_multiple($wrapperObj, $release_id, $pricing_tier_arr)
	{
		$isPDO = $wrapperObj instanceof QueryWrapper;
		$pricing_tier_arr = array_keys($pricing_tier_arr);
		if($release_id && is_array($pricing_tier_arr) && count($pricing_tier_arr)){
			$sql = 'INSERT INTO release_pricing_tier (release_id, upc, pricing_tier_id, start_date, active)
			    SELECT r.release_id, r.upc, dpt.pricing_tier_id, CURDATE(), "Y"
			    FROM releases r, dms_pricing_tier dpt
			    WHERE r.release_id = ?
			        AND dpt.pricing_tier_id IN (' . implode(', ', array_fill(0, count($pricing_tier_arr), '?')) . ')';

			$data_params = $isPDO ? [] : new QueryData();
			$isPDO ? $data_params[] = $release_id : $data_params->addDataParam('i', $release_id);
			foreach ($pricing_tier_arr as $val) {
			    $isPDO ? $data_params[] = $val : $data_params->addDataParam('i', $val);
			}
			$return_type = $isPDO ? QueryWrapper::RETURN_AFFECTED_ROWS : MysqliWrapper::RETURN_AFFECTED_ROWS;
			$affected_rows = $wrapperObj->executeQuery($sql, $data_params, $return_type);
			if ($affected_rows === false) {
			    $isPDO ? show_pdo_error($sql, $wrapperObj->getLastStatement()) : showierror($sql);
			}
			return $affected_rows;
		}
		return 0;
	}

	public static function deletePricingTierData($release_id, $wrapperObj)
	{
	    $isPDO = $wrapperObj instanceof QueryWrapper;
	    $dataParams = $isPDO ? [] : new QueryData();
	    $sql = 'DELETE FROM release_pricing_tier
	        WHERE release_id = ?';
	    $isPDO ? $dataParams[] = $release_id : $dataParams->addDataParam('i', $release_id);
	    $return_type = $isPDO ? QueryWrapper::RETURN_AFFECTED_ROWS : MysqliWrapper::RETURN_AFFECTED_ROWS;
	    $result = $wrapperObj->executeQuery($sql, $dataParams, $return_type);
	    if ($result === false) {
	        $isPDO ? show_pdo_error($sql, $wrapperObj->getLastStatement()) : showierror($sql);
	    }
	}
}
?>