class SqliteDatabaseTest(unittest.TestCase):
    def setUp(self):
        self.assertEqual(
            config.get_environment(),
            'unittest')  # Distructive Tests must run in unittest enviornment
        self.dbi = config.get_database_instance()
        self.db = config.get_database()
        self.dbu = DatabaseUtilities()
        self.db_create = DatabaseCreate()

        self.dbu.delete_all_tables()

    def test_to_db_value(self):
        self.assertEqual('123', self.db.to_db_value(123))
        self.assertEqual('"asdf"', self.db.to_db_value("asdf"))
        self.assertEqual('123.45', self.db.to_db_value(123.45))
        self.assertEqual('1', self.db.to_db_value(True))
        self.assertEqual('0', self.db.to_db_value(False))
        self.assertEqual('null', self.db.to_db_value(None))

    def test_get_data_structure(self):
        ds = self.db.get_data_structure('WhatEver')
        self.assertEqual('WhatEver', ds._table_name)

    def test_select(self):
        self.dbu.create_some_test_well_royalty_masters()
        self.dbu.create_some_test_leases()

        self.assertIsNotNone(
            self.db.select('Well', RoyaltyClassification='New Oil'))
        self.assertEqual(
            len(self.db.select('Well', Prov='SK', Classification='Other')), 2)
        self.assertEqual(len(self.db.select('Well')), 4)
        self.assertRaises(AppError, self.db.select, 'WrongTable')
        self.assertRaises(AppError,
                          self.db.select,
                          'WrongTable',
                          WrongAttr='WhoCares')
        self.assertRaises(AppError, self.db.select, 'Well', Foo='bar')
        self.assertEqual(len(self.db.select('Lease')), 3)
        self.assertEqual(len(self.db.select('Lease', Prov='SK')), 3)
        self.assertEqual(len(self.db.select('Well', ID=1000)), 0)

    def test_update(self):
        self.dbu.create_some_test_well_royalty_masters()

        # change all types of attributes, read another record and then read the record again to make sure the changes were made.
        well = self.db.select('Well', ID=2)
        well[0].UWI = 'Changed'
        well[0].LeaseID = 100
        well[0].CommencementDate = '2016-02-01 00:00:00'
        well[0].WellType = None
        self.db.update(well[0])
        well = self.db.select('Well', ID=1)
        self.assertEqual(well[0].ID, 1)
        self.assertEqual(well[0].UWI, 'SKWI111062705025W300')
        well = self.db.select('Well', ID=2)
        self.assertEqual(well[0].ID, 2)
        self.assertEqual(well[0].UWI, 'Changed')
        self.assertEqual(well[0].LeaseID, 100)
        self.assertEqual(well[0].CommencementDate, datetime(2016, 2, 1, 0, 0))
        self.assertEqual(well[0].WellType, None)

        ds = DataStructure()
        self.assertRaises(AttributeError, self.db.update, ds)

        ds._table_name = 'Well'
        self.assertRaises(AttributeError, self.db.update, ds)
        ds.ID = 100
        self.assertRaises(AppError, self.db.update, ds)

    def test_insert(self):
        self.db_create.well()

        well = DataStructure()
        well.UWI = 'UWI for this well'
        # Should raise this error since we need to get the structure from the database
        self.assertRaises(TypeError, self.db.insert)

        well = self.db.get_data_structure('Well')
        well.UWI = 'UWI for this well'
        self.db.insert(well)
        self.assertEqual(well.ID, 1)

        well = self.db.get_data_structure('Well')
        well.UWI = 'Different UWI for this well'
        self.db.insert(well)
        self.assertEqual(well.ID, 2)

        well = self.db.select('Well', ID=1)
        self.assertEqual(well[0].ID, 1)
        self.assertEqual(well[0].UWI, 'UWI for this well')

        well = self.db.get_data_structure('Well')
        well.UWI = 'Next Well UWI'
        well.ID = 10
        self.db.insert(well)

        well = self.db.select('Well', ID=1)
        self.assertEqual(well[0].ID, 1)
        self.assertEqual(well[0].UWI, 'UWI for this well')

        well = self.db.select('Well', ID=10)
        self.assertEqual(well[0].ID, 10)
        self.assertEqual(well[0].UWI, 'Next Well UWI')

        well = self.db.get_data_structure('Well')
        well.UWI = 'Just One More'
        self.db.insert(well)
        self.assertEqual(well.ID, 11)

        well = self.db.get_data_structure('Well')
        well.BadAttr = 'Just another value'
        self.assertRaises(AppError, self.db.insert, well)

        # if the ID is None,Blank,or zero we shold still be able to insert a record
        well = self.db.get_data_structure('Well')
        well.ID = None
        well.UWI = 'Just One More'
        self.db.insert(well)
        self.assertEqual(well.ID, 12)
        well.ID = 0
        self.db.insert(well)
        self.assertEqual(well.ID, 13)
        well.ID = ''
        self.db.insert(well)
        self.assertEqual(well.ID, 14)

    def test_delete(self):
        self.dbu.create_some_test_well_royalty_masters()

        self.assertEqual(4, len(self.db.select('Well')))

        self.db.delete('Well', 2)
        self.assertEqual(3, len(self.db.select('Well')))
        self.assertEqual(0, len(self.db.select('Well', ID=2)))

    def test_date_format(self):
        self.dbu.create_some_test_well_royalty_masters()

        well = self.db.select('Well', ID=1)
        self.assertTrue(isinstance(well[0].CommencementDate, datetime))
