import sqlite3
import csv, os
from typing import TextIO, List

def run_query(db: str, db_query: str, args: tuple = None) -> List[tuple]:
    """Return the results of running query db_query on database with name db.

    Optional query argumnts for can be included in args.
    """
    
    con = sqlite3.connect(db)
    cur = con.cursor()
    
    if args is None: 
        cur.execute(db_query)
    else:
        cur.execute(db_query, args)

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

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 create_temp_table(db: str, temperature_file: TextIO) -> None:
    """Create a temperature table in the database db and populate it with the 
    contents of temperature_file. 
    """
    
    con = sqlite3.connect(db)
    cur = con.cursor()

    # Create the Temperature table
    cur.execute('''CREATE TABLE temperature (
    city TEXT, 
    avgHigh REAL, 
    avgLow REAL)''')
    reader = csv.reader(temperature_file)
    # Populate the Temperature Table
    for data in reader:
        cur.execute('''INSERT INTO temperature 
        VALUES(?, ?, ? )''', \
            (data[0], data[1], data[2]))   

    # close the cursor
    cur.close()
    
    #commit the changes
    con.commit() 
    
    # close the connection
    con.close() 

def cold_and_snowy(db: str) -> List[tuple]:
    """Return the cities with at least 250cm of snow and
    average low less than 1 from database db"""
    
    # 1. Decide which tables to combine: precipitation and temperature
    # 2. Which columns make sense to join the tables on?:
    #      precipitation.city = temperature.city
    # 3. Specify the WHERE clause/condition
    #      precipitation.snow >= 250 AND temperature.avgLow < 1
    
    query = '''SELECT precipitation.city
            FROM precipitation JOIN temperature 
            ON precipitation.city = temperature.city
            WHERE  precipitation.snow >= ? AND temperature.avgLow < ?'''
    
    return run_query(db, query, (250, 1))
  
def same_avg_low(db: str) -> List[tuple]:
    """Return the cities from the join of the 
    temperature table with itself such that the
    cities have the same avgerage low.
    """
    
    query = '''SELECT A.city, B.city
               FROM temperature A JOIN temperature B
               ON A.city != B.city
               WHERE A.avgLow = B.avgLow'''
    
    return run_query(db, query)

if __name__ == '__main__':
    db = 'weather.db'
    # create the database weather.db
    make_tables = True
    if make_tables:
        if os.path.exists(db):
            os.remove(db) # deletes database file
    
        # set up the precipitation table
        precipitation_file = open('precipitation.csv')
        create_precipitation_table(db, precipitation_file)
        precipitation_file.close()
            
        # We create a temperature table in the same database as precipitation
        temperature_file = open('temperature.csv')
        create_temp_table(db, temperature_file)
        temperature_file.close()    

    print(cold_and_snowy(db))
    print(same_avg_low(db))