Ecossistema Familiar Raiz

-- Plataforma Raiz: estrutura inicial. Contas e envio de e-mail serão conectados em etapa posterior.
CREATE TYPE public.family_permission AS ENUM ('primary_holder_1', 'primary_holder_2', 'adult_member', 'minor_dependent');
CREATE TABLE public.families (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), name text NOT NULL,
 status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','active')),
 created_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT ON public.families TO authenticated;
GRANT ALL ON public.families TO service_role;
ALTER TABLE public.families ENABLE ROW LEVEL SECURITY;
CREATE TABLE public.users (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), family_id uuid NOT NULL REFERENCES public.families(id),
 auth_user_id uuid, display_name text NOT NULL, birth_date date NOT NULL,
 access_mode text NOT NULL DEFAULT 'assisted' CHECK (access_mode IN ('assisted','individual')),
 created_at timestamptz NOT NULL DEFAULT now(),
 UNIQUE(family_id,id), UNIQUE(family_id,auth_user_id),
 CHECK (access_mode <> 'individual' OR auth_user_id IS NOT NULL)
);
GRANT SELECT ON public.users TO authenticated;
GRANT ALL ON public.users TO service_role;
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
CREATE TABLE public.user_roles (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), family_id uuid NOT NULL,
 user_id uuid NOT NULL UNIQUE, role public.family_permission NOT NULL,
 FOREIGN KEY(family_id,user_id) REFERENCES public.users(family_id,id)
);
GRANT SELECT ON public.user_roles TO authenticated;
GRANT ALL ON public.user_roles TO service_role;
ALTER TABLE public.user_roles ENABLE ROW LEVEL SECURITY;
CREATE UNIQUE INDEX family_holder_slot ON public.user_roles(family_id,role)
 WHERE role IN ('primary_holder_1','primary_holder_2');
CREATE TABLE public.family_tree_nodes (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), family_id uuid NOT NULL REFERENCES public.families(id),
 user_id uuid, parent_node_id uuid, display_name text NOT NULL,
 relationship text NOT NULL, created_at timestamptz NOT NULL DEFAULT now(),
 UNIQUE(family_id,id), FOREIGN KEY(family_id,user_id) REFERENCES public.users(family_id,id),
 FOREIGN KEY(family_id,parent_node_id) REFERENCES public.family_tree_nodes(family_id,id),
 CHECK (parent_node_id IS DISTINCT FROM id)
);
GRANT SELECT ON public.family_tree_nodes TO authenticated;
GRANT ALL ON public.family_tree_nodes TO service_role;
ALTER TABLE public.family_tree_nodes ENABLE ROW LEVEL SECURITY;
CREATE TABLE public.invitations (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), family_id uuid NOT NULL REFERENCES public.families(id),
 email text NOT NULL, token_hash text NOT NULL UNIQUE CHECK(length(token_hash)=64),
 invited_by uuid NOT NULL, tree_node_id uuid, relationship text NOT NULL,
 status text NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','accepted','revoked')),
 expires_at timestamptz NOT NULL, accepted_by uuid, accepted_at timestamptz,
 created_at timestamptz NOT NULL DEFAULT now(),
 FOREIGN KEY(family_id,invited_by) REFERENCES public.users(family_id,id),
 FOREIGN KEY(family_id,tree_node_id) REFERENCES public.family_tree_nodes(family_id,id),
 FOREIGN KEY(family_id,accepted_by) REFERENCES public.users(family_id,id)
);
-- Tokens e e-mails não são expostos pela API pública.
GRANT ALL ON public.invitations TO service_role;
ALTER TABLE public.invitations ENABLE ROW LEVEL SECURITY;
CREATE TABLE public.family_records (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), family_id uuid NOT NULL REFERENCES public.families(id),
 author_id uuid NOT NULL, kind text NOT NULL CHECK(kind IN ('post','diary','interaction','photo','album')),
 visibility text NOT NULL DEFAULT 'private' CHECK(visibility IN ('private','family')),
 body text NOT NULL DEFAULT '', media_path text, created_at timestamptz NOT NULL DEFAULT now(),
 FOREIGN KEY(family_id,author_id) REFERENCES public.users(family_id,id)
);
GRANT SELECT,INSERT,UPDATE,DELETE ON public.family_records TO authenticated;
GRANT ALL ON public.family_records TO service_role;
ALTER TABLE public.family_records ENABLE ROW LEVEL SECURITY;
CREATE TABLE public.family_audit_events (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), family_id uuid NOT NULL REFERENCES public.families(id),
 actor_auth_id uuid, entity text NOT NULL, entity_id uuid NOT NULL, action text NOT NULL,
 before_data jsonb, after_data jsonb, created_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT ON public.family_audit_events TO authenticated;
GRANT ALL ON public.family_audit_events TO service_role;
ALTER TABLE public.family_audit_events ENABLE ROW LEVEL SECURITY;

CREATE FUNCTION public.is_family_member(fid uuid) RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public
AS $$ SELECT EXISTS(SELECT 1 FROM public.users WHERE family_id=fid AND auth_user_id=auth.uid()) $$;
CREATE FUNCTION public.is_family_holder(fid uuid) RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public
AS $$ SELECT EXISTS(SELECT 1 FROM public.users u JOIN public.user_roles r ON r.user_id=u.id AND r.family_id=u.family_id
 WHERE u.family_id=fid AND u.auth_user_id=auth.uid() AND r.role IN ('primary_holder_1','primary_holder_2')) $$;
CREATE FUNCTION public.can_read_record(fid uuid, author uuid, shared boolean) RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public
AS $$ SELECT public.is_family_member(fid) AND EXISTS(SELECT 1 FROM public.users u WHERE u.id=author AND u.family_id=fid
 AND (u.auth_user_id=auth.uid() OR shared OR (u.birth_date > (CURRENT_DATE - INTERVAL '18 years')::date AND public.is_family_holder(fid)))) $$;
CREATE FUNCTION public.can_write_record(fid uuid, author uuid) RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public
AS $$ SELECT EXISTS(SELECT 1 FROM public.users u WHERE u.id=author AND u.family_id=fid
 AND (u.auth_user_id=auth.uid() OR (u.access_mode='assisted' AND u.birth_date > (CURRENT_DATE - INTERVAL '18 years')::date AND public.is_family_holder(fid)))) $$;
REVOKE ALL ON FUNCTION public.is_family_member(uuid), public.is_family_holder(uuid), public.can_read_record(uuid,uuid,boolean), public.can_write_record(uuid,uuid) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.is_family_member(uuid), public.is_family_holder(uuid), public.can_read_record(uuid,uuid,boolean), public.can_write_record(uuid,uuid) TO authenticated,service_role;
CREATE POLICY family_read ON public.families FOR SELECT TO authenticated USING(public.is_family_member(id));
CREATE POLICY members_read ON public.users FOR SELECT TO authenticated USING(public.is_family_member(family_id));
CREATE POLICY roles_read ON public.user_roles FOR SELECT TO authenticated USING(public.is_family_member(family_id));
CREATE POLICY tree_read ON public.family_tree_nodes FOR SELECT TO authenticated USING(public.is_family_member(family_id));
CREATE POLICY record_read ON public.family_records FOR SELECT TO authenticated USING(public.can_read_record(family_id,author_id,visibility='family'));
CREATE POLICY record_insert ON public.family_records FOR INSERT TO authenticated WITH CHECK(public.can_write_record(family_id,author_id));
CREATE POLICY record_update ON public.family_records FOR UPDATE TO authenticated USING(public.can_write_record(family_id,author_id)) WITH CHECK(public.can_write_record(family_id,author_id));
CREATE POLICY record_delete ON public.family_records FOR DELETE TO authenticated USING(public.can_write_record(family_id,author_id));
CREATE POLICY audit_read ON public.family_audit_events FOR SELECT TO authenticated USING(public.is_family_holder(family_id));

CREATE FUNCTION public.validate_family_core() RETURNS trigger
LANGUAGE plpgsql SET search_path = public AS $$
DECLARE dob date; linked uuid;
BEGIN
 IF TG_TABLE_NAME='users' THEN
  IF NEW.birth_date>CURRENT_DATE THEN RAISE EXCEPTION 'Data de nascimento futura'; END IF;
  IF TG_OP='UPDATE' AND (NEW.family_id<>OLD.family_id OR NEW.auth_user_id IS DISTINCT FROM OLD.auth_user_id) THEN RAISE EXCEPTION 'Vinculo imutavel'; END IF;
 ELSIF TG_TABLE_NAME='user_roles' THEN
  SELECT birth_date,auth_user_id INTO dob,linked FROM public.users WHERE id=NEW.user_id AND family_id=NEW.family_id;
  IF dob IS NULL THEN RAISE EXCEPTION 'Membro inexistente'; END IF;
  IF NEW.role='minor_dependent' AND dob <= (CURRENT_DATE-INTERVAL '18 years')::date THEN RAISE EXCEPTION 'Dependente deve ter menos de 18 anos'; END IF;
  IF NEW.role<>'minor_dependent' AND dob > (CURRENT_DATE-INTERVAL '18 years')::date THEN RAISE EXCEPTION 'Permissao adulta exige 18 anos'; END IF;
  IF NEW.role IN ('primary_holder_1','primary_holder_2') AND linked IS NULL THEN RAISE EXCEPTION 'Titular exige conta individual'; END IF;
 ELSIF TG_TABLE_NAME='invitations' THEN
  IF TG_OP='INSERT' AND NEW.expires_at<=now() THEN RAISE EXCEPTION 'Convite vencido'; END IF;
  IF NOT EXISTS(SELECT 1 FROM public.user_roles WHERE family_id=NEW.family_id AND user_id=NEW.invited_by AND role IN ('primary_holder_1','primary_holder_2')) THEN RAISE EXCEPTION 'Somente titulares convidam'; END IF;
 ELSIF TG_TABLE_NAME='family_records' AND TG_OP='UPDATE' THEN
  IF NEW.family_id<>OLD.family_id OR NEW.author_id<>OLD.author_id THEN RAISE EXCEPTION 'Autoria imutavel'; END IF;
 END IF;
 RETURN NEW;
END $$;
CREATE TRIGGER validate_user BEFORE INSERT OR UPDATE ON public.users FOR EACH ROW EXECUTE FUNCTION public.validate_family_core();
CREATE TRIGGER validate_role BEFORE INSERT OR UPDATE ON public.user_roles FOR EACH ROW EXECUTE FUNCTION public.validate_family_core();
CREATE TRIGGER validate_invitation BEFORE INSERT OR UPDATE ON public.invitations FOR EACH ROW EXECUTE FUNCTION public.validate_family_core();
CREATE TRIGGER validate_record BEFORE UPDATE ON public.family_records FOR EACH ROW EXECUTE FUNCTION public.validate_family_core();

CREATE FUNCTION public.enforce_dual_holders() RETURNS trigger
LANGUAGE plpgsql SET search_path=public AS $$
DECLARE fid uuid; n integer;
BEGIN
 fid := CASE WHEN TG_TABLE_NAME='families' THEN COALESCE(NEW.id,OLD.id) ELSE COALESCE(NEW.family_id,OLD.family_id) END;
 IF EXISTS(SELECT 1 FROM public.families WHERE id=fid AND status='active') THEN
  SELECT count(*) INTO n FROM public.user_roles r JOIN public.users u ON u.id=r.user_id AND u.family_id=r.family_id
  WHERE r.family_id=fid AND r.role IN ('primary_holder_1','primary_holder_2') AND u.birth_date <= (CURRENT_DATE-INTERVAL '18 years')::date AND u.auth_user_id IS NOT NULL;
  IF n<>2 THEN RAISE EXCEPTION 'Nucleo ativo exige dois titulares adultos'; END IF;
 END IF;
 RETURN NULL;
