# core/storage.py
import sqlite3
from pathlib import Path
import json
from datetime import datetime

DATA_DIR = Path("data")
DATA_DIR.mkdir(exist_ok=True)
DB_PATH = DATA_DIR / "missions.db"

def init_db():
    conn = sqlite3.connect(DB_PATH)
    conn.executescript("""
    CREATE TABLE IF NOT EXISTS missions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        task TEXT NOT NULL,
        started_at TEXT,
        ended_at TEXT,
        plan TEXT,          -- ← nueva columna para el plan del manager
        results TEXT,           -- JSON string
        doc_file TEXT,
        events TEXT             -- JSON string
    );

    CREATE TABLE IF NOT EXISTS chat_sessions (
        session_id TEXT PRIMARY KEY,
        history TEXT,           -- JSON
        last_updated TEXT
    );
    """)
    conn.commit()
    conn.close()

def save_mission(mission: dict):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute("""
        INSERT INTO missions (task, started_at, ended_at, plan, results, doc_file, events)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    """, (
        mission["task"],
        mission.get("started_at"),
        mission.get("ended_at", datetime.now().isoformat()),
        json.dumps(mission.get("plan", {})),
        json.dumps(mission.get("results", {})),
        mission.get("doc_file"),
        json.dumps(mission.get("events", []))
    ))
    conn.commit()
    mission["id"] = c.lastrowid
    conn.close()
    return mission["id"]

def load_recent_missions(limit=50):
    conn = sqlite3.connect(DB_PATH)
    rows = conn.execute(
        "SELECT * FROM missions ORDER BY id DESC LIMIT ?",
        (limit,)
    ).fetchall()
    conn.close()
    return [
        {
            "id": r[0], "task": r[1], "started_at": r[2],
            "ended_at": r[3], "results": json.loads(r[4]),
            "doc_file": r[5], "events": json.loads(r[6])
        } for r in rows
    ]

# Inicializar al importar
init_db()