Database

// Total devices by protocol and tenant
@Query(value = """
    SELECT c.protocol_id AS protocolId,
           COUNT(*) AS count,
           c.location_id AS ispId
    FROM cpe c
    GROUP BY c.protocol_id, c.location_id
    """, nativeQuery = true)
List<ProtocolCountProjection> countByProtocol();

// Online status for non-TR069 devices (is_online = 1)
List<OnlineStatusProjection> countOnlineStatusByProtocolNonTR();

// Online status for TR069 via cpe_next_session_time
List<OnlineStatusProjection> countOnlineStatusTR(LocalDateTime endTime);

// Task count by status and date (previous 1-2 days)
List<TaskStatusProjection> countTasksByStatusAndDate(
    LocalDateTime startTime, LocalDateTime endTime);

// Task count by type and hour (previous 24 hours)
List<TaskTypeProjection> countTasksByTypeAndHour(
    LocalDateTime startTime, LocalDateTime endTime);

SELECT 'Completed' AS status, COUNT(*) AS count,
       DATE_FORMAT(created, '%Y-%m-%d') AS day, c.location_id AS ispId
FROM cpe_completed_task t JOIN cpe c ON t.cpe_id = c.id
WHERE t.created BETWEEN :start AND :end
GROUP BY day, c.location_id
UNION ALL
SELECT 'Failed' AS status, COUNT(*) AS count, ...
UNION ALL
SELECT 'Rejected' AS status, COUNT(*) AS count, ...
UNION ALL
SELECT 'Pending' AS status, COUNT(*) AS count, ...  -- repeats = 0
UNION ALL
SELECT 'Sent' AS status, COUNT(*) AS count, ...     -- repeats > 0

// Event count by type and hour
List<EventTypeProjection> countEventsByTypeAndHour(
    LocalDateTime startTime, LocalDateTime endTime);

// Total event count by tenant
List<EventCountProjection> countEventsInTimeRange(
    LocalDateTime startTime, LocalDateTime endTime);

List<Isp> findAllByIdIn(List<Integer> ids);