END $$;
CREATE CONSTRAINT TRIGGER dual_family AFTER INSERT OR UPDATE ON public.families DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION public.enforce_dual_holders();
CREATE CONSTRAINT TRIGGER dual_roles AFTER INSERT OR UPDATE OR DELETE ON public.user_roles DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION public.enforce_dual_holders();
CREATE CONSTRAINT TRIGGER dual_users AFTER UPDATE OR DELETE ON public.users DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION public.enforce_dual_holders();

CREATE FUNCTION public.audit_family_change() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path=public AS $$
DECLARE b jsonb; a jsonb; v jsonb;
BEGIN
 IF TG_OP<>'INSERT' THEN b:=to_jsonb(OLD); END IF;
 IF TG_OP<>'DELETE' THEN a:=to_jsonb(NEW); END IF;
 -- O hash secreto nunca entra no historico acessivel aos titulares.
 IF TG_TABLE_NAME='invitations' THEN b:=b-'token_hash'; a:=a-'token_hash'; END IF;
 v:=COALESCE(a,b);
 INSERT INTO public.family_audit_events(family_id,actor_auth_id,entity,entity_id,action,before_data,after_data)
 VALUES((v->>'family_id')::uuid,auth.uid(),TG_TABLE_NAME,(v->>'id')::uuid,TG_OP,b,a);
 RETURN NULL;
END $$;
CREATE TRIGGER audit_records AFTER INSERT OR UPDATE OR DELETE ON public.family_records FOR EACH ROW EXECUTE FUNCTION public.audit_family_change();
CREATE TRIGGER audit_users AFTER INSERT OR UPDATE OR DELETE ON public.users FOR EACH ROW EXECUTE FUNCTION public.audit_family_change();
CREATE TRIGGER audit_roles AFTER INSERT OR UPDATE OR DELETE ON public.user_roles FOR EACH ROW EXECUTE FUNCTION public.audit_family_change();
CREATE TRIGGER audit_tree AFTER INSERT OR UPDATE OR DELETE ON public.family_tree_nodes FOR EACH ROW EXECUTE FUNCTION public.audit_family_change();
CREATE TRIGGER audit_invitations AFTER INSERT OR UPDATE OR DELETE ON public.invitations FOR EACH ROW EXECUTE FUNCTION public.audit_family_change();

CREATE FUNCTION public.accept_family_invitation(raw_token text, member_name text, dob date) RETURNS uuid
LANGUAGE plpgsql SECURITY DEFINER SET search_path=public AS $$
DECLARE inv public.invitations; uid uuid; verified_email text;
BEGIN
 IF auth.uid() IS NULL THEN RAISE EXCEPTION 'Autenticacao obrigatoria'; END IF;
 SELECT lower(email) INTO verified_email FROM auth.users WHERE id=auth.uid() AND email_confirmed_at IS NOT NULL;
 IF verified_email IS NULL THEN RAISE EXCEPTION 'Confirme o e-mail'; END IF;
 IF dob IS NULL OR dob > (CURRENT_DATE-INTERVAL '18 years')::date THEN RAISE EXCEPTION 'Menores exigem cadastro assistido pelos titulares'; END IF;
 IF length(trim(member_name)) NOT BETWEEN 2 AND 100 OR length(raw_token)<32 THEN RAISE EXCEPTION 'Dados invalidos'; END IF;
 SELECT * INTO inv FROM public.invitations WHERE token_hash=encode(sha256(convert_to(raw_token,'UTF8')),'hex') AND lower(email)=verified_email AND status='pending' AND expires_at>now() FOR UPDATE;
 IF inv.id IS NULL THEN RAISE EXCEPTION 'Convite invalido, usado ou vencido'; END IF;
 INSERT INTO public.users(family_id,auth_user_id,display_name,birth_date,access_mode) VALUES(inv.family_id,auth.uid(),trim(member_name),dob,'individual') RETURNING id INTO uid;
 INSERT INTO public.user_roles(family_id,user_id,role) VALUES(inv.family_id,uid,'adult_member');
 IF inv.tree_node_id IS NOT NULL THEN
  UPDATE public.family_tree_nodes SET user_id=uid,display_name=trim(member_name) WHERE id=inv.tree_node_id AND family_id=inv.family_id AND user_id IS NULL;
  IF NOT FOUND THEN RAISE EXCEPTION 'Ramo ja ocupado'; END IF;
 ELSE
  INSERT INTO public.family_tree_nodes(family_id,user_id,display_name,relationship) VALUES(inv.family_id,uid,trim(member_name),inv.relationship);
 END IF;
 UPDATE public.invitations SET status='accepted',accepted_by=uid,accepted_at=now() WHERE id=inv.id;
 RETURN uid;
END $$;
REVOKE ALL ON FUNCTION public.accept_family_invitation(text,text,date) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.accept_family_invitation(text,text,date) TO authenticated;
REVOKE ALL ON FUNCTION public.validate_family_core(), public.enforce_dual_holders(), public.audit_family_change() FROM PUBLIC;
CREATE INDEX users_family ON public.users(family_id);
CREATE INDEX records_family_author ON public.family_records(family_id,author_id);
CREATE INDEX tree_family ON public.family_tree_nodes(family_id);
CREATE INDEX audit_family ON public.family_audit_events(family_id,created_at);
COMMENT ON TABLE public.users IS 'Membros do nucleo; nao guarda senhas. Identidade real pertence ao provedor de autenticacao.';
COMMENT ON TABLE public.user_roles IS 'Permissoes protegidas separadas dos dados do membro. Dois titulares tem igual autoridade.';
COMMENT ON TABLE public.family_records IS 'Fundacao de conteudo; midia futura exige armazenamento privado e URLs temporarias. Nao armazenar URLs publicas.';
CREATE OR REPLACE FUNCTION public.enforce_dual_holders() RETURNS trigger
LANGUAGE plpgsql SET search_path=public AS $$
DECLARE fid uuid; n integer; row_data jsonb;
BEGIN
 row_data := CASE WHEN TG_OP='DELETE' THEN to_jsonb(OLD) ELSE to_jsonb(NEW) END;
 fid := (CASE WHEN TG_TABLE_NAME='families' THEN row_data->>'id' ELSE row_data->>'family_id' END)::uuid;
 IF EXISTS(SELECT 1 FROM public.families WHERE id=fid AND status='active') THEN
  SELECT count(*) INTO n FROM public.user_roles r JOIN public.users u ON u.id=r.user_id AND u.family_id=r.family_id
  WHERE r.family_id=fid AND r.role IN ('primary_holder_1','primary_holder_2') AND u.birth_date <= (CURRENT_DATE-INTERVAL '18 years')::date AND u.auth_user_id IS NOT NULL;
  IF n<>2 THEN RAISE EXCEPTION 'Nucleo ativo exige dois titulares adultos'; END IF;
 END IF;
 RETURN NULL;
END $$;ALTER POLICY audit_read ON public.family_audit_events TO authenticated USING (
 public.is_family_holder(family_id) AND
 (entity <> 'family_records' OR public.can_read_record(family_id,
 (COALESCE(after_data,before_data)->>'author_id')::uuid,
 COALESCE(after_data,before_data)->>'visibility' = 'family'))
);
CREATE OR REPLACE FUNCTION public.is_family_holder(fid uuid) RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public
AS $$ SELECT EXISTS(SELECT 1 FROM public.users u JOIN public.user_roles r ON r.user_id=u.id AND r.family_id=u.family_id
 WHERE u.family_id=fid AND u.auth_user_id=auth.uid() AND u.birth_date <= (CURRENT_DATE-INTERVAL '18 years')::date AND r.role IN ('primary_holder_1','primary_holder_2')) $$;REVOKE ALL ON TABLE public.families, public.users, public.user_roles, public.family_tree_nodes, public.invitations, public.family_records, public.family_audit_events FROM PUBLIC, anon, authenticated;
GRANT SELECT ON TABLE public.families, public.users, public.user_roles, public.family_tree_nodes, public.family_audit_events TO authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.family_records TO authenticated;
GRANT ALL ON TABLE public.families, public.users, public.user_roles, public.family_tree_nodes, public.invitations, public.family_records, public.family_audit_events TO service_role;
REVOKE ALL ON TABLE public.inscricoes FROM PUBLIC, anon, authenticated;
GRANT INSERT ON TABLE public.inscricoes TO anon, authenticated;
GRANT ALL ON TABLE public.inscricoes TO service_role;
REVOKE ALL ON FUNCTION public.is_family_member(uuid), public.is_family_holder(uuid), public.can_read_record(uuid,uuid,boolean), public.can_write_record(uuid,uuid), public.accept_family_invitation(text,text,date), public.validate_family_core(), public.enforce_dual_holders(), public.audit_family_change() FROM PUBLIC, anon, authenticated;
GRANT EXECUTE ON FUNCTION public.is_family_member(uuid), public.is_family_holder(uuid), public.can_read_record(uuid,uuid,boolean), public.can_write_record(uuid,uuid) TO authenticated, service_role;
GRANT EXECUTE ON FUNCTION public.accept_family_invitation(text,text,date) TO authenticated;
CREATE OR REPLACE FUNCTION public.accept_family_invitation(raw_token text, member_name text, dob date) RETURNS uuid
LANGUAGE plpgsql SECURITY DEFINER SET search_path=public AS $$
DECLARE inv public.invitations; uid uuid; verified_email text;
BEGIN
 IF auth.uid() IS NULL THEN RAISE EXCEPTION 'Autenticacao obrigatoria'; END IF;
 IF raw_token IS NULL OR length(raw_token) NOT BETWEEN 32 AND 512 OR member_name IS NULL OR length(trim(member_name)) NOT BETWEEN 2 AND 100 THEN RAISE EXCEPTION 'Dados invalidos'; END IF;
 SELECT lower(email) INTO verified_email FROM auth.users WHERE id=auth.uid() AND email_confirmed_at IS NOT NULL;
 IF verified_email IS NULL THEN RAISE EXCEPTION 'Confirme o e-mail'; END IF;
 IF dob IS NULL OR dob > (CURRENT_DATE-INTERVAL '18 years')::date THEN RAISE EXCEPTION 'Menores exigem cadastro assistido pelos titulares'; END IF;
 SELECT * INTO inv FROM public.invitations WHERE token_hash=encode(sha256(convert_to(raw_token,'UTF8')),'hex') AND lower(email)=verified_email AND status='pending' AND expires_at>now() FOR UPDATE;
 IF inv.id IS NULL THEN RAISE EXCEPTION 'Convite invalido, usado ou vencido'; END IF;
 INSERT INTO public.users(family_id,auth_user_id,display_name,birth_date,access_mode) VALUES(inv.family_id,auth.uid(),trim(member_name),dob,'individual') RETURNING id INTO uid;
 INSERT INTO public.user_roles(family_id,user_id,role) VALUES(inv.family_id,uid,'adult_member');
 IF inv.tree_node_id IS NOT NULL THEN
  UPDATE public.family_tree_nodes SET user_id=uid,display_name=trim(member_name) WHERE id=inv.tree_node_id AND family_id=inv.family_id AND user_id IS NULL;
  IF NOT FOUND THEN RAISE EXCEPTION 'Ramo ja ocupado'; END IF;
 ELSE
  INSERT INTO public.family_tree_nodes(family_id,user_id,display_name,relationship) VALUES(inv.family_id,uid,trim(member_name),inv.relationship);
 END IF;
 UPDATE public.invitations SET status='accepted',accepted_by=uid,accepted_at=now() WHERE id=inv.id;
 RETURN uid;
