import {
  BadRequestException,
  Injectable,
  NotFoundException,
} from '@nestjs/common';
import { CreateOrderDto } from './dto/create-order.dto';
import { UpdateOrderDto } from './dto/update-order.dto';
import { InjectRepository } from '@nestjs/typeorm';
import { Order } from '@/entities/order.entity';
import { DataSource, Repository } from 'typeorm';
import { OrderItem } from '@/entities/order-item.entity';
import { DefaultRoles } from '@/entities/role.entity';
import { HandwrittenBill } from '@/entities/handwritten-bill.entity';
import { UpdateReleaseDate } from './dto/update-release-date.dto';
import * as ExcelJS from 'exceljs';

@Injectable()
export class OrderService {
  constructor(
    private dataSource: DataSource,
    @InjectRepository(Order)
    private orderRepository: Repository<Order>,
    @InjectRepository(OrderItem)
    private orderItemRepository: Repository<OrderItem>,
  ) { }

  async create(createOrderDto: CreateOrderDto, user: any): Promise<Order> {
    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.connect();
    await queryRunner.startTransaction();

    try {
      let finalOrderCode: string;
      let createdAt: Date;

      if (createOrderDto.order_code) {
        const orderCodeNum = Number(createOrderDto.order_code);

        const isInHandwritten = await this.isOrderCodeInHandwrittenRange(
          queryRunner,
          orderCodeNum,
        );

        if (!isInHandwritten) {
          throw new BadRequestException(
            `Mã đơn ${createOrderDto.order_code} không nằm trong khoảng đơn viết tay. Không thể tạo.`,
          );
        }

        const existingOrder = await queryRunner.manager
          .getRepository(Order)
          .createQueryBuilder('orders')
          .where('orders.order_code = :orderCode', {
            orderCode: createOrderDto.order_code,
          })
          .getOne();

        if (existingOrder) {
          throw new BadRequestException(
            `Mã đơn ${createOrderDto.order_code} đã được sử dụng.`,
          );
        }

        finalOrderCode = createOrderDto.order_code;

        createdAt = createOrderDto.created_at
          ? new Date(createOrderDto.created_at)
          : new Date();
      } else {
        const reservedRanges = await queryRunner.manager
          .getRepository(HandwrittenBill)
          .createQueryBuilder('handwritten_bill')
          .orderBy('handwritten_bill.from', 'ASC')
          .getMany();

        let candidateCode = 1;
        let foundCode = false;

        while (!foundCode) {
          let isInRange = false;

          for (const range of reservedRanges) {
            if (candidateCode >= range.from && candidateCode <= range.to) {
              isInRange = true;
              candidateCode = range.to + 1;
              break;
            }
          }

          if (!isInRange) {
            const existingOrder = await queryRunner.manager
              .getRepository(Order)
              .createQueryBuilder('orders')
              .where('orders.order_code = :orderCode', {
                orderCode: String(candidateCode),
              })
              .getOne();

            if (!existingOrder) {
              foundCode = true;
            } else {
              candidateCode++;
            }
          }
        }

        finalOrderCode = String(candidateCode);
        createdAt = new Date();
      }

      const order = queryRunner.manager.create(Order, {
        ...createOrderDto,
        order_code: finalOrderCode,
        created_at: createdAt,
        author_id: user.id,
        is_handwritten: createOrderDto.is_handwritten ?? false,
      });

      if (createOrderDto.items && createOrderDto.items.length > 0) {
        order.items = createOrderDto.items.map((itemDto) =>
          queryRunner.manager.create(OrderItem, itemDto),
        );
      } else {
        throw new BadRequestException('Vui lòng nhập hàng hoá');
      }

      await queryRunner.manager.save(Order, order);
      await queryRunner.commitTransaction();
      return order;
    } catch (err) {
      await queryRunner.rollbackTransaction();
      throw err;
    } finally {
      await queryRunner.release();
    }
  }
  private async isOrderCodeInHandwrittenRange(
    queryRunner: any,
    orderCode: number,
  ): Promise<boolean> {
    const overlap = await queryRunner.manager
      .getRepository(HandwrittenBill)
      .createQueryBuilder('handwritten_bill')
      .where(':code BETWEEN handwritten_bill.from AND handwritten_bill.to', {
        code: orderCode,
      })
      .getOne();

    return !!overlap;
  }

