// Ayudas para las pruebas de la semana 6. La conexión se configura con las
// variables de entorno (PGDATABASE la pone make test).
import assert from 'node:assert/strict';
import pg from 'pg';

export const pool = new pg.Pool();

// Corre fn(client) dentro de una transacción que siempre termina en
// ROLLBACK: las pruebas no dejan rastros en la base de datos.
export 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();
  }
}

// Ejecuta una sentencia que debe fallar y devuelve el error. El savepoint
// deja la transacción usable después del error.
export async function expectError(client, text, values = []) {
  await client.query('SAVEPOINT attempt');
  try {
    await client.query(text, values);
  } catch (err) {
    await client.query('ROLLBACK TO SAVEPOINT attempt');
    return err;
  }
  await client.query('RELEASE SAVEPOINT attempt');
  assert.fail(`se esperaba un error: ${text}`);
}

// Una sola fila, un solo valor: SELECT count(*) ... => número.
export async function scalar(client, text, values = []) {
  const { rows } = await client.query(text, values);
  return Number(Object.values(rows[0])[0]);
}

-- Semana 6, Sesión B: qué restricción conserva cada traducción de la
-- especialización, y la agregación. Todo va dentro de BEGIN ... ROLLBACK;
-- ON_ERROR_ROLLBACK deshace solo la sentencia que falla.
-- Uso: psql -d chasqui_w06 -f extended-demo.sql (después de make).
\set ON_ERROR_ROLLBACK on
\set SHOW_CONTEXT never

BEGIN;

-- Método 1: un estudiante tiene que ser antes una persona.
INSERT INTO isa_levels.student VALUES (9, 30);

-- Una sola tabla: un instructor sin sueldo.
INSERT INTO isa_single.person VALUES (4, 'Raúl Vega', false, true, NULL, NULL);

-- Método 2: nada impide que la misma persona tenga otro nombre en la otra
-- tabla (aquí, sin tilde).
INSERT INTO isa_leaves.instructor VALUES (1, 'Carla Rios', 2500);
SELECT person_id, name FROM isa_leaves.student
UNION
SELECT person_id, name FROM isa_leaves.instructor
ORDER BY person_id;

-- Agregación: solo se evalúa una tripleta que está en proj_guide.
INSERT INTO university.evaluation VALUES (4, 11, 21, 31, '2026-06-20', 15);

ROLLBACK;

-- Semana 6, Sesión B: E-R extendido, con el ejemplo de la universidad del
-- libro (Silberschatz, sec. 6.8).
--
--   Persona se especializa en Estudiante (tot_cred) e Instructor (salary),
--   con solapamiento y parcial: alguien puede ser las dos cosas, o ninguna.
--
-- Se traduce de tres formas, una por esquema, para comparar qué
-- restricciones se conservan en cada una:
--   isa_levels   una tabla por nivel, con la misma clave (método 1 del libro)
--   isa_leaves   solo tablas para las subclases (método 2 del libro)
--   isa_single   una sola tabla con todas las columnas
--
-- Al final, el esquema university traduce una agregación: la relación
-- ternaria proj_guide (instructor, estudiante, proyecto) tratada como
-- entidad para relacionarla con Evaluación.
--
-- Las consultas sobre estos esquemas están en queries.sql.

SET client_min_messages = warning;
DROP SCHEMA IF EXISTS isa_levels, isa_leaves, isa_single, university CASCADE;

-- ------------------------------------------------- método 1: una por nivel
CREATE SCHEMA isa_levels;

CREATE TABLE isa_levels.person (
  person_id integer PRIMARY KEY,
  name      text NOT NULL
);

CREATE TABLE isa_levels.student (
  person_id integer PRIMARY KEY,
  tot_cred  integer NOT NULL,
  CONSTRAINT student_tot_cred_ck CHECK (tot_cred >= 0),
  CONSTRAINT student_person_fk FOREIGN KEY (person_id)
    REFERENCES isa_levels.person ON DELETE CASCADE
);

CREATE TABLE isa_levels.instructor (
  person_id integer PRIMARY KEY,
  salary    numeric(8, 2) NOT NULL,
  CONSTRAINT instructor_salary_ck CHECK (salary > 0),
  CONSTRAINT instructor_person_fk FOREIGN KEY (person_id)
    REFERENCES isa_levels.person ON DELETE CASCADE
);

INSERT INTO isa_levels.person VALUES (1, 'Carla Ríos'), (2, 'Jorge Paz'), (3, 'Elena Soto');
INSERT INTO isa_levels.student VALUES (1, 120), (3, 48);
INSERT INTO isa_levels.instructor VALUES (2, 6500), (3, 1800);

-- ------------------------------------------- método 2: solo las subclases
CREATE SCHEMA isa_leaves;

CREATE TABLE isa_leaves.student (
  person_id integer PRIMARY KEY,
  name      text NOT NULL,
  tot_cred  integer NOT NULL CHECK (tot_cred >= 0)
);

CREATE TABLE isa_leaves.instructor (
  person_id integer PRIMARY KEY,
  name      text NOT NULL,
  salary    numeric(8, 2) NOT NULL CHECK (salary > 0)
);

-- Elena es estudiante e instructora: su nombre se guarda dos veces.
INSERT INTO isa_leaves.student VALUES (1, 'Carla Ríos', 120), (3, 'Elena Soto', 48);
INSERT INTO isa_leaves.instructor VALUES (2, 'Jorge Paz', 6500), (3, 'Elena Soto', 1800);

-- ---------------------------------------------------- una sola tabla
CREATE SCHEMA isa_single;

-- Con solapamiento hace falta un indicador por subclase (no basta una
-- columna kind). Cada CHECK ata el indicador a las columnas de su subclase.
CREATE TABLE isa_single.person (
  person_id     integer PRIMARY KEY,
  name          text NOT NULL,
  is_student    boolean NOT NULL,
  is_instructor boolean NOT NULL,
  tot_cred      integer CHECK (tot_cred >= 0),
  salary        numeric(8, 2) CHECK (salary > 0),
  CONSTRAINT person_student_ck CHECK (is_student = (tot_cred IS NOT NULL)),
  CONSTRAINT person_instructor_ck CHECK (is_instructor = (salary IS NOT NULL))
);

INSERT INTO isa_single.person VALUES
  (1, 'Carla Ríos', true, false, 120, NULL),
  (2, 'Jorge Paz', false, true, NULL, 6500),
  (3, 'Elena Soto', true, true, 48, 1800);

-- -------------------------------------------------------------- agregación
CREATE SCHEMA university;

CREATE TABLE university.instructor (
  instructor_id integer PRIMARY KEY,
  name          text NOT NULL
);

