from gn3.db.species import get_all_species

def gen_dropdown_json(conn):
    """Generates and outputs (as json file) the data for the main dropdown menus on
    the home page
    """
    species = get_all_species(conn)
    groups = get_groups(species, conn)
    types = get_types(groups, conn)
    datasets = get_datasets(types, conn)
    return dict(species=species,
                groups=groups,
                types=types,
                datasets=datasets)


def get_groups(species, conn):
    """Build groups list"""
    groups = {}
    with conn.cursor() as cursor:
        for species_name, _species_full_name in species:
            groups[species_name] = []
            query = ("SELECT InbredSet.Name, InbredSet.FullName, "
                 "IFNULL(InbredSet.Family, 'None') "
                 "FROM InbredSet, Species WHERE Species.Name = '{}' "
                 "AND InbredSet.SpeciesId = Species.Id GROUP by "
                 "InbredSet.Name ORDER BY IFNULL(InbredSet.FamilyOrder, "
                 "InbredSet.FullName) ASC, IFNULL(InbredSet.Family, "
                 "InbredSet.FullName) ASC, InbredSet.FullName ASC, "
                 "InbredSet.MenuOrderId ASC").format(species_name)
            cursor.execute(query)
            results = cursor.fetchall()
            for result in results:
                family_name = "Family:" + str(result[2])
                groups[species_name].append(
                    [str(result[0]), str(result[1]), family_name])
    return groups


def get_types(groups, conn):
    """Build types list"""
    types = {}

    for species, group_dict in list(groups.items()):
        types[species] = {}
        for group_name, _group_full_name, _family_name in group_dict:
            if phenotypes_exist(group_name, conn):
                types[species][group_name] = [
                    ("Phenotypes", "Traits and Cofactors", "Phenotypes")]
            if genotypes_exist(group_name, conn):
                if group_name in types[species]:
                    types[species][group_name] += [
                        ("Genotypes", "DNA Markers and SNPs", "Genotypes")]
                else:
                    types[species][group_name] = [
                        ("Genotypes", "DNA Markers and SNPs", "Genotypes")]
            if group_name in types[species]:
                types_list = build_types(species, group_name, conn)
                if len(types_list) > 0:
                    types[species][group_name] += types_list
            else:
                types_list = build_types(species, group_name, conn)
                if len(types_list) > 0:
                    types[species][group_name] = types_list
                else:
                    types[species].pop(group_name, None)
                    groups[species] = list(
                        group for group in groups[species]
                        if group[0] != group_name)
    return types


def phenotypes_exist(group_name, conn):
    results = []
    with conn.cursor() as cursor:
        cursor.execute(
            ("SELECT Name FROM PublishFreeze "
             "WHERE PublishFreeze.Name = "
             "'{}'").format(group_name + "Publish"))
        results = cursor.fetchone()
    return bool(results)


def genotypes_exist(group_name, conn):
    with conn.cursor() as cursor:
        cursor.execute(
            ("SELECT Name FROM GenoFreeze " +
             "WHERE GenoFreeze.Name = '{}'").format(
                 group_name + "Geno"))
        results = cursor.fetchone()
        return bool(results)


def build_types(species, group, conn):
    """Fetches tissues

    Gets the tissues with data for this species/group
    (all types except phenotype/genotype are tissues)

    """

    query = ("SELECT DISTINCT Tissue.Name "
             "FROM ProbeFreeze, ProbeSetFreeze, InbredSet, "
             "Tissue, Species WHERE Species.Name = '{0}' "
             "AND Species.Id = InbredSet.SpeciesId AND "
             "InbredSet.Name = '{1}' AND ProbeFreeze.TissueId = "
             "Tissue.Id AND ProbeFreeze.InbredSetId = InbredSet.Id "
             "AND ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id "
             "ORDER BY Tissue.Name").format(species, group)

    results = []
    with conn.cursor() as cursor:
        cursor.execute(query)
        for result in cursor.fetchall():
            if bool(result):
                these_datasets = build_datasets(species,
                                                group, result[0], conn)
                if len(these_datasets) > 0:
                    results.append([str(result[0]), str(result[0]),
                                    "Molecular Traits"])
    return results


