app.py

"""Tracker de tiempos de estudio — pentesting roadmap.

Stack: Flask + SQLite + HTMX (sin build step, sin npm).

Uso:
    python3 app.py            # arranca en http://localhost:5050
    python3 seed.py           # carga el roadmap (solo primera vez)

Comandos útiles desde shell:
    sqlite3 db.sqlite3 'SELECT * FROM sessions ORDER BY started_at DESC LIMIT 10;'
"""
import sqlite3
from datetime import datetime, timezone
from zoneinfo import ZoneInfo
from flask import Flask, g, render_template, request, redirect, url_for
import os

DB_PATH = os.path.join(os.path.dirname(__file__), "db.sqlite3")
TZ_ASU = ZoneInfo("America/Asuncion")

app = Flask(__name__)


@app.template_filter("asu")
def to_asu(iso_utc):
    """Convierte un ISO UTC a string legible en hora de Asunción."""
    if not iso_utc:
        return ""
    dt = datetime.fromisoformat(iso_utc)
    if dt.tzinfo is None:
        dt = dt.replace(tzinfo=timezone.utc)
    return dt.astimezone(TZ_ASU).strftime("%Y-%m-%d %H:%M:%S")


@app.template_filter("asu_short")
def to_asu_short(iso_utc):
    """Versión corta: solo HH:MM:SS de Asunción."""
    if not iso_utc:
        return ""
    dt = datetime.fromisoformat(iso_utc)
    if dt.tzinfo is None:
        dt = dt.replace(tzinfo=timezone.utc)
    return dt.astimezone(TZ_ASU).strftime("%H:%M:%S")


@app.template_filter("hms")
def to_hms(hours_float):
    """Convierte horas decimales a 'HH:MM:SS'."""
    if hours_float is None:
        return "—"
    total_seconds = int(round(hours_float * 3600))
    h = total_seconds // 3600
    m = (total_seconds % 3600) // 60
    s = total_seconds % 60
    return f"{h:02d}:{m:02d}:{s:02d}"


def db():
    if "db" not in g:
        g.db = sqlite3.connect(DB_PATH)
        g.db.row_factory = sqlite3.Row
    return g.db


@app.teardown_appcontext
def close_db(error):
    conn = g.pop("db", None)
    if conn:
        conn.close()


def now_iso():
    return datetime.now(timezone.utc).isoformat(timespec="seconds")


def hours_between(start_iso, end_iso):
    """Devuelve horas como float entre dos ISO timestamps."""
    s = datetime.fromisoformat(start_iso)
    e = datetime.fromisoformat(end_iso)
    return (e - s).total_seconds() / 3600.0


def get_active_session():
    row = db().execute(
        """SELECT s.id, s.started_at, s.item_id, i.name AS item_name, p.name AS phase_name
           FROM sessions s
           JOIN items i ON s.item_id = i.id
           JOIN phases p ON i.phase_id = p.id
           WHERE s.ended_at IS NULL
           ORDER BY s.started_at DESC
           LIMIT 1"""
    ).fetchone()
    if not row:
        return None
    elapsed = hours_between(row["started_at"], now_iso())
    return {
        "id": row["id"],
        "item_id": row["item_id"],
        "item_name": row["item_name"],
        "phase_name": row["phase_name"],
        "started_at": row["started_at"],
        "elapsed_hours": elapsed,
    }


def get_all_modules():
    """Devuelve dict item_id → lista de módulos ordenados."""
    rows = db().execute(
        """SELECT id, item_id, section, name, duration_secs, order_idx, completed, completed_at
           FROM modules ORDER BY item_id, order_idx"""
    ).fetchall()
    result = {}
    for r in rows:
        iid = r["item_id"]
        if iid not in result:
            result[iid] = []
        result[iid].append({
            "id": r["id"],
            "section": r["section"],
            "name": r["name"],
            "duration_secs": r["duration_secs"],
            "order_idx": r["order_idx"],
            "completed": bool(r["completed"]),
            "completed_at": r["completed_at"],
        })
    return result


