import unittest
import math
import sqlite3
import datetime
try:
    import pyperclip
except:
    print("You will need to import the pyperclip module in order to get this to copy output to the clipboard.")
    
    


def yesterday():
    return datetime.datetime.now() - datetime.timedelta(days=1)


class GetBiggestAreaLakeQueryTests(unittest.TestCase):
    def points(self):
        return 10
    
    def test_name(self):
        return "get_biggest_area_lake_query"

    def test_default_case(self):
        stu_connection = sqlite3.connect('lakeland-lakes.db')
        try:
            stu_connection.execute("PRAGMA foreign_keys = 1")
            stu_cursor = stu_connection.cursor() 

            #test cases
            func = main.get_biggest_area_lake_query
            sql = func()
            correct = "Lake Parker"
            stu_cursor.execute(sql)
            results = stu_cursor.fetchall()
            self.assertTrue(len(results) >= 1, "Doesn't return a result!")
            self.assertTrue(len(results) <= 1, "Returns multiple results when it shouldn't!")
            result = results[0][0]
            self.assertTrue(result == correct, "Doesn't return the correct result on the actual lakes of Lakeland.")


            #modify the table before executing again
            sql = "INSERT INTO lakes VALUES (?, ?, ?, ?, ?, ?)"
            params = ['X', 9999, 10, 10, 5.1, 5.1]
            stu_cursor.execute(sql, params)

            #now make the query again
            sql = func()
            correct = "X"
            stu_cursor.execute(sql)
            results = stu_cursor.fetchall()
            self.assertTrue(len(results) >= 1, "Doesn't return a result!")
            self.assertTrue(len(results) <= 1, "Returns multiple results when it shouldn't!")
            result = results[0][0]
            self.assertTrue(result == correct, "Doesn't return the correct result after I modified the table.")
            
            sql = "DELETE FROM lakes WHERE name = ?"
            params = ('X', )
            stu_cursor.execute(sql, params)
            
            stu_connection.close()
        except Exception as e:
            stu_connection.close()
            raise e



class GetDeepestLakeQueryTests(unittest.TestCase):
    def points(self):
        return 10
    
    def test_name(self):
        return "get_deepest_lake_query"

    def test_default_case(self):
        stu_connection = sqlite3.connect('lakeland-lakes.db')
        try:
            stu_connection.execute("PRAGMA foreign_keys = 1")
            stu_cursor = stu_connection.cursor() 

            #test cases
            func = main.get_deepest_lake_query
            sql = func()
            correct = "Lake Crago"
            stu_cursor.execute(sql)
            results = stu_cursor.fetchall()
            self.assertTrue(len(results) >= 1, "Doesn't return a result!")
            self.assertTrue(len(results) <= 1, "Returns multiple results when it shouldn't!")
            result = results[0][0]
            self.assertTrue(result == correct, "Doesn't return the correct result on the actual lakes of Lakeland.")


            #modify the table before executing again
            sql = "INSERT INTO lakes VALUES (?, ?, ?, ?, ?, ?)"
            params = ['X', 10, 10, 9999, 5.1, 5.1]
            stu_cursor.execute(sql, params)

            #now make the query again
            sql = func()
            correct = "X"
            stu_cursor.execute(sql)
            results = stu_cursor.fetchall()
            self.assertTrue(len(results) >= 1, "Doesn't return a result!")
            self.assertTrue(len(results) <= 1, "Returns multiple results when it shouldn't!")
            result = results[0][0]
            self.assertTrue(result == correct, "Doesn't return the correct result after I modified the table.")
            
            sql = "DELETE FROM lakes WHERE name = ?"
            params = ('X', )
            stu_cursor.execute(sql, params)
            
            stu_connection.close()
        except Exception as e:
            stu_connection.close()
            raise e



