from utils import sqlConnect


def introData(site):
    result = []
    siteList = handleSite(site)
    connect = sqlConnect.MySQLConnection()
    # 获取该部位的intro数据
    for item in siteList:
        sql_intro = 'SELECT BodySite, FORMAT(disease_num, 0) disease_num, FORMAT(loaded_run_num, 0) loaded_run_num, FORMAT(QC_0_num, 0) QC_0_num, FORMAT(QC_1_num, 0) QC_1_num, FORMAT(run_num, 0) run_num, FORMAT(species_num, 0) species_num, FORMAT(genus_num, 0) genus_num, FORMAT(project_num, 0) project_num FROM mbodymap_intro WHERE BodySite="{}"'.format(
            item)
        site_intro = connect.queryOne(sql_intro)
        result.append({
            item + 'PhenotypeIntro': site_intro
        })
    return result


def tblData(site):
    result = []
    siteList = handleSite(site)
    connect = sqlConnect.MySQLConnection()
    for site_item in siteList:
        # 获取该部位所有的疾病
        sql_disease = 'SELECT DISTINCT disease FROM mbodymap_samples WHERE BodySite="{}"'.format(site_item)
        site_disease = connect.query(sql_disease)
        site_result = []
        for item in site_disease:
            # 过滤disease=NA 或者disease=Null
            if item['disease'] == 'NA' or item['disease'] is None:
                continue
            # 查询疾病的名字
            sql_disease_name = 'SELECT term FROM mesh_data WHERE uid="{}"'.format(item['disease'])
            disease_name = connect.queryOne(sql_disease_name)['term']
            # 获取run id 和 project_id的数量
            sql_runs = 'SELECT COUNT(run_id) run_id_num, COUNT(DISTINCT project_id) project_id_num FROM mbodymap_samples WHERE disease="{}" AND BodySite="{}"'.format(item['disease'], site_item)
            runs_num = connect.queryOne(sql_runs)['run_id_num']
            sql_runs = 'SELECT COUNT(DISTINCT project_id) project_id_num FROM mbodymap_samples WHERE disease="{}" AND BodySite="{}"'.format(
                item['disease'], site_item)
            project_num = connect.queryOne(sql_runs)['project_id_num']
            # 获取processed valid failed runs的数量
            valid = 0
            # failed = 0
            sql_loaded_runs = 'SELECT accession_id,QCStatus,uid FROM mbodymap_loaded_samples WHERE accession_id IN (SELECT run_id From mbodymap_samples WHERE disease="{}" AND BodySite="{}")'.format(
                item['disease'], site_item)
            loaded_runs = connect.query(sql_loaded_runs)
            processed_num = len(loaded_runs)
            for loaded_run in loaded_runs:
                # valid failed
                if loaded_run['QCStatus'] == 1:
                    valid += 1
                # else:
                #     failed += 1
            # 查询有关species的数量
            sql_species = 'SELECT COUNT(DISTINCT ncbi_taxon_id) from mbodymap_species_to_samples WHERE BodySite="{}" AND disease="{}"'.format(
                site_item, item['disease'])
            species = connect.queryOne(sql_species)['COUNT(DISTINCT ncbi_taxon_id)']
            # 查询有关genus的数量
            sql_genus = 'SELECT COUNT(DISTINCT ncbi_taxon_id) from mbodymap_genus_to_samples WHERE BodySite="{}" AND disease="{}"'.format(
                site_item, item['disease'])
            genus = connect.queryOne(sql_genus)['COUNT(DISTINCT ncbi_taxon_id)']
            site_result.append({
                'disease': item['disease'],
                'disease_name': disease_name,
                'run_num': '{:,}'.format(runs_num),
                'processed_num': processed_num,
                'valid_num': valid,
                # 'failed_num': failed,
                'species_num': species,
                'project_num': project_num,
                'genus_num': genus
            })
            # site_result.append({
            #     'disease': item['disease'],
            #     'disease_name': disease_name,
            #     # 'run_num': runs_num,
            #     'processed_num': '{:,}'.format(processed_num),
            #     'valid_num': '{:,}'.format(valid),
            #     # 'failed_num': failed,
            #     'species_num': '{:,}'.format(species),
            #     'project_num': '{:,}'.format(project_num),
            #     'genus_num': '{:,}'.format(genus)
            # })
        result.append({
            site_item + 'PhenotypeTbl': site_result
        })
    return result


