// Ayuda compartida por las pruebas: un pool y una función que devuelve la
// primera fila de una consulta. Usa PGDATABASE (make test pone chasqui_w03).
import pg from 'pg';

export const pool = new pg.Pool();

/** Ejecuta una consulta parametrizada y devuelve su primera fila. */
export async function one(sql, params = []) {
  const { rows } = await pool.query(sql, params);
  return rows[0];
}

/** Ejecuta una consulta parametrizada y devuelve todas sus filas. */
export async function all(sql, params = []) {
  return (await pool.query(sql, params)).rows;
}

-- Semana 3: consultas que fallan a propósito, para leer el mensaje de error.
-- Se corre sobre chasqui_w03:  psql -d chasqui_w03 -f errors.sql
-- No se detiene en el primer error; make test no lo corre (lo revisan las
-- pruebas de errors.test.mjs).

-- 1. Un agregado en WHERE.
SELECT route_id
FROM trip
WHERE count(*) > 50
GROUP BY route_id;

-- 2. Una columna en SELECT que no está en GROUP BY ni depende de él.
SELECT region, name, count(*)
FROM agency
GROUP BY region;

-- 3. Una subconsulta escalar que devuelve más de una fila.
SELECT name,
       (SELECT shipment_id FROM shipment s
        WHERE s.origin_agency_id = a.agency_id) AS envio
FROM agency a;

-- 4. Las dos partes de un UNION con distinta cantidad de columnas.
SELECT city, region FROM agency
UNION
SELECT city FROM customer;

-- 5. Borrar envíos que todavía tienen eventos.
BEGIN;
DELETE FROM shipment
WHERE price < (SELECT avg(price) FROM shipment);
ROLLBACK;

-- Semana 3, sesión B: INSERT, UPDATE y DELETE.
-- Se corre sobre chasqui_w03:  psql -d chasqui_w03 -f modification.sql
-- Cada bloque va entre BEGIN y ROLLBACK: los datos quedan como estaban.

\pset null '∅'

-- ------------------------------------------------------------
-- 1. INSERT ... VALUES y RETURNING
-- ------------------------------------------------------------
BEGIN;

INSERT INTO customer (customer_id, dni, first_name, last_name, phone, city)
VALUES (1801, '70000001', 'Rosa', 'Quispe Mamani', NULL, 'Cusco');

INSERT INTO shipment (shipment_id, sender_id, recipient_id, origin_agency_id,
                      dest_agency_id, trip_id, weight_kg, declared_value,
                      price, status, created_at)
VALUES (12001, 1801, 152, 6, 1, NULL, 2.50, NULL, 18.00, 'registered', now());

-- RETURNING (de PostgreSQL) devuelve las filas modificadas.
UPDATE shipment
SET status = 'delivered', delivered_at = now()
WHERE shipment_id = 12001
RETURNING shipment_id, status, delivered_at IS NOT NULL AS tiene_fecha;

ROLLBACK;

-- ------------------------------------------------------------
-- 2. INSERT ... SELECT y UPDATE con subconsulta escalar
-- ------------------------------------------------------------
BEGIN;

CREATE TABLE frequent_sender (
  customer_id integer PRIMARY KEY REFERENCES customer,
  shipments   integer NOT NULL,
  total_paid  numeric(10, 2)
);

-- Se inserta el resultado de una consulta: los remitentes con 15 envíos o más.
INSERT INTO frequent_sender (customer_id, shipments)
SELECT sender_id, count(*)
FROM shipment
GROUP BY sender_id
HAVING count(*) >= 15;

-- total_paid quedó en NULL. Se calcula con una subconsulta correlacionada.
UPDATE frequent_sender f
SET total_paid = (SELECT sum(s.price)
                  FROM shipment s
                  WHERE s.sender_id = f.customer_id);

SELECT customer_id, shipments, total_paid
FROM frequent_sender
ORDER BY total_paid DESC
LIMIT 3;

ROLLBACK;

-- ------------------------------------------------------------
-- 3. UPDATE con CASE y con subconsulta en WHERE
-- ------------------------------------------------------------
BEGIN;

SELECT sum(base_fare) AS antes FROM route;

-- 5 % para las rutas de más de 500 km y 3 % para las demás, en una sentencia.
UPDATE route
SET base_fare = CASE
                  WHEN distance_km > 500 THEN round(base_fare * 1.05, 2)
                  ELSE round(base_fare * 1.03, 2)
                END;

SELECT sum(base_fare) AS despues FROM route;

-- 10 % más en las rutas con más de 75 viajes.
UPDATE route
SET base_fare = round(base_fare * 1.10, 2)
WHERE route_id IN (SELECT route_id
                   FROM trip
                   GROUP BY route_id
                   HAVING count(*) > 75);

ROLLBACK;

-- ------------------------------------------------------------
-- 4. DELETE con subconsulta
-- ------------------------------------------------------------
BEGIN;

SELECT round(avg(price), 2) AS promedio,
       count(*) FILTER (WHERE price < (SELECT avg(price) FROM shipment)) AS bajo_promedio
FROM shipment;

-- Primero los eventos de esos envíos (shipment_event los referencia).
DELETE FROM shipment_event
WHERE shipment_id IN (SELECT shipment_id
                      FROM shipment
                      WHERE price < (SELECT avg(price) FROM shipment));

-- El promedio se calcula una vez, antes de borrar.
DELETE FROM shipment
WHERE price < (SELECT avg(price) FROM shipment);

-- Ahora el promedio es otro, y hay envíos por debajo del nuevo promedio.
SELECT count(*) AS quedan, round(avg(price), 2) AS promedio_nuevo,
       count(*) FILTER (WHERE price < (SELECT avg(price) FROM shipment))
         AS bajo_promedio_nuevo
FROM shipment;

ROLLBACK;

-- ------------------------------------------------------------
-- 5. UPDATE sin WHERE: modifica todas las filas
-- ------------------------------------------------------------
BEGIN;

UPDATE route SET base_fare = 0;

SELECT count(*) AS rutas_gratis FROM route WHERE base_fare = 0;

ROLLBACK;

