-- Migration: Activity events feed (normalized stream for transfers + app events + bridge stitching)
-- Description: Single table for activity feed; event_type in (TRANSFER, APP_ACTION, CLAIM, BRIDGE_OUT, BRIDGE_IN);
-- routing.path = ALT | CCIP for bridge-aware stitching. See docs/04-configuration/ACTIVITY_FEED_SPEC.md
-- Requires PostgreSQL 13+ (gen_random_uuid() is built-in).

CREATE TABLE IF NOT EXISTS activity_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    chain_id INTEGER NOT NULL,
    transaction_hash VARCHAR(66) NOT NULL,
    log_index INTEGER NOT NULL DEFAULT 0,
    block_number BIGINT NOT NULL,
    block_timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
    actor VARCHAR(42) NOT NULL,
    subject VARCHAR(42),
    event_type VARCHAR(32) NOT NULL,
    contract_address VARCHAR(42),
    data JSONB,
    routing JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(chain_id, transaction_hash, log_index)
);

CREATE INDEX idx_activity_events_chain_id ON activity_events(chain_id);
CREATE INDEX idx_activity_events_actor ON activity_events(actor);
CREATE INDEX idx_activity_events_subject ON activity_events(subject);
CREATE INDEX idx_activity_events_event_type ON activity_events(event_type);
CREATE INDEX idx_activity_events_block_timestamp ON activity_events(block_timestamp);
CREATE INDEX idx_activity_events_block_timestamp_id ON activity_events(block_timestamp, id);
CREATE INDEX idx_activity_events_contract ON activity_events(contract_address);
CREATE INDEX idx_activity_events_routing ON activity_events USING GIN (routing);

COMMENT ON TABLE activity_events IS 'Normalized activity feed: transfers, app events, bridge in/out; routing.path = ALT | CCIP for stitch';
COMMENT ON COLUMN activity_events.actor IS 'Wallet that initiated the action';
COMMENT ON COLUMN activity_events.subject IS 'Optional: user/account/tokenId/resource';
COMMENT ON COLUMN activity_events.routing IS 'Bridge stitching: { path: ALT|CCIP, fromChain, toChain, bridgeTxHash? }';