DROP TABLE IF EXISTS historia CASCADE;
DROP TABLE IF EXISTS eksponat CASCADE;
DROP TABLE IF EXISTS artysta CASCADE;
DROP TABLE IF EXISTS galeria CASCADE;
DROP TABLE IF EXISTS instytucja CASCADE;

-- Tworzenie tabel
CREATE TABLE artysta (
    ID_artysta SERIAL PRIMARY KEY,
    imie VARCHAR(20) NOT NULL,
    nazwisko VARCHAR(20) NOT NULL,
    rok_urodzenia INT NOT NULL,
    rok_smierci INT NULL
);

CREATE TABLE eksponat (
    ID_eksponat SERIAL PRIMARY KEY,
    tytul VARCHAR(50) NOT NULL,
    typ VARCHAR(30) NOT NULL,
    wysokosc DECIMAL(5,2) NOT NULL,
    szerokosc DECIMAL(5,2) NOT NULL,
    artysta INT NULL,
    czy_cenny VARCHAR(3) NOT NULL CHECK (czy_cenny IN ('tak','nie')),
    CONSTRAINT artysta_eksponat FOREIGN KEY (artysta) REFERENCES artysta(ID_artysta)
);

CREATE TABLE galeria (
    ID_galeria SERIAL PRIMARY KEY,
    nazwa VARCHAR(30) NOT NULL,
    miasto VARCHAR(30) NOT NULL
);

CREATE TABLE instytucja (
    ID_instytucja SERIAL PRIMARY KEY,
    nazwa VARCHAR(30) NOT NULL,
    miasto VARCHAR(30) NOT NULL
);

CREATE TABLE historia (
    ID_historia SERIAL PRIMARY KEY,
    eksponat INT NOT NULL,
    galeria INT NULL,
    instytucja INT NULL,
    sala INT NULL,
    magazyn VARCHAR(3) NOT NULL CHECK (magazyn IN ('tak','nie')),
    data_rozpoczecia DATE NOT NULL,
    data_zakonczenia DATE NULL,
    CONSTRAINT eksponat_ FOREIGN KEY (eksponat) REFERENCES eksponat(ID_eksponat),
    CONSTRAINT historia_galeria FOREIGN KEY (galeria) REFERENCES galeria(ID_galeria),
    CONSTRAINT historia_instytucja FOREIGN KEY (instytucja) REFERENCES instytucja(ID_instytucja)
);


CREATE OR REPLACE FUNCTION usun_artyste_bez_eksponatow()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM artysta a
    WHERE a.ID_artysta = OLD.artysta
    AND NOT EXISTS (
        SELECT 1 FROM eksponat WHERE artysta = OLD.artysta
    );
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_usun_artyste_po_usunieciu_eksponatu
AFTER DELETE ON eksponat
FOR EACH ROW EXECUTE FUNCTION usun_artyste_bez_eksponatow();

CREATE OR REPLACE FUNCTION sprawdz_artyste_po_aktualizacji_eksponatu()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.artysta IS NOT NULL AND OLD.artysta <> NEW.artysta THEN
        DELETE FROM artysta
        WHERE ID_artysta = OLD.artysta
        AND NOT EXISTS (SELECT 1 FROM eksponat WHERE artysta = OLD.artysta);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_sprawdz_artyste_po_update_eksponatu
AFTER UPDATE OF artysta ON eksponat
FOR EACH ROW EXECUTE FUNCTION sprawdz_artyste_po_aktualizacji_eksponatu();

CREATE OR REPLACE FUNCTION sprawdz_czy_artysta_ma_eksponat()
RETURNS TRIGGER AS $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM eksponat WHERE artysta = NEW.ID_artysta
    ) THEN
        RAISE EXCEPTION 'Artysta musi mieć przynajmniej jeden eksponat!';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER artysta_ma_eksponat_trigger
AFTER INSERT ON artysta
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE FUNCTION sprawdz_czy_artysta_ma_eksponat();


-- Funkcja sprawdzająca poprawność daty wypożyczenia
CREATE OR REPLACE FUNCTION sprawdz_data_wypozyczenia()
RETURNS TRIGGER AS
$$
BEGIN
    IF NEW.data_zakonczenia IS NOT NULL AND NEW.data_zakonczenia < NEW.data_rozpoczecia THEN
        RAISE EXCEPTION 'Data zakończenia nie może być wcześniejsza niż rozpoczęcia!';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger sprawdzający datę wypożyczenia
