import { MinioService } from '@/common/minio/minio.service';
import { HttpException, HttpStatus, Inject, Injectable } from '@nestjs/common';
import { and, eq, gte, ilike, lte, or, SQL, sql } from 'drizzle-orm';
import { NodePgDatabase } from 'drizzle-orm/node-postgres';
import { DRIZZLE_PROVIDER } from 'src/database/drizzle-provider';
import * as schema from 'src/database/index';
import { states, trainingSurveys } from 'src/database/index';

import { PaginationDto } from '../../common/dto/pagination.dto';
import { CreateTrainingDto } from './dto/create-training.dto';
import { TrainingStatisticsFilterDto } from './dto/training-statistics-filter.dto';
import { UpdateTrainingDto } from './dto/update-training.dto';

@Injectable()
export class TrainingService {
  constructor(
    @Inject(DRIZZLE_PROVIDER) private drizzle: NodePgDatabase<typeof schema>,
    private readonly minioService: MinioService,
  ) { }

  async findAll(paginationDto?: PaginationDto) {
    const {
      page = 1,
      limit = 10,
      search = '',
      sortBy = 'id',
      sortOrder = 'asc',
    } = paginationDto || {};

    const offset = (page - 1) * limit;

    let searchCondition: SQL<unknown> | undefined;
    if (search) {
      searchCondition = or(ilike(trainingSurveys.ospName, `%${search}%`));
    }

    const orderBy =
      sortOrder === 'asc'
        ? sql`${trainingSurveys[sortBy as keyof typeof trainingSurveys]} asc`
        : sql`${trainingSurveys[sortBy as keyof typeof trainingSurveys]} desc`;

    const totalCountResult = await this.drizzle
      .select({ count: sql<number>`count(*)` })
      .from(trainingSurveys)
      .where(searchCondition);

    const totalCount = Number(totalCountResult[0].count);
    const totalPages = Math.ceil(totalCount / limit);

    const data = await this.drizzle
      .select()
      .from(trainingSurveys)
      .where(searchCondition)
      .orderBy(orderBy)
      .limit(limit)
      .offset(offset);

    const meta = {
      page,
      limit,
      totalCount,
      totalPages,
      hasNextPage: page < totalPages,
      hasPreviousPage: page > 1,
      nextPage: page < totalPages ? page + 1 : null,
      previousPage: page > 1 ? page - 1 : null,
    };

    return { data, meta };
  }

