<?php

namespace App\Services;

use App\Models\ExcelUpload;
use App\Models\ExcelUploadLine;
use App\Models\ExcelUploadLog;
use App\Models\ExcelUploadType;
use App\Repositories\PeriodRepository;
use App\Services\ExcelProcessors\CodesTokuProcessor;
use App\Services\ExcelProcessors\DebtsProcessor;
use App\Services\ExcelProcessors\ParentsProcessor;
use App\Services\ExcelProcessors\StudentsProcessor;
use Exception;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Facades\Validator;
use PhpOffice\PhpSpreadsheet\IOFactory;

class ExcelUploadService
{
    /**
     * Listar uploads
     */
    public function list(Request $request)
    {
        $query = ExcelUpload::with(['uploadedBy:id,name,email', 'uploadType:code,name,icon,color_class'])
            ->latest('uploaded_at');

        // Filtros
        if ($request->has('type')) {
            $query->where('type', $request->type);
        }

        if ($request->has('status')) {
            $query->where('status', $request->status);
        }

        if ($request->has('date_from')) {
            $query->where('uploaded_at', '>=', $request->date_from);
        }

        if ($request->has('date_to')) {
            $query->where('uploaded_at', '<=', $request->date_to);
        }

        if ($request->has('search')) {
            $search = $request->search;
            $query->where(function ($q) use ($search) {
                $q->where('file_name', 'like', "%{$search}%")
                    ->orWhere('original_name', 'like', "%{$search}%");
            });
        }

        $perPage = $request->get('per_page', 15);
        $uploads = $query->paginate($perPage);

        return [
            'uploads' => $uploads->items(),
            'pagination' => [
                'total' => $uploads->total(),
                'per_page' => $uploads->perPage(),
                'current_page' => $uploads->currentPage(),
                'last_page' => $uploads->lastPage(),
                'from' => $uploads->firstItem(),
                'to' => $uploads->lastItem(),
            ],
        ];
    }

    /**
     * Mostrar detalle de upload
     */
    public function show($id)
    {
        return ExcelUpload::with([
            'uploadedBy:id,name,email',
            'uploadType:code,name,icon,color_class',
            'lines' => function ($query) {
                $query->orderBy('line_number');
            }
        ])->findOrFail($id);
    }

    /**
     * Crear nuevo upload
     */
    public function store(Request $request)
    {
        $file = $request->file('file');
        $type = $request->input('type');
        $userId = Auth::id();
        Log::info(['$userId' => $userId]);
        // Crear el upload
        $upload = $this->createUpload($file, $type, $userId);

        // Procesar el archivo
        $this->processUpload($upload);

        return $upload->load(['uploadedBy:id,name,email', 'uploadType:code,name,icon,color_class']);
    }

    /**
     * Crear un nuevo upload
     */
    public function createUpload($file, string $type, int $userId): ExcelUpload
    {
        $uploadType = ExcelUploadType::where('code', $type)->firstOrFail();

        $fileName = uniqid('upload_') . '_' . time() . '.' . $file->getClientOriginalExtension();
        $filePath = "uploads/excel/{$type}/" . $fileName;

        Storage::put($filePath, file_get_contents($file));

        $upload = ExcelUpload::create([
            'type' => $type,
            'file_name' => $fileName,
            'file_path' => $filePath,
            'original_name' => $file->getClientOriginalName(),
            'file_size' => $file->getSize(),
            'status' => 'PENDING',
            'uploaded_by' => $userId,
            'uploaded_at' => now(),
        ]);

        ExcelUploadLog::logCreated($upload->id, $userId, [
            'original_name' => $file->getClientOriginalName(),
            'size' => $file->getSize(),
        ]);

        return $upload;
    }

