# encoding: utf-8
from flask import Blueprint, json
from app.model.base import dbconn, graph_driver

bp = Blueprint('neodata', __name__, url_prefix='/neodata')


@bp.route('/t2', methods=['POST', 'GET'])
def t2():
    # graph_sql3 = "match (a:`症状`)-[]->(m:`症状`) where a.name='鼻分泌物多浓' return m,a"
    # graph_sql4 = "match (a:`用途`) return a"

    graph_driver.execute_query(
        "MERGE (a:Person {name: $name}) "
        "MERGE (friend:Person {name: $friend_name}) "
        "MERGE (a)-[:KNOWS]->(friend)",
        name="aaa", friend_name="bbb",
    )

    # graph_sql5 = "g.V().label().dedup()"
    # with graph_driver.session() as session:
    #     graph_data3 = session.run(graph_sql3)
    #     for record in graph_data3:
    #         print(record.data()[0])
    # graph_data4 = graph.run(graph_sql4).data()
    # graph_data5 = graph.run(graph_sql5).data()

    # node_useto = Node("用途", name="测试111")
    # graph.create(node_useto)

    # node_matcher = NodeMatcher(graph)

    # node_treatment = node_matcher.match('用途', name='测试111').first()
    # if node_treatment is None:
    #     node_treatment = Node('用途', name='测试111')
    #     graph.create(node_treatment)
    #
    # node_medicinal = node_matcher.match('药品', name='测试222').first()
    # if node_medicinal is None:
    #     node_medicinal = Node('药品', name='测试222')
    #     graph.create(node_medicinal)
    #
    # link3 = Relationship(node_treatment, '适用药品', node_medicinal)
    #
    # graph.create(link3)

    # graph.create(node_cate2)
    # link1 = Relationship(node_cate, '分类', node_cate2)

    medicinal = {}
    # for val2 in graph_data3:
    #     print(val2)

    # illness_data[key1]['treatment'] = medicinal

    # result.append(illness_data[key1])
    return json.dumps(medicinal)


@bp.route('/neo_symptoms')
def neo_symptoms():

    rows = dbconn.query(
        sql='SELECT isy.cate_id, s.id, s.`name` FROM illness_symptoms as isy left join symptoms as s on isy.symptoms_id = s.id where isy.id > 0'
    )

    for val in rows:
        
        sql2 = """MERGE (a:疾病 {id: $ida, children:3, type:1}) 
                MERGE (b:病症 {id: $idb, name: $name, type:1}) 
                MERGE (a)-[:病症]->(b)
                MERGE (b)-[:疾病]->(a)"""
        
        graph_driver.execute_query(sql2, ida=val['cate_id'], idb=val['id'], name=val['name'])
        
    return '1'


@bp.route('/neo_treatment')
def neo_treatment():

    rows = dbconn.query(
        sql='SELECT it.cate_id, t.id, t.`name`, t.medicinal_data FROM illness_treatment as it left join treatment as t on it.treatment_id = t.id where it.id > 0'
    )

    for val in rows:
        sql2 = """MERGE (a:疾病 {id: $ida, children:3, type:1}) 
                MERGE (b:治疗方案 {id: $idb, name: $name, medicinal_data: $medicinal_data, type:1}) 
                MERGE (a)-[:治疗]->(b)"""
        
        graph_driver.execute_query(sql2, ida=val['cate_id'], idb=val['id'], name=val['name'], medicinal_data=val['medicinal_data'])
        
    return '1'


@bp.route('/neo_treatment_drug')
def neo_treatment_drug():

    rows = dbconn.query(
        sql='SELECT tdr.treatment_id, tdr.drug_id FROM treatment_drug as tdr where id > 0'
    )

    for val in rows:
        sql2 = """MERGE (a:治疗方案 {id: $ida, type:1}) 
                MERGE (b:药品 {id: $idb, type:1}) 
                MERGE (a)-[:用药]->(b)"""
        
        graph_driver.execute_query(sql2, ida=val['treatment_id'], idb=val['drug_id'])
        
    return '1'


