-- Semana 2, sesión A: cada expresión del álgebra relacional de los apuntes
-- y su traducción a SQL. Se corre en psql con  \i algebra.sql
-- (base de datos chasqui_w02, la crea  make).
--
-- Para que la traducción respete la semántica de conjuntos del álgebra se
-- usa SELECT DISTINCT cuando la proyección puede repetir tuplas. Todas las
-- consultas que se muestran llevan ORDER BY: sin él, el orden de las filas
-- no está definido.

-- A1. Selección: σ kind = 'bus' ∧ capacity_kg > 5000 (vehicle)
SELECT *
FROM vehicle
WHERE kind = 'bus' AND capacity_kg > 5000
ORDER BY vehicle_id;

-- A2. Proyección: Π city (customer). Cada ciudad una sola vez.
SELECT DISTINCT city
FROM customer
ORDER BY city;

-- A2b. Cuántas filas tiene cada versión: sin DISTINCT (multiconjunto) y con DISTINCT.
SELECT (SELECT count(*) FROM customer)              AS select_city,
       (SELECT count(DISTINCT city) FROM customer) AS proyeccion_city;

-- A3. Composición: Π name (σ region = 'Puno' (agency))
SELECT name
FROM agency
WHERE region = 'Puno'
ORDER BY name;

-- A4. Producto cartesiano: |agency × vehicle| = |agency| · |vehicle|
SELECT (SELECT count(*) FROM agency)       AS agencias,
       (SELECT count(*) FROM vehicle)      AS vehiculos,
       (SELECT count(*) FROM agency, vehicle) AS producto;

-- A4b. Un producto pequeño:
--      Π name, plate (σ region = 'Puno' (agency) × σ kind = 'van' (vehicle))
SELECT a.name, v.plate
FROM agency AS a, vehicle AS v
WHERE a.region = 'Puno' AND v.kind = 'van'
ORDER BY a.name, v.plate;

-- A5. Join θ: los envíos de más de 140 kg con el nombre del remitente.
--     Π shipment_id, first_name, last_name, weight_kg
--       (σ weight_kg > 140 (shipment ⋈ sender_id = customer_id customer))
SELECT s.shipment_id, c.first_name, c.last_name, s.weight_kg
FROM shipment AS s, customer AS c
WHERE s.sender_id = c.customer_id
  AND s.weight_kg > 140
ORDER BY s.weight_kg DESC, s.shipment_id
LIMIT 5;

-- A5b. Tamaño del join frente al tamaño del producto.
SELECT (SELECT count(*) FROM shipment, customer) AS producto,
       (SELECT count(*) FROM shipment AS s, customer AS c
         WHERE s.sender_id = c.customer_id)     AS join_theta;

-- A5c. Si la clave foránea admite NULL, el join pierde tuplas: los envíos
--      sin viaje asignado (trip_id NULL) no encuentran pareja.
--      |shipment ⋈ shipment.trip_id = trip.trip_id trip|
SELECT (SELECT count(*) FROM shipment)                    AS envios,
       (SELECT count(*) FROM shipment AS s, trip AS t
         WHERE s.trip_id = t.trip_id)                     AS con_viaje,
       (SELECT count(*) FROM shipment WHERE trip_id IS NULL) AS sin_viaje;

-- A6. Natural join: trip ⋈ vehicle (el único atributo común es vehicle_id).
SELECT t.trip_id, t.vehicle_id, v.plate, v.kind
FROM trip AS t, vehicle AS v
WHERE t.vehicle_id = v.vehicle_id
ORDER BY t.trip_id
LIMIT 3;

-- A6b. El natural join que nadie pidió: customer ⋈ agency iguala city.
SELECT count(*) AS cliente_agencia_misma_ciudad
FROM customer AS c, agency AS a
WHERE c.city = a.city;

-- A6c. Sin atributos en común, el natural join es el producto cartesiano:
--      route y agency no comparten ningún nombre de columna.
SELECT (SELECT count(*) FROM route NATURAL JOIN agency) AS natural_join,
       (SELECT count(*) FROM route, agency)             AS producto;

-- A7. Unión: clientes que enviaron o recibieron algo.
--     Π sender_id (shipment) ∪ Π recipient_id (shipment)
SELECT count(*) AS union_clientes
FROM (SELECT sender_id FROM shipment
      UNION
      SELECT recipient_id FROM shipment) AS u;

