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;

CREATE TABLE artysta (
    idartysty varchar(6) NOT NULL,
    imie varchar(30),
    nazwisko varchar(30),
    rokurodzenia varchar(10),
    roksmierci varchar(10),
    CONSTRAINT artysta_pk PRIMARY KEY (idartysty)
);

CREATE TABLE eksponat (
    ideksponatu varchar(6) NOT NULL,
    tytul varchar(60) NOT NULL,
    typ varchar(30) NOT NULL,
    cennosc boolean NOT NULL,
    wysokosccm decimal(10,2) NOT NULL,
    szerokosccm decimal(10,2) NOT NULL,
    waga decimal(10,3) NOT NULL,
    idartysty varchar(6),
    CONSTRAINT eksponat_pk PRIMARY KEY (ideksponatu),
    CONSTRAINT eksponat_artysta FOREIGN KEY (idartysty) REFERENCES artysta (idartysty)
);

CREATE TABLE galeria (
    idgalerii varchar(6) NOT NULL,
    nazwa varchar(60) NOT NULL,
    miasto varchar(60) NOT NULL,
    CONSTRAINT galeria_pk PRIMARY KEY (idgalerii)
);

CREATE TABLE historia (
    idhistorii varchar(6) NOT NULL,
    idsali varchar(6),
    datarozpoczecia date NOT NULL,
    datazakonczenia date,
    ideksponatu varchar(6) NOT NULL,
    idgalerii varchar(6),
    idinstytucji varchar(6),
    CONSTRAINT historia_pk PRIMARY KEY (idhistorii)
);

CREATE TABLE instytucja (
    idinstytucji varchar(6) NOT NULL,
    nazwa varchar(60) NOT NULL,
    miasto varchar(60) NOT NULL,
    CONSTRAINT instytucja_pk PRIMARY KEY (idinstytucji)
);

ALTER TABLE historia 
ADD CONSTRAINT historia_eksponat FOREIGN KEY (ideksponatu) REFERENCES eksponat (ideksponatu);

ALTER TABLE historia 
ADD CONSTRAINT historia_galeria FOREIGN KEY (idgalerii) REFERENCES galeria (idgalerii);

ALTER TABLE historia 
ADD CONSTRAINT historia_instytucja FOREIGN KEY (idinstytucji) REFERENCES instytucja (idinstytucji);

ALTER TABLE artysta 
ADD CONSTRAINT sprawdz_urodzenie_smierc 
CHECK (rokurodzenia IS NULL OR roksmierci IS NULL OR rokurodzenia < roksmierci);

ALTER TABLE historia 
ADD CONSTRAINT sprawdz_daty 
CHECK (datazakonczenia IS NULL OR datarozpoczecia < datazakonczenia);


CREATE OR REPLACE FUNCTION sprawdzeksponatpozaMuzeum()
RETURNS TRIGGER AS $$
DECLARE
    dnipoza INTEGER; 
    czycenny BOOLEAN;
BEGIN
 
    SELECT cennosc
    INTO czycenny
    FROM eksponat
    WHERE ideksponatu = NEW.ideksponatu;

    IF NEW.idgalerii IS NOT NULL THEN
        RETURN NEW;
    END IF;

    IF czycenny AND NEW.idgalerii IS NULL AND NEW.idinstytucji IS NOT NULL THEN
        RAISE EXCEPTION 'Cenny eksponat nie może być wypożyczany poza galerię (ideksponatu: %)', NEW.ideksponatu;
    END IF;

    SELECT COALESCE(SUM(datazakonczenia - datarozpoczecia), 0) + 
           (NEW.datazakonczenia - NEW.datarozpoczecia)
    INTO dnipoza
    FROM historia
    WHERE ideksponatu = NEW.ideksponatu 
    AND idgalerii IS NULL -- Eksponat nie jest w galerii
    AND idinstytucji IS NOT NULL -- Eksponat jest w instytucji
    AND EXTRACT(YEAR FROM datarozpoczecia) = EXTRACT(YEAR FROM NEW.datarozpoczecia);

    IF dnipoza > 30 THEN
        RAISE EXCEPTION 'Eksponat nie może być poza muzeum (w instytucji) dłużej niż 30 dni rocznie (ideksponatu: %)', NEW.ideksponatu;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER sprawdzeksponatpozaMuzeum_trigger
BEFORE INSERT OR UPDATE ON historia
FOR EACH ROW
EXECUTE FUNCTION sprawdzeksponatpozaMuzeum();

CREATE OR REPLACE VIEW artystabezzeksponatu AS
SELECT a.idartysty, a.imie, a.nazwisko
FROM artysta a
LEFT JOIN eksponat e ON a.idartysty = e.idartysty
WHERE e.ideksponatu IS NULL;