-- Errores a propósito: el esquema de Chasqui rechaza datos que violan sus
-- claves y sus restricciones. Cada sentencia falla sola y no cambia nada.
-- Desde psql: \i constraints.sql
-- (make test no corre este archivo; las pruebas de keys.test.mjs verifican
-- los mismos errores).

-- Clave candidata {dni}: dos clientes no pueden tener el mismo DNI.
INSERT INTO customer (customer_id, dni, first_name, last_name, city)
VALUES (1801, '61830975', 'Rosa', 'Chávez Ríos', 'Ayacucho');

-- Clave primaria: no puede faltar.
INSERT INTO customer (customer_id, dni, first_name, last_name, city)
VALUES (NULL, '00000001', 'Rosa', 'Chávez Ríos', 'Ayacucho');

-- Clave primaria: no puede repetirse.
INSERT INTO customer (customer_id, dni, first_name, last_name, city)
VALUES (1, '00000001', 'Rosa', 'Chávez Ríos', 'Ayacucho');

-- Integridad referencial: el remitente 99999 no existe.
INSERT INTO shipment (shipment_id, sender_id, recipient_id, origin_agency_id,
                      dest_agency_id, weight_kg, price, status, created_at)
VALUES (12001, 99999, 337, 9, 8, 2.5, 15.00, 'registered', now());

-- Integridad referencial, del otro lado: no se puede borrar una agencia a la
-- que todavía apuntan rutas y envíos.
DELETE FROM agency WHERE agency_id = 1;

-- Una regla que en el archivo nadie verificaba: el peso debe ser positivo.
INSERT INTO shipment (shipment_id, sender_id, recipient_id, origin_agency_id,
                      dest_agency_id, weight_kg, price, status, created_at)
VALUES (12001, 178, 337, 9, 8, -5, 15.00, 'registered', now());

-- Una clave foránea tiene que apuntar a una clave: customer.city no lo es.
CREATE TABLE complaint (
  complaint_id integer PRIMARY KEY,
  city         text REFERENCES customer (city)
);

-- Ejemplos de la semana 1 sobre la base de datos de Chasqui.
-- Desde psql: \i examples.sql   (o: psql -d chasqui_w01 -f examples.sql)
-- Todo lo que modifica algo va entre BEGIN y ROLLBACK: al terminar, la base
-- de datos queda como estaba. Los errores a propósito están en constraints.sql.

-- ------------------------------------------------------------
-- Sesión A: el DBMS
-- ------------------------------------------------------------

-- Una consulta declarativa: se dice qué se quiere, no cómo buscarlo.
SELECT name, city
FROM agency
WHERE region = 'Puno';

-- La pregunta "¿cuántos envíos hay en cada estado?" es una consulta, no un
-- programa nuevo (compárenlo con countByStatus en files.mjs).
SELECT status, count(*)
FROM shipment
GROUP BY status
ORDER BY status;

-- Nivel físico: la tabla customer es un archivo dentro del directorio de datos.
SELECT pg_relation_filepath('customer') AS file,
       pg_size_pretty(pg_relation_size('customer')) AS size;

-- Independencia física: agregar un índice cambia cómo se buscan las filas,
-- pero no la consulta ni su resultado.
BEGIN;
SELECT count(*) FROM customer WHERE city = 'Arequipa';
EXPLAIN (COSTS OFF) SELECT count(*) FROM customer WHERE city = 'Arequipa';
CREATE INDEX customer_city_idx ON customer (city);
SELECT count(*) FROM customer WHERE city = 'Arequipa';
EXPLAIN (COSTS OFF) SELECT count(*) FROM customer WHERE city = 'Arequipa';
ROLLBACK;

-- Nivel de vistas: un usuario de counter ve los clientes sin DNI ni teléfono.
BEGIN;
CREATE VIEW customer_counter AS
  SELECT customer_id, first_name, last_name, city
  FROM customer;
SELECT * FROM customer_counter ORDER BY customer_id LIMIT 3;
ROLLBACK;

