-- Drop trigger first if it exists
DROP TRIGGER IF EXISTS trg_set_shipment_delivered_at ON shipments;

CREATE OR REPLACE FUNCTION set_shipment_delivered_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- Only when status changes FROM something else TO 6
    IF OLD.shipment_status_id IS DISTINCT FROM 6
       AND NEW.shipment_status_id = 6 THEN
       
        NEW.shipment_delivered_at := NOW();
    END IF;

    RETURN NEW;
END;
$$;


CREATE TRIGGER trg_set_shipment_delivered_at
BEFORE UPDATE OF shipment_status_id
ON shipments
FOR EACH ROW
EXECUTE FUNCTION set_shipment_delivered_at();