CREATE TABLE university.student (
  student_id integer PRIMARY KEY,
  name       text NOT NULL
);

CREATE TABLE university.project (
  project_id integer PRIMARY KEY,
  title      text NOT NULL
);

-- proj_guide: ternaria sin flechas; la clave es la unión de las tres.
CREATE TABLE university.proj_guide (
  instructor_id integer NOT NULL REFERENCES university.instructor,
  student_id    integer NOT NULL REFERENCES university.student,
  project_id    integer NOT NULL REFERENCES university.project,
  CONSTRAINT proj_guide_pk PRIMARY KEY (instructor_id, student_id, project_id)
);

-- eval_for relaciona la agregación proj_guide con Evaluación: muchos a uno
-- de Evaluación a proj_guide, total del lado de Evaluación, así que se
-- funde en evaluation como una clave foránea compuesta NOT NULL hacia
-- proj_guide (no hacia las tres tablas por separado).
CREATE TABLE university.evaluation (
  evaluation_id integer PRIMARY KEY,
  instructor_id integer NOT NULL,
  student_id    integer NOT NULL,
  project_id    integer NOT NULL,
  evaluated_on  date NOT NULL,
  grade         integer NOT NULL CHECK (grade BETWEEN 0 AND 20),
  CONSTRAINT evaluation_proj_guide_fk FOREIGN KEY (instructor_id, student_id, project_id)
    REFERENCES university.proj_guide
);

INSERT INTO university.instructor VALUES (10, 'Jorge Paz'), (11, 'Elena Soto');
INSERT INTO university.student VALUES (20, 'Carla Ríos'), (21, 'Diego Lara');
INSERT INTO university.project VALUES (30, 'Sensores en el río Rímac'), (31, 'Compilador para niños');
INSERT INTO university.proj_guide VALUES (10, 20, 30), (11, 20, 30), (10, 21, 31);
INSERT INTO university.evaluation VALUES
  (1, 10, 20, 30, '2026-06-10', 16),
  (2, 11, 20, 30, '2026-06-12', 14),
  (3, 10, 21, 31, '2026-06-15', 18);

-- Semana 6, Sesión B: las restricciones del diagrama de la biblioteca, en
-- acción. Casi cada sentencia falla a propósito; después de cada una se
-- imprime su SQLSTATE. Todo va dentro de BEGIN ... ROLLBACK.
-- ON_ERROR_ROLLBACK pone un savepoint antes de cada sentencia, así que un
-- error deshace solo esa sentencia y la transacción sigue.
-- Uso: psql -d chasqui_w06 -f library-demo.sql (después de make).
\set ON_ERROR_ROLLBACK on
\set SHOW_CONTEXT never
SET search_path = library;

BEGIN;

-- 1. publica es total del lado de Libro: un libro sin editorial.
INSERT INTO book (isbn, title, pub_year) VALUES ('9786120000059', 'Paco Yunque', 1951);
\echo :LAST_ERROR_SQLSTATE

-- 2. ... y la editorial tiene que existir.
INSERT INTO book VALUES ('9786120000059', 'Paco Yunque', 1951, 9);
\echo :LAST_ERROR_SQLSTATE

-- 3. escribe es muchos a muchos, pero el mismo par (libro, autor) no se repite.
INSERT INTO book_author VALUES ('9786120000011', 1, 2);
\echo :LAST_ERROR_SQLSTATE

-- 4. Ejemplar es débil: el discriminador se repite entre libros, no dentro de uno.
INSERT INTO copy VALUES ('9786120000028', 2, '2026-06-01');
INSERT INTO copy VALUES ('9786120000028', 2, '2026-06-02');
\echo :LAST_ERROR_SQLSTATE

-- 5. asignado es uno a uno: Rosa ya tiene el casillero 101.
UPDATE locker SET member_id = 1 WHERE locker_no = 102;
\echo :LAST_ERROR_SQLSTATE

-- 6. Un ejemplar está en a lo más un préstamo abierto (índice único parcial).
INSERT INTO loan VALUES (5, 3, '9786120000035', 1, '2026-05-10', '2026-05-24', NULL);
\echo :LAST_ERROR_SQLSTATE

-- 7. La especialización es disjunta: Rosa es estudiante, no puede ser docente.
INSERT INTO teacher (member_id, department) VALUES (1, 'Matemática');
\echo :LAST_ERROR_SQLSTATE

-- 8. Libro participa de forma total en escribe: un libro sin autores pasa
--    el INSERT (el trigger es diferido) y falla al revisar las restricciones.
INSERT INTO book VALUES ('9786120000066', 'Poemas humanos', 1939, 3);
SET CONSTRAINTS ALL IMMEDIATE;
\echo :LAST_ERROR_SQLSTATE

-- 9. El libro sigue ahí (solo se deshizo el SET CONSTRAINTS). Con su
--    autor, en la misma transacción, la revisión pasa.
INSERT INTO book_author VALUES ('9786120000066', 6, 1);
SET CONSTRAINTS ALL IMMEDIATE;

-- 10. Borrar un libro borra sus ejemplares (entidad débil, ON DELETE CASCADE),
--     salvo que algún ejemplar tenga préstamos.
DELETE FROM book WHERE isbn = '9786120000028';
SELECT count(*) AS copies_left FROM copy WHERE isbn = '9786120000028';
DELETE FROM book WHERE isbn = '9786120000035';
\echo :LAST_ERROR_SQLSTATE

ROLLBACK;
RESET search_path;

-- Semana 6, Sesión B: el diagrama E-R de una biblioteca universitaria,
-- traducido a tablas. Va en su propio esquema (library) dentro de
-- chasqui_w06, así que no toca las tablas de Chasqui.
--
-- El diagrama, en la notación de texto de los apuntes: <r> es una relación,
-- <<r>> la identificadora, --- una línea simple, --> "a lo más uno" hacia
-- esa entidad y == participación total de ese lado.
--
--   book == <publishes> --> publisher
--   book == <writes> --- author            (muchos a muchos, con position)
--   copy == <<has_copy>> --> book          (copy es débil; discriminador copy_no)
--   loan == <borrows> --> member
--   loan == <loan_of> --> copy
--   member <-- <assigned> --> locker       (uno a uno, parcial de los dos lados)
--   member se especializa en student y teacher (disjunta y total)
--   member tiene el atributo multivaluado {phone}
--
-- Cada restricción lleva un nombre: es lo que ve quien recibe el error.

SET client_min_messages = warning;
DROP SCHEMA IF EXISTS library CASCADE;
CREATE SCHEMA library;
SET search_path = library;

