/*
    Cria a Base de dados caso não exista, com os seus filegroups repartidos
    pelos 6 discos existentes;
*/
IF NOT EXISTS (SELECT name FROM master.sys.DATABASES WHERE name = 'bd1_PedroSantos_12551_EmanuelTeixeira_12552')
    CREATE DATABASE bd1_PedroSantos_12551_EmanuelTeixeira_12552
    ON PRIMARY
  ( NAME=database_Primary,
    FILENAME=
       'C:\BD2\database_Prm.mdf',
    SIZE=8MB,
    MAXSIZE=128MB,
    FILEGROWTH=64MB),
FILEGROUP database_FG1
 ( NAME = database_FG1_Dat1,
    FILENAME =
       'F:\BD2\database_FG1_1.ndf',
    SIZE = 8MB,
    FILEGROWTH=64MB),
     ( NAME = database_FG1_Dat2,
    FILENAME =
       'G:\BD2\database_FG1_2.ndf',
    SIZE = 8MB,
    FILEGROWTH=64MB),
FILEGROUP database_FG2
     ( NAME = database_FG2_Dat1,
    FILENAME =
       'H:\BD2\database_FG2_1.ndf',
    SIZE = 4MB,
    FILEGROWTH=8MB),
  ( NAME = database_FG2_Dat2,
    FILENAME =
       'I:\BD2\database_FG2_2.ndf',
    SIZE = 4MB,
    FILEGROWTH=8MB),
     ( NAME = database_FG2_Dat3,
    FILENAME =
       'J:\BD2\database_FG2_3.ndf',
    SIZE = 4MB,
    FILEGROWTH=2MB)
LOG ON
  ( NAME=database_log,
    FILENAME =
       'K:\BD2\database.ldf',
    SIZE=4MB,
    FILEGROWTH=2MB);
GO
/*
    Fim de criar base de dados
*/

/*
    Cria os logins ao nivel do servidor
*/
USE master

IF NOT EXISTS (SELECT * FROM sys.syslogins WHERE name = 'Administrador')
    BEGIN
        CREATE LOGIN Administrador WITH PASSWORD = 'admin',
        DEFAULT_DATABASE = bd1_PedroSantos_12551_EmanuelTeixeira_12552;
    END
GO

IF NOT EXISTS (SELECT * FROM sys.syslogins WHERE name = 'Aluno')
    BEGIN
        CREATE LOGIN Aluno WITH PASSWORD = 'aluno',
        DEFAULT_DATABASE = bd1_PedroSantos_12551_EmanuelTeixeira_12552;
    END
GO

IF NOT EXISTS (SELECT * FROM sys.syslogins WHERE name = 'Docente')
    BEGIN
        CREATE LOGIN Docente WITH PASSWORD = 'docente',
        DEFAULT_DATABASE = bd1_PedroSantos_12551_EmanuelTeixeira_12552;
    END
GO

IF NOT EXISTS (SELECT * FROM sys.syslogins WHERE name = 'Coordenador')
    BEGIN
        CREATE LOGIN Coordenador WITH PASSWORD = 'coordenador',
        DEFAULT_DATABASE = bd1_PedroSantos_12551_EmanuelTeixeira_12552;
    END
GO

IF NOT EXISTS (SELECT * FROM sys.syslogins WHERE name = 'GIVA')
    BEGIN
        CREATE LOGIN GIVA WITH PASSWORD = 'giva',
        DEFAULT_DATABASE = bd1_PedroSantos_12551_EmanuelTeixeira_12552;
    END
GO
/*
    Fim de criar logins ao nivel do servidor
*/

/*
    Cria os users ao nivel da base de dados
*/
USE bd1_PedroSantos_12551_EmanuelTeixeira_12552

IF NOT EXISTS (SELECT * FROM bd1_PedroSantos_12551_EmanuelTeixeira_12552.sys.sysusers WHERE name = 'Administrador')
    BEGIN
        CREATE USER Administrador FOR LOGIN Administrador;
        EXEC sp_addrolemember N'db_owner', N'Administrador';
    END
GO

IF NOT EXISTS (SELECT * FROM bd1_PedroSantos_12551_EmanuelTeixeira_12552.sys.sysusers WHERE name = 'Aluno')
    BEGIN
        CREATE USER Aluno FOR LOGIN Aluno;
    END
