"""Benchmark queries: (name, feature, SQL).  The same text runs with
provsql.active off (plain SQL over the same tables) and on."""

QUERIES = [
 # --- core
 ("scan",          "scan of a tracked table",
  "SELECT id, v FROM r"),
 ("select_1pct",   "selection, 1% of the rows",
  "SELECT id, v FROM r WHERE b = 7"),
 ("expr",          "target-list expressions",
  "SELECT id, CASE WHEN v > 500 THEN 'hi' ELSE 'lo' END, v * 2 + 1, coalesce(t, '-') FROM r"),
 ("join_untracked","join with an untracked relation (no gate)",
  "SELECT r.id, p.label FROM r JOIN d_plain p ON r.a = p.a"),
 ("join_2",        "two-way join, one times gate per output row",
  "SELECT r.id, d.label FROM r JOIN d ON r.a = d.a"),
 ("join_3",        "three-way join, one 3-child times gate per output row",
  "SELECT r.id, s.id, d.label FROM r, s, d WHERE r.a = s.a AND r.a = d.a AND r.b < 10 AND s.w < 100"),
 ("selfjoin",      "self-join",
  "SELECT x.id, y.id FROM r x JOIN r y ON x.a = y.a AND x.id < y.id WHERE x.b < 20"),
 ("from_subquery", "FROM subquery over a join",
  "SELECT q.id, q.w FROM (SELECT r.id, s.w, r.b FROM r JOIN s ON r.id = s.id) q WHERE q.b < 50"),
 # --- duplicate elimination, set operations
 ("distinct_small_groups", "DISTINCT, groups of 10 rows",
  "SELECT DISTINCT a FROM r"),
 ("distinct_big_groups",   "DISTINCT, 100 groups of N/100 rows",
  "SELECT DISTINCT b FROM r"),
 ("union_all",     "UNION ALL",
  "SELECT a FROM r UNION ALL SELECT a FROM s"),
 ("union",         "UNION",
  "SELECT a FROM r UNION SELECT a FROM s"),
 ("except",        "EXCEPT",
  "SELECT a FROM r EXCEPT SELECT a FROM s WHERE w < 500"),
 # --- aggregation
 ("count_small_groups", "count(*), groups of 10 rows",
  "SELECT a, count(*) FROM r GROUP BY a"),
 ("sum_small_groups",   "sum, groups of 10 rows",
  "SELECT a, sum(v) FROM r GROUP BY a"),
 ("sum_big_groups",     "sum, 100 groups of N/100 rows",
  "SELECT b, sum(v) FROM r GROUP BY b"),
 ("multi_agg",          "four aggregates, groups of 10 rows",
  "SELECT a, count(*), sum(v), min(v), max(v) FROM r GROUP BY a"),
 ("scalar_agg",         "scalar aggregation",
  "SELECT sum(v) FROM r"),
 ("agg_filter",         "aggregate with FILTER",
  "SELECT a, count(*) FILTER (WHERE v > 500) FROM r GROUP BY a"),
 ("agg_over_join",      "aggregate over a join",
  "SELECT d.label, sum(r.v) FROM r JOIN d ON r.a = d.a GROUP BY d.label"),
 ("having",             "HAVING on an aggregate",
  "SELECT a FROM r GROUP BY a HAVING sum(v) > 5000"),
 ("where_on_agg",       "WHERE on an aggregate of a subquery",
  "SELECT a, c FROM (SELECT a, count(*) AS c FROM r GROUP BY a) q WHERE c >= 10"),
 ("agg_distinct",       "count(DISTINCT)",
  "SELECT a, count(DISTINCT v) FROM r GROUP BY a"),
 # --- subqueries outside FROM
 ("exists",        "correlated EXISTS",
  "SELECT d.a FROM d WHERE EXISTS (SELECT 1 FROM s WHERE s.a = d.a AND s.w < 500)"),
 ("not_exists",    "correlated NOT EXISTS",
  "SELECT d.a FROM d WHERE NOT EXISTS (SELECT 1 FROM s WHERE s.a = d.a AND s.w < 500)"),
 ("in_sub",        "IN (subquery)",
  "SELECT d.a FROM d WHERE d.a IN (SELECT s.a FROM s WHERE s.w < 100)"),
 ("not_in_sub",    "NOT IN (subquery)",
  "SELECT d.a FROM d WHERE d.a NOT IN (SELECT s.a FROM s WHERE s.w < 100)"),
 ("scalar_sub",    "correlated scalar aggregate subquery in the target list",
  "SELECT d.a, (SELECT max(s.w) FROM s WHERE s.a = d.a) FROM d"),
 # --- outer joins
 ("left_join",     "LEFT JOIN of two tracked relations",
  "SELECT d.a, s.w FROM d LEFT JOIN s ON s.a = d.a AND s.w < 100"),
 # --- recursion
 ("recursive",     "recursive CTE, reachability on an acyclic graph",
  "WITH RECURSIVE reach(n) AS (SELECT 0 UNION SELECT e.dst FROM edge e JOIN reach ON e.src = reach.n) SELECT n FROM reach"),
]