--------------------------------------------

    /*routerQuantity*/
--------------------------------------------

WITH RECURSIVE Calendar AS (
  SELECT 
    CAST(
      '2025-12-01 00:00:00' AS DATETIME
    ) AS report_hour 
  UNION ALL 
  SELECT 
    report_hour + INTERVAL 1 HOUR 
  FROM 
    Calendar 
  WHERE 
    report_hour < '2026-01-07 23:00:00'
) 
SELECT 
  c.report_hour, 
  COUNT(ep.id) AS routerQuantity 
FROM 
  Calendar c 
  INNER JOIN EdgeRouter ep ON ep.createdAt <= c.report_hour 
  AND (
    ep.deletedAt = 0 
    OR FROM_UNIXTIME(ep.deletedAt / 1000) > c.report_hour
  ) 
  AND ep.networkId = UUID_TO_BIN(
    '951eaaef-9d60-4db2-8401-c29dffcee637'
  ) 
  INNER JOIN Host h ON ep.hostId = h.id 
GROUP BY 
  c.report_hour 
ORDER BY 
  c.report_hour ASC;





SELECT 
  ep.name, 
  ep.createdAt, 
  h.provider, 
  ep.* 
FROM 
  CoreManagement.EdgeRouter ep 
  INNER JOIN CoreManagement.Host h ON ep.hostId = h.id 
Where 
  ep.networkId = UUID_TO_BIN(
    '951eaaef-9d60-4db2-8401-c29dffcee637'
  ) 
  AND ep.deletedAt = 0 
  AND h.deletedAt = 'CUSTOMER' 
order by 
  ep.createdAt desc;


--------------------------------------------

    /*registeredRouterQuantity*/
--------------------------------------------


WITH RECURSIVE Calendar AS (
  SELECT 
    CAST(
      '2025-12-01 00:00:00' AS DATETIME
    ) AS report_hour 
  UNION ALL 
  SELECT 
    report_hour + INTERVAL 1 HOUR 
  FROM 
    Calendar 
  WHERE 
    report_hour < '2026-01-07 23:00:00'
) 
SELECT 
  c.report_hour, 
  COUNT(ep.id) AS registeredRouterQuantity 
FROM 
  Calendar c 
  INNER JOIN EdgeRouter ep ON ep.createdAt <= c.report_hour 
  AND (
    ep.deletedAt = 0 
    OR FROM_UNIXTIME(ep.deletedAt / 1000) > c.report_hour
  ) 
  AND ep.networkId = UUID_TO_BIN(
    '951eaaef-9d60-4db2-8401-c29dffcee637'
  ) 
  AND ep.jwt IS NULL 
  INNER JOIN Host h ON ep.hostId = h.id 
GROUP BY 
  c.report_hour 
ORDER BY 
  c.report_hour ASC;

--------------------------------------------

    /* registeredCloudRouterQuantity*/
--------------------------------------------




WITH RECURSIVE Calendar AS (
  SELECT 
    CAST(
      '2025-12-01 00:00:00' AS DATETIME
    ) AS report_hour 
  UNION ALL 
  SELECT 
    report_hour + INTERVAL 1 HOUR 
  FROM 
    Calendar 
  WHERE 
    report_hour < '2026-01-07 23:00:00'
) 
SELECT 
  c.report_hour, 
  COUNT(ep.id) AS registeredCloudRouterQuantity 
FROM 
  Calendar c 
  INNER JOIN EdgeRouter ep ON ep.createdAt <= c.report_hour 
  AND (
    ep.deletedAt = 0 
    OR FROM_UNIXTIME(ep.deletedAt / 1000) > c.report_hour
  ) 
  AND ep.networkId = UUID_TO_BIN(
    '951eaaef-9d60-4db2-8401-c29dffcee637'
  ) 
  AND ep.jwt IS NULL 
  INNER JOIN Host h ON ep.hostId = h.id 
  AND h.provider not in ('CUSTOMER') 
GROUP BY 
  c.report_hour 
ORDER BY 
  c.report_hour ASC;


--------------------------------------------

    /* CustomerUnRegisteredRouter*/ NOT REUIRED
--------------------------------------------


