"""Menu generation code for the data in the dropdowns in the index page."""

from typing import Tuple
from functools import reduce

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(conn, tuple(row[0] for row in species))
    types = get_types(conn, groups)
    datasets = get_datasets(conn, types)
    return dict(species=species,
                groups=groups,
                types=types,
                datasets=datasets)

def get_groups(conn, species_names: Tuple[str, ...]):
    """Build groups list"""
    with conn.cursor() as cursor:
        query = (
            "SELECT InbredSet.Name, InbredSet.FullName, "
            "IFNULL(InbredSet.Family, 'None'), "
            "Species.Name AS species_name "
            "FROM Species "
            "INNER JOIN InbredSet ON InbredSet.SpeciesId = Species.Id "
            "WHERE Species.Name IN "
            f"({', '.join(['%s']*len(species_names))}) "
            "GROUP BY InbredSet.Name "
            "ORDER BY IFNULL(InbredSet.FamilyOrder, InbredSet.FullName) ASC, "
            "IFNULL(InbredSet.Family, InbredSet.FullName) ASC, "
            "InbredSet.FullName ASC, "
            "InbredSet.MenuOrderId ASC")
        cursor.execute(query, tuple(species_names))
        results = cursor.fetchall()

    def __organise_by_species(acc, row):
        family_name = f"Family:{str(row[2])}"
        species_name = row[3]
        key_exists = bool(acc.get(species_name, False))
        if not key_exists:
            return {
                **acc,
                species_name: [[str(row[0]), str(row[1]), family_name],]
            }

        return {
            **acc,
            species_name: acc[species_name] + [
                [str(row[0]), str(row[1]), family_name],]
        }

    return reduce(__organise_by_species, results, {})

def get_types(conn, groups):
    """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(conn, group_name):
                types[species][group_name] = [
                    ("Phenotypes", "Traits and Cofactors", "Phenotypes")]
            if genotypes_exist(conn, group_name):
                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(conn, species, group_name)
                if len(types_list) > 0:
                    types[species][group_name] += types_list
            else:
                types_list = build_types(conn, species, group_name)
                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(conn, group_name):
    "Check whether phenotypes exist for the given group"
    with conn.cursor() as cursor:
        cursor.execute(
            ("SELECT Name FROM PublishFreeze "
             "WHERE PublishFreeze.Name = %s"),
            (group_name + "Publish",))
        results = cursor.fetchone()
        return bool(results)

def genotypes_exist(conn, group_name):
    "Check whether genotypes exist for the given group"
    with conn.cursor() as cursor:
        cursor.execute(
            ("SELECT Name FROM GenoFreeze " +
             "WHERE GenoFreeze.Name = %s"),
            (group_name + "Geno",))
        results = cursor.fetchone()
        return bool(results)

def build_types(conn, species, group):
    """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 = %s "
        "AND Species.Id = InbredSet.SpeciesId AND "
        "InbredSet.Name = %s AND ProbeFreeze.TissueId = "
        "Tissue.Id AND ProbeFreeze.InbredSetId = InbredSet.Id "
        "AND ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id "
        "ORDER BY Tissue.Name")
    results = []
    with conn.cursor() as cursor:
        cursor.execute(query, (species, group))
        for result in cursor.fetchall():
            if bool(result):
                these_datasets = build_datasets(conn, species, group, result[0])
                if len(these_datasets) > 0:
                    results.append([
                        str(result[0]), str(result[0]), "Molecular Traits"])

    return results

def get_datasets(conn, types):
    """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(
                    conn, species, group, type_name[0])
                if bool(these_datasets):
                    datasets[species][group][type_name[0]] = these_datasets

    return datasets

def build_datasets(conn, species, group, type_name):
    """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 = %s AND "
                 "PublishFreeze.InbredSetId = InbredSet.Id AND "
                 "InfoFiles.InfoPageName = PublishFreeze.Name "
                 "ORDER BY PublishFreeze.CreateTime ASC"), (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 = %s AND "
                     "PublishFreeze.InbredSetId = InbredSet.Id "
                     "ORDER BY PublishFreeze.CreateTime ASC"), (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 = %s AND "
                 "GenoFreeze.InbredSetId = InbredSet.Id AND "
                 "InfoFiles.InfoPageName = GenoFreeze.ShortName "
                 "ORDER BY GenoFreeze.CreateTime "
                 "DESC"), (group,))
            results = cursor.fetchone()
            dataset_id = "None"
            if bool(results):
                dataset_id = str(results[0])

            dataset_value = f"{group}Geno"
            dataset_text = f"{group} Genotypes"
            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 = %s AND Species.Id = "
                 "InbredSet.SpeciesId AND InbredSet.Name = %s "
                 "AND ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id "
                 "AND Tissue.Name = %s AND ProbeFreeze.TissueId = "
                 "Tissue.Id AND ProbeFreeze.InbredSetId = InbredSet.Id "
                 "AND ProbeSetFreeze.public > 0 "
                 "ORDER BY -ProbeSetFreeze.OrderList DESC, "
                 "ProbeSetFreeze.CreateTime "
                 "DESC"), (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