-- Después de los ROLLBACK, nada cambió.
SELECT (SELECT count(*) FROM customer) AS clientes,
       (SELECT count(*) FROM shipment) AS envios,
       (SELECT count(*) FROM shipment_event) AS eventos,
       (SELECT sum(base_fare) FROM route) AS tarifas,
       to_regclass('frequent_sender') AS tabla_nueva;

-- Semana 3, sesión A: operaciones de conjuntos, NULL y agregación.
-- Se corre sobre chasqui_w03:  psql -d chasqui_w03 -f sets-null-aggregates.sql
-- Ninguna consulta modifica datos.

\pset null '∅'

-- ------------------------------------------------------------
-- 1. Operaciones de conjuntos
-- ------------------------------------------------------------

-- 1.1 Ciudades con agencia o con clientes (UNION elimina duplicados).
SELECT count(*) AS ciudades
FROM (SELECT city FROM agency UNION SELECT city FROM customer) AS t;

-- 1.2 Con ALL se conservan los duplicados: 29 agencias + 1800 clientes.
SELECT count(*) AS filas
FROM (SELECT city FROM agency UNION ALL SELECT city FROM customer) AS t;

-- 1.3 Ciudades con clientes pero sin agencia: ninguna.
SELECT city FROM customer
EXCEPT
SELECT city FROM agency;

-- 1.4 Remitentes que nunca fueron destinatarios, y al revés.
SELECT count(*) AS solo_remitentes
FROM (SELECT sender_id FROM shipment
      EXCEPT
      SELECT recipient_id FROM shipment) AS t;

SELECT count(*) AS solo_destinatarios
FROM (SELECT recipient_id FROM shipment
      EXCEPT
      SELECT sender_id FROM shipment) AS t;

SELECT count(*) AS ambos
FROM (SELECT sender_id FROM shipment
      INTERSECT
      SELECT recipient_id FROM shipment) AS t;

-- 1.5 Multiconjuntos: EXCEPT ALL resta copias. Lima tiene tres agencias y
-- Arequipa dos; al quitar una copia de cada ciudad quedan las "repetidas".
SELECT city FROM agency
EXCEPT ALL
SELECT DISTINCT city FROM agency
ORDER BY city;

-- 1.6 INTERSECT ALL conserva min(m, n) copias; EXCEPT ALL, max(m - n, 0).
(VALUES ('Lima'), ('Lima'), ('Lima'), ('Cusco'))
INTERSECT ALL
(VALUES ('Lima'), ('Lima'), ('Cusco'), ('Cusco'))
ORDER BY 1;

(VALUES ('Lima'), ('Lima'), ('Lima'), ('Cusco'))
EXCEPT ALL
(VALUES ('Lima'), ('Lima'), ('Cusco'), ('Cusco'))
ORDER BY 1;

-- 1.7 Para eliminar duplicados, dos NULL cuentan como iguales.
SELECT NULL AS x UNION SELECT NULL;

-- ------------------------------------------------------------
-- 2. NULL y lógica de tres valores
-- ------------------------------------------------------------

-- 2.1 Aritmética y comparaciones con NULL.
SELECT 10 + NULL AS suma,
       NULL = NULL AS igual,
       NULL <> NULL AS distinto,
       NULL IS NULL AS es_null;

-- 2.2 Tablas de verdad de AND, OR y NOT (∅ es unknown).
SELECT p, q, p AND q AS p_and_q, p OR q AS p_or_q, NOT p AS not_p
FROM (VALUES (true), (false), (NULL::boolean)) AS a(p)
CROSS JOIN (VALUES (true), (false), (NULL::boolean)) AS b(q)
ORDER BY p DESC NULLS LAST, q DESC NULLS LAST;

-- 2.3 WHERE se queda solo con las filas cuyo predicado es verdadero.
SELECT count(*) AS total,
       count(*) FILTER (WHERE declared_value > 500) AS mayor,
       count(*) FILTER (WHERE declared_value <= 500) AS menor_igual,
       count(*) FILTER (WHERE declared_value > 500
                           OR declared_value <= 500) AS cualquiera,
       count(*) FILTER (WHERE declared_value IS NULL) AS sin_valor
FROM shipment;

-- 2.4 NOT no rescata a los NULL: NOT unknown sigue siendo unknown.
SELECT count(*) AS no_mayor
FROM shipment
WHERE NOT (declared_value > 500);

-- 2.5 "= NULL" nunca es verdadero; se escribe IS NULL.
SELECT count(*) AS igual_null FROM customer WHERE phone = NULL;
SELECT count(*) AS is_null    FROM customer WHERE phone IS NULL;

-- 2.6 IS UNKNOWN pregunta por el valor de verdad de una condición.
SELECT count(*) AS desconocido
FROM shipment
WHERE (declared_value > 500) IS UNKNOWN;

-- 2.7 IS DISTINCT FROM trata dos NULL como iguales.
SELECT NULL IS DISTINCT FROM NULL AS d1,
       NULL IS DISTINCT FROM 5    AS d2,
       5 IS NOT DISTINCT FROM 5   AS d3;

-- 2.8 coalesce reemplaza el NULL al mostrarlo.
SELECT customer_id, first_name, coalesce(phone, 'sin teléfono') AS phone
FROM customer
WHERE customer_id BETWEEN 1 AND 6
ORDER BY customer_id;

-- ------------------------------------------------------------
-- 3. Funciones de agregación
-- ------------------------------------------------------------

-- 3.1 count(*), count(columna) y count(DISTINCT columna).
SELECT count(*) AS filas, count(phone) AS con_telefono,
       count(DISTINCT city) AS ciudades
FROM customer;

-- 3.2 avg ignora los NULL: no es sum / count(*).
SELECT round(avg(declared_value), 2) AS promedio,
       round(sum(declared_value) / count(*), 2) AS sum_entre_filas,
       round(sum(declared_value) / count(declared_value), 2) AS sum_entre_valores
FROM shipment;

-- 3.3 Sin GROUP BY, un agregado sobre cero filas devuelve UNA fila:
-- count da 0 y los demás dan NULL.
SELECT count(*) AS n, count(price) AS n_price, sum(price) AS suma,
       avg(price) AS promedio, min(price) AS minimo, max(price) AS maximo
FROM shipment
WHERE price < 0;

-- 3.4 Con GROUP BY, cero filas de entrada son cero grupos: cero filas.
SELECT status, count(*) AS n
FROM shipment
WHERE price < 0
GROUP BY status;

