import prisma from "../config/prisma.js";
import { Prisma } from "../generated/prisma/client.js";
import { BOX_TRACKED_VEGETABLE_IDS } from "../constants/boxTracking.js";
import Unit from "../constants/units.js";
import whatsappService from "./whatsapp.service.js";
import { saveBase64File, deleteUploadedFile } from "../utils/saveBase64File.js";

const SIGNATURE_FOLDER = "box-return-signatures";
const baseUrl = process.env.BASE_URL || "";

export class BoxReturnService {
  /**
   * Get supplier box ledger — outstanding tomato boxes per supplier
   */
  async getSupplierBoxLedger(search?: string) {
    const supplierWhere: Prisma.SupplierWhereInput = {
      deletedAt: null,
      ...(search
        ? {
          OR: [
            { name: { contains: search } },
            { tamilName: { contains: search } },
          ],
        }
        : {}),
    };

    const suppliers = await prisma.supplier.findMany({
      where: supplierWhere,
      select: {
        id: true,
        name: true,
        tamilName: true,
        mobileNo: true,
      },
      orderBy: { name: "asc" },
    });

    // Aggregate incoming boxes from purchases (where unit == Unit.BOX and vegetableId in BOX_TRACKED_VEGETABLE_IDS)
    const incomingAgg = await prisma.purchase.groupBy({
      by: ["supplierId"],
      _sum: { bags: true },
      where: {
        unit: Unit.BOX as any,
        vegetableId: { in: BOX_TRACKED_VEGETABLE_IDS },
        deletedAt: null,
      },
    });

    const incomingMap = new Map<string, number>();
    for (const item of incomingAgg) {
      if (item.supplierId) {
        incomingMap.set(item.supplierId.toString(), Number(item._sum.bags ?? 0));
      }
    }

    // Aggregate returned boxes from box_returns
    const returnedAgg = await prisma.boxReturn.groupBy({
      by: ["supplierId"],
      _sum: { boxCount: true },
    });

    const returnedMap = new Map<string, number>();
    for (const item of returnedAgg) {
      returnedMap.set(item.supplierId.toString(), Number(item._sum.boxCount ?? 0));
    }

    const ledger = suppliers.map((sup) => {
      const supId = sup.id.toString();
      const totalIncomingBoxes = incomingMap.get(supId) || 0;
      const totalReturnedBoxes = returnedMap.get(supId) || 0;
      const outstandingBoxes = totalIncomingBoxes - totalReturnedBoxes;

      return {
        supplierId: sup.id,
        supplierName: sup.name,
        supplierTamilName: sup.tamilName,
        mobileNo: sup.mobileNo,
        totalIncomingBoxes,
        totalReturnedBoxes,
        outstandingBoxes,
      };
    });

    if (!search) {
      return ledger.filter(
        (item) => item.totalIncomingBoxes > 0 || item.totalReturnedBoxes > 0 || item.outstandingBoxes !== 0
      );
    }

    return ledger;
  }

  /**
   * Get incoming box purchases history (where unit == Unit.BOX and vegetableId in BOX_TRACKED_VEGETABLE_IDS)
   */
  async getIncomingBoxHistory(
    filters: {
      supplierId?: string | bigint;
      startDate?: string;
      endDate?: string;
      search?: string;
    },
    page: number = 1,
    limit: number = 20
  ) {
    const where: Prisma.PurchaseWhereInput = {
      unit: Unit.BOX as any,
      vegetableId: { in: BOX_TRACKED_VEGETABLE_IDS },
      deletedAt: null,
    };

    if (filters.supplierId) {
      where.supplierId = BigInt(filters.supplierId);
    }

    if (filters.search) {
      const search = filters.search;
      where.OR = [
        { lotNo: { contains: search } },
        { billNo: { contains: search } },
        { supplier: { name: { contains: search } } },
        { vegetable: { name: { contains: search } } },
      ];
    }

    if (filters.startDate || filters.endDate) {
      where.purchaseDate = {};
      if (filters.startDate) {
        where.purchaseDate.gte = new Date(filters.startDate);
      }
      if (filters.endDate) {
        where.purchaseDate.lte = new Date(filters.endDate);
      }
    }

    const skip = (page - 1) * limit;

    const [items, total] = await Promise.all([
      prisma.purchase.findMany({
        where,
        skip,
        take: limit,
        orderBy: { purchaseDate: "desc" },
        include: {
          supplier: { select: { id: true, name: true, tamilName: true } },
          vegetable: { select: { id: true, name: true, tamilName: true } },
        },
      }),
      prisma.purchase.count({ where }),
    ]);

    return {
      items,
      total,
      page,
      limit,
      totalPages: Math.ceil(total / limit),
    };
  }