  async findAll(
    page: number = 1,
    limit: number = 10,
    orderCode?: string,
    searchName?: string,
    user?: any,
  ): Promise<{ data: Order[]; total: number; page: number; limit: number }> {
    const roleIds = user.role.map((item) => item.id);
    const isCustomer = roleIds.includes(DefaultRoles.CUSTOMER);

    const qb = this.orderRepository
      .createQueryBuilder('orders')
      .leftJoin('orders.author', 'author')
      .addSelect(['author.id', 'author.name']);

    if (isCustomer) {
      qb.andWhere('orders.author_id = :author_id', {
        author_id: user.id,
      });
    }

    if (orderCode && orderCode.trim()) {
      qb.andWhere('orders.order_code LIKE :orderCode', {
        orderCode: `%${orderCode.trim()}%`,
      });
    }

    if (searchName && searchName.trim()) {
      qb.andWhere(
        '(orders.sender_company LIKE :searchName OR orders.receiver_company LIKE :searchName)',
        {
          searchName: `%${searchName.trim()}%`,
        }
      );
    }

    const [allData, total] = await qb
      .leftJoinAndSelect('orders.tracking_events', 'tracking')
      .getManyAndCount();

    allData.sort((a, b) => {
      const aCode = Number(a.order_code);
      const bCode = Number(b.order_code);
      if (aCode === bCode) {
        return new Date(b.created_at).getTime() - new Date(a.created_at).getTime();
      }
      return bCode - aCode;
    });

    const startIndex = (page - 1) * limit;
    const endIndex = startIndex + limit;
    const data = allData.slice(startIndex, endIndex);

    return {
      data,
      total,
      page,
      limit,
    };
  }

  async findOne(id: number, user?: any): Promise<Order> {
    const roleIds = user.role.map((item) => item.id);
    const isCustomer = roleIds.includes(DefaultRoles.CUSTOMER);

    let order;

    if (isCustomer) {
      order = await this.orderRepository
        .createQueryBuilder('orders')
        .leftJoinAndSelect('orders.items', 'items')
        .leftJoinAndSelect('orders.tracking_events', 'tracking')
        .where('orders.id = :id AND orders.author_id = :userId', {
          id,
          userId: user.id,
        })
        .orderBy('tracking.created_at', 'DESC')
        .getOne();
    } else {
      order = await this.orderRepository
        .createQueryBuilder('orders')
        .leftJoinAndSelect('orders.items', 'items')
        .leftJoinAndSelect('orders.tracking_events', 'tracking')
        .where('orders.id = :id', { id })
        .orderBy('tracking.created_at', 'DESC')
        .getOne();
    }

    if (!order) {
      throw new NotFoundException(`Order không tồn tại`);
    }

    return order;
  }

