drop table if exists regiao cascade;
drop table if exists concelho cascade;
drop table if exists instituicao cascade;
drop table if exists medico cascade;
drop table if exists consulta cascade;
drop table if exists prescricao cascade;
drop table if exists analise cascade;
drop table if exists venda_farmacia cascade;
drop table if exists prescricao_venda cascade;

create table regiao(
    num_regiao integer unique 
    nome_reg varchar(40) not null,
    num_habitantes integer not null,
    primary key(num_regiao),
    CHECK ( nome_reg in ('Norte', 'Centro', 'Lisboa', 'Alentejo', 'Algarve') ) );

create table concelho(
    num_concelho integer unique,
    nome_con varchar(50) not null, 
    num_regiao integer not null,
    num_habitantes integer not null,
    primary key(num_concelho),
    foreign key(num_regiao) references regiao(num_regiao)
        on delete cascade
        on update cascade,
    CHECK (nome_con in ('Agueda', 'Albergaria-a-Velha', 'Anadia', 'Arouca', 'Aveiro', 'Castelo de Paiva', 'Espinho', 'Estarreja', 'Olhavo', 'Mealhada', 'Murtosa', 'Oliveira de Azemeis', 'Oliveira do Bairro', 'Ovar', 'Santa Maria da Feira', 'Sao Joao da Madeira',
                  'Sever do Vouga', 'Vagos', 'Vale de Cambra', 'Aljustrel', 'Almodovar', 'Alvito', 'Barrancos', 'Beja', 'Castro Verde', 'Cuba', 'Ferreira do Alentejo', 'Mertola', 'Moura', 'Odemira', 'Ourique', 'Serpa', 'Vidigueira', 'Amares', 'Barcelos', 
                  'Braga', 'Cabeceiras de Basto', 'Celorico de Basto', 'Esposende', 'Fafe', 'Guimaraes', 'Povoa de Lanhoso', 'Terras de Bouro', 'Vieira do Minho', 'Vila Nova de Famalicao', 'Vila Verde', 'Vizela', 'Alfandega da Fe', 'Braganca', 'Carrazeda de Ansiaes', 'Freixo de Espada a Cinta', 'Macedo de Cavaleiros', 'Miranda do Douro', 'Mirandela', 
                  'Mogadouro', 'Torre de Moncorvo', 'Vila Flor', 'Vimioso', 'Vinhais', 'Belmonte', 'Castelo Branco', 'Covilha', 'Fundao', 'Idanha-a-Nova', 'Oleiros', 'Penamacor', 'Proenca-a-Nova', 'Serta', 'Vila de Rei', 'Vila Velha de Rodao', 'Arganil', 'Cantanhede', 'Coimbra', 'Condeixa-a-Nova', 'Figueira da Foz', 'Gois', 'Lousa', 'Mira', 'Miranda do Corvo',
                  'Montemor-o-Velho', 'Oliveira do Hospital', 'Pampilhosa da Serra', 'Penacova', 'Penela', 'Soure', 'Tabua', 'Vila Nova de Poiares', 'Alandroal', 'Arraiolos', 'Borba', 'Estremoz', 'Evora', 'Montemor-o-Novo', 'Mora', 'Mourao', 'Portel', 'Redondo', 'Reguengos de Monsaraz', 'Vendas Novas', 'Viana do Alentejo', 'Vila Vicosa', 'Albufeira', 'Alcoutim', 
                  'Aljezur', 'Castro Marim', 'Faro', 'Lagoa', 'Lagos', 'Loule', 'Monchique', 'Olhao', 'Portimao', 'Sao Brás de Alportel', 'Silves', 'Tavira', 'Vila do Bispo', 'Vila Real de Santo Antonio', 'Aguiar da Beira', 'Almeida', 'Celorico da Beira', 'Figueira de Castelo Rodrigo', 'Fornos de Algodres', 'Gouveia', 'Guarda', 'Manteigas', 'Meda', 'Pinhel', 'Sabugal',
                  'Seia', 'Trancoso', 'Vila Nova de Foz Coa', 'Alcobaca', 'Alvaiazere', 'Ansiao', 'Batalha', 'Bombarral', 'Caldas da Rainha', 'Castanheira de Pera', 'Figueiro dos Vinhos', 'Leiria', 'Marinha Grande', 'Nazare', 'obidos', 'Pedrogao Grande', 'Peniche', 'Pombal', 'Porto de Mos', 'Alenquer', 'Amadora', 'Arruda dos Vinhos', 'Azambuja', 'Cadaval', 'Cascais', 
                  'Lisboa', 'Loures', 'Lourinha', 'Mafra', 'Odivelas', 'Oeiras', 'Sintra', 'Sobral de Monte Agraco', 'Torres Vedras', 'Vila Franca de Xira', 'Alter do Chao', 'Arronches', 'Avis', 'Campo Maior', 'Castelo de Vide', 'Crato', 'Elvas', 'Fronteira', 'Gaviao', 'Marvao', 'Monforte', 'Nisa', 'Ponte de Sor', 'Portalegre', 'Sousel', 'Amarante', 'Baiao', 'Felgueiras',
                  'Gondomar', 'Lousada', 'Maia', 'Marco de Canaveses', 'Matosinhos', 'Pacos de Ferreira', 'Paredes', 'Penafiel', 'Porto', 'Povoa de Varzim', 'Santo Tirso', 'Trofa', 'Valongo', 'Vila do Conde', 'Vila Nova de Gaia', 'Abrantes', 'Alcanena', 'Almeirim', 'Alpiarca', 'Benavente', 'Cartaxo', 'Chamusca', 'Constancia', 'Coruche', 'Entroncamento', 'Ferreira do Zezere',
                  'Golega', 'Macao', 'Ourem', 'Rio Maior', 'Salvaterra de Magos', 'Santarem', 'Sardoal', 'Tomar', 'Torres Novas', 'Vila Nova da Barquinha', 'Alcacer do Sal', 'Alcochete', 'Almada', 'Barreiro', 'Grandola', 'Moita', 'Montijo', 'Palmela', 'Santiago do Cacem', 'Seixal', 'Sesimbra', 'Setubal', 'Sines', 'Arcos de Valdevez', 'Caminha', 'Melgaco', 'Moncao', 'Paredes de Coura',
                  'Ponte da Barca', 'Ponte de Lima', 'Valenca', 'Viana do Castelo', 'Vila Nova de Cerveira', 'Alijo', 'Boticas', 'Chaves', 'Mesao Frio', 'Mondim de Basto', 'Montalegre', 'Murca', 'Peso da Regua', 'Ribeira de Pena', 'Sabrosa', 'Santa Marta de Penaguiao', 'Valpacos', 'Vila Pouca de Aguiar', 'Vila Pouca de Aguiar', 'Vila Real', 'Armamar', 'Carregal do Sal', 'Castro Daire',
                  'Cinfaes', 'Lamego', 'Mangualde', 'Moimenta da Beira', 'Mortagua', 'Nelas', 'Oliveira de Frades', 'Penalva do Castelo', 'Penedono', 'Resende', 'Santa Comba Dao', 'Sao Joao da Pesqueira', 'Sao Pedro do Sul', 'Satao', 'Sernancelhe', 'Tabuaco', 'Tarouca', 'Tondela', 'Vila Nova de Paiva', 'Viseu', 'Vouzela') ) );

