-- +goose Up
-- +goose StatementBegin

-- ─────────────────────────────────────────────────────────────────────────────
-- AUDIT LOGS
-- Captures every mutating operation with before/after JSONB snapshots.
-- tenant_id is intentionally NOT a FK so deletions don't cascade audit history.
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE audit_logs (
    id          UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id   UUID        NOT NULL,   -- no FK: preserve history after tenant delete
    user_id     UUID,                   -- nullable: system operations have no user
    acao        TEXT        NOT NULL,   -- POST | PUT | PATCH | DELETE | LOGIN | ...
    tabela      TEXT        NOT NULL,   -- table / resource affected
    entidade_id TEXT,                   -- PK of the affected row (string for flexibility)
    antes       JSONB,                  -- snapshot before the mutation
    depois      JSONB,                  -- snapshot after the mutation
    ip          INET,                   -- caller IP address
    user_agent  TEXT,
    request_id  TEXT,                   -- X-Request-ID for correlation
    criado_em   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Composite index for the most common query patterns
CREATE INDEX idx_audit_tenant_criado    ON audit_logs (tenant_id, criado_em DESC);
CREATE INDEX idx_audit_tenant_tabela    ON audit_logs (tenant_id, tabela, criado_em DESC);
CREATE INDEX idx_audit_entidade         ON audit_logs (tenant_id, tabela, entidade_id);
CREATE INDEX idx_audit_user             ON audit_logs (tenant_id, user_id, criado_em DESC);

-- GIN index for JSONB search (search inside before/after snapshots)
CREATE INDEX idx_audit_antes_gin  ON audit_logs USING GIN (antes);
CREATE INDEX idx_audit_depois_gin ON audit_logs USING GIN (depois);

-- ─────────────────────────────────────────────────────────────────────────────
-- RLS
-- ─────────────────────────────────────────────────────────────────────────────
ALTER TABLE audit_logs ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON audit_logs
    USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

-- INSERT bypass: the audit middleware runs as the app role, not tenant role,
-- so we allow insert from the service role without RLS filtering.
CREATE POLICY audit_insert ON audit_logs
    AS PERMISSIVE FOR INSERT
    WITH CHECK (true);

-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
DROP TABLE IF EXISTS audit_logs CASCADE;
-- +goose StatementEnd