#!/usr/bin/python3
# coding=utf-8

from bottle import route, run, template, post, request
import MySQLdb
from SetupMinersClass import SetupMiner
from kea_hosts_generane import ConfigClass
import kea_hosts_generane


@route('/')
def server_homepage():
    conf = ConfigClass()
    conf.readall()
    sql = "SELECT " \
          " expire, " \
          " INET_NTOA(address) as ip4, " \
          " HEX(hwaddr)as hw_addr, " \
          " hostname " \
          " FROM lease4 " \
          "where INET_NTOA(address) like '%.%.0.%'" \
          " ORDER BY expire DESC;"
    print(conf.dbconf)
    try:
        conn = MySQLdb.connect(host=conf.dbconf['dbhost'], user=conf.dbconf['dbuser'],
                               passwd=conf.dbconf['dbpass'], db=conf.dbconf['dbbase'])
    except MySQLdb.Error as err:
        print("Connection error: {}".format(err))
        conn.close()
    try:
        cur = conn.cursor(MySQLdb.cursors.DictCursor)
        cur.execute(sql)
        data = cur.fetchall()
    except MySQLdb.Error as err:
        print("Query error: {}".format(err))
    conn.close()
    data_list = []
    for row in data:
        temp = [row['expire'], row['ip4'], row['hw_addr'], row['hostname']]
        data_list.append(temp)
    return template('tpl/main.tpl', A=data_list)


@post('/')
def reboot_hosts():
    conf = ConfigClass()
    conf.readall()
    i = 1
    data = []
    while request.POST.get("reboot_%s" % i, default=False):
        hostname = request.POST.getall('reboot_%s' % i)
        print(hostname)
        sm = SetupMiner(hostname[0], conf.minerconf, telnets=['/sbin/reboot'], sshs=['/sbin/reboot'])
        print(sm.result)
        data.append("host {} log : {}".format(hostname, sm.result))
        i += 1
    return template('tpl/reboot_hosts.tpl', A=data)


@route('/add_hosts')
def server_add_hosts():
    conf = ConfigClass()
    conf.readall()
    sql = "SELECT " \
          " expire, " \
          " INET_NTOA(address) as ip4, " \
          " HEX(hwaddr)as hw_addr, " \
          " hostname " \
          " FROM lease4 " \
          "where INET_NTOA(address) like '%.%.0.%'" \
          " ORDER BY expire DESC;"
    try:
        conn = MySQLdb.connect(host=conf.dbconf['dbhost'], user=conf.dbconf['dbuser'],
                               passwd=conf.dbconf['dbpass'], db=conf.dbconf['dbbase'])
    except MySQLdb.Error as err:
        print("Connection error: {}".format(err))
        conn.close()
    try:
        cur = conn.cursor(MySQLdb.cursors.DictCursor)
        cur.execute(sql)
        data = cur.fetchall()
    except MySQLdb.Error as err:
        print("Query error: {}".format(err))
    conn.close()
    data_list = []
    for row in data:
        temp = [row['expire'], row['ip4'], row['hw_addr'], row['hostname']]
        data_list.append(temp)
    return template('tpl/add_hosts.tpl', A=data_list)


