
-- Creating the BigMash table.
CREATE TEMPORARY TABLE RAW_MASH (
		OID_YSSSC BIGINT UNSIGNED,
		OID_EY BIGINT UNSIGNED,
		OID_SSC BIGINT UNSIGNED,
		OID_REG BIGINT UNSIGNED,
		OID_STU BIGINT UNSIGNED,
		SSC_NAME VARCHAR(150)
	);


-- Populating the BigMash table.
INSERT INTO RAW_MASH
	SELECT YEAR_STUDENT_SPECIAL_SEASON_CODE.OID AS OID_YSSSC,
		EXECUTION_INTERVAL.OID AS OID_EY,
		SPECIAL_SEASON_CODE.OID AS OID_SSC,
		REGISTRATION.OID AS OID_REG,
		STUDENT.OID AS OID_STU,
		SPECIAL_SEASON_CODE.SITUATION AS SSC_NAME
	FROM (
		(((YEAR_STUDENT_SPECIAL_SEASON_CODE INNER JOIN EXECUTION_INTERVAL ON YEAR_STUDENT_SPECIAL_SEASON_CODE.OID_EXECUTION_YEAR = EXECUTION_INTERVAL.OID)
		INNER JOIN SPECIAL_SEASON_CODE ON YEAR_STUDENT_SPECIAL_SEASON_CODE.OID_SPECIAL_SEASON_CODE = SPECIAL_SEASON_CODE.OID)
		INNER JOIN REGISTRATION ON YEAR_STUDENT_SPECIAL_SEASON_CODE.OID_STUDENT = REGISTRATION.OID)
		INNER JOIN STUDENT ON REGISTRATION.OID_STUDENT = STUDENT.OID
	);


-- Creating the Translation table.
CREATE TEMPORARY TABLE SSC2STATUTE (
		SSC_NAME VARCHAR(150),
		STATUTE_NAME VARCHAR(255)
	);



-- Populating the Translation table.
INSERT INTO SSC2STATUTE(SSC_NAME, STATUTE_NAME)
	VALUES
		('Finalistas', 'SENIOR'),
		('Trabalhadores Estudantes', 'WORKING_STUDENT'),
		('Desportistas de Alta Competição', 'PROFESSIONAL_ATHLETE'),
		('Portadores de deficiência Física ou Sensorial', 'HANDICAPPED'),
		('Parturientes ou Cônjuges', 'PARTURIENT_OR_SPOUSE'),
		('Conselho Directivo', 'DIRECTIVE_COUNCIL'),
		('Direcção da AEIST', 'AEIST_ASSOCIATIVE_LEADER'),
		('Dirigentes Associativos Juvenis', 'ASSOCIATIVE_LEADER'),
		('Mesa Assembleia de Representantes', 'ASSEMBLY_OF_REPRESENTATIVES'),
		('Comissão Executiva do Conselho Pedagógico', 'PEDAGOCICAL_COUNCIL_EXECUTIVE_BOARD'),
		('Mesa da Assembleia Geral e Conselho Fiscal e Disciplinar da AEIST', 'AEIST_FISCAL_COUNCIL_AND_GENERAL_ASSEMBLY_SECRETARIES'),
		('Comissões da Assembleia de Representantes', 'ASSEMBLY_OF_REPRESENTATIVES_COMMITTEES'),
		('Comissão Coordenadora do Conselho Pedagógico', 'PEDAGOGICAL_COUNCIL_COORDINATING_COMMITTEE'),
		('Bolseiros do IST', 'IST_GRANT_OWNER'),
		('Secções Autónomas da AEIST', 'AEIST_AUTONOMOUS_SECTIONS'),
		('Desportistas do IST', 'IST_ATHLETE'),
		('Tunas do IST', 'IST_TUNA_MEMBER'),
		('Academias Militares', 'SPECIAL_SEASON_GRANTED_BY_MILITARY_PROTOCOL'),
		('Atestados Médicos', 'SPECIAL_SEASON_GRANTED_BY_MEDICAL_JUSTIFICATION'),
		('Regime de transição da LEGI para Tagus Park', 'SPECIAL_SEASON_GRANTED_BY_LEGI_TRANSITION_REGIME'),
		('Outros (requerimentos)', 'SPECIAL_SEASON_GRANTED_BY_REQUEST');