-- A8. Diferencia: agencias desde las que no sale ninguna ruta.
--     Π agency_id (agency) − Π origin_agency_id (route)
SELECT agency_id FROM agency
EXCEPT
SELECT origin_agency_id FROM route
ORDER BY agency_id;

-- A8b. Una diferencia vacía: agencias que son origen de alguna ruta y
--      nunca destino. Π origin_agency_id (route) − Π dest_agency_id (route)
SELECT origin_agency_id FROM route
EXCEPT
SELECT dest_agency_id FROM route
ORDER BY origin_agency_id;

-- A8c. Remitentes que nunca recibieron un envío.
--      Π sender_id (shipment) − Π recipient_id (shipment)
SELECT count(*) AS solo_remitentes
FROM (SELECT sender_id FROM shipment
      EXCEPT
      SELECT recipient_id FROM shipment) AS d;

-- A8d. Clientes que no aparecen en ningún envío.
--      Π customer_id (customer) − (Π sender_id (shipment) ∪ Π recipient_id (shipment))
SELECT count(*) AS sin_envios
FROM (SELECT customer_id FROM customer
      EXCEPT
      (SELECT sender_id FROM shipment
       UNION
       SELECT recipient_id FROM shipment)) AS d;

-- A9. Intersección: clientes que enviaron y también recibieron.
--     Π sender_id (shipment) ∩ Π recipient_id (shipment)
SELECT count(*) AS envian_y_reciben
FROM (SELECT sender_id FROM shipment
      INTERSECT
      SELECT recipient_id FROM shipment) AS i;

-- A9b. La misma intersección escrita con dos diferencias: r ∩ s = r − (r − s)
SELECT count(*) AS envian_y_reciben
FROM (SELECT sender_id FROM shipment
      EXCEPT
      (SELECT sender_id FROM shipment
       EXCEPT
       SELECT recipient_id FROM shipment)) AS i;

-- A10. Renombramiento: rutas que tienen su ruta de vuelta.
--      ρ r1 (route) ⋈ r1.origin_agency_id = r2.dest_agency_id
--                     ∧ r1.dest_agency_id = r2.origin_agency_id ρ r2 (route)
SELECT count(*) AS pares_ordenados
FROM route AS r1, route AS r2
WHERE r1.origin_agency_id = r2.dest_agency_id
  AND r1.dest_agency_id = r2.origin_agency_id;

-- A10b. Cada par una sola vez (r1.route_id < r2.route_id).
SELECT r1.route_id AS ida, r2.route_id AS vuelta,
       r1.origin_agency_id, r1.dest_agency_id
FROM route AS r1, route AS r2
WHERE r1.origin_agency_id = r2.dest_agency_id
  AND r1.dest_agency_id = r2.origin_agency_id
  AND r1.route_id < r2.route_id
ORDER BY r1.route_id
LIMIT 5;

-- A11. Asignación: rutas entre dos agencias de la misma región.
--      o ← ρ o (agency)
--      d ← ρ d (agency)
--      ro ← route ⋈ route.origin_agency_id = o.agency_id o
--      Π route_id, o.name, d.name, distance_km
--        (σ o.region = d.region (ro ⋈ route.dest_agency_id = d.agency_id d))
SELECT r.route_id, o.name AS origen, d.name AS destino, r.distance_km
FROM route AS r, agency AS o, agency AS d
WHERE r.origin_agency_id = o.agency_id
  AND r.dest_agency_id = d.agency_id
  AND o.region = d.region
ORDER BY r.route_id;

-- A12. Equivalencia: σ region = 'Cusco' (route ⋈θ agency)
--                  = route ⋈θ σ region = 'Cusco' (agency),
--      con θ: origin_agency_id = agency_id.
SELECT r.route_id, r.dest_agency_id, r.distance_km
FROM route AS r, agency AS a
WHERE r.origin_agency_id = a.agency_id
  AND a.region = 'Cusco'
ORDER BY r.route_id;

-- A12b. Cuántas tuplas combina cada forma antes de filtrar.
SELECT (SELECT count(*) FROM route, agency)                        AS tuplas_antes_de_sigma,
       (SELECT count(*) FROM route,
                (SELECT * FROM agency WHERE region = 'Cusco') AS a) AS tuplas_con_sigma_adentro;

-- Semana 2, sesión B: consultas básicas de SQL sobre Chasqui.
-- Se corre en psql con  \i basics.sql  (base de datos chasqui_w02).

-- B1. SQL trabaja con multiconjuntos: SELECT no elimina duplicados.
SELECT city
FROM customer
ORDER BY customer_id
LIMIT 5;

