-- Boat routes
CREATE TABLE app.boat_route (
  route_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  origin TEXT NOT NULL,
  destination TEXT NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Boats
CREATE TABLE app.boat (
  boat_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  passenger_capacity INT NOT NULL,
  cargo_capacity_kg NUMERIC,
  is_active BOOLEAN NOT NULL DEFAULT true,
  notes TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Boat trips
CREATE TABLE app.boat_trip (
  trip_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  boat_id UUID REFERENCES app.boat(boat_id),
  route_id UUID NOT NULL REFERENCES app.boat_route(route_id),
  scheduled_departure_at TIMESTAMPTZ NOT NULL,
  scheduled_arrival_at TIMESTAMPTZ,
  status app.boat_trip_status NOT NULL DEFAULT 'planned',
  notes TEXT,
  created_by_user_id UUID NOT NULL REFERENCES app."user"(user_id),
  updated_by_user_id UUID REFERENCES app."user"(user_id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_boat_trip_status ON app.boat_trip(status);
CREATE INDEX idx_boat_trip_departure ON app.boat_trip(scheduled_departure_at);

-- Boat reservation requests (user intent)
CREATE TABLE app.boat_reservation_request (
  request_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  trip_id UUID NOT NULL REFERENCES app.boat_trip(trip_id),
  user_id UUID NOT NULL REFERENCES app."user"(user_id),
  stay_id UUID REFERENCES app.stay(stay_id),
  retreat_id UUID,
  requested_seats INT NOT NULL DEFAULT 1,
  cargo_notes TEXT,
  special_requirements TEXT,
  priority_reason TEXT,
  status app.boat_reservation_status NOT NULL DEFAULT 'submitted',
  reviewed_by_user_id UUID REFERENCES app."user"(user_id),
  reviewed_at TIMESTAMPTZ,
  review_notes TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_boat_reservation_trip ON app.boat_reservation_request(trip_id);
CREATE INDEX idx_boat_reservation_user ON app.boat_reservation_request(user_id);
CREATE INDEX idx_boat_reservation_status ON app.boat_reservation_request(status);

-- Boat manifest entries (confirmed passengers)
CREATE TABLE app.boat_manifest_entry (
  entry_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  trip_id UUID NOT NULL REFERENCES app.boat_trip(trip_id),
  reservation_request_id UUID REFERENCES app.boat_reservation_request(request_id),
  user_id UUID NOT NULL REFERENCES app."user"(user_id),
  stay_id UUID REFERENCES app.stay(stay_id),
  status app.boat_manifest_status NOT NULL DEFAULT 'confirmed',
  confirmed_by_user_id UUID NOT NULL REFERENCES app."user"(user_id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_boat_manifest_trip ON app.boat_manifest_entry(trip_id);

SELECT app.enable_audit('boat_route', 'route_id');
SELECT app.enable_audit('boat', 'boat_id');
SELECT app.enable_audit('boat_trip', 'trip_id');
SELECT app.enable_audit('boat_reservation_request', 'request_id');
SELECT app.enable_audit('boat_manifest_entry', 'entry_id');