from utils import sqlConnect
from service.taxonsPageService import getSpeciesPhenotype, getGenusPhenotype


def speciesDetails(ncbi_taxon_id, site):
    connect = sqlConnect.MySQLConnection()
    # 表格数据
    speciesTableRes = getSpeciesPhenotype(ncbi_taxon_id, site)
    # barPlot数据
    barPlot_results = []
    linePlot_results = []
    xPlot = []
    yPlot = []
    sql_barPlot = 'SELECT disease, loaded_uid_num_QC1, relative_abundance_max FROM mbodymap_species_to_samples WHERE ncbi_taxon_id={} AND BodySite="{}" AND loaded_uid_num_QC1>10'.format(
        ncbi_taxon_id, site)
    phenotypes = connect.query(sql_barPlot)
    for phenotype in phenotypes:
        # barPlot
        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'])
        disease_name = connect.queryOne(sql_disease_name)['term']
        barPlot_results.append({
            'disease': disease_name,
            'prevalence': phenotype['loaded_uid_num_QC1'] / int(total_disease_of_taxon_in_site) * 100
        })
        # linePlot
        sql_all_reiative_abundance = 'SELECT t1.relative_abundance ra FROM mbodymap_relative_species_abundances t1, mbodymap_samples t2, mbodymap_loaded_samples t3 WHERE t2.BodySite="{}" AND t2.disease="{}" AND t2.run_id=t3.accession_id AND t1.loaded_uid=t3.uid AND t1.ncbi_taxon_id={}'.format(
            site, phenotype['disease'], ncbi_taxon_id)
        all_reiative_abundance = connect.query(sql_all_reiative_abundance)
        ## 设置标尺
        max_relative_abundance = int(phenotype['relative_abundance_max'])
        if max_relative_abundance % 2 == 0:
            max_relative_abundance += 1
        else:
            max_relative_abundance += 2
        roundX = []
        if max_relative_abundance == 100:
            for i in range(1, 101, 2):
                roundX.append(i)
        else:
            for i in range(1, max_relative_abundance + 2, 2):
                roundX.append(i)
        ## 统计尺度值的总数
        roundY = []
        for rule in roundX:
            rule_value = 0
            for relative_abundance in all_reiative_abundance:
                if relative_abundance['ra'] >= rule:
                    rule_value += 1
            roundY.append(rule_value / int(total_disease_of_taxon_in_site) * 100)
        if phenotype['disease'] == 'D006262':
            linePlot_results.append({
                'type': 'scatter',
                'x': roundX,
                'y': roundY,
                'name': disease_name,
                'mode': 'lines+markers',
                'marker': {
                    'color': 'green'
                },
                'line': {
                    'color': 'green'
                }
            })
        else:
            linePlot_results.append({
                'type': 'scatter',
                'x': roundX,
                'y': roundY,
                'name': disease_name,
                'mode': 'lines+markers'
            })
    # print(barPlot_results)
    results_sorted = sorted(barPlot_results, key=lambda result: result['prevalence'], reverse=True)
    # print(results_sorted)
    for results_sorted_item in results_sorted:
        xPlot.append(format(results_sorted_item['prevalence'], '.1f'))
        yPlot.append(results_sorted_item['disease'])
    results = {
        'speciesTableRes': speciesTableRes,
        'xPlot': xPlot,
        'yPlot': yPlot,
        'linePlot': linePlot_results
    }
    return results


