--Task 2:
SET SERVEROUTPUT ON;
ALTER session SET nls_date_format = 'dd/mm/yy';
CREATE OR REPLACE TRIGGER docking_trigger
BEFORE INSERT OR UPDATE ON DOCKING
FOR EACH ROW
ENABLE
DECLARE
    s_cargo    SHIPS.cargo_weight%TYPE;
    p_capacity PIERS.capacity%TYPE;
BEGIN
    -- Verifying that [ arrival date <= departure date ]:
    IF (:NEW.arrival_date > :NEW.departure_date) THEN
        DBMS_OUTPUT.put_line('WRONG! :(');
        RAISE_APPLICATION_ERROR(-20500,'Bad Dates: arrival_date Date > departure_date Date.');
    END IF;
    -- Verifying that [ cargo wheight <= capacity ]:
    SELECT SHIPS.cargo_weight INTO s_cargo
    FROM SHIPS
    WHERE SHIPS.sid = :NEW.sid;
    SELECT PIERS.capacity INTO p_capacity
    FROM PIERS
    WHERE PIERS.pid = :NEW.pid;
    IF s_cargo > p_capacity THEN
        DBMS_OUTPUT.put_line('WRONG! :(');
        RAISE_APPLICATION_ERROR(-20500,'Cargo Error: There is not enough capacity for all the cargo.');
    ELSE
        DBMS_OUTPUT.put_line('RIGHT! :)');
        UPDATE PIERS
        SET totalShips = totalShips + 1
        WHERE pid = :NEW.pid;
    END IF;
END;
/
----------------------------------------------------------------------------------------------------------------------------------------------------------------

--Task 4:
-- 4a:
DECLARE
    max_row  DOCKING%ROWTYPE;
    temp DOCKING%ROWTYPE;
CURSOR docking_cursor IS
    SELECT * FROM DOCKING
    WHERE pid = TO_NUMBER('&pierID');
BEGIN
    OPEN docking_cursor;
    FETCH docking_cursor INTO temp;
    max_row := temp;
    DBMS_OUTPUT.put_line('This report for pier ' || temp.pid || ':');
    LOOP
        EXIT WHEN docking_cursor%NOTFOUND;
        IF (temp.departure_date - temp.arrival_date > max_row.departure_date - max_row.arrival_date ) THEN
            max_row := temp;
        END IF;
        FETCH docking_cursor INTO temp;
    END LOOP;
    CLOSE docking_cursor;
    OPEN docking_cursor;
    FETCH docking_cursor INTO temp;
    LOOP
        EXIT WHEN docking_cursor%NOTFOUND;
        IF ( temp.departure_date - temp.arrival_date = max_row.departure_date - max_row.arrival_date ) THEN
            DBMS_OUTPUT.put_line( temp.sid || ' | ' || temp.arrival_date || ' | ' || temp.departure_date || ' | ' || ( temp.departure_date - temp.arrival_date) || ' days.');
        END IF;
        FETCH docking_cursor INTO temp;
    END LOOP;
    CLOSE docking_cursor;
    EXCEPTION
        WHEN OTHERS THEN DBMS_OUTPUT.put_line('Invalid input: Please insert an integer.');
END;
/
-- 4b:
DECLARE
    max_row DOCKING%ROWTYPE;
    counter NUMBER(3) := 0;
CURSOR docking_cursor IS
    SELECT *
    FROM DOCKING
    WHERE pid = TO_NUMBER('&pierID');
BEGIN
    FOR d IN docking_cursor
    LOOP
        IF (counter = 0 ) THEN
            max_row := d;
            counter := 1;
            DBMS_OUTPUT.put_line('This report for pier ' || max_row.pid || ':');
        ELSIF ( d.departure_date - d.arrival_date > max_row.departure_date - max_row.arrival_date ) THEN
            max_row := d;
        END IF;
    END LOOP;
    FOR d IN docking_cursor
    LOOP
        IF( d.departure_date - d.arrival_date = max_row.departure_date - max_row.arrival_date ) THEN
            DBMS_OUTPUT.put_line(d.sid || ' | ' || d.arrival_date || ' | ' || d.departure_date || ' | ' || (d.departure_date - d.arrival_date) || ' days');
        END IF;
    END LOOP;
    EXCEPTION
        WHEN OTHERS THEN DBMS_OUTPUT.put_line('Invalid input: please insert an integer');
