-- ============================================================
-- MIGRACIÓN: Sincronización BD Local
-- Fecha: 2026-02-24
-- Descripción: Crea tablas de control de sync y agrega
--              updated_at a tablas que no lo tengan
-- ============================================================

-- -----------------------------------------------
-- 1. TABLAS DE CONTROL DE SINCRONIZACIÓN
-- -----------------------------------------------

-- Historial de cada sincronización
CREATE TABLE IF NOT EXISTS sync_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tabla VARCHAR(100) NOT NULL,
    sucursal VARCHAR(100) NOT NULL,
    direccion ENUM('push','pull') NOT NULL,
    registros_enviados INT DEFAULT 0,
    registros_recibidos INT DEFAULT 0,
    registros_conflictos INT DEFAULT 0,
    ultimo_sync_timestamp DATETIME(6) NULL,
    estado ENUM('iniciado','completado','error') DEFAULT 'iniciado',
    error_mensaje TEXT NULL,
    duracion_ms INT DEFAULT 0,
    created_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
    INDEX idx_tabla (tabla),
    INDEX idx_sucursal (sucursal),
    INDEX idx_estado (estado),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Marca de agua por tabla/sucursal
CREATE TABLE IF NOT EXISTS sync_control (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tabla VARCHAR(100) NOT NULL,
    sucursal VARCHAR(100) NOT NULL,
    last_push_at DATETIME(6) NULL,
    last_pull_at DATETIME(6) NULL,
    UNIQUE KEY uk_tabla_sucursal (tabla, sucursal)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mapeo de IDs remotos a locales (para FK en pull)
CREATE TABLE IF NOT EXISTS sync_id_map (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tabla VARCHAR(100) NOT NULL,
    sucursal_origen VARCHAR(100) NOT NULL,
    id_remoto INT NOT NULL,
    id_local INT NOT NULL,
    UNIQUE KEY uk_mapping (tabla, sucursal_origen, id_remoto),
    INDEX idx_local (tabla, id_local)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- -----------------------------------------------
-- 2. AGREGAR updated_at A TABLAS QUE NO LO TENGAN
-- -----------------------------------------------
-- Usamos un procedimiento para verificar antes de agregar

DELIMITER //

DROP PROCEDURE IF EXISTS add_updated_at_if_missing//

CREATE PROCEDURE add_updated_at_if_missing(IN p_table VARCHAR(100))
BEGIN
    DECLARE col_exists INT DEFAULT 0;

    SELECT COUNT(*) INTO col_exists
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = p_table
      AND COLUMN_NAME = 'updated_at';

    IF col_exists = 0 THEN
        SET @sql = CONCAT(
            'ALTER TABLE `', p_table, '` ',
            'ADD COLUMN updated_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)'
        );
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        -- Inicializar con fecha_creacion si existe, o NOW()
        SET @has_fecha = 0;
        SELECT COUNT(*) INTO @has_fecha
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = p_table
          AND COLUMN_NAME = 'fecha_creacion';

        IF @has_fecha > 0 THEN
            SET @sql2 = CONCAT('UPDATE `', p_table, '` SET updated_at = fecha_creacion WHERE updated_at IS NULL');
            PREPARE stmt2 FROM @sql2;
            EXECUTE stmt2;
            DEALLOCATE PREPARE stmt2;
        END IF;
    END IF;

    -- Agregar índice a updated_at si no existe
    SET @idx_exists = 0;
    SELECT COUNT(*) INTO @idx_exists
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = p_table
      AND INDEX_NAME = 'idx_updated_at';

    IF @idx_exists = 0 THEN
        SET @sql3 = CONCAT('ALTER TABLE `', p_table, '` ADD INDEX idx_updated_at (updated_at)');
        PREPARE stmt3 FROM @sql3;
        EXECUTE stmt3;
        DEALLOCATE PREPARE stmt3;
    END IF;
END//

DELIMITER ;

-- Aplicar a todas las tablas existentes en la BD
CALL add_updated_at_if_missing('categorias');
CALL add_updated_at_if_missing('almacenes');
CALL add_updated_at_if_missing('configuraciones');
CALL add_updated_at_if_missing('cajas');
CALL add_updated_at_if_missing('cat_regimen_fiscal');
CALL add_updated_at_if_missing('cat_uso_cfdi');
CALL add_updated_at_if_missing('cat_forma_pago');
CALL add_updated_at_if_missing('cat_metodo_pago');
CALL add_updated_at_if_missing('roles');
CALL add_updated_at_if_missing('proveedores');
CALL add_updated_at_if_missing('vehiculos');
CALL add_updated_at_if_missing('rangos_flete');
CALL add_updated_at_if_missing('permisos');
CALL add_updated_at_if_missing('usuarios');
CALL add_updated_at_if_missing('productos');
CALL add_updated_at_if_missing('clientes');
CALL add_updated_at_if_missing('rol_permisos');
CALL add_updated_at_if_missing('usuario_almacenes');
CALL add_updated_at_if_missing('producto_equivalencias');
CALL add_updated_at_if_missing('descuentos_volumen');
CALL add_updated_at_if_missing('cliente_telefonos');
CALL add_updated_at_if_missing('cliente_direcciones');
CALL add_updated_at_if_missing('cliente_datos_fiscales');
CALL add_updated_at_if_missing('configuracion_rangos');
CALL add_updated_at_if_missing('ventas');
CALL add_updated_at_if_missing('ventas_mostrador');
CALL add_updated_at_if_missing('ordenes_compra');
CALL add_updated_at_if_missing('rutas_entrega');
CALL add_updated_at_if_missing('venta_detalles');
CALL add_updated_at_if_missing('venta_pagos');
CALL add_updated_at_if_missing('ventas_mostrador_detalles');
CALL add_updated_at_if_missing('ventas_mostrador_pagos');
CALL add_updated_at_if_missing('cortes_caja');
CALL add_updated_at_if_missing('retiros_efectivo');
CALL add_updated_at_if_missing('ingresos_efectivo');
CALL add_updated_at_if_missing('orden_compra_detalle');
CALL add_updated_at_if_missing('ruta_pedidos');
CALL add_updated_at_if_missing('pagos_clientes');
CALL add_updated_at_if_missing('facturas');
CALL add_updated_at_if_missing('factura_conceptos');
CALL add_updated_at_if_missing('factura_ventas');
CALL add_updated_at_if_missing('factura_ventas_mostrador');
CALL add_updated_at_if_missing('precio_rangos');
CALL add_updated_at_if_missing('diferencia_costos');
CALL add_updated_at_if_missing('vehiculo_rangos');
CALL add_updated_at_if_missing('log_actividad');
CALL add_updated_at_if_missing('log_seguridad');
CALL add_updated_at_if_missing('sesiones');

-- Limpiar
DROP PROCEDURE IF EXISTS add_updated_at_if_missing;

-- -----------------------------------------------
-- VERIFICACIÓN
-- -----------------------------------------------
SELECT 'sync_log' AS tabla, COUNT(*) AS registros FROM sync_log
UNION ALL
SELECT 'sync_control', COUNT(*) FROM sync_control
UNION ALL
SELECT 'sync_id_map', COUNT(*) FROM sync_id_map;
