-- HU-ROOMS: Crear tablas base (sin offices)

-- TABLA: locations (jerarquía de ubicaciones)
CREATE TABLE IF NOT EXISTS public.locations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  parent_id UUID REFERENCES public.locations(id) ON DELETE CASCADE,
  level_name TEXT NOT NULL DEFAULT '',
  value TEXT NOT NULL,
  path TEXT[] NOT NULL DEFAULT '{}',
  depth INTEGER NOT NULL DEFAULT 0,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- TABLA: resource_types (catálogo de tipos de recurso)
CREATE TABLE IF NOT EXISTS public.resource_types (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  icon TEXT,
  description TEXT,
  is_active BOOLEAN DEFAULT true,
  booking_unit TEXT DEFAULT 'per_slot',
  slot_size INTEGER DEFAULT 60,
  time_granularity INTEGER DEFAULT 30,
  reminders_enabled BOOLEAN DEFAULT false,
  booking_advance_minutes INTEGER,
  cancellation_deadline_minutes INTEGER,
  editing_deadline_minutes INTEGER,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- TABLA: resources (entidad genérica reservable)
CREATE TABLE IF NOT EXISTS public.resources (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  resource_type_id UUID NOT NULL REFERENCES public.resource_types(id) ON DELETE RESTRICT,
  location_id UUID REFERENCES public.locations(id) ON DELETE SET NULL,
  name TEXT NOT NULL,
  description TEXT,
  capacity INTEGER DEFAULT 1,
  floor TEXT,
  amenities JSONB DEFAULT '[]',
  metadata JSONB DEFAULT '{}',
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- TABLA: users (perfil mínimo de usuario)
CREATE TABLE IF NOT EXISTS public.users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  auth_id UUID UNIQUE,
  email TEXT UNIQUE NOT NULL,
  display_name TEXT,
  avatar_url TEXT,
  role TEXT DEFAULT 'user',
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- TABLA: reservations (reservas)
CREATE TABLE IF NOT EXISTS public.reservations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  resource_id UUID NOT NULL REFERENCES public.resources(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
  title TEXT,
  start_at TIMESTAMPTZ NOT NULL,
  end_at TIMESTAMPTZ NOT NULL,
  status TEXT DEFAULT 'confirmed',
  checked_in_at TIMESTAMPTZ,
  cancelled_at TIMESTAMPTZ,
  cancellation_reason TEXT,
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  CONSTRAINT valid_time_range CHECK (end_at > start_at)
);

-- TABLA: resource_policies (reglas por tipo)
CREATE TABLE IF NOT EXISTS public.resource_policies (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  resource_type_id UUID REFERENCES public.resource_types(id) ON DELETE CASCADE,
  min_duration_minutes INTEGER DEFAULT 30,
  max_duration_minutes INTEGER DEFAULT 480,
  advance_booking_days INTEGER DEFAULT 30,
  cancellation_window_minutes INTEGER DEFAULT 60,
  no_show_threshold_minutes INTEGER DEFAULT 10,
  requires_approval BOOLEAN DEFAULT false,
  max_reservations_per_day INTEGER,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- TABLA: no_show_events (registro para analítica)
CREATE TABLE IF NOT EXISTS public.no_show_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  reservation_id UUID NOT NULL REFERENCES public.reservations(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
  detected_at TIMESTAMPTZ DEFAULT now(),
  notes TEXT
);