@bp.route('/neo_treatment_medicinal')
def neo_treatment_medicinal():

    sql_str = 'SELECT tm.medicinal_id, tm.treatment_id, me.name FROM treatment_medicinal as tm left join medicinal as me on tm.medicinal_id = me.id where tm.id > 0'

    rows = dbconn.query(
        sql=sql_str
    )

    for val in rows:
        sql2 = """MERGE (a:治疗方案 {id: $ida, type:1}) 
                MERGE (b:治疗方案 {id: $idb, name: $name, type:1}) 
                MERGE (a)-[:治疗]->(b)"""
        
        graph_driver.execute_query(sql2, ida=val['treatment_id'], idb=val['medicinal_id'], name=val['name'])

    return '1'


@bp.route('/neo_biochemical')
def neo_biochemical():

    rows = dbconn.query(
        sql='SELECT id, abbreviation, `name`, _range, lab_category_id, biochemical_id FROM lab_category_biochemical where id > 0'
    )

    for val in rows:
                
        if val['_range'] == 'high':
            cn_standard = '高'
        else:
            cn_standard = '低'

        name = val['name'] + '（' + cn_standard + '）'
        
        sql2 = """MERGE (a:疾病 {id: $ida, children:3, type:1}) 
                MERGE (b:检查项 {id: $idb, name: $name, cn_name: $cn_name, abbreviation:$abbreviation, standard:$standard, category_id:$category_id, type:1}) 
                MERGE (a)-[:检查]->(b)"""
        
        graph_driver.execute_query(sql2, ida=val['lab_category_id'], idb=val['biochemical_id'], name=name, cn_name=val['name'],
                                    abbreviation=val['abbreviation'], standard=val['_range'],
                                    category_id=val['lab_category_id'])

    return '1'


@bp.route('/neo_prestext')
def neo_prestext():

    # node_matcher = NodeMatcher(graph)

    # pres = dbconn.query(
    #     sql='SELECT langtext, prescription_id FROM lab_category_prescription where id > 0 group by prescription_id'
    # )

    # for val in pres:
    #     sql2 = """MERGE (a:疾病 {id: $ida, children:3, type:1}) 
    #             MERGE (b:治疗原则 {id: $idb, name: $name, cn_name: $cn_name, abbreviation:$abbreviation, standard:$standard, category_id:$category_id, type:1}) 
    #             MERGE (a)-[:治疗]->(b)"""
        
    #     graph_driver.execute_query(sql2, ida=val['lab_category_id'], idb=val['biochemical_id'], name=name, cn_name=val['name'],
    #                                 abbreviation=val['abbreviation'], standard=val['_range'],
    #                                 category_id=val['lab_category_id'])
    #     node_pres = node_matcher.match('治疗原则', id=val['prescription_id']).first()
    #     if node_pres is None:
    #         continue

    #     text = json.loads(val['langtext'])
    #     node_text = Node('')

    #     link3 = Relationship(node_pres, '治疗', node_text)
    #     graph.create(link3)

    return '1'


@bp.route('/neo_pres')
def neo_pres():

    pres = dbconn.query(
        sql='SELECT id, `name`, lab_category_id, langtext, prescription_id FROM lab_category_prescription where id > 0'
    )

    for val in pres:
        sql2 = """MERGE (a:疾病 {id: $ida, children:3, type:1}) 
                MERGE (b:治疗原则 {id: $idb, name: $name, category_id:$category_id, type:1}) 
                MERGE (a)-[:治疗]->(b)"""
        
        graph_driver.execute_query(sql2, ida=val['lab_category_id'], idb=val['prescription_id'], name=val['name'], category_id=val['lab_category_id'])

    return '1'


