import {
  Injectable,
  Logger,
  NotFoundException,
  Scope,
  UnprocessableEntityException,
} from '@nestjs/common';
import { BaseReportService } from '../shared/services/base-report.service';
import { CreateReportExportDto } from '../shared/dto/report-export.create.dto';
import {
  GetReportExportDto,
  GetReportExportFileNameDto,
  GetReportExportProcessingDto,
} from '../shared/dto/report-export.get.dto';
import { InjectDataSource, InjectRepository } from '@nestjs/typeorm';
import { ExportReportHistoryModel } from '../shared/models/export-report-history.model';
import { CONNECTION_NAME } from 'src/core/strings/constants/base.constants';
import { DataSource, Repository } from 'typeorm';
import { ReportConfigs } from '../shared/configs';
import { ReportConfigEntity } from '../shared/entities/report-config.entity';
import { ReportQueryBuilder, capitalizeEachWord } from '../shared/helpers';
import { DATA_FORMAT, REPORT_HISTORY_STATUS } from '../shared/constant';

import * as fs from 'fs';
import * as path from 'path';
import * as ExcelJS from 'exceljs';
import { roundingCurrency } from '../shared/helpers';
import { createPaginationMeta } from 'src/core/response/domain/utils/pagination-meta.helper';
import * as moment from 'moment';

@Injectable({ scope: Scope.REQUEST })
export class ReportExportService extends BaseReportService {
  private readonly logger = new Logger(ReportExportService.name);
  private readonly exportLimitPartition = process.env.EXPORT_LIMIT_PARTITION
    ? parseInt(process.env.EXPORT_LIMIT_PARTITION)
    : 100;

  constructor(
    @InjectDataSource(CONNECTION_NAME.DEFAULT)
    private dataSource: DataSource,

    @InjectRepository(ExportReportHistoryModel, CONNECTION_NAME.DEFAULT)
    private exportHistoryRepo: Repository<ExportReportHistoryModel>,
  ) {
    super();
  }

  getReportConfigByUniqueName(group_name, unique_name): ReportConfigEntity {
    return ReportConfigs.find(
      (item) =>
        item.unique_name === unique_name && item.group_name === group_name,
    );
  }

  changeTimeZone(date, timeZone) {
    if (typeof date === 'string') {
      return new Date(
        new Date(date).toLocaleString('en-US', {
          timeZone,
        }),
      );
    }

    return new Date(
      date.toLocaleString('en-US', {
        timeZone,
      }),
    );
  }