-- DML: un cambio de teléfono toca una sola fila.
BEGIN;
UPDATE customer SET phone = '999000111' WHERE dni = '48479833';
ROLLBACK;

-- Cliente y servidor: con quién estoy conectado.
SELECT current_database(), current_user, pg_backend_pid() > 0 AS has_backend;

-- ------------------------------------------------------------
-- Sesión B: el modelo relacional
-- ------------------------------------------------------------

-- El esquema de la relación customer (meta-comando de psql).
\d customer

-- Una parte de la instancia actual.
SELECT * FROM customer ORDER BY customer_id LIMIT 5;

-- NULL: teléfono desconocido; fecha de entrega que todavía no aplica.
SELECT count(*) AS customers, count(phone) AS with_phone
FROM customer;

SELECT status, count(*) AS shipments, count(delivered_at) AS with_delivered_at,
       count(trip_id) AS with_trip
FROM shipment
GROUP BY status
ORDER BY status;

-- Una tabla de SQL no es un conjunto: SELECT city repite valores.
SELECT count(*) AS rows, count(DISTINCT city) AS distinct_cities
FROM customer;

-- ¿{dni} es superclave en la instancia de hoy? Ningún grupo repetido.
SELECT dni, count(*)
FROM customer
GROUP BY dni
HAVING count(*) > 1;

-- ¿{first_name, last_name}? Hay contraejemplos: no es superclave.
SELECT first_name, last_name, count(*)
FROM customer
GROUP BY first_name, last_name
HAVING count(*) > 1
ORDER BY last_name, first_name
LIMIT 5;

SELECT count(*) AS rows, count(DISTINCT (first_name, last_name)) AS distinct_names
FROM customer;

-- ¿{phone}? Cuidado con NULL: GROUP BY junta todos los NULL en un grupo.
SELECT phone, count(*)
FROM customer
GROUP BY phone
HAVING count(*) > 1;

SELECT phone, count(*)
FROM customer
WHERE phone IS NOT NULL
GROUP BY phone
HAVING count(*) > 1;

-- {customer_id, city} es superclave, pero no mínima: {customer_id} ya basta.
SELECT customer_id, city, count(*)
FROM customer
GROUP BY customer_id, city
HAVING count(*) > 1;

-- shipment_event: {shipment_id} solo no identifica un evento.
SELECT count(*) AS events, count(DISTINCT shipment_id) AS shipments,
       count(DISTINCT (shipment_id, seq)) AS distinct_pairs
FROM shipment_event;

SELECT * FROM shipment_event WHERE shipment_id = 1 ORDER BY seq;

-- route: dos claves candidatas, {route_id} y {origin_agency_id, dest_agency_id}.
\d route

SELECT * FROM route ORDER BY route_id LIMIT 3;

-- Claves foráneas: un envío apunta a dos clientes y a dos agencias.
SELECT * FROM shipment WHERE shipment_id = 1;

-- phone no es clave: el esquema acepta otro cliente con el mismo teléfono.
BEGIN;
INSERT INTO customer (customer_id, dni, first_name, last_name, phone, city)
VALUES (1801, '00000001', 'Rosa', 'Chávez Ríos', '909975104', 'Ayacucho');
SELECT customer_id, dni, first_name, last_name, phone
FROM customer
WHERE phone = '909975104'
ORDER BY customer_id;
ROLLBACK;

// Chasqui con archivos: un CSV de envíos por agencia, mantenido por un
// programa propio. Sirve para ver los problemas que resuelve un DBMS.
//
//   node files.mjs     exporta los envíos a archivos temporales y muestra
//                      redundancia, inconsistencia, un corte a medio escribir
//                      y una regla que nadie verifica
import fs from 'node:fs';
import os from 'node:os';
import path from 'node:path';
import pg from 'pg';

// Columnas de cada archivo. Los datos del remitente se copian en cada fila.
export const COLUMNS = [
  'shipment_id', 'created_at', 'sender_dni', 'sender_name', 'sender_phone',
  'dest_city', 'weight_kg', 'price', 'status',
];

const fileName = (dir, agencyId) => path.join(dir, `agency-${String(agencyId).padStart(2, '0')}.csv`);