-- Useless code for ESs picking functions from an EY
/*
DROP FUNCTION IF EXISTS pickBegin;
delimiter $$
CREATE FUNCTION pickBegin(OID_EY BIGINT)
	RETURNS BIGINT
BEGIN
	SELECT *
	INTO YEAR_SEMESTERS_TUPLE
	FROM EXECUTION_INTERVAL
	WHERE OID_EXECUTION_YEAR = OID_EY;
	
	RETURN (SELECT OID
			FROM YEAR_SEMESTER_TUPLE
			WHERE NAME = '1 Semestre');
END
delimiter ;


DROP FUNCTION IF EXISTS pickEnd;
delimiter $$
CREATE FUNCTION pickEnd(OID_EY BIGINT)
	RETURNS BIGINT
BEGIN
	SELECT *
	INTO YEAR_SEMESTERS_TUPLE
	FROM EXECUTION_INTERVAL
	WHERE OID_EXECUTION_YEAR = OID_EY;
	
	RETURN (SELECT OID
			FROM YEAR_SEMESTER_TUPLE
			WHERE NAME = '2 Semestre');
END
delimiter ;
*/


-- Creating the table containing the new records.
CREATE TEMPORARY TABLE NEW_STAT_RECORDS (
		STATUTE_TYPE VARCHAR(255),
		OID_BEGIN_EXECUTION_PERIOD BIGINT UNSIGNED,
		OID_END_EXECUTION_PERIOD BIGINT UNSIGNED,
		OID_ROOT_DOMAIN_OBJECT BIGINT UNSIGNED,
		OID_STUDENT BIGINT UNSIGNED
	);



-- Joining the BigMash with the Translation table.
INSERT INTO NEW_STAT_RECORDS(STATUTE_TYPE, OID_BEGIN_EXECUTION_PERIOD, OID_END_EXECUTION_PERIOD, OID_STUDENT)
	SELECT SSC2STATUTE.STATUTE_NAME AS STATUTE_TYPE,
		(SELECT OID
		 FROM EXECUTION_INTERVAL
		 WHERE OID_EXECUTION_YEAR = RAW_MASH.OID_EY AND NAME = '1 Semestre') AS OID_BEGIN_EXECUTION_PERIOD,
		 (SELECT OID
		 FROM EXECUTION_INTERVAL
		 WHERE OID_EXECUTION_YEAR = RAW_MASH.OID_EY AND NAME = '2 Semestre') AS OID_END_EXECUTION_PERIOD,
		 RAW_MASH.OID_STU AS OID_STUDENT
		
	FROM (
		RAW_MASH INNER JOIN SSC2STATUTE ON RAW_MASH.SSC_NAME = SSC2STATUTE.SSC_NAME
	);


-- Adding the OID_ROOT_DOMAIN_OBJECT value to all new records.
UPDATE NEW_STAT_RECORDS SET OID_ROOT_DOMAIN_OBJECT = (SELECT OID FROM ROOT_DOMAIN_OBJECT);


-- Saving an index to later know from where were the new entries added.
SET @last_entry = (select max(ID_INTERNAL) from STUDENT_STATUTE);

-- Generating the CREATION_DATE.
SET @created_just_now = now();


-- Inserting the newly created records as new STUDENT_STATUTE entries.
INSERT INTO STUDENT_STATUTE(STATUTE_TYPE, OID_BEGIN_EXECUTION_PERIOD, OID_END_EXECUTION_PERIOD, OID_ROOT_DOMAIN_OBJECT, OID_STUDENT, CREATION_DATE)
	SELECT STATUTE_TYPE, OID_BEGIN_EXECUTION_PERIOD, OID_END_EXECUTION_PERIOD, OID_ROOT_DOMAIN_OBJECT, OID_STUDENT, @created_just_now
	FROM NEW_STAT_RECORDS;

	
-- Setting the OIDs for all new entries.
UPDATE STUDENT_STATUTE, FF$DOMAIN_CLASS_INFO
	SET STUDENT_STATUTE.OID = (FF$DOMAIN_CLASS_INFO.DOMAIN_CLASS_ID << 32) + STUDENT_STATUTE.ID_INTERNAL
	WHERE FF$DOMAIN_CLASS_INFO.DOMAIN_CLASS_NAME = 'net.sourceforge.fenixedu.domain.student.StudentStatute'
		AND STUDENT_STATUTE.ID_INTERNAL > @last_entry; 
