<?php
class ModelSaleSchedule extends Model {

	public function getScheduleOrderDb($order_id = null) {
		
		$sql = "SELECT order_id, firstname, lastname, email, DATE_FORMAT(date_added, '%d/%m/%Y %H:%i:%s') AS date_added_formatted 
        FROM `" . DB_PREFIX . "order` 
        WHERE order_id = '" . (int)$order_id['id'] . "'";

    	$order_query = $this->db->query($sql);
	
		return $order_query->row;
	}

	public function getScheduledDb($order_id = null) {
		
		$sql = "SELECT * FROM `" . DB_PREFIX . "schedule_times` INNER JOIN `" . DB_PREFIX . "schedule`
		ON `" . DB_PREFIX . "schedule_times`.`schedule_id` = `" . DB_PREFIX . "schedule`.`schedule_id` 
		WHERE order_id = '" . (int)$order_id['id'] . "'";

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

		if (empty($order_query->row)) {
			return false;
		}
		
		return $order_query->row;
	}

	public function updateOrderDb($order_id = null) { 
		
		$sql = "UPDATE `" . DB_PREFIX . "schedule_times` SET order_id = NULL WHERE order_id = '" . (int)$order_id['id'] . "'";

		$order_query = $this->db->query($sql);
	
		return $order_query;
	}

	public function updateScheduleBlockDb($data = null) { 

		foreach ($data['id'] as $key => $id) {
		
			$sql = "UPDATE `" . DB_PREFIX . "schedule_times` SET blocked_time = ". (int)$data['flag'] ." WHERE schedule_times_id = '" . (int)$id . "'";

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

			if(!$schedule_query){
				return $schedule_query;
			}	
		}
	
		return $schedule_query;
	} 

	public function updateupdateScheduleStatusDb($data = null) { 

		foreach ($data['status'] as $key => $id) {
		
			$sql = "UPDATE `" . DB_PREFIX . "schedule` SET `status` = 0 WHERE schedule_id = '" . (int)$id . "'";

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

			if(!$schedule_query){
				return $schedule_query;
			}	
		}
	
		return $schedule_query;
	} 

	public function postOrderDb($order = null){

		$sql = "UPDATE `" . DB_PREFIX . "schedule_times` SET order_id = " . (int)$order['id'] . " WHERE schedule_times_id = '" . (int)$order['schedule_times_id'] . "'";

    	$order_query = $this->db->query($sql);
	
		return $order_query;
	}

	public function getScheduleDb($schedule_id = null) {

		 if(isset($schedule_id['id'])){
			$sql = "SELECT * FROM `" . DB_PREFIX . "schedule` WHERE schedule_id = '" . $schedule_id['id'] . "'";
		}else{
			$sql = "SELECT * FROM `" . DB_PREFIX . "schedule` ORDER BY `status` DESC, date_start ASC";
		  }

    	$schedule_query = $this->db->query($sql);
	
		return $schedule_query->rows;
	}
	
	public function getScheduleTimeDb($schedule_id) {

		$sql = "SELECT *, DATE_FORMAT(`" . DB_PREFIX . "schedule_times`.`time`, '%H:%i') AS formatted_time
        FROM `" . DB_PREFIX . "schedule`
        INNER JOIN `" . DB_PREFIX . "schedule_times` ON `" . DB_PREFIX . "schedule`.`schedule_id` = `" . DB_PREFIX . "schedule_times`.`schedule_id`
        WHERE `" . DB_PREFIX . "schedule`.`schedule_id` = '" . (int)$schedule_id['id'] . "'";

		$schedule_time_query = $this->db->query($sql);
		
		return $schedule_time_query->rows;
	}

