9 files changed, 203 insertions, 0 deletions
diff --git a/graphs/sql/maas/correct_values.sql b/graphs/sql/maas/correct_values.sql
new file mode 100644
index 0000000..3c9efbd
--- /dev/null
+++ b/graphs/sql/maas/correct_values.sql
@@ -0,0 +1,41 @@
+INSERT INTO "memorin"."zoned_devices_logs" (
+    "device_serial",
+    "version_id",
+    "zone_id",
+    "lat",
+    "long",
+    "temp",
+    "battery",
+    "signal_strength"
+)
+SELECT
+    -- The device should not be deactivated
+    (
+        SELECT "serial_number"
+        FROM "memorin"."devices"
+        WHERE "deactivated_at" IS NULL
+        ORDER BY random()
+        LIMIT 1
+    ),
+    -- The version should only exist
+    (
+        SELECT "id"
+        FROM "memorin"."device_versions"
+        ORDER BY random()
+        LIMIT 1
+    ),
+    "zone"."id" AS "zone_id",
+    -- Latitude and Longitude should be in the boundary box of the zone
+    random() * ("zone"."max_latitude" - "zone"."min_latitude")
+    + "zone"."min_latitude" AS "lat",
+    random() * ("zone"."max_longitude" - "zone"."min_longitude")
+    + "zone"."min_longitude" AS "long",
+    -- Temperature should be between 34 and 45
+    floor(random() * (45 - 34 + 1) + 34) AS "temp",
+    -- Battery percentage should be between 0 and 100
+    floor(random() * (100 + 1)) AS "battery",
+    -- Signal strength should be between 0 and 5
+    floor(random() * (5 + 1)) AS "signal_strength"
+FROM
+    "memorin"."geographic_zones" AS "zone"
+ORDER BY random() LIMIT 1;
diff --git a/graphs/sql/maas/incorrect_values.sql b/graphs/sql/maas/incorrect_values.sql
new file mode 100644
index 0000000..07ec90d
--- /dev/null
+++ b/graphs/sql/maas/incorrect_values.sql
@@ -0,0 +1,19 @@
+INSERT INTO "memorin"."zoned_devices_logs" (
+    "device_serial",
+    "version_id",
+    "zone_id",
+    "lat",
+    "long",
+    "temp",
+    "battery",
+    "signal_strength"
+) VALUES (
+    'W4JGVEMW51LE',
+    '0.0.0',
+    39,
+    -12.3,
+    0,
+    2,
+    290,
+    12
+);
diff --git a/graphs/sql/maas/req01.sql b/graphs/sql/maas/req01.sql
new file mode 100644
index 0000000..d017c93
--- /dev/null
+++ b/graphs/sql/maas/req01.sql
@@ -0,0 +1,15 @@
+CREATE VIEW memorin.devices_summary AS
+    SELECT
+    total_devices,
+    active_devices,
+    total_devices - active_devices AS inactive_devices
+    FROM
+    (
+        SELECT COUNT(*) AS total_devices
+        FROM memorin.devices
+    ) AS d,
+    (
+        SELECT COUNT(*) AS active_devices
+        FROM memorin.devices
+        WHERE deactivated_at IS NULL
+    ) AS a;
diff --git a/graphs/sql/maas/req02.sql b/graphs/sql/maas/req02.sql
new file mode 100644
index 0000000..1d95021
--- /dev/null
+++ b/graphs/sql/maas/req02.sql
@@ -0,0 +1,22 @@
+CREATE TEMPORARY VIEW devices_metrics AS (
+    SELECT
+    device_serial,
+    version_id,
+    logs.id AS log_id,
+    latitude AS lat,
+    longitude AS long,
+    temperature AS temp,
+    battery_percentage AS battery,
+    signal_strength
+    FROM
+    (
+        SELECT serial_number
+        FROM memorin.devices
+        WHERE deactivated_at IS NULL
+    ) AS active,
+    memorin.device_logs AS logs
+    INNER JOIN memorin.device_versions AS v
+    ON version_id = v.id
+    WHERE serial_number = device_serial
+    ORDER BY device_serial, v.released_at DESC, log_id
+);
diff --git a/graphs/sql/maas/req03.sql b/graphs/sql/maas/req03.sql
new file mode 100644
index 0000000..9b724cc
--- /dev/null
+++ b/graphs/sql/maas/req03.sql
@@ -0,0 +1,47 @@
+CREATE VIEW memorin.zoned_devices_logs AS
+    SELECT
+    device_serial,
+    version_id,
+    zone_id,
+    latitude AS lat,
+    longitude AS long,
+    temperature AS temp,
+    battery_percentage AS battery,
+    signal_strength
+    FROM
+    memorin.device_logs AS logs
+    WHERE
+    device_serial IN (
+        SELECT serial_number
+        FROM memorin.devices
+        WHERE deactivated_at IS NULL
+    )
+    AND version_id IN (
+        SELECT id
+        FROM memorin.device_versions
+        WHERE id = logs.version_id
+    )
+    AND temperature BETWEEN 34 AND 45
+    AND signal_strength BETWEEN 0 AND 5
+    AND battery_percentage BETWEEN 0 AND 100
+    AND longitude BETWEEN (
+        SELECT min_longitude
+        FROM memorin.geographic_zones
+        WHERE id = logs.zone_id
+    )
+    AND (
+        SELECT max_longitude
+        FROM memorin.geographic_zones
+        WHERE id = logs.zone_id
+    )
+    AND latitude BETWEEN (
+        SELECT min_latitude
+        FROM memorin.geographic_zones
+        WHERE id = logs.zone_id
+    )
+    AND (
+        SELECT max_latitude
+        FROM memorin.geographic_zones
+        WHERE id = logs.zone_id
+    )
+    WITH CHECK OPTION;
diff --git a/graphs/sql/maas/req04_select.sql b/graphs/sql/maas/req04_select.sql
new file mode 100644
index 0000000..8829df5
--- /dev/null
+++ b/graphs/sql/maas/req04_select.sql
@@ -0,0 +1,4 @@
+REFRESH MATERIALIZED VIEW memorin.outdated_devices;
+SELECT *
+FROM memorin.outdated_devices
+ORDER BY versions_behind DESC, serial_number
diff --git a/graphs/sql/maas/req04_view.sql b/graphs/sql/maas/req04_view.sql
new file mode 100644
index 0000000..bd00c7d
--- /dev/null
+++ b/graphs/sql/maas/req04_view.sql
@@ -0,0 +1,21 @@
+CREATE MATERIALIZED VIEW memorin.outdated_devices AS
+    SELECT
+        serial_number,
+        version_id,
+        released_at,
+        eol_timestamp,
+        (
+            SELECT COUNT(*) AS versions_behind
+            FROM memorin.device_versions AS vs
+            WHERE vs.released_at > v.released_at
+        ) AS versions_behind
+    FROM
+        memorin.devices AS d,
+        memorin.device_versions AS v
+    WHERE eol_timestamp IS NOT NULL
+    AND v.id = version_id
+    AND serial_number IN (
+        SELECT serial_number
+        FROM memorin.devices
+        WHERE deactivated_at IS NULL
+    );
diff --git a/graphs/sql/maas/req05_select.sql b/graphs/sql/maas/req05_select.sql
new file mode 100644
index 0000000..2a8e816
--- /dev/null
+++ b/graphs/sql/maas/req05_select.sql
@@ -0,0 +1,12 @@
+SELECT
+    name AS zone_name,
+    COUNT(DISTINCT data_center_id) AS data_center_count,
+    COUNT(DISTINCT sh.id) AS total_servers,
+    SUM(core_count) AS total_cores,
+    SUM(ram) AS total_ram,
+    SUM(storage) AS total_storage
+FROM memorin.server_hierarchy AS sh
+INNER JOIN memorin.geographic_zones AS z
+ON z.id = zone_id
+GROUP BY name
+ORDER BY data_center_count DESC, total_servers DESC, zone_name
diff --git a/graphs/sql/maas/req05_view.sql b/graphs/sql/maas/req05_view.sql
new file mode 100644
index 0000000..b6e792f
--- /dev/null
+++ b/graphs/sql/maas/req05_view.sql
@@ -0,0 +1,22 @@
+CREATE RECURSIVE VIEW memorin.server_hierarchy (id,zone_id,core_count,ram,storage,data_center_id) AS (
+    SELECT
+        id,
+        zone_id,
+        core_count,
+        ram,
+        storage,
+        id AS data_center_id
+    FROM memorin.servers
+    WHERE master_id IS NULL
+    UNION ALL
+    SELECT
+        servers.id,
+        servers.zone_id,
+        servers.core_count,
+        servers.ram,
+        servers.storage,
+        sh.data_center_id
+    FROM memorin.servers
+    INNER JOIN server_hierarchy AS sh
+    ON sh.id = servers.master_id
+);