-- 3.5 Si todos los valores son NULL hay filas, pero sum no tiene qué sumar.
SELECT count(*) AS filas, count(declared_value) AS valores,
       sum(declared_value) AS suma,
       coalesce(sum(declared_value), 0) AS suma_o_cero
FROM shipment
WHERE declared_value IS NULL;

-- ------------------------------------------------------------
-- 4. GROUP BY y HAVING
-- ------------------------------------------------------------

-- 4.1 Envíos por estado.
SELECT status, count(*) AS n
FROM shipment
GROUP BY status
ORDER BY n DESC;

-- 4.2 Ingresos por agencia de origen, con su nombre. a.name se puede usar
-- sin ponerlo en GROUP BY porque depende de la clave primaria a.agency_id.
SELECT a.agency_id, a.name, sum(s.price) AS ingresos
FROM agency a, shipment s
WHERE s.origin_agency_id = a.agency_id
GROUP BY a.agency_id
ORDER BY ingresos DESC
LIMIT 5;

-- 4.3 Los NULL de la columna agrupada forman un solo grupo.
SELECT trip_id, count(*) AS n
FROM shipment
GROUP BY trip_id
ORDER BY n DESC, trip_id
LIMIT 3;

-- 4.4 Grupos frente a valores distintos: DISTINCT y GROUP BY juntan los NULL
-- en un valor más; count(DISTINCT) no lo cuenta.
SELECT (SELECT count(*) FROM (SELECT DISTINCT trip_id FROM shipment) AS d)
         AS valores_distintos,
       count(DISTINCT trip_id) AS count_distinct
FROM shipment;

-- 4.5 HAVING filtra grupos: rutas con más de 50 viajes.
SELECT count(*) AS rutas
FROM (SELECT route_id
      FROM trip
      GROUP BY route_id
      HAVING count(*) > 50) AS t;

-- 4.6 Rutas con más de 75 viajes, contando todos los viajes...
SELECT route_id, count(*) AS viajes, count(arrival_at) AS llegados
FROM trip
GROUP BY route_id
HAVING count(*) > 75
ORDER BY route_id;

-- 4.7 ...y contando solo los que ya llegaron: la condición sobre filas va en
-- WHERE, se aplica antes de agrupar y cambia los grupos.
SELECT route_id, count(*) AS viajes
FROM trip
WHERE arrival_at IS NOT NULL
GROUP BY route_id
HAVING count(*) > 75
ORDER BY route_id;

-- 4.8 Agregación con FILTER: varias cuentas en una sola pasada.
SELECT a.region,
       count(*) AS envios,
       count(*) FILTER (WHERE s.status = 'delivered') AS entregados,
       count(*) FILTER (WHERE s.status = 'returned') AS devueltos
FROM agency a, shipment s
WHERE s.origin_agency_id = a.agency_id
GROUP BY a.region
ORDER BY envios DESC
LIMIT 5;

-- Semana 3, sesión B: subconsultas anidadas, en FROM, escalares y WITH.
-- Se corre sobre chasqui_w03:  psql -d chasqui_w03 -f subqueries.sql
-- Ninguna consulta modifica datos.

\pset null '∅'

-- ------------------------------------------------------------
-- 1. Pertenencia: IN y NOT IN
-- ------------------------------------------------------------

-- 1.1 Clientes que enviaron algo.
SELECT count(*) AS remitentes
FROM customer
WHERE customer_id IN (SELECT sender_id FROM shipment);

-- 1.2 La trampa: viajes que no llevan ningún envío, con NOT IN.
-- shipment.trip_id tiene NULL (envíos sin asignar) y el resultado es vacío.
SELECT count(*) AS viajes_vacios
FROM trip
WHERE trip_id NOT IN (SELECT trip_id FROM shipment);

-- 1.3 Corrección 1: sacar los NULL de la subconsulta.
SELECT count(*) AS viajes_vacios
FROM trip
WHERE trip_id NOT IN (SELECT trip_id FROM shipment WHERE trip_id IS NOT NULL);

-- 1.4 Corrección 2: NOT EXISTS (la forma recomendada).
SELECT count(*) AS viajes_vacios
FROM trip t
WHERE NOT EXISTS (SELECT 1 FROM shipment s WHERE s.trip_id = t.trip_id);

-- 1.5 Corrección 3: diferencia de conjuntos.
SELECT count(*) AS viajes_vacios
FROM (SELECT trip_id FROM trip
      EXCEPT
      SELECT trip_id FROM shipment) AS t;

-- 1.6 La trampa en miniatura.
SELECT 3 NOT IN (1, 2)       AS sin_null,
       3 NOT IN (1, 2, NULL) AS con_null,
       1 NOT IN (1, 2, NULL) AS presente,
       3 IN (1, 2, NULL)     AS in_con_null;

-- ------------------------------------------------------------
-- 2. Comparación con conjuntos: SOME y ALL
-- ------------------------------------------------------------

-- 2.1 Envíos más caros que todos los que salieron de Tumbes - Centro.
SELECT count(*) AS mas_caros
FROM shipment
WHERE price > ALL (SELECT price FROM shipment WHERE origin_agency_id = 10);

-- 2.2 Lo mismo con max: equivalente, porque price es NOT NULL.
SELECT count(*) AS mas_caros
FROM shipment
WHERE price > (SELECT max(price) FROM shipment WHERE origin_agency_id = 10);

-- 2.3 Envíos más caros que alguno de Tumbes - Centro.
SELECT count(*) AS mas_caros_que_alguno
FROM shipment
WHERE price > SOME (SELECT price FROM shipment WHERE origin_agency_id = 10);

-- 2.4 Cuantificadores con NULL y con conjunto vacío.
SELECT 5 > ALL (VALUES (1), (2))         AS all_sin_null,
       5 > ALL (VALUES (1), (NULL::int)) AS all_unknown,
       5 > ALL (VALUES (9), (NULL::int)) AS all_falso,
       5 > SOME (VALUES (9), (NULL::int)) AS some_unknown,
       5 > SOME (VALUES (1), (NULL::int)) AS some_verdad;