    /**
     * Procesar el archivo Excel
     */
    public function processUpload(ExcelUpload $upload): void
    {
        DB::beginTransaction();
        ini_set("memory_limit", -1);
        set_time_limit(0);
        try {
            $upload->markAsProcessing();

            ExcelUploadLog::logProcessingStarted(
                $upload->id,
                $upload->uploaded_by,
                ['started_at' => now()->toDateTimeString()]
            );

            $spreadsheet = IOFactory::load($upload->getFullFilePath());
            $worksheet = $spreadsheet->getActiveSheet();
            $rows = $worksheet->toArray();

            $headers = array_shift($rows);
            $headers = array_map(fn($h) => strUpperSinTildes(($h)), $headers);
            // Limpiar caracteres especiales de headers (¿, ?, !, etc.)
            $headers = array_map(fn($h) => preg_replace('/[^A-ZÑ0-9_]/u', '', $h), $headers);

            $uploadType = $upload->uploadType;
            $processor = $this->getProcessor($uploadType->code);

            $lineNumber = 1;
            $successCount = 0;
            $errorCount = 0;
            $warningCount = 0;

            foreach ($rows as $row) {
                $lineNumber++;

                if (empty(array_filter($row))) {
                    continue;
                }

                $data = array_combine($headers, $row);

                Log::info(['$data' => $data]);

                $result = $processor->processLine($data, $lineNumber);

                ExcelUploadLine::create([
                    'upload_id' => $upload->id,
                    'line_number' => $lineNumber,
                    'status' => $result['status'],
                    'data' => $data,
                    'error_message' => $result['error'] ?? null,
                    'warning_message' => $result['warning'] ?? null,
                    'editable' => $result['status'] === 'ERROR',
                ]);

                if ($result['status'] === 'SUCCESS') {
                    $successCount++;
                } elseif ($result['status'] === 'ERROR') {
                    $errorCount++;
                } elseif ($result['status'] === 'WARNING') {
                    $warningCount++;
                }
            }

            // Guardar orden de columnas del procesador en metadata
            $columnOrder = $processor->getColumnOrder();
            $metadata = $upload->metadata ?? [];
            if ($columnOrder) {
                $metadata['column_order'] = $columnOrder;
            }

            $upload->update([
                'total_rows' => $lineNumber - 1,
                'success_rows' => $successCount,
                'error_rows' => $errorCount,
                'warning_rows' => $warningCount,
                'status' => 'PENDING_CONFIRMATION',
                'processed_at' => now(),
                'metadata' => $metadata,
            ]);

            ExcelUploadLog::logProcessingCompleted(
                $upload->id,
                $upload->uploaded_by,
                [
                    'total' => $lineNumber - 1,
                    'success' => $successCount,
                    'errors' => $errorCount,
                    'warnings' => $warningCount,
                ]
            );

            DB::commit();
        } catch (\Exception $e) {
            DB::rollBack();
            $upload->markAsError($e->getMessage());
            ExcelUploadLog::logError($upload->id, $upload->uploaded_by, 'Error al procesar el archivo: ' . $e->getMessage());
            throw $e;
        }
    }

    private function getProcessor($code)

    {
        switch ($code) {
            case 'PARENTS':
                return new ParentsProcessor();
            case 'STUDENTS':
                return new StudentsProcessor();
            case 'DEBTS':
                return new DebtsProcessor();
            case 'CODE_TOKU':
                return new CodesTokuProcessor();
            default:
                throw new Exception('Tipo de carga no soportada', 500);
        }
    }
    /**
     * Actualizar una línea
     */
    public function updateLine($uploadId, $lineNumber, Request $request)
    {
        $validator = Validator::make($request->all(), [
            'data' => 'required|array',
        ]);

        if ($validator->fails()) {
            throw new \Exception($validator->errors()->first(), 422);
        }

        DB::beginTransaction();

        try {
            $upload = ExcelUpload::findOrFail($uploadId);

            if (!$upload->isPendingConfirmation()) {
                throw new \Exception('Solo se pueden editar líneas de cargas pendientes de confirmación', 400);
            }

            $line = ExcelUploadLine::where('upload_id', $upload->id)
                ->where('line_number', $lineNumber)
                ->firstOrFail();

            if (!$line->editable) {
                throw new \Exception('Esta línea no es editable', 400);
            }

            $newData = $request->input('data');
            $userId = Auth::id();

            $uploadType = $upload->uploadType;
            $processor = $this->getProcessor($uploadType->code);

            $result = $processor->processLine($newData, $lineNumber);

            $line->updateData($newData, $userId);
            $line->changeStatus($result['status'], $result['error'] ?? $result['warning'] ?? null);

            $upload->updateCounters();

            ExcelUploadLog::logLineEdited($upload->id, $userId, $lineNumber, ['new_status' => $result['status']]);

            DB::commit();

            return $line->fresh();
        } catch (\Exception $e) {
            DB::rollBack();
            throw $e;
        }
    }

