-- Semana 8: los ejemplos del capítulo, en el orden en que aparecen.
-- Cada consulta va precedida de "-- name: nombre"; las pruebas
-- (json.test.mjs) corren este mismo texto. Base de datos chasqui_w08, con
-- json-setup.sql ya cargado (make lo carga).
--
--   psql chasqui_w08 -f examples.sql     corre todo y muestra los resultados

\pset null '∅'

-- ------------------------------------------------------------ json y jsonb

-- name: json_vs_jsonb
SELECT '{"b": 1,   "a": 2, "a": 3}'::json  AS as_json,
       '{"b": 1,   "a": 2, "a": 3}'::jsonb AS as_jsonb;

-- name: jsonb_equality
SELECT '{"a": 1, "b": [1, 2]}'::jsonb = '{"b": [1, 2], "a": 1}'::jsonb AS same_object,
       '[1, 2]'::jsonb = '[2, 1]'::jsonb AS same_array,
       '1.50'::jsonb = '1.5'::jsonb AS same_number;

-- name: jsonb_typeof
SELECT jsonb_typeof('{"a": 1}') AS object, jsonb_typeof('[1, 2]') AS array,
       jsonb_typeof('"Puno"') AS string, jsonb_typeof('3.5') AS number,
       jsonb_typeof('true') AS boolean, jsonb_typeof('null') AS null;

-- --------------------------------------------------------------- operadores

-- name: details_sample
SELECT shipment_id, details
FROM shipment
WHERE shipment_id IN (1, 2, 5, 12)
ORDER BY shipment_id;

-- name: arrows
SELECT details -> 'dimensions_cm'          AS dims,
       details -> 'dimensions_cm' -> 'l'   AS l_jsonb,
       details #> '{dimensions_cm,l}'      AS l_path,
       details #>> '{dimensions_cm,l}'     AS l_text,
       details ->> 'fragile'               AS fragile_text,
       details -> 'tags'                   AS tags
FROM shipment
WHERE shipment_id = 12;

-- name: arrow_types
SELECT pg_typeof(details -> 'fragile')  AS arrow,
       pg_typeof(details ->> 'fragile') AS double_arrow
FROM shipment
WHERE shipment_id = 12;

-- name: array_index
SELECT details -> 'tags' -> 0  AS first_tag,
       details -> 'tags' ->> 1 AS second_tag,
       details -> 'tags' -> -1 AS last_tag,
       details -> 'tags' -> 5  AS no_tag
FROM shipment
WHERE shipment_id = 5;

-- name: json_null
SELECT shipment_id,
       details -> 'instructions'            AS arrow,
       details ->> 'instructions'           AS double_arrow,
       (details -> 'instructions') IS NULL  AS arrow_is_null,
       details ? 'instructions'             AS has_key
FROM shipment
WHERE shipment_id IN (1, 2, 4, 5)
ORDER BY shipment_id;

-- name: fragile_containment
SELECT count(*) AS fragile
FROM shipment
WHERE details @> '{"fragile": true}';

-- name: fragile_text
SELECT count(*) AS fragile
FROM shipment
WHERE details ->> 'fragile' = 'true';

-- name: fragile_three_ways
SELECT details ->> 'fragile' AS fragile, count(*) AS shipments
FROM shipment
GROUP BY details ->> 'fragile'
ORDER BY fragile;

-- name: containment_rules
SELECT '{"tags": ["ropa", "regalo"], "fragile": true}'::jsonb @> '{"tags": ["regalo"]}' AS sub_array,
       '[1, 2, 3]'::jsonb @> '[3, 1, 1]'                         AS any_order,
       '{"dimensions_cm": {"l": 40, "w": 30}}'::jsonb @> '{"dimensions_cm": {"l": 40}}' AS nested,
       '{"dimensions_cm": {"l": 40, "w": 30}}'::jsonb @> '{"l": 40}' AS not_top_level,
       '{"fragile": true}'::jsonb @> '{}'                        AS empty;

-- name: existence
SELECT count(*) FILTER (WHERE details ? 'insurance')                         AS insured,
       count(*) FILTER (WHERE details ?| ARRAY['insurance', 'fragile'])      AS insured_or_flag,
       count(*) FILTER (WHERE details ?& ARRAY['insurance', 'dimensions_cm']) AS insured_and_dims
FROM shipment;

-- name: weight_text_wrong
SELECT count(*) AS heavy
FROM shipment
WHERE details ->> 'weight_declared' > '9';

-- name: weight_text_examples
SELECT '10' > '9' AS text_cmp, 10 > 9 AS num_cmp,
       '10'::jsonb > '9'::jsonb AS jsonb_cmp;

-- name: weight_numeric_right
SELECT count(*) AS heavy
FROM shipment
WHERE (details ->> 'weight_declared')::numeric > 9;

-- name: weight_jsonb_cmp
SELECT count(*) AS heavy
FROM shipment
WHERE details -> 'weight_declared' > '9';

-- name: weight_disagreement
SELECT count(*) AS shipments
FROM shipment
WHERE ((details ->> 'weight_declared') > '9')
   <> ((details ->> 'weight_declared')::numeric > 9);