-- B1b. Filas con y sin DISTINCT.
SELECT (SELECT count(*) FROM (SELECT city FROM customer) AS t)          AS con_duplicados,
       (SELECT count(*) FROM (SELECT DISTINCT city FROM customer) AS t) AS sin_duplicados;

-- B2. Dinero: double precision es binario y aproximado; numeric es exacto.
SELECT 0.1::double precision + 0.2::double precision = 0.3 AS con_double,
       0.1::numeric + 0.2::numeric = 0.3                   AS con_numeric;

-- B2b. Lo que de verdad guarda un double.
SELECT 0.1::double precision + 0.2::double precision AS suma_double,
       0.1::numeric + 0.2::numeric                   AS suma_numeric;

-- B3. Varias tablas en FROM: producto cartesiano filtrado por WHERE.
SELECT s.shipment_id, c.last_name, s.price
FROM shipment AS s, customer AS c
WHERE s.sender_id = c.customer_id
ORDER BY s.shipment_id
LIMIT 5;

-- B3b. Sin la condición de join: ningún error, 21,6 millones de filas.
SELECT count(*) AS filas
FROM shipment, customer;

-- B4. Tres tablas: envío, nombre del remitente y ciudad de la agencia de origen.
SELECT s.shipment_id, c.first_name || ' ' || c.last_name AS remitente,
       a.city AS ciudad_origen
FROM shipment AS s, customer AS c, agency AS a
WHERE s.sender_id = c.customer_id
  AND s.origin_agency_id = a.agency_id
ORDER BY s.shipment_id
LIMIT 5;

-- B5. Expresiones y alias de columna. El alias se puede usar en ORDER BY.
SELECT shipment_id, price, price * 1.18 AS total
FROM shipment
WHERE price * 1.18 > 230
ORDER BY total DESC, shipment_id;

-- B6. LIKE: % es cualquier cadena, _ es exactamente un carácter.
SELECT count(*) AS apellido_con_q
FROM customer
WHERE last_name LIKE 'Q%';

SELECT plate
FROM vehicle
WHERE plate LIKE '_3_-%'
ORDER BY plate;

-- B6b. LIKE distingue mayúsculas; ILIKE (de PostgreSQL) no.
SELECT (SELECT count(*) FROM customer WHERE last_name LIKE 'q%')  AS con_like,
       (SELECT count(*) FROM customer WHERE last_name ILIKE 'q%') AS con_ilike;

-- B7. BETWEEN incluye los dos extremos.
SELECT route_id, distance_km
FROM route
WHERE distance_km BETWEEN 100 AND 121
ORDER BY distance_km, route_id;

-- B8. ORDER BY ... DESC y LIMIT: los 10 envíos más pesados del 16 de marzo
--     de 2026 (hora de Lima). shipment_id desempata los pesos iguales.
SELECT shipment_id, weight_kg, created_at
FROM shipment
WHERE created_at >= '2026-03-16' AND created_at < '2026-03-17'
ORDER BY weight_kg DESC, shipment_id
LIMIT 10;

-- B8b. Lo mismo con la sintaxis del estándar.
SELECT shipment_id, weight_kg, created_at
FROM shipment
WHERE created_at >= '2026-03-16' AND created_at < '2026-03-17'
ORDER BY weight_kg DESC, shipment_id
FETCH FIRST 10 ROWS ONLY;

-- B9. Clientes de Trujillo con apellido que empieza con Q, por apellido y nombre.
SELECT last_name, first_name
FROM customer
WHERE city = 'Trujillo' AND last_name LIKE 'Q%'
ORDER BY last_name, first_name;

-- B10. Las operaciones de conjuntos de SQL sí eliminan duplicados
--      (salvo que se pida ALL, que se ve en la semana 3).
SELECT (SELECT count(*) FROM (SELECT city FROM agency
                              UNION ALL
                              SELECT city FROM customer) AS t) AS union_all,
       (SELECT count(*) FROM (SELECT city FROM agency
                              UNION
                              SELECT city FROM customer) AS t) AS union_conjunto;

-- B11. Del álgebra a SQL: agencias de origen de los envíos de más de 145 kg.
--      Π name (σ weight_kg > 145 (shipment) ⋈ origin_agency_id = agency_id agency)
SELECT DISTINCT a.name
FROM shipment AS s, agency AS a
WHERE s.origin_agency_id = a.agency_id
  AND s.weight_kg > 145
ORDER BY a.name;

