-- Balon uçuşu trip-level kısıtlaması
-- Bir trip içinde sadece 1 balon uçuşu olabilir

-- Trigger function to enforce one balloon per trip
CREATE OR REPLACE FUNCTION check_one_balloon_per_trip()
RETURNS TRIGGER AS $$
DECLARE
  v_trip_id UUID;
  v_place_type TEXT;
  v_existing_count INTEGER;
BEGIN
  -- Get the trip_id from trip_day_id
  SELECT trip_id INTO v_trip_id
  FROM trip_days
  WHERE id = NEW.trip_day_id;

  -- Get the place type
  SELECT type INTO v_place_type
  FROM places
  WHERE id = NEW.place_id;

  -- If this is a balloon place, check if trip already has one
  IF v_place_type = 'hot-air-balloon' THEN
    SELECT COUNT(*) INTO v_existing_count
    FROM trip_places tp
    JOIN trip_days td ON tp.trip_day_id = td.id
    JOIN places p ON tp.place_id = p.id
    WHERE td.trip_id = v_trip_id
      AND p.type = 'hot-air-balloon'
      AND tp.id != COALESCE(NEW.id, '00000000-0000-0000-0000-000000000000'::UUID);

    IF v_existing_count > 0 THEN
      RAISE EXCEPTION 'Bir seyahatte yalnızca 1 balon uçuşu eklenebilir';
    END IF;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger for INSERT and UPDATE
DROP TRIGGER IF EXISTS enforce_one_balloon_per_trip ON trip_places;
CREATE TRIGGER enforce_one_balloon_per_trip
  BEFORE INSERT OR UPDATE ON trip_places
  FOR EACH ROW
  EXECUTE FUNCTION check_one_balloon_per_trip();

COMMENT ON FUNCTION check_one_balloon_per_trip IS 'Ensures only one hot-air-balloon activity per trip';
