from os import environ as env
from keystoneauth1 import loading
from keystoneauth1 import session
from novaclient import client as nvclient
from cinderclient import client as cdclient
from keystoneclient import client as ksclient
import pymysql
import time


#################Openstack COnnection######################

loader = loading.get_plugin_loader('password')
auth = loader.load_from_options(auth_url=env['OS_AUTH_URL'],username=env['OS_USERNAME'],password=env['OS_PASSWORD'],project_id='1760223f21b241979daf7a12b1af7b60',  user_domain_name=env['OS_USER_DOMAIN_NAME'])
sess = session.Session(auth=auth)
nova = nvclient.Client('2', session=sess)
cinder = cdclient.Client('2', session=sess)
keystone = ksclient.Client('3', session=sess)

#########Database Connection##########################

connection = pymysql.connect(host='localhost', user='root', password='Ro3118086', db='billing', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)

######## General List ####################################

instances=nova.servers.list(search_opts={'all_tenants': 1})
flavors_list=nova.flavors.list()
users=keystone.users.list()
volumes=cinder.volumes.list(search_opts={'all_tenants': 1})

###############Cycles to be executed#################################

def find_dbobject_by_id (table_name,column_id,value_id):
    cursor=connection.cursor()
    sql_query=f"SELECT COUNT(1) AS QTY FROM {table_name} where {column_id} = '{value_id}'"
    cursor.execute(sql_query)
    qty=0
    for row in cursor:
        qty=row['QTY']
    return qty


for flavor in flavors:
    flavor_id=flavor.id
    flavor_name=flavor.name
    qty=find_dbobject_by_id("flavor","id", flavor_id)
    if qty == 0 :
        sql= ("INSERT INTO flavor (id,name) VALUES (%s,%s)")
        cursor.execute(sql,(flavor_id,flavor_name))
        connection.commit()
    elif qty ==1 :
        sql_2= "UPDATE flavor set name = %s where id = %s"
        cursor.execute(sql_2,(flavor_name,flavor_id))
        connection.commit()
    else:
        print(f"Flavor ID : {flavor_id} appear {qty} times")

for user in users:
    user_id=user.id
    user_name=user.name
    qty=find_dbobject_by_id("user","id", user_id)
    if qty == 0 :
        sql= ("INSERT INTO user (id,name) VALUES (%s,%s)")
        cursor.execute(sql,(user_id,user_name))
        connection.commit()
    elif qty ==1 :
        sql_2= "UPDATE user set name = %s where id = %s"
        cursor.execute(sql_2,(user_name,user_id))
        connection.commit()
    else:
        print(f"Flavor ID : {user_id} appear {qty} times")

for volume in volumes:
    volume_id=volume.id
    volume_name=volume.name
    volume_type=volume.volume_type
    volume_size=volume.size
    volume_project_id=volume.to_dict()['os-vol-tenant-attr:tenant_id']
    if qty == 0 :
        sql= ("INSERT INTO volume (id,name,type,size,project_id) VALUES (%s,%s,%s,%s,%s)")
        cursor.execute(sql,(volume_id,volume_name,volume_type,volume_size,volume_project_id))
        connection.commit()
    elif qty ==1 :
        sql_2= "UPDATE user set name = %s, type = %s, size = %s,project_id= %s where id = %s"
        cursor.execute(sql_2,(volume_name,volume_type,volume_size,volume_project_id,volume_id))
        connection.commit()
    else:
        print(f"Flavor ID : {user_id} appear {qty} times")




for instance in instances:

    instance_name=instance.name
    instance_id=instance.id
    instance_flavor_id=instance.flavor['id']
    instance_project_id=instance.tenant_id
    instance_status=instance.status
    instance_created=instance.created
    instance_updated=instance.updated
    flavors=nova.flavors.get(flavor=instance.flavor['id'])
    volumes= instance.to_dict()['os-extended-volumes:volumes_attached']
    qty=find_dbobject_by_id("server","id", instance_id)
    for volume in volumes:
        instance_volume_id=volume['id']
        if qty == 0 :
            sql= ("INSERT INTO server_volume (server_id,volume_id) VALUES (%s,%s)")
            cursor.execute(sql,(instance_id,instance_volume_id))
            connection.commit()
        elif qty ==1 :
            sql_2= "UPDATE server_volume set volume_id = %s where server_id = %s"
            cursor.execute(sql_2,(instance_volume_id,instance_id))
            connection.commit()