-- Semana 4, Sesión B: restricciones de integridad en acción.
-- Casi cada sentencia de este script falla a propósito. Todo va dentro de
-- BEGIN ... ROLLBACK, así que la base de datos queda como estaba.
-- ON_ERROR_ROLLBACK (opción de psql) pone un savepoint antes de cada
-- sentencia: un error deshace solo esa sentencia y la transacción sigue.
\set ON_ERROR_ROLLBACK on

-- 1. Una transacción: todo o nada.
SELECT count(*) FROM shipment_event WHERE shipment_id = 1;
BEGIN;
DELETE FROM shipment_event WHERE shipment_id = 1;
SELECT count(*) FROM shipment_event WHERE shipment_id = 1;
ROLLBACK;
SELECT count(*) FROM shipment_event WHERE shipment_id = 1;

BEGIN;

-- 2. Una violación de cada tipo sobre las tablas de Chasqui.
\set VERBOSITY verbose
INSERT INTO customer (customer_id, dni, first_name, last_name, city)
VALUES (9001, '61830975', 'Rosa', 'Quispe', 'Puno');
\set VERBOSITY default

INSERT INTO shipment (shipment_id, sender_id, recipient_id, origin_agency_id,
                      dest_agency_id, weight_kg, price, status, created_at)
VALUES (90001, 99999, 1, 1, 6, 2.5, 30, 'registered', now());

INSERT INTO vehicle (vehicle_id, plate, kind, capacity_kg)
VALUES (99, 'M0T-001', 'moto', 150);

INSERT INTO shipment (shipment_id, sender_id, recipient_id, origin_agency_id,
                      dest_agency_id, weight_kg, status, created_at)
VALUES (90001, 1, 2, 1, 6, 2.5, 'registered', now());

DELETE FROM agency WHERE agency_id = 6;

-- Una restricción sin nombre explícito: el nombre lo inventa PostgreSQL.
UPDATE shipment SET delivered_at = created_at - interval '1 day'
WHERE shipment_id = 1;

-- 3. CHECK y NULL: unknown no rechaza.
SELECT NULL::numeric > 0 AS result;
INSERT INTO demo.claim (shipment_id, refund) VALUES (1, NULL);
INSERT INTO demo.claim (shipment_id, refund) VALUES (1, 0);

-- 4. UNIQUE y NULL: los NULL no chocan entre sí.
SELECT count(*) AS customers, count(phone) AS with_phone FROM customer;
ALTER TABLE customer ADD CONSTRAINT customer_phone_uq UNIQUE (phone);
ALTER TABLE customer DROP CONSTRAINT customer_phone_uq;
ALTER TABLE customer ADD CONSTRAINT customer_phone_uq UNIQUE NULLS NOT DISTINCT (phone);

-- 5. Una implicación: si hay compensación, el reclamo fue aceptado.
INSERT INTO demo.claim (shipment_id, refund) VALUES (1, 50);
INSERT INTO demo.claim (shipment_id, status, refund) VALUES (1, 'accepted', 50);

-- 6. Agregar una restricción cuando ya hay datos que no la cumplen.
ALTER TABLE demo.counter
  ADD CONSTRAINT counter_recent_ck CHECK (opened_on >= '2025-01-01');

-- 7. Acciones referenciales en el esquema demo.
SELECT full_name, agency_id, counter_no FROM demo.clerk WHERE agency_id = 1 ORDER BY dni;
DELETE FROM demo.counter WHERE agency_id = 1 AND counter_no = 3;
UPDATE demo.counter SET counter_no = 7 WHERE agency_id = 1 AND counter_no = 2;
SELECT full_name, agency_id, counter_no FROM demo.clerk WHERE agency_id = 1 ORDER BY dni;

INSERT INTO agency (agency_id, name, city, region)
VALUES (30, 'Nasca - Centro', 'Nasca', 'Ica');
INSERT INTO demo.counter (agency_id, counter_no) VALUES (30, 1), (30, 2);
DELETE FROM agency WHERE agency_id = 30;
SELECT count(*) FROM demo.counter WHERE agency_id = 30;

-- 8. Una clave foránea debe apuntar a una clave candidata.
CREATE TABLE demo.counter_note (
  counter_no smallint REFERENCES demo.counter (counter_no),
  note       text
);

-- 9. Clave foránea compuesta: la ventanilla tiene que ser de la misma agencia.
INSERT INTO demo.clerk (dni, full_name, agency_id, counter_no)
VALUES ('46789012', 'Pedro Salas Ugarte', 1, 5);
INSERT INTO demo.clerk (dni, full_name, agency_id, counter_no)
VALUES ('46789012', 'Pedro Salas Ugarte', 6, 3);

-- MATCH SIMPLE: si una columna de la clave foránea es NULL, no se verifica.
ALTER TABLE demo.clerk DROP CONSTRAINT clerk_agency_fk;
INSERT INTO demo.clerk (dni, full_name, agency_id, counter_no)
VALUES ('47890123', 'Elena Paz Soto', 999, NULL);
SELECT full_name, agency_id, counter_no FROM demo.clerk WHERE agency_id = 999;

ROLLBACK;

-- 10. Restricciones diferidas: se verifican al hacer commit.
BEGIN;
SET CONSTRAINTS demo.clerk_counter_fk DEFERRED;
INSERT INTO demo.clerk (dni, full_name, agency_id, counter_no)
VALUES ('48901234', 'Raúl Inga Paucar', 6, 3);
INSERT INTO demo.counter (agency_id, counter_no) VALUES (6, 3);
SET CONSTRAINTS demo.clerk_counter_fk IMMEDIATE;
ROLLBACK;

BEGIN;
SET CONSTRAINTS demo.clerk_counter_fk DEFERRED;
INSERT INTO demo.clerk (dni, full_name, agency_id, counter_no)
VALUES ('48901234', 'Raúl Inga Paucar', 6, 4);
COMMIT;

-- Un UNIQUE no diferible se verifica fila por fila.
BEGIN;
CREATE TABLE demo.slot (n integer UNIQUE);
INSERT INTO demo.slot VALUES (1), (2), (3);
UPDATE demo.slot SET n = n + 1;
CREATE TABLE demo.slot2 (n integer UNIQUE DEFERRABLE);
INSERT INTO demo.slot2 VALUES (1), (2), (3);
UPDATE demo.slot2 SET n = n + 1;
ROLLBACK;

-- 11. Reglas que miran otras tablas: ni CREATE ASSERTION ni subconsultas en CHECK.
BEGIN;
CREATE ASSERTION refund_within_value CHECK (NOT EXISTS (
  SELECT 1 FROM demo.claim c JOIN shipment s USING (shipment_id)
  WHERE c.refund > s.declared_value));

