-- Q2. Airports Reachable within K Hops
MATCH (origin:airport)
WHERE elementId(origin) = 'airport[12339]'
MATCH p = (origin)-[flights:flight*1..3]->(:airport)
WHERE all(flight IN flights WHERE flight.Year = 2020)
RETURN DISTINCT
       last(flights).DestAirportID AS airport_id,
       last(flights).Dest AS code,
       last(flights).DestCityName AS city,
       length(p) AS hop_count
ORDER BY hop_count, code;

MATCH p = (:Airport {airport_id: $originAirportId})-[f:FLIGHT]->{1,3}(dest:Airport)
WHERE all(flight IN f WHERE flight.year = $Year)
RETURN DISTINCT
    dest.airport_id AS airport_id,
    dest.code AS code,
    dest.city AS city,
    length(p) AS hop_count
ORDER BY hop_count, code
LIMIT $limit;

-- Q3. Airports Exactly Three Flights Away
MATCH (origin:airport)
WHERE elementId(origin) = 'airport[12339]'
MATCH p = (origin)-[flights:flight*3]->(:airport)
WHERE all(flight IN flights WHERE flight.Year = 2020)
RETURN DISTINCT last(flights).DestAirportID AS airport_id,
       last(flights).Dest AS code,
       last(flights).DestCityName AS city,
       length(p) AS hop_count
ORDER BY code;

MATCH p = (:Airport {airport_id: $originAirportId})-[f:FLIGHT]->{3,3}(dest:Airport)
WHERE all(flight IN f WHERE flight.year = $Year)
RETURN DISTINCT dest.airport_id AS airport_id, dest.code AS code, dest.city AS city, length(p) AS hop_count
ORDER BY code
LIMIT $limit;

-- Q4. Delayed One-Hop Destinations
MATCH (:airport)-[f:flight]->(:airport)
WHERE f.Origin = 'IND'
  AND f.Year = 2020
  AND f.DepDelayMinutes >= 120
RETURN f.DestAirportID AS airport_id,
       f.Dest AS code,
       f.FlightDate AS flight_date,
       f.IATA_CODE_Reporting_Airline AS airline,
       f.DepDelayMinutes AS departure_delay
ORDER BY departure_delay DESC;

MATCH (origin:Airport {airport_id: $originAirportId})-[f:FLIGHT]->(dest:Airport)
WHERE f.Year = $year
  AND f.DepDelayMinutes >= $minDelayMinutes
RETURN dest.airport_id AS airport_id, dest.code AS code, f.FlightDate AS flight_date,
       f.IATA_CODE_Reporting_Airline AS airline, f.DepDelayMinutes AS departure_delay
ORDER BY departure_delay DESC
LIMIT $limit;

-- Q5. Airports by State and Code Prefix
MATCH (:airport)-[f:flight]->(:airport)
WHERE f.OriginState = 'CA'
  AND f.Origin STARTS WITH 'S'
RETURN DISTINCT f.OriginAirportID AS airport_id,
       f.Origin AS code,
       f.OriginCityName AS city,
       f.OriginStateName AS state_name
ORDER BY code;

MATCH (airport:Airport)
WHERE airport.state = $state
  AND airport.code STARTS WITH $codePrefix
RETURN airport.airport_id AS airport_id, airport.code AS code, airport.city AS city, airport.stateName AS state_name
ORDER BY code
LIMIT $limit;

-- Q6. Long Non-Cancelled Flights
MATCH (:airport)-[f:flight]->(:airport)
WHERE f.Year = 2020
  AND f.Cancelled = 0
  AND f.Distance >= 2000
RETURN f.Origin AS origin,
       f.Dest AS dest,
       f.FlightDate AS flight_date,
       f.IATA_CODE_Reporting_Airline AS airline,
       f.Distance AS distance
ORDER BY distance DESC;

MATCH (origin:Airport)-[f:FLIGHT]->(dest:Airport)
WHERE f.Year = $year
  AND f.Cancelled = 0
  AND f.Distance >= $minDistance
