LocalFoodAI_FerRo988/database.py

import sqlite3
import os
import logging
import secrets
from datetime import datetime, timedelta
from typing import Optional, Dict, Any, List

logger = logging.getLogger(__name__)

# Locate db correctly in the same directory
DB_PATH = os.path.join(os.path.dirname(__file__), "localfood.db")

def get_db_connection():
    # Enable higher timeout and disable thread checks for FastAPI async compatibility
    conn = sqlite3.connect(DB_PATH, timeout=30.0, check_same_thread=False)
    conn.row_factory = sqlite3.Row
    # Enable Write-Ahead Log (WAL) mode for simultaneous read/write operations
    conn.execute('PRAGMA journal_mode=WAL')
    conn.execute('PRAGMA synchronous=NORMAL')
    return conn

def create_tables():
    """Initialize the SQLite database with required tables"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # Create users table securely locally
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password_hash TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        ''')

        # Create sessions table for database-backed tokens
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS sessions (
            token TEXT PRIMARY KEY,
            user_id INTEGER NOT NULL,
            expires_at TIMESTAMP NOT NULL,
            FOREIGN KEY (user_id) REFERENCES users (id)
        )
        ''')

        # Create localized foods table based on Sprint 5 architecture
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS foods (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            category TEXT,
            base_weight_g REAL DEFAULT 100.0,
            calories REAL DEFAULT 0.0,
            protein_g REAL DEFAULT 0.0,
            fat_g REAL DEFAULT 0.0,
            carbs_g REAL DEFAULT 0.0,
            fiber_g REAL DEFAULT 0.0,
            sugar_g REAL DEFAULT 0.0,
            sodium_mg REAL DEFAULT 0.0,
            vitamin_a_iu REAL DEFAULT 0.0,
            vitamin_c_mg REAL DEFAULT 0.0,
            calcium_mg REAL DEFAULT 0.0,
            iron_mg REAL DEFAULT 0.0,
            potassium_mg REAL DEFAULT 0.0,
            cholesterol_mg REAL DEFAULT 0.0,
            source TEXT DEFAULT 'System'
        )
        ''')

        # Create chat history table for Sprint 6 persistence
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS chat_messages (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            role TEXT NOT NULL,
            content TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users (id)
        )
        ''')
        
        # Create minimal user_profiles table for macro targets (US-07)
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS user_profiles (
            user_id INTEGER PRIMARY KEY,
            target_calories INTEGER DEFAULT 2000,
            target_protein_g INTEGER DEFAULT 150,
            target_carbs_g INTEGER DEFAULT 200,
            target_fat_g INTEGER DEFAULT 65,
            FOREIGN KEY (user_id) REFERENCES users (id)
        )
        ''')
        
        # Create user-named meals table for Sprint 8
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS saved_meals (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            name TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users (id)
        )
        ''')
        
        # Create meal items table to link multiple foods to a single saved meal
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS meal_items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            meal_id INTEGER NOT NULL,
            food_id INTEGER NOT NULL,
            amount_g REAL NOT NULL,
            FOREIGN KEY (meal_id) REFERENCES saved_meals (id) ON DELETE CASCADE,
            FOREIGN KEY (food_id) REFERENCES foods (id)
        )
        ''')
        
        # Create index for rapid fuzzy search compatibility
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_food_name ON foods(name COLLATE NOCASE)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_saved_meals_user ON saved_meals(user_id)')
        
        conn.commit()
        logger.info("Database and tables initialized successfully.")
    except Exception as e:
        logger.error(f"Error initializing database: {e}")
        raise
    finally:
        if conn:
            conn.close()
def save_user_meal(user_id: int, name: str, items: List[Dict[str, Any]]) -> Optional[int]:
    """Persist a collection of food items as a named meal list for a user"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 1. Create the meal header
        cursor.execute(
            "INSERT INTO saved_meals (user_id, name) VALUES (?, ?)",
            (user_id, name)
        )
        meal_id = cursor.lastrowid
        
        # 2. Add each item linked to this meal
        for item in items:
            cursor.execute(
                "INSERT INTO meal_items (meal_id, food_id, amount_g) VALUES (?, ?, ?)",
                (meal_id, item['food_id'], item['amount_g'])
            )
        
        conn.commit()
        return meal_id
    except Exception as e:
        logger.error(f"Error saving user meal: {e}")
        if conn: conn.rollback()
        return None
    finally:
        if conn: conn.close()