-- Conjunto de entidades fuerte: una tabla con sus atributos simples.
CREATE TABLE publisher (
  publisher_id integer PRIMARY KEY,
  name         text NOT NULL,
  city         text NOT NULL,
  CONSTRAINT publisher_name_uq UNIQUE (name)
);

-- publica: muchos a uno de Libro a Editorial, total del lado de Libro.
-- Se funde en la tabla del lado "muchos": una clave foránea NOT NULL.
CREATE TABLE book (
  isbn         text PRIMARY KEY,
  title        text NOT NULL,
  pub_year     integer NOT NULL,
  publisher_id integer NOT NULL,
  CONSTRAINT book_isbn_ck CHECK (isbn ~ '^[0-9]{13}$'),
  CONSTRAINT book_publisher_fk FOREIGN KEY (publisher_id) REFERENCES publisher
);

-- El atributo compuesto nombre (first_name, last_name) se aplana.
CREATE TABLE author (
  author_id  integer PRIMARY KEY,
  first_name text NOT NULL,
  last_name  text NOT NULL
);

-- escribe: muchos a muchos, con el atributo position (orden en la tapa).
-- Tabla propia; la clave es la unión de las claves de los participantes.
CREATE TABLE book_author (
  isbn      text NOT NULL,
  author_id integer NOT NULL,
  position  integer NOT NULL,
  CONSTRAINT book_author_pk PRIMARY KEY (isbn, author_id),
  CONSTRAINT book_author_position_uq UNIQUE (isbn, position),
  CONSTRAINT book_author_position_ck CHECK (position >= 1),
  CONSTRAINT book_author_book_fk FOREIGN KEY (isbn) REFERENCES book ON DELETE CASCADE,
  CONSTRAINT book_author_author_fk FOREIGN KEY (author_id) REFERENCES author
);

-- Ejemplar: entidad débil de Libro. Clave = clave del owner + discriminador.
-- La relación identificadora (tiene) no lleva tabla propia: es la clave
-- foránea. Si se borra el libro, se borran sus ejemplares.
CREATE TABLE copy (
  isbn        text NOT NULL,
  copy_no     integer NOT NULL,
  acquired_on date NOT NULL,
  CONSTRAINT copy_pk PRIMARY KEY (isbn, copy_no),
  CONSTRAINT copy_no_ck CHECK (copy_no >= 1),
  CONSTRAINT copy_book_fk FOREIGN KEY (isbn) REFERENCES book ON DELETE CASCADE
);

-- Socio, generalización de Estudiante y Docente (disjunta y total).
-- kind dice a qué subclase pertenece; UNIQUE (member_id, kind) existe para
-- que las subclases lo referencien y no puedan elegir la equivocada.
CREATE TABLE member (
  member_id  integer PRIMARY KEY,
  dni        char(8) NOT NULL,
  first_name text NOT NULL,
  last_name  text NOT NULL,
  joined_on  date NOT NULL,
  kind       text NOT NULL,
  CONSTRAINT member_dni_uq UNIQUE (dni),
  CONSTRAINT member_kind_ck CHECK (kind IN ('student', 'teacher')),
  CONSTRAINT member_id_kind_uq UNIQUE (member_id, kind)
);

-- Atributo multivaluado {phone}: tabla aparte, clave (owner, valor).
CREATE TABLE member_phone (
  member_id integer NOT NULL,
  phone     text NOT NULL,
  CONSTRAINT member_phone_pk PRIMARY KEY (member_id, phone),
  CONSTRAINT member_phone_ck CHECK (phone ~ '^9[0-9]{8}$'),
  CONSTRAINT member_phone_member_fk FOREIGN KEY (member_id) REFERENCES member ON DELETE CASCADE
);

-- Subclases: misma clave que la superclase. La clave foránea compuesta
-- (member_id, kind) con kind fijo impide que un socio sea de las dos.
CREATE TABLE student (
  member_id    integer PRIMARY KEY,
  kind         text NOT NULL DEFAULT 'student',
  student_code text NOT NULL,
  faculty      text NOT NULL,
  CONSTRAINT student_kind_ck CHECK (kind = 'student'),
  CONSTRAINT student_code_uq UNIQUE (student_code),
  CONSTRAINT student_member_fk FOREIGN KEY (member_id, kind)
    REFERENCES member (member_id, kind) ON DELETE CASCADE
);

CREATE TABLE teacher (
  member_id  integer PRIMARY KEY,
  kind       text NOT NULL DEFAULT 'teacher',
  department text NOT NULL,
  CONSTRAINT teacher_kind_ck CHECK (kind = 'teacher'),
  CONSTRAINT teacher_member_fk FOREIGN KEY (member_id, kind)
    REFERENCES member (member_id, kind) ON DELETE CASCADE
);

-- asignado: uno a uno entre Socio y Casillero, parcial de los dos lados.
-- Clave foránea en uno de los lados, con UNIQUE y que admite NULL.
CREATE TABLE locker (
  locker_no integer PRIMARY KEY,
  floor     integer NOT NULL,
  member_id integer,
  CONSTRAINT locker_member_uq UNIQUE (member_id),
  CONSTRAINT locker_member_fk FOREIGN KEY (member_id) REFERENCES member ON DELETE SET NULL
);

-- Préstamo: un socio puede llevarse el mismo ejemplar varias veces, así que
-- no es un conjunto de relaciones entre Socio y Ejemplar sino una entidad
-- con dos relaciones muchos a uno, totales del lado de Préstamo.
CREATE TABLE loan (
  loan_id     integer PRIMARY KEY,
  member_id   integer NOT NULL,
  isbn        text NOT NULL,
  copy_no     integer NOT NULL,
  loaned_on   date NOT NULL,
  due_on      date NOT NULL,
  returned_on date,
  CONSTRAINT loan_member_fk FOREIGN KEY (member_id) REFERENCES member,
  CONSTRAINT loan_copy_fk FOREIGN KEY (isbn, copy_no) REFERENCES copy,
  CONSTRAINT loan_due_ck CHECK (due_on > loaned_on),
  CONSTRAINT loan_returned_ck CHECK (returned_on >= loaned_on)
);

-- Un ejemplar está en a lo más un préstamo abierto: un índice único
-- parcial (solo sobre las filas sin devolver). Es de PostgreSQL, no del
-- estándar.
CREATE UNIQUE INDEX loan_open_copy_uq ON loan (isbn, copy_no) WHERE returned_on IS NULL;

