from service.taxonsToSiteService import getSpeciesData, getGenusData
from utils import sqlConnect


# def getSpecies(checkedSites, page, limit, propName, propOrder):
#     connect = sqlConnect.MySQLConnection()
#     search = ''
#     result = []
#     for item in checkedSites:
#         if item == 'The large intestine':
#             search += ("'Rectum'" + ',' + "'Colon'" + ',' + "'Cecum'" + ',')
#         elif item == 'The small intestine':
#             search += ("'Ileum'" + ',' + "'Duodenum'" + ',')
#         else:
#             search += ("'%s'" + ',') % (item)
#     search = search.rstrip(',')
#     sql_species = 'SELECT * FROM mbodymap_species_to_site WHERE BodySite IN ({}) AND loaded_uid_num>1 AND med>=0.0001 ORDER BY {} {}'.format(
#         search, propName, propOrder)
#     print(sql_species)
#     species = connect.query(sql_species)
#     total = len(species)
#     if page == 1:
#         i = 0
#         for item in species:
#             if i <= 9:
#                 taxon_phenotype = getSpeciesPhenotype(item['ncbi_taxon_id'], item['BodySite'])
#                 result.append({
#                     'BodySite': item['BodySite'],
#                     'loaded_uid_num': item['loaded_uid_num'],
#                     'med': item['med'],
#                     'name': item['name'],
#                     'ncbi_taxon_id': item['ncbi_taxon_id'],
#                     'site_num': item['site_num'],
#                     'phenotype': taxon_phenotype
#                 })
#                 i += 1
#     else:
#         i = 0
#         for item in species:
#             if i <= (page - 1) * limit - 1:
#                 i += 1
#                 continue
#             if (page - 1) * limit - 1 < i <= page * limit - 1:
#                 taxon_phenotype = getSpeciesPhenotype(item['ncbi_taxon_id'], item['BodySite'])
#                 result.append({
#                     'BodySite': item['BodySite'],
#                     'loaded_uid_num': item['loaded_uid_num'],
#                     'med': item['med'],
#                     'name': item['name'],
#                     'ncbi_taxon_id': item['ncbi_taxon_id'],
#                     'site_num': item['site_num'],
#                     'phenotype': taxon_phenotype
#                 })
#                 i += 1
#             if i > page * limit - 1:
#                 break
#     return result, total


def getSpecies(checkedSites, page, limit, propName, propOrder, secondName, secondOrder):
    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)
    search = search.rstrip(',')
    sql_species = 'SELECT DISTINCT ncbi_taxon_id, name, site_num, SUM(loaded_uid_num) sample_num FROM mbodymap_species_to_site WHERE BodySite IN ({}) AND loaded_uid_num>1 AND med>=0.0001 GROUP BY ncbi_taxon_id, name, site_num ORDER BY {} {}'.format(
        search, propName, propOrder)
    species = connect.query(sql_species)
    total = len(species)
    if page == 1:
        i = 0
        for item in species:
            if i <= 9:
                species_result.append({
                    'name': item['name'],
                    'ncbi_taxon_id': item['ncbi_taxon_id'],
                    'site_num': item['site_num'],
                    'sample_num': '{:,}'.format(int(item['sample_num']))
                })
                i += 1
    else:
        i = 0
        for item in species:
            if i <= (page - 1) * limit - 1:
                i += 1
                continue
            if (page - 1) * limit - 1 < i <= page * limit - 1:
                species_result.append({
                    'name': item['name'],
                    'ncbi_taxon_id': item['ncbi_taxon_id'],
                    'site_num': item['site_num'],
                    'sample_num': '{:,}'.format(int(item['sample_num']))
                })
                i += 1
            if i > page * limit - 1:
                break
    result = getTaxonsPageSpeciseData(species_result, secondName, secondOrder)
    return result, total


def getTaxonsPageSpeciseData(arr, secondName, secondOrder):
    connect = sqlConnect.MySQLConnection()
    for item in arr:
        sql = 'SELECT * FROM mbodymap_species_to_site_relative_abundance WHERE ncbi_taxon_id={} ORDER BY {} {}'.format(
            item['ncbi_taxon_id'], secondName, secondOrder)
        result = connect.query(sql)
        item['secondTableData'] = result
    return arr


