"""Database and utility functions for phenotypes."""
from typing import Optional

import MySQLdb as mdb
from MySQLdb.cursors import DictCursor

from uploader.db_utils import debug_query

def datasets_by_population(
        conn: mdb.Connection,
        species_id: int,
        population_id: int
) -> tuple[dict, ...]:
    """Retrieve all of a population's phenotype studies."""
    with conn.cursor(cursorclass=DictCursor) as cursor:
        cursor.execute(
            "SELECT s.SpeciesId, pf.* FROM Species AS s "
            "INNER JOIN InbredSet AS iset ON s.Id=iset.SpeciesId "
            "INNER JOIN PublishFreeze AS pf ON iset.Id=pf.InbredSetId "
            "WHERE s.Id=%s AND iset.Id=%s;",
                       (species_id, population_id))
        return tuple(dict(row) for row in cursor.fetchall())