ALTER TABLE demo.claim ADD CONSTRAINT claim_refund_within_value_ck
  CHECK (refund <= (SELECT declared_value FROM shipment s
                    WHERE s.shipment_id = claim.shipment_id));

-- El truco de esconder la subconsulta en una función: el motor lo acepta,
-- pero solo mira la fila de claim que cambia.
CREATE FUNCTION demo.declared_value_of(p_shipment_id integer) RETURNS numeric
  LANGUAGE sql STABLE
  AS 'SELECT declared_value FROM shipment WHERE shipment_id = p_shipment_id';
ALTER TABLE demo.claim ADD CONSTRAINT claim_refund_within_value_ck
  CHECK (refund <= demo.declared_value_of(shipment_id));
INSERT INTO demo.claim (shipment_id, status, refund) VALUES (1, 'accepted', 2000);
INSERT INTO demo.claim (shipment_id, status, refund) VALUES (1, 'accepted', 1000);
UPDATE shipment SET declared_value = 100 WHERE shipment_id = 1;
SELECT c.refund, s.declared_value
FROM demo.claim c JOIN shipment s USING (shipment_id);
ROLLBACK;

-- 12. Tipos definidos por el usuario y columnas identity.
BEGIN;
INSERT INTO demo.clerk (dni, full_name, agency_id) VALUES ('1234567X', 'Sin DNI', 1);
INSERT INTO demo.claim (shipment_id, status) VALUES (1, 'closed');
SELECT enum_range(NULL::demo.claim_status);
INSERT INTO demo.clerk (clerk_id, dni, full_name, agency_id)
VALUES (100, '49012345', 'Marco Tito Huanca', 1);
INSERT INTO demo.clerk (clerk_id, dni, full_name, agency_id)
OVERRIDING SYSTEM VALUE
VALUES (100, '49012345', 'Marco Tito Huanca', 1);
ROLLBACK;

-- 13. Índices, como anuncio: una línea.
BEGIN;
CREATE INDEX shipment_sender_idx ON shipment (sender_id);
\d shipment
ROLLBACK;

// Ayudas para las pruebas de la semana 4. 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 4, Sesión B: tablas de ejemplo para las restricciones de integridad.
-- Viven en un esquema aparte, demo, para no tocar las tablas de Chasqui:
-- las ventanillas (counter) de cada agencia, el personal que atiende en
-- ellas (clerk) y los reclamos (claim) de los clientes por un envío.
-- make las crea después de cargar los datos; también se cargan con \i demo.sql.

SET client_min_messages = warning;
DROP SCHEMA IF EXISTS demo CASCADE;
CREATE SCHEMA demo;

-- Tipos definidos por el usuario: un dominio y un enum.
CREATE DOMAIN demo.dni AS char(8)
  CONSTRAINT dni_format_ck CHECK (VALUE ~ '^[0-9]{8}$');

CREATE TYPE demo.claim_status AS ENUM ('open', 'accepted', 'rejected');

-- Una ventanilla se identifica por su agencia y su número: clave compuesta.
CREATE TABLE demo.counter (
  agency_id  integer  NOT NULL,
  counter_no smallint NOT NULL,
  opened_on  date     NOT NULL DEFAULT current_date,
  CONSTRAINT counter_pk PRIMARY KEY (agency_id, counter_no),
  CONSTRAINT counter_agency_fk FOREIGN KEY (agency_id)
    REFERENCES agency ON DELETE CASCADE,
  CONSTRAINT counter_no_ck CHECK (counter_no BETWEEN 1 AND 20)
);

-- Una persona del personal trabaja en una agencia y, si tiene ventanilla
-- asignada, en una ventanilla de esa misma agencia.
CREATE TABLE demo.clerk (
  clerk_id   integer GENERATED ALWAYS AS IDENTITY,
  dni        demo.dni NOT NULL,
  full_name  text     NOT NULL,
  agency_id  integer  NOT NULL,
  counter_no smallint,
  hired_at   timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT clerk_pk PRIMARY KEY (clerk_id),
  CONSTRAINT clerk_dni_uq UNIQUE (dni),
  CONSTRAINT clerk_agency_fk FOREIGN KEY (agency_id) REFERENCES agency,
  CONSTRAINT clerk_counter_fk FOREIGN KEY (agency_id, counter_no)
    REFERENCES demo.counter
    ON DELETE SET NULL (counter_no)
    ON UPDATE CASCADE
    DEFERRABLE INITIALLY IMMEDIATE
);

-- Un reclamo por un envío. refund es la compensación que se paga.
CREATE TABLE demo.claim (
  claim_id    integer GENERATED ALWAYS AS IDENTITY,
  shipment_id integer NOT NULL,
  status      demo.claim_status NOT NULL DEFAULT 'open',
  opened_at   timestamptz NOT NULL DEFAULT now(),
  closed_at   timestamptz,
  refund      numeric(8, 2),
  CONSTRAINT claim_pk PRIMARY KEY (claim_id),
  CONSTRAINT claim_shipment_fk FOREIGN KEY (shipment_id) REFERENCES shipment,
  CONSTRAINT claim_refund_ck CHECK (refund > 0),
  CONSTRAINT claim_closed_after_opened_ck CHECK (closed_at >= opened_at),
  -- "si hay compensación, el reclamo fue aceptado": p → q se escribe NOT p OR q
  CONSTRAINT claim_refund_needs_accepted_ck CHECK (refund IS NULL OR status = 'accepted')
);

-- Datos de partida: tres ventanillas en Lima - La Victoria, dos en Cusco.
INSERT INTO demo.counter (agency_id, counter_no, opened_on) VALUES
  (1, 1, '2024-03-01'), (1, 2, '2024-03-01'), (1, 3, '2025-07-15'),
  (6, 1, '2024-05-20'), (6, 2, '2024-05-20');

INSERT INTO demo.clerk (dni, full_name, agency_id, counter_no, hired_at) VALUES
  ('41234567', 'Rosa Quispe Mamani',  1, 1,    '2024-03-01 08:00-05'),
  ('42345678', 'Luis Huamán Torres',  1, 2,    '2024-03-01 08:00-05'),
  ('43456789', 'Carmen Flores Ríos',  1, 3,    '2025-07-15 08:00-05'),
  ('44567890', 'Jorge Ccori Apaza',   6, 1,    '2024-05-20 08:00-05'),
  ('45678901', 'Ana Condori Vilca',   6, NULL, '2026-02-02 08:00-05');

-- Semana 4, Sesión A: expresiones de join sobre Chasqui.
-- No cambia datos (el único ALTER TABLE se deshace con ROLLBACK).
-- Se corre con \i joins.sql en psql -d chasqui_w04.

