-- ===========================================================================
--  CLASE 10 · LA BASE DONDE SE VENDE NO ES LA BASE DONDE SE REPORTA
--  Arquitectura de Software · Ingeniería de Sistemas · UTadeo · 2026-2S
--
--  Lo corremos juntos, en orden, y usted anota los tiempos en la hoja de
--  trabajo. Al final de la clase el archivo queda corrido completo y la
--  hoja llena: eso es lo que se entrega.
--
--  ----------------------------------------------------------------------
--  QUÉ VIENE ESCRITO Y QUÉ NO · lea esto primero
--
--     PASO A        ESCRITO.  La tabla plana de Excel, tal como llegan
--                   los datos en la vida real, con sus tres fallas.
--                   No la teclee: córrala y mírela.
--
--     PASOS B, C, D  EN BLANCO.  La normalización: 1FN, 2FN y 3FN.
--                   ESTO LO ESCRIBIMOS JUNTOS, en vivo. Usted lo replica
--                   debajo, en este mismo archivo. Teclearlo es la lección.
--
--     PASOS 0 a 12   ESCRITOS.  El ejercicio de CQRS completo. Los corre,
--                   anota los tiempos y llena la hoja.
--                      0      el modelo normalizado, con volumen de verdad
--                      1 a 6  el informe, su plan, los índices, y por qué
--                             no alcanzan
--                      7 y 8  lo que cuestan los índices al escribir
--                      9 a 11 el modelo de lectura, y lo que se paga
--                      12     cuánto cuesta mantenerlo al día
--
--  ----------------------------------------------------------------------
--  CÓMO SE CORRE
--
--     1. pgAdmin  ->  panel izquierdo  ->  servidor PostgreSQL local
--     2. Clic derecho sobre la base  postgres  ->  Query Tool
--     3. Ícono de carpeta  ->  abra este archivo
--     4. SELECCIONE CON EL MOUSE UN SOLO PASO y presione F5
--
--     NUNCA le dé F5 al archivo completo: se pierde el tiempo de cada
--     paso, que es justamente lo que estamos midiendo.
--
--     El tiempo sale abajo a la derecha, en "Query returned successfully
--     in ...". Ese número va en la hoja.
--
--  ----------------------------------------------------------------------
--  UNA ADVERTENCIA QUE VALE NOTA
--
--  El archivo viene completo a propósito: para que no pierdan la clase
--  transcribiendo. Pero el PASO 3 es una predicción que se escribe ANTES
--  de correr el PASO 4, y esa predicción es parte de la calificación.
--
--  Si usted ya corrió el PASO 5 cuando le pregunten por la predicción,
--  esa predicción vale cero. No es trampa que lo lea: es trampa que lo
--  corra adelantado y después diga que lo predijo.
--
--  Vamos al ritmo del grupo.
--
--  ----------------------------------------------------------------------
--  QUÉ SE SUBE A AVATA, Y CUÁNDO
--
--  HOY MISMO, el día de la clase, antes de las 11:59 p.m., en AVATA,
--  en la actividad «Clase 10 · Modelo de lectura». Un solo envío por
--  grupo, con dos archivos:
--
--     G#_Clase10_Taller.sql   su archivo del taller, corrido y guardado
--     G#_Clase10_Hoja.pdf     la hoja de trabajo escaneada o fotografiada
--
--  Reemplace G# por su grupo: G1, G2, G3, G4, G5 o G6.
--  La hoja en papel se entrega en clase; a AVATA va el respaldo.
--
--  Lo que no esté en AVATA hoy antes de las 11:59 p.m. no se califica.
-- ===========================================================================



-- ===========================================================================
--  PASO A · ASÍ LLEGAN LOS DATOS EN LA VIDA REAL
--
--  Esta tabla ya está escrita. No la tecleen: córranla y mírenla.
--  Es el archivo de Excel tal como lo manda el área comercial de una
--  distribuidora: una fila por factura, los productos repartidos en
--  columnas, y absolutamente todo como texto.
--
--  Sin llave primaria. Sin llaves foráneas. Sin un solo tipo de dato.
--  Y así arranca la mayoría de los proyectos de datos de verdad.
--
--  Corre en un segundo. Son cinco filas, puestas a mano.
-- ===========================================================================

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