-- Participación total de Libro en escribe: todo libro tiene al menos un
-- autor. Ni NOT NULL ni una clave foránea lo pueden decir (el autor está en
-- otra tabla), así que lo revisa un constraint trigger diferido: al COMMIT,
-- cuando ya se insertaron el libro y sus autores.
CREATE FUNCTION book_has_author() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_isbn text;
BEGIN
  -- El libro insertado (NEW) o el del autor quitado (OLD).
  v_isbn := CASE WHEN TG_TABLE_NAME = 'book' THEN NEW.isbn ELSE OLD.isbn END;
  -- Si el libro ya no existe (se borró con sus autores), no hay nada que revisar.
  IF EXISTS (SELECT 1 FROM library.book WHERE isbn = v_isbn)
     AND NOT EXISTS (SELECT 1 FROM library.book_author WHERE isbn = v_isbn) THEN
    RAISE EXCEPTION 'el libro % no tiene autores', v_isbn
      USING ERRCODE = 'check_violation', CONSTRAINT = 'book_has_author';
  END IF;
  RETURN NULL;
END;
$$;

CREATE CONSTRAINT TRIGGER book_has_author_ins
  AFTER INSERT ON book
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION book_has_author();

CREATE CONSTRAINT TRIGGER book_has_author_del
  AFTER DELETE OR UPDATE OF isbn ON book_author
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION book_has_author();

-- Atributos derivados: no se guardan, se calculan en una vista.
CREATE VIEW book_stock AS
SELECT b.isbn, b.title,
       count(c.copy_no) AS copies,
       count(c.copy_no) FILTER (WHERE NOT EXISTS (
         SELECT 1 FROM loan l
         WHERE l.isbn = c.isbn AND l.copy_no = c.copy_no AND l.returned_on IS NULL)) AS available
FROM book b
  LEFT JOIN copy c ON c.isbn = b.isbn
GROUP BY b.isbn, b.title;

-- Datos de ejemplo. Los libros y autores existen; las editoriales y los
-- ISBN son inventados.
BEGIN;
INSERT INTO publisher VALUES
  (1, 'Editorial Andina', 'Lima'),
  (2, 'Fondo Universitario', 'Arequipa'),
  (3, 'Ediciones del Sur', 'Cusco');

INSERT INTO book VALUES
  ('9786120000011', 'Los ríos profundos', 1958, 1),
  ('9786120000028', 'La ciudad y los perros', 1963, 1),
  ('9786120000035', 'Database System Concepts', 2019, 2),
  ('9786120000042', 'Trilce', 1922, 3);

INSERT INTO author VALUES
  (1, 'José María', 'Arguedas'),
  (2, 'Mario', 'Vargas Llosa'),
  (3, 'Abraham', 'Silberschatz'),
  (4, 'Henry F.', 'Korth'),
  (5, 'S.', 'Sudarshan'),
  (6, 'César', 'Vallejo');

INSERT INTO book_author VALUES
  ('9786120000011', 1, 1),
  ('9786120000028', 2, 1),
  ('9786120000035', 3, 1),
  ('9786120000035', 4, 2),
  ('9786120000035', 5, 3),
  ('9786120000042', 6, 1);

INSERT INTO copy VALUES
  ('9786120000011', 1, '2024-03-01'),
  ('9786120000011', 2, '2024-03-01'),
  ('9786120000028', 1, '2024-05-10'),
  ('9786120000035', 1, '2025-02-20'),
  ('9786120000035', 2, '2025-02-20'),
  ('9786120000035', 3, '2026-03-02');

INSERT INTO member VALUES
  (1, '45127788', 'Rosa', 'Quispe', '2025-03-15', 'student'),
  (2, '40981234', 'Luis', 'Huamán', '2024-08-01', 'teacher'),
  (3, '47220011', 'Ana', 'Mamani', '2026-03-20', 'student');

INSERT INTO student (member_id, student_code, faculty) VALUES
  (1, '20251234A', 'FIIS'),
  (3, '20261010C', 'FIEE');
INSERT INTO teacher (member_id, department) VALUES
  (2, 'Ciencias de la Computación');

INSERT INTO member_phone VALUES
  (1, '987654321'),
  (1, '912345678'),
  (2, '956001122');

INSERT INTO locker VALUES
  (101, 1, 1),
  (102, 1, NULL),
  (201, 2, NULL);

INSERT INTO loan VALUES
  (1, 1, '9786120000035', 1, '2026-04-01', '2026-04-15', '2026-04-12'),
  (2, 1, '9786120000035', 1, '2026-05-02', '2026-05-16', NULL),
  (3, 2, '9786120000011', 1, '2026-05-05', '2026-05-19', NULL),
  (4, 3, '9786120000035', 2, '2026-05-06', '2026-05-20', '2026-05-21');
COMMIT;

RESET search_path;

-- Semana 6: consultas de los apuntes. Cada una empieza con "-- name: ...";
-- las pruebas (queries.test.mjs) leen este archivo, corren cada consulta por
-- su nombre y comparan con lo que dicen los apuntes.
-- Para verlas todas: psql -d chasqui_w06 -f queries.sql
-- (antes, make carga library.sql y extended.sql).

-- ------------------------------------------------ Chasqui: participación

-- name: shipment_trip_participation
-- Viaja_en (Envío, Viaje) es parcial del lado de Envío: hay envíos sin viaje.
SELECT count(*) FILTER (WHERE trip_id IS NULL) AS without_trip,
       count(*) AS shipments
FROM shipment;

-- name: trips_without_shipments
-- ... y también del lado de Viaje: hay viajes que no llevan ningún envío.
SELECT count(*) AS trips_without_shipments
FROM trip t
WHERE NOT EXISTS (SELECT 1 FROM shipment s WHERE s.trip_id = t.trip_id);

-- name: agencies_without_routes
-- Agencia participa de forma parcial en Origen y en Destino de Ruta.
SELECT a.agency_id, a.name
FROM agency a
WHERE NOT EXISTS (SELECT 1 FROM route r
                  WHERE a.agency_id IN (r.origin_agency_id, r.dest_agency_id));

-- name: customers_never_sent
-- Cliente participa de forma parcial en Remitente.
SELECT count(*) AS never_sent
FROM customer c
WHERE NOT EXISTS (SELECT 1 FROM shipment s WHERE s.sender_id = c.customer_id);

-- name: shipments_per_trip
-- Un viaje lleva muchos envíos; un envío va en a lo más un viaje.
SELECT min(n) AS min_shipments, max(n) AS max_shipments
FROM (SELECT trip_id, count(*) AS n
      FROM shipment
      WHERE trip_id IS NOT NULL
      GROUP BY trip_id) AS per_trip;

