import os
import sqlite3
from datetime import datetime, timezone, timedelta

DB_PATH = os.getenv("APP_DB_PATH", os.path.join(os.path.dirname(__file__), "..", "data", "app.db"))
DB_DIR = os.path.dirname(DB_PATH)

DDL = """
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS stocks(
  code TEXT PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS indicators_daily(
  code TEXT NOT NULL,
  snapshot_at DATE NOT NULL,
  per REAL, roe REAL, pbr REAL, sales_growth REAL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY(code, snapshot_at),
  FOREIGN KEY(code) REFERENCES stocks(code)
);

CREATE TABLE IF NOT EXISTS recommendations_latest(
  code TEXT PRIMARY KEY,
  rule_set_id TEXT NOT NULL,
  reason TEXT,
  scored_at DATETIME NOT NULL,
  FOREIGN KEY(code) REFERENCES stocks(code)
);

CREATE TABLE IF NOT EXISTS meta(
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL
);
"""

SEED_STOCKS = [
    ("7203", "トヨタ自動車"),
    ("6758", "ソニーグループ"),
    ("9432", "日本電信電話"),
    ("9984", "ソフトバンクグループ"),
    ("4063", "信越化学工業"),
]

def jst_now_iso():
    jst = timezone(timedelta(hours=9))
    return datetime.now(jst).isoformat(timespec="seconds")

def main():
    os.makedirs(DB_DIR, exist_ok=True)
    con = sqlite3.connect(DB_PATH)
    con.row_factory = sqlite3.Row
    cur = con.cursor()

    # DDL
    cur.executescript(DDL)

    # 初期銘柄（存在チェックしてからINSERT）
    for code, name in SEED_STOCKS:
        cur.execute("INSERT OR IGNORE INTO stocks(code, name) VALUES (?,?)", (code, name))

    # デモ用に軽く最新指標を1日分だけ入れておく（任意）
    today = datetime.now(timezone(timedelta(hours=9))).date().isoformat()
    now = jst_now_iso()
    demo_indicators = [
        ("7203", today, 12.3, 13.1, 1.2, 4.8, now),
        ("6758", today, 14.8, 12.5, 1.6, 3.4, now),
    ]
    for row in demo_indicators:
        cur.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
        """, row)

    # デモ用おすすめ（ROE>=12のものを入れておく）
    cur.execute("DELETE FROM recommendations_latest")
    for code, _ in SEED_STOCKS:
        # 最新スナップショットのROEを確認
        r = cur.execute("""
            SELECT roe FROM indicators_daily
             WHERE code = ?
             ORDER BY snapshot_at DESC
             LIMIT 1
        """, (code,)).fetchone()
        if r and r["roe"] is not None and r["roe"] >= 12.0:
            cur.execute("""
                INSERT INTO recommendations_latest(code, rule_set_id, reason, scored_at)
                VALUES (?, 'default', 'ROE>=12', ?)
            """, (code, now))

    # 最終更新（meta）
    cur.execute("""
        INSERT INTO meta(key, value)
        VALUES('last_update', ?)
        ON CONFLICT(key) DO UPDATE SET value=excluded.value
    """, (now,))

    con.commit()
    con.close()
    print(f"[OK] Initialized DB at: {DB_PATH}")

if __name__ == "__main__":
    main()