SistemaMantenimiento/sistema-mantenimiento-back/app/Http/Controllers/AsyncValidateLoadArchivesController.php

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\Validator;
use PhpOffice\PhpSpreadsheet\IOFactory;
use ZipArchive;
use Illuminate\Support\Str;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use ElephantIO\Client;


/**
 * Async Controller for validating Excel and ZIP files for equipment documentation loading
 * Validates Excel headers structure and compares file listings with ZIP contents using queues
 */
class AsyncValidateLoadArchivesController extends Controller
{
    private $responseController;
    private $encryptionController;
    private $documentManagementController;
    private $functionsController;
    private $notificationsController;
    private $socketClient;

    // Expected headers in row 7 of Excel file with their corresponding column letters
    private $expectedHeaders = [
        ['letter' => 'D', 'category' => 'CÓDIGO DE EQUIPO'],
        ['letter' => 'H', 'category' => 'FICHA DE SEGURIDAD DE PRODUCTOS QUÍMICOS'],
        ['letter' => 'J', 'category' => 'FICHA TÉCNICA'],
        ['letter' => 'L', 'category' => 'FOTOGRAFÍAS - DIAGRAMAS'],
        ['letter' => 'N', 'category' => 'DATOS DEL PROVEEDOR'],
        ['letter' => 'P', 'category' => 'MANUALES DE OPERACIÓN'],
        ['letter' => 'R', 'category' => 'MANUALES DE MANTENIMIENTO PREVENTIVO'],
        ['letter' => 'T', 'category' => 'MANUALES DE MANTENIMIENTO CORRECTIVO'],
        ['letter' => 'V', 'category' => 'DOCUMENTOS ADICIONALES']
    ];

    // Mapping of file extensions to their types for validation
    private $extensionTypes = [
        // Archivos de almacenamiento
        'zip' => 'STORAGE',
        'rar' => 'STORAGE',
        'tar' => 'STORAGE',
        '7z' => 'STORAGE',
        // Formatos de audio
        'mp3' => 'AUDIO',
        'mpeg' => 'AUDIO',
        'wav' => 'AUDIO',
        'ogg' => 'AUDIO',
        'opus' => 'AUDIO',
        // Archivos de imagen
        'jpeg' => 'IMG',
        'jpg' => 'IMG',
        'png' => 'IMG',
        'gif' => 'IMG',
        'bmp' => 'IMG',
        'tiff' => 'IMG',
        'svg' => 'IMG',
        // Archivos de texto
        'txt' => 'TEXT',
        // Archivos de video
        'webm' => 'VIDEO',
        'mpeg4' => 'VIDEO',
        'mp4' => 'VIDEO',
        '3gpp' => 'VIDEO',
        'mov' => 'VIDEO',
        'avi' => 'VIDEO',
        'wmv' => 'VIDEO',
        'flv' => 'VIDEO',
        // Planos de Auto CAD
        'dwg' => 'CAD',
        'dxf' => 'CAD',
        // Modelos de 3D Studio
        '3ds' => '3D',
        // Dibujos de Illustrator
        'ai' => 'ILLUSTRATOR',
        // Imágenes de Photoshop
        'psd' => 'PHOTOSHOP',
        // Formato de documento portátil
        'pdf' => 'PDF',
        // Hoja de cálculo en Excel
        'xls' => 'EXCEL',
        'xlsx' => 'EXCEL',
        // Presentaciones de PowerPoint
        'ppt' => 'POWERPOINT',
        'pptx' => 'POWERPOINT',
        // Documentos de texto de Word
        'doc' => 'WORD',
        'docx' => 'WORD',
        // Dibujos de Visio
        'vsd' => 'VISIO',
        'vsdx' => 'VISIO'
    ];

    public function __construct()
    {
        $this->responseController = new ResponseController();
        $this->encryptionController = new EncryptionController();
        $this->documentManagementController = new DocumentManagementController();
        $this->functionsController = new FunctionsController();
        $this->notificationsController = new NotificationsController();

        $url = 'http://localhost:3200';
        $this->socketClient = new Client(Client::engine(Client::CLIENT_4X, $url));
        $this->socketClient->initialize();
        $this->socketClient->of('/');
    }


