-- =====================================================================
--  A91 · SCHEMA INICIAL DOS BANCOS
-- ---------------------------------------------------------------------
--  Banco 1 — A91_OPERACIONAL : PostgreSQL 16 + TimescaleDB + pgcrypto
--            cadeia B2B2C, identidade (cofre PII), saúde, consentimento,
--            comunicação paciente <-> profissional, gamificação.
--  Banco 2 — A91_VECTOR      : PostgreSQL 16 + pgvector (INSTÂNCIA SEPARADA)
--            embeddings da IA do nutricionista. Dados NÃO anonimizados.
--
--  Convenções: UUID v4 como PK, timestamptz em UTC, snake_case.
--  Backend: PHP 8   |   Frontend: React
-- =====================================================================


-- #####################################################################
-- #  BANCO 1 — A91_OPERACIONAL
-- #####################################################################
--  Rode em uma database própria:   CREATE DATABASE a91_operacional;
--  \c a91_operacional

CREATE EXTENSION IF NOT EXISTS pgcrypto;     -- gen_random_uuid + pgp_sym_encrypt (cofre)
CREATE EXTENSION IF NOT EXISTS timescaledb;  -- hypertables (séries temporais)

CREATE SCHEMA IF NOT EXISTS app;             -- funções utilitárias (sessão, consentimento)

-- ---------------------------------------------------------------------
--  TIPOS (ENUMs)
-- ---------------------------------------------------------------------
CREATE TYPE empresa_tipo        AS ENUM ('distribuidor','clinica','academia','outro');
CREATE TYPE entidade_tipo       AS ENUM ('a91','empresa','profissional','usuario');
CREATE TYPE equipamento_tipo    AS ENUM ('anel','pulseira','balanca');
CREATE TYPE equipamento_status  AS ENUM ('estoque','alocado','ativo','manutencao','inativo');
CREATE TYPE sexo_tipo           AS ENUM ('M','F','outro','nao_informado');
CREATE TYPE registro_status     AS ENUM ('ativo','inativo','suspenso');
CREATE TYPE escopo_consent      AS ENUM ('sono','cardio','atividade','bioimpedancia','temperatura','comunicacao','tudo');
CREATE TYPE metrica_tipo        AS ENUM ('steps','heart_rate','spo2','temperature','sleep','battery');
CREATE TYPE comunic_direcao     AS ENUM ('prof_para_paciente','paciente_para_prof');
CREATE TYPE comunic_tipo        AS ENUM ('mensagem','recomendacao','alerta');
CREATE TYPE plano_tipo          AS ENUM ('dieta','treino');
CREATE TYPE plano_status        AS ENUM ('rascunho','ativo','concluido','cancelado');

-- ---------------------------------------------------------------------
--  CADEIA B2B2C
-- ---------------------------------------------------------------------
CREATE TABLE empresas (
    id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    razao_social  text          NOT NULL,
    cnpj          varchar(14)   NOT NULL UNIQUE,
    tipo          empresa_tipo  NOT NULL,
    cidade        text,
    uf            char(2),
    status        registro_status NOT NULL DEFAULT 'ativo',
    criado_em     timestamptz   NOT NULL DEFAULT now()
);
COMMENT ON TABLE empresas IS 'FHIR: Organization. O "B" do meio (distribuidor/clínica/academia).';

CREATE TABLE profissionais (
    id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    empresa_id    uuid          NOT NULL REFERENCES empresas(id),
    nome          text          NOT NULL,
    conselho      text,                       -- CRN / CREF
    especialidade text,
    email         citext,                     -- (habilite a extensão citext se quiser unicidade case-insensitive)
    status        registro_status NOT NULL DEFAULT 'ativo',
    criado_em     timestamptz   NOT NULL DEFAULT now()
);
COMMENT ON TABLE profissionais IS 'FHIR: Practitioner / PractitionerRole.';