  /**
   * Get box return history
   */
  async getBoxReturns(
    filters: {
      supplierId?: string | bigint;
      startDate?: string;
      endDate?: string;
      search?: string;
    },
    page: number = 1,
    limit: number = 20
  ) {
    const where: Prisma.BoxReturnWhereInput = {};

    if (filters.supplierId) {
      where.supplierId = BigInt(filters.supplierId);
    }

    if (filters.search) {
      const search = filters.search;
      where.OR = [
        { supplier: { name: { contains: search } } },
        { remarks: { contains: search } },
      ];
    }

    if (filters.startDate || filters.endDate) {
      where.returnDate = {};
      if (filters.startDate) {
        where.returnDate.gte = new Date(filters.startDate);
      }
      if (filters.endDate) {
        where.returnDate.lte = new Date(filters.endDate);
      }
    }

    const skip = (page - 1) * limit;

    const [items, total] = await Promise.all([
      prisma.boxReturn.findMany({
        where,
        skip,
        take: limit,
        orderBy: { returnDate: "desc" },
        include: {
          supplier: { select: { id: true, name: true, tamilName: true } },
          createdByUser: { select: { id: true, name: true } },
        },
      }),
      prisma.boxReturn.count({ where }),
    ]);

    // Map signature filename → full URL
    const mappedItems = items.map((item) => ({
      ...item,
      signatureUrl: item.signature
        ? `${baseUrl}/uploads/${SIGNATURE_FOLDER}/${item.signature}`
        : null,
    }));

    return {
      items: mappedItems,
      total,
      page,
      limit,
      totalPages: Math.ceil(total / limit),
    };
  }

  /**
   * Get a single box return record by ID
   */
  async getBoxReturnById(id: bigint) {
    const record = await prisma.boxReturn.findUnique({
      where: { id },
      include: {
        supplier: { select: { id: true, name: true, tamilName: true } },
        createdByUser: { select: { id: true, name: true } },
      },
    });

    if (!record) {
      throw new Error("Box return record not found");
    }

    return {
      ...record,
      signatureUrl: record.signature
        ? `${baseUrl}/uploads/${SIGNATURE_FOLDER}/${record.signature}`
        : null,
    };
  }

  /**
   * Create box return record with validation against available outstanding boxes
   */
  async createBoxReturn(data: {
    supplierId: bigint;
    returnDate: string | Date;
    boxCount: number | Prisma.Decimal;
    remarks?: string;
    signature?: string | null;
    createdBy?: bigint | null;
  }) {
    const supplierId = BigInt(data.supplierId);
    const supplier = await prisma.supplier.findFirst({
      where: { id: supplierId, deletedAt: null },
    });

    if (!supplier) {
      throw new Error("Supplier not found");
    }

    const requestedBoxes = Number(data.boxCount);
    if (isNaN(requestedBoxes) || requestedBoxes <= 0) {
      throw new Error("Box count must be greater than 0");
    }

    // 1. Calculate incoming boxes for supplier for box tracked vegetables
    const incomingAgg = await prisma.purchase.aggregate({
      _sum: { bags: true },
      where: {
        supplierId,
        unit: Unit.BOX as any,
        vegetableId: { in: BOX_TRACKED_VEGETABLE_IDS },
        deletedAt: null,
      },
    });
    const totalIncomingBoxes = Number(incomingAgg._sum.bags ?? 0);

    // 2. Calculate already returned boxes for supplier
    const returnedAgg = await prisma.boxReturn.aggregate({
      _sum: { boxCount: true },
      where: { supplierId },
    });
    const totalReturnedBoxes = Number(returnedAgg._sum.boxCount ?? 0);

    const outstandingBoxes = totalIncomingBoxes - totalReturnedBoxes;

    if (requestedBoxes > outstandingBoxes) {
      const available = Math.max(0, outstandingBoxes);
      throw new Error(
        `Cannot return ${requestedBoxes} boxes. Supplier '${supplier.name}' only has ${available} pending box(es) to return.`
      );
    }

    // Save base64 signature as a file on disk; store only the filename in DB
    let signatureFilename: string | null = null;
    if (data.signature) {
      signatureFilename = saveBase64File(data.signature, SIGNATURE_FOLDER, "sig");
    }

    const record = await prisma.boxReturn.create({
      data: {
        supplierId,
        returnDate: new Date(data.returnDate),
        boxCount: new Prisma.Decimal(requestedBoxes),
        remarks: data.remarks || null,
        signature: signatureFilename ?? null,
        createdBy: data.createdBy ?? null,
      },
      include: {
        supplier: { select: { id: true, name: true, tamilName: true } },
      },
    });

    // Send WhatsApp notification
    try {
      if (supplier.mobileNo) {
        // Fetch vegetable name from tracked vegetables or fallback to Tomato
        const trackedVegetables = await prisma.vegetable.findMany({
          where: { id: { in: BOX_TRACKED_VEGETABLE_IDS } },
          select: { name: true },
        });
        const vegetableName = trackedVegetables.map((v) => v.name).join(", ") || "Tomato";
        const remainingBoxes = Math.max(0, outstandingBoxes - requestedBoxes);

        await whatsappService.sendSupplierBoxReturnMessage({
          phone: supplier.mobileNo,
          supplierName: supplier.name,
          totalBoxesPurchased: totalIncomingBoxes,
          vegetableName: vegetableName,
          returnedBoxes: requestedBoxes,
          retainedBoxes: remainingBoxes,
        });
      }
    } catch (whatsappErr: any) {
      console.error(
        "WhatsApp supplier box return notification error:",
        whatsappErr?.message || whatsappErr
      );
    }

    return record;
  }