  async getStatistics(filterDto: TrainingStatisticsFilterDto) {
    const { startDate, endDate, stateId, municipalityId, parishId, ospType } =
      filterDto;

    const filters: SQL[] = [];

    if (startDate)
      filters.push(gte(trainingSurveys.visitDate, new Date(startDate)));
    if (endDate)
      filters.push(lte(trainingSurveys.visitDate, new Date(endDate)));
    if (stateId) filters.push(eq(trainingSurveys.state, stateId));
    if (municipalityId)
      filters.push(eq(trainingSurveys.municipality, municipalityId));
    if (parishId) filters.push(eq(trainingSurveys.parish, parishId));
    if (ospType) filters.push(eq(trainingSurveys.ospType, ospType));

    const whereCondition = filters.length > 0 ? and(...filters) : undefined;

    // Ejecutamos todas las consultas en paralelo con Promise.all para mayor velocidad
    const [
      totalOspsResult,
      totalProducersResult,
      totalProductsResult, // Nuevo: Calculado desde el JSON
      statusDistribution,
      activityDistribution,
      typeDistribution,
      stateDistribution,
      yearDistribution,
    ] = await Promise.all([
      // 1. Total OSPs
      this.drizzle
        .select({ count: sql<number>`count(*)` })
        .from(trainingSurveys)
        .where(whereCondition),

      // 2. Total Productores (Columna plana que mantuviste)
      this.drizzle
        .select({
          sum: sql<number>`SUM(${trainingSurveys.womenCount} + ${trainingSurveys.menCount})`,
        })
        .from(trainingSurveys)
        .where(whereCondition),

      // 3. NUEVO: Total Productos (Contamos el largo del array JSON productList)
      this.drizzle
        .select({
          sum: sql<number>`sum(jsonb_array_length(${trainingSurveys.productList}))`,
        })
        .from(trainingSurveys)
        .where(whereCondition),

      // 4. Distribución por Estatus
      this.drizzle
        .select({
          name: trainingSurveys.currentStatus,
          value: sql<number>`count(*)`,
        })
        .from(trainingSurveys)
        .where(whereCondition)
        .groupBy(trainingSurveys.currentStatus),

      // 5. Distribución por Actividad
      this.drizzle
        .select({
          name: trainingSurveys.productiveActivity,
          value: sql<number>`count(*)`,
        })
        .from(trainingSurveys)
        .where(whereCondition)
        .groupBy(trainingSurveys.productiveActivity),

      // 6. Distribución por Tipo
      this.drizzle
        .select({
          name: trainingSurveys.ospType,
          value: sql<number>`count(*)`,
        })
        .from(trainingSurveys)
        .where(whereCondition)
        .groupBy(trainingSurveys.ospType),

      // 7. Distribución por Estado (CORREGIDO con COALESCE)
      this.drizzle
        .select({
          // Si states.name es NULL, devuelve 'Sin Asignar'
          name: sql<string>`COALESCE(${states.name}, 'Sin Asignar')`,
          value: sql<number>`count(${trainingSurveys.id})`,
        })
        .from(trainingSurveys)
        .leftJoin(states, eq(trainingSurveys.state, states.id))
        .where(whereCondition)
        // Importante: Agrupar también por el resultado del COALESCE o por states.name
        .groupBy(states.name),

      // 8. Distribución por Año
      this.drizzle
        .select({
          name: sql<string>`cast(${trainingSurveys.companyConstitutionYear} as text)`,
          value: sql<number>`count(*)`,
        })
        .from(trainingSurveys)
        .where(whereCondition)
        .groupBy(trainingSurveys.companyConstitutionYear)
        .orderBy(trainingSurveys.companyConstitutionYear),
    ]);

    return {
      totalOsps: Number(totalOspsResult[0]?.count || 0),
      totalProducers: Number(totalProducersResult[0]?.sum || 0),
      totalProducts: Number(totalProductsResult[0]?.sum || 0), // Dato extraído del JSON

      statusDistribution: statusDistribution.map((item) => ({
        ...item,
        value: Number(item.value),
      })),
      activityDistribution: activityDistribution.map((item) => ({
        ...item,
        value: Number(item.value),
      })),
      typeDistribution: typeDistribution.map((item) => ({
        ...item,
        value: Number(item.value),
      })),
      stateDistribution: stateDistribution.map((item) => ({
        ...item,
        value: Number(item.value),
      })),
      yearDistribution: yearDistribution.map((item) => ({
        ...item,
        value: Number(item.value),
      })),
    };
  }

  async findOne(id: number) {
    const find = await this.drizzle
      .select()
      .from(trainingSurveys)
      .where(eq(trainingSurveys.id, id));

    if (find.length === 0) {
      throw new HttpException(
        'Training record not found',
        HttpStatus.NOT_FOUND,
      );
    }

    return find[0];
  }

  private async saveFiles(files: Express.Multer.File[]): Promise<string[]> {
    if (!files || files.length === 0) return [];

    const savedPaths: string[] = [];
    for (const file of files) {
      const objectName = await this.minioService.upload(file, 'training');
      const fileUrl = this.minioService.getPublicUrl(objectName);
      savedPaths.push(fileUrl);
    }
    return savedPaths;
  }

