"""Functions for querying the database for general information"""
from contextlib import closing
from sqlite3 import connect, Connection, PARSE_DECLTYPES, PARSE_COLNAMES

from configurations import default_database


def get_all_entry_ids(database: str = None):
    """Gets the id of every entry in the database

    :rtype: list
    :param database: a str representing the database that is being queried
    :return: a list of ints representing the entry ids
    """
    db = connect(database) if database else connect(default_database())
    with closing(db) as d:
        t = d.execute('SELECT entry_id FROM dates ORDER BY created').fetchall()
        return [x[0] for x in t]


def get_all_tags(database: str = None):
    """Gets all tags in the database

    :rtype: list
    :param database: a str representing the database that is being queried
    :return: a list of str representing all tags used in the database
    """
    db = connect(database) if database else connect(default_database())
    with closing(db) as d:
        t = set(d.execute('SELECT tag FROM tags ORDER BY tag').fetchall())
        return [x[0] for x in t]


def get_all_dates(database: str = None):
    """Gets the date for every entry in the database

    :rtype: list
    :param database: a str representing the database that is being queried
    :return: a list of datetime objects
    """
    types = PARSE_DECLTYPES | PARSE_COLNAMES
    db = connect(database, detect_types=types) if database else connect(default_database(), detect_types=types)
    with closing(db) as d:
        dates = [x[0] for x in d.execute('SELECT created FROM dates ORDER BY created').fetchall()]
        return dates


def get_oldest_date(database: str = None):
    """

    :rtype: datetime.datetime
    :param database: a str representing the location of a journal database
    :return: a datetime representing the date of the oldest entry in the database
    """
    db = default_database(database) if database else default_database()
    try:
        return get_all_dates(db)[0]
    except IndexError:
        return None


def get_newest_date(database: str = None):
    """

    :rtype: datetime.datetime
    :param database: a str representing the location of a journal database
    :return: a datetime representing the date of the newest entry in the database
    """
    db = default_database(database) if database else default_database()
    try:
        return get_all_dates(db)[-1]
    except IndexError:
        return None


def get_all_children(database: str = None):
    """Gets the ids of all entries that were generated by another entry

    :rtype: list
    :param database: a Connection or str representing the database that is being queried
    :return: a list of ints representing entries
    """
    db = connect(database) if database else connect(default_database())
    with closing(db) as d:
        return [x[0] for x in d.execute('SELECT child from relations').fetchall()]


def get_all_parents(database: str = None):
    """Gets the ids of all entries that have generated another entry

    :rtype: list
    :param database: a Connection or str representing the database that is being queried
    :return: a list of ints representing entries
    """
    db = connect(database) if database else connect(default_database())
    with closing(db) as d:
        return [x[0] for x in d.execute('SELECT parent from relations').fetchall()]


def get_all_relations(database: str = None):
    """Gets all relation pairs from the database

    :rtype: tuple
    :param database: a Connection or str representing the database that is being queried
    :return: a collection of linked pairs, each representing a parent-child relationship
    """
    db = connect(database) if database else connect(default_database())
    with closing(db) as d:
        pairs = d.execute('SELECT child,parent FROM relations')
    return pairs


def get_number_of_entries(database: str = None):
    """Counts the number of entries in the database.

    :rtype: int
    :param database: a Connection or str representing the database that is being queried
    :return: an int representing the number of entries in the database
    """
    db = connect(database) if database else connect(default_database())
    with closing(db) as d:
        count = d.execute('SELECT COUNT() FROM bodies').fetchone()[0]
    return count


def get_years(database: str = None):
    """Lists, in order, the years in which the database has entries

    :rtype: list
    :param database: a Connection or str representing the database that is being queried
    :return: a list representing the years in which the database has entries
    """
    db = connect(database) if database else connect(default_database())
    with closing(db) as d:
        years = list({x[0] for x in d.execute('SELECT year FROM dates')})
        years.sort()
    return years


def database_is_empty(database: str = None):
    if len(get_all_entry_ids(database if database else default_database())) == 0:
        return True
    else:
        return False


def close_connection(database: Connection):
    """Closes the connection to the database

    :param database: a Connection or str representing the database that is being queried
    """
    database.close()
