# PythonLand 投資×Python シリーズ 第13回
# 「Streamlitで投資ダッシュボードを作る（SQLite × Plotly・完全ローカル）」
# https://pythonland.tech/investment-dashboard-streamlit.html
#
# 起動: streamlit run investment_dashboard.py
#       （python investment_dashboard.py では画面が出ません＝記事8章）
#
# ■ このアプリが守っていること
#   1. 第8回 dividends.db・第11回 perks.db は file:...?mode=ro（読み取り専用）で開き、
#      1行も書き込みません。書き込もうとすると sqlite3 が例外で止めます。
#   2. 外部との通信は、サイドバーの「時価を取得」ボタンを押したときだけ発生します。
#      押さなければ完全にオフラインで動きます（起動時も通信しません）。
#   3. 画面に出るのは自分で入力した記録の集計です。銘柄の評価も売買の判断もしません。
#      取得した時価を保有株数と掛けた「評価額」も出しません。
#   4. localhost で動くローカルアプリです。クラウドには載せません（記事8章）。
#
# 検証環境: Windows 11 / Python 3.12.10 / streamlit 1.58.0 / pandas 3.0.3 / plotly 6.8.0
#           （2026-09-07 実行）
# 必要なライブラリ: pip install streamlit pandas plotly（8章の時価取得だけ yfinance も要ります）
# ライセンス: MIT（https://pythonland.tech/ のコードは MIT ライセンスで公開しています）

"""第8回・第11回のSQLiteと第10回・第12回のCSVを、1画面に並べて表示するStreamlitアプリ。"""
from __future__ import annotations

import argparse
import sqlite3
import sys
from datetime import datetime
from pathlib import Path

import pandas as pd
import streamlit as st

APP_DIR = Path(__file__).resolve().parent


def cli_defaults() -> argparse.Namespace:
    """起動時にDBのパスを渡せるようにする（省略可）。

    streamlit run investment_dashboard.py -- --div-db C:/path/dividends.db
    のように、-- のうしろに書いた引数がスクリプトに届く。
    parse_known_args にしているのは、Streamlit 自身の引数を巻き込まないため。
    """
    ap = argparse.ArgumentParser(add_help=False)
    ap.add_argument("--div-db", default="")
    ap.add_argument("--perks-db", default="")
    args, _ = ap.parse_known_args()
    return args


ARGS = cli_defaults()

SQL_YEARS = """
SELECT DISTINCT strftime('%Y', paid_on) AS year FROM dividends ORDER BY year DESC
"""
SQL_YEAR_KPI = """
SELECT COUNT(*) AS n, COALESCE(SUM(net_sen), 0) / 100.0 AS net_yen
  FROM dividends WHERE strftime('%Y', paid_on) = ?
"""
SQL_BY_MONTH = """
SELECT strftime('%m', paid_on) AS month, COUNT(*) AS n,
       SUM(net_sen) / 100.0 AS net_yen
  FROM dividends WHERE strftime('%Y', paid_on) = ?
 GROUP BY month ORDER BY month
"""
SQL_DETAIL = """
SELECT d.paid_on AS paid_on, s.name AS name, d.shares AS shares,
       d.per_share_sen / 100.0 AS per_share_yen, d.net_sen / 100.0 AS net_yen
  FROM dividends d JOIN stocks s ON s.ticker = d.ticker
 WHERE strftime('%Y', d.paid_on) = ?
 ORDER BY d.paid_on DESC, d.id DESC
"""
SQL_PERK_MONTHS = """
SELECT pm.month AS month, COUNT(*) AS n,
       SUM(CASE WHEN p.held_shares >= p.min_shares THEN 1 ELSE 0 END) AS reached
  FROM perk_months pm JOIN perks p ON p.id = pm.perk_id
 GROUP BY pm.month ORDER BY pm.month
"""
SQL_PERK_LIST = """
SELECT p.code AS code, p.name AS name, p.min_shares AS min_shares,
       p.held_shares AS held_shares,
       group_concat(pm.month, '/') AS months
  FROM perks p LEFT JOIN perk_months pm ON pm.perk_id = p.id
 GROUP BY p.id ORDER BY p.code
"""
SQL_PERK_KPI = """
SELECT (SELECT COUNT(*) FROM perks) AS n_perks,
       (SELECT COUNT(DISTINCT month) FROM perk_months) AS n_months
"""


