#!/bin/bash
# Copyright 2025, VMware by Broadcom Corporation, All rights reserved.
set -e

policy_file="all_policies.out"
cluster_file="all_clusters.out"
pod_ns="tca-mgr"
pod_name="postgres-0"
declare -A staled_cluster_id_map

echo "list policies with same name but referring to different cluster id"
kubectl exec  -n $pod_ns $pod_name   -c pg-container -- psql -d caas_hub -c 'SELECT uid,name,"clusterName","clusterId" FROM policy_intent WHERE (name, "clusterName") IN (SELECT name, "clusterName" FROM policy_intent GROUP BY name, "clusterName" HAVING COUNT(*)>1);'

echo "list all policies as following"
kubectl exec  -n $pod_ns $pod_name -c pg-container -- psql -d caas_hub -c 'select "clusterId",uid,name,"clusterName","tknpName",created_at from policy_intent;' 2> /dev/null
# store all policies to all_policies.out
kubectl exec  -n $pod_ns $pod_name -c pg-container -- psql -d caas_hub -t -c 'select "clusterId",uid,name,"clusterName","tknpName",created_at from policy_intent;' > $policy_file 2> /dev/null

echo "list list all clusters as following"
kubectl exec  -n $pod_ns $pod_name -c pg-container -- psql -d tca -c " select val->>'clusterName' as \"clusterName\",val->'metadata'->>'mgmtClusterName' as \"mgmtClusterName\", val->>'id' as \"cluster_id\" from \"K8sClusterDetails\" where val->>'rowType'='cluster';"
#store all clusters to $cluster_file
kubectl exec  -n $pod_ns $pod_name -c pg-container -- psql -d tca -t -c " select val->>'id' as \"cluster_id\", val->>'clusterName' as \"clusterName\",val->'metadata'->>'mgmtClusterName' as \"mgmtClusterName\" from \"K8sClusterDetails\" where val->>'rowType'='cluster';" > $cluster_file 2> /dev/null

while IFS='|' read -r cluster_id _; do
cluster_id=$(echo "$cluster_id" | xargs)
if ! grep -q "$cluster_id" $cluster_file; then
    # echo "cluster_id $cluster_id is missing from $cluster_file"
    # grep "$cluster_id" $policy_file
    # echo ""
    cluster_name=$(cat $policy_file | grep $cluster_id | head -n 1 | awk '{print $7}')
    staled_cluster_id_map[$cluster_id]="$cluster_name"
fi
done < $policy_file

for key in "${!staled_cluster_id_map[@]}"; do
    echo "$key is a staled cluster id for ${staled_cluster_id_map[$key]}"
    echo "=====> Detected following staled policies"
    cat $policy_file | grep $key
done

if [ ${#staled_cluster_id_map[@]} -eq 0 ]; then
  echo "No stale policy is detected."
fi