#!/bin/bash

DB=vsphere.db

### listo i datacenter e li inserisco nel db
echo "Listo i DC e li inserisco nel DB $DB"
echo

govc ls | while read -r path; do
  abs_path="${path#./}"
  name=$(basename "$abs_path")
  echo datacenter abs_path: $abs_path
  echo datacenter name: $name
  echo

  ### inserisco nel db
  sqlite3 "$DB" "INSERT OR IGNORE INTO datacenter(name, path) VALUES('$name', '$abs_path');"
done
###

echo stampo il contenuto della tabella datacenter del db:
echo
sqlite3 "$DB" "SELECT * FROM datacenter;"
echo

### per ogni dc in datacenter trovo i cluster e popolo il db
echo per ogni dc in datacenter trovo i cluster e popolo il db
echo 
sqlite3 "$DB" "SELECT name FROM datacenter;" | while read -r dc; do
  echo "Datacenter=$dc"
  echo
  govc ls /$dc/host | while read -r path; do
    abs_path="${path#./}"
    name=$(basename "$abs_path")
    echo cluster abs_path: $abs_path
    echo cluster name: $name
    echo
    echo "Aggiungo cluster $name in datacenter $dc"
    sqlite3 "$DB" "INSERT OR IGNORE INTO cluster(datacenter_id, name, path) VALUES ((SELECT id FROM datacenter WHERE name='$dc'), '$name', '$abs_path');"
  done
done
###

echo stampo il contenuto della tabella cluster del db:
echo
sqlite3 "$DB" "SELECT * FROM datacenter;"
echo
sqlite3 "$DB" "SELECT * FROM cluster;"
echo

### per ogni dc in datacenter e per ogni cluster nella tabella cluster trovo le vlan disponibili e popolo il db
echo per ogni dc in datacenter e per ogni cluster nella tabella cluster trovo le vlan disponibili e popolo il db
echo
sqlite3 "$DB" "SELECT id FROM datacenter;" | while read -r id_dc; do
  dc=$(sqlite3 "$DB" "SELECT name FROM datacenter WHERE id='$id_dc'")
  echo "Datacenter=$dc Datacenter_id=$id_dc"
  echo
  sqlite3 "$DB" "SELECT name FROM cluster WHERE datacenter_id='$id_dc' ;" | while read -r cluster; do
    echo "Datacenter=$dc Datacenter_id=$id_dc"
    echo "Cluster=$cluster"
    echo
    govc ls /$dc/host/$cluster | grep vlan | while read -r vlan; do
      abs_path="${vlan#./}"
      name=$(basename "$abs_path")
      echo network abs_path: $abs_path
      echo network name: $name
      echo
      echo "aggiungo la vlan nel db"
      sqlite3 "$DB" "INSERT OR IGNORE INTO network(datacenter_id, cluster_id, name, path) VALUES ((SELECT id FROM datacenter WHERE name='$dc'), (SELECT id FROM cluster WHERE name='$cluster'), '$name', '$abs_path');"
    done
  done
done
###

echo stampo il contenuto delle tabelle del db:
echo
sqlite3 "$DB" "SELECT * FROM datacenter;"
echo
sqlite3 "$DB" "SELECT * FROM cluster;"
echo
sqlite3 "$DB" "SELECT * FROM network;"
echo

### per ogni datacenter aggiungo i relativi datastore
echo per ogni datacenter aggiungo i relativi datastore
echo
sqlite3 "$DB" "SELECT name FROM datacenter;" | while read -r dc; do
  echo "Datacenter=$dc"
  echo
  govc ls /$dc/datastore | while read -r path; do
    abs_dspath="${path#./}"
    dsname=$(basename "$abs_dspath")
    echo datastore abs_path: $abs_dspath
    echo datastore name: $dsname
    echo
    #echo "Aggiungo il datastore $name nel db"
    #sqlite3 "$DB" "INSERT OR IGNORE INTO datastore(datacenter_id, name, path) VALUES ((SELECT id FROM datacenter WHERE name='$dc'), '$name', '$abs_path');"
    echo per ogni datastore, estraggo le info e le aggiungo nel db
    echo
    govc datastore.info -dc=$dc -json $dsname \
      | jq -r '.datastores[] | [.summary.capacity, .summary.freeSpace, .summary.type] | @tsv' \
      | while IFS=$'\t' read -r capacity freeSpace type; do
      sqlite3 $DB "INSERT OR IGNORE INTO datastore(datacenter_id, name, path, capacity, freespace, type) VALUES((SELECT id FROM datacenter WHERE name='$dc'), '$dsname', '$abs_dspath', $capacity, $freeSpace, '$type');"
    done
  done
done
###


echo stampo il contenuto delle tabelle del db:
echo
sqlite3 "$DB" "SELECT * FROM datacenter;"
echo
sqlite3 "$DB" "SELECT * FROM cluster;"
echo
sqlite3 "$DB" "SELECT * FROM network;"
echo
sqlite3 "$DB" "SELECT * FROM datastore;"
echo