-- plxruby regression tests
CREATE EXTENSION IF NOT EXISTS plx;
SET client_min_messages = warning;

-- scalar return, arithmetic
CREATE FUNCTION rb_add(a int, b int) RETURNS int LANGUAGE plxruby AS $$
return a + b * 2
$$;
SELECT rb_add(3, 4);

-- if / elsif / else with a type annotation
CREATE FUNCTION rb_grade(score int) RETURNS text LANGUAGE plxruby AS $$
grade #:: text
if score >= 90
  grade = "A"
elsif score >= 80
  grade = "B"
else
  grade = "F"
end
return grade
$$;
SELECT rb_grade(95), rb_grade(85), rb_grade(70);

-- unless modifier
CREATE FUNCTION rb_zcheck(n int) RETURNS text LANGUAGE plxruby AS $$
return "empty" unless n != 0
return "nonzero"
$$;
SELECT rb_zcheck(0), rb_zcheck(5);

-- while / until
CREATE FUNCTION rb_countdown(n int) RETURNS int LANGUAGE plxruby AS $$
c = 0 #:: integer
while n > 0
  c = c + n
  n = n - 1
end
return c
$$;
SELECT rb_countdown(5);

-- for loop with next / break
CREATE FUNCTION rb_sum_skip(n int) RETURNS int LANGUAGE plxruby AS $$
total = 0
for i in 1..n
  next if i == 3
  break if i > 7
  total = total + i
end
return total
$$;
SELECT rb_sum_skip(10);

-- exclusive range via .each
CREATE FUNCTION rb_exsum(n int) RETURNS int LANGUAGE plxruby AS $$
s = 0
(0...n).each do |j|
  s = s + j
end
return s
$$;
SELECT rb_exsum(5);

-- ternary
CREATE FUNCTION rb_sign(x int) RETURNS text LANGUAGE plxruby AS $$
return x >= 0 ? "nonneg" : "neg"
$$;
SELECT rb_sign(-4), rb_sign(4);

-- inference of multiple types + interpolation
CREATE FUNCTION rb_fmt() RETURNS text LANGUAGE plxruby AS $$
label = "count"
n = 42
active = true
return "#{label}=#{n} active=#{active}"
$$;
SELECT rb_fmt();

-- numeric annotation + interpolation
CREATE FUNCTION rb_interest(principal numeric) RETURNS text LANGUAGE plxruby AS $$
rate = 0.05 #:: numeric
amount #:: numeric
amount = principal * rate
return "interest: #{amount}"
$$;
SELECT rb_interest(1000);

-- SETOF via emit
CREATE FUNCTION rb_rows(n int) RETURNS TABLE(idx int, label text) LANGUAGE plxruby AS $$
for i in 1..n
  idx = i
  label = "row#{i}"
  emit
end
$$;
SELECT * FROM rb_rows(3);

-- query iteration and fetch_one against a table
CREATE TABLE rb_orders(grp int, amount bigint);
INSERT INTO rb_orders VALUES (1,10),(1,20),(1,30),(2,5);
CREATE FUNCTION rb_grp_total(g int) RETURNS bigint LANGUAGE plxruby AS $$
total = 0 #:: bigint
query("SELECT amount FROM rb_orders WHERE grp = #{g}").each do |row|
  total = total + row.amount
end
return total
$$;
SELECT rb_grp_total(1), rb_grp_total(2);

CREATE TABLE rb_users(id int, name text);
INSERT INTO rb_users VALUES (1,'Alice'),(2,'Bob');
CREATE FUNCTION rb_name(uid int) RETURNS text LANGUAGE plxruby AS $$
u = fetch_one("SELECT id, name FROM rb_users WHERE id = #{uid}")
return u.name
$$;
SELECT rb_name(2);

-- return_query
CREATE TABLE rb_vip(id int);
INSERT INTO rb_vip VALUES (3),(1),(2);
CREATE FUNCTION rb_vips() RETURNS SETOF int LANGUAGE plxruby AS $$
return_query("SELECT id FROM rb_vip ORDER BY id")
$$;
SELECT * FROM rb_vips();

-- begin / rescue
CREATE FUNCTION rb_safediv(d int) RETURNS int LANGUAGE plxruby AS $$
begin
  return 100 / d
rescue => e
  raise notice: "caught: #{e.message}"
  return -1
end
$$;
SELECT rb_safediv(4), rb_safediv(0);

-- raise with errcode
CREATE FUNCTION rb_checkpos(v int) RETURNS int LANGUAGE plxruby AS $$
if v < 0
  raise exception: "negative: #{v}", errcode: "22023"
end
return v
$$;
SELECT rb_checkpos(7);

-- DO block (body is Ruby)
DO LANGUAGE plxruby $$
raise warning: "plxruby do-block ran: #{6 * 7}"
$$;

