WITH service_recovery AS (

SELECT

se.service_id,

se.block_timestamp as eviction_time,

-- Look for next checkpoint after eviction

(

SELECT MIN(block_timestamp)

FROM crosschain.olas.ez_service_checkpoints sc

WHERE sc.service_id = se.service_id

AND sc.block_timestamp > se.block_timestamp

) as next_checkpoint_time,

-- Look for next donation after eviction

(

SELECT MIN(block_timestamp)

FROM crosschain.olas.ez_service_donations sd

WHERE sd.service_id = se.service_id

AND sd.block_timestamp > se.block_timestamp

) as next_donation_time

FROM crosschain.olas.ez_service_evictions se

SELECT

service_id,

eviction_time,

next_checkpoint_time,

next_donation_time,

DATEDIFF('hour', eviction_time, next_checkpoint_time) as hours_to_next_checkpoint,

DATEDIFF('hour', eviction_time, next_donation_time) as hours_to_next_donation,

CASE

WHEN next_checkpoint_time IS NULL AND next_donation_time IS NULL THEN 'No Recovery'

WHEN next_checkpoint_time IS NOT NULL OR next_donation_time IS NOT NULL THEN 'Recovered'

END as recovery_status

FROM service_recovery

ORDER BY eviction_time DESC;