import logging
import re
from collections import defaultdict

from inventory_provider.db import db

logger = logging.getLogger(__name__)


def _convert_to_dict(crs):
    return [dict((crs.description[i][0], "" if value is None else value)
                 for i, value in enumerate(row)) for row in crs.fetchall()]


def _infinera_field_update(record):
    equipment_parts = record["equipment"].rsplit("-", 1)
    card_parts = record["card_id"].split("-", 1)
    record["interface_name"] = ""
    record["equipment"] = equipment_parts[0]
    try:
        record["interface_name"] = equipment_parts[1] + "-"
    except IndexError:
        pass  # Nothing to see here
    try:
        record["interface_name"] += card_parts[1]
    except IndexError:
        record["interface_name"] += card_parts[0]
    if record["port"] is not None and record["port"] != "":
        record["interface_name"] += "-" + record["port"]
    record["interface_name"] = record["interface_name"] \
        .replace("--", "-").upper()

    #

    equipment_parts = record["other_end_equipment"].rsplit("-", 1)
    card_parts = record["other_end_card_id"].split("-", 1)
    record["other_end_interface_name"] = ""
    record["other_end_equipment"] = equipment_parts[0]
    try:
        record["other_end_interface_name"] = equipment_parts[1] + "-"
    except IndexError:
        pass  # Nothing to see here
    try:
        record["other_end_interface_name"] += card_parts[1]
    except IndexError:
        record["other_end_interface_name"] += card_parts[0]
    if record["other_end_port"] is not None and record["other_end_port"] != "":
        record["other_end_interface_name"] += "-" + record["other_end_port"]
    record["other_end_interface_name"] = record["other_end_interface_name"] \
        .replace("--", "-").upper()
    return record


def _juniper_field_update(record):

    def _separator(ifc_name):
        if '-' in ifc_name:
            return '/'
        if '/' in ifc_name:
            return '/'
        return ''

    def _empty(s):
        return s is None or s == ''

    for end in ['', 'other_end_']:
        if not record[end + "interface_name"]:
            record[end + "interface_name"] = record[end + "card_id"]
            if not _empty(record[end + "port"]):
                record[end + "interface_name"] += \
                    _separator(record[end + "interface_name"]) \
                    + str(record[end + "port"])
        if not _empty(record[end + "logical_unit"]):
            record[end + "interface_name"] += \
                "." + str(record[end + "logical_unit"])

    return record


def _cisco_field_update(record):
    return _juniper_field_update(record)


def _coriant_field_update(record):
    record["interface_name"] = record["card_id"]
    if record["port"] is not None and record["port"] != "":
        separator = "/" if "-" in record["interface_name"] else ""
        record["interface_name"] += separator + str(record["port"])
    return record


def _update_fields(r):
    func = globals().get("_" + r["manufacturer"] + "_field_update")
    return func(r) if func else r


def get_geant_lambdas(connection):  # pragma: no cover

    _sql = """
SELECT
  c.absid AS id,
  c.name as name,
  LOWER(c.status) AS status,
  p.short_descr as project
FROM vcircuitconns c
left join events p on p.absid = c.PTR_project
WHERE
    c.status != 'terminated'
    AND c.service_type = 'geant lambda'
"""

    with db.cursor(connection) as crs:
        crs.execute(_sql)
        r = _convert_to_dict(crs)
    return r