RETURN origin.code AS origin, dest.code AS dest, f.FlightDate AS flight_date,
       f.IATA_CODE_Reporting_Airline AS airline, f.Distance AS distance
ORDER BY distance DESC
LIMIT $limit;

-- Q7. Reachability between Two Airports
MATCH (source:airport)
WHERE elementId(source) = 'airport[12339]'
MATCH (target:airport)
WHERE elementId(target) = 'airport[12478]'
MATCH p = (source)-[:flight*1..3]->(target)
RETURN count(p) > 0 AS reachable
LIMIT 1;

MATCH p = ANY (:Airport {airport_id: $sourceAirportId})-[:FLIGHT]->{1,3}(:Airport {airport_id: $targetAirportId})
RETURN true AS reachable;

-- Q8. Shortest Airport Route
MATCH (source:airport)
WHERE elementId(source) = 'airport[12339]'
MATCH (target:airport)
WHERE elementId(target) = 'airport[12478]'
MATCH p = shortestPath((source)-[:flight*1..4]->(target))
RETURN p, length(p) AS hop_count;

MATCH p = SHORTEST (:Airport {airport_id: $sourceAirportId})-[:FLIGHT]->{1,4}(:Airport {airport_id: $targetAirportId})
RETURN p, length(p) AS hop_count;

-- Q9. Simple Routes up to Three Legs
MATCH (source:airport)
WHERE elementId(source) = 'airport[12339]'
MATCH (target:airport)
WHERE elementId(target) = 'airport[12478]'
MATCH p = (source)-[:flight*1..3]->(target)
WHERE all(n IN nodes(p) WHERE single(m IN nodes(p) WHERE m = n))
RETURN p, length(p) AS hop_count
ORDER BY hop_count;

MATCH p = ACYCLIC (:Airport {airport_id: $sourceAirportId})-[:FLIGHT]->{1,3}(:Airport {airport_id: $targetAirportId})
RETURN p, length(p) AS hop_count
ORDER BY hop_count
LIMIT $limit;

-- Q10. Top Weighted Routes by Distance
MATCH (source:airport)
WHERE elementId(source) = 'airport[12339]'
MATCH (target:airport)
WHERE elementId(target) = 'airport[12478]'
MATCH p = (source)-[:flight*1..3]->(target)
WHERE all(n IN nodes(p) WHERE single(m IN nodes(p) WHERE m = n))
WITH p, reduce(total = 0, f IN relationships(p) |
               total + coalesce(f.Distance, 0)) AS total_distance
RETURN p, length(p) AS hop_count, total_distance
ORDER BY total_distance ASC;

MATCH p = ACYCLIC (:Airport {airport_id: $sourceAirportId})-[:FLIGHT]->{1,3}(:Airport {airport_id: $targetAirportId})
WITH p, reduce(total = 0, f IN relationships(p) | total + coalesce(f.Distance, 0)) AS total_distance
RETURN p, length(p) AS hop_count, total_distance
ORDER BY total_distance ASC
LIMIT $limit;

-- Q11. Directed Triangle of Airports
MATCH (a:airport)-[ab:flight]->(b:airport)-[bc:flight]->(c:airport)-[:flight]->(a)
WHERE ab.OriginAirportID < ab.DestAirportID
  AND ab.DestAirportID < bc.DestAirportID
RETURN ab.Origin AS airport_a,
       ab.Dest AS airport_b,
       bc.Dest AS airport_c;

MATCH (a:Airport)-[:FLIGHT]->(b:Airport)-[:FLIGHT]->(c:Airport)-[:FLIGHT]->(a)
WHERE a.airport_id < b.airport_id AND b.airport_id < c.airport_id
RETURN a.code AS airport_a, b.code AS airport_b, c.code AS airport_c
LIMIT $limit;

