CREATE DEFINER=`glink_tquach`@`%` TRIGGER `machine_sessions_AFTER_INSERT` AFTER INSERT ON `machine_sessions` FOR EACH ROW
BEGIN
	DECLARE message_err text CHARACTER SET utf8;
    DECLARE current_date_cp DATE;
    DECLARE uptime DATETIME;
    DECLARE downtime DATETIME;
    SET current_date_cp = DATE_FORMAT(NEW.current_date,'%Y-%m-%d');
    IF(time_to_sec(NEW.uptime) > time_to_sec(NEW.downtime)) THEN
		SET uptime = concat(current_date_cp,' ',NEW.uptime);
    	SET downtime = concat(DATE_FORMAT(DATE_ADD(current_date_cp, INTERVAL 1 DAY),'%Y-%m-%d'),' ',NEW.downtime);
	ELSE 
		IF(NEW.shift = 2)
        THEN
		SET uptime = concat(DATE_FORMAT(DATE_ADD(current_date_cp, INTERVAL 1 DAY),'%Y-%m-%d'),' ',NEW.uptime);
	    SET downtime = concat(DATE_FORMAT(DATE_ADD(current_date_cp, INTERVAL 1 DAY),'%Y-%m-%d'),' ',NEW.downtime);
        ELSE
        SET uptime = concat(current_date_cp,' ',NEW.uptime);
    	SET downtime = concat(current_date_cp,' ',NEW.downtime);
        END IF;
	END IF;
    IF ((EXISTS(SELECT * FROM machine_sessions WHERE machine_id = NEW.machine_id
		AND DATE_FORMAT(machine_sessions.current_date,'%Y-%m-%d') = DATE_FORMAT(NEW.current_date,'%Y-%m-%d') 
		AND shift = NEW.shift
        AND primary_id <> NEW.primary_id
        AND (TIME(concat(DATE_FORMAT(machine_sessions.current_date,'%Y-%m-%d'),' ',machine_sessions.uptime)) > TIME(uptime))
        AND (
			(TIME(concat(DATE_FORMAT(machine_sessions.current_date,'%Y-%m-%d'),' ',machine_sessions.downtime)) <= TIME(uptime) AND (TIME(uptime) >= TIME(downtime)))
            OR (TIME(uptime) < TIME(downtime) AND TIME(downtime) < TIME(concat(DATE_FORMAT(DATE_ADD(current_date_cp, INTERVAL 1 DAY),'%Y-%m-%d'),' ',"23:59:59")))
		)
	)) OR (EXISTS(SELECT * FROM machine_sessions WHERE machine_id = NEW.machine_id
		AND DATE_FORMAT(machine_sessions.current_date,'%Y-%m-%d') = DATE_FORMAT(NEW.current_date,'%Y-%m-%d')
        AND primary_id <> NEW.primary_id
        AND (
			(TIME(uptime) < TIME(concat(DATE_FORMAT(machine_sessions.current_date,'%Y-%m-%d'),' ',machine_sessions.uptime)) AND TIME(downtime) > UNIX_TIMESTAMP(concat(DATE_FORMAT(machine_sessions.current_date,'%Y-%m-%d'),' ',machine_sessions.downtime)))
            OR
            (TIME(uptime) < TIME(concat(DATE_FORMAT(machine_sessions.current_date,'%Y-%m-%d'),' ',machine_sessions.downtime)) AND TIME(downtime) > UNIX_TIMESTAMP(concat(DATE_FORMAT(machine_sessions.current_date,'%Y-%m-%d'),' ',machine_sessions.downtime)))
        )
    ))) AND NEW.orderdetail_id != -1
        THEN
        SET message_err = "Thời gian bắt đầu tiếp tục không hợp lệ, vui lòng kiểm tra!";
        SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = message_err;
    END IF;
END