-- HU-ROOMS: Schema base para sistema de reservas de espacios
-- Extensión para soporte de rangos temporales con exclusión
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- ============================================
-- 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, -- 'meeting_room', 'parking_spot', 'desk', 'locker', 'equipment'
  name TEXT NOT NULL,
  icon TEXT, -- nombre del icono para UI
  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, -- detalle de ubicación (torre, piso, box, etc.)
  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, -- referencia opcional a auth.users si se usa Supabase Auth
  email TEXT UNIQUE NOT NULL,
  display_name TEXT,
  avatar_url TEXT,
  role TEXT DEFAULT 'user', -- 'user', 'admin'
  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, -- título opcional de la reserva
  start_at TIMESTAMPTZ NOT NULL,
  end_at TIMESTAMPTZ NOT NULL,
  status TEXT DEFAULT 'confirmed', -- 'confirmed', 'checked_in', 'completed', 'cancelled', 'no_show'
  checked_in_at TIMESTAMPTZ,
  cancelled_at TIMESTAMPTZ,
  cancellation_reason TEXT,
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),

  -- Validación: end_at debe ser posterior a start_at
  CONSTRAINT valid_time_range CHECK (end_at > start_at)
);

-- Nota: La validación de solapamiento se hará a nivel de aplicación
-- para permitir mayor flexibilidad con estados de reserva

-- ============================================
-- 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, -- 8 horas
  advance_booking_days INTEGER DEFAULT 30, -- días de anticipación máxima
  cancellation_window_minutes INTEGER DEFAULT 60, -- tiempo mínimo antes para cancelar
  no_show_threshold_minutes INTEGER DEFAULT 10, -- umbral para marcar no-show
  requires_approval BOOLEAN DEFAULT false,
  max_reservations_per_day INTEGER, -- límite por usuario por día
  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
);

-- ============================================
-- ÍNDICES para consultas frecuentes
-- ============================================
CREATE INDEX IF NOT EXISTS idx_reservations_resource_time
ON public.reservations(resource_id, start_at, end_at)
WHERE status IN ('confirmed', 'checked_in');

CREATE INDEX IF NOT EXISTS idx_reservations_user
ON public.reservations(user_id, start_at);

CREATE INDEX IF NOT EXISTS idx_reservations_status
ON public.reservations(status);

CREATE INDEX IF NOT EXISTS idx_resources_type
ON public.resources(resource_type_id)
WHERE is_active = true;

CREATE INDEX IF NOT EXISTS idx_users_email
ON public.users(email);

CREATE INDEX IF NOT EXISTS idx_locations_parent
ON public.locations(parent_id);

-- ============================================
-- RLS (Row Level Security)
-- ============================================
ALTER TABLE public.locations ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.resource_types ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.resources ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.reservations ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.resource_policies ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.no_show_events ENABLE ROW LEVEL SECURITY;

-- Políticas de lectura pública (PoC)
CREATE POLICY "locations_read_all" ON public.locations FOR SELECT USING (true);
CREATE POLICY "resource_types_read_all" ON public.resource_types FOR SELECT USING (true);
CREATE POLICY "resources_read_all" ON public.resources FOR SELECT USING (true);
CREATE POLICY "resource_policies_read_all" ON public.resource_policies FOR SELECT USING (true);

-- Políticas para users
CREATE POLICY "users_read_all" ON public.users FOR SELECT USING (true);
CREATE POLICY "users_insert" ON public.users FOR INSERT WITH CHECK (true);
CREATE POLICY "users_update_own" ON public.users FOR UPDATE USING (true);

-- Políticas para reservations
CREATE POLICY "reservations_read_all" ON public.reservations FOR SELECT USING (true);
CREATE POLICY "reservations_insert" ON public.reservations FOR INSERT WITH CHECK (true);
CREATE POLICY "reservations_update" ON public.reservations FOR UPDATE USING (true);
CREATE POLICY "reservations_delete" ON public.reservations FOR DELETE USING (true);

-- Políticas para no_show_events
CREATE POLICY "no_show_events_read_all" ON public.no_show_events FOR SELECT USING (true);
CREATE POLICY "no_show_events_insert" ON public.no_show_events FOR INSERT WITH CHECK (true);

-- Políticas admin (PoC: permisivas para authenticated)
CREATE POLICY "locations_all_auth" ON public.locations AS PERMISSIVE FOR ALL TO authenticated USING (true) WITH CHECK (true);
CREATE POLICY "resource_types_all_auth" ON public.resource_types AS PERMISSIVE FOR ALL TO authenticated USING (true) WITH CHECK (true);
CREATE POLICY "resources_all_auth" ON public.resources AS PERMISSIVE FOR ALL TO authenticated USING (true) WITH CHECK (true);
CREATE POLICY "resource_policies_all_auth" ON public.resource_policies AS PERMISSIVE FOR ALL TO authenticated USING (true) WITH CHECK (true);