def get_datasets(types, conn):
    """Build datasets list"""
    datasets = {}
    for species, group_dict in list(types.items()):
        datasets[species] = {}
        for group, type_list in list(group_dict.items()):
            datasets[species][group] = {}
            for type_name in type_list:
                these_datasets = build_datasets(species, group,
                                                type_name[0], conn)
                if bool(these_datasets):
                    datasets[species][group][type_name[0]] = these_datasets

    return datasets


def build_datasets(species, group, type_name, conn):
    """Gets dataset names from database"""
    dataset_text = dataset_value = None
    datasets = []
    with conn.cursor() as cursor:
        if type_name == "Phenotypes":
            cursor.execute(
                ("SELECT InfoFiles.GN_AccesionId, PublishFreeze.Name, "
                 "PublishFreeze.FullName FROM InfoFiles, PublishFreeze, "
                 "InbredSet WHERE InbredSet.Name = '{}' AND "
                 "PublishFreeze.InbredSetId = InbredSet.Id AND "
                 "InfoFiles.InfoPageName = PublishFreeze.Name "
                 "ORDER BY PublishFreeze.CreateTime ASC").format(group))
            results = cursor.fetchall()
            if bool(results):
                for result in results:
                    dataset_id = str(result[0])
                    dataset_value = str(result[1])
                    dataset_text = str(result[2])
                    if group == 'MDP':
                        dataset_text = "Mouse Phenome Database"

                    datasets.append([dataset_id, dataset_value, dataset_text])
            else:
                cursor.execute(
                    ("SELECT PublishFreeze.Name, PublishFreeze.FullName "
                     "FROM PublishFreeze, InbredSet "
                     "WHERE InbredSet.Name = '{}' AND "
                     "PublishFreeze.InbredSetId = InbredSet.Id "
                     "ORDER BY PublishFreeze.CreateTime ASC")
                    .format(group))
                result = cursor.fetchone()
                dataset_id = "None"
                dataset_value = str(result[0])
                dataset_text = str(result[1])
                datasets.append([dataset_id, dataset_value, dataset_text])

        elif type_name == "Genotypes":
            cursor.execute(
                ("SELECT InfoFiles.GN_AccesionId "
                 "FROM InfoFiles, GenoFreeze, InbredSet "
                 "WHERE InbredSet.Name = '{}' AND "
                 "GenoFreeze.InbredSetId = InbredSet.Id AND "
                 "InfoFiles.InfoPageName = GenoFreeze.ShortName "
                 "ORDER BY GenoFreeze.CreateTime "
                 "DESC").format(group))
            results = cursor.fetchone()
            dataset_id = "None"
            if bool(results):
                dataset_id = str(results[0])

            dataset_value = "%sGeno" % group
            dataset_text = "%s Genotypes" % group
            datasets.append([dataset_id, dataset_value, dataset_text])

        else:  # for mRNA expression/ProbeSet
            cursor.execute(
                ("SELECT ProbeSetFreeze.Id, ProbeSetFreeze.Name, "
                 "ProbeSetFreeze.FullName FROM ProbeSetFreeze, "
                 "ProbeFreeze, InbredSet, Tissue, Species WHERE "
                 "Species.Name = '{0}' AND Species.Id = "
                 "InbredSet.SpeciesId AND InbredSet.Name = '{1}' "
                 "AND ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id "
                 "AND Tissue.Name = '{2}' AND ProbeFreeze.TissueId = "
                 "Tissue.Id AND ProbeFreeze.InbredSetId = InbredSet.Id "
                 "AND ProbeSetFreeze.public > 0 "
                 "ORDER BY -ProbeSetFreeze.OrderList DESC, "
                 "ProbeSetFreeze.CreateTime "
                 "DESC").format(species, group, type_name))
            results = cursor.fetchall()
            datasets = []
            for dataset_info in results:
                this_dataset_info = []
                for info in dataset_info:
                    this_dataset_info.append(str(info))
                datasets.append(this_dataset_info)

    return datasets