drop table if exists Artysta CASCADE;

drop table if exists Eksponat CASCADE;

drop table if exists Galeria CASCADE;

drop table if exists Instytucja CASCADE;

drop table if exists Magazyn CASCADE;

drop table if exists Sala CASCADE;

drop table if exists Wypożyczenia CASCADE;

drop table if exists Historia_magazynowania CASCADE;

drop table if exists Historia_ekspozycji CASCADE;



-- Tworzenie tabeli Artysta
CREATE TABLE Artysta (
    ID_artysty			SERIAL PRIMARY KEY,
    imię 				VARCHAR(50) NOT NULL,
    nazwisko 			VARCHAR(50) NOT NULL,
    rok_urodzenia 		INTEGER CHECK (rok_urodzenia>0) NOT NULL,
    rok_śmierci 		INTEGER CHECK (rok_śmierci>0) NULL
);

-- Tworzenie tabeli Eksponat
CREATE TABLE Eksponat (
    ID_eksponatu 		SERIAL PRIMARY KEY,
    tytuł 				VARCHAR(50) NOT NULL,
    typ 				VARCHAR(50) NOT NULL,
    wysokość 			INTEGER CHECK(wysokość>0) NOT NULL,
    szerokość 			INTEGER CHECK (szerokość>0) NOT NULL,
    waga 				INTEGER CHECK (waga>0) NOT NULL,
    ID_artysty 			INTEGER NULL REFERENCES Artysta(ID_artysty),
    najcenniejszy 		BOOLEAN DEFAULT FALSE NOT NULL
    
);

-- Tworzenie tabeli Galeria
CREATE TABLE Galeria (
    ID_galerii 			SERIAL PRIMARY KEY,
    nazwa 				VARCHAR(50) NOT NULL,
    miasto 				VARCHAR(50) NOT NULL
);

-- Tworzenie tabeli Instytucja
CREATE TABLE Instytucja (
    ID_instytucji 		SERIAL PRIMARY KEY,
    nazwa 				VARCHAR(50) NOT NULL,
    miasto				VARCHAR(50) NOT NULL
);

-- Tworzenie tabeli Magazyn
CREATE TABLE Magazyn (
    ID_magazynu 		SERIAL PRIMARY KEY,
    nazwa 				VARCHAR(50) NOT NULL,
    miasto 				VARCHAR(50) NOT NULL
);

-- Tworzenie tabeli Sala
CREATE TABLE Sala (
    ID_sali 			SERIAL PRIMARY KEY,
    ID_galerii 			INTEGER NOT NULL REFERENCES Galeria(ID_galerii),
    nazwa_sali 			VARCHAR(50) NOT NULL
    );
	
-- Tworzenie tabeli Wypożyczenia
CREATE TABLE Wypożyczenia (
    ID_wypożyczenia		SERIAL PRIMARY KEY,
    ID_eksponatu		INTEGER NOT NULL REFERENCES Eksponat(ID_eksponatu),
    ID_instytucji		INTEGER NOT NULL REFERENCES Instytucja(ID_instytucji),
    data_rozpoczęcia	DATE NOT NULL,
    data_zakończenia	DATE NOT NULL,
    CONSTRAINT unikalne_wypozyczenie UNIQUE (ID_eksponatu, data_rozpoczęcia, data_zakończenia)
);



-- Tworzenie tabeli Historia_magazynowania
CREATE TABLE Historia_magazynowania (
    ID_magazynowania 	SERIAL PRIMARY KEY,
    ID_eksponatu		INTEGER NOT NULL REFERENCES Eksponat(ID_eksponatu),
    ID_magazynu 		INTEGER NOT NULL  REFERENCES Magazyn(ID_magazynu),
    cel_przechowywania 	VARCHAR(50) NOT NULL,
    data_rozpoczęcia	DATE NOT NULL,
    data_zakończenia	DATE NULL
);