-- Semana 2, sesión B: definición de datos. Se crean versiones propias de
-- vehicle y trip en un esquema aparte, taller, para no tocar las tablas de
-- Chasqui. Se corre en psql con  \i ddl.sql ; varias sentencias fallan a
-- propósito para leer el error. Al final quedan las tablas taller.vehicle y
-- taller.trip, que se pueden revisar con  \d taller.trip .

DROP SCHEMA IF EXISTS taller CASCADE;
CREATE SCHEMA taller;
SET search_path TO taller;

CREATE TABLE vehicle (
  vehicle_id  integer,
  plate       varchar(7) NOT NULL,
  kind        text NOT NULL,
  capacity_kg integer NOT NULL,
  CONSTRAINT vehicle_pk PRIMARY KEY (vehicle_id)
);

CREATE TABLE trip (
  trip_id      integer,
  vehicle_id   integer NOT NULL,
  departure_at timestamptz NOT NULL,
  arrival_at   timestamptz,
  CONSTRAINT trip_pk PRIMARY KEY (trip_id),
  CONSTRAINT trip_vehicle_fk FOREIGN KEY (vehicle_id) REFERENCES vehicle (vehicle_id)
);

-- D1. Dos filas correctas.
INSERT INTO vehicle VALUES (1, 'R2T-605', 'bus', 4700);
INSERT INTO trip VALUES (1, 1, '2026-03-16 08:00', '2026-03-16 17:30');

-- D2. Un viaje con un vehículo que no existe: viola la clave foránea.
INSERT INTO trip VALUES (2, 99, '2026-03-16 09:00', NULL);

-- D3. Un vehículo sin placa: viola NOT NULL.
INSERT INTO vehicle VALUES (2, NULL, 'van', 900);

-- D4. Otro vehículo con vehicle_id 1: viola la clave primaria.
INSERT INTO vehicle VALUES (1, 'T8D-416', 'bus', 4800);

-- D5. Una placa más larga que varchar(7).
INSERT INTO vehicle VALUES (3, 'ABC-12345', 'truck', 12000);

-- D6. No se puede borrar vehicle mientras trip la referencia.
DROP TABLE vehicle;

-- D7. Agregar una columna a una tabla que ya tiene filas: queda en NULL.
ALTER TABLE vehicle ADD COLUMN brand text;
SELECT vehicle_id, plate, brand FROM vehicle;

-- D8. DELETE borra filas; la tabla sigue existiendo.
DELETE FROM trip;
SELECT count(*) AS viajes FROM trip;

RESET search_path;

-- Semana 2: consultas que PostgreSQL rechaza a propósito. Cada una
-- corresponde a un "Error común" de los apuntes. Se corre en psql con
--   \i errors.sql
-- y cada sentencia termina en ERROR. Las pruebas (queries.test.mjs)
-- comprueban el código de error (SQLSTATE) de cada una.

-- E1. Proyectar demasiado pronto: σ city = 'Cusco' (Π name (agency)).
--     Después de la proyección ya no existe city.
SELECT *
FROM (SELECT name FROM agency) AS t
WHERE city = 'Cusco';

-- E2. Unión entre relaciones no compatibles: Π agency_id (agency) ∪ Π name (agency).
SELECT agency_id FROM agency
UNION
SELECT name FROM agency;

-- E3. Nombre de columna ambiguo: las dos tablas tienen origin_agency_id.
SELECT shipment_id, origin_agency_id
FROM shipment, route
WHERE shipment.trip_id = 1;

-- E4. Un alias de SELECT no existe todavía cuando se evalúa WHERE.
SELECT price * 1.18 AS total
FROM shipment
WHERE total > 100;

// Pruebas de la sesión A: cada par de formulaciones que los apuntes llaman
// equivalentes devuelve lo mismo, y cada número que citan los apuntes sale
// de los datos. Usan la base de datos chasqui_w02 (make test la crea).
import { test, after } from 'node:test';
import assert from 'node:assert/strict';
import pg from 'pg';

const pool = new pg.Pool();
after(() => pool.end());

const rows = async (sql, params = []) => (await pool.query(sql, params)).rows;
const count = async (sql, params = []) => Number((await rows(sql, params))[0].count);

// Compara dos resultados como multiconjuntos: mismo contenido, sin importar el orden.
const bag = (rs) => rs.map((r) => JSON.stringify(Object.values(r))).sort();
const sameBag = async (sqlA, sqlB, params = []) => {
  const [a, b] = await Promise.all([rows(sqlA, params), rows(sqlB, params)]);
  assert.deepEqual(bag(a), bag(b));
  return a.length;
};

