-- ============================================
-- MIGRACIÓN: Sistema Multitenant
-- Fecha: 2026-01-26
-- Descripción: Agregar estructura multitenant con tenant_id a todas las tablas
-- ============================================

-- ============================================
-- PASO 1: CREAR TABLA DE TENANTS
-- ============================================

CREATE TABLE IF NOT EXISTS tenants (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    codigo VARCHAR(50) NOT NULL UNIQUE,
    configuracion JSON DEFAULT NULL,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_activo (activo),
    INDEX idx_codigo (codigo)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

-- Insertar tenant por defecto para datos existentes
INSERT INTO tenants (id, nombre, codigo, activo) 
VALUES (1, 'Tenant Principal', 'default', 1)
ON DUPLICATE KEY UPDATE nombre=nombre;

-- ============================================
-- PASO 2: AGREGAR tenant_id A TABLAS PRINCIPALES
-- ============================================

-- Tabla clientes
ALTER TABLE clientes 
ADD COLUMN tenant_id INT UNSIGNED NOT NULL DEFAULT 1 AFTER Id_cliente,
ADD INDEX idx_tenant_cliente (tenant_id, Id_cliente),
ADD FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE RESTRICT;

-- Tabla boletas
ALTER TABLE boletas 
ADD COLUMN tenant_id INT UNSIGNED NOT NULL DEFAULT 1 AFTER id_boleta,
ADD INDEX idx_tenant_boleta (tenant_id, id_boleta),
ADD FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE RESTRICT;

-- Tabla boletas_snapshot
ALTER TABLE boletas_snapshot 
ADD COLUMN tenant_id INT UNSIGNED NOT NULL DEFAULT 1 AFTER id_snapshot,
ADD INDEX idx_tenant_snapshot (tenant_id, id_snapshot),
ADD FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE RESTRICT;

-- Tabla boletas_detalle
ALTER TABLE boletas_detalle 
ADD COLUMN tenant_id INT UNSIGNED NOT NULL DEFAULT 1 AFTER id_detalle,
ADD INDEX idx_tenant_detalle (tenant_id, id_detalle),
ADD FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE RESTRICT;

-- Tabla deudas
ALTER TABLE deudas 
ADD COLUMN tenant_id INT UNSIGNED NOT NULL DEFAULT 1 AFTER id_deuda,
ADD INDEX idx_tenant_deuda (tenant_id, id_deuda),
ADD FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE RESTRICT;

-- Tabla lecturas_clie_mensual
ALTER TABLE lecturas_clie_mensual 
ADD COLUMN tenant_id INT UNSIGNED NOT NULL DEFAULT 1 AFTER id_lectura,
ADD INDEX idx_tenant_lectura (tenant_id, id_lectura),
ADD FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE RESTRICT;

-- Tabla pagos_socios
ALTER TABLE pagos_socios 
ADD COLUMN tenant_id INT UNSIGNED NOT NULL DEFAULT 1 AFTER id_pago,
ADD INDEX idx_tenant_pago (tenant_id, id_pago),
ADD FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE RESTRICT;

-- Tabla subsidios
ALTER TABLE subsidios 
ADD COLUMN tenant_id INT UNSIGNED NOT NULL DEFAULT 1 AFTER id_subsidio,
ADD INDEX idx_tenant_subsidio (tenant_id, id_subsidio),
ADD FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE RESTRICT;

-- Tabla repactaciones
ALTER TABLE repactaciones 
ADD COLUMN tenant_id INT UNSIGNED NOT NULL DEFAULT 1 AFTER id_repactacion,
ADD INDEX idx_tenant_repactacion (tenant_id, id_repactacion),
ADD FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE RESTRICT;

-- Tabla reposiciones
ALTER TABLE reposiciones 
ADD COLUMN tenant_id INT UNSIGNED NOT NULL DEFAULT 1 AFTER Id_reposicion,
ADD INDEX idx_tenant_reposicion (tenant_id, Id_reposicion),
ADD FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE RESTRICT;

-- Tabla multas_socio
ALTER TABLE multas_socio 
ADD COLUMN tenant_id INT UNSIGNED NOT NULL DEFAULT 1 AFTER Id_multas,
ADD INDEX idx_tenant_multas (tenant_id, Id_multas),
ADD FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE RESTRICT;

-- Tabla honorarios
ALTER TABLE honorarios 
ADD COLUMN tenant_id INT UNSIGNED NOT NULL DEFAULT 1 AFTER id_honorario,
ADD INDEX idx_tenant_honorario (tenant_id, id_honorario),
ADD FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE RESTRICT;

-- Tabla giros_depositos
ALTER TABLE giros_depositos 
ADD COLUMN tenant_id INT UNSIGNED NOT NULL DEFAULT 1 AFTER id_giro,
ADD INDEX idx_tenant_giro (tenant_id, id_giro),
ADD FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE RESTRICT;

-- Tabla resumenes
ALTER TABLE resumenes 
ADD COLUMN tenant_id INT UNSIGNED NOT NULL DEFAULT 1 AFTER id_resumen,
ADD INDEX idx_tenant_resumen (tenant_id, id_resumen),
ADD FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE RESTRICT;

-- ============================================
-- PASO 3: AGREGAR tenant_id A TABLAS DE CONFIGURACIÓN
-- ============================================

-- Tabla datos_apr (configuración por tenant)
ALTER TABLE datos_apr 
ADD COLUMN tenant_id INT UNSIGNED NOT NULL DEFAULT 1 AFTER id_datos_apr,
ADD INDEX idx_tenant_datos (tenant_id),
ADD FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE RESTRICT;

-- Tabla sectores (pueden ser compartidos o por tenant)
ALTER TABLE sectores 
ADD COLUMN tenant_id INT UNSIGNED NOT NULL DEFAULT 1 AFTER id_sector,
ADD INDEX idx_tenant_sector (tenant_id, id_sector),
ADD FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE RESTRICT;

-- ============================================
-- PASO 4: ACTUALIZAR PROCEDIMIENTOS ALMACENADOS
-- ============================================
-- NOTA: Los procedimientos almacenados necesitarán ser actualizados
-- para incluir tenant_id en sus queries. Esto se hará en una migración separada
-- o manualmente según la complejidad de cada procedimiento.

-- ============================================
-- VERIFICACIÓN
-- ============================================
-- Ejecutar para verificar:
-- SELECT COUNT(*) as total_tenants FROM tenants;
-- SELECT tenant_id, COUNT(*) as registros FROM clientes GROUP BY tenant_id;
-- SHOW INDEX FROM clientes WHERE Key_name LIKE 'idx_tenant%';