CREATE TABLE v0_excel (
    factura          varchar(12),
    fecha            varchar(10),      -- '15/03/2026', texto, no fecha
    vendedor         varchar(80),
    vendedor_cedula  varchar(15),
    vendedor_ciudad  varchar(40),
    cliente          varchar(120),
    cliente_nit      varchar(20),
    cliente_ciudad   varchar(40),
    cliente_depto    varchar(40),
    producto_1       varchar(80),
    cantidad_1       varchar(5),
    precio_1         varchar(20),
    producto_2       varchar(80),
    cantidad_2       varchar(5),
    precio_2         varchar(20),
    producto_3       varchar(80),
    cantidad_3       varchar(5),
    precio_3         varchar(20)
);

INSERT INTO v0_excel VALUES
('FV-1001','15/03/2026','Carlos Ramirez','79456123','Bogota',
 'Tienda La Esquina','900123456-1','Soacha','Cundinamarca',
 'Arroz Diana 500g','12','2800','Aceite Premier 1L','6','9500','Panela Dona Panela','4','3200'),

('FV-1002','15/03/2026','Carlos Ramírez','79456123','Bogotá',
 'Supermercado El Trigal','900987654-2','Chia','Cundinamarca',
 'Arroz Diana 500g','30','2800','Atun Van Camps','24','5400',NULL,NULL,NULL),

('FV-1003','16/03/2026','Luisa Fernanda Gomez','52789456','Medellin',
 'Minimercado San Jose','901456789-3','Envigado','Antioquia',
 'Leche Colanta 1L','48','3900',NULL,NULL,NULL,NULL,NULL,NULL),

('FV-1004','16/03/2026','Luisa F. Gomez','52789456','Medellin',
 'Tienda La Esquina','900123456-1','Soacha','Cundinamarca',
 'Aceite Premier 1L','12','9500','Arroz Diana 500g','20','2800','Jabon Rey 300g','10','2100'),

-- Esta factura trae el precio con formato colombiano. Está así a
-- propósito: es el error que rompe la primera carga en todo proyecto real.
('FV-1005','17/03/2026','Carlos Ramirez','79456123','Bogota',
 'Panaderia El Buen Pan','901222333-4','Bogota','Cundinamarca',
 'Harina Haz de Oros','15','4.350,00','Azucar Incauca 1kg','8','4100',NULL,NULL,NULL);


-- Mírenla completa antes de seguir.
SELECT * FROM v0_excel;


-- --- Problema 1 · el producto vive en una columna, no en una fila --------
--  ¿Cuánto arroz se vendió? Toca recorrer las tres columnas a mano.
--  Y si mañana una factura trae un cuarto producto, HAY QUE ALTERAR LA TABLA.

SELECT sum(cantidad) AS unidades_de_arroz
FROM (SELECT cantidad_1::int AS cantidad FROM v0_excel WHERE producto_1 = 'Arroz Diana 500g'
      UNION ALL
      SELECT cantidad_2::int             FROM v0_excel WHERE producto_2 = 'Arroz Diana 500g'
      UNION ALL
      SELECT cantidad_3::int             FROM v0_excel WHERE producto_3 = 'Arroz Diana 500g') t;


-- --- Problema 2 · el mismo vendedor, escrito de tres formas --------------
--  La cédula dice que es UNA sola persona. El nombre dice que son tres.
--
--  Esta es la ANOMALÍA DE ACTUALIZACIÓN: el dato está repetido en cada
--  fila, entonces se puede contradecir. Y cualquier informe de comisiones
--  va a contar a Carlos dos veces.

SELECT vendedor_cedula,
       count(DISTINCT vendedor)             AS formas_de_escribir_el_nombre,
       string_agg(DISTINCT vendedor, ' | ') AS variantes
FROM v0_excel
GROUP BY vendedor_cedula
ORDER BY 2 DESC;


-- --- Problema 3 · todo es texto, entonces nada se puede sumar ------------
--  La línea de abajo FALLA. Quítenle los dos guiones y córranla:
--
--      ERROR:  invalid input syntax for type numeric: "4.350,00"
--
--  Hay que ver el error. No basta con que se lo cuenten.