  async update(
    id: number,
    updateOrderDto: UpdateOrderDto,
  ): Promise<Order | undefined> {
    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.connect();
    await queryRunner.startTransaction();

    try {
      const existingOrder = await queryRunner.manager.findOne(Order, {
        where: { id },
        relations: ['items'],
      });

      if (!existingOrder) {
        throw new NotFoundException(`Order không tồn tại`);
      }

      Object.assign(existingOrder, updateOrderDto);

      if (updateOrderDto.items && Array.isArray(updateOrderDto.items)) {
        const incomingItemIds = new Set<number>();
        const itemsToProcess: OrderItem[] = [];

        for (const itemDto of updateOrderDto.items) {
          let item;

          if (itemDto.id) {
            item = await queryRunner.manager.findOne(OrderItem, {
              where: { id: itemDto.id, order_id: id },
            });

            if (item) {
              Object.assign(item, itemDto);
              incomingItemIds.add(item.id);
            } else {
              item = queryRunner.manager.create(OrderItem, {
                ...itemDto,
                order_id: id,
                order: existingOrder,
              });
            }
          } else {
            item = queryRunner.manager.create(OrderItem, {
              ...itemDto,
              order_id: id,
              order: existingOrder,
            });
          }

          itemsToProcess.push(item);
        }

        existingOrder.items = await queryRunner.manager.save(
          OrderItem,
          itemsToProcess,
        );

        if (incomingItemIds.size > 0) {
          await queryRunner.manager
            .createQueryBuilder()
            .delete()
            .from(OrderItem)
            .where('order_id = :orderId AND id NOT IN (:...itemIds)', {
              orderId: id,
              itemIds:
                incomingItemIds.size > 0 ? Array.from(incomingItemIds) : [0],
            })
            .execute();
        } else {
          await queryRunner.manager
            .createQueryBuilder()
            .delete()
            .from(OrderItem)
            .where('order_id = :orderId', { orderId: id })
            .execute();
        }
      } else if (updateOrderDto.items === null) {
        await queryRunner.manager
          .createQueryBuilder()
          .delete()
          .from(OrderItem)
          .where('order_id = :orderId', { orderId: id })
          .execute();

        existingOrder.items = [];
      }

      const updatedOrder = await queryRunner.manager.save(Order, existingOrder);

      await queryRunner.commitTransaction();

      const data = await this.orderRepository.findOne({
        where: { id: updatedOrder.id },
        relations: ['items', 'tracking_events'],
      });

      if (!data) {
        throw new NotFoundException(`Order không tồn tại sau khi cập nhật`);
      }

      return data;
    } catch (err) {
      await queryRunner.rollbackTransaction();
      throw err;
    } finally {
      await queryRunner.release();
    }
  }

  async remove(id: number) {
    const order = await this.orderRepository.findOne({ where: { id: id } });
    if (!order) {
      throw new NotFoundException(`Order không tồn tại`);
    }
    await this.orderRepository.remove(order);
    return { message: 'Xoá thành công' };
  }

  async validateOrderCode(orderCode: string): Promise<boolean> {
    const overlap = await this.dataSource
      .getRepository(HandwrittenBill)
      .createQueryBuilder('handwritten_bill')
      .where(
        ':orderCode BETWEEN handwritten_bill.from AND handwritten_bill.to',
        { orderCode },
      )
      .getOne();

    return !!overlap;
  }