SELECT 5 > ALL (SELECT 1 WHERE false)  AS all_vacio,
       5 > SOME (SELECT 1 WHERE false) AS some_vacio,
       5 > (SELECT max(1) WHERE false)  AS max_vacio,
       NULL::int NOT IN (SELECT 1 WHERE false) AS null_not_in_vacio;

-- ------------------------------------------------------------
-- 3. EXISTS y subconsultas correlacionadas
-- ------------------------------------------------------------

-- 3.1 Clientes que nunca recibieron un envío.
SELECT count(*) AS nunca_recibieron
FROM customer c
WHERE NOT EXISTS (SELECT 1 FROM shipment s WHERE s.recipient_id = c.customer_id);

-- 3.2 Clientes que fueron remitentes y también destinatarios: tres formas.
SELECT count(*) AS ambos FROM (
  SELECT sender_id FROM shipment
  INTERSECT
  SELECT recipient_id FROM shipment) AS t;

SELECT count(*) AS ambos
FROM customer
WHERE customer_id IN (SELECT sender_id FROM shipment)
  AND customer_id IN (SELECT recipient_id FROM shipment);

SELECT count(*) AS ambos
FROM customer c
WHERE EXISTS (SELECT 1 FROM shipment s WHERE s.sender_id = c.customer_id)
  AND EXISTS (SELECT 1 FROM shipment s WHERE s.recipient_id = c.customer_id);

-- 3.3 "Para todo" con doble negación: clientes que enviaron paquetes a todas
-- las agencias de la región Lima (no existe agencia de Lima a la que no
-- hayan enviado).
SELECT count(*) AS a_todas_lima
FROM customer c
WHERE NOT EXISTS (
  SELECT 1
  FROM agency a
  WHERE a.region = 'Lima'
    AND NOT EXISTS (SELECT 1
                    FROM shipment s
                    WHERE s.sender_id = c.customer_id
                      AND s.dest_agency_id = a.agency_id));

-- 3.4 La misma pregunta contando: tantas agencias de Lima distintas como
-- agencias tiene Lima.
SELECT count(*) AS a_todas_lima
FROM (SELECT s.sender_id
      FROM shipment s, agency a
      WHERE s.dest_agency_id = a.agency_id AND a.region = 'Lima'
      GROUP BY s.sender_id
      HAVING count(DISTINCT a.agency_id) =
             (SELECT count(*) FROM agency WHERE region = 'Lima')) AS t;

-- 3.5 Con un divisor vacío (una región sin agencias) "para todo" es
-- verdadero para todos los clientes.
SELECT count(*) AS a_todas_ninguna
FROM customer c
WHERE NOT EXISTS (
  SELECT 1
  FROM agency a
  WHERE a.region = 'Lima Metropolitana'
    AND NOT EXISTS (SELECT 1
                    FROM shipment s
                    WHERE s.sender_id = c.customer_id
                      AND s.dest_agency_id = a.agency_id));

-- 3.6 La división del álgebra recorre el dividendo: con el divisor vacío
-- devuelve a todos los remitentes, no a todos los clientes.
SELECT count(*) AS division
FROM (SELECT DISTINCT sender_id FROM shipment) AS r
WHERE NOT EXISTS (
  SELECT 1
  FROM agency a
  WHERE a.region = 'Lima Metropolitana'
    AND NOT EXISTS (SELECT 1
                    FROM shipment s
                    WHERE s.sender_id = r.sender_id
                      AND s.dest_agency_id = a.agency_id));

-- 3.7 La versión que cuenta, con el divisor vacío: nadie.
SELECT count(*) AS a_todas_ninguna
FROM (SELECT s.sender_id
      FROM shipment s, agency a
      WHERE s.dest_agency_id = a.agency_id AND a.region = 'Lima Metropolitana'
      GROUP BY s.sender_id
      HAVING count(DISTINCT a.agency_id) =
             (SELECT count(*) FROM agency WHERE region = 'Lima Metropolitana')) AS t;

-- ------------------------------------------------------------
-- 4. Subconsultas en FROM y WITH
-- ------------------------------------------------------------

-- 4.1 Precio promedio por ruta, y las rutas cuyo promedio pasa de S/ 80.
SELECT route_id, round(avg_price, 2) AS avg_price
FROM (SELECT t.route_id, avg(s.price) AS avg_price
      FROM shipment s, trip t
      WHERE s.trip_id = t.trip_id
      GROUP BY t.route_id) AS route_price
WHERE avg_price > 80
ORDER BY avg_price DESC;

-- 4.2 Con WITH: peso total por ruta y las rutas por encima del promedio.
WITH route_load AS (
  SELECT t.route_id, sum(s.weight_kg) AS total_kg
  FROM shipment s, trip t
  WHERE s.trip_id = t.trip_id
  GROUP BY t.route_id
)
SELECT count(*) AS rutas_sobre_promedio,
       (SELECT count(*) FROM route_load) AS rutas
FROM route_load
WHERE total_kg > (SELECT avg(total_kg) FROM route_load);

-- 4.3 Envíos que pagaron más del doble del promedio de su ruta: cuántos son
-- y los cinco más caros en proporción.
WITH route_price AS (
  SELECT t.route_id, avg(s.price) AS avg_price
  FROM shipment s, trip t
  WHERE s.trip_id = t.trip_id
  GROUP BY t.route_id
),
expensive AS (
  SELECT s.shipment_id, t.route_id, s.price, rp.avg_price
  FROM shipment s, trip t, route_price rp
  WHERE s.trip_id = t.trip_id
    AND t.route_id = rp.route_id
    AND s.price > 2 * rp.avg_price
)
SELECT count(*) AS envios FROM expensive;

WITH route_price AS (
  SELECT t.route_id, avg(s.price) AS avg_price
  FROM shipment s, trip t
  WHERE s.trip_id = t.trip_id
  GROUP BY t.route_id
)
SELECT s.shipment_id, t.route_id, s.price, round(rp.avg_price, 2) AS avg_price,
       round(s.price / rp.avg_price, 2) AS veces
FROM shipment s, trip t, route_price rp
WHERE s.trip_id = t.trip_id
  AND t.route_id = rp.route_id
  AND s.price > 2 * rp.avg_price
ORDER BY veces DESC, s.shipment_id
LIMIT 5;

-- ------------------------------------------------------------
-- 5. Subconsultas escalares y LATERAL
-- ------------------------------------------------------------

