o
    adh                     @   s^   d dl Z d dlZd dlmZmZmZmZ e ddZdd Z	deee ee
 f fdd	ZdS )
    N)ListTupleOptionalDictAPP_DB_PATHzdata/app.dbc                  C   s,   t jt jtdd tt} tj| _| S )NT)exist_ok)	osmakedirspathdirnameDB_PATHsqlite3connectRowrow_factory)con r   3/var/www/html/app/./repositories/recommendations.py_conn   s   
r   returnc                  C   sx   t  /} | d}| }|r|d r|d nd}d}dd | | D }||fW  d   S 1 s5w   Y  dS )u   
    recommendations_latest と stocks, indicators_daily(最新) をJOINして一覧取得。
    MVPでは簡略に recommendations_latest と stocks をJOIN、
    指標は最新値をsubqueryで取得する実装に差し替え予定。
    z@SELECT MAX(scored_at) AS last_update FROM recommendations_latestlast_updateNa  
        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
        c                 S   s   g | ]}t |qS r   )dict).0rr   r   r   
<listcomp>&   s    z-fetch_all_recommendations.<locals>.<listcomp>)r   executefetchonefetchall)r   curlastr   sqlrowsr   r   r   fetch_all_recommendations   s   
$r"   )r   r   typingr   r   r   r   getenvr   r   strr"   r   r   r   r   <module>   s    "