ALTER TABLE kanadi_bundle_sessions
ADD COLUMN IF NOT EXISTS session_id VARCHAR(255),
ADD COLUMN IF NOT EXISTS user_id UUID REFERENCES kanadi_users(id) ON DELETE SET NULL,
ADD COLUMN IF NOT EXISTS device_id UUID REFERENCES kanadi_devices(id) ON DELETE SET NULL,
ADD COLUMN IF NOT EXISTS validator_ids TEXT[],
ADD COLUMN IF NOT EXISTS ip VARCHAR(45),
ADD COLUMN IF NOT EXISTS user_agent TEXT,
ADD COLUMN IF NOT EXISTS referrer TEXT,
ADD COLUMN IF NOT EXISTS result VARCHAR(50),
ADD COLUMN IF NOT EXISTS confidence FLOAT,
ADD COLUMN IF NOT EXISTS metadata JSONB,
ADD COLUMN IF NOT EXISTS started_at TIMESTAMP,
ADD COLUMN IF NOT EXISTS completed_at TIMESTAMP;

ALTER TABLE kanadi_validator_sessions
DROP COLUMN IF EXISTS data;

ALTER TABLE kanadi_validator_sessions
ALTER COLUMN bundle_session_id SET NOT NULL;

CREATE INDEX IF NOT EXISTS idx_bundle_sessions_session_id ON kanadi_bundle_sessions(session_id);
CREATE INDEX IF NOT EXISTS idx_bundle_sessions_user_id ON kanadi_bundle_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_bundle_sessions_device_id ON kanadi_bundle_sessions(device_id);
CREATE INDEX IF NOT EXISTS idx_bundle_sessions_ip ON kanadi_bundle_sessions(ip);
CREATE INDEX IF NOT EXISTS idx_bundle_sessions_result ON kanadi_bundle_sessions(result);
CREATE INDEX IF NOT EXISTS idx_bundle_sessions_started_at ON kanadi_bundle_sessions(started_at);
CREATE INDEX IF NOT EXISTS idx_bundle_sessions_created_at ON kanadi_bundle_sessions(created_at);