// CSV mínimo: sin comillas, así que ningún campo puede tener comas.
const toLine = (row) => COLUMNS.map((c) => {
  const v = String(row[c] ?? '');
  if (v.includes(',') || v.includes('\n')) throw new Error(`campo con coma: ${v}`);
  return v;
}).join(',');

export function readCsv(file) {
  const [header, ...lines] = fs.readFileSync(file, 'utf8').split('\n').filter((l) => l !== '');
  const cols = header.split(',');
  return lines.map((l) => Object.fromEntries(l.split(',').map((v, i) => [cols[i], v])));
}

// Reescribe el archivo completo, fila por fila, como haría un programa simple.
// Con crashAfter = k, el "corte de luz" llega después de escribir k filas.
export function writeCsv(file, rows, { crashAfter = Infinity } = {}) {
  const fd = fs.openSync(file, 'w'); // 'w' vacía el archivo antes de escribir
  try {
    fs.writeSync(fd, COLUMNS.join(',') + '\n');
    rows.forEach((row, i) => {
      if (i === crashAfter) throw new Error('corte de luz');
      fs.writeSync(fd, toLine(row) + '\n');
    });
  } finally {
    fs.closeSync(fd);
  }
}

export const listFiles = (dir) =>
  fs.readdirSync(dir).filter((f) => f.endsWith('.csv')).sort().map((f) => path.join(dir, f));

// Un archivo por agencia de origen, con los envíos que salieron de ella.
export async function exportAgencyFiles(pool, dir) {
  const { rows } = await pool.query(
    `SELECT s.origin_agency_id, s.shipment_id,
            to_char(s.created_at, 'YYYY-MM-DD') AS created_at,
            c.dni AS sender_dni, c.first_name || ' ' || c.last_name AS sender_name,
            c.phone AS sender_phone, d.city AS dest_city,
            s.weight_kg, s.price, s.status
     FROM shipment s
       JOIN customer c ON c.customer_id = s.sender_id
       JOIN agency d ON d.agency_id = s.dest_agency_id
     ORDER BY s.shipment_id`,
  );
  const byAgency = Map.groupBy(rows, (r) => r.origin_agency_id);
  for (const [agencyId, list] of byAgency) writeCsv(fileName(dir, agencyId), list);
  return { files: byAgency.size, rows: rows.length };
}

// Cambia el teléfono de un cliente en un solo archivo. Devuelve cuántas filas cambió.
export function updatePhone(file, dni, phone, opts) {
  const rows = readCsv(file);
  let changed = 0;
  for (const r of rows) {
    if (r.sender_dni === dni) {
      r.sender_phone = phone;
      changed++;
    }
  }
  writeCsv(file, rows, opts);
  return changed;
}

// En qué archivos aparece un cliente y con qué teléfonos.
export function findCustomer(dir, dni) {
  const files = new Set();
  const phones = new Set();
  let rows = 0;
  for (const file of listFiles(dir)) {
    for (const r of readCsv(file)) {
      if (r.sender_dni === dni) {
        files.add(file);
        phones.add(r.sender_phone);
        rows++;
      }
    }
  }
  return { files: [...files], phones: [...phones], rows };
}

// Una pregunta nueva ("¿cuántos envíos hay en cada estado?") exige otro programa.
export function countByStatus(dir) {
  const counts = {};
  for (const file of listFiles(dir)) {
    for (const r of readCsv(file)) counts[r.status] = (counts[r.status] ?? 0) + 1;
  }
  return counts;
}

// Agregar una fila: el archivo acepta cualquier cosa.
export function appendShipment(file, row) {
  fs.appendFileSync(file, toLine(row) + '\n');
}

export const makeTempDir = () => fs.mkdtempSync(path.join(os.tmpdir(), 'chasqui-csv-'));

