-- Execute no SQL Editor do Supabase. Não coloque a service_role key no navegador.
create extension if not exists pgcrypto;
create table if not exists public.profiles (
 id uuid primary key references auth.users(id) on delete cascade,
 full_name text not null,
 department text not null default '',
 annual_days integer not null default 22 check (annual_days between 0 and 60),
 role text not null default 'employee' check (role in ('employee','manager')),
 legacy_id uuid unique
);
create table if not exists public.leaves (
 id uuid primary key default gen_random_uuid(),
 employee_id uuid not null references public.profiles(id) on delete cascade,
 starts_on date not null,
 ends_on date not null,
 status text not null default 'pending' check (status in ('pending','approved','rejected','historical')),
 note text not null default '',
 created_at timestamptz not null default now(),
 decided_at timestamptz,
 decided_by uuid references public.profiles(id),
 legacy_id uuid unique,
 check (ends_on >= starts_on)
);
create or replace function public.is_manager() returns boolean language sql stable security definer set search_path = '' as $$
 select exists(select 1 from public.profiles where id = (select auth.uid()) and role='manager');
$$;
alter table public.profiles enable row level security;
alter table public.leaves enable row level security;
drop policy if exists profiles_read on public.profiles;
create policy profiles_read on public.profiles for select to authenticated using (id=(select auth.uid()) or public.is_manager());
drop policy if exists profiles_manager_insert on public.profiles;
create policy profiles_manager_insert on public.profiles for insert to authenticated with check (public.is_manager());
drop policy if exists profiles_manager_update on public.profiles;
create policy profiles_manager_update on public.profiles for update to authenticated using (public.is_manager()) with check (public.is_manager());
drop policy if exists leaves_read on public.leaves;
create policy leaves_read on public.leaves for select to authenticated using (employee_id=(select auth.uid()) or public.is_manager());
drop policy if exists leaves_request on public.leaves;
create policy leaves_request on public.leaves for insert to authenticated with check (employee_id=(select auth.uid()) and status='pending' and decided_at is null and decided_by is null and legacy_id is null);
-- Atualizações são exclusivamente por função de aprovação, não por UPDATE direto.
create or replace function public.decide_leave(p_id uuid, p_status text) returns void
language plpgsql security definer set search_path = '' as $$
begin
 if not public.is_manager() then raise exception 'Apenas a gestora pode decidir'; end if;
 if p_status not in ('approved','rejected') then raise exception 'Estado inválido'; end if;
 update public.leaves set status=p_status, decided_at=now(), decided_by=(select auth.uid()) where id=p_id and status='pending';
 if not found then raise exception 'Pedido não encontrado ou já decidido'; end if;
end $$;
revoke all on function public.decide_leave(uuid,text) from public;
grant execute on function public.decide_leave(uuid,text) to authenticated;
-- Impedir que os funcionários se atribuam permissões: nenhuma política de UPDATE para eles.
-- Criar utilizadores em Authentication > Users, e associar IDs em profiles via SQL Editor.