import os
import connection
from psycopg2.extras import RealDictCursor

DIR_PATH = os.path.abspath(os.path.dirname(__file__))
DATA_DIRECTORY = 'sample_data'
UPLOAD_FOLDER = os.path.join(DIR_PATH, 'static/uploads')
COLUMN_SEPARATOR = ","


def get_path(filename):
    return os.path.join(DIR_PATH, DATA_DIRECTORY, filename)


@connection.connection_handler
def get_latest_five_questions(cursor):
    cursor.execute(f"""
                    SELECT * FROM question
                    ORDER BY submission_time DESC
                    LIMIT 5;
                    """)
    return cursor.fetchall()


@connection.connection_handler
def get_questions(cursor, order_by, order):
    cursor.execute(f"""
                    SELECT * FROM question
                    ORDER BY {order_by} {order};
                    """)
    return cursor.fetchall()


@connection.connection_handler
def add_question(cursor, title, message, image, user_id):
    default_value = 'default'
    time_now = 'now()'
    cursor.execute(f"""
                    INSERT INTO question
                                (id, user_id, submission_time, view_number, 
                                vote_number, title, message, image)
                    VALUES ({default_value}, {user_id}, {time_now}, 0, 0, 
                            '{title}', '{message}', '{image}')
                    RETURNING id
                    """)
    data = cursor.fetchall()
    return data[0]['id']


@connection.connection_handler
def display_questions(cursor, question_id):
    cursor.execute(f"""
                    SELECT * FROM question
                    WHERE id = {question_id};
                    """)
    return cursor.fetchall()


@connection.connection_handler
def get_question_answers(cursor, question_id):
    cursor.execute(f"""
                    SELECT * FROM answer
                    WHERE question_id = {question_id}
                    ORDER BY accepted DESC, vote_number DESC;
                    """)
    return cursor.fetchall()


@connection.connection_handler
def get_question_comments(cursor, question_id):
    cursor.execute(f"""
                    SELECT * FROM comment
                    WHERE question_id = {question_id};
                    """)
    return cursor.fetchall()


@connection.connection_handler
def get_answer_comments(cursor, question_id):
    cursor.execute(f"""
                    SELECT comment.id, comment.answer_id, 
                           comment.message, comment.edited_count FROM comment
                    JOIN answer a ON a.id = comment.answer_id
                    WHERE a.question_id = {question_id};
                    """)
    return cursor.fetchall()


@connection.connection_handler
def inc_view_number(cursor, question_id):
    cursor.execute("""
                    UPDATE question
                    SET view_number = view_number + 1
                    WHERE id = %(question_id)s;
                    """,
                   {'question_id': question_id})


@connection.connection_handler
def post_answer(cursor, question_id, message, image, user_id):
    default_value = 'default'
    time_now = 'now()'
    no_votes = 0
    cursor.execute(f"""
                    INSERT INTO answer 
                                (id, user_id, submission_time, vote_number, 
                                question_id, message, image)
                    VALUES ({default_value}, {user_id}, {time_now}, {no_votes}, 
                            {question_id}, '{message}', '{image}')
                    """)


@connection.connection_handler
def add_comment(cursor, message, question_id, answer_id, user_id):
    no_edit = 0
    time_now = 'now()'
    if answer_id:
        cursor.execute("""
                        INSERT INTO comment 
                                    (answer_id, user_id, message, 
                                    submission_time, edited_count) 
                        VALUES (%s, %s, %s, %s, %s)""",
                               (answer_id, user_id, message, time_now, no_edit))
    elif question_id:
        cursor.execute("""
                        INSERT INTO comment 
                                    (question_id, user_id, message, 
                                    submission_time, edited_count) 
                        VALUES (%s, %s, %s, %s, %s)""",
                               (question_id, user_id, message, time_now, no_edit))


@connection.connection_handler
def get_comment(cursor, comment_id):
    cursor.execute(f"""
                    SELECT * FROM comment
                    WHERE id = {comment_id}
                    """)
    return cursor.fetchall()


@connection.connection_handler
def get_answer(cursor, answer_id):
    cursor.execute(f"""
                    SELECT * FROM answer
                    WHERE id = {answer_id}
                    """)
    return cursor.fetchall()


