DROP TABLE Author cascade;
DROP TABLE Exhibit cascade;
DROP TABLE Exhibit_Action cascade;
DROP TABLE Galery cascade;
DROP TABLE Lease_Institution cascade;

--Tables

CREATE TABLE Author (
	Auth_id SERIAL  PRIMARY KEY,
	Auth_name VARCHAR(50) NOT NULL,
	Auth_surname VARCHAR(50) NOT NULL,
   	Auth_birth_year INTEGER NOT NULL,
	Author_death_year INTEGER
);

CREATE TABLE Exhibit (
	Ex_id SERIAL  PRIMARY KEY,
	Ex_name VARCHAR(70) NOT NULL,
	Auth_id INTEGER,
	Ex_type VARCHAR(70) NOT NULL,
	Ex_width NUMERIC(7,2) NOT NULL CHECK(Ex_width>0),
	Ex_height NUMERIC(7,2) NOT NULL CHECK(Ex_height>0),
	Ex_weight NUMERIC(7,2) NOT NULL CHECK(Ex_weight>0),
	Ex_is_important BOOLEAN NOT NULL DEFAULT FALSE
);


CREATE TABLE Exhibit_Action (
	Act_id SERIAL  PRIMARY KEY,
	Ex_id INTEGER NOT NULL,
	Act_type CHAR(1) NOT NULL,
	Inst_id INTEGER,
	Gal_id INTEGER,
	Date_of_change DATE NOT NULL,
	Date_of_end DATE
);

CREATE TABLE Lease_Institution (
	Inst_id SERIAL NOT NULL,
	Inst_name VARCHAR(100) NOT NULL,
	Inst_city VARCHAR(70) NOT NULL
);

CREATE TABLE Galery (
	Gal_id SERIAL NOT NULL,
	Gal_name VARCHAR(50) NOT NULL,
	Gal_room INTEGER NOT NULL CHECK(Gal_room>0)
);

CREATE OR REPLACE FUNCTION delete_autor()
RETURNS TRIGGER AS
$$
DECLARE
    v_count INTEGER;
BEGIN
    -- Sprawdź czy istnieją inne eksponaty tego autora
    SELECT COUNT(*)
    INTO v_count
    FROM exhibit
    WHERE auth_id = OLD.auth_id;
    
    -- Usuń autora tylko jeśli nie ma już innych jego eksponatów
    IF v_count = 0 THEN
        DELETE FROM author
        WHERE auth_id = OLD.auth_id;
    END IF;
    
    RETURN OLD;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Wystąpił błąd podczas usuwania autora: %', SQLERRM;
        RAISE;
END;
$$ LANGUAGE plpgsql;

-- Utworzenie triggera
CREATE OR REPLACE TRIGGER trg_delete_autor
AFTER DELETE ON exhibit
FOR EACH ROW
EXECUTE FUNCTION delete_autor();

CREATE OR REPLACE FUNCTION In_Storage(M_Ex_id INTEGER)
RETURNS BOOLEAN AS $$
DECLARE a INTEGER;
BEGIN
	SELECT COUNT(Act_id) INTO a FROM Exhibit_Action WHERE Ex_id=M_ex_id AND Act_type='m' AND Date_of_end IS NULL;
	
	IF a=1 THEN
		RETURN TRUE;
	ELSE 
		RETURN FALSE;
	END IF;