  private async deleteFile(fileUrl: string) {
    if (!fileUrl) return;

    try {
      // If it's a full URL, we need to extract the part after the bucket name
      if (fileUrl.startsWith('http')) {
        const url = new URL(fileUrl);
        const pathname = url.pathname; // /bucket/folder/filename
        const parts = pathname.split('/').filter(Boolean); // ['bucket', 'folder', 'filename']

        // The first part is the bucket name, the rest is the object name
        if (parts.length >= 2) {
          const objectName = parts.slice(1).join('/');
          await this.minioService.delete(objectName);
          return;
        }
      }

      // If it's not a URL or doesn't match the expected format, pass it as is
      await this.minioService.delete(fileUrl);
    } catch (error) {
      // Fallback if URL parsing fails
      await this.minioService.delete(fileUrl);
    }
  }

  async create(
    createTrainingDto: CreateTrainingDto,
    files: Express.Multer.File[],
    userId: number,
  ) {
    // 1. Guardar fotos

    const photoPaths = await this.saveFiles(files);

    // 2. Extraer solo visitDate para formatearlo.
    // Ya NO extraemos state, municipality, etc. porque no vienen en el DTO.
    const { visitDate, state, municipality, parish, productiveActivityOther, ...rest } =
      createTrainingDto;

    const [newRecord] = await this.drizzle
      .insert(trainingSurveys)
      .values({
        // Insertamos el resto de datos planos y las listas (arrays)
        ...rest,

        // Conversión de fecha
        visitDate: new Date(visitDate),

        // Borra las tildes y cambia el texto a mayusculas
        productiveActivityOther: productiveActivityOther.toUpperCase().normalize("NFD").replace(/[\u0300-\u036f]/g, ""),

        // 3. Asignar fotos de forma segura
        photo1: photoPaths[0] ?? null,
        photo2: photoPaths[1] ?? null,
        photo3: photoPaths[2] ?? null,
        state: Number(state) ?? null,
        municipality: Number(municipality) ?? null,
        parish: Number(parish) ?? null,
        hasTransport: rest.hasTransport === 'true' ? true : false,
        isOpenSpace: rest.isOpenSpace === 'true' ? true : false,
        isExporting: rest.isExporting === 'true' ? true : false,
        createdBy: userId,
        updatedBy: userId,
      })
      .returning();

    return newRecord;
  }

  async update(
    id: number,
    updateTrainingDto: UpdateTrainingDto,
    files: Express.Multer.File[],
    userId: number,
  ) {
    const currentRecord = await this.findOne(id);
    const photoFields = ['photo1', 'photo2', 'photo3'] as const;

    // 1. Guardar fotos nuevas en MinIO
    const newFilePaths = await this.saveFiles(files);

    const updateData: any = { ...updateTrainingDto };

    // 2. Determinar el estado final de las fotos (diff)
    // - Si el DTO tiene un valor (URL existente o ''), lo usamos.
    // - Si el DTO no tiene el campo (undefined), mantenemos el de la DB.
    const finalPhotos: (string | null)[] = photoFields.map((field) => {
      const dtoValue = updateData[field];
      if (dtoValue !== undefined) {
        return dtoValue === '' ? null : dtoValue;
      }
      return currentRecord[field];
    });

    // 3. Asignar los nuevos paths subidos a los slots que quedaron vacíos
    if (newFilePaths.length > 0) {
      let newIdx = 0;
      for (let i = 0; i < 3 && newIdx < newFilePaths.length; i++) {
        if (!finalPhotos[i]) {
          finalPhotos[i] = newFilePaths[newIdx];
          newIdx++;
        }
      }
    }

    // 4. LIMPIEZA: Borrar de MinIO los archivos que ya no están en ningún slot
    const oldPhotos = photoFields
      .map((f) => currentRecord[f])
      .filter((p): p is string => Boolean(p));
    const newPhotosSet = new Set(finalPhotos.filter(Boolean));

    for (const oldPath of oldPhotos) {
      if (!newPhotosSet.has(oldPath)) {
        await this.deleteFile(oldPath);
      }
    }

    // 5. Preparar datos finales para la DB
    updateData.photo1 = finalPhotos[0];
    updateData.photo2 = finalPhotos[1];
    updateData.photo3 = finalPhotos[2];

    if (updateTrainingDto.visitDate) {
      updateData.visitDate = new Date(updateTrainingDto.visitDate);
    }

    // actualizamos el id del usuario que actualizo el registro
    updateData.updatedBy = userId;
    updateData.hasTransport =
      updateTrainingDto.hasTransport === 'true' ? true : false;
    updateData.isOpenSpace =
      updateTrainingDto.isOpenSpace === 'true' ? true : false;
    updateData.isExporting =
      updateTrainingDto.isExporting === 'true' ? true : false;

    const [updatedRecord] = await this.drizzle
      .update(trainingSurveys)
      .set(updateData)
      .where(eq(trainingSurveys.id, id))
      .returning();

    return updatedRecord;
  }

