drop table if exists STUDENT_KIND;
create table STUDENT_KIND (
   ID_INTERNAL int(11) not null auto_increment,
   STUDENT_TYPE int(11) not null,
   MIN_COURSES_TO_ENROL int(11) not null,
   MAX_COURSES_TO_ENROL int(11) not null,
   MAX_NAC_TO_ENROL int(11) not null,
   primary key (ID_INTERNAL),
   unique U1 (STUDENT_TYPE)
)type=InnoDB;

delete from STUDENT_KIND;
insert into STUDENT_KIND values (1, 1, 3, 7, 10);

drop table if exists ENROLMENT_EVALUATION;
create table ENROLMENT_EVALUATION (
   ID_INTERNAL int(11) not null auto_increment,
   GRADE varchar(11),
   EVALUATION_TYPE int(11) not null,
   EXAM_DATE date,
   GRADE_AVAILABLE_DATE date,
   RESPONSIBLE_PERSON int(11),
   STATE int(11) not null,
   KEY_ENROLMENT int(11) not null,
   primary key (ID_INTERNAL),
   unique U1 (KEY_ENROLMENT, EVALUATION_TYPE, GRADE)
)type=InnoDB;

drop table if exists ENROLMENT;
create table ENROLMENT (
   ID_INTERNAL int(11) not null auto_increment,
   KEY_STUDENT_CURRICULAR_PLAN int(11) not null,
   KEY_CURRICULAR_COURSE_SCOPE int(11) not null,
   KEY_EXECUTION_PERIOD int(11) not null,
   STATE int(11) not null,
   CLASS_NAME varchar(255) not null,
   KEY_CURRICULAR_COURSE_FOR_OPTION int (11),
   EVALUATION_TYPE int(11) not null,
   primary key (ID_INTERNAL),
   unique U1 (KEY_STUDENT_CURRICULAR_PLAN, KEY_CURRICULAR_COURSE_SCOPE, KEY_EXECUTION_PERIOD)
)type=InnoDB;

drop table if exists almeida_coddisc;
CREATE TABLE almeida_coddisc (
  codint int(11) NOT NULL auto_increment,
  coddis varchar(200) default NULL,
  nomedis varchar(200) default NULL,
  PRIMARY KEY  (codInt)
) TYPE=MyISAM;

DELETE FROM CURRICULAR_COURSE_EXECUTION_COURSE WHERE KEY_EXECUTION_COURSE = 34308 AND KEY_CURRICULAR_COURSE = 1551;
UPDATE CURRICULAR_COURSE_SCOPE SET KEY_CURRICULAR_COURSE = 1568 WHERE ID_INTERNAL = 1247;
DELETE FROM CURRICULAR_COURSE WHERE ID_INTERNAL = 1551;

ALTER TABLE DEGREE_CURRICULAR_PLAN ADD DEGREE_DURATION int(11);
ALTER TABLE DEGREE_CURRICULAR_PLAN ADD MINIMAL_YEAR_FOR_OPTIONAL_COURSES int(11);

ALTER TABLE CURRICULAR_COURSE_SCOPE ADD MAX_INCREMENT_NAC int(11);
ALTER TABLE CURRICULAR_COURSE_SCOPE ADD MIN_INCREMENT_NAC int(11);
ALTER TABLE CURRICULAR_COURSE_SCOPE ADD WEIGTH int(11);
ALTER TABLE CURRICULAR_COURSE ADD UNIVERSITY_CODE varchar(255);
ALTER TABLE BRANCH ADD KEY_DEGREE_CURRICULAR_PLAN int(11) NOT NULL default '0';
ALTER TABLE EXAM ADD ENROLLMENT_BEGIN_DAY date;
ALTER TABLE EXAM ADD ENROLLMENT_BEGIN_TIME time;
ALTER TABLE EXAM ADD ENROLLMENT_END_DAY date;
ALTER TABLE EXAM ADD ENROLLMENT_END_TIME time;

ALTER TABLE STUDENT CHANGE COLUMN KEY_STUDENT_GROUP_INFO KEY_STUDENT_KIND int(11) unsigned NOT NULL;
ALTER TABLE SITE CHANGE COLUMN ALTERNATIVE_SITE ALTERNATIVE_SITE VARCHAR (255);
ALTER TABLE STUDENT_CURRICULAR_PLAN ADD GIVEN_CREDITS FLOAT(10,2);

update EXECUTION_PERIOD set STATE = 'C' where STATE = 'A';
update EXECUTION_YEAR set STATE = 'C' where STATE = 'A';

