-- Usuń tabeli w odpowiedniej kolejności

-- tables

-- Table: Sala
CREATE TABLE Sala (
    ID SERIAL PRIMARY KEY,
    nazwa VARCHAR(200) NOT NULL
);

-- Table: Miasto
CREATE TABLE Miasto (
    ID SERIAL PRIMARY KEY,
    nazwa VARCHAR(200) NOT NULL
);

-- Table: Artysta
CREATE TABLE Artysta (
    ID SERIAL PRIMARY KEY,
    imie VARCHAR(200) NOT NULL, 
    nazwisko VARCHAR(200) NOT NULL,  
    rok_urodzenia DATE NOT NULL,
    rok_smierci DATE,
    CONSTRAINT urodziny_przed_smiercia
        CHECK (
            (rok_smierci IS NULL) OR (rok_urodzenia <= rok_smierci)  -- Warunek: urodziny muszą być przed śmiercią (jeśli data śmierci jest podana)
        )
);

-- Table: Eksponat
CREATE TABLE Eksponat (
    ID SERIAL PRIMARY KEY,
    tytul VARCHAR(200) NOT NULL,
    wysokosc_cm DECIMAL(10,2) NOT NULL CHECK (wysokosc_cm > 0),
    szerokosc_cm DECIMAL(10,2) NOT NULL CHECK (szerokosc_cm > 0),
    waga DECIMAL(10,2) NOT NULL CHECK (waga > 0),
    czy_najcenniejszy VARCHAR(3) NOT NULL CHECK (czy_najcenniejszy IN ('Tak', 'Nie')),
    artysta_id INT NOT NULL REFERENCES Artysta(ID)
);

-- Table: Galeria
CREATE TABLE Galeria (
    ID SERIAL PRIMARY KEY,  
    nazwa VARCHAR(200) NOT NULL,
    sala_id INT NOT NULL REFERENCES Sala(ID)
);

-- Table: Instytucja
CREATE TABLE Instytucja (
    ID SERIAL PRIMARY KEY,
    nazwa VARCHAR(200) NOT NULL,
    miasto_id INT NOT NULL REFERENCES Miasto(ID)
);

-- Table: Historia_ekspozycji
CREATE TABLE Historia_ekspozycji (
    ID SERIAL PRIMARY KEY,
    eksponat_id INT NOT NULL REFERENCES Eksponat(ID),
    galeria_id INT NOT NULL REFERENCES Galeria(ID),  
    data_roz DATE NOT NULL,
    data_zak DATE NOT NULL,
    sala_id INT NOT NULL REFERENCES Sala(ID),
    CONSTRAINT sprawdz_daty_eks CHECK (data_zak >= data_roz) 
);

-- Table: Historia_magazyn
CREATE TABLE Historia_magazyn (
    ID SERIAL PRIMARY KEY,
    eksponat_id INT NOT NULL REFERENCES Eksponat(ID),
    data_roz DATE NOT NULL,
    data_zak DATE NOT NULL,
    cel VARCHAR(200) NOT NULL,
    CONSTRAINT sprawdz_daty_mag CHECK (data_zak >= data_roz) 
);

-- Table: Historia_wypozyczen
CREATE TABLE Historia_wypozyczen (
    ID SERIAL PRIMARY KEY,
    data_wypozyczenia DATE NOT NULL,
    data_oddania DATE NOT NULL,
    instytucja_id INT NOT NULL REFERENCES Instytucja(ID),
    eksponat_id INT NOT NULL REFERENCES Eksponat(ID),
    CONSTRAINT sprawdz_daty_wyp CHECK (data_oddania >= data_wypozyczenia)
);

