<?php
/**
 * @copyright    OCTemplates
 * @support      https://octemplates.net/
 * @license      LICENSE.txt
 */
class ModelOctemplatesModuleOctPriceTracker extends Model {

	public function checkRequest($request_id) {
		$query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "oct_price_requests` WHERE request_id = '" . (int)$request_id . "'");

		return $query->row;
	}

	public function deleteRequest($request_id) {
		$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 . "'");
	}

	public function getRequests($data = array()) {
		$sql = "SELECT pr.*, pd.name AS product_name, 
				CONCAT(c.firstname, ' ', c.lastname) AS customer_name_full,
				c.customer_id,
				cur.code AS currency_code, cur.symbol_left, cur.symbol_right
				FROM " . DB_PREFIX . "oct_price_requests pr 
				LEFT JOIN " . DB_PREFIX . "product_description pd ON (pr.product_id = pd.product_id AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "') 
				LEFT JOIN " . DB_PREFIX . "customer c ON (pr.customer_id = c.customer_id)
				LEFT JOIN " . DB_PREFIX . "currency cur ON (pr.currency_id = cur.currency_id)
				WHERE 1";

		if (!empty($data['filter_email'])) {
			$sql .= " AND pr.email LIKE '" . $this->db->escape($data['filter_email']) . "%'";
		}

		if (!empty($data['filter_product'])) {
			$sql .= " AND pd.name LIKE '" . $this->db->escape($data['filter_product']) . "%'";
		}

		if (!empty($data['filter_customer'])) {
			$sql .= " AND (pr.customer_name LIKE '" . $this->db->escape($data['filter_customer']) . "%' OR CONCAT(c.firstname, ' ', c.lastname) LIKE '" . $this->db->escape($data['filter_customer']) . "%')";
		}

		if (isset($data['filter_status']) && $data['filter_status'] !== '') {
			$sql .= " AND pr.status = '" . $this->db->escape($data['filter_status']) . "'";
		}

		$sql .= " ORDER BY pr.date_created DESC";

		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}

			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}

		$query = $this->db->query($sql);

		return $query->rows;
	}

	public function getTotalRequests($data = array()) {
		$sql = "SELECT COUNT(*) as total FROM " . DB_PREFIX . "oct_price_requests pr 
				LEFT JOIN " . DB_PREFIX . "product_description pd ON (pr.product_id = pd.product_id AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "') 
				LEFT JOIN " . DB_PREFIX . "customer c ON (pr.customer_id = c.customer_id)
				LEFT JOIN " . DB_PREFIX . "currency cur ON (pr.currency_id = cur.currency_id)
				WHERE 1";

		if (!empty($data['filter_email'])) {
			$sql .= " AND pr.email LIKE '" . $this->db->escape($data['filter_email']) . "%'";
		}

		if (!empty($data['filter_product'])) {
			$sql .= " AND pd.name LIKE '" . $this->db->escape($data['filter_product']) . "%'";
		}

		if (!empty($data['filter_customer'])) {
			$sql .= " AND (pr.customer_name LIKE '" . $this->db->escape($data['filter_customer']) . "%' OR CONCAT(c.firstname, ' ', c.lastname) LIKE '" . $this->db->escape($data['filter_customer']) . "%')";
		}

		if (isset($data['filter_status']) && $data['filter_status'] !== '') {
			$sql .= " AND pr.status = '" . $this->db->escape($data['filter_status']) . "'";
		}

		$query = $this->db->query($sql);

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

	public function getPendingRequests($limit = 1000, $start = 0) {
		$sql = "SELECT pr.*, p.price 
				FROM " . DB_PREFIX . "oct_price_requests pr
				LEFT JOIN " . DB_PREFIX . "product p ON (pr.product_id = p.product_id)
				WHERE pr.status = 'pending' 
				AND p.status = '1'
				ORDER BY pr.request_id ASC";

		if ($limit > 0) {
			$sql .= " LIMIT " . (int)$start . "," . (int)$limit;
		}

		$query = $this->db->query($sql);

		return $query->rows;
	}

	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 getLogs($data = array()) {
		$sql = "SELECT pl.*, pr.email, pr.customer_name, pr.product_id, pd.name AS product_name,
				cur.code AS currency_code, cur.symbol_left, cur.symbol_right
				FROM " . DB_PREFIX . "oct_price_logs pl
				LEFT JOIN " . DB_PREFIX . "oct_price_requests pr ON (pl.request_id = pr.request_id)
				LEFT JOIN " . DB_PREFIX . "product_description pd ON (pr.product_id = pd.product_id AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "')
				LEFT JOIN " . DB_PREFIX . "currency cur ON (pr.currency_id = cur.currency_id)
				WHERE 1";

		if (!empty($data['filter_email'])) {
			$sql .= " AND pr.email LIKE '" . $this->db->escape($data['filter_email']) . "%'";
		}

		if (!empty($data['filter_product'])) {
			$sql .= " AND pd.name LIKE '" . $this->db->escape($data['filter_product']) . "%'";
		}

		if (isset($data['filter_status']) && $data['filter_status'] !== '') {
			$sql .= " AND pl.status = '" . $this->db->escape($data['filter_status']) . "'";
		}

		$sql .= " ORDER BY pl.date_sent DESC";

		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}

			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}

		$query = $this->db->query($sql);

		return $query->rows;
	}

	public function getTotalLogs($data = array()) {
		$sql = "SELECT COUNT(*) as total FROM " . DB_PREFIX . "oct_price_logs pl
				LEFT JOIN " . DB_PREFIX . "oct_price_requests pr ON (pl.request_id = pr.request_id)
				LEFT JOIN " . DB_PREFIX . "product_description pd ON (pr.product_id = pd.product_id AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "')
				LEFT JOIN " . DB_PREFIX . "currency cur ON (pr.currency_id = cur.currency_id)
				WHERE 1";

		if (!empty($data['filter_email'])) {
			$sql .= " AND pr.email LIKE '" . $this->db->escape($data['filter_email']) . "%'";
		}

		if (!empty($data['filter_product'])) {
			$sql .= " AND pd.name LIKE '" . $this->db->escape($data['filter_product']) . "%'";
		}

		if (isset($data['filter_status']) && $data['filter_status'] !== '') {
			$sql .= " AND pl.status = '" . $this->db->escape($data['filter_status']) . "'";
		}

		$query = $this->db->query($sql);

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

	public function getStatistics() {
		$statistics = array();

		$query = $this->db->query("SELECT COUNT(*) as total FROM " . DB_PREFIX . "oct_price_requests");
		$statistics['total_requests'] = $query->row['total'];

		$query = $this->db->query("SELECT COUNT(*) as total FROM " . DB_PREFIX . "oct_price_requests WHERE status = 'pending'");
		$statistics['pending_requests'] = $query->row['total'];

		$query = $this->db->query("SELECT COUNT(*) as total FROM " . DB_PREFIX . "oct_price_requests WHERE status = 'sent'");
		$statistics['sent_requests'] = $query->row['total'];

		$query = $this->db->query("SELECT COUNT(*) as total FROM " . DB_PREFIX . "oct_price_logs WHERE date_sent >= DATE_SUB(NOW(), INTERVAL 30 DAY)");
		$statistics['notifications_30_days'] = $query->row['total'];

		$query = $this->db->query("SELECT pr.product_id, pd.name, COUNT(*) as count
			FROM " . DB_PREFIX . "oct_price_requests pr
			LEFT JOIN " . DB_PREFIX . "product_description pd ON (pr.product_id = pd.product_id AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "')
			GROUP BY pr.product_id
			ORDER BY count DESC
			LIMIT 10");
		$statistics['top_products'] = $query->rows;

		return $statistics;
	}

	public function getTotalCallArray($data = []) {
		$sql = "SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "oct_price_requests`";

		if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
			$sql .= " WHERE status = '" . $this->db->escape($data['filter_status']) . "'";
		}

		$query = $this->db->query($sql);

		return $query->row['total'];
	}	
	
	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;
	}

	public function install() {
		$this->db->query("
			CREATE TABLE IF NOT EXISTS " . DB_PREFIX . "oct_price_requests (
				request_id INT(11) AUTO_INCREMENT,
				product_id INT(11) NOT NULL,
				customer_id INT(11) NULL,
				customer_name VARCHAR(255) NOT NULL,
				email VARCHAR(255) NOT NULL,
				phone VARCHAR(20) NULL,
				current_price DECIMAL(15,4) NOT NULL,
				currency_id INT(11) NOT NULL DEFAULT '1',
				language_id INT(11) NOT NULL DEFAULT '1',
				date_created DATETIME NOT NULL,
				status ENUM('pending', 'sent') NOT NULL DEFAULT 'pending',
				
				PRIMARY KEY (request_id),
				INDEX idx_product_id (product_id),
				INDEX idx_customer_id (customer_id),
				INDEX idx_status (status),
				INDEX idx_email (email),
				INDEX idx_status_product_id (status, product_id),
				INDEX idx_status_request_id (status, request_id),
				INDEX idx_product_status_created (product_id, status, date_created)
			) ENGINE=MyISAM DEFAULT CHARSET=utf8;
		");

		$this->db->query("
			CREATE TABLE IF NOT EXISTS " . DB_PREFIX . "oct_price_logs (
				log_id INT(11) AUTO_INCREMENT,
				request_id INT(11) NOT NULL,
				old_price DECIMAL(15,4) NOT NULL,
				new_price DECIMAL(15,4) NOT NULL,
				date_sent DATETIME NOT NULL,
				status ENUM('success', 'failed') NOT NULL DEFAULT 'success',
				
				PRIMARY KEY (log_id),
				INDEX idx_request_id (request_id),
				INDEX idx_date_sent (date_sent),
				INDEX idx_status_date (status, date_sent)
			) ENGINE=MyISAM DEFAULT CHARSET=utf8;
		");
	}

	public function uninstall() {
		$this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "oct_price_requests`");
		$this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "oct_price_logs`");
	}
}