GO

IF NOT EXISTS (SELECT * FROM bd1_PedroSantos_12551_EmanuelTeixeira_12552.sys.sysusers WHERE name = 'Docente')
    BEGIN
        CREATE USER Docente FOR LOGIN Docente;
    END
GO

IF NOT EXISTS (SELECT * FROM bd1_PedroSantos_12551_EmanuelTeixeira_12552.sys.sysusers WHERE name = 'Coordenador')
    BEGIN
        CREATE USER Coordenador FOR LOGIN Coordenador;
    END
GO

IF NOT EXISTS (SELECT * FROM bd1_PedroSantos_12551_EmanuelTeixeira_12552.sys.sysusers WHERE name = 'GIVA')
    BEGIN
        CREATE USER GIVA FOR LOGIN GIVA;
        EXEC sp_addrolemember N'db_datareader', N'GIVA';
        EXEC sp_addrolemember N'db_backupoperator', N'GIVA';
    END
GO
/*
    Fim de Criar os users ao nivel da base de dados
*/

/*
    Criar Scheemas
*/
    USE bd1_PedroSantos_12551_EmanuelTeixeira_12552;
GO

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'Propostas')
BEGIN
    EXEC( 'CREATE SCHEMA Propostas AUTHORIZATION Docente' );
    GRANT SELECT ON SCHEMA::Propostas TO Aluno
    GRANT SELECT, UPDATE, DELETE, INSERT ON SCHEMA::Propostas TO Coordenador
END
GO

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'Avaliacao')
BEGIN
    EXEC('CREATE SCHEMA Avaliacao AUTHORIZATION Coordenador');
    GRANT SELECT, INSERT, DELETE, ALTER ON SCHEMA::Avaliacao TO Docente
    DENY INSERT, DELETE, ALTER ON SCHEMA::Avaliacao TO Aluno
    GRANT SELECT, UPDATE, DELETE, INSERT ON SCHEMA::Avaliacao TO GIVA
END

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'Coordenacao')
BEGIN
    EXEC( 'CREATE SCHEMA Coordenacao AUTHORIZATION Coordenador');
    GRANT SELECT, UPDATE, DELETE, INSERT ON SCHEMA::Coordenacao TO GIVA
END

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'GIVA')
BEGIN
    EXEC( 'CREATE SCHEMA GIVA AUTHORIZATION GIVA');
END
/*
    Fim de criar Scheemas
*/

/*
    Criar Tabelas da BD
*/
CREATE TABLE GIVA.Cidade(
    cod_cidade SMALLINT IDENTITY(1, 1) PRIMARY KEY,
    nome_cidade NVARCHAR(30) NOT NULL,
    pais_cidade VARCHAR(30) NOT NULL
)ON database_FG1;
GO

CREATE TABLE GIVA.InstituicaoEnsino(
    cod_instituicao_ensino SMALLINT IDENTITY(1,1) PRIMARY KEY,
    cod_cidade SMALLINT NOT NULL
        FOREIGN KEY(cod_cidade)
        REFERENCES GIVA.Cidade
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    nome_instituicao_ensino NVARCHAR(100) NOT NULL,
    morada_instituicao_ensino VARCHAR(100) NOT NULL,
    cod_postal_instituicao_ensino VARCHAR(30) NOT NULL,
    numero_telefone_instituicao_ensino VARCHAR(30) NOT NULL,
    email_instituicao_ensino VARCHAR(100) NOT NULL,
    website_instituicao_ensino VARCHAR(100)
)ON database_FG2;
GO

CREATE TABLE GIVA.Escola(
    cod_escola SMALLINT IDENTITY(1,1) PRIMARY KEY,
    cod_instituicao_ensino SMALLINT NOT NULL,
        FOREIGN KEY (cod_instituicao_ensino)
        REFERENCES GIVA.InstituicaoEnsino
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    nome_escola VARCHAR(100) NOT NULL,
    morada_escola VARCHAR(100) NOT NULL,
    cod_postal_escola VARCHAR(30) NOT NULL,
    numero_telefone_escola VARCHAR(30) NOT NULL,
    email_escola VARCHAR(100) NOT NULL
    )ON database_FG2;
GO

