

import csv

from fabric_common.core.connection import ConnectionManager
from fabric_common.core.pgsql import RemotePgsql
from fabric_common.core.command import RemoteCommand


from sshtunnel import SSHTunnelForwarder
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy import inspect
import pandas as pd
from operations.core.db import Operations

# https://towardsdatascience.com/how-to-query-postgresql-using-python-with-ssh-in-3-steps-cde626444817
# https://stackoverflow.com/questions/1517635/save-pl-pgsql-output-from-postgresql-to-a-csv-file


class PGSQLMonitoring(Operations):
    def __init__(self, params):
        self.params       = params
        Operations.__init__(self, params = params)

        #print(self.cluster)
        pgres = Postgresql_connect(
            pgres_host='localhost', 
            pgres_port=5432, 
            db='postgres', 
            ssh=True, 
            ssh_user=self.cluster.get('login'), 
            ssh_host=self.cluster.get('host'),
            ssh_pwd = <PASSWORD>'),
            ssh_pkey='')

        """
        a = pgres.schemas(db='pg_database')
        print(a)
        """

        """
        a = pgres.tables(db='postgres',schema='pg_database')
        print(a)
        """

        sql_statement = """
        SELECT datname FROM pg_database
        ;
        """
        query_df = pgres.query(db='postgres', query=sql_statement)
        print(type(query_df))

    #SELECT datname FROM pg_database;


        
        


class Monitoring:
    def __init__(self, cluster, params):
        self.params       = params
        self.cluster = cluster

        self.manager = ConnectionManager(cluster = self.cluster)

        self.cnx = self.manager.connect()
        self.rc = RemoteCommand(conn = self.cnx)

        # pg
        self.rp = RemotePgsql(manager = self.manager)



class Postgresql_connect(object):
    def __init__(self, pgres_host, pgres_port, db, ssh, ssh_user, ssh_host, ssh_pwd, ssh_pkey):
        # SSH Tunnel Variables
        self.pgres_host = pgres_host
        self.pgres_port = pgres_port

        self.psql_user = "postgres"
        self.psql_pass = "<PASSWORD>"
        
        if ssh == True:
            self.server = SSHTunnelForwarder(
                (ssh_host, 22),
                ssh_username=ssh_user,
                ssh_password=<PASSWORD>,
                #ssh_private_key=ssh_pkey,
                remote_bind_address=(pgres_host, pgres_port),
            )
            server = self.server
            server.start() #start ssh server
            self.local_port = server.local_bind_port
            #print(f'Server connected via SSH || Local Port: {self.local_port}...')
        elif ssh == False:
            pass

    def schemas(self, db):
        engine = create_engine(f'postgresql://{self.psql_user}:{self.psql_pass}@{self.pgres_host}:{self.local_port}/{db}')
        inspector = inspect(engine)
        print ('Postgres database engine inspector created...')
        schemas = inspector.get_schema_names()
        self.schemas_df = pd.DataFrame(schemas,columns=['schema name'])
        print(f"Number of schemas: {len(self.schemas_df)}")
        engine.dispose()
        return self.schemas_df
    
    def tables(self, db, schema):
        engine = create_engine(f'postgresql://{self.psql_user}:{self.psql_pass}@{self.pgres_host}:{self.local_port}/{db}')
        inspector = inspect(engine)
        print ('Postgres database engine inspector created...')
        tables = inspector.get_table_names(schema=schema)
        self.tables_df = pd.DataFrame(tables,columns=['table name'])
        print(f"Number of tables: {len(self.tables_df)}")
        engine.dispose()
        return self.tables_df

    def query(self, db, query):
        engine = create_engine(f'postgresql://{self.psql_user}:{self.psql_pass}@{self.pgres_host}:{self.local_port}/{db}')
        #print (f'Database [{db}] session created...')
        self.query_df = pd.read_sql(query,engine)
        #print ('<> Query Sucessful <>')
        engine.dispose()
        return self.query_df