SIGMA Presença

SIGMAPresença em cursos ← Voltar ao site

SIGMA · CNPJ 67.636.975/0001-81 · Dados tratados conforme a LGPD (Lei 13.709/2018)

-- SIGMA Presença — estrutura do banco online (Supabase)
-- Cole tudo no "SQL Editor" do Supabase e clique em RUN.
-- Pode ser executado mais de uma vez sem problema.

create extension if not exists pgcrypto;

-- ============ tabelas ============
create table if not exists sp_cursos (
  id uuid primary key default gen_random_uuid(),
  nome text not null,
  instrutor text,
  d1 date, d2 date,
  ch text, local text,
  lat double precision, lng double precision, raio int default 300,
  criado timestamptz default now()
);
create table if not exists sp_participantes (
  id uuid primary key default gen_random_uuid(),
  curso_id uuid not null references sp_cursos(id) on delete cascade,
  nome text not null, cpf text, email text,
  lgpd boolean default false, lgpd_at timestamptz,
  criado timestamptz default now()
);
create table if not exists sp_presencas (
  id uuid primary key default gen_random_uuid(),
  curso_id uuid not null references sp_cursos(id) on delete cascade,
  part_id uuid not null references sp_participantes(id) on delete cascade,
  data date not null,
  hora text, foto text, geo jsonb, dentro boolean, origem text,
  criado timestamptz default now(),
  unique (part_id, data)
);

-- ============ quem pode entrar no painel ============
-- Só os e-mails desta lista enxergam e alteram os dados, mesmo que alguém
-- consiga criar uma conta no projeto. Para incluir outro instrutor, repita
-- a linha do insert com o e-mail dele.
create table if not exists sp_instrutores (
  email text primary key
);
insert into sp_instrutores (email) values ('c.pessini@hotmail.com')
  on conflict do nothing;

create or replace function sp_eh_instrutor() returns boolean
language sql stable security definer set search_path = public as $$
  select exists (
    select 1 from sp_instrutores
    where lower(email) = lower(coalesce(auth.jwt() ->> 'email', ''))
  );
$$;
revoke all on function sp_eh_instrutor() from public;
grant execute on function sp_eh_instrutor() to authenticated;

-- ============ regras de acesso (RLS) ============
alter table sp_cursos enable row level security;
alter table sp_participantes enable row level security;
alter table sp_presencas enable row level security;
alter table sp_instrutores enable row level security;  -- sem regras: ninguém lê pela internet

drop policy if exists sp_auth_cursos on sp_cursos;
drop policy if exists sp_auth_part on sp_participantes;
drop policy if exists sp_auth_pres on sp_presencas;
create policy sp_auth_cursos on sp_cursos for all to authenticated
  using (sp_eh_instrutor()) with check (sp_eh_instrutor());
create policy sp_auth_part on sp_participantes for all to authenticated
  using (sp_eh_instrutor()) with check (sp_eh_instrutor());
create policy sp_auth_pres on sp_presencas for all to authenticated
  using (sp_eh_instrutor()) with check (sp_eh_instrutor());

-- ============ o que o participante (sem login) consegue fazer ============
-- 1) ver o nome do curso e a lista de nomes; 2) registrar a própria presença de hoje.
create or replace function sp_roster(p_curso uuid) returns json
language sql security definer set search_path = public as $$
  select json_build_object(
    'curso', (select json_build_object('nome', c.nome, 'instrutor', c.instrutor, 'd1', c.d1, 'd2', c.d2, 'local', c.local)
              from sp_cursos c where c.id = p_curso),
    'lista', coalesce((
      select json_agg(json_build_object('id', p.id, 'nome', p.nome,
               'ja', exists (select 1 from sp_presencas s where s.part_id = p.id
                             and s.data = (now() at time zone 'America/Sao_Paulo')::date)) order by p.nome)
      from sp_participantes p where p.curso_id = p_curso), '[]'::json));
$$;

create or replace function sp_registrar(p_curso uuid, p_part uuid, p_foto text, p_geo jsonb, p_origem text) returns json
language plpgsql security definer set search_path = public as $$
declare
  c sp_cursos;
  hoje date := (now() at time zone 'America/Sao_Paulo')::date;
  hh text := to_char(now() at time zone 'America/Sao_Paulo', 'HH24:MI');
  dent boolean := null; dist double precision; g jsonb := p_geo;
begin
  select * into c from sp_cursos where id = p_curso;
  if not found then return json_build_object('ok', false, 'msg', 'Curso não encontrado'); end if;
  if not exists (select 1 from sp_participantes where id = p_part and curso_id = p_curso) then
    return json_build_object('ok', false, 'msg', 'Participante não encontrado neste curso'); end if;
  if (c.d1 is not null and hoje < c.d1) or (c.d2 is not null and hoje > c.d2) then
    return json_build_object('ok', false, 'msg', 'Hoje está fora do período do curso'); end if;
  if exists (select 1 from sp_presencas where part_id = p_part and data = hoje) then
    return json_build_object('ok', false, 'msg', 'A presença de hoje já foi registrada'); end if;
  if c.lat is not null and g is not null and g ? 'lat' then
    dist := 6371000 * 2 * asin(sqrt(
      power(sin(radians(((g->>'lat')::float - c.lat) / 2)), 2) +
      cos(radians(c.lat)) * cos(radians((g->>'lat')::float)) *
      power(sin(radians(((g->>'lng')::float - c.lng) / 2)), 2)));
    dent := dist <= coalesce(c.raio, 300);
    g := g || jsonb_build_object('dist', round(dist));
  end if;
  insert into sp_presencas (curso_id, part_id, data, hora, foto, geo, dentro, origem)
    values (p_curso, p_part, hoje, hh, left(p_foto, 400000), g, dent, coalesce(p_origem, 'participante'));
  update sp_participantes set lgpd = true, lgpd_at = coalesce(lgpd_at, now()) where id = p_part;
  return json_build_object('ok', true, 'hora', hh, 'dentro', dent);
end $$;

revoke all on function sp_roster(uuid) from public;
revoke all on function sp_registrar(uuid, uuid, text, jsonb, text) from public;
grant execute on function sp_roster(uuid) to anon, authenticated;
grant execute on function sp_registrar(uuid, uuid, text, jsonb, text) to anon, authenticated;