"Function to standardize the connection to MySQL"
import os
from contextlib import asynccontextmanager
from urllib.parse import quote_plus

from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import sessionmaker


class MySQLConnector:
    "Class to standardize the connection to MySQL"

    def __init__(self):
        "Constructor to initialize the connection to MySQL"
        # Ensure these environment variables are set for your RDS instance
        user = os.environ.get("rds_username")
        password = os.environ.get("rds_password")
        host = os.environ.get("rds_route")  # RDS endpoint
        dbname = os.environ.get("rds_database")
        encoded_password = quote_plus(password)

        # The connection string remains the same
        database_url = f"mysql+asyncmy://{user}:{encoded_password}@{host}/{dbname}"
        self.engine = create_async_engine(database_url, echo=True)
        self.async_session = sessionmaker(
            bind=self.engine, class_=AsyncSession, expire_on_commit=False
        )

    @asynccontextmanager
    async def get_session(self):
        """Return an async session properly"""
        session = self.async_session()
        try:
            yield session
        finally:
            await session.close()

    async def close(self):
        "Method to close the engine"
        await self.engine.dispose()


# Example usage
if __name__ == "__main__":
    import asyncio

    from sqlalchemy import inspect

    async def main():
        """
        Perform a connection operation to mysql database
        """

        connector = MySQLConnector()
        print(connector)

        # Print tables in the database using run_sync
        async with connector.engine.connect() as connection:

            def get_tables(conn):
                inspector = inspect(conn)
                return inspector.get_table_names()

            tables = await connection.run_sync(get_tables)
            print("Tables in the database:", tables)

        # Ensure the engine is properly closed
        await connector.close()

    asyncio.run(main())