import re
from typing import Dict, List
from psycopg2 import sql

from app.core.database.pool import get_connection, release_connection


def add_item_to_database(table_name, item_dict, return_column=None):
    columns = sql.SQL(', ').join(map(sql.Identifier, item_dict.keys()))
    values_placeholders = sql.SQL(', ').join(
        sql.Placeholder() * len(item_dict))

    # Conditionally add the RETURNING clause
    if return_column:
        returning_clause = sql.SQL("RETURNING {}").format(
            sql.Identifier(return_column))
    else:
        returning_clause = sql.SQL("")

    query = sql.SQL("INSERT INTO {} ({}) VALUES ({}) {}").format(
        sql.Identifier(table_name),
        columns,
        values_placeholders,
        returning_clause
    )

    conn = None
    try:
        conn = get_connection()
        with conn.cursor() as cursor:
            cursor.execute(query, list(item_dict.values()))
            conn.commit()
            if return_column:
                new_id = cursor.fetchone()[0]
                return new_id
        return {}

    except Exception as e:
        if conn:
            conn.rollback()
        return {'error': 'Greška pri zahtevu', 'details': str(e)}

    finally:
        if conn:
            release_connection(conn)


# Fetch rows from a table with optional filtering by conditions
def get_items_from_database(table_name: str, conditions: Dict = None):

    # Map suffixes to SQL operators
    OPERATOR_MAP = {
        'gt': '>',
        'gte': '>=',
        'lt': '<',
        'lte': '<=',
        'eq': '=',
        'ne': '!=',
    }

    conn = None
    try:
        conn = get_connection()
        with conn.cursor() as cursor:
            query = sql.SQL(
                "SELECT * FROM {}").format(sql.Identifier(table_name))
            values = []

            # If filtering conditions are provided
            if conditions:
                condition_statements = []

                for key, value in conditions.items():
                    # Match optional suffix: __gt, __lt, etc. (expects
                    # {column_name}__{operator})
                    match = re.match(
                        r'^(?P<column>\w+)(?:__(?P<op>\w+))?$', key)
                    if not match:
                        continue  # Skip malformed filter keys

                    column = match.group('column')
                    op_suffix = match.group('op')
                    operator = OPERATOR_MAP.get(op_suffix, '=')

                    condition_statements.append(
                        sql.SQL("{} {} %s").format(
                            sql.Identifier(column),
                            sql.SQL(operator)))
                    values.append(value)

                if condition_statements:
                    query += sql.SQL(" WHERE ") + \
                        sql.SQL(" AND ").join(condition_statements)

            # Execute query
            cursor.execute(query, tuple(values))
            items = cursor.fetchall()
            column_names = [desc[0] for desc in cursor.description]
            result = [dict(zip(column_names, row)) for row in items]

        return result

    except Exception as e:
        if conn:
            conn.rollback()
        return {'error': 'Greška pri zahtevu', 'details': str(e)}

    finally:
        if conn:
            release_connection(conn)


# Updates items in a table based on conditions
def update_items_in_database(
        table_name: str,
        updated_fields: Dict,
        conditions: Dict):
    if not updated_fields:
        return {'error': 'Nisu poslata polja koja treba ažurirati'}
    if not conditions:
        return {'error': 'Neophodan barem jedan uslov za ažuriranje'}

    conn = None
    try:
        conn = get_connection()
        with conn.cursor() as cursor:
            # Set clause for updating fields
            set_clause = sql.SQL(', ').join([
                sql.SQL("{} = %s").format(sql.Identifier(field))
                for field in updated_fields.keys()
            ])

            # WHERE clause for filtering items
            condition_statements = [sql.SQL("{} = %s").format(
                sql.Identifier(field)) for field in conditions.keys()]
            query = sql.SQL("UPDATE {} SET {} WHERE ").format(
                sql.Identifier(table_name),
                set_clause
            ) + sql.SQL(" AND ").join(condition_statements)

            params = list(updated_fields.values()) + list(conditions.values())

            cursor.execute(query, params)
            conn.commit()

            if cursor.rowcount == 0:
                return {
                    'error': 'Nije pronadjeno ništa što se poklapa sa zadatim uslovima'}

            return {}

    except Exception as e:
        if conn:
            conn.rollback()
        return {'error': 'Greška pri operaciji sa bazom', 'details': str(e)}

    finally:
        if conn:
            release_connection(conn)


# Delete item from database based on conditions
def delete_items_from_database(table_name: str, conditions: Dict):
    if not conditions:
        return {'error': 'Neophodan barem jedan uslov za brisanje'}

    conn = None
    try:
        conn = get_connection()
        with conn.cursor() as cursor:
            # Construct the WHERE clause dynamically
            condition_statements = [sql.SQL("{} = %s").format(
                sql.Identifier(key)) for key in conditions.keys()]
            query = sql.SQL("DELETE FROM {} WHERE ").format(
                sql.Identifier(table_name)) + sql.SQL(" AND ").join(condition_statements)
            values = list(conditions.values())

            cursor.execute(query, values)
            conn.commit()

            return {}

    except Exception as e:
        if conn:
            conn.rollback()
        return {'error': 'Greška pri operaciji sa bazom', 'details': str(e)}

    finally:
        if conn:
            release_connection(conn)