-- name: long_packages
SELECT count(*) AS long_packages
FROM shipment
WHERE (details #>> '{dimensions_cm,l}')::integer > 50;

-- name: bad_cast
SELECT ('{"l": "cuarenta"}'::jsonb ->> 'l')::integer;

-- ------------------------------------------------------- de JSON a filas

-- name: tags_count
SELECT tag, count(*) AS shipments
FROM shipment
  CROSS JOIN LATERAL jsonb_array_elements_text(details -> 'tags') AS t(tag)
GROUP BY tag
ORDER BY shipments DESC, tag;

-- name: each_key
SELECT key, value, jsonb_typeof(value) AS type
FROM shipment, jsonb_each(details)
WHERE shipment_id = 12
ORDER BY key;

-- name: keys_count
SELECT k AS key, count(*) AS shipments
FROM shipment, jsonb_object_keys(details) AS k
GROUP BY k
ORDER BY shipments DESC, key;

-- name: events_recordset
SELECT ev.*
FROM tracking_export t,
     jsonb_to_recordset(t.doc -> 'events') AS ev(seq integer, at timestamptz, agency text, kind text)
WHERE (t.doc ->> 'shipment')::integer = 72
ORDER BY ev.seq;

-- ----------------------------------------------------------- SQL/JSON path

-- name: path_query_tags
SELECT shipment_id, jsonb_path_query(details, '$.tags[*]') AS tag
FROM shipment
WHERE shipment_id IN (5, 9)
ORDER BY shipment_id, tag;

-- name: path_filter
SELECT count(*) AS long_packages
FROM shipment
WHERE jsonb_path_exists(details, '$.dimensions_cm ? (@.l > 50)');

-- name: path_operator
SELECT count(*) AS long_packages
FROM shipment
WHERE details @? '$.dimensions_cm ? (@.l > 50)';

-- name: path_predicate
SELECT count(*) AS long_packages
FROM shipment
WHERE details @@ '$.dimensions_cm.l > 50';

-- name: path_vars
SELECT count(*) AS long_packages
FROM shipment
WHERE jsonb_path_exists(details, '$.dimensions_cm ? (@.l > $min)', '{"min": 50}');

-- name: path_returned_where
SELECT doc ->> 'shipment' AS shipment,
       jsonb_path_query_first(doc, '$.events[*] ? (@.kind == "returned").agency') AS returned_at
FROM tracking_export
WHERE doc @? '$.events[*] ? (@.kind == "returned")'
ORDER BY (doc ->> 'shipment')::integer
LIMIT 5;

-- name: path_lax_strict
SELECT jsonb_path_query_array('{"tags": "ropa"}', 'lax $.tags[*]') AS lax_array,
       jsonb_path_query_array('{"a": 1}', 'lax $.tags')            AS lax_missing,
       jsonb_path_exists('{"a": 1}', 'strict $.tags', silent => true) AS strict_silent;

-- name: path_strict_error
SELECT jsonb_path_query('{"a": 1}', 'strict $.tags');

-- name: sqljson_standard
SELECT JSON_VALUE(details, '$.weight_declared' RETURNING integer) AS weight,
       JSON_QUERY(details, '$.tags')                               AS tags,
       JSON_EXISTS(details, '$.insurance')                         AS insured
FROM shipment
WHERE shipment_id = 5;

-- name: json_table
SELECT jt.*
FROM tracking_export t,
     JSON_TABLE(t.doc, '$.events[*]'
       COLUMNS (seq integer PATH '$.seq',
                agency text PATH '$.agency',
                kind text PATH '$.kind')) AS jt
WHERE (t.doc ->> 'shipment')::integer = 72
ORDER BY jt.seq;

-- ---------------------------------------------------------- de filas a JSON

-- name: row_to_json
SELECT row_to_json(a) AS as_json, to_jsonb(a) AS as_jsonb
FROM agency a
WHERE agency_id = 13;

-- name: build_object
SELECT json_build_object('name', name, 'city', city, 'agency_id', agency_id)  AS j,
       jsonb_build_object('name', name, 'city', city, 'agency_id', agency_id) AS jb
FROM agency
WHERE agency_id = 13;

-- name: object_agg
SELECT jsonb_object_agg(status, n) AS by_status
FROM (SELECT status, count(*) AS n FROM shipment GROUP BY status) AS t;

-- name: tracking_doc
SELECT jsonb_build_object(
         'shipment', s.shipment_id,
         'status', s.status,
         'events', jsonb_agg(jsonb_build_object('at', e.at, 'kind', e.kind) ORDER BY e.seq))
       AS doc
FROM shipment s
  JOIN shipment_event e ON e.shipment_id = s.shipment_id
WHERE s.shipment_id = 72
GROUP BY s.shipment_id, s.status;

-- name: agency_routes_wrong
SELECT a.name, jsonb_agg(d.name ORDER BY d.name) AS destinations
FROM agency a
  LEFT JOIN route r ON r.origin_agency_id = a.agency_id
  LEFT JOIN agency d ON d.agency_id = r.dest_agency_id
WHERE a.agency_id IN (10, 29)
GROUP BY a.agency_id, a.name
ORDER BY a.name;

-- name: agency_routes_right
SELECT a.name,
       coalesce(jsonb_agg(d.name ORDER BY d.name) FILTER (WHERE d.agency_id IS NOT NULL), '[]')
         AS destinations
FROM agency a
  LEFT JOIN route r ON r.origin_agency_id = a.agency_id
  LEFT JOIN agency d ON d.agency_id = r.dest_agency_id
WHERE a.agency_id IN (10, 29)
GROUP BY a.agency_id, a.name
ORDER BY a.name;

-- name: export_check
SELECT count(*) AS docs,
       count(*) FILTER (WHERE jsonb_array_length(doc -> 'events') = 0) AS without_events,
       max(jsonb_array_length(doc -> 'events')) AS max_events
FROM tracking_export;

-- ---------------------------------------------------- modificar documentos

-- name: concat_null
SELECT NULL::jsonb || '{"fragile": true}'::jsonb                 AS null_concat,
       coalesce(NULL::jsonb, '{}') || '{"fragile": true}'::jsonb AS with_coalesce;

-- name: modify_ops
SELECT details || '{"fragile": false}'                  AS replace_key,
       details - 'insurance'                            AS remove_key,
       details #- '{dimensions_cm,h}'                   AS remove_path,
       jsonb_set(details, '{dimensions_cm,l}', '45')    AS set_path
FROM shipment
WHERE shipment_id = 12;

-- Semana 8: índices sobre jsonb (un adelanto de la parte 3 del curso).
-- Corre dentro de una transacción que termina en ROLLBACK:
--
--   psql chasqui_w08 -f indexes.sql
--
-- Cada sentencia va precedida de "-- name: nombre"; json.test.mjs corre este
-- mismo texto. EXPLAIN (COSTS OFF) muestra el plan sin los costos estimados.

BEGIN;

-- name: plan_no_index
EXPLAIN (COSTS OFF)
SELECT count(*) FROM shipment WHERE details @> '{"tags": ["medicamentos"]}';

-- name: create_gin
CREATE INDEX shipment_details_gin ON shipment USING gin (details);

-- name: gin_size
SELECT pg_size_pretty(pg_relation_size('shipment_details_gin')) AS gin_size;

-- name: plan_gin_containment
EXPLAIN (COSTS OFF)
SELECT count(*) FROM shipment WHERE details @> '{"tags": ["medicamentos"]}';

-- name: plan_gin_exists
EXPLAIN (COSTS OFF)
SELECT count(*) FROM shipment WHERE details ? 'insurance';

-- name: plan_gin_text
EXPLAIN (COSTS OFF)
SELECT count(*) FROM shipment WHERE details ->> 'fragile' = 'true';

-- name: drop_gin
DROP INDEX shipment_details_gin;

-- name: create_path_ops
CREATE INDEX shipment_details_path_ops ON shipment USING gin (details jsonb_path_ops);

-- name: path_ops_size
SELECT pg_size_pretty(pg_relation_size('shipment_details_path_ops')) AS path_ops_size;

-- name: plan_path_ops_containment
EXPLAIN (COSTS OFF)
SELECT count(*) FROM shipment WHERE details @> '{"tags": ["medicamentos"]}';

-- name: plan_path_ops_exists
EXPLAIN (COSTS OFF)
SELECT count(*) FROM shipment WHERE details ? 'insurance';

-- name: create_expression
CREATE INDEX shipment_insurer_idx ON shipment ((details #>> '{insurance,insurer}'));

-- name: plan_expression
EXPLAIN (COSTS OFF)
SELECT count(*) FROM shipment WHERE details #>> '{insurance,insurer}' = 'Seguros Andes';

-- name: done
ROLLBACK;

-- Semana 8: datos semiestructurados sobre Chasqui.
--
-- 1. Agrega a shipment la columna hipotética details jsonb (no está en el
--    dataset del curso) y la llena con valores que se deducen de cada fila,
--    para que todos obtengan los mismos resultados.
-- 2. Crea la vista shipment_tracking, que arma el seguimiento de cada envío
--    como un documento JSON.
-- 3. Guarda una copia de esos documentos en tracking_export, como si otro
--    sistema los hubiera recibido y guardado tal cual.
--
-- Se carga con make, después de chasqui-load.

ALTER TABLE shipment ADD COLUMN details jsonb;

-- Cada pieza es un objeto o '{}'; || junta objetos. Un envío sin ninguna
-- pieza queda con details NULL (no se registró nada).
UPDATE shipment SET details = NULLIF(
    CASE shipment_id % 6
      WHEN 0 THEN '{"fragile": true}'::jsonb
      WHEN 3 THEN '{"fragile": false}'
      ELSE '{}'
    END
  || CASE WHEN shipment_id % 4 = 0
       THEN jsonb_build_object('dimensions_cm', jsonb_build_object(
              'l', 20 + shipment_id % 61,
              'w', 15 + shipment_id % 31,
              'h', 10 + shipment_id % 23))
       ELSE '{}'
     END
  || CASE
       WHEN shipment_id % 250 = 0 THEN '{"tags": ["medicamentos", "urgente"]}'::jsonb
       WHEN shipment_id % 10 = 0 THEN '{"tags": ["electrónica"]}'
       WHEN shipment_id % 10 = 3 THEN '{"tags": ["documentos"]}'
       WHEN shipment_id % 10 = 5 THEN '{"tags": ["alimentos", "perecible"]}'
       WHEN shipment_id % 10 = 7 THEN '{"tags": ["ropa", "regalo"]}'
       WHEN shipment_id % 10 = 9 THEN '{"tags": ["electrónica", "regalo"]}'
       ELSE '{}'
     END
  || CASE WHEN declared_value >= 1500
       THEN jsonb_build_object('insurance', jsonb_build_object(
              'insurer', (ARRAY['Seguros Inti', 'Seguros Andes', 'Seguros Pacha'])[shipment_id % 3 + 1],
              'policy', 'POL-' || shipment_id))
       ELSE '{}'
     END
  || CASE WHEN shipment_id % 5 = 0
       THEN jsonb_build_object('weight_declared', round(weight_kg)::integer)
       ELSE '{}'
     END
  || CASE shipment_id % 8
       WHEN 1 THEN '{"instructions": "llamar antes de entregar"}'::jsonb
       WHEN 5 THEN '{"instructions": null}'
       ELSE '{}'
     END,
  '{}'::jsonb);

-- El seguimiento de un envío como un solo documento. Los eventos van en un
-- arreglo ordenado por seq.
CREATE VIEW shipment_tracking AS
SELECT s.shipment_id,
       jsonb_build_object(
         'shipment', s.shipment_id,
         'status', s.status,
         'from', o.name,
         'to', d.name,
         'events', (SELECT coalesce(jsonb_agg(jsonb_build_object(
                                      'seq', e.seq,
                                      'at', e.at,
                                      'agency', a.name,
                                      'kind', e.kind) ORDER BY e.seq), '[]')
                    FROM shipment_event e
                      JOIN agency a ON a.agency_id = e.agency_id
                    WHERE e.shipment_id = s.shipment_id)
       ) AS doc
FROM shipment s
  JOIN agency o ON o.agency_id = s.origin_agency_id
  JOIN agency d ON d.agency_id = s.dest_agency_id;

-- Los documentos "exportados": una tabla con una sola columna jsonb.
CREATE TABLE tracking_export (
  export_id serial PRIMARY KEY,
  doc       jsonb NOT NULL
);

INSERT INTO tracking_export (doc)
SELECT doc FROM shipment_tracking ORDER BY shipment_id;

ANALYZE shipment;
ANALYZE tracking_export;

-- Semana 8: cambiar documentos jsonb, restricciones sobre ellos e índices.
-- Todo corre dentro de una transacción que termina en ROLLBACK, así que se
-- puede cargar las veces que se quiera:
--
--   psql chasqui_w08 -f modify.sql
--
-- Cada sentencia va precedida de "-- name: nombre"; json.test.mjs corre este
-- mismo texto, también dentro de una transacción.

\pset null '∅'
BEGIN;

-- ------------------------------------------------------ || con un NULL

-- name: update_null_details
UPDATE shipment
SET details = details || '{"fragile": true}'
WHERE shipment_id = 2
RETURNING shipment_id, details;

-- name: update_coalesce
UPDATE shipment
SET details = coalesce(details, '{}') || '{"fragile": true}'
WHERE shipment_id = 2
RETURNING shipment_id, details;

-- name: update_set_path
UPDATE shipment
SET details = jsonb_set(details, '{dimensions_cm,l}', '45')
WHERE shipment_id = 12
RETURNING details -> 'dimensions_cm' AS dims;

-- ------------------------------------------------------- restricciones

-- name: check_object
ALTER TABLE shipment
  ADD CONSTRAINT shipment_details_object_ck
  CHECK (details IS NULL OR jsonb_typeof(details) = 'object');

-- name: check_fragile
ALTER TABLE shipment
  ADD CONSTRAINT shipment_details_fragile_ck
  CHECK (NOT details ? 'fragile' OR jsonb_typeof(details -> 'fragile') = 'boolean');

-- name: savepoint_1
SAVEPOINT before_bad_update;

-- name: bad_fragile
UPDATE shipment
SET details = details || '{"fragile": "sí"}'
WHERE shipment_id = 12;

-- name: rollback_1
ROLLBACK TO SAVEPOINT before_bad_update;

-- Nada impide guardar en details una agencia que no existe: no hay clave
-- foránea dentro de un documento.
-- name: no_foreign_key
UPDATE shipment
SET details = details || '{"dest_agency_id": 999}'
WHERE shipment_id = 12
RETURNING details ->> 'dest_agency_id' AS dest_agency_id,
          EXISTS (SELECT 1 FROM agency WHERE agency_id = 999) AS agency_exists;

-- name: done
ROLLBACK;

-- Semana 8: los problemas de repaso del capítulo, sobre Chasqui.
-- Cada consulta va precedida de "-- name: nombre"; review.test.mjs corre
-- este mismo texto.
--
--   psql chasqui_w08 -f review.sql

\pset null '∅'

-- Problema 1: envíos de salida desde el 20 de junio, por agencia, incluidas
-- las agencias que no tuvieron ninguno.

-- name: outer_where
SELECT a.name, count(s.shipment_id) AS shipments
FROM agency a
  LEFT JOIN shipment s ON s.origin_agency_id = a.agency_id
WHERE s.created_at >= '2026-06-20'
GROUP BY a.agency_id, a.name
ORDER BY shipments, a.name
LIMIT 3;

-- name: outer_on
SELECT a.name, count(s.shipment_id) AS shipments
FROM agency a
  LEFT JOIN shipment s ON s.origin_agency_id = a.agency_id
                      AND s.created_at >= '2026-06-20'
GROUP BY a.agency_id, a.name
ORDER BY shipments, a.name
LIMIT 3;

-- name: outer_count_star
SELECT a.name, count(*) AS shipments
FROM agency a
  LEFT JOIN shipment s ON s.origin_agency_id = a.agency_id
                      AND s.created_at >= '2026-06-20'
GROUP BY a.agency_id, a.name
ORDER BY shipments, a.name
LIMIT 3;

-- Problema 2: ¿los datos contradicen la dependencia funcional
-- origin_agency_id, dest_agency_id, weight_kg → price?

-- name: fd_refuted
SELECT count(*) AS counterexamples
FROM (SELECT origin_agency_id, dest_agency_id, weight_kg
      FROM shipment
      GROUP BY origin_agency_id, dest_agency_id, weight_kg
      HAVING count(DISTINCT price) > 1) AS t;

-- name: fd_not_refuted
SELECT count(*) AS counterexamples
FROM (SELECT origin_agency_id, dest_agency_id, weight_kg, declared_value
      FROM shipment
      GROUP BY origin_agency_id, dest_agency_id, weight_kg, declared_value
      HAVING count(DISTINCT price) > 1) AS t;

-- Problema 3: el valor declarado promedio.

-- name: avg_nulls
SELECT count(*)                          AS shipments,
       count(declared_value)             AS declared,
       round(avg(declared_value), 2)     AS avg_declared,
       round(sum(declared_value) / count(*), 2) AS sum_over_all,
       round(avg(coalesce(declared_value, 0)), 2) AS avg_zero
FROM shipment;

-- name: avg_empty
SELECT count(*) AS shipments, sum(price) AS revenue, avg(price) AS avg_price
FROM shipment
WHERE origin_agency_id = 29;

// Lee un archivo .sql con consultas marcadas "-- name: nombre" y devuelve un
// objeto { nombre: texto }. Así las pruebas corren el mismo texto que el
// archivo que se muestra en clase.
import { readFileSync } from 'node:fs';

export function namedQueries(file) {
  const text = readFileSync(new URL(file, import.meta.url), 'utf8');
  const parts = text.split(/^-- name: (\w+)\n/m);
  const queries = {};
  for (let i = 1; i < parts.length; i += 2) {
    queries[parts[i]] = parts[i + 1].trim();
  }
  return queries;
}

// El seguimiento de un envío como documento JSON, leído desde Node.js.
//
//   node tracking.mjs 72        imprime el documento del envío 72
//
// pg convierte las columnas json y jsonb en objetos de JavaScript: la
// aplicación no llama a JSON.parse.
import pg from 'pg';

export async function trackingDoc(db, shipmentId) {
  const { rows } = await db.query(
    'SELECT doc FROM shipment_tracking WHERE shipment_id = $1',
    [shipmentId],
  );
  return rows.length === 0 ? null : rows[0].doc;
}

// Envíos cuyo details contiene el objeto pattern (un objeto de JavaScript).
// pg manda el objeto como texto JSON; ::jsonb lo convierte en el servidor.
export async function shipmentsMatching(db, pattern) {
  const { rows } = await db.query(
    `SELECT shipment_id FROM shipment
     WHERE details @> $1::jsonb
     ORDER BY shipment_id`,
    [JSON.stringify(pattern)],
  );
  return rows.map((r) => r.shipment_id);
}

if (import.meta.url === `file://${process.argv[1]}`) {
  const pool = new pg.Pool();
  try {
    const doc = await trackingDoc(pool, Number(process.argv[2] ?? 72));
    console.log(JSON.stringify(doc, null, 2));
  } finally {
    await pool.end();
  }
}

// Pruebas de tracking.mjs: lo que pg entrega cuando una columna es jsonb.
import { test, before, after } from 'node:test';
import assert from 'node:assert/strict';
import pg from 'pg';
import { trackingDoc, shipmentsMatching } from './tracking.mjs';

let pool;
before(() => {
  pool = new pg.Pool();
});
after(() => pool.end());

test('trackingDoc devuelve un objeto de JavaScript, no texto', async () => {
  const doc = await trackingDoc(pool, 72);
  assert.equal(typeof doc, 'object');
  assert.equal(doc.shipment, 72);
  assert.equal(doc.status, 'returned');
  assert.deepEqual(doc.events.map((e) => e.kind), ['received', 'departed', 'arrived', 'returned']);
  assert.equal(await trackingDoc(pool, 999999), null);
});

test('dentro de un jsonb, un número llega como number y una fecha como string', async () => {
  const doc = await trackingDoc(pool, 72);
  assert.equal(typeof doc.events[0].seq, 'number');
  assert.equal(typeof doc.events[0].at, 'string');
  assert.equal(doc.events[0].at, '2026-02-15T18:18:00-05:00');
  // La misma fecha como columna timestamptz sí llega como Date; un numeric, como string.
  const { rows: [r] } = await pool.query(
    'SELECT at, (SELECT price FROM shipment WHERE shipment_id = 72) AS price FROM shipment_event WHERE shipment_id = 72 AND seq = 1',
  );
  assert.ok(r.at instanceof Date);
  assert.equal(typeof r.price, 'string');
});

test('shipmentsMatching manda el patrón como JSON y usa @>', async () => {
  const ids = await shipmentsMatching(pool, { tags: ['medicamentos'] });
  assert.equal(ids.length, 48);
  assert.deepEqual(ids.slice(0, 3), [250, 500, 750]);
});

test('un objeto como parámetro se manda como JSON; un arreglo, no', async () => {
  const { rows: [o] } = await pool.query('SELECT $1::jsonb AS j', [{ tags: ['ropa'] }]);
  assert.deepEqual(o.j, { tags: ['ropa'] });
  // pg convierte un arreglo de JavaScript en un arreglo de PostgreSQL: {"ropa","regalo"}.
  await assert.rejects(pool.query('SELECT $1::jsonb AS j', [['ropa', 'regalo']]), {
    code: '22P02', message: 'invalid input syntax for type json',
  });
  const { rows: [a] } = await pool.query('SELECT $1::jsonb AS j', [JSON.stringify(['ropa', 'regalo'])]);
  assert.deepEqual(a.j, ['ropa', 'regalo']);
});

test('un número de jsonb más grande que 2^53 pierde precisión en JavaScript', async () => {
  const { rows: [r] } = await pool.query(`SELECT '{"n": 12345678901234567890}'::jsonb AS j, '{"n": 12345678901234567890}'::jsonb ->> 'n' AS t`);
  assert.notEqual(String(r.j.n), '12345678901234567890');
  assert.equal(r.t, '12345678901234567890');
});

// Pruebas de los ejemplos del capítulo: corren el texto de examples.sql y
// comprueban los resultados que se muestran en los apuntes. Base de datos
// chasqui_w08, con json-setup.sql ya cargado (make lo carga).
import { test, before, after } from 'node:test';
import assert from 'node:assert/strict';
import pg from 'pg';
import { namedQueries } from './sqlfile.mjs';

const q = namedQueries('./examples.sql');
let pool;
before(() => {
  pool = new pg.Pool();
});
after(() => pool.end());

const all = async (name) => (await pool.query(q[name])).rows;
const one = async (name) => (await all(name))[0];

// ---------------------------------------------------------------- json y jsonb

test('json guarda el texto; jsonb lo normaliza y se queda con la última clave', async () => {
  // pg convierte los dos en objetos: se compara el texto que devuelve el servidor.
  const [row] = (await pool.query(
    `SELECT as_json::text AS j, as_jsonb::text AS jb FROM (${q.json_vs_jsonb.replace(/;$/, '')}) t`,
  )).rows;
  assert.deepEqual(row, { j: '{"b": 1,   "a": 2, "a": 3}', jb: '{"a": 3, "b": 1}' });
  const [parsed] = await all('json_vs_jsonb');
  assert.deepEqual(parsed.as_jsonb, { a: 3, b: 1 });
});

test('igualdad de jsonb: el orden de las claves no importa, el de un arreglo sí', async () => {
  assert.deepEqual(await one('jsonb_equality'), { same_object: true, same_array: false, same_number: true });
});

test('json no tiene operador de igualdad', async () => {
  await assert.rejects(pool.query(`SELECT '{"a": 1}'::json = '{"a": 1}'::json`), { code: '42883' });
});

test('jsonb rechaza un texto que no es JSON', async () => {
  await assert.rejects(pool.query(`SELECT '{fragile: true}'::jsonb`), {
    code: '22P02', message: 'invalid input syntax for type json',
  });
});

test('jsonb_typeof distingue los seis tipos de JSON', async () => {
  assert.deepEqual(await one('jsonb_typeof'), {
    object: 'object', array: 'array', string: 'string', number: 'number', boolean: 'boolean', null: 'null',
  });
});

// ---------------------------------------------------------------- operadores

test('details: cada envío trae claves distintas, o nada', async () => {
  const rows = await all('details_sample');
  assert.deepEqual(rows.map((r) => r.shipment_id), [1, 2, 5, 12]);
  assert.equal(rows[1].details, null);
  assert.deepEqual(rows[2].details, { tags: ['alimentos', 'perecible'], instructions: null, weight_declared: 11 });
});

test('-> devuelve jsonb, ->> devuelve text, #> y #>> siguen un camino', async () => {
  const row = await one('arrows');
  assert.deepEqual(row.dims, { h: 22, l: 32, w: 27 });
  assert.equal(row.l_jsonb, 32);
  assert.equal(row.l_path, 32);
  assert.equal(row.l_text, '32');
  assert.equal(row.fragile_text, 'true');
  assert.equal(row.tags, null);
  assert.deepEqual(await one('arrow_types'), { arrow: 'jsonb', double_arrow: 'text' });
});

test('índices de arreglo: desde 0, negativos desde el final, fuera de rango NULL', async () => {
  assert.deepEqual(await one('array_index'), {
    first_tag: 'alimentos', second_tag: 'perecible', last_tag: 'perecible', no_tag: null,
  });
});

test('null de JSON frente a NULL de SQL', async () => {
  const rows = await pool.query(q.json_null.replace(/details -> 'instructions'(\s+)AS arrow,/,
    "(details -> 'instructions')::text$1AS arrow,"));
  assert.deepEqual(rows.rows, [
    { shipment_id: 1, arrow: '"llamar antes de entregar"', double_arrow: 'llamar antes de entregar', arrow_is_null: false, has_key: true },
    { shipment_id: 2, arrow: null, double_arrow: null, arrow_is_null: true, has_key: null },
    { shipment_id: 4, arrow: null, double_arrow: null, arrow_is_null: true, has_key: false },
    { shipment_id: 5, arrow: 'null', double_arrow: null, arrow_is_null: false, has_key: true },
  ]);
});

test('frágiles: @> y ->> dan lo mismo; la mayoría no dice nada', async () => {
  assert.deepEqual(await one('fragile_containment'), { fragile: '2000' });
  assert.deepEqual(await one('fragile_text'), { fragile: '2000' });
  assert.deepEqual(await all('fragile_three_ways'), [
    { fragile: 'false', shipments: '2000' },
    { fragile: 'true', shipments: '2000' },
    { fragile: null, shipments: '8000' },
  ]);
});

test('reglas de la contención @>', async () => {
  assert.deepEqual(await one('containment_rules'), {
    sub_array: true, any_order: true, nested: true, not_top_level: false, empty: true,
  });
});

test('existencia de claves: ?, ?| y ?&', async () => {
  assert.deepEqual(await one('existence'), { insured: '3571', insured_or_flag: '6386', insured_and_dims: '907' });
});

test('comparar el texto de ->> con > da otro resultado que comparar números', async () => {
  assert.deepEqual(await one('weight_text_wrong'), { heavy: '78' });
  assert.deepEqual(await one('weight_text_examples'), { text_cmp: false, num_cmp: true, jsonb_cmp: true });
  assert.deepEqual(await one('weight_numeric_right'), { heavy: '1595' });
  assert.deepEqual(await one('weight_jsonb_cmp'), { heavy: '1595' });
  assert.deepEqual(await one('weight_disagreement'), { shipments: '1517' });
  assert.deepEqual(await one('long_packages'), { long_packages: '1474' });
});

test('una conversión que no se puede hacer es un error, no un NULL', async () => {
  await assert.rejects(all('bad_cast'), { code: '22P02', message: 'invalid input syntax for type integer: "cuarenta"' });
});

// ---------------------------------------------------------- de JSON a filas

test('jsonb_array_elements_text: una fila por etiqueta', async () => {
  const rows = await all('tags_count');
  assert.equal(rows.length, 8);
  assert.deepEqual(rows[0], { tag: 'regalo', shipments: '2400' });
  assert.deepEqual(rows.at(-1), { tag: 'urgente', shipments: '48' });
});

test('jsonb_each y jsonb_object_keys', async () => {
  const rows = await all('each_key');
  assert.deepEqual(rows.map((r) => [r.key, r.type]), [
    ['dimensions_cm', 'object'], ['fragile', 'boolean'], ['insurance', 'object'],
  ]);
  const keys = await all('keys_count');
  assert.deepEqual(keys.map((r) => [r.key, r.shipments]), [
    ['tags', '6000'], ['fragile', '4000'], ['insurance', '3571'],
    ['dimensions_cm', '3000'], ['instructions', '3000'], ['weight_declared', '2400'],
  ]);
});

test('jsonb_to_recordset y JSON_TABLE devuelven los eventos del envío 72 como filas', async () => {
  const rows = await all('events_recordset');
  assert.deepEqual(rows.map((r) => r.kind), ['received', 'departed', 'arrived', 'returned']);
  assert.ok(rows[0].at instanceof Date);
  const jt = await all('json_table');
  assert.deepEqual(jt.map((r) => [r.seq, r.kind]), rows.map((r) => [r.seq, r.kind]));
});

// ---------------------------------------------------------------- SQL/JSON path

test('jsonb_path_query devuelve un valor por fila', async () => {
  const rows = await all('path_query_tags');
  assert.deepEqual(rows.map((r) => [r.shipment_id, r.tag]), [
    [5, 'alimentos'], [5, 'perecible'], [9, 'electrónica'], [9, 'regalo'],
  ]);
});

test('el filtro de un camino, con función, con @?, con @@ y con variables', async () => {
  for (const name of ['path_filter', 'path_operator', 'path_predicate', 'path_vars']) {
    assert.deepEqual(await one(name), { long_packages: '1474' }, name);
  }
});

test('envíos devueltos y dónde', async () => {
  const rows = await all('path_returned_where');
  assert.deepEqual(rows[1], { shipment: '72', returned_at: 'Lima - La Victoria' });
  const [{ n }] = (await pool.query(
    `SELECT count(*) AS n FROM tracking_export WHERE doc @? '$.events[*] ? (@.kind == "returned")'`,
  )).rows;
  assert.equal(n, '319');
});

test('modo lax y modo strict', async () => {
  assert.deepEqual(await one('path_lax_strict'), { lax_array: ['ropa'], lax_missing: [], strict_silent: null });
  await assert.rejects(all('path_strict_error'), { message: 'JSON object does not contain key "tags"' });
});

test('JSON_VALUE, JSON_QUERY y JSON_EXISTS del estándar', async () => {
  assert.deepEqual(await one('sqljson_standard'), { weight: 11, tags: ['alimentos', 'perecible'], insured: false });
});

// ------------------------------------------------------------ de filas a JSON

test('row_to_json y to_jsonb', async () => {
  const [row] = (await pool.query(
    'SELECT row_to_json(a)::text AS j, to_jsonb(a)::text AS jb FROM agency a WHERE agency_id = 13',
  )).rows;
  assert.equal(row.j, '{"agency_id":13,"name":"Ica - Centro","city":"Ica","region":"Ica"}');
  assert.equal(row.jb, '{"city": "Ica", "name": "Ica - Centro", "region": "Ica", "agency_id": 13}');
  assert.ok(q.row_to_json.includes('row_to_json(a)'));
});

test('json_build_object conserva el orden; jsonb_build_object no', async () => {
  const [row] = (await pool.query(`SELECT j::text, jb::text FROM (${q.build_object.replace(/;$/, '')}) t`)).rows;
  assert.deepEqual(row, {
    j: '{"name" : "Ica - Centro", "city" : "Ica", "agency_id" : 13}',
    jb: '{"city": "Ica", "name": "Ica - Centro", "agency_id": 13}',
  });
});

test('jsonb_object_agg arma un objeto con una clave por grupo', async () => {
  assert.deepEqual((await one('object_agg')).by_status, {
    returned: 319, delivered: 11115, in_transit: 66, registered: 500,
  });
});

test('el seguimiento del envío 72 como un documento', async () => {
  const { doc } = await one('tracking_doc');
  assert.equal(doc.status, 'returned');
  assert.deepEqual(doc.events.map((e) => e.kind), ['received', 'departed', 'arrived', 'returned']);
  assert.equal(doc.events[0].at, '2026-02-15T18:18:00-05:00');
});

test('jsonb_agg sobre un LEFT JOIN: [null] sin FILTER, [] con FILTER y coalesce', async () => {
  assert.deepEqual(await all('agency_routes_wrong'), [
    { name: 'Iquitos - Centro', destinations: [null] },
    { name: 'Tumbes - Centro', destinations: ['Piura - Castilla'] },
  ]);
  assert.deepEqual(await all('agency_routes_right'), [
    { name: 'Iquitos - Centro', destinations: [] },
    { name: 'Tumbes - Centro', destinations: ['Piura - Castilla'] },
  ]);
});

test('tracking_export tiene un documento por envío', async () => {
  assert.deepEqual(await one('export_check'), { docs: '12000', without_events: '0', max_events: 4 });
});

test('jsonb || con NULL es NULL', async () => {
  assert.deepEqual(await one('concat_null'), { null_concat: null, with_coalesce: { fragile: true } });
});

test('||, -, #- y jsonb_set', async () => {
  const row = await one('modify_ops');
  assert.equal(row.replace_key.fragile, false);
  assert.equal(row.remove_key.insurance, undefined);
  assert.deepEqual(row.remove_path.dimensions_cm, { l: 32, w: 27 });
  assert.deepEqual(row.set_path.dimensions_cm, { h: 22, l: 45, w: 27 });
});

// Pruebas de modify.sql (cambios, restricciones) e indexes.sql (índices y
// planes). Cada archivo corre en un solo client, dentro de una transacción
// que termina en ROLLBACK: la base de datos queda como estaba.
import { test, before, after } from 'node:test';
import assert from 'node:assert/strict';
import pg from 'pg';
import { namedQueries } from './sqlfile.mjs';

const modify = namedQueries('./modify.sql');
const indexes = namedQueries('./indexes.sql');
let pool;
before(() => {
  pool = new pg.Pool();
});
after(() => pool.end());

async function inRollback(fn) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    return await fn(client);
  } finally {
    await client.query('ROLLBACK');
    client.release();
  }
}

const plan = async (client, text) =>
  (await client.query(text)).rows.map((r) => r['QUERY PLAN']).join('\n');

test('details || ... sobre un details NULL deja NULL; con coalesce, no', async () => {
  await inRollback(async (client) => {
    const a = await client.query(modify.update_null_details);
    assert.deepEqual(a.rows, [{ shipment_id: 2, details: null }]);
    const b = await client.query(modify.update_coalesce);
    assert.deepEqual(b.rows, [{ shipment_id: 2, details: { fragile: true } }]);
    const c = await client.query(modify.update_set_path);
    assert.deepEqual(c.rows, [{ dims: { h: 22, l: 45, w: 27 } }]);
  });
});

test('CHECK sobre jsonb: rechaza "fragile" que no es booleano', async () => {
  await inRollback(async (client) => {
    await client.query(modify.check_object);
    await client.query(modify.check_fragile);
    await client.query(modify.savepoint_1);
    await assert.rejects(client.query(modify.bad_fragile), {
      code: '23514', constraint: 'shipment_details_fragile_ck',
    });
    await client.query(modify.rollback_1);
    // Pero nada impide guardar una agencia que no existe.
    const r = await client.query(modify.no_foreign_key);
    assert.deepEqual(r.rows, [{ dest_agency_id: '999', agency_exists: false }]);
  });
});

test('GIN (jsonb_ops) sirve para @> y ?, no para ->>', async () => {
  await inRollback(async (client) => {
    assert.match(await plan(client, indexes.plan_no_index), /Seq Scan on shipment/);
    await client.query(indexes.create_gin);
    assert.match(await plan(client, indexes.plan_gin_containment), /Bitmap Index Scan on shipment_details_gin/);
    assert.match(await plan(client, indexes.plan_gin_exists), /Bitmap Index Scan on shipment_details_gin/);
    assert.match(await plan(client, indexes.plan_gin_text), /Seq Scan on shipment/);
  });
});

test('jsonb_path_ops sirve para @> pero no para ?; un índice de expresión sirve para una clave', async () => {
  await inRollback(async (client) => {
    await client.query(indexes.create_path_ops);
    assert.match(await plan(client, indexes.plan_path_ops_containment), /Bitmap Index Scan on shipment_details_path_ops/);
    assert.match(await plan(client, indexes.plan_path_ops_exists), /Seq Scan on shipment/);
    await client.query(indexes.create_expression);
    assert.match(await plan(client, indexes.plan_expression), /Bitmap Index Scan on shipment_insurer_idx/);
  });
});

test('el GIN con jsonb_path_ops es más chico', async () => {
  await inRollback(async (client) => {
    await client.query(indexes.create_gin);
    await client.query(indexes.create_path_ops);
    const { rows: [r] } = await client.query(
      `SELECT pg_relation_size('shipment_details_gin') > pg_relation_size('shipment_details_path_ops') AS smaller`,
    );
    assert.equal(r.smaller, true);
  });
});

test('tamaños de los dos GIN: 440 kB y 304 kB', async () => {
  await inRollback(async (client) => {
    await client.query(indexes.create_gin);
    assert.deepEqual((await client.query(indexes.gin_size)).rows, [{ gin_size: '440 kB' }]);
    await client.query(indexes.drop_gin);
    await client.query(indexes.create_path_ops);
    assert.deepEqual((await client.query(indexes.path_ops_size)).rows, [{ path_ops_size: '304 kB' }]);
  });
});

// Pruebas de los problemas de repaso (review.sql) sobre Chasqui.
import { test, before, after } from 'node:test';
import assert from 'node:assert/strict';
import pg from 'pg';
import { namedQueries } from './sqlfile.mjs';

const q = namedQueries('./review.sql');
let pool;
before(() => {
  pool = new pg.Pool();
});
after(() => pool.end());

const all = async (name) => (await pool.query(q[name])).rows;

test('LEFT JOIN: la condición en WHERE pierde la agencia sin envíos; en ON, no', async () => {
  assert.deepEqual(await all('outer_where'), [
    { name: 'Huancavelica - Centro', shipments: '1' },
    { name: 'Pucallpa - Centro', shipments: '2' },
    { name: 'Abancay - Centro', shipments: '3' },
  ]);
  assert.deepEqual((await all('outer_on'))[0], { name: 'Iquitos - Centro', shipments: '0' });
  // count(*) cuenta la fila de NULL que agrega el LEFT JOIN.
  assert.deepEqual((await all('outer_count_star'))[1], { name: 'Iquitos - Centro', shipments: '1' });
  const where = await pool.query(q.outer_where.replace(/LIMIT 3;$/, ''));
  const on = await pool.query(q.outer_on.replace(/LIMIT 3;$/, ''));
  assert.deepEqual([where.rowCount, on.rowCount], [28, 29]);
});

test('los datos refutan una dependencia, pero no pueden demostrar otra', async () => {
  assert.deepEqual(await all('fd_refuted'), [{ counterexamples: '1083' }]);
  assert.deepEqual(await all('fd_not_refuted'), [{ counterexamples: '0' }]);
});

test('avg ignora los NULL; sum y avg sobre cero filas son NULL; count es 0', async () => {
  assert.deepEqual(await all('avg_nulls'), [{
    shipments: '12000', declared: '7136', avg_declared: '1512.33', sum_over_all: '899.33', avg_zero: '899.33',
  }]);
  assert.deepEqual(await all('avg_empty'), [{ shipments: '0', revenue: null, avg_price: null }]);
});

# Semana 8: datos semiestructurados, JSON y jsonb en PostgreSQL.
# Se usa dentro del contenedor del curso.
#
#   make          crea la base de datos chasqui_w08 con los datos de ejemplo,
#                 le agrega la columna details y los documentos de seguimiento
#   make test     la vuelve a crear y corre las pruebas (*.test.mjs)
#   make clean    borra chasqui_w08

DB = chasqui_w08
PSQL = psql -X -q -v ON_ERROR_STOP=1 -d $(DB)

.PHONY: all test clean

all:
	chasqui-load $(DB)
	$(PSQL) -f json-setup.sql

# Algunas pruebas crean índices dentro de una transacción: se corren una
# después de la otra.
test: all
	PGDATABASE=$(DB) node --test --test-concurrency=1 --test-reporter=spec

clean:
	dropdb --if-exists $(DB)