from utils import sqlConnect
import json


def getSpeciesIntro(ncbi_taxon_id):
    connect = sqlConnect.MySQLConnection()
    sql_intro = 'SELECT name, COUNT(DISTINCT BodySite) site_num, COUNT(DISTINCT disease) disease_num, SUM(loaded_uid_num) total_runs, SUM(loaded_uid_num_QC1) valid_runs FROM mbodymap_species_to_samples WHERE ncbi_taxon_id={} GROUP BY name'.format(
        ncbi_taxon_id)
    intro = connect.queryOne(sql_intro)
    sql_mvp = 'select attributes from cross_db_links where ncbi_taxon_id={}'.format('ncbi_taxon_id')
    mvp = connect.queryOne(sql_mvp)
    sql_gmrepo = 'select * from gmrepo_species where ncbi_taxon_id={}'.format('ncbi_taxon_id')
    gmrepo = connect.queryOne(sql_gmrepo)
    result = {
        'ncbi_taxon_id': ncbi_taxon_id,
        'name': intro['name'],
        'site_num': int(intro['site_num']),
        'total_runs': '{:,}'.format(int(intro['total_runs'])),
        'valid_runs': '{:,}'.format(int(intro['valid_runs'])),
        'disease_num': '{:,}'.format(int(intro['disease_num']))
    }
    if mvp is not None:
        result['ifMvp'] = True
        result['mvpData'] = json.loads(mvp['attributes'])
    if gmrepo is not None:
        result['ifGmrepo'] = True
    sql_hmdad = 'select * from hmdad_taxon where ncbi_taxon_id={}'.format('ncbi_taxon_id')
    hmdad = connect.queryOne(sql_hmdad)
    if hmdad is not None:
        result['ifHmdad'] = True
    return result


def getSpeciesData(ncbi_taxon_id, propName, propOrder):
    connect = sqlConnect.MySQLConnection()
    sql = 'SELECT * FROM mbodymap_species_to_site_relative_abundance WHERE ncbi_taxon_id={} ORDER BY {} {}'.format(
        ncbi_taxon_id, propName, propOrder)
    result = connect.query(sql)
    return result


def getGenusIntro(ncbi_taxon_id):
    connect = sqlConnect.MySQLConnection()
    sql_intro = 'SELECT name, COUNT(DISTINCT BodySite) site_num, COUNT(DISTINCT disease) disease_num, SUM(loaded_uid_num) total_runs, SUM(loaded_uid_num_QC1) valid_runs FROM mbodymap_genus_to_samples WHERE ncbi_taxon_id={} GROUP BY name'.format(
        ncbi_taxon_id)
    # print(sql_intro)
    intro = connect.queryOne(sql_intro)
    sql_mvp = 'select attributes from cross_db_links where ncbi_taxon_id={}'.format('ncbi_taxon_id')
    mvp = connect.queryOne(sql_mvp)
    sql_gmrepo = 'select * from gmrepo_genus where ncbi_taxon_id={}'.format('ncbi_taxon_id')
    gmrepo = connect.queryOne(sql_gmrepo)
    result = {
        'ncbi_taxon_id': ncbi_taxon_id,
        'name': intro['name'],
        'site_num': int(intro['site_num']),
        'total_runs': '{:,}'.format(int(intro['total_runs'])),
        'valid_runs': '{:,}'.format(int(intro['valid_runs'])),
        'disease_num': '{:,}'.format(int(intro['disease_num']))
    }
    if mvp is not None:
        result['ifMvp'] = True
        result['mvpData'] = json.loads(mvp['attributes'])
    if gmrepo is not None:
        result['ifGmrepo'] = True
    sql_hmdad = 'select * from hmdad_taxon where ncbi_taxon_id={}'.format('ncbi_taxon_id')
    hmdad = connect.queryOne(sql_hmdad)
    if hmdad is not None:
        result['ifHmdad'] = True
    return result


def getGenusData(ncbi_taxon_id, propName, propOrder):
    connect = sqlConnect.MySQLConnection()
    sql = 'SELECT * FROM mbodymap_genus_to_site_relative_abundance WHERE ncbi_taxon_id={} ORDER BY {} {}'.format(
        ncbi_taxon_id, propName, propOrder)
    result = connect.query(sql)
    return result


def getMarkerSpecies(ncbi_taxon_id):
    connect = sqlConnect.MySQLConnection()
    # -----------    判断是否是marker ------------------------------
    sql_marker = 'select * from curated_lefse_analysis_results where ncbi_taxon_id={} limit 1'.format(
        ncbi_taxon_id)
    if_marker = connect.queryOne(sql_marker)
    if if_marker is None:
        marker = False
        return marker
    else:
        # ----------    marker intro    ---------------------------------
        sql_intro = 'select count(1) comparisons, count(distinct project_id) projects, count(distinct bodysite) bodysites, count(distinct phenotype2) unique_pheno from curated_lefse_analysis_results where ncbi_taxon_id={}'.format(
            ncbi_taxon_id)
        intro = connect.queryOne(sql_intro)
        # ------------  marker table    --------------------------------
        sql_table = 'SELECT * FROM `curated_lefse_analysis_results` WHERE ncbi_taxon_id={} ORDER BY lda DESC'.format(
            ncbi_taxon_id)
        table = connect.query(sql_table)
        # ------------- bar data    ----------------------------------------
        barData = []
        sql_pheno = 'select distinct phenotype1, phenotype1_name, phenotype2, phenotype2_name from curated_lefse_analysis_results WHERE ncbi_taxon_id={}'.format(
            ncbi_taxon_id)
        phenotypes = connect.query(sql_pheno)
        for pheno in phenotypes:
            sql_phenoData = 'select project_id, bodysite, lda from curated_lefse_analysis_results WHERE ncbi_taxon_id={} and phenotype1="{}" and phenotype2="{}"'.format(
                ncbi_taxon_id, pheno['phenotype1'], pheno['phenotype2']
            )
            phenoData = connect.query(sql_phenoData)
            xaxis = []
            yaxis = []
            for item in phenoData:
                xaxis.append(item['lda'])
                yaxis.append(item['project_id'] + ' (' + item['bodysite'] + ')')
            barData.append({
                'phenotype1': pheno['phenotype1'],
                'phenotype2': pheno['phenotype2'],
                'phenotype1_name': pheno['phenotype1_name'],
                'phenotype2_name': pheno['phenotype2_name'],
                'xaxis': xaxis,
                'yaxis': yaxis
            })

        # --------------    result  -----------------------------------
        result = {
            'intro': intro,
            'table': table,
            'bar': barData
        }
        return result