/*1.	

DROP PROCEDURE IF EXISTS getPathToRoot;
DELIMITER $
CREATE PROCEDURE getPathToRoot(leafId INT)
BEGIN
	DECLARE bname VARCHAR(250);
	DECLARE btitle VARCHAR(250);
	DECLARE parentId INT;

	SELECT branch INTO parentId FROM business WHERE id=leafId;
	SELECT title INTO btitle FROM business WHERE id=leafId;
	SELECT businessname INTO bname FROM business WHERE id=leafId;

	WHILE parentId IS NOT NULL
	DO
		SELECT bname, btitle;
		SELECT businessname INTO bname FROM business WHERE id=parentId;
		SELECT title INTO btitle FROM business WHERE id=parentId;
		SELECT branch INTO parentId FROM business WHERE id=parentId;

	END WHILE;

	SELECT bname, btitle;
END$
DELIMITER ;
CALL getPathToRoot(54879);

/* 2. 

DROP PROCEDURE IF EXISTS getAuditorsPerBusiness;
DELIMITER $
CREATE PROCEDURE getAuditorsPerBusiness(doyId INT)
BEGIN
	DECLARE bid INT;
	DECLARE not_found INT;
	
	DECLARE bcursor CURSOR FOR
	SELECT id FROM business 
	WHERE doy_id=doyId;
	
	DECLARE CONTINUE HANDLER FOR NOT FOUND
	SET not_found=1;
	
	SET not_found=0;
	OPEN bcursor;
	
	REPEAT
		FETCH bcursor INTO bid;
		IF(not_found=0)
		THEN
			SELECT id, businessname, title FROM business WHERE id=bid;
			SELECT name, afm, soel, type
			FROM auditor_check
			INNER JOIN auditor ON afm_auditor=person_afm
			INNER JOIN person ON person_afm=afm
			WHERE bus_id=bid;
		END IF;
	UNTIL(not_found=1)
	END REPEAT;
	
	CLOSE bcursor;
END$
DELIMITER ;
CALL getAuditorsPerBusiness(3);



/* 3. 

DROP PROCEDURE IF EXISTS deleteBusiness;

DELIMITER $

CREATE PROCEDURE deleteBusiness(IN bid INT)
BEGIN
	DECLARE parent INT;
	DECLARE child INT;
	DECLARE not_found INT;
	
	DECLARE bcursor CURSOR FOR
	SELECT id FROM business WHERE branch=bid;
	
	DECLARE CONTINUE HANDLER FOR NOT FOUND
	SET not_found=1;
	
	SELECT branch INTO parent FROM business WHERE id=bid;
	
	SET not_found=0;
	OPEN bcursor;
	
	REPEAT
		FETCH bcursor INTO child;
		IF(not_found=0)
		THEN
			UPDATE business SET branch=parent WHERE id=child;
		END IF;
	UNTIL(not_found=1)
	END REPEAT;
	
	DELETE FROM business WHERE id=bid;
	
END$

DELIMITER ;

SELECT child.id AS ChildId, parent.id AS ParentId
FROM business AS child
LEFT JOIN business AS parent ON child.branch=parent.id;
CALL deleteBusiness(54876);
SELECT child.id AS ChildId, parent.id AS ParentId
FROM business AS child
LEFT JOIN business AS parent ON child.branch=parent.id;

/* 4. Note: 45.00 για το 45% -> 45.00, not 0.45.
*/ 

DROP PROCEDURE IF EXISTS handlePercentages;
DELIMITER $

