from utils import sqlConnect


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)
    # print(sql_intro)
    intro = connect.queryOne(sql_intro)
    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']))
    }
    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)
    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']))
    }
    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