# ------------------------------------------------------- 読み取り専用の接続（3章）
@st.cache_resource(show_spinner=False)
def connect_ro(db_path: str) -> sqlite3.Connection:
    """DBを読み取り専用で開く。書き込もうとすると sqlite3 が例外で止める。

    check_same_thread=False にしているのは、キャッシュした接続が再実行のたびに
    別スレッドから使われることがあるため。書き込みを一切しないので、
    複数スレッドから触られても壊れるものがない（記事3章）。
    """
    return sqlite3.connect(f"file:{db_path}?mode=ro", uri=True, check_same_thread=False)


def db_stamp(db_path: str, watch: bool) -> float:
    """DBファイルの更新時刻。キャッシュのキーに混ぜて、更新を拾わせる（記事4章）。"""
    if not watch:
        return 0.0                      # 固定値＝更新しても同じキー＝古い結果が返り続ける
    try:
        return Path(db_path).stat().st_mtime
    except OSError:
        return 0.0


@st.cache_data(show_spinner=False)
def read_sql(_conn: sqlite3.Connection, db_path: str, sql: str,
             params: tuple, stamp: float) -> pd.DataFrame:
    """クエリ結果をキャッシュする。

    先頭の _conn はアンダースコア始まりなのでキャッシュのキーから外れる
    （接続オブジェクトはハッシュできないため）。キーになるのは db_path・sql・
    params・stamp の4つ。stamp が変わると読み直す（記事4章）。
    """
    return pd.read_sql_query(sql, _conn, params=params)


def open_db(db_path: str) -> sqlite3.Connection | None:
    """パスを受け取って接続を返す。開けなければ画面にエラーを出して None。"""
    if not db_path.strip():
        return None
    try:
        return connect_ro(db_path.strip())
    except sqlite3.Error as exc:
        st.error(f"開けませんでした: {type(exc).__name__}: {exc}")
        return None


def query(conn: sqlite3.Connection, db_path: str, sql: str,
          params: tuple, stamp: float) -> pd.DataFrame:
    """読めなければ空のDataFrameを返す（テーブルが無いDBを指した場合など）。"""
    try:
        return read_sql(conn, db_path.strip(), sql, params, stamp)
    except (sqlite3.Error, pd.errors.DatabaseError) as exc:
        st.error(f"読めませんでした: {type(exc).__name__}: {exc}")
        return pd.DataFrame()


# ------------------------------------------------------------ 表示の部品（6〜7章）
def yen(value: float) -> str:
    """円に丸めて3桁区切りにする。第8回のDBは金額を銭（円×100）で持っている。"""
    return f"{value:,.0f} 円"


DIVIDEND_COLUMNS = {
    "paid_on": st.column_config.TextColumn("入金日"),
    "name": st.column_config.TextColumn("銘柄"),
    "shares": st.column_config.NumberColumn("株数", format="%,d"),
    "per_share_yen": st.column_config.NumberColumn("1株あたり", format="yen"),
    "net_yen": st.column_config.NumberColumn("入金額", format="yen"),
}
MONTH_COLUMNS = {
    "month": st.column_config.TextColumn("月"),
    "n": st.column_config.NumberColumn("件数", format="%d"),
    "net_yen": st.column_config.NumberColumn("入金額の合計", format="yen"),
}
PERK_MONTH_COLUMNS = {
    "month": st.column_config.NumberColumn("権利確定月", format="%d月"),
    "n": st.column_config.NumberColumn("登録件数", format="%d"),
    "reached": st.column_config.NumberColumn("うち到達", format="%d"),
}
PERK_LIST_COLUMNS = {
    "code": st.column_config.TextColumn("コード"),
    "name": st.column_config.TextColumn("名称"),
    "min_shares": st.column_config.NumberColumn("必要株数", format="%,d"),
    "held_shares": st.column_config.NumberColumn("保有株数", format="%,d"),
    "months": st.column_config.TextColumn("権利確定月"),
}


def show_table(df: pd.DataFrame, columns: dict, height: int | str = "auto") -> None:
    """列の見せ方をまとめて指定して表を出す。width='stretch' が現行の書き方（5章）。"""
    if df.empty:
        st.info("表示できる行がありません。")
        return
    st.dataframe(df, width="stretch", hide_index=True,
                 column_config=columns, height=height)


