-- ============================================================
-- MIGRACIÓN: Sincronización BD NUBE
-- Fecha: 2026-02-24
-- Descripción: Idéntica a la local + agrega sucursal e id_origen
--              a cada tabla para identificar el origen de registros
-- ============================================================

-- -----------------------------------------------
-- 1. TABLAS DE CONTROL DE SINCRONIZACIÓN (iguales a local)
-- -----------------------------------------------

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;

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;

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
-- -----------------------------------------------

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;

        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;

    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 ;

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');

DROP PROCEDURE IF EXISTS add_updated_at_if_missing;


-- -----------------------------------------------
-- 3. AGREGAR sucursal + id_origen A CADA TABLA
-- -----------------------------------------------

DELIMITER //

DROP PROCEDURE IF EXISTS add_cloud_columns//

CREATE PROCEDURE add_cloud_columns(IN p_table VARCHAR(100))
BEGIN
    DECLARE col_suc INT DEFAULT 0;
    DECLARE col_ido INT DEFAULT 0;
    DECLARE uk_exists INT DEFAULT 0;

    -- Verificar si ya tiene sucursal
    SELECT COUNT(*) INTO col_suc
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = p_table
      AND COLUMN_NAME = 'sucursal';

    IF col_suc = 0 THEN
        SET @sql1 = CONCAT(
            'ALTER TABLE `', p_table, '` ',
            "ADD COLUMN sucursal VARCHAR(100) NOT NULL DEFAULT ''"
        );
        PREPARE stmt FROM @sql1;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;

    -- Verificar si ya tiene id_origen
    SELECT COUNT(*) INTO col_ido
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = p_table
      AND COLUMN_NAME = 'id_origen';

    IF col_ido = 0 THEN
        SET @sql2 = CONCAT(
            'ALTER TABLE `', p_table, '` ',
            'ADD COLUMN id_origen INT NOT NULL DEFAULT 0'
        );
        PREPARE stmt2 FROM @sql2;
        EXECUTE stmt2;
        DEALLOCATE PREPARE stmt2;
    END IF;

    -- Agregar unique key compuesto si no existe
    SELECT COUNT(*) INTO uk_exists
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = p_table
      AND INDEX_NAME = 'uk_sucursal_id_origen';

    IF uk_exists = 0 THEN
        SET @sql3 = CONCAT(
            'ALTER TABLE `', p_table, '` ',
            'ADD UNIQUE KEY uk_sucursal_id_origen (sucursal, id_origen)'
        );
        PREPARE stmt3 FROM @sql3;
        EXECUTE stmt3;
        DEALLOCATE PREPARE stmt3;
    END IF;

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

    IF @idx_suc = 0 THEN
        SET @sql4 = CONCAT('ALTER TABLE `', p_table, '` ADD INDEX idx_sucursal (sucursal)');
        PREPARE stmt4 FROM @sql4;
        EXECUTE stmt4;
        DEALLOCATE PREPARE stmt4;
    END IF;
END//

DELIMITER ;

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

DROP PROCEDURE IF EXISTS add_cloud_columns;

-- -----------------------------------------------
-- 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;