CREATE TABLE GIVA.Curso(
    cod_curso SMALLINT PRIMARY KEY,
    cod_escola SMALLINT NOT NULL,
        FOREIGN KEY(cod_escola)
        REFERENCES GIVA.Escola
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    nome_curso VARCHAR(100) NOT NULL
)ON database_FG2;
GO

CREATE TABLE Coordenacao.CoordenadorEstagiosProjetos(
    cod_coordenador_estagios_projetos SMALLINT IDENTITY(1, 1) PRIMARY KEY,
    cod_curso SMALLINT NOT NULL,
        FOREIGN KEY(cod_curso)
        REFERENCES GIVA.Curso
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    nome_coordenador_estagios_projetos NVARCHAR(100) NOT NULL,
    data_nascimento_coordenador_estagios_projetos VARCHAR(20) NOT NULL,
    genero_coordenador_estagios_projetos VARCHAR(15) NOT NULL,
    morada_coordenador_estagios_projetos VARCHAR(100) NOT NULL,
    cod_postal_coordenador_estagios_projetos VARCHAR(30) NOT NULL,
    nacionalidade_coordenador_estagios_projetos VARCHAR(50) NOT NULL,
    numero_telefone_coordenador_estagios_projetos VARCHAR(30) NOT NULL,
    email_coordenador_estagios_projetos VARCHAR(100) NOT NULL,
    instituicao_origem_coordenador_estagios_projetos VARCHAR(100) NOT NULL,
    CONSTRAINT check_genero
    CHECK(genero_coordenador_estagios_projetos IN ('M', 'F'))
)ON database_FG2;
GO

CREATE TABLE Coordenacao.InstituicaoAcolhimento(
    cod_instituicao_acolhimento SMALLINT IDENTITY(1, 1) PRIMARY KEY,
    cod_cidade SMALLINT NOT NULL
        FOREIGN KEY(cod_cidade)
        REFERENCES GIVA.Cidade
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    nome_instituicao_acolhimento VARCHAR(100) NOT NULL,
    morada_instituicao_acolhimento VARCHAR(100) NOT NULL,
    cod_posta_instituicao_acolhimento VARCHAR(30) NOT NULL,
    telefone_instituicao_acolhimento VARCHAR(30) NOT NULL,
    email_instituicao_acolhimento VARCHAR(100) NOT NULL,
    website_instituicao_acolhimento VARCHAR(100)
)ON database_FG1;
GO

CREATE TABLE Propostas.PropostasProjetosDisponiveis(
    cod_proposta_projeto_disponivel SMALLINT IDENTITY(1, 1) PRIMARY KEY,
    cod_coordenador_estagios_projetos SMALLINT NOT NULL
        FOREIGN KEY(cod_coordenador_estagios_projetos)
        REFERENCES Coordenacao.CoordenadorEstagiosProjetos
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    tema_projeto_disponivel VARCHAR(246) NOT NULL,
    descricao_projeto_disponivel VARCHAR(1024) NOT NULL
    )ON database_FG1;
GO

CREATE TABLE Propostas.PropostasEstagiosDisponiveis(
    cod_proposta_estagio_disponivel SMALLINT IDENTITY(1, 1) PRIMARY KEY,
    cod_coordenador_estagios_projetos SMALLINT NOT NULL
        FOREIGN KEY(cod_coordenador_estagios_projetos)
        REFERENCES Coordenacao.CoordenadorEstagiosProjetos
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    cod_instituicao_acolhimento SMALLINT NOT NULL
        FOREIGN KEY(cod_instituicao_acolhimento)
        REFERENCES Coordenacao.InstituicaoAcolhimento
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    tema_estagio_disponivel VARCHAR(246) NOT NULL,
    descicao_estagio_disponivel VARCHAR(1024) NOT NULL,
    requisitos_estagio_disponivel VARCHAR(1024)
)ON database_FG1;
GO

