-- =============================================================================
-- OC y Clasificación — 1/3: columnas y tablas en la base del CTC
-- Base: asercolg_po (conexión `mysql`) · MariaDB 10.6
-- Ver specs/Especificacion_CTC_OC_Clasificacion.md §A.5
-- =============================================================================
--
-- CLAUDE.md §3: el esquema se administra fuera del repositorio. Este script lo
-- aplica el DBA a mano; NO se crea migración.
--
-- Es idempotente: ADD COLUMN IF NOT EXISTS / CREATE TABLE IF NOT EXISTS.
-- Se puede volver a correr sin duplicar nada.
--
-- =============================================================================
-- ORDEN DE EJECUCIÓN
-- =============================================================================
--
--   1º  specs/sql/oc_clasificacion_01_ctc.sql      <- ESTE archivo (asercolg_po)
--   2º  specs/sql/oc_clasificacion_02_cti.sql      (asercolg_bitacora, conexión `cti`)
--   3º  specs/sql/oc_clasificacion_03_central.sql  (asercolg_central, Central / cia)
--
-- Los tres son independientes entre sí (no hay FK entre bases), pero el código
-- del módulo necesita los tres antes de activarse.
--
-- =============================================================================
-- ¿QUÉ TOCA?
-- =============================================================================
--
--   PO          : 16 columnas nuevas, todas NULL o con DEFAULT. No cambia ni
--                 borra ninguna columna existente. `pedido` ya existe: no se toca
--                 su definición; cambia la forma de generarlo (RN-2.8).
--   po_detalle  : 1 columna nueva (`precio_calculado`) + `valor_unitario` pasa de
--                 DOUBLE(22,2) a DECIMAL(20,6) (RN-1.13: un precio calculado como
--                 total ÷ cantidad no cabe en 2 decimales). Solo lleva lo que es
--                 propio de la línea: los datos del producto van en `material`.
--   material    : 2 columnas nuevas (`estado_producto`, `id_solicitud_clasificacion`).
--                 Los datos del producto que llegan en la OC usan columnas que ya
--                 existen (RN-1.23): código SAP -> `codigo_sap`, código producto
--                 -> `codigo`, descripción corta -> `nombre`. `id_importador` ya
--                 existe y empieza a diligenciarse con compania.id_websimex.
--                 El índice único (grupo, codigo, nombre) pasa a ser
--                 (grupo, id_importador, codigo, nombre) (RN-1.23, A.4 #16).
--   Tablas nuevas: po_motivo_cambio_modalidad, po_modalidad_historial,
--                  pedido_consecutivo.
--
-- ⚠ El cambio de tipo de `po_detalle.valor_unitario` (PASO 2b) reescribe la
--   tabla (~212.000 filas) y bloquea escrituras mientras dura. Correrlo fuera
--   del horario de carga. Los ADD COLUMN son instantáneos en MariaDB 10.6.
--
-- =============================================================================
-- PASO 0 — Estado antes (ejecutar y guardar la salida)
-- =============================================================================

SHOW COLUMNS FROM `PO`;
SHOW COLUMNS FROM `po_detalle`;
SHOW COLUMNS FROM `material`;
SHOW INDEX FROM `material`;

SELECT TABLE_NAME, TABLE_ROWS
  FROM information_schema.TABLES
 WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME IN ('po_motivo_cambio_modalidad', 'po_modalidad_historial', 'pedido_consecutivo');

-- Diagnóstico de la tarea 0.2 (informativo, RN-5.5): qué códigos de modalidad
-- hay hoy en PO. Los históricos NO se convierten; solo se documentan.
SELECT `codigo_medio_trans`, COUNT(*) AS ordenes
  FROM `PO`
 GROUP BY `codigo_medio_trans`
 ORDER BY ordenes DESC;

-- Valores de `pedido` repetidos (por eso no se crea índice UNIQUE, RN-2.8).
SELECT COUNT(*) AS con_pedido, COUNT(DISTINCT `pedido`) AS distintos
  FROM `PO`
 WHERE `pedido` IS NOT NULL AND `pedido` <> '';

-- Materiales con importador (RN-1.23). Medido el 22/10: solo el grupo 27, 1.228
-- filas, todas iguales a un compania.id_websimex del grupo.
SELECT m.`id_grupo_empresarial`, COUNT(*) AS materiales,
       SUM(c.`id` IS NOT NULL) AS coincide_compania
  FROM `material` m
  LEFT JOIN `compania` c
         ON c.`id_grupo_empresarial` = m.`id_grupo_empresarial`
        AND c.`id_websimex` = m.`id_importador`
 WHERE m.`id_importador` IS NOT NULL
 GROUP BY m.`id_grupo_empresarial`;

-- Pares grupo + código repetidos (con nombres distintos). Medido el 22/10: 809.
-- No se corrigen; el formulario los detecta al editar (RN-1.23).
SELECT COUNT(*) AS codigos_repetidos
  FROM (SELECT 1
          FROM `material`
         WHERE `codigo` IS NOT NULL AND `codigo` <> ''
         GROUP BY `id_grupo_empresarial`, `codigo`
        HAVING COUNT(*) > 1) x;

-- Grupos con más de un importador activo: en ellos la compañía del material se
-- elige al crearlo (RN-1.23). Medido el 22/10: 10 grupos (el 45 tiene 35).
SELECT `id_grupo_empresarial`, COUNT(DISTINCT `id_websimex`) AS importadores
  FROM `compania`
 WHERE `inactivo` = 0
 GROUP BY `id_grupo_empresarial`
HAVING importadores > 1
 ORDER BY importadores DESC;

-- =============================================================================
-- PASO 1 — Columnas nuevas en PO
-- =============================================================================

ALTER TABLE `PO`
  ADD COLUMN IF NOT EXISTS `id_importador`           INT(11)      DEFAULT NULL COMMENT 'ID_IMPORTADOR de Websimex. Siempre lleno en OC nuevas (D3); con CTC = compania.id_websimex',
  ADD COLUMN IF NOT EXISTS `sistema_origen`          VARCHAR(10)  DEFAULT NULL COMMENT 'CTC | CENTRAL (RN-1.21)',
  ADD COLUMN IF NOT EXISTS `canal_origen`            VARCHAR(10)  DEFAULT NULL COMMENT 'IA | MANUAL | EXCEL, según el medio de creación (RN-1.9)',
  ADD COLUMN IF NOT EXISTS `ruta_archivo_origen`     VARCHAR(500) DEFAULT NULL COMMENT 'Clave del objeto PRIVADO en S3 (Excel importado o documento de la IA). Nunca una URL (RN-1.9)',
  ADD COLUMN IF NOT EXISTS `fecha_entrega_proveedor` DATE         DEFAULT NULL COMMENT 'Fecha de entrega del proveedor; distinta de fecha_etd (RN-1.3)',
  ADD COLUMN IF NOT EXISTS `condicion_pago`          VARCHAR(150) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `id_comprador`            INT(11)      DEFAULT NULL COMMENT 'FK lógica a asercolg_central.comprador_interno.id (RN-8.7)',
  ADD COLUMN IF NOT EXISTS `comprador_nombre`        VARCHAR(150) DEFAULT NULL COMMENT 'Comprador interno tal como llegó en la carga, mientras no exista el catálogo de Central (tarea 4.4)',
  ADD COLUMN IF NOT EXISTS `usuario_solicitante`     VARCHAR(150) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `email_solicitante`       VARCHAR(150) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `proveedor_por_crear`     VARCHAR(200) DEFAULT NULL COMMENT 'Nombre recibido si el proveedor no existe en Websimex; se limpia al asociar id_exportador (RN-1.22)',
  ADD COLUMN IF NOT EXISTS `id_exportador`           INT(11)      DEFAULT NULL COMMENT 'comex.exportadores.ID_EXPORTADOR (RN-1.19, D9)',
  ADD COLUMN IF NOT EXISTS `codigo_pais_origen`      VARCHAR(10)  DEFAULT NULL COMMENT 'comex.paises.CODIGO_PAIS (RN-1.20)',
  ADD COLUMN IF NOT EXISTS `codigo_pais_procedencia` VARCHAR(10)  DEFAULT NULL COMMENT 'comex.paises.CODIGO_PAIS (RN-1.20)',
  ADD COLUMN IF NOT EXISTS `codigo_moneda`           VARCHAR(5)   DEFAULT NULL COMMENT 'comex.monedas.CODIGO_MONEDA, ISO 4217 (RN-1.20)',
  ADD COLUMN IF NOT EXISTS `codigo_incoterm`         VARCHAR(5)   DEFAULT NULL COMMENT 'comex.condiciones_entrega.CODIGO_CONDICION_ENTREGA (RN-1.20)',
  ADD COLUMN IF NOT EXISTS `codigo_puerto_origen`    VARCHAR(10)  DEFAULT NULL COMMENT 'comex.ex_lugares_embar_arrib_inales, UN/LOCODE (RN-1.20)';

CREATE INDEX IF NOT EXISTS `ix_po_importador`   ON `PO` (`id_importador`, `inactivo`);
CREATE INDEX IF NOT EXISTS `ix_po_entrega`      ON `PO` (`fecha_entrega_proveedor`);
CREATE INDEX IF NOT EXISTS `ix_po_pedido`       ON `PO` (`pedido`);

-- =============================================================================
-- PASO 2 — po_detalle
-- =============================================================================

-- 2a. Columna nueva (instantáneo)
ALTER TABLE `po_detalle`
  ADD COLUMN IF NOT EXISTS `precio_calculado` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 si el precio unitario se calculó como total ÷ cantidad (RN-1.13)';

-- 2b. Precisión del precio unitario. ⚠ REESCRIBE LA TABLA: correr fuera de horario.
--     Se omite solo si ya es DECIMAL(20,6).
SET @tipo_actual = (SELECT COLUMN_TYPE
                      FROM information_schema.COLUMNS
                     WHERE TABLE_SCHEMA = DATABASE()
                       AND TABLE_NAME = 'po_detalle'
                       AND COLUMN_NAME = 'valor_unitario');

SET @sql = IF(@tipo_actual = 'decimal(20,6)',
              'SELECT ''valor_unitario ya es DECIMAL(20,6)'' AS aviso',
              'ALTER TABLE `po_detalle` MODIFY `valor_unitario` DECIMAL(20,6) DEFAULT NULL');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- =============================================================================
-- PASO 3 — material
-- =============================================================================
--
-- El estado y la solicitud son del producto, no de la línea: al clasificar un
-- material, todas las OC que lo tienen ven el cambio. Cada material pertenece a
-- un importador (`id_importador`, RN-1.23), así que el estado se evalúa contra
-- los productos de Websimex de ese importador.
--
-- Los materiales existentes quedan con NULL en las dos columnas: no se evalúan
-- hacia atrás.

ALTER TABLE `material`
  ADD COLUMN IF NOT EXISTS `estado_producto`            VARCHAR(15) DEFAULT NULL COMMENT 'EXISTE | NUEVO | NO_CODIFICADO frente a comex.productos del id_importador (tarea 2.2). NULL en materiales anteriores al módulo',
  ADD COLUMN IF NOT EXISTS `id_solicitud_clasificacion` INT(11)     DEFAULT NULL COMMENT 'FK lógica a asercolg_bitacora.clasificacion_solicitud.id: última solicitud del material';

CREATE INDEX IF NOT EXISTS `ix_material_solicitud` ON `material` (`id_solicitud_clasificacion`);

-- 3b. Índice único por importador (A.4 #16, opción a). Un mismo producto puede
--     existir una vez por cada compañía del grupo.
--
--     El índice actual se llama `Índice 2` (con tilde y espacio). Para no
--     depender del charset del cliente, se busca por sus columnas y no por su
--     nombre. Se borra y se crea en un mismo ALTER, así la tabla nunca queda
--     sin índice único. Es más permisivo que el actual, así que ninguna fila
--     existente puede violarlo. INPLACE + LOCK=NONE: no bloquea escrituras.
--
--     ⚠ MariaDB no compara los NULL en un índice único: los materiales con
--     `id_importador` nulo (todos los anteriores, salvo el grupo 27) quedan sin
--     protección contra duplicados en la base. La primera capa es la
--     validación del formulario y del importador (RN-1.23, tareas 1.4 y 1.8).
SET @indice_actual = (SELECT INDEX_NAME
                        FROM information_schema.STATISTICS
                       WHERE TABLE_SCHEMA = DATABASE()
                         AND TABLE_NAME = 'material'
                         AND NON_UNIQUE = 0
                         AND INDEX_NAME <> 'PRIMARY'
                       GROUP BY INDEX_NAME
                      HAVING GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) = 'id_grupo_empresarial,codigo,nombre');

SET @existe_nuevo = (SELECT COUNT(*)
                       FROM information_schema.STATISTICS
                      WHERE TABLE_SCHEMA = DATABASE()
                        AND TABLE_NAME = 'material'
                        AND INDEX_NAME = 'uk_material_importador');

SET @sql = CASE
  WHEN @existe_nuevo > 0 THEN
    'SELECT ''uk_material_importador ya existe'' AS aviso'
  WHEN @indice_actual IS NULL THEN
    'ALTER TABLE `material` ADD UNIQUE INDEX `uk_material_importador` (`id_grupo_empresarial`, `id_importador`, `codigo`, `nombre`), ALGORITHM=INPLACE, LOCK=NONE'
  ELSE
    CONCAT('ALTER TABLE `material` DROP INDEX `', @indice_actual, '`, ',
           'ADD UNIQUE INDEX `uk_material_importador` (`id_grupo_empresarial`, `id_importador`, `codigo`, `nombre`), ',
           'ALGORITHM=INPLACE, LOCK=NONE')
END;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- =============================================================================
-- PASO 4 — Tablas nuevas
-- =============================================================================

-- Lista corta de motivos de cambio de modalidad (RN-5.2). El motivo se elige
-- de aquí y se complementa con una observación libre.
CREATE TABLE IF NOT EXISTS `po_motivo_cambio_modalidad` (
  `id`         INT(11)      NOT NULL AUTO_INCREMENT,
  `nombre`     VARCHAR(100) NOT NULL,
  `orden`      INT(11)      NOT NULL DEFAULT 0,
  `estado`     TINYINT(4)   NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP    NULL DEFAULT NULL,
  `updated_at` TIMESTAMP    NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_po_motivo_cambio_modalidad_nombre` (`nombre`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Historial inmutable (RN-5.3): la aplicación solo inserta.
CREATE TABLE IF NOT EXISTS `po_modalidad_historial` (
  `id`              INT(11)      NOT NULL AUTO_INCREMENT,
  `id_po`           INT(11)      NOT NULL,
  `valor_anterior`  CHAR(2)      DEFAULT NULL COMMENT 'comex.medios_trasporte.CODIGO_MEDIO_TRANS',
  `valor_nuevo`     CHAR(2)      NOT NULL     COMMENT 'comex.medios_trasporte.CODIGO_MEDIO_TRANS',
  `id_motivo`       INT(11)      NOT NULL,
  `observacion`     VARCHAR(500) DEFAULT NULL,
  `sistema_origen`  VARCHAR(10)  NOT NULL     COMMENT 'CTC | CENTRAL: dónde se hizo el cambio (RN-5.4)',
  `id_user`         INT(11)      NOT NULL     COMMENT 'users.id (CTC) o usuario de Central',
  `usuario`         VARCHAR(150) DEFAULT NULL COMMENT 'Nombre al momento del cambio, para no depender del sistema de origen',
  `fecha`           DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_po_modalidad_historial_po` (`id_po`, `fecha`),
  CONSTRAINT `fk_po_modalidad_historial_motivo`
    FOREIGN KEY (`id_motivo`) REFERENCES `po_motivo_cambio_modalidad` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Consecutivo de pedido (RN-2.8): AAAAMM + 5 dígitos, global, se reinicia cada
-- mes. Se lee y se incrementa dentro de una transacción con
-- SELECT ... FOR UPDATE; eso garantiza que no haya colisiones.
CREATE TABLE IF NOT EXISTS `pedido_consecutivo` (
  `periodo`    CHAR(6)    NOT NULL COMMENT 'AAAAMM',
  `ultimo`     INT(11)    NOT NULL DEFAULT 0 COMMENT 'Último consecutivo entregado en el periodo (máx. 99999)',
  `updated_at` TIMESTAMP  NULL DEFAULT NULL,
  PRIMARY KEY (`periodo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- PASO 5 — Datos iniciales
-- =============================================================================

-- PROPUESTA: la lista de motivos la valida el negocio antes de correr este paso.
INSERT INTO `po_motivo_cambio_modalidad` (`nombre`, `orden`, `estado`, `created_at`)
SELECT v.nombre, v.orden, 1, NOW()
  FROM (SELECT 'Solicitud del cliente'          AS nombre, 1 AS orden UNION ALL
        SELECT 'Urgencia en la entrega',               2 UNION ALL
        SELECT 'Costo del flete',                      3 UNION ALL
        SELECT 'Disponibilidad de espacio o equipo',   4 UNION ALL
        SELECT 'Corrección de un dato errado',         5 UNION ALL
        SELECT 'Otro (detallar en la observación)',    6) v
 WHERE NOT EXISTS (SELECT 1 FROM `po_motivo_cambio_modalidad` m WHERE m.nombre = v.nombre);

-- =============================================================================
-- PASO 6 — Verificación
-- =============================================================================

SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE
  FROM information_schema.COLUMNS
 WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME = 'PO'
   AND COLUMN_NAME IN ('id_importador', 'sistema_origen', 'canal_origen', 'ruta_archivo_origen',
                       'fecha_entrega_proveedor', 'condicion_pago', 'id_comprador', 'comprador_nombre', 'usuario_solicitante',
                       'email_solicitante', 'proveedor_por_crear', 'id_exportador', 'codigo_pais_origen',
                       'codigo_pais_procedencia', 'codigo_moneda', 'codigo_incoterm', 'codigo_puerto_origen');
-- Esperado: 17 filas, todas IS_NULLABLE = YES.

SELECT COLUMN_NAME, COLUMN_TYPE
  FROM information_schema.COLUMNS
 WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME = 'po_detalle'
   AND COLUMN_NAME IN ('precio_calculado', 'valor_unitario');
-- Esperado: 2 filas; valor_unitario = decimal(20,6).

SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE
  FROM information_schema.COLUMNS
 WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME = 'material'
   AND COLUMN_NAME IN ('estado_producto', 'id_solicitud_clasificacion', 'id_importador');
-- Esperado: 3 filas, todas IS_NULLABLE = YES (id_importador ya existía, bigint(20)).

SELECT INDEX_NAME, NON_UNIQUE, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS columnas
  FROM information_schema.STATISTICS
 WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME = 'material'
 GROUP BY INDEX_NAME, NON_UNIQUE;
-- Esperado: PRIMARY (id) · uk_material_importador (id_grupo_empresarial,
-- id_importador, codigo, nombre), NON_UNIQUE = 0 · ix_material_solicitud.
-- Ya no debe aparecer `Índice 2`.

SELECT COUNT(*) AS motivos FROM `po_motivo_cambio_modalidad`;
-- Esperado: 6.

-- =============================================================================
-- Para revertir (solo si el módulo no ha escrito datos todavía)
-- =============================================================================
--
--   DROP TABLE IF EXISTS `po_modalidad_historial`;
--   DROP TABLE IF EXISTS `po_motivo_cambio_modalidad`;
--   DROP TABLE IF EXISTS `pedido_consecutivo`;
--   DROP INDEX IF EXISTS `ix_material_solicitud` ON `material`;
--   -- Volver al índice anterior solo si no hay dos materiales con el mismo
--   -- grupo + codigo + nombre en importadores distintos; si los hay, falla:
--   ALTER TABLE `material`
--     DROP INDEX `uk_material_importador`,
--     ADD UNIQUE INDEX `Índice 2` (`id_grupo_empresarial`, `codigo`, `nombre`);
--   ALTER TABLE `material`
--     DROP COLUMN IF EXISTS `estado_producto`, DROP COLUMN IF EXISTS `id_solicitud_clasificacion`;
--   ALTER TABLE `po_detalle` DROP COLUMN IF EXISTS `precio_calculado`;
--   ALTER TABLE `PO`
--     DROP COLUMN IF EXISTS `id_importador`, DROP COLUMN IF EXISTS `sistema_origen`, ... (las 17 del PASO 1);
--
--   `material.id_importador` NO se borra: ya existía antes de este script.
--   `valor_unitario` NO se devuelve a DOUBLE(22,2): perdería los decimales que
--   ya se hayan guardado.