DROP TABLE IF EXISTS Historia_ekspozycji;
DROP TABLE IF EXISTS Sala;
DROP TABLE IF EXISTS Galeria;
DROP TABLE IF EXISTS Historia_wypożyczeń;
DROP TABLE IF EXISTS Inna_instytucja;
DROP TABLE IF EXISTS Historia_magazynowania;
DROP TABLE IF EXISTS Magazyn;
DROP TABLE IF EXISTS Eksponat;
DROP TABLE IF EXISTS Artysta;

CREATE TABLE Artysta (
  id_artysty INTEGER PRIMARY KEY,
  imię VARCHAR(15) NOT NULL,
  nazwisko VARCHAR(15) NOT NULL,
  rok_urodzenia INTEGER NOT NULL,
  rok_śmierci INTEGER);

CREATE TABLE Eksponat (
  id_eksponatu INTEGER PRIMARY KEY,
  tytuł VARCHAR(20) NOT NULL,
  typ VARCHAR(10) NOT NULL,
  wysokość INTEGER NOT NULL,
  szerokość INTEGER NOT NULL,
  waga INTEGER NOT NULL,
  id_artysty INTEGER,
  najcenniejszy BOOLEAN DEFAULT FALSE,
  FOREIGN KEY (id_artysty) REFERENCES Artysta(id_artysty) ON DELETE CASCADE);

CREATE TABLE Magazyn (
  id_magazynu INTEGER PRIMARY KEY,
  nazwa VARCHAR(20) NOT NULL);

CREATE TABLE Historia_magazynowania (
  id_magazynowania INTEGER PRIMARY KEY,
  id_magazynu INTEGER NOT NULL,
  id_eksponatu INTEGER NOT NULL,
  data_rozpoczęcia DATE,
  data_zakończenia DATE,
  FOREIGN KEY (id_magazynu) REFERENCES Magazyn(id_magazynu),
  FOREIGN KEY (id_eksponatu) REFERENCES Eksponat(id_eksponatu));

CREATE TABLE Inna_instytucja (
  id_instytucji INTEGER PRIMARY KEY,
  nazwa_instytucji VARCHAR(20) NOT NULL,
  miasto VARCHAR(20) NOT NULL);

CREATE TABLE Historia_wypożyczeń (
  id_wypożyczenia INTEGER PRIMARY KEY,
  id_instytucji INTEGER NOT NULL,
  id_eksponatu INTEGER NOT NULL,
  data_rozpoczęcia DATE,
  data_zakończenia DATE,
  FOREIGN KEY (id_instytucji) REFERENCES Inna_instytucja(id_instytucji),
  FOREIGN KEY (id_eksponatu) REFERENCES Eksponat(id_eksponatu));

CREATE TABLE Galeria (
  id_galerii INTEGER PRIMARY KEY,
  nazwa VARCHAR(20) NOT NULL);

CREATE TABLE Sala (
  id_sali INTEGER PRIMARY KEY,
  nazwa_sali VARCHAR(20) NOT NULL,
  id_galerii INTEGER NOT NULL,
  FOREIGN KEY (id_galerii) REFERENCES Galeria(id_galerii));

CREATE TABLE Historia_ekspozycji (
  id_ekspozycji INTEGER PRIMARY KEY,
  id_sali INTEGER NOT NULL,
  id_eksponatu INTEGER NOT NULL,
  data_rozpoczęcia DATE,
  data_zakończenia DATE,
  FOREIGN KEY (id_sali) REFERENCES Sala(id_sali),
  FOREIGN KEY (id_eksponatu) REFERENCES Eksponat(id_eksponatu));


CREATE OR REPLACE FUNCTION sprawdź_czas_wypożyczeń()
RETURNS TRIGGER AS $$
DECLARE
    łączna_liczba_dni INTEGER;
    początek_roku DATE;
    koniec_roku DATE;
    rok_rozpoczęcia INTEGER;
    rok_zakończenia INTEGER;