class GetBiggestVolumeLakeQueryTests(unittest.TestCase):
    def points(self):
        return 5
    
    def test_name(self):
        return "get_biggest_volume_lake_query"

    def test_default_case(self):
        stu_connection = sqlite3.connect('lakeland-lakes.db')
        try:
            stu_connection.execute("PRAGMA foreign_keys = 1")
            stu_cursor = stu_connection.cursor() 

            #test cases
            func = main.get_biggest_volume_lake_query
            sql = func()
            correct = "Lake Parker"
            stu_cursor.execute(sql)
            results = stu_cursor.fetchall()
            self.assertTrue(len(results) >= 1, "Doesn't return a result!")
            self.assertTrue(len(results) <= 1, "Returns multiple results when it shouldn't!")
            result = results[0][0]
            self.assertTrue(result == correct, "Doesn't return the correct result on the actual lakes of Lakeland.")


            #modify the table before executing again
            sql = "INSERT INTO lakes VALUES (?, ?, ?, ?, ?, ?)"
            params = ['X', 10, 9999999999999, 10, 5.1, 5.1]
            stu_cursor.execute(sql, params)

            #now make the query again
            sql = func()
            correct = "X"
            stu_cursor.execute(sql)
            results = stu_cursor.fetchall()
            self.assertTrue(len(results) >= 1, "Doesn't return a result!")
            self.assertTrue(len(results) <= 1, "Returns multiple results when it shouldn't!")
            result = results[0][0]
            self.assertTrue(result == correct, "Doesn't return the correct result after I modified the table.")
            
            sql = "DELETE FROM lakes WHERE name = ?"
            params = ('X', )
            stu_cursor.execute(sql, params)
            
            stu_connection.close()
        except Exception as e:
            stu_connection.close()
            raise e



class GetHighestAverageDepthLakeQueryTests(unittest.TestCase):
    def points(self):
        return 10
    
    def test_name(self):
        return "get_highest_average_depth_lake_query"

    def test_default_case(self):
        stu_connection = sqlite3.connect('lakeland-lakes.db')
        try:
            stu_connection.execute("PRAGMA foreign_keys = 1")
            stu_cursor = stu_connection.cursor() 

            #test cases
            func = main.get_highest_average_depth_lake_query
            sql = func()
            correct = "Lake Beulah"
            stu_cursor.execute(sql)
            results = stu_cursor.fetchall()
            self.assertTrue(len(results) >= 1, "Doesn't return a result!")
            self.assertTrue(len(results) <= 1, "Returns multiple results when it shouldn't!")
            result = results[0][0]
            self.assertTrue(result == correct, "Doesn't return the correct result on the actual lakes of Lakeland.")


            #modify the table before executing again
            sql = "INSERT INTO lakes VALUES (?, ?, ?, ?, ?, ?)"
            params = ['X', 1, 99999999, 10, 5.1, 5.1]
            stu_cursor.execute(sql, params)

            #now make the query again
            sql = func()
            correct = "X"
            stu_cursor.execute(sql)
            results = stu_cursor.fetchall()
            self.assertTrue(len(results) >= 1, "Doesn't return a result!")
            self.assertTrue(len(results) <= 1, "Returns multiple results when it shouldn't!")
            result = results[0][0]
            self.assertTrue(result == correct, "Doesn't return the correct result after I modified the table.")
            
            sql = "DELETE FROM lakes WHERE name = ?"
            params = ('X', )
            stu_cursor.execute(sql, params)
            
            stu_connection.close()
        except Exception as e:
            stu_connection.close()
            raise e