-- 1. La misma consulta con coma y WHERE (semana 2) y con JOIN ... ON.
SELECT count(*)
FROM shipment s, agency a
WHERE s.origin_agency_id = a.agency_id
  AND a.region = 'Cusco';

SELECT count(*)
FROM shipment s
  JOIN agency a ON a.agency_id = s.origin_agency_id
WHERE a.region = 'Cusco';

-- 2. CROSS JOIN: el producto cartesiano explícito (29 agencias × 24 vehículos).
SELECT count(*) FROM agency CROSS JOIN vehicle;

-- 3. USING: la columna común aparece una sola vez, y sin prefijo.
SELECT trip_id, shipment_id, route_id, weight_kg, departure_at
FROM shipment JOIN trip USING (trip_id)
WHERE shipment_id = 1;

-- 4. NATURAL JOIN combina por todas las columnas de nombre común.
SELECT count(*) FROM customer NATURAL JOIN agency;     -- combina por city
SELECT count(*) FROM shipment NATURAL JOIN route;      -- por origin_agency_id y dest_agency_id

SELECT c.customer_id, c.last_name, a.name
FROM customer c NATURAL JOIN agency a
WHERE c.customer_id = 3;

-- 4b. Alguien agrega una columna con un nombre que ya existe en la otra tabla.
BEGIN;
ALTER TABLE route ADD COLUMN created_at timestamptz NOT NULL DEFAULT now();
SELECT count(*) FROM shipment NATURAL JOIN route;
SELECT count(*) FROM shipment JOIN route USING (origin_agency_id, dest_agency_id);
ROLLBACK;

-- 5. La misma tabla dos veces: remitente y destinatario de un envío.
SELECT s.shipment_id,
       snd.first_name || ' ' || snd.last_name AS sender,
       rcp.first_name || ' ' || rcp.last_name AS recipient
FROM shipment s
  JOIN customer snd ON snd.customer_id = s.sender_id
  JOIN customer rcp ON rcp.customer_id = s.recipient_id
WHERE s.shipment_id <= 3
ORDER BY s.shipment_id;

-- 6. LEFT JOIN: todas las agencias con su cantidad de envíos de origen.
SELECT a.name, count(s.shipment_id) AS shipments
FROM agency a
  LEFT JOIN shipment s ON s.origin_agency_id = a.agency_id
GROUP BY a.agency_id, a.name
ORDER BY shipments
LIMIT 3;

-- 7. Error común: count(*) cuenta la fila completada con NULL.
SELECT a.name, count(*) AS wrong, count(s.shipment_id) AS right
FROM agency a
  LEFT JOIN shipment s ON s.origin_agency_id = a.agency_id
WHERE a.agency_id IN (10, 29)
GROUP BY a.agency_id, a.name;

-- 8. Error común: el filtro sobre la tabla de la derecha en WHERE.
SELECT a.name, count(s.shipment_id) AS delivered
FROM agency a
  LEFT JOIN shipment s ON s.origin_agency_id = a.agency_id
WHERE a.agency_id IN (10, 29)
  AND s.status = 'delivered'
GROUP BY a.agency_id, a.name;

SELECT a.name, count(s.shipment_id) AS delivered
FROM agency a
  LEFT JOIN shipment s ON s.origin_agency_id = a.agency_id
                      AND s.status = 'delivered'
WHERE a.agency_id IN (10, 29)
GROUP BY a.agency_id, a.name;

-- 9. Al revés: un filtro sobre la tabla de la izquierda en ON no quita filas.
SELECT count(*) AS rows, count(s.shipment_id) AS with_shipment
FROM agency a
  LEFT JOIN shipment s ON s.origin_agency_id = a.agency_id
                      AND a.region = 'Tumbes';

-- 10. Anti-join: los clientes que nunca enviaron nada.
SELECT count(*)
FROM customer c
  LEFT JOIN shipment s ON s.sender_id = c.customer_id
WHERE s.shipment_id IS NULL;

-- 11. RIGHT JOIN es un LEFT JOIN con las tablas al revés.
SELECT count(*)
FROM shipment s
  RIGHT JOIN customer c ON s.sender_id = c.customer_id
WHERE s.shipment_id IS NULL;

-- 12. FULL JOIN (tablas del esquema demo, ver demo.sql): ventanillas sin
-- nadie asignado y personas sin ventanilla, en un solo resultado.
SELECT k.agency_id, k.counter_no, c.full_name
FROM demo.counter k
  FULL JOIN demo.clerk c ON c.agency_id = k.agency_id
                        AND c.counter_no = k.counter_no
ORDER BY k.agency_id, k.counter_no;

-- Semana 4, Sesión B: roles, GRANT y REVOKE.
-- Los roles son de todo el servidor, no de una base de datos. Por eso el
-- script entero va dentro de BEGIN ... ROLLBACK: CREATE ROLE también se
-- deshace. postgres es superusuario y no está sujeto a privilegios; para
-- probar hay que cambiar de rol con SET ROLE.
\set ON_ERROR_ROLLBACK on

BEGIN;

-- 1. Roles de grupo (sin LOGIN) y un usuario (un rol con LOGIN).
CREATE ROLE counter_clerk;
CREATE ROLE courier;
CREATE ROLE analyst;
CREATE ROLE maria LOGIN;
GRANT counter_clerk TO maria;

-- 2. Privilegios.
GRANT SELECT ON customer, agency TO counter_clerk;
GRANT INSERT ON shipment TO counter_clerk;
GRANT SELECT ON shipment TO courier;
GRANT UPDATE (status, delivered_at) ON shipment TO courier;
GRANT SELECT ON customer_public TO analyst;

-- 3. maria hereda los privilegios de counter_clerk.
SET ROLE maria;
SELECT current_user, count(*) AS customers FROM customer;
INSERT INTO shipment (shipment_id, sender_id, recipient_id, origin_agency_id,
                      dest_agency_id, weight_kg, price, status, created_at)
VALUES (12001, 1, 2, 1, 6, 2.5, 30, 'registered', now());
UPDATE shipment SET price = 0 WHERE shipment_id = 12001;
SELECT count(*) FROM shipment;
RESET ROLE;

-- 4. Privilegios por columna.
SET ROLE courier;
UPDATE shipment SET status = 'delivered', delivered_at = now() WHERE shipment_id = 46;
UPDATE shipment SET price = 0 WHERE shipment_id = 46;
RESET ROLE;

-- 5. Una vista como mecanismo de seguridad.
SET ROLE analyst;
SELECT * FROM customer_public WHERE customer_id <= 2;
SELECT dni, phone FROM customer WHERE customer_id <= 2;
RESET ROLE;