class DatabaseUtilities(object):

    def __init__(self):
        self.db_instance = config.get_database_instance()
        self.db_create = DatabaseCreate()
    
    def delete_all_tables(self):
        """ Used only for unit tests. It was put here so the database itself did not have distructive code in it """
        for table in self.db_instance.get_table_names():
            self.delete_table(table)
        self.db_instance.commit()
        
    def delete_table(self,table):
        self.db_instance.execute('DROP TABLE %s' % table)

    def create_some_test_wells(self):
        if not 'Well' in self.db_instance.get_table_names():
            self.db_create.well()
            
        statement = """
            INSERT INTO Well VALUES(1,'SKWI111062705025W300','SK','Oil','OL',1,'New Oil','Heavy',0,0.25,'2014-12-01 00:00:00',1);
            INSERT INTO Well VALUES(2,'SKWI112062705025W300','SK','Oil','OL',2,'Third Tier Oil','Southwest',0,0.95,'2014-12-01 00:00:00',1);
            INSERT INTO Well VALUES(3,'SKWI113062705025W300','SK','Oil','OL',2,'Fourth Tier Oil','Other',0,1.0,NULL,NULL);
            INSERT INTO Well VALUES(4,'SKWI114062705025W300','SK','Oil','OL',3,'Old Oil','Other',0,1.0,NULL,NULL);
        """
        
        self.db_instance.execute_statement(statement)

    def create_some_test_royalties(self):
        if not 'RoyaltyMaster' in self.db_instance.get_table_names():
            self.db_create.royalty_master()

        statement = """
            INSERT INTO RoyaltyMaster VALUES(3, 'OL', 'All', 'SKProvCrownVar, GORR', 1.2, 50, 'SaskWellHead', 'Y', 'Y', 'mprod,250,2,300,3,400,4,500,5,0,6', NULL)
        """

        self.db_instance.execute_statement(statement)


    def create_some_test_monthly(self):
        if not 'Monthly' in self.db_instance.get_table_names():
            self.db_create.monthly()

        statement = """
            INSERT INTO Monthly Values(4, '2015-09-29 00:00:00', 201501, 4, 'Oil', 2, 740, 100, 2.2, 221.123456, 2.123455, 0.123455)
        """
        self.db_instance.execute_statement(statement)

    def create_calc(self):
        if not 'Calc' in self.db_instance.get_table_names():
            self.db_create.calc()

    def create_some_test_leases(self):
        if not 'Lease' in self.db_instance.get_table_names():
            self.db_create.lease()
        statement = """
            INSERT INTO Lease VALUES(1,'OL','SK',123,2345,NULL);
            INSERT INTO Lease VALUES(2,'OL','SK',123,2346,NULL);
            INSERT INTO Lease VALUES(3,'OL','SK',123,2347,NULL);
        """
        self.db_instance.execute_statement(statement)

    def create_some_test_econdata(self):
        if not 'ECONdata' in self.db_instance.get_table_names():
            self.db_create.econ_data()
        statement = """
            INSERT INTO ECONData VALUES(39,'Jan.',201501,181,223,262,0.0934,2.34,23.12,1734,21.73,502,27.11,626,0.1085,2.71,
            26.84,2013,32.38,747,38.94,899,0.1181,2.95,29.22,2192,35.58,821,40.54,936,52.3,1207);
        """
        self.db_instance.execute_statement(statement)