    /**
     * Confirmar upload
     */
    public function confirm($id)
    {
        DB::beginTransaction();
        ini_set("memory_limit", -1);
        set_time_limit(0);

        try {
            $upload = ExcelUpload::with('lines')->findOrFail($id);

            if (!$upload->canBeConfirmed()) {
                throw new \Exception('La carga no puede ser confirmada. Verifica que no tenga errores.', 400);
            }

            $userId = Auth::id();
            $uploadType = $upload->uploadType;
            $processor = $this->getProcessor($uploadType->code);

            $created = 0;
            $updated = 0;

            // === Solo líneas exitosas ===
            $successLines = $upload->lines()->success()->get();

            $period = null;
            if (in_array($uploadType->code, ['DEBTS', 'CODE_TOKU'])) {
                $periodRepository = new PeriodRepository();
                $period = $periodRepository->getActive();
                if (empty($period)) {
                    throw new Exception("Para confirmar carga primero habilite un periodo.");
                }
            }

            // === CASO ESPECIAL: Agrupar por RUT_CLIENTE para CODE_TOKU ===
            if ($uploadType->code === 'CODE_TOKU') {

                // Agrupar todas las líneas por RUT_CLIENTE
                $groupedData = [];
                foreach ($successLines as $line) {
                    $rut = $line->data['RUT_CLIENTE'] ?? null;
                    if (!$rut) continue;

                    if (!isset($groupedData[$rut])) {
                        $groupedData[$rut] = [];
                    }

                    // Guardamos las líneas del mismo cliente
                    $groupedData[$rut][] = $line->data;
                }

                // Procesamos cada grupo de cliente
                foreach ($groupedData as $rutCliente => $groupLines) {
                    // Le pasamos todas las líneas del cliente agrupadas
                    $result = $processor->createOrUpdateRecord($groupLines, $period);

                    if (isset($result['action']) && $result['action'] === 'updated') {
                        $updated++;
                    } else {
                        $created++;
                    }
                }
            } elseif ($uploadType->code === 'STUDENTS') {
                // === CASO STUDENTS: Fase 1 crear/actualizar, Fase 2 contratos agrupados por apoderado ===

                // Fase 1: Crear/actualizar todos los estudiantes (sin contratos)
                $studentsNeedingContract = [];

                foreach ($successLines as $line) {
                    $result = $processor->createOrUpdateRecord($line->data, $period);

                    if (isset($result['action']) && $result['action'] === 'updated') {
                        $updated++;
                    } else {
                        $created++;
                    }

                    if (isset($result['model'])) {
                        $line->linkToRecord($result['model']);

                        // Recopilar estudiantes que necesitan contrato
                        $student = $result['model'];
                        $periodoYear = $processor->getValue($line->data, 'PERIODO');
                        $periodForStudent = \App\Models\Period::where('period_year', $periodoYear)
                            ->where('status', 1)->where('finished', 0)->first();

                        if ($periodForStudent) {
                            $canceledStatusId = \App\Models\StatusContract::where('code', 'canceled')->value('id');
                            $studentHasContract = \App\Models\ContractDetail::where('student_id', $student->id)
                                ->whereHas('contract', function ($q) use ($periodForStudent, $canceledStatusId) {
                                    $q->where('period_id', $periodForStudent->id);
                                    if ($canceledStatusId) {
                                        $q->where('status_contract_id', '!=', $canceledStatusId);
                                    }
                                })->exists();

                            if (!$studentHasContract) {
                                // Leer PAGA_CUOTA_INCORPORACION desde el Excel (SI por defecto)
                                $pagaCuotaValue = $processor->getValue($line->data, 'PAGA_CUOTA_INCORPORACION');
                                $paysIncorporationFee = is_null($pagaCuotaValue) ? true : (strUpper($pagaCuotaValue) === 'SI');

                                $studentsNeedingContract[] = [
                                    'student_id' => $student->id,
                                    'parent_id'  => $student->financial_parent_id,
                                    'course_id'  => $student->course_id,
                                    'period_id'  => $periodForStudent->id,
                                    'pays_incorporation_fee' => $paysIncorporationFee,
                                ];
                            }
                        }
                    }
                }

                // Fase 2: Agrupar por apoderado y crear contratos (como v1 API)
                // Sin transacciones anidadas — usa la transacción de confirm() directamente
                if (!empty($studentsNeedingContract)) {
                    $contractRepo = app(\App\Repositories\ContractRepository::class);
                    $debtService = app(\App\Services\DebtCalculationService::class);
                    $grouped = collect($studentsNeedingContract)->groupBy('parent_id');

                    foreach ($grouped as $parentId => $studentGroup) {
                        $periodId = $studentGroup->first()['period_id'];
                        $parent = \App\Models\Parents::find($parentId);
                        if (!$parent) continue;

                        // Mapa student_id → pays_incorporation_fee (del Excel)
                        $incorporationFeeMap = [];
                        foreach ($studentGroup as $sg) {
                            $incorporationFeeMap[$sg['student_id']] = $sg['pays_incorporation_fee'] ?? true;
                        }

                        // Ordenar por course.ordering DESC (curso más alto primero = sibling_order 1)
                        $orderedStudents = \App\Models\Student::whereIn('students.id', $studentGroup->pluck('student_id'))
                            ->join('courses', 'students.course_id', '=', 'courses.id')
                            ->orderByDesc('courses.ordering')
                            ->select('students.*')
                            ->get();

                        // Separar PG/JI del resto
                        $pgCourseIds = [\App\Services\ApiInboundService::PG_COURSE_ID, \App\Services\ApiInboundService::JI_COURSE_ID];

                        // Contratos por tipo de matrícula
                        $contractsByType = [];

                        foreach ($orderedStudents as $student) {
                            $isPlayGroup = in_array($student->course_id, $pgCourseIds);
                            $enrollmentType = $isPlayGroup ? 'playgroup' : 'regular';

                            // Buscar o crear contrato para este tipo
                            if (!isset($contractsByType[$enrollmentType])) {
                                $inCourseStatusId = $contractRepo->getStatus('in_course', 'contract');

                                $existingContract = \App\Models\Contract::where('financial_parent_id', $parentId)
                                    ->where('period_id', $periodId)
                                    ->where('enrollment_type', $enrollmentType)
                                    ->where('status_contract_id', $inCourseStatusId)
                                    ->first();

                                if (!$existingContract) {
                                    // Crear nuevo contrato
                                    $periodYear = \App\Models\Period::where('id', $periodId)->value('period_year') ?? date('Y');
                                    $apiService = app(\App\Services\ApiInboundService::class);
                                    $contractCode = $apiService->generateContractCode($periodYear, $parentId, $periodId);

                                    $isPassport = ($parent->document_type ?? 'RUT') === 'PASSPORT';
                                    $signatureStatus = $isPlayGroup ? 'not_required' : ($isPassport ? 'pending_manual' : 'pending');

                                    $existingContract = $contractRepo->findOrcreate($contractCode, $parent, $periodId);
                                    $existingContract->enrollment_type = $enrollmentType;
                                    $existingContract->status_contract_id = $inCourseStatusId;
                                    $existingContract->status_payment_id = $contractRepo->getStatus('pending', 'payment');
                                    $existingContract->status_signature_id = $contractRepo->getStatus($signatureStatus, 'signature');
                                    $existingContract->observation = $isPlayGroup
                                        ? 'Contrato Play Group generado desde carga Excel.'
                                        : 'Contrato generado desde carga Excel.';
                                    $existingContract->save();

                                    Log::info("Contrato {$existingContract->code_contract} creado desde Excel para apoderado {$parentId}");
                                }

                                $contractsByType[$enrollmentType] = $existingContract;
                            }

                            $contract = $contractsByType[$enrollmentType];

                            // Agregar estudiante al contrato (sin recalcular hermanos aún)
                            $existingDetail = \App\Models\ContractDetail::where('contract_id', $contract->id)
                                ->where('student_id', $student->id)
                                ->exists();

                            if (!$existingDetail) {
                                $paysIncFee = $incorporationFeeMap[$student->id] ?? true;
                                $debts = $debtService->calculateStudentDebts($student->id, $periodId, $contract->id, null, $paysIncFee);
                                Log::info("Estudiante {$student->id} agregado a contrato {$contract->code_contract} con " . count($debts) . " deudas (paga_incorporacion=" . ($paysIncFee ? 'SI' : 'NO') . ")");
                            }
                        }

                        // Recalcular precios de hermanos UNA VEZ después de agregar todos los estudiantes
                        try {
                            $debtService->recalculateFamilySiblingDebts($parentId, $periodId);
                        } catch (\Exception $e) {
                            Log::warning("Error al recalcular hermanos para apoderado {$parentId}: " . $e->getMessage());
                        }
                    }
                }

            } else {
                // === CASO NORMAL: línea por línea ===

                foreach ($successLines as $line) {
                    $result = $processor->createOrUpdateRecord($line->data, $period);

                    if (isset($result['action']) && $result['action'] === 'updated') {
                        $updated++;
                    } else {
                        $created++;
                    }

                    if (isset($result['model'])) {
                        $line->linkToRecord($result['model']);
                    }
                }
            }

            // === Finalizar carga ===
            $upload->markAsCompleted();

            ExcelUploadLog::logConfirmed($upload->id, $userId, [
                'created' => $created,
                'updated' => $updated,
                'total' => $created + $updated,
            ]);

            DB::commit();

            return [
                'created' => $created,
                'updated' => $updated,
                'total' => $created + $updated,
            ];
        } catch (\Exception $e) {
            DB::rollBack();
            ExcelUploadLog::logError($upload->id, Auth::id(), 'Error al confirmar la carga: ' . $e->getMessage());
            throw $e;
        }
    }


