-- Could not auto-generate a down migration.
-- Please write an appropriate down migration for the SQL below:
-- DROP MATERIALIZED VIEW public.cloudaccount_k8s_resource_aggregate;
--
-- CREATE MATERIALIZED VIEW public.cloudaccount_k8s_resource_aggregate
-- TABLESPACE pg_default
-- as with cluster_nodes as (
-- select
-- 	cksna.tenant_id,
-- 	cksna.account_id,
-- 	count(distinct
--                 case
--                     when cksna.is_active is not false then cksna.name
--                     else null::text
--                 end) as node_count,
-- 	count(distinct
--                 case
--                     when lower(cksna.node_type) = 'spot'::text and cksna.is_active is not false then cksna.name
--                     else null::text
--                 end) as spot_node_count,
-- 	count(distinct
--                 case
--                     when lower(cksna.node_type) = 'on-demand'::text or lower(cksna.node_type) = 'on_demand'::text and cksna.is_active is not false then cksna.name
--                     else null::text
--                 end) as ondemand_node_count,
-- 	sum(cksna.avg_cpu_used) as avg_cpu_used_node,
-- 	sum(cksna.max_cpu_used) as max_cpu_used_node,
-- 	sum(
--                 case
--                     when cksna.is_active is not false then cksna.cpu_capacity
--                     else null::double precision
--                 end) as total_cpu_capacity,
-- 	sum(cksna.avg_memory_used) as avg_memory_used_node,
-- 	sum(cksna.max_memory_used) as max_memory_used_node,
-- 	sum(
--                 case
--                     when cksna.is_active is not false then cksna.memory_capacity
--                     else null::double precision
--                 end) as total_memory_capacity,
-- 	sum(
--                 case
--                     when cksna.is_active is not false then cksna.memory_allocatable
--                     else null::double precision
--                 end) as total_memory_allocatable,
-- 	sum(
--                 case
--                     when cksna.is_active is not false then cksna.cpu_allocatable
--                     else null::double precision
--                 end) as total_cpu_allocatable,
-- 	sum(
--                 case
--                     when cksna.is_active is not false then cksna.cpu_allocated
--                     else null::double precision
--                 end) as total_cpu_allocated,
-- 	sum(
--                 case
--                     when cksna.is_active is not false then cksna.memory_allocated
--                     else null::double precision
--                 end) as total_memory_allocated,
-- 	sum(
--                 case
--                     when cksna.is_active is not false then cksna.pods_count::double precision
--                     else null::double precision
--                 end) as pods_count,
-- 	cksna."timestamp",
-- 	sum(cksna.node_cost) as node_cost,
-- 	avg(
--                 case
--                     when cksna.is_active is not false then cksna.max_total_efficiency
--                     else null::double precision
--                 end) as total_efficiency,
-- 	max(cksna.node_region) as region
-- from
-- 	cloudaccount_k8s_resource_node_aggregate cksna
-- group by
-- 	cksna.tenant_id,
-- 	cksna.account_id,
-- 	cksna."timestamp"
--         ),
-- cluster_pods as (
-- select
-- 	ckspa.tenant_id,
-- 	ckspa.account_id,
-- 	count(distinct ckspa.pod_name) as pod_count,
-- 	count(
--                 case
--                     when lower(ckspa.status)= 'failed' then 1
--                     else null::integer
--                 end) as failed_pod_count,
-- 	count(
--                 case
--                     when lower(ckspa.status) = 'running' then 1
--                     else null::integer
--                 end) as running_pod_count,
-- 	count(
--                 case
--                     when lower(ckspa.status) = 'succeeded' or lower(ckspa.status) = 'terminated' then 1
--                     else null::integer
--                 end) as completed_pod_count,
-- 	count(
--                 case
--                     when lower(ckspa.status) = 'pending_pod_count' then 1
--                     else null::integer
--                 end) as pending_pod_count,
-- 	count(distinct (ckspa.namespace_name || '.'::text) || ckspa.workload_name) as workload_count,
-- 	sum(ckspa.pod_cost) as pod_cost,
-- 	ckspa."timestamp"
-- from
-- 	cloudaccount_k8s_resource_pod_aggregate ckspa
-- group by
-- 	ckspa.tenant_id,
-- 	ckspa.account_id,
-- 	ckspa."timestamp"
--         ),
-- cloud_spend_mtd as (
-- select
-- 	sum(cn_1.node_cost) as mtd_cost,
-- 	cn_1.account_id as cloud_account_id
-- from
-- 	cloudaccount_k8s_resource_node_aggregate cn_1
-- where
-- 	date_trunc('month'::text, cn_1."timestamp") = date_trunc('month'::text, CURRENT_DATE::timestamp without time zone)
-- 	and date_trunc('year'::text, cn_1."timestamp") = date_trunc('year'::text, CURRENT_DATE::timestamp without time zone)
-- group by
-- 	cn_1.account_id
--         ),
-- cloud_spend_previous as (
-- select
-- 	sum(cn_1.node_cost) as previous_cost,
-- 	cn_1.account_id as cloud_account_id
-- from
-- 	cloudaccount_k8s_resource_node_aggregate cn_1
-- where
-- 	date_trunc('month'::text, cn_1."timestamp") = date_trunc('month'::text, (CURRENT_DATE::timestamp without time zone - interval '30 DAY'))
-- 		and date_trunc('year'::text, cn_1."timestamp") = date_trunc('year'::text, (CURRENT_DATE::timestamp without time zone - interval '30 DAY'))
-- 	group by
-- 		cn_1.account_id
--         ),
-- account_events as(
-- select
-- 	e.tenant ,
-- 	e.cloud_account_id ,
-- 	sum( case when e.subject_type = 'pod' then 1 else 0 end ) as pod_issue_count,
-- 	sum( case when e.subject_type = 'node' then 1 else 0 end) as node_issue_count,
-- 	sum( case when e.subject_type in ('job', 'deployment', 'statefulset', 'demonset') then 1 else 0 end) as workload_issue_count
-- from
-- 	events e
-- where
-- 	e.created_at > NOW() - interval '1 DAY'
-- 	and e.failure = 'True'
-- group by
-- 	e.tenant ,
-- 	e.cloud_account_id
-- 	),
-- account_events_yesterday as(
-- select
-- 	e.tenant ,
-- 	e.cloud_account_id ,
-- 	sum( case when e.subject_type = 'pod' then 1 else 0 end ) as pod_issue_count,
-- 	sum( case when e.subject_type = 'node' then 1 else 0 end) as node_issue_count,
-- 	sum( case when e.subject_type in ('job', 'deployment', 'statefulset', 'demonset') then 1 else 0 end) as workload_issue_count
-- from
-- 	events e
-- where
-- 	e.created_at > NOW() - interval '2 DAY'
-- 	and e.created_at < NOW() - interval '1 DAY'
-- 	and e.failure = 'True'
-- group by
-- 	e.tenant ,
-- 	e.cloud_account_id
-- 	)
--  select
-- 	cn.tenant_id,
-- 	cn.account_id,
-- 	ca.account_name,
-- 	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,
-- 	cn.pods_count as pod_count,
-- 	cp.failed_pod_count,
-- 	cp.completed_pod_count,
-- 	cp.running_pod_count,
-- 	cp.pending_pod_count,
-- 	cp.pod_cost,
-- 	cn.total_cpu_capacity,
-- 	cn.total_cpu_allocatable,
-- 	cn.total_memory_capacity,
-- 	cn.total_memory_allocatable,
-- 	cn.total_memory_allocated,
-- 	cn.total_cpu_allocated,
-- 	csm.mtd_cost,
-- 	csp.previous_cost,
-- 	cn."timestamp",
-- 	cn.node_cost,
-- 	cn.node_cost as workload_cost,
-- 	row_number() over (partition by cn.tenant_id,
-- 	cn.account_id
-- order by
-- 	cn."timestamp" desc) as rn,
-- 	crsa.best_practice_score,
-- 	crsa.right_sizing_score,
-- 	cn.total_efficiency,
-- 	0 as total_idle_cost,
-- 	e.pod_issue_count,
-- 	e.node_issue_count,
-- 	e.workload_issue_count,
-- 	ye.pod_issue_count as old_pod_issue_count,
-- 	ye.node_issue_count as old_node_issue_count,
-- 	ye.workload_issue_count as old_workload_issue_count,
-- 	cn.region
-- from
-- 	cluster_nodes cn
-- left 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
-- left join cloud_spend_mtd csm on
-- 	csm.cloud_account_id = ca.id
-- left join cloud_spend_previous csp on
-- 	csp.cloud_account_id = ca.id
-- left join cloudaccount_k8s_resource_score_aggregate crsa on
-- 	crsa.cloud_account_id = ca.id
-- left join account_events e on
-- 	e.cloud_account_id = ca.id
-- left join account_events_yesterday ye on
-- 	ye.cloud_account_id = ca.id;
-- 	
-- 	
-- CREATE UNIQUE INDEX cloudaccount_k8s_resource_aggregate_pk ON public.cloudaccount_k8s_resource_aggregate USING btree (tenant_id, account_id, "timestamp");
