import sqlite3
import csv
from typing import TextIO, List

def create_precipitation_table(db: str, data_file: TextIO) -> None:
    """Populate the database db with the contents of data_file
    as follows:
    Create a table called precipitation with four columns:
    city (text), snow (real), total (integer), days (integer).
    
    data_file (csv file): every line contatins one city, 
    snowfall amount, total precipitation amount, 
    and number of days per line
    """
    
    # Connect to database db
    con = sqlite3.connect(db)
    
    # Get the cursor
    cur = con.cursor()
    
    # Create precipitation table
    cur.execute(
        """CREATE TABLE precipitation (
        city TEXT,
        snow REAL,
        total INTEGER,
        days INTEGER)""")
    
    # Populate the precipitation table
    # Loop through each line in the csv file and insert data into the table
    reader = csv.reader(data_file)
    for line in reader:
        city = line[0]
        snow = line[1]
        total = line[2]
        days = line[3]
        
        # Now, we execute a 'INSERT' SQL query
        cur.execute("""INSERT INTO precipitation
        VALUES(?, ?, ?, ?)""", (city, snow, total, days))
  
    # commit (save) changes
    con.commit()
    # Close the cursor
    cur.close()
    # Close the connection
    con.close()  

def print_all_precipitation(db: str) -> None:
    """Print all records in the precipitation table
    in database with name db, one per line"""
    
    # Connect to the database before we run our query
    con = sqlite3.connect(db)
    
    # Get the cursor
    cur = con.cursor()  
    
    # Get the data from the database (also known as 'select'ing data)
    # To get the data, we use the SELECT statement
    # By default SELECT will give you all the rows
    cur.execute('SELECT * FROM precipitation')
    # This query is saying: 
    #'Select all columns from the table precipitation.'
    
    # Method 1: Fetch and print everything selected:
    #result = cur.fetchall()
    #print(result)
    #for record in result:
        #print(record)
       
    # Method 2: Iterate through the cursor
    #for record in cur:
        #print(record)
    
    # Method 3: Using the fetchone() method 
    record = cur.fetchone()
    while record != None:
        print(record)
        record = cur.fetchone()
    
    # If you want just the first 3 records:
    #for i in range(3):
        #result = cur.fetchone()
        #print(result)
    
    
    # No need to commit/save here - only getting info from database
    # Close the cursor
    cur.close()
    # Close the connection
    con.close()     
    
def city_snow(db: str) -> List[tuple]:
    """Return a list of tuples with the city and snow information
    [(city, snow),...] from the precipitation table in 
    the database with name db.
    """
    
    # Connect to the database before we run our query
    con = sqlite3.connect(db)
    
    # Get the cursor
    cur = con.cursor()  
    
    # This query specifies the names of the columns we want
    # to select and the table name that these columns exist in
    cur.execute('SELECT city, snow FROM precipitation')
    # This query is saying: 
    #'Select the city and snow columns from the table precipitation.'
    
    # Possible errors:
    #cur.execute('SELECT city, snow FROM precipita')
    #cur.execute('SELECT city, snowstorm FROM precipitation')
    
    # Changing the column order works fine, although it does
    # not follow the docstring description in this case
    #cur.execute('SELECT snow, city FROM precipitation')
    
    result = cur.fetchall()
    
    # No need to commit/save here - only getting info from database
    # Close the cursor
    cur.close()
    # Close the connection
    con.close() 
    
    return result
    
# Let's make a helper function for running queries (SELECT statements)
def run_query(db: str, db_query: str) -> List[tuple]:
    """Return the results of running db_query on database
    with name db.
    """
    # Connect to the database before we run our query
    con = sqlite3.connect(db)
    
    # Get the cursor
    cur = con.cursor()  
    
    cur.execute(db_query)

    result = cur.fetchall()
    
    # No need to commit/save here - only getting info from database
    # Close the cursor
    cur.close()
    # Close the connection
    con.close()  
    # Return the result of the query
    return result
    
def city_total(db: str) -> List[tuple]:
    """Return a list of tuples with the city and total information
    [(city, total),...] from the precipitation table in 
    the database with name db.
    """
    # Write the query
    query = 'SELECT city, total FROM precipitation'
    
    # Call our run_query function and return its return value
    return run_query(db, query)

# Queries with Conditional statements (kind of like if-statements
# that look for a specific property on record values)
def snow_over_200(db: str) -> List[tuple]:
    """Return a list of cities from the population table
    in the databse db, where the amount of snowfall is > 200cm
    
    Do not use Python if-statements - only make this work
    with a SQL query
    """
    # Write the query
    query = 'SELECT city, snow FROM precipitation WHERE snow > 200'
    
    # run the query
    return run_query(db, query)

def snow_over_200_v2(db: str) -> List[tuple]:
    """Return a list of cities from the population table
    in the databse db, where the amount of snowfall is > 200cm
    
    Do not use Python if-statements - only make this work
    with a SQL query
    """
    
    con = sqlite3.connect(db)    
    cur = con.cursor()  
    
    cur.execute("""SELECT city, snow FROM precipitation 
                   WHERE snow > ?""", (200,))

    result = cur.fetchall()
    
    cur.close()
    con.close()  
    return result

# A run_query that takes an optional argument
def run_query_v2(db: str, db_query: str, args: tuple = None) -> List[tuple]:
    """Return the results of running db_query on database
    with name db.
    """
    # Connect to the database before we run our query
    con = sqlite3.connect(db)
    
    # Get the cursor
    cur = con.cursor()  
    
    if args is None: # if no arguments are given:
        cur.execute(db_query)
    else:
        cur.execute(db_query, args)

    result = cur.fetchall()
    
    cur.close()
    con.close()  
    return result

def snow_over_200_v3(db: str) -> List[tuple]:
    """Return a list of cities from the population table
    in the databse db, where the amount of snowfall is > 200cm
    
    Do not use Python if-statements - only make this work
    with a SQL query
    """
    # Write the query
    query = 'SELECT city, snow FROM precipitation WHERE snow > ?'
    
    # run the query
    return run_query_v2(db, query, (200,))
    
if __name__ == '__main__':
    db = 'weather.db'
    # create the database weather.db
    create_database = True
    
    if create_database:
        # set up the precipitation table
        precipitation_file = open('precipitation.csv')
        create_precipitation_table(db, precipitation_file)
        precipitation_file.close()
        
    #print_all_precipitation(db)
    print(city_snow(db))
    print(city_total(db))
    print(snow_over_200(db))
    print(snow_over_200_v2(db))
    print(snow_over_200_v3(db))