    /**
     * Cancelar upload
     */
    public function cancel($id, Request $request)
    {
        $upload = ExcelUpload::findOrFail($id);

        if ($upload->isCompleted()) {
            throw new \Exception('No se puede cancelar una carga completada', 400);
        }

        $reason = $request->input('reason', 'Cancelado por el usuario');
        $userId = Auth::id();

        $upload->update(['status' => 'CANCELLED', 'notes' => $reason]);
        ExcelUploadLog::logCancelled($upload->id, $userId, $reason);

        return null;
    }

    /**
     * Eliminar upload
     */
    public function delete($id)
    {
        $upload = ExcelUpload::findOrFail($id);

        if ($upload->isCompleted()) {
            throw new \Exception('No se puede eliminar una carga completada', 400);
        }

        $upload->deleteFile();
        $upload->delete();

        return null;
    }

    /**
     * Reprocesar upload
     */
    public function reprocess($id)
    {
        $upload = ExcelUpload::findOrFail($id);

        if (!in_array($upload->status, ['ERROR', 'CANCELLED'])) {
            throw new \Exception('Solo se pueden reprocesar cargas con error o canceladas', 400);
        }

        $upload->lines()->delete();

        $upload->update([
            'status' => 'PENDING',
            'total_rows' => 0,
            'success_rows' => 0,
            'error_rows' => 0,
            'warning_rows' => 0,
            'processed_at' => null,
        ]);

        $this->processUpload($upload);

        return $upload->fresh();
    }

