drop table localidades cascade constraints;
drop table moradas cascade constraints;
drop table hoteis cascade constraints;
drop table tiposdoc cascade constraints;
drop table docid cascade constraints;
drop table pessoas cascade constraints;
drop table funcoes cascade constraints;
drop table funcionarios cascade constraints;
drop table clientes cascade constraints;
drop table pagamentos cascade constraints;
drop table reservas cascade constraints;
drop table pagamentosreservas cascade constraints;
drop table tiposespacos cascade constraints;
drop table espacos cascade constraints;
drop sequence seq_id_reserva;
drop sequence seq_id_pessoa;
drop sequence seq_id_hotel;
drop sequence seq_id_pagamento;
drop sequence seq_id_reservas;
drop sequence seq_id_espacos;

create table localidades (
    CP varchar(20),
    pais varchar(100),
    nome varchar(100) not null,
    primary key (CP, pais)
);

create table moradas (
    CP varchar(20),
    pais varchar(100),
    rua varchar(100),
    numeroRua integer,
    primary key (CP, pais, rua, numeroRua),
    foreign key (CP, pais) references localidades(CP, pais)
);

create table tiposDoc (
	tipoDoc char(2),
	descricao varchar(25),
	primary key (tipoDoc)
);

create table docid (
    tipoDoc char(2),
    identificadorDoc varchar(15),
    primary key (tipoDoc, identificadorDoc),
	foreign key (tipoDoc) references tiposDoc(tipoDoc)
);

create table pessoas (
    idPessoa integer,
    nome varchar(200) not null,
    dataNascimento date not null,
    CP varchar(20) not null,
    pais varchar(100) not null,
    rua varchar(100) not null,
    numeroRua integer not null,
    tipoDoc char(2) not null,
    identificadorDoc varchar(15) not null,
    primary key (idPessoa),
    foreign key (CP, pais, rua, numeroRua) references moradas(CP, pais, rua, numeroRua),
    foreign key (tipoDoc, identificadorDoc) references docID(tipoDoc, identificadorDoc)
);

create table funcoes (
    codigoF char(2),
    descricaoF varchar(20) not null,
    primary key (codigoF)
);

create table funcionarios (
    idPessoa integer,
    salario float not null,
    codigoF char(2) not null,
    localTrabalho integer not null,
    primary key (idPessoa),
    foreign key (idPessoa) references pessoas(idPessoa),
    foreign key (codigoF) references funcoes(codigoF)
);

create table hoteis (
    idHotel integer,
    nome varchar(50) not null,
    CP varchar(20) not null,
    pais varchar(100) not null,
    rua varchar(100) not null,
    numeroRua integer not null,
    gestor integer,
    primary key (idHotel),
    foreign key (CP, pais, rua, numeroRua) references moradas(CP, pais, rua, numeroRua),
    foreign key (gestor) references funcionarios(idPessoa),
	constraint morada_unique unique (CP, pais, rua, numeroRua)
);

alter table funcionarios add constraint fk_funcionarios foreign key (localTrabalho) references hoteis(idHotel);

create table clientes (
    idPessoa integer,
    primary key (idPessoa),
    foreign key (idPessoa) references pessoas(idPessoa)
);

create table pagamentos (
    idPagamento integer,
    montante float not null,
    dataPag date not null,
    primary key (idPagamento)
);

create table tiposespacos (
    tipo varchar(20),
    lotacao integer not null,
    preco float not null,
    primary key (tipo)
);

create table espacos (
	idHotel integer,
    numero varchar(6),
    tipo varchar(20) not null,
    primary key (idHotel, numero),
    foreign key (tipo) references tiposespacos(tipo)
);

create table reservas (
    idReserva integer,
    dataEntr date not null,
    dataSai date not null,
    idPessoa integer not null,
	idHotel integer not null,
	numero varchar(6) not null,
    primary key (idReserva),
    foreign key (idPessoa) references clientes(idPessoa),
	foreign key (idHotel, numero) references espacos(idHotel, numero),
	constraint data_unique unique (idPessoa, dataEntr, dataSai),
	constraint check_date check (dataEntr < dataSai)
);