-- Q12. Four-Airport Cycle
MATCH p = (a:airport)-[ab:flight]->(b:airport)-[bc:flight]->(c:airport)-[cd:flight]->(d:airport)-[:flight]->(a)
WHERE elementId(a) <> elementId(b)
  AND elementId(a) <> elementId(c)
  AND elementId(a) <> elementId(d)
  AND elementId(b) <> elementId(c)
  AND elementId(b) <> elementId(d)
  AND elementId(c) <> elementId(d)
RETURN ab.Origin AS airport_a,
       ab.Dest AS airport_b,
       bc.Dest AS airport_c,
       cd.Dest AS airport_d;

MATCH (a:Airport)-[:FLIGHT]->(b:Airport)-[:FLIGHT]->(c:Airport)-[:FLIGHT]->(d:Airport)-[:FLIGHT]->(a)
WHERE a <> b AND a <> c AND a <> d AND b <> c AND b <> d AND c <> d
RETURN a.code AS airport_a, b.code AS airport_b, c.code AS airport_c, d.code AS airport_d
LIMIT $limit;

-- Q13. Hub Airports by Distinct Destinations
MATCH (center:airport)-[f:flight]->(dest:airport)
WHERE f.Year = 2020
WITH center,
     f.OriginAirportID AS airport_id,
     f.Origin AS code,
     f.OriginCityName AS city,
     count(DISTINCT dest) AS destination_count
WHERE destination_count >= 190
RETURN airport_id, code, city, destination_count
ORDER BY destination_count DESC;

MATCH (center:Airport)-[f:FLIGHT]->(dest:Airport)
WHERE f.Year = $year
WITH center, count(DISTINCT dest) AS destination_count
WHERE destination_count >= $minDegree
RETURN center.airport_id AS airport_id, center.code AS code, center.city AS city, destination_count
ORDER BY destination_count DESC
LIMIT $limit;

-- Q14. Four-Airport Clique
MATCH (a:airport)-[:flight]-(b:airport),
      (a)-[:flight]-(c:airport),
      (a)-[:flight]-(d:airport),
      (b)-[:flight]-(c),
      (b)-[:flight]-(d),
      (c)-[:flight]-(d)
WHERE elementId(a) < elementId(b)
  AND elementId(b) < elementId(c)
  AND elementId(c) < elementId(d)
RETURN elementId(a) AS airport_a,
       elementId(b) AS airport_b,
       elementId(c) AS airport_c,
       elementId(d) AS airport_d;

MATCH (a:Airport)-[:FLIGHT]-(b:Airport),
      (a)-[:FLIGHT]-(c:Airport),
      (a)-[:FLIGHT]-(d:Airport),
      (b)-[:FLIGHT]-(c),
      (b)-[:FLIGHT]-(d),
      (c)-[:FLIGHT]-(d)
WHERE a.airport_id < b.airport_id AND b.airport_id < c.airport_id AND c.airport_id < d.airport_id
RETURN a.code AS airport_a, b.code AS airport_b, c.code AS airport_c, d.code AS airport_d
LIMIT $limit;

-- Q15. Two-Hop Airport Connections
MATCH (source:airport)-[first:flight]->(:airport)-[second:flight]->(dest:airport)
WHERE elementId(source) = 'airport[12339]'
  AND first.Year = 2020
  AND second.Year = 2020
  AND elementId(source) <> elementId(dest)
RETURN first.Dest AS connection_airport,
       second.Dest AS destination,
       count(*) AS connection_count
ORDER BY connection_count DESC;

MATCH (source:Airport {airport_id: $sourceAirportId})-[first:FLIGHT]->(middle:Airport)-[second:FLIGHT]->(dest:Airport)
WHERE first.Year = $year
  AND second.Year = $year
  AND source <> dest
RETURN middle.code AS connection_airport, dest.code AS destination, count(*) AS connection_count
ORDER BY connection_count DESC
LIMIT $limit;

