-- ==========================================================================
-- Agencia PSI — Migracao: tabelas de Documentos & Arquivos
-- ==========================================================================
-- Criado por: Leonardo Nohama
-- Data: 2026-03-29 · Sao Carlos/SP — Brasil
--
-- Proposito:
--   Modulo completo de documentos do paciente.
--   Tabelas: documents, document_access_logs, document_signatures,
--            document_share_links.
--
-- Relacionamentos:
--   documents.patient_id       → patients(id)
--   documents.owner_id         → auth.users(id)
--   documents.tenant_id        → tenants(id)
--   documents.agenda_evento_id → agenda_eventos(id) (opcional)
--   document_access_logs.documento_id  → documents(id)
--   document_signatures.documento_id   → documents(id)
--   document_share_links.documento_id  → documents(id)
--
-- RLS: owner_id = auth.uid() para documents, signatures e share_links.
--      access_logs: somente INSERT (imutavel) + SELECT por tenant.
-- ==========================================================================


-- --------------------------------------------------------------------------
-- 1. Tabela principal: documents
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.documents (
    id                      uuid DEFAULT gen_random_uuid() NOT NULL,

    -- Contexto de acesso
    owner_id                uuid NOT NULL,
    tenant_id               uuid NOT NULL,

    -- Vinculo com paciente
    patient_id              uuid NOT NULL REFERENCES public.patients(id) ON DELETE CASCADE,

    -- Arquivo no Storage
    bucket_path             text NOT NULL,
    storage_bucket          text NOT NULL DEFAULT 'documents',
    nome_original           text NOT NULL,
    mime_type               text,
    tamanho_bytes           bigint,

    -- Classificacao
    tipo_documento          text NOT NULL DEFAULT 'outro',
        -- laudo | receita | exame | termo_assinado | relatorio_externo
        -- identidade | convenio | declaracao | atestado | recibo | outro
    categoria               text,
    descricao               text,
    tags                    text[] DEFAULT '{}',

    -- Vinculo opcional com sessao/nota
    agenda_evento_id        uuid REFERENCES public.agenda_eventos(id) ON DELETE SET NULL,
    session_note_id         uuid,

    -- Visibilidade & controle de acesso
    visibilidade            text NOT NULL DEFAULT 'privado',
        -- privado | compartilhado_supervisor | compartilhado_portal
    compartilhado_portal    boolean DEFAULT false NOT NULL,
    compartilhado_supervisor boolean DEFAULT false NOT NULL,
    compartilhado_em        timestamptz,
    expira_compartilhamento timestamptz,

    -- Upload pelo paciente (portal)
    enviado_pelo_paciente   boolean DEFAULT false NOT NULL,
    status_revisao          text DEFAULT 'aprovado',
        -- pendente | aprovado | rejeitado
    revisado_por            uuid,
    revisado_em             timestamptz,

    -- Quem fez upload
    uploaded_by             uuid NOT NULL,
    uploaded_at             timestamptz DEFAULT now() NOT NULL,

    -- Soft delete com retencao (LGPD / CFP)
    deleted_at              timestamptz,
    deleted_by              uuid,
    retencao_ate            timestamptz,

    -- Controle
    created_at              timestamptz DEFAULT now(),
    updated_at              timestamptz DEFAULT now(),

    CONSTRAINT documents_pkey PRIMARY KEY (id),

    -- Validacoes
    CONSTRAINT documents_tipo_check CHECK (
        tipo_documento = ANY (ARRAY[
            'laudo', 'receita', 'exame', 'termo_assinado', 'relatorio_externo',
            'identidade', 'convenio', 'declaracao', 'atestado', 'recibo', 'outro'
        ])
    ),
    CONSTRAINT documents_visibilidade_check CHECK (
        visibilidade = ANY (ARRAY['privado', 'compartilhado_supervisor', 'compartilhado_portal'])
    ),
    CONSTRAINT documents_status_revisao_check CHECK (
        status_revisao = ANY (ARRAY['pendente', 'aprovado', 'rejeitado'])
    )
);


-- --------------------------------------------------------------------------
-- 2. Indices — documents
-- --------------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS docs_patient_idx
    ON public.documents USING btree (patient_id);

CREATE INDEX IF NOT EXISTS docs_owner_idx
    ON public.documents USING btree (owner_id);

CREATE INDEX IF NOT EXISTS docs_tenant_idx
    ON public.documents USING btree (tenant_id);

CREATE INDEX IF NOT EXISTS docs_tipo_idx
    ON public.documents USING btree (patient_id, tipo_documento);

CREATE INDEX IF NOT EXISTS docs_tags_idx
    ON public.documents USING gin (tags);

CREATE INDEX IF NOT EXISTS docs_uploaded_at_idx
    ON public.documents USING btree (patient_id, uploaded_at DESC);