END $$;
COMMENT ON FUNCTION public.accept_family_invitation(text,text,date) IS 'Authenticated confirmed-email single-use redemption; rejects null inputs and bounds token length before hashing.';CREATE OR REPLACE FUNCTION public.prevent_family_tree_cycle() RETURNS trigger
LANGUAGE plpgsql SET search_path=public AS $$
BEGIN
 IF TG_OP='UPDATE' AND NEW.family_id IS DISTINCT FROM OLD.family_id THEN RAISE EXCEPTION 'Nucleo da arvore imutavel'; END IF;
 IF NEW.parent_node_id IS NULL THEN RETURN NEW; END IF;
 PERFORM 1 FROM public.families WHERE id=NEW.family_id FOR UPDATE;
 IF NEW.parent_node_id=NEW.id OR EXISTS (
  WITH RECURSIVE ancestry AS (
   SELECT id,parent_node_id FROM public.family_tree_nodes WHERE id=NEW.parent_node_id AND family_id=NEW.family_id
   UNION
   SELECT n.id,n.parent_node_id FROM public.family_tree_nodes n JOIN ancestry a ON n.id=a.parent_node_id WHERE n.family_id=NEW.family_id
  ) SELECT 1 FROM ancestry WHERE id=NEW.id
 ) THEN RAISE EXCEPTION 'Ciclo genealogico nao permitido'; END IF;
 RETURN NEW;
END $$;
REVOKE ALL ON FUNCTION public.prevent_family_tree_cycle() FROM PUBLIC,anon,authenticated;
CREATE TRIGGER prevent_tree_cycles BEFORE INSERT OR UPDATE ON public.family_tree_nodes FOR EACH ROW EXECUTE FUNCTION public.prevent_family_tree_cycle();
COMMENT ON FUNCTION public.prevent_family_tree_cycle() IS 'Rejects cyclic ancestor chains and immutable family changes; serializes parent assignments through a family row lock.';ALTER FUNCTION public.prevent_family_tree_cycle() SECURITY DEFINER;
ALTER FUNCTION public.prevent_family_tree_cycle() SET search_path=public;
REVOKE ALL ON FUNCTION public.prevent_family_tree_cycle() FROM PUBLIC,anon,authenticated;
COMMENT ON FUNCTION public.prevent_family_tree_cycle() IS 'Internal trigger only: locks family row and checks ancestry under definer privileges; direct RPC execution denied to anonymous and authenticated roles.';ALTER TABLE public.family_tree_nodes ADD COLUMN second_parent_node_id uuid, ADD COLUMN spouse_node_id uuid, ADD COLUMN birth_date date;
ALTER TABLE public.family_tree_nodes ADD CONSTRAINT tree_second_parent_family_fk FOREIGN KEY(family_id,second_parent_node_id) REFERENCES public.family_tree_nodes(family_id,id), ADD CONSTRAINT tree_spouse_family_fk FOREIGN KEY(family_id,spouse_node_id) REFERENCES public.family_tree_nodes(family_id,id);
ALTER TABLE public.invitations ADD COLUMN intended_role public.family_permission NOT NULL DEFAULT 'adult_member';
GRANT SELECT(id,family_id,email,tree_node_id,relationship,status,expires_at,created_at,intended_role) ON public.invitations TO authenticated;
CREATE POLICY holder_read_invitation_summary ON public.invitations FOR SELECT TO authenticated USING(public.is_family_holder(family_id));
CREATE OR REPLACE FUNCTION public.validate_extended_tree() RETURNS trigger LANGUAGE plpgsql SET search_path=public AS $$
BEGIN
 PERFORM 1 FROM public.families WHERE id=NEW.family_id FOR UPDATE;
 IF NEW.birth_date>CURRENT_DATE OR length(trim(NEW.display_name)) NOT BETWEEN 2 AND 100 THEN RAISE EXCEPTION 'Nome ou nascimento invalido'; END IF;
 IF NEW.spouse_node_id=NEW.id OR NEW.parent_node_id=NEW.id OR NEW.second_parent_node_id=NEW.id OR (NEW.parent_node_id IS NOT NULL AND NEW.parent_node_id=NEW.second_parent_node_id) THEN RAISE EXCEPTION 'Vinculo familiar invalido'; END IF;
 IF EXISTS(WITH RECURSIVE ancestors(id) AS (SELECT unnest(ARRAY[NEW.parent_node_id,NEW.second_parent_node_id]) UNION SELECT unnest(ARRAY[n.parent_node_id,n.second_parent_node_id]) FROM public.family_tree_nodes n JOIN ancestors a ON n.id=a.id WHERE n.family_id=NEW.family_id) SELECT 1 FROM ancestors WHERE id=NEW.id) THEN RAISE EXCEPTION 'Ciclo genealogico'; END IF;
 RETURN NEW;
END $$;
REVOKE ALL ON FUNCTION public.validate_extended_tree() FROM PUBLIC,anon,authenticated;
CREATE TRIGGER validate_extended_tree BEFORE INSERT OR UPDATE ON public.family_tree_nodes FOR EACH ROW EXECUTE FUNCTION public.validate_extended_tree();
CREATE FUNCTION public.manage_family_workspace(payload jsonb) RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path=public AS $$
DECLARE action text:=payload->>'action'; fid uuid; uid uuid; nid uuid; inv_id uuid; target public.family_tree_nodes; dob date; mode text; intended public.family_permission; raw text; em text; current_email text;
BEGIN
 IF auth.uid() IS NULL THEN RAISE EXCEPTION 'Entre na sua conta'; END IF;
 SELECT lower(email) INTO current_email FROM auth.users WHERE id=auth.uid() AND email_confirmed_at IS NOT NULL;
 IF current_email IS NULL THEN RAISE EXCEPTION 'Confirme seu e-mail'; END IF;
 IF octet_length(payload::text)>12000 THEN RAISE EXCEPTION 'Dados muito extensos'; END IF;
 IF action='create' THEN
  IF length(trim(payload->>'familyName')) NOT BETWEEN 2 AND 100 OR length(trim(payload->>'name')) NOT BETWEEN 2 AND 100 THEN RAISE EXCEPTION 'Informe os nomes'; END IF;
  dob:=(payload->>'birthDate')::date;
  IF dob IS NULL OR dob>(CURRENT_DATE-INTERVAL '18 years')::date THEN RAISE EXCEPTION 'Titular deve ter 18 anos'; END IF;
  PERFORM pg_advisory_xact_lock(hashtext(auth.uid()::text));
  IF EXISTS(SELECT 1 FROM public.users WHERE auth_user_id=auth.uid()) THEN RAISE EXCEPTION 'Sua conta ja pertence a uma familia'; END IF;
  INSERT INTO public.families(name) VALUES(trim(payload->>'familyName')) RETURNING id INTO fid;
  INSERT INTO public.users(family_id,auth_user_id,display_name,birth_date,access_mode) VALUES(fid,auth.uid(),trim(payload->>'name'),dob,'individual') RETURNING id INTO uid;
  INSERT INTO public.user_roles(family_id,user_id,role) VALUES(fid,uid,'primary_holder_1');
  INSERT INTO public.family_tree_nodes(family_id,user_id,display_name,relationship,birth_date) VALUES(fid,uid,trim(payload->>'name'),'Titular',dob);
  RETURN jsonb_build_object('familyId',fid);
 END IF;
 fid:=(payload->>'familyId')::uuid;
 PERFORM 1 FROM public.families WHERE id=fid FOR UPDATE;
 IF NOT public.is_family_holder(fid) THEN RAISE EXCEPTION 'Somente titulares podem alterar esta familia'; END IF;
 SELECT id INTO uid FROM public.users WHERE family_id=fid AND auth_user_id=auth.uid();
 IF action='revoke' THEN
  UPDATE public.invitations SET status='revoked' WHERE id=(payload->>'invitationId')::uuid AND family_id=fid AND status='pending';
  RETURN '{}'::jsonb;
 END IF;
 IF action NOT IN ('add','edit','invite') THEN RAISE EXCEPTION 'Acao invalida'; END IF;
 nid:=nullif(payload->>'nodeId','')::uuid;
 IF action IN ('edit','invite') THEN
  SELECT * INTO target FROM public.family_tree_nodes WHERE id=nid AND family_id=fid;
  IF target.id IS NULL THEN RAISE EXCEPTION 'Parente nao encontrado'; END IF;
 END IF;
 IF action IN ('add','edit') THEN
  IF length(trim(payload->>'name')) NOT BETWEEN 2 AND 100 OR length(trim(payload->>'relationship')) NOT BETWEEN 2 AND 100 THEN RAISE EXCEPTION 'Nome e parentesco obrigatorios'; END IF;
  dob:=nullif(payload->>'birthDate','')::date;
  IF dob>CURRENT_DATE THEN RAISE EXCEPTION 'Nascimento futuro'; END IF;
  IF action='add' THEN
   INSERT INTO public.family_tree_nodes(family_id,display_name,relationship,birth_date,parent_node_id,second_parent_node_id,spouse_node_id) VALUES(fid,trim(payload->>'name'),trim(payload->>'relationship'),dob,nullif(payload->>'parentId','')::uuid,nullif(payload->>'secondParentId','')::uuid,nullif(payload->>'spouseId','')::uuid) RETURNING id INTO nid;
   mode:=coalesce(payload->>'mode','tree');
   IF mode NOT IN ('tree','assisted','invite') THEN RAISE EXCEPTION 'Modo invalido'; END IF;
   IF mode='assisted' THEN
    IF dob IS NULL OR dob<=(CURRENT_DATE-INTERVAL '18 years')::date THEN RAISE EXCEPTION 'Dependente deve ter menos de 18 anos'; END IF;
    INSERT INTO public.users(family_id,display_name,birth_date) VALUES(fid,trim(payload->>'name'),dob) RETURNING id INTO inv_id;
    INSERT INTO public.user_roles(family_id,user_id,role) VALUES(fid,inv_id,'minor_dependent');
    UPDATE public.family_tree_nodes SET user_id=inv_id WHERE id=nid;
   END IF;
  ELSE
   UPDATE public.family_tree_nodes SET display_name=trim(payload->>'name'),relationship=trim(payload->>'relationship'),parent_node_id=nullif(payload->>'parentId','')::uuid,second_parent_node_id=nullif(payload->>'secondParentId','')::uuid,spouse_node_id=nullif(payload->>'spouseId','')::uuid,birth_date=CASE WHEN user_id IS NULL THEN dob ELSE birth_date END WHERE id=nid AND family_id=fid;
   IF target.user_id IS NOT NULL THEN UPDATE public.users SET display_name=trim(payload->>'name') WHERE id=target.user_id AND family_id=fid; END IF;
  END IF;
 END IF;
 IF action='invite' OR (action='add' AND mode='invite') THEN
  SELECT * INTO target FROM public.family_tree_nodes WHERE id=nid AND family_id=fid;
  IF target.user_id IS NOT NULL THEN RAISE EXCEPTION 'Este parente ja possui cadastro'; END IF;
  IF target.birth_date>(CURRENT_DATE-INTERVAL '18 years')::date THEN RAISE EXCEPTION 'Menores exigem acesso assistido'; END IF;
  em:=lower(trim(payload->>'email'));
  IF em IS NULL OR length(em)>255 OR em !~ '^[^[:space:]@]+@[^[:space:]@]+\.[^[:space:]@]+$' OR em=current_email THEN RAISE EXCEPTION 'Informe outro e-mail valido'; END IF;
  intended:=coalesce(nullif(payload->>'role',''),'adult_member')::public.family_permission;
  IF intended NOT IN ('adult_member','primary_holder_2') THEN RAISE EXCEPTION 'Permissao de convite invalida'; END IF;
  IF intended='primary_holder_2' AND EXISTS(SELECT 1 FROM public.user_roles WHERE family_id=fid AND role='primary_holder_2') THEN RAISE EXCEPTION 'Segundo titular ja definido'; END IF;
  IF EXISTS(SELECT 1 FROM public.invitations WHERE family_id=fid AND status='pending' AND expires_at>now() AND (tree_node_id=nid OR lower(email)=em OR (intended='primary_holder_2' AND intended_role='primary_holder_2'))) THEN RAISE EXCEPTION 'Ja existe um convite pendente; cancele antes de gerar outro'; END IF;
  raw:=encode(gen_random_bytes(32),'hex');
  INSERT INTO public.invitations(family_id,email,token_hash,invited_by,tree_node_id,relationship,intended_role,expires_at) VALUES(fid,em,encode(sha256(convert_to(raw,'UTF8')),'hex'),uid,nid,target.relationship,intended,now()+interval '7 days') RETURNING id INTO inv_id;
  RETURN jsonb_build_object('nodeId',nid,'token',raw,'invitationId',inv_id);
 END IF;
 RETURN jsonb_build_object('nodeId',nid);