create table pagamentosreservas (
    idReserva integer,
    idPagamento integer,
    primary key (idReserva, idPagamento),
    foreign key (idReserva) references reservas(idReserva),
    foreign key (idPagamento) references pagamentos(idPagamento)
);

create sequence seq_id_reserva
	start with 1
	increment by 1;

create sequence seq_id_pessoa
    start with 1
    increment by 1;

create sequence seq_id_hotel
    start with 1
    increment by 1;

create sequence seq_id_pagamento
    start with 1
    increment by 1;

create sequence seq_id_reservas
    start with 1
    increment by 1;

create sequence seq_id_espacos
    start with 1
    increment by 1;
	
-- TIPOS DOCUMENTO DE IDENTIFICACAO
insert into tiposDoc (tipoDoc, descricao) values ('PS', 'Passaporte');
insert into tiposDoc (tipoDoc, descricao) values ('CC', 'Cartão de Cidadão');
insert into tiposDoc (tipoDoc, descricao) values ('BI', 'Bilhete de Identidade');

-- FUNCOES FUNCIONARIOS
insert into funcoes (codigoF, descricaoF) values ('GT', 'Gestor');
insert into funcoes (codigoF, descricaoF) values ('LP', 'Limpeza');
insert into funcoes (codigoF, descricaoF) values ('SG', 'Segurança');
insert into funcoes (codigoF, descricaoF) values ('CZ', 'Cozinheiro');
insert into funcoes (codigoF, descricaoF) values ('EM', 'Empregado de Mesa');
insert into funcoes (codigoF, descricaoF) values ('RC', 'Receção');
insert into funcoes (codigoF, descricaoF) values ('AM', 'Animador');

-- TIPOS ESPACOS
insert into tiposespacos (tipo, lotacao, preco) values ('Individual', 1, 20);
insert into tiposespacos (tipo, lotacao, preco) values ('Duplo', 2, 40);
insert into tiposespacos (tipo, lotacao, preco) values ('Suíte', 2, 200);
insert into tiposespacos (tipo, lotacao, preco) values ('Salão', 150, 1000);

create or replace view registar_funcionario as
    select idPessoa, dataNascimento, pessoas.nome as nome, rua, numeroRua, 
			CP, pais, localidades.nome as localidade, tipoDoc, identificadorDoc, salario, codigoF, localTrabalho
        from funcionarios inner join pessoas using(idPessoa) inner join moradas using (CP, pais, rua, numeroRua)
            inner join localidades using (CP, pais);
        
create or replace trigger registar_funcionario_trigger
    instead of insert on registar_funcionario
    declare 
        c number;
        ident number;
    begin
        select count(*) into c from hoteis, funcoes where idHotel = :new.localTrabalho and codigoF = :new.codigoF;
        if(c > 0) then			
                select count(*) into c from localidades where  CP = :new.CP and pais = :new.pais;
					if(c = 0) then 
						insert into localidades (CP, pais, nome) values (:new.CP, :new.pais, :new.localidade);
					end if;
                select count(*) into c from moradas
							where  CP = :new.CP and pais = :new.pais and rua = :new.rua and numeroRua = :new.numeroRua;
					if(c = 0) then
						insert into moradas (CP, pais, rua, numeroRua) values (:new.CP, :new.pais, :new.rua, :new.numeroRua);
					end if;
                select count(*) into c from pessoas where tipoDoc = :new.tipoDoc and identificadorDoc = :new.identificadorDoc;
					if(c = 0) then
						ident := seq_id_pessoa.nextval;
						insert into docid (tipoDoc, identificadorDoc) values (:new.tipoDoc, :new.identificadorDoc);
						insert into pessoas (idPessoa, nome, dataNascimento, CP, pais, rua, numeroRua, tipoDoc, identificadorDoc) values
							(ident, :new.nome, :new.dataNascimento, :new.CP, :new.pais, :new.rua, :new.numeroRua, 
							:new.tipoDoc, :new.identificadorDoc);
					end if;
				select idPessoa into ident from pessoas where tipoDoc = :new.tipoDoc and identificadorDoc = :new.identificadorDoc;
				insert into funcionarios (idPessoa, salario, codigoF, localTrabalho) values
					(ident, :new.salario, :new.codigoF, :new.localTrabalho);
				select gestor into c from hoteis where idHotel = :new.localTrabalho;
					if(:new.codigoF = 'GT' and c is null) then
						update hoteis set gestor = ident where idHotel = :new.localTrabalho;
					end if;
		else
			Raise_Application_Error (-20100, 'Local de trabalho ou função não existente. Registo não efetuado!');
		end if;
    end;
