from utils import sqlConnect
import json


def introData(phenotype):
    connect = sqlConnect.MySQLConnection()
    sql_intro = 'SELECT note FROM mesh_data WHERE uid="{}"'.format(phenotype)
    phenotype_intro = connect.queryOne(sql_intro)
    # if phenotype == 'D006262':
    #     # sql_marker = 'select distinct phenotype1, phenotype1_name, phenotype2, phenotype2_name, count(distinct ncbi_taxon_id) taxa_num from curated_lefse_analysis_results ' \
    #     #              'GROUP BY phenotype1, phenotype1_name, phenotype2, phenotype2_name ORDER BY taxa_num desc'
    #     # marker = connect.query(sql_marker)
    #     phenotype_intro['marker'] = False
    # else:
    #     sql_marker = 'select distinct phenotype1, phenotype1_name, phenotype2, phenotype2_name, count(distinct ncbi_taxon_id) taxa_num from curated_lefse_analysis_results ' \
    #                  'where phenotype2="{}" GROUP BY phenotype1, phenotype1_name, phenotype2, phenotype2_name ORDER BY taxa_num desc'.format(phenotype)
    #     marker = connect.query(sql_marker)
    #     if len(marker) == 0:
    #         phenotype_intro['marker'] = False
    #     else:
    #         phenotype_intro['marker'] = marker
    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.01'.format(
        phenotype, site)
    species_phenotype_tbl = connect.query(sql_species_tbl)

    for species_item in species_phenotype_tbl:
        sql_mvp = 'select attributes from cross_db_links where ncbi_taxon_id={}'.format(species_item['ncbi_taxon_id'])
        mvp = connect.queryOne(sql_mvp)
        if mvp is not None:
            species_item['ifMvp'] = True
            species_item['mvpData'] = json.loads(mvp['attributes'])
        sql_gmrepo = 'select * from gmrepo_species where ncbi_taxon_id={}'.format(species_item['ncbi_taxon_id'])
        gmrepo = connect.queryOne(sql_gmrepo)
        if gmrepo is not None:
            species_item['ifGmrepo'] = True
        sql_hmdad = 'select * from hmdad_taxon where ncbi_taxon_id={}'.format(species_item['ncbi_taxon_id'])
        hmdad = connect.queryOne(sql_hmdad)
        if hmdad is not None:
            species_item['ifHmdad'] = True

    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.01'.format(
        phenotype, site)
    genus_phenotype_tbl = connect.query(sql_genus_tbl)

    for genus_item in genus_phenotype_tbl:
        sql_mvp = 'select attributes from cross_db_links where ncbi_taxon_id={}'.format(genus_item['ncbi_taxon_id'])
        mvp = connect.queryOne(sql_mvp)
        if mvp is not None:
            genus_item['ifMvp'] = True
            genus_item['mvpData'] = json.loads(mvp['attributes'])
        sql_gmrepo = 'select * from gmrepo_genus where ncbi_taxon_id={}'.format(genus_item['ncbi_taxon_id'])
        gmrepo = connect.queryOne(sql_gmrepo)
        if gmrepo is not None:
            genus_item['ifGmrepo'] = True
        sql_hmdad = 'select * from hmdad_taxon where ncbi_taxon_id={}'.format(species_item['ncbi_taxon_id'])
        hmdad = connect.queryOne(sql_hmdad)
        if hmdad is not None:
            species_item['ifHmdad'] = True

    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)
    # router
    sql_runs = 'SELECT COUNT(run_id) run_id_num, COUNT(DISTINCT project_id) project_id_num FROM mbodymap_samples WHERE disease="{}" AND BodySite="{}"'.format(
        phenotype, site)
    runs_num = connect.queryOne(sql_runs)['run_id_num']
    sql_loaded_runs = 'SELECT accession_id,QCStatus,uid FROM mbodymap_loaded_samples WHERE accession_id IN (SELECT run_id From mbodymap_samples WHERE disease="{}" AND BodySite="{}")'.format(
        phenotype, site)
    loaded_runs = connect.query(sql_loaded_runs)
    processed_num = len(loaded_runs)
    valid = 0
    for loaded_run in loaded_runs:
        # valid failed
        if loaded_run['QCStatus'] == 1:
            valid += 1
    router = {
        'runs_num': runs_num,
        'processed_num': processed_num,
        'valid': valid
    }

    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, 'router': router}