-- Q16. Three-Hop Line Pattern
MATCH p = (a:airport)-[ab:flight]->(b:airport)-[bc:flight]->(c:airport)-[cd:flight]->(d:airport)
WHERE elementId(a) <> elementId(b)
  AND elementId(a) <> elementId(c)
  AND elementId(a) <> elementId(d)
  AND elementId(b) <> elementId(c)
  AND elementId(b) <> elementId(d)
  AND elementId(c) <> elementId(d)
RETURN ab.Origin AS airport_a,
       ab.Dest AS airport_b,
       bc.Dest AS airport_c,
       cd.Dest AS airport_d,
       p;

MATCH p = (a:Airport)-[:FLIGHT]->(b:Airport)-[:FLIGHT]->(c:Airport)-[:FLIGHT]->(d:Airport)
WHERE a <> b AND a <> c AND a <> d AND b <> c AND b <> d AND c <> d
RETURN a.code AS airport_a, b.code AS airport_b, c.code AS airport_c, d.code AS airport_d, p
LIMIT $limit;

-- Q17. Airport Degree Summary
MATCH (airport:airport)
OPTIONAL MATCH (airport)-[:flight]->(dest:airport)
WITH airport, count(DISTINCT dest) AS out_degree
RETURN count(airport) AS airport_count,
       avg(out_degree) AS avg_out_degree,
       min(out_degree) AS min_out_degree,
       max(out_degree) AS max_out_degree;

MATCH (airport:Airport)
OPTIONAL MATCH (airport)-[:FLIGHT]->(dest:Airport)
WITH airport, count(DISTINCT dest) AS out_degree
RETURN count(airport) AS airport_count,
       avg(out_degree) AS avg_out_degree,
       min(out_degree) AS min_out_degree,
       max(out_degree) AS max_out_degree;

-- Q18. State-Level Degree Summary
MATCH (airport:airport)
OPTIONAL MATCH (airport)-[f:flight]->(dest:airport)
WITH airport, f.OriginState AS state, count(DISTINCT dest) AS out_degree
RETURN state,
       count(airport) AS airport_count,
       avg(out_degree) AS avg_out_degree,
       min(out_degree) AS min_out_degree,
       max(out_degree) AS max_out_degree
ORDER BY avg_out_degree DESC;

MATCH (airport:Airport)
OPTIONAL MATCH (airport)-[:FLIGHT]->(dest:Airport)
WITH airport, count(DISTINCT dest) AS out_degree
RETURN airport.state AS state,
       count(airport) AS airport_count,
       avg(out_degree) AS avg_out_degree,
       min(out_degree) AS min_out_degree,
       max(out_degree) AS max_out_degree
ORDER BY avg_out_degree DESC
LIMIT $limit;

-- Q19. Flight Edge Distance Summary
MATCH (:airport)-[f:flight]->(:airport)
WHERE f.Year = 2020
RETURN count(f) AS flight_count,
       avg(f.Distance) AS avg_distance,
       min(f.Distance) AS min_distance,
       max(f.Distance) AS max_distance,
       avg(f.ArrDelayMinutes) AS avg_arrival_delay;

MATCH (:Airport)-[f:FLIGHT]->(:Airport)
WHERE f.Year = $year
RETURN count(f) AS flight_count,
       avg(f.Distance) AS avg_distance,
       min(f.Distance) AS min_distance,
       max(f.Distance) AS max_distance,
       avg(f.ArrDelayMinutes) AS avg_arrival_delay;

-- Q20. State Pair Flight Statistics
MATCH (:airport)-[f:flight]->(:airport)
WHERE f.Year = 2020
RETURN f.OriginState AS origin_state,
       f.DestState AS dest_state,
       count(f) AS flight_count,
       avg(f.Distance) AS avg_distance,
       avg(f.ArrDelayMinutes) AS avg_arrival_delay
ORDER BY flight_count DESC;

MATCH (origin:Airport)-[f:FLIGHT]->(dest:Airport)
WHERE f.Year = $year
RETURN origin.state AS origin_state,
       dest.state AS dest_state,
       count(f) AS flight_count,
       avg(f.Distance) AS avg_distance,
       avg(f.ArrDelayMinutes) AS avg_arrival_delay