/

create or replace trigger editar_funcionario
	instead of update on registar_funcionario
	declare 
        c number;
	begin
		update funcionarios set salario = :new.salario, codigoF = :new.codigoF, localTrabalho = :new.localTrabalho
			where idPessoa = :old.idPessoa;
		update pessoas set nome = :new.nome, dataNascimento = :new.dataNascimento, CP = :new.CP, pais = :new.pais, rua = :new.rua,
			numeroRua = :new.numeroRua where idPessoa = :old.idPessoa;
		select count(*) into c from moradas
			where  CP = :new.CP and pais = :new.pais and rua = :new.rua and numeroRua = :new.numeroRua;
			if(c = 0) then
				insert into moradas (CP, pais, rua, numeroRua) values (:new.CP, :new.pais, :new.rua, :new.numeroRua);
			end if;
		select count(*) into c from localidades where  CP = :new.CP and pais = :new.pais;
			if(c = 0) then 
				insert into localidades (CP, pais, nome) values (:new.CP, :new.pais, :new.localidade);
			end if;
	end;
/

create or replace trigger eliminar_funcionario
	instead of delete on registar_funcionario
	declare
		c number;
	begin
		delete from funcionarios where idPessoa = :old.idPessoa;
		delete from docid where tipoDoc = :old.tipoDoc and identificadorDoc = :old.identificadorDoc;
		select count(*) into c from clientes where idPessoa = :old.idPessoa;
		if(c = 0) then
			delete from pessoas where idPessoa = :old.idPessoa;
		end if;
	end;
/

create or replace view registar_cliente as
    select idPessoa, dataNascimento, pessoas.nome as nome, rua, numeroRua, 
		CP, pais, localidades.nome as localidade, tipoDoc, identificadorDoc 
			from clientes inner join pessoas using (idPessoa) inner join moradas using (CP, pais, rua, numeroRua) inner join localidades using (CP, pais);
    
create or replace trigger registar_cliente_trigger
    instead of insert on registar_cliente
    declare
        c number;
        ident number;
    begin
		select count(*) into c from localidades where  CP = :new.CP and pais = :new.pais;
			if(c = 0) then 
				insert into localidades (CP, pais, nome) values (:new.CP, :new.pais, :new.localidade);
			end if;
		select count(*) into c from moradas where  CP = :new.CP and pais = :new.pais and rua = :new.rua and numeroRua = :new.numeroRua;
			if(c = 0) then
				insert into moradas (CP, pais, rua, numeroRua) values (:new.CP, :new.pais, :new.rua, :new.numeroRua);
			end if;
		select count(*) into c from pessoas where tipoDoc = :new.tipoDoc and identificadorDoc = :new.identificadorDoc;
			if( c = 0) then
				ident := seq_id_pessoa.nextval;
				insert into docid (tipoDoc, identificadorDoc) values (:new.tipoDoc, :new.identificadorDoc);
				insert into pessoas (idPessoa, nome, dataNascimento, CP, pais, rua, numeroRua, tipoDoc, identificadorDoc) values
					(ident, :new.nome, :new.dataNascimento, :new.CP, :new.pais, :new.rua, :new.numeroRua, 
						:new.tipoDoc, :new.identificadorDoc);
			end if;
        select idPessoa into ident from pessoas where tipoDoc = :new.tipoDoc and identificadorDoc = :new.identificadorDoc;
		insert into clientes (idPessoa) values (ident);
    end;
/