-- Tworzenie tabeli Historia_ekspozycji
CREATE TABLE Historia_ekspozycji (
    ID_ekspozycji		SERIAL PRIMARY KEY,
    ID_eksponatu		INTEGER NOT NULL REFERENCES Eksponat(ID_eksponatu),
    ID_sali				INTEGER NOT NULL REFERENCES Sala(ID_sali),
    data_rozpoczęcia	DATE NOT NULL,
    data_zakończenia	DATE NULL
);

-- 30 dni
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

    -- Określenie lat rozpoczęcia i zakończenia wypożyczenia
    rok_rozpoczęcia := DATE_PART('year', NEW.data_rozpoczęcia);
    rok_zakończenia := DATE_PART('year', NEW.data_zakończenia);

    -- Pętla dla każdego roku, który obejmuje wypożyczenie
    FOR rok IN rok_rozpoczęcia..rok_zakończenia LOOP
        łączna_liczba_dni := 0;

        -- Ustalanie początkowej i końcowej daty dla danego roku
        początek_roku := DATE (rok || '-01-01');

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


        -- Obliczanie liczby dni wypożyczenia w danym roku
		SELECT SUM(
			LEAST(data_zakończenia, koniec_roku) - GREATEST(data_rozpoczęcia, początek_roku)
		) INTO łączna_liczba_dni
        FROM Wypożyczenia
        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)
        );

        -- Jeżeli suma dni wypożyczenia przekroczy 30, wyrzuć błąd
        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;



--min 1 eksponat artysty w muzeum

--Funkcja uwzględnia sytuacje, w których daty ekspozycji i magazynowania mogą się nakładać/dopełniać, np. chcemy wypożyczyć obraz w terminie 01.01.2024-10.01.2024, 
--a 1 inne dzieło artysty znajduje się na ekspozycji 01.01.2024-05.01.2024, a potem w magazynie 06.01.2024-10.01.2024. Ostatecznie intresujące nas dzieło można wypożyczyć, bo całkowity okres przebywania innego dzieła artysty w muzeum(na ekspozycji i w magazynie) pokrywa okres wypozyczenia
--Opis działania: szukam okresu ekspozycji lub magazynowania pokrywajacego okres wypożyczenia. zaczynam od daty rozpoczecia wypozyczenia i sprawdzam czy co najmniej 1 dzien wypożyczenia jest pokryty. Jeśli nie jest, to znaczy, że wypożyczenie nie jest możliwe. jesli jest to notuję datę końca i sprawdzam dla kolejnego okresu zaczynającego się dzień po dacie końca.
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;

--rok urodzenia i smierci
CREATE OR REPLACE FUNCTION sprawdz_date_smierci()
RETURNS TRIGGER AS $$
BEGIN
    -- Jeśli rok śmierci został podany, sprawdzamy, czy jest większy lub równy niż rok urodzenia
    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;

    -- Jeśli wszystko jest OK, zwracamy nowy rekord
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

--rozpoczecie przed zakonczeniem
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;

-- Trigger dla tabeli Wypożyczenia
CREATE OR REPLACE TRIGGER sprawdź_czas_wypożyczeń_trigger
AFTER INSERT OR UPDATE ON Wypożyczenia
FOR EACH ROW 
EXECUTE PROCEDURE sprawdź_czas_wypożyczeń();


--min 1 eksponat artysty w muzeum
CREATE OR REPLACE TRIGGER sprawdź_min_eksponaty_artysty_trigger
AFTER INSERT OR UPDATE ON Wypożyczenia
FOR EACH ROW
EXECUTE PROCEDURE sprawdź_min_eksponaty_artysty();

--rok urodzenia i smierci
CREATE OR REPLACE TRIGGER sprawdz_date_smierci_trigger
BEFORE INSERT OR UPDATE ON Artysta
FOR EACH ROW
EXECUTE FUNCTION sprawdz_date_smierci();