  async create(body: CreateReportExportDto) {
    let historyDataID = undefined;
    try {
      const config = this.getReportConfigByUniqueName(
        body.group_name,
        body.unique_name,
      );
      const query_model = body.query_model;
      const columnState = body.column_state;
      const timeZone = body.time_zone;
      const fName = body.file_name;
      const defaultData = this.injectDefaultColumnCreate({});

      query_model.groupKeys = []; // set [] on report only for get all data;
      const DATA_SOURCE_NAME = 'dataSource';

      const builder = new ReportQueryBuilder(config, query_model);
      const SQL_EXPORT = builder.getSqlExport();
      const SQL_EXPORT_COUNT = builder.getSqlCountExport();

      const count = await this[`${DATA_SOURCE_NAME}`].query(SQL_EXPORT_COUNT);
      const totalRow = parseInt(count[0].count);

      const limit = this.exportLimitPartition;

      const partitionOffset = Array.from(
        { length: Math.ceil(totalRow / limit) },
        (_, i) => i * limit,
      );

      const defaultFileName = fName ?? config.label;
      const fileName = `${defaultFileName} (${Number(new Date())})`;

      const exportHistory = {
        id: undefined,
        group_name: config.group_name,
        unique_name: config.unique_name,
        label: config.label,
        file_name: defaultFileName,
        file_url: null,
        total_data: totalRow,
        processing_data: 0,
        status: REPORT_HISTORY_STATUS.PROCESSING,
        last_process_offset: 0,
        last_process_limit: limit,
        query_export: '',
        ...defaultData,
      };

      this.logger.verbose(`EXPORT ${fileName} VIA STREAMING`);
      this.logger.verbose(`EXPORT_LIMIT_PARTITION = ${limit}`);

      const directory = './uploads/report-data/';
      const filePath = path.join(directory, fileName);
      if (!fs.existsSync(directory)) {
        fs.mkdirSync(directory, { recursive: true });
      }
      const workbookOptions = {
        filename: filePath + '.xlsx',
        useStyles: true,
      };

      exportHistory.file_url = fileName + '.xlsx';

      //save export history
      const historySave: any = await this.exportHistoryRepo.save(
        exportHistory as any,
      );
      exportHistory.id = historySave.id;
      historyDataID = historySave.id;

      // Create a new workbook
      const workbook = new ExcelJS.stream.xlsx.WorkbookWriter(workbookOptions);
      // Create a new worksheet
      const reportSheet = workbook.addWorksheet('Report');

      let configColumns = config.column_configs;
      const rowGroups = query_model?.rowGroupCols ?? [];

      if (rowGroups.length > 0) {
        const valueCol = query_model?.valueCols ?? [];
        configColumns = [...rowGroups, ...valueCol].map((i) => {
          return config.column_configs.find((conf) => conf.column === i.id);
        });
      }

      const columnStateIds = [
        ...rowGroups.map((i) => i.id),
        ...columnState.map((i) => i.colId),
      ];

      const newConfigColumn = columnStateIds
        .map((i) => {
          const findData = configColumns.find((col) => col.column === i);
          return findData;
        })
        .filter(Boolean);

      reportSheet.columns = newConfigColumn.map((conf) => {
        const isDate =
          conf.format === DATA_FORMAT.DATE_EPOCH ||
          conf.format === DATA_FORMAT.DATE_TIMESTAMP;
        const isCurrency =
          conf.format === DATA_FORMAT.CURRENCY ||
          conf.format === DATA_FORMAT.MINUS_CURRENCY;
        const isNumber = conf.format === DATA_FORMAT.NUMBER;

        const excelColumn = {
          header: conf.label,
          key: conf.column,
          width: 20,
        };

        if (isDate) {
          Object.assign(excelColumn, { style: { numFmt: 'dd/mm/yyyy' } });
        } else if (isCurrency) {
          Object.assign(excelColumn, { style: { numFmt: '0.00' } });
        } else if (isNumber) {
          Object.assign(excelColumn, { style: { numFmt: '' } });
        }

        return excelColumn;
      });

      // Making first line in excel bold
      reportSheet.getRow(1).eachCell((cell) => {
        cell.font = { bold: true };
      });

      for (const offset of partitionOffset) {
        const limitSql = ` limit ${limit} offset ${offset} `;
        const newSqlExport = SQL_EXPORT + limitSql;

        const data = await this[`${DATA_SOURCE_NAME}`].query(newSqlExport);

        //update export history
        exportHistory.processing_data += data.length;
        exportHistory.updated_at = Number(new Date());
        exportHistory.last_process_offset = offset;
        exportHistory.query_export = newSqlExport;

        await this.exportHistoryRepo.save(exportHistory as any);

        for (const item of data) {
          const realItem = {};
          for (const itemKey of Object.keys(item)) {
            const confCol = configColumns.find((c) => c.column === itemKey);
            const isStatus = confCol.format === DATA_FORMAT.STATUS;
            const isDate = confCol.format === DATA_FORMAT.DATE_EPOCH;
            const isDateTimestamp =
              confCol.format === DATA_FORMAT.DATE_TIMESTAMP;
            const isNumber = confCol.format === DATA_FORMAT.NUMBER;
            const isCurrency = confCol.format === DATA_FORMAT.CURRENCY;
            const isMinusCurrency =
              confCol.format === DATA_FORMAT.MINUS_CURRENCY;
            const isBoolean = confCol.format === DATA_FORMAT.BOOLEAN;
            const isPercentage = confCol.format === DATA_FORMAT.PERCENTAGE;

            const isSetInitNull = isNumber || isCurrency || isMinusCurrency;

            const isTextUpperCase =
              confCol.format === DATA_FORMAT.TEXT_UPPERCASE;
            const isTextLowerCase =
              confCol.format === DATA_FORMAT.TEXT_LOWERCASE;

            if (isStatus) {
              if (item[itemKey]) {
                Object.assign(realItem, {
                  [`${itemKey}`]: capitalizeEachWord(
                    item[itemKey].split('_').join(' '),
                  ),
                });
              }
            } else if (isDate) {
              if (item[itemKey]) {
                Object.assign(realItem, {
                  [`${itemKey}`]: this.changeTimeZone(
                    new Date(Number(item[itemKey])),
                    timeZone,
                  ),
                });
              }
            } else if (isDateTimestamp) {
              if (item[itemKey]) {
                Object.assign(realItem, {
                  [`${itemKey}`]: this.changeTimeZone(
                    new Date(item[itemKey]),
                    timeZone,
                  ),
                });
              }
            } else if (isSetInitNull) {
              const realValue = item[itemKey] ?? 0;
              let valueItem = realValue;
              if (isCurrency) {
                valueItem = roundingCurrency(realValue);
              } else if (isMinusCurrency) {
                valueItem = roundingCurrency(realValue) * -1;
              }

              Object.assign(realItem, { [`${itemKey}`]: Number(valueItem) });
            } else if (isPercentage) {
              const realValue = item[itemKey]
                ? `${item[itemKey]}%`
                : item[itemKey];
              Object.assign(realItem, { [`${itemKey}`]: realValue });
            } else if (isBoolean) {
              let realValue = '';
              if (item[itemKey] === true || item[itemKey] === 1)
                realValue = 'Yes';
              else if (item[itemKey] === false || item[itemKey] === 0)
                realValue = 'No';
              Object.assign(realItem, { [`${itemKey}`]: realValue });
            } else if (isTextUpperCase) {
              Object.assign(realItem, {
                [`${itemKey}`]: item[itemKey]?.toUpperCase(),
              });
            } else if (isTextLowerCase) {
              Object.assign(realItem, {
                [`${itemKey}`]: item[itemKey]?.toLowerCase(),
              });
            } else {
              Object.assign(realItem, { [`${itemKey}`]: item[itemKey] });
            }
          }

          reportSheet.addRow(realItem).commit();
        }
      }

      reportSheet.commit();
      workbook.commit();

      // update file_url on data export history using scrip bellow
      // exportHistory.file_url = fileName + '.xlsx';
      exportHistory.total_data = exportHistory.processing_data;
      exportHistory.status = 'done';
      exportHistory.updated_at = Number(new Date());
      await this.exportHistoryRepo.save(exportHistory as any);
    } catch (error) {
      this.exportHistoryRepo.update(historyDataID, {
        status: REPORT_HISTORY_STATUS.FAILED,
      });
      this.logger.error(error);
      return new Error(error);
    }
  }