def get_fibre_spans(connection):

    _sql = """
SELECT c.absid, c.name,
parent.absid parent_absid, parent.name parent_name,
parent.status parent_status, LOWER(parent.circuit_type) parent_type,
pa.name pop_a, pa.abbreviation pop_abbr_a,
ea.name equipment_a, LOWER(ea.type) eq_type_a,
pb.name pop_b, pb.abbreviation pop_abbr_b,
eb.name equipment_b, LOWER(eb.type) eq_type_b
FROM vcircuitconns c
INNER JOIN pop pa ON pa.absid = c.PTR_pop_a
INNER JOIN pop pb ON pb.absid = c.PTR_pop_b
INNER JOIN equipment ea ON ea.absid = c.PTR_equip_a
INNER JOIN equipment eb ON eb.absid = c.PTR_equip_b
INNER JOIN circuit_glue cg ON c.absid = cg.PTR_component
INNER JOIN circuit parent ON parent.absid = cg.PTR_circuit
WHERE
c.is_circuit = 1 AND c.status != 'terminated' AND parent.status != 'terminated'
AND c.circuit_type = 'fibre span'
"""

    ne_details = {}
    with db.cursor(connection) as crs:
        crs.execute(_sql)
        rows = _convert_to_dict(crs)
    for row in rows:
        if row['parent_type'] != 'fibre route':
            logger.debug(f'Wrong Parent Type c: {row["absid"]} '
                         f'p: {row["parent_absid"]} {row["parent_type"]}')
            continue
        ne_pattern = r'.+-(OLA|DTNX)\d+-\d.*'
        ne_a_match = re.match(ne_pattern, row['equipment_a'])
        ne_b_match = re.match(ne_pattern, row['equipment_b'])
        if ne_a_match:
            ne_details[f'{row["equipment_a"]}_{row["parent_absid"]}'] = {
                'ne': row['equipment_a'],
                'df_route': row['parent_name'],
                'df_route_id': row['parent_absid'],
                'df_status': row['parent_status'],
                'pop': row['pop_a'],
                'pop_abbreviation': row['pop_abbr_a'],
            }
        if ne_b_match:
            ne_details[f'{row["equipment_b"]}_{row["parent_absid"]}'] = {
                'ne': row['equipment_b'],
                'df_route': row['parent_name'],
                'df_route_id': row['parent_absid'],
                'df_status': row['parent_status'],
                'pop': row['pop_b'],
                'pop_abbreviation': row['pop_abbr_b']
            }
    by_ne = defaultdict(lambda: [])
    for d in ne_details.values():
        by_ne[d['ne']].append(d)

    yield from by_ne.items()


def get_circuits(connection):
    _sql = """
SELECT *
FROM (SELECT
  c.absid AS id,
  c.name,
  LOWER(c.status) AS status,
  LOWER(c.circuit_type) AS circuit_type,
  LOWER(c.service_type) AS service_type,
  events.short_descr AS project,
  pop_a.name as pop_name,
  pop_a.abbreviation as pop_abbreviation,
  pop_b.name as other_end_pop_name,
  pop_b.abbreviation as other_end_pop_abbreviation,
  e_a.name AS equipment,
  e_b.name AS other_end_equipment,
  cc.port_a AS port,
  cc.port_b AS other_end_port,
  cc.int_LU_a AS logical_unit,
  cc.int_LU_b AS other_end_logical_unit,
  LOWER(o.name) AS manufacturer,
  LOWER(ec_a.card_id) AS card_id,
  LOWER(ec_b.card_id) AS other_end_card_id,
  LOWER(IF(pp_a.interface_name IS NULL, '', pp_a.interface_name))
    AS interface_name,
  LOWER(IF(pp_b.interface_name IS NULL, '', pp_b.interface_name))
    AS other_end_interface_name
FROM circuit c
  INNER JOIN circuit_connections cc ON cc.circ_absid = c.absid
  LEFT JOIN pop pop_a ON pop_a.absid = cc.PTR_pop_a
  LEFT JOIN pop pop_b ON pop_b.absid = cc.PTR_pop_b
  INNER JOIN equipment e_a ON e_a.absid = cc.PTR_equip_a
  LEFT JOIN equipment e_b ON e_b.absid = cc.PTR_equip_b
  LEFT JOIN events ON events.absid = cc.PTR_project
  INNER JOIN equipment_card ec_a ON ec_a.absid = cc.PTR_card_a
  LEFT JOIN equipment_card ec_b ON ec_b.absid = cc.PTR_card_b
  LEFT JOIN organisation o ON o.absid = ec_a.manufacturer
  LEFT JOIN port_plugin pp_a
    ON pp_a.PTR_card = cc.PTR_card_a AND pp_a.port = cc.port_a
  LEFT JOIN port_plugin pp_b
    ON pp_b.PTR_card = cc.PTR_card_b AND pp_b.port = cc.port_b
WHERE c.status != 'terminated' AND is_circuit = 1
UNION
SELECT
  c.absid AS id,
  c.name,
  LOWER(c.status) AS status,
  LOWER(c.circuit_type) AS circuit_type,
  LOWER(c.service_type) AS service_type,
  events.short_descr AS project,
  pop_b.name as pop_name,
  pop_b.abbreviation as pop_abbreviation,
  pop_a.name as other_end_pop_name,
  pop_a.abbreviation as other_end_pop_abbreviation,
  e_b.name AS equipment,
  e_a.name AS other_end_equipment,
  cc.port_b AS port,
  cc.port_a AS other_end_port,
  cc.int_LU_b AS logical_unit,
  cc.int_LU_a AS other_end_logical_unit,
  LOWER(o.name) AS manufacturer,
  LOWER(ec_b.card_id) AS card_id,
  LOWER(ec_a.card_id) AS other_end_card_id,
  LOWER(IF(pp_b.interface_name IS NULL, '', pp_b.interface_name))
    AS interface_name,
  LOWER(IF(pp_a.interface_name IS NULL, '', pp_a.interface_name))
    AS other_end_interface_name
FROM circuit c
  INNER JOIN circuit_connections cc ON cc.circ_absid = c.absid
  LEFT JOIN pop pop_a ON pop_a.absid = cc.PTR_pop_a
  LEFT JOIN pop pop_b ON pop_b.absid = cc.PTR_pop_b
  LEFT JOIN equipment e_a ON e_a.absid = cc.PTR_equip_a
  INNER JOIN equipment e_b ON e_b.absid = cc.PTR_equip_b
  LEFT JOIN events ON events.absid = cc.PTR_project
  LEFT JOIN equipment_card ec_a ON ec_a.absid = cc.PTR_card_a
  INNER JOIN equipment_card ec_b ON ec_b.absid = cc.PTR_card_b
  LEFT JOIN organisation o ON o.absid = ec_b.manufacturer
  LEFT JOIN port_plugin pp_a
    ON pp_a.PTR_card = cc.PTR_card_a AND pp_a.port = cc.port_a
  LEFT JOIN port_plugin pp_b
    ON pp_b.PTR_card = cc.PTR_card_b AND pp_b.port = cc.port_b
WHERE c.status != 'terminated' AND is_circuit = 1)
AS inner_query
WHERE
    equipment IS NOT NULL
    AND equipment != ''
    AND circuit_type IN (
      'path', 'service', 'l2circuit', 'link-aggr-group')
ORDER BY
name,
 FIELD(status,
  'spare',
  'planned',
  'ordered',
  'installed',
  'operational')
"""

    with db.cursor(connection) as crs:
        crs.execute(_sql)
        r = _convert_to_dict(crs)

    r = list(map(_update_fields, r))
    return r