class DatabaseUtilities(object):

    def __init__(self):
        self.db_instance = config.get_database_instance()
        self.db_create = DatabaseCreate()
    
    def delete_all_tables(self):
        """ Used only for unit tests. It was put here so the database itself did not have distructive code in it """
        for table in self.db_instance.get_table_names():
            self.delete_table(table)
        self.db_instance.commit()
        
    def delete_table(self,table):
        self.db_instance.execute('DROP TABLE %s' % table)

    def create_some_test_wells(self):
        if not 'Well' in self.db_instance.get_table_names():
            self.db_create.well()
            
        statement = """
            INSERT INTO Well VALUES(1,'SKWI111062705025W300','SK','Oil','OL',1,'New Oil','Heavy',0,0.25,'2014-12-01 00:00:00',1);
            INSERT INTO Well VALUES(2,'SKWI112062705025W300','SK','Oil','OL',2,'Third Tier Oil','Southwest',0,0.95,'2014-12-01 00:00:00',1);
            INSERT INTO Well VALUES(3,'SKWI113062705025W300','SK','Oil','OL',2,'Fourth Tier Oil','Other',0,1.0,NULL,NULL);
            INSERT INTO Well VALUES(4,'SKWI114062705025W300','SK','Oil','OL',3,'Old Oil','Other',0,1.0,NULL,NULL);
        """
        
        self.db_instance.execute_statement(statement)

    def create_some_test_royalties(self):
        if not 'RoyaltyMaster' in self.db_instance.get_table_names():
            self.db_create.royalty_master()

        statement = """
            INSERT INTO RoyaltyMaster VALUES(3, 'OL', 'All', 'SKProvCrownVar, GORR', 1.2, 50, 'SaskWellHead', 'Y', 'Y', 'mprod,250,2,300,3,400,4,500,5,0,6', NULL)
        """

        self.db_instance.execute_statement(statement)


    def create_some_test_monthly(self):
        if not 'Monthly' in self.db_instance.get_table_names():
            self.db_create.monthly()

        statement = """
            INSERT INTO Monthly Values(4, '2015-09-29 00:00:00', 201501, 4, 'Oil', 2, 740, 100, 2.2, 221.123456, 2.123455, 0.123455)
        """
        self.db_instance.execute_statement(statement)

    def create_calc(self):
        if not 'Calc' in self.db_instance.get_table_names():
            self.db_create.calc()

    def create_some_test_leases(self):
        if not 'Lease' in self.db_instance.get_table_names():
            self.db_create.lease()
        statement = """
            INSERT INTO Lease VALUES(1,'OL','SK',123,2345,NULL);
            INSERT INTO Lease VALUES(2,'OL','SK',123,2346,NULL);
            INSERT INTO Lease VALUES(3,'OL','SK',123,2347,NULL);
        """
        self.db_instance.execute_statement(statement)

    def create_some_test_econdata(self):
        if not 'ECONdata' in self.db_instance.get_table_names():
            self.db_create.econ_oil()
        statement = """
            INSERT INTO ECONData VALUES(39,'Jan.',201501,181,223,262,0.0934,2.34,23.12,1734,21.73,502,27.11,626,0.1085,2.71,
            26.84,2013,32.38,747,38.94,899,0.1181,2.95,29.22,2192,35.58,821,40.54,936,52.3,1207);
        """
        self.db_instance.execute_statement(statement)