--trigger daty
-- Trigger dla tabeli Wypożyczenia
CREATE OR REPLACE TRIGGER sprawdź_datę_trigger_wypożyczenia
BEFORE INSERT OR UPDATE ON Wypożyczenia
FOR EACH ROW
EXECUTE FUNCTION sprawdź_datę();

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

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

-- Przykładowe dane do tabeli Artysta
INSERT INTO Artysta (imię, nazwisko, rok_urodzenia, rok_śmierci)
VALUES
('Pablo', 'Picasso', 1881, 1973),
('Vincent', 'van Gogh', 1853, 1890),
('Claude', 'Monet', 1840, 1926),
('Leonardo', 'da Vinci', 1452, 1519),
('Frida', 'Kahlo', 1907, 1954);
INSERT INTO Artysta (imię, nazwisko, rok_urodzenia) VALUES ('David', 'Hockney', 1937);

-- Przykładowe dane do tabeli Galeria
INSERT INTO Galeria (nazwa, miasto) VALUES 
('Galeria Sztuki Nowoczesnej', 'Warszawa'),
('Galeria Uffizi', 'Florencja'),
('Galeria Louvre', 'Paryż'),
('Galeria Tate Modern', 'Londyn');

-- Przykładowe dane do tabeli Instytucja
INSERT INTO Instytucja (nazwa, miasto) VALUES
('Muzeum Narodowe w Warszawie', 'Warszawa'),
('Muzeum Guggenheima', 'Nowy Jork'),
('Muzeum Van Gogha', 'Amsterdam'),
('Muzeum Tate', 'Londyn');

-- Przykładowe dane do tabeli Magazyn
INSERT INTO Magazyn (nazwa, miasto) VALUES
('Magazyn A', 'Warszawa'),
('Magazyn B', 'Londyn'),
('Magazyn C', 'Paryż');

-- Przykładowe dane do tabeli Sala
INSERT INTO Sala (ID_galerii, nazwa_sali) VALUES
(1, 'Sala 1'),
(1, 'Sala 2'),
(2, 'Sala Główna'),
(3, 'Sala Nowoczesna');

-- Przykładowe dane do tabeli Eksponat
INSERT INTO Eksponat (tytuł, typ, wysokość, szerokość, waga, ID_artysty, najcenniejszy) VALUES
('Guernica', 'Obraz', 349, 777, 120, 1, TRUE),
('Panny z Awinionu', 'Obraz', 244, 234, 2, 1, TRUE),
('Starry Night', 'Obraz', 74, 92, 40, 2, TRUE),
('Water Lilies', 'Obraz', 200, 300, 150, 3, FALSE),
('Mona Lisa', 'Obraz', 77, 53, 30, 4, TRUE),
('The Two Fridas', 'Obraz', 178, 199, 90, 5, FALSE);

-- Przykładowe dane do tabeli Historia_magazynowania
INSERT INTO Historia_magazynowania (ID_eksponatu, ID_magazynu, cel_przechowywania, data_rozpoczęcia, data_zakończenia) VALUES
(1, 1, 'Przechowywanie w magazynie', '2025-01-21', NULL),
(2, 2, 'Przechowywanie w magazynie', '2025-01-19', NULL),
(3, 3, 'Przechowywanie w magazynie', '2025-02-02', NULL),
(5, 2, 'Przechowywanie w magazynie', '2025-02-02', NULL),
(6, 3, 'Przechowywanie w magazynie', '2025-02-02', NULL);
-- Przykładowe dane do tabeli Historia_ekspozycji
INSERT INTO Historia_ekspozycji (ID_eksponatu, ID_sali, data_rozpoczęcia, data_zakończenia) VALUES
(1, 1, '2025-01-11', '2025-01-20'),
(2, 2, '2025-01-01', '2025-01-18'),
(4, 3, '2025-01-15', NULL);

-- Przykładowe dane do tabeli Wypożyczenia
INSERT INTO Wypożyczenia (ID_eksponatu, ID_instytucji, data_rozpoczęcia, data_zakończenia) VALUES
(1, 1, '2025-01-01', '2025-01-10');