test('selección: buses de más de 5000 kg', async () => {
  const r = await rows(
    `SELECT vehicle_id FROM vehicle WHERE kind = $1 AND capacity_kg > $2 ORDER BY vehicle_id`,
    ['bus', 5000],
  );
  assert.deepEqual(r.map((x) => x.vehicle_id), [4, 7, 8]);
});

test('selección: σ a∧b (r) = σ a (σ b (r))', async () => {
  const n = await sameBag(
    `SELECT * FROM vehicle WHERE kind = 'bus' AND capacity_kg > 5000`,
    `SELECT * FROM (SELECT * FROM vehicle WHERE capacity_kg > 5000) AS t WHERE kind = 'bus'`,
  );
  assert.equal(n, 3);
});

test('proyección: Π city (customer) tiene 26 tuplas; SELECT city, 1800 filas', async () => {
  assert.equal(await count(`SELECT count(*) FROM (SELECT DISTINCT city FROM customer) AS t`), 26);
  assert.equal(await count(`SELECT count(*) FROM (SELECT city FROM customer) AS t`), 1800);
});

test('composición: Π name (σ region = Puno (agency))', async () => {
  const r = await rows(`SELECT name FROM agency WHERE region = $1 ORDER BY name`, ['Puno']);
  assert.deepEqual(r.map((x) => x.name), ['Juliaca - Centro', 'Puno - Centro']);
});

test('seleccionar y proyectar: da lo mismo proyectar antes si se conserva el atributo', async () => {
  await sameBag(
    `SELECT DISTINCT name FROM agency WHERE region = 'Puno'`,
    `SELECT DISTINCT name FROM (SELECT DISTINCT name, region FROM agency) AS t WHERE region = 'Puno'`,
  );
});

test('producto cartesiano: |agency × vehicle| = 29 · 24 = 696', async () => {
  const a = await count(`SELECT count(*) FROM agency`);
  const v = await count(`SELECT count(*) FROM vehicle`);
  assert.equal(a, 29);
  assert.equal(v, 24);
  assert.equal(await count(`SELECT count(*) FROM agency, vehicle`), a * v);
  assert.equal(
    await count(`SELECT count(*) FROM agency AS a, vehicle AS v WHERE a.region = 'Puno' AND v.kind = 'van'`),
    2 * 6,
  );
});

test('join θ: r ⋈θ s = σθ (r × s)', async () => {
  const n = await sameBag(
    `SELECT s.shipment_id, c.customer_id FROM shipment AS s, customer AS c
     WHERE s.sender_id = c.customer_id`,
    `SELECT s.shipment_id, c.customer_id FROM shipment AS s JOIN customer AS c
     ON s.sender_id = c.customer_id`,
  );
  // sender_id es NOT NULL y es clave foránea a una clave primaria: cada envío
  // encuentra exactamente un cliente.
  assert.equal(n, 12000);
  assert.equal(await count(`SELECT count(*) FROM shipment, customer`), 21600000);
});

test('join θ por una clave foránea que admite NULL: se pierden 500 envíos', async () => {
  const n = await count(`SELECT count(*) FROM shipment AS s, trip AS t WHERE s.trip_id = t.trip_id`);
  const nulls = await count(`SELECT count(*) FROM shipment WHERE trip_id IS NULL`);
  assert.equal(nulls, 500);
  assert.equal(n, 12000 - nulls);
});

test('join θ: los envíos de más de 140 kg son 35', async () => {
  assert.equal(
    await count(`SELECT count(*) FROM shipment AS s, customer AS c
                 WHERE s.sender_id = c.customer_id AND s.weight_kg > 140`),
    35,
  );
});

test('natural join: trip ⋈ vehicle = join por vehicle_id, con |trip| tuplas', async () => {
  const n = await sameBag(
    `SELECT t.trip_id, v.plate FROM trip AS t, vehicle AS v WHERE t.vehicle_id = v.vehicle_id`,
    `SELECT trip_id, plate FROM trip NATURAL JOIN vehicle`,
  );
  assert.equal(n, await count(`SELECT count(*) FROM trip`));
  assert.equal(n, 5914);
});

test('natural join: customer ⋈ agency iguala city sin que nadie lo pida', async () => {
  const n = await sameBag(
    `SELECT c.customer_id, a.agency_id FROM customer AS c, agency AS a WHERE c.city = a.city`,
    `SELECT customer_id, agency_id FROM customer NATURAL JOIN agency`,
  );
  assert.equal(n, 3433);
});

