import os
import sqlite3
from typing import Optional, Dict

DB_PATH = os.getenv("APP_DB_PATH", "data/app.db")

def _conn():
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    con = sqlite3.connect(DB_PATH)
    con.row_factory = sqlite3.Row
    return con

def fetch_latest_snapshot(code: str) -> Optional[Dict]:
    """
    銘柄の最新スナップショットを返す。
    stocks + indicators_daily（最新行）をJOIN。
    """
    with _conn() as con:
        sql = """
        SELECT s.code, s.name,
               id.snapshot_at, id.per, id.roe, id.pbr, id.sales_growth
          FROM stocks s
     LEFT JOIN indicators_daily id
            ON id.code = s.code
           AND id.snapshot_at = (
               SELECT MAX(snapshot_at) FROM indicators_daily WHERE code = s.code
           )
         WHERE s.code = ?
        """
        row = con.execute(sql, (code,)).fetchone()
        return dict(row) if row else None

# 参考：バッチ用のUPSERT（必要に応じて利用）
def upsert_indicator(code: str, snapshot_at: str, per: float | None, roe: float | None,
                     pbr: float | None, sales_growth: float | None, updated_at: str) -> None:
    with _conn() as con:
        con.execute("""
            INSERT INTO indicators_daily(code, snapshot_at, per, roe, pbr, sales_growth, updated_at)
            VALUES(?,?,?,?,?,?,?)
            ON CONFLICT(code, snapshot_at) DO UPDATE SET
              per=excluded.per, roe=excluded.roe, pbr=excluded.pbr,
              sales_growth=excluded.sales_growth, updated_at=excluded.updated_at
        """, (code, snapshot_at, per, roe, pbr, sales_growth, updated_at))
        con.commit()