  async getAll(query: GetReportExportDto) {
    const modelName = ExportReportHistoryModel.name;

    const page = query.page;
    const limit = query.limit;

    const creator_id = this.getUser().id;
    const group_names = query.group_names;
    const unique_names = query.unique_names;
    const statuses = query.statuses;

    const qb = this.exportHistoryRepo
      .createQueryBuilder(modelName)
      .where((query) => {
        if (unique_names) {
          query.andWhere(`unique_name IN (:...unique_names)`, { unique_names });
        }
        if (group_names) {
          query.andWhere(`group_name IN (:...group_names)`, { group_names });
        }

        if (statuses) {
          query.andWhere(`status IN (:...statuses)`, { statuses });
        }

        query.andWhere(`creator_id = :creator_id`, { creator_id });
      })
      .orderBy(`${modelName}.created_at`, 'DESC');

    const [data, total] = await qb
      .take(+limit)
      .skip(+limit * +page - +limit)
      .getManyAndCount();

    const meta = createPaginationMeta(page, limit, data.length, total);

    return { data, meta };
  }

  async delete(id: string) {
    const findData = await this.exportHistoryRepo.findOneBy({ id });
    if (findData && findData?.file_url) {
      try {
        const directory = './uploads/report-data/';
        const filePath = path.join(directory, findData.file_url);
        if (fs.existsSync(filePath)) {
          fs.unlinkSync(filePath);
          await this.exportHistoryRepo.delete(id);
          this.logger.warn(
            `Successfully deleted the ${findData.file_url} file`,
          );
          return { success: true, message: 'File deleted successfully' };
        } else {
          throw new NotFoundException('File not found');
        }
      } catch (error) {
        throw new UnprocessableEntityException('File could not be deleted');
      }
    }
    throw new UnprocessableEntityException();
  }

