-- TRAIL DPS 4.2.3 - Validation apres depot FTP
-- A executer dans phpMyAdmin OVH, base synoluidpstrack, onglet SQL.
-- Ces requetes ne modifient pas les donnees.

SELECT 'tracking_positions_latest rows' AS check_name, COUNT(*) AS row_count
FROM tracking_positions_latest;

SELECT 'tracking_positions_trace rows' AS check_name, COUNT(*) AS row_count
FROM tracking_positions_trace;

SELECT 'active bindings by actor' AS check_name, actor_type, COUNT(*) AS binding_count
FROM tracking_bindings
WHERE status = 'active'
GROUP BY actor_type;

SELECT 'latest freshness' AS check_name,
       freshness_status,
       COUNT(*) AS row_count
FROM tracking_positions_latest
GROUP BY freshness_status;

SELECT 'raw purge candidates > 8h' AS check_name, COUNT(*) AS candidate_count
FROM tracking_positions
WHERE recorded_at_server < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 8 HOUR);

SELECT 'trace purge candidates > 7d' AS check_name, COUNT(*) AS candidate_count
FROM tracking_positions_trace
WHERE recorded_at_server < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 7 DAY);

SELECT 'api log purge candidates > 30d' AS check_name, COUNT(*) AS candidate_count
FROM tracking_api_logs
WHERE created_at < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY);

SELECT 'error log purge candidates > 30d' AS check_name, COUNT(*) AS candidate_count
FROM tracking_error_logs
WHERE created_at < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY);
