'''A tiny in-memory database for use in CS
304 examples.

The database stores a list of tuples, where each tuple represents a country,
with columns for the id of the country (a numeric identifier), the name of the
country, the name of the capital, and the continent it is on. 

There are several search functions and an insert function. We mostly will *not*
use the insert function, as we will be using the database as a read-only most of
the time.

v1. Fall 2024 Scott D. Anderson 
v2. July 2025 Scott D. Anderson

'''

# this is the in-memory database, which is a list of tuples. The first
# tuple is a dummy row, which is used to provide column names. The rest of the
# tuples are the actual data rows.

country_tuples = [
    # dummy row in element zero
    ('cid', 'country', 'capital', 'continent'), 
    (1, 'France', 'Paris', 'Europe'),
    (2, 'Germany', 'Berlin', 'Europe'),
    (3, 'Italy', 'Rome', 'Europe'),
    (4, 'China', 'Beijing', 'Asia'),
    (5, 'India', 'New Dehli', 'Asia'),
    (6, 'Japan', 'Tokyo', 'Asia'),
    (7, 'United States', 'Washingon, DC', 'North America'),
    (8, 'Canada', 'Montreal', 'North America'),
    (9, 'Mexico', 'Mexico City', 'North America'),
    (10, 'Ghana', 'Accra', 'Africa'),
    (11, 'Nigeria', 'Abuja', 'Africa'),
    (12, 'Kenya', 'Nairobi', 'Africa'),
    (13, 'Brazil', 'Brasília', 'South America'),
    (14, 'Chile', 'Santiago', 'South America'),
    (15, 'Argentina', 'Buenos Aires', 'South America'),
]

def lookup_by_id(cid):
    '''Returns a country's data, looking it up by id in the list of
    tuples. Returns None if not found.'''
    if cid < 0 or cid >= len(country_tuples):
        return None
    else:
        return country_tuples[cid]

def column_index(column_name):
    '''Returns the index of a column in the country_tuples list. Returns -1 
    if the column name is not found.'''
    for idx,name in enumerate(country_tuples[0]):
        if name == column_name:
            return idx
    # if we get here, the column name was not found
    return -1

def lookup_by_col_one(column_name, column_value, as_dict=False):
    '''Returns a country's data, looking it up by name in the list of
    tuples. Returns the first match. Returns None if not found. Raises as
    exception if the column name is not valid. If as_dict is True, 
    returns the result as a dictionary with column names as keys, 
    otherwise returns a tuple.'''
    idx = column_index(column_name)
    if idx == -1:
        raise Exception(f'no such column: {column_name}')
    for ct in country_tuples:
        if ct[idx] == column_value:
            if as_dict:
                return dict(zip(country_tuples[0], ct))
            else:
                return ct
            return ct
    # the following is not necessary, but it's nice to be clear
    return None

def lookup_by_col_all(column_name, column_value, as_dict=False):
    '''Returns a list of all countries' data matching column_value 
    in column_name.
    Returns an empty list if none found. Raises an exception if the column name
    is not valid. If as_dict is True, returns the results as a list of
    dictionaries with column names as keys, otherwise returns a list of tuples.'''
    idx = column_index(column_name)
    if idx == -1:
        raise Exception(f'no such column: {column_name}')
    results = []
    for ct in country_tuples:
        if ct[idx] == column_value:
            if as_dict:
                results.append(dict(zip(country_tuples[0], ct)))
            else:
                results.append(ct)
    return results

def insert_country(cname, capital, continent):
    '''Inserts a new country into the database. The country must not already be
    in the database, and we use the name of the country to check. If the 
    country is not in the database, we add it with a new id, which is the 
    next available index in the list. The function returns the index of the 
    newly inserted country. It raises an exception if the country is already 
    in the database.'''
    c = lookup_by_col_one('country',cname) 
    if c is not None:
        raise Exception(f'country {cname} is already in the database')
    cid = len(country_tuples) 
    country_tuples.append((cid, cname, capital, continent))

if __name__ == '__main__':
    print('Testing the country database functions...')
    # some simple tests 
    print(lookup_by_id(1))  # should return the tuple for France
    print(lookup_by_col_one('country', 'Japan'))  # should return Japan
    print(lookup_by_col_one('capital', 'Berlin'))
    print(lookup_by_col_one('continent', 'Africa')) # first African country
    print(lookup_by_col_all('continent', 'Africa')) # all African countries
    print(lookup_by_col_one('country', 'India', True))
    print(lookup_by_col_one('capital', 'Ottowa', True))
    print(lookup_by_col_one('continent', 'Europe', True)) # first African country
    print(lookup_by_col_all('continent', 'Europe', True)) # all African countries
    spain_id = insert_country('Spain', 'Madrid', 'Europe')
    print(f'Inserted Spain with id {spain_id}')  # should print the id of Spain
    print(lookup_by_col_one('country', 'Spain'))  # should return the tuple
    print(lookup_by_col_all('continent', 'Europe'))  # returns all European countries
    print(lookup_by_col_all('continent', 'Europe', as_dict=True))  
    france_id = insert_country('France', 'Paris', 'Europe') # should raise an exception