-- SELECT sum(precio_1::numeric) FROM v0_excel;

--  Un solo dato con formato colombiano tumba la carga completa. Limpiarlo
--  es la mitad del trabajo de cualquier proyecto de datos, y nadie lo
--  presupuesta:

UPDATE v0_excel
   SET precio_1 = replace(replace(precio_1, '.', ''), ',', '.')
 WHERE precio_1 LIKE '%,%';

SELECT sum(precio_1::numeric) AS ahora_si_suma FROM v0_excel;


-- --- Y una cuarta, que no es consulta sino pregunta ----------------------
--  Fíjense en la columna cliente_depto. Mírenla contra cliente_ciudad.
--
--  ¿De qué depende el departamento? ¿De la factura? ¿Del cliente?
--  ¿O de la ciudad?
--
--  Guarden la respuesta: es la que explica la tercera forma normal.

SELECT DISTINCT cliente_ciudad, cliente_depto FROM v0_excel ORDER BY 1;



-- ===========================================================================
--
--      >>>  AQUÍ PARAMOS. LO QUE SIGUE LO ESCRIBIMOS JUNTOS.  <<<
--
--  Las tres fallas del PASO A se arreglan con lo mismo: NORMALIZAR.
--  Y eso no se lee: se hace. Lo vamos a escribir en vivo, en este orden,
--  y ustedes lo replican debajo de esta línea, en este mismo archivo.
--
--     PASO B · PRIMERA FORMA NORMAL
--              Un valor por celda, y nada de grupos repetidos de columnas.
--              Las tres parejas producto/cantidad/precio se giran y se
--              vuelven filas.  ->  arregla el problema 1
--
--     PASO C · SEGUNDA FORMA NORMAL
--              La llave es (factura, producto). Pero el vendedor y el
--              cliente dependen SOLO de la factura, no de la pareja.
--              Eso es una dependencia parcial, y se parte la tabla.
--
--     PASO D · TERCERA FORMA NORMAL
--              El cliente y su ciudad no dependen de la factura: dependen
--              del NIT. Y el departamento depende de la ciudad.
--              Eso es una dependencia transitiva.
--              ->  arregla el problema 2, y es la respuesta a la pregunta
--                  de cliente_depto
--
--  TECLÉENLO, no lo copien de ningún lado. Ahí está la lección.
--
-- ===========================================================================
--
--  >>>  ESCRIBAN AQUÍ LOS PASOS B, C Y D  <<<
--
--
--
--
--
--
--
--
--
--
--
-- ===========================================================================
--
--  Y DE AQUÍ EN ADELANTE VUELVE A ESTAR TODO ESCRITO.
--
--  Lo que sigue es el ejercicio de CQRS, y va completo: ustedes lo corren
--  paso por paso, anotan los tiempos y llenan la hoja de trabajo.
--
--  Un aviso sobre la escala, para que no los confunda. La tabla del PASO A
--  tiene cinco filas: sirve para VER el problema de diseño, no para medir
--  nada. Para medir hace falta volumen, y eso no se escribe a mano.
--
--  Así que el PASO 0 monta un modelo normalizado —el mismo tipo de modelo
--  que acabamos de construir en el tablero— pero de una tienda, y con los
--  datos generados: 800.000 pedidos y 2.400.000 líneas.
--
--  Donde allá decía «factura», acá dice «pedido». Es el mismo concepto.
-- ===========================================================================



-- ===========================================================================
--  PASO 0 · PREPARAR LA BASE
--  Se corre UNA sola vez. Tarda entre 20 segundos y un minuto.
--
--  Qué está haciendo: generate_series(1, 2400000) produce los números del
--  1 al 2.400.000, y de cada número sale una fila. Por eso no hay nada
--  que descargar: los datos se fabrican acá.
--
--  Al terminar deben salir 800.000 pedidos y 2.400.000 líneas.
--  Si le sale otra cosa, levante la mano antes de seguir.
-- ===========================================================================

-- El PASO A ya creó el esquema. Si usted arrancó directo acá, esta línea
-- lo crea igual.
CREATE SCHEMA IF NOT EXISTS tienda;

