"""Database functions for species."""
from typing import Optional

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 FROM Species")
        return tuple(cursor.fetchall())

    return tuple()

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
        }