DROP TABLE IF EXISTS almeida_curram;
CREATE TABLE almeida_curram (
  codInt int(11) NOT NULL auto_increment,
  codCur int(11) default NULL,
  codRam int(11) default NULL,
  codOrien int(11) default NULL,
  descri varchar(200) default NULL, 
  PRIMARY KEY  (codInt)
) TYPE=MyISAM;


DELETE FROM EXECUTION_YEAR WHERE ID_INTERNAL = 2;
INSERT INTO EXECUTION_YEAR values (2, '2003/2004', 'NO');
INSERT INTO EXECUTION_PERIOD values (2, '1 Semestre', 2, 'NO', 1);

UPDATE BRANCH SET KEY_DEGREE_CURRICULAR_PLAN = 1 WHERE KEY_DEGREE_CURRICULAR_PLAN = 0;

drop table if exists EXAM_STUDENT;
create table EXAM_STUDENT (
   ID_INTERNAL int(11) not null auto_increment,
   KEY_EXAM int(11) not null,
   KEY_STUDENT int(11) not null,
   primary key (ID_INTERNAL),
   unique U1 (KEY_EXAM, KEY_STUDENT))
   type=InnoDB;


INSERT INTO DEGREE (ID_INTERNAL, CODE, NAME, TYPE_DEGREE) VALUES (46, 'MEA', 'Engenharia Aeroespacial', 2);
INSERT INTO DEGREE (ID_INTERNAL, CODE, NAME, TYPE_DEGREE) VALUES (47, 'MCEM', 'Ciência e Engenharia de Materiais', 2);
INSERT INTO DEGREE_CURRICULAR_PLAN (ID_INTERNAL, NAME, KEY_DEGREE, STATE, INITIAL_DATE, END_DATE, KEY_DEGREE_CURRICULAR_PLAN_ENROLMENT_INFO, DEGREE_DURATION, MINIMAL_YEAR_FOR_OPTIONAL_COURSES) VALUES (46, 'MEA03/04', 46, 1, '2003-02-06', NULL, NULL, NULL, NULL);
INSERT INTO DEGREE_CURRICULAR_PLAN (ID_INTERNAL, NAME, KEY_DEGREE, STATE, INITIAL_DATE, END_DATE, KEY_DEGREE_CURRICULAR_PLAN_ENROLMENT_INFO, DEGREE_DURATION, MINIMAL_YEAR_FOR_OPTIONAL_COURSES) VALUES (47, 'MCEM03/04', 47, 1, '2003-02-06', NULL, NULL, NULL, NULL);

INSERT INTO EXECUTION_DEGREE (ID_INTERNAL, ACADEMIC_YEAR, KEY_DEGREE_CURRICULAR_PLAN, KEY_TEACHER, TEMPORARY_EXAM_MAP) VALUES (65, 2, 46, NULL, 1);
INSERT INTO EXECUTION_DEGREE (ID_INTERNAL, ACADEMIC_YEAR, KEY_DEGREE_CURRICULAR_PLAN, KEY_TEACHER, TEMPORARY_EXAM_MAP) VALUES (66, 2, 47, NULL, 1);

INSERT INTO EXECUTION_PERIOD (ID_INTERNAL, NAME, KEY_EXECUTION_YEAR, STATE, SEMESTER) VALUES (3, '1 Semestre', 1, 'NO', 1);

drop table if exists BRANCH;
create table BRANCH (
   ID_INTERNAL int(11) not null auto_increment,
   BRANCH_CODE varchar(50) not null,
   BRANCH_NAME varchar(255) not null,
   KEY_DEGREE_CURRICULAR_PLAN integer(11) not null,
   primary key (ID_INTERNAL),
   unique U1 (BRANCH_CODE, KEY_DEGREE_CURRICULAR_PLAN)
)type=InnoDB;

drop table if exists BRANCH;
create table BRANCH (
   ID_INTERNAL int(11) not null auto_increment,
   BRANCH_CODE varchar(50) not null,
   BRANCH_NAME varchar(255) not null,
   KEY_DEGREE_CURRICULAR_PLAN integer(11) not null,
   primary key (ID_INTERNAL),
   unique U1 (BRANCH_CODE, KEY_DEGREE_CURRICULAR_PLAN)
)type=InnoDB;