WITH RECURSIVE Calendar AS (
  SELECT 
    CAST(
      '2025-12-01 00:00:00' AS DATETIME
    ) AS report_hour 
  UNION ALL 
  SELECT 
    report_hour + INTERVAL 1 HOUR 
  FROM 
    Calendar 
  WHERE 
    report_hour < '2026-01-07 23:00:00'
) 
SELECT 
  c.report_hour, 
  COUNT(ep.id) AS CustomerUnRegisteredRouter 
FROM 
  Calendar c 
  LEFT JOIN EdgeRouter ep ON ep.createdAt <= c.report_hour 
  AND (
    ep.deletedAt = 0 
    OR FROM_UNIXTIME(ep.deletedAt / 1000) > c.report_hour
  ) 
  AND ep.networkId = UUID_TO_BIN(
    '951eaaef-9d60-4db2-8401-c29dffcee637'
  ) 
  AND ep.jwt IS NOT NULL 
  INNER JOIN Host h ON ep.hostId = h.id 
  AND h.provider = 'CUSTOMER' 
GROUP BY 
  c.report_hour 
ORDER BY 
  c.report_hour ASC;



--------------------------------------------

    /* registeredPrivateRouterQuantity*/
--------------------------------------------



WITH RECURSIVE Calendar AS (
  SELECT 
    CAST(
      '2025-12-01 00:00:00' AS DATETIME
    ) AS report_hour 
  UNION ALL 
  SELECT 
    report_hour + INTERVAL 1 HOUR 
  FROM 
    Calendar 
  WHERE 
    report_hour < '2026-01-07 23:00:00'
) 
SELECT 
  c.report_hour, 
  COUNT(ep.id) AS registeredPrivateRouterQuantity 
FROM 
  Calendar c 
  LEFT JOIN EdgeRouter ep ON ep.createdAt <= c.report_hour 
  AND (
    ep.deletedAt = 0 
    OR FROM_UNIXTIME(ep.deletedAt / 1000) > c.report_hour
  ) 
  AND ep.networkId = UUID_TO_BIN(
    '951eaaef-9d60-4db2-8401-c29dffcee637'
  ) 
  AND ep.jwt IS NULL 
  INNER JOIN Host h ON ep.hostId = h.id 
  AND h.provider = 'CUSTOMER' 
GROUP BY 
  c.report_hour 
ORDER BY 
  c.report_hour ASC;

--------------------------------------------

    /* registeredEndpointQuantity*/
--------------------------------------------


WITH RECURSIVE Calendar AS (
  SELECT 
    CAST(
      '2025-12-01 00:00:00' AS DATETIME
    ) AS report_hour 
  UNION ALL 
  SELECT 
    report_hour + INTERVAL 1 HOUR 
  FROM 
    Calendar 
  WHERE 
    report_hour < '2026-01-07 23:00:00'
) 
SELECT 
  c.report_hour, 
  COUNT(ep.id) AS registeredEndpointQuantity 
FROM 
  Calendar c 
  LEFT JOIN Endpoint ep ON ep.networkId = UUID_TO_BIN(
    '951eaaef-9d60-4db2-8401-c29dffcee637'
  ) 
  AND ep.typeId NOT IN ('Router') 
  AND ep.createdAt <= c.report_hour 
  AND ep.type IS NOT NULL 
  AND (
    ep.deletedAt = 0 
    OR FROM_UNIXTIME(ep.deletedAt / 1000) > c.report_hour
  ) 
GROUP BY 
  c.report_hour 
ORDER BY 
  c.report_hour ASC;


--------------------------------------------

    /* endpointQuantity*/
--------------------------------------------
WITH RECURSIVE Calendar AS (
    SELECT CAST('2026-01-06 00:00:00' AS DATETIME) AS report_hour
    UNION ALL
    SELECT report_hour + INTERVAL 1 HOUR
    FROM Calendar
    WHERE report_hour < '2026-01-06 23:00:00'
)
SELECT 
    c.report_hour,
    COUNT(ep.id) AS endpointQuantity
FROM Calendar c
LEFT JOIN Endpoint ep ON 
    ep.networkId = UUID_TO_BIN('951eaaef-9d60-4db2-8401-c29dffcee637')
    AND ep.typeId NOT IN ('Router')
    AND ep.typeId IS NOT NULL
    AND ep.createdAt <= c.report_hour 
    AND (
        ep.deletedAt = 0 
        OR FROM_UNIXTIME(ep.deletedAt / 1000) > c.report_hour
    )
GROUP BY c.report_hour
ORDER BY c.report_hour ASC;