from inventory_provider.db import db

geant_lambda_sql = """SELECT
                      absid AS id,
                      name,
                      LOWER(status) AS status
                    FROM circuit
                    WHERE
                    status != 'terminated'
                    AND service_type = 'geant lambda'"""

circuit_hierarchy_query = """SELECT
                       pc.name AS parent_circuit,
                       pc.absid AS parent_circuit_id,
                       LOWER(pc.status) AS parent_circuit_status,
                       cc.name AS child_circuit,
                       cc.absid AS child_circuit_id,
                       LOWER(cc.status) AS child_circuit_status,
                       cg.segment_group AS segment_group
                     FROM circuit_glue cg
                     INNER JOIN circuit pc ON pc.absid = cg.PTR_circuit
                     INNER JOIN circuit cc ON cc.absid = cg.PTR_component"""


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.name as pop_name,
                      pop.abbreviation as pop_abbreviation,
                      pop_b.name as other_end_pop_name,
                      pop_b.abbreviation as other_end_pop_abbreviation,
                      e.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.card_id) AS card_id,
                      LOWER(ec_b.card_id) AS other_end_card_id,
                      LOWER(
                        IF(pp.interface_name IS NULL,
                        '', pp.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
                        ON pop.absid = cc.PTR_pop_a
                      LEFT JOIN pop pop_b
                        ON pop_b.absid = cc.PTR_pop_b
                      INNER JOIN equipment e
                        ON e.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
                        ON ec.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.manufacturer
                      LEFT JOIN port_plugin pp
                        ON pp.PTR_card = cc.PTR_card_a AND pp.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.name as pop_name,
                      pop.abbreviation as pop_abbreviation,
                      pop_b.name as other_end_pop_name,
                      pop_b.abbreviation as other_end_pop_abbreviation,
                      e.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.card_id) AS card_id,
                      LOWER(ec_b.card_id) AS other_end_card_id,
                      LOWER(
                        IF(pp.interface_name IS NULL,
                        '', pp.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
                        ON pop.absid = cc.PTR_pop_a
                      LEFT JOIN pop pop_b
                        ON pop_b.absid = cc.PTR_pop_b
                      INNER JOIN equipment e
                        ON e.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
                        ON ec.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.manufacturer
                      LEFT JOIN port_plugin pp
                        ON pp.PTR_card = cc.PTR_card_a AND pp.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 circuit_type IN ('path', 'service', 'l2circuit')
                    ORDER BY
                     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()
    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"])
    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_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 {
                'name': r['equipment_name' + suffix],
                'card id': r['card_id' + suffix],
                'port number': r['port' + suffix],
                'pop': {
                    'absid': r['pop_absid' + suffix],
                    '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 {
            'absid': 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