def genusDetails(ncbi_taxon_id, site):
    connect = sqlConnect.MySQLConnection()
    # 表格数据
    speciesTableRes = getGenusPhenotype(ncbi_taxon_id, site)
    # barPlot数据
    barPlot_results = []
    linePlot_results = []
    xPlot = []
    yPlot = []
    sql_barPlot = 'SELECT disease, loaded_uid_num_QC1, relative_abundance_max FROM mbodymap_genus_to_samples WHERE ncbi_taxon_id={} AND BodySite="{}" AND loaded_uid_num_QC1>10'.format(
        ncbi_taxon_id, site)
    phenotypes = connect.query(sql_barPlot)
    for phenotype in phenotypes:
        # barPlot
        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'])
        disease_name = connect.queryOne(sql_disease_name)['term']
        barPlot_results.append({
            'disease': disease_name,
            'prevalence': phenotype['loaded_uid_num_QC1'] / int(total_disease_of_taxon_in_site) * 100
        })
        # linePlot
        sql_all_reiative_abundance = 'SELECT t1.relative_abundance ra FROM mbodymap_relative_species_abundances t1, mbodymap_samples t2, mbodymap_loaded_samples t3 WHERE t2.BodySite="{}" AND t2.disease="{}" AND t2.run_id=t3.accession_id AND t1.loaded_uid=t3.uid AND t1.ncbi_taxon_id={}'.format(
            site, phenotype['disease'], ncbi_taxon_id)
        all_reiative_abundance = connect.query(sql_all_reiative_abundance)
        ## 设置标尺
        max_relative_abundance = int(phenotype['relative_abundance_max'])
        if max_relative_abundance % 2 == 0:
            max_relative_abundance += 1
        else:
            max_relative_abundance += 2
        roundX = []
        if max_relative_abundance == 100:
            for i in range(1, 101, 2):
                roundX.append(i)
        else:
            for i in range(1, max_relative_abundance + 2, 2):
                roundX.append(i)
        ## 统计尺度值的总数
        roundY = []
        for rule in roundX:
            rule_value = 0
            for relative_abundance in all_reiative_abundance:
                if relative_abundance['ra'] >= rule:
                    rule_value += 1
            roundY.append(rule_value / int(total_disease_of_taxon_in_site) * 100)
        if phenotype['disease'] == 'D006262':
            linePlot_results.append({
                'type': 'scatter',
                'x': roundX,
                'y': roundY,
                'name': disease_name,
                'mode': 'lines+markers',
                'marker': {
                    'color': 'green'
                },
                'line': {
                    'color': 'green'
                }
            })
        else:
            linePlot_results.append({
                'type': 'scatter',
                'x': roundX,
                'y': roundY,
                'name': disease_name,
                'mode': 'lines+markers'
            })
    # print(barPlot_results)
    results_sorted = sorted(barPlot_results, key=lambda result: result['prevalence'], reverse=True)
    # print(results_sorted)
    for results_sorted_item in results_sorted:
        xPlot.append(format(results_sorted_item['prevalence'], '.1f'))
        yPlot.append(results_sorted_item['disease'])
    results = {
        'speciesTableRes': speciesTableRes,
        'xPlot': xPlot,
        'yPlot': yPlot,
        'linePlot': linePlot_results
    }
    return results


def getGenusBoxPlot(ncbi_taxon_id, site):
    connect = sqlConnect.MySQLConnection()
    # 表格数据
    boxPlot_results = []
    sql_barPlot = 'SELECT disease, loaded_uid_num_QC1, relative_abundance_max FROM mbodymap_genus_to_samples WHERE ncbi_taxon_id={} AND BodySite="{}" AND loaded_uid_num_QC1>10'.format(
        ncbi_taxon_id, site)
    phenotypes = connect.query(sql_barPlot)
    for phenotype in phenotypes:
        sql_disease_name = 'SELECT term FROM mesh_data WHERE uid="{}"'.format(phenotype['disease'])
        disease_name = connect.queryOne(sql_disease_name)['term']
        # linePlot
        sql_all_reiative_abundance = 'SELECT t1.relative_abundance ra FROM mbodymap_relative_species_abundances t1, mbodymap_samples t2, mbodymap_loaded_samples t3 WHERE t2.BodySite="{}" AND t2.disease="{}" AND t2.run_id=t3.accession_id AND t1.loaded_uid=t3.uid AND t1.ncbi_taxon_id={}'.format(
            site, phenotype['disease'], ncbi_taxon_id)
        all_reiative_abundance = connect.query(sql_all_reiative_abundance)
        box_value = []
        for relative_abundance in all_reiative_abundance:
            box_value.append(relative_abundance['ra'])
        # boxPlot
        if phenotype['disease'] == 'D006262':
            boxPlot_results.append({
                'type': 'box',
                'x': box_value,
                'name': disease_name,
                'boxpoints': 'Outliers',
                'width': 0.5,
                'fillcolor': 'green',
                'marker': {
                    'color': 'green'
                },
                'line': {
                    'color': 'green'
                }
            })
        else:
            boxPlot_results.append({
                'type': 'box',
                'x': box_value,
                'name': disease_name,
                'boxpoints': 'Outliers',
                'width': 0.5
            })
    results = {
        'boxPlot': boxPlot_results
    }
    return results