END $$;
REVOKE ALL ON FUNCTION public.manage_family_workspace(jsonb) FROM PUBLIC,anon;
GRANT EXECUTE ON FUNCTION public.manage_family_workspace(jsonb) TO authenticated;
CREATE OR REPLACE FUNCTION public.accept_family_invitation(raw_token text,member_name text,dob date) RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER SET search_path=public AS $$
DECLARE inv public.invitations; uid uuid; verified_email text; fid uuid;
BEGIN
 IF auth.uid() IS NULL THEN RAISE EXCEPTION 'Autenticacao obrigatoria'; END IF;
 IF raw_token IS NULL OR length(raw_token) NOT BETWEEN 32 AND 512 OR member_name IS NULL OR length(trim(member_name)) NOT BETWEEN 2 AND 100 THEN RAISE EXCEPTION 'Dados invalidos'; END IF;
 SELECT lower(email) INTO verified_email FROM auth.users WHERE id=auth.uid() AND email_confirmed_at IS NOT NULL;
 IF verified_email IS NULL THEN RAISE EXCEPTION 'Confirme o e-mail'; END IF;
 IF dob IS NULL OR dob>(CURRENT_DATE-INTERVAL '18 years')::date THEN RAISE EXCEPTION 'Menores exigem cadastro assistido pelos titulares'; END IF;
 SELECT family_id INTO fid FROM public.invitations WHERE token_hash=encode(sha256(convert_to(raw_token,'UTF8')),'hex');
 PERFORM 1 FROM public.families WHERE id=fid FOR UPDATE;
 SELECT * INTO inv FROM public.invitations WHERE token_hash=encode(sha256(convert_to(raw_token,'UTF8')),'hex') AND lower(email)=verified_email AND status='pending' AND expires_at>now() FOR UPDATE;
 IF inv.id IS NULL THEN RAISE EXCEPTION 'Convite invalido, usado, vencido ou destinado a outro e-mail'; END IF;
 IF NOT EXISTS(SELECT 1 FROM public.user_roles WHERE family_id=inv.family_id AND user_id=inv.invited_by AND role IN ('primary_holder_1','primary_holder_2')) THEN RAISE EXCEPTION 'Convite sem titular autorizado'; END IF;
 IF EXISTS(SELECT 1 FROM public.users WHERE auth_user_id=auth.uid()) THEN RAISE EXCEPTION 'Sua conta ja pertence a uma familia'; END IF;
 INSERT INTO public.users(family_id,auth_user_id,display_name,birth_date,access_mode) VALUES(inv.family_id,auth.uid(),trim(member_name),dob,'individual') RETURNING id INTO uid;
 INSERT INTO public.user_roles(family_id,user_id,role) VALUES(inv.family_id,uid,inv.intended_role);
 IF inv.tree_node_id IS NOT NULL THEN
  UPDATE public.family_tree_nodes SET user_id=uid,display_name=trim(member_name),birth_date=dob WHERE id=inv.tree_node_id AND family_id=inv.family_id AND user_id IS NULL;
  IF NOT FOUND THEN RAISE EXCEPTION 'Ramo ja ocupado'; END IF;
 ELSE INSERT INTO public.family_tree_nodes(family_id,user_id,display_name,relationship,birth_date) VALUES(inv.family_id,uid,trim(member_name),inv.relationship,dob);
 END IF;
 UPDATE public.invitations SET status='accepted',accepted_by=uid,accepted_at=now() WHERE id=inv.id;
 IF inv.intended_role='primary_holder_2' THEN UPDATE public.families SET status='active' WHERE id=inv.family_id; END IF;
 RETURN uid;
END $$;
REVOKE ALL ON FUNCTION public.accept_family_invitation(text,text,date) FROM PUBLIC,anon;
GRANT EXECUTE ON FUNCTION public.accept_family_invitation(text,text,date) TO authenticated;DO $$ DECLARE definition text; BEGIN
 SELECT pg_get_functiondef('public.manage_family_workspace(jsonb)'::regprocedure) INTO definition;
 definition:=replace(definition,'raw:=encode(gen_random_bytes(32),''hex'');','raw:=replace(gen_random_uuid()::text,''-'','''')||replace(gen_random_uuid()::text,''-'','''');');
 definition:=replace(definition,'IF length(trim(payload->>''familyName'')) NOT BETWEEN 2 AND 100 OR length(trim(payload->>''name'')) NOT BETWEEN 2 AND 100','IF coalesce(length(trim(payload->>''familyName'')),0) NOT BETWEEN 2 AND 100 OR coalesce(length(trim(payload->>''name'')),0) NOT BETWEEN 2 AND 100');
 definition:=replace(definition,'IF length(trim(payload->>''name'')) NOT BETWEEN 2 AND 100 OR length(trim(payload->>''relationship'')) NOT BETWEEN 2 AND 100','IF coalesce(length(trim(payload->>''name'')),0) NOT BETWEEN 2 AND 100 OR coalesce(length(trim(payload->>''relationship'')),0) NOT BETWEEN 2 AND 100');
 EXECUTE definition;
 SELECT pg_get_functiondef('public.accept_family_invitation(text,text,date)'::regprocedure) INTO definition;
 definition:=replace(definition,'IF EXISTS(SELECT 1 FROM public.users WHERE auth_user_id=auth.uid()) THEN','PERFORM pg_advisory_xact_lock(hashtext(auth.uid()::text)); IF EXISTS(SELECT 1 FROM public.users WHERE auth_user_id=auth.uid()) THEN');
 EXECUTE definition;
END $$;ALTER FUNCTION public.validate_extended_tree() SECURITY DEFINER;
REVOKE ALL ON FUNCTION public.validate_extended_tree() FROM PUBLIC,anon,authenticated;
-- Fotos familiares: criar previamente o bucket privado family-photos pela API de armazenamento (limite 5 MB).
CREATE TABLE public.family_person_photos (
 node_id uuid PRIMARY KEY,
 family_id uuid NOT NULL REFERENCES public.families(id),
 storage_path text NOT NULL,
 updated_at timestamptz NOT NULL DEFAULT now(),
 CONSTRAINT photo_node_family_fk FOREIGN KEY(family_id,node_id) REFERENCES public.family_tree_nodes(family_id,id) ON DELETE CASCADE
);
GRANT SELECT ON public.family_person_photos TO authenticated;
GRANT ALL ON public.family_person_photos TO service_role;
ALTER TABLE public.family_person_photos ENABLE ROW LEVEL SECURITY;
CREATE POLICY family_photo_read ON public.family_person_photos FOR SELECT TO authenticated USING(public.is_family_member(family_id));
CREATE FUNCTION public.can_access_family_photo(object_name text, writing boolean) RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path=public AS $$
 SELECT EXISTS(SELECT 1 FROM public.family_tree_nodes n WHERE n.family_id::text=split_part(object_name,'/',1) AND n.id::text=split_part(object_name,'/',2) AND CASE WHEN writing THEN public.is_family_holder(n.family_id) ELSE public.is_family_member(n.family_id) END);
$$;
REVOKE ALL ON FUNCTION public.can_access_family_photo(text,boolean) FROM PUBLIC,anon;
GRANT EXECUTE ON FUNCTION public.can_access_family_photo(text,boolean) TO authenticated;
CREATE POLICY private_family_photo_read ON storage.objects FOR SELECT TO authenticated USING(bucket_id='family-photos' AND public.can_access_family_photo(name,false));
CREATE POLICY private_family_photo_insert ON storage.objects FOR INSERT TO authenticated WITH CHECK(bucket_id='family-photos' AND public.can_access_family_photo(name,true) AND name ~ '^[0-9a-f-]{36}/[0-9a-f-]{36}/[0-9a-f-]{36}\.jpg$');
CREATE POLICY private_family_photo_delete ON storage.objects FOR DELETE TO authenticated USING(bucket_id='family-photos' AND public.can_access_family_photo(name,true));
CREATE FUNCTION public.set_family_person_photo(fid uuid,nid uuid,photo_path text) RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path=public AS $$
BEGIN
 IF NOT public.is_family_holder(fid) OR NOT EXISTS(SELECT 1 FROM public.family_tree_nodes WHERE id=nid AND family_id=fid) THEN RAISE EXCEPTION 'Somente titulares da família podem alterar fotos.'; END IF;
 IF photo_path IS NULL THEN DELETE FROM public.family_person_photos WHERE node_id=nid AND family_id=fid; RETURN; END IF;
 IF split_part(photo_path,'/',1)<>fid::text OR split_part(photo_path,'/',2)<>nid::text OR photo_path !~ '^[0-9a-f-]{36}/[0-9a-f-]{36}/[0-9a-f-]{36}\.jpg$' OR NOT EXISTS(SELECT 1 FROM storage.objects WHERE bucket_id='family-photos' AND name=photo_path AND metadata->>'mimetype'='image/jpeg') THEN RAISE EXCEPTION 'Foto inválida ou não enviada.'; END IF;
 INSERT INTO public.family_person_photos(node_id,family_id,storage_path) VALUES(nid,fid,photo_path) ON CONFLICT(node_id) DO UPDATE SET storage_path=EXCLUDED.storage_path,updated_at=now();
