backend/memory/database.py

from fastapi import APIRouter
from pydantic import BaseModel

router = APIRouter()

class DbQueryRequest(BaseModel):
    query: str

class DbResultResponse(BaseModel):
    success: bool
    message: str

@router.post("/api/database/execute", response_model=DbResultResponse)
async def execute_query(req: DbQueryRequest):
    return DbResultResponse(
        success=True,
        message=f"Executed: {req.query}"
    )

import sqlite3
import asyncio
import os
import logging
import uuid

logger = logging.getLogger(__name__)

from backend.services.usb_monitor import get_db_path
DB_PATH = get_db_path()
os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)

def init_db():
    try:
        with sqlite3.connect(DB_PATH) as conn:
            conn.execute("PRAGMA journal_mode=WAL;")
            conn.execute('''
                CREATE TABLE IF NOT EXISTS model_generations (
                    id TEXT PRIMARY KEY,
                    description TEXT,
                    image_source_tier TEXT,
                    model_path TEXT,
                    persona TEXT,
                    engine TEXT DEFAULT 'stable_fast_3d_local',
                    inference_target TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                );
            ''')
            conn.execute("CREATE INDEX IF NOT EXISTS idx_model_gen_persona ON model_generations(persona);")
            conn.execute('''
                CREATE TABLE IF NOT EXISTS omega_events (
                    id TEXT PRIMARY KEY,
                    event_type TEXT NOT NULL,
                    timestamp TEXT NOT NULL,
                    source TEXT NOT NULL,
                    priority TEXT NOT NULL,
                    payload_json TEXT NOT NULL
                );
            ''')
            conn.execute("CREATE INDEX IF NOT EXISTS idx_omega_events_type ON omega_events(event_type);")
            conn.commit()
    except Exception as e:
        logger.error(f"Failed to initialize SQLite for model_generations: {e}")

init_db()

async def lookup_model_description(description: str) -> dict | None:
    def _lookup():
        with sqlite3.connect(DB_PATH) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            cursor.execute('''
                SELECT model_path, engine, inference_target 
                FROM model_generations 
                WHERE description = ?
                ORDER BY created_at DESC LIMIT 1
            ''', (description,))
            row = cursor.fetchone()
            if row:
                return dict(row)
            return None
    try:
        return await asyncio.to_thread(_lookup)
    except Exception as e:
        logger.error(f"SQLite lookup error: {e}")
        return None

async def insert_model_generation(description: str, image_tier: str, model_path: str, persona: str, inference_target: str):
    def _insert():
        with sqlite3.connect(DB_PATH) as conn:
            conn.execute('''
                INSERT INTO model_generations (id, description, image_source_tier, model_path, persona, inference_target)
                VALUES (?, ?, ?, ?, ?, ?)
            ''', (str(uuid.uuid4()), description, image_tier, model_path, persona, inference_target))
            conn.commit()
    try:
        await asyncio.to_thread(_insert)
    except Exception as e:
        logger.error(f"SQLite insert error: {e}")