CREATE SCHEMA IF NOT EXISTS app;

-- ============================================================
-- Enum types
-- ============================================================

CREATE TYPE app.user_status AS ENUM ('active', 'inactive', 'archived');
CREATE TYPE app.stay_request_type AS ENUM ('guest', 'volunteer', 'staff', 'facilitator', 'day_visit');
CREATE TYPE app.stay_request_status AS ENUM ('submitted', 'under_review', 'approved', 'rejected', 'cancelled');
CREATE TYPE app.stay_type AS ENUM ('guest', 'volunteer', 'staff', 'facilitator', 'day_visitor');
CREATE TYPE app.stay_status AS ENUM ('pending', 'confirmed', 'checked_in', 'checked_out', 'cancelled');
CREATE TYPE app.room_type AS ENUM ('private', 'shared', 'dorm', 'facilitator', 'staff');
CREATE TYPE app.room_block_type AS ENUM ('maintenance', 'retreat_reserved', 'admin_hold', 'other');
CREATE TYPE app.room_request_status AS ENUM ('submitted', 'reviewed', 'satisfied', 'unsatisfied', 'cancelled');
CREATE TYPE app.room_allocation_status AS ENUM ('planned', 'active', 'completed', 'cancelled');
CREATE TYPE app.boat_trip_status AS ENUM ('planned', 'open', 'full', 'closed', 'cancelled', 'completed');
CREATE TYPE app.boat_reservation_status AS ENUM ('submitted', 'under_review', 'confirmed', 'waitlisted', 'declined', 'cancelled');
CREATE TYPE app.boat_manifest_status AS ENUM ('confirmed', 'boarded', 'no_show', 'cancelled');
CREATE TYPE app.task_category AS ENUM ('operations', 'maintenance', 'kitchen', 'retreat', 'transport', 'housekeeping');
CREATE TYPE app.task_status AS ENUM ('planned', 'open', 'assigned', 'in_progress', 'blocked', 'completed', 'cancelled');
CREATE TYPE app.task_assignment_status AS ENUM ('proposed', 'accepted', 'declined', 'reassigned', 'completed');

-- ============================================================
-- Users
-- ============================================================

CREATE TABLE app."user" (
  user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL UNIQUE,
  phone TEXT,
  first_name TEXT,
  last_name TEXT,
  display_name TEXT,
  roles TEXT[] NOT NULL DEFAULT '{}',
  status app.user_status NOT NULL DEFAULT 'active',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE app.user_profile (
  profile_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL UNIQUE REFERENCES app."user"(user_id) ON DELETE CASCADE,
  date_of_birth DATE,
  nationality TEXT,
  notes TEXT,
  emergency_contact_name TEXT,
  emergency_contact_phone TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);