test('natural join sin atributos comunes = producto cartesiano', async () => {
  await sameBag(`SELECT * FROM route NATURAL JOIN agency`, `SELECT * FROM route, agency`);
  assert.equal(await count(`SELECT count(*) FROM route NATURAL JOIN agency`), 120 * 29);
});

test('unión: 1758 clientes enviaron o recibieron; ∪ es conmutativa', async () => {
  const n = await sameBag(
    `SELECT sender_id FROM shipment UNION SELECT recipient_id FROM shipment`,
    `SELECT recipient_id FROM shipment UNION SELECT sender_id FROM shipment`,
  );
  assert.equal(n, 1758);
});

test('|r ∪ s| = |r| + |s| − |r ∩ s|', async () => {
  const r = await count(`SELECT count(DISTINCT sender_id) FROM shipment`);
  const s = await count(`SELECT count(DISTINCT recipient_id) FROM shipment`);
  const i = await count(`SELECT count(*) FROM (SELECT sender_id FROM shipment
                         INTERSECT SELECT recipient_id FROM shipment) AS t`);
  assert.deepEqual([r, s, i], [1718, 1738, 1698]);
  assert.equal(r + s - i, 1758);
});

test('diferencia: la única agencia sin rutas de salida es la 29 (Iquitos)', async () => {
  const r = await rows(`SELECT agency_id FROM agency EXCEPT SELECT origin_agency_id FROM route`);
  assert.deepEqual(r.map((x) => x.agency_id), [29]);
  const [a] = await rows(`SELECT name FROM agency WHERE agency_id = $1`, [29]);
  assert.equal(a.name, 'Iquitos - Centro');
});

test('diferencia vacía: toda agencia de origen es también destino', async () => {
  const r = await rows(`SELECT origin_agency_id FROM route EXCEPT SELECT dest_agency_id FROM route`);
  assert.equal(r.length, 0);
});

test('diferencia: no es conmutativa (20 frente a 40)', async () => {
  const sr = await count(`SELECT count(*) FROM (SELECT sender_id FROM shipment
                          EXCEPT SELECT recipient_id FROM shipment) AS t`);
  const rs = await count(`SELECT count(*) FROM (SELECT recipient_id FROM shipment
                          EXCEPT SELECT sender_id FROM shipment) AS t`);
  assert.equal(sr, 20);
  assert.equal(rs, 40);
});

test('diferencia: 42 clientes no aparecen en ningún envío', async () => {
  assert.equal(
    await count(`SELECT count(*) FROM (SELECT customer_id FROM customer
                 EXCEPT (SELECT sender_id FROM shipment UNION SELECT recipient_id FROM shipment)) AS t`),
    42,
  );
});

test('intersección: r ∩ s = r − (r − s)', async () => {
  const n = await sameBag(
    `SELECT sender_id FROM shipment INTERSECT SELECT recipient_id FROM shipment`,
    `SELECT sender_id FROM shipment
     EXCEPT (SELECT sender_id FROM shipment EXCEPT SELECT recipient_id FROM shipment)`,
  );
  assert.equal(n, 1698);
});

test('renombramiento: las 120 rutas tienen ruta de vuelta (60 pares)', async () => {
  const self = `FROM route AS r1, route AS r2
    WHERE r1.origin_agency_id = r2.dest_agency_id AND r1.dest_agency_id = r2.origin_agency_id`;
  assert.equal(await count(`SELECT count(*) ${self}`), 120);
  assert.equal(await count(`SELECT count(*) ${self} AND r1.route_id < r2.route_id`), 60);
});

test('asignación: 12 rutas unen dos agencias de la misma región', async () => {
  const n = await sameBag(
    `SELECT r.route_id FROM route AS r, agency AS o, agency AS d
     WHERE r.origin_agency_id = o.agency_id AND r.dest_agency_id = d.agency_id
       AND o.region = d.region`,
    // la misma consulta en pasos con nombre, como la asignación del álgebra
    `WITH ro AS (SELECT r.*, o.region AS o_region FROM route AS r, agency AS o
                 WHERE r.origin_agency_id = o.agency_id),
          rod AS (SELECT ro.*, d.region AS d_region FROM ro, agency AS d
                  WHERE ro.dest_agency_id = d.agency_id)
     SELECT route_id FROM rod WHERE o_region = d_region`,
  );
  assert.equal(n, 12);
});