-- 6. WITH GRANT OPTION y REVOKE ... CASCADE.
GRANT SELECT ON route TO counter_clerk WITH GRANT OPTION;
SET ROLE counter_clerk;
GRANT SELECT ON route TO analyst;
RESET ROLE;
REVOKE SELECT ON route FROM counter_clerk;
REVOKE SELECT ON route FROM counter_clerk CASCADE;
SET ROLE analyst;
SELECT count(*) FROM route;
RESET ROLE;

-- 7. Quién puede qué sobre shipment.
\dp shipment

ROLLBACK;

-- Semana 4, Sesión B: fechas, horas, intervalos y conversiones.
-- Solo lectura; se corre con \i types.sql en psql -d chasqui_w04.
-- chasqui-load deja la zona horaria de la base de datos en America/Lima.

-- 1. La zona horaria de la sesión.
SHOW timezone;

-- 2. timestamptz guarda un instante; se muestra en la zona de la sesión.
SELECT shipment_id, created_at FROM shipment WHERE shipment_id = 1;
SET timezone = 'UTC';
SELECT shipment_id, created_at FROM shipment WHERE shipment_id = 1;
SET timezone = 'America/Lima';

-- 3. timestamp (sin zona) no es un instante: depende de quién lo lea.
SELECT TIMESTAMP '2026-07-28 09:00' AS ts,
       TIMESTAMPTZ '2026-07-28 09:00' AS tstz,
       TIMESTAMP '2026-07-28 09:00' AT TIME ZONE 'UTC' AS ts_read_as_utc;

-- 4. La hora de Lima de un instante, y la fecha local de un envío.
SELECT created_at,
       created_at AT TIME ZONE 'America/Lima' AS lima_local,
       created_at AT TIME ZONE 'Europe/Madrid' AS madrid_local,
       CAST(created_at AS date) AS lima_date
FROM shipment
WHERE shipment_id = 1;

-- 5. interval: la resta de dos instantes.
SELECT trip_id, departure_at, arrival_at, arrival_at - departure_at AS duration
FROM trip
WHERE trip_id = 996;

SELECT avg(arrival_at - departure_at) AS avg_duration,
       max(arrival_at - departure_at) AS max_duration
FROM trip;

-- 6. Aritmética con fechas e intervalos.
SELECT DATE '2026-12-25' - DATE '2026-10-31' AS days_to_christmas,
       DATE '2026-10-31' + 30 AS plus_30_days,
       TIMESTAMPTZ '2026-10-31 18:00' + INTERVAL '1 day 2 hours' AS later,
       extract(dow FROM DATE '2026-10-31') AS day_of_week;

-- 7. Conversión de tipos: CAST estándar y :: de PostgreSQL.
SELECT CAST('42.50' AS numeric) + 1 AS with_cast,
       '42.50'::numeric + 1 AS with_colons,
       7 / 2 AS int_division,
       7 / 2.0 AS numeric_division,
       CAST(7 AS numeric) / 2 AS cast_division;

-- Semana 4, Sesión A: usar las vistas de views.sql.
-- Todo lo que cambia datos va dentro de BEGIN ... ROLLBACK.
-- ON_ERROR_ROLLBACK hace que un error no aborte la transacción entera
-- (es una opción de psql: pone un savepoint antes de cada sentencia).
\set ON_ERROR_ROLLBACK on

-- 1. Una vista se usa como una tabla.
SELECT * FROM route_detail WHERE route_id <= 3;

-- 2. Una vista sobre otra vista.
SELECT count(*) FROM long_route;

-- 3. Una vista no guarda datos: ve los cambios al instante.
--    Una vista materializada, no, hasta el REFRESH.
BEGIN;
UPDATE route SET distance_km = 900 WHERE route_id = 1;
SELECT count(*) FROM long_route;
INSERT INTO trip (trip_id, route_id, vehicle_id, departure_at, arrival_at)
VALUES (90001, 1, 1, '2026-10-30 07:00-05', '2026-10-30 19:00-05');
SELECT trips FROM route_trip_stats WHERE route_id = 1;
REFRESH MATERIALIZED VIEW route_trip_stats;
SELECT trips FROM route_trip_stats WHERE route_id = 1;
ROLLBACK;

-- 4. Vista actualizable con WITH CHECK OPTION.
BEGIN;
UPDATE truck SET capacity_kg = capacity_kg + 500 WHERE vehicle_id = 11 RETURNING *;
UPDATE truck SET capacity_kg = 1 WHERE vehicle_id = 19;      -- 19 es una van
UPDATE truck SET kind = 'van' WHERE vehicle_id = 11;
INSERT INTO truck (vehicle_id, plate, kind, capacity_kg) VALUES (99, 'Z9Z-999', 'van', 1500);
INSERT INTO truck (vehicle_id, plate, kind, capacity_kg) VALUES (99, 'Z9Z-999', 'truck', 9000);
SELECT vehicle_id, kind FROM vehicle WHERE vehicle_id = 99;
ROLLBACK;

-- 5. Una vista con join no es actualizable automáticamente.
BEGIN;
UPDATE route_detail SET base_fare = 50 WHERE route_id = 1;
ROLLBACK;

-- Semana 4, Sesión A: vistas de ejemplo sobre Chasqui.
-- make las crea después de cargar los datos; también se cargan con \i views.sql.

SET client_min_messages = warning;
DROP MATERIALIZED VIEW IF EXISTS route_trip_stats;
DROP VIEW IF EXISTS long_route, route_detail, truck, customer_public;

-- Una ruta por fila con los nombres de sus dos agencias: agency aparece dos veces.
CREATE VIEW route_detail AS
SELECT r.route_id,
       o.name AS origin_name,
       d.name AS dest_name,
       r.distance_km,
       r.base_fare
FROM route r
  JOIN agency o ON o.agency_id = r.origin_agency_id
  JOIN agency d ON d.agency_id = r.dest_agency_id;

-- Una vista definida sobre otra vista.
CREATE VIEW long_route AS
SELECT route_id, origin_name, dest_name, distance_km
FROM route_detail
WHERE distance_km > 800;

-- Vista materializada: guarda el resultado. Se actualiza con REFRESH.
CREATE MATERIALIZED VIEW route_trip_stats AS
SELECT r.route_id,
       count(t.trip_id)                    AS trips,
       avg(t.arrival_at - t.departure_at)  AS avg_duration
FROM route r
  LEFT JOIN trip t ON t.route_id = r.route_id
GROUP BY r.route_id;

-- Vista actualizable sobre una sola tabla; WITH CHECK OPTION rechaza
-- las filas que la vista no mostraría.
CREATE VIEW truck AS
SELECT vehicle_id, plate, kind, capacity_kg
FROM vehicle
WHERE kind = 'truck'
WITH CHECK OPTION;

