-- ============================================================
-- Retreat enum types
-- ============================================================

CREATE TYPE app.retreat_status AS ENUM ('draft', 'published', 'active', 'completed', 'cancelled');
CREATE TYPE app.retreat_person_role AS ENUM ('participant', 'facilitator', 'assistant_facilitator', 'organizer', 'support_staff');
CREATE TYPE app.retreat_attendance_status AS ENUM ('invited', 'registered', 'confirmed', 'checked_in', 'cancelled');

-- ============================================================
-- Retreats
-- ============================================================

CREATE TABLE app.retreat (
  retreat_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  description TEXT,
  start_at DATE NOT NULL,
  end_at DATE NOT NULL,
  status app.retreat_status NOT NULL DEFAULT 'draft',
  capacity INT,
  default_check_in_at TIMESTAMPTZ,
  default_check_out_at TIMESTAMPTZ,
  created_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_retreat_status ON app.retreat(status);
CREATE INDEX idx_retreat_dates ON app.retreat(start_at, end_at);

-- ============================================================
-- Retreat persons
-- ============================================================

CREATE TABLE app.retreat_person (
  retreat_person_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  retreat_id UUID NOT NULL REFERENCES app.retreat(retreat_id) ON DELETE CASCADE,
  user_id UUID REFERENCES app."user"(user_id),
  stay_id UUID REFERENCES app.stay(stay_id),
  full_name TEXT,
  email TEXT,
  phone TEXT,
  role_type app.retreat_person_role NOT NULL DEFAULT 'participant',
  attendance_status app.retreat_attendance_status NOT NULL DEFAULT 'registered',
  arrival_override_at TIMESTAMPTZ,
  departure_override_at TIMESTAMPTZ,
  notes TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(retreat_id, user_id)
);

CREATE INDEX idx_retreat_person_retreat ON app.retreat_person(retreat_id);
CREATE INDEX idx_retreat_person_user ON app.retreat_person(user_id);

-- ============================================================
-- Retreat schedule items
-- ============================================================

CREATE TABLE app.retreat_schedule_item (
  item_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  retreat_id UUID NOT NULL REFERENCES app.retreat(retreat_id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  description TEXT,
  starts_at TIMESTAMPTZ NOT NULL,
  ends_at TIMESTAMPTZ NOT NULL,
  location TEXT,
  lead_retreat_person_id UUID REFERENCES app.retreat_person(retreat_person_id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_retreat_schedule_retreat ON app.retreat_schedule_item(retreat_id);
CREATE INDEX idx_retreat_schedule_times ON app.retreat_schedule_item(starts_at, ends_at);

-- Enable audit
SELECT app.enable_audit('retreat', 'retreat_id');
SELECT app.enable_audit('retreat_person', 'retreat_person_id');
SELECT app.enable_audit('retreat_schedule_item', 'item_id');