-- Excluir soft-deleted da listagem padrao
CREATE INDEX IF NOT EXISTS docs_active_idx
    ON public.documents USING btree (patient_id, uploaded_at DESC)
    WHERE deleted_at IS NULL;

-- Busca textual no nome do arquivo
CREATE INDEX IF NOT EXISTS docs_nome_trgm_idx
    ON public.documents USING gin (nome_original gin_trgm_ops);


-- --------------------------------------------------------------------------
-- 3. Trigger updated_at — documents
-- --------------------------------------------------------------------------
CREATE TRIGGER trg_documents_updated_at
    BEFORE UPDATE ON public.documents
    FOR EACH ROW
    EXECUTE FUNCTION public.set_updated_at();


-- --------------------------------------------------------------------------
-- 4. Trigger: registrar na patient_timeline ao adicionar documento
-- --------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.fn_documents_timeline_insert()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
    INSERT INTO public.patient_timeline (
        patient_id, tenant_id, evento_tipo,
        titulo, descricao, icone_cor,
        link_ref_tipo, link_ref_id,
        gerado_por, ocorrido_em
    ) VALUES (
        NEW.patient_id,
        NEW.tenant_id,
        'documento_adicionado',
        'Documento adicionado: ' || COALESCE(NEW.nome_original, 'arquivo'),
        'Tipo: ' || COALESCE(NEW.tipo_documento, 'outro'),
        'blue',
        'documento',
        NEW.id,
        NEW.uploaded_by,
        NEW.uploaded_at
    );
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_documents_timeline_insert
    AFTER INSERT ON public.documents
    FOR EACH ROW
    EXECUTE FUNCTION public.fn_documents_timeline_insert();


-- --------------------------------------------------------------------------
-- 5. RLS — documents
-- --------------------------------------------------------------------------
ALTER TABLE public.documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY "documents: owner full access"
    ON public.documents
    USING  (owner_id = auth.uid())
    WITH CHECK (owner_id = auth.uid());


-- --------------------------------------------------------------------------
-- 6. Comentarios — documents
-- --------------------------------------------------------------------------
COMMENT ON TABLE  public.documents                              IS 'Documentos e arquivos vinculados a pacientes. Armazenados no Supabase Storage.';
COMMENT ON COLUMN public.documents.owner_id                     IS 'Terapeuta dono do documento (auth.uid()).';
COMMENT ON COLUMN public.documents.tenant_id                    IS 'Tenant do terapeuta.';
COMMENT ON COLUMN public.documents.patient_id                   IS 'Paciente ao qual o documento pertence.';
COMMENT ON COLUMN public.documents.bucket_path                  IS 'Caminho do arquivo no Supabase Storage bucket.';
COMMENT ON COLUMN public.documents.storage_bucket               IS 'Nome do bucket no Storage. Default: documents.';
COMMENT ON COLUMN public.documents.nome_original                IS 'Nome original do arquivo enviado.';
COMMENT ON COLUMN public.documents.mime_type                    IS 'MIME type do arquivo. Ex: application/pdf, image/jpeg.';
COMMENT ON COLUMN public.documents.tamanho_bytes                IS 'Tamanho do arquivo em bytes.';
COMMENT ON COLUMN public.documents.tipo_documento               IS 'Tipo: laudo|receita|exame|termo_assinado|relatorio_externo|identidade|convenio|declaracao|atestado|recibo|outro.';
COMMENT ON COLUMN public.documents.categoria                    IS 'Categoria livre para organizacao adicional.';
COMMENT ON COLUMN public.documents.tags                         IS 'Tags livres para busca e filtro. Array de text.';
COMMENT ON COLUMN public.documents.visibilidade                 IS 'privado|compartilhado_supervisor|compartilhado_portal.';
COMMENT ON COLUMN public.documents.compartilhado_portal         IS 'true = visivel para o paciente no portal.';
COMMENT ON COLUMN public.documents.compartilhado_supervisor     IS 'true = visivel para o supervisor.';
COMMENT ON COLUMN public.documents.enviado_pelo_paciente        IS 'true = upload feito pelo paciente via portal.';
COMMENT ON COLUMN public.documents.status_revisao               IS 'pendente|aprovado|rejeitado — para uploads do paciente.';
COMMENT ON COLUMN public.documents.deleted_at                   IS 'Soft delete: data da exclusao. NULL = ativo.';
COMMENT ON COLUMN public.documents.retencao_ate                 IS 'LGPD/CFP: arquivo retido ate esta data mesmo apos soft delete.';


