src/db.py

"""共用 PostgreSQL 同步連線池 — 關閉 F4 v2 SR-6 deferred。

【為什麼是 sync pool 不是 AsyncConnectionPool？】
ml-service 的 DB 呼叫本來就是 sync psycopg 透過 `asyncio.to_thread` 包起
來的（counterfeit_anchor / valuation_observations / brand_normalizer /
xgboost_valuation 訓練 fetch）。改成 async pool 會逼所有 caller 重寫成
async，但 torch forward + joblib.dump 等 CPU-bound 工作仍要 to_thread，
沒有整體淨收益。Sync pool + to_thread 維持當前 async-discipline 不變。

【為什麼集中在 src/db.py 而非各模組各自開？】
`/api/v1/ml/valuation/predict` 帶 image_url 時：
  1. counterfeit_anchor.query_anchor_similarity() → psycopg.connect() 一次
  2. valuation_observations.record_observation() → psycopg.connect() 一次
兩條 path 平行 await，每次 request 開兩條 TCP 握手 + auth；Neon 的
connection limit 是 25（Free tier），併發 10 user 同時打就會觸發 ECONN /
SSL handshake 排隊。Pool 把這個攤銷成 1-5 條長連線。

【閾值如何選？】
- ML_DB_POOL_MIN=1：首個請求不付握手延遲（~80ms 對 Neon）
- ML_DB_POOL_MAX=5：counterfeit_anchor + valuation_observations 平行
  + brand_normalizer 過期 reload + 偶發 retry buffer，5 對單 worker 足夠
- timeout=10s：超過代表 pool 飽和，fail fast 比排隊好（caller 是
  fire-and-forget side-effect，drop 一筆 observation 比卡 event loop 好）

【驅動行為】
- pool.connection() context exit 自動 commit（成功）/ rollback（例外）
- 不再呼叫 conn.commit()，原本 valuation_observations 寫入用的 explicit
  commit 是 `with psycopg.connect()` 雙重保險，pool 拿掉一層
"""

from __future__ import annotations

import logging
import os
from collections.abc import Iterator
from contextlib import contextmanager

from psycopg import Connection
from psycopg_pool import ConnectionPool

logger = logging.getLogger(__name__)

DATABASE_URL = os.environ.get(
    "DATABASE_URL",
    "postgresql://wardrobe:wardrobe_dev@localhost:5432/wardrobe_os",
)

_POOL_MIN = int(os.environ.get("ML_DB_POOL_MIN", "1"))
_POOL_MAX = int(os.environ.get("ML_DB_POOL_MAX", "5"))
_POOL_TIMEOUT_S = float(os.environ.get("ML_DB_POOL_TIMEOUT_S", "10.0"))

_pool: ConnectionPool | None = None


def get_pool() -> ConnectionPool:
    """Lazy singleton。第一次呼叫建立 + open（eager handshake）。

    `open=True` 讓 pool 在初始化期就開 min_size 條連線，第一個
    `get_conn()` 不付握手成本。如果 DB 暫時不可用，pool 會自己 retry
    背景連線，acquire 時若仍無 conn 會在 timeout 後 raise。

    【為什麼 local var 然後才 commit `_pool`？】
    `ConnectionPool(..., open=True)` 失敗的話，部分驅動實作會在
    `__init__` 把欄位都填好之後才在 `_open()` 階段 raise — 如果直接
    `_pool = ConnectionPool(...)`，半破的 instance 已經被 commit 進
    singleton，下次 `get_pool()` 看到 non-None 直接回 broken pool，
    永遠卡在 `PoolTimeout`。Local var pattern 對齊 feedback memory
    `feedback_singleton_partial_load_poison.md`（CR conf 92, 8076f1a
    post-review）。
    """
    global _pool
    if _pool is None:
        pool = ConnectionPool(
            DATABASE_URL,
            min_size=_POOL_MIN,
            max_size=_POOL_MAX,
            timeout=_POOL_TIMEOUT_S,
            name="ml-service",
            open=True,
        )
        _pool = pool
    return _pool


@contextmanager
def get_conn() -> Iterator[Connection]:
    """從 pool 拿一條連線，block 結束自動歸還。

    用法：
        with get_conn() as conn:
            conn.execute("SELECT ...")

    成功退出 context → autocommit；例外 → rollback；連線歸還而非關閉。
    取代散落的 `with psycopg.connect(DATABASE_URL) as conn:` pattern。
    """
    pool = get_pool()
    with pool.connection() as conn:
        yield conn


def close_pool() -> None:
    """Drain + close pool。FastAPI lifespan teardown 呼叫。

    幂等：呼叫多次不會 raise（pool 內部記 closed state）。close_pool 之後
    下次 get_pool 會建一個新的（測試用得到，prod 不應該發生）。
    """
    global _pool
    if _pool is not None:
        try:
            _pool.close()
        except Exception as exc:
            logger.warning("ml-service db pool close failed: %s", exc)
        _pool = None