-- Los clientes sin DNI ni teléfono, para el rol de análisis (Sesión B).
CREATE VIEW customer_public AS
SELECT customer_id, first_name, last_name, city
FROM customer;

// Pruebas de las restricciones de integridad (Sesión B): cada INSERT,
// UPDATE o DELETE inválido debe fallar con el SQLSTATE y la restricción
// esperados. Los códigos de la clase 23 (integrity constraint violation):
//   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());

const SHIPMENT_COLS = `shipment (shipment_id, sender_id, recipient_id, origin_agency_id,
  dest_agency_id, weight_kg, price, status, created_at)`;

test('23505: un DNI repetido', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client,
      `INSERT INTO customer (customer_id, dni, first_name, last_name, city)
       SELECT 9001, dni, 'Rosa', 'Quispe', 'Puno' FROM customer WHERE customer_id = 1`);
    assert.equal(err.code, '23505');
    assert.equal(err.constraint, 'customer_dni_key');
  });
});

test('23503: un envío con un remitente que no existe', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client,
      `INSERT INTO ${SHIPMENT_COLS} VALUES (90001, 99999, 1, 1, 6, 2.5, 30, 'registered', now())`);
    assert.equal(err.code, '23503');
    assert.equal(err.constraint, 'shipment_sender_id_fkey');
  });
});

test('23503: borrar una agencia que tiene rutas', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client, 'DELETE FROM agency WHERE agency_id = 6');
    assert.equal(err.code, '23503');
    assert.equal(err.table, 'route');
  });
});

test('23514: un vehículo de tipo moto', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client,
      "INSERT INTO vehicle (vehicle_id, plate, kind, capacity_kg) VALUES (99, 'M0T-001', 'moto', 150)");
    assert.equal(err.code, '23514');
    assert.equal(err.constraint, 'vehicle_kind_check');
  });
});

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

test('un CHECK deja pasar NULL (unknown no es false)', async () => {
  await inRollback(async (client) => {
    await client.query('INSERT INTO demo.claim (shipment_id, refund) VALUES (1, NULL)');
    const err = await expectError(client, 'INSERT INTO demo.claim (shipment_id, refund) VALUES (1, 0)');
    assert.equal(err.code, '23514');
    assert.equal(err.constraint, 'claim_refund_ck');
  });
});

test('UNIQUE admite varios NULL; NULLS NOT DISTINCT, no', async () => {
  await inRollback(async (client) => {
    assert.ok(await scalar(client, 'SELECT count(*) - count(phone) FROM customer') > 1);
    await client.query('ALTER TABLE customer ADD CONSTRAINT customer_phone_uq UNIQUE (phone)');
    await client.query('ALTER TABLE customer DROP CONSTRAINT customer_phone_uq');
    const err = await expectError(client,
      'ALTER TABLE customer ADD CONSTRAINT customer_phone_uq UNIQUE NULLS NOT DISTINCT (phone)');
    assert.equal(err.code, '23505');
  });
});

test('una implicación con CHECK: compensación solo si el reclamo fue aceptado', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client, 'INSERT INTO demo.claim (shipment_id, refund) VALUES (1, 50)');
    assert.equal(err.constraint, 'claim_refund_needs_accepted_ck');
    await client.query("INSERT INTO demo.claim (shipment_id, status, refund) VALUES (1, 'accepted', 50)");
  });
});

test('ADD CONSTRAINT falla si hay filas que no la cumplen', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client,
      "ALTER TABLE demo.counter ADD CONSTRAINT counter_recent_ck CHECK (opened_on >= '2025-01-01')");
    assert.equal(err.code, '23514');
  });
});

test('ON DELETE SET NULL (counter_no) y ON UPDATE CASCADE', async () => {
  await inRollback(async (client) => {
    await client.query('DELETE FROM demo.counter WHERE agency_id = 1 AND counter_no = 3');
    await client.query('UPDATE demo.counter SET counter_no = 7 WHERE agency_id = 1 AND counter_no = 2');
    const { rows } = await client.query(
      "SELECT dni, agency_id, counter_no FROM demo.clerk WHERE dni IN ('42345678', '43456789') ORDER BY dni");
    assert.deepEqual(rows, [
      { dni: '42345678', agency_id: 1, counter_no: 7 },
      { dni: '43456789', agency_id: 1, counter_no: null },
    ]);
  });
});

test('ON DELETE SET NULL sin lista de columnas pondría NULL también en agency_id (23502)', async () => {
  await inRollback(async (client) => {
    await client.query(`ALTER TABLE demo.clerk DROP CONSTRAINT clerk_counter_fk,
      ADD CONSTRAINT clerk_counter_fk FOREIGN KEY (agency_id, counter_no)
        REFERENCES demo.counter ON DELETE SET NULL`);
    const err = await expectError(client, 'DELETE FROM demo.counter WHERE agency_id = 1 AND counter_no = 3');
    assert.equal(err.code, '23502');
    assert.equal(err.column, 'agency_id');
  });
});

test('ON DELETE CASCADE: borrar una agencia borra sus ventanillas', async () => {
  await inRollback(async (client) => {
    await client.query("INSERT INTO agency VALUES (30, 'Nasca - Centro', 'Nasca', 'Ica')");
    await client.query('INSERT INTO demo.counter (agency_id, counter_no) VALUES (30, 1), (30, 2)');
    await client.query('DELETE FROM agency WHERE agency_id = 30');
    assert.equal(await scalar(client, 'SELECT count(*) FROM demo.counter WHERE agency_id = 30'), 0);
  });
});

test('una clave foránea necesita una clave candidata del otro lado (42830)', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client,
      'CREATE TABLE demo.counter_note (counter_no smallint REFERENCES demo.counter (counter_no))');
    assert.equal(err.code, '42830');
  });
});

test('clave foránea compuesta: la ventanilla debe ser de la misma agencia', async () => {
  await inRollback(async (client) => {
    // La ventanilla 3 existe en la agencia 1, pero no en la 6.
    const err = await expectError(client,
      "INSERT INTO demo.clerk (dni, full_name, agency_id, counter_no) VALUES ('46789012', 'Pedro Salas', 6, 3)");
    assert.equal(err.code, '23503');
    assert.equal(err.constraint, 'clerk_counter_fk');
  });
});

test('MATCH SIMPLE: una clave foránea compuesta con un NULL no se verifica', async () => {
  await inRollback(async (client) => {
    await client.query('ALTER TABLE demo.clerk DROP CONSTRAINT clerk_agency_fk');
    await client.query(
      "INSERT INTO demo.clerk (dni, full_name, agency_id, counter_no) VALUES ('47890123', 'Elena Paz', 999, NULL)");
    // Con MATCH FULL, la misma fila se rechaza.
    const err = await expectError(client, `ALTER TABLE demo.clerk DROP CONSTRAINT clerk_counter_fk,
      ADD CONSTRAINT clerk_counter_fk FOREIGN KEY (agency_id, counter_no)
        REFERENCES demo.counter MATCH FULL`);
    assert.equal(err.code, '23503');
  });
});