-- 5.1 En SELECT: la cantidad de eventos de cada envío.
SELECT s.shipment_id, s.status,
       (SELECT count(*)
        FROM shipment_event e
        WHERE e.shipment_id = s.shipment_id) AS eventos
FROM shipment s
WHERE s.shipment_id IN (1, 36, 46, 63)
ORDER BY s.shipment_id;

-- 5.2 Una subconsulta escalar que no encuentra filas vale NULL.
SELECT (SELECT name FROM agency WHERE agency_id = 99) AS agencia;

-- 5.3 LATERAL: el último evento de cada envío.
SELECT s.shipment_id, s.status, last.seq, last.kind
FROM shipment s
CROSS JOIN LATERAL (SELECT e.seq, e.kind
                    FROM shipment_event e
                    WHERE e.shipment_id = s.shipment_id
                    ORDER BY e.seq DESC
                    LIMIT 1) AS last
WHERE s.shipment_id IN (1, 36, 46, 63)
ORDER BY s.shipment_id;

// Las consultas de errors.sql fallan con el mensaje que citan los apuntes.
import { test, after } from 'node:test';
import assert from 'node:assert/strict';
import { pool } from './db.mjs';

after(() => pool.end());

test('un agregado no puede ir en WHERE', async () => {
  await assert.rejects(
    pool.query('SELECT route_id FROM trip WHERE count(*) > 50 GROUP BY route_id'),
    /aggregate functions are not allowed in WHERE/,
  );
});

test('las columnas del SELECT deben estar en GROUP BY', async () => {
  await assert.rejects(
    pool.query('SELECT region, name, count(*) FROM agency GROUP BY region'),
    /column "agency.name" must appear in the GROUP BY clause/,
  );
});

test('pero basta agrupar por la clave primaria', async () => {
  const { rows } = await pool.query(
    'SELECT agency_id, name, region FROM agency GROUP BY agency_id ORDER BY agency_id LIMIT 1',
  );
  assert.deepEqual(rows, [{ agency_id: 1, name: 'Lima - La Victoria', region: 'Lima' }]);
});

test('una subconsulta escalar no puede devolver varias filas', async () => {
  await assert.rejects(
    pool.query(`SELECT name, (SELECT shipment_id FROM shipment s
                WHERE s.origin_agency_id = a.agency_id) AS envio FROM agency a`),
    /more than one row returned by a subquery used as an expression/,
  );
});

test('las partes de un UNION deben tener las mismas columnas', async () => {
  await assert.rejects(
    pool.query('SELECT city, region FROM agency UNION SELECT city FROM customer'),
    /each UNION query must have the same number of columns/,
  );
});

test('no se puede borrar un envío que tiene eventos', async () => {
  await assert.rejects(
    pool.query('DELETE FROM shipment WHERE price < (SELECT avg(price) FROM shipment)'),
    /violates foreign key constraint "shipment_event_shipment_id_fkey"/,
  );
});

// Pruebas de INSERT, UPDATE y DELETE. Cada prueba trabaja en una transacción
// propia sobre una sola conexión y termina con ROLLBACK: los datos no cambian.
import { test, after } from 'node:test';
import assert from 'node:assert/strict';
import { pool, one } from './db.mjs';

after(() => pool.end());

/** Corre fn(client) entre BEGIN y ROLLBACK en una misma conexión. */
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();
  }
}

test('INSERT y UPDATE ... RETURNING', async () => {
  await inRollback(async (c) => {
    await c.query(
      `INSERT INTO customer (customer_id, dni, first_name, last_name, phone, city)
       VALUES ($1, $2, $3, $4, NULL, $5)`,
      [1801, '70000001', 'Rosa', 'Quispe Mamani', 'Cusco'],
    );
    await c.query(
      `INSERT INTO shipment (shipment_id, sender_id, recipient_id, origin_agency_id,
         dest_agency_id, weight_kg, price, status, created_at)
       VALUES ($1, $2, $3, $4, $5, $6, $7, 'registered', now())`,
      [12001, 1801, 152, 6, 1, 2.5, 18],
    );
    const { rows, rowCount } = await c.query(
      `UPDATE shipment SET status = 'delivered', delivered_at = now()
       WHERE shipment_id = $1 RETURNING shipment_id, status`,
      [12001],
    );
    assert.equal(rowCount, 1);
    assert.deepEqual(rows, [{ shipment_id: 12001, status: 'delivered' }]);
  });
});

test('INSERT ... SELECT inserta el resultado de una consulta', async () => {
  await inRollback(async (c) => {
    await c.query(`CREATE TABLE frequent_sender (
      customer_id integer PRIMARY KEY, shipments integer NOT NULL, total_paid numeric(10, 2))`);
    const ins = await c.query(`
      INSERT INTO frequent_sender (customer_id, shipments)
      SELECT sender_id, count(*) FROM shipment GROUP BY sender_id HAVING count(*) >= 15`);
    assert.equal(ins.rowCount, 100);
    const upd = await c.query(`
      UPDATE frequent_sender f
      SET total_paid = (SELECT sum(s.price) FROM shipment s WHERE s.sender_id = f.customer_id)`);
    assert.equal(upd.rowCount, 100);
    const { rows } = await c.query('SELECT count(*)::int AS n FROM frequent_sender WHERE total_paid IS NULL');
    assert.equal(rows[0].n, 0);
  });
});

test('UPDATE con CASE toca todas las rutas en una sentencia', async () => {
  await inRollback(async (c) => {
    const upd = await c.query(`
      UPDATE route SET base_fare = CASE WHEN distance_km > 500 THEN round(base_fare * 1.05, 2)
                                        ELSE round(base_fare * 1.03, 2) END`);
    assert.equal(upd.rowCount, 120);
    const { rows } = await c.query('SELECT sum(base_fare)::text AS s FROM route');
    assert.equal(rows[0].s, '2920.34');
  });
});

test('UPDATE con subconsulta en WHERE', async () => {
  await inRollback(async (c) => {
    const upd = await c.query(`
      UPDATE route SET base_fare = round(base_fare * 1.10, 2)
      WHERE route_id IN (SELECT route_id FROM trip GROUP BY route_id HAVING count(*) > 75)`);
    assert.equal(upd.rowCount, 10);
  });
});