-- name: vehicles_without_trips
-- Hoy todo vehículo tiene viajes. Eso es un dato de esta instancia, no una
-- restricción: el esquema acepta un vehículo nuevo sin viajes.
SELECT count(*) AS vehicles_without_trips
FROM vehicle v
WHERE NOT EXISTS (SELECT 1 FROM trip t WHERE t.vehicle_id = v.vehicle_id);

-- name: events_of_shipment_1
-- La entidad débil Evento: seq distingue los eventos de un mismo envío.
SELECT shipment_id, seq, at, agency_id, kind
FROM shipment_event
WHERE shipment_id = 1
ORDER BY seq;

-- name: seq_is_not_a_key
-- seq solo no identifica un evento: el evento 1 existe para cada envío.
SELECT count(*) AS events_with_seq_1
FROM shipment_event
WHERE seq = 1;

-- name: status_vs_last_event
-- status casi se deriva del último evento del envío.
SELECT s.status, e.kind AS last_event, count(*) AS shipments
FROM shipment s
  JOIN LATERAL (SELECT kind
                FROM shipment_event e
                WHERE e.shipment_id = s.shipment_id
                ORDER BY seq DESC
                LIMIT 1) AS e ON true
GROUP BY s.status, e.kind
ORDER BY s.status, e.kind;

-- name: delivered_at_vs_event
-- delivered_at repite el momento del evento delivered: no hay ni una diferencia.
SELECT count(*) AS delivered_events,
       count(*) FILTER (WHERE e.at <> s.delivered_at) AS different
FROM shipment s
  JOIN shipment_event e ON e.shipment_id = s.shipment_id AND e.kind = 'delivered';

-- name: trip_duration
-- Un atributo derivado: la duración del viaje no se guarda, se calcula.
SELECT trip_id, departure_at, arrival_at, arrival_at - departure_at AS duration
FROM trip
ORDER BY trip_id
LIMIT 3;

-- ------------------------------------------------ biblioteca y universidad

-- name: book_stock
-- Copias y ejemplares disponibles: atributos derivados, en una vista.
SELECT isbn, title, copies, available
FROM library.book_stock
ORDER BY isbn;

-- name: book_authors
-- El muchos a muchos escribe, de vuelta como una lista por libro.
SELECT b.title, string_agg(a.last_name, ', ' ORDER BY ba.position) AS authors
FROM library.book b
  JOIN library.book_author ba ON ba.isbn = b.isbn
  JOIN library.author a ON a.author_id = ba.author_id
GROUP BY b.isbn, b.title
ORDER BY b.title;

-- name: members_with_kind
-- La generalización Socio, con los datos de su subclase.
SELECT m.member_id, m.first_name, m.kind, s.faculty, t.department
FROM library.member m
  LEFT JOIN library.student s ON s.member_id = m.member_id
  LEFT JOIN library.teacher t ON t.member_id = m.member_id
ORDER BY m.member_id;

-- name: levels_everyone
-- Método 1: todas las personas con lo que son, un join externo por subclase.
SELECT p.person_id, p.name, s.tot_cred, i.salary
FROM isa_levels.person p
  LEFT JOIN isa_levels.student s ON s.person_id = p.person_id
  LEFT JOIN isa_levels.instructor i ON i.person_id = p.person_id
ORDER BY p.person_id;

-- name: leaves_everyone
-- Método 2: todas las personas son una unión. Elena sale una vez solo
-- porque sus dos filas coinciden en id y nombre.
SELECT person_id, name FROM isa_leaves.student
UNION
SELECT person_id, name FROM isa_leaves.instructor
ORDER BY person_id;

-- name: evaluations
-- Agregación: cada evaluación es de una tripleta de proj_guide.
SELECT e.evaluation_id, i.name AS instructor, s.name AS student, p.title, e.grade
FROM university.evaluation e
  JOIN university.instructor i ON i.instructor_id = e.instructor_id
  JOIN university.student s ON s.student_id = e.student_id
  JOIN university.project p ON p.project_id = e.project_id
ORDER BY e.evaluation_id;

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

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

// Pruebas del E-R extendido (extended.sql): qué restricciones conserva cada
// traducción de la especialización Persona -> Estudiante, Instructor, y la
// agregación proj_guide evaluada por Evaluación.
import { test, after } from 'node:test';
import assert from 'node:assert/strict';
import { pool, inRollback, expectError, scalar } from './db.mjs';

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

// ------------------------------------------------ método 1: una por nivel

test('método 1, 23503: un estudiante que no es una persona', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client, 'INSERT INTO isa_levels.student VALUES (9, 30)');
    assert.equal(err.code, '23503');
    assert.equal(err.constraint, 'student_person_fk');
  });
});

test('método 1: el solapamiento y la participación parcial se aceptan', async () => {
  await inRollback(async (client) => {
    // Elena (3) ya es las dos cosas; una persona nueva puede no ser ninguna.
    await client.query("INSERT INTO isa_levels.person VALUES (4, 'Raúl Vega')");
    assert.equal(await scalar(client, `SELECT count(*) FROM isa_levels.person p
      WHERE NOT EXISTS (SELECT 1 FROM isa_levels.student s WHERE s.person_id = p.person_id)
        AND NOT EXISTS (SELECT 1 FROM isa_levels.instructor i WHERE i.person_id = p.person_id)`), 1);
  });
});

test('método 1: borrar la persona borra sus filas de subclase', async () => {
  await inRollback(async (client) => {
    await client.query('DELETE FROM isa_levels.person WHERE person_id = 3');
    assert.equal(await scalar(client, 'SELECT count(*) FROM isa_levels.student WHERE person_id = 3'), 0);
    assert.equal(await scalar(client, 'SELECT count(*) FROM isa_levels.instructor WHERE person_id = 3'), 0);
  });
});

test('método 1, 23502: un instructor sin sueldo', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client, 'INSERT INTO isa_levels.instructor (person_id) VALUES (1)');
    assert.equal(err.code, '23502');
  });
});

// ------------------------------------------------ método 2: solo subclases

test('método 2: nada impide dos nombres distintos para la misma persona', async () => {
  await inRollback(async (client) => {
    await client.query("INSERT INTO isa_leaves.instructor VALUES (1, 'Carla Rios', 2500)");
    const { rows } = await client.query(`SELECT person_id, name FROM isa_leaves.student
      UNION SELECT person_id, name FROM isa_leaves.instructor ORDER BY person_id, name`);
    assert.equal(rows.length, 4);
    assert.deepEqual(rows.filter((r) => r.person_id === 1).map((r) => r.name), ['Carla Rios', 'Carla Ríos']);
  });
});

// ------------------------------------------------ una sola tabla