class GetMostSouthernLakeQueryTests(unittest.TestCase):
    def points(self):
        return 10
    
    def test_name(self):
        return "get_most_southern_lake_query"

    def test_default_case(self):
        stu_connection = sqlite3.connect('lakeland-lakes.db')
        try:
            stu_connection.execute("PRAGMA foreign_keys = 1")
            stu_cursor = stu_connection.cursor() 

            #test cases
            func = main.get_most_southern_lake_query
            sql = func()
            correct = "Lake Miriam"
            stu_cursor.execute(sql)
            results = stu_cursor.fetchall()
            self.assertTrue(len(results) >= 1, "Doesn't return a result!")
            self.assertTrue(len(results) <= 1, "Returns multiple results when it shouldn't!")
            result = results[0][0]
            self.assertTrue(result == correct, "Doesn't return the correct result on the actual lakes of Lakeland.")


            #modify the table before executing again
            sql = "INSERT INTO lakes VALUES (?, ?, ?, ?, ?, ?)"
            params = ['X', 10, 10, 10, -5.1, 5.1]
            stu_cursor.execute(sql, params)

            #now make the query again
            sql = func()
            correct = "X"
            stu_cursor.execute(sql)
            results = stu_cursor.fetchall()
            self.assertTrue(len(results) >= 1, "Doesn't return a result!")
            self.assertTrue(len(results) <= 1, "Returns multiple results when it shouldn't!")
            result = results[0][0]
            self.assertTrue(result == correct, "Doesn't return the correct result after I modified the table.")
            
            sql = "DELETE FROM lakes WHERE name = ?"
            params = ('X', )
            stu_cursor.execute(sql, params)
            
            stu_connection.close()
        except Exception as e:
            stu_connection.close()
            raise e



class GetNumberOfBigLakesQueryTests(unittest.TestCase):
    def points(self):
        return 10
    
    def test_name(self):
        return "get_number_of_big_lakes_query"

    def test_default_case(self):
        stu_connection = sqlite3.connect('lakeland-lakes.db')
        try:
            stu_connection.execute("PRAGMA foreign_keys = 1")
            stu_cursor = stu_connection.cursor() 

            #test cases
            func = main.get_number_of_big_lakes_query
            sql = func()
            correct = 10
            stu_cursor.execute(sql)
            results = stu_cursor.fetchall()
            self.assertTrue(len(results) >= 1, "Doesn't return a result!")
            self.assertTrue(len(results) <= 1, "Returns multiple results when it shouldn't!")
            result = results[0][0]
            self.assertTrue(result == correct, "Doesn't return the correct result on the actual lakes of Lakeland.")


            #modify the table before executing again
            sql = "INSERT INTO lakes VALUES (?, ?, ?, ?, ?, ?)"
            params = ['X', 10, 9999999999999, 10, 5.1, 5.1]
            stu_cursor.execute(sql, params)

            #now make the query again
            sql = func()
            correct = 11
            stu_cursor.execute(sql)
            results = stu_cursor.fetchall()
            self.assertTrue(len(results) >= 1, "Doesn't return a result!")
            self.assertTrue(len(results) <= 1, "Returns multiple results when it shouldn't!")
            result = results[0][0]
            self.assertTrue(result == correct, "Doesn't return the correct result after I modified the table.")
            
            sql = "DELETE FROM lakes WHERE name = ?"
            params = ('X', )
            stu_cursor.execute(sql, params)
            
            stu_connection.close()
        except Exception as e:
            stu_connection.close()
            raise e