	public function getScheduleDateDb($date_start, $date_end){
		
		$sql = "SELECT DATE_ADD('$date_start', INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY) AS fullDate,
        DAY(DATE_ADD('$date_start', INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY)) AS dayNumber,
        CASE MONTH(DATE_ADD('$date_start', INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY))
            WHEN 1 THEN 'Janeiro'
            WHEN 2 THEN 'Fevereiro'
            WHEN 3 THEN 'Março'
            WHEN 4 THEN 'Abril'
            WHEN 5 THEN 'Maio'
            WHEN 6 THEN 'Junho'
            WHEN 7 THEN 'Julho'
            WHEN 8 THEN 'Agosto'
            WHEN 9 THEN 'Setembro'
            WHEN 10 THEN 'Outubro'
            WHEN 11 THEN 'Novembro'
            WHEN 12 THEN 'Dezembro'
        END AS monthName,
        CASE DAYNAME(DATE_ADD('$date_start', INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY))
            WHEN 'Monday' THEN 'Segunda'
            WHEN 'Tuesday' THEN 'Terça'
            WHEN 'Wednesday' THEN 'Quarta'
            WHEN 'Thursday' THEN 'Quinta'
            WHEN 'Friday' THEN 'Sexta'
            WHEN 'Saturday' THEN 'Sábado'
            WHEN 'Sunday' THEN 'Domingo'
        END AS dayName,
        CASE 
            WHEN DATE_ADD('$date_start', INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY) < CURDATE() THEN 0
            ELSE 1
        END AS `status`
		FROM
			(SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
			CROSS JOIN 
			(SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
			CROSS JOIN 
			(SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
		WHERE
			DATE_ADD('$date_start', INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY) BETWEEN '$date_start' AND '$date_end'
		ORDER BY
			fullDate";

		$schedule_date_query = $this->db->query($sql);
				
		return $schedule_date_query->rows;
	}

	public function getScheduleTimesDb($time_start, $time_end, $interval_to_meet){

		$sql = "SELECT TIME_FORMAT(ADDTIME('$time_start', SEC_TO_TIME((('$interval_to_meet' * increments.incremento)) * 60)),'%H:%i') 
				AS dividTime
				FROM
					(SELECT a.a + (10 * b.a) AS incremento
					FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8) AS a
					CROSS JOIN 
					(SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS b) AS increments
				WHERE
					ADDTIME('$time_start', SEC_TO_TIME((('$interval_to_meet' * increments.incremento)) * 60))<= '$time_end'
				ORDER BY
				dividTime";
					
		$schedule_times_query = $this->db->query($sql);
				
		return $schedule_times_query->rows;
	}

	public function getParcialScheduleDb($time_start, $time_end, $schedule_id){
		
		$sql = "SELECT *, DATE_FORMAT(t.time, '%H:%i') AS formatted_time
		FROM `" . DB_PREFIX . "schedule` AS s
		INNER JOIN `" . DB_PREFIX . "schedule_times` AS t ON s.schedule_id = t.schedule_id
		INNER JOIN (
			SELECT DISTINCT `date`
			FROM `" . DB_PREFIX . "schedule_times`
			WHERE `date` BETWEEN '$time_start' AND '$time_end'
		) AS sub ON t.`date` = sub.`date`
		WHERE s.schedule_id = '$schedule_id[id]'";

		$schedule_teste_query = $this->db->query($sql);
						
		return $schedule_teste_query->rows;

	}

	public function addSchedule($data = null, $availableTimes = null) {
		
		if ($data) {
			$data['date_start'] = implode( '-', array_reverse( explode( '/', $data['date_start'] ) ) );
			$data['date_end'] = implode( '-', array_reverse( explode( '/', $data['date_end'] ) ) );

			if (!isset( $data['notes'] )) $data['notes'] = '';
			if (!isset( $data['work_on_weekend'] )) $data['work_on_weekend'] = false;
			if (!isset( $data['interval_for_lunch'] )) $data['interval_for_lunch'] = null;
			if (!isset( $data['interval_for_lunch_status'] )) $data['interval_for_lunch_status'] = false;
								
			$sql = 'INSERT INTO oc_schedule SET `name` = \'' . $data['name'] . '\',
																					`status` = \'' . boolval($data['status']) . '\',
																					`type`   = \'' . $data['type'] . '\',
																					`date_start`   = \'' . $data['date_start'] . '\',
																					`date_end`   = \'' . $data['date_end'] . '\',
																					`time_start`   = \'' . $data['time_start'] . '\',
																					`time_end`   = \'' . $data['time_end'] . '\',
																					`work_on_weekend`   = \'' . boolval($data['work_on_weekend']) . '\', 
																					`interval_to_meet`   = \'' . $data['interval_to_meet'] . '\', 
																					`interval_for_lunch_status`   = \'' . boolval($data['interval_for_lunch_status']) . '\', 
																					`interval_for_lunch`   = \'' . $data['interval_for_lunch'] . '\', 
																					`notes`   = \'' . $data['notes'] . '\' ;';
																						
			$response_add_schedule = $this->db->query($sql);

			if (!$response_add_schedule) return false;

			$ID = $this->db->query( 'SELECT LAST_INSERT_ID() as id;' )->row;
			$response = $this->addScheduleDatetimes($availableTimes, $ID['id']);

			if ($response) {
				return $return = [
					'status' => true,
					'schedule_id' => $ID['id'],
				];
			}	

		} else {
			return false;
		}
	}

	public function addScheduleDatetimes($availableTimes = null, $ID = null) {

		if ($availableTimes && $ID) {

			foreach ($availableTimes as $datetime) {
				$datetime['date'] = implode( '-', array_reverse( explode( '/', $datetime['date'] ) ) );
				$datetime['isLunchTime'] == 'true' ? $datetime['isLunchTime'] = true : $datetime['isLunchTime'] = false;
				$datetime['isWeekend'] == 'true' ? $datetime['isWeekend'] = true : $datetime['isWeekend'] = false;
				
				$sql = 'INSERT INTO oc_schedule_times SET `date` = \'' . $datetime['date'] . '\',
																									`time` = \'' . $datetime['time'] . '\',
																									`blocked_time`   = \'' . $datetime['isLunchTime'] . '\',
																									`weekend`   = \'' . $datetime['isWeekend'] . '\',
																									`schedule_id`   = \'' . $ID . '\';';
			
				$this->db->query($sql);
			}

			return true;

		} else {
			return false;
		}
	}

	public function editSchedule($data = null) {
		
		if ($data) {
			if (!isset( $data['notes'] )) $data['notes'] = '';
			
			$sql = "SELECT COUNT(*) as 'length' FROM `" . DB_PREFIX . "schedule` 
							INNER JOIN `" . DB_PREFIX . "schedule_times` 
							ON `" . DB_PREFIX . "schedule`.`schedule_id` = `" . DB_PREFIX . "schedule_times`.`schedule_id` 
							WHERE `" . DB_PREFIX . "schedule`.`schedule_id` = '" . (int)$data['schedule_id'] . "' 
							AND `" . DB_PREFIX . "schedule_times`.`order_id` IS NOT NULL";

			$results = $this->db->query($sql);
			$length = $results->row['length'];
	
			$schedule_has_order = (int)$length > 0 ?? false;

			if ($schedule_has_order == false) {

				$sql = 'UPDATE oc_schedule SET 	`name` = \'' . $data['name'] . '\',
																				`status` = \'' . boolval($data['status']) . '\',
																				`type`   = \'' . $data['type'] . '\',
																				`notes`   = \'' . $data['notes'] . '\'
																	WHERE `schedule_id` = ' . (int)$data['schedule_id'] . ';';
																						
				$response = $this->db->query($sql);
				
				if ($response == false) {
					return $response = [
						'message' => 'Ocorreu um erro no processo de edição da agenda!',
						'type' => 'warning',
					];	
				}
				
			
				return $result = [
					'message' => 'Agenda editada com sucesso!',
					'type' => 'success',
				];	
			
			}
	
			return $schedule_has_order = [
				'message' => 'Não foi possível editar a agenda. Contém registro de agendamento!',
				'type' => 'error',
			];

			
		} else {
			return false;
		}
	}

	public function deleteScheduleHasNotOrder($schedule_id) {
		$sql = "SELECT COUNT(*) as 'length' FROM `" . DB_PREFIX . "schedule` 
							INNER JOIN `" . DB_PREFIX . "schedule_times` 
							ON `" . DB_PREFIX . "schedule`.`schedule_id` = `" . DB_PREFIX . "schedule_times`.`schedule_id` 
							WHERE `" . DB_PREFIX . "schedule`.`schedule_id` = '" . (int)$schedule_id . "' 
							AND `" . DB_PREFIX . "schedule_times`.`order_id` IS NOT NULL";

		$results = $this->db->query($sql);
		$length = $results->row['length'];

		$schedule_has_order = (int)$length > 0 ?? false;

		if ($schedule_has_order == false) {

			$sql = "DELETE FROM `" . DB_PREFIX . "schedule_times` WHERE `schedule_id` = " . (int)$schedule_id;
			$response = $this->db->query($sql);
			
			if ($response == false) {
				return $response = [
					'message' => 'Ocorreu um erro no processo de deleção de horários da agenda!',
					'type' => 'warning',
				];	
			}
			
			$sql = "DELETE FROM `" . DB_PREFIX . "schedule` WHERE `schedule_id` = " . (int)$schedule_id;
			$response = $this->db->query($sql);

			if ($response == false) {
				return $result = [
					'message' => 'Ocorreu um erro no processo de deleção da agenda!',
					'type' => 'warning',
				];	
			}

			return $result = [
				'message' => 'Agenda excluída com sucesso!',
				'type' => 'success',
			];	
		
		}

		return $schedule_has_order = [
			'message' => 'Não foi possível excluir a agenda. Contém registro de agendamento!',
			'type' => 'error',
		];
	}

	public function deleteOlderScheduleDatetimes() {
		$sql = "DELETE FROM oc_schedule_times ost 
						WHERE ost.`date` < CURDATE() 
						AND ost.`order_id` IS NULL;";
		$response = $this->db->query($sql);

		return $response;
	}
}