CREATE OR REPLACE FUNCTION sprawdz_nakladajace_sie_eksponaty_ekspozycje() 
RETURNS TRIGGER AS $$
BEGIN
    -- Sprawdzamy nakładanie w tej samej tabeli
    IF EXISTS (
        SELECT 1 FROM Historia_ekspozycji 
        WHERE eksponat_id = NEW.eksponat_id
        AND (NEW.data_roz BETWEEN data_roz AND data_zak 
             OR NEW.data_zak BETWEEN data_roz AND data_zak
             OR data_roz BETWEEN NEW.data_roz AND NEW.data_zak
             OR data_zak BETWEEN NEW.data_roz AND NEW.data_zak)
    ) THEN
        RAISE EXCEPTION 'Eksponat % jest już wystawiony w tym okresie.', NEW.eksponat_id;
    END IF;

    -- Sprawdzamy, czy eksponat nie jest w magazynie w tym okresie
    IF EXISTS (
        SELECT 1 FROM Historia_magazyn 
        WHERE eksponat_id = NEW.eksponat_id
        AND (NEW.data_roz BETWEEN data_roz AND data_zak 
             OR NEW.data_zak BETWEEN data_roz AND data_zak
             OR data_roz BETWEEN NEW.data_roz AND NEW.data_zak
             OR data_zak BETWEEN NEW.data_roz AND NEW.data_zak)
    ) THEN
        RAISE EXCEPTION 'Eksponat % jest już w magazynie w tym okresie.', NEW.eksponat_id;
    END IF;

    -- Sprawdzamy, czy eksponat nie jest wypożyczony w tym okresie
    IF EXISTS (
        SELECT 1 FROM Historia_wypozyczen 
        WHERE eksponat_id = NEW.eksponat_id
        AND (NEW.data_roz BETWEEN data_wypozyczenia AND data_oddania 
             OR NEW.data_zak BETWEEN data_wypozyczenia AND data_oddania
             OR data_wypozyczenia BETWEEN NEW.data_roz AND NEW.data_zak
             OR data_oddania BETWEEN NEW.data_roz AND NEW.data_zak)
    ) THEN
        RAISE EXCEPTION 'Eksponat % jest już wypożyczony w tym okresie.', NEW.eksponat_id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_sprawdz_ekspozycje
BEFORE INSERT ON Historia_ekspozycji
FOR EACH ROW
EXECUTE FUNCTION sprawdz_nakladajace_sie_eksponaty_ekspozycje();

CREATE OR REPLACE FUNCTION sprawdz_nakladajace_sie_eksponaty_magazyn() 
RETURNS TRIGGER AS $$
BEGIN
    -- Sprawdzamy nakładanie w tej samej tabeli
    IF EXISTS (
        SELECT 1 FROM Historia_magazyn 
        WHERE eksponat_id = NEW.eksponat_id
        AND (NEW.data_roz BETWEEN data_roz AND data_zak 
             OR NEW.data_zak BETWEEN data_roz AND data_zak
             OR data_roz BETWEEN NEW.data_roz AND NEW.data_zak
             OR data_zak BETWEEN NEW.data_roz AND NEW.data_zak)
    ) THEN
        RAISE EXCEPTION 'Eksponat % jest już w magazynie w tym okresie.', NEW.eksponat_id;
    END IF;

    -- Sprawdzamy, czy eksponat nie jest na ekspozycji w tym okresie
    IF EXISTS (
        SELECT 1 FROM Historia_ekspozycji 
        WHERE eksponat_id = NEW.eksponat_id
        AND (NEW.data_roz BETWEEN data_roz AND data_zak 
             OR NEW.data_zak BETWEEN data_roz AND data_zak
             OR data_roz BETWEEN NEW.data_roz AND NEW.data_zak
             OR data_zak BETWEEN NEW.data_roz AND NEW.data_zak)
    ) THEN
        RAISE EXCEPTION 'Eksponat % jest już na ekspozycji w tym okresie.', NEW.eksponat_id;
    END IF;

    -- Sprawdzamy, czy eksponat nie jest wypożyczony w tym okresie
    IF EXISTS (
        SELECT 1 FROM Historia_wypozyczen 
        WHERE eksponat_id = NEW.eksponat_id
        AND (NEW.data_roz BETWEEN data_wypozyczenia AND data_oddania 
             OR NEW.data_zak BETWEEN data_wypozyczenia AND data_oddania
             OR data_wypozyczenia BETWEEN NEW.data_roz AND NEW.data_zak
             OR data_oddania BETWEEN NEW.data_roz AND NEW.data_zak)
    ) THEN
        RAISE EXCEPTION 'Eksponat % jest już wypożyczony w tym okresie.', NEW.eksponat_id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_sprawdz_magazyn