-- Esta línea le dice a Postgres en qué esquema buscar cuando uno escribe
-- "pedidos" sin prefijo. Vive solo mientras dure la conexión, por eso se
-- repite al inicio de cada paso.
SET search_path TO tienda;

-- Se botan solo las cinco tablas, no el esquema: así la tabla plana del
-- PASO A queda viva y usted puede volver a ella cuando quiera.
DROP TABLE IF EXISTS items_pedido, pedidos, productos, clientes, sedes CASCADE;


CREATE TABLE sedes (
    id      int         PRIMARY KEY,
    nombre  varchar(40) NOT NULL
);

CREATE TABLE clientes (
    id      int          PRIMARY KEY,
    nombre  varchar(60)  NOT NULL,
    ciudad  varchar(40)  NOT NULL
);

CREATE TABLE productos (
    id         int          PRIMARY KEY,
    nombre     varchar(60)  NOT NULL,
    categoria  varchar(30)  NOT NULL
);

CREATE TABLE pedidos (
    id          bigint     PRIMARY KEY,
    cliente_id  int        NOT NULL REFERENCES clientes(id),
    sede_id     int        NOT NULL REFERENCES sedes(id),
    fecha       timestamp  NOT NULL
);

--  Fíjese en algo a propósito: esta tabla NO declara llaves foráneas,
--  aunque pedido_id y producto_id claramente lo son.
--
--  La razón es de medición. Una llave foránea obliga al motor a validar,
--  en CADA fila insertada, que el padre exista. Con las dos llaves
--  puestas, insertar 150.000 líneas se demoró 1.992 ms solo en
--  validaciones, contra 491 ms sin ellas. Ese costo se le suma igual a
--  los dos escenarios del PASO 7 y del PASO 8, y nos tapa justamente la
--  diferencia que queremos ver hoy, que es la del índice.
--
--  Anote el hallazgo, porque es tan importante como el del día:
--  una llave foránea también se paga en cada escritura. En producción
--  se pone, porque la integridad vale más que los milisegundos. Hoy la
--  dejamos afuera para poder aislar una variable.
CREATE TABLE items_pedido (
    id               bigint         PRIMARY KEY,
    pedido_id        bigint         NOT NULL,
    producto_id      int            NOT NULL,
    cantidad         int            NOT NULL,
    precio_unitario  numeric(12,2)  NOT NULL
);


-- Catálogos
INSERT INTO sedes
SELECT g, 'Sede ' || g
FROM generate_series(1, 6) AS g;

INSERT INTO clientes
SELECT g,
       'Cliente ' || g,
       (ARRAY['Bogota','Medellin','Cali','Barranquilla'])[1 + (g % 4)]
FROM generate_series(1, 50000) AS g;

INSERT INTO productos
SELECT g,
       'Producto ' || g,
       (ARRAY['Bebidas','Aseo','Snacks','Lacteos','Panaderia'])[1 + (g % 5)]
FROM generate_series(1, 2000) AS g;


-- Dos años de pedidos: del 1 de enero de 2025 al 31 de diciembre de 2026.
-- La hora también se mueve, porque en producción nadie vende todo a
-- medianoche y eso importa cuando uno agrupa por mes.
INSERT INTO pedidos
SELECT g,
       1 + (g % 50000),
       1 + (g % 6),
       timestamp '2025-01-01' + ((g - 1) % 730) * interval '1 day'
                              + (g % 86400)     * interval '1 second'
FROM generate_series(1, 800000) AS g;

-- Tres líneas por pedido, en promedio.
INSERT INTO items_pedido
SELECT g,
       1 + (g % 800000),
       1 + (g % 2000),
       1 + (g % 5),
       (1000 + (g % 90) * 500)::numeric
FROM generate_series(1, 2400000) AS g;

-- ANALYZE actualiza las estadísticas. Sin esto el motor planea a ciegas.
ANALYZE;


-- Verificación. Deben salir 800.000 y 2.400.000.
SELECT 'pedidos'      AS tabla, count(*) AS filas FROM pedidos
UNION ALL
SELECT 'items_pedido' AS tabla, count(*) AS filas FROM items_pedido;



