database.py

import sqlite3
from datetime import date


# ---------------- DATABASE CONNECTION ----------------
def get_connection():
    conn = sqlite3.connect("fitplan.db", check_same_thread=False)
    return conn


# ---------------- CREATE TABLES ----------------
def create_tables():
    conn = get_connection()
    cur = conn.cursor()

    cur.execute("""
    CREATE TABLE IF NOT EXISTS users(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        email TEXT UNIQUE,
        password TEXT,
        otp TEXT
    )
    """)

    cur.execute("""
    CREATE TABLE IF NOT EXISTS fitness_data(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_email TEXT,
        date TEXT,
        weight REAL
    )
    """)

    conn.commit()
    conn.close()


# ---------------- REGISTER USER ----------------
def register_user(email, password):

    conn = get_connection()
    cur = conn.cursor()

    try:
        cur.execute(
            "INSERT INTO users(email,password) VALUES (?,?)",
            (email, password)
        )
        conn.commit()
        return True
    except:
        return False
    finally:
        conn.close()


# ---------------- LOGIN USER ----------------
def login_user(email, password):

    conn = get_connection()
    cur = conn.cursor()

    cur.execute(
        "SELECT * FROM users WHERE email=? AND password=?",
        (email, password)
    )

    user = cur.fetchone()

    conn.close()

    return user


# ---------------- SAVE WEIGHT ----------------
def save_weight(email, weight):

    today = str(date.today())

    conn = get_connection()
    cur = conn.cursor()

    cur.execute(
        "INSERT INTO fitness_data(user_email,date,weight) VALUES (?,?,?)",
        (email, today, weight)
    )

    conn.commit()
    conn.close()


# ---------------- GET WEIGHT HISTORY ----------------
def get_weight_history(email):

    conn = get_connection()
    cur = conn.cursor()

    cur.execute(
        "SELECT date,weight FROM fitness_data WHERE user_email=?",
        (email,)
    )

    data = cur.fetchall()

    conn.close()

    return data