from utils import sqlConnect
import json


def getIntroBoxData():
    connect = sqlConnect.MySQLConnection()
    # phenotype comparisons number
    sql_pheno = 'select distinct phenotype1, phenotype1_name, phenotype2, phenotype2_name from curated_lefse_analysis_results'
    pheno = connect.query(sql_pheno)
    pheno_num = len(pheno)
    # projects number
    sql_projects = 'select count(distinct project_id) project_num from curated_lefse_analysis_results'
    project_num = connect.queryOne(sql_projects)['project_num']
    # taxa number
    sql_taxa = 'select count(distinct ncbi_taxon_id) taxa_num from curated_lefse_analysis_results'
    taxa_num = connect.queryOne(sql_taxa)['taxa_num']
    # table data
    result = []
    for item in pheno:
        sql_tbl = 'select count(distinct project_id) project_num, count(distinct bodysite) site_num, count(distinct ncbi_taxon_id) taxa_num from curated_lefse_analysis_results where phenotype1="{}" and phenotype2="{}"'.format(
            item['phenotype1'], item['phenotype2'])
        tbl = connect.query(sql_tbl)
        # print(tbl)
        result.append({
            'phenotype1': item['phenotype1'],
            'phenotype1_name': item['phenotype1_name'],
            'phenotype2': item['phenotype2'],
            'phenotype2_name': item['phenotype2_name'],
            'project_num': tbl[0]['project_num'],
            'site_num': tbl[0]['site_num'],
            'taxa_num': tbl[0]['taxa_num']
        })
    return {
        'pheno_num': pheno_num,
        'project_num': project_num,
        'taxa_num': taxa_num,
        'tableData': result
    }