# -*- coding: utf-8 -*-
##----------------------------------------------------------------------
## Add new link from /tmp/mac-port.csv to sa_client_links
##----------------------------------------------------------------------
## Copyright (C) 2007-2011 The NOC Project
## See LICENSE for details
##----------------------------------------------------------------------

# Python modules
import os
# NOC modules
import set_staticvlan_env
from noc.staticvlan.models import connect, mysql_connect

c = connect(); conn = c[0]; cur = c[1]
c = mysql_connect(); mysql_conn = c[0]; mysql_cur = c[1]

mysql_cur.execute("SELECT * FROM `netup`.`user_mac`")
clients = mysql_cur.fetchall()
mysql_cur.close()
mysql_conn.close()

#clients_links = open('/tmp/mac-port.csv', 'r').split(';\n')
#clients_links.close()
clients_links = os.popen('sort /tmp/mac-port.csv').read().split('\r\n')

# Записываем связи:
k=-1
for i in range(len(clients_links)-1):
    cli_link = clients_links[i].split(',')
    mac = cli_link[2]
    for j in range(len(clients)):
        if mac == clients[j][3]:
            k=j
            break
    if k <> -1:
        switch = cli_link[0]
        port = cli_link[1]
        # Fix for DLink DGS-3100-24TG
        if len(port.split(':')) > 1:
            port = port.split(':')[0] + '00' + port.split(':')[1]

        id_cli = clients[k][0]
        id_account = clients[k][1]
        id_s_link = clients[k][2]
#       name = clients[k][4]

        cur.execute( "SELECT id FROM sa_managedobject WHERE address = '%s'"%switch )
        mo_id = cur.fetchall()[0][0]

        cur.execute( 'SELECT id FROM sa_client_links WHERE switch_id = %s and port = %s', ( mo_id, port ) )
        cli_link_id = cur.fetchall()
        cur.execute( "SELECT id FROM sa_client_links WHERE mac = '%s'"%mac )
        cli_mac_id = cur.fetchall()
        cur.execute( 'SELECT id FROM sa_managedobject_links WHERE switch1_id = %s and port1 = %s', ( mo_id, port ) )
        switch1_link_id = cur.fetchall()
        cur.execute( 'SELECT id FROM sa_managedobject_links WHERE switch2_id = %s and port2 = %s', ( mo_id, port ) )
        switch2_link_id = cur.fetchall()

        if not switch1_link_id and not switch2_link_id:
            if not cli_mac_id and not cli_link_id:
                cur.execute( "INSERT INTO sa_client_links (switch_id, port, mac, id_cli, id_account, id_s_link, name, properties ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s )", ( mo_id, port, mac, id_cli, id_account, id_s_link, 'Client', 'auto discovery' ) )

            elif not cli_mac_id and cli_link_id:
                cli_link_id = cli_link_id[0]
                cur.execute( "UPDATE sa_client_links SET mac=%s, id_cli=%s, id_account=%s, id_s_link=%s  WHERE id=%s", ( mac, id_cli, id_account, id_s_link, cli_link_id ) )

            elif cli_mac_id and not cli_link_id:
                cli_mac_id = cli_mac_id[0][0]
                cur.execute( "DELETE FROM sa_client_links WHERE id = %s"%str(cli_mac_id) )
                cur.execute( "INSERT INTO sa_client_links (switch_id, port, mac, id_cli, id_account, id_s_link, name, properties ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s )", ( mo_id, port, mac, id_cli, id_account, id_s_link, 'Client', 'auto discovery' ) )

            elif cli_mac_id and cli_link_id and cli_mac_id <> cli_link_id:
                cli_mac_id = cli_mac_id[0][0]
                cli_link_id = cli_link_id[0]
                cur.execute( "DELETE FROM sa_client_links WHERE id = %s"%str(cli_mac_id) )
                cur.execute( "UPDATE sa_client_links SET mac=%s, id_cli=%s, id_account=%s, id_s_link=%s  WHERE id=%s", ( mac, id_cli, id_account, id_s_link, cli_link_id ) )
        k=-1

conn.commit()
cur.close()
conn.close()