import psycopg2
import os
from psycopg2 import Error
from dotenv import load_dotenv


class DataBase():
    def __init__(self):
        # env variables for database connection
        load_dotenv()
        DATABASE = os.getenv('DATABASE')
        USER = os.getenv('DB_USER')
        PASSWORD = os.getenv('PASSWORD')
        HOST = os.getenv('HOST')
        PORT = os.getenv('PORT')

        self.words_table = "words"
        self.languages_table = "languages"


        try:
            self.connection = psycopg2.connect(user=USER,
                                  password=PASSWORD,
                                  host=HOST,
                                  port=PORT,
                                  database=DATABASE)
            self.cursor = self.connection.cursor()
        except (Exception, Error) as error:
            print("Error while connecting to PostgreSQL", error)


        create_languages_table = """
            CREATE TABLE IF NOT EXISTS {} (
                id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 
                language VARCHAR(50)
	        );"""

        create_words_table = """
            CREATE TABLE IF NOT EXISTS {} (
                word1 TEXT, 
                id_lang1 INTEGER, 
                word2 TEXT, 
                id_lang2 INTEGER,
                
                CONSTRAINT fk_language1
                    FOREIGN KEY(id_lang1)
                        REFERENCES languages(id),
                        
                CONSTRAINT fk_language2
                    FOREIGN KEY(id_lang2)
                        REFERENCES languages(id)
            );"""
        try:
            self.cursor.execute(create_languages_table.format(self.languages_table))
            self.connection.commit()
            self.cursor.execute(create_words_table.format(self.words_table))
            self.connection.commit()
        except (Exception, Error) as error:
            print("Error while creating table ", error)

    def checkData(self, data):
        for item in data:
            if not all(x.isalpha() or x.isspace() for x in item):
                return 1

        lang1 = data[1]
        lang2 = data[3]

        get_lang_id_command = "SELECT id FROM languages WHERE (language = '{}')"
        self.cursor.execute(get_lang_id_command.format(lang1))
        id_lang1 = self.cursor.fetchone()[0]

        self.cursor.execute(get_lang_id_command.format(lang2))
        self.cursor.execute(get_lang_id_command.format(lang2))
        id_lang2 = self.cursor.fetchone()[0]

        if id_lang1 > id_lang2:
            data = self.reverseWords(data)

        command = """
            SELECT * FROM {}
                WHERE word1 = '{}' AND 
                id_lang1 = (SELECT id FROM languages WHERE (language = '{}')) AND 
                word2 = '{}' AND 
                id_lang2 = (SELECT id FROM languages WHERE (language = '{}')); 
            """
        self.cursor.execute(command.format(self.words_table, data[0], data[1], data[2], data[3]))

        if self.cursor.rowcount != 0:
            return 2
        return 0

    def getLanguages(self):
        select_command = "SELECT language FROM {}"
        try:
            self.cursor.execute(select_command.format(self.languages_table))
            return self.cursor.fetchall()
        except (Exception, Error) as error:
            print("Error while getting languages", error)
        

    # inserting language if it doesn't exist
    def insertLanguage(self, language):
        select_command = "SELECT language FROM {} WHERE (language = '{}')"
        self.cursor.execute(select_command.format(self.languages_table, language))

        if self.cursor.rowcount == 0:
            insert_command = "INSERT INTO {}(language) VALUES ('{}')"
            try:
                self.cursor.execute(insert_command.format(self.languages_table, language))
            except (Exception, Error) as error:
                print("Error while inserting language ", error)
            self.connection.commit()

            return 0
        else:
            return 1


    def insertWords(self, data):
        
        # checking for languages id order
        lang1 = data[1]
        lang2 = data[3]

        get_lang_id_command = "SELECT id FROM languages WHERE (language = '{}')"
        self.cursor.execute(get_lang_id_command.format(lang1))
        id_lang1 = self.cursor.fetchone()[0]

        self.cursor.execute(get_lang_id_command.format(lang2))
        self.cursor.execute(get_lang_id_command.format(lang2))
        id_lang2 = self.cursor.fetchone()[0]

        if id_lang1 > id_lang2:
            data = self.reverseWords(data)

        insert_command = """
            INSERT INTO {} VALUES ('{}', (SELECT id FROM {} WHERE (language = '{}')),
				'{}', (SELECT id FROM {} WHERE (language = '{}')))
        """
        try:
            self.cursor.execute(insert_command.format(self.words_table, data[0], self.languages_table,  data[1], data[2], self.languages_table, data[3]))
            self.connection.commit()
        except (Exception, Error) as error:
            print("Error while inserting words ", error)
        

    def searchWord(self, lst):
        command = """
            SELECT w.word1, l1.language, w.word2, l2.language
            FROM words w
            INNER JOIN languages l1 on w.id_lang1 = l1.id
            INNER JOIN languages l2 on w.id_lang2 = l2.id
            WHERE (w.word1 = '{}' AND w.id_lang1 = (SELECT id FROM languages WHERE (language = '{}')) AND w.id_lang2 = (SELECT id FROM languages WHERE (language = '{}'))) OR 
                (w.word2 = '{}' AND w.id_lang2 = (SELECT id FROM languages WHERE (language = '{}')) AND w.id_lang1 = (SELECT id FROM languages WHERE (language = '{}')))
            """
        try:
            self.cursor.execute(command.format(lst[0], lst[1], lst[2], lst[0], lst[1], lst[2]))
            return self.cursor.fetchall()
        except (Exception, Error) as error:
            print("Error while searching words ", error)
        
    def reverseWords(self, pair):
        return (pair[2], pair[3], pair[0], pair[1])

    def reverse(pair, item1, item2):
        aux = item1
        item1 = item2
        item2 = aux

    def getDictionary(self, languages):

        reverse = False

        lang1 = languages[0]
        lang2 = languages[1]

        get_lang_id_command = "SELECT id FROM languages WHERE (language = '{}')"
        self.cursor.execute(get_lang_id_command.format(lang1))
        id_lang1 = self.cursor.fetchone()[0]

        self.cursor.execute(get_lang_id_command.format(lang2))
        id_lang2 = self.cursor.fetchone()[0]

        if id_lang1 > id_lang2:
            reverse = True
        
        if reverse:
            command = """
            SELECT word2, word1
            FROM words 
            WHERE (id_lang2 = (SELECT id FROM languages WHERE (language = '{}')) AND 
                id_lang1 = (SELECT id FROM languages WHERE (language = '{}'))) ORDER BY word2
            """
        else:
            command = """
                SELECT word1, word2 
                FROM words 
                WHERE (id_lang1 = (SELECT id FROM languages WHERE (language = '{}')) AND 
                    id_lang2 = (SELECT id FROM languages WHERE (language = '{}'))) ORDER BY word1
                """
        try:
            self.cursor.execute(command.format(languages[0], languages[1]))

            return self.cursor.fetchall()

        except (Exception, Error) as error:
            print("Error while searching words ", error)   

    def update(self, old_pair, new_pair):

        self.insertLanguage(new_pair[1])
        self.insertLanguage(new_pair[3])
        
        command = """
            UPDATE words SET word1 = '{}',
		        id_lang1 = (SELECT id FROM languages WHERE language = '{}'),
		        word2 = '{}',
		        id_lang2 = (SELECT id FROM languages WHERE language = '{}')
		    WHERE (word1 = '{}' AND
			    id_lang1 = (SELECT id FROM languages WHERE language = '{}') AND
			    word2 = '{}' AND
			    id_lang2 = (SELECT id FROM languages WHERE language = '{}'))
            """
        try:
            self.cursor.execute(command.format(new_pair[0], new_pair[1], new_pair[2], new_pair[3], old_pair[0], old_pair[1], old_pair[2], old_pair[3]))
            self.connection.commit()
            return 0
        except (Exception, Error) as error:
            print("Error while updating words ", error)
            return 1

    def deleteWords(self, data):

        # checking for languages id order
        lang1 = data[1]
        lang2 = data[3]

        get_lang_id_command = "SELECT id FROM languages WHERE (language = '{}')"
        self.cursor.execute(get_lang_id_command.format(lang1))
        id_lang1 = self.cursor.fetchone()[0]

        self.cursor.execute(get_lang_id_command.format(lang2))
        self.cursor.execute(get_lang_id_command.format(lang2))
        id_lang2 = self.cursor.fetchone()[0]

        if id_lang1 > id_lang2:
            data = self.reverseWords(data)

        command = """
            DELETE FROM words 
            WHERE (word1 = '{}' AND
			    id_lang1 = (SELECT id FROM languages WHERE language = '{}') AND
			    word2 = '{}' AND
			    id_lang2 = (SELECT id FROM languages WHERE language = '{}'))
        """   
        try:
            self.cursor.execute(command.format(data[0], data[1], data[2], data[3]))
            self.connection.commit()
            return 2
        except (Exception, Error) as error:
            print("Error while deleting words ", error)
            return 1


    def deleteLanguage(self, language):
        delete_words_command = """
            DELETE FROM words 
            WHERE (id_lang1 = (SELECT id FROM languages WHERE (language = '{}')) OR 
                id_lang2 = (SELECT id FROM languages WHERE (language = '{}')))
            """    
        delete_language_command = """
            DELETE FROM languages
            WHERE language = '{}'
            """
        try:
            self.cursor.execute(delete_words_command.format(language, language))
            self.cursor.execute(delete_language_command.format(language))
            self.connection.commit()
            return 0
        except (Exception, Error) as error:
            print("Error while deleting the words or language ", error)
            return 1