BEGIN

    rok_rozpoczęcia := DATE_PART('year', NEW.data_rozpoczęcia);
    rok_zakończenia := DATE_PART('year', NEW.data_zakończenia);

    FOR rok IN rok_rozpoczęcia..rok_zakończenia LOOP
        łączna_liczba_dni := 0;

        początek_roku := DATE (rok || '-01-01');

        koniec_roku := DATE (rok || '-12-31');

		SELECT SUM(
			LEAST(data_zakończenia, koniec_roku) - GREATEST(data_rozpoczęcia, początek_roku)
		) INTO łączna_liczba_dni
        FROM Historia_wypożyczeń
        WHERE ID_eksponatu = NEW.ID_eksponatu
        AND (data_rozpoczęcia BETWEEN początek_roku AND koniec_roku
            OR data_zakończenia BETWEEN początek_roku AND koniec_roku
            OR (data_rozpoczęcia < początek_roku AND data_zakończenia > początek_roku)
        );

        IF łączna_liczba_dni > 30 THEN
            RAISE EXCEPTION 'Eksponat % przekroczył limit 30 dni wypożyczenia w roku %', NEW.ID_eksponatu, rok;
        END IF;
    END LOOP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION sprawdź_min_eksponaty_artysty()
RETURNS TRIGGER AS $$
DECLARE
    koniec DATE;
    znaleziono BOOLEAN;
BEGIN

    SELECT NEW.data_rozpoczęcia INTO koniec;


    WHILE koniec < NEW.data_zakończenia LOOP
        WITH EM AS (
            SELECT ID_eksponatu, data_rozpoczęcia, COALESCE(data_zakończenia, NEW.data_zakończenia) AS data_zakończenia
            FROM Historia_ekspozycji
            UNION ALL
            SELECT ID_eksponatu, data_rozpoczęcia, COALESCE(data_zakończenia, NEW.data_zakończenia) AS data_zakończenia
            FROM Historia_magazynowania
        )

        SELECT EXISTS (
            SELECT 1
            FROM EM
            JOIN Eksponat E ON EM.ID_eksponatu = E.ID_eksponatu
            WHERE E.ID_artysty = (SELECT ID_artysty FROM Eksponat WHERE ID_eksponatu = NEW.ID_eksponatu)
              AND (EM.data_rozpoczęcia <= koniec AND EM.data_zakończenia >= koniec)
        ) INTO znaleziono;

        IF NOT znaleziono THEN
            RAISE EXCEPTION 'Artysta % musi mieć przynajmniej jeden eksponat w muzeum w trakcie wypożyczenia!',
                (SELECT imię || ' ' || nazwisko FROM Artysta WHERE ID_artysty = (SELECT ID_artysty FROM Eksponat WHERE ID_eksponatu = NEW.ID_eksponatu));
        END IF;

        WITH EM AS (
            SELECT ID_eksponatu, data_rozpoczęcia, COALESCE(data_zakończenia, NEW.data_zakończenia) AS data_zakończenia
            FROM Historia_ekspozycji
            UNION ALL
            SELECT ID_eksponatu, data_rozpoczęcia, COALESCE(data_zakończenia, NEW.data_zakończenia) AS data_zakończenia
            FROM Historia_magazynowania
        )
        SELECT
            MAX(EM.data_zakończenia) + 1
        INTO
            koniec
        FROM
            EM
        JOIN
            Eksponat E ON EM.ID_eksponatu = E.ID_eksponatu
        WHERE
            E.ID_artysty = (SELECT ID_artysty FROM Eksponat WHERE ID_eksponatu = NEW.ID_eksponatu)
            AND (EM.data_rozpoczęcia <= koniec AND EM.data_zakończenia >= koniec);
    END LOOP;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION sprawdz_date_smierci()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.rok_śmierci IS NOT NULL AND NEW.rok_śmierci < NEW.rok_urodzenia THEN
        RAISE EXCEPTION 'Rok śmierci nie może być wcześniejszy niż rok urodzenia';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION sprawdź_datę()
RETURNS TRIGGER AS $$
BEGIN

    IF NEW.data_zakończenia IS NOT NULL AND NEW.data_zakończenia < NEW.data_rozpoczęcia THEN
        RAISE EXCEPTION 'Data zakończenia (ID: %) nie może być wcześniejsza niż data rozpoczęcia!', NEW.ID;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER sprawdź_czas_wypożyczeń_trigger
AFTER INSERT OR UPDATE ON Historia_wypożyczeń
FOR EACH ROW
EXECUTE PROCEDURE sprawdź_czas_wypożyczeń();

CREATE OR REPLACE TRIGGER sprawdź_min_eksponaty_artysty_trigger
AFTER INSERT OR UPDATE ON Historia_wypożyczeń
FOR EACH ROW
EXECUTE PROCEDURE sprawdź_min_eksponaty_artysty();

