from utils import sqlConnect


def introData(phenotype):
    connect = sqlConnect.MySQLConnection()
    sql_intro = 'SELECT note FROM mesh_data WHERE uid="{}"'.format(phenotype)
    phenotype_intro = connect.queryOne(sql_intro)
    return phenotype_intro


def tblData(phenotype, site):
    connect = sqlConnect.MySQLConnection()
    # species
    sql_species_tbl = 'SELECT ncbi_taxon_id, name, loaded_uid_num, relative_abundance_sum, relative_abundance_std, relative_abundance_avg, relative_abundance_med FROM mbodymap_species_to_samples WHERE disease="{}" AND BodySite="{}" AND loaded_uid_num>1 AND relative_abundance_med>0.0001'.format(
        phenotype, site)
    species_phenotype_tbl = connect.query(sql_species_tbl)

    sql_species_total = 'SELECT COUNT(1) num FROM mbodymap_species_to_samples WHERE disease="{}" AND BodySite="{}"'.format(
        phenotype, site)
    species_total = connect.queryOne(sql_species_total)['num']
    species_filter_total = len(species_phenotype_tbl)
    species_result = {
        'species_phenotype_tbl': species_phenotype_tbl,
        'species_total': '{:,}'.format(species_total),
        'species_filter_total': '{:,}'.format(species_filter_total)
    }

    # genus
    sql_genus_tbl = 'SELECT ncbi_taxon_id, name, loaded_uid_num, relative_abundance_sum, relative_abundance_std, relative_abundance_avg, relative_abundance_med FROM mbodymap_genus_to_samples WHERE disease="{}" AND BodySite="{}" AND loaded_uid_num>1 AND relative_abundance_med>0.0001'.format(
        phenotype, site)
    genus_phenotype_tbl = connect.query(sql_genus_tbl)

    sql_genus_total = 'SELECT COUNT(1) num FROM mbodymap_genus_to_samples WHERE disease="{}" AND BodySite="{}"'.format(
        phenotype, site)
    genus_total = connect.queryOne(sql_genus_total)['num']
    genus_filter_total = len(genus_phenotype_tbl)
    genus_result = {
        'genus_phenotype_tbl': genus_phenotype_tbl,
        'genus_total': '{:,}'.format(genus_total),
        'genus_filter_total': '{:,}'.format(genus_filter_total)
    }

    # projects
    # 补充intro框中的project数量
    sql_project_num = 'SELECT COUNT(DISTINCT project_id) num FROM mbodymap_samples WHERE BodySite="{}" AND disease="{}"'.format(
        site, phenotype)
    project_num = connect.queryOne(sql_project_num)['num']

    sql_project = 'SELECT DISTINCT project_id FROM mbodymap_samples WHERE BodySite="{}" AND disease="{}"'.format(site,
                                                                                                                 phenotype)
    projects = connect.query(sql_project)
    projects_phenotype_tbl = []
    for project in projects:
        sql_project_tbl = 'SELECT `Original Project description` description, `Number of samples` sample_num, `Number of runs` runs_num, `Related Publications` publication FROM `mbodymap_projects` WHERE `NCBI Project ID`="{}"'.format(
            project['project_id'])
        project_tbl = connect.queryOne(sql_project_tbl)
        sql_phenotypes = 'SELECT DISTINCT t1.disease, t3.term FROM mbodymap_samples t1, mbodymap_loaded_samples t2, mesh_data t3 WHERE t1.project_id="{}" AND t1.run_id=t2.accession_id AND t1.disease=t3.uid'.format(
            project['project_id'])
        phenotypes = connect.query(sql_phenotypes)
        projects_phenotype_tbl.append({
            'project': project['project_id'],
            'description': project_tbl['description'] if project_tbl else 'NA',
            'total_runs': project_tbl['sample_num'] if project_tbl else 'NA',
            'processed_runs': project_tbl['runs_num'] if project_tbl else 'NA',
            'publication': project_tbl['publication'] if project_tbl else 'NA',
            'related_phenotype': phenotypes
        })

    # samples/runs
    sql_sum_tbl = 'SELECT t1.project_id, t1.run_id, t1.experiment_type, t1.instrument_model, t1.BodySite_info, t1.country, t1.sex, t1.host_age, t2.QCStatus FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE BodySite="{}" AND disease="{}" AND t1.run_id=t2.accession_id'.format(
        site, phenotype)
    samples_phenotype_tbl = connect.query(sql_sum_tbl)

    return {'species_result': species_result, 'genus_result': genus_result, 'project_num': project_num,
            'projects_phenotype_tbl': projects_phenotype_tbl, 'samples_phenotype_tbl': samples_phenotype_tbl}