END;
$$ LANGUAGE plpgsql;
--------------
CREATE OR REPLACE FUNCTION create_exhibit_action()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO Exhibit_Action (
        Ex_id,
        Act_type,
        Inst_id,
        Gal_id,
        Date_of_change,
        Date_of_end
    ) VALUES (
        NEW.Ex_id,
        'm',
        NULL,
        NULL,
        CURRENT_DATE,
        NULL
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER exhibit_after_insert
    AFTER INSERT ON Exhibit
    FOR EACH ROW
    EXECUTE FUNCTION create_exhibit_action();
----
CREATE OR REPLACE FUNCTION On_Exhibition(M_Ex_id INTEGER)
RETURNS BOOLEAN
AS $$
DECLARE 
    a INTEGER;
BEGIN
    SELECT COUNT(Act_id) 
    INTO a 
    FROM Exhibit_Action 
    WHERE Ex_id = M_Ex_id 
    AND Act_type = 'e' 
    AND Date_of_end IS NULL;
    
    RETURN a = 1;
END;
$$ LANGUAGE plpgsql;
---
CREATE OR REPLACE PROCEDURE Move_to_exhibition(M_Ex_id INTEGER, M_Gal_id INTEGER)
LANGUAGE plpgsql
AS $$
BEGIN
    IF In_Storage(M_Ex_id)  THEN
        -- Najpierw zaktualizuj poprzedni rekord
        UPDATE Exhibit_Action 
        SET Date_of_end = CURRENT_DATE 
        WHERE Ex_id = M_Ex_id 
        AND Date_of_end IS NULL;

        -- Następnie dodaj nowy rekord
        INSERT INTO Exhibit_Action 
            (Ex_id, Act_type, Inst_id, Gal_id, Date_of_change, Date_of_end)
        VALUES 
            (M_Ex_id, 'e', NULL, M_Gal_id, CURRENT_DATE, NULL);

        RAISE NOTICE 'Exhibit moved successfully';
    ELSE
        RAISE EXCEPTION 'Exhibit not available in storage';
    END IF;
END;
$$;
-----
CREATE OR REPLACE PROCEDURE Move_to_storage(M_Ex_id INTEGER)
LANGUAGE plpgsql
AS $$
BEGIN
    IF On_Exhibition(M_Ex_id)  THEN
        -- Najpierw zaktualizuj poprzedni rekord
        UPDATE Exhibit_Action 
        SET Date_of_end = CURRENT_DATE 
        WHERE Ex_id = M_Ex_id 
        AND Date_of_end IS NULL;

        -- Następnie dodaj nowy rekord
        INSERT INTO Exhibit_Action 
            (Ex_id, Act_type, Inst_id, Gal_id, Date_of_change, Date_of_end)
        VALUES 
            (M_Ex_id, 'm', NULL, NULL, CURRENT_DATE, NULL);

        RAISE NOTICE 'Exhibit moved successfully';
    ELSE
        RAISE EXCEPTION 'Exhibit not available on exhibition';
    END IF;
END;
$$;
-------------
-----
----

CREATE OR REPLACE FUNCTION Is_important(M_Ex_id INTEGER)
RETURNS BOOLEAN
AS $$
DECLARE 
    Is_imp BOOLEAN;
BEGIN
    SELECT Ex_is_important 
    INTO Is_imp 
    FROM Exhibit 
    WHERE Ex_id = M_Ex_id;
    
    IF Is_imp = TRUE THEN
        RAISE EXCEPTION 'Cannot lease due to being too important';
    ELSE 
        RETURN FALSE;
    END IF;
END;
$$ LANGUAGE plpgsql;

-----------
CREATE OR REPLACE FUNCTION Check_lease_time(
    M_Ex_id INTEGER, 
    M_start_date DATE, 
    M_end_date DATE
)
RETURNS BOOLEAN
AS $$
DECLARE
    Used_days INTEGER;
    New_lease_days INTEGER;
BEGIN
    -- Sprawdzenie kolejności dat
    IF M_end_date <= M_start_date THEN
        RAISE EXCEPTION 'End date must be later than start date';
    END IF;

    -- Obliczanie sumy dni wypożyczenia w roku dla podanego eksponatu
    SELECT COALESCE(
        SUM(
            (Date_of_end - Date_of_change)
        ), 0
    )
    INTO Used_days
    FROM Exhibit_Action
    WHERE Ex_id = M_Ex_id 
    AND Act_type = 'w'  -- tylko wypożyczenia
    AND EXTRACT(YEAR FROM Date_of_change) = EXTRACT(YEAR FROM M_start_date);

    -- Obliczanie długości nowego wypożyczenia
    New_lease_days := (M_end_date - M_start_date);

    -- Sprawdzenie czy suma dni nie przekroczy 30
    IF (Used_days + New_lease_days) < 30 THEN
        RETURN TRUE;
    ELSE
        RAISE EXCEPTION 'Total lease days in year would exceed 30 days. Currently used: % days, Requested: % days', 
            Used_days, New_lease_days;
    END IF;
END;
$$ LANGUAGE plpgsql;
--------

CREATE OR REPLACE FUNCTION Check_author(M_Ex_id INTEGER)
RETURNS BOOLEAN
AS $$
DECLARE 
    Remaining_inhouse INTEGER := 0;
BEGIN
    -- Sprawdzenie liczby dzieł autora w magazynie
    SELECT COUNT(*) 
    INTO Remaining_inhouse 
    FROM Exhibit 
    LEFT JOIN Exhibit_Action ON Exhibit.Ex_id = Exhibit_Action.Ex_id 
    WHERE Exhibit.Ex_id = M_Ex_id 
    AND Exhibit_Action.Date_of_end IS NULL;
    
    -- Sprawdzenie czy można wypożyczyć
    IF Remaining_inhouse > 0 
       OR (SELECT Auth_id FROM Exhibit WHERE Ex_id = M_Ex_id) IS NULL THEN
        RETURN TRUE;
    ELSE 
        RAISE EXCEPTION 'Cannot lease due to being authors only work in storage';
    END IF;
END;
$$ LANGUAGE plpgsql;
----------
CREATE OR REPLACE PROCEDURE Lease(
    M_Ex_id INTEGER, 
    M_Inst_id INTEGER, 
    M_start_date DATE, 
    M_end_date DATE
)
LANGUAGE plpgsql
AS $$
BEGIN
    IF (On_Exhibition(M_Ex_id) OR In_Storage(M_Ex_id)) 
       AND NOT Is_important(M_Ex_id) 
       AND Check_author(M_Ex_id) 
       AND Check_lease_time(M_Ex_id, M_start_date, M_end_date) THEN
        
        -- Najpierw zakończ aktualny rekord
        UPDATE Exhibit_Action 
        SET Date_of_end = M_start_date 
        WHERE Ex_id = M_Ex_id 
        AND Date_of_end IS NULL;

        -- Dodaj rekord wypożyczenia
        INSERT INTO Exhibit_Action 
            (Ex_id, Act_type, Inst_id, Gal_id, Date_of_change, Date_of_end)
        VALUES 
            (M_Ex_id, 'w', M_Inst_id, NULL, M_start_date, M_end_date);

        -- Dodaj rekord przeniesienia do magazynu po wypożyczeniu
        INSERT INTO Exhibit_Action 
            (Ex_id, Act_type, Inst_id, Gal_id, Date_of_change, Date_of_end)
        VALUES 
            (M_Ex_id, 'm', NULL, NULL, M_end_date, NULL);

        RAISE NOTICE 'Exhibit leased successfully with automatic storage after lease';
    ELSE
        RAISE EXCEPTION 'Exhibit not available for lease';
    END IF;
END;
$$;
---------------
CREATE OR REPLACE FUNCTION get_exhibit_info(search_name VARCHAR)
RETURNS SETOF RECORD
AS $$
SELECT 
    e.Ex_id,
    e.Ex_name,
    e.Ex_type,
    e.Ex_width,
    e.Ex_height,
    e.Ex_weight,
    e.Ex_is_important,
    -- Dane autora
    a.Auth_name,
    a.Auth_surname,
    a.Auth_birth_year,
    a.Author_death_year,
    -- Informacja o lokalizacji
    CASE 
        WHEN ea.Act_type = 'w' THEN 'Wypożyczenie'::VARCHAR(20)
        WHEN ea.Act_type = 'm' THEN 'Magazyn'::VARCHAR(20)
        WHEN ea.Act_type = 'e' THEN 'Wystawa'::VARCHAR(20)
        ELSE 'Nieznana'::VARCHAR(20)
    END AS Current_location,
    CASE 
        WHEN ea.Act_type = 'w' THEN li.Inst_name
        WHEN ea.Act_type = 'e' THEN g.Gal_name
        ELSE NULL
    END::VARCHAR(100) AS Location_details,
    ea.Date_of_change
FROM 
    Exhibit e
    LEFT JOIN Author a ON e.Auth_id = a.Auth_id
    LEFT JOIN Exhibit_Action ea ON e.Ex_id = ea.Ex_id AND ea.Date_of_end IS NULL
    LEFT JOIN Lease_Institution li ON ea.Inst_id = li.Inst_id
    LEFT JOIN Galery g ON ea.Gal_id = g.Gal_id
WHERE 
    e.Ex_name ILIKE '%' || search_name || '%';
$$ LANGUAGE SQL;
-------------
CREATE OR REPLACE VIEW exhibit_info AS
SELECT 
    e.Ex_id,
    e.Ex_name,
    e.Ex_type,
    e.Ex_width,
    e.Ex_height,
    e.Ex_weight,
    e.Ex_is_important,
    -- Dane autora
    a.Auth_name,
    a.Auth_surname,
    a.Auth_birth_year,
    a.Author_death_year,
    -- Informacja o lokalizacji
    CASE 
        WHEN ea.Act_type = 'w' THEN 'Wypożyczenie'::VARCHAR(20)
        WHEN ea.Act_type = 'm' THEN 'Magazyn'::VARCHAR(20)
        WHEN ea.Act_type = 'e' THEN 'Wystawa'::VARCHAR(20)
        ELSE 'Nieznana'::VARCHAR(20)
    END AS Current_location,
    CASE 
        WHEN ea.Act_type = 'w' THEN li.Inst_name
        WHEN ea.Act_type = 'e' THEN g.Gal_name
        ELSE NULL
    END::VARCHAR(100) AS Location_details,
    ea.Date_of_change
FROM 
    Exhibit e
    LEFT JOIN Author a ON e.Auth_id = a.Auth_id
    LEFT JOIN Exhibit_Action ea ON e.Ex_id = ea.Ex_id AND ea.Date_of_end IS NULL
    LEFT JOIN Lease_Institution li ON ea.Inst_id = li.Inst_id
    LEFT JOIN Galery g ON ea.Gal_id = g.Gal_id;
----
