o
    dh                     @   s   d dl Z d dlZd dlmZmZ e ddZdd Zdedee fd	d
Z	dedede
dB de
dB de
dB de
dB deddfddZdS )    N)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   ./var/www/html/app/./repositories/indicators.py_conn   s   
r   codereturnc                 C   sP   t  }d}||| f }|rt|ndW  d   S 1 s!w   Y  dS )ut   
    銘柄の最新スナップショットを返す。
    stocks + indicators_daily（最新行）をJOIN。
    ad  
        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 = ?
        N)r   executefetchonedict)r   r   sqlrowr   r   r   fetch_latest_snapshot   s
   $r   snapshot_atperroepbrsales_growth
updated_atc                 C   sN   t  }|d| ||||||f |  W d    d S 1 s w   Y  d S )Na^  
            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
        )r   r   commit)r   r   r   r   r   r   r    r   r   r   r   upsert_indicator"   s   
"r"   )r   r   typingr   r   getenvr
   r   strr   floatr"   r   r   r   r   <module>   s    