import sys
import csv
import time
import pexpect
import psycopg2

class RunCmdFromList():

    def run_cmd(self, site_name):
        try:

            #get oam_ip
            site_ip = self._get_remote_region_oam_ip(site_name)
            print('site_ip ' + site_ip) 

            ssh_prefix = 'ssh -o StrictHostKeyChecking=no -t sysadmin@' + site_ip
            raw_cmd = " sudo reboot  "
            cmd1 = ssh_prefix + raw_cmd
            print(cmd1)
            self._execute_cmd_and_save_op(cmd1, raw_cmd, 'xxxxxxx',  additional_password=True)
            print('called ' + raw_cmd + ' for ' + site_name)

        except Exception as e:
            print(e)

    def _execute_cmd_and_save_op(self, cmd, raw_cmd, password,  additional_password=False):
        cmds = []
        #print(cmd)
        cmds.append(cmd)
        all_lines = self._run_command_get_all_lines(cmds, password, additional_password=additional_password)
        print(all_lines)
        print(raw_cmd + ":\n")
        print("-------------------------\n")
        for line in all_lines.split("\n"):
            if 'Connection' not in line:
                print(line)
                print("\n")
        print("\n")

    def _run_command_get_all_lines(self, commands, host_password,  timeout=None, additional_password=False):
        #print("Inside _run_command_get_all_lines")
        all_lines = []
        for command in commands:
            print("Executing.." + command)
            child = pexpect.spawn(command)
            child.timeout=timeout
            try:
                i = child.expect(['password: ','Connection refused\r\r\n'], timeout=timeout)
                if i == 0:
                    child.sendline(host_password)
                    if additional_password:
                        child.expect_exact("Password:")
                        child.sendline(host_password)
                    all_lines = child.read()
                    all_lines = all_lines.rstrip().lstrip()
                    all_lines = all_lines.decode('utf-8').replace('\r\n', '\n')
                    #logger.info(all_lines)
                if i == 1:
                    print("Connection refused")
            except:
                print(str(child))
        return all_lines

    def _get_remote_region_oam_ip(self, cluster_name):

        """ Connect to the PostgreSQL database server """
        conn = None
        oam_ip = None
        try:
            print('Connecting to the PostgreSQL database...')

            conn = psycopg2.connect(
                host='xxxxxxx',
                port='5432',
                database="faredge",
                user="faredge",
                password='xxxxxx')

            # create a cursor
            cur = conn.cursor()

            # execute a statement
            print('select oam_vip_address  from caas_wrbatch where cluster_name=' + site_name )
            sql = 'select oam_vip_address  from caas_wrbatch where cluster_name=' + '\'' + site_name + '\''
            cur.execute(sql)

            #print('PostgreSQL database version:')
            #cur.execute('SELECT version()')

            # display the PostgreSQL database server version
            oam_ip = cur.fetchone()
            print( site_name + ' ' + str(oam_ip))

            # close the communication with the PostgreSQL
            cur.close()
        except (Exception, psycopg2.DatabaseError) as error:
            print(error)
        finally:
            if conn is not None:
                conn.close()
                print('Database connection closed.')

        print(site_name + " OAM IP:" + oam_ip[0])
        return oam_ip[0]


if __name__ == '__main__':

    if len(sys.argv) < 1:
        print("Usage:")
        print("  python site_run_command.py <site_list_csv_file>")
        exit(0)

    site_list_file = sys.argv[1]
    print("Input WR installed site list file:" + site_list_file)
    runCmdFromList = RunCmdFromList()

    with open(site_list_file) as csv_file:
        csv_reader = csv.reader(csv_file, delimiter=',')
        line_count = 0
        for row in csv_reader:
            print(f'\t{row[0]}')
            #site_name = 'wsbomagj-d325030-001';
            site_name = row[0];
            runCmdFromList.run_cmd(site_name)
            line_count += 1
            time.sleep(3)


    print(f'Processed {line_count} lines.')