  async remove(id: number) {
    const record = await this.findOne(id);

    // Delete associated files
    if (record.photo1) await this.deleteFile(record.photo1);
    if (record.photo2) await this.deleteFile(record.photo2);
    if (record.photo3) await this.deleteFile(record.photo3);

    const [deletedRecord] = await this.drizzle
      .delete(trainingSurveys)
      .where(eq(trainingSurveys.id, id))
      .returning();

    return {
      message: 'Training record deleted successfully',
      data: deletedRecord,
    };
  }

  // async exportTemplate() {

  //   const templatePath = path.join(
  //     __dirname,
  //     'export_template',
  //     'excel.osp.xlsx',
  //   );
  //   const templateBuffer = fs.readFileSync(templatePath);

  //   const workbook: any = await XlsxPopulate.fromDataAsync(templateBuffer);
  //   const sheet = workbook.sheet(0);

  // const records = await this.drizzle
  //       .select({
  //         coorFullName: trainingSurveys.coorFullName,
  //         visitDate: trainingSurveys.visitDate,
  //         stateName: states.name,
  //         municipalityName: municipalities.name,
  //         parishName: parishes.name,
  //         communeName: trainingSurveys.communeName,
  //         siturCodeCommune: trainingSurveys.siturCodeCommune,
  //         communalCouncil: trainingSurveys.communalCouncil,
  //         siturCodeCommunalCouncil: trainingSurveys.siturCodeCommunalCouncil,
  //         productiveActivity: trainingSurveys.productiveActivity,
  //         ospName: trainingSurveys.ospName,
  //         ospAddress: trainingSurveys.ospAddress,
  //         ospRif: trainingSurveys.ospRif,
  //         ospType: trainingSurveys.ospType,
  //         currentStatus: trainingSurveys.currentStatus,
  //         companyConstitutionYear: trainingSurveys.companyConstitutionYear,
  //         ospResponsibleFullname: trainingSurveys.ospResponsibleFullname,
  //         ospResponsibleCedula: trainingSurveys.ospResponsibleCedula,
  //         ospResponsibleRif: trainingSurveys.ospResponsibleRif,
  //         ospResponsiblePhone: trainingSurveys.ospResponsiblePhone,
  //         ospResponsibleEmail: trainingSurveys.ospResponsibleEmail,
  //         civilState: trainingSurveys.civilState,
  //         familyBurden: trainingSurveys.familyBurden,
  //         numberOfChildren: trainingSurveys.numberOfChildren,
  //         generalObservations: trainingSurveys.generalObservations,
  //         paralysisReason: trainingSurveys.paralysisReason,
  //         productList: trainingSurveys.productList,
  //         infrastructureMt2: trainingSurveys.infrastructureMt2,
  //         photo1: trainingSurveys.photo1,
  //         photo2: trainingSurveys.photo2,
  //         photo3: trainingSurveys.photo3,
  //       })
  //       .from(trainingSurveys)
  //       .leftJoin(states, eq(trainingSurveys.state, states.id))
  //       .leftJoin(
  //         municipalities,
  //         eq(trainingSurveys.municipality, municipalities.id),
  //       )
  //       .leftJoin(parishes, eq(trainingSurveys.parish, parishes.id))
  //       .execute();