test('una restricción diferida se verifica al hacer commit', async () => {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query('SET CONSTRAINTS demo.clerk_counter_fk DEFERRED');
    await client.query(
      "INSERT INTO demo.clerk (dni, full_name, agency_id, counter_no) VALUES ('48901234', 'Raúl Inga', 6, 4)");
    await assert.rejects(client.query('COMMIT'), { code: '23503', constraint: 'clerk_counter_fk' });
  } finally {
    client.release();
  }
  assert.equal(await scalar(pool, "SELECT count(*) FROM demo.clerk WHERE dni = '48901234'"), 0);
});

test('SET CONSTRAINTS ... IMMEDIATE verifica en ese momento lo pendiente', async () => {
  await inRollback(async (client) => {
    await client.query('SET CONSTRAINTS demo.clerk_counter_fk DEFERRED');
    await client.query(
      "INSERT INTO demo.clerk (dni, full_name, agency_id, counter_no) VALUES ('48901234', 'Raúl Inga', 6, 3)");
    await client.query('INSERT INTO demo.counter (agency_id, counter_no) VALUES (6, 3)');
    await client.query('SET CONSTRAINTS demo.clerk_counter_fk IMMEDIATE');
  });
});

test('NOT NULL y CHECK no se pueden diferir', async () => {
  await inRollback(async (client) => {
    const check = await expectError(client,
      'CREATE TABLE demo.bad (x integer, CONSTRAINT bad_ck CHECK (x > 0) DEFERRABLE)');
    assert.match(check.message, /CHECK constraints cannot be marked DEFERRABLE/);
    const notNull = await expectError(client, 'CREATE TABLE demo.bad (x integer NOT NULL DEFERRABLE)');
    assert.equal(notNull.code, '42601');
  });
});

test('un UNIQUE no diferible se verifica fila por fila; uno DEFERRABLE, al final de la sentencia', async () => {
  await inRollback(async (client) => {
    await client.query('CREATE TABLE demo.slot (n integer UNIQUE)');
    await client.query('INSERT INTO demo.slot VALUES (1), (2), (3)');
    const err = await expectError(client, 'UPDATE demo.slot SET n = n + 1');
    assert.equal(err.code, '23505');
    await client.query('CREATE TABLE demo.slot2 (n integer UNIQUE DEFERRABLE)');
    await client.query('INSERT INTO demo.slot2 VALUES (1), (2), (3)');
    assert.equal((await client.query('UPDATE demo.slot2 SET n = n + 1')).rowCount, 3);
  });
});

test('PostgreSQL no tiene CREATE ASSERTION ni subconsultas en CHECK', async () => {
  await inRollback(async (client) => {
    const a = await expectError(client, 'CREATE ASSERTION a CHECK (NOT EXISTS (SELECT 1 FROM demo.claim))');
    assert.equal(a.code, '0A000');
    const b = await expectError(client, `ALTER TABLE demo.claim ADD CHECK
      (refund <= (SELECT declared_value FROM shipment s WHERE s.shipment_id = claim.shipment_id))`);
    assert.equal(b.code, '0A000');
  });
});

test('un CHECK con una función que lee otra tabla no se vuelve a verificar', async () => {
  await inRollback(async (client) => {
    await client.query(`CREATE FUNCTION demo.declared_value_of(p integer) RETURNS numeric
      LANGUAGE sql STABLE AS 'SELECT declared_value FROM shipment WHERE shipment_id = p'`);
    await client.query(`ALTER TABLE demo.claim ADD CONSTRAINT claim_refund_within_value_ck
      CHECK (refund <= demo.declared_value_of(shipment_id))`);
    const err = await expectError(client,
      "INSERT INTO demo.claim (shipment_id, status, refund) VALUES (1, 'accepted', 2000)");
    assert.equal(err.constraint, 'claim_refund_within_value_ck');
    await client.query("INSERT INTO demo.claim (shipment_id, status, refund) VALUES (1, 'accepted', 1000)");
    // Cambia la otra tabla: el CHECK no se entera y la regla queda rota.
    await client.query('UPDATE shipment SET declared_value = 100 WHERE shipment_id = 1');
    assert.equal(await scalar(client, `SELECT count(*) FROM demo.claim c JOIN shipment s
      USING (shipment_id) WHERE c.refund > s.declared_value`), 1);
  });
});

test('dominio, enum y columna identity', async () => {
  await inRollback(async (client) => {
    const dni = await expectError(client,
      "INSERT INTO demo.clerk (dni, full_name, agency_id) VALUES ('1234567X', 'Sin DNI', 1)");
    assert.equal(dni.code, '23514');
    assert.equal(dni.constraint, 'dni_format_ck');
    const status = await expectError(client, "INSERT INTO demo.claim (shipment_id, status) VALUES (1, 'closed')");
    assert.equal(status.code, '22P02');
    const id = await expectError(client,
      "INSERT INTO demo.clerk (clerk_id, dni, full_name, agency_id) VALUES (100, '49012345', 'Marco Tito', 1)");
    assert.equal(id.code, '428C9');
    await client.query(`INSERT INTO demo.clerk (clerk_id, dni, full_name, agency_id)
      OVERRIDING SYSTEM VALUE VALUES (100, '49012345', 'Marco Tito', 1)`);
  });
});

test('los valores por defecto se llenan solos', async () => {
  await inRollback(async (client) => {
    const { rows: [c] } = await client.query(
      'INSERT INTO demo.claim (shipment_id) VALUES (1) RETURNING status, opened_at, claim_id');
    assert.equal(c.status, 'open');
    assert.ok(c.opened_at instanceof Date);
    assert.equal(typeof c.claim_id, 'number');
  });
});

// Pruebas de lo que afirman los apuntes sobre joins (Sesión A).
import { test, after } from 'node:test';
import assert from 'node:assert/strict';
import { pool, inRollback, scalar } from './db.mjs';

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

const count = (text) => scalar(pool, text);

test('coma con WHERE y JOIN ... ON dan el mismo resultado', async () => {
  const comma = await count(`SELECT count(*) FROM shipment s, agency a
    WHERE s.origin_agency_id = a.agency_id AND a.region = 'Cusco'`);
  const join = await count(`SELECT count(*) FROM shipment s
    JOIN agency a ON a.agency_id = s.origin_agency_id WHERE a.region = 'Cusco'`);
  assert.equal(comma, join);
});

