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)
        # sql_marker_intro = 'select count(distinct ncbi_taxon_id) taxa_num, count(distinct phenotype2) pheno_num, count(distinct project_id) project_num from curated_lefse_analysis_results where bodysite="{}"'.format(item)
        # marker_intro = connect.queryOne(sql_marker_intro)
        # if marker_intro['taxa_num'] == 0:
        #     site_intro['marker'] = False
        # else:
        #     site_intro['marker'] = marker_intro
        result.append({
            item + 'PhenotypeIntro': site_intro
        })
    return result


def tblData(site):
    result = []
    siteList = handleSite(site)
    connect = sqlConnect.MySQLConnection()
    for site_item in siteList:
        has_marker = False
        # 获取该部位所有的疾病
        sql_disease = 'SELECT DISTINCT disease FROM mbodymap_samples WHERE BodySite="{}"'.format(site_item)
        site_disease = connect.query(sql_disease)
        # sql_site_has_marker = 'select * from curated_lefse_analysis_results where bodysite="{}" limit 1'.format(site_item)
        # site_has_marker = connect.queryOne(sql_site_has_marker)
        # if site_has_marker is not None:
        #     has_marker = True
        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)']
            # 疾病部位关联
            sql_association = 'select association from mbodymap_bodysite_disease_association where bodysite="{}" and disease="{}"'.format(
                site_item, item['disease']
            )
            association = connect.queryOne(sql_association)['association']
            # marker
            if has_marker:
                if item['disease'] == 'D006262':
                    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,
                        # 'marker': True,
                        'association': association
                    })
                else:
                    sql_pheno_has_marker = 'select * from curated_lefse_analysis_results where bodysite="{}" and phenotype2="{}"'.format(site_item, item['disease'])
                    pheno_has_marker = connect.queryOne(sql_pheno_has_marker)
                    if pheno_has_marker is None:
                        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,
                            # 'marker': False,
                            'association': association
                        })
                    else:
                        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,
                            # 'marker': True,
                            'association': association
                        })
            else:
                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,
                    # 'marker': False,
                    'association': association
                })
            # 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()
    # print(searchOpt)
    if searchOpt == 'id':
        sql = 'SELECT DISTINCT ncbi_taxon_id, taxon_rank_level, name from mbodymap_taxa 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 ncbi_taxon_id, taxon_rank_level, name from mbodymap_taxa where name like "{}%" limit 10'.format(
            searchInput)
        searchResult = 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 uid, disease_name term from mbodymap_phenotype_index 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 disease uid, disease_name term from mbodymap_phenotype_index where disease_name LIKE "{}%" limit 10'.format(searchInput)
        searchResult = connect.query(sql)
        # 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