@post('/add_hosts')
def adding_hosts():
    conf = ConfigClass()
    conf.readall()
    s_select = "SELECT " \
          " expire, " \
          " INET_NTOA(address) as ip4, " \
          " HEX(hwaddr)as hw_addr, " \
          " hostname " \
          " FROM lease4 " \
          "where INET_NTOA(address) like '10.100.0%'" \
          " ORDER BY expire DESC;"
    s_insert = "insert into 1c_import (serial, net_num, hw_addr, imported) values ('%s', %s, '%s', 0);"

    i = 1
    data = []
    while request.POST.get("hostname_%s" % i, default=False):
        hostname = request.POST.getall('hostname_%s' % i)
        print(request.POST.getall('hostname_%s' % i))
        if len(hostname) == 3:
            try:
                conn = MySQLdb.connect(host=conf.dbconf['dbhost'], user=conf.dbconf['dbuser'],
                                       passwd=conf.dbconf['dbpass'], db=conf.dbconf['dbbase'])
            except MySQLdb.Error as err:
                data.append("connection error: {} <br>".format(err))
                conn.close()
            try:
                cur = conn.cursor(MySQLdb.cursors.Cursor)
                sql = s_insert % tuple(hostname)
                cur.execute(sql)
                conn.commit()
                data.append("host {} proceed with request : {} <br>".format(hostname[0], sql))
            except MySQLdb.Error as err:
                data.append("host {} Query error: {} <br>".format(hostname[0], err))
            conn.close()
        i += 1
    kea_hosts_generane.main()
    return template('tpl/a_add_hosts.tpl', A=data)


@route('/hosts')
def server_hosts_repo():
    conf = ConfigClass()
    conf.readall()
    sql = "SELECT " \
          "INET_NTOA(hosts.ipv4_address) AS ip4," \
          " hosts.hostname," \
          " HEX(dhcp_identifier)as hw_addr, " \
          "	if(hex(inet_ntoa(lease4.address)), '+','-') as leased " \
          "FROM " \
          " hosts " \
          "LEFT JOIN" \
          " lease4" \
          " on" \
          "	 dhcp_identifier = hwaddr " \
          "ORDER BY ip4;"
    try:
        conn = MySQLdb.connect(host=conf.dbconf['dbhost'], user=conf.dbconf['dbuser'],
                               passwd=conf.dbconf['dbpass'], db=conf.dbconf['dbbase'])
    except MySQLdb.Error as err:
        print("Connection error: {}".format(err))
        conn.close()
    try:
        cur = conn.cursor(MySQLdb.cursors.DictCursor)
        cur.execute(sql)
        data = cur.fetchall()
    except MySQLdb.Error as err:
        print("Query error: {}".format(err))
    conn.close()
    data_list = []
    for row in data:
        temp = list()
        temp.append(row['ip4'])
        temp.append(row['hostname'])
        temp.append(row['hw_addr'])
        temp.append(row['leased'])
        data_list.append(temp)
    return template('tpl/hosts.tpl', A=data_list)


@post('/hosts')
def remove_host():
    conf = ConfigClass()
    s_remove1c = "delete from 1c_import where replace(hw_addr, ':', '') = '%s';"
    s_removehost = "delete from hosts where dhcp_identifier = unhex('%s');"
    hosts_del = request.forms.getall('remove_host')
    del_1c = request.forms.getall('remove_import')
    try:
        conn = MySQLdb.connect(host=conf.dbconf['dbhost'], user=conf.dbconf['dbuser'],
                               passwd=conf.dbconf['dbpass'], db=conf.dbconf['dbbase'])
    except MySQLdb.Error as err:
        print("Connection error: {}".format(err))
        conn.close()
    try:
        cur = conn.cursor(MySQLdb.cursors.Cursor)
        for host in hosts_del:
            try:
                cur.execute(s_removehost % host)
                conn.commit()
            except MySQLdb.Error as err:
                print("Query error: {}".format(err))
            print(s_removehost % host)
        for host in del_1c:
            try:
                cur.execute(s_removehost % host)
                conn.commit()
            except MySQLdb.Error as err:
                print("Query error: {}".format(err))
            try:
                cur.execute(s_remove1c % host)
                conn.commit()
            except MySQLdb.Error as err:
                print("Query error: {}".format(err))

            print(s_remove1c % host)
        conn.commit()
        conn.close()
    except MySQLdb.Error as err:
        print("Query error: {}".format(err))
        conn.close()
    print(request.forms.getall('remove_host'))
    print(request.forms.getall('remove_import'))
    return template('tpl/remove_hosts.tpl')


run(host='0.0.0.0', port=8080, debug=True)