sqlite_handler.py

import sqlite3
from datetime import datetime, timedelta, timezone
from config import LOCAL_DB_PATH
import hf_db_sync
import pandas as pd

DEFAULT_USER_ID = "0001"

def save_result_to_db(carb_grams, current_bg, icr, isf, result, actual_insulin, timing_id, timestamp_text=None):
    hf_db_sync.download_and_prepare_db()

    if timestamp_text:
        try:
            timestamp = datetime.strptime(timestamp_text, "%Y-%m-%d %H:%M")
        except ValueError:
            return "❌ 日時の形式が正しくありません（例: 2025-04-22 14:30）"
    else:
        JST = timezone(timedelta(hours=9))
        timestamp = datetime.now(JST)

    timestamp_str = timestamp.strftime('%Y-%m-%d %H:%M:%S')

    conn = sqlite3.connect(LOCAL_DB_PATH)
    cursor = conn.cursor()

    cursor.execute("""
    INSERT INTO records (
        user_id, timestamp, carbs, current_bg, icr, isf,
        carb_insulin, correction_insulin, total_insulin, target_bg, timing_id
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (
        DEFAULT_USER_ID,
        timestamp_str,
        carb_grams,
        current_bg,
        icr,
        isf,
        result["carb_insulin"],
        result["correction_insulin"],
        actual_insulin,
        result["expected_bg"],
        timing_id
    ))

    conn.commit()
    conn.close()

    hf_db_sync.upload_db_to_dataset()

    return f"✅ 記録しました（インスリン {actual_insulin} 単位, タイミング ID: {timing_id}）"

def load_records():
    try:
        hf_db_sync.download_and_prepare_db()

        conn = sqlite3.connect(LOCAL_DB_PATH)
        query = """
        SELECT * FROM records ORDER BY timestamp
        """
        df = pd.read_sql_query(query, conn)
        conn.close()

        if not df.empty:
            df["timestamp"] = pd.to_datetime(df["timestamp"])

        return df

    except Exception as e:
        print(f"❌ DB読み込みエラー: {e}")
        return pd.DataFrame()