database.py

# database.py
"""
Database operations for Solo Leveling Life System

Handles all SQLite database operations including:
- Player stats and progression
- Tasks, trackers, and goals
- Journal entries and skills/wisdom
- Chat history and level configuration
"""

import sqlite3
import json
from datetime import datetime
from config import DB_FILE

def get_db():
    """Establishes database connection with dictionary-like row access."""
    conn = sqlite3.connect(DB_FILE)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    """
    Initialize database schema and populate with default data.
    Creates all necessary tables for the life gamification system.
    """
    conn = get_db()
    c = conn.cursor()

    # Player table - stores character stats and progression
    c.execute('''CREATE TABLE IF NOT EXISTS player (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        level INTEGER DEFAULT 1,
        exp INTEGER DEFAULT 0,
        expToNext INTEGER DEFAULT 100,
        availablePoints INTEGER DEFAULT 0,
        STRENGTH INTEGER DEFAULT 10,
        INTELLIGENCE INTEGER DEFAULT 10,
        CHARISMA INTEGER DEFAULT 10,
        ENDURANCE INTEGER DEFAULT 10,
        AGILITY INTEGER DEFAULT 10,
        WISDOM INTEGER DEFAULT 10,
        CONFIDENCE INTEGER DEFAULT 10,
        WILLPOWER INTEGER DEFAULT 10,
        HEALTH INTEGER DEFAULT 10,
        PHYSICALS INTEGER DEFAULT 10,
        PERCEPTION INTEGER DEFAULT 10,
        HEIGHT REAL DEFAULT 0,
        WEIGHT REAL DEFAULT 0
    )''')

    # Tasks table - daily and one-time tasks with rewards
    c.execute('''CREATE TABLE IF NOT EXISTS tasks (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        exp INTEGER NOT NULL,
        rewards TEXT NOT NULL,
        type TEXT DEFAULT 'daily',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )''')

    # Trackers table - habit tracking with streaks
    c.execute('''CREATE TABLE IF NOT EXISTS trackers (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        days INTEGER DEFAULT 0,
        exp INTEGER NOT NULL,
        stat TEXT NOT NULL,
        points INTEGER NOT NULL,
        lastUpdate TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )''')

    # Skills and wisdom entries
    c.execute('''CREATE TABLE IF NOT EXISTS skills_wisdom (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        description TEXT,
        type TEXT NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )''')

    # Gym personal records tracking
    c.execute('''CREATE TABLE IF NOT EXISTS gym_pr (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        workout_name TEXT NOT NULL,
        set_number INTEGER NOT NULL,
        rep_number INTEGER NOT NULL,
        extra_notes TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )''')

    # Goals and achievements system
    c.execute('''CREATE TABLE IF NOT EXISTS goals (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        description TEXT,
        target_value INTEGER,
        current_value INTEGER DEFAULT 0,
        stat_type TEXT,
        exp_reward INTEGER DEFAULT 0,
        completed BOOLEAN DEFAULT 0,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )''')

    # Personal journal entries
    c.execute('''CREATE TABLE IF NOT EXISTS journal_entries (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        content TEXT NOT NULL,
        collapsed BOOLEAN DEFAULT 0,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )''')

    # AI chatbot conversation history
    c.execute('''CREATE TABLE IF NOT EXISTS chat_history (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_message TEXT NOT NULL,
        bot_response TEXT NOT NULL,
        timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
    )''')

    # Level progression configuration
    c.execute('''CREATE TABLE IF NOT EXISTS levels_config (
        level INTEGER PRIMARY KEY,
        exp_needed INTEGER NOT NULL
    )''')

    # Initialize default data if tables are empty
    c.execute("SELECT COUNT(*) FROM player")
    if c.fetchone()[0] == 0:
        c.execute("INSERT INTO player DEFAULT VALUES")

    # Populate level progression data
    c.execute("SELECT COUNT(*) FROM levels_config")
    if c.fetchone()[0] == 0:
        levels_data = []
        exp_1 = 100
        exp_2 = 250
        levels_data.append((1, exp_1))
        levels_data.append((2, exp_2))

        # Generate exponential level progression
        for level_num in range(3, 21):
            exp_n = exp_2 + (exp_2 - exp_1 + 50)
            levels_data.append((level_num, exp_n))
            exp_1 = exp_2
            exp_2 = exp_n
        
        c.executemany("INSERT INTO levels_config (level, exp_needed) VALUES (?, ?)", levels_data)

    conn.commit()
    conn.close()