def update_user_meal(user_id: int, meal_id: int, name: str, items: Optional[List[Dict[str, Any]]] = None) -> bool:
    """Update a user's saved meal (name and optionally items), verifying ownership."""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # Verify ownership first
        cursor.execute("SELECT id FROM saved_meals WHERE id = ? AND user_id = ?", (meal_id, user_id))
        if not cursor.fetchone():
            return False
            
        cursor.execute(
            "UPDATE saved_meals SET name = ? WHERE id = ? AND user_id = ?",
            (name, meal_id, user_id)
        )
        
        if items is not None:
            # Delete old items
            cursor.execute("DELETE FROM meal_items WHERE meal_id = ?", (meal_id,))
            # Insert new items
            for item in items:
                cursor.execute(
                    "INSERT INTO meal_items (meal_id, food_id, amount_g) VALUES (?, ?, ?)",
                    (meal_id, item['food_id'], item['amount_g'])
                )
                
        conn.commit()
        return True
    except Exception as e:
        logger.error(f"Error updating user meal: {e}")
        if conn: conn.rollback()
        return False
    finally:
        if conn: conn.close()

def delete_user_meal(user_id: int, meal_id: int) -> bool:
    """Delete a user's saved meal and its items, verifying ownership."""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        # Verify ownership and get the meal ID
        cursor.execute("SELECT id FROM saved_meals WHERE id = ? AND user_id = ?", (meal_id, user_id))
        if not cursor.fetchone():
            return False
            
        # Manually delete items first to handle missing PRAGMA foreign_keys
        cursor.execute("DELETE FROM meal_items WHERE meal_id = ?", (meal_id,))
        cursor.execute("DELETE FROM saved_meals WHERE id = ? AND user_id = ?", (meal_id, user_id))
        
        success = cursor.rowcount > 0
        conn.commit()
        return success
    except Exception as e:
        logger.error(f"Error deleting user meal: {e}")
        if conn: conn.rollback()
        return False
    finally:
        if conn: conn.close()


def get_user_meals(user_id: int) -> List[Dict[str, Any]]:
    """Retrieve all saved meals for a user, including total nutritional calculations"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 1. Get all meals for this user
        cursor.execute("SELECT id, name, created_at FROM saved_meals WHERE user_id = ? ORDER BY created_at DESC", (user_id,))
        meals = [dict(row) for row in cursor.fetchall()]
        
        # 2. For each meal, get the items and calculate totals
        for meal in meals:
            cursor.execute('''
                SELECT mi.food_id as id, f.name, mi.amount_g, f.calories, f.protein_g, f.fat_g, f.carbs_g 
                FROM meal_items mi
                JOIN foods f ON mi.food_id = f.id
                WHERE mi.meal_id = ?
            ''', (meal['id'],))
            
            items = cursor.fetchall()
            
            # Add detailed items to the dictionary for the frontend
            meal['items'] = [dict(item) for item in items]
            
            # Calculate totals based on weight (database stores per 100g)
            total_cals = 0.0
            total_protein = 0.0
            total_fat = 0.0
            total_carbs = 0.0
            
            for item in items:
                ratio = item['amount_g'] / 100.0
                total_cals += item['calories'] * ratio
                total_protein += item['protein_g'] * ratio
                total_fat += item['fat_g'] * ratio
                total_carbs += item['carbs_g'] * ratio
                
            meal['total_calories'] = round(total_cals, 1)
            meal['total_protein_g'] = round(total_protein, 1)
            meal['total_fat_g'] = round(total_fat, 1)
            meal['total_carbs_g'] = round(total_carbs, 1)
            
        return meals
    except Exception as e:
        logger.error(f"Error fetching user meals: {e}")
        return []
    finally:
        if conn: conn.close()


def get_user_by_username(username: str) -> Optional[Dict[str, Any]]:
    """Retrieve user dictionary if they exist"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM users WHERE username = ?", (username,))
        row = cursor.fetchone()
        return dict(row) if row else None
    except Exception as e:
        logger.error(f"Database error fetching user: {e}")
        return None
    finally:
        if conn: conn.close()

def create_user(username: str, password_hash: str) -> Optional[int]:
    """Creates a user securely. Returns user_id if successful, None if username exists."""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO users (username, password_hash) VALUES (?, ?)",
            (username, password_hash)
        )
        user_id = cursor.lastrowid
        conn.commit()
        return user_id
    except sqlite3.IntegrityError:
        return None
    except Exception as e:
        logger.error(f"Database error during user creation: {e}")
        raise
    finally:
        if conn: conn.close()

def create_session(user_id: int) -> str:
    """Create a secure 32-character session token in the DB valid for 7 days"""
    token = secrets.token_urlsafe(32)
    expires_at = datetime.now() + timedelta(days=7)
    
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO sessions (token, user_id, expires_at) VALUES (?, ?, ?)",
            (token, user_id, expires_at)
        )
        conn.commit()
        return token
    except Exception as e:
        logger.error(f"Error creating session: {e}")
        raise
    finally:
        if conn: conn.close()

