-- TRAIL DPS 4.2 - LOT 15.18
-- Correctif horodatage UTC des remises a zero SMS de licence.
-- A executer dans la base licence synolui94.
-- Objectif : aligner license_sms_usage_resets.reset_at sur l'horodatage UTC
-- du snapshot OVH_SMS_RENEWAL_RESET, car les premiers correctifs avaient ecrit
-- reset_at avec NOW() alors que sms_logs.created_at est stocke en UTC.

CREATE TABLE IF NOT EXISTS license_sms_usage_resets (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  license_id INT UNSIGNED NOT NULL,
  reset_at DATETIME NOT NULL,
  reason VARCHAR(64) NOT NULL DEFAULT 'LICENSE_RENEWAL',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_license_sms_usage_resets_license_time (license_id, reset_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

UPDATE license_sms_usage_resets r
JOIN (
  SELECT license_id, MAX(computed_at) AS reset_at_utc
  FROM license_usage_stats
  WHERE source = 'OVH_SMS_RENEWAL_RESET'
  GROUP BY license_id
) s ON s.license_id = r.license_id
SET r.reset_at = s.reset_at_utc
WHERE s.reset_at_utc IS NOT NULL
  AND r.reset_at <> s.reset_at_utc
  AND ABS(TIMESTAMPDIFF(SECOND, r.reset_at, s.reset_at_utc)) <= 43200;