test('DELETE evalúa la subconsulta antes de borrar', async () => {
  await inRollback(async (c) => {
    const below = 'price < (SELECT avg(price) FROM shipment)';
    const ev = await c.query(
      `DELETE FROM shipment_event WHERE shipment_id IN (SELECT shipment_id FROM shipment WHERE ${below})`,
    );
    assert.equal(ev.rowCount, 29090);
    const del = await c.query(`DELETE FROM shipment WHERE ${below}`);
    assert.equal(del.rowCount, 7532);
    // Si el promedio se recalculara al borrar, no quedaría nadie bajo el promedio nuevo.
    const { rows } = await c.query(`SELECT count(*)::int AS n, count(*) FILTER (WHERE ${below})::int AS bajo FROM shipment`);
    assert.equal(rows[0].n, 12000 - 7532);
    assert.ok(rows[0].bajo > 0);
  });
});

test('después de los ROLLBACK los datos siguen iguales', async () => {
  const r = await one(`
    SELECT (SELECT count(*) FROM customer)::int AS clientes,
           (SELECT count(*) FROM shipment)::int AS envios,
           (SELECT count(*) FROM shipment_event)::int AS eventos,
           (SELECT sum(base_fare) FROM route)::text AS tarifas,
           to_regclass('frequent_sender') IS NULL AS sin_tabla`);
  assert.deepEqual(r, { clientes: 1800, envios: 12000, eventos: 46421, tarifas: '2814.00', sin_tabla: true });
});

// Pruebas de la sesión A: conjuntos, NULL y agregación. Fijan los números que
// aparecen en los apuntes (con los datos de ejemplo de chasqui_w03).
// pg devuelve bigint y numeric como texto: por eso los ::int y los ::text.
import { test, after } from 'node:test';
import assert from 'node:assert/strict';
import { pool, one, all } from './db.mjs';

after(() => pool.end());

test('UNION elimina duplicados; UNION ALL los conserva', async () => {
  const r = await one(`
    SELECT (SELECT count(*) FROM (SELECT city FROM agency
                                  UNION SELECT city FROM customer) AS t)::int AS u,
           (SELECT count(*) FROM (SELECT city FROM agency
                                  UNION ALL SELECT city FROM customer) AS t)::int AS ua`);
  assert.deepEqual(r, { u: 26, ua: 1829 });
});

test('EXCEPT no es simétrico; INTERSECT es la parte común', async () => {
  const r = await one(`
    SELECT (SELECT count(*) FROM (SELECT sender_id FROM shipment
            EXCEPT SELECT recipient_id FROM shipment) AS t)::int AS s_menos_r,
           (SELECT count(*) FROM (SELECT recipient_id FROM shipment
            EXCEPT SELECT sender_id FROM shipment) AS t)::int AS r_menos_s,
           (SELECT count(*) FROM (SELECT sender_id FROM shipment
            INTERSECT SELECT recipient_id FROM shipment) AS t)::int AS ambos`);
  assert.deepEqual(r, { s_menos_r: 20, r_menos_s: 40, ambos: 1698 });
});

test('EXCEPT ALL e INTERSECT ALL cuentan copias', async () => {
  const exceptAll = await all(`
    SELECT city FROM agency EXCEPT ALL SELECT DISTINCT city FROM agency ORDER BY city`);
  assert.deepEqual(exceptAll.map((r) => r.city), ['Arequipa', 'Lima', 'Lima']);
  const inter = await all(`
    (VALUES ('Lima'), ('Lima'), ('Lima'), ('Cusco'))
    INTERSECT ALL (VALUES ('Lima'), ('Lima'), ('Cusco'), ('Cusco')) ORDER BY 1`);
  assert.deepEqual(inter.map((r) => r.column1), ['Cusco', 'Lima', 'Lima']);
});

test('para eliminar duplicados dos NULL son iguales', async () => {
  const rows = await all('SELECT NULL AS x UNION SELECT NULL');
  assert.deepEqual(rows, [{ x: null }]);
});

test('NULL en aritmética y comparaciones da NULL', async () => {
  const r = await one('SELECT 10 + NULL AS s, NULL = NULL AS eq, NULL IS NULL AS isnull');
  assert.deepEqual(r, { s: null, eq: null, isnull: true });
});

test('tablas de verdad de la lógica de tres valores', async () => {
  const r = await one(`
    SELECT (true AND NULL) AS t_and_u, (false AND NULL) AS f_and_u,
           (true OR NULL) AS t_or_u, (false OR NULL) AS f_or_u,
           (NOT NULL::boolean) AS not_u`);
  assert.deepEqual(r, { t_and_u: null, f_and_u: false, t_or_u: true, f_or_u: null, not_u: null });
});

test('x > 500 OR x <= 500 no devuelve las filas con x NULL', async () => {
  const r = await one(`
    SELECT count(*)::int AS total,
           count(*) FILTER (WHERE declared_value > 500 OR declared_value <= 500)::int AS cualquiera,
           count(*) FILTER (WHERE declared_value IS NULL)::int AS sin_valor,
           count(*) FILTER (WHERE NOT (declared_value > 500))::int AS no_mayor,
           count(*) FILTER (WHERE (declared_value > 500) IS UNKNOWN)::int AS desconocido
    FROM shipment`);
  assert.deepEqual(r, { total: 12000, cualquiera: 7136, sin_valor: 4864, no_mayor: 1097, desconocido: 4864 });
});

test('= NULL nunca es verdadero; IS NULL sí', async () => {
  const r = await one(`
    SELECT (SELECT count(*) FROM customer WHERE phone = NULL)::int AS eq,
           (SELECT count(*) FROM customer WHERE phone IS NULL)::int AS isnull`);
  assert.deepEqual(r, { eq: 0, isnull: 259 });
});

test('IS DISTINCT FROM trata dos NULL como iguales', async () => {
  const r = await one(`SELECT NULL IS DISTINCT FROM NULL AS a, NULL IS DISTINCT FROM 5 AS b`);
  assert.deepEqual(r, { a: false, b: true });
});