@bp.route('/neo_cate2')
def neo_cate2():

    # node_matcher = NodeMatcher(graph)

    # category3 = dbconn.query(
    #     sql='SELECT id, parent_id, `name`, description, examintions, remarks, prognosis, adjuvanttherapy FROM lab_category where id > 0 and children = 3'
    # )

    # for val3 in category3:
    #     # node_cate3 = Node("疾病（三）", id=val3['id'], name=val3['name'], parent_id=val3['parent_id'])

    #     node_cate3 = node_matcher.match('疾病（三）', id=val3['id']).first()
    #     if node_cate3 is None:
    #         node_cate3 = Node("疾病（三）", id=val3['id'], name=val3['name'], parent_id=val3['parent_id'])

        # description = replace_self(val3['description'])
        # if description is not None:
        #     node_description = Node("描述", name=description)
        #     link3 = Relationship(node_cate3, '描述', node_description)
        #     graph.create(link3)
        #
        # examintions = replace_self(val3['examintions'])
        # if examintions is not None:
        #     node_examintions = Node("建议检测项目", name=examintions)
        #     link4 = Relationship(node_cate3, '建议检测项目', node_examintions)
        #     graph.create(link4)
        #
        # remarks = replace_self(val3['remarks'])
        # if remarks is not None:
        #     node_remarks = Node("医嘱", name=remarks)
        #     link5 = Relationship(node_cate3, '医嘱', node_remarks)
        #     graph.create(link5)

        # prognosis = replace_self(val3['prognosis'])
        # if prognosis is not None:
        #     node_prognosis = Node("预后", name=prognosis)
        #     link6 = Relationship(node_cate3, '预后', node_prognosis)
        #     graph.create(link6)

        # adjuvanttherapy = replace_self(val3['adjuvanttherapy'])
        # if adjuvanttherapy is not None:
        #     dict_adj = json.loads(adjuvanttherapy)
        #     str_adj = ''
        #     for adj in dict_adj:
        #         str_adj += adj['servicename'] + '。'

        #     node_adjuvanttherapy = Node("辅助疗法", name=str_adj)
        #     link7 = Relationship(node_cate3, '辅助疗法', node_adjuvanttherapy)
        #     graph.create(link7)

    return '1'


@bp.route('/neo_cate')
def neo_cate():

    category = dbconn.query(
        sql='SELECT id, parent_id, `name`, description, examintions, remarks, prognosis, adjuvanttherapy FROM lab_category where id > 0 and children = 1'
    )

    category2 = dbconn.query(
        sql='SELECT id, parent_id, `name`, description, examintions, remarks, prognosis, adjuvanttherapy FROM lab_category where id > 0 and children = 2'
    )

    category3 = dbconn.query(
        sql='SELECT id, parent_id, `name`, description, examintions, remarks, prognosis, adjuvanttherapy FROM lab_category where id > 0 and children = 3'
    )

    for val in category:
        
        sql1 = 'MERGE (a:疾病 {id: $id, name: $name, parent_id: $parent_id, children:1, type:1}) '
        graph_driver.execute_query(sql1, id=val['id'], name=val['name'], parent_id=val['parent_id'])
        
        for val2 in category2:
            if val2['parent_id'] == val['id']:
                sql2 = """MERGE (a:疾病 {id: $ida, name: $namea, parent_id: $parent_ida, children:1, type:1}) 
                MERGE (b:疾病 {id: $idb, name: $nameb, parent_id: $parent_idb, children:2, type:1}) 
                MERGE (a)-[:分类]->(b)"""
                graph_driver.execute_query(sql2,ida=val['id'], namea=val['name'], parent_ida=val['parent_id'],
                                           idb=val2['id'], nameb=val2['name'], parent_idb=val2['parent_id'])

                for val3 in category3:
                    if val3['parent_id'] == val2['id']:
                        sql2 = """MERGE (a:疾病 {id: $ida, name: $namea, parent_id: $parent_ida, children:2, type:1}) 
                            MERGE (b:疾病 {id: $idb, name: $nameb, parent_id: $parent_idb, description:$description 
                            , examintions:$examintions, remarks:$remarks, prognosis:$prognosis
                            , adjuvanttherapy:$adjuvanttherapy, children:3, type:1}) 
                            MERGE (a)-[:分类]->(b)"""
                        graph_driver.execute_query(sql2,ida=val2['id'], namea=val2['name'], parent_ida=val2['parent_id'],
                                           idb=val3['id'], nameb=val3['name'], parent_idb=val3['parent_id'],
                                           description=replace_self(val3['description']),examintions=replace_self(val3['examintions']),
                                           remarks=replace_self(val3['remarks']),prognosis=replace_self(val3['prognosis']),
                                           adjuvanttherapy=replace_self(val3['adjuvanttherapy']))
                        
    return "1"


