<?php
/**
 * @copyright    OCTemplates
 * @support      https://octemplates.net/
 * @license      LICENSE.txt
 */

class ModelOctemplatesModuleOctPriceTracker extends Model {
	
	public function addRequest($data) {
		$this->db->query("
			INSERT INTO " . DB_PREFIX . "oct_price_requests 
			SET 
				product_id = '" . (int)$data['pid'] . "', 
				customer_id = '" . (int)$data['customer_id'] . "', 
				customer_name = '" . $this->db->escape($data['customer_name']) . "', 
				email = '" . $this->db->escape($data['email']) . "', 
				phone = '" . $this->db->escape($data['phone']) . "', 
				current_price = '" . (float)$data['current_price'] . "',
				currency_id = '" . (int)$data['currency_id'] . "',
				language_id = '" . (int)$data['language_id'] . "',
				date_created = NOW(),
				status = 'pending'
		");
	}

	public function isAlreadySubscribed($data) {
		$query = $this->db->query("
			SELECT * 
			FROM " . DB_PREFIX . "oct_price_requests 
			WHERE product_id = '" . (int) $data['pid'] . "' 
			AND status = 'pending' 
			AND email = '" . $this->db->escape($data['email']) . "'
		");

		return $query->num_rows > 0;
	}

	public function getPendingRequests($limit = 1000, $start = 0, $product_id = null) {
		$sql = "SELECT pr.*, p.price, p.tax_class_id, p.status as product_status,
				c.code as currency_code, pr.language_id
				FROM " . DB_PREFIX . "oct_price_requests pr
				LEFT JOIN " . DB_PREFIX . "product p ON (pr.product_id = p.product_id)
				LEFT JOIN " . DB_PREFIX . "currency c ON (pr.currency_id = c.currency_id)
				WHERE pr.status = 'pending' AND p.status = '1'";
		
		if ($product_id !== null) {
			$sql .= " AND pr.product_id = '" . (int)$product_id . "'";
		}
		
		$sql .= " ORDER BY pr.request_id ASC LIMIT " . (int)$start . "," . (int)$limit;
		
		$requests = $this->db->query($sql)->rows;
		
		if (!empty($requests)) {
			$product_ids = array_column($requests, 'product_id');
			$specials = $this->getSpecialPrices($product_ids);
			
			foreach ($requests as &$request) {
				$request['special'] = $specials[$request['product_id']] ?? null;
			}
		}
		
		return $requests;
	}

	private function getSpecialPrices($product_ids) {
		$customer_group_id = (int)$this->config->get('config_customer_group_id');
		$ids = implode(',', array_map('intval', $product_ids));
		
		$sql = "SELECT product_id, price
				FROM " . DB_PREFIX . "product_special
				WHERE product_id IN (" . $ids . ")
				AND customer_group_id = '" . $customer_group_id . "'
				AND ((date_start = '0000-00-00' OR date_start <= NOW()) 
				AND (date_end = '0000-00-00' OR date_end > NOW()))
				ORDER BY product_id, priority ASC, price ASC";
				
		$query = $this->db->query($sql);
		$result = array();
		
		foreach ($query->rows as $row) {
			if (!isset($result[$row['product_id']])) {
				$result[$row['product_id']] = $row['price'];
			}
		}
		
		return $result;
	}

	public function updateRequestStatus($request_id, $status = 'sent') {
		$this->db->query("UPDATE " . DB_PREFIX . "oct_price_requests 
			SET status = '" . $this->db->escape($status) . "' 
			WHERE request_id = '" . (int)$request_id . "'");
	}

	public function addPriceLog($request_id, $old_price, $new_price, $status = 'success') {
		$this->db->query("INSERT INTO " . DB_PREFIX . "oct_price_logs SET
			request_id = '" . (int)$request_id . "',
			old_price = '" . (float)$old_price . "',
			new_price = '" . (float)$new_price . "',
			date_sent = NOW(),
			status = '" . $this->db->escape($status) . "'");
	}

	public function getUserRequests($customer_id, $start = 0, $limit = 20) {
		$query = $this->db->query("
			SELECT pr.*, pd.name AS product_name, pl.date_sent,
			c.code AS currency_code, c.symbol_left, c.symbol_right, c.decimal_place
			FROM " . DB_PREFIX . "oct_price_requests pr
			LEFT JOIN " . DB_PREFIX . "product_description pd ON (pr.product_id = pd.product_id)
			LEFT JOIN " . DB_PREFIX . "oct_price_logs pl ON (pr.request_id = pl.request_id)
			LEFT JOIN " . DB_PREFIX . "currency c ON (pr.currency_id = c.currency_id)
			WHERE pr.customer_id = '" . (int)$customer_id . "'
			AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'
			ORDER BY pr.date_created DESC
			LIMIT " . (int)$start . "," . (int)$limit
		);

		return $query->rows;
	}

	public function getTotalUserRequests($customer_id) {
		$query = $this->db->query("
			SELECT COUNT(*) AS total 
			FROM " . DB_PREFIX . "oct_price_requests 
			WHERE customer_id = '" . (int)$customer_id . "'
		");

		return $query->row['total'];
	}

	public function removeRequest($request_id) {
		$customer_id = $this->customer->getId();
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "oct_price_requests WHERE request_id = '" . (int)$request_id . "' AND customer_id = '" . (int)$customer_id . "'");

		if ($query->num_rows) {
			$this->db->query("DELETE FROM " . DB_PREFIX . "oct_price_requests WHERE request_id = '" . (int)$request_id . "'");
			$this->db->query("DELETE FROM " . DB_PREFIX . "oct_price_logs WHERE request_id = '" . (int)$request_id . "'");
			return true;
		} else {
			return false;
		}
	}

	public function getRequestByToken($token) {
		$query = $this->db->query("
			SELECT pr.*, pd.name AS product_name
			FROM " . DB_PREFIX . "oct_price_requests pr
			LEFT JOIN " . DB_PREFIX . "product_description pd ON (pr.product_id = pd.product_id)
			WHERE MD5(CONCAT(pr.request_id, pr.email, pr.date_created)) = '" . $this->db->escape($token) . "'
			AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'
		");

		return $query->row;
	}

	public function unsubscribeByToken($token) {
		$request = $this->getRequestByToken($token);
		
		if ($request) {
			$this->db->query("DELETE FROM " . DB_PREFIX . "oct_price_requests WHERE request_id = '" . (int)$request['request_id'] . "'");
			$this->db->query("DELETE FROM " . DB_PREFIX . "oct_price_logs WHERE request_id = '" . (int)$request['request_id'] . "'");
			return $request;
		}
		
		return false;
	}

	public function generateUnsubscribeToken($request_id, $email, $date_created) {
		return md5($request_id . $email . $date_created);
	}

	public function getProductCurrentPrice($product_id) {
		$query = $this->db->query("
			SELECT price, special, tax_class_id 
			FROM " . DB_PREFIX . "product 
			WHERE product_id = '" . (int)$product_id . "' 
			AND status = '1'
		");

		if ($query->num_rows) {
			$product = $query->row;
			$current_price = $product['special'] ? $product['special'] : $product['price'];
			return $this->tax->calculate($current_price, $product['tax_class_id'], $this->config->get('config_tax'));
		}

		return false;
	}

	public function hasActivePriceRequests($product_id) {
		$query = $this->db->query("
			SELECT COUNT(*) as total
			FROM " . DB_PREFIX . "oct_price_requests 
			WHERE product_id = '" . (int)$product_id . "' 
			AND status = 'pending'
		");

		return $query->row['total'] > 0;
	}
}