db_init.py

import sqlite3
from config import DB_PATH,HF_TOKEN, REPO_ID, DATA_FILE_HUB, DATA_FILE_LOCAL, DEFAULT_USER_ID
import pandas as pd

def init_db(db_path):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS timing (
        id INTEGER PRIMARY KEY,
        label TEXT NOT NULL
    )
    """)

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS records (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id TEXT,
        timestamp TEXT,
        carbs REAL,
        current_bg REAL,
        icr REAL,
        isf REAL,
        carb_insulin REAL,
        correction_insulin REAL,
        total_insulin REAL,
        target_bg REAL,
        timing_id INTEGER,
        FOREIGN KEY (timing_id) REFERENCES timing(id)
    )
    """)

    timing_data = [
        (1, "朝食"), (2, "昼食"), (3, "夜食"),
        (4, "おやつ"), (5, "追加"), (6, "寝前")
    ]
    cursor.executemany("INSERT OR IGNORE INTO timing (id, label) VALUES (?, ?)", timing_data)

    conn.commit()
    conn.close()

    
###############################CSVからDBにデータ移行########################################
# 1. DB準備
hf_db_sync.download_and_prepare_db()

# 2. CSV初期化
def initialize_data_file():
    try:
        hf_hub_download(
            repo_id=REPO_ID,
            filename=DATA_FILE_HUB,
            local_dir="./",
            repo_type="dataset",
            token=HF_TOKEN
        )
        print("✅ 既存のCSVファイルを取得しました")
    except Exception as e:
        print(f"⚠️ CSVが取得できなかったため、新規作成します: {e}")
        df = pd.DataFrame(columns=[
            'user_id', 'timestamp', 'carbs', 'current_bg', 'icr', 'isf',
            'carb_insulin', 'correction_insulin', 'total_insulin', 'target_bg', 'timing'
        ])
        df.to_csv(DATA_FILE_LOCAL, index=False)

initialize_data_file()

# 3. CSV読み込みと変換
csv_path = DATA_FILE_LOCAL
df = pd.read_csv(csv_path)

timing_map = {
    "朝食": 1, "昼食": 2, "夜食": 3,
    "おやつ": 4, "追加": 5, "寝前": 6,
    None: None, "": None
}

if "timing" in df.columns:
    df["timing_id"] = df["timing"].map(timing_map)
    df.drop(columns=["timing"], inplace=True)
else:
    df["timing_id"] = None

# 4. DBに書き込み
conn = sqlite3.connect(LOCAL_DB_PATH)
df.to_sql("records", conn, if_exists="append", index=False)
conn.close()

print(f"✅ {len(df)} 行を SQLite に移行しました")

# 5. HF Dataset へアップロード
hf_db_sync.upload_db_to_dataset()


##############DB内容確認#########################
hf_db_sync.download_and_prepare_db()

conn = sqlite3.connect(LOCAL_DB_PATH)  # ← あなたのDBパスに変更
cursor = conn.cursor()

cursor.execute("SELECT count(*) FROM records")
tables = cursor.fetchall()

conn.close()

print("📋 テーブル一覧:")
for t in tables:
    print("-", t[0])