-- USUÁRIO (pseudonimizado): SEM identificadores diretos aqui.
CREATE TABLE usuarios (
    id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),  -- pseudônimo usado em todo o sistema
    sexo          sexo_tipo     NOT NULL DEFAULT 'nao_informado',
    faixa_etaria  varchar(7),                 -- ex.: '35-39' (generalizado p/ privacidade)
    criado_em     timestamptz   NOT NULL DEFAULT now()
);
COMMENT ON TABLE usuarios IS 'FHIR: Patient (pseudonimizado). PII fica em usuarios_identidade.';

-- COFRE PII — cifrado, isolado, auditado. Reidentificação só com a chave.
CREATE TABLE usuarios_identidade (
    usuario_id      uuid PRIMARY KEY REFERENCES usuarios(id) ON DELETE CASCADE,
    nome_cif        bytea NOT NULL,   -- pgp_sym_encrypt(...)
    cpf_cif         bytea NOT NULL,
    email_cif       bytea,
    data_nasc_cif   bytea,            -- data de nascimento EXATA mora aqui (não em usuarios)
    foto_url        text,
    atualizado_em   timestamptz NOT NULL DEFAULT now()
);
COMMENT ON TABLE usuarios_identidade IS
  'Cofre PII. Chave NÃO fica no banco: use KMS/segredo via current_setting(app.vault_key). Acesso deve ser auditado.';

-- ---------------------------------------------------------------------
--  EQUIPAMENTOS + CADEIA DE CUSTÓDIA
-- ---------------------------------------------------------------------
CREATE TABLE equipamentos (
    id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tipo          equipamento_tipo   NOT NULL,
    modelo        text               NOT NULL,
    numero_serie  text               NOT NULL UNIQUE,
    mac_ble       macaddr,
    firmware      text,
    status        equipamento_status NOT NULL DEFAULT 'estoque',
    criado_em     timestamptz        NOT NULL DEFAULT now()
);
COMMENT ON TABLE equipamentos IS 'FHIR: Device.';

-- Cada transferência do equipamento ao longo da cadeia = 1 linha.
CREATE TABLE alocacoes (
    id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    equipamento_id uuid          NOT NULL REFERENCES equipamentos(id),
    origem_tipo   entidade_tipo  NOT NULL,
    origem_id     uuid,                       -- nulo quando origem = 'a91' (fabricante)
    destino_tipo  entidade_tipo  NOT NULL,
    destino_id    uuid           NOT NULL,
    data_inicio   timestamptz    NOT NULL DEFAULT now(),
    data_fim      timestamptz,
    ativo         boolean        NOT NULL DEFAULT true,
    CHECK (data_fim IS NULL OR data_fim >= data_inicio)
);
COMMENT ON TABLE alocacoes IS 'Cadeia de custódia B2B2C (FHIR: Provenance). origem/destino polimórficos.';
-- Um equipamento só pode ter UMA alocação ativa por vez:
CREATE UNIQUE INDEX uq_alocacao_ativa ON alocacoes (equipamento_id) WHERE ativo;

-- Quais aparelhos o usuário usa hoje (ponte para as camadas de saúde).
CREATE TABLE usuario_dispositivos (
    id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    usuario_id     uuid    NOT NULL REFERENCES usuarios(id),
    equipamento_id uuid    NOT NULL REFERENCES equipamentos(id),
    ativo          boolean NOT NULL DEFAULT true,
    pareado_em     timestamptz NOT NULL DEFAULT now(),
    UNIQUE (usuario_id, equipamento_id)
);

-- ---------------------------------------------------------------------
--  VÍNCULO + CONSENTIMENTO (LGPD)
-- ---------------------------------------------------------------------
CREATE TABLE vinculos (
    id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    usuario_id     uuid NOT NULL REFERENCES usuarios(id),
    profissional_id uuid NOT NULL REFERENCES profissionais(id),
    status         registro_status NOT NULL DEFAULT 'ativo',
    data_inicio    timestamptz NOT NULL DEFAULT now(),
    UNIQUE (usuario_id, profissional_id)
);
COMMENT ON TABLE vinculos IS 'FHIR: CareTeam. Relação paciente <-> profissional.';