CREATE TRIGGER trigger_sprawdz_data_wypozyczenia
BEFORE INSERT OR UPDATE ON historia
FOR EACH ROW
EXECUTE FUNCTION sprawdz_data_wypozyczenia();

-- Funkcja sprawdzająca magazyn i przypisanie
CREATE OR REPLACE FUNCTION sprawdz_magazyn_i_wypozyczenie()
RETURNS TRIGGER AS
$$
BEGIN
    IF NEW.magazyn = 'tak' AND (NEW.galeria IS NOT NULL OR NEW.instytucja IS NOT NULL) THEN
        RAISE EXCEPTION 'Eksponat w magazynie nie może być wystawiany!';
    ELSIF NEW.magazyn = 'nie' AND NEW.galeria IS NULL AND NEW.instytucja IS NULL THEN
        RAISE EXCEPTION 'Eksponat poza magazynem musi być przypisany!';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger sprawdzający magazyn
CREATE TRIGGER trigger_sprawdz_magazyn_i_wypozyczenie
BEFORE INSERT OR UPDATE ON historia
FOR EACH ROW
EXECUTE FUNCTION sprawdz_magazyn_i_wypozyczenie();

-- Funkcja sprawdzająca, czy eksponat nie jest już przypisany
CREATE OR REPLACE FUNCTION sprawdz_czy_eksponat_juz_uzywany()
RETURNS TRIGGER AS
$$
DECLARE
    czy_uzywany boolean;
BEGIN
    SELECT EXISTS (
        SELECT 1
        FROM historia
        WHERE eksponat = NEW.eksponat
          AND (galeria IS NOT NULL OR instytucja IS NOT NULL OR magazyn = 'tak')
          AND data_zakonczenia IS NULL
    ) INTO czy_uzywany;

    IF czy_uzywany THEN
        RAISE EXCEPTION 'Eksponat nie moze byc wykorzystany!';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger sprawdzający przypisanie eksponatu
CREATE TRIGGER trigger_sprawdz_czy_eksponat_juz_uzywany
BEFORE INSERT OR UPDATE ON historia
FOR EACH ROW
EXECUTE FUNCTION sprawdz_czy_eksponat_juz_uzywany();

CREATE OR REPLACE FUNCTION galeria_albo_instytucja()
RETURNS TRIGGER AS
$$
BEGIN

    IF (NEW.galeria IS NOT NULL AND NEW.instytucja IS NOT NULL) THEN
        RAISE EXCEPTION 'Eksponat nie może być w galerii i instytucji równocześnie.';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger sprawdzający przypisanie eksponatu
CREATE TRIGGER trigger_galeria_albo_instytucja
BEFORE INSERT OR UPDATE ON historia
FOR EACH ROW
EXECUTE FUNCTION galeria_albo_instytucja();


CREATE OR REPLACE FUNCTION sprawdz_cenny_eksponat()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (
        SELECT 1 
        FROM eksponat 
        WHERE 
            ID_eksponat = NEW.eksponat 
            AND czy_cenny = 'tak'
    ) AND NEW.instytucja IS NOT NULL THEN
        RAISE EXCEPTION 'Cennych eksponatów nie można przypisywać do instytucji';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER historia_cenny_eksponat_check
BEFORE INSERT OR UPDATE ON historia
FOR EACH ROW EXECUTE FUNCTION sprawdz_cenny_eksponat();

-- Funkcja sprawdzająca duplikaty artystów
CREATE OR REPLACE FUNCTION sprawdz_duplikat_artysty()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM artysta
        WHERE imie = NEW.imie
        AND nazwisko = NEW.nazwisko
        AND rok_urodzenia = NEW.rok_urodzenia
        AND ID_artysta != NEW.ID_artysta
    ) THEN
        RAISE EXCEPTION 'Artysta o podanych danych już istnieje!';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger dla artystów
CREATE TRIGGER trigger_sprawdz_duplikat_artysty
BEFORE INSERT OR UPDATE ON artysta
FOR EACH ROW
EXECUTE FUNCTION sprawdz_duplikat_artysty();