  //   let currentRow = 2;

  //   for (const record of records) {
  //     const date = new Date(record.visitDate);
  //     const dateStr = date.toLocaleDateString('es-VE');
  //     const timeStr = date.toLocaleTimeString('es-VE');

  //     sheet.cell(`A${currentRow}`).value(record.coorFullName);
  //     sheet.cell(`C${currentRow}`).value(dateStr);
  //     sheet.cell(`D${currentRow}`).value(timeStr);
  //     sheet.cell(`E${currentRow}`).value(record.stateName || '');
  //     sheet.cell(`F${currentRow}`).value(record.municipalityName || '');
  //     sheet.cell(`G${currentRow}`).value(record.parishName || '');
  //     sheet.cell(`H${currentRow}`).value(record.communeName);
  //     sheet.cell(`I${currentRow}`).value(record.siturCodeCommune);
  //     sheet.cell(`J${currentRow}`).value(record.communalCouncil);
  //     sheet.cell(`K${currentRow}`).value(record.siturCodeCommunalCouncil);
  //     sheet.cell(`L${currentRow}`).value(record.productiveActivity);
  //     sheet.cell(`M${currentRow}`).value(''); // requerimiento financiero description
  //     sheet.cell(`N${currentRow}`).value(record.ospName);
  //     sheet.cell(`O${currentRow}`).value(record.ospAddress);
  //     sheet.cell(`P${currentRow}`).value(record.ospRif);
  //     sheet.cell(`Q${currentRow}`).value(record.ospType);
  //     sheet.cell(`R${currentRow}`).value(record.currentStatus);
  //     sheet.cell(`S${currentRow}`).value(record.companyConstitutionYear);

  //     const products = (record.productList as any[]) || [];
  //     const totalProducers = products.reduce(
  //       (sum, p) =>
  //         sum + (Number(p.menCount) || 0) + (Number(p.womenCount) || 0),
  //       0,
  //     );
  //     const productsDesc = products.map((p) => p.name).join(', ');

  //     sheet.cell(`T${currentRow}`).value(totalProducers);
  //     sheet.cell(`U${currentRow}`).value(productsDesc);
  //     sheet.cell(`V${currentRow}`).value(record.infrastructureMt2);
  //     sheet.cell(`W${currentRow}`).value('');
  //     sheet.cell(`X${currentRow}`).value(record.paralysisReason || '');
  //     sheet.cell(`Y${currentRow}`).value(record.ospResponsibleFullname);
  //     sheet.cell(`Z${currentRow}`).value(record.ospResponsibleCedula);
  //     sheet.cell(`AA${currentRow}`).value(record.ospResponsibleRif);
  //     sheet.cell(`AB${currentRow}`).value(record.ospResponsiblePhone);
  //     sheet.cell(`AC${currentRow}`).value(record.ospResponsibleEmail);
  //     sheet.cell(`AD${currentRow}`).value(record.civilState);
  //     sheet.cell(`AE${currentRow}`).value(record.familyBurden);
  //     sheet.cell(`AF${currentRow}`).value(record.numberOfChildren);
  //     sheet.cell(`AG${currentRow}`).value(record.generalObservations || '');

  //     sheet.cell(`AH${currentRow}`).value(record.photo1 || '');
  //     sheet.cell(`AI${currentRow}`).value(record.photo2 || '');
  //     sheet.cell(`AJ${currentRow}`).value(record.photo3 || '');

  //     currentRow++;
  //   }

  //   return await workbook.outputAsync();
  // }