test('CROSS JOIN es el producto cartesiano', async () => {
  const agencies = await count('SELECT count(*) FROM agency');
  const vehicles = await count('SELECT count(*) FROM vehicle');
  assert.equal(await count('SELECT count(*) FROM agency CROSS JOIN vehicle'), agencies * vehicles);
});

test('USING muestra la columna común una sola vez; ON, dos veces', async () => {
  const using = await pool.query('SELECT * FROM shipment JOIN trip USING (trip_id) LIMIT 1');
  const on = await pool.query('SELECT * FROM shipment s JOIN trip t ON t.trip_id = s.trip_id LIMIT 1');
  const named = (r) => r.fields.filter((f) => f.name === 'trip_id').length;
  assert.equal(named(using), 1);
  assert.equal(named(on), 2);
  // Con ON, trip_id sin prefijo es ambiguo (42702); con USING, no.
  await pool.query('SELECT trip_id FROM shipment JOIN trip USING (trip_id) LIMIT 1');
  await assert.rejects(
    pool.query('SELECT trip_id FROM shipment s JOIN trip t ON t.trip_id = s.trip_id LIMIT 1'),
    { code: '42702' });
});

test('customer NATURAL JOIN agency combina por city', async () => {
  const natural = await count('SELECT count(*) FROM customer NATURAL JOIN agency');
  const byCity = await count('SELECT count(*) FROM customer c JOIN agency a ON a.city = c.city');
  assert.equal(natural, byCity);
  assert.ok(natural > (await count('SELECT count(*) FROM customer')));
});

test('NATURAL JOIN cambia de significado si aparece una columna común nueva', async () => {
  await inRollback(async (client) => {
    const before = await scalar(client, 'SELECT count(*) FROM shipment NATURAL JOIN route');
    assert.equal(before, await scalar(client, 'SELECT count(*) FROM shipment'));
    await client.query('ALTER TABLE route ADD COLUMN created_at timestamptz NOT NULL DEFAULT now()');
    assert.equal(await scalar(client, 'SELECT count(*) FROM shipment NATURAL JOIN route'), 0);
    const using = await scalar(client,
      'SELECT count(*) FROM shipment JOIN route USING (origin_agency_id, dest_agency_id)');
    assert.equal(using, before);
  });
});

const perAgency = `SELECT a.agency_id, count(*)::int AS star, count(s.shipment_id)::int AS col
  FROM agency a LEFT JOIN shipment s ON s.origin_agency_id = a.agency_id
  GROUP BY a.agency_id`;

test('LEFT JOIN conserva todas las agencias; count(*) cuenta 1 donde no hay envíos', async () => {
  const { rows } = await pool.query(perAgency);
  assert.equal(rows.length, await count('SELECT count(*) FROM agency'));
  const iquitos = rows.find((r) => r.agency_id === 29);
  assert.deepEqual([iquitos.star, iquitos.col], [1, 0]);
});

test('un filtro sobre la tabla de la derecha en WHERE convierte el LEFT JOIN en inner join', async () => {
  const where = await count(`SELECT count(DISTINCT a.agency_id) FROM agency a
    LEFT JOIN shipment s ON s.origin_agency_id = a.agency_id WHERE s.status = 'delivered'`);
  const on = await count(`SELECT count(DISTINCT a.agency_id) FROM agency a
    LEFT JOIN shipment s ON s.origin_agency_id = a.agency_id AND s.status = 'delivered'`);
  const inner = await count(`SELECT count(DISTINCT a.agency_id) FROM agency a
    JOIN shipment s ON s.origin_agency_id = a.agency_id WHERE s.status = 'delivered'`);
  assert.equal(on, await count('SELECT count(*) FROM agency'));
  assert.equal(where, inner);
  assert.ok(where < on);
});

test('en un inner join da lo mismo poner el filtro en ON o en WHERE', async () => {
  const where = await count(`SELECT count(*) FROM agency a
    JOIN shipment s ON s.origin_agency_id = a.agency_id WHERE s.status = 'returned'`);
  const on = await count(`SELECT count(*) FROM agency a
    JOIN shipment s ON s.origin_agency_id = a.agency_id AND s.status = 'returned'`);
  assert.equal(where, on);
});

test('un filtro sobre la tabla de la izquierda en ON no quita filas', async () => {
  const { rows: [r] } = await pool.query(`SELECT count(*)::int AS n, count(s.shipment_id)::int AS m
    FROM agency a LEFT JOIN shipment s
      ON s.origin_agency_id = a.agency_id AND a.region = 'Tumbes'`);
  const tumbes = await count(`SELECT count(*) FROM shipment s JOIN agency a
    ON a.agency_id = s.origin_agency_id WHERE a.region = 'Tumbes'`);
  const others = await count(`SELECT count(*) FROM agency WHERE region <> 'Tumbes'`);
  assert.equal(r.m, tumbes);
  assert.equal(r.n, tumbes + others);
});

test('el anti-join con LEFT JOIN da lo mismo que NOT EXISTS y que el RIGHT JOIN al revés', async () => {
  const left = await count(`SELECT count(*) FROM customer c
    LEFT JOIN shipment s ON s.sender_id = c.customer_id WHERE s.shipment_id IS NULL`);
  const right = await count(`SELECT count(*) FROM shipment s
    RIGHT JOIN customer c ON s.sender_id = c.customer_id WHERE s.shipment_id IS NULL`);
  const notExists = await count(`SELECT count(*) FROM customer c
    WHERE NOT EXISTS (SELECT 1 FROM shipment s WHERE s.sender_id = c.customer_id)`);
  assert.equal(left, notExists);
  assert.equal(right, notExists);
});

test('FULL JOIN conserva las filas sin pareja de los dos lados', async () => {
  const { rows } = await pool.query(`SELECT k.counter_no, c.full_name
    FROM demo.counter k FULL JOIN demo.clerk c
      ON c.agency_id = k.agency_id AND c.counter_no = k.counter_no`);
  assert.ok(rows.some((r) => r.full_name === null));   // ventanilla sin nadie
  assert.ok(rows.some((r) => r.counter_no === null));  // persona sin ventanilla
});

// Pruebas de autorización (Sesión B). Los roles son de todo el servidor:
// cada prueba los crea dentro de una transacción que termina en ROLLBACK,
// con el prefijo w04_ para no chocar con roles de otras bases de datos.
// 42501 es insufficient_privilege.
import { test, after } from 'node:test';
import assert from 'node:assert/strict';
import { pool, inRollback, expectError, scalar } from './db.mjs';

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