def get_dashboard_data():
    """Devuelve fases con items y métricas calculadas."""
    modules_by_item = get_all_modules()
    rows = db().execute(
        """SELECT
              p.id AS phase_id, p.name AS phase_name, p.order_idx AS phase_order,
              i.id AS item_id, i.name AS item_name, i.estimated_hours,
              i.category, i.url, i.completed,
              COALESCE(SUM(
                CASE WHEN s.ended_at IS NOT NULL
                     THEN (julianday(s.ended_at) - julianday(s.started_at)) * 24
                     ELSE 0
                END
              ), 0) AS hours_invested,
              COUNT(s.id) AS sessions_count,
              MAX(CASE WHEN s.ended_at IS NULL THEN 1 ELSE 0 END) AS has_active
           FROM phases p
           JOIN items i ON i.phase_id = p.id
           LEFT JOIN sessions s ON s.item_id = i.id
           GROUP BY p.id, i.id
           ORDER BY p.order_idx, i.order_idx"""
    ).fetchall()

    phases = {}
    for r in rows:
        pid = r["phase_id"]
        if pid not in phases:
            phases[pid] = {
                "id": pid,
                "name": r["phase_name"],
                "order": r["phase_order"],
                "items": [],
                "total_estimated": 0.0,
                "total_invested": 0.0,
            }

        invested = float(r["hours_invested"])
        estimated = float(r["estimated_hours"])
        completed = bool(r["completed"])

        # Status según ratio invertido/estimado
        has_active = bool(r["has_active"])
        if estimated == 0:
            status = "milestone"
            ratio = 0.0
        else:
            ratio = invested / estimated
            if completed and ratio < 0.5:
                status = "under"
            elif ratio > 1.25 and not completed:
                status = "over"
            elif completed:
                status = "done"
            elif has_active or ratio >= 0.05:
                status = "in_progress"
            else:
                status = "pending"

        item_mods = modules_by_item.get(r["item_id"], [])
        done_mods = sum(1 for m in item_mods if m["completed"])
        # Primer módulo incompleto = posición actual
        current_module = next((m for m in item_mods if not m["completed"]), None)

        phases[pid]["items"].append({
            "id": r["item_id"],
            "name": r["item_name"],
            "category": r["category"],
            "url": r["url"],
            "estimated_hours": estimated,
            "hours_invested": invested,
            "sessions_count": r["sessions_count"],
            "completed": completed,
            "status": status,
            "ratio": ratio,
            "modules": item_mods,
            "modules_done": done_mods,
            "modules_total": len(item_mods),
            "current_module": current_module,
        })
        phases[pid]["total_estimated"] += estimated
        phases[pid]["total_invested"] += invested

    return sorted(phases.values(), key=lambda p: p["order"])


@app.route("/")
def index():
    now_asu = datetime.now(TZ_ASU).strftime("%Y-%m-%d %H:%M:%S")
    return render_template(
        "index.html",
        phases=get_dashboard_data(),
        active=get_active_session(),
        now=f"{now_asu} (Asunción)",
    )


@app.route("/start/<int:item_id>", methods=["POST"])
def start(item_id):
    # Cerrar sesión activa si existe (no permitimos solapamiento)
    active = get_active_session()
    if active:
        db().execute(
            "UPDATE sessions SET ended_at = ? WHERE id = ?",
            (now_iso(), active["id"]),
        )
    db().execute(
        "INSERT INTO sessions (item_id, started_at) VALUES (?, ?)",
        (item_id, now_iso()),
    )
    db().commit()
    return redirect(url_for("index"))


@app.route("/stop", methods=["POST"])
def stop():
    notes = request.form.get("notes", "").strip() or None
    active = get_active_session()
    if active:
        db().execute(
            "UPDATE sessions SET ended_at = ?, notes = ? WHERE id = ?",
            (now_iso(), notes, active["id"]),
        )
        db().commit()
    return redirect(url_for("index"))


@app.route("/toggle-complete/<int:item_id>", methods=["POST"])
def toggle_complete(item_id):
    row = db().execute("SELECT completed FROM items WHERE id = ?", (item_id,)).fetchone()
    new_val = 0 if row["completed"] else 1
    db().execute("UPDATE items SET completed = ? WHERE id = ?", (new_val, item_id))
    db().commit()
    return redirect(url_for("index"))


@app.route("/toggle-module/<int:module_id>", methods=["POST"])
def toggle_module(module_id):
    row = db().execute(
        "SELECT item_id, order_idx, completed FROM modules WHERE id = ?", (module_id,)
    ).fetchone()
    item_id = row["item_id"]
    order_idx = row["order_idx"]
    currently_done = bool(row["completed"])

    if not currently_done:
        # Marcar este y todos los anteriores (order_idx <=)
        ts = now_iso()
        db().execute(
            """UPDATE modules SET completed = 1, completed_at = ?
               WHERE item_id = ? AND order_idx <= ? AND completed = 0""",
            (ts, item_id, order_idx),
        )
    else:
        # Desmarcar este y todos los siguientes (order_idx >=)
        db().execute(
            """UPDATE modules SET completed = 0, completed_at = NULL
               WHERE item_id = ? AND order_idx >= ?""",
            (item_id, order_idx),
        )
    db().commit()
    return redirect(url_for("index"))


@app.route("/sessions")
def sessions():
    rows = db().execute(
        """SELECT s.id, s.started_at, s.ended_at, s.notes,
                  i.name AS item_name, p.name AS phase_name,
                  CASE WHEN s.ended_at IS NOT NULL
                       THEN (julianday(s.ended_at) - julianday(s.started_at)) * 24
                       ELSE NULL
                  END AS hours
           FROM sessions s
           JOIN items i ON s.item_id = i.id
           JOIN phases p ON i.phase_id = p.id
           ORDER BY s.started_at DESC
           LIMIT 200"""
    ).fetchall()
    return render_template("sessions.html", sessions=rows)


def migrate():
    """Migraciones no destructivas para DBs existentes."""
    conn = sqlite3.connect(DB_PATH)
    try:
        conn.execute("ALTER TABLE modules ADD COLUMN completed_at TEXT")
        conn.commit()
    except sqlite3.OperationalError:
        pass  # columna ya existe
    conn.close()


if __name__ == "__main__":
    if not os.path.exists(DB_PATH):
        from seed import init
        init()
    migrate()
    debug = os.environ.get("TRACKER_DEBUG") == "1"
    app.run(host="127.0.0.1", port=5050, debug=debug, use_reloader=debug)