@connection.connection_handler
def get_user_id_by_question_id(cursor, question_id):
    cursor.execute(f"""
            SELECT u.id as userid FROM users u
            JOIN question q ON q.user_id = u.id
            WHERE q.id = {question_id} 
    """)
    return cursor.fetchall()

@connection.connection_handler
def mark_answered(cursor, answer_id, question_id):
    cursor.execute(f"""
            UPDATE answer
            SET accepted = FALSE 
            WHERE answer.question_id = {question_id}
            """)

    cursor.execute(f"""
            UPDATE answer
            SET accepted = TRUE 
            WHERE id={answer_id}""")


@connection.connection_handler
def unmark_answered(cursor, answer_id, question_id):
    cursor.execute(f"""
            UPDATE answer
            SET accepted = FALSE 
            WHERE answer.question_id = {question_id}
            """)


@connection.connection_handler
def get_question(cursor, question_id):
    cursor.execute(f"""
                    SELECT * FROM question
                    WHERE id = {question_id}
                    """)
    return cursor.fetchall()


@connection.connection_handler
def update_answer_vote_number(cursor, answer_id, vote_type):
    query = "UPDATE answer"
    if vote_type == 'up':
        query += "\n" + "SET vote_number = (vote_number + 1)"
    else:
        query += "\n" + "SET vote_number = (vote_number - 1)"
    query += "\n" + f"WHERE id = {answer_id}"
    cursor.execute(query)


@connection.connection_handler
def update_question_vote_number(cursor, question_id, vote_type):
    query = "UPDATE question"
    if vote_type == 'up':
        query += "\n" + "SET vote_number = (vote_number + 1)"
    else:
        query += "\n" + "SET vote_number = (vote_number - 1)"
    query += "\n" + f"WHERE id = {question_id}"
    cursor.execute(query)


@connection.connection_handler
def delete_answer(cursor, answer_id):
    cursor.execute(f"""
                DELETE FROM comment
                WHERE answer_id = {answer_id};
                
                DELETE FROM answer
                WHERE id = {answer_id};
                """)


@connection.connection_handler
def delete_question(cursor, question_id):
    cursor.execute(f"""
                DELETE FROM comment
                USING answer a
                WHERE a.question_id = {question_id} and a.id = comment.answer_id;
                
                DELETE FROM answer
                WHERE question_id = {question_id};
                
                DELETE FROM question_tag
                WHERE question_id = {question_id};
                
                DELETE FROM question
                WHERE id = {question_id};
                """)
    

@connection.connection_handler
def delete_comment(cursor, comment_id):
    cursor.execute(f"""
                DELETE FROM comment
                WHERE id = {comment_id};
                """)


@connection.connection_handler
def edit_comment(cursor, comment_id, message):
    time_now = 'now()'
    cursor.execute(f"""
                    UPDATE comment
                    SET message = '{message}',
                        submission_time = {time_now},
                        edited_count = (edited_count + 1)
                    WHERE id = '{comment_id}';
                   """)


@connection.connection_handler
def edit_answer(cursor, answer_id, message, image):
    time_now = 'now()'
    cursor.execute(f"""
                    UPDATE answer
                    SET message = '{message}',
                        submission_time = {time_now},
                        image = '{image}'
                    WHERE id = {answer_id};
                   """)


@connection.connection_handler
def edit_question(cursor, question_id, message, image):
    time_now = 'now()'
    cursor.execute(f"""
                    UPDATE question
                    SET message = '{message}',
                        submission_time = {time_now},
                        image = '{image}'
                    WHERE id = {question_id}
                    """)


@connection.connection_handler
def get_search_data(cursor, search_string):
    search_tables = ['question', 'answer', 'comment']
    search_fields = {
                    'question': ['title', 'message'],
                    'answer': ['message'],
                    'comment': ['message']
                    }
    search_results = []
    for table in search_tables:
        query_select = f"SELECT '{table}' as data_type, * FROM {table}"
        query_filter = ""
        for field in search_fields[table]:
            if query_filter:
                query_filter += ' OR '
            else:
                query_filter = "WHERE "
            query_filter += "lower("+field+")" + f" like '%{search_string.lower()}%'"
        query = query_select + "\n" + query_filter
        cursor.execute(query)
        results = cursor.fetchall()
        for row in results:
            search_results.append(row)
    return search_results