# Player Operations
def get_player_data():
    """Retrieve current player data with formatted stats."""
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM player ORDER BY id DESC LIMIT 1")
    row = c.fetchone()
    conn.close()
    
    if row:
        player = dict(row)
        # Format stats as dictionary for frontend compatibility
        player['stats'] = {
            'STRENGTH': max(10, player['STRENGTH']),
            'INTELLIGENCE': max(10, player['INTELLIGENCE']),
            'CHARISMA': max(10, player['CHARISMA']),
            'ENDURANCE': max(10, player['ENDURANCE']),
            'AGILITY': max(10, player['AGILITY']),
            'WISDOM': max(10, player['WISDOM']),
            'CONFIDENCE': max(10, player['CONFIDENCE']),
            'WILLPOWER': max(10, player['WILLPOWER']),
            'HEALTH': max(10, player['HEALTH']),
            'PHYSICALS': max(10, player['PHYSICALS']),
            'PERCEPTION': max(10, player['PERCEPTION'])
        }
        
        # Include physical measurements
        player['height'] = player.get('HEIGHT', 0)
        player['weight'] = player.get('WEIGHT', 0)

        # Clean up response by removing individual stat columns
        stat_columns = ['STRENGTH', 'INTELLIGENCE', 'CHARISMA', 'ENDURANCE', 'AGILITY', 
                       'WISDOM', 'CONFIDENCE', 'WILLPOWER', 'HEALTH', 'PHYSICALS', 
                       'PERCEPTION', 'HEIGHT', 'WEIGHT']
        for stat in stat_columns:
            if stat in player:
                del player[stat]
        
        return player
    return None

def update_player_data(data):
    """Update player stats, level, and experience."""
    conn = get_db()
    c = conn.cursor()

    # Extract update values
    updated_level = data.get("level")
    updated_exp = data.get("exp")
    updated_expToNext = data.get("expToNext")
    updated_available_points = data.get("availablePoints")
    
    stats = data.get("stats", {})
    new_height = data.get("height")
    new_weight = data.get("weight")

    # Get current stats as fallback for missing values
    c.execute("SELECT * FROM player WHERE id = 1")
    current_player_row = c.fetchone()
    current_stats = {
        'STRENGTH': current_player_row['STRENGTH'],
        'INTELLIGENCE': current_player_row['INTELLIGENCE'],
        'CHARISMA': current_player_row['CHARISMA'],
        'ENDURANCE': current_player_row['ENDURANCE'],
        'AGILITY': current_player_row['AGILITY'],
        'WISDOM': current_player_row['WISDOM'],
        'CONFIDENCE': current_player_row['CONFIDENCE'],
        'WILLPOWER': current_player_row['WILLPOWER'],
        'HEALTH': current_player_row['HEALTH'],
        'PHYSICALS': current_player_row['PHYSICALS'],
        'PERCEPTION': current_player_row['PERCEPTION']
    } if current_player_row else {stat: 10 for stat in ['STRENGTH', 'INTELLIGENCE', 'CHARISMA', 'ENDURANCE', 'AGILITY', 'WISDOM', 'CONFIDENCE', 'WILLPOWER', 'HEALTH', 'PHYSICALS', 'PERCEPTION']}

    # Update player record
    c.execute("""UPDATE player SET 
                 level = ?, exp = ?, expToNext = ?, availablePoints = ?,
                 STRENGTH = ?, INTELLIGENCE = ?, CHARISMA = ?, ENDURANCE = ?,
                 AGILITY = ?, WISDOM = ?, CONFIDENCE = ?, WILLPOWER = ?,
                 HEALTH = ?, PHYSICALS = ?, PERCEPTION = ?, HEIGHT = ?, WEIGHT = ?
                 WHERE id = 1""",
              (updated_level, updated_exp, updated_expToNext, updated_available_points,
               stats.get("STRENGTH", current_stats['STRENGTH']),
               stats.get("INTELLIGENCE", current_stats['INTELLIGENCE']),
               stats.get("CHARISMA", current_stats['CHARISMA']),
               stats.get("ENDURANCE", current_stats['ENDURANCE']),
               stats.get("AGILITY", current_stats['AGILITY']),
               stats.get("WISDOM", current_stats['WISDOM']),
               stats.get("CONFIDENCE", current_stats['CONFIDENCE']),
               stats.get("WILLPOWER", current_stats['WILLPOWER']),
               stats.get("HEALTH", current_stats['HEALTH']),
               stats.get("PHYSICALS", current_stats['PHYSICALS']),
               stats.get("PERCEPTION", current_stats['PERCEPTION']),
               new_height, new_weight))
    conn.commit()
    conn.close()
    
    return {
        "success": True, 
        "new_available_points": updated_available_points,
        "level_up": False,
        "new_level": updated_level,
        "new_exp_to_next": updated_expToNext
    }