-- case/when (simple and searched forms)
CREATE FUNCTION rb_case_simple(n int) RETURNS text LANGUAGE plxruby AS $$
case n
when 1
  return "one"
when 2, 3
  return "few"
else
  return "many"
end
$$;
SELECT rb_case_simple(1), rb_case_simple(3), rb_case_simple(9);

CREATE FUNCTION rb_case_searched(n int) RETURNS text LANGUAGE plxruby AS $$
case
when n < 0
  return "neg"
when n == 0
  return "zero"
else
  return "pos"
end
$$;
SELECT rb_case_searched(-5), rb_case_searched(0), rb_case_searched(5);

-- assert
CREATE FUNCTION rb_assert(n int) RETURNS int LANGUAGE plxruby AS $$
assert(n > 0, "must be positive")
return n
$$;
SELECT rb_assert(5);

-- row_count via GET DIAGNOSTICS, and found?
CREATE TABLE rb_t(a int);
CREATE FUNCTION rb_ins(n int) RETURNS bigint LANGUAGE plxruby AS $$
rc = 0 #:: bigint
execute("INSERT INTO rb_t SELECT g FROM generate_series(1, #{n}) g")
rc = row_count()
return rc
$$;
SELECT rb_ins(4);

CREATE FUNCTION rb_found() RETURNS boolean LANGUAGE plxruby AS $$
perform("SELECT 1 WHERE false")
return found?
$$;
SELECT rb_found();

-- FOREACH over an array, and %ROWTYPE, and CONSTANT
CREATE FUNCTION rb_sumarr(a int[]) RETURNS int LANGUAGE plxruby AS $$
total = 0 #:: int
v #:: int
a.each do |v|
  total = total + v
end
return total
$$;
SELECT rb_sumarr(ARRAY[10,20,30]);

CREATE TABLE rb_emp(id int, name text);
INSERT INTO rb_emp VALUES (1,'Alice'),(2,'Bob');
CREATE FUNCTION rb_rowtype(eid int) RETURNS text LANGUAGE plxruby AS $$
e #:: rb_emp%ROWTYPE
e = fetch_one("SELECT * FROM rb_emp WHERE id = #{eid}")
return e.name
$$;
SELECT rb_rowtype(2);

CREATE FUNCTION rb_const(r numeric) RETURNS numeric LANGUAGE plxruby AS $$
pi = 3.14159 #:: numeric const
return pi * r * r
$$;
SELECT rb_const(2);

-- cursors: open_cursor / fetch_from / found? / close_cursor
CREATE TABLE rb_nums(v int);
INSERT INTO rb_nums SELECT generate_series(1,5);
CREATE FUNCTION rb_cursor_sum() RETURNS int LANGUAGE plxruby AS $$
total = 0 #:: int
c = open_cursor("SELECT v FROM rb_nums ORDER BY v")
row = fetch_from(c)
while found?
  total = total + row.v
  row = fetch_from(c)
end
close_cursor(c)
return total
$$;
SELECT rb_cursor_sum();

-- labeled loop (break outer) and stacked diagnostics
CREATE FUNCTION rb_labeled(n int) RETURNS int LANGUAGE plxruby AS $$
count = 0 #:: int
outer: for i in 1..n
  for j in 1..n
    count = count + 1
    break outer if i + j >= 4
  end
end
return count
$$;
SELECT rb_labeled(5);

CREATE TABLE rb_uq(id int PRIMARY KEY);
INSERT INTO rb_uq VALUES (1);
CREATE FUNCTION rb_diag(k int) RETURNS text LANGUAGE plxruby AS $$
begin
  execute("INSERT INTO rb_uq(id) VALUES ($1)", k)
  return "inserted"
rescue PG::UniqueViolation => e
  return "constraint=#{e.constraint} detail=#{e.detail}"
end
$$;
SELECT rb_diag(1);

-- single-quoted strings are raw: backslash escapes stay literal ('\t' -> \ t)
CREATE FUNCTION rb_raw_squote() RETURNS text LANGUAGE plxruby AS $$
  return 'x\ty'
$$;
-- double-quoted strings still process escapes ("\n" -> newline)
CREATE FUNCTION rb_dquote_esc() RETURNS text LANGUAGE plxruby AS $$
  return "a\nb"
$$;
SELECT rb_raw_squote() AS raw_val,
       length(rb_raw_squote()) AS raw_len,
       rb_raw_squote() ~ E'\\\\' AS raw_backslash_kept,
       length(rb_dquote_esc()) AS dquote_len;

-- non-decimal integer literals convert to decimal
CREATE FUNCTION rb_hex() RETURNS int LANGUAGE plxruby AS $$
  return 0xff
$$;
SELECT rb_hex();