def getTaxonsPageGenusData(arr, secondName, secondOrder):
    connect = sqlConnect.MySQLConnection()
    for item in arr:
        sql = 'SELECT * FROM mbodymap_genus_to_site_relative_abundance WHERE ncbi_taxon_id={} ORDER BY {} {}'.format(
            item['ncbi_taxon_id'], secondName, secondOrder)
        print(sql)
        result = connect.query(sql)
        item['secondTableData'] = result
    return arr


def getSpeciesIntro():
    connect = sqlConnect.MySQLConnection()
    sql_sample_num = 'SELECT SUM(loaded_run_num) sample_num FROM mbodymap_intro'
    sample_num = int(connect.queryOne(sql_sample_num)['sample_num'])
    sql_species_num = 'SELECT COUNT(DISTINCT ncbi_taxon_id) species_num FROM mbodymap_species_to_site'
    species_num = connect.queryOne(sql_species_num)['species_num']
    sql_genus_num = 'SELECT COUNT(DISTINCT ncbi_taxon_id) genus_num FROM mbodymap_genus_to_site'
    genus_num = connect.queryOne(sql_genus_num)['genus_num']
    sql_filter_species_num = 'SELECT COUNT(DISTINCT ncbi_taxon_id) species_num FROM mbodymap_species_to_site WHERE loaded_uid_num>1 AND med>=0.0001'
    filter_species_num = connect.queryOne(sql_filter_species_num)['species_num']
    sql_filter_genus_num = 'SELECT COUNT(DISTINCT ncbi_taxon_id) genus_num FROM mbodymap_genus_to_site WHERE loaded_uid_num>1 AND med>=0.0001'
    filter_genus_num = connect.queryOne(sql_filter_genus_num)['genus_num']
    species_intro = {
        'sample_num': '{:,}'.format(sample_num),
        'species_num': '{:,}'.format(species_num),
        'genus_num': '{:,}'.format(genus_num),
        'filter_species_num': '{:,}'.format(filter_species_num),
        'filter_genus_num': '{:,}'.format(filter_genus_num)
    }
    return species_intro


def getSpeciesPhenotype(ncbi_taxon_id, site):
    connect = sqlConnect.MySQLConnection()
    result = []
    sql_phenotype = 'SELECT disease, loaded_uid_num_QC1, relative_abundance_std, relative_abundance_avg, relative_abundance_med FROM mbodymap_species_to_samples WHERE ncbi_taxon_id={} AND BodySite="{}"'.format(
        ncbi_taxon_id, site)
    phenotypes = connect.query(sql_phenotype)
    for phenotype in phenotypes:
        sql_total_disease_of_taxon_in_site = 'SELECT COUNT(1) total FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE disease="{}" AND BodySite="{}" AND t1.run_id=t2.accession_id AND t2.QCStatus=1'.format(
            phenotype['disease'], site)
        total_disease_of_taxon_in_site = connect.queryOne(sql_total_disease_of_taxon_in_site)['total']
        sql_disease_name = 'SELECT term FROM mesh_data WHERE uid="{}"'.format(phenotype['disease'])
        name = connect.queryOne(sql_disease_name)['term']
        result.append({
            'phenotype': name,
            'mesh_id': phenotype['disease'],
            'loaded_uid_num': phenotype['loaded_uid_num_QC1'],
            'std': phenotype['relative_abundance_std'],
            'avg': phenotype['relative_abundance_avg'],
            'med': phenotype['relative_abundance_med'],
            'total': int(total_disease_of_taxon_in_site)
        })
    return result


