"""Database functions for species."""
import math
from typing import Optional
from functools import reduce

import MySQLdb as mdb
from MySQLdb.cursors import DictCursor

def all_species(conn: mdb.Connection) -> tuple:
    "Retrieve the species from the database."
    with conn.cursor(cursorclass=DictCursor) as cursor:
        cursor.execute(
            "SELECT Id AS SpeciesId, SpeciesName, LOWER(Name) AS Name, "
            "MenuName, FullName, TaxonomyId, Family, FamilyOrderId, OrderId "
            "FROM Species ORDER BY FamilyOrderId ASC, OrderID ASC")
        return tuple(cursor.fetchall())

    return tuple()

def order_species_by_family(species: tuple[dict, ...]) -> list:
    """Order the species by their family"""
    def __family_order_id__(item):
        orderid = item["FamilyOrderId"]
        return math.inf if orderid is None else orderid
    def __order__(ordered, current):
        _key = (__family_order_id__(current), current["Family"])
        return {
            **ordered,
            _key: ordered.get(_key, tuple()) + (current,)
        }
    ordered = reduce(__order__, species, {})
    return sorted(tuple(ordered.items()), key=lambda item: item[0][0])


def species_by_id(conn: mdb.Connection, speciesid) -> dict:
    "Retrieve the species from the database by id."
    with conn.cursor(cursorclass=DictCursor) as cursor:
        cursor.execute(
            "SELECT Id AS SpeciesId, SpeciesName, LOWER(Name) AS Name, "
            "MenuName, FullName, TaxonomyId, Family, FamilyOrderId, OrderId "
            "FROM Species WHERE SpeciesId=%s",
            (speciesid,))
        return cursor.fetchone()


def save_species(conn: mdb.Connection,
                 common_name: str,
                 scientific_name: str,
                 taxon_id: Optional[str] = None) -> int:
    """
    Save a new species to the database.

    Parameters
    ----------
    conn: A connection to the MariaDB database.
    taxon_id: The taxonomy identifier for the new species.
    common_name: The species' common name.
    scientific_name; The species' scientific name.
    """
    genus, species = scientific_name.split(" ")
    species = {
        "common_name": common_name,
        "common_name_lower": common_name.lower(),
        "menu_name": f"{common_name} ({genus[0]}. {species.lower()})",
        "scientific_name": scientific_name,
        "taxon_id": taxon_id
    }
    with conn.cursor() as cursor:
        cursor.execute(
            "INSERT INTO Species("
            "SpeciesName, Name, MenuName, FullName, TaxonomyId"
            ") VALUES ("
            "%(common_name)s, %(common_name_lower)s, %(menu_name)s, "
            "%(scientific_name)s, %(taxon_id)s"
            ")",
            species)
        species_id = cursor.lastrowid
        cursor.execute("UPDATE Species SET SpeciesId=%s WHERE Id=%s",
                       (species_id, species_id))
        return {
            **species,
            "species_id": species_id
        }