-- LOT 11.23 - controles non destructifs des alertes dashboard admin

-- 1. Licences proches expiration < 30 jours
SELECT id, license_key, customer_name, status, end_date, DATEDIFF(end_date, NOW()) AS days_remaining
FROM licenses
WHERE end_date IS NOT NULL
  AND end_date >= NOW()
  AND end_date < DATE_ADD(NOW(), INTERVAL 30 DAY)
  AND status NOT IN ('revoked', 'disabled', 'cancelled')
ORDER BY end_date ASC;

-- 2. Quotas courses depasses sur dernier snapshot PLACE_LICENSE_CHECK
SELECT l.id, l.license_key, l.customer_name, l.max_courses_rolling_year, u.rolling_365_course_count AS course_count, u.computed_at
FROM licenses l
INNER JOIN (
    SELECT s.*
    FROM license_usage_stats s
    INNER JOIN (
        SELECT license_id, MAX(id) AS latest_id
        FROM license_usage_stats
        WHERE source = 'PLACE_LICENSE_CHECK' OR source LIKE 'LOCAL_PLACE%'
        GROUP BY license_id
    ) latest ON latest.latest_id = s.id
) u ON u.license_id = l.id
WHERE l.max_courses_rolling_year IS NOT NULL
  AND l.max_courses_rolling_year > 0
  AND u.rolling_365_course_count > l.max_courses_rolling_year
ORDER BY u.rolling_365_course_count DESC;