def getGenus(checkedSites, page, limit, propName, propOrder, secondName, secondOrder):
    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)
    search = search.rstrip(',')
    sql_species = 'SELECT DISTINCT ncbi_taxon_id, name, site_num, SUM(loaded_uid_num) sample_num FROM mbodymap_genus_to_site WHERE BodySite IN ({}) AND loaded_uid_num>1 AND med>=0.0001 GROUP BY ncbi_taxon_id, name, site_num ORDER BY {} {}'.format(
        search, propName, propOrder)
    species = connect.query(sql_species)
    total = len(species)
    if page == 1:
        i = 0
        for item in species:
            if i <= 9:
                species_result.append({
                    'name': item['name'],
                    'ncbi_taxon_id': item['ncbi_taxon_id'],
                    'site_num': item['site_num'],
                    'sample_num': '{:,}'.format(int(item['sample_num']))
                })
                i += 1
    else:
        i = 0
        for item in species:
            if i <= (page - 1) * limit - 1:
                i += 1
                continue
            if (page - 1) * limit - 1 < i <= page * limit - 1:
                species_result.append({
                    'name': item['name'],
                    'ncbi_taxon_id': item['ncbi_taxon_id'],
                    'site_num': item['site_num'],
                    'sample_num': '{:,}'.format(int(item['sample_num']))
                })
                i += 1
            if i > page * limit - 1:
                break
    result = getTaxonsPageGenusData(species_result, secondName, secondOrder)
    return result, total


# def getGenusIntro():
#     connect = sqlConnect.MySQLConnection()
#     sql_sample_num = 'SELECT SUM(loaded_run_num) sample_num FROM mbodymap_intro'
#     sample_num = int(connect.queryOne(sql_sample_num)['sample_num'])
#     sql_species_num = 'SELECT COUNT(DISTINCT ncbi_taxon_id) species_num FROM mbodymap_species_to_site'
#     species_num = connect.queryOne(sql_species_num)['species_num']
#     sql_genus_num = 'SELECT COUNT(DISTINCT ncbi_taxon_id) genus_num FROM mbodymap_genus_to_site'
#     genus_num = connect.queryOne(sql_genus_num)['genus_num']
#     sql_filter_species_num = 'SELECT COUNT(DISTINCT ncbi_taxon_id) species_num FROM mbodymap_species_to_site WHERE loaded_uid_num>1 AND med>=0.0001'
#     filter_species_num = connect.queryOne(sql_filter_species_num)['species_num']
#     sql_filter_genus_num = 'SELECT COUNT(DISTINCT ncbi_taxon_id) genus_num FROM mbodymap_genus_to_site WHERE loaded_uid_num>1 AND med>=0.0001'
#     filter_genus_num = connect.queryOne(sql_filter_genus_num)['genus_num']
#     species_intro = {
#         'sample_num': sample_num,
#         'species_num': species_num,
#         'genus_num': genus_num,
#         'filter_species_num': filter_species_num,
#         'filter_genus_num': filter_genus_num
#     }
#     return species_intro


def getGenusPhenotype(ncbi_taxon_id, site):
    connect = sqlConnect.MySQLConnection()
    result = []
    sql_phenotype = 'SELECT disease, loaded_uid_num_QC1, relative_abundance_std, relative_abundance_avg, relative_abundance_med FROM mbodymap_genus_to_samples WHERE ncbi_taxon_id={} AND BodySite="{}"'.format(
        ncbi_taxon_id, site)
    print(sql_phenotype)
    phenotypes = connect.query(sql_phenotype)
    for phenotype in phenotypes:
        sql_total_disease_of_taxon_in_site = 'SELECT COUNT(1) total FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE disease="{}" AND BodySite="{}" AND t1.run_id=t2.accession_id AND t2.QCStatus=1'.format(
            phenotype['disease'], site)
        total_disease_of_taxon_in_site = connect.queryOne(sql_total_disease_of_taxon_in_site)['total']
        sql_disease_name = 'SELECT term FROM mesh_data WHERE uid="{}"'.format(phenotype['disease'])
        name = connect.queryOne(sql_disease_name)['term']
        result.append({
            'phenotype': name,
            'mesh_id': phenotype['disease'],
            'loaded_uid_num': phenotype['loaded_uid_num_QC1'],
            'std': phenotype['relative_abundance_std'],
            'avg': phenotype['relative_abundance_avg'],
            'med': phenotype['relative_abundance_med'],
            'total': int(total_disease_of_taxon_in_site)
        })
    return result