
CREATE TABLE artists (
    artist_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    year_of_birth INT,
    year_of_death INT,
    CONSTRAINT valid_years CHECK (year_of_death IS NULL OR year_of_death >= year_of_birth)
);

CREATE TABLE exhibits (
    exhibit_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    type_of_work TEXT NOT NULL,
    height NUMERIC NOT NULL,
    width NUMERIC NOT NULL,
    weight NUMERIC NOT NULL,
    artist_id INT REFERENCES artists(artist_id) ON DELETE CASCADE,
    is_invaluable BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE institutions (
    institution_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE exhibit_status (
    status_id SERIAL PRIMARY KEY,
    exhibit_id INT REFERENCES exhibits(exhibit_id) ON DELETE CASCADE,
    status_type TEXT NOT NULL CHECK (status_type IN ('storage', 'display', 'loan')),
    institution_id INT REFERENCES institutions(institution_id) ON DELETE SET NULL,
    start_date DATE NOT NULL,
    end_date DATE,
    CONSTRAINT valid_loan_period CHECK (
        (status_type = 'loan' AND end_date IS NOT NULL AND end_date <= start_date + INTERVAL '30 days' AND end_date >= start_date) OR
        (status_type <> 'loan')
    )
);

CREATE TABLE exhibit_history (
    history_id SERIAL PRIMARY KEY,
    exhibit_id INT REFERENCES exhibits(exhibit_id) ON DELETE CASCADE,
    status_type TEXT NOT NULL CHECK (status_type IN ('storage', 'display', 'loan')),
    institution_id INT REFERENCES institutions(institution_id) ON DELETE SET NULL,
    start_date DATE NOT NULL,
    end_date DATE
);

-- Stored procedures

--DROP FUNCTION add_exhibit_status;

--DROP FUNCTION update_indefinite_status_end_date;

CREATE OR REPLACE PROCEDURE add_exhibit_status(
    p_exhibit_id INT,
    p_status_type TEXT,
    p_institution_id INT,
    p_start_date DATE,
    p_end_date DATE
) AS $$
BEGIN

    IF NOT EXISTS (SELECT 1 FROM exhibits WHERE exhibit_id = p_exhibit_id) THEN
        RAISE EXCEPTION 'Exhibit ID % does not exist.', p_exhibit_id;
    END IF;

    
    IF p_institution_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM institutions WHERE institution_id = p_institution_id) THEN
        RAISE EXCEPTION 'Institution ID % does not exist.', p_institution_id;
    END IF;	

    DELETE FROM exhibit_status WHERE exhibit_id = p_exhibit_id;

        INSERT INTO exhibit_status (exhibit_id, status_type, institution_id, start_date, end_date)
    VALUES (p_exhibit_id, p_status_type, p_institution_id, p_start_date, p_end_date);

        INSERT INTO exhibit_history (exhibit_id, status_type, institution_id, start_date, end_date)
    VALUES (p_exhibit_id, p_status_type, p_institution_id, p_start_date, p_end_date);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION enforce_invaluable_exhibit_rule() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status_type = 'loan' AND EXISTS (
        SELECT 1 FROM exhibits WHERE exhibit_id = NEW.exhibit_id AND is_invaluable = TRUE
    ) THEN
        RAISE EXCEPTION 'Invaluable exhibits cannot be loaned.';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER add_exhibit_status_trigger
AFTER INSERT ON exhibit_status
FOR EACH ROW
EXECUTE FUNCTION enforce_artist_exhibit_rule2();

CREATE OR REPLACE TRIGGER enforce_artist_exhibit_rule_trigger
AFTER UPDATE OR DELETE ON exhibit_status
FOR EACH ROW
EXECUTE FUNCTION enforce_artist_exhibit_rule2();

CREATE OR REPLACE TRIGGER enforce_artist_exhibit_rule_trigger_exhibits
AFTER DELETE ON exhibits
FOR EACH ROW
EXECUTE FUNCTION enforce_artist_exhibit_rule();

CREATE OR REPLACE TRIGGER enforce_invaluable_exhibit_rule_trigger
BEFORE INSERT OR UPDATE ON exhibit_status
FOR EACH ROW
EXECUTE FUNCTION enforce_invaluable_exhibit_rule();

CREATE OR REPLACE PROCEDURE update_indefinite_status_end_date(p_exhibit_id int, p_start_date date)
AS $$
DECLARE
    last_status_start_date DATE;
    last_status_end_date DATE;
BEGIN
    SELECT start_date INTO last_status_start_date
    FROM exhibit_history
    WHERE exhibit_id = p_exhibit_id
    ORDER BY start_date DESC
    LIMIT 1;

    SELECT end_date INTO last_status_end_date
    FROM exhibit_history
    WHERE exhibit_id = p_exhibit_id
    ORDER BY start_date DESC
    LIMIT 1;

    IF last_status_start_date IS NOT NULL AND p_start_date < last_status_start_date THEN
        RAISE EXCEPTION 'Invalid start_date: The new start_date (%) cannot be before the last status start_date (%).', NEW.start_date, last_status_start_date;
    END IF;
    
    --IF last_status_end_date IS NULL

    UPDATE exhibit_history
    SET end_date = p_start_date
    WHERE exhibit_id = p_exhibit_id
      AND history_id = (
          SELECT history_id
          FROM exhibit_history
          WHERE exhibit_id = p_exhibit_id
          ORDER BY start_date DESC
          LIMIT 1
      );
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION call_procedure() RETURNS TRIGGER AS $$
BEGIN
    CALL update_indefinite_status_end_date(NEW.exhibit_id, NEW.start_date);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER update_indefinite_status_end_date_trigger
AFTER INSERT ON exhibit_status
FOR EACH ROW
EXECUTE FUNCTION call_procedure();

-- Example data insertion

INSERT INTO artists (name, year_of_birth, year_of_death) VALUES
('Unknown Artist', NULL, NULL),
('Leonardo da Vinci', 1452, 1519),
('Pablo Picasso', 1881, 1973),
('Vincent van Gogh', 1853, 1890),
('Claude Monet', 1840, 1926);

INSERT INTO exhibits (title, type_of_work, height, width, weight, artist_id, is_invaluable) VALUES
('Mona Lisa', 'Painting', 77, 53, 8, 2, TRUE),
('Guernica', 'Painting', 349, 776, 100, 3, FALSE),
('Ancient Vase', 'Ceramics', 30, 20, 10, 1, FALSE),
('The Starry Night', 'Painting', 73.7, 92.1, 15, 4, TRUE),
('Water Lilies', 'Painting', 200, 180, 30, 5, FALSE),
('The Last Supper', 'Painting', 460, 880, 200, 2, TRUE);

INSERT INTO institutions (name) VALUES
('Louvre Museum'),
('Metropolitan Museum of Art'),
('British Museum'),
('Uffizi Gallery');