CREATE OR REPLACE TRIGGER sprawdz_date_smierci_trigger
BEFORE INSERT OR UPDATE ON Artysta
FOR EACH ROW
EXECUTE FUNCTION sprawdz_date_smierci();

CREATE OR REPLACE TRIGGER sprawdź_datę_trigger_wypożyczenia
BEFORE INSERT OR UPDATE ON Historia_wypożyczeń
FOR EACH ROW
EXECUTE FUNCTION sprawdź_datę();

CREATE OR REPLACE TRIGGER sprawdź_datę_trigger_historia_magazynowania
BEFORE INSERT OR UPDATE ON Historia_magazynowania
FOR EACH ROW
EXECUTE FUNCTION sprawdź_datę();

CREATE OR REPLACE TRIGGER sprawdź_datę_trigger_historia_ekspozycji
BEFORE INSERT OR UPDATE ON Historia_ekspozycji
FOR EACH ROW
EXECUTE FUNCTION sprawdź_datę();


INSERT INTO Artysta VALUES
(1, 'Zofia', 'Nowicka', 1980, NULL),
(2, 'Krzysztof', 'Mazurek', 1975, NULL),
(3, 'Ewa', 'Zielińska', 1920, 2005),
(4, 'Tomasz', 'Kamiński', 1890, 1965),
(5, 'Anna', 'Wiśniewska', 1910, 1980);

INSERT INTO Eksponat VALUES
(1, 'Drzewo', 'obraz', 100, 150, 20, 1, FALSE),
(2, 'Krąg', 'rzeźba', 200, 100, 300, 2, TRUE),
(3, 'Cień', 'instalacja', 400, 300, 700, 3, FALSE),
(4, 'Słońce', 'obraz', 120, 180, 25, 4, TRUE),
(5, 'Chmura', 'rzeźba', 150, 200, 350, 5, TRUE),
(6, 'Las', 'obraz', 90, 140, 15, 1, FALSE),
(7, 'Kula', 'rzeźba', 180, 120, 250, 2, FALSE),
(8, 'Zmierzch', 'instalacja', 350, 270, 650, 3, FALSE),
(9, 'Księżyc', 'obraz', 130, 175, 22, 4, FALSE),
(10, 'Mgła', 'rzeźba', 160, 210, 330, 5, FALSE);

INSERT INTO Magazyn VALUES
(1, 'Magazyn 1'),
(2, 'Magazyn 2'),
(3, 'Magazyn 3');

INSERT INTO Historia_magazynowania VALUES
(1, 1, 1, '2023-01-10', '2023-01-11'),
(2, 2, 2, '2023-07-01', '2023-07-05'),
(3, 3, 3, '2024-01-01', '2024-01-04'),
(4, 1, 4, '2023-03-01', '2023-03-03'),
(5, 2, 5, '2024-02-01', '2024-02-07'),
(6, 3, 6, '2024-03-01', NULL),
(7, 1, 7, '2023-04-01', NULL),
(8, 2, 8, '2024-05-01', NULL),
(9, 3, 9, '2024-06-01', NULL),
(10, 1, 10, '2024-07-01', NULL);

INSERT INTO Inna_instytucja VALUES
(1, 'Instytucja 1', 'Berlin'),
(2, 'Instytucja 2', 'Warszawa'),
(3, 'Instytucja 3', 'Praga');

INSERT INTO Historia_wypożyczeń VALUES
(1, 1, 1, '2024-07-01', '2024-07-13'),
(2, 2, 2, '2023-08-15', '2023-09-01'),
(3, 3, 3, '2024-01-01', '2024-01-04');

INSERT INTO Galeria VALUES
(1, 'Galeria 1'),
(2, 'Galeria 2'),
(3, 'Galeria 3');

INSERT INTO Sala VALUES
(1, 'Sala A1', 1),
(2, 'Sala B1', 2),
(3, 'Sala C1', 3);

INSERT INTO Historia_ekspozycji VALUES
(1, 1, 1, '2023-06-11', '2023-12-31'),
(2, 2, 2, '2024-01-01', NULL),
(3, 3, 4, '2023-03-05', '2023-06-01'),
(4, 1, 3, '2024-01-05', NULL),
(5, 2, 5, '2023-12-01', '2024-01-31');
