-- -----------------------------------------------------------------------------
-- 006_timestamp_supersession.sql
-- Sprint: SPRINT_20260119_009 Evidence Storage for Timestamps
-- Task: EVT-005 - Re-Timestamping Support
-- Description: Schema extension for timestamp supersession chain.
-- -----------------------------------------------------------------------------

-- Add supersession column for re-timestamping chain
ALTER TABLE evidence.timestamp_tokens
ADD COLUMN IF NOT EXISTS supersedes_id UUID REFERENCES evidence.timestamp_tokens(id);

-- Index for finding superseding timestamps
CREATE INDEX IF NOT EXISTS idx_timestamp_supersedes ON evidence.timestamp_tokens(supersedes_id);

-- Index for finding timestamps by expiry (for re-timestamp scheduling)
-- Note: We need to track TSA certificate expiry separately - for now use generation_time + typical cert lifetime
CREATE INDEX IF NOT EXISTS idx_timestamp_for_retimestamp 
ON evidence.timestamp_tokens(generation_time) 
WHERE supersedes_id IS NULL;  -- Only query leaf timestamps (not already superseded)

-- Comments
COMMENT ON COLUMN evidence.timestamp_tokens.supersedes_id IS 'ID of the timestamp this supersedes (for re-timestamping chain)';

-- Rollback script (execute separately if needed):
-- ALTER TABLE evidence.timestamp_tokens DROP COLUMN IF EXISTS supersedes_id;
-- DROP INDEX IF EXISTS evidence.idx_timestamp_supersedes;
-- DROP INDEX IF EXISTS evidence.idx_timestamp_for_retimestamp;