-- ===========================================================================
--  PASO 1 · EL INFORME
--
--  El gerente pide las ventas de marzo de 2026, por categoría.
--  Es la consulta más normal del mundo: dos JOIN, un filtro de fecha y
--  un GROUP BY. Nada que no sepan hacer desde el semestre pasado.
--
--  ANOTE ESTE TIEMPO. Es el número contra el que vamos a comparar todo
--  lo que sigue.
-- ===========================================================================

SET search_path TO tienda;

SELECT date_trunc('month', p.fecha)::date                        AS mes,
       pr.categoria,
       round(sum(i.cantidad * i.precio_unitario) / 1000000.0, 1) AS venta_millones,
       count(*)                                                  AS lineas_leidas
FROM       pedidos      p
INNER JOIN items_pedido i  ON i.pedido_id   = p.id
INNER JOIN productos    pr ON pr.id         = i.producto_id
WHERE p.fecha >= timestamp '2026-03-01'
  AND p.fecha <  timestamp '2026-04-01'
GROUP BY 1, 2
ORDER BY 1, 2;

--  Dos detalles de la consulta que vale la pena mirar:
--
--  date_trunc('month', fecha) recorta la fecha al primer día del mes,
--  pero devuelve un timestamp, así que arrastra el 00:00:00. El ::date
--  lo convierte en fecha limpia. Sin eso la columna sale fea.
--
--  El filtro usa >= y < en vez de BETWEEN. Con BETWEEN hay que acordarse
--  de restar un día y de la hora; así no. Es la forma correcta de filtrar
--  un rango de fechas cuando la columna tiene hora.



-- ===========================================================================
--  PASO 2 · EL PLAN
--
--  Ahora le preguntamos al motor QUÉ HIZO para responder eso. Es una
--  palabra delante de la consulta, y es la herramienta más útil que se
--  van a llevar hoy.
--
--  BUSQUE EN LA SALIDA la línea que menciona items_pedido.
--  ANOTE EN LA HOJA cuántas filas dice que leyó.
-- ===========================================================================

SET search_path TO tienda;

EXPLAIN (ANALYZE, COSTS OFF)
SELECT date_trunc('month', p.fecha)::date,
       pr.categoria,
       sum(i.cantidad * i.precio_unitario)
FROM       pedidos      p
INNER JOIN items_pedido i  ON i.pedido_id = p.id
INNER JOIN productos    pr ON pr.id       = i.producto_id
WHERE p.fecha >= timestamp '2026-03-01'
  AND p.fecha <  timestamp '2026-04-01'
GROUP BY 1, 2;

--  Lo que va a ver: "Seq Scan on items_pedido ... rows=2400000".
--  Seq Scan quiere decir que recorrió la tabla completa, fila por fila.
--  Dos millones cuatrocientas mil filas leídas, para entregar cinco
--  resultados. Guarde ese número.



-- ===========================================================================
--  PASO 3 · LA PREDICCIÓN            >>>  NO ES SQL. NO TOQUE EL TECLADO.
--
--  En el PASO 4 vamos a crear tres índices diseñados exactamente para
--  acelerar el informe del PASO 1. Antes de crearlos, escriba en la hoja:
--
--     a) ¿Cuánto va a mejorar el informe? Un número o un porcentaje.
--     b) ¿Qué le va a pasar a la velocidad de insertar pedidos?
--        Más rápido, igual o más lento. Y por qué.
--
--  La predicción equivocada vale lo mismo que la correcta. Lo que no
--  vale es no comprometerse con una respuesta.
--
--  Esto se califica. Y se califica antes de correr el PASO 4.
-- ===========================================================================



-- ===========================================================================
--  PASO 4 · LOS TRES ÍNDICES
--
--  ix_pedidos_fecha    encontrar rápido los pedidos de un mes
--  ix_items_pedido     encontrar rápido las líneas de un pedido
--  ix_items_producto   encontrar rápido las líneas de un producto
--
--  Los tres están hechos a la medida de la consulta del PASO 1.
--
--  Fíjese cuánto se demora en CREARLOS. Ese tiempo también lo paga
--  alguien, y en una tabla de producción se paga con la tabla bloqueada.
-- ===========================================================================

