"""数据库：async engine + session，SQLAlchemy 2.0 声明式基类。"""
from datetime import datetime, timezone

from sqlalchemy import event
from sqlalchemy.ext.asyncio import (
    AsyncSession,
    async_sessionmaker,
    create_async_engine,
)
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

from app.config import settings

_is_sqlite = settings.database_url.startswith("sqlite")

# SQLite 需要 check_same_thread=False 以配合异步
engine = create_async_engine(
    settings.database_url,
    echo=settings.debug,
    connect_args={"check_same_thread": False} if _is_sqlite else {},
)

if _is_sqlite:

    @event.listens_for(engine.sync_engine, "connect")
    def _sqlite_pragmas(dbapi_conn, _record):
        """每个连接都要单独设置：WAL 让读写不再互斥，外键约束默认是关的。"""
        cur = dbapi_conn.cursor()
        cur.execute("PRAGMA journal_mode=WAL")
        cur.execute("PRAGMA synchronous=NORMAL")
        cur.execute("PRAGMA foreign_keys=ON")
        # 写锁等待，避免并发写直接报 database is locked
        cur.execute("PRAGMA busy_timeout=5000")
        cur.close()

SessionLocal = async_sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)


class Base(DeclarativeBase):
    """所有 ORM 模型的基类。"""


def _utcnow() -> datetime:
    return datetime.now(timezone.utc)


class TimestampMixin:
    """通用时间戳字段。

    用 Python 侧的 datetime（带微秒）而非 SQL 的 now()：SQLite 的
    CURRENT_TIMESTAMP 只精确到秒，同一秒内的多条记录无法排出先后，
    会让"取最近一次作答"这类查询出错。
    """

    created_at: Mapped[datetime] = mapped_column(default=_utcnow, nullable=False)
    updated_at: Mapped[datetime] = mapped_column(
        default=_utcnow, onupdate=_utcnow, nullable=False
    )


async def get_db():
    """FastAPI 依赖：提供一个请求级 session。"""
    async with SessionLocal() as session:
        yield session