from inventory_provider.db import db

geant_lambda_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'"""

circuit_hierarchy_query = """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"""


retrieve_services_query = """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')"""


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):
    if not record["interface_name"]:
        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"])
    if record["logical_unit"] is not None and record["logical_unit"] != "":
        record["interface_name"] += "." + str(record["logical_unit"])

    if not record["other_end_interface_name"]:
        record["other_end_interface_name"] = record["other_end_card_id"]
        if record["other_end_port"] is not None \
                and record["other_end_port"] != "":
            separator = ""
            if "-" in record["other_end_interface_name"]:
                separator = "/"
            record["other_end_interface_name"] += \
                separator + str(record["other_end_port"])
    if record["other_end_logical_unit"] is not None \
            and record["other_end_logical_unit"] != "":
        record["other_end_interface_name"] += \
            "." + str(record["other_end_logical_unit"])
    return 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
    with db.cursor(connection) as crs:
        crs.execute(geant_lambda_sql)
        r = _convert_to_dict(crs)
    return r


def get_circuits(connection):
    with db.cursor(connection) as crs:
        crs.execute(retrieve_services_query)
        r = _convert_to_dict(crs)

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


def get_circuit_hierarchy(connection):  # pragma: no cover
    with db.cursor(connection) as crs:
        crs.execute(circuit_hierarchy_query)
        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(l, n):
        for x in range(0, len(l), n):
            yield l[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]}