-- =====================================================
-- SISTEMA DE SINCRONIZACIÓN MULTI-CLIENTE
-- =====================================================
-- Autor: BDI Central Server
-- Fecha: 2026-02-26
-- Propósito: Gestionar múltiples clientes con sus propios pricefiles SFTP
-- =====================================================

-- -----------------------------------------------------
-- Tabla: sftp_sync_clients
-- Descripción: Clientes independientes para sincronización
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sftp_sync_clients` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `client_name` VARCHAR(255) NOT NULL COMMENT 'Nombre del cliente',
  `client_code` VARCHAR(50) NOT NULL COMMENT 'Código único del cliente (ej: LBSISTEMAS)',
  `email` VARCHAR(255) NULL COMMENT 'Email de contacto',
  `phone` VARCHAR(50) NULL COMMENT 'Teléfono de contacto',
  `active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=Activo, 0=Inactivo',
  `notes` TEXT NULL COMMENT 'Notas adicionales',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_client_code` (`client_code`),
  KEY `idx_active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Clientes para sincronización de pricefiles';


-- -----------------------------------------------------
-- Tabla: client_sftp_credentials
-- Descripción: Credenciales SFTP por cliente
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client_sftp_credentials` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `client_id` INT(11) NOT NULL COMMENT 'FK a sftp_sync_clients',
  `sftp_host` VARCHAR(255) NOT NULL COMMENT 'Host SFTP (ej: ftp.cliente.com)',
  `sftp_port` INT(5) NOT NULL DEFAULT 22 COMMENT 'Puerto SFTP',
  `sftp_user` VARCHAR(100) NOT NULL COMMENT 'Usuario SFTP',
  `sftp_password` VARCHAR(255) NOT NULL COMMENT 'Contraseña SFTP (encriptada)',
  `sftp_directory` VARCHAR(500) NOT NULL COMMENT 'Ruta del directorio en SFTP',
  `sftp_filename` VARCHAR(100) NOT NULL DEFAULT 'PRICE.ZIP' COMMENT 'Nombre del archivo a descargar',
  `connection_type` ENUM('sftp', 'ftp', 'ftps') NOT NULL DEFAULT 'sftp' COMMENT 'Tipo de conexión',
  `active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=Activo, 0=Inactivo',
  `last_connection_test` TIMESTAMP NULL COMMENT 'Última prueba de conexión',
  `last_connection_status` VARCHAR(255) NULL COMMENT 'Estado de última conexión',
  `last_sync_date` TIMESTAMP NULL COMMENT 'Última sincronización exitosa',
  `last_sync_products_inserted` INT(11) NULL DEFAULT 0 COMMENT 'Productos insertados en última sync',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_client_id` (`client_id`),
  KEY `idx_active` (`active`),
  CONSTRAINT `fk_credentials_client` 
    FOREIGN KEY (`client_id`) 
    REFERENCES `sftp_sync_clients` (`id`) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Credenciales SFTP de clientes';


-- -----------------------------------------------------
-- Tabla: client_sync_log
-- Descripción: Log de sincronizaciones
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client_sync_log` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `client_id` INT(11) NOT NULL COMMENT 'FK a sftp_sync_clients',
  `sync_type` ENUM('full', 'incremental', 'manual') NOT NULL DEFAULT 'incremental',
  `status` ENUM('started', 'downloading', 'processing', 'completed', 'error') NOT NULL,
  `products_found` INT(11) NULL DEFAULT 0 COMMENT 'Productos encontrados en PRICE.TXT',
  `products_inserted` INT(11) NULL DEFAULT 0 COMMENT 'Productos nuevos insertados',
  `products_skipped` INT(11) NULL DEFAULT 0 COMMENT 'Productos existentes (omitidos)',
  `manufacturers_linked` INT(11) NULL DEFAULT 0 COMMENT 'Relaciones de marcas creadas',
  `categories_linked` INT(11) NULL DEFAULT 0 COMMENT 'Relaciones de categorías creadas',
  `error_message` TEXT NULL COMMENT 'Mensaje de error si falla',
  `execution_time_seconds` INT(11) NULL COMMENT 'Tiempo de ejecución en segundos',
  `started_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  KEY `idx_client_id` (`client_id`),
  KEY `idx_status` (`status`),
  KEY `idx_started_at` (`started_at`),
  CONSTRAINT `fk_synclog_client` 
    FOREIGN KEY (`client_id`) 
    REFERENCES `sftp_sync_clients` (`id`) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Log de sincronizaciones de clientes';


-- -----------------------------------------------------
-- DATOS DE EJEMPLO (OPCIONAL - COMENTAR SI NO SE NECESITA)
-- -----------------------------------------------------

-- Cliente de ejemplo: LB Sistemas Connec
INSERT INTO `sftp_sync_clients` (`client_name`, `client_code`, `email`, `active`, `notes`) 
VALUES 
  ('LB Sistemas Connec', 'LBSISTEMAS', 'contacto@lbsistemas.com', 1, 'Cliente principal de prueba'),
  ('Cliente Demo', 'DEMO', 'demo@example.com', 0, 'Cliente de pruebas - INACTIVO');

-- Credenciales de ejemplo para LB Sistemas (AJUSTAR CON DATOS REALES)
INSERT INTO `client_sftp_credentials` 
  (`client_id`, `sftp_host`, `sftp_port`, `sftp_user`, `sftp_password`, `sftp_directory`, `active`) 
SELECT 
  id, 
  'ftp.lbsistemas.com', 
  22, 
  'usuario_sftp', 
  'password_temporal', 
  '/home/pricefile/', 
  1
FROM `sftp_sync_clients` 
WHERE `client_code` = 'LBSISTEMAS';


-- =====================================================
-- VERIFICACIÓN
-- =====================================================
SELECT 'Tablas creadas exitosamente' as status;
SELECT TABLE_NAME, TABLE_ROWS 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'bdicentralserver_sc' 
  AND TABLE_NAME IN ('sftp_sync_clients', 'client_sftp_credentials', 'client_sync_log');