  async updateFailedData(group_names, unique_names) {
    const creator_id = this.getUser().id;
    const aMinutesAgo = moment().subtract(5, 'minutes').valueOf();

    await this.exportHistoryRepo
      .createQueryBuilder()
      .update(ExportReportHistoryModel)
      .set({
        status: REPORT_HISTORY_STATUS.FAILED,
        updated_at: moment().valueOf(),
      })
      .where((query) => {
        if (group_names) {
          query.andWhere(`group_name IN (:...group_names)`, { group_names });
        }
        if (unique_names) {
          query.andWhere(`unique_name IN (:...unique_names)`, { unique_names });
        }
        query.andWhere(`status = :status`, {
          status: REPORT_HISTORY_STATUS.PROCESSING,
        });
        query.andWhere(`updated_at < :aMinutesAgo`, { aMinutesAgo });
        query.andWhere(`creator_id = :creator_id`, { creator_id });
      })
      .execute();
  }

  async getAllProcessing(query: GetReportExportProcessingDto) {
    const creator_id = this.getUser().id;
    const modelName = ExportReportHistoryModel.name;

    const group_names = query.group_names;
    const unique_names = query.unique_names;

    await this.updateFailedData(group_names, unique_names);
    const aMinutesAgo = moment().subtract(100, 'seconds').valueOf();

    const qb = this.exportHistoryRepo
      .createQueryBuilder(modelName)
      .where((query) => {
        if (group_names) {
          query.andWhere(`unique_name IN (:...unique_names)`, { unique_names });
        }
        if (unique_names) {
          query.andWhere(`group_name IN (:...group_names)`, { group_names });
        }

        query.andWhere(`status IN (:...status)`, {
          status: ['processing', 'failed', 'done'],
        });
        query.andWhere(`updated_at > :aMinutesAgo`, { aMinutesAgo });
        query.andWhere(`creator_id = :creator_id`, { creator_id });
      })
      .orderBy(`${modelName}.created_at`, 'DESC');

    const data = await qb.getMany();

    return {
      data: data,
    };
  }

  async getListHistoryFileName(query: GetReportExportFileNameDto) {
    const modelName = ExportReportHistoryModel.name;

    const creator_id = this.getUser().id;
    const unique_names = query.unique_names;
    const group_names = query.group_names;

    const qb = this.exportHistoryRepo
      .createQueryBuilder(modelName)
      .select(`${modelName}.file_name`)
      .where((query) => {
        if (unique_names) {
          query.andWhere(`unique_name IN (:...unique_names)`, { unique_names });
        }
        if (group_names) {
          query.andWhere(`group_name IN (:...group_names)`, { group_names });
        }

        query.andWhere(`creator_id = :creator_id`, { creator_id });
      })
      .distinct(true);

    const newData = await qb.getRawMany();
    return newData.map((el) => el.ExportReportHistoryModel_file_name);
  }
}
