-- Part 1. Execute the following statements;

CREATE DATABASE pstest;
USE pstest;

CREATE TABLE Person (
	id INT NOT NULL PRIMARY KEY,
	fullname VARCHAR(128) NOT NULL,
	departmentid INT NOT NULL
);

CREATE TABLE Department (
	id INT NOT NULL PRIMARY KEY,
	name VARCHAR(128) NOT NULL
);

INSERT INTO Department(id, name) 
VALUES (1, 'Dept1'), (2, 'Dept2'), (3, 'Dept3');

INSERT INTO Person (id, fullname, departmentid)
VALUES (1, 'Alice', 1), (2, 'Bob', 2), (3, 'Carol', 3);

CREATE PROCEDURE `pstest_procedure`()
BEGIN
DROP TEMPORARY TABLE IF EXISTS PersonDepartment;
CREATE TEMPORARY TABLE PersonDepartment(
	personid INT NOT NULL,
	personfullname VARCHAR(128) NOT NULL,
	departmentid INT NOT NULL,
	departmentname VARCHAR(128) NOT NULL);

PREPARE pstest FROM 
'INSERT INTO PersonDepartment(personid, personfullname, departmentid, departmentname)
SELECT Person.id AS Person_ID, 
        Person.fullname AS Person_Name,
        Department.id AS Department_ID,
	  Department.name AS Department_Name
	  FROM Person INNER JOIN Department ON Person.departmentid = Department.id
WHERE Person.id <= 1000';

EXECUTE pstest;
SELECT * FROM PersonDepartment;
DEALLOCATE PREPARE pstest;
END;

CREATE TABLE MyTable(
	id INT NOT NULL,
	name VARCHAR(128) NOT NULL);
	
CREATE VIEW v_mytable AS SELECT id, name from MyTable;

INSERT INTO MyTable(id, name) VALUES(1, 'Mike'), (2, 'John'), (3, 'Alice');


CREATE PROCEDURE failing_procedure()
BEGIN
	PREPARE stmt3 FROM 'SELECT * FROM v_mytable';
	EXECUTE stmt3;
	DEALLOCATE PREPARE stmt3;
END;

-- Part 2. Run code from the file fragment.cpp thousands of times;

-- Part 3. Run the code below:

/*-------------------------------------------*/

CALL failing_procedure();