# --------------------------------------------------- 第6回のFigureを貼る（7章）
def sixth_figure():
    """第6回 dashboard.py の build_dashboard() が返す Figure をそのまま受け取る。

    dashboard.py を同じフォルダに置いておくと表示される。取引履歴と損益一覧は
    第6回の __main__ にある架空のサンプルと同じ値（実運用では第3回のCSV読み込みと
    第4回の集計結果に差し替える）。
    """
    sys.path.insert(0, str(APP_DIR))
    try:
        import dashboard                      # 第6回の配布コード
    except ModuleNotFoundError:
        return None

    transactions = pd.DataFrame(
        [
            ("2025-04-10", "ALPHA", "buy", 100, 2500.0, 250.0),
            ("2025-05-15", "BETA", "buy", 200, 1200.0, 220.0),
            ("2025-06-01", "GAMMA", "buy", 300, 800.0, 330.0),
            ("2025-09-12", "ALPHA", "buy", 100, 3000.0, 250.0),
            ("2025-11-11", "GAMMA", "buy", 100, 950.0, 275.0),
            ("2026-01-30", "BETA", "sell", 200, 1050.0, 200.0),
            ("2026-02-20", "ALPHA", "sell", 50, 3400.0, 275.0),
        ],
        columns=["date", "ticker", "type", "qty", "price", "fee"],
    )
    transactions["date"] = pd.to_datetime(transactions["date"])
    summary = pd.DataFrame({
        "ticker": ["ALPHA", "BETA", "GAMMA"],
        "qty": [150, 0, 400],
        "market_value": [487500.0, 0.0, 312000.0],
        "total_pl": [111725.0, -25620.0, -17605.0],
    })
    prices = dashboard.make_price_history()
    qty, value = dashboard.build_value_history(transactions, prices)
    return dashboard.build_dashboard(qty, value, summary), dashboard.CONFIG


def optional_matplotlib_figure():
    """第5回・第7回のような『Figureを返す関数』の差し込み口（7章）。

    同じフォルダに my_charts.py を置き、その中に Figure を返す build_figure() を
    定義すると、下の st.pyplot(fig) にそのまま渡される。用意していなければ何もしない。
    """
    sys.path.insert(0, str(APP_DIR))
    try:
        from my_charts import build_figure
    except ModuleNotFoundError:
        return None
    return build_figure()


# -------------------------------------------------- 時価の取得（外部通信・8章）
def fetch_last_price(symbol: str) -> None:
    """ボタンを押したときだけ外部と通信する。取れた値は表示するだけ。

    保有株数と掛けた評価額は出さない。この道具は記録を並べるためのもので、
    資産を査定するためのものではない（記事1章・8章）。
    """
    symbol = symbol.strip()
    if not symbol:
        st.warning("銘柄コードを入れてください。")
        return
    try:
        import yfinance as yf               # 通信するときだけ読み込む
        hist = yf.Ticker(symbol).history(period="1d")
    except Exception as exc:                # 通信・パースの失敗をここで止める
        st.error(f"取得できませんでした: {type(exc).__name__}")
        return
    if hist.empty:
        st.warning("値が取れませんでした。コードの形式と通信の状態を確認してください。")
        return
    st.metric(f"{symbol} の終値", f"{float(hist['Close'].iloc[-1]):,.2f}")
    st.caption(
        f"取得: {datetime.now():%Y-%m-%d %H:%M}／対象日: {hist.index[-1]:%Y-%m-%d}。"
        "yfinance は Yahoo! Finance の非公式ラッパーで、値の正確性・遅延・提供の継続は"
        "保証されません。参考表示だけで、保有株数と掛けた評価額は出しません。"
    )


# ------------------------------------------------------------------ 画面（1章）
st.set_page_config(page_title="投資ダッシュボード（ローカル）", layout="wide")

with st.sidebar:
    st.header("読み込むファイル")
    div_db = st.text_input("配当DB（第8回 dividends.db）", value=ARGS.div_db,
                           placeholder="C:/Users/you/dividends.db")
    perks_db = st.text_input("優待DB（第11回 perks.db）", value=ARGS.perks_db,
                             placeholder="C:/Users/you/perks.db")
    st.caption("どちらも読み取り専用（mode=ro）で開きます。1行も書き込みません。")
    year_slot = st.container()      # 年の選択はここに置く（中身はDBを読んでから入れる）

    st.divider()
    st.header("読み直しの設定")
    watch = st.checkbox("DBの更新時刻を見て読み直す", value=True,
                        help="外すと、別のアプリでDBを更新しても古い値が出続けます（記事4章の実演）")
    if st.button("キャッシュを捨てて読み直す"):
        st.cache_data.clear()
        st.rerun()

    st.divider()
    st.header("時価の取得")
    st.caption("⚠️ このボタンを押したときだけ外部と通信します。押さなければ完全にオフラインです。")
    symbol = st.text_input("銘柄コード（例: 1234.T の形式）", key="symbol")
    if st.button("時価を取得（外部通信）"):
        fetch_last_price(symbol)