-- Funkcja sprawdzająca duplikaty galerii
CREATE OR REPLACE FUNCTION sprawdz_duplikat_galerii()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM galeria
        WHERE nazwa = NEW.nazwa
        AND miasto = NEW.miasto
        AND ID_galeria != NEW.ID_galeria
    ) THEN
        RAISE EXCEPTION 'Galeria o podanych danych już istnieje!';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger dla galerii
CREATE TRIGGER trigger_sprawdz_duplikat_galerii
BEFORE INSERT OR UPDATE ON galeria
FOR EACH ROW
EXECUTE FUNCTION sprawdz_duplikat_galerii();

-- Funkcja sprawdzająca duplikaty instytucji
CREATE OR REPLACE FUNCTION sprawdz_duplikat_instytucji()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM instytucja
        WHERE nazwa = NEW.nazwa
        AND miasto = NEW.miasto
        AND ID_instytucja != NEW.ID_instytucja
    ) THEN
        RAISE EXCEPTION 'Instytucja o podanych danych już istnieje!';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger dla instytucja
CREATE TRIGGER trigger_sprawdz_duplikat_instytucji
BEFORE INSERT OR UPDATE ON instytucja
FOR EACH ROW
EXECUTE FUNCTION sprawdz_duplikat_instytucji();

-- Funkcja sprawdzająca duplikaty eksponatów
CREATE OR REPLACE FUNCTION sprawdz_duplikat_eksponatu()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM eksponat
        WHERE tytul = NEW.tytul
        AND artysta IS NOT DISTINCT FROM NEW.artysta
        AND typ = NEW.typ
        AND wysokosc = NEW.wysokosc
        AND szerokosc = NEW.szerokosc
        AND ID_eksponat != NEW.ID_eksponat
    ) THEN
        RAISE EXCEPTION 'Eksponat o podanych danych już istnieje!';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger dla eksponatów
CREATE TRIGGER trigger_sprawdz_duplikat_eksponatu
BEFORE INSERT OR UPDATE ON eksponat
FOR EACH ROW
EXECUTE FUNCTION sprawdz_duplikat_eksponatu();

-- Funkcja sprawdzająca poprawność roku śmierci
CREATE OR REPLACE FUNCTION check_artist_years()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.rok_smierci IS NOT NULL AND NEW.rok_smierci < NEW.rok_urodzenia THEN
        RAISE EXCEPTION 'Rok śmierci nie może być wcześniejszy niż rok urodzenia';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger sprawdzajacy rok śmierci
CREATE TRIGGER artist_year_trigger
BEFORE INSERT OR UPDATE ON artysta
FOR EACH ROW
EXECUTE FUNCTION check_artist_years();


-- Fukcja sparwdzająca, czy eksponat nie teleportuje się 
CREATE OR REPLACE FUNCTION check_exhibit_availability()
RETURNS TRIGGER AS $$
BEGIN
  IF EXISTS (
    SELECT 1
    FROM historia
    WHERE eksponat = NEW.eksponat         
      AND ID_historia != NEW.ID_historia   
      AND (
        (data_rozpoczecia, COALESCE(data_zakonczenia, '9999-12-31'::date)) 
        OVERLAPS 
        (NEW.data_rozpoczecia, COALESCE(NEW.data_zakonczenia, '9999-12-31'::date))
      )
  ) THEN
    RAISE EXCEPTION 'Eksponat % jest już przypisany w innym miejscu w podanym okresie', NEW.eksponat;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger zapobiegający teleportacji
CREATE TRIGGER check_exhibit_availability_trigger
BEFORE INSERT OR UPDATE ON historia
FOR EACH ROW
EXECUTE FUNCTION check_exhibit_availability();



CREATE OR REPLACE FUNCTION sprawdz_limit_wypozyczen()
RETURNS TRIGGER AS $$
DECLARE
    dni_wypozyczenia_1 int;
    dni_wypozyczenia_2 int;
    ostatni_dzien date := COALESCE(NEW.data_zakonczenia, CURRENT_DATE);
