xml_to_sqlite_tool

Convert SUMO XML results to SQLite database for advanced SQL-based analysis.

IMPORTANT: This enables detailed queries that summary statistics cannot answer:
- Top N queries (e.g., "What are the top 10 roads by density?")
- Specific edge analysis (e.g., "What is the congestion level at the Gangnam Station intersection?")
- Comparative analysis (e.g., "How did density change before and after the policy?")
- Temporal analysis (e.g., "How does speed vary across times of day?")
- Road-level aggregation (e.g., "What is the average congestion across all of Teheran-ro?")

DATABASE SCHEMA (IMPORTANT):
- simulations: (simulation_id, created_at, vehicle_count, net_file, route_file, description)
- edge_info: (simulation_id, edge_id, road_name, length, num_lanes, speed_limit)
  * Network topology from .net.xml — enables road-level analysis
  * road_name: Human-readable street name (e.g., "테헤란로", "9th Avenue")
  * length: Edge length in meters
  * num_lanes: Number of lanes
  * speed_limit: Speed limit in km/h
- vehicle_info: (simulation_id, vehicle_id, vehicle_type, fuel_type, origin_edge, destination_edge, origin_road, destination_road)
  * Per-vehicle metadata from tripinfo + network — enables OD and fleet analysis
  * vehicle_type: SUMO vType (e.g., "passenger", "truck")
  * fuel_type: Classified from emissionClass — "gasoline", "diesel", "electric", "unknown"
  * origin_edge / destination_edge: First/last edge IDs
  * origin_road / destination_road: Human-readable road names (from edge_info)
- trips: (simulation_id, trip_id, duration, routeLength, waitingTime, timeLoss, depart, arrival, ...)
- edge_metrics: (simulation_id, edge_id, interval_begin, interval_end, speed, density, waitingTime,
                 timeLoss, occupancy, entered, left, ...)
  * PRIMARY KEY: (simulation_id, edge_id, interval_begin)
- network_state: (simulation_id, time, running, halting, waiting, meanSpeed, meanSpeedRelative, ...)
  * Network-wide time-series from summary.xml — 1 row per simulation second
  * Enables temporal analysis: congestion onset, performance curves, before/after comparison
  * PRIMARY KEY: (simulation_id, time)

KEY: Table name is 'edge_metrics', NOT 'edgedata'!

=== ROAD-LEVEL ANALYSIS WITH edge_info ===
For road-level congestion analysis (instead of edge-level), JOIN edge_info:

-- Road-level weighted density (RECOMMENDED for congestion ranking)
SELECT ei.road_name,
       ROUND(SUM(em.density * ei.length) / SUM(ei.length), 2) AS weighted_density,
       ROUND(SUM(ei.length), 1) AS total_length_m
FROM edge_metrics em
JOIN edge_info ei ON em.simulation_id = ei.simulation_id AND em.edge_id = ei.edge_id
WHERE em.simulation_id = '1_baseline' AND ei.road_name IS NOT NULL
GROUP BY ei.road_name
ORDER BY weighted_density DESC LIMIT 10;

-- Filter out micro-segments (< 10m)
WHERE ei.length > 10

-- Query by road name (NO need for get_edge_ids_from_road_name_tool!)
WHERE ei.road_name = '테헤란로'

DESIGN: Single unified DB with simulation_id for comparative studies
- Same network's simulations → Same DB file
- Different simulations → Different simulation_ids in same DB
- Enables SQL JOIN for before/after comparison

Args:
    tripinfo_xml: Path to tripinfo XML file
    edgedata_xml: Path to edgedata XML file
    edgedata_emission_xml: Path to edgedata emission XML file
    output_dir: Output directory for database (default: output/analysis)
    simulation_id: REQUIRED — NEVER leave as None!
        Format: "{N}_{scenario_name}" where N is the sequential number.
        - Check current simulations in context to determine N.
        - Use short, descriptive English names.
        - Examples: "1_baseline", "2_road_closure_teheran", "3_lane_reduction_gangnam",
          "4_tls_optimize_seocho", "5_speed_limit_gangnam"
    net_file: Path to network (.net.xml) file used in this simulation
    route_file: Path to route (.rou.xml) file used in this simulation
    description: REQUIRED — NEVER leave as None!
        Human-readable English description of the scenario.
        Displayed in the UI for scenario comparison.
        Always describe WHAT was changed and WHERE.
        Examples:
        - "Baseline simulation (Gangnam Station 1km)"
        - "Road closure on Teheran-ro near Gangnam Station (500m)"
        - "Lane reduction on Gangnam-daero (3 to 2 lanes)"
        - "Signal timing optimization at Seocho-daero intersection"
        - "Speed limit reduced to 30km/h on Teheran-ro"
    summary_xml: Path to summary XML file from sumo_runner result's top-level "summary_xml" field.
        IMPORTANT: Always provide this! Without it, dashboard time-series charts will be empty.

Returns:
    Dict with db_file, simulation_id, and metadata

Examples:
    # After sumo_runner returns result with metadata.absolute_summary:
    xml_to_sqlite_tool(
        tripinfo_xml="gangnam_tripinfo.xml",
        edgedata_xml="gangnam_edgedata.xml",
        edgedata_emission_xml="gangnam_emission.xml",
        simulation_id="1_baseline",
        description="Baseline simulation (Gangnam Station 1km)",
        summary_xml="/path/to/gangnam_summary.xml"
    )