DatabaseProgramming: proc&triggers.sql

-- Го откажува летот и ги ажурира сите поврзани билети, boarding passes, arrivals и departures на статус „Cancelled", доколку летот не е веќе во тек или завршен.

CREATE OR REPLACE PROCEDURE CancelFlight(
    p_FlightId INT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_flight_exists     BOOLEAN;
    v_current_status    VARCHAR(20);
    v_ticket_count      INT;
    v_boarding_count    INT;
    v_arrivals_count    INT;
    v_departures_count  INT;
BEGIN
    SELECT EXISTS (
        SELECT 1 FROM Flights WHERE FlightId = p_FlightId
    ) INTO v_flight_exists;

    IF NOT v_flight_exists THEN
        RAISE EXCEPTION 'Flight with ID % does not exist.', p_FlightId;
    END IF;

    SELECT OperatingStatus
    INTO v_current_status
    FROM Flights
    WHERE FlightId = p_FlightId;

    IF v_current_status = 'Cancelled' THEN
        RAISE EXCEPTION 'Flight with ID % is already cancelled.', p_FlightId;
    END IF;

    IF v_current_status IN ('Departed', 'En Route', 'Landed', 'Arrived') THEN
        RAISE EXCEPTION 'Flight with ID % cannot be cancelled because it is in status: %.',
            p_FlightId, v_current_status;
    END IF;

    UPDATE Flights
    SET OperatingStatus = 'Cancelled'
    WHERE FlightId = p_FlightId;

    GET DIAGNOSTICS v_ticket_count = ROW_COUNT;
    RAISE NOTICE 'Flights updated: % row(s)', v_ticket_count;

    UPDATE Ticket
    SET Status = 'Cancelled'
    WHERE FlightId = p_FlightId
      AND Status NOT IN ('Cancelled', 'Refunded', 'Used');

    GET DIAGNOSTICS v_ticket_count = ROW_COUNT;
    RAISE NOTICE 'Tickets cancelled: % row(s)', v_ticket_count;

    UPDATE BoardingPass
    SET Status = 'Cancelled'
    WHERE TicketId IN (
        SELECT TicketId FROM Ticket WHERE FlightId = p_FlightId
    )
    AND Status NOT IN ('Cancelled', 'Boarded');

    GET DIAGNOSTICS v_boarding_count = ROW_COUNT;
    RAISE NOTICE 'Boarding passes cancelled: % row(s)', v_boarding_count;

    UPDATE Arrivals
    SET Status = 'Cancelled'
    WHERE FlightId = p_FlightId
      AND Status NOT IN ('Cancelled', 'Arrived', 'Landed');

    GET DIAGNOSTICS v_arrivals_count = ROW_COUNT;
    RAISE NOTICE 'Arrivals cancelled: % row(s)', v_arrivals_count;

    UPDATE Departures
    SET Status = 'Cancelled'
    WHERE FlightId = p_FlightId
      AND Status NOT IN ('Cancelled', 'Departed');

    GET DIAGNOSTICS v_departures_count = ROW_COUNT;
    RAISE NOTICE 'Departures cancelled: % row(s)', v_departures_count;

    RAISE NOTICE 'Flight with ID % has been successfully cancelled.', p_FlightId;

EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Error while cancelling flight %: %', p_FlightId, SQLERRM;
END;
$$;


-- Го одложува летот за дадени минути со поместување на DepartureTime и ArrivalTime, и го ажурира статусот во „Delayed" во Flights, Arrivals и Departures.

CREATE OR REPLACE PROCEDURE DelayFlight(
    p_FlightId      INT,
    p_DelayMinutes  INT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_flight_exists         BOOLEAN;
    v_current_status        VARCHAR(20);
    v_old_departure         TIMESTAMP;
    v_old_arrival           TIMESTAMP;
    v_new_departure         TIMESTAMP;
    v_new_arrival           TIMESTAMP;
    v_updated_flights       INT;
    v_updated_arrivals      INT;
    v_updated_departures    INT;
BEGIN
    SELECT EXISTS (
        SELECT 1 FROM Flights WHERE FlightId = p_FlightId
    ) INTO v_flight_exists;

    IF NOT v_flight_exists THEN
        RAISE EXCEPTION 'Flight with ID % does not exist.', p_FlightId;
    END IF;

    IF p_DelayMinutes <= 0 THEN
        RAISE EXCEPTION 'Delay time must be greater than 0 minutes. Provided: %',
            p_DelayMinutes;
    END IF;

    SELECT OperatingStatus, DepartureTime, ArrivalTime
    INTO v_current_status, v_old_departure, v_old_arrival
    FROM Flights
    WHERE FlightId = p_FlightId;

    IF v_current_status IN ('Cancelled', 'Landed', 'Arrived') THEN
        RAISE EXCEPTION 'Flight with ID % cannot be delayed because it is in status: %.',
            p_FlightId, v_current_status;
    END IF;

    IF v_current_status IN ('Departed', 'En Route') THEN
        RAISE EXCEPTION 'Flight with ID % is already in the air and cannot be delayed (status: %).',
            p_FlightId, v_current_status;
    END IF;

    v_new_departure := v_old_departure + (p_DelayMinutes || ' minutes')::INTERVAL;
    v_new_arrival   := v_old_arrival   + (p_DelayMinutes || ' minutes')::INTERVAL;

    RAISE NOTICE 'Old DepartureTime: % -> New: %', v_old_departure, v_new_departure;
    RAISE NOTICE 'Old ArrivalTime:   % -> New: %', v_old_arrival,   v_new_arrival;

    UPDATE Flights
    SET OperatingStatus = 'Delayed',
        DepartureTime   = v_new_departure,
        ArrivalTime     = v_new_arrival
    WHERE FlightId = p_FlightId;

    GET DIAGNOSTICS v_updated_flights = ROW_COUNT;
    RAISE NOTICE 'Flights updated: % row(s)', v_updated_flights;

    UPDATE Arrivals
    SET Status      = 'Delayed',
        ArrivalTime = v_new_arrival
    WHERE FlightId = p_FlightId
      AND Status NOT IN ('Cancelled', 'Landed', 'Arrived');

    GET DIAGNOSTICS v_updated_arrivals = ROW_COUNT;
    RAISE NOTICE 'Arrivals updated: % row(s)', v_updated_arrivals;

    UPDATE Departures
    SET Status        = 'Delayed',
        DepartureTime = v_new_departure
    WHERE FlightId = p_FlightId
      AND Status NOT IN ('Cancelled', 'Departed');

    GET DIAGNOSTICS v_updated_departures = ROW_COUNT;
    RAISE NOTICE 'Departures updated: % row(s)', v_updated_departures;

    RAISE NOTICE 'Flight with ID % has been successfully delayed by % minutes.', p_FlightId, p_DelayMinutes;

EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Error while delaying flight %: %', p_FlightId, SQLERRM;
END;
$$;


--Доделува отворен гејт на лет (за arrival или departure) и го менува статусот на гејтот во „Occupied".

CREATE OR REPLACE PROCEDURE AssignGateToFlight(
    p_FlightId   INT,
    p_GateId     INT,
    p_Direction  VARCHAR(20)
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_flight_exists     BOOLEAN;
    v_gate_exists       BOOLEAN;
    v_gate_status       VARCHAR(20);
    v_flight_status     VARCHAR(20);
    v_direction_upper   VARCHAR(20);
BEGIN
    v_direction_upper := UPPER(TRIM(p_Direction));

    IF v_direction_upper NOT IN ('ARRIVAL', 'DEPARTURE') THEN
        RAISE EXCEPTION 'Invalid direction: %. Use ARRIVAL or DEPARTURE.', p_Direction;
    END IF;

    SELECT EXISTS (
        SELECT 1 FROM Flights WHERE FlightId = p_FlightId
    ) INTO v_flight_exists;

    IF NOT v_flight_exists THEN
        RAISE EXCEPTION 'Flight with ID % does not exist.', p_FlightId;
    END IF;

    SELECT OperatingStatus INTO v_flight_status
    FROM Flights WHERE FlightId = p_FlightId;

    IF v_flight_status IN ('Cancelled', 'Arrived', 'Landed') THEN
        RAISE EXCEPTION 'Cannot assign a gate to a flight with status: %.', v_flight_status;
    END IF;

    SELECT EXISTS (
        SELECT 1 FROM Gate WHERE GateId = p_GateId
    ) INTO v_gate_exists;

    IF NOT v_gate_exists THEN
        RAISE EXCEPTION 'Gate with ID % does not exist.', p_GateId;
    END IF;

    SELECT Status INTO v_gate_status
    FROM Gate WHERE GateId = p_GateId;

    IF v_gate_status NOT IN ('Open') THEN
        RAISE EXCEPTION 'Gate with ID % is not available. Current status: %.',
            p_GateId, v_gate_status;
    END IF;

    IF v_direction_upper = 'ARRIVAL' THEN

        IF NOT EXISTS (SELECT 1 FROM Arrivals WHERE FlightId = p_FlightId) THEN
            RAISE EXCEPTION 'No Arrivals record found for flight with ID %.', p_FlightId;
        END IF;

        UPDATE Arrivals
        SET GateId = p_GateId
        WHERE FlightId = p_FlightId;

        RAISE NOTICE 'Gate % assigned to Arrival for flight %.', p_GateId, p_FlightId;

    ELSE

        IF NOT EXISTS (SELECT 1 FROM Departures WHERE FlightId = p_FlightId) THEN
            RAISE EXCEPTION 'No Departures record found for flight with ID %.', p_FlightId;
        END IF;

        UPDATE Departures
        SET GateId = p_GateId
        WHERE FlightId = p_FlightId;

        RAISE NOTICE 'Gate % assigned to Departure for flight %.', p_GateId, p_FlightId;

    END IF;

    UPDATE Gate
    SET Status = 'Occupied'
    WHERE GateId = p_GateId;

    RAISE NOTICE 'Gate % status changed to Occupied.', p_GateId;

EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Error while assigning gate: %', SQLERRM;
END;
$$;


--Го менува статусот на гејт со валидација на дозволени транзиции (на пример, „Under Maintenance" може да оди само во „Open" или „Closed").

CREATE OR REPLACE PROCEDURE ChangeGateStatus(
    p_GateId    INT,
    p_NewStatus VARCHAR(20)
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_gate_exists   BOOLEAN;
    v_old_status    VARCHAR(20);
BEGIN
    SELECT EXISTS (
        SELECT 1 FROM Gate WHERE GateId = p_GateId
    ) INTO v_gate_exists;

    IF NOT v_gate_exists THEN
        RAISE EXCEPTION 'Gate with ID % does not exist.', p_GateId;
    END IF;

    IF p_NewStatus NOT IN ('Open', 'Closed', 'Boarding', 'Occupied', 'Under Maintenance') THEN
        RAISE EXCEPTION 'Invalid status: %. Allowed values: Open, Closed, Boarding, Occupied, Under Maintenance.',
            p_NewStatus;
    END IF;

    SELECT Status INTO v_old_status
    FROM Gate WHERE GateId = p_GateId;

    IF v_old_status = p_NewStatus THEN
        RAISE EXCEPTION 'Gate with ID % is already in status: %.', p_GateId, p_NewStatus;
    END IF;

    IF v_old_status = 'Under Maintenance' AND p_NewStatus NOT IN ('Open', 'Closed') THEN
        RAISE EXCEPTION 'A gate under maintenance can only transition to Open or Closed.';
    END IF;

    IF v_old_status = 'Boarding' AND p_NewStatus NOT IN ('Open', 'Closed', 'Occupied') THEN
        RAISE EXCEPTION 'A gate in Boarding can only transition to Open, Closed or Occupied.';
    END IF;

    UPDATE Gate
    SET Status = p_NewStatus
    WHERE GateId = p_GateId;

    RAISE NOTICE 'Gate % status changed: % -> %.', p_GateId, v_old_status, p_NewStatus;

EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Error while changing gate status: %', SQLERRM;
END;
$$;


--Доделува слободна писта на лет (arrival или departure), проверувајќи дека пистата припаѓа на точниот аеродром, и го менува статусот на пистата во „Occupied".

CREATE OR REPLACE PROCEDURE AssignRunwayToFlight(
    p_FlightId  INT,
    p_RunwayId  INT,
    p_Direction VARCHAR(20)
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_flight_exists     BOOLEAN;
    v_runway_exists     BOOLEAN;
    v_runway_status     VARCHAR(20);
    v_flight_status     VARCHAR(20);
    v_direction_upper   VARCHAR(20);
    v_runway_airport    INT;
    v_origin_airport    INT;
    v_dest_airport      INT;
BEGIN
    v_direction_upper := UPPER(TRIM(p_Direction));

    IF v_direction_upper NOT IN ('ARRIVAL', 'DEPARTURE') THEN
        RAISE EXCEPTION 'Invalid direction: %. Use ARRIVAL or DEPARTURE.', p_Direction;
    END IF;

    SELECT EXISTS (
        SELECT 1 FROM Flights WHERE FlightId = p_FlightId
    ) INTO v_flight_exists;

    IF NOT v_flight_exists THEN
        RAISE EXCEPTION 'Flight with ID % does not exist.', p_FlightId;
    END IF;

    SELECT OperatingStatus, OriginAirportId, DestinationAirportId
    INTO v_flight_status, v_origin_airport, v_dest_airport
    FROM Flights WHERE FlightId = p_FlightId;

    IF v_flight_status IN ('Cancelled', 'Arrived') THEN
        RAISE EXCEPTION 'Cannot assign a runway to a flight with status: %.', v_flight_status;
    END IF;

    SELECT EXISTS (
        SELECT 1 FROM Runway WHERE RunwayId = p_RunwayId
    ) INTO v_runway_exists;

    IF NOT v_runway_exists THEN
        RAISE EXCEPTION 'Runway with ID % does not exist.', p_RunwayId;
    END IF;

    SELECT Status, AirportId INTO v_runway_status, v_runway_airport
    FROM Runway WHERE RunwayId = p_RunwayId;

    IF v_runway_status NOT IN ('Open') THEN
        RAISE EXCEPTION 'Runway with ID % is not available. Current status: %.',
            p_RunwayId, v_runway_status;
    END IF;

    IF v_direction_upper = 'ARRIVAL' AND v_runway_airport != v_dest_airport THEN
        RAISE EXCEPTION 'Runway % does not belong to the destination airport (ID: %).',
            p_RunwayId, v_dest_airport;
    END IF;

    IF v_direction_upper = 'DEPARTURE' AND v_runway_airport != v_origin_airport THEN
        RAISE EXCEPTION 'Runway % does not belong to the origin airport (ID: %).',
            p_RunwayId, v_origin_airport;
    END IF;

    IF v_direction_upper = 'ARRIVAL' THEN

        IF NOT EXISTS (SELECT 1 FROM Arrivals WHERE FlightId = p_FlightId) THEN
            RAISE EXCEPTION 'No Arrivals record found for flight with ID %.', p_FlightId;
        END IF;

        UPDATE Arrivals
        SET RunwayId = p_RunwayId
        WHERE FlightId = p_FlightId;

        RAISE NOTICE 'Runway % assigned to Arrival for flight %.', p_RunwayId, p_FlightId;

    ELSE

        IF NOT EXISTS (SELECT 1 FROM Departures WHERE FlightId = p_FlightId) THEN
            RAISE EXCEPTION 'No Departures record found for flight with ID %.', p_FlightId;
        END IF;

        UPDATE Departures
        SET RunwayId = p_RunwayId
        WHERE FlightId = p_FlightId;

        RAISE NOTICE 'Runway % assigned to Departure for flight %.', p_RunwayId, p_FlightId;

    END IF;

    UPDATE Runway
    SET Status = 'Occupied'
    WHERE RunwayId = p_RunwayId;

    RAISE NOTICE 'Runway % status changed to Occupied.', p_RunwayId;

EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Error while assigning runway: %', SQLERRM;
END;
$$;


--Го отвора или затвора терминалот заедно со сите негови гејтови, но блокира затворање ако има активни гејтови во статус „Boarding" или „Occupied"

CREATE OR REPLACE PROCEDURE OpenCloseTerminal(
    p_TerminalId INT,
    p_Action     VARCHAR(10)
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_terminal_exists   BOOLEAN;
    v_current_status    VARCHAR(20);
    v_action_upper      VARCHAR(10);
    v_active_flights    INT;
    v_new_status        VARCHAR(20);
BEGIN
    v_action_upper := UPPER(TRIM(p_Action));

    IF v_action_upper NOT IN ('OPEN', 'CLOSE') THEN
        RAISE EXCEPTION 'Invalid action: %. Use OPEN or CLOSE.', p_Action;
    END IF;

    SELECT EXISTS (
        SELECT 1 FROM Terminal WHERE TerminalId = p_TerminalId
    ) INTO v_terminal_exists;

    IF NOT v_terminal_exists THEN
        RAISE EXCEPTION 'Terminal with ID % does not exist.', p_TerminalId;
    END IF;

    SELECT TerminalStatus INTO v_current_status
    FROM Terminal WHERE TerminalId = p_TerminalId;

    IF v_current_status = 'Under Maintenance' THEN
        RAISE EXCEPTION 'Terminal with ID % is under maintenance and cannot be opened or closed directly.',
            p_TerminalId;
    END IF;

    IF v_action_upper = 'OPEN' THEN
        v_new_status := 'Open';
    ELSE
        v_new_status := 'Closed';
    END IF;

    IF v_current_status = v_new_status THEN
        RAISE EXCEPTION 'Terminal with ID % is already in status: %.', p_TerminalId, v_new_status;
    END IF;

    IF v_action_upper = 'CLOSE' THEN
        SELECT COUNT(*) INTO v_active_flights
        FROM Gate
        WHERE TerminalId = p_TerminalId
          AND Status IN ('Boarding', 'Occupied');

        IF v_active_flights > 0 THEN
            RAISE EXCEPTION 'Terminal cannot be closed. There are % active gate(s) in Boarding/Occupied status.',
                v_active_flights;
        END IF;

        UPDATE Gate
        SET Status = 'Closed'
        WHERE TerminalId = p_TerminalId
          AND Status = 'Open';

        RAISE NOTICE 'All open gates in terminal % have been closed.', p_TerminalId;
    END IF;

    IF v_action_upper = 'OPEN' THEN
        UPDATE Gate
        SET Status = 'Open'
        WHERE TerminalId = p_TerminalId
          AND Status = 'Closed';

        RAISE NOTICE 'All closed gates in terminal % have been opened.', p_TerminalId;
    END IF;

    UPDATE Terminal
    SET TerminalStatus = v_new_status
    WHERE TerminalId = p_TerminalId;

    RAISE NOTICE 'Terminal % successfully updated: % -> %.', p_TerminalId, v_current_status, v_new_status;

EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Error while changing terminal status: %', SQLERRM;
END;
$$;


--Закажува одржување на авион со валидација дека авионот нема активни летови, проверка за преклопување на термини, автоматско доделување на слободен хангар и креирање на запис во AircraftMaintenance со поставување на авионот во статус „Under Maintenance".

CREATE OR REPLACE PROCEDURE ScheduleAircraftMaintenance(
    p_AircraftId        INT,
    p_AirportId         INT,
    p_MaintenanceType   VARCHAR(20),
    p_StartDate         TIMESTAMP,
    p_EndDate           TIMESTAMP,
    p_WorkDescription   TEXT DEFAULT NULL,
    p_MaintenanceCost   INT DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_aircraft_exists       BOOLEAN;
    v_aircraft_status       VARCHAR(20);
    v_airport_exists        BOOLEAN;
    v_airport_status        VARCHAR(20);
    v_hangar_id             INT;
    v_overlap_count         INT;
    v_duration_minutes      INT;
    v_new_maintenance_id    INT;
BEGIN
    SELECT EXISTS (
        SELECT 1 FROM Aircraft WHERE AircraftId = p_AircraftId
    ) INTO v_aircraft_exists;

    IF NOT v_aircraft_exists THEN
        RAISE EXCEPTION 'Aircraft with ID % does not exist.', p_AircraftId;
    END IF;

    SELECT Status INTO v_aircraft_status
    FROM Aircraft WHERE AircraftId = p_AircraftId;

    IF v_aircraft_status IN ('Retired') THEN
        RAISE EXCEPTION 'Aircraft with ID % is retired and cannot be scheduled for maintenance.', p_AircraftId;
    END IF;

    IF v_aircraft_status IN ('Active') THEN
        IF EXISTS (
            SELECT 1 FROM Flights
            WHERE AircraftId = p_AircraftId
              AND OperatingStatus IN ('Boarding', 'Departed', 'En Route', 'Delayed')
        ) THEN
            RAISE EXCEPTION 'Aircraft with ID % has active flights and cannot be scheduled for maintenance.', p_AircraftId;
        END IF;
    END IF;

    IF p_StartDate IS NULL THEN
        RAISE EXCEPTION 'StartDate cannot be null.';
    END IF;

    IF p_EndDate IS NOT NULL AND p_EndDate <= p_StartDate THEN
        RAISE EXCEPTION 'EndDate must be after StartDate.';
    END IF;

    IF p_StartDate < NOW() THEN
        RAISE EXCEPTION 'StartDate cannot be in the past. Provided: %', p_StartDate;
    END IF;

    IF p_MaintenanceType NOT IN ('Routine', 'Repair', 'Inspection', 'Overhaul', 'Emergency') THEN
        RAISE EXCEPTION 'Invalid MaintenanceType: %. Allowed: Routine, Repair, Inspection, Overhaul, Emergency.',
            p_MaintenanceType;
    END IF;

    SELECT COUNT(*) INTO v_overlap_count
    FROM AircraftMaintenance
    WHERE AircraftId = p_AircraftId
      AND Status NOT IN ('Completed', 'Cancelled')
      AND (
          (p_StartDate BETWEEN StartDate AND COALESCE(EndDate, p_StartDate + INTERVAL '1 day'))
          OR
          (COALESCE(p_EndDate, p_StartDate + INTERVAL '1 day') BETWEEN StartDate AND COALESCE(EndDate, p_StartDate + INTERVAL '1 day'))
          OR
          (p_StartDate <= StartDate AND COALESCE(p_EndDate, p_StartDate + INTERVAL '1 day') >= COALESCE(EndDate, p_StartDate + INTERVAL '1 day'))
      );

    IF v_overlap_count > 0 THEN
        RAISE EXCEPTION 'Aircraft with ID % already has % overlapping maintenance record(s) in that period.',
            p_AircraftId, v_overlap_count;
    END IF;

    SELECT EXISTS (
        SELECT 1 FROM Airport WHERE AirportId = p_AirportId
    ) INTO v_airport_exists;

    IF NOT v_airport_exists THEN
        RAISE EXCEPTION 'Airport with ID % does not exist.', p_AirportId;
    END IF;

    SELECT OperationalStatus INTO v_airport_status
    FROM Airport WHERE AirportId = p_AirportId;

    IF v_airport_status NOT IN ('Open', 'Restricted') THEN
        RAISE EXCEPTION 'Airport with ID % is not available for maintenance. Status: %.',
            p_AirportId, v_airport_status;
    END IF;

    SELECT h.HangarId INTO v_hangar_id
    FROM Hangar h
    WHERE h.AirportId = p_AirportId
      AND h.Status = 'Open'
      AND (
          h.HangarCapacity IS NULL
          OR (
              SELECT COUNT(*) FROM StaysAt sa WHERE sa.HangarId = h.HangarId
          ) < h.HangarCapacity
      )
    ORDER BY h.HangarId
    LIMIT 1;

    IF v_hangar_id IS NULL THEN
        RAISE NOTICE 'No available hangar found at airport ID %. Maintenance will proceed without hangar assignment.',
            p_AirportId;
    ELSE
        RAISE NOTICE 'Hangar ID % assigned for aircraft ID %.', v_hangar_id, p_AircraftId;

        INSERT INTO StaysAt (HangarId, AircraftId)
        VALUES (v_hangar_id, p_AircraftId)
        ON CONFLICT DO NOTHING;
    END IF;

    IF p_EndDate IS NOT NULL THEN
        v_duration_minutes := EXTRACT(EPOCH FROM (p_EndDate - p_StartDate))::INT / 60;
    ELSE
        v_duration_minutes := NULL;
    END IF;

    INSERT INTO AircraftMaintenance (
        AircraftId,
        MaintenanceType,
        StartDate,
        EndDate,
        DurationMinutes,
        WorkDescription,
        Status,
        MaintenanceCost,
        AirportId
    )
    VALUES (
        p_AircraftId,
        p_MaintenanceType,
        p_StartDate,
        p_EndDate,
        v_duration_minutes,
        p_WorkDescription,
        'Scheduled',
        p_MaintenanceCost,
        p_AirportId
    )
    RETURNING MaintenanceId INTO v_new_maintenance_id;

    UPDATE Aircraft
    SET Status = 'Under Maintenance'
    WHERE AircraftId = p_AircraftId;

    RAISE NOTICE 'Maintenance ID % successfully scheduled for aircraft ID % at airport ID % from % to %.',
        v_new_maintenance_id, p_AircraftId, p_AirportId, p_StartDate, COALESCE(p_EndDate::TEXT, 'TBD');

EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Error while scheduling maintenance for aircraft %: %', p_AircraftId, SQLERRM;
END;
$$;


--Пред INSERT на WorksOn, повикува функција која проверува дали датумите на вработениот се валидни во однос на одржувањето.

CREATE TRIGGER trg_check_workson_dates
BEFORE INSERT ON WorksOn
FOR EACH ROW
EXECUTE FUNCTION check_employee_maintenance_dates();


--Исто како претходниот тригер, но се активира пред UPDATE на WorksOn.

CREATE TRIGGER trg_check_workson_dates_update
BEFORE UPDATE ON WorksOn
FOR EACH ROW
EXECUTE FUNCTION check_employee_maintenance_dates();