-- Parse status distribution
SELECT 
    parse_status,
    COUNT(*) AS flights
FROM dbo.adl_flight_plan
WHERE fp_route IS NOT NULL
GROUP BY parse_status;

-- Current queue depth by tier
SELECT 
    parse_tier,
    status,
    COUNT(*) AS queued
FROM dbo.adl_parse_queue
GROUP BY parse_tier, status
ORDER BY parse_tier;

-- Oldest pending items
SELECT TOP 10
    pq.flight_uid,
    c.callsign,
    pq.parse_tier,
    pq.queued_utc,
    pq.attempts
FROM dbo.adl_parse_queue pq
JOIN dbo.adl_flight_core c ON c.flight_uid = pq.flight_uid
WHERE pq.status = 'PENDING'
ORDER BY pq.queued_utc;

-- Check for unresolved waypoints
SELECT TOP 20
    fw.fix_name,
    COUNT(*) AS unresolved_count
FROM dbo.adl_flight_waypoints fw
WHERE fw.lat IS NULL
GROUP BY fw.fix_name
ORDER BY COUNT(*) DESC;

-- fn_GetTokenType returns:
SKIP        -- DCT, DIRECT, IFR, VFR
SPEED_ALT   -- N0450F350, FL350, A350
SID         -- DEEZZ5.DEEZZ (ends with digit, has dot)
STAR        -- WAVEY.WAVEY2 (starts with fix, ends with digit)
SID_OR_STAR -- Ambiguous format
AIRWAY      -- J36, V123, Q76, UL607, A15
LATLON      -- 40N73W, N4012W07305
AIRPORT     -- KJFK (4-letter ICAO prefix)
FIX         -- DEEZZ, WAVEY (2-5 letters)
UNKNOWN     -- Unrecognized

-- fn_ExpandAirwayNames returns fix sequence
-- Input: J36, entry=DEEZZ, exit=JEEMY
-- Output: DEEZZ, COLIN, JEEMY (if that's the J36 sequence)

-- Resolve each fix using previous fix's coordinates
SELECT TOP 1 lat, lon
FROM #candidates c
WHERE c.fix_name = @wp_name
ORDER BY 
    -- Prefer candidates on the current airway
    CASE WHEN @current_airway IS NOT NULL 
              AND c.on_airways LIKE '%' + @current_airway + '%' 
         THEN 0 ELSE 1 END,
    -- Then by distance to previous fix
    geography::Point(@prev_lat, @prev_lon, 4326).STDistance(
        geography::Point(c.lat, c.lon, 4326)
    );

-- Debug parse for specific flight
EXEC dbo.sp_ParseRoute 
    @flight_uid = 12345, 
    @debug = 1;