strona grzegorza na bazy danych

drop table if exists Instytucja cascade;
drop table if exists Eksponat cascade;
drop table if exists Wypozyczenie cascade;
drop table if exists Ekspozycja cascade;
drop table if exists Artysta cascade;

create table Instytucja (
       id_instytucji numeric(8) primary key,
       nazwa varchar(31) not null,
       miasto varchar(31) not null
);

-- jedynie artyści z naszych zasobów
create table Artysta (
       id_artysty numeric(8) primary key,
       imie varchar(31) not null,
       -- nullable nazwisko? istotnie
       -- https://en.wikipedia.org/wiki/Mononymous_person
       nazwisko varchar(31),
       urodziny date not null,
       smierciny date
);

-- możemy sobie wyobrazić sztukę konceptualną nieposiadającą tytułu ni wymiarów
create table Eksponat (
       id_eksponatu numeric(8) primary key,
       tytul varchar,
       typ varchar not null,
       wysokosc numeric(8),
       szerokosc numeric(8),
       waga numeric(8),
       dostepny boolean not null,
       id_artysty numeric(8) references Artysta
);

-- połączenie instytucji z eksponatem
create table Wypozyczenie (
       id_eksponatu numeric(8) references Eksponat,
       id_instytucji numeric(8) references Instytucja,
       poczatek date not null,
       -- wypożyczamy tylko znając datę końca wypożyczenia, dyrektor muzeum
       -- jest nieufny (a poza tym ułatwia to triggerowanie limitu 30 dni)
       koniec date not null,
       constraint wypo_k unique (id_instytucji, id_eksponatu)
);

-- połączenie eksponatu z galerią
create table Ekspozycja (       
       id_galerii numeric(8) not null,
       id_eksponatu numeric(8) references Eksponat,
       nr_sali numeric(8) not null,
       poczatek date not null,
       koniec date,
       constraint ekspo_k unique (id_galerii, id_eksponatu)
);

-- kto się kryje i kurzy
create table Magazynienie (
       id_eksponatu numeric(8) references Eksponat primary key,
       poczatek date not null,
       koniec date
);

-- uwaga: trzy powyższe tabele odpowiadają również za historię danego eksponatu!
-- użytkownik zechce dokonać odpowiednich zapytań

-- TRIGGERY

-- część warunków wynika ze struktry tabel np wśród Eksponatów
-- można mieć jedynie tych autorów, których mamy w rejestrze. Ale jednocześnie
-- każdego autora musimy mieć jakiś eksponat (o tym poniżej), co powoduje problem
-- jajkokurzy. Na szczęście zamiast robić na to trigger możemy po prostu
-- w naszym interfejsie umożliwić jedynie dodawanie jednocześnie tych dwu rzeczy,
-- czy coś w tym stylu.
--
-- Triggery niech się ograniczą do tych ograniczeń wynikłych nie ze struktur
-- relacji, a z działania naszego muzuem.

-- musimy zapewnić następujące warunki:
-- 1. eksponat może być poza muzeum maksymalnie 30 dni rocznie
-- 2. każdego z naszych artystów musimy posiadać przynajmniej jeden eksponat!
-- we support our people

-- ten trigger odpowiada za punkt 1., będzie się uruchamiał przy próbie dodania
-- wszelakich wypożyczeń BEFORE
create or replace function limit_obczyzny () returns trigger as $$
declare
    ile_dni_rok integer;
begin
    -- wstępna wersja, gdzie "w tym roku" oznacza "z koncem w tym roku"
    with dlugosci(id_eksponatu, ile) as (
         select w.id_eksponatu, w.koniec - w.poczatek + 1
           from Wypozyczenie w
          where date_part('year', w.koniec) = date_part('year', NEW.koniec)
    )
    select sum(ile) into ile_dni_rok
      from dlugosci d
     where d.id_eksponatu = NEW.id_eksponatu
  group by d.id_eksponatu;

  if ile_dni_rok + NEW.koniec - NEW.poczatek + 1 > 30 then
     raise exception 'Eksponat byłby wypożyczony na ponad 30 dni w roku!';
  end if;

  return NEW;
end;      
$$ language plpgsql;

create trigger t_obczyzna
before update or insert on Wypozyczenie
for each row execute procedure limit_obczyzny();

-- ten trigger natomiast obsługuje punkt 2., tj. posiadanie na stanie naszych
-- artystów. Będzie się odpalał AFTER.
create or replace function artysta_na_stanie () returns trigger as $$
declare
    najmniej integer;
begin
    -- licze tym wielkim zapytaniem dla kazdego artysty ile jego eksponatow
    -- jest na stanie. najpierw biorę eksponaty na stanie, a potem grupuje po
    -- artystach
    with recursive eksponaty_na_stanie(id_eksponatu, id_artysty) as (
         select mgz.id_eksponatu, mgz_id_artysty
           from Magazynienie mgz
          where mgz.poczatek < current_date
            and (mgz.koniec > current_date or mgz.koniec is null)
         union all
         select glr.id_eksponatu, glr.id_artysty
           from Ekspozycja glr
          where glr.poczatek < current_date
            and (glr.koniec > current_date or glr.koniec is null)
    ),
    ile_dziel(id_artysty, ile) as (
        select id_artysty, count(id_eksponatu)
          from eksponaty_na_stanie
      group by id_artysty
    )
    select min(ile) into najmniej from ile_dziel;

    if ile = 0 then
       raise exception 'Wyzerowano pewnego artystę!';
    end if;

    return NEW;

end;
$$ language plpgsql;

create trigger t_art_wyp
after update or insert on Wypozyczenie
for each row execute procedure artysta_na_stanie();