CREATE PROCEDURE handlePercentages(bid INT)
BEGIN
	DECLARE totalPerc FLOAT(5,2);
	DECLARE afmToErase CHAR(9);
	DECLARE currPerc FLOAT(5,2);
	DECLARE currAfm CHAR(9);
	DECLARE currDiff FLOAT(5,2);
	DECLARE minDiff FLOAT(5,2);
	
	DECLARE not_found INT;
	DECLARE share_cursor CURSOR FOR
	SELECT afm_shareholder, percentage FROM share WHERE  bus_id=bid;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found = 1;
	
	SET totalPerc=0.0;
	SELECT SUM(percentage) INTO totalPerc
	FROM share WHERE bus_id=bid;
		
	SELECT totalPerc as 'Arxiko Synoliko Pososto';
	
	IF(totalPerc>100.0) THEN
		
		WHILE(totalPerc>100.0) DO
			SET minDiff = totalPerc+1;
			SET afmToErase=NULL;
			
			SET not_found=0;
			OPEN share_cursor;
	
			REPEAT
				SET currAfm=NULL;
				SET currPerc=0.0;
				FETCH share_cursor INTO currAfm, currPerc;
				IF(not_found=0)
				THEN
					SET currDiff = ABS(100.0 - (totalPerc-currPerc));
					IF( currDiff < minDiff) THEN
						SET afmToErase = currAfm;
						SET minDiff = currDiff;
						/*SELECT afmToErase as 'Selected Afm',currPerc as 'Pososto' ,minDiff as 'Diafora';*/
					END IF;
				END IF;
			UNTIL(not_found=1)
			END REPEAT; 
	
			CLOSE share_cursor;
			
			SELECT percentage INTO currPerc FROM share WHERE afm_shareholder=afmToErase AND bus_id=bid;
			SELECT afmToErase as 'Deleting AFM', currPerc as 'Deleting Percentage';
			
			DELETE FROM share WHERE afm_shareholder=afmToErase AND bus_id=bid;
			
			SELECT SUM(percentage) INTO totalPerc
			FROM share WHERE bus_id=bid;
			SELECT totalPerc as 'Neo Synoliko Pososto';
		END WHILE;
	END IF;
	
END$

DELIMITER ;

CALL handlePercentages(18777);




/* 1. 

DROP TRIGGER IF EXISTS checkEmail;

DELIMITER $

CREATE TRIGGER checkEmail BEFORE INSERT ON person
FOR EACH ROW 
BEGIN
	IF(NEW.email IS NOT NULL AND NEW.email NOT LIKE '%_@%_.%_')
	THEN
		INSERT INTO doy(name) VALUES (NULL);
	END IF;
END$

DELIMITER ;

/* 2. 
DROP TRIGGER IF EXISTS checkBusinessTitle;

DELIMITER $

CREATE TRIGGER checkBusinessTitle BEFORE INSERT ON belongs
FOR EACH ROW
BEGIN
	DECLARE sameTitleCount INT;
	SET sameTitleCount=0;
	
	SELECT COUNT(*) INTO sameTitleCount 
	FROM business
	INNER JOIN belongs ON belongs.bus_id=business.id
	WHERE ch_name=NEW.ch_name
	AND business.title IN (
		SELECT title FROM business 
		WHERE id=NEW.bus_id
	);
	
	IF(sameTitleCount>0) THEN
		INSERT INTO chamber(name) VALUES (NULL);
	END IF;
END$
DELIMITER ;


INSERT INTO business VALUES 
('Tzatzadaki Maria', 'Tzatzadakis','1998-08-04 00:00',31202,'Patra',20100,180,NULL,3);
INSERT INTO belongs VALUES(31202,'Epimelitirio Axaias');

/* 3. 

Σημείωση: Δείτε τις συναρτήσεις για Date και Time της MySQL (http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html)*/ 

DROP TRIGGER IF EXISTS checkAuditDates;

DELIMITER $
CREATE TRIGGER checkAuditDates BEFORE INSERT ON auditor_check
FOR EACH ROW
BEGIN
	DECLARE currDiff INT;
	
	SET currDiff = 0;
	
	SET currDiff=DATEDIFF(NEW.startdate,NEW.enddate);
	IF(currDiff<7) THEN
		SET NEW.enddate=NEW.startdate + INTERVAL 7 DAY;
	END IF;
END$
DELIMITER ;

SELECT * FROM auditor_check;
INSERT INTO auditor_check VALUES 
(31201,'200000333','2012-12-11 00:00','2012-12-13 00:00');