database.py

import sqlite3
import os
from datetime import date

DB_FILE = "users.db"


def get_connection():
    return sqlite3.connect(DB_FILE, check_same_thread=False)


def recreate_database():
    if os.path.exists(DB_FILE):
        os.remove(DB_FILE)


def create_tables():

    try:
        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 weight_history(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            email TEXT,
            date TEXT,
            weight REAL
        )
        """)

        conn.commit()
        conn.close()

    except sqlite3.DatabaseError:
        # database corrupted → recreate
        recreate_database()

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

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

        cur.execute("""
        CREATE TABLE weight_history(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            email TEXT,
            date TEXT,
            weight REAL
        )
        """)

        conn.commit()
        conn.close()


# -----------------------
# USER FUNCTIONS
# -----------------------

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()


def login_user_db(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


# -----------------------
# OTP
# -----------------------

def save_otp(email, otp):

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

    cur.execute(
        "UPDATE users SET otp=? WHERE email=?",
        (otp, email)
    )

    conn.commit()
    conn.close()


def verify_otp_db(email, otp):

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

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

    user = cur.fetchone()

    conn.close()

    return user


# -----------------------
# WEIGHT TRACKER
# -----------------------

def save_weight(email, weight):

    today = str(date.today())

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

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

    conn.commit()
    conn.close()


def get_weight_history(email):

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

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

    data = cur.fetchall()

    conn.close()

    return data