database.py

import sqlite3
import json
from datetime import date, datetime

conn = sqlite3.connect("fitplan.db", check_same_thread=False)
conn.row_factory = sqlite3.Row
cursor = conn.cursor()
cursor.execute("PRAGMA foreign_keys = ON")

# ── TABLE 1: Core authentication ─────────────────────────────────────────────
cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
    id       INTEGER PRIMARY KEY AUTOINCREMENT,
    name     TEXT    NOT NULL,
    email    TEXT    UNIQUE NOT NULL,
    password TEXT    NOT NULL,
    created_at TEXT  DEFAULT (datetime('now'))
)
""")

# ── TABLE 2: Health onboarding & safety profile ───────────────────────────────
cursor.execute("""
CREATE TABLE IF NOT EXISTS user_onboarding (
    email             TEXT PRIMARY KEY,
    conditions        TEXT,   -- JSON list: e.g. ["Lower Back Pain", "Hypertension"]
    pain_points       TEXT,   -- JSON list: e.g. ["Knee", "Shoulder"]
    past_surgeries    TEXT,
    pregnancy_status  TEXT    DEFAULT 'None',
    allergens         TEXT,   -- JSON list: e.g. ["Dairy", "Gluten"]
    dietary_type      TEXT    DEFAULT 'Pure Veg',
    regional_cuisine  TEXT    DEFAULT 'Generic Indian',
    leisure_sports    TEXT,   -- JSON list for Sunday recovery activity choices
    onboarding_complete INTEGER DEFAULT 0,
    FOREIGN KEY(email) REFERENCES users(email)
)
""")

# ── TABLE 3: Fluctuating biometrics & training parameters ─────────────────────
cursor.execute("""
CREATE TABLE IF NOT EXISTS user_metrics (
    email     TEXT PRIMARY KEY,
    name      TEXT,
    gender    TEXT,
    height    REAL,
    weight    REAL,
    age       INTEGER,
    goal      TEXT,
    level     TEXT,
    equipment TEXT,   -- JSON list
    bmi       REAL,
    bmi_status TEXT,
    updated_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY(email) REFERENCES users(email)
)
""")

# ── TABLE 4: Weekly training + nutrition blueprint ────────────────────────────
cursor.execute("""
CREATE TABLE IF NOT EXISTS weekly_blueprints (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    email           TEXT NOT NULL,
    week_start      TEXT NOT NULL,   -- ISO date: YYYY-MM-DD (Monday)
    training_matrix TEXT,            -- Full 7-day workout plan text
    nutrition_matrix TEXT,           -- Full 7-day diet plan text
    raw_full_plan   TEXT,            -- Original unsplit LLM output
    generated_at    TEXT DEFAULT (datetime('now')),
    FOREIGN KEY(email) REFERENCES users(email)
)
""")

# ── TABLE 5: Engagement, streaks & daily completion logs ──────────────────────
cursor.execute("""
CREATE TABLE IF NOT EXISTS engagement_matrix (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    email           TEXT NOT NULL,
    log_date        TEXT NOT NULL,       -- ISO date: YYYY-MM-DD
    day_number      INTEGER NOT NULL,    -- 1–7 within the weekly cycle
    workout_done    INTEGER DEFAULT 0,   -- 0 or 1
    diet_done       INTEGER DEFAULT 0,
    flexspace_note  TEXT,                -- Any realtime alteration applied
    flexspace_patch TEXT,                -- LLM-returned patch text
    current_streak  INTEGER DEFAULT 0,
    UNIQUE(email, log_date),
    FOREIGN KEY(email) REFERENCES users(email)
)
""")

conn.commit()


# ─────────────────────────────────────────────────────────────────────────────
# AUTH HELPERS
# ─────────────────────────────────────────────────────────────────────────────

def add_user(name: str, email: str, password: str) -> bool:
    try:
        cursor.execute(
            "INSERT INTO users (name, email, password) VALUES (?, ?, ?)",
            (name, email, password)
        )
        conn.commit()
        return True
    except Exception:
        return False


def user_exists(email: str):
    cursor.execute("SELECT id FROM users WHERE email=?", (email,))
    return cursor.fetchone()


def verify_login(email: str, password: str):
    cursor.execute(
        "SELECT id, name, email FROM users WHERE email=? AND password=?",
        (email, password)
    )
    return cursor.fetchone()


# ─────────────────────────────────────────────────────────────────────────────
# ONBOARDING HELPERS
# ─────────────────────────────────────────────────────────────────────────────

def save_onboarding(email, conditions, pain_points, past_surgeries,
                    pregnancy_status, allergens, dietary_type,
                    regional_cuisine, leisure_sports):
    cursor.execute("""
        INSERT INTO user_onboarding
            (email, conditions, pain_points, past_surgeries, pregnancy_status,
             allergens, dietary_type, regional_cuisine, leisure_sports, onboarding_complete)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 1)
        ON CONFLICT(email) DO UPDATE SET
            conditions=excluded.conditions,
            pain_points=excluded.pain_points,
            past_surgeries=excluded.past_surgeries,
            pregnancy_status=excluded.pregnancy_status,
            allergens=excluded.allergens,
            dietary_type=excluded.dietary_type,
            regional_cuisine=excluded.regional_cuisine,
            leisure_sports=excluded.leisure_sports,
            onboarding_complete=1
    """, (
        email,
        json.dumps(conditions),
        json.dumps(pain_points),
        past_surgeries,
        pregnancy_status,
        json.dumps(allergens),
        dietary_type,
        regional_cuisine,
        json.dumps(leisure_sports)
    ))
    conn.commit()


def get_onboarding(email) -> dict | None:
    cursor.execute("SELECT * FROM user_onboarding WHERE email=?", (email,))
    row = cursor.fetchone()
    if not row:
        return None
    d = dict(row)
    for key in ("conditions", "pain_points", "allergens", "leisure_sports"):
        try:
            d[key] = json.loads(d[key]) if d[key] else []
        except Exception:
            d[key] = []
    return d


def onboarding_complete(email) -> bool:
    cursor.execute(
        "SELECT onboarding_complete FROM user_onboarding WHERE email=?", (email,)
    )
    row = cursor.fetchone()
    return bool(row and row[0])


# ─────────────────────────────────────────────────────────────────────────────
# METRICS HELPERS
# ─────────────────────────────────────────────────────────────────────────────

def save_metrics(email, name, gender, height, weight, age,
                 goal, level, equipment, bmi, bmi_status):
    cursor.execute("""
        INSERT INTO user_metrics
            (email, name, gender, height, weight, age, goal, level,
             equipment, bmi, bmi_status, updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, datetime('now'))
        ON CONFLICT(email) DO UPDATE SET
            name=excluded.name, gender=excluded.gender,
            height=excluded.height, weight=excluded.weight,
            age=excluded.age, goal=excluded.goal,
            level=excluded.level, equipment=excluded.equipment,
            bmi=excluded.bmi, bmi_status=excluded.bmi_status,
            updated_at=datetime('now')
    """, (
        email, name, gender, height, weight, age, goal, level,
        json.dumps(equipment), bmi, bmi_status
    ))
    conn.commit()


def get_metrics(email) -> dict | None:
    cursor.execute("SELECT * FROM user_metrics WHERE email=?", (email,))
    row = cursor.fetchone()
    if not row:
        return None
    d = dict(row)
    try:
        d["equipment"] = json.loads(d["equipment"]) if d["equipment"] else []
    except Exception:
        d["equipment"] = []
    return d


# ─────────────────────────────────────────────────────────────────────────────
# BLUEPRINT HELPERS
# ─────────────────────────────────────────────────────────────────────────────

def _current_week_monday() -> str:
    today = date.today()
    monday = today - __import__("datetime").timedelta(days=today.weekday())
    return monday.isoformat()


def save_blueprint(email, training_matrix, nutrition_matrix, raw_full_plan):
    week_start = _current_week_monday()
    cursor.execute("""
        INSERT INTO weekly_blueprints
            (email, week_start, training_matrix, nutrition_matrix, raw_full_plan)
        VALUES (?, ?, ?, ?, ?)
    """, (email, week_start, training_matrix, nutrition_matrix, raw_full_plan))
    conn.commit()


def get_latest_blueprint(email) -> dict | None:
    cursor.execute("""
        SELECT * FROM weekly_blueprints
        WHERE email=?
        ORDER BY generated_at DESC
        LIMIT 1
    """, (email,))
    row = cursor.fetchone()
    return dict(row) if row else None


def update_blueprint_patch(email, day_number, field, patch_text):
    """Applies a FlexSpace patch to a specific day cell in the latest blueprint."""
    bp = get_latest_blueprint(email)
    if not bp:
        return
    target_col = "training_matrix" if field == "workout" else "nutrition_matrix"
    existing = bp[target_col] or ""
    marker = f"[DAY_{day_number}_PATCH]"
    patched = f"{existing}\n\n{marker}\n{patch_text}"
    cursor.execute(f"""
        UPDATE weekly_blueprints
        SET {target_col}=?
        WHERE id=?
    """, (patched, bp["id"]))
    conn.commit()


# ─────────────────────────────────────────────────────────────────────────────
# ENGAGEMENT / STREAK HELPERS
# ─────────────────────────────────────────────────────────────────────────────

def _calc_streak(email) -> int:
    cursor.execute("""
        SELECT log_date FROM engagement_matrix
        WHERE email=? AND workout_done=1
        ORDER BY log_date DESC
    """, (email,))
    rows = [r[0] for r in cursor.fetchall()]
    if not rows:
        return 0

    streak = 0
    check = date.today()
    for d_str in rows:
        d = date.fromisoformat(d_str)
        if d == check or d == check - __import__("datetime").timedelta(days=1):
            streak += 1
            check = d - __import__("datetime").timedelta(days=1)
        else:
            break
    return streak


def log_day_completion(email, day_number, workout_done=True, diet_done=False):
    today = date.today().isoformat()
    streak = _calc_streak(email) + (1 if workout_done else 0)
    cursor.execute("""
        INSERT INTO engagement_matrix
            (email, log_date, day_number, workout_done, diet_done, current_streak)
        VALUES (?, ?, ?, ?, ?, ?)
        ON CONFLICT(email, log_date) DO UPDATE SET
            workout_done=excluded.workout_done,
            diet_done=excluded.diet_done,
            current_streak=excluded.current_streak
    """, (email, today, day_number, int(workout_done), int(diet_done), streak))
    conn.commit()
    return streak


def log_flexspace(email, day_number, note, patch_text):
    today = date.today().isoformat()
    cursor.execute("""
        INSERT INTO engagement_matrix (email, log_date, day_number, flexspace_note, flexspace_patch)
        VALUES (?, ?, ?, ?, ?)
        ON CONFLICT(email, log_date) DO UPDATE SET
            flexspace_note=excluded.flexspace_note,
            flexspace_patch=excluded.flexspace_patch
    """, (email, today, day_number, note, patch_text))
    conn.commit()


def get_streak(email) -> int:
    cursor.execute("""
        SELECT current_streak FROM engagement_matrix
        WHERE email=?
        ORDER BY log_date DESC LIMIT 1
    """, (email,))
    row = cursor.fetchone()
    return row[0] if row else 0


def get_completed_days(email) -> list[str]:
    cursor.execute("""
        SELECT log_date FROM engagement_matrix
        WHERE email=? AND workout_done=1
    """, (email,))
    return [r[0] for r in cursor.fetchall()]


def get_today_log(email) -> dict | None:
    today = date.today().isoformat()
    cursor.execute(
        "SELECT * FROM engagement_matrix WHERE email=? AND log_date=?",
        (email, today)
    )
    row = cursor.fetchone()
    return dict(row) if row else None