-- SIP Online — Baseline v1.0
-- Supabase schema for Base Cadastral V2.0 migration
-- Reference: sip_base_cadastral_v2_0.html

create extension if not exists pgcrypto;

-- Updated timestamp helper
create or replace function public.set_updated_at()
returns trigger
language plpgsql
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

-- ---------------------------------------------------------------------
-- Configurações gerais
-- Substitui: anoAtivo/localStorage
-- ---------------------------------------------------------------------
create table if not exists public.configuracoes (
  chave text primary key,
  valor text not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create trigger trg_configuracoes_updated_at
before update on public.configuracoes
for each row execute function public.set_updated_at();

insert into public.configuracoes (chave, valor)
values ('ano_ativo', '2026')
on conflict (chave) do nothing;

-- ---------------------------------------------------------------------
-- Alunos
-- Cadastro permanente da criança.
-- Preserva os campos da Base Cadastral V2.0, incluindo campos editáveis
-- encontrados no formulário de aluno.
-- ---------------------------------------------------------------------
create table if not exists public.alunos (
  id text primary key,
  aluno_id text not null unique,
  nome text not null,
  data_nascimento text,
  cidade text,
  escola text,
  responsavel text,
  cpf_responsavel text,
  telefone_responsavel text,
  cep text,
  rua text,
  numero text,
  bairro text,
  uf text,
  observacoes text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists idx_alunos_nome on public.alunos using btree (nome);
create index if not exists idx_alunos_cidade on public.alunos using btree (cidade);
create index if not exists idx_alunos_escola on public.alunos using btree (escola);

create trigger trg_alunos_updated_at
before update on public.alunos
for each row execute function public.set_updated_at();

-- ---------------------------------------------------------------------
-- Turmas
-- Turmas disponíveis por ano letivo.
-- ---------------------------------------------------------------------
create table if not exists public.turmas (
  id text primary key,
  turma_id text not null unique,
  ano text not null,
  cidade text not null,
  nome text not null,
  dia text not null,
  horario text not null,
  capacidade text,
  instrutores text,
  local text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint uq_turmas_ano_cidade_nome unique (ano, cidade, nome)
);

create index if not exists idx_turmas_ano on public.turmas using btree (ano);
create index if not exists idx_turmas_cidade on public.turmas using btree (cidade);
create index if not exists idx_turmas_dia_horario on public.turmas using btree (dia, horario);

create trigger trg_turmas_updated_at
before update on public.turmas
for each row execute function public.set_updated_at();

-- ---------------------------------------------------------------------
-- Matrículas
-- Vínculo aluno × ano × cidade × turma.
-- Preserva a separação Cadastro x Matrícula.
-- ---------------------------------------------------------------------
create table if not exists public.matriculas (
  id text primary key,
  matricula_id text not null unique,
  aluno_id text not null references public.alunos(aluno_id) on update cascade on delete restrict,
  turma_id text references public.turmas(turma_id) on update cascade on delete set null,
  ano text not null,
  cidade text not null,
  turma text not null,
  serie_matricula text,
  situacao text not null default 'Ativa',
  observacoes text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint ck_matriculas_situacao check (situacao in ('Ativa','Lista de espera','Inativa','Desistente','Transferida')),
  constraint uq_matriculas_aluno_ano_cidade_turma unique (aluno_id, ano, cidade, turma)
);

create index if not exists idx_matriculas_aluno on public.matriculas using btree (aluno_id);
create index if not exists idx_matriculas_ano on public.matriculas using btree (ano);
create index if not exists idx_matriculas_cidade_turma on public.matriculas using btree (cidade, turma);
create index if not exists idx_matriculas_situacao on public.matriculas using btree (situacao);

create trigger trg_matriculas_updated_at
before update on public.matriculas
for each row execute function public.set_updated_at();

-- ---------------------------------------------------------------------
-- Totem V2.2.4 — preparado para integração futura.
-- Não altera a Base Cadastral.
-- ---------------------------------------------------------------------
create table if not exists public.registros_totem (
  id uuid primary key default gen_random_uuid(),
  aluno_id text references public.alunos(aluno_id) on update cascade on delete set null,
  matricula_id text references public.matriculas(matricula_id) on update cascade on delete set null,
  ano text,
  cidade text,
  turma text,
  momento text not null,
  resposta text not null,
  created_at timestamptz not null default now()
);

create index if not exists idx_registros_totem_data on public.registros_totem using btree (created_at);
create index if not exists idx_registros_totem_contexto on public.registros_totem using btree (ano, cidade, turma, momento);

-- ---------------------------------------------------------------------
-- Portfólio V1.6 — preparado para integração futura.
-- Não altera a Base Cadastral.
-- ---------------------------------------------------------------------
create table if not exists public.portfolio_registros (
  id uuid primary key default gen_random_uuid(),
  aluno_id text references public.alunos(aluno_id) on update cascade on delete set null,
  matricula_id text references public.matriculas(matricula_id) on update cascade on delete set null,
  ano text,
  cidade text,
  turma text,
  modelo text,
  resposta_1 text,
  resposta_2 text,
  resposta_3 text,
  observacao text,
  foto_url text,
  audio_url text,
  created_at timestamptz not null default now()
);

create index if not exists idx_portfolio_data on public.portfolio_registros using btree (created_at);
create index if not exists idx_portfolio_contexto on public.portfolio_registros using btree (ano, cidade, turma, modelo);

-- ---------------------------------------------------------------------
-- Views de compatibilidade com CSVs da Base Cadastral V2.0
-- Mantêm nomes/ordem lógica usados nas exportações atuais.
-- ---------------------------------------------------------------------
create or replace view public.v_export_alunos_v2 as
select
  aluno_id,
  nome,
  data_nascimento,
  cidade,
  escola,
  responsavel,
  cpf_responsavel,
  telefone_responsavel
from public.alunos;

create or replace view public.v_export_turmas_v2 as
select
  turma_id,
  ano,
  cidade,
  nome,
  dia,
  horario,
  capacidade,
  instrutores,
  local
from public.turmas;

create or replace view public.v_export_matriculas_v2 as
select
  m.matricula_id,
  m.aluno_id,
  m.ano,
  m.cidade,
  m.turma,
  a.nome,
  a.escola,
  m.serie_matricula,
  m.situacao
from public.matriculas m
join public.alunos a on a.aluno_id = m.aluno_id;

create or replace view public.v_export_matriculas_ativas_v2 as
select *
from public.v_export_matriculas_v2
where situacao = 'Ativa';

-- ---------------------------------------------------------------------
-- RLS: habilitado, com política aberta temporária para protótipo autenticado.
-- Em produção, substituir por políticas por perfil/cidade.
-- ---------------------------------------------------------------------
alter table public.configuracoes enable row level security;
alter table public.alunos enable row level security;
alter table public.turmas enable row level security;
alter table public.matriculas enable row level security;
alter table public.registros_totem enable row level security;
alter table public.portfolio_registros enable row level security;

create policy "authenticated read configuracoes" on public.configuracoes for select to authenticated using (true);
create policy "authenticated write configuracoes" on public.configuracoes for all to authenticated using (true) with check (true);

create policy "authenticated read alunos" on public.alunos for select to authenticated using (true);
create policy "authenticated write alunos" on public.alunos for all to authenticated using (true) with check (true);

create policy "authenticated read turmas" on public.turmas for select to authenticated using (true);
create policy "authenticated write turmas" on public.turmas for all to authenticated using (true) with check (true);

create policy "authenticated read matriculas" on public.matriculas for select to authenticated using (true);
create policy "authenticated write matriculas" on public.matriculas for all to authenticated using (true) with check (true);

create policy "authenticated read registros_totem" on public.registros_totem for select to authenticated using (true);
create policy "authenticated write registros_totem" on public.registros_totem for all to authenticated using (true) with check (true);

create policy "authenticated read portfolio_registros" on public.portfolio_registros for select to authenticated using (true);
create policy "authenticated write portfolio_registros" on public.portfolio_registros for all to authenticated using (true) with check (true);