ORDER BY flight_count DESC
LIMIT $limit;

-- Q21. Route Counts by Path Length
MATCH (source:airport)
WHERE elementId(source) = 'airport[12339]'
MATCH p = (source)-[:flight*1..3]->(target:airport)
RETURN length(p) AS path_length,
       count(p) AS path_count,
       count(DISTINCT target) AS distinct_target_count
ORDER BY path_length;

MATCH p = (:Airport {airport_id: $sourceAirportId})-[:FLIGHT]->{1,3}(target:Airport)
RETURN length(p) AS path_length,
       count(p) AS path_count,
       count(DISTINCT target) AS distinct_target_count
ORDER BY path_length;

-- Q22. Route Counts by Length and Target State
MATCH (source:airport)
WHERE elementId(source) = 'airport[12339]'
MATCH p = (source)-[flights:flight*1..3]->(target:airport)
RETURN length(p) AS path_length,
       last(flights).DestState AS target_state,
       count(p) AS path_count,
       count(DISTINCT target) AS distinct_target_count
ORDER BY path_length, path_count DESC;

MATCH p = (:Airport {airport_id: $sourceAirportId})-[:FLIGHT]->{1,3}(target:Airport)
RETURN length(p) AS path_length,
       target.state AS target_state,
       count(p) AS path_count,
       count(DISTINCT target) AS distinct_target_count
ORDER BY path_length, path_count DESC
LIMIT $limit;

-- Q23. Airports Serving Two Destination States
MATCH (origin:airport)-[to_a:flight]->(:airport)
MATCH (origin:airport)-[to_b:flight]->(:airport)
WHERE to_a.DestState = 'IN'
  AND to_b.DestState = 'IL'
RETURN DISTINCT to_a.OriginAirportID AS airport_id,
       to_a.Origin AS code,
       to_a.OriginCityName AS city,
       to_a.OriginState AS state
ORDER BY code;

MATCH (origin:Airport)-[:FLIGHT]->(destA:Airport {state: $stateA})
MATCH (origin)-[:FLIGHT]->(destB:Airport {state: $stateB})
RETURN DISTINCT origin.airport_id AS airport_id, origin.code AS code, origin.city AS city, origin.state AS state
ORDER BY code
LIMIT $limit;

-- Q24. Top Airports by Degree Centrality
MATCH (airport:airport)-[:flight]-(neighbor:airport)
WITH airport, count(DISTINCT neighbor) AS degree
MATCH (airport)-[meta:flight]->(:airport)
RETURN DISTINCT meta.OriginAirportID AS airport_id,
       meta.Origin AS code,
       meta.OriginCityName AS city,
       degree
ORDER BY degree DESC;

-- Q25. Airport PageRank
CALL algo.paral.pagerank({
  labels: ['airport'],
  relationshipTypes: ['flight']
}) YIELD id, score
RETURN id AS airport_id, score
ORDER BY score DESC;

-- Q26. Weakly Connected Airport Components
CALL algo.wcc({
  labels: ['airport'],
  relationshipTypes: ['flight']
}) YIELD id, componentId
RETURN componentId, count(*) AS component_size
ORDER BY component_size DESC;

-- Q27. Same-day Connections
MATCH p = (a:airport)-[r1:flight]->(:airport)-[r2:flight]->(c:airport)
WHERE r1.FlightDate = r2.FlightDate
  AND r1.CRSArrTime + 100 <= r2.CRSDepTime
  AND elementId(a) = 'airport[12892]'
  AND elementId(c) = 'airport[12953]'
  AND r1.Year = 2020
WITH r1.Month AS month, count(p) AS path_count
RETURN month, path_count
ORDER BY month;

MATCH p = (a:Airport)-[r1:FLIGHT]->(b:Airport)-[r2:FLIGHT]->(c:Airport)
WHERE r1.FlightDate = r2.FlightDate
  AND r1.CRSArrTime + 100 <= r2.CRSDepTime
  AND a.airport_id = 12892
  AND c.airport_id = 12953
  AND r1.Year = $year