# Task Operations
def get_all_tasks():
    """Retrieve all tasks with parsed reward data."""
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM tasks ORDER BY created_at DESC")
    tasks = [dict(row) for row in c.fetchall()]
    conn.close()
    
    for task in tasks:
        task['rewards'] = json.loads(task['rewards'])
        if 'type' not in task or task['type'] is None:
            task['type'] = 'daily'
    return tasks

def add_new_task(name, exp, rewards, task_type, exp_only_task):
    """Add a new task to the database."""
    conn = get_db()
    c = conn.cursor()
    
    rewards_to_save = [] if exp_only_task else rewards
    
    c.execute("INSERT INTO tasks (name, exp, rewards, type) VALUES (?, ?, ?, ?)",
              (name, exp, json.dumps(rewards_to_save), task_type))
    conn.commit()
    task_id = c.lastrowid
    conn.close()
    return {"id": task_id, "success": True}

def delete_task_by_id(task_id):
    """Delete a task by its ID."""
    conn = get_db()
    c = conn.cursor()
    c.execute("DELETE FROM tasks WHERE id = ?", (task_id,))
    conn.commit()
    conn.close()
    return {"success": True}

# Tracker Operations
def get_all_trackers():
    """Retrieve all habit trackers."""
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM trackers ORDER BY created_at DESC")
    trackers = [dict(row) for row in c.fetchall()]
    conn.close()
    return trackers

def add_new_tracker(name, exp, stat, points):
    """Add a new habit tracker."""
    today = datetime.today().strftime('%Y-%m-%d')
    conn = get_db()
    c = conn.cursor()
    c.execute("INSERT INTO trackers (name, days, exp, stat, points, lastUpdate) VALUES (?, ?, ?, ?, ?, ?)",
              (name, 0, exp, stat, points, today))
    conn.commit()
    tracker_id = c.lastrowid
    conn.close()
    return {"id": tracker_id, "success": True}

def update_tracker_data(tracker_id, days, last_update):
    """Update tracker's day count and last update timestamp."""
    conn = get_db()
    c = conn.cursor()
    c.execute("UPDATE trackers SET days = ?, lastUpdate = ? WHERE id = ?",
              (days, last_update, tracker_id))
    conn.commit()
    conn.close()
    return {"success": True}

# Gym PR Operations
def get_all_gym_prs():
    """Retrieve all gym personal records."""
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM gym_pr ORDER BY created_at DESC")
    prs = [dict(row) for row in c.fetchall()]
    conn.close()
    return prs

def add_new_gym_pr(workout_name, set_number, rep_number, extra_notes):
    """Add a new gym personal record."""
    conn = get_db()
    c = conn.cursor()
    c.execute("INSERT INTO gym_pr (workout_name, set_number, rep_number, extra_notes) VALUES (?, ?, ?, ?)",
              (workout_name, set_number, rep_number, extra_notes))
    conn.commit()
    pr_id = c.lastrowid
    conn.close()
    return {"id": pr_id, "success": True}

def delete_gym_pr_by_id(pr_id):
    """Delete a gym personal record by ID."""
    conn = get_db()
    c = conn.cursor()
    c.execute("DELETE FROM gym_pr WHERE id = ?", (pr_id,))
    conn.commit()
    conn.close()
    return {"success": True}

# Goal Operations
def get_all_goals():
    """Retrieve all goals and achievements."""
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM goals ORDER BY created_at DESC")
    goals = [dict(row) for row in c.fetchall()]
    conn.close()
    return goals