SET search_path TO tienda;

CREATE INDEX ix_pedidos_fecha  ON pedidos(fecha);
CREATE INDEX ix_items_pedido   ON items_pedido(pedido_id);
CREATE INDEX ix_items_producto ON items_pedido(producto_id);

ANALYZE;

-- Confirme que quedaron los tres:
SELECT indexname, tablename
FROM pg_indexes
WHERE schemaname = 'tienda'
  AND indexname LIKE 'ix_%'
ORDER BY tablename, indexname;



-- ===========================================================================
--  PASO 5 · EL MISMO INFORME, AHORA CON ÍNDICES
--
--  No lo vuelva a escribir: suba al PASO 1 y dele F5 otra vez.
--  Esta consulta está acá solo para que quede en el orden del archivo.
--
--  ANOTE EL TIEMPO Y COMPÁRELO CON EL DEL PASO 1.
-- ===========================================================================

SET search_path TO tienda;

SELECT date_trunc('month', p.fecha)::date                        AS mes,
       pr.categoria,
       round(sum(i.cantidad * i.precio_unitario) / 1000000.0, 1) AS venta_millones,
       count(*)                                                  AS lineas_leidas
FROM       pedidos      p
INNER JOIN items_pedido i  ON i.pedido_id = p.id
INNER JOIN productos    pr ON pr.id       = i.producto_id
WHERE p.fecha >= timestamp '2026-03-01'
  AND p.fecha <  timestamp '2026-04-01'
GROUP BY 1, 2
ORDER BY 1, 2;



-- ===========================================================================
--  PASO 6 · EL PLAN OTRA VEZ
--
--  Tres preguntas, en este orden. Respóndalas mirando la salida, no
--  adivinando:
--
--     1. ¿Aparece ix_pedidos_fecha en el plan?
--     2. ¿Qué dice la línea de items_pedido? ¿Cuántas filas leyó?
--     3. Si el índice SÍ se está usando, ¿por qué el informe no mejoró?
-- ===========================================================================

SET search_path TO tienda;

EXPLAIN (ANALYZE, COSTS OFF)
SELECT date_trunc('month', p.fecha)::date,
       pr.categoria,
       sum(i.cantidad * i.precio_unitario)
FROM       pedidos      p
INNER JOIN items_pedido i  ON i.pedido_id = p.id
INNER JOIN productos    pr ON pr.id       = i.producto_id
WHERE p.fecha >= timestamp '2026-03-01'
  AND p.fecha <  timestamp '2026-04-01'
GROUP BY 1, 2;

--  La respuesta a la pregunta 3, cuando ya la hayan discutido:
--
--  El índice funcionó. Encontró rápido los pedidos de marzo. El problema
--  nunca fue ENCONTRAR las filas: era SUMARLAS. Para dar cinco números
--  hay que recorrer igual dos millones cuatrocientas mil líneas, y eso
--  ningún índice lo arregla.
--
--  Un índice sirve para encontrar pocas filas entre muchas.
--  No sirve para no tener que leer muchas filas.



-- ===========================================================================
--  PASO 7 · ESCRIBIR, CON LOS ÍNDICES PUESTOS
--
--  Dos INSERT: cincuenta mil pedidos y ciento cincuenta mil líneas.
--  CÓRRALOS POR SEPARADO y anote los dos tiempos.
--
--  El segundo es el que importa.
-- ===========================================================================

SET search_path TO tienda;

INSERT INTO pedidos
SELECT 900000 + g,
       1 + (g % 50000),
       1 + (g % 6),
       timestamp '2026-06-01' + (g % 30) * interval '1 day'
FROM generate_series(1, 50000) AS g;

INSERT INTO items_pedido
SELECT 3000000 + g,
       900001 + (g % 50000),
       1 + (g % 2000),
       1 + (g % 5),
       (1000 + (g % 90) * 500)::numeric
FROM generate_series(1, 150000) AS g;