BEGIN
    SELECT COALESCE(SUM(
        LEAST(data_zakonczenia, MAKE_DATE(EXTRACT(YEAR FROM data_rozpoczecia)::INT, 12, 31)) 
        - data_rozpoczecia + 1),0)
    INTO dni_wypozyczenia_1
    FROM historia
    WHERE eksponat = NEW.eksponat
        AND EXTRACT(YEAR FROM data_rozpoczecia) = EXTRACT(YEAR FROM NEW.data_rozpoczecia)
        AND instytucja IS NOT NULL;

    SELECT COALESCE(SUM(data_zakonczenia 
        - GREATEST(data_rozpoczecia, MAKE_DATE(EXTRACT(YEAR FROM data_zakonczenia)::INT, 1, 1)) + 1),0)
    INTO dni_wypozyczenia_2
    FROM historia
    WHERE eksponat = NEW.eksponat
        AND EXTRACT(YEAR FROM data_zakonczenia) = EXTRACT(YEAR FROM ostatni_dzien)
        AND instytucja IS NOT NULL;

    IF ((NEW.instytucja IS NOT NULL) 
		AND ((dni_wypozyczenia_1 + COALESCE((LEAST(ostatni_dzien, 
		MAKE_DATE(EXTRACT(YEAR FROM NEW.data_rozpoczecia)::INT, 12, 31)) - NEW.data_rozpoczecia + 1),0) > 30)
        OR (dni_wypozyczenia_2 + COALESCE((ostatni_dzien - GREATEST(NEW.data_rozpoczecia, 
        MAKE_DATE(EXTRACT(YEAR FROM ostatni_dzien)::INT, 1, 1)) + 1),0) > 30))) THEN
        RAISE EXCEPTION 'Limit 30 dni wypożyczenia w roku został przekroczony!';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger sprawdzający limit wypożyczeń
CREATE TRIGGER trigger_sprawdz_limit_wypozyczen
BEFORE INSERT OR UPDATE ON historia
FOR EACH ROW
EXECUTE FUNCTION sprawdz_limit_wypozyczen();

BEGIN; 

-- Artyści i eksponaty w jednej transakcji
INSERT INTO artysta (imie, nazwisko, rok_urodzenia, rok_smierci) VALUES
('Leonardo', 'da Vinci', 1452, 1519),
('Pablo', 'Picasso', 1881, 1973),
('Vincent', 'van Gogh', 1853, 1890),
('Claude', 'Monet', 1840, 1926),
('Salvador', 'Dalí', 1904, 1989);

INSERT INTO eksponat (tytul, typ, wysokosc, szerokosc, artysta, czy_cenny) VALUES
('Mona Lisa', 'Obraz', 77.0, 53.0, currval(pg_get_serial_sequence('artysta', 'id_artysta'))-4, 'tak'),
('Guernica', 'Obraz', 349.0, 777.0, currval(pg_get_serial_sequence('artysta', 'id_artysta'))-3, 'nie'),
('Słoneczniki', 'Obraz', 92.0, 73.0, currval(pg_get_serial_sequence('artysta', 'id_artysta'))-2, 'nie'),
('Impresja, wschód słońca', 'Obraz', 39.0, 32.0, currval(pg_get_serial_sequence('artysta', 'id_artysta'))-1, 'nie'),
('Trwałość pamięci', 'Obraz', 24.0, 33.0, currval(pg_get_serial_sequence('artysta', 'id_artysta')), 'tak');

COMMIT; 

-- Pozostałe dane
INSERT INTO galeria (nazwa, miasto) VALUES
('Luwr', 'Paryż'),
('Muzeum Picassa', 'Barcelona'),
('Muzeum van Gogha', 'Amsterdam'),
('Muzeum Orsay', 'Paryż'),
('Muzeum Salvador Dalí', 'Figueres');

INSERT INTO instytucja (nazwa, miasto) VALUES
('Muzeum Luwr', 'Paryż'),
('Fundacja Picassa', 'Barcelona'),
('Muzeum van Gogha', 'Amsterdam'),
('Muzeum Orsay', 'Paryż'),
('Muzeum Salvador Dalí', 'Figueres');

INSERT INTO historia (eksponat, galeria, instytucja, sala, magazyn, data_rozpoczecia, data_zakonczenia) VALUES
(1, 1, NULL, 101, 'nie', '2024-01-01', '2024-01-10'),
(2, 2, NULL, 202, 'nie', '2024-02-01', '2024-02-15'),
(3, 3, NULL, 303, 'nie', '2024-03-01', '2024-03-10'),
(4, NULL, NULL, 404, 'tak', '2024-04-01', NULL),
(5, NULL, NULL, 505, 'tak', '2024-05-01', NULL);