CREATE TABLE consentimentos (
    id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    usuario_id     uuid NOT NULL REFERENCES usuarios(id),
    profissional_id uuid NOT NULL REFERENCES profissionais(id),
    escopo         escopo_consent NOT NULL,
    concedido_em   timestamptz NOT NULL DEFAULT now(),
    revogado_em    timestamptz,
    ativo          boolean GENERATED ALWAYS AS (revogado_em IS NULL) STORED
);
COMMENT ON TABLE consentimentos IS 'FHIR: Consent. Aplicado via RLS nas tabelas de saúde.';
CREATE INDEX ix_consent_lookup ON consentimentos (usuario_id, profissional_id, escopo) WHERE revogado_em IS NULL;

-- ---------------------------------------------------------------------
--  COMUNICAÇÃO (duas vias) + PLANOS
-- ---------------------------------------------------------------------
CREATE TABLE comunicacoes (
    id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    usuario_id     uuid NOT NULL REFERENCES usuarios(id),
    profissional_id uuid NOT NULL REFERENCES profissionais(id),
    direcao        comunic_direcao NOT NULL,
    tipo           comunic_tipo    NOT NULL DEFAULT 'mensagem',
    conteudo       text            NOT NULL,
    anexos         jsonb           NOT NULL DEFAULT '[]',
    lida           boolean         NOT NULL DEFAULT false,
    criado_em      timestamptz     NOT NULL DEFAULT now()
);
COMMENT ON TABLE comunicacoes IS 'FHIR: Communication.';
CREATE INDEX ix_comunic_thread ON comunicacoes (usuario_id, profissional_id, criado_em DESC);

CREATE TABLE planos_cuidado (
    id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    usuario_id     uuid NOT NULL REFERENCES usuarios(id),
    profissional_id uuid NOT NULL REFERENCES profissionais(id),
    tipo           plano_tipo  NOT NULL,
    conteudo       jsonb       NOT NULL,        -- refeições, séries, metas...
    inicio         date,
    fim            date,
    status         plano_status NOT NULL DEFAULT 'rascunho',
    criado_em      timestamptz NOT NULL DEFAULT now()
);
COMMENT ON TABLE planos_cuidado IS 'FHIR: CarePlan / NutritionOrder.';

