# --------------------------------------
# --------------------------------------
DROP PROCEDURE IF EXISTS ValidateQuery;
DELIMITER //
CREATE PROCEDURE ValidateQuery(IN qNum INT, IN queryTableName VARCHAR(255))
BEGIN
	DECLARE cname VARCHAR(64);
	DECLARE done INT DEFAULT FALSE;
	DECLARE cur CURSOR FOR SELECT c.column_name FROM information_schema.columns c WHERE 
c.table_schema='movies' AND c.table_name=queryTableName;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

	# Add the column fingerprints into a tmp table
	DROP TABLE IF EXISTS cFps;
	CREATE TABLE cFps (
  	  `val` VARCHAR(50) NOT NULL
	) 
	ENGINE = InnoDB;

	OPEN cur;
	read_loop: LOOP
		FETCH cur INTO cname;
		IF done THEN
      			LEAVE read_loop;
    		END IF;
		
		DROP TABLE IF EXISTS ordered_column;
		SET @order_by_c = CONCAT('CREATE TABLE ordered_column as SELECT ', cname, ' FROM ', queryTableName, ' ORDER BY ', cname);
		PREPARE order_by_c_stmt FROM @order_by_c;
		EXECUTE order_by_c_stmt;
		
		SET @query = CONCAT('SELECT md5(group_concat(', cname, ', "")) FROM ordered_column INTO @cfp');
		PREPARE stmt FROM @query;
		EXECUTE stmt;

		INSERT INTO cFps values(@cfp);
		DROP TABLE IF EXISTS ordered_column;
	END LOOP;
	CLOSE cur;

	# Order fingerprints
	DROP TABLE IF EXISTS oCFps;
	SET @order_by = 'CREATE TABLE oCFps as SELECT val FROM cFps ORDER BY val'; 
	PREPARE order_by_stmt FROM @order_by;
	EXECUTE order_by_stmt;

	# Read the values of the result
	SET @q_yours = 'SELECT md5(group_concat(val, "")) FROM oCFps INTO @yours';
	PREPARE q_yours_stmt FROM @q_yours;
	EXECUTE q_yours_stmt;

	SET @q_fp = CONCAT('SELECT fp FROM fingerprints WHERE qnum=', qNum,' INTO @rfp');
	PREPARE q_fp_stmt FROM @q_fp;
	EXECUTE q_fp_stmt;

	SET @q_diagnosis = CONCAT('select IF(@rfp = @yours, "OK", "ERROR") into @diagnosis');
	PREPARE q_diagnosis_stmt FROM @q_diagnosis;
	EXECUTE q_diagnosis_stmt;

	INSERT INTO results values(qNum, @rfp, @yours, @diagnosis);

	DROP TABLE IF EXISTS cFps;
	DROP TABLE IF EXISTS oCFps;
END//
DELIMITER ;

# --------------------------------------

# Execute queries (Insert here your queries).

# Validate the queries
CREATE TABLE results (
  `qnum` INTEGER  NOT NULL,
  `rfp` VARCHAR(50)  NOT NULL,
  `yours` VARCHAR(50)  NOT NULL,
  `diagnosis` VARCHAR(10)  NOT NULL
)
ENGINE = InnoDB;


# -------------
# Q1
create table q as # Do NOT delete this line. Add the query below.

select m.title 
from movie m, movie_has_genre mg, genre g
where m.movie_id in(select r.movie_id 
                    from role r, actor a 
					where a.actor_id = r.actor_id and a.last_name = "Allen")
and m.movie_id = mg.movie_id and g.genre_id = mg.genre_id and g.genre_name = "Comedy";

CALL ValidateQuery(1, 'q');
# -------------


# -------------
# Q2
create table q as # Do NOT delete this line. Add the query below.

select d.last_name, m.title
from director d, movie m, movie_has_director md
where d.director_id = md.director_id and m.movie_id = md.movie_id
and m.movie_id in(select r.movie_id 
                  from role r, actor a 
				  where a.actor_id = r.actor_id and a.last_name = "Allen")
and 2 <= (select count(distinct mg.genre_id) 
          from movie_has_director md, movie_has_genre mg
          where d.director_id = md.director_id and md.movie_id = mg.movie_id);

CALL ValidateQuery(2, 'q');
# -------------


# -------------
# Q3
create table q as # Do NOT delete this line. Add the query below.

select a.last_name
from actor a
where a.actor_id in(select r.actor_id
					from role r
					where r.movie_id in(select m.movie_id
                                        from movie m, movie_has_director md, director d
                                        where m.movie_id = md.movie_id and md.director_id = d.director_id 
										and a.last_name = d.last_name))
and a.actor_id in(select r1.actor_id
				  from role r1, role r2, movie_has_genre mg1, movie_has_genre mg2
				  where r1.movie_id in(select m.movie_id
									   from movie m, movie_has_director md, director d
									   where m.movie_id = md.movie_id and md.director_id = d.director_id 
									   and a.last_name != d.last_name)
				  and r2.movie_id in(select m.movie_id
									 from movie m, movie_has_director md, director d
									 where m.movie_id = md.movie_id and md.director_id = d.director_id 
									 and a.last_name = d.last_name)
				  and r1.actor_id = a.actor_id and a.actor_id != r2.actor_id
				  and mg1.movie_id = r1.movie_id and mg2.movie_id = r2.movie_id and mg1.genre_id = mg2.genre_id);