def getSpeciesBoxPlot(ncbi_taxon_id, site):
    connect = sqlConnect.MySQLConnection()
    # 表格数据
    boxPlot_results = []
    sql_barPlot = 'SELECT disease, loaded_uid_num_QC1, relative_abundance_max FROM mbodymap_species_to_samples WHERE ncbi_taxon_id={} AND BodySite="{}" AND loaded_uid_num_QC1>10'.format(
        ncbi_taxon_id, site)
    phenotypes = connect.query(sql_barPlot)
    for phenotype in phenotypes:
        sql_disease_name = 'SELECT term FROM mesh_data WHERE uid="{}"'.format(phenotype['disease'])
        disease_name = connect.queryOne(sql_disease_name)['term']
        # linePlot
        sql_all_reiative_abundance = 'SELECT t1.relative_abundance ra FROM mbodymap_relative_species_abundances t1, mbodymap_samples t2, mbodymap_loaded_samples t3 WHERE t2.BodySite="{}" AND t2.disease="{}" AND t2.run_id=t3.accession_id AND t1.loaded_uid=t3.uid AND t1.ncbi_taxon_id={}'.format(
            site, phenotype['disease'], ncbi_taxon_id)
        all_reiative_abundance = connect.query(sql_all_reiative_abundance)
        box_value = []
        for relative_abundance in all_reiative_abundance:
            box_value.append(relative_abundance['ra'])
        # boxPlot
        if phenotype['disease'] == 'D006262':
            boxPlot_results.append({
                'type': 'box',
                'x': box_value,
                'name': disease_name,
                'boxpoints': 'Outliers',
                'width': 0.1,
                'fillcolor': 'green',
                'marker': {
                    'color': 'green'
                },
                'line': {
                    'color': 'green'
                }
            })
        else:
            boxPlot_results.append({
                'type': 'box',
                'x': box_value,
                'name': disease_name,
                'boxpoints': 'Outliers',
                'width': 0.1
            })
    results = {
        'boxPlot': boxPlot_results
    }
    return results


def getSpeciesIntro(ncbi_taxon_id, site):
    connect = sqlConnect.MySQLConnection()
    sql_intro = 'SELECT name, disease_num, loaded_uid_num FROM mbodymap_species_to_site WHERE ncbi_taxon_id={} AND BodySite="{}"'.format(
        ncbi_taxon_id, site)
    # print(sql_intro)
    intro = connect.queryOne(sql_intro)
    result = {
        'ncbi_taxon_id': ncbi_taxon_id,
        'name': intro['name'],
        'disease_num': int(intro['disease_num']),
        'loaded_uid_num': '{:,}'.format(int(intro['loaded_uid_num']))
    }
    return result


def getGenusIntro(ncbi_taxon_id, site):
    connect = sqlConnect.MySQLConnection()
    sql_intro = 'SELECT name, disease_num, loaded_uid_num FROM mbodymap_genus_to_site WHERE ncbi_taxon_id={} AND BodySite="{}"'.format(
        ncbi_taxon_id, site)
    # print(sql_intro)
    intro = connect.queryOne(sql_intro)
    result = {
        'ncbi_taxon_id': ncbi_taxon_id,
        'name': intro['name'],
        'disease_num': int(intro['disease_num']),
        'loaded_uid_num': '{:,}'.format(int(intro['loaded_uid_num']))
    }
    return result