insert into BRANCH (BRANCH_CODE, BRANCH_NAME, KEY_DEGREE_CURRICULAR_PLAN) values ('', '', '24');
insert into BRANCH (BRANCH_CODE, BRANCH_NAME, KEY_DEGREE_CURRICULAR_PLAN) values ('', '', '25');
insert into BRANCH (BRANCH_CODE, BRANCH_NAME, KEY_DEGREE_CURRICULAR_PLAN) values ('', '', '26');
insert into BRANCH (BRANCH_CODE, BRANCH_NAME, KEY_DEGREE_CURRICULAR_PLAN) values ('', '', '27');
insert into BRANCH (BRANCH_CODE, BRANCH_NAME, KEY_DEGREE_CURRICULAR_PLAN) values ('', '', '28');
insert into BRANCH (BRANCH_CODE, BRANCH_NAME, KEY_DEGREE_CURRICULAR_PLAN) values ('', '', '29');
insert into BRANCH (BRANCH_CODE, BRANCH_NAME, KEY_DEGREE_CURRICULAR_PLAN) values ('', '', '30');
insert into BRANCH (BRANCH_CODE, BRANCH_NAME, KEY_DEGREE_CURRICULAR_PLAN) values ('', '', '31');
insert into BRANCH (BRANCH_CODE, BRANCH_NAME, KEY_DEGREE_CURRICULAR_PLAN) values ('', '', '32');
insert into BRANCH (BRANCH_CODE, BRANCH_NAME, KEY_DEGREE_CURRICULAR_PLAN) values ('', '', '33');
insert into BRANCH (BRANCH_CODE, BRANCH_NAME, KEY_DEGREE_CURRICULAR_PLAN) values ('', '', '34');
insert into BRANCH (BRANCH_CODE, BRANCH_NAME, KEY_DEGREE_CURRICULAR_PLAN) values ('', '', '35');
insert into BRANCH (BRANCH_CODE, BRANCH_NAME, KEY_DEGREE_CURRICULAR_PLAN) values ('', '', '36');
insert into BRANCH (BRANCH_CODE, BRANCH_NAME, KEY_DEGREE_CURRICULAR_PLAN) values ('', '', '37');
insert into BRANCH (BRANCH_CODE, BRANCH_NAME, KEY_DEGREE_CURRICULAR_PLAN) values ('', '', '38');
insert into BRANCH (BRANCH_CODE, BRANCH_NAME, KEY_DEGREE_CURRICULAR_PLAN) values ('', '', '39');
insert into BRANCH (BRANCH_CODE, BRANCH_NAME, KEY_DEGREE_CURRICULAR_PLAN) values ('', '', '40');
insert into BRANCH (BRANCH_CODE, BRANCH_NAME, KEY_DEGREE_CURRICULAR_PLAN) values ('', '', '41');
insert into BRANCH (BRANCH_CODE, BRANCH_NAME, KEY_DEGREE_CURRICULAR_PLAN) values ('', '', '42');
insert into BRANCH (BRANCH_CODE, BRANCH_NAME, KEY_DEGREE_CURRICULAR_PLAN) values ('', '', '43');
insert into BRANCH (BRANCH_CODE, BRANCH_NAME, KEY_DEGREE_CURRICULAR_PLAN) values ('', '', '44');
insert into BRANCH (BRANCH_CODE, BRANCH_NAME, KEY_DEGREE_CURRICULAR_PLAN) values ('', '', '45');
insert into BRANCH (BRANCH_CODE, BRANCH_NAME, KEY_DEGREE_CURRICULAR_PLAN) values ('', '', '46');
insert into BRANCH (BRANCH_CODE, BRANCH_NAME, KEY_DEGREE_CURRICULAR_PLAN) values ('', '', '47');

INSERT INTO PERSON VALUES("12635", "6854047", "LISBOA", "2000-10-24", NULL, "ANDRE VENTURA DA CRUZ MARNOTO ZUQUETE", "1965-11-06", "EDUARDO MARIA RATO MARTINS ZUQUETE", "MARIA MARGARIDA VENTURA DA C M MARTINS ZUQUETE", "", "ALVALADE", "LISBOA", "LISBOA", "R.PROF.PRADO COELHO, 28-7 FRT", "", "1600-654", "LISBOA", "LUMIAR", "LISBOA", "LISBOA", "217510313", "", "andre.zuquete@gsd.inesc-id.pt", "", "190449918", "", "F3091", "4c886f3235ba261a304a1c9a9b0ea113", "1", "LISBOA", "1", "1", "2");
INSERT INTO ass_FUNCIONARIO VALUES("1611", "12635", "3091", "0", "1970-01-01", "0", "0", "0", "0", "LISBOA", "0", "1970-01-01", "1970-01-01", "0", "1970-01-01 01:00:00");