import { MAX_FILAS, validarConsulta } from './validar-sql';

const PERMITIDAS = new Set(['v_ventas', 'v_clientes']);

describe('validarConsulta', () => {
  it.each([
    'SELECT cliente, sum(total) AS total FROM soporte_ro.v_ventas GROUP BY cliente ORDER BY 2 DESC',
    "SELECT count(*) FROM soporte_ro.v_ventas WHERE fecha >= current_date - interval '30 days'",
    `WITH t AS (SELECT cliente, sum(total) AS s FROM soporte_ro.v_ventas GROUP BY cliente)
     SELECT c.nombre, round(t.s) FROM t JOIN soporte_ro.v_clientes c ON c.nombre = t.cliente`,
    "SELECT to_char(date_trunc('month', fecha), 'YYYY-MM') AS mes, sum(total) FROM soporte_ro.v_ventas GROUP BY 1;",
  ])('acepta: %s', (sql) => {
    const r = validarConsulta(sql, PERMITIDAS);
    expect(r).toMatchObject({ ok: true });
    if (r.ok) expect(r.sql).toMatch(new RegExp(`LIMIT ${MAX_FILAS}$`));
  });

  it.each([
    ['SELECT * FROM public.factura_cab', /soporte_ro/],
    ['SELECT * FROM factura_cab', /soporte_ro/],
    ['SELECT * FROM soporte_ro.v_rrhh_sueldos', /no tiene permiso/],
    ['SELECT * FROM soporte_ro.v_ventas; DROP TABLE x', /";"|una sola/i],
    ["SELECT set_config('app.empresa_id', 'x', true)", /set_config/],
    [
      "WITH x AS (SELECT set_config('app.empresa_id', 'otra', true)) SELECT * FROM x, soporte_ro.v_ventas",
      /set_config/,
    ],
    ["SELECT current_setting('app.firma')", /current_setting/],
    ['SELECT pg_sleep(10)', /pg_sleep/],
    ['SELECT * FROM soporte_ro.v_ventas FOR UPDATE', /for update/i],
    ['UPDATE soporte_ro.v_ventas SET total = 0', /SELECT|update/i],
    ['SELECT * INTO tmp FROM soporte_ro.v_ventas', /into/i],
    ['SELECT md5(cliente) FROM soporte_ro.v_ventas', /md5/],
    ['SELECT * FROM soporte_ro.v_ventas -- comentario', /comentarios/],
    ['SELECT * FROM generate_series(1, 100000000)', /generate_series/],
    ['', /vacía/],
  ])('rechaza: %s', (sql, motivo) => {
    const r = validarConsulta(sql, PERMITIDAS);
    expect(r.ok).toBe(false);
    if (!r.ok) expect(r.error).toMatch(motivo);
  });

  it('un CTE con el nombre de una vista no esquiva la lista', () => {
    const r = validarConsulta('WITH v_ventas AS (SELECT 1) SELECT * FROM public.usuario', PERMITIDAS);
    expect(r.ok).toBe(false);
  });
});