  // async exportTemplate(id: number) {
  //   // Validar que el registro exista
  //   const exist = await this.findOne(id);
  //   if (!exist) throw new NotFoundException(`No se encontro el registro`);

  //   // Obtener los datos del registro
  //   const records = await this.drizzle
  //     .select({
  //       // id: trainingSurveys.id,
  //       visitDate: trainingSurveys.visitDate,
  //       ospName: trainingSurveys.ospName,
  //       productiveSector: trainingSurveys.productiveSector,
  //       ospAddress: trainingSurveys.ospAddress,
  //       ospRif: trainingSurveys.ospRif,

  //       siturCodeCommune: trainingSurveys.siturCodeCommune,
  //       communeEmail: trainingSurveys.communeEmail,
  //       communeRif: trainingSurveys.communeRif,
  //       communeSpokespersonName: trainingSurveys.communeSpokespersonName,
  //       communeSpokespersonPhone: trainingSurveys.communeSpokespersonPhone,

  //       siturCodeCommunalCouncil: trainingSurveys.siturCodeCommunalCouncil,
  //       communalCouncilRif: trainingSurveys.communalCouncilRif,
  //       communalCouncilSpokespersonName:
  //         trainingSurveys.communalCouncilSpokespersonName,
  //       communalCouncilSpokespersonPhone:
  //         trainingSurveys.communalCouncilSpokespersonPhone,

  //       ospType: trainingSurveys.ospType,
  //       productiveActivity: trainingSurveys.productiveActivity, // Sector Productivo
  //       companyConstitutionYear: trainingSurveys.companyConstitutionYear,
  //       infrastructureMt2: trainingSurveys.infrastructureMt2,

  //       hasTransport: trainingSurveys.hasTransport,
  //       structureType: trainingSurveys.structureType,
  //       isOpenSpace: trainingSurveys.isOpenSpace,

  //       ospResponsibleFullname: trainingSurveys.ospResponsibleFullname,
  //       ospResponsibleCedula: trainingSurveys.ospResponsibleCedula,
  //       ospResponsiblePhone: trainingSurveys.ospResponsiblePhone,

  //       productList: trainingSurveys.productList,
  //       equipmentList: trainingSurveys.equipmentList,
  //       productionList: trainingSurveys.productionList,

  //       // photo1: trainingSurveys.photo1
  //     })
  //     .from(trainingSurveys)
  //     .where(eq(trainingSurveys.id, id));
  //   // .leftJoin(states, eq(trainingSurveys.state, states.id))
  //   // .leftJoin(municipalities,eq(trainingSurveys.municipality, municipalities.id))
  //   // .leftJoin(parishes, eq(trainingSurveys.parish, parishes.id))

  //   let equipmentList: any[] = Array.isArray(records[0].equipmentList)
  //     ? records[0].equipmentList
  //     : [];
  //   let productList: any[] = Array.isArray(records[0].productList)
  //     ? records[0].productList
  //     : [];
  //   let productionList: any[] = Array.isArray(records[0].productionList)
  //     ? records[0].productionList
  //     : [];

  //   console.log('equipmentList', equipmentList);
  //   console.log('productList', productList);
  //   console.log('productionList', productionList);

  //   let equipmentListArray: any[] = [];
  //   let productListArray: any[] = [];
  //   let productionListArray: any[] = [];

  //   const equipmentListCount = equipmentList.length;
  //   for (let i = 0; i < equipmentListCount; i++) {
  //     equipmentListArray.push([
  //       equipmentList[i].machine,
  //       '',
  //       equipmentList[i].quantity,
  //     ]);
  //   }

  //   const productListCount = productList.length;
  //   for (let i = 0; i < productListCount; i++) {
  //     productListArray.push([
  //       productList[i].productName,
  //       productList[i].dailyCount,
  //       productList[i].weeklyCount,
  //       productList[i].monthlyCount,
  //     ]);
  //   }