// El cliente que envió desde más agencias distintas (desempate por DNI).
export async function mostSpreadCustomer(pool) {
  const { rows } = await pool.query(
    `SELECT c.dni, count(DISTINCT s.origin_agency_id) AS agencies, count(*) AS shipments
     FROM customer c JOIN shipment s ON s.sender_id = c.customer_id
     WHERE c.phone IS NOT NULL
     GROUP BY c.dni
     ORDER BY agencies DESC, c.dni
     LIMIT 1`,
  );
  return rows[0];
}

async function main() {
  const pool = new pg.Pool();
  const dir = makeTempDir();
  try {
    const { files, rows } = await exportAgencyFiles(pool, dir);
    console.log(`${files} archivos, ${rows} filas en total`);

    // Redundancia: el teléfono del remitente se repite en cada envío.
    const { dni } = await mostSpreadCustomer(pool);
    const before = findCustomer(dir, dni);
    console.log(`DNI ${dni}: ${before.rows} filas en ${before.files.length} archivos, teléfonos ${before.phones.join(' ')}`);

    // Inconsistencia: el cliente avisa en una agencia y solo se corrige ese archivo.
    const changed = updatePhone(before.files[0], dni, '999000111');
    const after = findCustomer(dir, dni);
    console.log(`se corrigieron ${changed} filas de ${path.basename(before.files[0])}; teléfonos ahora: ${after.phones.join(' ')}`);

    // En la base de datos el teléfono está en una sola fila de customer.
    const client = await pool.connect();
    try {
      await client.query('BEGIN');
      const r = await client.query('UPDATE customer SET phone = $1 WHERE dni = $2', ['999000111', dni]);
      console.log(`en la base de datos: UPDATE ${r.rowCount}`);
      await client.query('ROLLBACK');
    } finally {
      client.release();
    }

    // Atomicidad: un corte de luz a mitad de la reescritura.
    const file = before.files[1];
    const total = readCsv(file).length;
    try {
      updatePhone(file, dni, '999000111', { crashAfter: Math.floor(total / 2) });
    } catch (e) {
      console.log(`${e.message} reescribiendo ${path.basename(file)}: tenía ${total} filas, quedan ${readCsv(file).length}`);
    }

    // Reglas escondidas: el archivo acepta un peso negativo.
    appendShipment(before.files[2], { ...readCsv(before.files[2])[0], shipment_id: '99999', weight_kg: '-5.00' });
    console.log(`fila con peso -5.00 agregada sin quejas a ${path.basename(before.files[2])}`);
  } finally {
    fs.rmSync(dir, { recursive: true, force: true });
    await pool.end();
  }
}

if (import.meta.main) await main();

// Claves del esquema de Chasqui: las que declara el esquema (catálogo de
// PostgreSQL) y una prueba sobre los datos de hoy para un conjunto de atributos.
//
//   node keys.mjs      imprime claves primarias, UNIQUE y claves foráneas de
//                      cada tabla, y prueba algunos conjuntos de customer
//
// Ojo: la prueba sobre los datos solo puede REFUTAR que un conjunto sea
// superclave (encontrando dos filas iguales). Nunca lo demuestra: una clave es
// una regla sobre todas las instancias válidas, no sobre la de hoy.
import pg from 'pg';

// Los nombres de tabla y columna no pueden ir como parámetros ($1): se
// verifican contra el catálogo y se escriben con escapeIdentifier.
async function checkColumns(pool, table, columns) {
  const { rows } = await pool.query(
    `SELECT column_name FROM information_schema.columns
     WHERE table_schema = 'public' AND table_name = $1`,
    [table],
  );
  const known = new Set(rows.map((r) => r.column_name));
  if (known.size === 0) throw new Error(`no existe la tabla ${table}`);
  for (const c of columns) if (!known.has(c)) throw new Error(`${table} no tiene la columna ${c}`);
}