@connection.connection_handler
def new_tag(cursor, name):
    cursor.execute(f"""
                    INSERT INTO tag (name)
                    VALUES ('{name}')
                    RETURNING id
                    """)
    data = cursor.fetchall()
    return data[0]['id']


@connection.connection_handler
def set_tag(cursor, question_id, tag_id):
    cursor.execute(f"""
                    INSERT INTO question_tag (question_id, tag_id)
                    VALUES ({question_id}, {tag_id})
                    """)


@connection.connection_handler
def get_tags(cursor):
    cursor.execute("""SELECT * FROM tag""")
    return cursor.fetchall()


@connection.connection_handler
def get_question_tags(cursor, question_id):
    cursor.execute(f"""
                    SELECT tag.id, tag.name FROM tag 
                    JOIN question_tag ON question_tag.tag_id=tag.id
                    WHERE question_tag.question_id={question_id}
                    """)
    return cursor.fetchall()


@connection.connection_handler
def delete_question_tag(cursor, question_id, tag_id):
    cursor.execute(f"""
                    DELETE FROM question_tag
                    WHERE question_id = {question_id} AND tag_id = {tag_id}
                    """)


@connection.connection_handler
def get_tag_name_and_count(cursor):
    cursor.execute("""
                    SELECT tag.name as tag, COUNT(question_tag.tag_id) as tag_uses
                    FROM tag
                    JOIN question_tag ON tag.id = question_tag.tag_id
                    GROUP BY tag.name;
                    """)
    return cursor.fetchall()


@connection.connection_handler
def get_user(cursor, user_id):
    cursor.execute(f"""
                    SELECT username, submission_time, reputation, image 
                    FROM users
                    WHERE id={user_id}
                    """)
    return cursor.fetchall()


@connection.connection_handler
def get_user_questions(cursor, user_id):
    cursor.execute(f"""
                    SELECT * FROM question
                    WHERE user_id={user_id}
                    """)
    return cursor.fetchall()


@connection.connection_handler
def get_user_answers(cursor, user_id):
    cursor.execute(f"""
                    SELECT * FROM answer
                    WHERE user_id={user_id}
                    """)
    return cursor.fetchall()


@connection.connection_handler
def get_user_comments(cursor, user_id):
    cursor.execute(f"""
                    SELECT * FROM comment
                    WHERE user_id={user_id}
                    """)
    return cursor.fetchall()


@connection.connection_handler
def users_data(cursor):
    cursor.execute("""
                    SELECT u.username, u.submission_time, 
                            count(q.id) AS count_question,
                            count(a.id) AS count_answer,
                            count(c.id) AS count_comment,
                            u.reputation
                        FROM users u
                        LEFT JOIN question q ON u.id = q.user_id
                        LEFT JOIN answer a ON u.id = a.user_id
                        LEFT JOIN comment c ON u.id = c.user_id
                        GROUP BY u.id;
                    """)
    return cursor.fetchall()


@connection.connection_handler
def gain_reputation(cursor: RealDictCursor, user_id: int, points: int) -> list:
    query="""
            UPDATE users
            SET reputation = reputation+%(points)s
            WHERE id = %(user_id)s
    """
    args = {'user_id': user_id, 'points': points}
    return cursor.execute(query, args)


@connection.connection_handler
def lose_reputation(cursor: RealDictCursor, user_id: int, points = 2) -> list:
    query="""
            UPDATE users
            SET reputation = reputation-%(points)s
            WHERE id = %(user_id)s
    """
    args = {'user_id': user_id, 'points': points}
    return cursor.execute(query, args)


@connection.connection_handler
def add_user(cursor, user_name, user_password):
    """
    returns username when adding a new user
    """

    query = """
            INSERT INTO "users" (username, password, submission_time)
            VALUES (%s, %s, current_timestamp)
            RETURNING username, id"""
    cursor.execute(query, (user_name, user_password,))
    return cursor.fetchone()


@connection.connection_handler
def get_user_data_by_username(cursor, username):
    query = """
            SELECT * 
            FROM users
            WHERE username = %s
            """
    cursor.execute(query, (username,))
    return cursor.fetchone()