<?php
class ModelSaleSchedule extends Model {

	public function getScheduleOrderDb($order_id = null) {
		
		$sql = "SELECT order_id, firstname, lastname, email, telephone,  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);

		$orderAffected = $this->db->countAffected() > 0 ? true : false;

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

	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) {
			
			$obs = !isset($data['observation']) || trim($data['observation']) === '' ? "NULL" : "'" . $this->db->escape($data['observation']) . "'";
		
			$sql = "UPDATE `" . DB_PREFIX . "schedule_times` SET blocked_time = ". (int)$data['flag'] .", observation = $obs 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 getScheduleWithoutOrderDB() {

		$sql = "SELECT * FROM `" . DB_PREFIX . "schedule`";

    	$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){

		$interval_seconds = $interval_to_meet * 60;
		$total_seconds_query = "SELECT TIME_TO_SEC(TIMEDIFF('$time_end', '$time_start')) AS total_seconds";
		$result = $this->db->query($total_seconds_query);
		$total_seconds = $result->row['total_seconds'];
		$num_intervals = intdiv($total_seconds, $interval_seconds);

		$sql = "SELECT TIME_FORMAT(ADDTIME('$time_start', SEC_TO_TIME(incremento * $interval_seconds)), '%H:%i') AS dividTime
			FROM (
				SELECT @row := @row + 1 AS incremento
				FROM (
					SELECT 0 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
				) t1,
				(
					SELECT 0 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
				) t2,
				(
					SELECT @row := -1
				) r
			) AS increments
			WHERE incremento <= $num_intervals
			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 
				WHERE `date` < CURDATE() 
				AND `order_id` IS NULL 
				AND `observation` IS NULL;";
				
		$response = $this->db->query($sql);

		return $response;
	}
}