// Revisa la instancia actual. Devuelve cuántas filas tienen NULL en alguno de
// los atributos y cuántos grupos de valores (sin NULL) se repiten.
export async function checkSuperkey(pool, table, columns) {
  await checkColumns(pool, table, columns);
  const t = pg.escapeIdentifier(table);
  const cols = columns.map((c) => pg.escapeIdentifier(c));
  const anyNull = cols.map((c) => `${c} IS NULL`).join(' OR ');
  const noNull = cols.map((c) => `${c} IS NOT NULL`).join(' AND ');
  const list = cols.join(', ');

  const nulls = await pool.query(`SELECT count(*) AS n FROM ${t} WHERE ${anyNull}`);
  const dups = await pool.query(
    `SELECT ${list}, count(*) AS n
     FROM ${t}
     WHERE ${noNull}
     GROUP BY ${list}
     HAVING count(*) > 1
     ORDER BY n DESC, ${list}`,
  );
  return {
    nulls: Number(nulls.rows[0].n),
    duplicateGroups: dups.rowCount,
    examples: dups.rows.slice(0, 3),
  };
}

// Claves primarias (p) y restricciones UNIQUE (u) declaradas en una tabla.
export async function declaredKeys(pool, table) {
  const { rows } = await pool.query(
    `SELECT con.conname AS name, con.contype AS type,
            array_agg(att.attname::text ORDER BY k.ord) AS columns
     FROM pg_constraint con
       CROSS JOIN LATERAL unnest(con.conkey) WITH ORDINALITY AS k(attnum, ord)
       JOIN pg_attribute att ON att.attrelid = con.conrelid AND att.attnum = k.attnum
     WHERE con.conrelid = $1::regclass AND con.contype IN ('p', 'u')
     GROUP BY con.conname, con.contype
     ORDER BY con.contype, con.conname`,
    [table],
  );
  return rows;
}

// Claves foráneas de una tabla: columnas locales y tabla referenciada.
export async function foreignKeys(pool, table) {
  const { rows } = await pool.query(
    `SELECT con.conname AS name, con.confrelid::regclass::text AS refers_to,
            array_agg(att.attname::text ORDER BY k.ord) AS columns
     FROM pg_constraint con
       CROSS JOIN LATERAL unnest(con.conkey) WITH ORDINALITY AS k(attnum, ord)
       JOIN pg_attribute att ON att.attrelid = con.conrelid AND att.attnum = k.attnum
     WHERE con.conrelid = $1::regclass AND con.contype = 'f'
     GROUP BY con.conname, con.confrelid
     ORDER BY con.conname`,
    [table],
  );
  return rows;
}

export const TABLES = ['agency', 'customer', 'vehicle', 'route', 'trip', 'shipment', 'shipment_event'];

async function main() {
  const pool = new pg.Pool();
  try {
    for (const table of TABLES) {
      const keys = await declaredKeys(pool, table);
      const fks = await foreignKeys(pool, table);
      const fmt = (k) => `{${k.columns.join(', ')}}`;
      const pk = keys.filter((k) => k.type === 'p').map(fmt);
      const uq = keys.filter((k) => k.type === 'u').map(fmt);
      console.log(`${table}: PK ${pk.join(' ')}${uq.length ? `  UNIQUE ${uq.join(' ')}` : ''}`);
      for (const fk of fks) console.log(`  ${fk.columns.join(', ')} -> ${fk.refers_to}`);
    }
    console.log('customer, sobre los datos de hoy:');
    for (const cols of [['dni'], ['first_name', 'last_name'], ['phone']]) {
      const r = await checkSuperkey(pool, 'customer', cols);
      console.log(`  {${cols.join(', ')}}: ${r.nulls} filas con NULL, ${r.duplicateGroups} grupos repetidos`);
    }
  } finally {
    await pool.end();
  }
}

if (import.meta.main) await main();

// Pruebas de files.mjs: el programa con archivos responde lo mismo que la base
// de datos, pero deja pasar inconsistencias, cortes a medias y datos absurdos.
// Usan la base de datos chasqui_w01 (make test la crea).
import { test, before, after } from 'node:test';
import assert from 'node:assert/strict';
import fs from 'node:fs';
import pg from 'pg';
import {
  exportAgencyFiles, listFiles, readCsv, updatePhone, findCustomer,
  countByStatus, appendShipment, makeTempDir, mostSpreadCustomer,
} from './files.mjs';