CREATE TABLE Coordenacao.Aluno(
    cod_aluno SMALLINT IDENTITY(1, 1) PRIMARY KEY,
    cod_curso SMALLINT NOT NULL
        FOREIGN KEY(cod_curso)
        REFERENCES GIVA.Curso
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    nome_aluno NVARCHAR(100) NOT NULL,
    numero_instituicao_aluno VARCHAR(20) NOT NULL UNIQUE,
    numero_identificacao_aluno VARCHAR(20) NOT NULL UNIQUE,
    data_nascimento_aluno VARCHAR(20) NOT NULL,
    genero_aluno VARCHAR(5) NOT NULL,
    morada_aluno VARCHAR(100) NOT NULL,
    cod_postal_aluno VARCHAR(30) NOT NULL,
    telefone1_aluno VARCHAR(30) NOT NULL,
    telefone2_aluno VARCHAR(30),
    nacionalidade_aluno VARCHAR(50) NOT NULL,
    email_aluno VARCHAR(100) NOT NULL,
    nome_familiar NVARCHAR(100),
    telefone_familiar VARCHAR(30),
    CONSTRAINT check_genero_aluno
    CHECK(genero_aluno IN ('M', 'F'))
)ON database_FG1;
GO

CREATE TABLE Coordenacao.AreaCientifica(
    cod_area_cientifica SMALLINT IDENTITY(1, 1) PRIMARY KEY,
    nome_area_cientifica VARCHAR(100) NOT NULL,
)ON database_FG2;
GO

CREATE TABLE Coordenacao.DocenteOrientador(
    cod_docente_orientador SMALLINT IDENTITY(1, 1) PRIMARY KEY,
    cod_area_cientifica SMALLINT NOT NULL
        FOREIGN KEY(cod_area_cientifica)
        REFERENCES Coordenacao.AreaCientifica
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    nome_docente_orientador NVARCHAR(150) NOT NULL,
    data_nascimento_docente_orientador VARCHAR(20) NOT NULL,
    genero_docente_orientador VARCHAR(20) NOT NULL,
    morada_docente_orientador VARCHAR(60) NOT NULL,
    cod_postal_docente_orientador VARCHAR(10) NOT NULL,
    nacionalidade_docente_orientador VARCHAR(20) NOT NULL,
    numero_telefone_docente_orientador VARCHAR(30) NOT NULL,
    email_docente_orientador VARCHAR (50) NOT NULL,
    instituicao_origem_docente_orientador VARCHAR(100) NOT NULL,
    CONSTRAINT check_genero_docente_orientador
    CHECK(genero_docente_orientador IN ('M', 'F'))
)ON database_FG1;
GO

CREATE TABLE Avaliacao.MomentoAvaliacao(
    cod_momento_avaliacao SMALLINT IDENTITY(1, 1) PRIMARY KEY,
    cod_aluno SMALLINT NOT NULL UNIQUE
        FOREIGN KEY(cod_aluno)
        REFERENCES Coordenacao.Aluno
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    data_momento_avaliacao VARCHAR(20) NOT NULL,
    local_momento_avaliacao VARCHAR(150) NOT NULL
)ON database_FG2;
GO

CREATE TABLE Avaliacao.Juri(
    cod_juri SMALLINT IDENTITY(1, 1) PRIMARY KEY,
    nome_juri NVARCHAR(100) NOT NULL,
    data_nascimento_juri VARCHAR(20) NOT NULL,
    genero_juri VARCHAR(20) NOT NULL,
    morada_juri VARCHAR(60) NOT NULL,
    cod_postal_juri VARCHAR(10) NOT NULL,
    nacionalidade_juri VARCHAR(30) NOT NULL,
    numero_telefone_juri VARCHAR(30) NOT NULL,
    email_juri VARCHAR(100) NOT NULL,
    CONSTRAINT check_genero_juri
    CHECK(genero_juri IN ('M', 'F'))
)ON database_FG1;
GO

CREATE TABLE Avaliacao.MomentoAvaliacao_Juri(
    cod_juri SMALLINT,
        FOREIGN KEY(cod_juri)
        REFERENCES Avaliacao.Juri
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    cod_momento_avaliacao SMALLINT,
        FOREIGN KEY(cod_momento_avaliacao)
        REFERENCES Avaliacao.MomentoAvaliacao
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    PRIMARY KEY(cod_juri, cod_momento_avaliacao)
)ON database_FG2;
GO