END;
/
----------------------------------------------------------------------------------------------------------------------------------------------------------------
--Task 5:
CREATE OR REPLACE FUNCTION countShips(arrival DOCKING.arrival_date%TYPE, departure DOCKING.departure_date%TYPE)
RETURN NUMBER IS
    counter NUMBER := 0;
    NULL_SHIPS EXCEPTION;
CURSOR docking_cursor IS
    SELECT *
    FROM DOCKING;
BEGIN
    SELECT COUNT(*) INTO counter
    FROM DOCKING
    WHERE ( arrival_date <= arrival AND departure_date >= departure );
    IF(counter = 0) THEN
        RAISE NULL_SHIPS;
    ELSE
        printShips(arrival,departure);
        RETURN counter;
    END IF;
    EXCEPTION
        WHEN NULL_SHIPS THEN
            RETURN -2;
END;
/

CREATE OR REPLACE PROCEDURE printShips(arrival DOCKING.arrival_date%TYPE, departure DOCKING.departure_date%TYPE) IS
    p_name PIERS.name%TYPE;
    s_name SHIPS.name%TYPE;
    CURSOR docking_cursor IS
    SELECT *
    FROM DOCKING;
BEGIN
    FOR d IN docking_cursor
    LOOP
        IF(d.arrival_date <= arrival AND d.departure_date >= departure) THEN
            SELECT SHIPS.name INTO s_name
            FROM SHIPS
            WHERE SHIPS.sid = d.sid;
            SELECT PIERS.name INTO p_name
            FROM PIERS
            WHERE PIERS.pid = d.pid;
            DBMS_OUTPUT.put_line(p_name || ' | ' || s_name || ' | ' || d.arrival_date || ' | ' || d.departure_date);
        END IF;
    END LOOP;
END;
/
DECLARE
    counter NUMBER := 0;
    arrival DOCKING.arrival_date%TYPE;
    departure DOCKING.departure_date%TYPE;
BEGIN
    arrival := '&Arrival';
    departure := '&Departure';
    DBMS_OUTPUT.put_line('From: ' || arrival);
    DBMS_OUTPUT.put_line('To: ' || departure);
    counter := countShips(arrival, departure);
    IF(counter = -2 ) THEN
        DBMS_OUTPUT.put_line('There are no ships in period: ' || arrival || '-' || departure);
    ELSE
        DBMS_OUTPUT.put_line('From ' || arrival || ' there were ' || counter || ' ships.');
    END IF;
END;
/
----------------------------------------------------------------------------------------------------------------------------------------------------------------
--Task 6:
CREATE OR REPLACE PROCEDURE printDocks(c SHIPS.country%TYPE) IS
    counter NUMBER := 0;
    temp    NUMBER := 0;
    avrg    NUMBER := 0;
    NULL_SHIPS EXCEPTION;
CURSOR docking_cursor IS
    SELECT *
    FROM DOCKING
    WHERE sid IN (
        SELECT sid
        FROM SHIPS
        WHERE country = c
    );
BEGIN
    FOR dc IN docking_cursor
    LOOP
        SELECT cargo_weight
        INTO temp
        FROM SHIPS
        WHERE sid = dc.sid;
        avrg := avrg + temp;
        DBMS_OUTPUT.put_line(dc.sid || ' | ' || dc.pid || ' | ' || dc.arrival_date || ' | ' || dc.departure_date);
        counter := counter + 1;
    END LOOP;
    IF(counter = 0) THEN
        RAISE NULL_SHIPS;
    ELSE
        DBMS_OUTPUT.put_line('Total ships: ' || counter);
        DBMS_OUTPUT.put_line('Avarage weight: ' || avrg/counter);
    END IF;
    EXCEPTION
        WHEN NULL_SHIPS THEN
            DBMS_OUTPUT.put_line('There is no information about ships from ' || c || '.');
END;
/

BEGIN
    printDocks('&Country');
END;
/