-- HU-ROOMS: Reservas v2 + notificaciones + ubicaciones jerarquicas

-- ============================================
-- Tipo de recurso: notificaciones por tipo
-- ============================================
ALTER TABLE public.resource_types
  ADD COLUMN IF NOT EXISTS reminders_enabled BOOLEAN DEFAULT false;

-- ============================================
-- Policies: ventanas de reserva/cancelacion/edicion (en minutos)
-- ============================================
ALTER TABLE public.resource_policies
  ADD COLUMN IF NOT EXISTS booking_advance_minutes INTEGER DEFAULT 14400, -- 10 dias
  ADD COLUMN IF NOT EXISTS cancellation_deadline_minutes INTEGER DEFAULT 300, -- 5 horas
  ADD COLUMN IF NOT EXISTS editing_deadline_minutes INTEGER DEFAULT 300; -- 5 horas

-- ============================================
-- Reservas: estado de recordatorio/reconfirmacion
-- ============================================
ALTER TABLE public.reservations
  ADD COLUMN IF NOT EXISTS reminder_sent_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS reconfirmation_status TEXT DEFAULT 'pending',
  ADD COLUMN IF NOT EXISTS reconfirmed_at TIMESTAMPTZ;

-- ============================================
-- Ubicaciones jerarquicas (arbol, max 20 niveles)
-- ============================================
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,
  value TEXT NOT NULL,
  depth INTEGER NOT NULL DEFAULT 1,
  path TEXT[] NOT NULL DEFAULT '{}',
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  CONSTRAINT locations_depth_range CHECK (depth >= 1 AND depth <= 20)
);

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

-- Trigger de updated_at para locations
CREATE OR REPLACE FUNCTION public.set_locations_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_locations_updated_at ON public.locations;
CREATE TRIGGER trg_locations_updated_at
BEFORE UPDATE ON public.locations
FOR EACH ROW
EXECUTE FUNCTION public.set_locations_updated_at();

-- RLS para locations
ALTER TABLE public.locations ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "locations_read_all" ON public.locations;
CREATE POLICY "locations_read_all" ON public.locations FOR SELECT USING (true);

DROP POLICY IF EXISTS "locations_admin" ON public.locations;
CREATE POLICY "locations_admin" ON public.locations FOR ALL USING (true);

