<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;

class NivelExportController extends Controller
{
    public function exportarExcel(Request $request)
    {
        $tabla = $request->input('tabla');

        switch ($tabla) {
           case 'niveles':
                return $this->generarReporte('niveles', 'NIVELES EDUCATIVOS', ['idNivel', 'nombreNivel', 'estado']);
            case 'materias_categorias':
                return $this->generarReporte('materias_categorias', 'CATEGORÍAS DE MATERIAS', ['categoria', 'ordenBoleta', 'estado']);
            case 'materias':
                return $this->generarReporte('materias', 'MATERIAS', ['idMateria','categoria','nombreMateria','idGradoGrupoRelacionado','afectaBoleta','materiaAcademica','mostrarMateria','estado']);
            case 'grados':
    return $this->generarReporte(
        'vista_grados_niveles',
        'GRADOS',
        [
            'Clave del grado' => 'idGrado',
            'Nombre del grado' => 'nombreGrado',
            'Nivel Relacionado' => 'nombreNivel'
        ]
    );

            case 'grupos':
                return $this->generarReporte('grupos', 'GRUPOS', ['idGrupo', 'nombreGrupo','Estado']);
            case 'usuarios':
                return $this->generarReporte('usuarios', 'USUARIOS', ['idUsuario', 'tipoUsuario', 'primerNombre','apellidoPaterno','grado','estatus']);
            case 'tipos_tareas':
                return $this->generarReporte('tareas_tipos', 'TIPOS DE TAREAS', ['idTareasTipos', 'nombreTareasTipos', 'envioPlatTareasTipos','estado']);
                 case 'relacion_Alumno':
                return $this->generarReporte('vista_alumnos_padres_grupos', 'Relacion Alumno', ['idUsuario', 'nombreAlumno', 'grado','nombrePadre','nombreGrupo']);
                case 'profesor':
              return $this->generarReporte('vista_profesores_materias_grupos', 'Relacion Profesores', ['idUsuario', 'nombreProfesor', 'idMateria','gradoGrupo',]);

            default:
                return response()->json(['error' => 'Tabla no válida'], 400);
        }
    }

    private function generarReporte($tabla, $titulo, $headers)
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $sheet->mergeCells('A1:P2');
        $sheet->setCellValue('A1', "REPORTE DE $titulo");
        $sheet->getStyle('A1:P2')->applyFromArray([
            'font' => [
                'bold' => true, 'size' => 18, 'name' => 'Century', 'color' => ['rgb' => 'FFFFFF']
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
            'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '23255E']],
            'borders' => [
                'bottom' => ['borderStyle' => Border::BORDER_THICK, 'color' => ['rgb' => 'FFFFFF']],
            ],
        ]);
        $sheet->getRowDimension(1)->setRowHeight(70);

        //
        $logo = new Drawing();
        $logo->setName('Logo');
        $logo->setDescription('Logo de la empresa');
        $logo->setPath(public_path('/Logo.png'));
        $logo->setHeight(90);
        $logo->setCoordinates('A1');
        $logo->setOffsetX(10);
        $logo->setOffsetY(5);
        $logo->setWorksheet($sheet);

        // Descripción
        $sheet->mergeCells('H3:J3');
        $sheet->setCellValue('H3', "Este reporte muestra los datos registrados en la tabla $tabla.");
        $sheet->getStyle('H3')->applyFromArray([
            'font' => ['italic' => true, 'size' => 10, 'name' => 'Century', 'color' => ['rgb' => '333333']],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ]);

        // Encabezados
        $sheet->fromArray($headers, null, 'H4');
        $lastCol = chr(72 + count($headers) - 1);
        $sheet->getStyle("H4:$lastCol" . '4')->applyFromArray([
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF'], 'size' => 12, 'name' => 'Century'],
            'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '4F81BD']],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
            'borders' => [
                'allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'FFFFFF']],
            ],
        ]);

        // Datos
$datos = DB::table($tabla)->select($headers)->get();
        $row = 5;

        foreach ($datos as $index => $registro) {
            $col = 'H';
            foreach ((array)$registro as $value) {
                $sheet->setCellValue($col . $row, $value);
                $col++;
            }

            $sheet->getRowDimension($row)->setRowHeight(25);
            $fillColor = $index % 2 === 0 ? 'D9E1F2' : 'FFFFFF';

            $sheet->getStyle("H$row:$lastCol$row")->applyFromArray([
                'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $fillColor]],
                'font' => ['name' => 'Calibri', 'size' => 11],
                'borders' => [
                    'allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'BFBFBF']],
                ],
            ]);
            $row++;
        }

        // Autosize columnas
        foreach (range('H', $lastCol) as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }

        $sheet->setAutoFilter("H4:$lastCol" . '4');

        $marcaAgua = new Drawing();

         $marcaAgua->setName('Marca de Agua');
                 $marcaAgua->setDescription('Marca de agua');
                 $marcaAgua->setPath(public_path('/MarcaAgua.png'));
        $marcaAgua->setCoordinates('H15');
             $marcaAgua->setHeight(400);
        $marcaAgua->setWorksheet($sheet);

        $writer = new Xlsx($spreadsheet);
        $filename = "$titulo.xlsx";
        $tempPath = tempnam(sys_get_temp_dir(), $filename);
        $writer->save($tempPath);

        return response()->download($tempPath, $filename, [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        ]);
    }
}