-- =========================
-- ATS (Talent CRM) – MVP
-- Multi-tenant + RLS + RPC + Vistas
-- =========================

-- Extensiones
create extension if not exists pgcrypto;
create extension if not exists pg_trgm;

-- Enums
do $$ begin
  create type etapa_postulacion as enum ('recibido','filtrado','entrevista1','tecnica','oferta','contratado','descartado');
exception when duplicate_object then null; end $$;

do $$ begin
  create type evento_tipo as enum ('nota','whatsapp_in','whatsapp_out','email_in','email_out','llamada','tarea','sistema');
exception when duplicate_object then null; end $$;

-- Empresas y miembros
create table if not exists public.empresas (
  id uuid primary key default gen_random_uuid(),
  nombre text not null,
  created_at timestamptz default now()
);

create table if not exists public.empresa_miembros (
  empresa_id uuid references public.empresas(id) on delete cascade,
  user_id uuid not null, -- = auth.users.id
  rol text check (rol in ('admin','reclutador','entrevistador','auditor')) not null default 'reclutador',
  primary key (empresa_id, user_id),
  created_at timestamptz default now()
);

-- Catálogos
create table if not exists public.skills (
  id uuid primary key default gen_random_uuid(),
  nombre text unique not null
);

-- Entidades
create table if not exists public.vacantes (
  id uuid primary key default gen_random_uuid(),
  empresa_id uuid not null references public.empresas(id) on delete cascade,
  titulo text not null,
  descripcion text,
  seniority text,
  modalidad text,
  ubicacion text,
  salario_min numeric, salario_max numeric,
  estado text not null default 'abierta',
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

create table if not exists public.candidatos (
  id uuid primary key default gen_random_uuid(),
  empresa_id uuid not null references public.empresas(id) on delete cascade,
  nombre text,
  email text,
  telefono_e164 text,
  ubicacion text,
  cv_storage_path text,
  linkedin_url text,
  portfolio_url text,
  notas text,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

create table if not exists public.candidato_skills (
  candidato_id uuid references public.candidatos(id) on delete cascade,
  skill_id uuid references public.skills(id) on delete restrict,
  nivel int check (nivel between 1 and 5),
  primary key (candidato_id, skill_id)
);

create table if not exists public.postulaciones (
  id uuid primary key default gen_random_uuid(),
  empresa_id uuid not null references public.empresas(id) on delete cascade,
  vacante_id uuid not null references public.vacantes(id) on delete cascade,
  candidato_id uuid not null references public.candidatos(id) on delete cascade,
  etapa etapa_postulacion not null default 'recibido',
  estado text not null default 'activa',
  prioridad int not null default 0,
  score numeric,
  fuente text,
  created_at timestamptz default now(),
  updated_at timestamptz default now(),
  unique (vacante_id, candidato_id)
);

create table if not exists public.eventos (
  id uuid primary key default gen_random_uuid(),
  empresa_id uuid not null references public.empresas(id) on delete cascade,
  candidato_id uuid not null references public.candidatos(id) on delete cascade,
  postulacion_id uuid references public.postulaciones(id) on delete cascade,
  tipo evento_tipo not null,
  contenido jsonb,
  creado_por uuid,
  created_at timestamptz default now()
);

-- Índices
create index if not exists idx_candidatos_empresa on public.candidatos(empresa_id);
create index if not exists idx_postulaciones_empresa on public.postulaciones(empresa_id);
create index if not exists idx_vacantes_empresa on public.vacantes(empresa_id);
create index if not exists idx_eventos_empresa on public.eventos(empresa_id);
create index if not exists idx_candidatos_busq on public.candidatos using gin ((to_tsvector('spanish',
  coalesce(nombre,'')||' '||coalesce(email,'')||' '||coalesce(ubicacion,''))));

-- Triggers updated_at
create or replace function public.touch_updated_at() returns trigger as $$
begin new.updated_at = now(); return new; end $$ language plpgsql;

drop trigger if exists vacantes_touch on public.vacantes;
create trigger vacantes_touch before update on public.vacantes
for each row execute function public.touch_updated_at();

drop trigger if exists candidatos_touch on public.candidatos;
create trigger candidatos_touch before update on public.candidatos
for each row execute function public.touch_updated_at();

drop trigger if exists postulaciones_touch on public.postulaciones;
create trigger postulaciones_touch before update on public.postulaciones
for each row execute function public.touch_updated_at();

-- RLS
alter table public.empresas enable row level security;
alter table public.empresa_miembros enable row level security;
alter table public.vacantes enable row level security;
alter table public.candidatos enable row level security;
alter table public.candidato_skills enable row level security;
alter table public.postulaciones enable row level security;
alter table public.eventos enable row level security;

-- Vista helper de membresía
create or replace view public._mi_empresas as
select em.empresa_id, em.user_id from public.empresa_miembros em;

-- Función membership
create or replace function public.is_member_of(empresa uuid) returns boolean
language sql stable as $$
  select exists (select 1 from public._mi_empresas m where m.empresa_id = empresa and m.user_id = auth.uid());
$$;

-- Policies
do $$ begin
  create policy empresas_select on public.empresas
    for select using (exists (select 1 from public._mi_empresas m where m.empresa_id = id and m.user_id = auth.uid()));
exception when duplicate_object then null; end $$;

do $$ begin
  create policy em_select on public.empresa_miembros
    for select using (empresa_id in (select empresa_id from public._mi_empresas where user_id = auth.uid()));
exception when duplicate_object then null; end $$;

do $$ begin
  create policy sel_vacantes on public.vacantes for select using (public.is_member_of(empresa_id));
  create policy ins_vacantes on public.vacantes for insert with check (public.is_member_of(empresa_id));
  create policy upd_vacantes on public.vacantes for update using (public.is_member_of(empresa_id)) with check (public.is_member_of(empresa_id));
exception when duplicate_object then null; end $$;

do $$ begin
  create policy sel_candidatos on public.candidatos for select using (public.is_member_of(empresa_id));
  create policy ins_candidatos on public.candidatos for insert with check (public.is_member_of(empresa_id));
  create policy upd_candidatos on public.candidatos for update using (public.is_member_of(empresa_id)) with check (public.is_member_of(empresa_id));
exception when duplicate_object then null; end $$;

do $$ begin
  create policy sel_candidato_skills on public.candidato_skills for select using (
    exists (select 1 from public.candidatos c where c.id = candidato_id and public.is_member_of(c.empresa_id))
  );
  create policy ins_candidato_skills on public.candidato_skills for insert with check (
    exists (select 1 from public.candidatos c where c.id = candidato_id and public.is_member_of(c.empresa_id))
  );
exception when duplicate_object then null; end $$;

do $$ begin
  create policy sel_postulaciones on public.postulaciones for select using (public.is_member_of(empresa_id));
  create policy ins_postulaciones on public.postulaciones for insert with check (public.is_member_of(empresa_id));
  create policy upd_postulaciones on public.postulaciones for update using (public.is_member_of(empresa_id)) with check (public.is_member_of(empresa_id));
exception when duplicate_object then null; end $$;

do $$ begin
  create policy sel_eventos on public.eventos for select using (public.is_member_of(empresa_id));
  create policy ins_eventos on public.eventos for insert with check (public.is_member_of(empresa_id));
exception when duplicate_object then null; end $$;

-- RPC: mover etapa (seguro)
create or replace function public.mover_etapa(p_postulacion uuid, p_nueva etapa_postulacion)
returns void language plpgsql security definer set search_path = public as $$
declare v_empresa uuid;
begin
  select empresa_id into v_empresa from public.postulaciones where id = p_postulacion;
  if v_empresa is null then raise exception 'postulacion no existe'; end if;
  if not public.is_member_of(v_empresa) then raise exception 'forbidden'; end if;

  update public.postulaciones set etapa = p_nueva where id = p_postulacion;

  insert into public.eventos(empresa_id, candidato_id, postulacion_id, tipo, contenido)
    select empresa_id, candidato_id, id, 'sistema', jsonb_build_object('accion','mover_etapa','nueva',p_nueva)
    from public.postulaciones where id = p_postulacion;
end $$;

-- Vista para Kanban
create or replace view public.v_kanban as
select p.id as postulacion_id, p.empresa_id, p.vacante_id, p.candidato_id, p.etapa, p.prioridad, p.score,
       c.nombre, c.ubicacion, c.email, c.telefono_e164, v.titulo as vacante
from public.postulaciones p
join public.candidatos c on c.id = p.candidato_id
join public.vacantes v on v.id = p.vacante_id;

-- (Opcional) policy explícita de la vista
do $$ begin
  create policy sel_v_kanban on public.v_kanban for select using (true);
exception when duplicate_object then null; end $$;

-- Policies storage.objects para bucket 'cv'
create policy if not exists cv_read on storage.objects
for select using (
  bucket_id = 'cv'
  and public.is_member_of( split_part(name, '/', 1)::uuid )
);

create policy if not exists cv_write on storage.objects
for insert with check (
  bucket_id = 'cv'
  and public.is_member_of( split_part(name, '/', 1)::uuid )
);

create policy if not exists cv_update_delete on storage.objects
for update using (
  bucket_id = 'cv'
  and public.is_member_of( split_part(name, '/', 1)::uuid )
) with check (
  bucket_id = 'cv'
  and public.is_member_of( split_part(name, '/', 1)::uuid )
);