CREATE TABLE Avaliacao.AvaliacaoFinal(
    cod_avaliacao_final SMALLINT IDENTITY(1, 1) PRIMARY KEY,
    cod_momento_avaliacao SMALLINT NOT NULL
        FOREIGN KEY(cod_momento_avaliacao)
        REFERENCES Avaliacao.MomentoAvaliacao
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    data_avaliacao_final VARCHAR(20) NOT NULL,
    nota_avaliacao_final SMALLINT NOT NULL,
    CONSTRAINT check_nota_avaliacao_final
    CHECK(nota_avaliacao_final BETWEEN 0 AND 20)
)ON database_FG2;
GO

CREATE TABLE Coordenacao.OrientadorAcolhimento(
    cod_orientador_acolhimento SMALLINT IDENTITY(1, 1) PRIMARY KEY,
    cod_instituicao_acolhimento SMALLINT NOT NULL
        FOREIGN KEY(cod_instituicao_acolhimento)
        REFERENCES Coordenacao.InstituicaoAcolhimento
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    nome_orientador_acolhimento NVARCHAR(100) NOT NULL,
    data_nascimento_orientador_acolhimento VARCHAR(20) NOT NULL,
    genero_orientador_acolhimento VARCHAR(20) NOT NULL,
    morada_orientador_acolhimento VARCHAR(100) NOT NULL,
    cod_postal_orientador_acolhimento VARCHAR(10) NOT NULL,
    nacionalidade_oriqntador_acolhimento VARCHAR(50) NOT NULL,
    numero_telefone_orientador_acolhimento VARCHAR(30) NOT NULL,
    email_orientador_acolhimento VARCHAR(100) NOT NULL,
    CONSTRAINT check_genero_orientador_acolhimento
    CHECK(genero_orientador_acolhimento IN ('M', 'F'))
)ON database_FG1;
GO

CREATE TABLE Propostas.PropostaEstagio(
    cod_estagio SMALLINT IDENTITY(1, 1) PRIMARY KEY,
    cod_aluno SMALLINT NOT NULL UNIQUE
        FOREIGN KEY(cod_aluno)
        REFERENCES Coordenacao.Aluno
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    cod_docente_orientador SMALLINT NOT NULL
        FOREIGN KEY(cod_docente_orientador)
        REFERENCES Coordenacao.DocenteOrientador
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    cod_orientador_acolhimento SMALLINT NOT NULL
        FOREIGN KEY(cod_orientador_acolhimento)
        REFERENCES Coordenacao.OrientadorAcolhimento
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    tema_estagio VARCHAR(250) NOT NULL,
    descricao_estagio VARCHAR(1024) NOT NULL,
    data_inicio_estagio VARCHAR(30) NOT NULL,
    data_conclusao_estagio VARCHAR(30) NOT NULL
)ON database_FG2;
GO

CREATE TABLE Propostas.PropostaProjeto(
    cod_projeto SMALLINT IDENTITY(1, 1) PRIMARY KEY,
    cod_aluno SMALLINT NOT NULL
        FOREIGN KEY(cod_aluno)
        REFERENCES Coordenacao.Aluno
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    cod_docente_orientador SMALLINT NOT NULL
        FOREIGN KEY(cod_docente_orientador)
        REFERENCES Coordenacao.DocenteOrientador
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    tema_projeto VARCHAR(200) NOT NULL,
    descricao_projeto VARCHAR(500) NOT NULL,
    data_inicio_projeto VARCHAR(20) NOT NULL,
    data_conclusao_projeto VARCHAR(20) NOT NULL
)ON database_FG2;
GO

CREATE TABLE Avaliacao.AvaliacaoOrientadorAcolhimento(
    cod_avaliacao_orientador_acolhimento SMALLINT IDENTITY(1, 1) PRIMARY KEY,
    cod_orientador_acolhimento SMALLINT NOT NULL
        FOREIGN KEY(cod_orientador_acolhimento)
        REFERENCES Coordenacao.OrientadorAcolhimento
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    cod_estagio SMALLINT NOT NULL
        FOREIGN KEY(cod_estagio)
        REFERENCES Propostas.PropostaEstagio
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    descricao_estagio_orientador_acolhimento VARCHAR(200) NOT NULL,
    nota_orientador_acolhimento SMALLINT NOT NULL,
    CONSTRAINT check_nota_orientador_acolhimento
    CHECK(nota_orientador_acolhimento BETWEEN 0 AND 20)
)ON database_FG2;
GO
/*
    Fim de criar as tabelas
*/