    /**
     * Obtener logs
     */
    public function getLogs($id)
    {
        ExcelUpload::findOrFail($id);

        return ExcelUploadLog::where('upload_id', $id)
            ->with('user:id,name,email')
            ->latest()
            ->get();
    }

    /**
     * Obtener tipos
     */
    public function getTypes()
    {
        return ExcelUploadType::active()
            ->ordered()
            ->get(['id', 'code', 'name', 'description', 'icon', 'color_class', 'template_file', 'active']);
    }

    /**
     * Obtener detalle de tipo
     */
    public function getTypeDetail($code)
    {
        return ExcelUploadType::where('code', $code)->firstOrFail();
    }

    /**
     * Descargar plantilla
     */
    public function downloadTemplate(string $code)
    {
        try {
            // Buscar tipo de carga
            $type = ExcelUploadType::where('code', strtoupper($code))->firstOrFail();
            $url = $type->getTemplateUrl();

            if (!$url) {
                throw new \Exception("No se encontró la plantilla asociada al tipo: {$code}", 404);
            }

            return [
                'file_name' => basename($type->template_file),
                'url' => $url,
            ];
        } catch (\Exception $e) {
            Log::error("⚠️ Error al obtener URL de plantilla: {$e->getMessage()}");
            throw new \Exception("Error al descargar plantilla.", 404);
        }
    }