    /**
     * EmitNotification use  
     */
    public function emitNotification(Request $request): bool
    {
        Log::info($request);
        $module = $request['module'];
        $title = $request['title'];
        $content = $request['content'];
        $actions = is_string($request['actions']) ? json_decode($request['actions'], true) : $request['actions'];
        //$audience = [$request['audience']];
        $audience = is_array($request['audience']) ? $request['audience'] : [$request['audience']];
        $idUser = $request['idUser'] ?? 1;
        $line = $request['line'] ?? 1;
        $idOrder = $request['idOrder'] ?? null;
        $orderType = $request['orderType'] ?? null;

        $this->notificationsController->emitNotification($module, $title, $content, $actions, $audience, $idUser, $line, $this->socketClient, $idOrder, $orderType);

        return true;
    }


    /***
     * Encryption endpoint
     *
     */

    public function encrypt(Request $request)
    {
        $encryptString = $this->encryptionController->encrypt($request['value']);
        if (!$encryptString) {
            return $this->responseController->makeResponse(true, "La cadena no fue encriptada correctamente", [], 400);
        }
        return $this->responseController->makeResponse(false, "Cadena encriptada correctamente", ['encrypted' => $encryptString], 200);
    }

    /***
     * Encryption endpoint
     *
     */

    public function decrypt(Request $request)
    {
        $encryptString = $this->encryptionController->decrypt($request['value']);
        if (!$encryptString) {
            return $this->responseController->makeResponse(true, "La cadena no fue desencriptada correctamente", [], 400);
        }
        return $this->responseController->makeResponse(false, "Cadena desencriptada correctamente", ['decrypted' => $encryptString], 200);
    }

    /**
     * Validation endpoint that dispatches file process to bat job
     */

