import sys
import pprint
import time
import tabulate
from databaseconnection import DatabaseConnection
from tools import clean_rows
from nm_hostscanner import HostScanner
# from sitedata import SiteData


class ServiceData:

    def __init__(self):
        self.lc = DatabaseConnection()
        self.conn = self.lc.get_db_connection()
        self.s = self.lc.get_schemas()
        self.s = self.s['dvlpmnt_nm']
        self.conn.autocommit = True
        self.cur = self.conn.cursor()

    def close(self):
        self.conn.close()

    def add_(self):
        pass

    def delete_(self, hostid):
        # DELETE FROM dvlpmnt_nm.sites WHERE site_id = in_site_id;
        sql = """DELETE from %s.hosts WHERE host_id = %s;""" % (self.s, hostid)
        try:
            self.cur.execute(sql)
        except Exception as e:
            print(sql)
            print(e)

    def update_(self, hostid, hostname, host_description, host_active):
        print(hostid, hostname, host_description, host_active)
        sql = """UPDATE %s.hosts
        SET
        hostname = '%s',
        host_description = '%s',
        host_active = %s
        WHERE
        host_id = %s;""" % (
            self.s,
            hostname,
            host_description,
            host_active,
            hostid)
        try:
            self.cur.execute(sql)
        except Exception as e:
            print(sql)
            print(e)

    def update_os(self, hostid):
        host = self.get_ip(hostid)
        print("host:", host)
        try:
            o = HostScanner(host, 4, 0)
            o.os_scan_host()
            res = o.hostos
            print(res)
        except:
            return False

        try:
            sql = """UPDATE %s.hosts
            SET
            os_gen = '%s',
            os_family = '%s',
            os_accuracy = %s,
            os_vendor = '%s',
            os_type = '%s'
            WHERE
            host_id = %s;""" % (
                self.s,
                res['osgen'],
                res['osfamily'],
                res['accuracy'],
                res['vendor'],
                res['type'],
                hostid)
            try:
                self.cur.execute(sql)
            except Exception as e:
                print(sql)
                print(e)
        except:
            print("Result from OS detection is invalid.")
            time.sleep(5)

    def list_(self, siteid):
        sql = """SELECT
        hostname AS name,
        host_description as description,
        os_family,
        os_gen,
        host_created AS created,
        interface_last_seen as last_seen,
        interface_mac_address AS MAC,
        ipaddress AS IP,
        host_id as ID
        FROM %s.v_sites_hosts_interfaces_ipaddresses
        WHERE site_id = %s;""" % (
            self.s,
            siteid
        )
        try:
            self.cur.execute(sql)
        except Exception as e:
            print("I can't run query:", e)
            print(sql)
            sys.exit(5)

        colnames = [desc[0] for desc in self.cur.description]
        rows = self.cur.fetchall()
        rows = clean_rows(rows)
        t = tabulate.tabulate(rows, headers=colnames, tablefmt="psql")

        idlist = []
        for r in rows:
            idlist.append(str(r[-1]))

        return t, idlist

    def get_(self, hostid):
        sql = """SELECT *
        FROM %s.hosts
        WHERE host_id = %s;""" % (
            self.s,
            hostid
        )
        try:
            self.cur.execute(sql)
        except:
            print("I can't SELECT from sites")
            sys.exit(5)

        d = {}
        colnames = [desc[0] for desc in self.cur.description]
        rows = self.cur.fetchall()
        for i in range(len(colnames)):
            d[colnames[i]] = rows[0][i]
        d['hostname'] = d['hostname'].replace('\'', '').replace('"', '')
        return d

    def get_ip(self, hostid):
        sql = """SELECT ipaddress
        FROM %s.v_sites_hosts_interfaces_ipaddresses
        WHERE host_id = %s;""" % (
            self.s,
            hostid
        )
        try:
            self.cur.execute(sql)
        except:
            print("I can't SELECT from sites")
            sys.exit(5)

        rows = self.cur.fetchall()
        return rows[0][0].split("/")[0]

    def get_hostlist_to_os_scan(self, siteid):
        sql = """SELECT *
        FROM %s.v_live_hosts_for_os_scan
        WHERE site_id = %s;""" % (
            self.s,
            siteid
        )
        try:
            self.cur.execute(sql)
        except Exception as e:
            print("I can't execute query:", sql)
            print("Error:", e)
            sys.exit(5)

        rows = self.cur.fetchall()
        return rows

    def get_num_of_hosts_to_os_scan(self, siteid):
        sql = """SELECT count(*)
        FROM %s.v_live_hosts_for_os_scan
        WHERE site_id = %s;""" % (
            self.s,
            siteid
        )
        try:
            self.cur.execute(sql)
        except Exception as e:
            print("I can't execute query:", sql)
            print("Error:", e)
            sys.exit(5)

        rows = self.cur.fetchall()
        return rows

    def get_tt_from_host(self, hostid):
        sql = """SELECT site_timing_template
        FROM %s.v_sites_hosts_interfaces_ipaddresses
        WHERE host_id = %s;""" % (
            self.s,
            hostid
        )
        try:
            self.cur.execute(sql)
        except Exception as e:
            print("I can't execute query:", sql)
            print("Error:", e)
            sys.exit(5)

        rows = self.cur.fetchall()
        res = rows[0][0]
        return res

    def get_interfaceid_from_host(self, hostid, ipaddress):
        sql = """SELECT interface_id
        FROM %s.v_sites_hosts_interfaces_ipaddresses
        WHERE host_id = %s and ipaddress = '%s';""" % (
            self.s,
            hostid,
            ipaddress
        )
        try:
            self.cur.execute(sql)
        except Exception as e:
            print("I can't execute query:", sql)
            print("Error:", e)
            sys.exit(5)

        rows = self.cur.fetchall()
        res = rows[0][0]
        return res

    def tcp_scan_host(self, hostid):
        print("Host ID:", hostid)
        ip = self.get_ip(hostid)
        # h = self.get_(hostid)
        pp.pprint(ip)
        tt = self.get_tt_from_host(hostid)
        pp.pprint(tt)

        hs = HostScanner(ip, tt, 0)
        r = hs.tcp_portscan_host()
        pp.pprint(r)

        interfaceid = self.get_interfaceid_from_host(hostid, ip)

        self.scanresult_to_database(r, ip, interfaceid)

    def scanresult_to_database(self, scanresult, host, interfaceid):
        # self.lc = DatabaseConnection()
        # self.conn = self.lc.get_db_connection()
        # self.s = self.lc.get_schemas()
        # self.s = self.s['dvlpmnt_nm']
        # self.conn.autocommit = True
        # self.cur = self.conn.cursor()
        if 'tcp' in scanresult['scan'][host]:
            for ts in scanresult['scan'][host]['tcp']:
                l = scanresult['scan'][host]['tcp'][ts]
                if l['conf'].isdigit():
                    conf = l['conf']
                else:
                    conf = 0
                ps1 = """%s, '%s', %s, %s, '%s', '%s'""" % (
                    interfaceid, 'tcp', ts,
                    conf, l['cpe'], l['extrainfo'])
                ps2 = """'%s', '%s', '%s', '%s', '%s'""" % (
                    l['name'], l['product'], l['reason'],
                    l['state'], l['version'])
                sql = """SELECT %s.create_port_scanning(%s, %s);""" % (
                    self.s, ps1, ps2)
                print(sql)
                try:
                    self.cur.execute(sql)
                except Exception as e:
                    print("I can't run query")
                    print(sql)
                    print(e)
                    sys.exit(5)

        if 'udp' in scanresult['scan'][host]:
            for ts in scanresult['scan'][host]['udp']:
                l = scanresult['scan'][host]['udp'][ts]
                if l['conf'].isdigit():
                    conf = l['conf']
                else:
                    conf = 0
                ps1 = """%s, '%s', %s, %s, '%s', '%s'""" % (
                    interfaceid, 'udp', ts,
                    conf, l['cpe'], l['extrainfo'])
                ps2 = """'%s', '%s', '%s', '%s', '%s'""" % (
                    l['name'], l['product'], l['reason'],
                    l['state'], l['version'])
                sql = """SELECT %s.create_port_scanning(%s, %s);""" % (
                    self.s, ps1, ps2)
                print(sql)
                try:
                    self.cur.execute(sql)
                except Exception as e:
                    print("I can't run query")
                    print(sql)
                    print(e)
                    sys.exit(5)


if __name__ == '__main__':
    print("This is a module.",
          "Please run the lidskjalvmanager in the future.")
    pp = pprint.PrettyPrinter()
    # hostid = 404
    hostid = 11
    # hostid = 730

    o = ServiceData()
    # ip = o.get_ip(hostid)  # decimator
    # print(ip)

    o.tcp_scan_host(hostid)

    # t, idl = o.list_(2)
    # print(t)
    # print(idl)

    # o.update_(4, "Dokumentationsserver", "Dell server", True)
    # o.delete_(53)

    # o.update_os(421)
    # pp.pprint(o.get_(421))
    # pp.pprint(o.get_ip(421))

    # r = o.get_hostlist_to_os_scan(2)
    # pp.pprint(r)
    # r = o.get_num_of_hosts_to_os_scan(2)
    # pp.pprint(r)

    o.conn.close()