END;
$$;
REVOKE ALL ON FUNCTION public.set_family_person_photo(uuid,uuid,text) FROM PUBLIC,anon;
GRANT EXECUTE ON FUNCTION public.set_family_person_photo(uuid,uuid,text) TO authenticated;
REVOKE ALL ON public.family_person_photos FROM anon,authenticated;
GRANT SELECT ON public.family_person_photos TO authenticated;
GRANT ALL ON public.family_person_photos TO service_role;

CREATE FUNCTION public.renew_family_invitation(invitation_id uuid) RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path=public AS $$
DECLARE inv public.invitations; raw text;
BEGIN
 SELECT * INTO inv FROM public.invitations WHERE id=invitation_id;
 IF inv.id IS NULL OR NOT public.is_family_holder(inv.family_id) THEN RAISE EXCEPTION 'Somente titulares podem renovar este convite'; END IF;
 PERFORM 1 FROM public.families WHERE id=inv.family_id FOR UPDATE;
 SELECT * INTO inv FROM public.invitations WHERE id=invitation_id FOR UPDATE;
 IF inv.status <> 'pending' THEN RAISE EXCEPTION 'Convite já aceito ou cancelado'; END IF;
 IF EXISTS(SELECT 1 FROM public.family_tree_nodes WHERE id=inv.tree_node_id AND user_id IS NOT NULL) THEN RAISE EXCEPTION 'Familiar já possui acesso'; END IF;
 IF inv.intended_role='primary_holder_2' AND EXISTS(SELECT 1 FROM public.user_roles WHERE family_id=inv.family_id AND role='primary_holder_2') THEN RAISE EXCEPTION 'Segundo titular já definido'; END IF;
 raw:=replace(gen_random_uuid()::text,'-','')||replace(gen_random_uuid()::text,'-','');
 UPDATE public.invitations SET token_hash=encode(sha256(convert_to(raw,'UTF8')),'hex'),expires_at=now()+interval '7 days' WHERE id=inv.id;
 RETURN jsonb_build_object('token',raw,'nodeId',inv.tree_node_id);
END $$;
REVOKE ALL ON FUNCTION public.renew_family_invitation(uuid) FROM PUBLIC,anon;
GRANT EXECUTE ON FUNCTION public.renew_family_invitation(uuid) TO authenticated;

