"""请求和后台各自拥有会话；短写事务避免模型等待期间占用 SQLite 写锁。"""
import asyncio
from contextlib import asynccontextmanager
from sqlalchemy import event, text
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker
from app.shared.config import settings
from app.domain.models import Base

settings.data_path.mkdir(parents=True, exist_ok=True)
engine = create_async_engine(settings.db_url, connect_args={"timeout": 30})
Session = async_sessionmaker(engine, expire_on_commit=False)
write_lock = asyncio.Lock()


@event.listens_for(engine.sync_engine, "connect")
def configure_sqlite(connection, record):
    """外键和 WAL 不依赖客户端设置，否则不同启动路径会产生不一致数据。"""
    cursor = connection.cursor()
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.execute("PRAGMA journal_mode=WAL")
    cursor.execute("PRAGMA busy_timeout=30000")
    cursor.close()


@asynccontextmanager
async def transaction():
    """先领取写锁再读取要修改的数据，避免 deferred 事务读后升级引发快照冲突。"""
    async with write_lock:
        async with Session() as session:
            await session.execute(text("BEGIN IMMEDIATE"))
            try:
                yield session
                await session.commit()
            except Exception:
                await session.rollback()
                raise


async def initialize():
    """首次建表并标记初始迁移；新版本变更应通过 Alembic，不能靠 create_all 改旧表。"""
    async with engine.begin() as connection:
        await connection.run_sync(Base.metadata.create_all)
        await connection.execute(text("CREATE TABLE IF NOT EXISTS alembic_version (version_num VARCHAR(32) NOT NULL PRIMARY KEY)"))
        await connection.execute(text("INSERT OR IGNORE INTO alembic_version(version_num) VALUES ('0001_initial')"))
    from app.application.seed import seed
    async with transaction() as session:
        await seed(session)