-- ===========================================================================
--  PASO 8 · LO MISMO, SIN LOS ÍNDICES
--
--  Devolvemos la apuesta: botamos los tres índices y volvemos a insertar
--  exactamente la misma cantidad de filas.
--
--  ANOTE LOS DOS TIEMPOS Y COMPÁRELOS CON LOS DEL PASO 7.
--  Esa división es la conclusión del primer bloque de la clase.
-- ===========================================================================

SET search_path TO tienda;

DROP INDEX IF EXISTS ix_pedidos_fecha;
DROP INDEX IF EXISTS ix_items_pedido;
DROP INDEX IF EXISTS ix_items_producto;

INSERT INTO pedidos
SELECT 1100000 + g,
       1 + (g % 50000),
       1 + (g % 6),
       timestamp '2026-07-01' + (g % 30) * interval '1 day'
FROM generate_series(1, 50000) AS g;

INSERT INTO items_pedido
SELECT 3400000 + g,
       1100001 + (g % 50000),
       1 + (g % 2000),
       1 + (g % 5),
       (1000 + (g % 90) * 500)::numeric
FROM generate_series(1, 150000) AS g;

--  Ojo con un detalle: la llave primaria de cada tabla tiene su propio
--  índice y ese NO se puede botar, porque es parte de la definición de
--  la tabla. Así que el tiempo "sin índices" todavía incluye el
--  mantenimiento de uno. La diferencia real es mayor que la que midió.



-- ===========================================================================
--  PASO 9 · EL MODELO DE LECTURA
--
--  Hasta aquí tratamos de encontrar las filas más rápido. El plan nos
--  dijo que el costo estaba en sumar. Entonces la pregunta cambia:
--
--      ¿y si la suma ya estuviera hecha?
--
--  Mire bien la consulta: es LA MISMA del PASO 1, sin el filtro de mes.
--  Lo único distinto es que en vez de correrla cada vez que alguien
--  pregunta, la corremos UNA VEZ y guardamos el resultado en una tabla.
--
--  ANOTE CUÁNTAS FILAS QUEDARON.
-- ===========================================================================

SET search_path TO tienda;

DROP TABLE IF EXISTS ventas_mes_categoria;

CREATE TABLE ventas_mes_categoria AS
SELECT date_trunc('month', p.fecha)::date      AS mes,
       pr.categoria,
       sum(i.cantidad * i.precio_unitario)     AS venta,
       sum(i.cantidad)                         AS unidades,
       count(*)                                AS lineas
FROM       pedidos      p
INNER JOIN items_pedido i  ON i.pedido_id = p.id
INNER JOIN productos    pr ON pr.id       = i.producto_id
GROUP BY 1, 2;

--  La llave primaria del modelo de lectura es la combinación por la que
--  se consulta. Aquí no es un id: es (mes, categoria).
ALTER TABLE ventas_mes_categoria ADD PRIMARY KEY (mes, categoria);

--  La marca de agua. Sin esto, la pantalla no puede decirle al usuario
--  de cuándo son los datos que está viendo, y eso es lo que convierte
--  un desfase en un reclamo.
ALTER TABLE ventas_mes_categoria ADD COLUMN actualizado_en timestamptz DEFAULT now();

ANALYZE ventas_mes_categoria;

SELECT count(*) AS filas_del_modelo_de_lectura FROM ventas_mes_categoria;



-- ===========================================================================
--  PASO 10 · EL INFORME CONTRA EL MODELO DE LECTURA
--
--  Son tres líneas. Escríbalas: el contraste se siente al escribirlo.
--
--  ANOTE EL TIEMPO Y DIVÍDALO CONTRA EL DEL PASO 1.
-- ===========================================================================

SET search_path TO tienda;

SELECT mes,
       categoria,
       round(venta / 1000000.0, 1) AS venta_millones,
       actualizado_en
FROM ventas_mes_categoria
WHERE mes = date '2026-03-01'
ORDER BY categoria;

--  Mismo resultado del PASO 1. Mismo motor, misma máquina.
--  Lo único que cambió es cuántas filas hubo que leer para producirlo.



