alter table LESSON_INSTANCE add column KEY_COURSE_LOAD int(11);
alter table LESSON_INSTANCE add index (KEY_COURSE_LOAD);

create table COURSE_LOAD_SHIFT (
	KEY_COURSE_LOAD int(11) not null, 
	KEY_SHIFT int(11) not null,  
	primary key (KEY_COURSE_LOAD, KEY_SHIFT), 
	key(KEY_COURSE_LOAD),
	key(KEY_SHIFT)
) type=InnoDB;

create table COURSE_LOAD (
  `ID_INTERNAL` int(11) NOT NULL auto_increment,
  `KEY_EXECUTION_COURSE` int(11),
  `KEY_ROOT_DOMAIN_OBJECT` int(11) NOT NULL default '1',
  `TOTAL_QUANTITY` text,
  `TYPE` text,
  `UNIT_QUANTITY` text,
  primary key (ID_INTERNAL),
  index (KEY_EXECUTION_COURSE),
  index (KEY_ROOT_DOMAIN_OBJECT)
) type=InnoDB;

select concat('insert into COURSE_LOAD (KEY_EXECUTION_COURSE, TYPE, TOTAL_QUANTITY, UNIT_QUANTITY) select ' , ID_INTERNAL , ', "TEORICA", 14 * ' , THEORETICAL_HOURS , ', round(' , THEORETICAL_HOURS , ' / (select round(count(distinct(LESSON.ID_INTERNAL))/count(distinct(SHIFT.ID_INTERNAL)),2) from SHIFT left join LESSON on SHIFT.ID_INTERNAL = LESSON.KEY_SHIFT where SHIFT.TIPO like "TEORICA" and SHIFT.KEY_DISCIPLINA_EXECUCAO = ' , ID_INTERNAL , '),2);') as "" from EXECUTION_COURSE;
select concat('insert into COURSE_LOAD (KEY_EXECUTION_COURSE, TYPE, TOTAL_QUANTITY, UNIT_QUANTITY) select ' , ID_INTERNAL , ', "TEORICO_PRATICA", 14 * ' , THEO_PRAT_HOURS , ', round(' , THEO_PRAT_HOURS , ' / (select round(count(distinct(LESSON.ID_INTERNAL))/count(distinct(SHIFT.ID_INTERNAL)),2) from SHIFT left join LESSON on SHIFT.ID_INTERNAL = LESSON.KEY_SHIFT where SHIFT.TIPO like "TEORICO_PRATICA" and SHIFT.KEY_DISCIPLINA_EXECUCAO = ' , ID_INTERNAL , '),2);') as "" from EXECUTION_COURSE;
select concat('insert into COURSE_LOAD (KEY_EXECUTION_COURSE, TYPE, TOTAL_QUANTITY, UNIT_QUANTITY) select ' , ID_INTERNAL , ', "PRATICA", 14 * ' , PRATICAL_HOURS , ', round(' , PRATICAL_HOURS , ' / (select round(count(distinct(LESSON.ID_INTERNAL))/count(distinct(SHIFT.ID_INTERNAL)),2) from SHIFT left join LESSON on SHIFT.ID_INTERNAL = LESSON.KEY_SHIFT where SHIFT.TIPO like "PRATICA" and SHIFT.KEY_DISCIPLINA_EXECUCAO = ' , ID_INTERNAL , '),2);') as "" from EXECUTION_COURSE;
select concat('insert into COURSE_LOAD (KEY_EXECUTION_COURSE, TYPE, TOTAL_QUANTITY, UNIT_QUANTITY) select ' , ID_INTERNAL , ', "LABORATORIAL", 14 * ' , LAB_HOURS , ', round(' , LAB_HOURS , ' / (select round(count(distinct(LESSON.ID_INTERNAL))/count(distinct(SHIFT.ID_INTERNAL)),2) from SHIFT left join LESSON on SHIFT.ID_INTERNAL = LESSON.KEY_SHIFT where SHIFT.TIPO like "LABORATORIAL" and SHIFT.KEY_DISCIPLINA_EXECUCAO = ' , ID_INTERNAL , '),2);') as "" from EXECUTION_COURSE;
select concat('insert into COURSE_LOAD (KEY_EXECUTION_COURSE, TYPE, TOTAL_QUANTITY, UNIT_QUANTITY) select ' , ID_INTERNAL , ', "PROBLEMS", 14 * ' , PROBLEMS_HOURS , ', round(' , PROBLEMS_HOURS , ' / (select round(count(distinct(LESSON.ID_INTERNAL))/count(distinct(SHIFT.ID_INTERNAL)),2) from SHIFT left join LESSON on SHIFT.ID_INTERNAL = LESSON.KEY_SHIFT where SHIFT.TIPO like "PROBLEMS" and SHIFT.KEY_DISCIPLINA_EXECUCAO = ' , ID_INTERNAL , '),2);') as "" from EXECUTION_COURSE;
select concat('insert into COURSE_LOAD (KEY_EXECUTION_COURSE, TYPE, TOTAL_QUANTITY, UNIT_QUANTITY) select ' , ID_INTERNAL , ', "SEMINARY", 14 * ' , SEMINARY_HOURS , ', round(' , SEMINARY_HOURS , ' / (select round(count(distinct(LESSON.ID_INTERNAL))/count(distinct(SHIFT.ID_INTERNAL)),2) from SHIFT left join LESSON on SHIFT.ID_INTERNAL = LESSON.KEY_SHIFT where SHIFT.TIPO like "SEMINARY" and SHIFT.KEY_DISCIPLINA_EXECUCAO = ' , ID_INTERNAL , '),2);') as "" from EXECUTION_COURSE;
select concat('insert into COURSE_LOAD (KEY_EXECUTION_COURSE, TYPE, TOTAL_QUANTITY, UNIT_QUANTITY) select ' , ID_INTERNAL , ', "FIELD_WORK", 14 * ' , FIELD_WORK_HOURS , ', round(' , FIELD_WORK_HOURS , ' / (select round(count(distinct(LESSON.ID_INTERNAL))/count(distinct(SHIFT.ID_INTERNAL)),2) from SHIFT left join LESSON on SHIFT.ID_INTERNAL = LESSON.KEY_SHIFT where SHIFT.TIPO like "FIELD_WORK" and SHIFT.KEY_DISCIPLINA_EXECUCAO = ' , ID_INTERNAL , '),2);') as "" from EXECUTION_COURSE;
select concat('insert into COURSE_LOAD (KEY_EXECUTION_COURSE, TYPE, TOTAL_QUANTITY, UNIT_QUANTITY) select ' , ID_INTERNAL , ', "TUTORIAL_ORIENTATION", 14 * ' , TUTORIAL_ORIENTATION_HOURS , ', round(' , TUTORIAL_ORIENTATION_HOURS , ' / (select round(count(distinct(LESSON.ID_INTERNAL))/count(distinct(SHIFT.ID_INTERNAL)),2) from SHIFT left join LESSON on SHIFT.ID_INTERNAL = LESSON.KEY_SHIFT where SHIFT.TIPO like "TUTORIAL_ORIENTATION" and SHIFT.KEY_DISCIPLINA_EXECUCAO = ' , ID_INTERNAL , '),2);') as "" from EXECUTION_COURSE;