  //   const productionListCount = productionList.length;
  //   for (let i = 0; i < productionListCount; i++) {
  //     productionListArray.push([
  //       productionList[i].rawMaterial,
  //       '',
  //       productionList[i].quantity,
  //     ]);
  //   }

  //   // Ruta de la plantilla
  //   const templatePath = path.join(
  //     __dirname,
  //     'export_template',
  //     'excel.osp.xlsx',
  //   );

  //   // Cargar la plantilla
  //   const book = await XlsxPopulate.fromFileAsync(templatePath);

  //   const isoString = records[0].visitDate;
  //   const dateObj = new Date(isoString);
  //   const fechaFormateada = dateObj.toLocaleDateString('es-ES');
  //   const horaFormateada = dateObj.toLocaleTimeString('es-ES', {
  //     hour: '2-digit',
  //     minute: '2-digit',
  //   });

  //   // Llenar los datos
  //   book.sheet(0).cell('A6').value(records[0].productiveSector);
  //   book.sheet(0).cell('D6').value(records[0].ospName);
  //   book.sheet(0).cell('L5').value(fechaFormateada);
  //   book.sheet(0).cell('L6').value(horaFormateada);
  //   book.sheet(0).cell('B10').value(records[0].ospAddress);
  //   book.sheet(0).cell('C11').value(records[0].communeEmail);
  //   book.sheet(0).cell('C12').value(records[0].communeSpokespersonName);
  //   book.sheet(0).cell('G11').value(records[0].communeRif);
  //   book.sheet(0).cell('G12').value(records[0].communeSpokespersonPhone);
  //   book.sheet(0).cell('C13').value(records[0].siturCodeCommune);
  //   book.sheet(0).cell('G13').value(records[0].siturCodeCommunalCouncil);
  //   book.sheet(0).cell('G14').value(records[0].communalCouncilRif);
  //   book.sheet(0).cell('C15').value(records[0].communalCouncilSpokespersonName);
  //   book
  //     .sheet(0)
  //     .cell('G15')
  //     .value(records[0].communalCouncilSpokespersonPhone);
  //   book.sheet(0).cell('C16').value(records[0].ospType);
  //   book.sheet(0).cell('C17').value(records[0].ospName);
  //   book.sheet(0).cell('C18').value(records[0].productiveActivity);
  //   book.sheet(0).cell('C19').value('Proveedores');
  //   book.sheet(0).cell('C20').value(records[0].companyConstitutionYear);
  //   book.sheet(0).cell('C21').value(records[0].infrastructureMt2);
  //   book.sheet(0).cell('G17').value(records[0].ospRif);

  //   book
  //     .sheet(0)
  //     .cell(records[0].hasTransport === true ? 'J19' : 'L19')
  //     .value('X');
  //   book
  //     .sheet(0)
  //     .cell(records[0].structureType === 'CASA' ? 'J20' : 'L20')
  //     .value('X');
  //   book
  //     .sheet(0)
  //     .cell(records[0].isOpenSpace === true ? 'J21' : 'L21')
  //     .value('X');

  //   book.sheet(0).cell('A24').value(records[0].ospResponsibleFullname);
  //   book.sheet(0).cell('C24').value(records[0].ospResponsibleCedula);
  //   book.sheet(0).cell('E24').value(records[0].ospResponsiblePhone);

  //   book.sheet(0).cell('J24').value('N Femenino');
  //   book.sheet(0).cell('L24').value('N Masculino');

  //   book
  //     .sheet(0)
  //     .range(`A28:C${equipmentListCount + 28}`)
  //     .value(equipmentListArray);
  //   book
  //     .sheet(0)
  //     .range(`E28:G${productionListCount + 28}`)
  //     .value(productionListArray);
  //   book
  //     .sheet(0)
  //     .range(`I28:L${productListCount + 28}`)
  //     .value(productListArray);

  //   return book.outputAsync();
  // }
}
