SELECT

DATE_TRUNC('month', COALESCE(sd.block_timestamp, sc.block_timestamp, se.block_timestamp)) AS month,

sr.service_id,

sr.name,

COUNT(DISTINCT sd.donor_address) AS unique_donors,

COUNT(DISTINCT sc.tx_hash) AS checkpoints_met,

COUNT(DISTINCT se.tx_hash) AS times_evicted,

SUM(sd.eth_amount) AS total_donations_received

FROM crosschain.olas.ez_service_registrations sr

LEFT JOIN crosschain.olas.ez_service_donations sd ON sr.service_id = sd.service_id

LEFT JOIN crosschain.olas.ez_service_checkpoints sc ON sr.service_id = sc.service_id

LEFT JOIN crosschain.olas.ez_service_evictions se ON sr.service_id = se.service_id

GROUP BY 1, 2, 3

SELECT

month,

service_id,

name,

unique_donors,

checkpoints_met,

times_evicted,

total_donations_received,

CASE

WHEN times_evicted = 0 THEN 'Healthy'

WHEN times_evicted = 1 THEN 'Warning'

ELSE 'At Risk'

END AS service_health_status

FROM service_metrics

ORDER BY month, checkpoints_met DESC;