"""Functions for handling genotypes."""
from typing import Optional
from functools import reduce
from datetime import datetime

import MySQLdb as mdb
from MySQLdb.cursors import Cursor, DictCursor
from flask import current_app as app

from gn_libs.mysqldb import debug_query

def genocode_by_population(
        conn: mdb.Connection, population_id: int) -> tuple[dict, ...]:
    """Get the allele/genotype codes."""
    with conn.cursor(cursorclass=DictCursor) as cursor:
        cursor.execute("SELECT * FROM GenoCode WHERE InbredSetId=%s",
                       (population_id,))
        return tuple(dict(item) for item in cursor.fetchall())


def genotype_markers_count(conn: mdb.Connection, species_id: int) -> int:
    """Find the total count of the genotype markers for a species."""
    with conn.cursor(cursorclass=DictCursor) as cursor:
        cursor.execute(
            "SELECT COUNT(Name) AS markers_count FROM Geno WHERE SpeciesId=%s",
            (species_id,))
        return int(cursor.fetchone()["markers_count"])


def genotype_markers(
        conn: mdb.Connection,
        species_id: int,
        population_id: int,
        offset: int = 0,
        limit: int = -1# no limit if negative, zero returns empty list.
) -> tuple[tuple[dict, ...], int]:
    """Retrieve markers from the database.

    Return: A tuple of:
            - Listing of the markers,
            - The total number of markers found in the system.
    """
    _query_template = (
        "SELECT %%COLS%% "
        "FROM Species AS spc "
        "INNER JOIN InbredSet AS iset "
        "ON spc.Id = iset.SpeciesId "
        "INNER JOIN GenoFreeze AS gfr "
        "ON iset.Id = gfr.InbredSetId "
        "INNER JOIN GenoXRef AS gxr "
        "ON gfr.Id = gxr.GenoFreezeId "
        "INNER JOIN Geno AS gno "
        "ON gxr.GenoId = gno.Id "
        "WHERE spc.Id=%s "
        "AND iset.Id=%s "
        "%%LIMIT%%")

    with conn.cursor(cursorclass=DictCursor) as cursor:
        cursor.execute(
            _query_template.replace("%%LIMIT%%", "").replace(
                "%%COLS%%", "COUNT(gno.Id) AS total_records"),
            (species_id, population_id))
        _total_records = cursor.fetchone()["total_records"]
        cursor.execute(
            _query_template.replace("%%COLS%%", "gno.*").replace(
                "%%LIMIT%%",
                (f"LIMIT {int(limit)} OFFSET {int(offset)}"
                 if bool(limit) and limit >= 0
                 else "")),
            (species_id, population_id))
        debug_query(cursor, app.logger)
        _records = tuple(dict(row) for row in cursor.fetchall())
        return _records, _total_records


def genotype_records(
        conn: mdb.Connection,
        species_id: int,
        population_id: int,
        offset: int = 0,
        limit: int = -1# no limit if negative, zero returns empty list.
) -> tuple[tuple[dict, ...], int]:
    """Retrieve the actual genotype records from the database.

    Returns: A tuple of:
             - the listing of the genotype data,
             - the total number of genotype records for this population.
    """
    def __organise_geno_records__(acc, row):
        _current_row = acc.get(row["GenoId"], {
            "GenoId": row["GenoId"],
            "data": {}
        })
        _current_row["data"][row["StrainName"]] = row["value"]
        return {
            **acc,
            _current_row["GenoId"]: _current_row
        }

    _query_template = (
        "SELECT gxr.GenoId, gxr.DataId, gdt.value, strn.Name AS StrainName "
        "FROM GenoXRef AS gxr "
        "INNER JOIN GenoData AS gdt ON gxr.DataId = gdt.Id "
        "INNER JOIN Strain AS strn ON gdt.StrainId = strn.Id "
        "WHERE gxr.GenoId IN (%%PARAMS_STR%%)")

    with conn.cursor(cursorclass=DictCursor) as cursor:
        _markers, _num_records = genotype_markers(
            conn, species_id, population_id, offset, limit)
        _genoids = tuple(_marker["Id"] for _marker in _markers)
        cursor.execute(
            _query_template.replace(
                "%%PARAMS_STR%%", ",".join(["%s"] * len(_genoids))),
            _genoids)
        _records: dict[str, dict] = reduce(
            __organise_geno_records__, cursor.fetchall(), {})
        return (
            tuple({
                **_marker,
                "data": _records.get(
                    _marker["Id"], {}
                ).get("data", {})
            } for _marker in _markers),
            _num_records)


def genotype_dataset(
        conn: mdb.Connection,
        species_id: int,
        population_id: int,
        dataset_id: Optional[int] = None
) -> Optional[dict]:
    """Retrieve genotype datasets from the database.

    Apparently, you should only ever have one genotype dataset for a population.
    """
    _query = (
        "SELECT gf.* FROM Species AS s INNER JOIN InbredSet AS iset "
        "ON s.Id=iset.SpeciesId INNER JOIN GenoFreeze AS gf "
        "ON iset.Id=gf.InbredSetId "
        "WHERE s.Id=%s AND iset.Id=%s")
    _params = (species_id, population_id)
    if bool(dataset_id):
        _query = _query + " AND gf.Id=%s"
        _params = _params + (dataset_id,)# type: ignore[assignment]

    with conn.cursor(cursorclass=DictCursor) as cursor:
        cursor.execute(_query, _params)
        debug_query(cursor, app.logger)
        result = cursor.fetchone()
        if bool(result):
            return dict(result)
        return None


def save_new_dataset(
        cursor: Cursor,
        population_id: int,
        name: str,
        fullname: str,
        shortname: str
) -> dict:
    """Save a new genotype dataset into the database."""
    params = {
        "InbredSetId": population_id,
        "Name": name,
        "FullName": fullname,
        "ShortName": shortname,
        "CreateTime": datetime.now().date().isoformat(),
        "public": 2,
        "confidentiality": 0,
        "AuthorisedUsers": None
    }
    cursor.execute(
        "INSERT INTO GenoFreeze("
        "Name, FullName, ShortName, CreateTime, public, InbredSetId, "
        "confidentiality, AuthorisedUsers"
        ") VALUES ("
        "%(Name)s, %(FullName)s, %(ShortName)s, %(CreateTime)s, %(public)s, "
        "%(InbredSetId)s, %(confidentiality)s, %(AuthorisedUsers)s"
        ")",
        params)
    return {**params, "Id": cursor.lastrowid}