    /**
     * Obtener estadísticas de tipo
     */
    public function getTypeStats($code, Request $request)
    {
        $type = ExcelUploadType::where('code', $code)->firstOrFail();

        $dateFrom = $request->get('date_from', now()->subDays(30));
        $dateTo = $request->get('date_to', now());

        return [
            'total' => $type->uploads()->whereBetween('uploaded_at', [$dateFrom, $dateTo])->count(),
            'completed' => $type->uploads()->completed()->whereBetween('uploaded_at', [$dateFrom, $dateTo])->count(),
            'pending_confirmation' => $type->uploads()->pendingConfirmation()->whereBetween('uploaded_at', [$dateFrom, $dateTo])->count(),
            'error' => $type->uploads()->where('status', 'ERROR')->whereBetween('uploaded_at', [$dateFrom, $dateTo])->count(),
            'total_rows' => $type->uploads()->whereBetween('uploaded_at', [$dateFrom, $dateTo])->sum('total_rows'),
            'success_rows' => $type->uploads()->whereBetween('uploaded_at', [$dateFrom, $dateTo])->sum('success_rows'),
            'error_rows' => $type->uploads()->whereBetween('uploaded_at', [$dateFrom, $dateTo])->sum('error_rows'),
        ];
    }

    /**
     * Obtener estadísticas generales
     */
    public function stats(Request $request)
    {
        $dateFrom = $request->get('date_from', now()->subDays(30));
        $dateTo = $request->get('date_to', now());

        return [
            'total' => ExcelUpload::whereBetween('uploaded_at', [$dateFrom, $dateTo])->count(),
            'by_status' => [
                'pending' => ExcelUpload::pending()->whereBetween('uploaded_at', [$dateFrom, $dateTo])->count(),
                'processing' => ExcelUpload::processing()->whereBetween('uploaded_at', [$dateFrom, $dateTo])->count(),
                'pending_confirmation' => ExcelUpload::pendingConfirmation()->whereBetween('uploaded_at', [$dateFrom, $dateTo])->count(),
                'completed' => ExcelUpload::completed()->whereBetween('uploaded_at', [$dateFrom, $dateTo])->count(),
                'error' => ExcelUpload::where('status', 'ERROR')->whereBetween('uploaded_at', [$dateFrom, $dateTo])->count(),
            ],
            'by_type' => ExcelUploadType::active()->get()->map(function ($type) use ($dateFrom, $dateTo) {
                return [
                    'code' => $type->code,
                    'name' => $type->name,
                    'count' => $type->uploads()->whereBetween('uploaded_at', [$dateFrom, $dateTo])->count(),
                ];
            }),
            'total_rows_processed' => ExcelUpload::whereBetween('uploaded_at', [$dateFrom, $dateTo])->sum('total_rows'),
            'total_success_rows' => ExcelUpload::whereBetween('uploaded_at', [$dateFrom, $dateTo])->sum('success_rows'),
            'total_error_rows' => ExcelUpload::whereBetween('uploaded_at', [$dateFrom, $dateTo])->sum('error_rows'),
            'total_warning_rows' => ExcelUpload::whereBetween('uploaded_at', [$dateFrom, $dateTo])->sum('warning_rows'),
        ];
    }