test('equivalencia: σ antes o después del join da lo mismo (6 rutas desde Cusco)', async () => {
  const n = await sameBag(
    `SELECT r.route_id, a.agency_id FROM route AS r, agency AS a
     WHERE r.origin_agency_id = a.agency_id AND a.region = 'Cusco'`,
    `SELECT r.route_id, a.agency_id FROM route AS r,
       (SELECT * FROM agency WHERE region = 'Cusco') AS a
     WHERE r.origin_agency_id = a.agency_id`,
  );
  assert.equal(n, 6);
  assert.equal(await count(`SELECT count(*) FROM route, agency`), 3480);
  assert.equal(await count(`SELECT count(*) FROM route, (SELECT * FROM agency WHERE region = 'Cusco') AS a`), 120);
});

// Pruebas de la sesión B: las consultas de basics.sql devuelven lo que dicen
// los apuntes, y ddl.sql y errors.sql fallan justo donde deben (se compara el
// código de error SQLSTATE, no el texto del mensaje).
import { test, after } from 'node:test';
import assert from 'node:assert/strict';
import { readFile } from 'node:fs/promises';
import pg from 'pg';

const pool = new pg.Pool();
after(() => pool.end());

const rows = async (sql, params = []) => (await pool.query(sql, params)).rows;
const count = async (sql, params = []) => Number((await rows(sql, params))[0].count);

// Parte un archivo .sql en sentencias: quita los comentarios -- y corta en
// cada ; de fin de línea. Basta para estos archivos, que no tienen ; en textos.
const statements = async (file) =>
  (await readFile(new URL(file, import.meta.url), 'utf8'))
    .split('\n')
    .filter((line) => !line.trimStart().startsWith('--'))
    .join('\n')
    .split(/;\s*\n/)
    .map((s) => s.trim())
    .filter((s) => s.length > 0);

// Corre las sentencias en una sola conexión y devuelve el SQLSTATE de cada
// una (null si terminó bien).
const runFile = async (file) => {
  const client = await pool.connect();
  try {
    const codes = [];
    for (const sql of await statements(file)) {
      try {
        await client.query(sql);
        codes.push(null);
      } catch (err) {
        codes.push(err.code);
      }
    }
    return codes;
  } finally {
    await client.query('RESET search_path');
    client.release();
  }
};

test('SELECT city repite ciudades; DISTINCT no', async () => {
  const [r] = await rows(`SELECT count(*) AS a, count(DISTINCT city) AS b FROM customer`);
  assert.deepEqual([Number(r.a), Number(r.b)], [1800, 26]);
});

test('double precision es aproximado; numeric es exacto', async () => {
  const [r] = await rows(
    `SELECT 0.1::double precision + 0.2::double precision = 0.3 AS d,
            0.1::numeric + 0.2::numeric = 0.3 AS n`,
  );
  assert.deepEqual(r, { d: false, n: true });
});

test('sin condición de join: 12000 · 1800 filas', async () => {
  assert.equal(await count(`SELECT count(*) FROM shipment, customer`), 12000 * 1800);
});

test('tres tablas en FROM: el envío 1 lo mandó Luis Mamani Chávez desde Piura', async () => {
  const [r] = await rows(
    `SELECT c.first_name || ' ' || c.last_name AS remitente, a.city
     FROM shipment AS s, customer AS c, agency AS a
     WHERE s.sender_id = c.customer_id AND s.origin_agency_id = a.agency_id
       AND s.shipment_id = $1`,
    [1],
  );
  assert.deepEqual(r, { remitente: 'Luis Mamani Chávez', city: 'Piura' });
});

test('ORDER BY con el alias = ORDER BY con la expresión', async () => {
  const a = await rows(`SELECT shipment_id, price * 1.18 AS total FROM shipment
                        WHERE price * 1.18 > 230 ORDER BY total DESC, shipment_id`);
  const b = await rows(`SELECT shipment_id, price * 1.18 AS total FROM shipment
                        WHERE price * 1.18 > 230 ORDER BY price * 1.18 DESC, shipment_id`);
  assert.deepEqual(a, b);
  assert.deepEqual(a.map((x) => x.shipment_id), [3645, 1618]);
});

test('LIKE distingue mayúsculas; ILIKE no', async () => {
  assert.equal(await count(`SELECT count(*) FROM customer WHERE last_name LIKE $1`, ['Q%']), 51);
  assert.equal(await count(`SELECT count(*) FROM customer WHERE last_name LIKE $1`, ['q%']), 0);
  assert.equal(await count(`SELECT count(*) FROM customer WHERE last_name ILIKE $1`, ['q%']), 51);
});

