import os
import asyncio
import subprocess
import contextlib
import contextvars
from typing import *

import wait4it
import sqlmodel
from sqlmodel import Session
from aioify import aioify

from twitterscraper.models.domain import TwitterProfile, TwitterTweet, JobHistoric
from twitterscraper.utils import Singleton


class Repository(Singleton):
    # https://sqlmodel.tiangolo.com/
    get: Callable[..., "Repository"]
    _session_contextvar: contextvars.ContextVar[Optional[Session]]

    def __init__(self, uri: str):
        self._engine = sqlmodel.create_engine(uri)
        self._session_contextvar = contextvars.ContextVar("session", default=None)
        # TODO implement pool_pre_ping & pool_recycle_time

        # Fix for https://github.com/tiangolo/sqlmodel/issues/189#issuecomment-1025190094
        from sqlmodel.sql.expression import Select, SelectOfScalar
        SelectOfScalar.inherit_cache = True  # type: ignore
        Select.inherit_cache = True  # type: ignore

    def _new_session(self) -> Session:
        session = Session(bind=self._engine)
        self._session_contextvar.set(session)
        return session

    def _get_context_session(self) -> Optional[Session]:
        return self._session_contextvar.get()

    def _clear_context_session(self):
        self._session_contextvar.set(None)

    @contextlib.contextmanager
    def session(self) -> Session:
        """Contextmanager that wraps code behind a database transaction (session).
        Any error during the execution rolls back the transaction, so any data saved is not persisted."""
        session = self._get_context_session()
        if session is not None:
            # Another session() contextmanager is already running; let it handle the commit/rollback
            yield session
            return

        session = self._new_session()
        print("New session")
        try:
            yield session
            print("Session Commit")
            session.commit()
        except Exception as ex:
            print("Session Rollback")
            session.rollback()
            raise ex
        finally:
            print("Session Close")
            session.close()
            self._clear_context_session()

    @contextlib.asynccontextmanager
    async def session_async(self) -> Session:
        """Contextmanager that wraps code behind a database transaction (session).
        Any error during the execution rolls back the transaction, so any data saved is not persisted."""
        session = self._get_context_session()
        if session is not None:
            # Another session() contextmanager is already running; let it handle the commit/rollback
            yield session
            return

        session = self._new_session()
        print("New session")
        try:
            yield session
            print("Session Commit")
            await aioify(session.commit)()
        except Exception as ex:
            print("Session Rollback")
            await aioify(session.rollback)()
            raise ex
        finally:
            print("Session Close")
            await aioify(session.close)()
            self._clear_context_session()

    def save_object(self, obj: sqlmodel.SQLModel, flush: bool = False):
        """Save or update any SQLModel object instance"""
        with self.session() as session:
            session.add(obj)
            if flush:
                session.flush([obj])

    async def save_object_async(self, *obj: sqlmodel.SQLModel, flush: bool = False):
        # return await aioify(self.save_object)(obj, flush)
        async with self.session_async() as session:
            await asyncio.gather(*[aioify(session.add)(_obj) for _obj in obj])
            if flush:
                await aioify(session.flush)(obj)

    def delete_object(self, obj: sqlmodel.SQLModel, flush: bool = False):
        with self.session() as session:
            session.delete(obj)
            if flush:
                session.flush([obj])

    async def delete_object_async(self, obj: sqlmodel.SQLModel, flush: bool = False):
        async with self.session_async() as session:
            await aioify(session.delete)(obj)
            if flush:
                await aioify(session.flush)([obj])

    def list_profiles(self) -> List[TwitterProfile]:
        with self.session() as session:
            query = sqlmodel.select(TwitterProfile)
            return session.exec(query).all()

    async def list_profiles_async(self, filter_active_profiles: Optional[bool] = None) -> List[TwitterProfile]:
        async with self.session_async() as session:
            query = 