import aiomysql
from config import settings
from common.logger import logger


class AsyncDb:
    def __init__(self):
        self.pool = None
        self.host = settings.database_host
        self.user = settings.database_user
        self.password = settings.database_password
        self.db = settings.database_name
        self.port = settings.database_port

    async def init(self, loop):
        self.pool = await aiomysql.create_pool(host=self.host, port=self.port, user=self.user,
                                               password=self.password, db=self.db, loop=loop, minsize=1, maxsize=100,
                                               init_command="SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;")

    async def deinit(self):
        self.pool.close()
        await self.pool.wait_closed()

    async def fetch_historic_stats(self) -> tuple:
        """
        fetch pending historic weather data points
        :return: the points for which the data has to be fetched
        :rtype: tuple
        """
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    query: str = 'select id, collection_id, lat, lng, point_id, "UTC" as timezone, ' \
                                 'CONVERT_TZ(NOW(), "UTC", "UTC") as ' \
                                 'historical_last_fetch_time from sites_table '
                    await cursor.execute(query)
                    results: tuple = await cursor.fetchall()
                    return results
        except Exception as e:
            logger.log(level=logger.error, msg=f'failed to fetch the historic jobs and error is {e}')
            return ()


mysql_aioclient = AsyncDb()