test('una tabla, 23514: un instructor sin sueldo', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client,
      "INSERT INTO isa_single.person VALUES (4, 'Raúl Vega', false, true, NULL, NULL)");
    assert.equal(err.code, '23514');
    assert.equal(err.constraint, 'person_instructor_ck');
  });
});

test('una tabla, 23514: créditos para quien no es estudiante', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client,
      'UPDATE isa_single.person SET tot_cred = 10 WHERE person_id = 2');
    assert.equal(err.code, '23514');
    assert.equal(err.constraint, 'person_student_ck');
  });
});

test('una tabla: listar a todos es un SELECT sin joins', async () => {
  await inRollback(async (client) => {
    assert.equal(await scalar(client, 'SELECT count(*) FROM isa_single.person'), 3);
    assert.equal(await scalar(client,
      'SELECT count(*) FROM isa_single.person WHERE is_student AND is_instructor'), 1);
  });
});

// ------------------------------------------------ agregación

test('agregación, 23503: evaluar una tripleta que no está en proj_guide', async () => {
  await inRollback(async (client) => {
    // El instructor 11, el estudiante 21 y el proyecto 31 existen por separado.
    assert.equal(await scalar(client, `SELECT (SELECT count(*) FROM university.instructor WHERE instructor_id = 11)
      + (SELECT count(*) FROM university.student WHERE student_id = 21)
      + (SELECT count(*) FROM university.project WHERE project_id = 31)`), 3);
    const err = await expectError(client,
      "INSERT INTO university.evaluation VALUES (4, 11, 21, 31, '2026-06-20', 15)");
    assert.equal(err.code, '23503');
    assert.equal(err.constraint, 'evaluation_proj_guide_fk');
  });
});

test('agregación: una misma tripleta puede tener varias evaluaciones', async () => {
  await inRollback(async (client) => {
    await client.query("INSERT INTO university.evaluation VALUES (4, 10, 20, 30, '2026-07-01', 17)");
    assert.equal(await scalar(client, `SELECT count(*) FROM university.evaluation
      WHERE (instructor_id, student_id, project_id) = (10, 20, 30)`), 2);
  });
});

test('agregación, 23502: una evaluación sin su tripleta', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client, `INSERT INTO university.evaluation
      (evaluation_id, instructor_id, student_id, evaluated_on, grade) VALUES (4, 10, 20, '2026-07-01', 17)`);
    assert.equal(err.code, '23502');
    assert.equal(err.column, 'project_id');
  });
});

// Pruebas de la traducción del diagrama de la biblioteca (library.sql): cada
// restricción del diagrama (cardinalidad, participación, entidad débil,
// especialización) tiene que rechazar los datos que la violan con el
// SQLSTATE esperado, y aceptar los que la cumplen. Códigos de la clase 23:
//   23502 not_null_violation     23503 foreign_key_violation
//   23505 unique_violation       23514 check_violation
import { test, after } from 'node:test';
import assert from 'node:assert/strict';
import { pool, inRollback, expectError, scalar } from './db.mjs';

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

// ------------------------------------------------ publica: muchos a uno, total

test('23502: un libro sin editorial (participación total en publica)', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client,
      "INSERT INTO library.book (isbn, title, pub_year) VALUES ('9786120000059', 'Paco Yunque', 1951)");
    assert.equal(err.code, '23502');
    assert.equal(err.column, 'publisher_id');
  });
});

test('23503: un libro con una editorial que no existe', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client,
      "INSERT INTO library.book VALUES ('9786120000059', 'Paco Yunque', 1951, 9)");
    assert.equal(err.code, '23503');
    assert.equal(err.constraint, 'book_publisher_fk');
  });
});

test('una editorial sin libros sí se acepta (participación parcial)', async () => {
  await inRollback(async (client) => {
    await client.query("INSERT INTO library.publisher VALUES (4, 'Editorial Nueva', 'Piura')");
    assert.equal(await scalar(client, 'SELECT count(*) FROM library.book WHERE publisher_id = 4'), 0);
  });
});

// ------------------------------------------------ escribe: muchos a muchos

test('23505: el mismo par (libro, autor) dos veces', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client,
      "INSERT INTO library.book_author VALUES ('9786120000011', 1, 2)");
    assert.equal(err.code, '23505');
    assert.equal(err.constraint, 'book_author_pk');
  });
});

test('un autor puede tener varios libros y un libro varios autores', async () => {
  await inRollback(async (client) => {
    // Vallejo también en el libro de Arguedas: no es cierto, pero el esquema lo permite.
    await client.query("INSERT INTO library.book_author VALUES ('9786120000011', 6, 2)");
    assert.equal(await scalar(client, 'SELECT count(*) FROM library.book_author WHERE author_id = 6'), 2);
    assert.equal(await scalar(client,
      "SELECT count(*) FROM library.book_author WHERE isbn = '9786120000035'"), 3);
  });
});

// --------------------------- participación total de Libro en escribe (diferida)

test('23514 al COMMIT: un libro sin autores', async () => {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    // El INSERT pasa: el constraint trigger es diferido.
    await client.query("INSERT INTO library.book VALUES ('9786120000066', 'Poemas humanos', 1939, 3)");
    await assert.rejects(client.query('COMMIT'), (err) => {
      assert.equal(err.code, '23514');
      assert.equal(err.constraint, 'book_has_author');
      return true;
    });
  } finally {
    client.release();
  }
  // El COMMIT fallido deshizo la transacción entera.
  assert.equal(await scalar(pool,
    "SELECT count(*) FROM library.book WHERE isbn = '9786120000066'"), 0);
});

test('un libro con su autor en la misma transacción pasa la revisión', async () => {
  await inRollback(async (client) => {
    await client.query("INSERT INTO library.book VALUES ('9786120000066', 'Poemas humanos', 1939, 3)");
    await client.query("INSERT INTO library.book_author VALUES ('9786120000066', 6, 1)");
    await client.query('SET CONSTRAINTS ALL IMMEDIATE');
  });
});

test('23514: quitarle a un libro su único autor', async () => {
  await inRollback(async (client) => {
    await client.query("DELETE FROM library.book_author WHERE isbn = '9786120000042'");
    const err = await expectError(client, 'SET CONSTRAINTS ALL IMMEDIATE');
    assert.equal(err.code, '23514');
  });
});

test('quitar uno de tres autores sí se puede', async () => {
  await inRollback(async (client) => {
    await client.query("DELETE FROM library.book_author WHERE isbn = '9786120000035' AND author_id = 5");
    await client.query('SET CONSTRAINTS ALL IMMEDIATE');
  });
});

