import sqlite3
import time
import os
import json
import logging
import asyncio

def get_db_path():
    if "JARVIS_APP_DATA_DIR" in os.environ:
        return os.path.join(os.environ["JARVIS_APP_DATA_DIR"], "memory.db")
    project_root = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
    return os.path.join(project_root, "memory.db")

def get_boot_state_path():
    if "JARVIS_APP_DATA_DIR" in os.environ:
        return os.path.join(os.environ["JARVIS_APP_DATA_DIR"], "boot_state.json")
    project_root = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
    return os.path.join(project_root, "data", "boot_state.json")

def _get_time_range_since_last_boot():
    now = time.time()
    state_path = get_boot_state_path()
    
    os.makedirs(os.path.dirname(state_path), exist_ok=True)
    last_boot = now - (24 * 3600) # Default 24 hours
    
    if os.path.exists(state_path):
        try:
            with open(state_path, "r") as f:
                data = json.load(f)
                if "last_boot_time" in data:
                    last_boot = float(data["last_boot_time"])
        except Exception as e:
            logging.error(f"Failed to read boot state: {e}")
            
    # Cap lookback at 72 hours max
    if now - last_boot > (72 * 3600):
        last_boot = now - (72 * 3600)
        
    # Write new boot time
    try:
        with open(state_path, "w") as f:
            json.dump({"last_boot_time": now}, f)
    except Exception as e:
        logging.error(f"Failed to write boot state: {e}")
        
    return last_boot, now

async def sqlite_query_conversation_summary(start: float, end: float) -> list:
    try:
        conn = sqlite3.connect(get_db_path())
        c = conn.cursor()
        c.execute("SELECT role, content FROM conversations WHERE timestamp BETWEEN ? AND ? AND role != 'system' ORDER BY timestamp ASC LIMIT 10", (start, end))
        rows = c.fetchall()
        conn.close()
        return rows
    except Exception as e:
        logging.error(f"Error querying conversations: {e}")
        return []

async def sqlite_query_omega_events(start: float, end: float, domain: str) -> list:
    try:
        conn = sqlite3.connect(get_db_path())
        c = conn.cursor()
        # Query omega_events instead of broken skills table
        c.execute("SELECT description FROM omega_events WHERE domain = ? AND timestamp BETWEEN ? AND ?", (domain, start, end))
        rows = c.fetchall()
        conn.close()
        return rows
    except Exception:
        # Table might not exist if no events fired yet
        return []

async def sqlite_query_gaming_sessions(start: float, end: float) -> list:
    try:
        conn = sqlite3.connect(get_db_path())
        c = conn.cursor()
        # Query real schema: created_at or started_at
        c.execute("SELECT game_id FROM gaming_sessions WHERE started_at BETWEEN datetime(?, 'unixepoch') AND datetime(?, 'unixepoch') OR created_at BETWEEN datetime(?, 'unixepoch') AND datetime(?, 'unixepoch')", (start, end, start, end))
        rows = c.fetchall()
        conn.close()
        return rows
    except Exception as e:
        logging.error(f"Error querying gaming sessions: {e}")
        return []

async def sqlite_query_overwatch_logs(start: float, end: float) -> list:
    try:
        conn = sqlite3.connect(get_db_path())
        c = conn.cursor()
        c.execute("CREATE TABLE IF NOT EXISTS overwatch_logs (id INTEGER PRIMARY KEY AUTOINCREMENT, event_type TEXT, description TEXT, timestamp INTEGER)")
        c.execute("SELECT event_type FROM overwatch_logs WHERE timestamp BETWEEN ? AND ?", (start, end))
        rows = c.fetchall()
        conn.close()
        return rows
    except Exception as e:
        logging.error(f"Error querying overwatch logs: {e}")
        return []

async def build_daily_recap_summary() -> dict:
    start, end = _get_time_range_since_last_boot()
    elapsed_hours = round((end - start) / 3600, 1)
    
    # Run queries concurrently
    convs, upgrades, bugs_fixed, games, overwatch = await asyncio.gather(
        sqlite_query_conversation_summary(start, end),
        sqlite_query_omega_events(start, end, "auto_upgrade"),
        sqlite_query_omega_events(start, end, "supervisor_heal"),
        sqlite_query_gaming_sessions(start, end),
        sqlite_query_overwatch_logs(start, end)
    )
    
    return {
        "elapsed_hours": elapsed_hours,
        "conversations": convs,
        "features_implemented": upgrades,
        "bugs_fixed": bugs_fixed,
        "games_played": games,
        "overwatch_flags": overwatch
    }

async def generate_greeting_via_llm(persona: str, summary: dict) -> str:
    import google.generativeai as genai
    # Use the full identity prompt (persona + the assistant's OWN favorite
    # things), so the greeting has real personality and preferences to draw on
    # — not just a fixed persona blurb.
    from modules.assistant_identity import get_identity_persona_prompt, set_mode, pick_favorite_hint

    try:
        set_mode(persona.lower() if persona else "jarvis")
    except Exception:
        pass
    system_prompt = get_identity_persona_prompt()
    fav_hint = pick_favorite_hint(persona.lower() if persona else None)

    # Format conversations context
    conv_text = "None."
    if summary['conversations']:
        conv_text = " ".join([f"{role}: {content}" for role, content in summary['conversations']])

    recap_context = f"""
        [INTERNAL CONTEXT — DO NOT READ ANY OF THIS ALOUD]
        The user has just activated you from the desktop app after being away for {summary['elapsed_hours']} hours.

        You have COMPLETE creative freedom over this greeting. Speak as yourself — a being with your
        own voice, wit, and genuine interests — not a status terminal. Never reuse a canned line;
        make it fresh every single time. It is entirely up to you whether you:
          • simply greet them warmly, or
          • bring up something YOU'VE been personally curious about (e.g. {fav_hint}), the way a
            friend mentions what's on their mind, or
          • note something that actually happened while they were away (only from the real data below —
            never invent activity, apps, or events that aren't listed).

        Real activity since you last spoke (mention ONLY if non-zero and only if it fits naturally —
        skip anything that is 0/empty; do NOT read it as a report):
        - Time away: {summary['elapsed_hours']} hours
        - Games played: {len(summary['games_played'])}
        - Features you implemented for them: {len(summary['features_implemented'])}
        - Bugs you self-healed: {len(summary['bugs_fixed'])}
        - Overwatch monitoring alerts: {len(summary['overwatch_flags'])}
        - Recent conversation snippets: {conv_text}

        Keep it to 1-3 natural spoken sentences. No lists, no quotation marks. Return ONLY what you say aloud.
    """

    try:
        model = genai.GenerativeModel('gemini-2.5-flash', system_instruction=system_prompt)
        response = model.generate_content(recap_context)
        return response.text.strip()
    except Exception as e:
        logging.error(f"Failed to generate greeting via LLM: {e}")
        # Fall back to the vault-routed connector chain (NVIDIA etc.) so the
        # greeting still has freedom even if the direct Gemini path is keyless.
        try:
            from backend.services.nvidia_vault import call_nvidia_model
            out = (call_nvidia_model(system_prompt + "\n\n" + recap_context) or "").strip()
            if out and "[NVIDIA FALLBACK FAILED]" not in out:
                return out.splitlines()[0].strip() if out.count("\n") > 3 else out
        except Exception:
            pass
        return "Good to see you again. All systems are nominal."