def get_access_services(connection):
    _sql = """
SELECT
  c.absid AS id,
  c.name,
  pop_a.name as pop_name,
  pop_b.name as other_end_pop_name,
  e_a.name AS equipment,
  e_b.name AS other_end_equipment,
  c.speed_value,
  c.speed_unit
FROM circuit c
  INNER JOIN circuit_connections cc ON cc.circ_absid = c.absid
  LEFT JOIN pop pop_a ON pop_a.absid = cc.PTR_pop_a
  LEFT JOIN pop pop_b ON pop_b.absid = cc.PTR_pop_b
  INNER JOIN equipment e_a ON e_a.absid = cc.PTR_equip_a
  LEFT JOIN equipment e_b ON e_b.absid = cc.PTR_equip_b
WHERE LOWER(c.status) = 'operational'
    AND LOWER(c.circuit_type) = 'service'
    AND LOWER(c.service_type)  = 'ip access'
    AND c.is_circuit = 1
    AND e_a.name IS NOT NULL
    AND e_a.name != ''
ORDER BY name
"""
    with db.cursor(connection) as crs:
        crs.execute(_sql)
        return _convert_to_dict(crs)


def get_circuit_hierarchy(connection):  # pragma: no cover

    _sql = """
SELECT
    pc.name AS parent_circuit,
    pc.absid AS parent_circuit_id,
    pc.circuit_type AS parent_circuit_type,
    LOWER(pc.status) AS parent_circuit_status,
    pp.short_descr AS parent_project,
    cc.name AS child_circuit,
    cc.absid AS child_circuit_id,
    cc.circuit_type AS child_circuit_type,
    LOWER(cc.status) AS child_circuit_status,
    cp.short_descr AS child_project,
    cg.segment_group AS segment_group
FROM circuit_glue cg
INNER JOIN vcircuitconns pc ON pc.absid = cg.PTR_circuit
INNER JOIN vcircuitconns cc ON cc.absid = cg.PTR_component
LEFT JOIN events pp on pp.absid = pc.PTR_project
LEFT JOIN events cp on cp.absid = cc.PTR_project
"""

    with db.cursor(connection) as crs:
        crs.execute(_sql)
        r = _convert_to_dict(crs)
    return r


