-- =====================================================
-- Migration: Módulo OTP genérico (Mensageria/Otp)
-- Data: 2026-04-29
-- =====================================================
--
-- Tabelas:
--   verificacoes_otp        — uma linha por OTP gerado
--   verificacoes_otp_eventos — auditoria append-only
--
-- Block columns são obrigatórias (padrão Galaxia). O framework popula
-- esses campos automaticamente — nada de DEFAULT 1 nem inserção manual
-- de block nas queries dos Laboratórios.
-- =====================================================

CREATE TABLE verificacoes_otp (
    id SERIAL PRIMARY KEY,

    -- Identificação do fluxo do consumidor
    contexto           VARCHAR(50)  NOT NULL,
    referencia_externa VARCHAR(255) NOT NULL,

    -- Envio
    canal        VARCHAR(20)  NOT NULL,
    destinatario VARCHAR(255) NOT NULL,

    -- Código (nunca em claro)
    codigo_hash    VARCHAR(255) NOT NULL,
    tamanho_codigo INTEGER      NOT NULL,

    -- Validade / política (snapshot no momento da criação)
    expira_em         TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    validade_segundos INTEGER NOT NULL,
    max_tentativas    INTEGER NOT NULL,
    tentativas        INTEGER NOT NULL DEFAULT 0,

    -- Estado
    status VARCHAR(30) NOT NULL DEFAULT 'aguardando_envio',
        -- aguardando_envio | enviado | validado
        -- | invalidado_tentativas | expirado | cancelado

    -- Carga livre do consumidor
    dados_adicionais JSONB,

    -- Timestamps de transição
    validado_em         TIMESTAMP WITHOUT TIME ZONE,
    cancelado_em        TIMESTAMP WITHOUT TIME ZONE,
    motivo_cancelamento TEXT,

    -- Origem da chamada (auditoria)
    ip_origem  VARCHAR(45),
    user_agent VARCHAR(500),

    -- Block padrão Galaxia (preenchido pelo framework)
    data_cadastro       TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    block_filial_id     INTEGER,
    block_usuario_id    INTEGER,
    usuario_cadastro    VARCHAR(255),
    data_atualizacao    TIMESTAMP WITHOUT TIME ZONE,
    usuario_atualizacao VARCHAR(255),
    block_data          TIMESTAMP WITHOUT TIME ZONE,
    block_quem          VARCHAR(255),
    block               INTEGER,
    block_empresa_id    INTEGER
);

CREATE TABLE verificacoes_otp_eventos (
    id SERIAL PRIMARY KEY,

    verificacao_otp_id INTEGER NOT NULL REFERENCES verificacoes_otp(id),

    tipo_evento VARCHAR(30) NOT NULL,
        -- criado | enviado | tentativa_errada | validado
        -- | expirado | cancelado | falha_envio | falha_evento

    detalhes JSONB,

    ip_origem  VARCHAR(45),
    user_agent VARCHAR(500),

    -- Block padrão Galaxia (preenchido pelo framework)
    data_cadastro       TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    block_filial_id     INTEGER,
    block_usuario_id    INTEGER,
    usuario_cadastro    VARCHAR(255),
    data_atualizacao    TIMESTAMP WITHOUT TIME ZONE,
    usuario_atualizacao VARCHAR(255),
    block_data          TIMESTAMP WITHOUT TIME ZONE,
    block_quem          VARCHAR(255),
    block               INTEGER,
    block_empresa_id    INTEGER
);

-- Índices
CREATE INDEX idx_verificacoes_otp_contexto_referencia
    ON verificacoes_otp (contexto, referencia_externa);
CREATE INDEX idx_verificacoes_otp_status
    ON verificacoes_otp (status);
CREATE INDEX idx_verificacoes_otp_expira_em
    ON verificacoes_otp (expira_em);

CREATE INDEX idx_verificacoes_otp_eventos_otp
    ON verificacoes_otp_eventos (verificacao_otp_id);
CREATE INDEX idx_verificacoes_otp_eventos_tipo
    ON verificacoes_otp_eventos (tipo_evento);