test('count(*), count(col) y count(DISTINCT col) difieren', async () => {
  const r = await one(`
    SELECT count(*)::int AS filas, count(phone)::int AS con_telefono,
           count(DISTINCT city)::int AS ciudades FROM customer`);
  assert.deepEqual(r, { filas: 1800, con_telefono: 1541, ciudades: 26 });
});

test('avg ignora los NULL: no es sum / count(*)', async () => {
  const r = await one(`
    SELECT round(avg(declared_value), 2)::text AS avg,
           round(sum(declared_value) / count(*), 2)::text AS por_fila,
           round(sum(declared_value) / count(declared_value), 2)::text AS por_valor
    FROM shipment`);
  assert.deepEqual(r, { avg: '1512.33', por_fila: '899.33', por_valor: '1512.33' });
});

test('sin GROUP BY, cero filas dan una fila: count 0 y lo demás NULL', async () => {
  const rows = await all(`
    SELECT count(*)::int AS n, count(price)::int AS n_price, sum(price) AS suma,
           avg(price) AS promedio, min(price) AS minimo, max(price) AS maximo
    FROM shipment WHERE price < 0`);
  assert.deepEqual(rows, [{ n: 0, n_price: 0, suma: null, promedio: null, minimo: null, maximo: null }]);
});

test('un HAVING puede descartar el único grupo', async () => {
  const rows = await all('SELECT count(*) FROM shipment WHERE price < 0 HAVING count(*) > 0');
  assert.equal(rows.length, 0);
});

test('array_agg, a diferencia de las cinco básicas, conserva los NULL', async () => {
  const r = await one('SELECT array_agg(x) AS a, count(x)::int AS n FROM (VALUES (1), (NULL)) AS v(x)');
  assert.deepEqual(r, { a: [1, null], n: 1 });
});

test('con GROUP BY, cero filas dan cero grupos', async () => {
  const rows = await all('SELECT status, count(*) FROM shipment WHERE price < 0 GROUP BY status');
  assert.equal(rows.length, 0);
});

test('sum de puros NULL es NULL aunque haya filas; coalesce lo vuelve 0', async () => {
  const r = await one(`
    SELECT count(*)::int AS filas, count(declared_value)::int AS valores,
           sum(declared_value) AS suma, coalesce(sum(declared_value), 0)::int AS cero
    FROM shipment WHERE declared_value IS NULL`);
  assert.deepEqual(r, { filas: 4864, valores: 0, suma: null, cero: 0 });
});

test('GROUP BY junta los NULL en un grupo; count(DISTINCT) no los cuenta', async () => {
  const first = await one(`
    SELECT trip_id, count(*)::int AS n FROM shipment
    GROUP BY trip_id ORDER BY n DESC, trip_id LIMIT 1`);
  assert.deepEqual(first, { trip_id: null, n: 500 });
  const r = await one(`
    SELECT (SELECT count(*) FROM (SELECT DISTINCT trip_id FROM shipment) AS d)::int AS valores,
           count(DISTINCT trip_id)::int AS distintos FROM shipment`);
  assert.deepEqual(r, { valores: 4877, distintos: 4876 });
});

test('envíos por estado', async () => {
  const rows = await all(`
    SELECT status, count(*)::int AS n FROM shipment GROUP BY status ORDER BY n DESC`);
  assert.deepEqual(rows, [
    { status: 'delivered', n: 11115 },
    { status: 'registered', n: 500 },
    { status: 'returned', n: 319 },
    { status: 'in_transit', n: 66 },
  ]);
});

test('HAVING filtra grupos; WHERE filtra filas antes de agrupar', async () => {
  const having = await one(`
    SELECT count(*)::int AS n FROM (SELECT route_id FROM trip
      GROUP BY route_id HAVING count(*) > 50) AS t`);
  assert.equal(having.n, 58);
  const todos = await all(`
    SELECT route_id FROM trip GROUP BY route_id HAVING count(*) > 75`);
  const llegados = await all(`
    SELECT route_id FROM trip WHERE arrival_at IS NOT NULL
    GROUP BY route_id HAVING count(*) > 75`);
  assert.equal(todos.length, 10);
  assert.equal(llegados.length, 0);
});

test('FILTER cuenta solo algunas filas del grupo', async () => {
  const r = await one(`
    SELECT count(*)::int AS envios,
           count(*) FILTER (WHERE s.status = 'delivered')::int AS entregados
    FROM agency a, shipment s
    WHERE s.origin_agency_id = a.agency_id AND a.region = 'Lima'`);
  assert.deepEqual(r, { envios: 3965, entregados: 3706 });
});

// Pruebas de la sesión B: subconsultas. La trampa de NOT IN con NULL, los
// cuantificadores con conjuntos vacíos y la división con NOT EXISTS.
import { test, after } from 'node:test';
import assert from 'node:assert/strict';
import { pool, one, all } from './db.mjs';

after(() => pool.end());

const count = async (sql, params = []) => (await one(`SELECT count(*)::int AS n FROM (${sql}) AS q`, params)).n;

test('IN: clientes que enviaron algo', async () => {
  assert.equal(await count('SELECT 1 FROM customer WHERE customer_id IN (SELECT sender_id FROM shipment)'), 1718);
});

test('NOT IN con un NULL en la subconsulta no devuelve filas', async () => {
  assert.equal(await count('SELECT trip_id FROM trip WHERE trip_id NOT IN (SELECT trip_id FROM shipment)'), 0);
});

test('las tres correcciones de NOT IN coinciden', async () => {
  const notExists = await all(`
    SELECT trip_id FROM trip t
    WHERE NOT EXISTS (SELECT 1 FROM shipment s WHERE s.trip_id = t.trip_id) ORDER BY trip_id`);
  const notInFiltered = await all(`
    SELECT trip_id FROM trip
    WHERE trip_id NOT IN (SELECT trip_id FROM shipment WHERE trip_id IS NOT NULL) ORDER BY trip_id`);
  const except = await all(`
    SELECT trip_id FROM trip EXCEPT SELECT trip_id FROM shipment ORDER BY trip_id`);
  assert.equal(notExists.length, 1038);
  assert.deepEqual(notInFiltered, notExists);
  assert.deepEqual(except, notExists);
});

test('IN y NOT IN con NULL en la lista', async () => {
  const r = await one(`
    SELECT 3 NOT IN (1, 2) AS a, 3 NOT IN (1, 2, NULL) AS b,
           1 NOT IN (1, 2, NULL) AS c, 3 IN (1, 2, NULL) AS d`);
  assert.deepEqual(r, { a: true, b: null, c: false, d: null });
});

