-- Script de migración manual CXP Integration
-- Ejecutar dentro del contenedor MySQL

-- =====================================================
-- PROVEEDORES: Campos fiscales
-- =====================================================
-- impuesto_id
SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'proveedores' AND column_name = 'impuesto_id');
SET @query := IF(@exists = 0, 'ALTER TABLE proveedores ADD COLUMN impuesto_id SMALLINT UNSIGNED NULL', 'SELECT 1');
PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- retencion_isr_id
SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'proveedores' AND column_name = 'retencion_isr_id');
SET @query := IF(@exists = 0, 'ALTER TABLE proveedores ADD COLUMN retencion_isr_id SMALLINT UNSIGNED NULL', 'SELECT 1');
PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- retencion_iva_id
SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'proveedores' AND column_name = 'retencion_iva_id');
SET @query := IF(@exists = 0, 'ALTER TABLE proveedores ADD COLUMN retencion_iva_id SMALLINT UNSIGNED NULL', 'SELECT 1');
PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- aplica_idp
SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'proveedores' AND column_name = 'aplica_idp');
SET @query := IF(@exists = 0, 'ALTER TABLE proveedores ADD COLUMN aplica_idp TINYINT(1) NOT NULL DEFAULT 0', 'SELECT 1');
PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- =====================================================
-- RETENCIONES: ISR Escalonado
-- =====================================================
SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'retenciones' AND column_name = 'es_escalonado');
SET @query := IF(@exists = 0, 'ALTER TABLE retenciones ADD COLUMN es_escalonado TINYINT(1) NOT NULL DEFAULT 0', 'SELECT 1');
PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'retenciones' AND column_name = 'limite_escala1');
SET @query := IF(@exists = 0, 'ALTER TABLE retenciones ADD COLUMN limite_escala1 DECIMAL(18,2) NULL', 'SELECT 1');
PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'retenciones' AND column_name = 'porcentaje_escala2');
SET @query := IF(@exists = 0, 'ALTER TABLE retenciones ADD COLUMN porcentaje_escala2 DECIMAL(6,3) NULL', 'SELECT 1');
PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- =====================================================
-- TIPOS_DOCUMENTO: Factura Especial
-- =====================================================
SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'tipos_documento' AND column_name = 'es_factura_especial');
SET @query := IF(@exists = 0, 'ALTER TABLE tipos_documento ADD COLUMN es_factura_especial TINYINT(1) NOT NULL DEFAULT 0', 'SELECT 1');
PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'tipos_documento' AND column_name = 'retiene_iva');
SET @query := IF(@exists = 0, 'ALTER TABLE tipos_documento ADD COLUMN retiene_iva TINYINT(1) NOT NULL DEFAULT 0', 'SELECT 1');
PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'tipos_documento' AND column_name = 'retiene_isr');
SET @query := IF(@exists = 0, 'ALTER TABLE tipos_documento ADD COLUMN retiene_isr TINYINT(1) NOT NULL DEFAULT 0', 'SELECT 1');
PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'tipos_documento' AND column_name = 'porcentaje_ret_iva');
SET @query := IF(@exists = 0, 'ALTER TABLE tipos_documento ADD COLUMN porcentaje_ret_iva DECIMAL(5,2) NULL', 'SELECT 1');
PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'tipos_documento' AND column_name = 'porcentaje_ret_isr');
SET @query := IF(@exists = 0, 'ALTER TABLE tipos_documento ADD COLUMN porcentaje_ret_isr DECIMAL(5,2) NULL', 'SELECT 1');
PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- =====================================================
-- DOCUMENTOS_CXP: Documento padre + IDP
-- =====================================================
SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'documentos_cxp' AND column_name = 'documento_padre_id');
SET @query := IF(@exists = 0, 'ALTER TABLE documentos_cxp ADD COLUMN documento_padre_id BIGINT UNSIGNED NULL', 'SELECT 1');
PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'documentos_cxp' AND column_name = 'idp_total');
SET @query := IF(@exists = 0, 'ALTER TABLE documentos_cxp ADD COLUMN idp_total DECIMAL(18,2) NOT NULL DEFAULT 0.00', 'SELECT 1');
PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- =====================================================
-- DOCUMENTOS_CXP_DET: IDP
-- =====================================================
SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'documentos_cxp_det' AND column_name = 'idp_galones');
SET @query := IF(@exists = 0, 'ALTER TABLE documentos_cxp_det ADD COLUMN idp_galones DECIMAL(18,4) NULL', 'SELECT 1');
PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'documentos_cxp_det' AND column_name = 'idp_monto');
SET @query := IF(@exists = 0, 'ALTER TABLE documentos_cxp_det ADD COLUMN idp_monto DECIMAL(18,2) NOT NULL DEFAULT 0.00', 'SELECT 1');
PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- =====================================================
-- Insertar retención ISR escalonada
-- =====================================================
INSERT IGNORE INTO retenciones (cliente_id, nombre, tipo, porcentaje, base_sobre, es_escalonado, limite_escala1, porcentaje_escala2, activo)
SELECT id, 'ISR Servicios (5%/7% escalonado)', 1, 5.000, 1, 1, 30000.00, 7.000, 1
FROM clientes c
WHERE NOT EXISTS (SELECT 1 FROM retenciones r WHERE r.cliente_id = c.id AND r.nombre LIKE '%escalonado%');

SELECT 'Migration completed successfully!' as result;
