
-- Could not auto-generate a down migration.
-- Please write an appropriate down migration for the SQL below:
-- CREATE UNIQUE INDEX if not exists cloudaccount_k8s_aggregate_pk
-- 	ON public.cloudaccount_k8s_aggregate
-- 	USING btree (tenant_id, account_id, "timestamp");

-- Could not auto-generate a down migration.
-- Please write an appropriate down migration for the SQL below:
-- create materialized view cloudaccount_k8s_aggregate as
-- WITH cluster_ranked_nodes AS (
--          SELECT cksna.tenant_id,
--             cksna.account_id,
--             cksna."timestamp"::date AS "timestamp",
--             cksna.node_name,
--             cksna.node_flavor,
--             cksna.avg_cpu_used,
--             cksna.max_cpu_used,
--             cksna.avg_memory_used,
--             cksna.max_memory_used,
--             cksna.node_type,
--             dense_rank() OVER (PARTITION BY cksna.tenant_id, cksna.account_id, (cksna."timestamp"::date), cksna.node_name, cksna.node_flavor ORDER BY cksna.tenant_id, cksna.account_id, (cksna."timestamp"::date), cksna.node_name, cksna.node_flavor DESC) AS node_rank
--            FROM cloudaccount_k8s_node_aggregate cksna
--         ), cluster_nodes AS (
--          SELECT cksna.tenant_id,
--             cksna.account_id,
--             cksna."timestamp",
--             count(DISTINCT cksna.node_name) AS node_count,
--             count(DISTINCT
--                 CASE
--                     WHEN cksna.node_type = 'spot'::text THEN cksna.node_name
--                     ELSE NULL::text
--                 END) AS spot_node_count,
--             count(DISTINCT
--                 CASE
--                     WHEN cksna.node_type = 'on_demand'::text THEN cksna.node_name
--                     ELSE NULL::text
--                 END) AS ondemand_node_count,
--             sum(
--                 CASE
--                     WHEN cksna.node_rank = 1 THEN cksna.avg_cpu_used
--                     ELSE NULL::double precision
--                 END) AS avg_cpu_used_node,
--             sum(
--                 CASE
--                     WHEN cksna.node_rank = 1 THEN cksna.max_cpu_used
--                     ELSE NULL::double precision
--                 END) AS max_cpu_used_node,
--             sum(
--                 CASE
--                     WHEN cksna.node_rank = 1 THEN (crd.resource_capacity ->> 'cpu_virtual'::text)::integer
--                     ELSE NULL::integer
--                 END) AS total_cpu_capacity,
--             sum(
--                 CASE
--                     WHEN cksna.node_rank = 1 THEN cksna.avg_memory_used
--                     ELSE NULL::double precision
--                 END) AS avg_memory_used_node,
--             sum(
--                 CASE
--                     WHEN cksna.node_rank = 1 THEN cksna.max_memory_used
--                     ELSE NULL::double precision
--                 END) AS max_memory_used_node,
--             sum(
--                 CASE
--                     WHEN cksna.node_rank = 1 THEN (crd.resource_capacity ->> 'memory_gb'::text)::integer
--                     ELSE NULL::integer
--                 END) AS total_memory_capacity
--            FROM cluster_ranked_nodes cksna
--              LEFT JOIN cloud_resource_details crd ON crd.resource_type = cksna.node_flavor
--           GROUP BY cksna.tenant_id, cksna.account_id, cksna."timestamp"
--           ORDER BY cksna.tenant_id, cksna.account_id, cksna."timestamp" DESC
--         ), cluster_pods AS (
--          SELECT ckspa.tenant_id,
--             ckspa.account_id,
--             ckspa."timestamp"::date AS "timestamp",
--             count(DISTINCT ckspa.pod_name) AS pod_count,
--             count(
--                 CASE
--                     WHEN ckspa.is_active = false THEN 1
--                     ELSE NULL::integer
--                 END) AS failed_pod_count,
--             count(DISTINCT (ckspa.namespace_name || '.'::text) || ckspa.workload_name) AS workload_count,
--             sum(ckspa.pod_cost) AS pod_cost
--            FROM cloudaccount_k8s_pod_aggregate ckspa
--           GROUP BY ckspa.tenant_id, ckspa.account_id, (ckspa."timestamp"::date)
--         )
--  SELECT cn.tenant_id,
--     cn.account_id,
--     ca.account_name,
--     cn."timestamp",
--     cn.node_count,
--     cn.spot_node_count,
--     cn.ondemand_node_count,
--     cn.avg_cpu_used_node,
--     cn.max_cpu_used_node,
--     cn.avg_memory_used_node,
--     cn.max_memory_used_node,
--     cp.workload_count,
--     cp.pod_count,
--     cp.failed_pod_count,
--     cp.pod_cost
--    FROM cluster_nodes cn
--      JOIN cluster_pods cp ON cn.tenant_id = cp.tenant_id AND cn.account_id = cp.account_id AND cn."timestamp" = cp."timestamp"
--      JOIN cloud_accounts ca ON cn.tenant_id = ca.tenant AND cn.account_id = ca.id
--   ORDER BY cn."timestamp" DESC;

-- Could not auto-generate a down migration.
-- Please write an appropriate down migration for the SQL below:
-- DROP MATERIALIZED VIEW "public"."cloudaccount_k8s_aggregate";