def get_level_config_data(level_num):
    """Get experience requirement for a specific level."""
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT exp_needed FROM levels_config WHERE level = ?", (level_num,))
    result = c.fetchone()
    conn.close()
    if result:
        return {"level": level_num, "exp_needed": result['exp_needed']}
    return None

def add_new_goal(title, description, target_value, stat_type, exp_reward):
    """Add a new goal to track progress."""
    conn = get_db()
    c = conn.cursor()
    c.execute("INSERT INTO goals (title, description, target_value, current_value, stat_type, exp_reward) VALUES (?, ?, ?, ?, ?, ?)",
              (title, description, target_value, 0, stat_type, exp_reward))
    conn.commit()
    goal_id = c.lastrowid
    conn.close()
    return {"id": goal_id, "success": True}

def update_goal_data(goal_id, current_value, completed):
    """Update goal progress and completion status."""
    conn = get_db()
    c = conn.cursor()
    c.execute("UPDATE goals SET current_value = ?, completed = ? WHERE id = ?",
              (current_value, int(completed), goal_id))
    conn.commit()
    conn.close()
    return {"success": True}

def delete_goal_by_id(goal_id):
    """Delete a goal by its ID."""
    conn = get_db()
    c = conn.cursor()
    c.execute("DELETE FROM goals WHERE id = ?", (goal_id,))
    conn.commit()
    conn.close()
    return {"success": True}

# Journal Operations
def get_all_journal_entries():
    """Retrieve all journal entries."""
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM journal_entries ORDER BY updated_at DESC")
    entries = [dict(row) for row in c.fetchall()]
    conn.close()
    return entries

def add_new_journal_entry(title, content):
    """Add a new journal entry."""
    conn = get_db()
    c = conn.cursor()
    c.execute("INSERT INTO journal_entries (title, content) VALUES (?, ?)",
              (title, content))
    conn.commit()
    entry_id = c.lastrowid
    conn.close()
    return {"id": entry_id, "success": True}

def update_journal_entry_data(entry_id, title, content, collapsed):
    """Update journal entry content and visibility state."""
    conn = get_db()
    c = conn.cursor()
    c.execute("UPDATE journal_entries SET title = ?, content = ?, collapsed = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?",
              (title, content, int(collapsed), entry_id))
    conn.commit()
    conn.close()
    return {"success": True}

def delete_journal_entry_by_id(entry_id):
    """Delete a journal entry by its ID."""
    conn = get_db()
    c = conn.cursor()
    c.execute("DELETE FROM journal_entries WHERE id = ?", (entry_id,))
    conn.commit()
    conn.close()
    return {"success": True}

# Skills & Wisdom Operations
def get_all_skills_wisdom():
    """Retrieve all skills and wisdom entries."""
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM skills_wisdom ORDER BY created_at DESC")
    entries = [dict(row) for row in c.fetchall()]
    conn.close()
    return entries

def add_new_skill_wisdom(name, description, entry_type):
    """Add a new skill or wisdom entry."""
    conn = get_db()
    c = conn.cursor()
    c.execute("INSERT INTO skills_wisdom (name, description, type) VALUES (?, ?, ?)",
              (name, description, entry_type))
    conn.commit()
    entry_id = c.lastrowid
    conn.close()
    return {"id": entry_id, "success": True}

def delete_skill_wisdom_by_id(entry_id):
    """Delete a skill or wisdom entry by its ID."""
    conn = get_db()
    c = conn.cursor()
    c.execute("DELETE FROM skills_wisdom WHERE id = ?", (entry_id,))
    conn.commit()
    conn.close()
    return {"success": True}

# Chat History Operations
def store_chat_message(user_message, bot_response):
    """Store AI chatbot conversation in database."""
    conn = get_db()
    c = conn.cursor()
    c.execute("INSERT INTO chat_history (user_message, bot_response) VALUES (?, ?)",
              (user_message, bot_response))
    conn.commit()
    conn.close()
    return {"success": True}

def get_recent_chat_history(limit=20):
    """Retrieve recent chat conversation history."""
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM chat_history ORDER BY timestamp DESC LIMIT ?", (limit,))
    messages = [dict(row) for row in c.fetchall()]
    conn.close()
    return messages