    /**
     * Eliminar una línea
     */
    public function deleteLine($uploadId, $lineNumber)
    {
        DB::beginTransaction();

        try {
            $upload = ExcelUpload::findOrFail($uploadId);

            if (!$upload->isPendingConfirmation()) {
                throw new \Exception('Solo se pueden eliminar líneas de cargas pendientes de confirmación', 400);
            }

            $line = ExcelUploadLine::where('upload_id', $uploadId)
                ->where('line_number', $lineNumber)
                ->firstOrFail();

            $line->delete();

            // Actualizar contadores
            $upload->updateCounters();

            // Si no quedan líneas, cancelar la subida
            $uploadCancelled = false;
            $reason = 'Carga cancelada automáticamente: se eliminaron todos los registros';
            if ($upload->lines()->count() === 0) {
                $upload->update([
                    'status' => 'CANCELLED',
                    'notes' => $reason,
                ]);
                ExcelUploadLog::logCancelled($upload->id, Auth::id(), $reason);
                $uploadCancelled = true;
            }

            DB::commit();

            return [
                'upload_cancelled' => $uploadCancelled,
            ];
        } catch (\Exception $e) {
            DB::rollBack();
            throw $e;
        }
    }

    /**
     * Eliminar múltiples líneas
     */
    public function bulkDeleteLines($uploadId, Request $request)
    {
        DB::beginTransaction();

        try {
            $upload = ExcelUpload::findOrFail($uploadId);

            if (!$upload->isPendingConfirmation()) {
                throw new \Exception('Solo se pueden eliminar líneas de cargas pendientes de confirmación', 400);
            }

            $lineNumbers = $request->input('line_numbers');

            $deleted = ExcelUploadLine::where('upload_id', $uploadId)
                ->whereIn('line_number', $lineNumbers)
                ->delete();

            // Actualizar contadores
            $upload->updateCounters();

            // Si no quedan líneas, cancelar la subida
            $uploadCancelled = false;
            $reason = 'Carga cancelada automáticamente: se eliminaron todos los registros';
            if ($upload->lines()->count() === 0) {
                $upload->update([
                    'status' => 'CANCELLED',
                    'notes' => $reason,
                ]);
                ExcelUploadLog::logCancelled($upload->id, Auth::id(), $reason);
                $uploadCancelled = true;
            }

            DB::commit();

            return [
                'deleted' => $deleted,
                'total' => count($lineNumbers),
                'upload_cancelled' => $uploadCancelled,
            ];
        } catch (\Exception $e) {
            DB::rollBack();
            throw $e;
        }
    }


    public function downloadAllLines($id)
    {
        $upload = ExcelUpload::with(['uploadType'])->findOrFail($id);

        if (!$upload->lines()->exists()) {
            throw new Exception('No hay líneas registradas para este archivo.', 404);
        }

        $fileName = sprintf(
            'resultado_%s_%s.xlsx',
            $upload->uploadType->name ?? 'upload',
            now()->format('Ymd_His')
        );

        return (object)[
            'upload'=> $upload,
            'fileName'=> $fileName,
        ];

    }
}