-- -----------------------------------------------------------------------------
-- 005_timestamp_evidence.sql
-- Sprint: SPRINT_20260119_009 Evidence Storage for Timestamps
-- Task: EVT-002 - PostgreSQL Schema Extension
-- Description: Schema for storing timestamp and revocation evidence.
-- -----------------------------------------------------------------------------

-- Ensure the evidence schema exists
CREATE SCHEMA IF NOT EXISTS evidence;

-- Timestamp evidence storage
CREATE TABLE IF NOT EXISTS evidence.timestamp_tokens (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    artifact_digest TEXT NOT NULL,
    digest_algorithm TEXT NOT NULL,
    tst_blob BYTEA NOT NULL,
    generation_time TIMESTAMPTZ NOT NULL,
    tsa_name TEXT NOT NULL,
    tsa_policy_oid TEXT NOT NULL,
    serial_number TEXT NOT NULL,
    tsa_chain_pem TEXT NOT NULL,
    ocsp_response BYTEA,
    crl_snapshot BYTEA,
    captured_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    provider_name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_timestamp_artifact_time UNIQUE (artifact_digest, generation_time)
);

-- Indexes for timestamp queries
CREATE INDEX IF NOT EXISTS idx_timestamp_artifact ON evidence.timestamp_tokens(artifact_digest);
CREATE INDEX IF NOT EXISTS idx_timestamp_generation ON evidence.timestamp_tokens(generation_time);
CREATE INDEX IF NOT EXISTS idx_timestamp_provider ON evidence.timestamp_tokens(provider_name);
CREATE INDEX IF NOT EXISTS idx_timestamp_created ON evidence.timestamp_tokens(created_at);

-- Revocation evidence storage
CREATE TABLE IF NOT EXISTS evidence.revocation_snapshots (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    certificate_fingerprint TEXT NOT NULL,
    source TEXT NOT NULL CHECK (source IN ('Ocsp', 'Crl', 'None')),
    raw_response BYTEA NOT NULL,
    response_time TIMESTAMPTZ NOT NULL,
    valid_until TIMESTAMPTZ NOT NULL,
    status TEXT NOT NULL CHECK (status IN ('Good', 'Revoked', 'Unknown')),
    revocation_time TIMESTAMPTZ,
    reason TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Indexes for revocation queries
CREATE INDEX IF NOT EXISTS idx_revocation_cert ON evidence.revocation_snapshots(certificate_fingerprint);
CREATE INDEX IF NOT EXISTS idx_revocation_valid ON evidence.revocation_snapshots(valid_until);
CREATE INDEX IF NOT EXISTS idx_revocation_status ON evidence.revocation_snapshots(status);
CREATE INDEX IF NOT EXISTS idx_revocation_created ON evidence.revocation_snapshots(created_at);

-- Comments
COMMENT ON TABLE evidence.timestamp_tokens IS 'RFC-3161 TimeStampToken evidence for long-term validation';
COMMENT ON TABLE evidence.revocation_snapshots IS 'OCSP/CRL certificate revocation evidence snapshots';

COMMENT ON COLUMN evidence.timestamp_tokens.artifact_digest IS 'SHA-256 digest of the timestamped artifact';
COMMENT ON COLUMN evidence.timestamp_tokens.tst_blob IS 'Raw DER-encoded RFC 3161 TimeStampToken';
COMMENT ON COLUMN evidence.timestamp_tokens.tsa_chain_pem IS 'PEM-encoded TSA certificate chain for LTV';
COMMENT ON COLUMN evidence.timestamp_tokens.ocsp_response IS 'Stapled OCSP response at signing time';
COMMENT ON COLUMN evidence.timestamp_tokens.crl_snapshot IS 'CRL snapshot at signing time (fallback for OCSP)';

COMMENT ON COLUMN evidence.revocation_snapshots.certificate_fingerprint IS 'SHA-256 fingerprint of the certificate';
COMMENT ON COLUMN evidence.revocation_snapshots.raw_response IS 'Raw OCSP response or CRL bytes';
COMMENT ON COLUMN evidence.revocation_snapshots.response_time IS 'thisUpdate from the response';
COMMENT ON COLUMN evidence.revocation_snapshots.valid_until IS 'nextUpdate from the response';