const pool = new pg.Pool();
let dir;
before(async () => {
  dir = makeTempDir();
  await exportAgencyFiles(pool, dir);
});
after(async () => {
  fs.rmSync(dir, { recursive: true, force: true });
  await pool.end();
});

test('un archivo por agencia de origen, con todos los envíos', async () => {
  const { rows } = await pool.query(
    'SELECT count(DISTINCT origin_agency_id) AS agencies, count(*) AS n FROM shipment');
  assert.equal(listFiles(dir).length, Number(rows[0].agencies));
  const total = listFiles(dir).reduce((sum, f) => sum + readCsv(f).length, 0);
  assert.equal(total, Number(rows[0].n));
});

test('countByStatus (un programa nuevo) coincide con GROUP BY (una consulta)', async () => {
  const { rows } = await pool.query('SELECT status, count(*) AS n FROM shipment GROUP BY status');
  const expected = Object.fromEntries(rows.map((r) => [r.status, Number(r.n)]));
  assert.deepEqual(countByStatus(dir), expected);
});

test('redundancia: el teléfono de un cliente se copia en cada envío', async () => {
  const { dni, agencies, shipments } = await mostSpreadCustomer(pool);
  const found = findCustomer(dir, dni);
  assert.equal(found.files.length, Number(agencies));
  assert.equal(found.rows, Number(shipments));
  assert.equal(found.phones.length, 1);
});

test('inconsistencia: corregir un solo archivo deja dos teléfonos', async () => {
  const { dni } = await mostSpreadCustomer(pool);
  const [first] = findCustomer(dir, dni).files;
  assert.ok(updatePhone(first, dni, '999000111') > 0);
  assert.equal(findCustomer(dir, dni).phones.length, 2);
});

test('en la base de datos el mismo cambio toca una sola fila', async () => {
  const { dni } = await mostSpreadCustomer(pool);
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const r = await client.query('UPDATE customer SET phone = $1 WHERE dni = $2', ['999000111', dni]);
    assert.equal(r.rowCount, 1);
  } finally {
    await client.query('ROLLBACK');
    client.release();
  }
});

test('un corte a mitad de la reescritura pierde filas', () => {
  const file = listFiles(dir)[1];
  const total = readCsv(file).length;
  assert.throws(() => updatePhone(file, '00000000', '999000111', { crashAfter: 10 }), /corte de luz/);
  assert.equal(readCsv(file).length, 10);
  assert.ok(total > 10);
});

test('el archivo acepta un peso negativo; la base de datos no', async () => {
  const file = listFiles(dir)[2];
  const row = { ...readCsv(file)[0], shipment_id: '99999', weight_kg: '-5.00' };
  appendShipment(file, row);
  assert.equal(readCsv(file).at(-1).weight_kg, '-5.00');

  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await assert.rejects(
      client.query(
        `INSERT INTO shipment (shipment_id, sender_id, recipient_id, origin_agency_id,
           dest_agency_id, weight_kg, price, status, created_at)
         VALUES (12001, 178, 337, 9, 8, $1, 15.00, 'registered', now())`,
        [-5],
      ),
      { code: '23514', constraint: 'shipment_weight_kg_check' },
    );
  } finally {
    await client.query('ROLLBACK');
    client.release();
  }
});

// Pruebas de las claves de Chasqui: lo que declara el esquema, lo que dicen los
// datos de hoy y los errores que da PostgreSQL al violar una restricción.
// Usan la base de datos chasqui_w01 (make test la crea).
import { test, after } from 'node:test';
import assert from 'node:assert/strict';
import pg from 'pg';
import { checkSuperkey, declaredKeys, foreignKeys } from './keys.mjs';

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

// Ejecuta fn dentro de una transacción que siempre termina en ROLLBACK.
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();
  }
}

// Espera que la sentencia falle con ese SQLSTATE y esa restricción.
const rejectsWith = (sql, params, code, constraint) =>
  inRollback((c) => assert.rejects(c.query(sql, params), (e) => {
    assert.equal(e.code, code);
    if (constraint) assert.equal(e.constraint, constraint);
    return true;
  }));