-- ---------------------------------------------------------------------
--  GAMIFICAÇÃO (família / amigos)
-- ---------------------------------------------------------------------
CREATE TABLE grupos (
    id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    nome            text NOT NULL,
    dono_usuario_id uuid NOT NULL REFERENCES usuarios(id),
    criado_em       timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE grupo_membros (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    grupo_id    uuid NOT NULL REFERENCES grupos(id) ON DELETE CASCADE,
    usuario_id  uuid NOT NULL REFERENCES usuarios(id),
    relacao     text,                          -- 'esposa', 'pai'...
    UNIQUE (grupo_id, usuario_id)
);
COMMENT ON TABLE grupos IS 'FHIR: Group.';

-- ---------------------------------------------------------------------
--  CAMADAS DE SAÚDE  (JSONB + TimescaleDB)
-- ---------------------------------------------------------------------
-- Leituras de anel/pulseira/balança (bruto). Nunca apagadas.
CREATE TABLE leituras (
    ts             timestamptz   NOT NULL,
    usuario_id     uuid          NOT NULL REFERENCES usuarios(id),
    equipamento_id uuid          NOT NULL REFERENCES equipamentos(id),
    tipo_disp      equipamento_tipo NOT NULL,
    metrica        metrica_tipo  NOT NULL,
    valor          numeric,
    unidade        text,
    payload        jsonb,                       -- bruto adicional do aparelho
    PRIMARY KEY (usuario_id, equipamento_id, metrica, ts)
);
SELECT create_hypertable('leituras', 'ts', chunk_time_interval => INTERVAL '7 days');
CREATE INDEX ix_leituras_metrica ON leituras (usuario_id, metrica, ts DESC);
COMMENT ON TABLE leituras IS 'FHIR: Observation. Hypertable particionada por tempo.';

-- Bioimpedância: 1 documento por pesagem (a balança manda muitos campos).
CREATE TABLE bioimpedancia (
    id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    usuario_id     uuid NOT NULL REFERENCES usuarios(id),
    equipamento_id uuid NOT NULL REFERENCES equipamentos(id),
    ts             timestamptz NOT NULL DEFAULT now(),
    dados          jsonb NOT NULL
        -- { peso, imc, gordura_pct, musculo_kg, agua_pct, proteina_pct,
        --   gordura_visceral, massa_ossea, metabolismo_basal, idade_metabolica }
);
CREATE INDEX ix_bio_usuario ON bioimpedancia (usuario_id, ts DESC);
COMMENT ON TABLE bioimpedancia IS 'FHIR: Observation (painel com componentes).';

-- Resumo diário: o que o dashboard lê. 1 valor por métrica (regra de negócio).
CREATE TABLE resumo_diario (
    usuario_id        uuid NOT NULL REFERENCES usuarios(id),
    data              date NOT NULL,
    metricas          jsonb NOT NULL,           -- { passos:{valor,fonte}, heart_rate:{...}, ... }
    idade_fisiologica int,
    atualizado_em     timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (usuario_id, data)
);

-- ---------------------------------------------------------------------
--  FUNÇÕES — sessão e consentimento
-- ---------------------------------------------------------------------
-- A aplicação (PHP) define por request:  SET app.current_usuario_id / app.current_professional_id
CREATE OR REPLACE FUNCTION app.usuario_atual() RETURNS uuid
LANGUAGE sql STABLE AS $$ SELECT NULLIF(current_setting('app.current_usuario_id', true),'')::uuid $$;

CREATE OR REPLACE FUNCTION app.prof_atual() RETURNS uuid
LANGUAGE sql STABLE AS $$ SELECT NULLIF(current_setting('app.current_professional_id', true),'')::uuid $$;

-- Mapeia métrica -> escopo de consentimento
CREATE OR REPLACE FUNCTION app.escopo_da_metrica(m metrica_tipo) RETURNS escopo_consent
LANGUAGE sql IMMUTABLE AS $$
  SELECT CASE m
    WHEN 'heart_rate'  THEN 'cardio'
    WHEN 'spo2'        THEN 'cardio'
    WHEN 'steps'       THEN 'atividade'
    WHEN 'sleep'       THEN 'sono'
    WHEN 'temperature' THEN 'temperatura'
    ELSE 'atividade'
  END::escopo_consent
$$;

-- Profissional atual tem consentimento ativo (vínculo + escopo) para este usuário?
CREATE OR REPLACE FUNCTION app.prof_pode_ver(p_usuario uuid, p_escopo escopo_consent)
RETURNS boolean LANGUAGE sql STABLE AS $$
  SELECT EXISTS (
    SELECT 1
    FROM vinculos v
    JOIN consentimentos c
      ON c.usuario_id = v.usuario_id
     AND c.profissional_id = v.profissional_id
    WHERE v.profissional_id = app.prof_atual()
      AND v.usuario_id      = p_usuario
      AND v.status = 'ativo'
      AND c.revogado_em IS NULL
      AND c.escopo IN (p_escopo, 'tudo')
  )
$$;

-- ---------------------------------------------------------------------
--  ROW-LEVEL SECURITY  (o paciente vê o seu; o profissional só com consentimento)
-- ---------------------------------------------------------------------
ALTER TABLE leituras       ENABLE ROW LEVEL SECURITY;
ALTER TABLE bioimpedancia  ENABLE ROW LEVEL SECURITY;
ALTER TABLE resumo_diario  ENABLE ROW LEVEL SECURITY;
ALTER TABLE comunicacoes   ENABLE ROW LEVEL SECURITY;
ALTER TABLE planos_cuidado ENABLE ROW LEVEL SECURITY;

-- LEITURAS
CREATE POLICY leituras_self ON leituras FOR SELECT
  USING (usuario_id = app.usuario_atual());
CREATE POLICY leituras_prof ON leituras FOR SELECT
  USING (app.prof_pode_ver(usuario_id, app.escopo_da_metrica(metrica)));

-- BIOIMPEDÂNCIA
CREATE POLICY bio_self ON bioimpedancia FOR SELECT
  USING (usuario_id = app.usuario_atual());
CREATE POLICY bio_prof ON bioimpedancia FOR SELECT
  USING (app.prof_pode_ver(usuario_id, 'bioimpedancia'));

-- RESUMO DIÁRIO
CREATE POLICY resumo_self ON resumo_diario FOR SELECT
  USING (usuario_id = app.usuario_atual());
CREATE POLICY resumo_prof ON resumo_diario FOR SELECT
  USING (app.prof_pode_ver(usuario_id, 'tudo'));

-- COMUNICAÇÕES (paciente e profissional do vínculo)
CREATE POLICY comunic_self ON comunicacoes FOR ALL
  USING (usuario_id = app.usuario_atual()
      OR profissional_id = app.prof_atual());

-- PLANOS
CREATE POLICY plano_self ON planos_cuidado FOR ALL
  USING (usuario_id = app.usuario_atual()
      OR profissional_id = app.prof_atual());

-- ---------------------------------------------------------------------
--  COFRE — exemplos de uso (a chave vem de KMS, nunca hard-coded)
-- ---------------------------------------------------------------------
-- Gravar:
--   INSERT INTO usuarios_identidade (usuario_id, nome_cif, cpf_cif)
--   VALUES ($1,
--           pgp_sym_encrypt('Eduardo Silva', current_setting('app.vault_key')),
--           pgp_sym_encrypt('12345678900',   current_setting('app.vault_key')));
-- Ler (só processos autorizados, com auditoria):
--   SELECT pgp_sym_decrypt(nome_cif, current_setting('app.vault_key')) FROM usuarios_identidade WHERE usuario_id = $1;


-- #####################################################################
-- #  BANCO 2 — A91_VECTOR  (INSTÂNCIA SEPARADA)
-- #####################################################################
--  Roda em outra instância/credencial. NÃO compartilha conexão com o
--  operacional. Aqui os dados NÃO são anonimizados (o embedding pode
--  reconstruir conteúdo identificável) -> acesso, consentimento e
--  auditoria PRÓPRIOS, mesmo nível de proteção do banco operacional.
--
--  CREATE DATABASE a91_vector;  \c a91_vector

CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE saude_embeddings (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    usuario_id  uuid NOT NULL,                 -- pseudônimo (FK lógica ao operacional)
    origem      text NOT NULL,                 -- 'refeicao', 'historico', 'conversa'...
    conteudo    text NOT NULL,                 -- texto-fonte do embedding
    embedding   vector(1536) NOT NULL,         -- dimensão conforme o modelo usado
    metadata    jsonb NOT NULL DEFAULT '{}',
    criado_em   timestamptz NOT NULL DEFAULT now()
);

-- Índice de similaridade (cosseno). HNSW = boa recuperação + rápido.
CREATE INDEX ix_emb_hnsw ON saude_embeddings
    USING hnsw (embedding vector_cosine_ops);
CREATE INDEX ix_emb_usuario ON saude_embeddings (usuario_id);

-- Busca dos K mais parecidos para um usuário:
--   SELECT id, origem, conteudo, 1 - (embedding <=> $1) AS score
--   FROM saude_embeddings
--   WHERE usuario_id = $2
--   ORDER BY embedding <=> $1
--   LIMIT 5;

-- =====================================================================
--  FIM
-- =====================================================================