test('borrar el libro (y en cascada sus autores) no dispara el error', async () => {
  await inRollback(async (client) => {
    await client.query("DELETE FROM library.book WHERE isbn = '9786120000042'");
    await client.query('SET CONSTRAINTS ALL IMMEDIATE');
    assert.equal(await scalar(client,
      "SELECT count(*) FROM library.book_author WHERE isbn = '9786120000042'"), 0);
  });
});

// ------------------------------------------------ Ejemplar: entidad débil

test('el discriminador copy_no se repite entre libros, no dentro de uno', async () => {
  await inRollback(async (client) => {
    await client.query("INSERT INTO library.copy VALUES ('9786120000028', 2, '2026-06-01')");
    const err = await expectError(client,
      "INSERT INTO library.copy VALUES ('9786120000028', 2, '2026-06-02')");
    assert.equal(err.code, '23505');
    assert.equal(err.constraint, 'copy_pk');
    assert.equal(await scalar(client, 'SELECT count(*) FROM library.copy WHERE copy_no = 1'), 3);
  });
});

test('23503: un ejemplar de un libro que no existe', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client,
      "INSERT INTO library.copy VALUES ('9786129999999', 1, '2026-06-01')");
    assert.equal(err.code, '23503');
    assert.equal(err.constraint, 'copy_book_fk');
  });
});

test('borrar un libro borra sus ejemplares (ON DELETE CASCADE)', async () => {
  await inRollback(async (client) => {
    await client.query("DELETE FROM library.book WHERE isbn = '9786120000028'");
    assert.equal(await scalar(client,
      "SELECT count(*) FROM library.copy WHERE isbn = '9786120000028'"), 0);
  });
});

test('23503: no se borra un libro con ejemplares que tienen préstamos', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client, "DELETE FROM library.book WHERE isbn = '9786120000035'");
    assert.equal(err.code, '23503');
    assert.equal(err.constraint, 'loan_copy_fk');
  });
});

// ------------------------------------------------ asignado: uno a uno

test('23505: un socio con dos casilleros', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client,
      'UPDATE library.locker SET member_id = 1 WHERE locker_no = 102');
    assert.equal(err.code, '23505');
    assert.equal(err.constraint, 'locker_member_uq');
  });
});

test('varios casilleros libres (member_id NULL) no chocan con el UNIQUE', async () => {
  await inRollback(async (client) => {
    await client.query('INSERT INTO library.locker VALUES (202, 2, NULL)');
    assert.equal(await scalar(client,
      'SELECT count(*) FROM library.locker WHERE member_id IS NULL'), 3);
  });
});

// ------------------------------------------------ Préstamo

test('23505: un ejemplar en dos préstamos abiertos', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client,
      "INSERT INTO library.loan VALUES (5, 3, '9786120000035', 1, '2026-05-10', '2026-05-24', NULL)");
    assert.equal(err.code, '23505');
    assert.equal(err.constraint, 'loan_open_copy_uq');
  });
});

test('el mismo socio puede llevarse el mismo ejemplar otra vez', async () => {
  await inRollback(async (client) => {
    await client.query("UPDATE library.loan SET returned_on = '2026-05-09' WHERE loan_id = 2");
    await client.query(
      "INSERT INTO library.loan VALUES (5, 1, '9786120000035', 1, '2026-05-10', '2026-05-24', NULL)");
    assert.equal(await scalar(client,
      "SELECT count(*) FROM library.loan WHERE member_id = 1 AND isbn = '9786120000035' AND copy_no = 1"), 3);
  });
});

test('23514: un préstamo que vence antes de empezar', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client,
      "INSERT INTO library.loan VALUES (5, 3, '9786120000028', 1, '2026-05-10', '2026-05-01', NULL)");
    assert.equal(err.code, '23514');
    assert.equal(err.constraint, 'loan_due_ck');
  });
});

// ------------------------------------------------ especialización disjunta

test('23503: un estudiante que además quiere ser docente', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client,
      "INSERT INTO library.teacher (member_id, department) VALUES (1, 'Matemática')");
    assert.equal(err.code, '23503');
    assert.equal(err.constraint, 'teacher_member_fk');
  });
});

test('23514: una fila de teacher no puede declararse student', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client,
      "INSERT INTO library.teacher VALUES (1, 'student', 'Matemática')");
    assert.equal(err.code, '23514');
    assert.equal(err.constraint, 'teacher_kind_ck');
  });
});

test('23503: cambiar el kind de un socio que ya tiene fila en student', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client,
      "UPDATE library.member SET kind = 'teacher' WHERE member_id = 1");
    assert.equal(err.code, '23503');
    assert.equal(err.constraint, 'student_member_fk');
  });
});

test('la totalidad de la especialización no está garantizada', async () => {
  // Un socio sin fila en student ni en teacher: el esquema lo acepta.
  await inRollback(async (client) => {
    await client.query(
      "INSERT INTO library.member VALUES (4, '41112222', 'Juan', 'Flores', '2026-06-01', 'teacher')");
    await client.query('SET CONSTRAINTS ALL IMMEDIATE');
    assert.equal(await scalar(client, 'SELECT count(*) FROM library.teacher WHERE member_id = 4'), 0);
  });
});

// ------------------------------------------------ atributo multivaluado

test('23505: el mismo teléfono dos veces para un socio', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client,
      "INSERT INTO library.member_phone VALUES (1, '987654321')");
    assert.equal(err.code, '23505');
    assert.equal(err.constraint, 'member_phone_pk');
  });
});

test('borrar un socio borra sus teléfonos', async () => {
  await inRollback(async (client) => {
    await client.query('DELETE FROM library.loan WHERE member_id = 1');
    await client.query('DELETE FROM library.member WHERE member_id = 1');
    assert.equal(await scalar(client, 'SELECT count(*) FROM library.member_phone WHERE member_id = 1'), 0);
    // El casillero queda libre (ON DELETE SET NULL).
    assert.equal(await scalar(client,
      'SELECT count(*) FROM library.locker WHERE locker_no = 101 AND member_id IS NULL'), 1);
  });
});

// Pruebas de queries.sql: cada consulta, por su nombre, debe devolver lo que
// muestran los apuntes. También revisa dos cosas del esquema de Chasqui que
// usan los apuntes: qué participación expresa y qué pasa al borrar un envío.
import { test, after } from 'node:test';
import assert from 'node:assert/strict';
import { pool, inRollback, expectError, scalar } from './db.mjs';
import { namedQueries } from './sqlfile.mjs';

const q = namedQueries('./queries.sql');
const rows = async (name) => (await pool.query(q[name])).rows;

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

test('la base de datos es chasqui_w06', async () => {
  const { rows: [r] } = await pool.query('SELECT current_database() AS db');
  assert.equal(r.db, 'chasqui_w06');
});