def handleSite(site):
    if site == 'Large intestine':
        return ['Rectum', 'Colon', 'Cecum']
    elif site == 'Small intestine':
        return ['Ileum', 'Duodenum']
    else:
        return [site]


def searchTaxa(searchInput, searchOpt):
    connect = sqlConnect.MySQLConnection()
    if searchOpt == 'id':
        sql = 'SELECT DISTINCT ncbi_taxon_id,taxon_rank_level from mbodymap_relative_species_abundances where ncbi_taxon_id LIKE "{}%" limit 10'.format(
            searchInput)
        searchResult = connect.query(sql)
        for item in searchResult:
            sql_name = 'SELECT name FROM biosql_taxon_2 WHERE ncbi_taxon_id={}'.format(item['ncbi_taxon_id'])
            name = connect.queryOne(sql_name)['name']
            item['name'] = name
    else:
        sql = 'SELECT DISTINCT name, node_rank, ncbi_taxon_id from biosql_taxon_2 where name LIKE "%{}%" LIMIT 20'.format(
            searchInput)
        result = connect.query(sql)
        i = 0
        searchResult = []
        for item in result:
            if i < 10:
                sql_exist = 'SELECT * FROM mbodymap_relative_species_abundances where ncbi_taxon_id="{}"'.format(
                    item['ncbi_taxon_id'])
                exist = connect.queryOne(sql_exist)
                if exist:
                    print(exist)
                    searchResult.append(item)
                    i += 1
    return searchResult


def searchPhenotype(searchInput, searchOpt):
    connect = sqlConnect.MySQLConnection()
    if searchOpt == 'id':
        sql = 'SELECT DISTINCT disease from mbodymap_samples where disease LIKE "{}%" limit 10'.format(searchInput)
        searchResult = connect.query(sql)
        # print(searchResult)
        for item in searchResult:
            sql_name = 'SELECT term FROM mesh_data WHERE uid="{}"'.format(item['disease'])
            name = connect.queryOne(sql_name)['term']
            item['name'] = name
    else:
        sql = 'SELECT DISTINCT term, uid from mesh_data where term LIKE "%{}%" LIMIT 20'.format(searchInput)
        result = connect.query(sql)
        # print('11111')
        i = 0
        searchResult = []
        for item in result:
            if i < 10:
                sql_exist = 'SELECT * FROM mbodymap_samples where disease="{}"'.format(item['uid'])
                exist = connect.queryOne(sql_exist)
                if exist:
                    print(exist)
                    searchResult.append(item)
                    i += 1
    return searchResult


def searchBodysite(searchInput):
    connect = sqlConnect.MySQLConnection()
    sql = 'SELECT BodySite FROM mbodymap_intro WHERE BodySite LIKE "%{}%"'.format(searchInput)
    result = connect.query(sql)
    return result


def searchSample(searchInput):
    connect = sqlConnect.MySQLConnection()
    sql = 'SELECT accession_id FROM mbodymap_loaded_samples WHERE accession_id LIKE "%{}%" LIMIT 10'.format(searchInput)
    result = connect.query(sql)
    return result


def searchProject(searchInput):
    connect = sqlConnect.MySQLConnection()
    sql = 'SELECT `NCBI Project ID` FROM mbodymap_projects WHERE `NCBI Project ID` LIKE "%{}%" LIMIT 10'.format(
        searchInput)
    result = connect.query(sql)
    return result