class SqliteDatabaseTest(unittest.TestCase):

    def setUp(self):
        self.assertEqual(config.get_environment(),'unittest') # Distructive Tests must run in unittest enviornment
        self.dbi = config.get_database_instance()
        self.db = config.get_database()
        self.dbu = DatabaseUtilities()
        self.db_create = DatabaseCreate()
        
        self.dbu.delete_all_tables()
        
    def test_to_db_value(self):
        self.assertEqual('123', self.db.to_db_value(123))
        self.assertEqual('"asdf"', self.db.to_db_value("asdf"))
        self.assertEqual('123.45', self.db.to_db_value(123.45))
        self.assertEqual('1', self.db.to_db_value(True))
        self.assertEqual('0', self.db.to_db_value(False))
        self.assertEqual('null', self.db.to_db_value(None))
        
    def test_get_data_structure(self):
        ds = self.db.get_data_structure('WhatEver')
        self.assertEqual('WhatEver',ds._table_name)

    def test_select(self):
        self.dbu.create_some_test_well_royalty_masters()
        self.dbu.create_some_test_leases()
        
        self.assertIsNotNone(self.db.select('Well', RoyaltyClassification='New Oil'))
        self.assertEqual(len(self.db.select('Well', Prov='SK', Classification='Other')), 2)
        self.assertEqual(len(self.db.select('Well')), 4)
        self.assertRaises(AppError, self.db.select, 'WrongTable')
        self.assertRaises(AppError, self.db.select, 'WrongTable',WrongAttr='WhoCares')
        self.assertRaises(AppError, self.db.select, 'Well', Foo='bar')
        self.assertEqual(len(self.db.select('Lease')), 3)
        self.assertEqual(len(self.db.select('Lease', Prov='SK')), 3)
        self.assertEqual(len(self.db.select('Well', ID=1000)),0)
        
    def test_update(self):
        self.dbu.create_some_test_well_royalty_masters()

        # change all types of attributes, read another record and then read the record again to make sure the changes were made.
        well = self.db.select('Well', ID=2)
        well[0].UWI = 'Changed'
        well[0].LeaseID = 100
        well[0].CommencementDate = '2016-02-01 00:00:00'
        well[0].WellType = None
        self.db.update(well[0])
        well = self.db.select('Well', ID=1)
        self.assertEqual(well[0].ID, 1)
        self.assertEqual(well[0].UWI, 'SKWI111062705025W300')
        well = self.db.select('Well', ID=2)
        self.assertEqual(well[0].ID, 2)
        self.assertEqual(well[0].UWI, 'Changed')
        self.assertEqual(well[0].LeaseID, 100)
        self.assertEqual(well[0].CommencementDate, datetime(2016,2,1,0,0))
        self.assertEqual(well[0].WellType, None)

        ds = DataStructure()
        self.assertRaises(AttributeError, self.db.update, ds)
        
        ds._table_name = 'Well'
        self.assertRaises(AttributeError, self.db.update, ds)
        ds.ID = 100
        self.assertRaises(AppError, self.db.update, ds)

    def test_insert(self):
        self.db_create.well()
        
        well = DataStructure()
        well.UWI = 'UWI for this well'
        # Should raise this error since we need to get the structure from the database
        self.assertRaises(TypeError, self.db.insert)

        well = self.db.get_data_structure('Well')
        well.UWI = 'UWI for this well'
        self.db.insert(well)
        self.assertEqual(well.ID, 1)
        
        well = self.db.get_data_structure('Well')
        well.UWI = 'Different UWI for this well'
        self.db.insert(well)
        self.assertEqual(well.ID, 2)
        
        well = self.db.select('Well', ID=1)
        self.assertEqual(well[0].ID, 1)
        self.assertEqual(well[0].UWI, 'UWI for this well')
        
        well = self.db.get_data_structure('Well')
        well.UWI = 'Next Well UWI'
        well.ID = 10
        self.db.insert(well)
        
        well = self.db.select('Well', ID=1)
        self.assertEqual(well[0].ID, 1)
        self.assertEqual(well[0].UWI, 'UWI for this well')
        
        well = self.db.select('Well', ID=10)
        self.assertEqual(well[0].ID, 10)
        self.assertEqual(well[0].UWI, 'Next Well UWI')

        well = self.db.get_data_structure('Well')
        well.UWI = 'Just One More'
        self.db.insert(well)
        self.assertEqual(well.ID, 11)
        

        well = self.db.get_data_structure('Well')
        well.BadAttr = 'Just another value'
        self.assertRaises(AppError, self.db.insert,well)
        
        # if the ID is None,Blank,or zero we shold still be able to insert a record
        well = self.db.get_data_structure('Well')
        well.ID = None
        well.UWI = 'Just One More'
        self.db.insert(well)
        self.assertEqual(well.ID, 12)
        well.ID = 0
        self.db.insert(well)
        self.assertEqual(well.ID, 13)
        well.ID = ''
        self.db.insert(well)
        self.assertEqual(well.ID, 14)
        
        
    def test_delete(self):
        self.dbu.create_some_test_well_royalty_masters()

        self.assertEqual(4,len(self.db.select('Well')))
                         
        self.db.delete('Well', 2)
        self.assertEqual(3,len(self.db.select('Well')))
        self.assertEqual(0, len(self.db.select('Well', ID=2)))
        
    def test_date_format(self):
        self.dbu.create_some_test_well_royalty_masters()
        
        well = self.db.select('Well', ID=1)
        self.assertTrue(isinstance(well[0].CommencementDate,datetime))