create or replace trigger editar_cliente
	instead of update on registar_cliente
	declare 
        c number;
	begin
		update pessoas set nome = :new.nome, dataNascimento = :new.dataNascimento, CP = :new.CP, pais = :new.pais, rua = :new.rua,
			numeroRua = :new.numeroRua where idPessoa = :old.idPessoa;
		select count(*) into c from moradas
			where  CP = :new.CP and pais = :new.pais and rua = :new.rua and numeroRua = :new.numeroRua;
			if(c = 0) then
				insert into moradas (CP, pais, rua, numeroRua) values (:new.CP, :new.pais, :new.rua, :new.numeroRua);
			end if;
		select count(*) into c from localidades where  CP = :new.CP and pais = :new.pais;
			if(c = 0) then 
				insert into localidades (CP, pais, nome) values (:new.CP, :new.pais, :new.localidade);
			end if;
	end;
/

create or replace trigger eliminar_cliente
	instead of delete on registar_cliente
	declare
		c number;
	begin
		delete from clientes where idPessoa = :old.idPessoa;
		delete from docid where tipoDoc = :old.tipoDoc and identificadorDoc = :old.identificadorDoc;
		select count(*) into c from funcionarios where idPessoa = :old.idPessoa;
		if(c = 0) then
			delete from pessoas where idPessoa = :old.idPessoa;
		end if;
	end;
/

create or replace view registar_hotel as
    select hoteis.nome as nome, rua, numeroRua, CP, pais, localidades.nome as localidade
    from hoteis inner join moradas using (rua, numeroRua, CP, pais) inner join localidades using (CP, pais);

create or replace trigger registar_hotel_trigger
    instead of insert on registar_hotel
    declare
        c number;
        ident number := seq_id_hotel.nextval;
    begin
		select count(*) into c from localidades where CP = :new.CP and pais = :new.pais;
			if (c = 0) then
				insert into localidades (CP, pais, nome) values (:new.CP, :new.pais, :new.localidade);
			end if;
		select count (*) into c from moradas where CP = :new.CP and pais = :new.pais and rua = :new.rua and 
			numeroRua = :new.numeroRua;
			if (c = 0) then
				insert into moradas (CP, pais, rua, numeroRua) values (:new.CP, :new.pais, :new.rua, :new.numeroRua);
			end if;
		insert into hoteis (idHotel, nome, CP, pais, rua, numeroRua, gestor) values (ident, :new.nome, :new.CP, :new.pais, 
			:new.rua, :new.numeroRua, null);
    end;
/

create or replace view introduzir_reserva as
	select idReserva, dataEntr, dataSai, tipoDoc, identificadorDoc, idHotel, numero
		from espacos inner join reservas using (idHotel, numero) inner join clientes using (idPessoa) inner join pessoas using (idPessoa);

create or replace trigger introduzir_reserva_trigger
	instead of insert on introduzir_reserva
	declare
		ident number;
		idR number := seq_id_reserva.nextval;
	begin
		select idPessoa into ident from pessoas where tipoDoc = :new.tipoDoc and identificadorDoc = :new.identificadorDoc;
		insert into reservas (idReserva, dataEntr, dataSai, idPessoa, idHotel, numero)
			values(idR, :new.dataEntr, :new.dataSai, ident, :new.idHotel, :new.numero);
	end;
/

create or replace trigger apagar_reserva_trigger
    instead of delete on introduzir_reserva
    declare
        c number;
    begin
        select count(*) into c from pagamentosreservas where idReserva = :old.idReserva;
        if (c > 0)
            then Raise_Application_Error (-20100, 'Reserva com pagamento já efetuado. Reserva não eliminada!');
        end if;
        delete from reservas where idReserva = :old.idReserva;
    end;
/

create or replace view registar_pagamento as
    select idPagamento, idReserva, montante, dataEntr, dataSai, tipoDoc, identificadorDoc
    from pagamentos inner join pagamentosreservas using (idPagamento) inner join reservas using (idReserva)
		inner join pessoas using (idPessoa);