-- ==========================================================================
-- 7. Tabela: document_access_logs (imutavel — auditoria)
-- ==========================================================================
CREATE TABLE IF NOT EXISTS public.document_access_logs (
    id              uuid DEFAULT gen_random_uuid() NOT NULL,

    documento_id    uuid NOT NULL REFERENCES public.documents(id) ON DELETE CASCADE,
    tenant_id       uuid NOT NULL,

    -- Acao realizada
    acao            text NOT NULL,
        -- visualizou | baixou | imprimiu | compartilhou | assinou
    user_id         uuid,
    ip              inet,
    user_agent      text,

    acessado_em     timestamptz DEFAULT now() NOT NULL,

    CONSTRAINT document_access_logs_pkey PRIMARY KEY (id),

    CONSTRAINT dal_acao_check CHECK (
        acao = ANY (ARRAY['visualizou', 'baixou', 'imprimiu', 'compartilhou', 'assinou'])
    )
);

-- Indices
CREATE INDEX IF NOT EXISTS dal_documento_idx
    ON public.document_access_logs USING btree (documento_id, acessado_em DESC);

CREATE INDEX IF NOT EXISTS dal_tenant_idx
    ON public.document_access_logs USING btree (tenant_id, acessado_em DESC);

CREATE INDEX IF NOT EXISTS dal_user_idx
    ON public.document_access_logs USING btree (user_id, acessado_em DESC);

-- RLS — somente INSERT (imutavel) + SELECT
ALTER TABLE public.document_access_logs ENABLE ROW LEVEL SECURITY;

CREATE POLICY "dal: tenant members can insert"
    ON public.document_access_logs
    FOR INSERT
    WITH CHECK (true);

CREATE POLICY "dal: tenant members can select"
    ON public.document_access_logs
    FOR SELECT
    USING (tenant_id IN (
        SELECT tm.tenant_id FROM public.tenant_members tm
        WHERE tm.user_id = auth.uid() AND tm.status = 'active'
    ));

-- Comentarios
COMMENT ON TABLE  public.document_access_logs           IS 'Log imutavel de acessos a documentos. Conformidade CFP e LGPD. Sem UPDATE/DELETE.';
COMMENT ON COLUMN public.document_access_logs.acao      IS 'visualizou|baixou|imprimiu|compartilhou|assinou.';


-- ==========================================================================
-- 8. Tabela: document_signatures (assinatura eletronica)
-- ==========================================================================
CREATE TABLE IF NOT EXISTS public.document_signatures (
    id                  uuid DEFAULT gen_random_uuid() NOT NULL,

    documento_id        uuid NOT NULL REFERENCES public.documents(id) ON DELETE CASCADE,
    tenant_id           uuid NOT NULL,

    -- Signatario
    signatario_tipo     text NOT NULL,
        -- paciente | responsavel_legal | terapeuta
    signatario_id       uuid,
    signatario_nome     text,
    signatario_email    text,

    -- Ordem e status
    ordem               smallint DEFAULT 1 NOT NULL,
    status              text NOT NULL DEFAULT 'pendente',
        -- pendente | enviado | assinado | recusado | expirado

    -- Dados da assinatura (preenchidos ao assinar)
    ip                  inet,
    user_agent          text,
    assinado_em         timestamptz,
    hash_documento      text,

    -- Controle
    criado_em           timestamptz DEFAULT now(),
    atualizado_em       timestamptz DEFAULT now(),

    CONSTRAINT document_signatures_pkey PRIMARY KEY (id),

    CONSTRAINT ds_signatario_tipo_check CHECK (
        signatario_tipo = ANY (ARRAY['paciente', 'responsavel_legal', 'terapeuta'])
    ),
    CONSTRAINT ds_status_check CHECK (
        status = ANY (ARRAY['pendente', 'enviado', 'assinado', 'recusado', 'expirado'])
    )
);

-- Indices
CREATE INDEX IF NOT EXISTS ds_documento_idx
    ON public.document_signatures USING btree (documento_id, ordem);

CREATE INDEX IF NOT EXISTS ds_tenant_idx
    ON public.document_signatures USING btree (tenant_id);

CREATE INDEX IF NOT EXISTS ds_status_idx
    ON public.document_signatures USING btree (documento_id, status);

-- Trigger updated_at
CREATE TRIGGER trg_ds_updated_at
    BEFORE UPDATE ON public.document_signatures
    FOR EACH ROW
    EXECUTE FUNCTION public.set_updated_at();

-- Trigger: ao assinar, registrar na patient_timeline
CREATE OR REPLACE FUNCTION public.fn_document_signature_timeline()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
    v_patient_id uuid;
    v_tenant_id  uuid;
    v_doc_nome   text;