    public function validateFilesWithBat(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'excel_file' => 'required|file|mimes:xlsx,xls',
            'zip_file' => 'required|file|mimes:zip'
        ]);

        if ($validator->fails()) {
            return $this->responseController->makeResponse(
                true,
                'Se encontraron uno o más errores.',
                $this->responseController->makeErrors($validator->errors()->messages()),
                400
            );
        }

        $jobId = Str::uuid();
        $userId = '0000000001';

        // $idUser = $this->encryptionController->decrypt($request->input('id_user'));
        // if(!$idUser){
        //     return $this->responseController->makeResponse(true, "El id del usuario que realizó la petición no fue encriptado correctamente", [], 400);
        // }

        // Validate Excel file headers structure
        $excelValidation = $this->validateExcelHeaders($request->file('excel_file'));
        if ($excelValidation['error']) {
            return $this->responseController->makeResponse(true, $excelValidation['message'], [], 400);
        }


        // Validate ZIP file integrity
        $zipValidation = $this->validateZipFile($request->file('zip_file'));
        if ($zipValidation['error']) {
            return $this->responseController->makeResponse(true, $zipValidation['message'], [], 400);
        }


        // Extract and validate file listings from Excel
        $filesValidation = $this->extractAndValidateFiles($request->file('excel_file'));
        if ($filesValidation['error']) {
            return $this->responseController->makeResponse(true, $filesValidation['message'], [], 400);
        }


        // Compare Excel file listings with ZIP contents
        $comparison = $this->compareExcelWithZip($filesValidation['files'], $request->file('zip_file'));

        // Check for file size validation errors
        if (isset($comparison['error'])) {
            return $this->responseController->makeResponse(true, $comparison['error'], [], 400);
        }


        // Upload temp files
        $tempFiles = $this->uploadTempFiles($request);
        if ($tempFiles['error']) {
            return $this->responseController->makeResponse(
                true,
                $tempFiles['message'],
                [
                    'valid' => false,
                    'missing_in_zip' => $comparison['missing_in_zip'] ?? [],
                    'extra_in_zip' => $comparison['extra_in_zip'] ?? []
                ],
                400
            );
        }

        if (!$comparison['valid']) {
            return $this->responseController->makeResponse(
                true,
                'Los archivos no coinciden entre Excel y ZIP',
                [
                    'valid' => false,
                    'missing_in_zip' => $comparison['missing_in_zip'],
                    'extra_in_zip' => $comparison['extra_in_zip']
                ],
                400
            );
        }


        $idFileZip = $this->encryptionController->decrypt($tempFiles['files']['zip']);
        if (!$idFileZip) {
            return $this->responseController->makeResponse(true, "El id del archivo que desea eliminar no fue encriptado correctamente", [], 400);
        }

        $idFileExcel = $this->encryptionController->decrypt($tempFiles['files']['excel']);
        if (!$idFileExcel) {
            return $this->responseController->makeResponse(true, "El id del archivo que desea eliminar no fue encriptado correctamente", [], 400);
        }

        // Guardar en bd::S002V01TVAJO 
        $validationJobId = DB::table('S002V01TVAJO')->insertGetId([
            'VAJO_COJO' => $jobId,
            'VAJO_IDUS' => $userId,
            'VAJO_NULI' => $request->input('linea'),
            'VAJO_ESTA' => 'queued',
            'VAJO_PRPE' => 0,
            'VAJO_EXTE' => $idFileExcel,
            'VAJO_ZITE' => $idFileZip,
            'VAJO_FERE' => now(),
            'VAJO_FEMO' => now()
        ]);

        return $this->responseController->makeResponse(
            false,
            'Validación iniciada',
            [
                'job_id' => $validationJobId,
                'status' => 'queued',
                'valid' => $comparison['valid'],
                'missing_in_zip' => $comparison['missing_in_zip'],
                'extra_in_zip' => $comparison['extra_in_zip']
            ]
        );
    }


    /**
     * Process endpoint that moves temp files to final storage
     */
    public function processLoadArchives(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'id_user' => 'required|string',
            'linea' => 'required|integer',
            'temp_files' => 'required|array',
            'temp_files.excel' => 'required|string',
            'temp_files.zip' => 'required|string',
            'individual_temp_files' => 'required|array'
        ]);

        if ($validator->fails()) {
            return $this->responseController->makeResponse(
                true,
                'Se encontraron uno o más errores.',
                $this->responseController->makeErrors($validator->errors()->messages()),
                400
            );
        }

        $form = $request->all();
        $idUser  = '0000000001';

        // $idUser = $this->encryptionController->decrypt($form['id_user']);
        // if (!$idUser) {
        //     return $this->responseController->makeResponse(true, 'ID de usuario inválido.', [], 400);
        // }

        $finalFiles = [];

        try {
            // Verificar estado de archivos antes de procesarlos
            $tempFiles = [$form['temp_files']['excel'], $form['temp_files']['zip']];
            foreach ($form['individual_temp_files'] as $tempFile) {
                $tempFiles[] = $tempFile['temp_id'];
            }

            foreach ($tempFiles as $tempId) {
                $tempIdDec = $this->encryptionController->decrypt($tempId);
                if ($tempIdDec) {
                    $tempFile = DB::table('S002V01TARTE')->where('ARTE_IDAR', $tempIdDec)->first();
                    if ($tempFile && $tempFile->ARTE_ESTA === 'Eliminado') {
                        return $this->responseController->makeResponse(
                            true,
                            'La acción no puede completarse porque uno o más archivos han sido eliminados lógicamente.',
                            [],
                            400
                        );
                    }
                }
            }

            // Move Excel to final
            $excelFinal = $this->moveToFinal($form['temp_files']['excel'], $form['linea'], $idUser);
            if (!$excelFinal) {
                return $this->responseController->makeResponse(true, 'Error procesando Excel', [], 400);
            }
            $finalFiles['excel'] = $excelFinal;

            // Move ZIP to final
            $zipFinal = $this->moveToFinal($form['temp_files']['zip'], $form['linea'], $idUser);
            if (!$zipFinal) {
                return $this->responseController->makeResponse(true, 'Error procesando ZIP', [], 400);
            }
            $finalFiles['zip'] = $zipFinal;

            // Move individual temp files to final storage
            $individualFiles = [];
            foreach ($form['individual_temp_files'] as $tempFile) {
                $finalFileId = $this->moveToFinal($tempFile['temp_id'], $form['linea'], $idUser);
                if ($finalFileId) {
                    $individualFiles[] = [
                        'original_name' => $tempFile['original_name'],
                        'final_id' => $finalFileId
                    ];
                }
            }
            $finalFiles['individual_files'] = $individualFiles;
            return $this->responseController->makeResponse(false, 'Archivos procesados exitosamente.', $finalFiles);
        } catch (\Exception $e) {
            return $this->responseController->makeResponse(true, 'Error interno: ' . $e->getMessage(), [], 500);
        }
    }

    /**
     * Moves a temporary file to final storage using DocumentManagementController
     */
    private function moveToFinal($tempId, $line, $idUser)
    {
        try {
            $tempIdDec = $this->encryptionController->decrypt($tempId);
            if (!$tempIdDec) {
                return false;
            }

            $tempFile = DB::table('S002V01TARTE')->where('ARTE_IDAR', $tempIdDec)->first();
            if (!$tempFile) {
                return false;
            }

            // Validar estado del archivo temporal
            if ($tempFile->ARTE_ESTA === 'Eliminado') {
                return false;
            }

            $result = $this->documentManagementController->moveFinalFile($line, 'ADSI', 'LA', $tempFile, $idUser);
            return $result[0] ? $result[1] : false;
        } catch (\Exception $e) {
            return false;
        }
    }

    /**
     * Validates that Excel file has the correct headers in row 7
     * Each column must match the expected category name exactly
     */
    private function validateExcelHeaders($file)
    {
        try {
            $spreadsheet = IOFactory::load($file->getPathname());
            $worksheet = $spreadsheet->getActiveSheet();

            // Check each expected header in row 7
            foreach ($this->expectedHeaders as $header) {
                $cellValue = $worksheet->getCell($header['letter'] . '7')->getValue();
                if (trim($cellValue) !== $header['category']) {
                    return [
                        'error' => true,
                        'message' => "El encabezado en la columna {$header['letter']} no coincide. Se esperaba: '{$header['category']}', se encontró: '{$cellValue}'"
                    ];
                }
            }


            return ['error' => false];
        } catch (\Exception $e) {
            return ['error' => true, 'message' => 'Error al procesar el archivo Excel: ' . $e->getMessage()];
        }
    }

    /**
     * Validates ZIP file integrity and ensures it's not empty
     */
    private function validateZipFile($file)
    {
        $zip = new ZipArchive();
        $result = $zip->open($file->getPathname());

        if ($result !== TRUE) {
            return ['error' => true, 'message' => 'No se pudo abrir el archivo ZIP.'];
        }

        if ($zip->numFiles === 0) {
            $zip->close();
            return ['error' => true, 'message' => 'El archivo ZIP está vacío.'];
        }


        $zip->close();
        return ['error' => false];
    }

    /**
     * Extracts file listings from Excel starting from row 8
     * Validates file extensions and creates structured file list
     * Only processes rows with equipment code (column D)
     */
    private function extractAndValidateFiles($file)
    {
        try {
            $spreadsheet = IOFactory::load($file->getPathname());
            $worksheet = $spreadsheet->getActiveSheet();
            $highestRow = $worksheet->getHighestRow();
            $files = [];



            // Process each row starting from row 8 (data rows)
            for ($row = 8; $row <= $highestRow; $row++) {
                $equipmentCode = trim($worksheet->getCell('D' . $row)->getValue());
                if (empty($equipmentCode)) continue; // Skip rows without equipment code

                $hasFiles = false;
                $rowFiles = [];

                // Check each file category column (excluding equipment code column D)
                foreach ($this->expectedHeaders as $header) {
                    if ($header['letter'] === 'D') continue;

                    $cellValue = trim($worksheet->getCell($header['letter'] . $row)->getValue());
                    if (empty($cellValue)) continue;

                    // Handle multiple files separated by commas
                    $fileNames = explode(',', $cellValue);
                    foreach ($fileNames as $fileName) {
                        $fileName = trim($fileName);
                        if (empty($fileName)) continue;

                        // Validate file extension 
                        $extension = strtolower(pathinfo($fileName, PATHINFO_EXTENSION));
                        if (!isset($this->extensionTypes[$extension])) {
                            return [
                                'error' => true,
                                'message' => "Extensión inválida '{$extension}' en archivo '{$fileName}' para equipo '{$equipmentCode}'"
                            ];
                        }

                        $rowFiles[] = [
                            'equipment_code' => $equipmentCode,
                            'file_name' => $fileName,
                            'type' => $this->getFileType($extension)
                        ];
                        $hasFiles = true;
                    }
                }

                if ($hasFiles) {
                    $files = array_merge($files, $rowFiles);
                }
            }


            return ['error' => false, 'files' => $files];
        } catch (\Exception $e) {

            return ['error' => true, 'message' => 'Error al extraer archivos del Excel: ' . $e->getMessage()];
        }
    }

    /**
     * Compares file listings from Excel with actual files in ZIP
     * Returns validation status and lists of missing/extra files
     */
    private function compareExcelWithZip($excelFiles, $zipFile)
    {
        $zip = new ZipArchive();
        $zip->open($zipFile->getPathname());



        // Extract all file names and sizes from ZIP
        $zipFiles = [];
        $zipFileSizes = [];
        for ($i = 0; $i < $zip->numFiles; $i++) {
            $fullPath = $zip->getNameIndex($i);

            // Skip directories (entries ending with /)
            if (substr($fullPath, -1) === '/') {
                continue;
            }

            // Extract filename and size
            $fileName = basename($fullPath);
            if (!empty($fileName)) {
                $zipFiles[] = $fileName;
                $zipFileSizes[$fileName] = $zip->statIndex($i)['size'];
            }
        }
        $zip->close();

        // Validate file sizes using FunctionsController
        foreach ($zipFileSizes as $fileName => $size) {
            $extension = strtolower(pathinfo($fileName, PATHINFO_EXTENSION));
            if (!$this->functionsController->checkFileSize($extension, $size)) {
                $sizeMB = round($size / (1024 * 1024), 2);
                return [
                    'valid' => false,
                    'error' => "El archivo '{$fileName}' excede el límite de tamaño. Tamaño: {$sizeMB}MB"
                ];
            }
        }

        // Get file names from Excel listings
        $excelFileNames = array_column($excelFiles, 'file_name');

        // Find discrepancies between Excel and ZIP
        $missingInZip = array_diff($excelFileNames, $zipFiles);
        $extraInZip = array_diff($zipFiles, $excelFileNames);

        $isValid = empty($missingInZip) && empty($extraInZip);


        return [
            'valid' => $isValid,
            'missing_in_zip' => array_values($missingInZip),
            'extra_in_zip' => array_values($extraInZip)
        ];
    }

    /**
     * Uploads Excel and ZIP files as temporary files using DocumentManagementController
     */
    private function uploadTempFiles(Request $request)
    {
        try {

            $tempFiles = [];

            // Upload Excel file
            $excelRequest = new Request();
            $excelRequest->files->set('file', $request->file('excel_file'));
            $excelRequest->merge([
                'id_user' => $request->input('id_user'),
                'linea' => $request->input('linea')
            ]);

            $excelResponse = $this->documentManagementController->uploadTempFile($excelRequest);
            $excelData = json_decode($excelResponse->getContent());

            if ($excelData->error) {
                $errorMsg = isset($excelData->message) ? $excelData->message : 'Error desconocido subiendo Excel';

                return ['error' => true, 'message' => 'Error subiendo Excel: ' . $errorMsg];
            }

            $tempFiles['excel'] = $excelData->response->idArchivo;

            // Upload ZIP file
            $zipRequest = new Request();
            $zipRequest->files->set('file', $request->file('zip_file'));
            $zipRequest->merge([
                'id_user' => $request->input('id_user'),
                'linea' => $request->input('linea')
            ]);

            $zipResponse = $this->documentManagementController->uploadTempFile($zipRequest);
            $zipData = json_decode($zipResponse->getContent());

            if ($zipData->error) {
                $errorMsg = isset($zipData->message) ? $zipData->message : 'Error desconocido subiendo ZIP';

                return ['error' => true, 'message' => 'Error subiendo ZIP: ' . $errorMsg];
            }

            $tempFiles['zip'] = $zipData->response->idArchivo;


            return ['error' => false, 'files' => $tempFiles];
        } catch (\Exception $e) {

            return ['error' => true, 'message' => 'Error en uploadTempFiles: ' . $e->getMessage()];
        }
    }



    /**
     * Gets file type based on extension
     */
    private function getFileType($extension)
    {
        $typeMap = [
            'zip' => 'STORAGE',
            'rar' => 'STORAGE',
            'tar' => 'STORAGE',
            '7z' => 'STORAGE',
            'mp3' => 'AUDIO',
            'mpeg' => 'AUDIO',
            'wav' => 'AUDIO',
            'ogg' => 'AUDIO',
            'opus' => 'AUDIO',
            'jpeg' => 'IMG',
            'jpg' => 'IMG',
            'png' => 'IMG',
            'gif' => 'IMG',
            'bmp' => 'IMG',
            'tiff' => 'IMG',
            'svg' => 'IMG',
            'txt' => 'TEXT',
            'webm' => 'VIDEO',
            'mpeg4' => 'VIDEO',
            'mp4' => 'VIDEO',
            '3gpp' => 'VIDEO',
            'mov' => 'VIDEO',
            'avi' => 'VIDEO',
            'wmv' => 'VIDEO',
            'flv' => 'VIDEO',
            'dwg' => 'CAD',
            'dxf' => 'CAD',
            '3ds' => '3D',
            'ai' => 'ILLUSTRATOR',
            'psd' => 'PHOTOSHOP',
            'pdf' => 'PDF',
            'xls' => 'EXCEL',
            'xlsx' => 'EXCEL',
            'ppt' => 'POWERPOINT',
            'pptx' => 'POWERPOINT',
            'doc' => 'WORD',
            'docx' => 'WORD',
            'vsd' => 'VISIO',
            'vsdx' => 'VISIO'
        ];
        return $typeMap[$extension] ?? 'UNKNOWN';
    }
}