create or replace trigger registar_pagamento_trigger
    instead of insert on registar_pagamento
    declare
		c number;
		p number;
        idR number;
        ident number;
		pag number := seq_id_pagamento.nextval;
    begin
		select idPessoa into ident from pessoas where tipoDoc = :new.tipoDoc and identificadorDoc = :new.identificadorDoc;
		select idReserva into idR from reservas where idPessoa = ident and dataEntr = :new.dataEntr and dataSai = :new.dataSai;
		insert into pagamentos (idPagamento, montante, dataPag) values (pag, :new.montante, :new.dataSai);
		/*select count(*) into c from pagamentosreservas where idReserva = idR;
		select preco * (dataSai - dataEntr) into p from pessoas inner join reservas using (idPessoa) inner join espacos using (idHotel, numero) inner join tipoespacos using (tipo)
			where dataEntr = :new.dataEntr and dataSai = :new.dataSai and tipoDoc = :new.tipoDoc and identificadorDoc = :new.identificadorDoc;
		if(c = 0 and p = :new.montante) then
			insert into pagamentosreservas (idReserva, idPagamento) values (idR, pag);
        end if;
		if (c = 0 and p != :new.montante) then
			Raise_Application_Error (-20100, 'O valor do montante está incorreto. Pagamento não efetuado!');
		else
			Raise_Application_Error (-20100, 'Reserva com pagamento já efetuado. Pagamento não aceite!');
		end if;*/
    end;
/

-- ESPACOS
insert into registar_espaco (idHotel, numero, tipo) values (1, '1.01', 'Individual');
insert into registar_espaco (idHotel, numero, tipo) values (1, '2.01', 'Duplo');
insert into registar_espaco (idHotel, numero, tipo) values (1, '3.01', 'Suíte');
insert into registar_espaco (idHotel, numero, tipo) values (1, '100', 'Salão');

-- TESTES
-- hoteis
insert into registar_hotel (nome, rua, numeroRua, CP, pais, localidade) values
    ('Alexandre', 'Rua 1 de Maio', 10, '2222-333', 'Portugal', 'Almada');

-- funcionarios
insert into registar_funcionario (idPessoa, dataNascimento, nome, rua, numeroRua, 
        CP, pais, localidade, tipoDoc, identificadorDoc, salario, codigoF, localTrabalho) values
    (1, '04-Dec-98', 'Hao', 'Rua de Teste', 1234, '1234-567', 'Portugal', 'Almada', 'CC', '123456789', 10000, 'GT', 1);
insert into registar_funcionario (idPessoa, dataNascimento, nome, rua, numeroRua, 
        CP, pais, localidade, tipoDoc, identificadorDoc, salario, codigoF, localTrabalho) values
(2, '04-Dec-98', 'Hao2', 'Rua de Teste', 1234, '1234-567', 'Portugal', 'Almada', 'CC', '123456788', 10000, 'RC', 1);

-- clientes
insert into registar_cliente (idPessoa, dataNascimento, nome, rua, numeroRua, CP, pais, localidade, tipoDoc, identificadorDoc) values
    (3, '04-Dec-98', 'Alexandre', 'Rua de Teste', 4, '2222-333', 'Portugal', 'Almada', 'CC', '123456782');
insert into registar_cliente (idPessoa, dataNascimento, nome, rua, numeroRua, CP, pais, localidade, tipoDoc, identificadorDoc) values
    (4, '04-Dec-98', 'Alexandre2', 'Rua de Teste', 4, '2222-333', 'Portugal', 'Almada', 'CC', '234567891');

-- reservas
insert into introduzir_reserva (idReserva, dataEntr, dataSai, tipoDoc, identificadorDoc, idHotel, numero) 
    values (1, '01-May-19', '03-May-19', 'CC', '123456782', 1, '1.01');
insert into introduzir_reserva (idReserva, dataEntr, dataSai, tipoDoc, identificadorDoc, idHotel, numero) 
    values (2, '01-May-19', '03-May-19', 'CC', '234567891', 1, '2.01');
-- reservas espacos
/*insert into reservasespacos (idHotel, numero, idReserva) values (1, '1.01', 1);
insert into reservasespacos (idHotel, numero, idReserva) values (1, '2.01', 1);*/

commit;