test('un usuario hereda los privilegios del rol de grupo', async () => {
  await inRollback(async (client) => {
    await client.query('CREATE ROLE w04_counter_clerk');
    await client.query('CREATE ROLE w04_maria LOGIN');
    await client.query('GRANT w04_counter_clerk TO w04_maria');
    await client.query('GRANT SELECT ON customer, agency TO w04_counter_clerk');
    await client.query('GRANT INSERT ON shipment TO w04_counter_clerk');
    await client.query('SET ROLE w04_maria');
    assert.equal(await scalar(client, 'SELECT count(*) FROM customer'), 1800);
    await 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, 1, 2, 1, 6, 2.5, 30, 'registered', now())`);
    const upd = await expectError(client, 'UPDATE shipment SET price = 0 WHERE shipment_id = 12001');
    assert.equal(upd.code, '42501');
    const sel = await expectError(client, 'SELECT count(*) FROM shipment');
    assert.equal(sel.code, '42501');
  });
});

test('GRANT UPDATE por columna', async () => {
  await inRollback(async (client) => {
    await client.query('CREATE ROLE w04_courier');
    await client.query('GRANT SELECT ON shipment TO w04_courier');
    await client.query('GRANT UPDATE (status, delivered_at) ON shipment TO w04_courier');
    await client.query('SET ROLE w04_courier');
    const ok = await client.query(
      "UPDATE shipment SET status = 'delivered', delivered_at = now() WHERE shipment_id = 46");
    assert.equal(ok.rowCount, 1);
    const err = await expectError(client, 'UPDATE shipment SET price = 0 WHERE shipment_id = 46');
    assert.equal(err.code, '42501');
  });
});

test('una vista sin DNI ni teléfono como mecanismo de seguridad', async () => {
  await inRollback(async (client) => {
    await client.query('CREATE ROLE w04_analyst');
    await client.query('GRANT SELECT ON customer_public TO w04_analyst');
    await client.query('SET ROLE w04_analyst');
    const { fields } = await client.query('SELECT * FROM customer_public LIMIT 1');
    assert.deepEqual(fields.map((f) => f.name), ['customer_id', 'first_name', 'last_name', 'city']);
    const err = await expectError(client, 'SELECT dni FROM customer LIMIT 1');
    assert.equal(err.code, '42501');
  });
});

test('REVOKE ... CASCADE quita también lo que se cedió con WITH GRANT OPTION', async () => {
  await inRollback(async (client) => {
    await client.query('CREATE ROLE w04_a');
    await client.query('CREATE ROLE w04_b');
    await client.query('GRANT SELECT ON route TO w04_a WITH GRANT OPTION');
    await client.query('SET ROLE w04_a');
    await client.query('GRANT SELECT ON route TO w04_b');
    await client.query('RESET ROLE');
    const err = await expectError(client, 'REVOKE SELECT ON route FROM w04_a');
    assert.equal(err.code, '2BP01');  // dependent_objects_still_exist
    await client.query('REVOKE SELECT ON route FROM w04_a CASCADE');
    await client.query('SET ROLE w04_b');
    const denied = await expectError(client, 'SELECT count(*) FROM route');
    assert.equal(denied.code, '42501');
  });
});

// Pruebas de las vistas de views.sql (Sesión A).
import { test, after } from 'node:test';
import assert from 'node:assert/strict';
import { pool, inRollback, expectError, scalar } from './db.mjs';

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

test('route_detail tiene una fila por ruta, con los dos nombres de agencia', async () => {
  assert.equal(await scalar(pool, 'SELECT count(*) FROM route_detail'),
    await scalar(pool, 'SELECT count(*) FROM route'));
  const { rows: [r] } = await pool.query('SELECT origin_name, dest_name FROM route_detail WHERE route_id = 1');
  assert.deepEqual(r, { origin_name: 'Lima - La Victoria', dest_name: 'Trujillo - Centro' });
});

test('una vista ve los cambios al instante; una vista materializada, recién con REFRESH', async () => {
  await inRollback(async (client) => {
    const long = await scalar(client, 'SELECT count(*) FROM long_route');
    await client.query('UPDATE route SET distance_km = 900 WHERE route_id = 1');
    assert.equal(await scalar(client, 'SELECT count(*) FROM long_route'), long + 1);

    const trips = 'SELECT trips FROM route_trip_stats WHERE route_id = 1';
    const before = await scalar(client, trips);
    await client.query(`INSERT INTO trip (trip_id, route_id, vehicle_id, departure_at, arrival_at)
      VALUES (90001, 1, 1, '2026-10-30 07:00-05', '2026-10-30 19:00-05')`);
    assert.equal(await scalar(client, trips), before);
    await client.query('REFRESH MATERIALIZED VIEW route_trip_stats');
    assert.equal(await scalar(client, trips), before + 1);
  });
});

test('una vista simple es actualizable y el cambio llega a la tabla', async () => {
  await inRollback(async (client) => {
    await client.query('UPDATE truck SET capacity_kg = 12000 WHERE vehicle_id = 11');
    assert.equal(await scalar(client, 'SELECT capacity_kg FROM vehicle WHERE vehicle_id = 11'), 12000);
    // La vista no muestra las vans, así que el UPDATE no las encuentra.
    const r = await client.query('UPDATE truck SET capacity_kg = 1 WHERE vehicle_id = 19');
    assert.equal(r.rowCount, 0);
  });
});

test('WITH CHECK OPTION rechaza filas que la vista no mostraría (44000)', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client, "UPDATE truck SET kind = 'van' WHERE vehicle_id = 11");
    assert.equal(err.code, '44000');
    const err2 = await expectError(client,
      "INSERT INTO truck (vehicle_id, plate, kind, capacity_kg) VALUES (99, 'Z9Z-999', 'van', 1500)");
    assert.equal(err2.code, '44000');
  });
});

test('una vista con join no es actualizable automáticamente', async () => {
  await inRollback(async (client) => {
    const err = await expectError(client, 'UPDATE route_detail SET base_fare = 50 WHERE route_id = 1');
    assert.equal(err.code, '55000');
  });
});

# Semana 4: joins, vistas, restricciones de integridad y autorización.
# Se usa dentro del contenedor del curso.
#
#   make          crea la base de datos chasqui_w04 con los datos de ejemplo,
#                 las vistas (views.sql) y el esquema demo (demo.sql)
#   make test     la vuelve a crear, corre los ejemplos (*.sql) y las
#                 pruebas (*.test.mjs)
#   make clean    borra chasqui_w04

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

.PHONY: all test clean

all:
	chasqui-load $(DB)
	$(PSQL) -f views.sql -f demo.sql

# Los archivos de pruebas corren uno tras otro (--test-concurrency=1):
# varias pruebas hacen ALTER TABLE dentro de una transacción, y en paralelo
# se bloquearían entre sí.
test: all
	$(PSQL) -o /dev/null -f joins.sql -f types.sql
	PGDATABASE=$(DB) node --test --test-concurrency=1 --test-reporter=spec

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