WITH r1.Month AS month, count(p) AS path_count
RETURN month, path_count
ORDER BY month;

-- Q28. Missed Connecting Flights
MATCH p = (a:airport)-[r1:flight]->(:airport)-[r2:flight]->(c:airport)
WHERE r1.FlightDate = r2.FlightDate
  AND r1.CRSArrTime + 100 <= r2.CRSDepTime
  AND r1.ArrTime >= r2.DepTime
  AND elementId(a) = 'airport[12892]'
  AND elementId(c) = 'airport[12953]'
  AND r1.Year = 2020
WITH r1.Month AS month, count(p) AS path_count
RETURN month, path_count
ORDER BY month;

MATCH p = (a:Airport)-[r1:FLIGHT]->(b:Airport)-[r2:FLIGHT]->(c:Airport)
WHERE r1.FlightDate = r2.FlightDate
  AND r1.CRSArrTime + 100 <= r2.CRSDepTime
  AND r1.ArrTime >= r2.DepTime
  AND a.airport_id = 12892
  AND c.airport_id = 12953
  AND r1.Year = $year
WITH r1.Month AS month, count(p) AS path_count
RETURN month, path_count
ORDER BY month;

-- Q29. Top 10 Aircraft Turnarounds
MATCH (:airport)-[r1:flight]->(n:airport)-[r2:flight]->(:airport)
WHERE r1.FlightDate = date('2020-12-25')
  AND r1.FlightDate = r2.FlightDate
  AND r1.Tail_Number = r2.Tail_Number
  AND r1.Tail_Number IS NOT NULL
  AND r1.Tail_Number <> ''
  AND r1.CRSArrTime < r2.CRSDepTime
WITH n, count(DISTINCT r1.Tail_Number) AS cnt
RETURN n, cnt
ORDER BY cnt DESC
LIMIT 10;

MATCH (:Airport)-[r1:FLIGHT]->(n:Airport)-[r2:FLIGHT]->(:Airport)
WHERE r1.FlightDate = date($date)
  AND r1.FlightDate = r2.FlightDate
  AND r1.Tail_Number = r2.Tail_Number
  AND r1.Tail_Number IS NOT NULL
  AND r1.Tail_Number <> ""
  AND r1.CRSArrTime < r2.CRSDepTime
WITH n, count(DISTINCT r1.Tail_Number) AS cnt
RETURN n, cnt
ORDER BY cnt DESC
LIMIT 10;

-- Q30. Three-Leg Flight Sequences
MATCH (:airport)-[r1:flight]->(:airport)-[r2:flight]->(:airport)-[r3:flight]->(:airport)
WHERE r1.FlightDate = date('2020-12-25')
  AND r1.FlightDate = r2.FlightDate
  AND r1.FlightDate = r3.FlightDate
  AND r1.Tail_Number = r2.Tail_Number
  AND r1.Tail_Number = r3.Tail_Number
  AND r1.Tail_Number IS NOT NULL
  AND r1.Tail_Number <> ''
  AND r1.CRSArrTime < r2.CRSDepTime
  AND r2.CRSArrTime < r3.CRSDepTime
RETURN count(DISTINCT r1.Tail_Number) AS cnt;

MATCH (:Airport)-[r1:FLIGHT]->(:Airport)-[r2:FLIGHT]->(:Airport)-[r3:FLIGHT]->(:Airport)
WHERE r1.FlightDate = date($date)
  AND r1.FlightDate = r2.FlightDate
  AND r1.FlightDate = r3.FlightDate
  AND r1.Tail_Number = r2.Tail_Number
  AND r1.Tail_Number = r3.Tail_Number
  AND r1.Tail_Number IS NOT NULL
  AND r1.Tail_Number <> ""
  AND r1.CRSArrTime < r2.CRSDepTime
  AND r2.CRSArrTime < r3.CRSDepTime
RETURN count(DISTINCT r1.Tail_Number) AS cnt;