def get_user_from_token(token: str) -> Optional[Dict[str, Any]]:
    """Verify a session token and return the associated user data if valid and not expired"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        # Find user if token exists and hasn't expired
        cursor.execute('''
            SELECT users.* FROM users
            JOIN sessions ON users.id = sessions.user_id
            WHERE sessions.token = ? AND sessions.expires_at > ?
        ''', (token, datetime.now()))
        row = cursor.fetchone()
        return dict(row) if row else None
    except Exception as e:
        logger.error(f"Database error verifying token: {e}")
        return None
    finally:
        if conn: conn.close()

def delete_session(token: str):
    """Securely remove a session token when the user logs out"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("DELETE FROM sessions WHERE token = ?", (token,))
        conn.commit()
    except Exception as e:
        logger.error(f"Error deleting session: {e}")
    finally:
        if conn: conn.close()

def search_foods_by_name(query: str, limit: int = 15) -> list[Dict[str, Any]]:
    """Securely search for foods matching a string query with relevance-based ordering"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # SQL Injection safe query utilizing LIKE parameterization
        # We prioritize: 
        # 1. Items NOT in 'Baby Foods' 
        # 2. Shorter names (usually more fundamental ingredients)
        # 3. Alphabetical order as a tie-breaker
        q = f"%{query}%"
        prefix_match = f"{query}%"
        
        cursor.execute('''
            SELECT * FROM foods 
            WHERE name LIKE ? 
            ORDER BY 
                CASE WHEN category = 'Baby Foods' THEN 1 ELSE 0 END,
                CASE WHEN name LIKE ? THEN 0 ELSE 1 END,
                LENGTH(name) ASC,
                name ASC
            LIMIT ?
        ''', (q, prefix_match, limit))
        
        rows = cursor.fetchall()
        return [dict(row) for row in rows]
    except Exception as e:
        logger.error(f"Error searching foods: {e}")
        return []
    finally:
        if conn: conn.close()

def save_chat_message(user_id: int, role: str, content: str):
    """Persist a chat message to the database"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO chat_messages (user_id, role, content) VALUES (?, ?, ?)",
            (user_id, role, content)
        )
        conn.commit()
    except Exception as e:
        logger.error(f"Error saving chat message: {e}")
    finally:
        if conn: conn.close()

def get_user_chat_history(user_id: int, limit: int = 50) -> list[Dict[str, Any]]:
    """Retrieve the most recent chat messages for a user"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        # Order by created_at DESC to get recent ones, then reverse for display
        cursor.execute('''
            SELECT role, content FROM chat_messages 
            WHERE user_id = ? 
            ORDER BY created_at DESC 
            LIMIT ?
        ''', (user_id, limit))
        rows = cursor.fetchall()
        return [dict(row) for row in reversed(rows)]
    except Exception as e:
        logger.error(f"Error fetching chat history: {e}")
        return []
    finally:
        if conn: conn.close()

def delete_user_chat_history(user_id: int) -> bool:
    """Clear all chat messages for a user"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("DELETE FROM chat_messages WHERE user_id = ?", (user_id,))
        conn.commit()
        return True
    except Exception as e:
        logger.error(f"Error deleting chat history: {e}")
        return False
    finally:
        if conn: conn.close()
def get_user_profile(user_id: int) -> Optional[Dict[str, Any]]:
    """Fetch the user's profile containing macro targets. Inserts defaults if none exists."""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM user_profiles WHERE user_id = ?", (user_id,))
        row = cursor.fetchone()
        
        if not row:
            # Create a default profile row if one does not exist
            cursor.execute('''
                INSERT INTO user_profiles (user_id) VALUES (?)
            ''', (user_id,))
            conn.commit()
            cursor.execute("SELECT * FROM user_profiles WHERE user_id = ?", (user_id,))
            row = cursor.fetchone()
            
        return dict(row) if row else None
    except Exception as e:
        logger.error(f"Error fetching user profile: {e}")
        return None
    finally:
        if conn: conn.close()

def get_food_by_id(food_id: int) -> Optional[Dict[str, Any]]:
    """Retrieve a single food item by its unique ID"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM foods WHERE id = ?", (food_id,))
        row = cursor.fetchone()
        return dict(row) if row else None
    except Exception as e:
        logger.error(f"Error fetching food by ID {food_id}: {e}")
        return None
    finally:
        if conn: conn.close()

def get_foods_by_ids(food_ids: List[int]) -> List[Dict[str, Any]]:
    """Retrieve multiple food items by their unique IDs in bulk"""
    if not food_ids:
        return []
        
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # Create placeholders for the IN clause
        placeholders = ', '.join(['?'] * len(food_ids))
        query = f"SELECT * FROM foods WHERE id IN ({placeholders})"
        
        cursor.execute(query, food_ids)
        rows = cursor.fetchall()
        return [dict(row) for row in rows]
    except Exception as e:
        logger.error(f"Error fetching foods by IDs {food_ids}: {e}")
        return []
    finally:
        if conn: conn.close()