@bp.route('/neo_clinical')
def neo_clinical():

    node_matcher = NodeMatcher(graph)

    # clinical_id = dbconn.query(
    #     sql='SELECT id, `name`, `value`, `qz`, lab_category_id FROM lab_clinical where id > 0'
    # )

    # sql='SELECT d.id, d.drug_name,a.res FROM drug as d left join api as a on d.drug_name = a.param where d.id > 943'
    clinical = dbconn.query(
        sql='SELECT id, `name`, `value`, `qz`, lab_category_id, clinical_id FROM lab_clinical where id > 0'
    )

    category = dbconn.query(
        sql='SELECT id, parent_id, `name` FROM lab_category where id > 0 and children = 3'
    )

    for val in clinical:
        # clinical_id = get_clinical(val['name'])
        node_clinical = node_matcher.match('病症', id=val['clinical_id']).first()
        if node_clinical is None:
            node_clinical = Node("病症", id=val['clinical_id'], name=val['name'])

        for val2 in category:
            if val2['id'] == val['lab_category_id']:
                node_cate = node_matcher.match('疾病（三）', id=val2['id']).first()
                if node_cate is None:
                    node_cate = Node("疾病（三）", id=val2['id'], name=val2['name'], parent_id=val2['parent_id'])

                # node_clinical = Node("病症", id=val['id'], name=val['name'], value=val['value'], qz=val['qz'], category_id=val['lab_category_id'])

                link2 = Relationship(node_clinical, '疾病', node_cate)
                link3 = Relationship(node_cate, '病症', node_clinical)

                graph.create(link2)
                graph.create(link3)

    return '1'


@bp.route('/neo_drug')
def neo_drug():

    drug = dbconn.query(
        sql='SELECT d.id, d.name FROM drug as d where d.id > 0 '
    )

    drug_disease = dbconn.query(
        sql='SELECT d.drug_id, d.disease_id, d.usage, d.pet_species FROM drug_disease as d where d.id > 0'
    )

    disease = dbconn.query(
        sql='SELECT d.id, d.name FROM disease as d where d.id > 0'
    )

    disease_data = {}

    for val in disease:
        disease_data[val['id']] = val['name']

    for val2 in drug:

        sql1 = 'MERGE (a:药品 {id: $id, name: $name, type:1})'
        graph_driver.execute_query(sql1, id=val2['id'], name=val2['name'])

        for val3 in drug_disease:
            if val2['id'] == val3['drug_id']:
                
                sql2 = """MERGE (a:药品 {id: $ida, type:1}) 
                        MERGE (b:适应症 {id: $idb, name: $name, type:1}) 
                        MERGE (a)-[:适应症]->(b)
                        MERGE (b)-[:用药]->(a)"""
                
                graph_driver.execute_query(sql2, ida=val2['id'], idb=val3['disease_id'], name=disease_data[val3['disease_id']])
    #             node_disease = node_matcher.match('适应症', id=val3['disease_id']).first()
    #             if node_disease is None:
    #                 node_disease = Node("适应症", id=val3['disease_id'], name=disease_data[val3['disease_id']])

    #             node_pet_species = Node("品种", drug_id=val3['drug_id'], disease_id=val3['disease_id'],
    #                                     name=val3['pet_species'])
    #             # link1 = Relationship(node_drug, '品种', node_pet_species)
    #             link2 = Relationship(node_disease, '品种', node_pet_species)

    #             node_usage = Node("用法用量", drug_id=val3['drug_id'], disease_id=val3['disease_id'], name=val3['usage'])
    #             link3 = Relationship(node_pet_species, '用法用量', node_usage)

    #             link4 = Relationship(node_drug, '适应症', node_disease)
    #             link1 = Relationship(node_disease, '用药', node_drug)

    #             graph.create(link1)
    #             graph.create(link2)
    #             graph.create(link3)
    #             graph.create(link4)

        # node_indication = Node("适应症", name=indication)
        # node_useto = Node("用途", name=useto)
        # node_usage = Node("用法用量", name=usage)
        # node_basis = Node("成分", name=basis)
        # node1_unreactions = Node("不良反应", name=unreactions)
        #
        # link1 = Relationship(node_gen_name, '适应症', node_indication)
        # graph.create(link1)
        #
        # link2 = Relationship(node_gen_name, '用途', node_useto)
        # graph.create(link2)
        #
        # link3 = Relationship(node_gen_name, '用法用量', node_usage)
        # graph.create(link3)
        #
        # link4 = Relationship(node_gen_name, '成分', node_basis)
        # graph.create(link4)
        #
        # link5 = Relationship(node_gen_name, '不良反应', node1_unreactions)
        # graph.create(link5)

    return "1"


def replace_self(str):
    return str.replace(" ", "").replace("\t", "").replace("\n", "")