// ------------------------------------------------ Chasqui: participación

test('shipment_trip_participation: 500 de 12 000 envíos sin viaje', async () => {
  assert.deepEqual(await rows('shipment_trip_participation'), [{ without_trip: '500', shipments: '12000' }]);
});

test('trips_without_shipments: 1038 viajes vacíos', async () => {
  assert.deepEqual(await rows('trips_without_shipments'), [{ trips_without_shipments: '1038' }]);
});

test('agencies_without_routes: solo Iquitos', async () => {
  assert.deepEqual(await rows('agencies_without_routes'), [{ agency_id: 29, name: 'Iquitos - Centro' }]);
});

test('customers_never_sent: 82', async () => {
  assert.deepEqual(await rows('customers_never_sent'), [{ never_sent: '82' }]);
});

test('shipments_per_trip: de 1 a 10', async () => {
  assert.deepEqual(await rows('shipments_per_trip'), [{ min_shipments: '1', max_shipments: '10' }]);
});

test('vehicles_without_trips: 0 hoy, pero el esquema acepta un vehículo sin viajes', async () => {
  assert.deepEqual(await rows('vehicles_without_trips'), [{ vehicles_without_trips: '0' }]);
  await inRollback(async (client) => {
    await client.query("INSERT INTO vehicle VALUES (99, 'NEW-001', 'van', 800)");
    assert.equal(await scalar(client, q.vehicles_without_trips), 1);
  });
});

test('events_of_shipment_1: cuatro eventos, seq de 1 a 4', async () => {
  const r = await rows('events_of_shipment_1');
  assert.deepEqual(r.map((e) => [e.seq, e.agency_id, e.kind]),
    [[1, 9, 'received'], [2, 9, 'departed'], [3, 8, 'arrived'], [4, 8, 'delivered']]);
});

test('seq_is_not_a_key: 12 000 eventos con seq = 1', async () => {
  assert.deepEqual(await rows('seq_is_not_a_key'), [{ events_with_seq_1: '12000' }]);
});

test('status_vs_last_event', async () => {
  const r = await rows('status_vs_last_event');
  assert.deepEqual(r.map((x) => [x.status, x.last_event, Number(x.shipments)]), [
    ['delivered', 'delivered', 11115],
    ['in_transit', 'arrived', 53],
    ['in_transit', 'departed', 13],
    ['registered', 'received', 500],
    ['returned', 'returned', 319],
  ]);
});

test('delivered_at_vs_event: ninguna diferencia', async () => {
  assert.deepEqual(await rows('delivered_at_vs_event'), [{ delivered_events: '11115', different: '0' }]);
});

test('trip_duration: los tres primeros viajes', async () => {
  const r = await rows('trip_duration');
  assert.deepEqual(r.map((x) => [x.trip_id, x.duration.hours, x.duration.minutes ?? 0]),
    [[1, 8, 30], [2, 16, 45], [3, 2, 15]]);
});

test('Chasqui: borrar un envío con eventos falla (sin ON DELETE CASCADE)', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client, 'DELETE FROM shipment WHERE shipment_id = 1');
    assert.equal(err.code, '23503');
    assert.equal(err.constraint, 'shipment_event_shipment_id_fkey');
  });
});

test('Chasqui: un envío sin remitente no entra (participación total)', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client, `INSERT INTO shipment (shipment_id, recipient_id,
      origin_agency_id, dest_agency_id, weight_kg, price, status, created_at)
      VALUES (90001, 2, 1, 6, 2.5, 30, 'registered', now())`);
    assert.equal(err.code, '23502');
    assert.equal(err.column, 'sender_id');
  });
});

// ------------------------------------------------ biblioteca y universidad

test('book_stock', async () => {
  const r = await rows('book_stock');
  assert.deepEqual(r.map((x) => [x.title, Number(x.copies), Number(x.available)]), [
    ['Los ríos profundos', 2, 1],
    ['La ciudad y los perros', 1, 1],
    ['Database System Concepts', 3, 2],
    ['Trilce', 0, 0],
  ]);
});

test('book_authors', async () => {
  const r = await rows('book_authors');
  assert.deepEqual(r[0], { title: 'Database System Concepts', authors: 'Silberschatz, Korth, Sudarshan' });
  assert.equal(r.length, 4);
});

test('members_with_kind', async () => {
  const r = await rows('members_with_kind');
  assert.deepEqual(r.map((x) => [x.member_id, x.kind, x.faculty, x.department]), [
    [1, 'student', 'FIIS', null],
    [2, 'teacher', null, 'Ciencias de la Computación'],
    [3, 'student', 'FIEE', null],
  ]);
});

test('levels_everyone y leaves_everyone: tres personas', async () => {
  const levels = await rows('levels_everyone');
  assert.deepEqual(levels.map((x) => [x.person_id, x.tot_cred, x.salary]),
    [[1, 120, null], [2, null, '6500.00'], [3, 48, '1800.00']]);
  const leaves = await rows('leaves_everyone');
  assert.deepEqual(leaves.map((x) => x.person_id), [1, 2, 3]);
});

test('evaluations', async () => {
  const r = await rows('evaluations');
  assert.deepEqual(r.map((x) => [x.evaluation_id, x.instructor, x.grade]),
    [[1, 'Jorge Paz', 16], [2, 'Elena Soto', 14], [3, 'Jorge Paz', 18]]);
});

# Semana 6: el modelo entidad-relación y su traducción a tablas.
# Se usa dentro del contenedor del curso.
#
#   make          crea la base de datos chasqui_w06 con los datos de ejemplo
#                 y le agrega los esquemas library (biblioteca), isa_levels,
#                 isa_leaves, isa_single y university (E-R extendido)
#   make test     la vuelve a crear, corre los ejemplos (*.sql) y las
#                 pruebas (*.test.mjs)
#   make clean    borra chasqui_w06

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

.PHONY: all test clean

all:
	chasqui-load $(DB)
	$(PSQL) -f library.sql -f extended.sql

# Las demostraciones fallan a propósito sentencia por sentencia, así que no
# llevan ON_ERROR_STOP; terminan en ROLLBACK. Los archivos de pruebas corren
# uno tras otro (--test-concurrency=1): varias pruebas cambian datos dentro
# de una transacción y otras cuentan filas.
test: all
	$(PSQL) -o /dev/null -f queries.sql
	psql -X -q -d $(DB) -o /dev/null -f library-demo.sql -f extended-demo.sql > /dev/null 2>&1
	PGDATABASE=$(DB) node --test --test-concurrency=1 --test-reporter=spec

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