import os
import sqlite3
from typing import List, Tuple, 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_all_recommendations() -> Tuple[List[Dict], Optional[str]]:
    """
    recommendations_latest と stocks, indicators_daily(最新) をJOINして一覧取得。
    MVPでは簡略に recommendations_latest と stocks をJOIN、
    指標は最新値をsubqueryで取得する実装に差し替え予定。
    """
    with _conn() as con:
        # 最終更新相当（scored_atの最大）
        cur = con.execute("SELECT MAX(scored_at) AS last_update FROM recommendations_latest")
        last = cur.fetchone()
        last_update = last["last_update"] if last and last["last_update"] else None

        # おすすめ一覧
        sql = """
        SELECT rl.code, s.name,
               id.per, id.roe, id.pbr, id.sales_growth
        FROM recommendations_latest rl
        JOIN stocks s ON s.code = rl.code
        LEFT JOIN indicators_daily id
          ON id.code = rl.code
         AND id.snapshot_at = (
            SELECT MAX(snapshot_at) FROM indicators_daily WHERE code = rl.code
         )
        ORDER BY rl.code ASC
        """
        rows = [dict(r) for r in con.execute(sql).fetchall()]
        return rows, last_update