  /**
   * Update box return record with validation against available outstanding boxes
   */
  async updateBoxReturn(
    id: bigint,
    data: {
      returnDate?: string | Date;
      boxCount?: number | Prisma.Decimal;
      remarks?: string;
      signature?: string | null;
    }
  ) {
    const existing = await prisma.boxReturn.findUnique({ where: { id } });
    if (!existing) {
      throw new Error("Box return record not found");
    }

    const supplierId = existing.supplierId;
    const oldBoxCount = Number(existing.boxCount);

    const updateData: Prisma.BoxReturnUpdateInput = {};
    if (data.returnDate) {
      updateData.returnDate = new Date(data.returnDate);
    }
    if (data.remarks !== undefined) {
      updateData.remarks = data.remarks || null;
    }
    if (data.signature !== undefined) {
      // Delete old signature file if it exists
      if (existing.signature) {
        deleteUploadedFile(existing.signature, SIGNATURE_FOLDER);
      }
      // Save new signature file (or clear if null)
      if (data.signature) {
        updateData.signature = saveBase64File(data.signature, SIGNATURE_FOLDER, "sig");
      } else {
        updateData.signature = null;
      }
    }

    if (data.boxCount !== undefined) {
      const requestedBoxes = Number(data.boxCount);
      if (isNaN(requestedBoxes) || requestedBoxes <= 0) {
        throw new Error("Box count must be greater than 0");
      }

      // Calculate available limit including current record's existing amount
      const incomingAgg = await prisma.purchase.aggregate({
        _sum: { bags: true },
        where: {
          supplierId,
          unit: Unit.BOX as any,
          vegetableId: { in: BOX_TRACKED_VEGETABLE_IDS },
          deletedAt: null,
        },
      });
      const totalIncomingBoxes = Number(incomingAgg._sum.bags ?? 0);

      const returnedAgg = await prisma.boxReturn.aggregate({
        _sum: { boxCount: true },
        where: { supplierId },
      });
      const totalReturnedBoxes = Number(returnedAgg._sum.boxCount ?? 0);

      const availableBoxes = totalIncomingBoxes - (totalReturnedBoxes - oldBoxCount);

      if (requestedBoxes > availableBoxes) {
        throw new Error(
          `Cannot return ${requestedBoxes} boxes. Max allowable return is ${Math.max(0, availableBoxes)} box(es).`
        );
      }

      updateData.boxCount = new Prisma.Decimal(requestedBoxes);
    }

    return await prisma.boxReturn.update({
      where: { id },
      data: updateData,
      include: {
        supplier: { select: { id: true, name: true, tamilName: true } },
      },
    });
  }

  /**
   * Delete box return record
   */
  async deleteBoxReturn(id: bigint) {
    const existing = await prisma.boxReturn.findUnique({ where: { id } });
    if (!existing) {
      throw new Error("Box return record not found");
    }

    // Remove associated signature file from disk
    if (existing.signature) {
      deleteUploadedFile(existing.signature, SIGNATURE_FOLDER);
    }

    return await prisma.boxReturn.delete({ where: { id } });
  }
}