BEFORE INSERT ON Historia_magazyn
FOR EACH ROW
EXECUTE FUNCTION sprawdz_nakladajace_sie_eksponaty_magazyn();


CREATE OR REPLACE FUNCTION sprawdz_nakladajace_sie_eksponaty_wypozyczenia() 
RETURNS TRIGGER AS $$
BEGIN
    -- Sprawdzamy nakładanie w tej samej tabeli
    IF EXISTS (
        SELECT 1 FROM Historia_wypozyczen 
        WHERE eksponat_id = NEW.eksponat_id
        AND (NEW.data_wypozyczenia BETWEEN data_wypozyczenia AND data_oddania
             OR NEW.data_oddania BETWEEN data_wypozyczenia AND data_oddania
             OR data_wypozyczenia BETWEEN NEW.data_wypozyczenia AND NEW.data_oddania
             OR data_oddania BETWEEN NEW.data_wypozyczenia AND NEW.data_oddania)
    ) THEN
        RAISE EXCEPTION 'Eksponat % jest już wypożyczony w tym okresie.', NEW.eksponat_id;
    END IF;

    -- Sprawdzamy, czy eksponat nie jest na ekspozycji w tym okresie
    IF EXISTS (
        SELECT 1 FROM Historia_ekspozycji 
        WHERE eksponat_id = NEW.eksponat_id
        AND (NEW.data_wypozyczenia BETWEEN data_roz AND data_zak 
             OR NEW.data_oddania BETWEEN data_roz AND data_zak
             OR data_roz BETWEEN NEW.data_wypozyczenia AND NEW.data_oddania
             OR data_zak BETWEEN NEW.data_wypozyczenia AND NEW.data_oddania)
    ) THEN
        RAISE EXCEPTION 'Eksponat % jest już na ekspozycji w tym okresie.', NEW.eksponat_id;
    END IF;

    -- Sprawdzamy, czy eksponat nie jest w magazynie w tym okresie
    IF EXISTS (
        SELECT 1 FROM Historia_magazyn 
        WHERE eksponat_id = NEW.eksponat_id
        AND (NEW.data_wypozyczenia BETWEEN data_roz AND data_zak 
             OR NEW.data_oddania BETWEEN data_roz AND data_zak
             OR data_roz BETWEEN NEW.data_wypozyczenia AND NEW.data_oddania
             OR data_zak BETWEEN NEW.data_wypozyczenia AND NEW.data_oddania)
    ) THEN
        RAISE EXCEPTION 'Eksponat % jest już w magazynie w tym okresie.', NEW.eksponat_id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_sprawdz_wypozyczenia
BEFORE INSERT ON Historia_wypozyczen
FOR EACH ROW
EXECUTE FUNCTION sprawdz_nakladajace_sie_eksponaty_wypozyczenia();



CREATE OR REPLACE PROCEDURE validate_museum_rules()
LANGUAGE plpgsql
AS $$
DECLARE
    loan_violations RECORD;
    violation_found BOOLEAN := FALSE;
    violation_message TEXT := '';