class GetNumberWestQueryTests(unittest.TestCase):
    def points(self):
        return 10
    
    def test_name(self):
        return "get_number_west_query"

    def test_default_case(self):
        stu_connection = sqlite3.connect('lakeland-lakes.db')
        try:
            stu_connection.execute("PRAGMA foreign_keys = 1")
            stu_cursor = stu_connection.cursor() 

            #test cases
            func = main.get_number_west_query
            number = 7
            longitude = -82
            params = [longitude]
            sql = func(*params)
            correct = number
            stu_cursor.execute(sql)
            results = stu_cursor.fetchall()
            self.assertTrue(len(results) >= 1, "Doesn't return a result!")
            self.assertTrue(len(results) <= 1, "Returns multiple results when it shouldn't!")
            result = results[0][0]
            self.assertTrue(result == correct, "Doesn't return the correct result on the actual lakes of Lakeland.")

            #try another parameter
            number = 24
            longitude = -81.94
            params = [longitude]
            sql = func(*params)
            correct = number
            stu_cursor.execute(sql)
            results = stu_cursor.fetchall()
            self.assertTrue(len(results) >= 1, "Doesn't return a result!")
            self.assertTrue(len(results) <= 1, "Returns multiple results when it shouldn't!")
            result = results[0][0]
            self.assertTrue(result == correct, "Doesn't return the correct result on the actual lakes of Lakeland.")


            #modify the table before executing again
            sql = "INSERT INTO lakes VALUES (?, ?, ?, ?, ?, ?)"
            params = ['X', 10, 10, 10, 5.1, -90]
            stu_cursor.execute(sql, params)

            #now make the query again
            number = 8
            longitude = -82
            params = [longitude]
            sql = func(*params)
            correct = number
            stu_cursor.execute(sql)
            results = stu_cursor.fetchall()
            self.assertTrue(len(results) >= 1, "Doesn't return a result!")
            self.assertTrue(len(results) <= 1, "Returns multiple results when it shouldn't!")
            result = results[0][0]
            self.assertTrue(result == correct, "Doesn't return the correct result after I modified the table.")


            
            #try another parameter
            number = 25
            longitude = -81.94
            params = [longitude]
            sql = func(*params)
            correct = number
            stu_cursor.execute(sql)
            results = stu_cursor.fetchall()
            self.assertTrue(len(results) >= 1, "Doesn't return a result!")
            self.assertTrue(len(results) <= 1, "Returns multiple results when it shouldn't!")
            result = results[0][0]
            self.assertTrue(result == correct, "Doesn't return the correct result after I modified the table.")
            
            stu_connection.close()
        except Exception as e:
            stu_connection.close()
            raise e



class GetDeepestWesternLakesTests(unittest.TestCase):
    def points(self):
        return 5
    
    def test_name(self):
        return "get_deepest_western_lakes_query"

    def test_default_case(self):
        stu_connection = sqlite3.connect('lakeland-lakes.db')
        try:
            stu_connection.execute("PRAGMA foreign_keys = 1")
            stu_cursor = stu_connection.cursor() 

            #test cases
            func = main.get_deepest_western_lakes_query
            corrects = ['Lake Beulah', 'Lake Bonnet', 'Lake Crago']
            quantity = len(corrects)
            #longitude = -82
            params = []
            sql = func(*params)
            stu_cursor.execute(sql)
            results = stu_cursor.fetchall()
            self.assertTrue(len(results) >= quantity, "Doesn't return enough results!")
            self.assertTrue(len(results) <= quantity, "Returns too many results!")
            answers = []
            for result in results:
                answers.append(result[0])
            for correct in corrects:
                self.assertTrue(correct in answers, "Doesn't get one of the correct lakes in Lakeland's actual lakes.")



            #modify the table before executing again
            sql = "INSERT INTO lakes VALUES (?, ?, ?, ?, ?, ?)"
            params = ['X', 10, 10, 1, -5.1, -90]
            stu_cursor.execute(sql, params)

            #now make the query again
            corrects = ['Lake Beulah', 'Lake Bonnet', 'Lake Crago', 'X']
            quantity = len(corrects)
            #longitude = -82
            params = []
            sql = func(*params)
            stu_cursor.execute(sql)
            results = stu_cursor.fetchall()
            self.assertTrue(len(results) >= quantity, "Doesn't return enough results!")
            self.assertTrue(len(results) <= quantity, "Returns too many results!")
            answers = []
            for result in results:
                answers.append(result[0])
            for correct in corrects:
                self.assertTrue(correct in answers, "Doesn't get one of the correct lakes after I modified the table.")
            
            stu_connection.close()
        except Exception as e:
            stu_connection.close()
            raise e



