from datetime import datetime
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker, AsyncSession
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy import String, Integer, Boolean, DateTime, Text, select, delete as sa_delete
from config import settings
import json


engine = create_async_engine(settings.db_url, echo=False)
async_session = async_sessionmaker(engine, expire_on_commit=False)


class Base(DeclarativeBase):
    pass


class Instance(Base):
    __tablename__ = "instances"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    name: Mapped[str] = mapped_column(String(255), nullable=False)
    host: Mapped[str] = mapped_column(String(255), nullable=False)
    port: Mapped[int] = mapped_column(Integer, nullable=False)
    username: Mapped[str] = mapped_column(String(255), nullable=False)
    password: Mapped[str] = mapped_column(String(255), nullable=False)
    version: Mapped[str] = mapped_column(String(32), default="auto")
    notes: Mapped[str] = mapped_column(Text, default="")
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)


class CacheEntry(Base):
    __tablename__ = "cache"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    instance_id: Mapped[int] = mapped_column(Integer, nullable=False, index=True)
    key: Mapped[str] = mapped_column(String(64), nullable=False)
    data: Mapped[str] = mapped_column(Text, nullable=False)
    fetched_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)


async def init_db():
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)


async def get_instances() -> list[Instance]:
    async with async_session() as session:
        result = await session.execute(select(Instance).order_by(Instance.name))
        return list(result.scalars().all())


async def get_instance(instance_id: int) -> Instance | None:
    async with async_session() as session:
        result = await session.execute(select(Instance).where(Instance.id == instance_id))
        return result.scalar_one_or_none()


async def add_instance(name: str, host: str, port: int, username: str, password: str,
                       version: str = "auto", notes: str = "") -> Instance:
    async with async_session() as session:
        inst = Instance(
            name=name, host=host, port=port, username=username,
            password=password, version=version, notes=notes
        )
        session.add(inst)
        await session.commit()
        await session.refresh(inst)
        return inst


async def update_instance(instance_id: int, **kwargs) -> Instance | None:
    async with async_session() as session:
        result = await session.execute(select(Instance).where(Instance.id == instance_id))
        inst = result.scalar_one_or_none()
        if not inst:
            return None
        for key, value in kwargs.items():
            if hasattr(inst, key):
                setattr(inst, key, value)
        inst.updated_at = datetime.utcnow()
        await session.commit()
        await session.refresh(inst)
        return inst


async def delete_instance(instance_id: int) -> bool:
    async with async_session() as session:
        result = await session.execute(select(Instance).where(Instance.id == instance_id))
        inst = result.scalar_one_or_none()
        if not inst:
            return False
        await session.delete(inst)
        await session.execute(sa_delete(CacheEntry).where(CacheEntry.instance_id == instance_id))
        await session.commit()
        return True


async def set_cache(instance_id: int, key: str, data: dict):
    async with async_session() as session:
        result = await session.execute(
            select(CacheEntry).where(
                CacheEntry.instance_id == instance_id,
                CacheEntry.key == key
            )
        )
        entry = result.scalar_one_or_none()
        if entry:
            entry.data = json.dumps(data)
            entry.fetched_at = datetime.utcnow()
        else:
            entry = CacheEntry(
                instance_id=instance_id,
                key=key,
                data=json.dumps(data)
            )
            session.add(entry)
        await session.commit()


async def get_cache(instance_id: int, key: str) -> dict | None:
    async with async_session() as session:
        result = await session.execute(
            select(CacheEntry).where(
                CacheEntry.instance_id == instance_id,
                CacheEntry.key == key
            )
        )
        entry = result.scalar_one_or_none()
        if entry:
            return {"data": json.loads(entry.data), "fetched_at": entry.fetched_at.isoformat()}
        return None