from utils import sqlConnect
import json


def getIntroBoxData():
    connect = sqlConnect.MySQLConnection()
    # taxa number and projects number
    sql_taxa_projects = 'SELECT COUNT(DISTINCT ncbi_taxon_id) taxa_num, COUNT(DISTINCT project_id) project_num FROM curated_lefse_analysis_results'
    taxa_num = connect.queryOne(sql_taxa_projects)['taxa_num']
    project_num = connect.queryOne(sql_taxa_projects)['project_num']
    # phenotype number
    sql_pheno = 'SELECT COUNT(DISTINCT phenotype2) phenotype_num FROM curated_lefse_analysis_results'
    phenotype_num = int(connect.queryOne(sql_pheno)['phenotype_num'])
    # species number
    sql_species = 'SELECT COUNT(DISTINCT ncbi_taxon_id) species_num FROM curated_lefse_analysis_results WHERE taxon_rank_level="species"'
    species_num = connect.queryOne(sql_species)['species_num']
    # genus number
    sql_genus = 'SELECT COUNT(DISTINCT ncbi_taxon_id) genus_num FROM curated_lefse_analysis_results WHERE taxon_rank_level="genus"'
    genus_num = connect.queryOne(sql_genus)['genus_num']
    return {
        'taxa_num': taxa_num,
        'project_num': project_num,
        'phenotype_num': phenotype_num,
        'species_num': species_num,
        'genus_num': genus_num
    }


def getMarkerTaxa(checkedSites):
    connect = sqlConnect.MySQLConnection()
    search = ''
    species_result = []
    for item in checkedSites:
        if item == 'Large intestine':
            search += ("'Rectum'" + ',' + "'Colon'" + ',' + "'Cecum'" + ',')
        elif item == 'Small intestine':
            search += ("'Ileum'" + ',' + "'Duodenum'" + ',')
        else:
            search += ("'%s'" + ',') % (item)
    # print(search)
    search = search.rstrip(',')
    sql_taxa_total = 'SELECT DISTINCT ncbi_taxon_id, taxon_rank_level FROM curated_lefse_analysis_results WHERE BodySite IN ({}) GROUP BY ncbi_taxon_id, taxon_rank_level'.format(
        search)
    taxa_total = connect.query(sql_taxa_total)
    for item in taxa_total:
        # entries 数量为所有结果的数量  project数量  phenotype comparisons 数量是 phenotype2 的数量，因为 phenotype1 只有健康
        sql_num = 'SELECT COUNT(1) entries_num, COUNT(DISTINCT project_id) project_num, COUNT(DISTINCT phenotype2) phenotype_num FROM `curated_lefse_analysis_results` where ncbi_taxon_id={}'.format(
            item['ncbi_taxon_id'])
        num = connect.queryOne(sql_num)
        sql_name = 'SELECT name FROM biosql_taxon_2 WHERE ncbi_taxon_id={}'.format(item['ncbi_taxon_id'])
        name = connect.queryOne(sql_name)['name']
        species_result.append({
            'name': name,
            'ncbi_taxon_id': item['ncbi_taxon_id'],
            'rank': item['taxon_rank_level'],
            'entries': num['entries_num'],
            'project': num['project_num'],
            'pheno_num': num['phenotype_num']
        })
    result = getMarkerPageData(species_result)
    return result


def getMarkerPageData(arr):
    connect = sqlConnect.MySQLConnection()
    for item in arr:
        sql = 'SELECT * FROM `curated_lefse_analysis_results` WHERE ncbi_taxon_id={} ORDER BY lda DESC'.format(
            item['ncbi_taxon_id'])
        result = connect.query(sql)
        item['secondTableData'] = result
        sql_mvp = 'select attributes from cross_db_links where ncbi_taxon_id={}'.format(item['ncbi_taxon_id'])
        mvp = connect.queryOne(sql_mvp)
        if mvp is not None:
            item['ifMvp'] = True
            item['mvpData'] = json.loads(mvp['attributes'])
            # print(type(mvp['attributes']))
        sql_gmrepo = 'select * from gmrepo_species where ncbi_taxon_id={}'.format(item['ncbi_taxon_id'])
        gmrepo = connect.queryOne(sql_gmrepo)
        if gmrepo is not None:
            item['ifGmrepo'] = True
        sql_hmdad = 'select * from hmdad_taxon where ncbi_taxon_id={}'.format(item['ncbi_taxon_id'])
        hmdad = connect.queryOne(sql_hmdad)
        if hmdad is not None:
            item['ifHmdad'] = True
    return arr