"""Functions for accessing the database relating to species populations."""
import MySQLdb as mdb
from MySQLdb.cursors import DictCursor

def population_by_id(conn: mdb.Connection, population_id) -> dict:
    """Get the grouping/population by id."""
    with conn.cursor(cursorclass=DictCursor) as cursor:
        cursor.execute("SELECT * FROM InbredSet WHERE InbredSetId=%s",
                       (population_id,))
        return cursor.fetchone()

def population_by_species_and_id(
        conn: mdb.Connection, species_id, population_id) -> dict:
    """Retrieve a population by its identifier and species."""
    with conn.cursor(cursorclass=DictCursor) as cursor:
        cursor.execute("SELECT * FROM InbredSet WHERE SpeciesId=%s AND Id=%s",
                       (species_id, population_id))
        return cursor.fetchone()

def populations_by_species(conn: mdb.Connection, speciesid) -> tuple:
    "Retrieve group (InbredSet) information from the database."
    with conn.cursor(cursorclass=DictCursor) as cursor:
        query = "SELECT * FROM InbredSet WHERE SpeciesId=%s"
        cursor.execute(query, (speciesid,))
        return tuple(cursor.fetchall())

    return tuple()

def save_population(conn: mdb.Connection, population_details: dict) -> dict:
    """Save the population details to the db."""
    with conn.cursor(cursorclass=DictCursor) as cursor:
        cursor.execute("SELECT MAX(Id) AS last_id FROM InbredSet")
        new_id = cursor.fetchone()["last_id"] + 1
        cursor.execute(
            "INSERT INTO InbredSet("
            "Id, InbredSetId, InbredSetName, Name, SpeciesId, FullName, "
            "MenuOrderId, Description"
            ") "
            "VALUES ("
            "%(Id)s, %(InbredSetId)s, %(InbredSetName)s, %(Name)s, "
            "%(SpeciesId)s, %(FullName)s, %(MenuOrderId)s, %(Description)s"
            ")",
            {
                "Id": new_id,
                "InbredSetId": new_id,
                "MenuOrderId": 0,
                **population_details
            })
        return {**population_details, "population_id": new_id}