import pandas as pd
import time
import pymysql
import logging
import sshtunnel
from sshtunnel import SSHTunnelForwarder
import  logging 
logging.basicConfig(filename='connection.log',  format='%(asctime)s %(message)s',
                    filemode='w'
)
logger = logging.getLogger()
logger.setLevel(logging.DEBUG)

ssh_host = '210.56.21.197'
ssh_username = 'devuser'
ssh_password = 'ubuntuhas'
database_username = 'root'
database_password = 'root'
database_name = 'smarthome_db'
localhost = '127.0.0.1'

def open_ssh_tunnel(verbose=False):
    try:
        if verbose:
            sshtunnel.DEFAULT_LOGLEVEL = logging.DEBUG

        global tunnel
        tunnel = SSHTunnelForwarder(
            (ssh_host, 22),
            ssh_username = ssh_username,
            ssh_password = ssh_password,
            remote_bind_address = ('127.0.0.1', 3306)
        )

        tunnel.start()
        logger.debug("tunnel connection successfully created")
    except BaseException as e:
        logger.error("Error while connecting to tunnel", e)

    # print(tunnel)

def mysql_connect():
    """Connect to a MySQL server using the SSH tunnel connection
    
    :return connection: Global MySQL database connection
    """
    try:
        global connection

        connection = pymysql.connect(
            host='127.0.0.1',
            user=database_username,
            passwd=database_password,
            db=database_name,
            port=tunnel.local_bind_port
        )
        logger.debug("mysql connected successfuly")
    except BaseException as e:
         logger.error("Error while connecting to MySQL", e)


     
def run_query(sql):
    return pd.read_sql_query(sql, connection)

def mysql_disconnect():
    """Closes the MySQL database connection.
    """
    connection.close()
def close_ssh_tunnel():
    """Closes the SSH tunnel connection.
    """ 
    tunnel.close

open_ssh_tunnel()
mysql_connect()

     
# Create a new record

# for x in range(4, 100): 
# cursor=connection.cursor()
# for x in range(904, 55000): 
#     sql = "INSERT INTO Customer_profile (First_name, Last_name, Contact_no, Email, Location) VALUES (%s, %s, %s, %s, %s)"
#     value = [('Morgan','avc','5866000','HR@gamil.com','Mumbai') ]
#     cursor.executemany(sql, value)
#     connection.commit()
#  values = [('Customer_id{}'.format(i), 'First_name{}'.format(i),'Last_name{}'.format(i),'Contact_no{}'.format(i),'Email{}'.format(i),'Location{}'.format(i)) for i in range(0,67)]
# start_time = time.perf_counter ()
# df = run_query("select Devices.ID ,dev_acc_list.r_id,dev_acc_list.user_def_title from Devices  left join dev_acc_list on dev_acc_list.device_id = Devices.ID where Devices.ID = 1102")
# df.head()
# print(df)
# end_time = time.perf_counter ()
# print(end_time - start_time, "seconds")