test('customer declara {customer_id} como clave primaria y {dni} como UNIQUE', async () => {
  assert.deepEqual(await declaredKeys(pool, 'customer'), [
    { name: 'customer_pkey', type: 'p', columns: ['customer_id'] },
    { name: 'customer_dni_key', type: 'u', columns: ['dni'] },
  ]);
});

test('route tiene dos claves candidatas y shipment_event una clave primaria compuesta', async () => {
  const route = await declaredKeys(pool, 'route');
  assert.deepEqual(route.map((k) => k.columns), [['route_id'], ['origin_agency_id', 'dest_agency_id']]);
  const event = await declaredKeys(pool, 'shipment_event');
  assert.deepEqual(event.map((k) => k.columns), [['shipment_id', 'seq']]);
});

test('route tiene dos claves foráneas distintas hacia agency', async () => {
  const fks = await foreignKeys(pool, 'route');
  assert.deepEqual(fks.map((f) => [f.columns, f.refers_to]), [
    [['dest_agency_id'], 'agency'],
    [['origin_agency_id'], 'agency'],
  ]);
});

test('shipment tiene cinco claves foráneas', async () => {
  const fks = await foreignKeys(pool, 'shipment');
  assert.deepEqual(fks.map((f) => `${f.columns}->${f.refers_to}`), [
    'dest_agency_id->agency', 'origin_agency_id->agency', 'recipient_id->customer',
    'sender_id->customer', 'trip_id->trip',
  ]);
});

test('los datos de hoy no refutan {dni} ni {customer_id, city}', async () => {
  assert.deepEqual(await checkSuperkey(pool, 'customer', ['dni']), { nulls: 0, duplicateGroups: 0, examples: [] });
  const r = await checkSuperkey(pool, 'customer', ['customer_id', 'city']);
  assert.equal(r.duplicateGroups, 0);
});

test('{first_name, last_name} queda refutada: hay nombres repetidos', async () => {
  const r = await checkSuperkey(pool, 'customer', ['first_name', 'last_name']);
  assert.equal(r.duplicateGroups, 27);
  assert.ok(r.examples.every((e) => e.n === '2'));
  const { rows } = await pool.query(
    'SELECT count(*) AS n, count(DISTINCT (first_name, last_name)) AS d FROM customer');
  assert.deepEqual(rows[0], { n: '1800', d: '1773' });
});

test('{shipment_id} no identifica un evento; {shipment_id, seq} sí', async () => {
  assert.ok((await checkSuperkey(pool, 'shipment_event', ['shipment_id'])).duplicateGroups > 0);
  assert.equal((await checkSuperkey(pool, 'shipment_event', ['shipment_id', 'seq'])).duplicateGroups, 0);
});

test('phone: sin repetidos hoy, con NULL, y aun así no es clave', async () => {
  assert.deepEqual(await checkSuperkey(pool, 'customer', ['phone']), { nulls: 259, duplicateGroups: 0, examples: [] });
  // Con GROUP BY, los 259 NULL forman un solo grupo.
  const { rows } = await pool.query('SELECT phone, count(*) AS n FROM customer GROUP BY phone HAVING count(*) > 1');
  assert.deepEqual(rows, [{ phone: null, n: '259' }]);
  // El esquema acepta un segundo cliente con el mismo teléfono.
  await inRollback(async (c) => {
    await c.query(
      `INSERT INTO customer (customer_id, dni, first_name, last_name, phone, city)
       VALUES ($1, $2, $3, $4, $5, $6)`,
      [1801, '00000001', 'Rosa', 'Chávez Ríos', '909975104', 'Ayacucho'],
    );
    const r = await c.query('SELECT count(*) AS n FROM customer WHERE phone = $1', ['909975104']);
    assert.equal(r.rows[0].n, '2');
  });
});

test('checkSuperkey no acepta columnas que no existen', async () => {
  await assert.rejects(checkSuperkey(pool, 'customer', ['email']), /no tiene la columna email/);
  await assert.rejects(checkSuperkey(pool, 'clientes', ['dni']), /no existe la tabla clientes/);
});

