import asyncio
import pandas as pd

from aiochclient import ChClient
from aiohttp import ClientSession
import uuid
import lcs
import time
import re

tempTableDelete = "DROP TABLE IF EXISTS datahouse.T_SCI_WOS_2020_ISSN_China_OutPut_Tmp"
tempTableCreate = ("CREATE TABLE datahouse.T_SCI_WOS_2020_ISSN_China_OutPut_Tmp ("
                   " `uuid` String,"
                   " `guid` String,"
                   " `name` Nullable(String),"
                   " `organization` Nullable(String),"
                   " `Second_organization` Nullable(String),"
                   " `email` Nullable(String),"
                   " `reprintauthor` Nullable(String),"
                   " `altname` Nullable(String),"
                   " `country` Nullable(String),"
                   " `firstauthor` Nullable(String),"
                   " `organizationdept` Nullable(String)"
                   " )"
                    " ENGINE = MergeTree"
                    " ORDER BY guid"
                    " SETTINGS index_granularity = 8192"
                   )
RelationDelete = "DROP TABLE IF EXISTS datahouse.T_SCI_WOS_2020_ISSN_China_OutPut_Experts_Relationship"
RelationCreate = ("CREATE TABLE datahouse.T_SCI_WOS_2020_ISSN_China_OutPut_Experts_Relationship ("
                   " `uuid` String,"
                   " `guid` String"
                   " )"
                    " ENGINE = MergeTree"
                    " ORDER BY uuid"
                    " SETTINGS index_granularity = 8192"
                   )

ExpertNewTmpDelete = "DROP TABLE IF EXISTS datahouse.T_SCI_WOS_2020_ISSN_China_OutPut_ExpertNew_Tmp;"
ExpertNewTmpCreate = ("CREATE TABLE datahouse.T_SCI_WOS_2020_ISSN_China_OutPut_ExpertNew_Tmp ("
                      " `uuid` String,"
                      " `guid` String,"
                      " `name` Nullable(String),"
                      " `organization` Nullable(String),"
                      " `Second_organization` Nullable(String),"
                      " `email` Nullable(String),"
                      " `reprintauthor` Nullable(String),"
                      " `altname` Nullable(String),"
                      " `country` Nullable(String),"
                      " `firstauthor` Nullable(String),"
                      " `organizationdept` Nullable(String)"
                      " )"
                      " ENGINE = MergeTree"
                      " ORDER BY uuid"
                      " SETTINGS index_granularity = 8192"
                     )
# 正式专家表
ExpertNewDelete = "DROP TABLE IF EXISTS datahouse.T_SCI_WOS_2020_ISSN_China_OutPut_ExpertNew;"
ExpertNewCreate = ("CREATE TABLE datahouse.T_SCI_WOS_2020_ISSN_China_OutPut_ExpertNew ("
                   " `uuid` String,"
                   " `name` Nullable(String),"
                   " `organization` Nullable(String),"
                   " `Second_organization` Nullable(String),"
                   " `email` Nullable(String),"
                   " `reprintauthor` Nullable(String),"
                   " `altname` Nullable(String),"
                   " `country` Nullable(String),"
                   " `firstauthor` Nullable(String),"
                   " `organizationdept` Nullable(String)"
                   " )"
                    " ENGINE = MergeTree"
                    " ORDER BY uuid"
                    " SETTINGS index_granularity = 8192"
                   )
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
        print(f"Is ClickHouse alive? -> {alive}")
        #step 1
        sql1 = (" select generateUUIDv4() as randomUUID,* from datahouse.T_SCI_WOS_2020_ISSN_China_OutPut_1 where guid in"
                " (select guid from datahouse.T_SCI_WOS_2020_ISSN_China_OutPut_1 where email='shibojiang@fudan.edu.cn')")

        sql1_rows = await client.fetch(sql1)
        df = pd.DataFrame(sql1_rows)
        dfgroupby = df.groupby(['name', 'organization']).size().sort_values(ascending=False)
        for row, index in dfgroupby.T.iteritems():
            print(row[0], row[1], index)


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