-- Staging table for pending nonces (with TTL)
CREATE TABLE pending_nonces (
    nonce TEXT NOT NULL PRIMARY KEY,
    channel_id BYTEA NOT NULL CHECK (LENGTH(channel_id) = 26),
    author_id BYTEA NOT NULL CHECK (LENGTH(author_id) = 26),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMPTZ NOT NULL
);

-- Index for expiry queries
CREATE INDEX idx_pending_nonces_expires_at ON pending_nonces (expires_at);

CREATE OR REPLACE FUNCTION set_nonce_expiry()
    RETURNS TRIGGER AS $$
BEGIN
    NEW.expires_at := NOW() + INTERVAL '5 minutes';
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trig_pending_nonces_expiry
    BEFORE INSERT ON pending_nonces
    FOR EACH ROW
EXECUTE FUNCTION set_nonce_expiry();