  async updateReleaseDate(id: number, updateReleaseDate: UpdateReleaseDate) {
    const order = await this.orderRepository.findOne({ where: { id: id } });
    if (!order) {
      throw new NotFoundException(`Order không tồn tại`);
    }

    await this.orderRepository.save({
      ...order,
      release_date: updateReleaseDate.release_date
    });

    return { message: 'Cập nhật thời gian giao hàng dự kiến thành công' };
  }
  async exportOrdersToExcel(
    startDate?: string,
    endDate?: string,
    senderCompanies?: string[],
    searchName?: string,
  ): Promise<Buffer> {
    const qb = this.orderRepository
      .createQueryBuilder('orders')
      .leftJoinAndSelect('orders.items', 'items')
      .leftJoinAndSelect('orders.tracking_events', 'tracking_events');
    if (startDate) {
      qb.andWhere('orders.created_at >= :startDate', {
        startDate: new Date(startDate),
      });
    }

    if (endDate) {
      const endDateTime = new Date(endDate);
      endDateTime.setHours(23, 59, 59, 999);
      qb.andWhere('orders.created_at <= :endDate', {
        endDate: endDateTime,
      });
    }

    if (senderCompanies && senderCompanies.length > 0) {
      const conditions = senderCompanies
        .map((_, index) => `orders.sender_company LIKE :senderCompany${index}`)
        .join(' OR ');

      const parameters = {};
      senderCompanies.forEach((company, index) => {
        parameters[`senderCompany${index}`] = `%${company.trim()}%`;
      });

      qb.andWhere(`(${conditions})`, parameters);
    }
    if (searchName && searchName.trim()) {
      qb.andWhere(
        '(orders.sender_company LIKE :searchName OR orders.receiver_company LIKE :searchName)',
        {
          searchName: `%${searchName.trim()}%`,
        }
      );
    }
    const orders = await qb
      .orderBy('orders.created_at', 'DESC')
      .getMany();

    if (!orders || orders.length === 0) {
      const filterMessages: string[] = [];

      if (startDate || endDate) {
        const dateRange: string[] = [];
        if (startDate) dateRange.push(`từ ${new Date(startDate).toLocaleDateString('vi-VN')}`);
        if (endDate) dateRange.push(`đến ${new Date(endDate).toLocaleDateString('vi-VN')}`);
        filterMessages.push(dateRange.join(' '));
      }
      if (senderCompanies && senderCompanies.length > 0) {
        filterMessages.push(`Không tìm thấy đơn hàng`);
      }
      if (searchName) {
        filterMessages.push(`Không tìm thấy đơn hàng`);
      }
      const message = filterMessages.length > 0
        ? `Không tìm thấy đơn hàng`
        : 'Không tìm thấy đơn hàng';
      const error: any = new BadRequestException(message);
      error.status = 400;
      error.error = 'Bad Request';
      throw error;
    }

    const workbook = new ExcelJS.Workbook();
    const sheet = workbook.addWorksheet('Sheet1');

    const getServiceTypeName = (serviceType: string): string => {
      const serviceTypeMap = {
        'fast': 'Nhanh',
        'economy': 'Tiết kiệm',
        'other': 'Khác'
      };
      return serviceTypeMap[serviceType?.toLowerCase()] || serviceType || '';
    };

    const roundUpToTwo = (value: number): number => {
      return Math.ceil(value * 100) / 100;
    };
    const formatNumber = (value: number): string => {
      const rounded = roundUpToTwo(value);
      return rounded.toLocaleString('en-US', {
        minimumFractionDigits: 2,
        maximumFractionDigits: 2,
      });
    };
    const formatCODAmount = (order: any): string => {
      if (!order.cod_amount && !order.total_value) return '0.00';
      let value: number;
      if (order.payment_method === 'cod' && order.total_value) {
        value = typeof order.total_value === 'string'
          ? parseFloat(order.total_value)
          : Number(order.total_value);
      } else {
        value = typeof order.cod_amount === 'string'
          ? parseFloat(order.cod_amount)
          : Number(order.cod_amount || 0);
      }

      if (isNaN(value)) return '0.00';

      return value.toLocaleString('en-US', {
        minimumFractionDigits: 2,
        maximumFractionDigits: 2,
      });
    };
    const calculateVolumeWeight = (length: number, width: number, height: number): number => {
      if (!length || !width || !height) return 0;
      const volume = (length * width * height) / 5000;
      return roundUpToTwo(volume);
    };

    const calculateRowHeight = (
      text: string | number,
      columnWidth: number,
      baseHeight = 18,
    ): number => {
      if (!text && text !== 0) return baseHeight;

      const textStr = String(text);
      const lines = textStr.split('\n');

      let maxLines = 1;
      lines.forEach(line => {
        const charsPerLine = columnWidth * 1.2;
        const estimatedLines = Math.ceil(line.length / charsPerLine);
        maxLines = Math.max(maxLines, estimatedLines);
      });

      return Math.min(Math.max(baseHeight, maxLines * 18), 120);
    };

    const headerRow1 = sheet.getRow(1);
    headerRow1.height = 30;

    sheet.mergeCells('A1:H1');
    const senderGroupCell = sheet.getCell('A1');
    senderGroupCell.value = 'THÔNG TIN NGƯỜI GỬI';
    senderGroupCell.font = { bold: true, size: 12, color: { argb: 'FF000000' } };
    senderGroupCell.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFFFFF00' }
    };
    senderGroupCell.alignment = {
      vertical: 'middle',
      horizontal: 'center'
    };
    senderGroupCell.border = {
      top: { style: 'thin' },
      left: { style: 'thin' },
      bottom: { style: 'thin' },
      right: { style: 'thin' }
    };

