import json
from pathlib import Path
from html import escape
from grafo_d_sqlite_adapter import load_database
import sqlite3
INPUT = "DATABASE/GRAFO_D_DATABASE.sqlite"
OUTPUT = "outputs/timeline_excel_like.html"
CELL_W = 120
CELL_H = 46
LEFT_PAD = 260
TOP_PAD = 38
BAR_H = 30
SELECTED_LANE_H = 56
COLORS = [
"#1d7afc", "#43a047", "#f4511e", "#8e24aa", "#e53935",
"#00a6a6", "#5145cd", "#d29600", "#d81b8a", "#795548"
]
raw_db = load_database()
if isinstance(raw_db, dict):
events = raw_db.get("events", [])
elif isinstance(raw_db, list):
events = raw_db
else:
events = []
def clean(v):
if v is None:
return ""
return str(v).strip()
def is_year_value(v):
try:
x = int(float(clean(v).replace("~", "")))
return -46000 <= x <= 9999
except Exception:
return False
def load_sqlite_graph_labels_for_empty_rows():
"""
Load graph rows directly from SQLite graphs.
A graph with zero events must still be visible as a graph/container.
It must not fabricate a timeline component.
"""
db_path = Path("DATABASE/GRAFO_D_DATABASE.sqlite")
labels = {}
if not db_path.exists():
return labels
con = sqlite3.connect(str(db_path))
con.row_factory = sqlite3.Row
try:
rows = con.execute(
"SELECT row_key, visible_name FROM graphs ORDER BY id"
).fetchall()
for row in rows:
row_key = str(row["row_key"] or "").strip()
visible_name = str(row["visible_name"] or row_key).strip()
if row_key:
labels[row_key] = visible_name or row_key
finally:
con.close()
return labels
rows = sorted(set(e["row"] for e in events))
score = {}
for r in rows:
vals = [e["name"] for e in events if e["row"] == r]
numeric_count = sum(1 for v in vals if is_year_value(v))
has_2025 = any(clean(v).replace("~", "") in ["2025", "2025.0"] for v in vals)
score[r] = numeric_count + (1000 if has_2025 else 0)
axis_row = max(score, key=score.get)
axis_events = [
e for e in events
if e["row"] == axis_row and is_year_value(e["name"])
]
# Ensure dynamic timeline axis covers all event columns.
# API/live events may extend beyond the last historical year marker.
max_event_col = max(e.get("end_col", 0) for e in events) if events else 0
max_axis_col = max((e.get("end_col", 0) for e in axis_events), default=0)
if max_event_col > max_axis_col:
live_years = sorted({
str(e.get("date", ""))[:4]
for e in events
if e.get("end_col", 0) > max_axis_col and str(e.get("date", ""))[:4].isdigit()
})
fallback_year = "2026"
for year in live_years or [fallback_year]:
axis_events.append({
"row": axis_row,
"name": year,
"start_col": max_axis_col + 1,
"end_col": max_event_col
})
break
graph_events = [
e for e in events
if e["row"] != axis_row
]
row_labels = {}
for e in graph_events:
row_labels.setdefault(e["row"], e["name"])
graph_labels = {}
if isinstance(raw_db, dict):
graph_labels = raw_db.get("ui_state", {}).get("graph_labels", {}) or {}
for row_key, visible_name in graph_labels.items():
if row_key in row_labels and visible_name:
row_labels[row_key] = visible_name
sqlite_graph_labels = load_sqlite_graph_labels_for_empty_rows()
for row_key, visible_name in sqlite_graph_labels.items():
row_labels[row_key] = visible_name or row_key
graph_rows = sorted(
set(e["row"] for e in graph_events) | set(sqlite_graph_labels.keys()),
key=lambda r: clean(row_labels.get(r, r)).lower()
)
max_col = max((e["end_col"] for e in events), default=1)
base_w = LEFT_PAD + max_col * CELL_W + 800
base_h = TOP_PAD + len(graph_rows) * SELECTED_LANE_H + 800
sidebar_items = []
for lane, row in enumerate(graph_rows):
color = COLORS[lane % len(COLORS)]
label = escape(clean(row_labels[row]))
sidebar_items.append(f"""
""")
event_data_js = []
for e in graph_events:
lane = graph_rows.index(e["row"])
color = COLORS[lane % len(COLORS)]
brief_value = (
e.get("brief_description")
or e.get("brief")
or e.get("BRIEF")
or e.get("description")
or ""
)
api_value = (
e.get("api_text")
or e.get("api")
or ""
)
event_data_js.append({
"id": clean(e.get("id")),
"db_id": e.get("db_id", ""),
"row": str(e["row"]),
"row_key": str(e.get("row_key") or e["row"]),
"name": clean(e["name"]),
"event_name": clean(e.get("event_name") or e["name"]),
"start": e["start_col"],
"end": e["end_col"],
"start_col": e["start_col"],
"end_col": e["end_col"],
"color": color,
# V55: campos canónicos visibles en frontend.
"date": clean(e.get("date") or e.get("date_text")),
"date_text": clean(e.get("date_text") or e.get("date")),
"year": e.get("year") or e.get("date_year") or "",
"month": e.get("month") or e.get("date_month") or "",
"day": e.get("day") or e.get("date_day") or "",
"place": clean(e.get("place") or e.get("PLACE")),
"PLACE": clean(e.get("place") or e.get("PLACE")),
"who": clean(e.get("who") or e.get("WHO")),
"WHO": clean(e.get("who") or e.get("WHO")),
"description": clean(brief_value),
"brief": clean(brief_value),
"BRIEF": clean(brief_value),
"brief_description": clean(brief_value),
"source_link": clean(e.get("source_link")),
"source": clean(e.get("source_link") or e.get("source")),
"api": clean(api_value),
"api_text": clean(api_value),
})
axis_data_js = [
{
"name": clean(e["name"]).replace(".0", ""),
"start": e["start_col"],
"end": e["end_col"],
"x": LEFT_PAD + ((e["start_col"] + e["end_col"]) / 2 - 1) * CELL_W
}
for e in axis_events
]
row_label_js = {
str(r): clean(row_labels[r])
for r in graph_rows
}
html = f"""
GRAFO D - Línea Temporal Inteligente