

CREATE TABLE Artist (
	id SERIAL PRIMARY KEY,
	name VARCHAR(255) NOT NULL,
	surname VARCHAR(255) NOT NULL,
	birth_date DATE NOT NULL,
	death_date DATE
);

CREATE TABLE LocationCategory(
	id SERIAL PRIMARY KEY,
	category VARCHAR(10) 
);
INSERT INTO LocationCategory (category) VALUES
('galeria'), ('magazyn'), ('instytucja');

CREATE TABLE Location (
	id SERIAL PRIMARY KEY,
	name VARCHAR(254) NOT NULL,
	category INTEGER NOT NULL,
	hall_nr INTEGER,
	city VARCHAR(254),
	FOREIGN KEY (category) REFERENCES LocationCategory(id),
	CHECK (
	(category != 1 OR hall_nr IS NOT NULL) OR
	(category != 2 OR city IS NOT NULL)
    )
);	
INSERT INTO Location (name, category) VALUES
('Magazyn', 2);

CREATE TABLE ExhibitCategory (
	id SERIAL PRIMARY KEY,
    category VARCHAR(255)
);
INSERT INTO ExhibitCategory (category) VALUES
('obraz'), ('rzezba'), ('fotografia');

CREATE TABLE Exhibit (
	id SERIAL PRIMARY KEY,
	id_author INTEGER REFERENCES Artist(id) ON DELETE CASCADE,
	id_location INTEGER REFERENCES Location(id) DEFAULT 1,
	title VARCHAR(255) NOT NULL,
	category INTEGER NOT NULL,
	out_days INTEGER DEFAULT 0 NOT NULL,
	height INTEGER,
	width INTEGER,
	weight INTEGER,
	FOREIGN KEY (category) REFERENCES ExhibitCategory(id)
);

CREATE TABLE Logs (
	id SERIAL PRIMARY KEY,
	id_exhibit INTEGER REFERENCES Exhibit(id) NOT NULL,
	id_location INTEGER REFERENCES Location(id) NOT NULL,
	beginning_date DATE DEFAULT CURRENT_DATE,
	end_date DATE DEFAULT NULL,
	CHECK (end_date >= beginning_date)
);

CREATE OR REPLACE FUNCTION is_displayed(
	id INTEGER
)
RETURNS BOOLEAN AS $$
BEGIN	
	IF id IN (SELECT DISTINCT id_exhibit from Logs) THEN
		RETURN TRUE;
	ELSE
		RETURN FALSE;
	END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION add_log(
	id_exhibit INTEGER,
	id_location INTEGER,
	beginning_date DATE DEFAULT CURRENT_DATE,
	end_date DATE DEFAULT NULL
)
RETURNS VOID AS $$
BEGIN
	IF is_displayed(id_exhibit) THEN
		UPDATE Logs
		SET end_date = add_log.beginning_date
		WHERE Logs.id = (SELECT MAX(id) FROM Logs WHERE add_log.id_exhibit = Logs.id_exhibit) AND Logs.end_date IS NULL;
	END IF;

	IF (SELECT category FROM Location WHERE id = add_log.id_location) = 3 THEN
		UPDATE Exhibit
		SET out_days = out_days + (end_date - beginning_date)
		WHERE Exhibit.id = id_exhibit;
	END IF;

	INSERT INTO Logs(id_exhibit, id_location, beginning_date, end_date)
	VALUES (id_exhibit, id_location, beginning_date, end_date);
END;
$$ LANGUAGE plpgsql;

-- Wstawianie danych do tabeli Artist
INSERT INTO Artist (name, surname, birth_date, death_date) VALUES
('Leonardo', 'da Vinci', '1452-04-15', '1519-05-02'),
('Michelangelo', 'Buonarroti', '1475-03-06', '1564-02-18'),
('Raphael', 'Sanzio', '1483-04-06', '1520-04-06'),
('Vincent', 'van Gogh', '1853-03-30', '1890-07-29'),
('Pablo', 'Picasso', '1881-10-25', '1973-04-08');

-- Wstawianie danych do tabeli Location
INSERT INTO Location (name, category, hall_nr, city) VALUES
('Galeria Narodowa', 1, 1, NULL),
('Muzeum Sztuki Nowoczesnej', 1, 2, NULL),
('Centrum Sztuki Współczesnej', 1, 3, NULL),
('Muzeum Narodowe w Krakowie', 3, NULL, 'Kraków'),
('Muzeum Narodowe w Warszawie', 3, NULL, 'Warszawa');

-- Wstawianie danych do tabeli Exhibit
INSERT INTO Exhibit (id_author, id_location, title, category, height, width, weight) VALUES
(1, 1, 'Mona Lisa', 1, 77, 53, NULL),
(1, 1, 'Ostatnia Wieczerza', 1, 460, 880, NULL),
(1, 2, 'Dama z gronostajem', 1, 55, 40, 650),
(2, 5, 'Dawid', 2, 517, NULL, NULL),
(3, 3, 'Szkoła Ateńska', 1, 500, 770, NULL),
(4, 1, 'Słoneczniki', 1, 93, 73, NULL),
(5, 2, 'Guernica', 1, 349, 776, NULL);

-- Wstawianie danych do tabeli Logs
INSERT INTO Logs (id_exhibit, id_location, beginning_date, end_date) VALUES
(1, 1, '2025-01-01', NULL),
(2, 1, '2024-12-03', NULL),
(3, 2, '2024-10-01', '2025-01-30'),
(4, 5, '2025-01-15', '2025-01-27'),
(5, 3, '2025-03-01', '2025-09-30'),
(6, 4, '2025-04-15', '2025-10-15'),
(7, 2, '2025-05-01', '2025-11-30');
