import asyncio

from aiochclient import ChClient
from aiohttp import ClientSession
import lcs
import uuid as UUID


async def main():
    async with ClientSession() as s:
        client = ChClient(s, url='http://115.29.55.141:8123/', user='zxcs', password='123123', database='datahouse')
        alive = await client.is_alive()  # returns True if connection is Ok

        # step5
        await client.fetch(
            "ALTER TABLE datahouse.T_SCI_WOS_2020_ISSN_China_OutPut_ExpertNew ADD COLUMN keywords String;")
        await client.fetch(
            "ALTER TABLE datahouse.T_SCI_WOS_2020_ISSN_China_OutPut_ExpertNew ADD COLUMN subjects String;")
        await client.fetch(
            "ALTER TABLE datahouse.T_SCI_WOS_2020_ISSN_China_OutPut_ExpertNew ADD COLUMN journals String;")

        sql8 = (
                " SELECT email from datahouse.T_SCI_WOS_2020_ISSN_China_OutPut_ExpertNew " +
                " where email is not null and email !='' " +
                " group by email " +
                " HAVING count(*) > 1 "
        )
        sql8_rows = await client.fetch(sql8)

        if sql8_rows is not None and len(sql8_rows) > 0:
            for sql8_rows_item in sql8_rows:
                if sql8_rows_item != '':
                    sql8_1 = (
                            " SELECT " +
                            " `uuid`,`name`,`organization`,`Second_organization`,`email`,`reprintauthor`,`altname`,`country`,`firstauthor`,`organizationdept`,`keywords`,`subjects`,`journals` " +
                            " from datahouse.T_SCI_WOS_2020_ISSN_China_OutPut_ExpertNew " +
                            " where email ='" + str(sql8_rows_item['email']) + "' ORDER BY LENGTH(name) DESC"
                    )
                    sql8_1_rows = await client.fetch(sql8_1)
                    if sql8_1_rows is not None and len(sql8_1_rows) > 0:
                        sameArr = []
                        for j in range(len(sql8_1_rows)):
                            itemj = sql8_1_rows[j]
                            namej = itemj['name']
                            for k in range(len(sql8_1_rows)):
                                if k > j:
                                    itemk = sql8_1_rows[k]
                                    namek = itemk['name']
                                    if namej == namek or lcs.isLCS(namej, namek):
                                        if itemj not in sameArr:
                                            sameArr.append(itemj)
                                        if itemk not in sameArr:
                                            sameArr.append(itemk)

                        if len(sameArr) > 0:
                            # uuid = str(sameArr[0]['uuid'] or '')
                            uuid = str(UUID.uuid4())
                            uuidArr = []
                            newOrganization = ''
                            for j in range(len(sameArr)):
                                sameItem = sameArr[j]
                                newOrganization += sameItem['organization']
                                if j < len(sameArr) - 1:
                                    newOrganization += ','
                                itemUuid = str(sameItem['uuid'])
                                uuidArr.append(itemUuid)

                            sql8_2 = (
                                    " ALTER TABLE datahouse.T_SCI_WOS_2020_ISSN_China_OutPut_ExpertNew " +
                                    " DELETE WHERE uuid in ("
                            )
                            sql8_3 = (
                                    "insert into datahouse.T_SCI_WOS_2020_ISSN_China_OutPut_ExpertNew values(" +
                                    "'" + uuid + "'," +
                                    "'" + str(sameArr[0]['name'] or '') + "'," +
                                    "'" + str(newOrganization) + "'," +
                                    "'" + str(sameArr[0]['Second_organization'] or '') + "'," +
                                    "'" + str(sameArr[0]['email'] or '') + "'," +
                                    "'" + str(sameArr[0]['reprintauthor'] or '') + "'," +
                                    "'" + str(sameArr[0]['altname'] or '') + "'," +
                                    "'" + str(sameArr[0]['country'] or '') + "'," +
                                    "'" + str(sameArr[0]['firstauthor'] or '') + "'," +
                                    "'" + str(sameArr[0]['organizationdept'] or '') + "'," +
                                    "''," +
                                    "''," +
                                    "''" +
                                    ")"
                            )
                            sql8_4 = (
                                    " SELECT " +
                                    " `uuid`,`guid`" +
                                    " FROM datahouse.T_SCI_WOS_2020_ISSN_China_OutPut_Experts_Relationship " +
                                    " WHERE uuid in ("
                            )
                            sql8_5 = (
                                    " ALTER TABLE datahouse.T_SCI_WOS_2020_ISSN_China_OutPut_Experts_Relationship " +
                                    " DELETE WHERE uuid in ("
                            )
                            for j in range(len(uuidArr)):
                                itemUuid = uuidArr[j]
                                itemSql = "'" + itemUuid + "'"
                                if j < len(uuidArr) - 1:
                                    itemSql += ','
                                sql8_2 += itemSql
                                sql8_4 += itemSql
                                sql8_5 += itemSql
                            sql8_2 += ')'
                            # 删除专家
                            # print(f"sql8_2 -> {sql8_2}")
                            await client.execute(sql8_2)
                            # 新增新专家
                            # print(f"sql8_3 -> {sql8_3}")
                            await client.execute(sql8_3)

                            sql8_4 += ')'
                            # 查询专家关系
                            sql8_4_rows = await client.fetch(sql8_4)
                            if sql8_4_rows is not None:
                                sql8_5 += ')'
                                # 删除旧专家关系
                                await client.execute(sql8_5)

                                sql8_6 = (
                                    "insert into datahouse.T_SCI_WOS_2020_ISSN_China_OutPut_Experts_Relationship values"
                                )
                                for j in range(len(sql8_4_rows)):
                                    item = sql8_4_rows[j]
                                    guid = str(item['guid'])
                                    sql8_6_item = "('" + uuid + "','" + guid + "')"
                                    if j < len(sql8_4_rows) - 1:
                                        sql8_6_item += ','
                                    sql8_6 += sql8_6_item
                                await client.execute(sql8_6)
        print('step 5 finish')
        # step6
        sql9_1 = ("SELECT uuid FROM datahouse.T_SCI_WOS_2020_ISSN_China_OutPut_ExpertNew")
        # print(f"sql9_1 -> {sql9_1}")
        sql9_1_rows = await client.fetch(sql9_1)
        for sql9_1_row in sql9_1_rows:
            _uuid = sql9_1_row["uuid"]
            sql9_2 = ("SELECT guid,DE,SC,SO FROM datahouse.T_SCI_WOS_2020_ISSN_China WHERE guid in ("
                      "SELECT guid FROM datahouse.T_SCI_WOS_2020_ISSN_China_OutPut_Experts_Relationship "
                      "WHERE uuid = '" + _uuid + "')")

            sql9_2_rows = await client.fetch(sql9_2)
            keywords = {}
            subjects = {}
            journals = {}
            for sql9_2_row in sql9_2_rows:
                de = sql9_2_row["DE"]
                if de is not None and de != '':
                    if keywords.get(de) is None:
                        keywords[de] = 1
                    else:
                        keywords[de] = keywords[de] + 1

                sc = sql9_2_row["SC"]
                if sc is not None and sc != '':
                    if subjects.get(sc) is None:
                        subjects[sc] = 1
                    else:
                        subjects[sc] = subjects[sc] + 1

                so = sql9_2_row["SO"]
                if so is not None and so != '':
                    if journals.get(so) is None:
                        journals[so] = 1
                    else:
                        journals[so] = journals[so] + 1

            sql9_2 = (
                    " ALTER TABLE datahouse.T_SCI_WOS_2020_ISSN_China_OutPut_ExpertNew " +
                    " UPDATE " +
                    " keywords='" + ",".join(keywords.keys()) + "' ," +
                    " subjects = '" + ",".join(subjects.keys()) + "' ," +
                    " journals='" + ",".join(journals.keys()) + "' " +
                    " where uuid = '" + _uuid + "' "
            )
            # print(f"sql9_2 -> {sql9_2}")
            await client.execute(sql9_2)

        print('step 6 finish')




if __name__ == '__main__':
    loop = asyncio.get_event_loop()
    loop.run_until_complete(main())