test('LIKE con _: placas con 3 en la segunda posición', async () => {
  const r = await rows(`SELECT plate FROM vehicle WHERE plate LIKE $1 ORDER BY plate`, ['_3_-%']);
  assert.deepEqual(r.map((x) => x.plate), ['F3E-445', 'G3S-419', 'H3H-976', 'J3D-585', 'L3A-234', 'S3G-624']);
});

test('BETWEEN a AND b = (>= a AND <= b)', async () => {
  const a = await rows(`SELECT route_id FROM route WHERE distance_km BETWEEN 100 AND 121 ORDER BY route_id`);
  const b = await rows(`SELECT route_id FROM route WHERE distance_km >= 100 AND distance_km <= 121 ORDER BY route_id`);
  assert.deepEqual(a, b);
  assert.deepEqual(a.map((x) => x.route_id), [85, 86]);
});

test('LIMIT 10 = FETCH FIRST 10 ROWS ONLY', async () => {
  const q = `SELECT shipment_id, weight_kg FROM shipment
             WHERE created_at >= '2026-03-16' AND created_at < '2026-03-17'
             ORDER BY weight_kg DESC, shipment_id`;
  const a = await rows(`${q} LIMIT 10`);
  const b = await rows(`${q} FETCH FIRST 10 ROWS ONLY`);
  assert.deepEqual(a, b);
  assert.equal(a[0].shipment_id, 3645);
  assert.equal(a[0].weight_kg, '144.60');
  assert.equal(await count(`SELECT count(*) FROM shipment
                            WHERE created_at >= '2026-03-16' AND created_at < '2026-03-17'`), 68);
});

test('UNION elimina duplicados; UNION ALL no', async () => {
  const all = await count(`SELECT count(*) FROM (SELECT city FROM agency UNION ALL SELECT city FROM customer) AS t`);
  const set = await count(`SELECT count(*) FROM (SELECT city FROM agency UNION SELECT city FROM customer) AS t`);
  assert.deepEqual([all, set], [29 + 1800, 26]);
});

test('errors.sql: cada consulta falla con el código esperado', async () => {
  assert.deepEqual(await runFile('errors.sql'), [
    '42703', // column "city" does not exist
    '42804', // UNION types integer and text cannot be matched
    '42702', // column reference "origin_agency_id" is ambiguous
    '42703', // column "total" does not exist
  ]);
});

test('ddl.sql: el motor rechaza exactamente las filas que violan restricciones', async () => {
  assert.deepEqual(await runFile('ddl.sql'), [
    null, // DROP SCHEMA IF EXISTS
    null, // CREATE SCHEMA
    null, // SET search_path
    null, // CREATE TABLE vehicle
    null, // CREATE TABLE trip
    null, // INSERT vehicle 1
    null, // INSERT trip 1
    '23503', // D2: foreign key
    '23502', // D3: not null
    '23505', // D4: primary key duplicada
    '22001', // D5: varchar(7) demasiado corto
    '2BP01', // D6: DROP TABLE con dependencias
    null, // D7: ALTER TABLE ADD COLUMN
    null, // D7: SELECT
    null, // D8: DELETE
    null, // D8: SELECT
    null, // RESET search_path
  ]);
  const r = await rows(`SELECT vehicle_id, plate, brand FROM taller.vehicle`);
  assert.deepEqual(r, [{ vehicle_id: 1, plate: 'R2T-605', brand: null }]);
  assert.equal(await count(`SELECT count(*) FROM taller.trip`), 0);
  await pool.query('DROP SCHEMA taller CASCADE');
});

# Semana 2: álgebra relacional y SQL básico sobre Chasqui.
# Se usa dentro del contenedor del curso.
#
#   make          crea la base de datos chasqui_w02 con los datos de ejemplo
#   make test     la vuelve a crear, corre algebra.sql y basics.sql y las pruebas
#                 (*.test.mjs); las pruebas corren también ddl.sql y errors.sql,
#                 que fallan a propósito
#   make clean    borra chasqui_w02

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

.PHONY: all test clean

all:
	chasqui-load $(DB)

test: all
	$(PSQL) -f algebra.sql > /dev/null
	$(PSQL) -f basics.sql > /dev/null
	PGDATABASE=$(DB) node --test --test-reporter=spec

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