CALL ValidateQuery(3, 'q');
# -------------


# -------------
# Q4
create table q as # Do NOT delete this line. Add the query below.

select distinct "yes" as result
from movie
where exists(select *
			 from movie m, movie_has_genre mg, genre g
			 where m.movie_id = mg.movie_id and mg.genre_id = g.genre_id and g.genre_name = "Drama" and m.year = 1995)
union
select distinct "no" as result
from movie
where not exists(select *
				 from movie m, movie_has_genre mg, genre g
				 where m.movie_id = mg.movie_id and mg.genre_id = g.genre_id and g.genre_name = "Drama" and m.year = 1995);

CALL ValidateQuery(4, 'q');
# -------------


# -------------
# Q5
create table q as # Do NOT delete this line. Add the query below.

select d1.last_name as d1, d2.last_name as d2
from director d1, director d2, movie_has_director md1, movie_has_director md2
where d1.director_id = md1.director_id and d2.director_id = md2.director_id and d1.director_id != d2.director_id
and d1.director_id > d2.director_id and md1.movie_id = md2.movie_id 
and md1.movie_id in(select m.movie_id
					from movie m
					where m.year >= 2000 and m.year <= 2006)
and d1.director_id in(select d.director_id
					  from director d, movie_has_director md
					  where d.director_id = md.director_id and 6 <= (select count(distinct mg.genre_id)
																	 from movie_has_genre mg
																	 where mg.movie_id = md.movie_id))
and d2.director_id in(select d.director_id
					  from director d, movie_has_director md
					  where d.director_id = md.director_id and 6 <= (select count(distinct mg.genre_id)
																	 from movie_has_genre mg
																	 where mg.movie_id = md.movie_id));

CALL ValidateQuery(5, 'q');
# -------------


# -------------
# Q6
create table q as # Do NOT delete this line. Add the query below.

select a.first_name, a.last_name, count(distinct d.director_id) as directors
from actor a, role r, movie_has_director md, director d
where r.actor_id = a.actor_id and 3 = (select count(*)
									   from role r
									   where r.actor_id = a.actor_id)
and r.movie_id = md.movie_id and md.director_id = d.director_id
group by a.actor_id;

CALL ValidateQuery(6, 'q');
# -------------


# -------------
# Q7
create table q as # Do NOT delete this line. Add the query below.

select g.genre_id, count(distinct md.director_id) as directors
from movie_has_director md, movie_has_genre mg, genre g
where md.movie_id = mg.movie_id and g.genre_id = mg.genre_id
and exists(select *
		   from movie_has_genre mg, movie m
		   where g.genre_id = mg.genre_id and m.movie_id = mg.movie_id
		   and 1 = (select count(*)
				    from movie_has_genre mg
				    where mg.movie_id = m.movie_id))
group by g.genre_id;

CALL ValidateQuery(7, 'q');
# -------------


# -------------
# Q8
create table q as # Do NOT delete this line. Add the query below.

select a.actor_id
from actor a
where (select count(distinct mg.genre_id)
	   from role r, movie_has_genre mg
	   where a.actor_id = r.actor_id and r.movie_id = mg.movie_id) = (select count(g.genre_id)
																	  from genre g);

CALL ValidateQuery(8, 'q');
# -------------


# -------------
# Q9
create table q as # Do NOT delete this line. Add the query below.

select g1.genre_id as g1, g2.genre_id as g2, count(distinct d.director_id) as directors
from genre g1, genre g2, director d
where g1.genre_id != g2.genre_id and g1.genre_id > g2.genre_id
and d.director_id in(select md.director_id
 				     from movie_has_genre mg, movie_has_director md
				     where mg.movie_id = md.movie_id and mg.genre_id = g1.genre_id)
and d.director_id in(select md.director_id
					 from movie_has_genre mg, movie_has_director md
					 where mg.movie_id = md.movie_id and mg.genre_id = g2.genre_id)
group by g1.genre_id, g2.genre_id;

CALL ValidateQuery(9, 'q');
# -------------


# -------------
# Q10
create table q as # Do NOT delete this line. Add the query below.

select a.actor_id, g.genre_id, count(*) as num
from actor a, genre g, movie m, role r
where not exists (select *
				  from director d, movie_has_director md
				  where d.director_id = md.director_id and md.movie_id = m.movie_id
				  and exists (select * 
							  from movie_has_genre mg, movie_has_director md
							  where d.director_id = md.director_id and mg.movie_id = md.movie_id and g.genre_id != mg.genre_id))
and a.actor_id = r.actor_id and r.movie_id = m.movie_id
group by a.actor_id, g.genre_id;

CALL ValidateQuery(10, 'q');
# -------------

DROP PROCEDURE IF EXISTS RealValue;
DROP PROCEDURE IF EXISTS ValidateQuery;
DROP PROCEDURE IF EXISTS RunRealQueries;