# from psycopg2 import sql
# import psycopg2


# def create_company_database(dbname, user="adminfpna", password="Maven%404321", host='fpna-dev-postgresql.postgres.database.azure.com', port=5432):
#     # Sanitize database name
#     sanitized_dbname = dbname.strip().lower().replace(" ", "_")

#     conn = psycopg2.connect(dbname='postgres', user=user, password=password, host=host, port=port, sslmode='require')
#     conn.autocommit = True

#     try:
#         with conn.cursor() as cursor:
#             cursor.execute(sql.SQL("CREATE DATABASE {}").format(sql.Identifier(sanitized_dbname)))
#             print(f"Database '{sanitized_dbname}' created successfully.")
#             return sanitized_dbname
#     except psycopg2.errors.DuplicateDatabase:
#         print(f"Database '{sanitized_dbname}' already exists.")
#         return False
#     finally:
#         conn.close()


# async def delete_company_database(dbname, user="adminfpna", password="Maven%404321", host='fpna-dev-postgresql.postgres.database.azure.com', port=5432):


#     # Connect to the default 'postgres' database
#     conn = psycopg2.connect(dbname='postgres', user=user, password=password, host=host, port=port, sslmode='require')
#     conn.autocommit = True

#     try:
#         with conn.cursor() as cursor:
#             # Terminate all connections to the database before dropping it
#             cursor.execute(sql.SQL("""
#                 SELECT pg_terminate_backend(pg_stat_activity.pid)
#                 FROM pg_stat_activity
#                 WHERE pg_stat_activity.datname = %s
#                   AND pid <> pg_backend_pid();
#             """), [dbname])

#             cursor.execute(sql.SQL("DROP DATABASE {}").format(sql.Identifier(dbname)))
#             print(f"Database '{dbname}' deleted successfully.")
#             return True
#     except psycopg2.errors.InvalidCatalogName:
#         print(f"Database '{dbname}' does not exist.")
#         return False
#     except Exception as e:
#         print(f"Failed to delete database '{dbname}': {str(e)}")
#         return False
#     finally:
#         conn.close()
import psycopg2
from psycopg2 import sql
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy import text

async def create_company_database(dbname: str):
    DATABASE_URL = "postgresql+asyncpg://avnadmin:AVNS_As15bncmWPRJZ26soQA@pg-185d6b1c-mavengroup-9e06.l.aivencloud.com:19481/postgres"
    engine = create_async_engine(DATABASE_URL, isolation_level="AUTOCOMMIT")

    async with engine.begin() as conn:
        sanitized_dbname = dbname.strip().lower().replace(" ", "_")
        try:
            await conn.execute(text(f'CREATE DATABASE "{sanitized_dbname}"'))
            print(f"✅ Database '{sanitized_dbname}' created successfully.")
        except Exception as e:
            print(f"❌ Failed to create database: {e}")

    return sanitized_dbname  # <-- important!

async def delete_company_database(dbname: str):
    DATABASE_URL = "postgresql+asyncpg://avnadmin:AVNS_As15bncmWPRJZ26soQA@pg-185d6b1c-mavengroup-9e06.l.aivencloud.com:19481/postgres"
    engine = create_async_engine(DATABASE_URL, isolation_level="AUTOCOMMIT")

    async with engine.begin() as conn:
        sanitized_dbname = dbname.strip().lower().replace(" ", "_")
        try:
            # Terminate connections first
            await conn.execute(text(f"""
                SELECT pg_terminate_backend(pid)
                FROM pg_stat_activity
                WHERE datname = '{sanitized_dbname}' AND pid <> pg_backend_pid();
            """))

            await conn.execute(text(f'DROP DATABASE "{sanitized_dbname}"'))
            print(f"🗑️ Database '{sanitized_dbname}' deleted successfully.")
        except Exception as e:
            print(f"❌ Failed to delete database: {e}")