class GetMostGatorsSightedQueryTests(unittest.TestCase):
    def points(self):
        return 10
    
    def test_name(self):
        return "get_most_gators_sighted_query"

    def test_default_case(self):
        stu_connection = sqlite3.connect('lakeland-lakes.db')
        try:
            stu_connection.execute("PRAGMA foreign_keys = 1")
            stu_cursor = stu_connection.cursor() 
            
            func = main.get_most_gators_sighted_query

            #next, clear out the gator_sightings table
            sql = "DELETE FROM gator_sightings WHERE 1=1"
            stu_cursor.execute(sql)
            
            
            sql = "INSERT INTO gator_sightings VALUES (?, ?, ?)"
            values = ('Lake Hollingsworth', 10, yesterday())
            stu_cursor.execute(sql, values)
            
            params = []
            sql = func(*params)
            stu_cursor.execute(sql)
            results = stu_cursor.fetchall()
            answers = []
            for result in results:
                answers.append(result[0])
            corrects = ['Lake Hollingsworth']
            self.assertTrue(len(results) >= len(corrects), "Didn't return enough results!")
            self.assertTrue(len(results) <= len(corrects), "Returns too many results!")
            for correct in corrects:
                self.assertTrue(correct in answers, "Doesn't get the lake(s) with the highest gator sighting.")
            
            
            sql = "INSERT INTO gator_sightings VALUES (?, ?, ?)"
            values = ('Lake Beulah', 3, yesterday())
            stu_cursor.execute(sql, values)
            
            params = []
            sql = func(*params)
            stu_cursor.execute(sql)
            results = stu_cursor.fetchall()
            answers = []
            for result in results:
                answers.append(result[0])
            corrects = ['Lake Hollingsworth']
            self.assertTrue(len(results) >= len(corrects), "Didn't return enough results!")
            self.assertTrue(len(results) <= len(corrects), "Returns too many results!")
            for correct in corrects:
                self.assertTrue(correct in answers, "Doesn't get the lake(s) with the highest gator sighting.")
            
            
            sql = "INSERT INTO gator_sightings VALUES (?, ?, ?)"
            values = ('Lake Crago', 10, yesterday())
            stu_cursor.execute(sql, values)
            
            params = []
            sql = func(*params)
            stu_cursor.execute(sql)
            results = stu_cursor.fetchall()
            answers = []
            for result in results:
                answers.append(result[0])
            corrects = ['Lake Hollingsworth', 'Lake Crago']
            self.assertTrue(len(results) >= len(corrects), "Didn't return enough results!")
            self.assertTrue(len(results) <= len(corrects), "Returns too many results!")
            for correct in corrects:
                self.assertTrue(correct in answers, "Doesn't get the lake(s) with the highest gator sighting.")
            
            stu_connection.close()
        except Exception as e:
            stu_connection.close()
            raise e



class GetGatorsLakesQueryTests(unittest.TestCase):
    def points(self):
        return 10
    
    def test_name(self):
        return "get_gators_lakes_query"

    def test_default_case(self):
        stu_connection = sqlite3.connect('lakeland-lakes.db')
        try:
            stu_connection.execute("PRAGMA foreign_keys = 1")
            stu_cursor = stu_connection.cursor() 

            #next, clear out the gator_sightings table
            sql = "DELETE FROM gator_sightings WHERE 1=1"
            stu_cursor.execute(sql)
            
            #insert things
            sql = """INSERT INTO gator_sightings VALUES (?, ?, ?)"""
            gator_data = [('Lake Hollingsworth', 10, yesterday()),
                        ('Lake Beulah', 3, yesterday()),
                        ('Lake Jim', 109, yesterday()),
                        ('Lake Crago', 15, yesterday())]
            stu_cursor.executemany(sql, gator_data)

            

            #test cases
            func = main.get_gator_lakes_query
            corrects = ['Lake Jim', 'Lake Hollingsworth', 'Lake Beulah', 'Lake Crago']
            quantity = len(corrects)
            #longitude = -82
            params = []
            sql = func(*params)
            stu_cursor.execute(sql)
            results = stu_cursor.fetchall()
            self.assertTrue(len(results) >= quantity, "Doesn't return enough results!")
            self.assertTrue(len(results) <= quantity, "Returns too many results!")
            answers = []
            for result in results:
                answers.append(result[0])
            for correct in corrects:
                self.assertTrue(correct in answers, "Doesn't get one of the lakes.")
            
            stu_connection.close()
        except Exception as e:
            stu_connection.close()
            raise e



