import unittest
import os

from address_finder.api import *

row1_insee_expected = Commune(
    codeInsee="33063", nom="BORDEAUX", codeZip="33000")
row1_insee_db = {
    DB_COL_COMMUNE_INSEE: row1_insee_expected.get('codeInsee'),
    DB_COL_COMMUNE_NAME: row1_insee_expected.get('nom'),
    DB_COL_COMMUNE_POSTE: row1_insee_expected.get('codeZip')
}
row2_insee_expected = Commune(codeInsee="64445", nom="PAU", codeZip="64000")
row2_insee_db = {
    DB_COL_COMMUNE_INSEE: row2_insee_expected.get('codeInsee'),
    DB_COL_COMMUNE_NAME: row2_insee_expected.get('nom'),
    DB_COL_COMMUNE_POSTE: row2_insee_expected.get('codeZip')
}

rows_db = [row1_insee_expected, row2_insee_expected]

fantoir_insee_to_test = "33063"
fantoir_expected = [
    FantoirVoie(
        id=f"{fantoir_insee_to_test}-9500",
        dateAjout=1987001,
        libelle=["rue toto"],
        typeVoie="voie",
        codeCommune=fantoir_insee_to_test,
        codeFantoir="9500",
        cleRivoli="X",
        nomCommune="BORDEAUX",
        predecesseur=False
    ),
    FantoirVoie(
        id=f"{fantoir_insee_to_test}-9501",
        dateAjout=1987001,
        libelle=["rue tata"],
        typeVoie="voie",
        codeCommune=fantoir_insee_to_test,
        codeFantoir="9501",
        cleRivoli="X",
        nomCommune="BORDEAUX",
        predecesseur=False
    )
]
fantoir_db = {
    DB_COL_FANTOIR_INSEE: f"{DB_FANTOIR_INSEE_KEY_SUFFIX}{fantoir_insee_to_test}",
    'value': json.dumps({
        'value': fantoir_expected,
        'expires': 'null'
    })
}

class TestAddressFinderAPI(unittest.TestCase):

    @classmethod
    def setUpClass(self):
        print("SETUP class")
        try:
            os.remove("testdb.sqlite")
        except OSError:
            pass
        self.conn = sqlite3.connect('testdb.sqlite')
        c = self.conn.cursor()

        # Create table communes (insee)
        c.execute(f'''CREATE TABLE {DB_TABLE_INSEE_NAME}
                  ({DB_COL_COMMUNE_INSEE} text, {DB_COL_COMMUNE_NAME} text, {DB_COL_COMMUNE_POSTE} text)''')
        # Create table fantoir voies
        c.execute(f'''CREATE TABLE {DB_TABLE_FANTOIR_NAME}
                  ({DB_COL_FANTOIR_INSEE} text, value text)''')

        c.execute(
            f"INSERT INTO {DB_TABLE_INSEE_NAME} VALUES (:{DB_COL_COMMUNE_INSEE}, :{DB_COL_COMMUNE_NAME}, :{DB_COL_COMMUNE_POSTE})", row1_insee_db)

        c.execute(
            f"INSERT INTO {DB_TABLE_INSEE_NAME} VALUES (:{DB_COL_COMMUNE_INSEE}, :{DB_COL_COMMUNE_NAME}, :{DB_COL_COMMUNE_POSTE})", row2_insee_db)

        c.execute(
            f"INSERT INTO {DB_TABLE_FANTOIR_NAME} VALUES (:{DB_COL_FANTOIR_INSEE}, :value)", fantoir_db)

        # Save (commit) the changes
        self.conn.commit()

    def test_no_db_returns_empty(self):
        try:
            os.remove("nodbhere.sqlite")
            addressFinder = AddressFinder("nodbhere.sqlite")
            communes = addressFinder.getCommunesFromNameOrZip(None)
            self.assertEqual(len(communes), 0)
            voies = addressFinder.getCommuneFantoirVoies(fantoir_insee_to_test)
            self.assertEqual(len(voies), 0)
            os.remove("nodbhere.sqlite")
        except OSError:
            pass

    def test_get_all_communes(self):
        addressFinder = AddressFinder("testdb.sqlite")
        communes = addressFinder.getCommunesFromNameOrZip(None)
        self.assertEqual(len(communes), 2)
        self.assertDictEqual(communes[0], row1_insee_expected)
        self.assertDictEqual(communes[1], row2_insee_expected)

    def test_get_commune_by_name(self):

        addressFinder = AddressFinder("testdb.sqlite")
        communes = addressFinder.getCommunesFromNameOrZip(
            row1_insee_expected.get('nom'))
        self.assertEqual(len(communes), 1)
        self.assertDictEqual(communes[0], row1_insee_expected)

    def test_get_commune_by_zip(self):

        addressFinder = AddressFinder("testdb.sqlite")
        communes = addressFinder.getCommunesFromNameOrZip(
            row2_insee_expected.get('codeZip'))
        self.assertEqual(len(communes), 1)
        self.assertDictEqual(communes[0], row2_insee_expected)

    def test_get_no_commune_bad_search(self):

        addressFinder = AddressFinder("testdb.sqlite")
        communes = addressFinder.getCommunesFromNameOrZip("baaaaad name")
        self.assertEqual(len(communes), 0)

    def test_get_voies_by_valid_insee(self):

        addressFinder = AddressFinder("testdb.sqlite")
        voies = addressFinder.getCommuneFantoirVoies(fantoir_insee_to_test)
        self.assertEqual(len(voies), 2)
        self.assertDictEqual(voies[0], fantoir_expected[0])
        self.assertDictEqual(voies[1], fantoir_expected[1])

    def test_get_no_voies_not_valid_insee(self):
        addressFinder = AddressFinder("testdb.sqlite")
        voies = addressFinder.getCommuneFantoirVoies("baaaaad")
        self.assertEqual(len(voies), 0)

    @classmethod
    def tearDownClass(self):
        self.conn.close()
        try:
            os.remove("testdb.sqlite")
        except OSError:
            pass


if __name__ == "__main__":
    unittest.main()