def lookup_pop_info(connection, equipment_name):
    query = """
SELECT
    e.name AS equipment_name,
    p.name AS pop_name,
    p.abbreviation AS pop_abbreviation,
    p.city AS pop_city,
    p.country AS pop_country,
    g.longitude,
    g.latitude,
    e.status
FROM equipment e
INNER JOIN pop p ON p.absid = e.PTR_pop
LEFT JOIN geocoding g ON g.absid = p.PTR_geocoding
WHERE (e.status = 'Installed' OR e.status = 'Operational')
    AND e.name = %s
ORDER BY FIELD(e.status, 'Operational', 'Installed')
"""

    def _row2rsp(row):
        return {
            'equipment-name': row['equipment_name'],
            'status': row['status'],
            'pop': {
                'name': row['pop_name'],
                'city': row['pop_city'],
                'country': row['pop_country'],
                'abbreviation': row['pop_abbreviation'],
                'longitude': row['longitude'],
                'latitude': row['latitude']
            }
        }

    with db.cursor(connection) as crs:
        crs.execute(query, [equipment_name])
        rows = _convert_to_dict(crs)
        return list([_row2rsp(r) for r in rows])


def lookup_lg_routers(connection):

    # this is literally the definition of the view vlg_routers
    # (cf. https://jira.software.geant.org/projects/LGR/issues/LGR-73)
    # but with the city added and filtering on names instead of absid's
    # for readability

    INTERNAL_POP_NAMES = {
        'Cambridge OC',
        'DANTE Lab',
        'Amsterdam GEANT Office'
    }

    query = """
SELECT
    e.name AS router_name,
    p.name AS pop_name,
    p.city AS pop_city,
    p.abbreviation AS pop_abbreviation,
    g.latitude AS pop_latitude,
    g.longitude AS pop_longitude,
    p.country_code AS pop_country_code,
    g.country AS pop_country,
    g.city AS pop_city
FROM
    opsdb.equipment e
    LEFT JOIN opsdb.pop p ON p.absid = e.PTR_pop
    LEFT JOIN opsdb.geocoding g ON g.absid = p.PTR_geocoding
    LEFT JOIN opsdb.organisation o ON o.absid = e.PTR_owner
WHERE
    e.model LIKE 'mx%'
    AND e.status = 'Operational'
    AND o.name = 'DANTE / GEANT'
    AND NOT (e.name REGEXP 'vpn-proxy|vrr|taas')
"""

    def _row2rsp(row):
        print(row)
        return {
            'equipment name': row['router_name'],
            'type':
                'INTERNAL'
                if row['pop_name'] in INTERNAL_POP_NAMES
                else 'CORE',
            'pop': {
                'name': row['pop_name'],
                'city': row['pop_city'],
                'country': row['pop_country'],
                'country code': row['pop_country_code'],
                'abbreviation': row['pop_abbreviation'],
                'longitude': row['pop_longitude'],
                'latitude': row['pop_latitude'],
            }
        }

    with db.cursor(connection) as crs:
        crs.execute(query)
        rows = _convert_to_dict(crs)
        return list([_row2rsp(r) for r in rows])


