-- TRAIL DPS 4.2.3 - Reference purge manuelle
-- A utiliser seulement si l endpoint purge.php n est pas utilise.
-- Executer d abord les SELECT de controle, puis les DELETE uniquement si le resultat est coherent.

-- Controle brut > 8h
SELECT COUNT(*) AS raw_positions_to_delete
FROM tracking_positions
WHERE recorded_at_server < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 8 HOUR);

-- Suppression batch brut > 8h
DELETE FROM tracking_positions
WHERE recorded_at_server < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 8 HOUR)
LIMIT 1000;

-- Controle trace > 7 jours
SELECT COUNT(*) AS trace_points_to_delete
FROM tracking_positions_trace
WHERE recorded_at_server < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 7 DAY);

-- Suppression batch trace > 7 jours
DELETE FROM tracking_positions_trace
WHERE recorded_at_server < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 7 DAY)
LIMIT 1000;

-- Controle logs > 30 jours
SELECT COUNT(*) AS api_logs_to_delete
FROM tracking_api_logs
WHERE created_at < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY);

SELECT COUNT(*) AS error_logs_to_delete
FROM tracking_error_logs
WHERE created_at < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY);

-- Suppression batch logs > 30 jours
DELETE FROM tracking_api_logs
WHERE created_at < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY)
LIMIT 1000;

DELETE FROM tracking_error_logs
WHERE created_at < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY)
LIMIT 1000;
