import csv
import io
import uuid
import datetime
import zipfile

import openpyxl
from django.apps import apps
from django.db import models
from django.db.models import Count, Q
from django.http import HttpResponse
from drf_yasg import openapi
from drf_yasg.utils import swagger_auto_schema
from rest_framework import status, viewsets
from rest_framework.decorators import api_view, permission_classes
from rest_framework.permissions import IsAuthenticated
from rest_framework.response import Response
from rest_framework.views import APIView

from api.customs.models import Cove, EDocument, Partida, Pedimento
from api.organization.models import Organizacion
from api.record.models import Document
from core.permissions import (
    get_org_context,
    require_permission,
    user_has_permission,
)
from .serializers import ExportModelSerializer

def export_model_to_csv(request, model_name, fields, module='datastage', filters=None):
    model = apps.get_model(module, model_name)
    queryset = model.objects.filter(**(filters or {})).values(*fields)
    response = HttpResponse(content_type='text/csv')
    response['Content-Disposition'] = f'attachment; filename="{model_name}.csv"'
    writer = csv.DictWriter(response, fieldnames=fields)
    writer.writeheader()
    for row in queryset:
        writer.writerow(row)
    return response


def export_model_to_excel(request, model_name, fields, module='datastage', filters=None):
    model = apps.get_model(module, model_name)
    queryset = model.objects.filter(**(filters or {})).values(*fields)
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.append(fields)
    for row in queryset:
        # Convertir cada valor a string para asegurar compatibilidad con Excel
        row_values = []
        for field in fields:
            value = row[field]
            # Si es UUID u otro objeto, convertirlo a string
            if hasattr(value, '__str__'):
                value = str(value)
            row_values.append(value)
        ws.append(row_values)
    output = io.BytesIO()
    wb.save(output)
    output.seek(0)
    response = HttpResponse(output.read(
    ), content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = f'attachment; filename="{model_name}.xlsx"'
    return response

# class ControlPedimentoView(APIView):
#     my_tags = ['Control-Pedimento']
#     permission_classes = [IsAuthenticated & (IsSameOrganization | IsSameOrganizationAndAdmin | IsSameOrganizationDeveloper | IsSuperUser)]

#     @swagger_auto_schema(request_body=ExportModelSerializer, responses={200: 'Archivo generado (Excel o CSV)'})
#     def post(self, request, *args, **kwargs):
#         """
#         Endpoint específico para exportación de DataStage con soporte múltiple
#         """
#         # Verificar si es modo múltiple
#         modo = request.data.get('modo', 'simple')
        
#         if modo == 'multiple':
#             return self.handle_multiple_export(request)
#         else:
#             return self.handle_simple_export(request)
        


class ExportDataStageView(APIView):
    my_tags = ['Reportes-DataStage']

    def get_permissions(self):
        if self.request.method == 'GET':
            return [IsAuthenticated(), require_permission('reportes.view')()]
        return [IsAuthenticated(), require_permission('reportes.export')()]

    # Constantes para partición
    # MAX_RECORDS_PER_FILE = 100  # Límite seguro por archivo
    MAX_RECORDS_PER_FILE = 120000  # Límite seguro por archivo

    def safe_excel_value(self, value):
        """
        Convierte cualquier valor a un formato seguro para Excel
        """
        if value is None:
            return ''
        elif isinstance(value, (uuid.UUID,)):
            return str(value)
        elif hasattr(value, 'uuid'):
            return str(value.uuid)
        elif hasattr(value, 'id'):
            return str(value.id)
        elif isinstance(value, (datetime.datetime, datetime.date)):
            return value.isoformat()
        elif isinstance(value, (dict, list)):
            return str(value)
        else:
            return str(value)

    def get(self, request, *args, **kwargs):
        """Retorna RFCs distintos de Registro501 para la organización activa del usuario."""
        try:
            Registro501 = apps.get_model('datastage', 'Registro501')

            org = get_org_context(request.user)
            if not org:
                return Response({'error': 'Sin organización activa'}, status=status.HTTP_403_FORBIDDEN)
            qs = Registro501.objects.filter(organizacion=org)

            rfcs = (
                qs.exclude(rfc__isnull=True)
                  .exclude(rfc='')
                  .values_list('rfc', flat=True)
                  .distinct()
                  .order_by('rfc')
            )
            return Response({'rfcs': list(rfcs)})
        except LookupError:
            return Response({'rfcs': []})

    @swagger_auto_schema(request_body=ExportModelSerializer, responses={200: 'Archivo generado (Excel o CSV)'})
    def post(self, request, *args, **kwargs):
        """
        Endpoint específico para exportación de DataStage con soporte múltiple
        """
        # Verificar si es modo múltiple
        modo = request.data.get('modo', 'simple')
        
        if modo == 'multiple':
            return self.handle_multiple_export(request)
        else:
            return self.handle_simple_export(request)
    
    def _resolve_org_filter(self, global_filters, user):
        """
        Devuelve los global_filters asegurando que siempre haya una organización.
        La org se obtiene de active_organization (superuser) o del campo organizacion (usuario normal).
        Retorna (filters_dict, error_response_or_None).
        """
        filters = dict(global_filters or {})
        if not filters.get('organizacion'):
            org = get_org_context(user)
            if not org:
                return None, Response(
                    {'error': 'Sin organización activa'},
                    status=status.HTTP_403_FORBIDDEN,
                )
            filters['organizacion'] = str(org.id)
        return filters, None

    def handle_simple_export(self, request):
        """Maneja exportación simple de DataStage (un solo modelo)"""
        model_name = request.data.get('model')
        fields = request.data.get('fields')
        global_filters = request.data.get('globalFilters', {})
        export_type = request.data.get('format', 'csv')
        module = 'datastage'

        if not model_name or not fields:
            return Response({'error': 'model and fields are required'}, status=status.HTTP_400_BAD_REQUEST)

        global_filters, err = self._resolve_org_filter(global_filters, request.user)
        if err:
            return err

        try:
            model = apps.get_model(module, model_name)
            filters = self.apply_global_filters_to_model(global_filters, model, request.user)
            
            queryset = model.objects.filter(**filters).values(*fields)
            total_records = queryset.count()
            
            if export_type == 'excel':
                # Verificar si necesita partición
                if total_records > self.MAX_RECORDS_PER_FILE:
                    return self.export_single_model_partitioned(request, model_name, fields, filters, total_records)
                else:
                    return export_model_to_excel(request, model_name, fields, module, filters)
            else:
                if total_records > self.MAX_RECORDS_PER_FILE:
                    return self.export_single_model_csv_partitioned(request, model_name, fields, filters, total_records)
                else:
                    return export_model_to_csv(request, model_name, fields, module, filters)
                
        except LookupError:
            return Response({'error': f'Model {model_name} not found'}, status=status.HTTP_404_NOT_FOUND)

    def handle_multiple_export(self, request):
        """Maneja exportación múltiple de DataStage (varios modelos)"""
        models_data = request.data.get('models', [])
        export_type = request.data.get('format', 'csv')
        global_filters = request.data.get('globalFilters', {})

        if not models_data:
            return Response({'error': 'models are required for multiple export'}, status=status.HTTP_400_BAD_REQUEST)

        global_filters, err = self._resolve_org_filter(global_filters, request.user)
        if err:
            return err

        related_keys = self.get_related_keys_from_filters(global_filters, models_data, request.user)
        
        if export_type == 'excel':
            return self.export_datastage_multiple_partitioned_excel_agrupados(request, models_data, global_filters, related_keys)
        else:
            return self.export_datastage_multiple_to_csv_combined(request, models_data, global_filters, related_keys)

    def estimate_total_records(self, models_data, global_filters, related_keys, user):
        """Estima el total de registros para todos los modelos"""
        total = 0
        for model_data in models_data:
            model_name = model_data.get('model')
            try:
                model = apps.get_model('datastage', model_name)
                filters = self.apply_related_filters(global_filters, model, related_keys, user)
                total += model.objects.filter(**filters).count()
            except:
                continue
        return total

    def export_datastage_multiple_to_excel(self, request, models_data, global_filters, related_keys):
        """Exporta múltiples modelos de DataStage con filtrado relacionado (múltiples hojas)"""
        wb = openpyxl.Workbook()
        wb.remove(wb.active)

        for model_data in models_data:
            model_name = model_data.get('model')
            fields = model_data.get('fields', [])
            
            if not model_name or not fields:
                continue

            try:
                model = apps.get_model('datastage', model_name)
                
                # 🔥 APLICAR FILTROS RELACIONADOS
                filters = self.apply_related_filters(global_filters, model, related_keys, request.user)
                
                # Si hay filtros, aplicarlos; si no, obtener todos los registros
                if filters:
                    queryset = model.objects.filter(**filters).values(*fields)
                else:
                    queryset = model.objects.none()  # No obtener nada si no hay filtros

                # Si no hay registros, saltar este modelo
                if queryset.count() == 0:
                    continue
                
                # Crear hoja (limitar nombre a 31 caracteres)
                sheet_name = model_name[:31]
                ws = wb.create_sheet(title=sheet_name)
                
                # Escribir encabezados
                ws.append(fields)
                
                # Escribir datos
                for row in queryset:
                    row_values = []
                    for field in fields:
                        value = row[field]
                        # 🔥 USAR safe_excel_value para convertir valores
                        row_values.append(self.safe_excel_value(value))
                    ws.append(row_values)
                    
            except LookupError:
                continue

        # Si no se crearon hojas, crear una vacía
        if len(wb.sheetnames) == 0:
            ws = wb.create_sheet(title="Sin datos")
            ws.append(["No se encontraron datos para los modelos especificados"])

        output = io.BytesIO()
        wb.save(output)
        output.seek(0)
        
        response = HttpResponse(
            output.read(),
            content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
        response['Content-Disposition'] = 'attachment; filename="datastage_related_report.xlsx"'
        return response
    
    def export_datastage_multiple_partitioned_excel_agrupados(self, request, models_data, global_filters, related_keys):
        """Exporta múltiples modelos de DataStage agrupados en la misma hoja de Excel, con particionado por límite de registros"""
        try:
            from api.organization.models import Organizacion
            org_mapping = {str(org.id): org.nombre for org in Organizacion.objects.all()}

            # 1. Recopilar todos los datos FUERA del contexto ZIP
            all_models_data = {}
            model_field_mappings = {}

            for model_data in models_data:
                model_name = model_data.get('model')
                fields = model_data.get('fields', [])

                if not model_name or not fields:
                    continue

                normalized_fields = []
                for f in fields:
                    try:
                        key = f.strip() if isinstance(f, str) else f
                    except Exception:
                        key = f

                    if isinstance(key, str) and key.lower() == 'organizacion':
                        if 'organizacion_id' not in normalized_fields:
                            normalized_fields.append('organizacion_id')
                    else:
                        if key not in normalized_fields:
                            normalized_fields.append(key)

                fields = normalized_fields

                required_fields = ['seccion_aduanera', 'patente', 'pedimento']
                for field in required_fields:
                    if field not in fields:
                        fields.append(field)

                if 'organizacion_id' not in fields and 'organizacion_id' in [f.name for f in apps.get_model('datastage', model_name)._meta.get_fields()]:
                    fields.append('organizacion_id')

                try:
                    model = apps.get_model('datastage', model_name)
                    filters = self.apply_related_filters(global_filters, model, related_keys, request.user)

                    if filters:
                        queryset = model.objects.filter(**filters).values(*fields)
                    else:
                        queryset = model.objects.none()

                    if queryset.count() == 0:
                        continue

                    relation_fields = [fn for fn in ['seccion_aduanera', 'patente', 'pedimento'] if fn in fields]
                    if not relation_fields:
                        relation_fields = ['datastage_id'] if 'datastage_id' in fields else [fields[0]]

                    if model_name not in model_field_mappings:
                        model_field_mappings[model_name] = fields

                    for record in queryset:
                        key_parts = [str(record[rf]) for rf in relation_fields if rf in record and record[rf] is not None]
                        if not key_parts:
                            import hashlib
                            key = hashlib.md5(str(sorted(record.items())).encode()).hexdigest()[:10]
                        else:
                            key = "_".join(key_parts)

                        processed_record = {}
                        for field_name, value in record.items():
                            if field_name == 'organizacion_id' and value:
                                org_id_str = str(value)
                                if org_id_str in org_mapping:
                                    processed_value = org_mapping[org_id_str]
                                else:
                                    try:
                                        org = Organizacion.objects.filter(id=value).first()
                                        processed_value = org.nombre if org else org_id_str
                                        org_mapping[org_id_str] = processed_value
                                    except Exception:
                                        processed_value = org_id_str
                            else:
                                processed_value = value

                            if field_name in relation_fields:
                                prefixed_field_name = field_name
                            else:
                                prefixed_field_name = f"{model_name}_{field_name}"

                            if field_name == 'organizacion_id':
                                prefixed_field_name = prefixed_field_name.replace('organizacion_id', 'organizacion_nombre')

                            processed_record[prefixed_field_name] = self.safe_excel_value(processed_value)

                        if key not in all_models_data:
                            all_models_data[key] = {'relation_fields': {}, 'model_records': {}}

                        for rel_field in relation_fields:
                            if rel_field in record:
                                all_models_data[key]['relation_fields'][rel_field] = record[rel_field]

                        if model_name not in all_models_data[key]['model_records']:
                            all_models_data[key]['model_records'][model_name] = []

                        all_models_data[key]['model_records'][model_name].append(processed_record)

                except LookupError:
                    continue

            # 2. Sin datos → Excel vacío (no JSON 404 que rompe la descarga en el frontend)
            if not all_models_data:
                wb = openpyxl.Workbook()
                ws = wb.active
                ws.title = "Sin datos"
                ws.append(["No se encontraron datos para los filtros especificados"])
                output = io.BytesIO()
                wb.save(output)
                output.seek(0)
                resp = HttpResponse(
                    output.read(),
                    content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
                )
                resp['Content-Disposition'] = 'attachment; filename="datastage_sin_datos.xlsx"'
                return resp

            # 3. Construir filas combinadas — repetir el último registro en lugar de dejar vacíos
            combined_rows = []
            for key, data in all_models_data.items():
                relation_fields_data = data['relation_fields']
                model_records = data['model_records']

                max_records_per_key = max((len(recs) for recs in model_records.values()), default=1)

                for i in range(max_records_per_key):
                    row_data = {}

                    for rel_field, rel_value in relation_fields_data.items():
                        row_data[rel_field] = self.safe_excel_value(rel_value)

                    for model_name, records in model_records.items():
                        # Usar posición i o el último registro disponible
                        record = records[i] if i < len(records) else records[-1]
                        for field_name, value in record.items():
                            row_data[field_name] = value

                    combined_rows.append(row_data)

            # 4. Encabezados ordenados
            all_fields_set = set()
            for row in combined_rows:
                all_fields_set.update(row.keys())

            all_fields = []
            for rel_field in ['seccion_aduanera', 'patente', 'pedimento']:
                if rel_field in all_fields_set:
                    all_fields.append(rel_field)
                    all_fields_set.discard(rel_field)

            org_fields = sorted(f for f in all_fields_set if 'organizacion' in f.lower())
            for org_field in org_fields:
                all_fields.append(org_field)
                all_fields_set.discard(org_field)

            all_fields.extend(sorted(all_fields_set))

            # 5. Filas de título y fecha de generación
            now_str = datetime.datetime.now().strftime('%d/%m/%Y %H:%M:%S')
            title_row = ["Reporte Datastage"]
            date_row = [f"Generado: {now_str}"]

            def _write_sheet(ws, sheet_name, page_rows):
                ws.title = sheet_name[:31]
                ws.append(title_row)
                ws.append(date_row)
                ws.append([])
                ws.append(all_fields)
                for row_data in page_rows:
                    ws.append([row_data.get(field, '') for field in all_fields])
                for column in ws.columns:
                    max_length = 0
                    col_letter = column[0].column_letter
                    for cell in column:
                        try:
                            if len(str(cell.value)) > max_length:
                                max_length = len(str(cell.value))
                        except Exception:
                            pass
                    ws.column_dimensions[col_letter].width = min(max_length + 2, 50)

            # 6. Excel directo si cabe en un archivo; ZIP solo si se necesita particionar
            from django.core.paginator import Paginator
            paginator = Paginator(combined_rows, self.MAX_RECORDS_PER_FILE)

            if paginator.num_pages == 1:
                wb = openpyxl.Workbook()
                _write_sheet(wb.active, "Datastage", paginator.page(1).object_list)
                output = io.BytesIO()
                wb.save(output)
                output.seek(0)
                resp = HttpResponse(
                    output.read(),
                    content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
                )
                resp['Content-Disposition'] = 'attachment; filename="datastage_reporte.xlsx"'
                return resp

            zip_buffer = io.BytesIO()
            with zipfile.ZipFile(zip_buffer, 'w', zipfile.ZIP_DEFLATED) as zip_file:
                for page_num in paginator.page_range:
                    page = paginator.page(page_num)
                    current_wb = openpyxl.Workbook()
                    _write_sheet(current_wb.active, f"Datastage_p{page_num}", page.object_list)
                    part_buffer = io.BytesIO()
                    current_wb.save(part_buffer)
                    part_buffer.seek(0)
                    zip_file.writestr(f"datastage_part{page_num}.xlsx", part_buffer.getvalue())

            zip_buffer.seek(0)
            resp = HttpResponse(zip_buffer.read(), content_type='application/zip')
            resp['Content-Disposition'] = 'attachment; filename="datastage_combinado.zip"'
            return resp

        except Exception as e:
            import traceback
            import logging
            logging.getLogger(__name__).error("Error en exportación combinada: %s", traceback.format_exc())
            return Response({'error': f'Error en exportación combinada: {str(e)}'}, status=status.HTTP_500_INTERNAL_SERVER_ERROR)


    def export_datastage_multiple_partitioned_excel_test_3(self, request, models_data, global_filters, related_keys):
        """Exporta múltiples modelos de DataStage agrupados en la misma hoja de Excel, con particionado por límite de registros"""
        try:
            zip_buffer = io.BytesIO()

            with zipfile.ZipFile(zip_buffer, 'w', zipfile.ZIP_DEFLATED) as zip_file:
                
                # 1. Recopilar todos los datos de cada modelo
                all_models_data = {}  # Ahora será una lista por clave
                model_field_mappings = {}

                for model_data in models_data:
                    model_name = model_data.get('model')
                    fields = model_data.get('fields', [])

                    if not model_name or not fields:
                        continue
                    
                    # Asegurar que tenemos los campos de relación
                    required_fields = ['seccion_aduanera', 'patente', 'pedimento']
                    for field in required_fields:
                        if field not in fields:
                            fields.append(field)

                    try:
                        model = apps.get_model('datastage', model_name)
                        filters = self.apply_related_filters(global_filters, model, related_keys, request.user)

                        if filters:
                            queryset = model.objects.filter(**filters).values(*fields)
                        else:
                            queryset = model.objects.none()

                        total_records = queryset.count()

                        if total_records == 0:
                            continue
                        
                        # Determinar campos de relación disponibles en este modelo
                        relation_fields = []
                        for field_name in ['seccion_aduanera', 'patente', 'pedimento']:
                            if field_name in fields:
                                relation_fields.append(field_name)

                        if not relation_fields:
                            # Si no hay campos de relación, usar un identificador único
                            relation_fields = ['datastage_id'] if 'datastage_id' in fields else [fields[0]]

                        # Guardar mapeo de campos para este modelo
                        if model_name not in model_field_mappings:
                            model_field_mappings[model_name] = fields

                        # Procesar cada registro
                        for record in queryset:
                            # Crear clave de relación
                            key_parts = []
                            for rel_field in relation_fields:
                                if rel_field in record and record[rel_field] is not None:
                                    key_parts.append(str(record[rel_field]))

                            if not key_parts:
                                # Si no hay campos de relación, usar un hash del registro
                                import hashlib
                                record_str = str(sorted(record.items()))
                                key = hashlib.md5(record_str.encode()).hexdigest()[:10]
                            else:
                                key = "_".join(key_parts)

                            # Agregar prefijo del modelo a los campos para evitar colisiones
                            prefixed_fields = {}
                            for field_name, value in record.items():
                                # Solo agregar prefijo si no es un campo de relación
                                if field_name in relation_fields:
                                    prefixed_field_name = field_name
                                else:
                                    prefixed_field_name = f"{model_name}_{field_name}"
                                prefixed_fields[prefixed_field_name] = self.safe_excel_value(value)

                            # 🔥 CORRECIÓN: Ahora almacenamos una LISTA de registros por clave
                            if key not in all_models_data:
                                all_models_data[key] = {
                                    'relation_fields': {},  # Campos de relación compartidos
                                    'model_records': {}     # Diccionario de listas por modelo
                                }

                            # Guardar campos de relación (solo una vez, ya que son los mismos)
                            for rel_field in relation_fields:
                                if rel_field in record:
                                    all_models_data[key]['relation_fields'][rel_field] = record[rel_field]

                            # 🔥 GUARDAR COMO LISTA: Crear lista si no existe
                            if model_name not in all_models_data[key]['model_records']:
                                all_models_data[key]['model_records'][model_name] = []

                            # Agregar este registro a la lista del modelo
                            all_models_data[key]['model_records'][model_name].append(prefixed_fields)

                    except LookupError:
                        continue
                    
                # Si no hay datos, retornar error
                if not all_models_data:
                    return Response({'error': 'No se encontraron datos para exportar'}, status=status.HTTP_404_NOT_FOUND)

                # 2. Crear estructura de filas combinadas
                # Ahora necesitamos expandir las filas cuando hay múltiples registros con la misma clave
                combined_rows = []

                for key, data in all_models_data.items():
                    relation_fields = data['relation_fields']
                    model_records = data['model_records']

                    # 🔥 NUEVO: Calcular cuántas filas necesitamos para esta clave
                    # Encontrar el modelo con más registros para esta clave
                    max_records_per_key = 1
                    for model_name, records in model_records.items():
                        if len(records) > max_records_per_key:
                            max_records_per_key = len(records)

                    # 🔗 CREAR UNA FILA POR CADA COMBINACIÓN
                    for i in range(max_records_per_key):
                        row_data = {}

                        # Campos de relación (mismos para todas las filas con esta clave)
                        for rel_field, rel_value in relation_fields.items():
                            row_data[rel_field] = self.safe_excel_value(rel_value)

                        # Datos de cada modelo
                        for model_name, records in model_records.items():
                            # Si hay un registro en esta posición i
                            if i < len(records):
                                record = records[i]
                                for field_name, value in record.items():
                                    row_data[field_name] = value
                            else:
                                # Si no hay más registros para este modelo, poner campos vacíos
                                for field_name in model_field_mappings.get(model_name, []):
                                    if field_name in ['seccion_aduanera', 'patente', 'pedimento']:
                                        # Los campos de relación ya están llenados
                                        continue
                                    prefixed_field_name = f"{model_name}_{field_name}"
                                    row_data[prefixed_field_name] = ''

                        combined_rows.append(row_data)

                # 3. Determinar todos los campos únicos para los encabezados
                all_fields_set = set()

                # Campos de relación primero
                common_relation_fields = ['seccion_aduanera', 'patente', 'pedimento']

                # Agregar todos los campos de todas las filas
                for row in combined_rows:
                    all_fields_set.update(row.keys())

                # Ordenar campos: relación primero, luego alfabéticamente
                all_fields = []
                for rel_field in common_relation_fields:
                    if rel_field in all_fields_set:
                        all_fields.append(rel_field)
                        all_fields_set.remove(rel_field)

                # Agregar el resto de campos ordenados alfabéticamente
                all_fields.extend(sorted(all_fields_set))

                total_records = len(combined_rows)

                # 4. Manejar particionado
                from django.core.paginator import Paginator
                paginator = Paginator(combined_rows, self.MAX_RECORDS_PER_FILE)

                for page_num in paginator.page_range:
                    page = paginator.page(page_num)

                    # Crear nuevo workbook para cada partición
                    current_wb = openpyxl.Workbook()
                    current_ws = current_wb.active

                    # Nombre de hoja limitado a 31 caracteres
                    sheet_name = f"Datastage_p{page_num}"
                    if len(sheet_name) > 31:
                        sheet_name = sheet_name[:31]
                    current_ws.title = sheet_name

                    # Escribir encabezados
                    current_ws.append(all_fields)

                    # Escribir datos de esta página
                    for row_data in page.object_list:
                        row_values = [row_data.get(field, '') for field in all_fields]
                        current_ws.append(row_values)

                    # Autoajustar anchos de columna
                    for column in current_ws.columns:
                        max_length = 0
                        column_letter = column[0].column_letter

                        for cell in column:
                            try:
                                if len(str(cell.value)) > max_length:
                                    max_length = len(str(cell.value))
                            except:
                                pass
                            
                        adjusted_width = min(max_length + 2, 50)
                        current_ws.column_dimensions[column_letter].width = adjusted_width

                    # Guardar archivo en ZIP
                    part_buffer = io.BytesIO()
                    current_wb.save(part_buffer)
                    part_buffer.seek(0)
                    zip_file.writestr(f"datastage_part{page_num}.xlsx", part_buffer.getvalue())


            zip_buffer.seek(0)

            response = HttpResponse(zip_buffer.read(), content_type='application/zip')
            response['Content-Disposition'] = 'attachment; filename="datastage_combinado.zip"'
            return response

        except Exception as e:
            import traceback
            import logging
            logging.getLogger(__name__).error("Error en exportación combinada: %s", traceback.format_exc())
            return Response({'error': f'Error en exportación combinada: {str(e)}'}, status=status.HTTP_500_INTERNAL_SERVER_ERROR)


    def export_datastage_multiple_partitioned_excel_test_2(self, request, models_data, global_filters, related_keys):
        """Exporta múltiples modelos de DataStage agrupados en la misma hoja de Excel, con particionado por límite de registros"""
        try:
            zip_buffer = io.BytesIO()

            with zipfile.ZipFile(zip_buffer, 'w', zipfile.ZIP_DEFLATED) as zip_file:

                # 1. Recopilar todos los datos de cada modelo por clave (aduana, patente, pedimento)
                all_models_data = {}
                model_field_mappings = {}

                for model_data in models_data:
                    model_name = model_data.get('model')
                    fields = model_data.get('fields', [])

                    if not model_name or not fields:
                        continue
                    
                    required_fields = ['seccion_aduanera', 'patente', 'pedimento']

                    for field in required_fields:
                        if field not in fields:
                            fields.append(field)

                    try:
                        model = apps.get_model('datastage', model_name)
                        filters = self.apply_related_filters(global_filters, model, related_keys, request.user)

                        if filters:
                            queryset = model.objects.filter(**filters).values(*fields)
                        else:
                            queryset = model.objects.none()

                        total_records = queryset.count()

                        if total_records == 0:
                            continue
                        
                        # Determinar campos de relación disponibles en este modelo
                        relation_fields = []
                        for field_name in ['seccion_aduanera', 'patente', 'pedimento']:
                            if field_name in fields:
                                relation_fields.append(field_name)

                        if not relation_fields:
                            # Si no hay campos de relación, usar un identificador único
                            relation_fields = ['datastage_id'] if 'datastage_id' in fields else [fields[0]]

                        # Procesar cada registro
                        for record in queryset:
                            # Crear clave de relación
                            key_parts = []
                            for rel_field in relation_fields:
                                if rel_field in record and record[rel_field] is not None:
                                    key_parts.append(str(record[rel_field]))

                            if not key_parts:
                                # Si no hay campos de relación, usar un hash del registro
                                import hashlib
                                record_str = str(sorted(record.items()))
                                key = hashlib.md5(record_str.encode()).hexdigest()[:10]
                            else:
                                key = "_".join(key_parts)

                            # Agregar prefijo del modelo a los campos para evitar colisiones
                            prefixed_fields = {}
                            for field_name, value in record.items():
                                prefixed_field_name = f"{model_name}_{field_name}"
                                prefixed_fields[prefixed_field_name] = self.safe_excel_value(value)
                                # Registrar mapeo de campos
                                if model_name not in model_field_mappings:
                                    model_field_mappings[model_name] = []
                                if field_name not in model_field_mappings[model_name]:
                                    model_field_mappings[model_name].append(field_name)

                            # Guardar datos bajo la clave
                            if key not in all_models_data:
                                all_models_data[key] = {
                                    'relation_fields': {},
                                    'model_data': {}
                                }

                            # Guardar campos de relación
                            for rel_field in relation_fields:
                                if rel_field in record:
                                    all_models_data[key]['relation_fields'][rel_field] = record[rel_field]

                            # Guardar datos del modelo
                            all_models_data[key]['model_data'][model_name] = prefixed_fields

                    except LookupError:
                        continue
                    
                # Si no hay datos, retornar error
                if not all_models_data:
                    return Response({'error': 'No se encontraron datos para exportar'}, status=status.HTTP_404_NOT_FOUND)

                # 2. Determinar todos los campos únicos que necesitaremos
                all_fields_set = set()

                # Primero agregar campos de relación comunes
                common_relation_fields = ['seccion_aduanera', 'patente', 'pedimento']

                for key, data in all_models_data.items():
                    # Agregar campos de relación
                    for rel_field in common_relation_fields:
                        if rel_field in data['relation_fields']:
                            all_fields_set.add(rel_field)

                    # Agregar campos de todos los modelos para esta clave
                    for model_name, model_fields in data['model_data'].items():
                        for field_name in model_fields.keys():
                            all_fields_set.add(field_name)

                # Convertir a lista ordenada (campos de relación primero)
                all_fields = []
                for rel_field in common_relation_fields:
                    if rel_field in all_fields_set:
                        all_fields.append(rel_field)
                        all_fields_set.remove(rel_field)

                # Luego agregar el resto de campos ordenados alfabéticamente
                all_fields.extend(sorted(all_fields_set))

                # 3. Crear datos combinados por fila
                combined_rows = []

                for key, data in all_models_data.items():
                    row_data = {}

                    # Campos de relación
                    for rel_field in common_relation_fields:
                        if rel_field in data['relation_fields']:
                            row_data[rel_field] = self.safe_excel_value(data['relation_fields'][rel_field])
                        else:
                            row_data[rel_field] = ''

                    # Datos de cada modelo
                    for model_name, model_fields in data['model_data'].items():
                        for field_name, value in model_fields.items():
                            row_data[field_name] = value

                    # Rellenar campos faltantes con vacío
                    for field in all_fields:
                        if field not in row_data:
                            row_data[field] = ''

                    combined_rows.append(row_data)

                total_records = len(combined_rows)

                # 4. Manejar particionado
                from django.core.paginator import Paginator
                paginator = Paginator(combined_rows, self.MAX_RECORDS_PER_FILE)

                for page_num in paginator.page_range:
                    page = paginator.page(page_num)

                    # Crear nuevo workbook para cada partición
                    current_wb = openpyxl.Workbook()
                    current_ws = current_wb.active

                    # Nombre de hoja limitado a 31 caracteres
                    sheet_name = f"Datastage_p{page_num}"
                    if len(sheet_name) > 31:
                        sheet_name = sheet_name[:31]
                    current_ws.title = sheet_name

                    # Escribir encabezados
                    current_ws.append(all_fields)

                    # Escribir datos de esta página
                    for row_data in page.object_list:
                        row_values = [row_data.get(field, '') for field in all_fields]
                        current_ws.append(row_values)

                    # Autoajustar anchos de columna (opcional)
                    for column in current_ws.columns:
                        max_length = 0
                        column_letter = column[0].column_letter

                        for cell in column:
                            try:
                                if len(str(cell.value)) > max_length:
                                    max_length = len(str(cell.value))
                            except:
                                pass
                            
                        adjusted_width = min(max_length + 2, 50)  # Máximo 50 caracteres
                        current_ws.column_dimensions[column_letter].width = adjusted_width

                    # Guardar archivo en ZIP
                    part_buffer = io.BytesIO()
                    current_wb.save(part_buffer)
                    part_buffer.seek(0)
                    zip_file.writestr(f"datastage_part{page_num}.xlsx", part_buffer.getvalue())

                    # Información de depuración
                    print(f"Creada partición {page_num} con {len(page.object_list)} registros combinados")

            zip_buffer.seek(0)

            response = HttpResponse(zip_buffer.read(), content_type='application/zip')
            response['Content-Disposition'] = 'attachment; filename="datastage_combinado.zip"'
            return response

        except Exception as e:
            import traceback
            import logging
            logging.getLogger(__name__).error("Error en exportación combinada: %s", traceback.format_exc())
            return Response({'error': f'Error en exportación combinada: {str(e)}'}, status=status.HTTP_500_INTERNAL_SERVER_ERROR)


    def export_datastage_multiple_partitioned_excel_test(self, request, models_data, global_filters, related_keys):
        """Exporta múltiples modelos de DataStage agrupados en la misma hoja de Excel, con particionado por límite de registros"""
        try:
            zip_buffer = io.BytesIO()

            with zipfile.ZipFile(zip_buffer, 'w', zipfile.ZIP_DEFLATED) as zip_file:
                file_counter = 1
                current_wb = None
                current_ws = None
                current_record_count = 0
                combined_fields = []  # Almacenar todos los campos únicos
                combined_data = []    # Almacenar todos los datos

                # 1. Primero recopilar todos los campos y datos
                all_models_data = {}

                for model_data in models_data:
                    model_name = model_data.get('model')
                    fields = model_data.get('fields', [])

                    if not model_name or not fields:
                        continue

                    try:
                        model = apps.get_model('datastage', model_name)
                        filters = self.apply_related_filters(global_filters, model, related_keys, request.user)

                        if filters:
                            queryset = model.objects.filter(**filters).values(*fields)
                        else:
                            queryset = model.objects.none()

                        total_records = queryset.count()

                        if total_records == 0:
                            continue
                        
                        # Almacenar los datos de este modelo
                        all_models_data[model_name] = {
                            'fields': fields,
                            'data': list(queryset),
                            'total_records': total_records
                        }

                        # Agregar campos únicos a la lista combinada
                        for field in fields:
                            if field not in combined_fields:
                                combined_fields.append(field)

                    except LookupError:
                        continue
                    
                # Si no hay datos, retornar error
                if not all_models_data:
                    return Response({'error': 'No se encontraron datos para exportar'}, status=status.HTTP_404_NOT_FOUND)

                # 2. Crear estructura de datos combinada
                # Primero, preparar los datos combinados
                for model_name, model_info in all_models_data.items():
                    fields = model_info['fields']
                    data = model_info['data']

                    for record in data:
                        combined_record = {}

                        # Para cada campo en la lista combinada
                        for combined_field in combined_fields:
                            if combined_field in fields:
                                # Si el campo existe en este modelo, usar su valor
                                value = record.get(combined_field)
                                combined_record[combined_field] = self.safe_excel_value(value)
                            else:
                                # Si no existe, poner vacío
                                combined_record[combined_field] = ''

                        # Agregar columna para identificar el modelo origen
                        combined_record['_modelo_origen'] = model_name

                        combined_data.append(combined_record)

                # Agregar campo de modelo origen a la lista de campos si no está ya
                if '_modelo_origen' not in combined_fields:
                    combined_fields.append('_modelo_origen')

                total_combined_records = len(combined_data)

                # 3. Manejar particionado
                from django.core.paginator import Paginator
                paginator = Paginator(combined_data, self.MAX_RECORDS_PER_FILE)

                for page_num in paginator.page_range:
                    page = paginator.page(page_num)

                    # Crear nuevo workbook para cada partición
                    current_wb = openpyxl.Workbook()
                    current_ws = current_wb.active
                    current_ws.title = f"Todos_Modelos_p{page_num}"[:31]

                    # Escribir encabezados
                    current_ws.append(combined_fields)

                    # Escribir datos de esta página
                    for record in page.object_list:
                        row_values = [record.get(field, '') for field in combined_fields]
                        current_ws.append(row_values)

                    # Guardar archivo en ZIP
                    part_buffer = io.BytesIO()
                    current_wb.save(part_buffer)
                    part_buffer.seek(0)
                    zip_file.writestr(f"datastage_combinado_part{page_num}.xlsx", part_buffer.getvalue())


            zip_buffer.seek(0)

            response = HttpResponse(zip_buffer.read(), content_type='application/zip')
            response['Content-Disposition'] = 'attachment; filename="datastage_combinado.zip"'
            return response

        except Exception as e:
            import traceback
            import logging
            logging.getLogger(__name__).error("Error en exportación combinada: %s", traceback.format_exc())
            return Response({'error': f'Error en exportación combinada: {str(e)}'}, status=status.HTTP_500_INTERNAL_SERVER_ERROR)

    def export_datastage_multiple_partitioned_excel(self, request, models_data, global_filters, related_keys):
        """Exporta múltiples modelos de DataStage a múltiples archivos Excel particionados inteligentemente"""
        try:
            zip_buffer = io.BytesIO()
            
            with zipfile.ZipFile(zip_buffer, 'w', zipfile.ZIP_DEFLATED) as zip_file:
                file_counter = 1
                current_wb = None
                current_file_records_count = 0
                MAX_SHEETS_PER_FILE = 10  # Límite de hojas por archivo Excel
                
                for model_data in models_data:
                    model_name = model_data.get('model')
                    fields = model_data.get('fields', [])
                    
                    if not model_name or not fields:
                        continue

                    try:
                        model = apps.get_model('datastage', model_name)
                        filters = self.apply_related_filters(global_filters, model, related_keys, request.user)
                        
                        if filters:
                            queryset = model.objects.filter(**filters).values(*fields)
                        else:
                            queryset = model.objects.none()
                            
                        total_records = queryset.count()
                        
                        if total_records == 0:
                            continue
                        
                        # Si el modelo necesita particionarse (más de MAX_RECORDS_PER_FILE)
                        if total_records > self.MAX_RECORDS_PER_FILE:
                            from django.core.paginator import Paginator
                            paginator = Paginator(queryset, self.MAX_RECORDS_PER_FILE)
                            
                            for page_num in paginator.page_range:
                                page = paginator.page(page_num)
                                
                                # Verificar si necesitamos crear nuevo archivo
                                # 1. Si no hay archivo actual
                                # 2. Si ya tenemos muchas hojas en este archivo
                                # 3. Si este archivo ya está "lleno" (muchos registros)
                                if (current_wb is None or 
                                    len(current_wb.sheetnames) >= MAX_SHEETS_PER_FILE or
                                    current_file_records_count > self.MAX_RECORDS_PER_FILE * 3):  # ~150K registros
                                    
                                    if current_wb is not None:
                                        # Guardar archivo actual en ZIP
                                        part_buffer = io.BytesIO()
                                        current_wb.save(part_buffer)
                                        part_buffer.seek(0)
                                        zip_file.writestr(f"datastage_part{file_counter}.xlsx", part_buffer.getvalue())
                                        file_counter += 1
                                    
                                    # Crear nuevo workbook
                                    current_wb = openpyxl.Workbook()
                                    current_wb.remove(current_wb.active)  # Remover hoja por defecto
                                    current_file_records_count = 0
                                
                                # Crear hoja para esta parte del modelo
                                sheet_name = f"{model_name[:25]}_p{page_num}"
                                ws = current_wb.create_sheet(title=sheet_name[:31])
                                ws.append(fields)
                                
                                # Escribir datos
                                for row in page.object_list:
                                    row_values = [self.safe_excel_value(row[field]) for field in fields]
                                    ws.append(row_values)
                                
                                current_file_records_count += len(page.object_list)
                                
                        else:
                            # Modelo pequeño (≤ MAX_RECORDS_PER_FILE)
                            # Verificar si necesitamos nuevo archivo
                            if (current_wb is None or 
                                len(current_wb.sheetnames) >= MAX_SHEETS_PER_FILE or
                                current_file_records_count + total_records > self.MAX_RECORDS_PER_FILE * 3):
                                
                                if current_wb is not None:
                                    # Guardar archivo actual
                                    part_buffer = io.BytesIO()
                                    current_wb.save(part_buffer)
                                    part_buffer.seek(0)
                                    zip_file.writestr(f"datastage_part{file_counter}.xlsx", part_buffer.getvalue())
                                    file_counter += 1
                                
                                # Crear nuevo workbook
                                current_wb = openpyxl.Workbook()
                                current_wb.remove(current_wb.active)
                                current_file_records_count = 0
                            
                            # Crear hoja para este modelo
                            sheet_name = model_name[:31]
                            ws = current_wb.create_sheet(title=sheet_name)
                            ws.append(fields)
                            
                            # Escribir datos
                            for row in queryset:
                                row_values = [self.safe_excel_value(row[field]) for field in fields]
                                ws.append(row_values)
                            
                            current_file_records_count += total_records
                            
                    except LookupError:
                        continue
                
                # Guardar el último workbook si existe
                if current_wb is not None:
                    part_buffer = io.BytesIO()
                    current_wb.save(part_buffer)
                    part_buffer.seek(0)
                    zip_file.writestr(f"datastage_part{file_counter}.xlsx", part_buffer.getvalue())
            
            zip_buffer.seek(0)
            
            response = HttpResponse(zip_buffer.read(), content_type='application/zip')
            response['Content-Disposition'] = 'attachment; filename="datastage_reports.zip"'
            return response
            
        except Exception as e:
            return Response({'error': f'Error en exportación particionada: {str(e)}'}, status=status.HTTP_500_INTERNAL_SERVER_ERROR)
        
    def export_datastage_multiple_to_csv_combined(self, request, models_data, global_filters, related_keys):
        """Exporta múltiples modelos combinados en un único CSV plano (misma lógica de agrupación que el Excel)."""
        import hashlib
        import logging
        import traceback
        logger = logging.getLogger(__name__)
        try:
            from api.organization.models import Organizacion
            org_mapping = {str(org.id): org.nombre for org in Organizacion.objects.all()}

            all_models_data = {}
            model_field_mappings = {}

            for model_data in models_data:
                model_name = model_data.get('model')
                fields = model_data.get('fields', [])
                if not model_name or not fields:
                    continue

                normalized_fields = []
                for f in fields:
                    key = f.strip() if isinstance(f, str) else f
                    if isinstance(key, str) and key.lower() == 'organizacion':
                        if 'organizacion_id' not in normalized_fields:
                            normalized_fields.append('organizacion_id')
                    else:
                        if key not in normalized_fields:
                            normalized_fields.append(key)
                fields = normalized_fields

                for req_field in ['seccion_aduanera', 'patente', 'pedimento']:
                    if req_field not in fields:
                        fields.append(req_field)

                try:
                    model = apps.get_model('datastage', model_name)
                    model_field_names = [f.name for f in model._meta.get_fields() if hasattr(f, 'name')]
                    if 'organizacion_id' not in fields and 'organizacion_id' in model_field_names:
                        fields.append('organizacion_id')

                    filters = self.apply_related_filters(global_filters, model, related_keys, request.user)
                    queryset = model.objects.filter(**filters).values(*fields) if filters else model.objects.none()
                    if queryset.count() == 0:
                        continue

                    relation_fields = [fn for fn in ['seccion_aduanera', 'patente', 'pedimento'] if fn in fields]
                    if not relation_fields:
                        relation_fields = ['datastage_id'] if 'datastage_id' in fields else [fields[0]]

                    if model_name not in model_field_mappings:
                        model_field_mappings[model_name] = fields

                    for record in queryset:
                        key_parts = [str(record[rf]) for rf in relation_fields if rf in record and record[rf] is not None]
                        key = "_".join(key_parts) if key_parts else hashlib.md5(str(sorted(record.items())).encode()).hexdigest()[:10]

                        processed_record = {}
                        for field_name, value in record.items():
                            if field_name == 'organizacion_id' and value:
                                org_id_str = str(value)
                                processed_value = org_mapping.get(org_id_str, org_id_str)
                            else:
                                processed_value = value

                            if field_name in relation_fields:
                                prefixed = field_name
                            else:
                                prefixed = f"{model_name}_{field_name}"
                            if field_name == 'organizacion_id':
                                prefixed = prefixed.replace('organizacion_id', 'organizacion_nombre')
                            processed_record[prefixed] = self.safe_excel_value(processed_value)

                        if key not in all_models_data:
                            all_models_data[key] = {'relation_fields': {}, 'model_records': {}}
                        for rel_field in relation_fields:
                            if rel_field in record:
                                all_models_data[key]['relation_fields'][rel_field] = record[rel_field]
                        if model_name not in all_models_data[key]['model_records']:
                            all_models_data[key]['model_records'][model_name] = []
                        all_models_data[key]['model_records'][model_name].append(processed_record)

                except LookupError:
                    continue

            # Sin datos → CSV con mensaje, no error HTTP
            if not all_models_data:
                buf = io.StringIO()
                csv.writer(buf).writerow(['No se encontraron datos para los filtros especificados'])
                resp = HttpResponse(buf.getvalue(), content_type='text/csv; charset=utf-8')
                resp['Content-Disposition'] = 'attachment; filename="datastage_sin_datos.csv"'
                return resp

            # Construir filas planas
            combined_rows = []
            for key, data in all_models_data.items():
                relation_fields_data = data['relation_fields']
                model_records = data['model_records']
                max_records = max((len(recs) for recs in model_records.values()), default=1)
                for i in range(max_records):
                    row_data = {}
                    for rel_field, rel_value in relation_fields_data.items():
                        row_data[rel_field] = self.safe_excel_value(rel_value)
                    for mn, records in model_records.items():
                        record = records[i] if i < len(records) else records[-1]
                        for field_name, value in record.items():
                            row_data[field_name] = value
                    combined_rows.append(row_data)

            # Encabezados: campos de relación primero, luego org, luego el resto
            all_fields_set = set()
            for row in combined_rows:
                all_fields_set.update(row.keys())

            all_fields = []
            for rel_field in ['seccion_aduanera', 'patente', 'pedimento']:
                if rel_field in all_fields_set:
                    all_fields.append(rel_field)
                    all_fields_set.discard(rel_field)
            org_fields = sorted(f for f in all_fields_set if 'organizacion' in f.lower())
            for org_field in org_fields:
                all_fields.append(org_field)
                all_fields_set.discard(org_field)
            all_fields.extend(sorted(all_fields_set))

            buf = io.StringIO()
            writer = csv.writer(buf)
            writer.writerow(all_fields)
            for row_data in combined_rows:
                writer.writerow([row_data.get(field, '') for field in all_fields])

            resp = HttpResponse(buf.getvalue(), content_type='text/csv; charset=utf-8')
            resp['Content-Disposition'] = 'attachment; filename="datastage_reporte.csv"'
            return resp

        except Exception as e:
            logger.error("Error en exportación CSV combinada: %s", traceback.format_exc())
            return Response({'error': f'Error en exportación CSV combinada: {str(e)}'}, status=status.HTTP_500_INTERNAL_SERVER_ERROR)

    def export_datastage_multiple_to_csv(self, request, models_data, global_filters, related_keys):
        """Exporta múltiples modelos de DataStage a múltiples archivos CSV en ZIP"""
        zip_buffer = io.BytesIO()
        
        with zipfile.ZipFile(zip_buffer, 'w', zipfile.ZIP_DEFLATED) as zip_file:
            
            for model_data in models_data:
                model_name = model_data.get('model')
                fields = model_data.get('fields', [])
                
                if not model_name or not fields:
                    continue

                try:
                    model = apps.get_model('datastage', model_name)
                    filters = self.apply_related_filters(global_filters, model, related_keys, request.user)
                    
                    queryset = model.objects.filter(**filters).values(*fields)
                    total_records = queryset.count()
                    
                    if total_records == 0:
                        continue
                    
                    csv_buffer = io.StringIO()
                    writer = csv.writer(csv_buffer)
                    writer.writerow(fields)
                    
                    for row in queryset:
                        row_values = [self.safe_excel_value(row[field]) for field in fields]
                        writer.writerow(row_values)
                    
                    # Agregar al ZIP
                    filename = f"{model_name}.csv"
                    zip_file.writestr(filename, csv_buffer.getvalue())
                        
                except LookupError:
                    continue

        zip_buffer.seek(0)
        
        response = HttpResponse(zip_buffer.read(), content_type='application/zip')
        response['Content-Disposition'] = 'attachment; filename="datastage_reports.zip"'
        return response

    def export_datastage_multiple_partitioned_csv(self, request, models_data, global_filters, related_keys):
        """Exporta múltiples modelos de DataStage a múltiples archivos CSV particionados en ZIP"""
        try:
            zip_buffer = io.BytesIO()
            
            with zipfile.ZipFile(zip_buffer, 'w', zipfile.ZIP_DEFLATED) as zip_file:
                
                for model_data in models_data:
                    model_name = model_data.get('model')
                    fields = model_data.get('fields', [])
                    
                    if not model_name or not fields:
                        continue

                    try:
                        model = apps.get_model('datastage', model_name)
                        filters = self.apply_related_filters(global_filters, model, related_keys, request.user)
                        
                        queryset = model.objects.filter(**filters).values(*fields)
                        total_records = queryset.count()
                        
                        if total_records == 0:
                            continue
                        
                        if total_records > self.MAX_RECORDS_PER_FILE:
                            from django.core.paginator import Paginator
                            paginator = Paginator(queryset, self.MAX_RECORDS_PER_FILE)
                            
                            for page_num in paginator.page_range:
                                page = paginator.page(page_num)
                                
                                csv_buffer = io.StringIO()
                                writer = csv.writer(csv_buffer)
                                
                                writer.writerow(fields)
                                
                                for row in page.object_list:
                                    row_values = [self.safe_excel_value(row[field]) for field in fields]
                                    writer.writerow(row_values)
                                
                                # Agregar al ZIP
                                filename = f"{model_name}_part{page_num}.csv"
                                zip_file.writestr(filename, csv_buffer.getvalue())
                                
                        else:
                            # Modelo pequeño, exportar completo
                            csv_buffer = io.StringIO()
                            writer = csv.writer(csv_buffer)
                            
                            # Escribir encabezados
                            writer.writerow(fields)
                            
                            # Escribir datos
                            for row in queryset:
                                row_values = [self.safe_excel_value(row[field]) for field in fields]
                                writer.writerow(row_values)
                            
                            # Agregar al ZIP
                            filename = f"{model_name}.csv"
                            zip_file.writestr(filename, csv_buffer.getvalue())
                            
                    except LookupError as e:
                        continue
                    except Exception as e:
                        continue

            zip_buffer.seek(0)
            
            response = HttpResponse(zip_buffer.read(), content_type='application/zip')
            response['Content-Disposition'] = 'attachment; filename="datastage_reports.zip"'
            return response
            
        except Exception as e:
            return Response({'error': f'Error en exportación CSV particionada: {str(e)}'}, status=status.HTTP_500_INTERNAL_SERVER_ERROR)
    
    def export_single_model_partitioned(self, request, model_name, fields, filters, total_records):
        """Exporta un solo modelo particionado a ZIP"""
        try:
            zip_buffer = io.BytesIO()
            module = 'datastage'
            
            model = apps.get_model(module, model_name)
            queryset = model.objects.filter(**filters).values(*fields)
            
            with zipfile.ZipFile(zip_buffer, 'w', zipfile.ZIP_DEFLATED) as zip_file:
                from django.core.paginator import Paginator
                paginator = Paginator(queryset, self.MAX_RECORDS_PER_FILE)
                
                for page_num in paginator.page_range:
                    page = paginator.page(page_num)
                    
                    # Crear Excel para esta parte
                    wb = openpyxl.Workbook()
                    ws = wb.active
                    ws.title = f"Parte_{page_num}"[:31]
                    ws.append(fields)
                    
                    for row in page.object_list:
                        row_values = [self.safe_excel_value(row[field]) for field in fields]
                        ws.append(row_values)
                    
                    part_buffer = io.BytesIO()
                    wb.save(part_buffer)
                    part_buffer.seek(0)
                    
                    filename = f"{model_name}_part{page_num}.xlsx"
                    zip_file.writestr(filename, part_buffer.getvalue())

            zip_buffer.seek(0)
            zip_content = zip_buffer.getvalue()
            
            response = HttpResponse(zip_content, content_type='application/zip')
            response['Content-Disposition'] = f'attachment; filename="{model_name}_particionado.zip"'
            response['Content-Length'] = len(zip_content)
            
            return response
            
        except Exception as e:
            return Response({'error': f'Error exportando modelo: {str(e)}'}, status=status.HTTP_500_INTERNAL_SERVER_ERROR)
        
    def export_single_model_csv_partitioned(self, request, model_name, fields, filters, total_records):
        """Exporta un solo modelo CSV particionado a ZIP"""
        try:
            zip_buffer = io.BytesIO()
            module = 'datastage'
            
            model = apps.get_model(module, model_name)
            queryset = model.objects.filter(**filters).values(*fields)
            
            with zipfile.ZipFile(zip_buffer, 'w', zipfile.ZIP_DEFLATED) as zip_file:
                from django.core.paginator import Paginator
                paginator = Paginator(queryset, self.MAX_RECORDS_PER_FILE)
                
                for page_num in paginator.page_range:
                    page = paginator.page(page_num)
                    
                    csv_buffer = io.StringIO()
                    writer = csv.writer(csv_buffer)
                    writer.writerow(fields)
                    
                    for row in page.object_list:
                        row_values = [self.safe_excel_value(row[field]) for field in fields]
                        writer.writerow(row_values)
                    
                    # Agregar al ZIP
                    filename = f"{model_name}_part{page_num}.csv"
                    zip_file.writestr(filename, csv_buffer.getvalue())

            zip_buffer.seek(0)
            
            zip_content = zip_buffer.getvalue()
            
            response = HttpResponse(zip_content, content_type='application/zip')
            response['Content-Disposition'] = f'attachment; filename="{model_name}_particionado.zip"'
            response['Content-Length'] = len(zip_content)
            
            return response
            
        except Exception as e:
            return Response({'error': f'Error exportando modelo CSV: {str(e)}'}, status=status.HTTP_500_INTERNAL_SERVER_ERROR)
        
    def get_related_keys_from_filters(self, global_filters, models_data, user):
        """
        Construye el conjunto de (patente, pedimento, datastage_id) que servirá como
        llave de cruce entre modelos.

        Regla clave: si el filtro RFC está activo, solo los modelos que tienen el campo
        'rfc' pueden contribuir a related_keys. Los modelos sin 'rfc' (ej. 505, 506)
        no se usan como semilla — solo se filtrarán más tarde usando las claves ya
        construidas, evitando que contaminen el resultado con pedimentos de otros RFC.
        """
        related_keys = {
            'patentes': set(),
            'pedimentos': set(),
            'datastage_ids': set()
        }

        # Sin filtros significativos → sin cruce
        if not any(v for v in global_filters.values() if v not in [None, '']):
            return {}

        rfc_filter_active = bool(global_filters.get('rfc'))
        date_filter_active = bool(global_filters.get('fecha_pago_desde') or global_filters.get('fecha_pago_hasta'))
        all_records_with_filters = []

        for model_data in models_data:
            model_name = model_data.get('model')
            try:
                model = apps.get_model('datastage', model_name)
                model_field_names = {f.name for f in model._meta.get_fields() if hasattr(f, 'name')}

                # Un modelo puede ser semilla de related_keys SOLO si tiene campos
                # para aplicar TODOS los filtros activos. Un modelo sin 'rfc' no puede
                # ser semilla cuando hay filtro de RFC (contaminaría con pedimentos de
                # otros RFCs). Igual para fecha_pago_real cuando hay filtro de fechas.
                if rfc_filter_active and 'rfc' not in model_field_names:
                    continue
                if date_filter_active and 'fecha_pago_real' not in model_field_names:
                    continue

                filters = self.apply_global_filters_to_model(global_filters, model, user)
                if not filters:
                    continue

                records = model.objects.filter(**filters).values('patente', 'pedimento', 'datastage_id')
                all_records_with_filters.extend(list(records))

            except LookupError:
                continue

        if not all_records_with_filters:
            return {'patentes': set(), 'pedimentos': set(), 'datastage_ids': set()}

        for record in all_records_with_filters:
            if record.get('patente'):
                related_keys['patentes'].add(record['patente'])
            if record.get('pedimento'):
                related_keys['pedimentos'].add(record['pedimento'])
            if record.get('datastage_id'):
                related_keys['datastage_ids'].add(record['datastage_id'])

        return {k: list(v) for k, v in related_keys.items() if v}
    
    def apply_global_filters_to_model(self, global_filters, model, user):
        """
        Aplica filtros globales - VERSIÓN CORREGIDA CON UUID
        """
        
        filters = {}
        model_fields = [f.name for f in model._meta.get_fields()]
        
        # ORGANIZACIÓN - Manejar como UUID
        org_value = global_filters.get('organizacion')
        if org_value and org_value != '' and 'organizacion' in model_fields:
            field = model._meta.get_field('organizacion')
            
            if hasattr(field, 'related_model'):  # Es ForeignKey
                # Convertir string a UUID
                try:
                    import uuid
                    org_uuid = uuid.UUID(org_value)
                    filters['organizacion_id'] = org_uuid
                except Exception as e:
                    # Fallback: dejar como string (puede no funcionar)
                    filters['organizacion_id'] = org_value
            else:  # Es CharField
                filters['organizacion'] = org_value
        
        # RFC - Manejar normalmente
        rfc_value = global_filters.get('rfc')
        if rfc_value and rfc_value != '' and 'rfc' in model_fields:
            filters['rfc'] = rfc_value
        
        # PATENTE
        if global_filters.get('patente'):
            filters['patente'] = global_filters['patente']
        
        # PEDIMENTO
        if global_filters.get('pedimento'):
            filters['pedimento'] = global_filters['pedimento']
        
        # FECHAS
        if 'fecha_pago_real' in model_fields:
            if global_filters.get('fecha_pago_desde'):
                filters['fecha_pago_real__gte'] = global_filters['fecha_pago_desde']
            
            if global_filters.get('fecha_pago_hasta'):
                filters['fecha_pago_real__lte'] = global_filters['fecha_pago_hasta']
        
        return filters
    
    def apply_related_filters(self, global_filters, model, related_keys, user):
        filters = {}
        model_fields = [f.name for f in model._meta.get_fields()]
        
        # 1. Organización — convertir a UUID igual que apply_global_filters_to_model
        if 'organizacion' in model_fields and global_filters.get('organizacion'):
            org_value = global_filters['organizacion']
            try:
                field = model._meta.get_field('organizacion')
                if hasattr(field, 'related_model'):
                    filters['organizacion_id'] = uuid.UUID(org_value)
                else:
                    filters['organizacion'] = org_value
            except Exception:
                filters['organizacion_id'] = org_value
        
        # 2. RFC (¡ESTO ES LO QUE FALTA!)
        if 'rfc' in model_fields and global_filters.get('rfc'):
            filters['rfc'] = global_filters['rfc']
        
        # 3. Fechas (SIEMPRE se aplican)
        if 'fecha_pago_real' in model_fields:
            if global_filters.get('fecha_pago_desde'):
                filters['fecha_pago_real__gte'] = global_filters['fecha_pago_desde']
            
            if global_filters.get('fecha_pago_hasta'):
                filters['fecha_pago_real__lte'] = global_filters['fecha_pago_hasta']
        
        # 🔥 SEGUNDO: Si hay related_keys, AÑADIRLAS a los filtros existentes
        if any(related_keys.values()):
            
            # Añadir patentes si existen
            if related_keys.get('patentes') and 'patente' in model_fields:
                filters['patente__in'] = related_keys['patentes']
            
            # Añadir pedimentos si existen
            if related_keys.get('pedimentos') and 'pedimento' in model_fields:
                filters['pedimento__in'] = related_keys['pedimentos']
            
            # Añadir datastage_ids si existen
            if related_keys.get('datastage_ids') and 'datastage_id' in model_fields:
                filters['datastage_id__in'] = related_keys['datastage_ids']
        
        else:
            # Solo patente y pedimento específicos (no listas)
            if 'patente' in model_fields and global_filters.get('patente'):
                filters['patente'] = global_filters['patente']
            
            if 'pedimento' in model_fields and global_filters.get('pedimento'):
                filters['pedimento'] = global_filters['pedimento']
        
        return filters

    def estimate_excel_file_size(self, num_records, num_columns):
        """Estima tamaño aproximado del archivo Excel"""
        # Estimación aproximada: 100 bytes por celda
        return num_records * num_columns * 100
    
    def export_with_size_control(self, request, models_data, global_filters, related_keys):
        """Versión con control de tamaño de archivo"""
        try:
            zip_buffer = io.BytesIO()
            
            with zipfile.ZipFile(zip_buffer, 'w', zipfile.ZIP_DEFLATED) as zip_file:
                file_counter = 1
                current_wb = None
                current_file_size_estimate = 0
                MAX_FILE_SIZE_ESTIMATE = 50 * 1024 * 1024  # 50MB estimado
                
                for model_data in models_data:
                    model_name = model_data.get('model')
                    fields = model_data.get('fields', [])
                    
                    if not model_name or not fields:
                        continue
                    
                    try:
                        model = apps.get_model('datastage', model_name)
                        filters = self.apply_related_filters(global_filters, model, related_keys, request.user)
                        
                        if filters:
                            queryset = model.objects.filter(**filters).values(*fields)
                        else:
                            queryset = model.objects.none()
                            
                        total_records = queryset.count()
                        
                        if total_records == 0:
                            continue
                        
                        # Calcular tamaño estimado para este modelo
                        model_size_estimate = self.estimate_excel_file_size(total_records, len(fields))
                        
                        # Si el modelo es muy grande o no cabe en el archivo actual
                        needs_new_file = (
                            current_wb is None or 
                            current_file_size_estimate + model_size_estimate > MAX_FILE_SIZE_ESTIMATE or
                            (total_records > self.MAX_RECORDS_PER_FILE and current_file_size_estimate > 0)
                        )
                        
                        if needs_new_file and current_wb is not None:
                            # Guardar archivo actual
                            part_buffer = io.BytesIO()
                            current_wb.save(part_buffer)
                            part_buffer.seek(0)
                            zip_file.writestr(f"datastage_part{file_counter}.xlsx", part_buffer.getvalue())
                            file_counter += 1
                            current_wb = None
                            current_file_size_estimate = 0
                        
                        if current_wb is None:
                            current_wb = openpyxl.Workbook()
                            current_wb.remove(current_wb.active)
                        
                        # Manejar modelos que exceden el límite por hoja
                        if total_records > self.MAX_RECORDS_PER_FILE:
                            from django.core.paginator import Paginator
                            paginator = Paginator(queryset, self.MAX_RECORDS_PER_FILE)
                            
                            for page_num in paginator.page_range:
                                page = paginator.page(page_num)
                                
                                # Crear hoja para esta parte
                                sheet_name = f"{model_name[:20]}_p{page_num}"[:31]
                                ws = current_wb.create_sheet(title=sheet_name)
                                ws.append(fields)
                                
                                for row in page.object_list:
                                    row_values = [self.safe_excel_value(row[field]) for field in fields]
                                    ws.append(row_values)
                                
                                # Actualizar tamaño estimado
                                page_size = self.estimate_excel_file_size(len(page.object_list), len(fields))
                                current_file_size_estimate += page_size
                                
                        else:
                            # Modelo pequeño, una hoja
                            sheet_name = model_name[:31]
                            ws = current_wb.create_sheet(title=sheet_name)
                            ws.append(fields)
                            
                            for row in queryset:
                                row_values = [self.safe_excel_value(row[field]) for field in fields]
                                ws.append(row_values)
                            
                            current_file_size_estimate += model_size_estimate
                            
                    except LookupError:
                        continue
                
                # Guardar último archivo si existe
                if current_wb is not None:
                    part_buffer = io.BytesIO()
                    current_wb.save(part_buffer)
                    part_buffer.seek(0)
                    zip_file.writestr(f"datastage_part{file_counter}.xlsx", part_buffer.getvalue())
            
            zip_buffer.seek(0)
            
            response = HttpResponse(zip_buffer.read(), content_type='application/zip')
            response['Content-Disposition'] = 'attachment; filename="datastage_reports.zip"'
            return response
            
        except Exception as e:
            return Response({'error': f'Error: {str(e)}'}, status=status.HTTP_500_INTERNAL_SERVER_ERROR)

class ExportModelView(APIView):
    my_tags = ['Reportes']

    def get_permissions(self):
        if self.request.method == 'GET':
            return [IsAuthenticated(), require_permission('reportes.view')()]
        return [IsAuthenticated(), require_permission('reportes.export')()]

    @swagger_auto_schema(
        manual_parameters=[
            openapi.Parameter('model', openapi.IN_QUERY, description="Nombre del modelo (ejemplo: Registro500)",
                              type=openapi.TYPE_STRING, required=True)
        ],
        responses={200: openapi.Response('Campos disponibles', schema=openapi.Schema(
            type=openapi.TYPE_OBJECT,
            properties={
                'fields': openapi.Schema(type=openapi.TYPE_ARRAY, items=openapi.Items(type=openapi.TYPE_STRING))
            }
        ))}
    )
    def get(self, request, *args, **kwargs):
        """
        Devuelve los campos disponibles para el modelo solicitado.
        Ejemplo: /api/reports/exportmodel/?model=Registro500
        """
        model_name = request.query_params.get('model')
        module = request.query_params.get('module', 'datastage')
        if not model_name:
            return Response({'error': 'model is required'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            model = apps.get_model(module, model_name)
        except LookupError:
            return Response({'error': f'Model {model_name} not found in app {module}'}, status=status.HTTP_404_NOT_FOUND)
        fields = [f.name for f in model._meta.fields]
        return Response({'fields': fields})

    @swagger_auto_schema(
        request_body=ExportModelSerializer,
        responses={200: 'Archivo generado (Excel o CSV)'}
    )
    def post(self, request, *args, **kwargs):
        model_name = request.data.get('model')
        fields = request.data.get('fields')
        filters = request.data.get('filters', {})
        org = get_org_context(request.user)
        filters['organizacion__id'] = org.id if org else None
        export_type = request.data.get('type', 'csv')
        module = request.data.get('module', 'datastage')

        if not model_name or not fields:
            return Response({'error': 'model and fields are required'}, status=status.HTTP_400_BAD_REQUEST)

        if export_type == 'excel':
            return export_model_to_excel(request, model_name, fields, module, filters)
        else:
            return export_model_to_csv(request, model_name, fields, module, filters)

# Resumen general para dashboard


@api_view(['GET'])
@permission_classes([IsAuthenticated, require_permission('reportes.view')])
def dashboard_summary(request):
    filters = {}
    user = request.user

    pedimento_app = request.query_params.get('pedimento_app')
    aduana = request.query_params.get('aduana')
    patente = request.query_params.get('patente')
    regimen = request.query_params.get('regimen')
    agente_aduanal = request.query_params.get('agente_aduanal')
    tipo_operacion = request.query_params.get('tipo_operacion')
    fecha_pago_gte = request.query_params.get('fecha_pago__gte')
    fecha_pago_lte = request.query_params.get('fecha_pago__lte')
    contribuyente__rfc = request.query_params.get('contribuyente__rfc')

    org = get_org_context(user)
    if not org:
        return Response({'error': 'Sin organización activa.'}, status=status.HTTP_403_FORBIDDEN)
    filters['organizacion_id'] = org.id

    # Importador: filtrar solo por sus RFC asignados
    if user.is_importador:
        rfcs = list(user.rfc.values_list('rfc', flat=True))
        if rfcs:
            filters['contribuyente__rfc__in'] = rfcs

    if pedimento_app:
        filters['pedimento_app'] = pedimento_app
    if aduana:
        filters['aduana'] = aduana
    if patente:
        filters['patente'] = patente
    if regimen:
        filters['regimen'] = regimen
    if agente_aduanal:
        filters['agente_aduanal'] = agente_aduanal
    if tipo_operacion:
        filters['tipo_operacion__tipo'] = tipo_operacion
    if fecha_pago_gte:
        filters['fecha_pago__gte'] = fecha_pago_gte
    if fecha_pago_lte:
        filters['fecha_pago__lte'] = fecha_pago_lte
    if contribuyente__rfc:
        filters['contribuyente__rfc'] = contribuyente__rfc
    # Filtrar pedimentos
    pedimentos_qs = Pedimento.objects.filter(**filters)
    pedimentos_total = pedimentos_qs.count()
    pedimentos_completos = pedimentos_qs.filter(existe_expediente=True).count()
    pedimentos_pendientes = pedimentos_total - pedimentos_completos

    # Usar los IDs de pedimentos filtrados para los demás modelos
    pedimento_ids = list(pedimentos_qs.values_list('id', flat=True))

    coves_total = Cove.objects.filter(pedimento_id__in=pedimento_ids).count()
    coves_procesados = Cove.objects.filter(
        pedimento_id__in=pedimento_ids, cove_descargado=True).count()
    acuse_coves_procesados = Cove.objects.filter(
        pedimento_id__in=pedimento_ids, acuse_cove_descargado=True).count()
    acuse_coves_pendientes = coves_total - acuse_coves_procesados
    coves_pendientes = coves_total - coves_procesados

    edocs_total = EDocument.objects.filter(
        pedimento_id__in=pedimento_ids).count()
    edocs_descargados = EDocument.objects.filter(
        pedimento_id__in=pedimento_ids, edocument_descargado=True).count()
    acuse_descargados = EDocument.objects.filter(
        pedimento_id__in=pedimento_ids, acuse_descargado=True).count()
    edocs_pendientes = edocs_total - edocs_descargados
    acuses_pendientes = edocs_total - acuse_descargados

    remesas_total = Document.objects.filter(
        document_type__id=3, pedimento_id__in=pedimento_ids).count()
    documentos_descargados = Document.objects.filter(
        pedimento_id__in=pedimento_ids).count()
    partidas_total = Partida.objects.filter(
        pedimento_id__in=pedimento_ids).count()
    partidas_descargadas = Partida.objects.filter(
        pedimento_id__in=pedimento_ids, descargado=True).count()
    partidas_pendientes = partidas_total - partidas_descargadas
    # Indicadores de cumplimiento
    cumplimiento_pedimentos = (
        pedimentos_completos / pedimentos_total * 100) if pedimentos_total else 0
    cumplimiento_acuse_coves = (
        acuse_coves_procesados / coves_total * 100) if coves_total else 0
    cumplimiento_coves = (
        coves_procesados / coves_total * 100) if coves_total else 0
    cumplimiento_edocs = (edocs_descargados /
                          edocs_total * 100) if edocs_total else 0
    cumplimiento_acuses = (acuse_descargados /
                           edocs_total * 100) if edocs_total else 0
    cumplimiento_partidas = (partidas_descargadas /
                             partidas_total * 100) if partidas_total else 0

    # Calcular cumplimiento total (promedio de todos los indicadores)
    indicadores = [
        cumplimiento_pedimentos,
        cumplimiento_coves,
        cumplimiento_acuse_coves,
        cumplimiento_edocs,
        cumplimiento_acuses,
        cumplimiento_partidas
    ]
    cumplimiento_total = sum(indicadores) / len(indicadores) if indicadores else 0

    return Response({
        "cumplimiento_total": round(cumplimiento_total, 2),
        "pedimentos": {
            "total": pedimentos_total,
            "completos": pedimentos_completos,
            "pendientes": pedimentos_pendientes,
            "cumplimiento": round(cumplimiento_pedimentos, 2)
        },
        "coves": {
            "total": coves_total,
            "coves_procesados": coves_procesados,
            "coves_pendientes": coves_pendientes,
            "coves_cumplimiento": round(cumplimiento_coves, 2),
            
        },
        "acuse_coves": {
            "total": coves_total,
            "acuse_coves_procesados": acuse_coves_procesados,
            "acuse_coves_pendientes": acuse_coves_pendientes,
            "acuse_coves_cumplimiento": round(cumplimiento_acuse_coves, 2)
        },
        "edocuments": {
            "total": edocs_total,
            "edocs_descargados": edocs_descargados,
            "edocs_pendientes": edocs_pendientes,
            "edocs_cumplimiento": round(cumplimiento_edocs, 2),    
        },
        "acuses":{
            "total": edocs_total,
            "acuse_descargados": acuse_descargados,
            "acuses_pendientes": acuses_pendientes,
            "acuses_cumplimiento": round(cumplimiento_acuses, 2)
        },
        "remesas": {
            "total": remesas_total
        },
        "documentos": {
            "descargados": documentos_descargados
        },
        "partidas": {
            "total": partidas_total,
            "partidas_descargadas": partidas_descargadas,
            "partidas_pendientes": partidas_pendientes,
            "cumplimiento": round(cumplimiento_partidas, 2)
        }
    })