test('> ALL equivale a > max cuando no hay NULL', async () => {
  const all1 = await count(`SELECT 1 FROM shipment
    WHERE price > ALL (SELECT price FROM shipment WHERE origin_agency_id = $1)`, [10]);
  const max1 = await count(`SELECT 1 FROM shipment
    WHERE price > (SELECT max(price) FROM shipment WHERE origin_agency_id = $1)`, [10]);
  assert.equal(all1, 409);
  assert.equal(max1, all1);
});

test('ALL y SOME con NULL', async () => {
  const r = await one(`
    SELECT 5 > ALL (VALUES (1), (NULL::int)) AS all_u,
           5 > ALL (VALUES (9), (NULL::int)) AS all_f,
           5 > SOME (VALUES (9), (NULL::int)) AS some_u,
           5 > SOME (VALUES (1), (NULL::int)) AS some_t`);
  assert.deepEqual(r, { all_u: null, all_f: false, some_u: null, some_t: true });
});

test('ALL sobre un conjunto vacío es verdadero; SOME es falso', async () => {
  const r = await one(`
    SELECT 5 > ALL (SELECT 1 WHERE false) AS a,
           5 > SOME (SELECT 1 WHERE false) AS s,
           5 > (SELECT max(1) WHERE false) AS m,
           NULL::int NOT IN (SELECT 1 WHERE false) AS n`);
  // > ALL y > max no son equivalentes cuando la subconsulta es vacía.
  assert.deepEqual(r, { a: true, s: false, m: null, n: true });
});

test('NOT EXISTS: clientes que nunca recibieron un envío', async () => {
  assert.equal(await count(`SELECT 1 FROM customer c
    WHERE NOT EXISTS (SELECT 1 FROM shipment s WHERE s.recipient_id = c.customer_id)`), 62);
});

// La división: clientes que enviaron a todas las agencias de una región.
const division = (region) => count(`
  SELECT 1 FROM customer c
  WHERE NOT EXISTS (
    SELECT 1 FROM agency a
    WHERE a.region = $1
      AND NOT EXISTS (SELECT 1 FROM shipment s
                      WHERE s.sender_id = c.customer_id AND s.dest_agency_id = a.agency_id))`, [region]);

test('para todo con doble NOT EXISTS coincide con la versión que cuenta', async () => {
  const byCount = await count(`
    SELECT s.sender_id FROM shipment s, agency a
    WHERE s.dest_agency_id = a.agency_id AND a.region = $1
    GROUP BY s.sender_id
    HAVING count(DISTINCT a.agency_id) = (SELECT count(*) FROM agency WHERE region = $1)`, ['Lima']);
  assert.equal(await division('Lima'), 175);
  assert.equal(byCount, 175);
});

test('con un divisor vacío, el resultado depende de qué recorre la consulta de afuera', async () => {
  const region = 'Lima Metropolitana';
  // Doble NOT EXISTS sobre customer: todos los clientes.
  assert.equal(await division(region), 1800);
  // La división del álgebra recorre el dividendo: todos los remitentes.
  const dividendo = await count(`
    SELECT 1 FROM (SELECT DISTINCT sender_id FROM shipment) AS r
    WHERE NOT EXISTS (
      SELECT 1 FROM agency a
      WHERE a.region = $1
        AND NOT EXISTS (SELECT 1 FROM shipment s
                        WHERE s.sender_id = r.sender_id AND s.dest_agency_id = a.agency_id))`, [region]);
  assert.equal(dividendo, 1718);
  // La versión que cuenta: nadie.
  const contando = await count(`
    SELECT s.sender_id FROM shipment s, agency a
    WHERE s.dest_agency_id = a.agency_id AND a.region = $1
    GROUP BY s.sender_id
    HAVING count(DISTINCT a.agency_id) = (SELECT count(*) FROM agency WHERE region = $1)`, [region]);
  assert.equal(contando, 0);
});

test('WITH: rutas con más peso que el promedio', async () => {
  const r = await one(`
    WITH route_load AS (
      SELECT t.route_id, sum(s.weight_kg) AS total_kg
      FROM shipment s, trip t WHERE s.trip_id = t.trip_id GROUP BY t.route_id)
    SELECT count(*)::int AS sobre, (SELECT count(*) FROM route_load)::int AS rutas
    FROM route_load WHERE total_kg > (SELECT avg(total_kg) FROM route_load)`);
  assert.deepEqual(r, { sobre: 58, rutas: 120 });
});

test('subconsulta escalar sin filas vale NULL', async () => {
  const r = await one('SELECT (SELECT name FROM agency WHERE agency_id = 99) AS agencia');
  assert.equal(r.agencia, null);
});

test('LATERAL: el último evento de cada envío', async () => {
  const rows = await all(`
    SELECT s.shipment_id, last.kind
    FROM shipment s
    CROSS JOIN LATERAL (SELECT e.kind FROM shipment_event e
                        WHERE e.shipment_id = s.shipment_id
                        ORDER BY e.seq DESC LIMIT 1) AS last
    WHERE s.shipment_id = ANY ($1) ORDER BY s.shipment_id`, [[1, 36, 46, 63]]);
  assert.deepEqual(rows.map((r) => r.kind), ['delivered', 'received', 'departed', 'returned']);
});

# Semana 3: conjuntos, NULL, agregación, subconsultas y modificación de datos.
# Se usa dentro del contenedor del curso.
#
#   make          crea la base de datos chasqui_w03 con los datos de ejemplo
#   make test     la vuelve a crear, corre los ejemplos (*.sql) y las pruebas
#                 (*.test.mjs), un archivo a la vez, porque algunas modifican
#                 las mismas filas dentro de transacciones
#   make clean    borra chasqui_w03

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

.PHONY: all test clean

all:
	chasqui-load $(DB)

test: all
	$(PSQL) -f sets-null-aggregates.sql > /dev/null
	$(PSQL) -f subqueries.sql > /dev/null
	$(PSQL) -f modification.sql > /dev/null
	PGDATABASE=$(DB) node --test --test-concurrency=1 --test-reporter=spec

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