BEGIN
    IF NEW.status = 'assinado' AND (OLD.status IS NULL OR OLD.status <> 'assinado') THEN
        SELECT d.patient_id, d.tenant_id, d.nome_original
        INTO v_patient_id, v_tenant_id, v_doc_nome
        FROM public.documents d
        WHERE d.id = NEW.documento_id;

        IF v_patient_id IS NOT NULL THEN
            INSERT INTO public.patient_timeline (
                patient_id, tenant_id, evento_tipo,
                titulo, descricao, icone_cor,
                link_ref_tipo, link_ref_id,
                gerado_por, ocorrido_em
            ) VALUES (
                v_patient_id,
                v_tenant_id,
                'documento_assinado',
                'Documento assinado: ' || COALESCE(v_doc_nome, 'documento'),
                'Assinado por ' || COALESCE(NEW.signatario_nome, NEW.signatario_tipo),
                'green',
                'documento',
                NEW.documento_id,
                NEW.signatario_id,
                NEW.assinado_em
            );
        END IF;
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_ds_timeline
    AFTER UPDATE ON public.document_signatures
    FOR EACH ROW
    EXECUTE FUNCTION public.fn_document_signature_timeline();

-- RLS
ALTER TABLE public.document_signatures ENABLE ROW LEVEL SECURITY;

CREATE POLICY "ds: tenant members access"
    ON public.document_signatures
    USING (tenant_id IN (
        SELECT tm.tenant_id FROM public.tenant_members tm
        WHERE tm.user_id = auth.uid() AND tm.status = 'active'
    ))
    WITH CHECK (tenant_id IN (
        SELECT tm.tenant_id FROM public.tenant_members tm
        WHERE tm.user_id = auth.uid() AND tm.status = 'active'
    ));

-- Comentarios
COMMENT ON TABLE  public.document_signatures                    IS 'Assinaturas eletronicas de documentos. Cada signatario tem seu registro.';
COMMENT ON COLUMN public.document_signatures.signatario_tipo    IS 'paciente|responsavel_legal|terapeuta.';
COMMENT ON COLUMN public.document_signatures.status             IS 'pendente|enviado|assinado|recusado|expirado.';
COMMENT ON COLUMN public.document_signatures.hash_documento     IS 'Hash SHA-256 do documento no momento da assinatura. Garante integridade.';
COMMENT ON COLUMN public.document_signatures.ip                 IS 'IP do signatario no momento da assinatura.';


-- ==========================================================================
-- 9. Tabela: document_share_links (links temporarios)
-- ==========================================================================
CREATE TABLE IF NOT EXISTS public.document_share_links (
    id              uuid DEFAULT gen_random_uuid() NOT NULL,

    documento_id    uuid NOT NULL REFERENCES public.documents(id) ON DELETE CASCADE,
    tenant_id       uuid NOT NULL,

    -- Token unico para o link
    token           text NOT NULL DEFAULT encode(gen_random_bytes(32), 'hex'),

    -- Limites
    expira_em       timestamptz NOT NULL,
    usos_max        smallint DEFAULT 5 NOT NULL,
    usos            smallint DEFAULT 0 NOT NULL,

    -- Quem criou
    criado_por      uuid NOT NULL,
    criado_em       timestamptz DEFAULT now(),

    -- Controle
    ativo           boolean DEFAULT true NOT NULL,

    CONSTRAINT document_share_links_pkey PRIMARY KEY (id),
    CONSTRAINT dsl_token_unique UNIQUE (token)
);

-- Indices
CREATE INDEX IF NOT EXISTS dsl_documento_idx
    ON public.document_share_links USING btree (documento_id);

CREATE INDEX IF NOT EXISTS dsl_token_idx
    ON public.document_share_links USING btree (token)
    WHERE ativo = true;

CREATE INDEX IF NOT EXISTS dsl_expira_idx
    ON public.document_share_links USING btree (expira_em)
    WHERE ativo = true;

-- RLS
ALTER TABLE public.document_share_links ENABLE ROW LEVEL SECURITY;

CREATE POLICY "dsl: creator full access"
    ON public.document_share_links
    USING  (criado_por = auth.uid())
    WITH CHECK (criado_por = auth.uid());

-- Politica publica de leitura por token (para acesso externo sem login)
CREATE POLICY "dsl: public read by token"
    ON public.document_share_links
    FOR SELECT
    USING (ativo = true AND expira_em > now() AND usos < usos_max);

-- Comentarios
COMMENT ON TABLE  public.document_share_links           IS 'Links temporarios assinados para compartilhar documento com profissional externo.';
COMMENT ON COLUMN public.document_share_links.token     IS 'Token unico gerado automaticamente (32 bytes hex).';
COMMENT ON COLUMN public.document_share_links.expira_em IS 'Data/hora de expiracao do link.';
COMMENT ON COLUMN public.document_share_links.usos_max  IS 'Numero maximo de acessos permitidos.';
COMMENT ON COLUMN public.document_share_links.usos      IS 'Numero de vezes que o link ja foi acessado.';


-- ==========================================================================
-- FIM DA MIGRACAO 005
-- ==========================================================================