    sheet.mergeCells('I1:X1');
    const receiverGroupCell = sheet.getCell('I1');
    receiverGroupCell.value = 'THÔNG TIN NGƯỜI NHẬN';
    receiverGroupCell.font = { bold: true, size: 12, color: { argb: 'FF000000' } };
    receiverGroupCell.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFFFFF00' }
    };
    receiverGroupCell.alignment = {
      vertical: 'middle',
      horizontal: 'center'
    };
    receiverGroupCell.border = {
      top: { style: 'thin' },
      left: { style: 'thin' },
      bottom: { style: 'thin' },
      right: { style: 'thin' }
    };

    const headerRow2 = sheet.getRow(2);
    headerRow2.height = 25;

    const columnHeaders = [
      { key: 'stt', header: 'STT', width: 8 },
      { key: 'created_at', header: 'NGÀY TẠO ĐƠN', width: 20 },
      { key: 'order_code', header: 'MÃ VẬN ĐƠN', width: 20 },
      { key: 'sender_company', header: 'TÊN CÔNG TY', width: 40 },
      { key: 'sender_address', header: 'ĐỊA CHỈ', width: 50 },
      { key: 'sender_name', header: 'NGƯỜI GỬI', width: 25 },
      { key: 'sender_phone', header: 'SỐ ĐIỆN THOẠI', width: 15 },
      { key: 'sender_email', header: 'EMAIL', width: 25 },
      { key: 'receiver_company', header: 'TÊN CÔNG TY', width: 40 },
      { key: 'receiver_address', header: 'ĐỊA CHỈ', width: 50 },
      { key: 'receiver_name', header: 'NGƯỜI NHẬN', width: 25 },
      { key: 'receiver_phone', header: 'SỐ ĐIỆN THOẠI', width: 15 },
      { key: 'receiver_country', header: 'QUỐC GIA', width: 15 },
      { key: 'receiver_city', header: 'TỈNH THÀNH', width: 15 },
      { key: 'items_count', header: 'TỔNG SỐ KIỆN HÀNG', width: 25 },
      { key: 'total_weight', header: 'TỔNG TRỌNG LƯỢNG', width: 25 },
      { key: 'item_length', header: 'L (cm)', width: 10 },
      { key: 'item_width', header: 'W (cm)', width: 10 },
      { key: 'item_height', header: 'H (cm)', width: 10 },
      { key: 'item_volume', header: 'THỂ TÍCH (KG)', width: 15 },
      { key: 'description_item', header: 'TÊN HÀNG', width: 80 },
      { key: 'service_type', header: 'LOẠI DỊCH VỤ', width: 15 },
      { key: 'cod_amount', header: 'COD', width: 15 },
      { key: 'notes', header: 'GHI CHÚ', width: 50 },
    ];

    columnHeaders.forEach((col, index) => {
      const columnIndex = index + 1;
      const cell = headerRow2.getCell(columnIndex);

      cell.value = col.header;
      cell.font = { bold: true, size: 11, color: { argb: 'FF000000' } };
      cell.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: '00FF00' }
      };
      cell.alignment = {
        vertical: 'middle',
        horizontal: 'center',
        wrapText: true
      };
      cell.border = {
        top: { style: 'thin' },
        left: { style: 'thin' },
        bottom: { style: 'thin' },
        right: { style: 'thin' }
      };

      sheet.getColumn(columnIndex).width = col.width;
    });

    let currentRow = 3;

    orders.forEach((order, index) => {
      const itemsCount = order.items?.reduce((total, item) => {
        return total + (item.quantity || 1);
      }, 0) || 0;
      const startRow = currentRow;

      const formattedTotalWeight = order.total_weight
        ? formatNumber(Number(order.total_weight))
        : '';
      if (itemsCount > 0 && order.items && order.items.length > 0) {
        order.items.forEach((item) => {
          const row = sheet.getRow(currentRow);

          const length = Number(item.length) || 0;
          const width = Number(item.width) || 0;
          const height = Number(item.height) || 0;
          const itemVolume = calculateVolumeWeight(length, width, height);

          const rowData = [
            index + 1,
            order.created_at ? new Date(order.created_at).toLocaleDateString('vi-VN') : '',
            order.order_code || '',
            order.sender_company || '',
            order.sender_address || '',
            order.sender_name || '',
            order.sender_phone || '',
            order.sender_email || '',
            order.receiver_company || '',
            order.receiver_address || '',
            order.receiver_name || '',
            order.receiver_phone || '',
            order.receiver_country || '',
            order.receiver_city || '',
            itemsCount,
            formattedTotalWeight,
            length || '',
            width || '',
            height || '',
            itemVolume ? formatNumber(itemVolume) : '',
            order.description_item || '',
            getServiceTypeName(order.service_type),
            formatCODAmount(order),
            '',
          ];

          let maxHeight = 20;
          rowData.forEach((value, colIndex) => {
            const columnWidth = columnHeaders[colIndex]?.width || 15;
            const estimatedHeight = calculateRowHeight(value, columnWidth);
            maxHeight = Math.max(maxHeight, estimatedHeight);
          });
          row.height = Math.min(maxHeight, 100);

          rowData.forEach((value, colIndex) => {
            const cell = row.getCell(colIndex + 1);
            cell.value = value;

            cell.alignment = {
              vertical: 'middle',
              horizontal: 'center',
              wrapText: true
            };

            cell.border = {
              top: { style: 'thin' },
              left: { style: 'thin' },
              bottom: { style: 'thin' },
              right: { style: 'thin' }
            };
          });

          currentRow++;
        });

        if (order.items.length > 1) {
          const endRow = currentRow - 1;
          const columnsToMerge = [
            1, 2, 3, 4, 5, 6, 7, 8,
            9, 10, 11, 12, 13, 14,
            15, 16,
            21, 22, 23, 24,
          ];

          columnsToMerge.forEach(col => {
            try {
              sheet.mergeCells(startRow, col, endRow, col);
              const cell = sheet.getCell(startRow, col);
              cell.alignment = {
                vertical: 'middle',
                horizontal: 'center',
                wrapText: true
              };
            } catch (error) {
              console.error(`Error merging cells at column ${col}:`, error);
            }
          });
        }
      } else {
        const row = sheet.getRow(currentRow);
        const rowData = [
          index + 1,
          order.created_at ? new Date(order.created_at).toLocaleString('vi-VN') : '',
          order.order_code || '',
          order.sender_company || '',
          order.sender_address || '',
          order.sender_name || '',
          order.sender_phone || '',
          order.sender_email || '',
          order.receiver_company || '',
          order.receiver_address || '',
          order.receiver_name || '',
          order.receiver_phone || '',
          order.receiver_country || '',
          order.receiver_city || '',
          0,
          formattedTotalWeight,
          '',
          '',
          '',
          '',
          '',
          getServiceTypeName(order.service_type),
          formatCODAmount(order),
          order.description_item || '',
        ];

        let maxHeight = 20;
        rowData.forEach((value, colIndex) => {
          const columnWidth = columnHeaders[colIndex]?.width || 15;
          const estimatedHeight = calculateRowHeight(value, columnWidth);
          maxHeight = Math.max(maxHeight, estimatedHeight);
        });
        row.height = Math.min(maxHeight, 100);

        rowData.forEach((value, colIndex) => {
          const cell = row.getCell(colIndex + 1);
          cell.value = value;

          cell.alignment = {
            vertical: 'middle',
            horizontal: 'center',
            wrapText: true
          };

          cell.border = {
            top: { style: 'thin' },
            left: { style: 'thin' },
            bottom: { style: 'thin' },
            right: { style: 'thin' }
          };
        });

        currentRow++;
      }
    });
    sheet.views = [
      {
        state: 'frozen',
        xSplit: 1,
        ySplit: 2,
        activeCell: 'B3'
      }
    ];

    const buffer = await workbook.xlsx.writeBuffer();
    return Buffer.from(buffer);
  }
}