-- ============================================================
-- BARBERÍA LUKAKIN — Schema inicial
-- ============================================================

-- Extensiones
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ============================================================
-- TABLA: perfiles (extiende auth.users de Supabase)
-- ============================================================
CREATE TABLE public.perfiles (
  id            UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  nombre        TEXT NOT NULL,
  telefono      TEXT,
  rol           TEXT NOT NULL DEFAULT 'cliente' CHECK (rol IN ('cliente', 'barbero', 'admin')),
  avatar_url    TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ============================================================
-- TABLA: barberos
-- ============================================================
CREATE TABLE public.barberos (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  perfil_id     UUID REFERENCES public.perfiles(id) ON DELETE SET NULL,
  nombre        TEXT NOT NULL,
  descripcion   TEXT,
  foto_url      TEXT,
  activo        BOOLEAN NOT NULL DEFAULT TRUE,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ============================================================
-- TABLA: horarios_barbero (horario semanal de cada barbero)
-- ============================================================
CREATE TABLE public.horarios_barbero (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  barbero_id    UUID NOT NULL REFERENCES public.barberos(id) ON DELETE CASCADE,
  dia_semana    SMALLINT NOT NULL CHECK (dia_semana BETWEEN 0 AND 6), -- 0=Domingo, 1=Lunes...
  hora_inicio   TIME NOT NULL,
  hora_fin      TIME NOT NULL,
  activo        BOOLEAN NOT NULL DEFAULT TRUE,
  UNIQUE (barbero_id, dia_semana)
);

-- ============================================================
-- TABLA: servicios
-- ============================================================
CREATE TABLE public.servicios (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  nombre          TEXT NOT NULL,
  descripcion     TEXT,
  duracion_min    SMALLINT NOT NULL DEFAULT 30, -- duración en minutos
  precio          NUMERIC(10,2),
  activo          BOOLEAN NOT NULL DEFAULT TRUE,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ============================================================
-- TABLA: citas
-- ============================================================
CREATE TABLE public.citas (
  id                    UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  cliente_id            UUID NOT NULL REFERENCES public.perfiles(id) ON DELETE CASCADE,
  barbero_id            UUID NOT NULL REFERENCES public.barberos(id) ON DELETE CASCADE,
  servicio_id           UUID NOT NULL REFERENCES public.servicios(id) ON DELETE CASCADE,
  fecha_inicio          TIMESTAMPTZ NOT NULL,
  fecha_fin             TIMESTAMPTZ NOT NULL,
  estado                TEXT NOT NULL DEFAULT 'pendiente'
                          CHECK (estado IN ('pendiente', 'confirmada', 'cancelada', 'completada')),
  notas                 TEXT,
  google_event_id       TEXT,         -- ID del evento en Google Calendar
  google_event_cliente  TEXT,         -- ID del evento en el GCal del cliente
  recordatorio_enviado  BOOLEAN NOT NULL DEFAULT FALSE,
  created_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at            TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ============================================================
-- TABLA: historial_cortes
-- ============================================================
CREATE TABLE public.historial_cortes (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  cliente_id    UUID NOT NULL REFERENCES public.perfiles(id) ON DELETE CASCADE,
  cita_id       UUID REFERENCES public.citas(id) ON DELETE SET NULL,
  barbero_id    UUID REFERENCES public.barberos(id) ON DELETE SET NULL,
  servicio_id   UUID REFERENCES public.servicios(id) ON DELETE SET NULL,
  notas         TEXT,                 -- preferencias, estilo, etc.
  fecha         DATE NOT NULL DEFAULT CURRENT_DATE,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ============================================================
-- ÍNDICES
-- ============================================================
CREATE INDEX idx_citas_cliente    ON public.citas(cliente_id);
CREATE INDEX idx_citas_barbero    ON public.citas(barbero_id);
CREATE INDEX idx_citas_fecha      ON public.citas(fecha_inicio);
CREATE INDEX idx_citas_estado     ON public.citas(estado);
CREATE INDEX idx_historial_cliente ON public.historial_cortes(cliente_id);

-- ============================================================
-- TRIGGERS: updated_at automático
-- ============================================================
CREATE OR REPLACE FUNCTION public.set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_perfiles_updated_at
  BEFORE UPDATE ON public.perfiles
  FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

CREATE TRIGGER trg_citas_updated_at
  BEFORE UPDATE ON public.citas
  FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

-- ============================================================
-- TRIGGER: crear perfil automáticamente al registrarse
-- ============================================================
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.perfiles (id, nombre, rol)
  VALUES (
    NEW.id,
    COALESCE(NEW.raw_user_meta_data->>'nombre', NEW.email),
    COALESCE(NEW.raw_user_meta_data->>'rol', 'cliente')
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER trg_on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- ============================================================
-- ROW LEVEL SECURITY (RLS)
-- ============================================================
ALTER TABLE public.perfiles        ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.barberos        ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.horarios_barbero ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.servicios       ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.citas           ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.historial_cortes ENABLE ROW LEVEL SECURITY;

-- Perfiles: cada usuario ve su propio perfil; admins ven todos
CREATE POLICY "perfil_select_own" ON public.perfiles
  FOR SELECT USING (auth.uid() = id);

CREATE POLICY "perfil_update_own" ON public.perfiles
  FOR UPDATE USING (auth.uid() = id);

-- Barberos: lectura pública, escritura solo admin
CREATE POLICY "barberos_select_all" ON public.barberos
  FOR SELECT USING (true);

CREATE POLICY "barberos_admin_all" ON public.barberos
  FOR ALL USING (
    EXISTS (SELECT 1 FROM public.perfiles WHERE id = auth.uid() AND rol = 'admin')
  );

-- Horarios: lectura pública
CREATE POLICY "horarios_select_all" ON public.horarios_barbero
  FOR SELECT USING (true);

CREATE POLICY "horarios_admin_all" ON public.horarios_barbero
  FOR ALL USING (
    EXISTS (SELECT 1 FROM public.perfiles WHERE id = auth.uid() AND rol = 'admin')
  );

-- Servicios: lectura pública, escritura solo admin
CREATE POLICY "servicios_select_all" ON public.servicios
  FOR SELECT USING (true);

CREATE POLICY "servicios_admin_all" ON public.servicios
  FOR ALL USING (
    EXISTS (SELECT 1 FROM public.perfiles WHERE id = auth.uid() AND rol = 'admin')
  );

-- Citas: cliente ve las suyas; admin ve todas
CREATE POLICY "citas_select_own" ON public.citas
  FOR SELECT USING (
    auth.uid() = cliente_id OR
    EXISTS (SELECT 1 FROM public.perfiles WHERE id = auth.uid() AND rol IN ('admin', 'barbero'))
  );

CREATE POLICY "citas_insert_own" ON public.citas
  FOR INSERT WITH CHECK (auth.uid() = cliente_id);

CREATE POLICY "citas_update_admin" ON public.citas
  FOR UPDATE USING (
    auth.uid() = cliente_id OR
    EXISTS (SELECT 1 FROM public.perfiles WHERE id = auth.uid() AND rol IN ('admin', 'barbero'))
  );

-- Historial: cliente ve el suyo; admin ve todo
CREATE POLICY "historial_select_own" ON public.historial_cortes
  FOR SELECT USING (
    auth.uid() = cliente_id OR
    EXISTS (SELECT 1 FROM public.perfiles WHERE id = auth.uid() AND rol = 'admin')
  );

-- ============================================================
-- DATOS SEMILLA: servicios iniciales
-- ============================================================
INSERT INTO public.servicios (nombre, descripcion, duracion_min, precio) VALUES
  ('Corte de cabello',     'Corte clásico o moderno a tu elección',        30, 150.00),
  ('Arreglo de barba',     'Perfilado y arreglo de barba',                 20, 100.00),
  ('Corte + Barba',        'Combo corte de cabello y arreglo de barba',    45, 220.00),
  ('Corte niño',           'Corte de cabello para niños menores de 12',    25, 120.00),
  ('Afeitado clásico',     'Afeitado a navaja con toalla caliente',        30, 180.00);