class GetMostGatorsByLakeQueryTests(unittest.TestCase):
    def points(self):
        return 10
    
    def test_name(self):
        return "get_most_gators_by_lake_query"

    def test_default_case(self):
        stu_connection = sqlite3.connect('lakeland-lakes.db')
        try:
            stu_connection.execute("PRAGMA foreign_keys = 1")
            stu_cursor = stu_connection.cursor() 

            #next, clear out the gator_sightings table
            sql = "DELETE FROM gator_sightings WHERE 1=1"
            stu_cursor.execute(sql)
            
            #add our own records for the tests
            sql = """INSERT INTO gator_sightings VALUES (?, ?, ?)
                        """
            gator_data = [('Lake Hollingsworth', 10, yesterday()),
                        ('Lake Beulah', 3, yesterday()),
                        ('Lake Hollingsworth', 8, yesterday()),
                        ('Lake Beulah', 5, yesterday()),
                        ('Lake Jim', 3, yesterday()),
                        ('Lake Crago', 21, yesterday()),
                        ('Lake Jim', 109, yesterday()),
                        ('Lake Crago', 15, yesterday())
            ]
            stu_cursor.executemany(sql, gator_data)

            

            #test cases
            func = main.get_most_gators_by_lake_query
            corrects = [('Lake Jim', 109), ('Lake Hollingsworth', 10), ('Lake Beulah', 5), ('Lake Crago', 21)]
            quantity = len(corrects)
            #longitude = -82
            params = []
            sql = func(*params)
            stu_cursor.execute(sql)
            results = stu_cursor.fetchall()
            self.assertTrue(len(results) >= quantity, "Doesn't return enough results!")
            self.assertTrue(len(results) <= quantity, "Returns too many results!")
            answers = []
            for result in results:
                answers.append(result)
            for correct in corrects:
                self.assertTrue(correct in answers, "Doesn't get one of the (lake, num_gators) pairs.")
            
            stu_connection.close()
        except Exception as e:
            stu_connection.close()
            raise e



   
test_classes = [
    GetBiggestAreaLakeQueryTests,
    GetDeepestLakeQueryTests,
    GetBiggestVolumeLakeQueryTests,
    GetHighestAverageDepthLakeQueryTests,
    GetMostSouthernLakeQueryTests,
    GetNumberOfBigLakesQueryTests,
    GetNumberWestQueryTests,
    GetDeepestWesternLakesTests,
    GetMostGatorsSightedQueryTests,
    GetGatorsLakesQueryTests,
    GetMostGatorsByLakeQueryTests
]


    
        
        
#Actually run the tests:        
        
if __name__ == "__main__":
    import importlib
    file_name = input("What is your team's name?\n")
    student_file_prefix = file_name + "_project3"
    #import student_X as main
    total_score = 0
    total_max = 0
    total_output = ""
    main = importlib.import_module(student_file_prefix)
    for test_class in test_classes:
        tests = test_class()
        test_points = tests.points()
        score = 0
        output = ""
        try:
            tests.test_default_case() 
            output = "All tests passed!"
            score = test_points
        except Exception as e:
            output += str(e)
        total_output = total_output + tests.test_name() + ": " + str(score) + "/" + str(test_points) + "\n  " + output + "\n\n"
        total_score += score
        total_max += test_points
    total_score_output = "Total score: " + str(total_score) + "/" + str(total_max)
    total_output =  total_score_output + "\n\n" + total_output
    print("Scored", score, "points!")
    print(total_output[:-2])
    print(total_score_output)