const NEW_CUSTOMER = `INSERT INTO customer (customer_id, dni, first_name, last_name, city)
                      VALUES ($1, $2, 'Rosa', 'Chávez Ríos', 'Ayacucho')`;
const NEW_SHIPMENT = `INSERT INTO shipment (shipment_id, sender_id, recipient_id, origin_agency_id,
                        dest_agency_id, weight_kg, price, status, created_at)
                      VALUES (12001, $1, 337, 9, 8, $2, 15.00, 'registered', now())`;

test('DNI repetido: 23505 en customer_dni_key', () =>
  rejectsWith(NEW_CUSTOMER, [1801, '61830975'], '23505', 'customer_dni_key'));

test('clave primaria repetida: 23505 en customer_pkey', () =>
  rejectsWith(NEW_CUSTOMER, [1, '00000001'], '23505', 'customer_pkey'));

test('clave primaria NULL: 23502 (not-null)', () =>
  rejectsWith(NEW_CUSTOMER, [null, '00000001'], '23502'));

test('remitente que no existe: 23503 en shipment_sender_id_fkey', () =>
  rejectsWith(NEW_SHIPMENT, [99999, 2.5], '23503', 'shipment_sender_id_fkey'));

test('borrar una agencia referenciada: 23503 en route_origin_agency_id_fkey', () =>
  rejectsWith('DELETE FROM agency WHERE agency_id = $1', [1], '23503', 'route_origin_agency_id_fkey'));

test('peso negativo: 23514 en shipment_weight_kg_check', () =>
  rejectsWith(NEW_SHIPMENT, [178, -5], '23514', 'shipment_weight_kg_check'));

test('una clave foránea debe apuntar a una clave: 42830', () =>
  rejectsWith('CREATE TABLE complaint (complaint_id integer PRIMARY KEY, city text REFERENCES customer (city))',
    [], '42830'));

const NEW_SHIPMENT_TRIP = `INSERT INTO shipment (shipment_id, sender_id, recipient_id, origin_agency_id,
                             dest_agency_id, trip_id, weight_kg, price, status, created_at)
                           VALUES (12001, 178, 337, 9, 8, $1, 2.5, 15.00, 'registered', now())`;

test('trip_id NULL no se verifica: los envíos registrados no tienen viaje', async () => {
  const { rows } = await pool.query(
    `SELECT count(*) AS n, count(trip_id) AS with_trip FROM shipment WHERE status = $1`, ['registered']);
  assert.deepEqual(rows[0], { n: '500', with_trip: '0' });
  // Un envío nuevo con trip_id NULL se acepta.
  await inRollback(async (c) => {
    const r = await c.query(NEW_SHIPMENT_TRIP, [null]);
    assert.equal(r.rowCount, 1);
  });
});

test('trip_id que no existe: 23503 en shipment_trip_id_fkey', () =>
  rejectsWith(NEW_SHIPMENT_TRIP, [999999], '23503', 'shipment_trip_id_fkey'));

test('count(*) llega como texto: bigint no siempre cabe en un Number', async () => {
  const { rows } = await pool.query('SELECT count(*) AS n FROM shipment WHERE status = $1', ['delivered']);
  assert.equal(typeof rows[0].n, 'string');
  assert.equal(Number(rows[0].n), 11115);
});

# Semana 1: Chasqui con archivos frente a un DBMS, y claves en el esquema de
# Chasqui. Se usa dentro del contenedor del curso.
#
#   make          crea la base de datos chasqui_w01 con los datos de ejemplo
#   make test     la vuelve a crear, corre los ejemplos y las pruebas (*.test.mjs)
#   make clean    borra chasqui_w01

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

.PHONY: all test clean

all:
	chasqui-load $(DB)

test: all
	@echo "== examples.sql"
	$(PSQL) -f examples.sql > /dev/null
	@echo "== files.mjs"
	PGDATABASE=$(DB) node files.mjs
	@echo "== keys.mjs"
	PGDATABASE=$(DB) node keys.mjs
	PGDATABASE=$(DB) node --test --test-reporter=spec

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