BEGIN
    -- Sprawdzenie naruszeń czasu wypożyczenia
    FOR loan_violations IN (
        WITH ExhibitLoans AS (
            SELECT 
                e.ID AS eksponat_id,
                e.tytul,
                EXTRACT(YEAR FROM hw.data_wypozyczenia) AS loan_year,
                SUM(
                    CASE 
                        WHEN hw.data_oddania IS NOT NULL THEN 
                            (hw.data_oddania - hw.data_wypozyczenia)
                        ELSE 
                            (CURRENT_DATE - hw.data_wypozyczenia)
                    END
                ) AS total_days_loaned
            FROM Eksponat e
            JOIN Historia_wypozyczen hw ON e.ID = hw.eksponat_id
            GROUP BY e.ID, e.tytul, EXTRACT(YEAR FROM hw.data_wypozyczenia)
            HAVING SUM(
                CASE 
                    WHEN hw.data_oddania IS NOT NULL THEN 
                        (hw.data_oddania - hw.data_wypozyczenia)
                    ELSE 
                        (CURRENT_DATE - hw.data_wypozyczenia)
                END
            ) > 30
        )
        SELECT * FROM ExhibitLoans
    ) LOOP
        violation_found := TRUE;
        RAISE NOTICE 'Loan Duration Violation: Eksponat ID % (%) był wypożyczony przez % dni w %',
            loan_violations.eksponat_id,
            loan_violations.tytul,
            loan_violations.total_days_loaned,
            loan_violations.loan_year;
        
        violation_message := violation_message || format(
            E'\nLoan Duration Violation: Eksponat %s był wypożyczony przez %s dni w %s',
            loan_violations.tytul,
            loan_violations.total_days_loaned::text,
            loan_violations.loan_year::text
        );
    END LOOP;

    -- Jeśli znaleziono naruszenia, rzuć wyjątek
    IF violation_found THEN
        RAISE EXCEPTION 'Museum rules violations found: %', violation_message;
    ELSE
        RAISE NOTICE 'All museum rules validated successfully.';
    END IF;
END;
$$;


CREATE OR REPLACE FUNCTION trigger_validate_museum_rules()
RETURNS TRIGGER AS $$
BEGIN
    CALL validate_museum_rules();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Wyzwalacz dla wypożyczeń
CREATE TRIGGER validate_on_loan_change
AFTER INSERT OR UPDATE OR DELETE ON Historia_wypozyczen
FOR EACH STATEMENT EXECUTE FUNCTION trigger_validate_museum_rules();

-- Funkcja wyzwalacza dla zakazu wypożyczania najcenniejszych eksponatów
CREATE OR REPLACE FUNCTION trigger_prevent_loan_of_valuable_exhibit()
RETURNS TRIGGER AS $$
BEGIN
    IF (SELECT czy_najcenniejszy FROM Eksponat WHERE ID = NEW.eksponat_id) = 'Tak' THEN
        RAISE EXCEPTION 'Eksponat oznaczony jako najcenniejszy nie może zostać wypożyczony.';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Wyzwalacz dla zakazu wypożyczeń najcenniejszych eksponatów
CREATE TRIGGER prevent_loan_of_valuable_exhibit
BEFORE INSERT ON Historia_wypozyczen
FOR EACH ROW EXECUTE FUNCTION trigger_prevent_loan_of_valuable_exhibit();

CREATE OR REPLACE FUNCTION SprawdzEksponatyArtysty() 
RETURNS TRIGGER AS $$
BEGIN
    -- Sprawdzamy, czy artysta ma przynajmniej jeden eksponat dostępny w muzeum
    IF NOT EXISTS (
        SELECT 1
        FROM Eksponat e
        WHERE e.artysta_id = (SELECT artysta_id FROM Eksponat WHERE ID = NEW.eksponat_id)
        AND e.ID NOT IN (
            -- Sprawdzamy, czy eksponat nie jest aktualnie wypożyczony
            SELECT he.eksponat_id
            FROM Historia_ekspozycji he
            WHERE he.data_zak >= CURRENT_DATE
        )
        AND e.ID NOT IN (
            -- Sprawdzamy, czy eksponat nie jest w magazynie
            SELECT hm.eksponat_id
            FROM Historia_magazyn hm
            WHERE hm.data_zak >= CURRENT_DATE
        )
    ) THEN
        RAISE EXCEPTION 'Każdy artysta musi mieć co najmniej jeden eksponat w muzeum.';
    END IF;

    -- Zwracamy nowy rekord, aby kontynuować operację (wstawienie)
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER SprawdzEksponatyArtysty_trigger
BEFORE INSERT ON Historia_wypozyczen
FOR EACH ROW
EXECUTE FUNCTION SprawdzEksponatyArtysty();



-- End of file.