-- ===========================================================================
--  PASO 11 · EL PEDIDO QUE NO APARECE
--
--  Todo bonito hasta aquí. Ahora veamos qué se paga.
--
--  Vamos a registrar una venta nueva de marzo, el mes que ya está
--  calculado en el modelo de lectura.
--
--  Dos preguntas:
--     ¿Quedó guardado el pedido?
--     ¿Lo muestra el informe?
-- ===========================================================================

SET search_path TO tienda;

INSERT INTO pedidos
VALUES (9999999, 1, 1, timestamp '2026-03-15 10:00:00');

INSERT INTO items_pedido
VALUES (99999999, 9999999, 1, 100, 1000000.00);


-- Lado de escritura: la venta está ahí. Compare con el PASO 1.
SELECT round(sum(i.cantidad * i.precio_unitario) / 1000000.0, 1) AS marzo_en_escritura
FROM       pedidos      p
INNER JOIN items_pedido i ON i.pedido_id = p.id
WHERE p.fecha >= timestamp '2026-03-01'
  AND p.fecha <  timestamp '2026-04-01';


-- Lado de lectura: todavía no la ve.
SELECT round(sum(venta) / 1000000.0, 1) AS marzo_en_lectura,
       max(actualizado_en)              AS datos_al
FROM ventas_mes_categoria
WHERE mes = date '2026-03-01';


--  Eso que acaba de ver no es un error. Es lo que se paga por haber
--  separado el lado de lectura del lado de escritura, y tiene nombre:
--
--      CONSISTENCIA EVENTUAL
--
--  El tamaño de ese desfase no lo decide el arquitecto. Lo decide el
--  negocio. Lo que hace el arquitecto es preguntarlo, escribirlo en la
--  decisión, y poner la marca de agua en la pantalla para que nadie lo
--  descubra por su cuenta un lunes a las ocho de la mañana.



-- ===========================================================================
--  PASO 12 · CERRAR EL CÍRCULO  ·  volver a tomar la foto
--
--  Opcional, si alcanza el tiempo. La pregunta que lo cierra todo:
--  ¿cuánto cuesta mantener el lado de lectura al día?
-- ===========================================================================

SET search_path TO tienda;

-- Recálculo completo. Es lo que haría un job programado de madrugada.
TRUNCATE ventas_mes_categoria;

INSERT INTO ventas_mes_categoria (mes, categoria, venta, unidades, lineas, actualizado_en)
SELECT date_trunc('month', p.fecha)::date,
       pr.categoria,
       sum(i.cantidad * i.precio_unitario),
       sum(i.cantidad),
       count(*),
       now()
FROM       pedidos      p
INNER JOIN items_pedido i  ON i.pedido_id = p.id
INNER JOIN productos    pr ON pr.id       = i.producto_id
GROUP BY 1, 2;

ANALYZE ventas_mes_categoria;

-- Ahora sí coinciden los dos lados.
SELECT round(sum(venta) / 1000000.0, 1) AS marzo_en_lectura,
       max(actualizado_en)              AS datos_al
FROM ventas_mes_categoria
WHERE mes = date '2026-03-01';

--  Anote cuánto se demoró ese recálculo. Esa es la respuesta a
--  "¿cada cuánto podemos refrescar?". No es una opinión: es un número.



-- ===========================================================================
--  RESUMEN · llene esta tabla en la hoja con SUS números
--
--     PASO  1   informe sin índices ....................... ______ ms
--     PASO  5   informe con los 3 índices ................. ______ ms
--     PASO  7   insertar 150.000 líneas CON índices ....... ______ ms
--     PASO  8   insertar 150.000 líneas SIN índices ....... ______ ms
--     PASO 10   informe contra el modelo de lectura ....... ______ ms
--     PASO  9   filas del modelo de lectura ............... ______
--     PASO 12   costo de refrescar el modelo .............. ______ ms
--
--  Las tres conclusiones salen de dividir:
--
--     paso 1 / paso 5    ->  cuánto ganó la LECTURA con índices
--     paso 7 / paso 8    ->  cuánto perdió la ESCRITURA con índices
--     paso 1 / paso 10   ->  cuánto ganó la LECTURA con el modelo aparte
--
--  Para borrar todo y volver a empezar:   DROP SCHEMA tienda CASCADE;
-- ===========================================================================