create table instituicao(
    nome_inst varchar(40) not null,
    tipo varchar(15) not null,
    num_regiao integer not null,
    num_concelho integer not null,
    primary key(nome_inst),
    foreign key(num_regiao) references regiao(num_regiao)
        on delete cascade
        on update cascade,   
    foreign key(num_concelho) references concelho(num_concelho)
        on delete cascade
        on update cascade,
    CHECK( tipo in ('farmacia', 'laboratorio', 'clinica', 'hospital')) );

create table medico(
    num_cedula integer unique,
    nome_med varchar(30) not null,
    especialidade varchar(30) not null,
    primary key(num_cedula) );

create table consulta(
    num_cedula integer ,
    num_doente integer,
    data_consulta date ,
    nome_instituicao varchar(40) not null,
    primary key(num_doente, num_cedula, data_consulta),
    foreign key(num_cedula) references medico(num_cedula)
        on delete cascade
        on update cascade,
    foreign key (nome_instituicao) references instituicao(nome_inst)
        on delete cascade
        on update cascade,
    
    CHECK( EXTRACT( dow from data_consulta ) != 1 ),
    CHECK( EXTRACT( dow from data_consulta ) != 7 ) );

create table prescricao(
    num_cedula integer ,
    num_doente integer ,
    data_presc date ,
    substancia varchar(40),
    quantidade integer not null,
    primary key(substancia , num_doente, data_presc, num_cedula),
    foreign key(num_cedula, num_doente, data_presc) references consulta(num_cedula, num_doente, data_consulta)
        on delete cascade
        on update cascade );

create table analise(
    num_analise integer unique,
    especialidade varchar(40) not null,
    num_cedula1 integer,
    num_doente integer,
    data_analise date,
    data_registo date not null,
    nome_analise varchar(40) not null,
    quant integer not null,
    inst varchar(40) not null,
    primary key(num_analise),
    foreign key(num_cedula1, num_doente, data_analise) references consulta(num_cedula, num_doente, data_consulta)
        on delete cascade
        on update cascade,
    foreign key(inst) references instituicao(nome_inst)
        on delete cascade
        on update cascade   );

create table venda_farmacia(
    num_venda integer,
    data_registo date not null,
    substancia varchar(40) not null,
    quant integer not null,
    preco integer not null,
    inst varchar (40) not null,
    primary key(num_venda),
    foreign key(inst) references instituicao(nome_inst)
        on delete cascade
        on update cascade );

create table prescricao_venda(
    num_cedula integer not null,
    num_doente integer not null,
    data_presc date not null,
    substancia varchar(40) not null,
    num_venda integer not null,
    primary key(num_cedula, num_doente, data_presc, substancia, num_venda),
    foreign key(num_cedula, num_doente, data_presc, substancia) 
        references prescricao(num_cedula, num_doente, data_presc, substancia)
            on delete cascade
            on update cascade,
    foreign key(num_venda) references venda_farmacia(num_venda)
        on delete cascade
        on update cascade );