from typing import Dict
from psycopg2 import sql, pool

from app.core.config import settings

from app.models.metadata.apartment_type import apartment_type_metadata
from app.models.metadata.apartment_selling import apartment_selling_metadata
from app.models.metadata.apartment_renting import apartment_renting_metadata
from app.models.metadata.building import building_metadata
from app.models.metadata.business_space_selling import business_space_selling_metadata
from app.models.metadata.business_space_renting import business_space_renting_metadata
from app.models.metadata.house_selling import house_selling_metadata
from app.models.metadata.house_renting import house_renting_metadata
from app.models.metadata.lead import leads_metadata
from app.models.metadata.lot_selling import lot_selling_metadata
from app.models.metadata.lot_renting import lot_renting_metadata
from app.models.metadata.new_apartment import new_apartment_metadata
from app.models.metadata.project import project_metadata
from app.models.metadata.image import image_metadata
from app.models.metadata.item import item_metadata
from app.models.metadata.hub_user import hub_user_metadata


# Global connection pool variable
connection_pool = None

# Create the connection pool


def init_db_pool():

    global connection_pool
    try:
        connection_pool = pool.SimpleConnectionPool(
            minconn=1,  # minimum number of connections
            maxconn=10,  # maximum number of connections
            dbname=settings.DATABASE_NAME,
            user=settings.DATABASE_USERNAME,
            password=settings.DATABASE_PASSWORD,
            host=settings.DATABASE_HOST,
            port=settings.DATABASE_PORT
        )
        return {}
    except Exception as e:
        return {
            'error': 'Greška pri kreiranju "connection pool-a"',
            'details': str(e)}

# Fetch a connection from the pool


def get_connection():
    global connection_pool
    if not connection_pool:
        raise Exception('"Connection pool" nije inicijalizovan')

    try:
        conn = connection_pool.getconn()
        if conn is None:
            raise Exception(
                'Neuspešno dobijanje konekcije iz "connection pool-a"')
        return conn
    except Exception as e:
        raise Exception(f'Neuspešno dobijanje konekcije iz baze: {str(e)}')

# Release the connection back to the pool


def release_connection(conn):
    global connection_pool
    if connection_pool:
        try:
            # Release the connection back to the pool
            connection_pool.putconn(conn)
        except Exception as e:
            return {
                'error': 'Greška pri vraćanju konekcije nazad u "connection pool"',
                'details': str(e)}

# Close all connections in the pool


def close_connection_pool():
    global connection_pool
    if connection_pool:
        try:
            connection_pool.closeall()
            return {}
        except Exception as e:
            return {
                'error': 'Greška pri zatvaranju "connection pool-a"',
                'details': str(e)}


def init_db():
    # Define metadata for all tables
    tables_metadata = [
        apartment_selling_metadata,
        apartment_renting_metadata,
        apartment_type_metadata,
        building_metadata,
        business_space_selling_metadata,
        business_space_renting_metadata,
        house_selling_metadata,
        house_renting_metadata,
        image_metadata,
        lot_selling_metadata,
        lot_renting_metadata,
        new_apartment_metadata,
        project_metadata,
        item_metadata,
        hub_user_metadata,
        leads_metadata
    ]

    # Loop through all tables and create them
    for table_metadata in tables_metadata:
        table_name = table_metadata['table_name']
        columns = table_metadata['columns']
        create_table_if_not_exists(table_name, columns)


def create_table_if_not_exists(table_name: str, columns: Dict[str, dict]):
    conn = None
    try:
        # Connect first to allow usage in .as_string()
        conn = get_connection()

        query_parts = [
            sql.SQL("CREATE TABLE IF NOT EXISTS {} (\n").format(
                sql.Identifier(table_name))]

        primary_key_columns = []

        for column_name, attributes in columns.items():
            sql_type = attributes.get('sql_type')
            constraints = attributes.get('constraints', {})

            column_ident = sql.Identifier(column_name)
            column_definition = sql.SQL("\t{} {}").format(
                column_ident, sql.SQL(sql_type))

            if constraints.get('unique'):
                column_definition += sql.SQL(" UNIQUE")

            if 'foreign_key' in constraints:
                foreign_key = constraints['foreign_key']
                ref_table = sql.Identifier(foreign_key.get('ref_table'))
                ref_column = sql.Identifier(foreign_key.get('ref_column'))

                fk_clause = sql.SQL(" REFERENCES {}({})").format(
                    ref_table, ref_column)

                if 'on_delete' in foreign_key:
                    fk_clause += sql.SQL(" ON DELETE {}").format(
                        sql.SQL(foreign_key['on_delete'].upper()))

                if 'on_update' in foreign_key:
                    fk_clause += sql.SQL(" ON UPDATE {}").format(
                        sql.SQL(foreign_key['on_update'].upper()))

                column_definition += fk_clause

            query_parts.append(column_definition + sql.SQL(",\n"))

            if constraints.get('primary_key'):
                primary_key_columns.append(column_ident)

        # Add primary key if any
        if primary_key_columns:
            pk_def = sql.SQL("PRIMARY KEY ({})").format(
                sql.SQL(', ').join(primary_key_columns))
            query_parts.append(sql.SQL("\t") + pk_def + sql.SQL(",\n"))

        # Remove last comma+newline
        if query_parts:
            last = query_parts[-1].as_string(conn).rstrip(",\n") + "\n"
            query_parts[-1] = sql.SQL(last)

        query_parts.append(sql.SQL(");\n"))
        query = sql.Composed(query_parts)

        with conn.cursor() as cursor:
            cursor.execute(query)
            conn.commit()
            return {}

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

    finally:
        if conn:
            release_connection(conn)