def lookup_coriant_path(connection, equipment_name, card_id, port_number):

    base_query = """
SELECT

    vcc.absid,
    vcc.category,
    vcc.circuit_type,
    vcc.service_type,
    vcc.peering_type,
    vcc.status,
    vcc.name,
    eq_a.name as equipment_name_a,
    eq_b.name as equipment_name_b,
    eqc_a.card_id as card_id_a,
    eqc_a.card_id as card_id_b,
    pop_a.name as pop_name_a,
    pop_b.name as pop_name_b,
    pop_a.abbreviation as pop_abbreviation_a,
    pop_b.abbreviation as pop_abbreviation_b,
    pop_a.absid as pop_absid_a,
    pop_b.absid as pop_absid_b,
    pop_a.city as pop_city_a,
    pop_b.city as pop_city_b,
    pop_a.country as pop_country_a,
    pop_b.country as pop_country_b,
    g_a.longitude as pop_longitude_a,
    g_a.latitude as pop_latitude_a,
    g_b.longitude as pop_longitude_b,
    g_b.latitude as pop_latitude_b,
    vcc.port_a,
    vcc.port_b

 FROM vcircuitconns vcc
    LEFT JOIN equipment eq_a ON eq_a.absid = vcc.PTR_equip_a
    LEFT JOIN equipment eq_b ON eq_b.absid = vcc.PTR_equip_b
    LEFT JOIN equipment_card eqc_a ON eqc_a.absid = vcc.PTR_card_a
    LEFT JOIN equipment_card eqc_b ON eqc_b.absid = vcc.PTR_card_b
    LEFT JOIN pop pop_a ON pop_a.absid = vcc.PTR_pop_a
    LEFT JOIN pop pop_b ON pop_b.absid = vcc.PTR_pop_b
    LEFT JOIN geocoding g_a ON g_a.absid = pop_a.PTR_geocoding
    LEFT JOIN geocoding g_b ON g_b.absid = pop_b.PTR_geocoding
"""

    def _fields2rsp(row):

        def _pop(r, suffix):
            return {
                'equipment name': r['equipment_name' + suffix],
                'card id': r['card_id' + suffix],
                'port number': r['port' + suffix],
                'pop': {
                    'name': r['pop_name' + suffix],
                    'city': r['pop_city' + suffix],
                    'country': r['pop_country' + suffix],
                    'abbreviation': r['pop_abbreviation' + suffix],
                    'longitude': r['pop_longitude' + suffix],
                    'latitude': r['pop_latitude' + suffix]
                }
            }

        return {
            'id': row['absid'],
            'category': row['category'],
            'circuit_type': row['circuit_type'],
            'service_type': row['service_type'],
            'peering_type': row['peering_type'],
            'status': row['status'],
            'name': row['name'],
            'a': _pop(row, '_a'),
            'b': _pop(row, '_b'),
        }

    circuit_query = base_query + """
WHERE vcc.status <> 'Terminated'
    AND (
        (eq_a.name = %(equipment_name)s
            AND eqc_a.card_id = %(card_id)s
            AND vcc.port_a = %(port_number)s )
        OR
        (eq_b.name = %(equipment_name)s
            AND eqc_b.card_id = %(card_id)s
            AND vcc.port_b = %(port_number)s ))

ORDER BY FIELD(vcc.circuit_type, 'path') DESC
    """

    parent_query = base_query + """
JOIN circuit_glue
    ON circuit_glue.PTR_circuit = vcc.absid
WHERE circuit_glue.PTR_component = %s
AND circuit_type = 'Path'
    """

    #     for_testing = """
    # SELECT parent.absid, child.absid,
    #        parent.circuit_type as parent_circuit_type,
    #        child.circuit_type as child_circuit_type,
    #        eq_a.name as equipment_name_a,
    #        eq_b.name as equipment_name_b,
    #        eqc_a.card_id as card_id_a,
    #        eqc_a.card_id as card_id_b,
    #        pop_a.name as pop_name_a,
    #        pop_b.name as pop_name_b,
    #        parent.port_a as port_a,
    #        parent.port_b as port_b,
    #        child.port_a as child_port_a,
    #        child.port_b as child_port_b
    #
    # FROM vcircuitconns parent
    #     RIGHT JOIN circuit_glue
    #         ON circuit_glue.PTR_circuit = parent.absid
    #     JOIN vcircuitconns child
    #         ON circuit_glue.PTR_component = child.absid
    #
    # LEFT JOIN equipment eq_a ON eq_a.absid = parent.PTR_equip_a
    # LEFT JOIN equipment eq_b ON eq_b.absid = parent.PTR_equip_b
    # LEFT JOIN equipment_card eqc_a ON eqc_a.absid = parent.PTR_card_a
    # LEFT JOIN equipment_card eqc_b ON eqc_b.absid = parent.PTR_card_b
    # LEFT JOIN pop pop_a ON pop_a.absid = parent.PTR_pop_a
    # LEFT JOIN pop pop_b ON pop_b.absid = parent.PTR_pop_b
    #
    # WHERE parent.circuit_type = 'Path'
    # AND child.circuit_type <> 'Path'
    # AND child.status = 'Operational'
    # AND (eq_a.name like 'grv%' or eq_b.name like 'grv%')
    #     """

    args = {
        'equipment_name': equipment_name,
        'card_id': card_id,
        'port_number': port_number
    }

    with db.cursor(connection) as crs:
        crs.execute(circuit_query, args)
        r = _convert_to_dict(crs)
        if not r:
            return None
        circuit = r[0]
        if circuit['circuit_type'].lower() == 'path':
            return _fields2rsp(circuit)

        crs.execute(parent_query, [circuit['absid']])
        r = _convert_to_dict(crs)
        return _fields2rsp(r[0]) if r else None


def get_service_users(connection, service_ids):

    def _sublists(all, n):
        for x in range(0, len(all), n):
            yield all[x:x + n]

    # not sure how to use a tuple in a prepared statement,
    # so this is just doing a dumb string replacement ...
    query = (
        'select c.absid, o.name'
        ' from organisation o'
        ' join circuit_orgs co on co.org_absid=o.absid'
        ' join circuit c on c.absid=co.circ_absid'
        ' where co.org_type=\'Circuit User\''
        ' and c.absid in (%s)')

    with db.cursor(connection) as crs:
        for chunk in _sublists(service_ids, 20):
            crs.execute(query % ','.join([str(x) for x in chunk]))
            for r in crs.fetchall():
                yield {'service_id': r[0], 'user': r[1]}