/*
create table vc(matricula varchar(8), nr_idcivil int, data_inicio date, data_fim date);
*/

delete from vc;
insert into vc values('AA-BB-11', 1234, to_date('2019-10-01', 'yyyy-mm-dd'), null);
insert into vc values('AA-BB-11', 5678, to_date('2019-10-04', 'yyyy-mm-dd'), to_date('2019-10-08', 'yyyy-mm-dd'));
insert into vc values('AA-BB-11', 9012, to_date('2019-10-06', 'yyyy-mm-dd'), to_date('2019-10-09', 'yyyy-mm-dd'));
insert into vc values('CC-DD-11', 1234, to_date('2019-10-08', 'yyyy-mm-dd'), to_date('2019-10-14', 'yyyy-mm-dd'));
insert into vc values('AA-BB-11', 1234, to_date('2019-10-10', 'yyyy-mm-dd'), null);
insert into vc values('EE-FF-11', 1234, to_date('2019-10-18', 'yyyy-mm-dd'), null);
--insert into vc values('GG-HH-11', 1234, to_date('2019-10-01', 'yyyy-mm-dd'), null);
commit;

/*
Escreva uma função que devolva um valor booleano indicando se existem sobreposições temporais nas associações de veículos a condutores.
*/

create or replace function fnc_existem_sobreposicoes return boolean
as
  v_count int;
begin
  select count(*) into v_count
    from (select *
           from vc a, vc b
          where (a.matricula != b.matricula or a.nr_idcivil != b.nr_idcivil or a.data_inicio != b.data_inicio)
            and a.matricula = b.matricula
            and a.data_inicio >= b.data_inicio and (a.data_inicio < b.data_fim or b.data_fim is null)
          union
         select *
           from vc a, vc b
          where (a.matricula != b.matricula or a.nr_idcivil != b.nr_idcivil or a.data_inicio != b.data_inicio)
            and a.nr_idcivil = b.nr_idcivil
            and a.data_inicio >= b.data_inicio and (a.data_inicio < b.data_fim or b.data_fim is null));
  return v_count > 0;
end;
/

declare
  v_flag boolean;
begin
  v_flag := fnc_verificar_sobreposicoes;
  if v_flag then
    dbms_output.put_line('Existem sobreposições');
  else
    dbms_output.put_line('Não existem sobreposições');
  end if;
end;
/

/*
Escreva um procedimento que detete, e corrija, as associações de condutores a veículos que se sobrepôem temporalmente.
Considere que os atributos data_inicio e data_fim são datas, e que os intervalos são fechados na data de início e abertos na data de fim.
A correção consiste em analisar as sobreposições, por matrícula e por condutor, e deve seguir a seguinte lógica:
a) Processar matrículas, e para cada registo que tenha de ser modificado, colocar a data de fim igual à data inicial do período seguinte.
b) Processar condutores, e para cada registo que tenha de ser modificado, colocar a data de fim igual à data inicial do período seguinte.
c) Se após o processamento não tiver sido possível corrigir todas as sobreposições, então deve abortar o processamento gerando uma exceção.
*/

create or replace procedure prc_corrigir_associacoes
as
  cursor c1 is
    select *
      from vc
     order by matricula, data_inicio
    for update of data_fim;
  cursor c2 is
    select *
      from vc
     order by nr_idcivil, data_inicio
    for update of data_fim;
  v_data date;
  v_flag boolean;
  ex_sobreposicoes exception;
begin
  for r in c1 loop
    select min(data_inicio) into v_data
      from vc
     where matricula = r.matricula
       and data_inicio > r.data_inicio;
    if (v_data is not null) and (v_data < r.data_fim or r.data_fim is null) then
      update vc
         set data_fim = v_data
       where current of c1;
    end if;
  end loop;
  for r in c2 loop
    select min(data_inicio) into v_data
      from vc
     where nr_idcivil = r.nr_idcivil
       and data_inicio > r.data_inicio;
    if (v_data is not null) and (v_data < r.data_fim or r.data_fim is null) then
      update vc
         set data_fim = v_data
       where current of c2;
    end if;
  end loop;
  if fnc_existem_sobreposicoes then
    raise ex_sobreposicoes;
  end if;
exception
  when ex_sobreposicoes then
    raise_application_error(-20000, 'Existem sobreposições');
end;
/

begin
  prc_corrigir_associacoes;
end;
/
select * from vc order by data_inicio;