-- Revisão de privacidade: verificar ambas as versões antes de expor o histórico.
ALTER POLICY audit_read ON public.family_audit_events TO authenticated USING (
 public.is_family_holder(family_id) AND
 (entity <> 'family_records' OR (
  (before_data IS NULL OR public.can_read_record(family_id,(before_data->>'author_id')::uuid,before_data->>'visibility' = 'family'))
  AND (after_data IS NULL OR public.can_read_record(family_id,(after_data->>'author_id')::uuid,after_data->>'visibility' = 'family'))
 ))
);
ALTER TABLE public.inscricoes ADD CONSTRAINT inscricoes_remaining_text_bounded CHECK (coalesce(char_length(tempo_casados),0) <= 100 AND coalesce(char_length(filhos),0) <= 100);
REVOKE INSERT ON public.inscricoes FROM anon, authenticated;
GRANT INSERT (membro,igreja,nome_esposo,nome_esposa,whatsapp,email,tempo_casados,filhos,expectativa,forma_pagamento,questionario,respondente,email_esposo,telefone_esposo,email_esposa,telefone_esposa) ON public.inscricoes TO anon, authenticated;
CREATE TABLE public.family_profiles (
  family_id uuid PRIMARY KEY REFERENCES public.families(id) ON DELETE CASCADE,
  city text NOT NULL DEFAULT '' CHECK (char_length(city) <= 80),
  state text NOT NULL DEFAULT '' CHECK (char_length(state) <= 40),
  foundations jsonb NOT NULL DEFAULT '{}'::jsonb CHECK (pg_column_size(foundations) <= 16384 AND jsonb_typeof(foundations) = 'object'),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE public.family_member_profiles (
  node_id uuid PRIMARY KEY,
  family_id uuid NOT NULL REFERENCES public.families(id) ON DELETE CASCADE,
  gender text NOT NULL DEFAULT '' CHECK (gender IN ('', 'masculino', 'feminino', 'outro', 'prefiro_nao_informar')),
  family_role text NOT NULL DEFAULT '' CHECK (char_length(family_role) <= 40),
  interests jsonb NOT NULL DEFAULT '{}'::jsonb CHECK (pg_column_size(interests) <= 8192 AND jsonb_typeof(interests) = 'object'),
  affection text[] NOT NULL DEFAULT '{}' CHECK (cardinality(affection) <= 5),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT member_profile_node_fk FOREIGN KEY (node_id, family_id) REFERENCES public.family_tree_nodes(id, family_id) ON DELETE CASCADE
);
GRANT SELECT ON public.family_profiles, public.family_member_profiles TO authenticated;
GRANT ALL ON public.family_profiles, public.family_member_profiles TO service_role;
ALTER TABLE public.family_profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.family_member_profiles ENABLE ROW LEVEL SECURITY;
CREATE POLICY family_profile_read ON public.family_profiles FOR SELECT TO authenticated USING (public.is_family_member(family_id));
CREATE POLICY member_profile_read ON public.family_member_profiles FOR SELECT TO authenticated USING (public.is_family_member(family_id));

CREATE OR REPLACE FUNCTION public.save_family_profile(fid uuid, payload jsonb) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  IF auth.uid() IS NULL OR NOT public.is_family_holder(fid) THEN RAISE EXCEPTION 'Somente titulares podem alterar o núcleo familiar'; END IF;
  INSERT INTO public.family_profiles(family_id, city, state, foundations, updated_at)
  VALUES (fid, left(coalesce(payload->>'city',''),80), left(coalesce(payload->>'state',''),40), coalesce(payload->'foundations','{}'::jsonb), now())
  ON CONFLICT (family_id) DO UPDATE SET city=EXCLUDED.city, state=EXCLUDED.state, foundations=EXCLUDED.foundations, updated_at=now();
END $$;

CREATE OR REPLACE FUNCTION public.save_member_profile(fid uuid, nid uuid, payload jsonb) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  IF auth.uid() IS NULL OR NOT public.is_family_holder(fid) THEN RAISE EXCEPTION 'Somente titulares podem alterar perfis'; END IF;
  IF NOT EXISTS (SELECT 1 FROM public.family_tree_nodes WHERE id=nid AND family_id=fid) THEN RAISE EXCEPTION 'Familiar não encontrado'; END IF;
  INSERT INTO public.family_member_profiles(node_id, family_id, gender, family_role, interests, affection, updated_at)
  VALUES (nid, fid, coalesce(payload->>'gender',''), left(coalesce(payload->>'role',''),40), coalesce(payload->'interests','{}'::jsonb),
    coalesce(ARRAY(SELECT jsonb_array_elements_text(coalesce(payload->'affection','[]'::jsonb))), '{}'), now())
  ON CONFLICT (node_id) DO UPDATE SET gender=EXCLUDED.gender, family_role=EXCLUDED.family_role, interests=EXCLUDED.interests, affection=EXCLUDED.affection, updated_at=now();
END $$;
REVOKE ALL ON FUNCTION public.save_family_profile(uuid, jsonb), public.save_member_profile(uuid, uuid, jsonb) FROM PUBLIC, anon;
GRANT EXECUTE ON FUNCTION public.save_family_profile(uuid, jsonb), public.save_member_profile(uuid, uuid, jsonb) TO authenticated;REVOKE ALL ON public.family_profiles, public.family_member_profiles FROM anon, authenticated, PUBLIC;
GRANT SELECT ON public.family_profiles, public.family_member_profiles TO authenticated;CREATE TABLE public.family_behavioral_assessments (
 node_id uuid PRIMARY KEY,
 family_id uuid NOT NULL REFERENCES public.families(id) ON DELETE CASCADE,
 version integer NOT NULL DEFAULT 1 CHECK (version = 1),
 answers jsonb NOT NULL CHECK (jsonb_typeof(answers) = 'array' AND jsonb_array_length(answers) = 20 AND pg_column_size(answers) <= 8192),
 self_primary text NOT NULL CHECK (self_primary IN ('acao','conexao','harmonia','estrutura')),
 self_secondary text NOT NULL CHECK (self_secondary IN ('acao','conexao','harmonia','estrutura') AND self_secondary <> self_primary),
 share_summary boolean NOT NULL DEFAULT false,
 updated_at timestamptz NOT NULL DEFAULT now(),
 CONSTRAINT assessment_node_family_fk FOREIGN KEY (node_id,family_id) REFERENCES public.family_tree_nodes(id,family_id) ON DELETE CASCADE
);
REVOKE ALL ON public.family_behavioral_assessments FROM PUBLIC, anon, authenticated;
GRANT SELECT ON public.family_behavioral_assessments TO authenticated;
GRANT ALL ON public.family_behavioral_assessments TO service_role;
ALTER TABLE public.family_behavioral_assessments ENABLE ROW LEVEL SECURITY;
CREATE POLICY assessment_self_read ON public.family_behavioral_assessments FOR SELECT TO authenticated USING (public.is_family_member(family_id) AND EXISTS (SELECT 1 FROM public.family_tree_nodes n JOIN public.users u ON u.id=n.user_id AND u.family_id=n.family_id WHERE n.id=node_id AND n.family_id=family_behavioral_assessments.family_id AND u.auth_user_id=auth.uid()));
CREATE FUNCTION public.save_adult_behavioral_assessment(fid uuid,nid uuid,payload jsonb) RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path=public AS $$
DECLARE item jsonb; birth date;
BEGIN
 IF auth.uid() IS NULL OR NOT public.is_family_member(fid) THEN RAISE EXCEPTION 'Acesso negado'; END IF;
 SELECT u.birth_date INTO birth FROM public.family_tree_nodes n JOIN public.users u ON u.id=n.user_id AND u.family_id=n.family_id WHERE n.id=nid AND n.family_id=fid AND u.auth_user_id=auth.uid();
 IF birth IS NULL OR birth > (CURRENT_DATE-INTERVAL '18 years')::date THEN RAISE EXCEPTION 'Autoavaliacao exclusiva do proprio adulto'; END IF;
 IF payload IS NULL OR jsonb_typeof(payload->'answers') IS DISTINCT FROM 'array' OR jsonb_array_length(payload->'answers') <> 20 THEN RAISE EXCEPTION 'Responda as 20 questoes'; END IF;
 FOR item IN SELECT jsonb_array_elements(payload->'answers') LOOP
  IF jsonb_typeof(item) IS DISTINCT FROM 'object' OR (item->>'primary') IS NULL OR (item->>'secondary') IS NULL OR (item->>'primary') NOT IN ('A','B','C','D') OR (item->>'secondary') NOT IN ('A','B','C','D') OR item->>'primary'=item->>'secondary' THEN RAISE EXCEPTION 'Duas tendencias distintas por questao'; END IF;
 END LOOP;
 IF payload->>'selfPrimary' IS NULL OR payload->>'selfSecondary' IS NULL OR payload->>'selfPrimary' NOT IN ('acao','conexao','harmonia','estrutura') OR payload->>'selfSecondary' NOT IN ('acao','conexao','harmonia','estrutura') OR payload->>'selfPrimary'=payload->>'selfSecondary' THEN RAISE EXCEPTION 'Autodeclaracao invalida'; END IF;
 IF jsonb_typeof(payload->'shareSummary') IS DISTINCT FROM 'boolean' THEN RAISE EXCEPTION 'Informe a preferencia de privacidade'; END IF;
 INSERT INTO public.family_behavioral_assessments(node_id,family_id,answers,self_primary,self_secondary,share_summary) VALUES(nid,fid,payload->'answers',payload->>'selfPrimary',payload->>'selfSecondary',(payload->>'shareSummary')::boolean)
 ON CONFLICT(node_id) DO UPDATE SET answers=EXCLUDED.answers,self_primary=EXCLUDED.self_primary,self_secondary=EXCLUDED.self_secondary,share_summary=EXCLUDED.share_summary,updated_at=now();
END $$;
REVOKE ALL ON FUNCTION public.save_adult_behavioral_assessment(uuid,uuid,jsonb) FROM PUBLIC,anon;
GRANT EXECUTE ON FUNCTION public.save_adult_behavioral_assessment(uuid,uuid,jsonb) TO authenticated;
CREATE FUNCTION public.family_behavioral_summaries(fid uuid) RETURNS TABLE(node_id uuid,ranking jsonb) LANGUAGE sql STABLE SECURITY DEFINER SET search_path=public AS $$
 WITH mappings AS (SELECT * FROM (VALUES
 (1,ARRAY['acao','conexao','harmonia','estrutura']), (2,ARRAY['conexao','acao','harmonia','estrutura']), (3,ARRAY['estrutura','acao','harmonia','conexao']), (4,ARRAY['harmonia','acao','conexao','estrutura']), (5,ARRAY['acao','conexao','harmonia','estrutura']),
 (6,ARRAY['harmonia','acao','estrutura','conexao']), (7,ARRAY['acao','estrutura','conexao','harmonia']), (8,ARRAY['acao','conexao','harmonia','estrutura']), (9,ARRAY['estrutura','acao','conexao','harmonia']), (10,ARRAY['conexao','estrutura','harmonia','acao']),
 (11,ARRAY['estrutura','acao','harmonia','conexao']), (12,ARRAY['estrutura','acao','harmonia','conexao']), (13,ARRAY['harmonia','acao','conexao','estrutura']), (14,ARRAY['acao','estrutura','conexao','harmonia']), (15,ARRAY['estrutura','acao','harmonia','conexao']),
 (16,ARRAY['acao','harmonia','estrutura','conexao']), (17,ARRAY['estrutura','acao','harmonia','conexao']), (18,ARRAY['acao','harmonia','estrutura','conexao']), (19,ARRAY['estrutura','acao','harmonia','conexao']), (20,ARRAY['acao','estrutura','harmonia','conexao'])) AS m(q,concepts)),
 choices AS (SELECT a.node_id, m.concepts[strpos('ABCD',e.item->>'primary')] AS profile,2 AS weight FROM public.family_behavioral_assessments a CROSS JOIN LATERAL jsonb_array_elements(a.answers) WITH ORDINALITY e(item,q) JOIN mappings m ON m.q=e.q WHERE a.family_id=fid AND public.is_family_member(fid) AND (a.share_summary OR EXISTS(SELECT 1 FROM public.family_tree_nodes n JOIN public.users u ON u.id=n.user_id AND u.family_id=n.family_id WHERE n.id=a.node_id AND n.family_id=fid AND u.auth_user_id=auth.uid()))
 UNION ALL SELECT a.node_id,m.concepts[strpos('ABCD',e.item->>'secondary')],1 FROM public.family_behavioral_assessments a CROSS JOIN LATERAL jsonb_array_elements(a.answers) WITH ORDINALITY e(item,q) JOIN mappings m ON m.q=e.q WHERE a.family_id=fid AND public.is_family_member(fid) AND (a.share_summary OR EXISTS(SELECT 1 FROM public.family_tree_nodes n JOIN public.users u ON u.id=n.user_id AND u.family_id=n.family_id WHERE n.id=a.node_id AND n.family_id=fid AND u.auth_user_id=auth.uid()))),
 totals AS (SELECT c.node_id,c.profile,sum(c.weight) AS points FROM choices c GROUP BY c.node_id,c.profile)
 SELECT t.node_id,jsonb_agg(jsonb_build_object('perfil',t.profile,'pontos',t.points,'percentual',round(t.points*100.0/60,1)) ORDER BY t.points DESC,array_position(ARRAY['acao','conexao','harmonia','estrutura'],t.profile)) FROM totals t GROUP BY t.node_id;
$$;
REVOKE ALL ON FUNCTION public.family_behavioral_summaries(uuid) FROM PUBLIC,anon;
GRANT EXECUTE ON FUNCTION public.family_behavioral_summaries(uuid) TO authenticated;DO $migration$
DECLARE definition text;
BEGIN
 SELECT pg_get_functiondef('public.family_behavioral_summaries(uuid)'::regprocedure) INTO definition;
 IF position('FROM totals t GROUP BY t.node_id' IN definition)=0 THEN RAISE EXCEPTION 'Unexpected summary definition'; END IF;
 definition := replace(definition,'FROM totals t GROUP BY t.node_id','FROM (SELECT totals.*, row_number() OVER (PARTITION BY totals.node_id ORDER BY totals.points DESC,array_position(ARRAY[''acao'',''conexao'',''harmonia'',''estrutura''],totals.profile)) AS position FROM totals) t WHERE t.position <= 2 GROUP BY t.node_id');
 EXECUTE definition;
END $migration$;
COMMENT ON FUNCTION public.family_behavioral_summaries(uuid) IS 'Only the two strongest conceptual profiles; shared solely by explicit opt-in or to the respondent. No answers or private report.';
-- ===== Migracao 0023: autodeclaracao removida do fluxo (colunas legadas) =====
COMMENT ON COLUMN public.family_behavioral_assessments.self_primary IS 'DEPRECATED: autodeclaracao removida do fluxo; valores legados ignorados pelo aplicativo.';
COMMENT ON COLUMN public.family_behavioral_assessments.self_secondary IS 'DEPRECATED: autodeclaracao removida do fluxo; valores legados ignorados pelo aplicativo.';
CREATE OR REPLACE FUNCTION public.save_adult_behavioral_assessment(fid uuid,nid uuid,payload jsonb) RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path=public AS $$
DECLARE item jsonb; birth date;
BEGIN
  IF auth.uid() IS NULL OR NOT public.is_family_member(fid) THEN RAISE EXCEPTION 'Acesso negado'; END IF;
  SELECT u.birth_date INTO birth FROM public.family_tree_nodes n JOIN public.users u ON u.id=n.user_id AND u.family_id=n.family_id WHERE n.id=nid AND n.family_id=fid AND u.auth_user_id=auth.uid();
  IF birth IS NULL OR birth > (CURRENT_DATE-INTERVAL '18 years')::date THEN RAISE EXCEPTION 'Autoavaliacao exclusiva do proprio adulto'; END IF;
  IF payload IS NULL OR jsonb_typeof(payload->'answers') IS DISTINCT FROM 'array' OR jsonb_array_length(payload->'answers') <> 20 THEN RAISE EXCEPTION 'Responda as 20 questoes'; END IF;
  FOR item IN SELECT jsonb_array_elements(payload->'answers') LOOP
    IF jsonb_typeof(item) IS DISTINCT FROM 'object' OR (item->>'primary') IS NULL OR (item->>'secondary') IS NULL OR (item->>'primary') NOT IN ('A','B','C','D') OR (item->>'secondary') NOT IN ('A','B','C','D') OR item->>'primary'=item->>'secondary' THEN RAISE EXCEPTION 'Duas tendencias distintas por questao'; END IF;
  END LOOP;
  IF jsonb_typeof(payload->'shareSummary') IS DISTINCT FROM 'boolean' THEN RAISE EXCEPTION 'Informe a preferencia de privacidade'; END IF;
  INSERT INTO public.family_behavioral_assessments(node_id,family_id,answers,self_primary,self_secondary,share_summary) VALUES(nid,fid,payload->'answers','acao','conexao',(payload->>'shareSummary')::boolean)
    ON CONFLICT(node_id) DO UPDATE SET answers=EXCLUDED.answers,share_summary=EXCLUDED.share_summary,updated_at=now();
END $$;
-- ===== Migração 0024: trilha adolescente privada =====
CREATE TABLE public.family_adolescent_assessments (
 node_id uuid PRIMARY KEY, family_id uuid NOT NULL REFERENCES public.families(id) ON DELETE CASCADE,
 version integer NOT NULL DEFAULT 1 CHECK(version=1),
 answers jsonb NOT NULL CHECK(jsonb_typeof(answers)='array' AND jsonb_array_length(answers)=20 AND pg_column_size(answers)<=8192),
 updated_at timestamptz NOT NULL DEFAULT now(),
 FOREIGN KEY(node_id,family_id) REFERENCES public.family_tree_nodes(id,family_id) ON DELETE CASCADE
);
REVOKE ALL ON public.family_adolescent_assessments FROM PUBLIC,anon,authenticated;
GRANT SELECT ON public.family_adolescent_assessments TO authenticated;
GRANT ALL ON public.family_adolescent_assessments TO service_role;
ALTER TABLE public.family_adolescent_assessments ENABLE ROW LEVEL SECURITY;
CREATE FUNCTION public.can_access_adolescent_assessment(fid uuid,nid uuid) RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path=public AS $$
 SELECT auth.uid() IS NOT NULL AND public.is_family_member(fid) AND EXISTS(
 SELECT 1 FROM public.family_tree_nodes n JOIN public.users u ON u.id=n.user_id AND u.family_id=n.family_id
 WHERE n.id=nid AND n.family_id=fid AND (u.auth_user_id=auth.uid() OR (u.access_mode='assisted' AND public.is_family_holder(fid)))
 AND u.birth_date <= (CURRENT_DATE-INTERVAL '13 years')::date AND u.birth_date > (CURRENT_DATE-INTERVAL '18 years')::date)
$$;
REVOKE ALL ON FUNCTION public.can_access_adolescent_assessment(uuid,uuid) FROM PUBLIC,anon;
GRANT EXECUTE ON FUNCTION public.can_access_adolescent_assessment(uuid,uuid) TO authenticated,service_role;
CREATE POLICY adolescent_protected_read ON public.family_adolescent_assessments FOR SELECT TO authenticated USING(public.can_access_adolescent_assessment(family_id,node_id));
CREATE FUNCTION public.save_adolescent_behavioral_assessment(fid uuid,nid uuid,payload jsonb) RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path=public AS $$
DECLARE item jsonb;
BEGIN
 IF NOT public.can_access_adolescent_assessment(fid,nid) THEN RAISE EXCEPTION 'Trilha exclusiva de 13 a 17 anos: acesso próprio ou assistido pelo titular'; END IF;
 IF payload IS NULL OR jsonb_typeof(payload->'answers') IS DISTINCT FROM 'array' OR jsonb_array_length(payload->'answers')<>20 OR pg_column_size(payload)>8192 THEN RAISE EXCEPTION 'Responda as 20 questões'; END IF;
 FOR item IN SELECT jsonb_array_elements(payload->'answers') LOOP
 IF jsonb_typeof(item) IS DISTINCT FROM 'object' OR item->>'primary' IS NULL OR item->>'secondary' IS NULL OR item->>'primary' NOT IN ('A','B','C','D') OR item->>'secondary' NOT IN ('A','B','C','D') OR item->>'primary'=item->>'secondary' THEN RAISE EXCEPTION 'Escolha duas tendências distintas por questão'; END IF;
 END LOOP;
 INSERT INTO public.family_adolescent_assessments(node_id,family_id,answers) VALUES(nid,fid,payload->'answers') ON CONFLICT(node_id) DO UPDATE SET answers=EXCLUDED.answers,updated_at=now();
END $$;
REVOKE ALL ON FUNCTION public.save_adolescent_behavioral_assessment(uuid,uuid,jsonb) FROM PUBLIC,anon;
GRANT EXECUTE ON FUNCTION public.save_adolescent_behavioral_assessment(uuid,uuid,jsonb) TO authenticated;
COMMENT ON TABLE public.family_adolescent_assessments IS 'Private adolescent 20-question self-assessment; assisted dependents respond in a holder session. Never exposed through family summaries.';CREATE TABLE public.family_child_assessments (
 node_id uuid PRIMARY KEY, family_id uuid NOT NULL REFERENCES public.families(id) ON DELETE CASCADE,
 version integer NOT NULL DEFAULT 1 CHECK(version=1),
 answers jsonb NOT NULL CHECK(jsonb_typeof(answers)='array' AND jsonb_array_length(answers)=20 AND pg_column_size(answers)<=8192),
 respondent_user_id uuid NOT NULL, updated_at timestamptz NOT NULL DEFAULT now(),
 FOREIGN KEY(node_id,family_id) REFERENCES public.family_tree_nodes(id,family_id) ON DELETE CASCADE
);
REVOKE ALL ON public.family_child_assessments FROM PUBLIC,anon,authenticated;
GRANT SELECT ON public.family_child_assessments TO authenticated;
GRANT ALL ON public.family_child_assessments TO service_role;
ALTER TABLE public.family_child_assessments ENABLE ROW LEVEL SECURITY;
CREATE FUNCTION public.can_access_child_assessment(fid uuid,nid uuid) RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path=public AS $$
 SELECT auth.uid() IS NOT NULL AND public.is_family_holder(fid) AND EXISTS (
 SELECT 1 FROM public.family_tree_nodes n LEFT JOIN public.users u ON u.id=n.user_id AND u.family_id=n.family_id
 WHERE n.id=nid AND n.family_id=fid
 AND COALESCE(u.birth_date,n.birth_date) <= (CURRENT_DATE-INTERVAL '9 years')::date
 AND COALESCE(u.birth_date,n.birth_date) > (CURRENT_DATE-INTERVAL '13 years')::date)
$$;
REVOKE ALL ON FUNCTION public.can_access_child_assessment(uuid,uuid) FROM PUBLIC,anon;
GRANT EXECUTE ON FUNCTION public.can_access_child_assessment(uuid,uuid) TO authenticated,service_role;
CREATE POLICY child_holder_read ON public.family_child_assessments FOR SELECT TO authenticated USING(public.can_access_child_assessment(family_id,node_id));
CREATE FUNCTION public.save_child_behavioral_assessment(fid uuid,nid uuid,payload jsonb) RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path=public AS $$
DECLARE item jsonb;
BEGIN
 IF auth.uid() IS NULL OR NOT public.is_family_holder(fid) THEN RAISE EXCEPTION 'Somente titulares podem responder pela criança'; END IF;
 PERFORM 1 FROM public.families WHERE id=fid FOR UPDATE;
 PERFORM 1 FROM public.family_tree_nodes WHERE id=nid AND family_id=fid FOR UPDATE;
 PERFORM 1 FROM public.users u JOIN public.family_tree_nodes n ON n.user_id=u.id AND n.family_id=u.family_id WHERE n.id=nid AND n.family_id=fid FOR UPDATE OF u;
 IF NOT public.can_access_child_assessment(fid,nid) THEN RAISE EXCEPTION 'Trilha exclusiva de 9 a 12 anos: acesso pelo titular'; END IF;
 IF payload IS NULL OR jsonb_typeof(payload->'answers') IS DISTINCT FROM 'array' THEN RAISE EXCEPTION 'Responda as 20 questões'; END IF;
 IF jsonb_array_length(payload->'answers')<>20 OR pg_column_size(payload)>8192 THEN RAISE EXCEPTION 'Responda as 20 questões'; END IF;
 FOR item IN SELECT jsonb_array_elements(payload->'answers') LOOP
 IF jsonb_typeof(item) IS DISTINCT FROM 'object' OR item->>'primary' IS NULL OR item->>'secondary' IS NULL OR item->>'primary' NOT IN ('A','B','C','D') OR item->>'secondary' NOT IN ('A','B','C','D') OR item->>'primary'=item->>'secondary' THEN RAISE EXCEPTION 'Escolha duas tendências distintas por questão'; END IF;
 END LOOP;
 INSERT INTO public.family_child_assessments(node_id,family_id,answers,respondent_user_id) VALUES(nid,fid,payload->'answers',auth.uid()) ON CONFLICT(node_id) DO UPDATE SET answers=EXCLUDED.answers,respondent_user_id=EXCLUDED.respondent_user_id,updated_at=now();
END $$;
REVOKE ALL ON FUNCTION public.save_child_behavioral_assessment(uuid,uuid,jsonb) FROM PUBLIC,anon;
GRANT EXECUTE ON FUNCTION public.save_child_behavioral_assessment(uuid,uuid,jsonb) TO authenticated;
COMMENT ON TABLE public.family_child_assessments IS 'Private 9-12 assessment based on holder observation. Tree-only children are supported. No child answers or summaries are shared with ordinary family members.';

-- Acesso assistido obrigatório e avaliação parental adolescente separada
CREATE FUNCTION public.enforce_minor_assisted_access() RETURNS trigger LANGUAGE plpgsql SET search_path=public AS $$ BEGIN IF NEW.birth_date > (CURRENT_DATE-INTERVAL '18 years')::date THEN NEW.access_mode:='assisted'; END IF; RETURN NEW; END $$;
REVOKE ALL ON FUNCTION public.enforce_minor_assisted_access() FROM PUBLIC,anon,authenticated;
CREATE TRIGGER enforce_minor_assisted BEFORE INSERT OR UPDATE ON public.users FOR EACH ROW EXECUTE FUNCTION public.enforce_minor_assisted_access();
CREATE TABLE public.family_adolescent_parent_assessments (
 node_id uuid PRIMARY KEY, family_id uuid NOT NULL REFERENCES public.families(id) ON DELETE CASCADE,
 version integer NOT NULL DEFAULT 1 CHECK(version=1), answers jsonb NOT NULL CHECK(jsonb_typeof(answers)='array' AND jsonb_array_length(answers)=20 AND pg_column_size(answers)<=8192),
 respondent_user_id uuid NOT NULL, updated_at timestamptz NOT NULL DEFAULT now(),
 FOREIGN KEY(node_id,family_id) REFERENCES public.family_tree_nodes(id,family_id) ON DELETE CASCADE
);
GRANT SELECT ON public.family_adolescent_parent_assessments TO authenticated;
GRANT ALL ON public.family_adolescent_parent_assessments TO service_role;
ALTER TABLE public.family_adolescent_parent_assessments ENABLE ROW LEVEL SECURITY;
CREATE OR REPLACE FUNCTION public.can_access_adolescent_assessment(fid uuid,nid uuid) RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path=public AS $$
 SELECT auth.uid() IS NOT NULL AND public.is_family_member(fid) AND EXISTS(
 SELECT 1 FROM public.family_tree_nodes n LEFT JOIN public.users u ON u.id=n.user_id AND u.family_id=n.family_id
 WHERE n.id=nid AND n.family_id=fid AND (public.is_family_holder(fid) OR (u.auth_user_id=auth.uid() AND u.access_mode='assisted'))
 AND COALESCE(u.birth_date,n.birth_date) <= (CURRENT_DATE-INTERVAL '13 years')::date AND COALESCE(u.birth_date,n.birth_date) > (CURRENT_DATE-INTERVAL '18 years')::date)
$$;
CREATE POLICY adolescent_parent_holder_read ON public.family_adolescent_parent_assessments FOR SELECT TO authenticated USING(public.is_family_holder(family_id) AND public.can_access_adolescent_assessment(family_id,node_id));
CREATE FUNCTION public.save_adolescent_parent_assessment(fid uuid,nid uuid,payload jsonb) RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path=public AS $$
DECLARE item jsonb;
BEGIN
 IF auth.uid() IS NULL OR NOT public.is_family_holder(fid) THEN RAISE EXCEPTION 'Somente titulares podem avaliar o adolescente'; END IF;
 PERFORM 1 FROM public.families WHERE id=fid FOR UPDATE;
 PERFORM 1 FROM public.family_tree_nodes WHERE id=nid AND family_id=fid FOR UPDATE;
 PERFORM 1 FROM public.users u JOIN public.family_tree_nodes n ON n.user_id=u.id AND n.family_id=u.family_id WHERE n.id=nid AND n.family_id=fid FOR UPDATE OF u;
 IF NOT public.can_access_adolescent_assessment(fid,nid) THEN RAISE EXCEPTION 'Trilha exclusiva de 13 a 17 anos'; END IF;
 IF payload IS NULL OR jsonb_typeof(payload->'answers') IS DISTINCT FROM 'array' THEN RAISE EXCEPTION 'Responda as 20 questões'; END IF;
 IF jsonb_array_length(payload->'answers')<>20 OR pg_column_size(payload)>8192 THEN RAISE EXCEPTION 'Responda as 20 questões'; END IF;
 FOR item IN SELECT jsonb_array_elements(payload->'answers') LOOP
 IF jsonb_typeof(item) IS DISTINCT FROM 'object' OR item->>'primary' IS NULL OR item->>'secondary' IS NULL OR item->>'primary' NOT IN ('A','B','C','D') OR item->>'secondary' NOT IN ('A','B','C','D') OR item->>'primary'=item->>'secondary' THEN RAISE EXCEPTION 'Escolha duas tendências distintas por questão'; END IF;
 END LOOP;
 INSERT INTO public.family_adolescent_parent_assessments(node_id,family_id,answers,respondent_user_id) VALUES(nid,fid,payload->'answers',auth.uid()) ON CONFLICT(node_id) DO UPDATE SET answers=EXCLUDED.answers,respondent_user_id=EXCLUDED.respondent_user_id,updated_at=now();
END $$;
REVOKE ALL ON FUNCTION public.save_adolescent_parent_assessment(uuid,uuid,jsonb) FROM PUBLIC,anon;
GRANT EXECUTE ON FUNCTION public.save_adolescent_parent_assessment(uuid,uuid,jsonb) TO authenticated;
COMMENT ON TABLE public.family_adolescent_parent_assessments IS 'Holder observation of adolescents, separate from assisted adolescent self-assessment; never exposed in family summaries.';
REVOKE ALL ON public.family_adolescent_parent_assessments FROM PUBLIC,anon,authenticated;
GRANT SELECT ON public.family_adolescent_parent_assessments TO authenticated;
GRANT ALL ON public.family_adolescent_parent_assessments TO service_role;
-- Trilha infantil: observação privada dos titulares (4 a 8 anos).
CREATE TABLE public.family_infant_assessments (
 node_id uuid PRIMARY KEY, family_id uuid NOT NULL REFERENCES public.families(id) ON DELETE CASCADE,
 version integer NOT NULL DEFAULT 1 CHECK(version=1),
 answers jsonb NOT NULL CHECK(jsonb_typeof(answers)='array' AND jsonb_array_length(answers)=20 AND pg_column_size(answers)<=8192),
 respondent_user_id uuid NOT NULL, updated_at timestamptz NOT NULL DEFAULT now(),
 FOREIGN KEY(node_id,family_id) REFERENCES public.family_tree_nodes(id,family_id) ON DELETE CASCADE
);
REVOKE ALL ON public.family_infant_assessments FROM PUBLIC,anon,authenticated;
GRANT SELECT ON public.family_infant_assessments TO authenticated;
GRANT ALL ON public.family_infant_assessments TO service_role;
ALTER TABLE public.family_infant_assessments ENABLE ROW LEVEL SECURITY;
CREATE FUNCTION public.can_access_infant_assessment(fid uuid,nid uuid) RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path=public AS $$
 SELECT auth.uid() IS NOT NULL AND public.is_family_holder(fid) AND EXISTS (
 SELECT 1 FROM public.family_tree_nodes n LEFT JOIN public.users u ON u.id=n.user_id AND u.family_id=n.family_id
 WHERE n.id=nid AND n.family_id=fid
 AND COALESCE(u.birth_date,n.birth_date) <= (CURRENT_DATE-INTERVAL '4 years')::date
 AND COALESCE(u.birth_date,n.birth_date) > (CURRENT_DATE-INTERVAL '9 years')::date)
$$;
REVOKE ALL ON FUNCTION public.can_access_infant_assessment(uuid,uuid) FROM PUBLIC,anon;
GRANT EXECUTE ON FUNCTION public.can_access_infant_assessment(uuid,uuid) TO authenticated,service_role;
CREATE POLICY infant_holder_read ON public.family_infant_assessments FOR SELECT TO authenticated USING(public.can_access_infant_assessment(family_id,node_id));
CREATE FUNCTION public.save_infant_behavioral_assessment(fid uuid,nid uuid,payload jsonb) RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path=public AS $$
DECLARE item jsonb;
BEGIN
 IF auth.uid() IS NULL OR NOT public.is_family_holder(fid) THEN RAISE EXCEPTION 'Somente titulares podem responder pela criança'; END IF;
 PERFORM 1 FROM public.families WHERE id=fid FOR UPDATE;
 PERFORM 1 FROM public.family_tree_nodes WHERE id=nid AND family_id=fid FOR UPDATE;
 PERFORM 1 FROM public.users u JOIN public.family_tree_nodes n ON n.user_id=u.id AND n.family_id=u.family_id WHERE n.id=nid AND n.family_id=fid FOR UPDATE OF u;
 IF NOT public.can_access_infant_assessment(fid,nid) THEN RAISE EXCEPTION 'Trilha exclusiva de 4 a 8 anos: acesso pelo titular'; END IF;
 IF payload IS NULL OR jsonb_typeof(payload->'answers') IS DISTINCT FROM 'array' THEN RAISE EXCEPTION 'Responda as 20 questões'; END IF;
 IF jsonb_array_length(payload->'answers')<>20 OR pg_column_size(payload)>8192 THEN RAISE EXCEPTION 'Responda as 20 questões'; END IF;
 FOR item IN SELECT jsonb_array_elements(payload->'answers') LOOP
 IF jsonb_typeof(item) IS DISTINCT FROM 'object' OR item->>'primary' IS NULL OR item->>'secondary' IS NULL OR item->>'primary' NOT IN ('A','B','C','D') OR item->>'secondary' NOT IN ('A','B','C','D') OR item->>'primary'=item->>'secondary' OR jsonb_typeof(item->'primary') IS DISTINCT FROM 'string' OR jsonb_typeof(item->'secondary') IS DISTINCT FROM 'string' THEN RAISE EXCEPTION 'Escolha duas tendências distintas por questão'; END IF;
 END LOOP;
 INSERT INTO public.family_infant_assessments(node_id,family_id,answers,respondent_user_id) VALUES(nid,fid,payload->'answers',auth.uid()) ON CONFLICT(node_id) DO UPDATE SET answers=EXCLUDED.answers,respondent_user_id=EXCLUDED.respondent_user_id,updated_at=now();
END $$;
REVOKE ALL ON FUNCTION public.save_infant_behavioral_assessment(uuid,uuid,jsonb) FROM PUBLIC,anon;
GRANT EXECUTE ON FUNCTION public.save_infant_behavioral_assessment(uuid,uuid,jsonb) TO authenticated;
COMMENT ON TABLE public.family_infant_assessments IS 'Private 4-8 assessment based on holder observation. Tree-only children supported. No answers or summaries shared with ordinary members.';

-- 0029_event_registration_family_access.sql
CREATE FUNCTION public.lookup_event_registration_email(candidate text) RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path=public AS $$ SELECT nullif(lower(btrim(candidate)), '') IS NOT NULL AND EXISTS (SELECT 1 FROM public.inscricoes i WHERE nullif(lower(btrim(i.email)), '')=lower(btrim(candidate)) OR nullif(lower(btrim(i.email_esposo)), '')=lower(btrim(candidate)) OR nullif(lower(btrim(i.email_esposa)), '')=lower(btrim(candidate))) $$;
REVOKE ALL ON FUNCTION public.lookup_event_registration_email(text) FROM PUBLIC,anon,authenticated;
GRANT EXECUTE ON FUNCTION public.lookup_event_registration_email(text) TO service_role;
CREATE FUNCTION public.lookup_family_access_email(candidate text) RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path=public AS $$ SELECT nullif(lower(btrim(candidate)), '') IS NOT NULL AND (public.lookup_event_registration_email(candidate) OR EXISTS (SELECT 1 FROM public.invitations i WHERE lower(btrim(i.email))=lower(btrim(candidate)) AND i.status='pending' AND i.expires_at>now() AND EXISTS (SELECT 1 FROM public.user_roles r WHERE r.family_id=i.family_id AND r.user_id=i.invited_by AND r.role IN ('primary_holder_1','primary_holder_2'))) OR EXISTS (SELECT 1 FROM auth.users a WHERE lower(btrim(a.email))=lower(btrim(candidate)) AND (EXISTS (SELECT 1 FROM public.users u WHERE u.auth_user_id=a.id) OR EXISTS (SELECT 1 FROM public.platform_admin_roles r WHERE r.user_id=a.id) OR EXISTS (SELECT 1 FROM public.event_organizer_roles r WHERE r.user_id=a.id)))) $$;
REVOKE ALL ON FUNCTION public.lookup_family_access_email(text) FROM PUBLIC,anon,authenticated;
GRANT EXECUTE ON FUNCTION public.lookup_family_access_email(text) TO service_role;
CREATE FUNCTION public.can_start_family_workspace() RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path=public AS $$ SELECT EXISTS (SELECT 1 FROM auth.users a WHERE a.id=auth.uid() AND a.email_confirmed_at IS NOT NULL AND (public.lookup_event_registration_email(a.email) OR public.is_platform_admin() OR public.is_event_organizer())) $$;
REVOKE ALL ON FUNCTION public.can_start_family_workspace() FROM PUBLIC,anon;
GRANT EXECUTE ON FUNCTION public.can_start_family_workspace() TO authenticated,service_role;
CREATE FUNCTION public.enforce_event_signup_access() RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path=public AS $$ BEGIN IF NOT public.lookup_family_access_email(NEW.email) AND coalesce(auth.role(),'') <> 'service_role' THEN RAISE EXCEPTION 'Inscricao no encontro ou convite familiar valido necessario'; END IF; RETURN NEW; END $$;
REVOKE ALL ON FUNCTION public.enforce_event_signup_access() FROM PUBLIC,anon,authenticated;
CREATE TRIGGER event_signup_access BEFORE INSERT ON auth.users FOR EACH ROW EXECUTE FUNCTION public.enforce_event_signup_access();
DO $migration$ DECLARE definition text; BEGIN SELECT pg_get_functiondef('public.manage_family_workspace(jsonb)'::regprocedure) INTO definition; IF position('IF action=''create'' THEN' in definition)=0 THEN RAISE EXCEPTION 'Family creation branch not found'; END IF; definition:=replace(definition, 'IF action=''create'' THEN', 'IF action=''create'' THEN
  IF NOT public.can_start_family_workspace() THEN RAISE EXCEPTION ''Use o e-mail inscrito no encontro para cadastrar sua familia''; END IF;'); EXECUTE definition; END $migration$;
COMMENT ON FUNCTION public.lookup_family_access_email(text) IS 'Private eligibility lookup: event participant, authorized existing member/admin, or valid holder-issued invitation. Never disclose registration rows to public clients.';
COMMENT ON FUNCTION public.can_start_family_workspace() IS 'Confirmed event participant or independently authorized administrator/organizer may self-provision; invitation-only accounts must redeem their fixed-role invitation.';

-- 0030_limit_family_access_email_requests.sql
CREATE TABLE public.family_access_email_limits (bucket_hash text PRIMARY KEY, window_started_at timestamptz NOT NULL DEFAULT now(), attempts integer NOT NULL DEFAULT 0);
GRANT ALL ON public.family_access_email_limits TO service_role;
ALTER TABLE public.family_access_email_limits ENABLE ROW LEVEL SECURITY;
CREATE FUNCTION public.reserve_family_access_email(candidate text) RETURNS boolean LANGUAGE plpgsql SECURITY DEFINER SET search_path=public AS $$ DECLARE bucket text; current_count integer; window_start timestamptz; BEGIN PERFORM pg_advisory_xact_lock(hashtext('family-access-email-limits')); FOREACH bucket IN ARRAY ARRAY['global',encode(sha256(convert_to(lower(btrim(candidate)),'UTF8')),'hex')] LOOP INSERT INTO public.family_access_email_limits(bucket_hash) VALUES(bucket) ON CONFLICT DO NOTHING; SELECT attempts,window_started_at INTO current_count,window_start FROM public.family_access_email_limits WHERE bucket_hash=bucket FOR UPDATE; IF window_start < now()-interval '1 hour' THEN current_count:=0; UPDATE public.family_access_email_limits SET attempts=0,window_started_at=now() WHERE bucket_hash=bucket; END IF; IF current_count >= (CASE WHEN bucket='global' THEN 30 ELSE 3 END) THEN RETURN false; END IF; END LOOP; UPDATE public.family_access_email_limits SET attempts=attempts+1 WHERE bucket_hash IN ('global',encode(sha256(convert_to(lower(btrim(candidate)),'UTF8')),'hex')); RETURN true; END $$;
REVOKE ALL ON FUNCTION public.reserve_family_access_email(text) FROM PUBLIC,anon,authenticated;
GRANT EXECUTE ON FUNCTION public.reserve_family_access_email(text) TO service_role;

-- 0031_restrict_family_email_limit_table.sql
REVOKE ALL ON TABLE public.family_access_email_limits FROM PUBLIC,anon,authenticated;
GRANT ALL ON TABLE public.family_access_email_limits TO service_role;