st.title("投資ダッシュボード")
st.caption(
    "第8回・第11回のSQLiteと、第10回・第12回のCSVを1画面に並べます。"
    "表示しているのは自分で入力した記録の集計で、銘柄の評価も売買の判断もしません。"
)

div_conn = open_db(div_db)
perks_conn = open_db(perks_db)
if div_conn is None and perks_conn is None:
    st.info("サイドバーにDBのパスを入れると表示が始まります。ファイルは読むだけです。")

# --- 上段：数字のまとめ ------------------------------------------------------
year = ""
kpi = st.columns(4)
if div_conn is not None:
    stamp = db_stamp(div_db, watch)
    years = query(div_conn, div_db, SQL_YEARS, (), stamp)
    if not years.empty:
        year = year_slot.selectbox("集計する年", years["year"].tolist(), key="year")
        row = query(div_conn, div_db, SQL_YEAR_KPI, (year,), stamp)
        kpi[0].metric(f"{year}年に記録した受取配当", yen(float(row["net_yen"][0])))
        kpi[1].metric(f"{year}年の記録件数", f"{int(row['n'][0])} 件")
if perks_conn is not None:
    pstamp = db_stamp(perks_db, watch)
    prow = query(perks_conn, perks_db, SQL_PERK_KPI, (), pstamp)
    if not prow.empty:
        kpi[2].metric("登録した優待", f"{int(prow['n_perks'][0])} 件")
        kpi[3].metric("権利確定月がある月", f"{int(prow['n_months'][0])} / 12 か月")
st.caption("上の数字は入力済みの記録を足したものです。増減の矢印や色は付けていません。")

# --- 配当（第8回のDB） -------------------------------------------------------
if div_conn is not None and year:
    st.subheader("受取配当の記録（第8回のDB・読み取り専用）")
    left, right = st.columns([1, 2])
    with left:
        show_table(query(div_conn, div_db, SQL_BY_MONTH, (year,), stamp), MONTH_COLUMNS)
    with right:
        show_table(query(div_conn, div_db, SQL_DETAIL, (year,), stamp),
                   DIVIDEND_COLUMNS)

# --- 優待（第11回のDB） ------------------------------------------------------
if perks_conn is not None:
    st.subheader("株主優待の権利確定月（第11回のDB・読み取り専用）")
    st.caption("配当DBの銘柄コードと優待DBのコードは体系が違うので、"
               "つなげずに並べています（記事3章）。")
    left, right = st.columns([1.1, 2])
    with left:
        show_table(query(perks_conn, perks_db, SQL_PERK_MONTHS, (), pstamp),
                   PERK_MONTH_COLUMNS)
    with right:
        show_table(query(perks_conn, perks_db, SQL_PERK_LIST, (), pstamp),
                   PERK_LIST_COLUMNS)

# --- 第6回のFigure -----------------------------------------------------------
st.subheader("評価額の推移と構成比（第6回のFigureをそのまま）")
built = sixth_figure()
if built is None:
    st.info("同じフォルダに第6回の dashboard.py を置くと、ここに表示されます。")
else:
    fig, config = built
    st.plotly_chart(fig, width="stretch", config=config, key="sixth")
    st.caption("第6回の build_dashboard() が返した Figure を貼っただけです（架空のサンプルデータ）。")

pyfig = optional_matplotlib_figure()
if pyfig is not None:
    st.pyplot(pyfig)
    import matplotlib.pyplot as plt
    plt.close(pyfig)                        # 再実行のたびに増えるので閉じる（7章）

# --- CSV（第10回・第12回の出力） --------------------------------------------
st.subheader("CSVを読む（第10回・第12回の出力）")
uploaded = st.file_uploader("CSVを選ぶ", type="csv",
                            help="第10回・第12回が書き出したCSVは先頭に # の行が付きます")
if uploaded is not None:
    df = pd.read_csv(uploaded, comment="#")   # # の行を読み飛ばす（記事6章）
    st.caption(f"{uploaded.name}／{len(df)} 行 × {len(df.columns)} 列")
    show_table(df, {}, height=260)
else:
    st.info("読み込んだCSVはこのPCの中だけで処理されます。どこにも送信しません。")