import {
  BadRequestException,
  ConflictException,
  Injectable,
  NotFoundException,
} from "@nestjs/common";
import { PrismaService } from "../../prisma/prisma.service";

import { handlePrismaError } from "src/common/lib/handlePrismaError";
import { ConfigService } from "@nestjs/config";
import {
  formatNoDataResponse,
  formatPaginatedResponse,
  getPaginationOptions,
} from "src/common/lib/pagination-helper";
import { CreateCylinderDto, CylinderStatus } from "./dto/create-cylinder.dto";
import { UpdateCylinderDto } from "./dto/update-cylinder.dto";
import { CylinderSelect } from "src/selects/cylinder";
import { Client_Cylinder_Select } from "src/selects/client_cylinder";
import { AssignToClientDto } from "./dto/assign-to-client.dto";
import { AccountType, CylinderMovement } from "@prisma/client";
import { AssignToWarehouseDto } from "./dto/assign-to-warehouse.dto";
import { RfidsDto } from "./dto/rfids.dto";
import { AssignClientDto } from "./dto/assign-client.dto";
import { BulkClaimCylindersDto } from "./dto/bulk-claim-cylinders.dto";
import { get } from "axios";

@Injectable()
export class CylindersService {
  findByRfid(rfid: string) {
    throw new Error("Method not implemented.");
  }
  constructor(
    private prisma: PrismaService,
    private readonly configService: ConfigService
  ) { }

  // ---------------- ASSIGN TO WAREHOUSE ----------------
  async assignToWarehouse(dto: AssignToWarehouseDto, user: any) {
    const { warehouseId, cylinderIds, cylinderRfids, action } = dto;

    if (!cylinderIds && !cylinderRfids) {
      throw new BadRequestException("Either cylinderIds or cylinderRfids must be provided.");
    }

    let finalCylinderIds: string[] = [];

    if (cylinderRfids && cylinderRfids.length > 0) {
      const cylindersByRfid = await this.prisma.cylinders.findMany({
        where: { rfid: { in: cylinderRfids } },
        select: { id: true },
      });
      if (cylindersByRfid.length !== cylinderRfids.length) {
        throw new NotFoundException("Some cylinders with provided RFIDs were not found.");
      }
      finalCylinderIds = cylindersByRfid.map((c) => c.id);
    } else if (cylinderIds && cylinderIds.length > 0) {
      finalCylinderIds = cylinderIds;
    }

    // Validate warehouse existence
    const warehouse = await this.prisma.warehouse.findUnique({
      where: { id: warehouseId },
    });

    if (!warehouse) {
      throw new NotFoundException("Warehouse not found");
    }

    const cylinders = await this.prisma.cylinders.findMany({
      where: { id: { in: finalCylinderIds } },
    });

    if (cylinders.length !== finalCylinderIds.length) {
      throw new NotFoundException("Some cylinders were not found");
    }

    return this.prisma.$transaction(async (tx) => {

      await tx.client_cylinders.updateMany({
        where: {
          cylinderId: { in: finalCylinderIds },
          isCylinderAssigned: true,
        },
        data: {
          isCylinderAssigned: false,
        },
      });

      await tx.cylinders.updateMany({
        where: { id: { in: finalCylinderIds } },
        data: {
          clientId: null,
          warehouseId,
          status: "INSIDE",
          is_client_owned: 0,
          clientAssignedDate: null,
        },
      });

      const logs = finalCylinderIds.map((cylinderId) => ({
        cylinderId,
        movement: action,
        remark: `Cylinder assign to ${warehouse.name}`,
        scannedBy: user?.name ?? user?.email,
        warehouse: warehouse.name,
      }));

      await tx.cylinder_logs.createMany({
        data: logs,
      });

      return { message: "Cylinders assigned to warehouse successfully" };
    });
  }

  // ---------------- MARK INSIDE ----------------
  // A plant user marks selected cylinders as physically received (INSIDE).
  // Only cylinders that are currently ASSIGNED to this plant's location are
  // updated; any other selected cylinders are ignored.
  async markInside(cylinderIds: string[], user: any) {
    if (!cylinderIds || cylinderIds.length === 0) {
      throw new BadRequestException("No cylinders selected.");
    }

    const currentUser = await this.prisma.users.findUnique({
      where: { id: user?.sub },
      select: { id: true, name: true, email: true, warehouseId: true },
    });

    if (!currentUser) {
      throw new NotFoundException("User not found.");
    }

    if (!currentUser.warehouseId) {
      throw new BadRequestException("No location is assigned to your account.");
    }

    const eligible = await this.prisma.cylinders.findMany({
      where: {
        id: { in: cylinderIds },
        status: CylinderStatus.ASSIGNED,
        warehouseId: currentUser.warehouseId,
      },
      select: { id: true, warehouse: { select: { name: true } } },
    });

    if (eligible.length === 0) {
      throw new BadRequestException(
        "None of the selected cylinders are assigned to your location.",
      );
    }

    const eligibleIds = eligible.map((c) => c.id);

    return this.prisma.$transaction(async (tx) => {
      await tx.cylinders.updateMany({
        where: { id: { in: eligibleIds } },
        data: { status: CylinderStatus.INSIDE as any },
      });

      const logs = eligible.map((c) => ({
        cylinderId: c.id,
        movement: CylinderMovement.INSIDE,
        remark: `Cylinder marked inside ${c.warehouse?.name || "location"}.`,
        scannedBy: currentUser.name ?? currentUser.email,
        warehouse: c.warehouse?.name,
      }));

      await tx.cylinder_logs.createMany({ data: logs });

      return {
        message: `${eligibleIds.length} cylinder(s) marked inside successfully`,
        updated: eligibleIds.length,
      };
    });
  }

  // ---------------- ASSIGN NEW CYLINDERS TO WAREHOUSE ----------------
  // Admin assigns selected cylinders to a warehouse. Only cylinders that are
  // currently NEW are affected: their status becomes ASSIGNED and their
  // warehouseId is set. Any selected cylinders in another status are ignored.
  async assignNewToWarehouse(warehouseId: string, cylinderIds: string[], user: any) {
    if (!warehouseId) {
      throw new BadRequestException("Please select a location.");
    }
    if (!cylinderIds || cylinderIds.length === 0) {
      throw new BadRequestException("No cylinders selected.");
    }

    const warehouse = await this.prisma.warehouse.findUnique({
      where: { id: warehouseId },
    });
    if (!warehouse) {
      throw new NotFoundException("Warehouse not found");
    }

    const eligible = await this.prisma.cylinders.findMany({
      where: {
        id: { in: cylinderIds },
        status: CylinderStatus.NEW,
      },
      select: { id: true },
    });

    if (eligible.length === 0) {
      throw new BadRequestException(
        "None of the selected cylinders are NEW.",
      );
    }

    const eligibleIds = eligible.map((c) => c.id);

    return this.prisma.$transaction(async (tx) => {
      await tx.cylinders.updateMany({
        where: { id: { in: eligibleIds } },
        data: {
          warehouseId,
          status: CylinderStatus.ASSIGNED as any,
        },
      });

      const logs = eligibleIds.map((cylinderId) => ({
        cylinderId,
        movement: CylinderMovement.NEW,
        remark: `Cylinder assigned to ${warehouse.name}`,
        scannedBy: user?.name ?? user?.email,
        warehouse: warehouse.name,
      }));

      await tx.cylinder_logs.createMany({ data: logs });

      return {
        message: `${eligibleIds.length} cylinder(s) assigned to ${warehouse.name} successfully`,
        updated: eligibleIds.length,
      };
    });
  }

  // ---------------- CREATE CYLINDER ----------------
  async create(createCylinderDto: CreateCylinderDto, user: any) {
    const { rfid, item_id } = createCylinderDto;

    // Unique RFID (optional field — only checked when a tag is supplied)
    if (rfid) {
      const existingRfid = await this.prisma.cylinders.findUnique({
        where: { rfid },
      });

      if (existingRfid) {
        throw new ConflictException("RFID tag already exists");
      }
    }

    const getuser = await this.prisma.users.findFirst({
      where: {
        id: user.sub
      },
      include: {
        warehouse: true
      }
    })

    if (!getuser) {
      throw new NotFoundException("User not Found");
    }

    // Unique Cylinder Serial No (item_id)
    const existingSerial = await this.prisma.cylinders.findUnique({
      where: { item_id },
    });

    if (existingSerial) {
      throw new ConflictException("Cylinder Serial Number already exists");
    }

    let targetWarehouseId: string | null = null;
    let targetWarehouseName: string | null = null;
    let cylinderStatus: CylinderStatus;

    if (getuser.role === 'PLANT') {
      // PLANT users: cylinder goes straight into their own warehouse
      targetWarehouseId = getuser.warehouseId || null;
      cylinderStatus = CylinderStatus.INSIDE;
    } else {
      // ADMIN / MANUFACTURER: newly manufactured cylinder
      targetWarehouseId = createCylinderDto.warehouseId || null;
      cylinderStatus = CylinderStatus.NEW;
    }

    if (targetWarehouseId) {
      const warehouse = await this.prisma.warehouse.findUnique({
        where: { id: targetWarehouseId },
        select: { name: true }
      });
      if (warehouse) {
        targetWarehouseName = warehouse.name;
      }
    }

    return this.prisma.$transaction(async (tx) => {
      const cylinder = await tx.cylinders.create({
        data: {
          ...createCylinderDto,
          // Empty string would clash on the unique index for a second untagged cylinder.
          rfid: rfid || null,
          status: cylinderStatus,
          warehouseId: targetWarehouseId,
          manufacturer_id: getuser.role === 'MANUFACTURER' ? getuser.id : null,
          created_by: getuser.id,
        },
        select: CylinderSelect,
      });

      await tx.cylinder_logs.create({
        data: {
          cylinderId: cylinder.id,
          movement: CylinderMovement.NEW,
          remark:
            "Cylinder with Serial Number " +
            cylinder.item_id +
            " is added to the system.",
          scannedBy: user?.name ?? user?.email,
        },
      });

      if (targetWarehouseId) {
        await tx.cylinder_logs.create({
          data: {
            cylinderId: cylinder.id,
            movement: CylinderMovement.NEW,
            remark: `Cylinder assign to ${targetWarehouseName}`,
            scannedBy: user?.name ?? user?.email,
          },
        });
      }

      return cylinder;
    });
  }

  //  async getUniqueLots() {
  //   const lots = await this.prisma.cylinders.findMany({
  //     select: {
  //       lotNumber: true,
  //     },
  //     where: {
  //       lotNumber: {
  //         not: null,
  //       },
  //     },
  //     distinct: ["lotNumber"],
  //   });

  //   return lots.map((item) => item.lotNumber).filter(Boolean);
  // }

  async getUniqueLots(page: number, perPage: number, search?: string) {
    const { skip, take } = getPaginationOptions({ page, perPage });

    const where: any = {
      lotNumber: {
        not: null,
      },
    };

    if (search && search.trim() !== '') {
      where.lotNumber = {
        contains: search.trim(),
        mode: 'insensitive',
      };
    }

    const lots = await this.prisma.cylinders.groupBy({
      by: ['lotNumber'],
      where: {
        ...where,
        lotNumber: { not: null },
      },
      skip,
      take,
      orderBy: {
        lotNumber: 'asc',
      },
    });

    const formattedLots = lots
      .map((item) => ({
        label: item.lotNumber,
        value: item.lotNumber,
      }))
      .filter((item) => item.label);

    const distinctTotal = (await this.prisma.cylinders.groupBy({
      by: ['lotNumber'],
      where: {
        lotNumber: {
          not: null,
          ...(search && search.trim() !== '' && { contains: search.trim(), mode: 'insensitive' }),
        },
      },
    })).length;

    return formatPaginatedResponse(formattedLots, distinctTotal, page, perPage);
  }

  async findAll(
    page: number,
    perPage: number,
    search?: string,
    status?: string,
    lot?: string,
    month?: number,
    dateFrom?: string,
    dateTo?: string,
    clientId?: string,
    warehouseId?: string,
    vehicleId?: string,
    driverId?: string,
    user?: any,
  ) {
    try {
      const { skip, take } = getPaginationOptions({ page, perPage });

      const where: any = {};

      // if (search) {
      //   where.OR = [
      //     { rfid: { contains: search } },
      //     { item_id: { contains: search } },
      //   ];
      // }

      if (search && search.trim() !== '') {
        if (search.includes(',')) {
          const searchTerms = search.split(',').map(term => term.trim()).filter(term => term.length > 0);
          if (searchTerms.length > 0) {
            where.OR = searchTerms.map(term => ({
              OR: [
                { rfid: { contains: term } },
                { item_id: { contains: term } },
              ],
            }));
          }
        } else {
          where.OR = [
            { rfid: { contains: search.trim() } },
            { item_id: { contains: search.trim() } },
          ];
        }
      }

      const currentUser = await this.prisma.users.findUnique({
        where: { id: user?.sub },
        select: { id: true, role: true, warehouseId: true }
      });

      if (!currentUser) {
        throw new NotFoundException("User not found.");
      }


      if (currentUser.role === AccountType.MANUFACTURER) {
        where.manufacturer_id = currentUser.id;
      } else if (currentUser.role === AccountType.PLANT) {
        // All Cylinder: cylinders in this plant's own location (any status)
        // plus available NEW cylinders not yet assigned to any location.
        const plantFilter = {
          OR: [
            { warehouseId: currentUser.warehouseId },
            { warehouseId: null, status: CylinderStatus.NEW },
          ],
        };

        if (!where.AND) where.AND = [];
        where.AND.push(plantFilter);
      } else if (warehouseId && warehouseId.trim() !== '') {
        where.warehouseId = warehouseId;
      }


      // calculate date 30 60 90  days back
      if (month) {
        const dateMonthsAgo = new Date();
        dateMonthsAgo.setMonth(dateMonthsAgo.getMonth() - month / 10);

        if (where.AND) {
          where.AND.push({
            clientAssignedDate: { lte: dateMonthsAgo },
          });
        } else {
          where.AND = [
            {
              clientAssignedDate: { lte: dateMonthsAgo },
            },
          ];
        }
      }

      if (status && status.trim() !== "") {
        if (status === "OUTSIDE") {
          where.status = "OUTSIDE";
          where.is_client_owned = 1;
        } else if (status === "OUTSIDE (W/O Client)") {
          where.status = "OUTSIDE";
          where.is_client_owned = 0;
        } else {
          where.status = status;
        }
      }

      if (lot && lot.trim() !== "") {
        where.lotNumber = lot;
      }

      if (clientId && clientId.trim() !== "") {
        where.clientId = clientId;
      }

      // Filter cylinders by the vehicle/driver/dispatch-date involved in any of
      // their movement logs. Driver + date range are merged into a single `some`
      // block so they match the SAME log (e.g. "cylinders driver X dispatched
      // between June 15-30"), not just any two unrelated logs. This compiles to
      // an indexed EXISTS subquery (see @@index on cylinder_logs.assigned_date),
      // so it stays fast regardless of how many logs exist.
      const logsFilter: any = {};
      if (vehicleId && vehicleId.trim() !== "") {
        logsFilter.vehicleId = vehicleId;
      }
      if (driverId && driverId.trim() !== "") {
        logsFilter.driverId = driverId;
      }
      if (dateFrom || dateTo) {
        logsFilter.assigned_date = {};
        if (dateFrom) {
          logsFilter.assigned_date.gte = new Date(dateFrom);
        }
        if (dateTo) {
          // Add one day to include the entire end date.
          const endDate = new Date(dateTo);
          endDate.setDate(endDate.getDate() + 1);
          logsFilter.assigned_date.lt = endDate;
        }
      }
      if (Object.keys(logsFilter).length > 0) {
        where.logs = { some: logsFilter };
      }

      const [itemsWithRelations, total] = await this.prisma.$transaction([
        this.prisma.cylinders.findMany({
          where,
          select: CylinderSelect,
          skip,
          take,
          orderBy: { createdAt: 'desc' },
        }),
        this.prisma.cylinders.count({ where }),
      ]);

      // Owner = the location (warehouse) of the user who created the cylinder.
      // created_by has no Prisma relation, so resolve the creators' warehouse
      // names in a single lookup and map them back onto each cylinder.
      const creatorIds = [
        ...new Set(
          itemsWithRelations
            .map(item => item.created_by)
            .filter((id): id is string => !!id),
        ),
      ];
      const creators = creatorIds.length
        ? await this.prisma.users.findMany({
          where: { id: { in: creatorIds } },
          select: { id: true, warehouse: { select: { name: true } } },
        })
        : [];
      const creatorLocationMap = new Map(
        creators.map(c => [c.id, c.warehouse?.name || 'N/A']),
      );

      const items = itemsWithRelations.map(item => {
        const { warehouse, client, claimedBy, ...rest } = item;
        return {
          ...rest,
          warehouseName: warehouse?.name || 'N/A',
          warehouseId: warehouse?.id || 'N/A',
          ownerLocation: item.created_by
            ? creatorLocationMap.get(item.created_by) || 'N/A'
            : 'N/A',
          clientName: client?.name || 'N/A',
          clientAddress: client?.address || 'N/A',
          clientPhone: client?.phone || 'N/A',
          clientId: client?.id || 'N/A',
          claimedByName: claimedBy?.name || 'N/A',
        };
      });

      return formatPaginatedResponse(items, total, page, perPage);

      // const [items, total] = await this.prisma.$transaction([
      //   this.prisma.cylinders.findMany({
      //     where,
      //     select: CylinderSelect,
      //     skip,
      //     take,
      //     orderBy: { createdAt: "desc" },
      //   }),
      //   this.prisma.cylinders.count({ where }),
      // ]);

      // return formatPaginatedResponse(items, total, page, perPage);
    } catch (error) {
      console.log(error);
      handlePrismaError(error, "Fetching paginated Cylinders");
    }
  }

  // ---------------- FIND ONE ----------------
  async findOne(id: any) {
    const cylinder = await this.prisma.cylinders.findUnique({
      where: { id },
      select: CylinderSelect,
    });

    if (!cylinder) {
      throw new NotFoundException(`Cylinder not found`);
    }

    return cylinder;
  }


  async getByRfid(rfid: any) {
    const cylinder = await this.prisma.cylinders.findUnique({
      where: { rfid },
      select: CylinderSelect,
    });

    if (!cylinder) {
      throw new NotFoundException(`Cylinder not found with rfid: ${rfid}`);
    }

    return cylinder;
  }

  // ---------------- UPDATE ----------------
  async update(id: string, updateCylinderDto: UpdateCylinderDto, user: any) {
    await this.findOne(id);

    if (updateCylinderDto.rfid) {
      const existingRfid = await this.prisma.cylinders.findFirst({
        where: {
          rfid: updateCylinderDto.rfid,
          NOT: { id },
        },
      });
      if (existingRfid) {
        throw new ConflictException("RFID tag already exists");
      }
    }

    const getuser = await this.prisma.users.findFirst({
      where: {
        id: user.sub
      },
      include: {
        warehouse: true
      }
    })

    if (!getuser) {
      throw new NotFoundException("User not Found");
    }

    // Use item_id (serial no), not cylinderSrNo
    if (updateCylinderDto.item_id) {
      const existingSerial = await this.prisma.cylinders.findFirst({
        where: {
          item_id: updateCylinderDto.item_id,
          NOT: { id },
        },
      });
      if (existingSerial) {
        throw new ConflictException("Cylinder Serial Number already exists");
      }
    }

    let targetWarehouseId: string | null = null;
    let targetWarehouseName: string | null = null;

    if (getuser.role === 'ADMIN') {
      targetWarehouseId = updateCylinderDto.warehouseId || null;
    } else {
      targetWarehouseId = getuser.warehouseId || null;
    }

    if (targetWarehouseId) {
      const warehouse = await this.prisma.warehouse.findUnique({
        where: { id: targetWarehouseId },
        select: { name: true }
      });
      if (warehouse) {
        targetWarehouseName = warehouse.name;
      }
    }

    return this.prisma.$transaction(async (tx) => {
      // Get current cylinder state to check for changes
      const currentCylinder = await tx.cylinders.findUnique({
        where: { id },
        select: { warehouseId: true, status: true, warehouse: { select: { name: true } } }
      });

      // Log warehouse changes
      if (currentCylinder?.warehouseId && !targetWarehouseId) {
        // Warehouse removed
        await tx.cylinder_logs.create({
          data: {
            cylinderId: id,
            movement: CylinderMovement.NEW,
            remark: `Cylinder removed from ${currentCylinder.warehouse?.name || 'warehouse'} via update.`,
            scannedBy: user?.name ?? user?.email,
            warehouse: currentCylinder.warehouse?.name,
          },
        });
      } else if (targetWarehouseId && targetWarehouseId !== currentCylinder?.warehouseId) {
        // Warehouse added or changed
        await tx.cylinder_logs.create({
          data: {
            cylinderId: id,
            movement: currentCylinder?.status as any,
            remark: `Cylinder reassigned to ${targetWarehouseName} via update.`,
            scannedBy: user?.name ?? user?.email,
            warehouse: targetWarehouseName,
          },
        });
      }

      // Determine the status: assigning a warehouse to a NEW cylinder marks it
      // ASSIGNED; removing the warehouse sends it back to NEW.
      let newStatus = currentCylinder?.status;
      if (targetWarehouseId && currentCylinder?.status === CylinderStatus.NEW) {
        newStatus = CylinderStatus.ASSIGNED;
      } else if (!targetWarehouseId && (currentCylinder?.status === CylinderStatus.INSIDE || currentCylinder?.status === CylinderStatus.OUTSIDE || currentCylinder?.status === CylinderStatus.ASSIGNED)) {
        newStatus = CylinderStatus.NEW;
      }
      const dataToUpdate: any = {
        ...updateCylinderDto,
        warehouseId: targetWarehouseId,
        status: newStatus as any
      };

      // RFID is optional; clearing it must store null, not "", which would
      // clash on the unique index with any other untagged cylinder.
      if (updateCylinderDto.rfid !== undefined) {
        dataToUpdate.rfid = updateCylinderDto.rfid || null;
      }

      if (!targetWarehouseId) {
        dataToUpdate.clientId = null;
        dataToUpdate.is_client_owned = 0;
        dataToUpdate.clientAssignedDate = null;
        
        await tx.client_cylinders.updateMany({
            where: {
                cylinderId: id,
                isCylinderAssigned: true,
            },
            data: {
                isCylinderAssigned: false,
            },
        });
      }


      return tx.cylinders.update({
        where: { id },
        data: {
          ...dataToUpdate
        },
        select: CylinderSelect,
      });
    });
  }

  // ---------------- REMOVE ----------------
  async remove(id: string) {
    const cylinder = await this.prisma.cylinders.findUnique({ where: { id } });
    if (!cylinder) throw new NotFoundException(`Cylinder not found`);

    return this.prisma.$transaction(async (tx) => {
      await tx.cylinder_logs.deleteMany({
        where: { cylinderId: id },
      });

      return tx.cylinders.delete({
        where: { id },
        select: { id: true },
      });
    });
  }

  // ---------------- CHANGE STATUS ----------------
  async changeStatus(id: string) {
    const cylinder = await this.findOne(id);

    const newStatus =
      cylinder.status === CylinderStatus.INSIDE
        ? CylinderStatus.OUTSIDE
        : CylinderStatus.INSIDE;

    return this.prisma.cylinders.update({
      where: { id },
      data: { status: newStatus },
      select: { id: true, status: true },
    });
  }

  // ---------------- BULK CLAIM CYLINDERS ----------------
  async bulkClaimCylinders(dto: BulkClaimCylindersDto, authenticatedUser: any) {
    const { cylinderIds, cylinderRfids, claimBy } = dto;

    if (!cylinderIds && !cylinderRfids) {
      throw new BadRequestException("Either cylinderIds or cylinderRfids must be provided for bulk claim.");
    }

    const effectiveClaimId = (authenticatedUser.role === AccountType.ADMIN && claimBy)
      ? claimBy
      : authenticatedUser.sub;

    const claimingUser = await this.prisma.users.findUnique({
      where: { id: effectiveClaimId },
      select: { name: true, email: true },
    });
    const scannedByInfo = claimingUser?.name ?? claimingUser?.email ?? effectiveClaimId;

    let finalCylinderIds: string[] = [];

    if (cylinderRfids && cylinderRfids.length > 0) {
      const cylindersByRfid = await this.prisma.cylinders.findMany({
        where: { rfid: { in: cylinderRfids } },
        select: { id: true },
      });
      if (cylindersByRfid.length !== cylinderRfids.length) {
        throw new NotFoundException("Some cylinders with provided RFIDs were not found.");
      }
      finalCylinderIds = cylindersByRfid.map((c) => c.id);
    } else if (cylinderIds && cylinderIds.length > 0) {
      finalCylinderIds = cylinderIds;
    }

    if (finalCylinderIds.length === 0) {
      return { message: "No cylinders provided for claiming." };
    }

    return this.prisma.$transaction(async (tx) => {
      const existingCylinders = await tx.cylinders.findMany({
        where: {
          id: { in: finalCylinderIds },
          claim_status: true,
        },
        select: { id: true, item_id: true },
      });

      const alreadyClaimedIds = new Set(existingCylinders.map(c => c.id));
      const cylindersToClaim = finalCylinderIds.filter(id => !alreadyClaimedIds.has(id));

      if (cylindersToClaim.length === 0) {
        throw new ConflictException("All provided cylinders are already claimed.");
      }

      const { count } = await tx.cylinders.updateMany({
        where: {
          id: { in: cylindersToClaim },
          claim_status: false,
        },
        data: {
          claim_status: true,
          claimedById: effectiveClaimId,
        },
      });

      const logs = cylindersToClaim.map((cylinderId) => ({
        cylinderId,
        movement: CylinderMovement.NEW,
        remark: `Cylinder claimed by ${scannedByInfo}`,
        scannedBy: scannedByInfo,
      }));

      if (logs.length > 0) {
        await tx.cylinder_logs.createMany({
          data: logs,
        });
      }

      return { message: `${count} cylinders claimed successfully.` };
    });
  }

  // ---------------- GET CLAIMS ----------------
  async getClaims(page: number, perPage: number, search?: string, user?: any) {
    const { skip, take } = getPaginationOptions({ page, perPage });

    const where: any = {
      claim_status: true,
    };

    if (user && user.role !== 'ADMIN') {
      where.claimedById = user.sub;
    }

    if (search && search.trim() !== '') {
      where.OR = [
        { rfid: { contains: search.trim(), } },
        { item_id: { contains: search.trim(), } },
      ];
    }

    const [items, total] = await this.prisma.$transaction([
      this.prisma.cylinders.findMany({
        where,
        select: CylinderSelect,
        skip,
        take,
        orderBy: { createdAt: 'desc' },
      }),
      this.prisma.cylinders.count({ where }),
    ]);

    if (total === 0) return formatNoDataResponse("No claimed cylinders found.");
    return formatPaginatedResponse(items, total, page, perPage);
  }

  // ---------------- SEARCH ----------------
  async searchCylinder(query: string, page: number, perPage: number) {
    const cleanedQuery = query?.trim();
    if (!cleanedQuery)
      throw new BadRequestException("Search query is required");

    const { skip, take } = getPaginationOptions({ page, perPage });

    const where = {
      OR: [
        { rfid: { contains: cleanedQuery } },
        { item_id: { contains: cleanedQuery } }, // <-- FIXED
      ],
    };

    const [items, total] = await this.prisma.$transaction([
      this.prisma.cylinders.findMany({
        where,
        select: CylinderSelect,
        skip,
        take,
        orderBy: { createdAt: "desc" },
      }),
      this.prisma.cylinders.count({ where }),
    ]);

    if (total === 0) return formatNoDataResponse("Cylinder Not Found.");
    return formatPaginatedResponse(items, total, page, perPage);
  }

  // ---------------- GET ALL (REPORT) ----------------
  async getAllCylinder() {
    return await this.prisma.cylinders.findMany();
  }

  // ---------------- ASSIGN CLIENT (EXISTING CYLINDERS) ----------------
  async assignClient(
    body: AssignClientDto,
    user: any
  ) {
    const { clientId, cylinderIds, lotNumber, challanDate, dispatchDate } = body;

    const client = await this.prisma.clients.findUnique({
      where: { id: clientId },
    });

    if (!client) {
      throw new NotFoundException("Client not found");
    }

    return this.prisma.$transaction(async (tx) => {
      // Find cylinders that are eligible (OUTSIDE and not yet owned by a client)
      const eligibleCylinders = await tx.cylinders.findMany({
        where: {
          id: { in: cylinderIds },
          is_client_owned: 0,
          status: "OUTSIDE",
        },
        select: { id: true }
      });

      const eligibleIds = eligibleCylinders.map(c => c.id);

      if (eligibleIds.length === 0) {
        return { message: "No eligible cylinders (OUTSIDE and not client-owned) were found for assignment." };
      }

      await tx.cylinders.updateMany({
        where: {
          id: { in: eligibleIds },
        },
        data: {
          clientId,
          is_client_owned: 1,
          challanDate,
          dispatchDate,
          ...(lotNumber && { lotNumber }),
        },
      });

      const existing = await tx.client_cylinders.findMany({
        where: {
          clientId,
          cylinderId: { in: eligibleIds },
          isCylinderAssigned: true,
        },
        select: { cylinderId: true },
      });

      const existingIds = new Set(existing.map(e => e.cylinderId));

      const newData = eligibleIds
        .filter(id => !existingIds.has(id))
        .map(cylinderId => ({
          cylinderId,
          clientId,
          assignedBy: user?.name ?? user?.email,
          isCylinderAssigned: true,
          challanDate,
          dispatchDate,
          ...(lotNumber && { lotNumber }),
        }));

      if (newData.length > 0) {
        await tx.client_cylinders.createMany({
          data: newData,
        });
      }

      // Add movement logs
      const logs = eligibleIds.map((cylinderId) => ({
        cylinderId,
        movement: CylinderMovement.OUTSIDE,
        remark: lotNumber
          ? `Cylinder assigned to ${client.name} (Lot: ${lotNumber})`
          : `Cylinder assigned to ${client.name}`,
        scannedBy: user?.name ?? user?.email,
        lotNumber,
        challanDate,
        dispatchDate,
      }));

      await tx.cylinder_logs.createMany({
        data: logs,
      });

      return {
        message: `Client assigned successfully to ${eligibleIds.length} cylinders`,
      };
    });
  }

  // ---------------- ASSIGN TO CLIENT / LOT OUTSIDE ----------------
  async assignToClient(assignToClientDto: AssignToClientDto, user: any) {
    const { clientId, cylinderIds, cylinderRfids, lotNumber, driverId, vehicleId, driverName, vehicleNumber, challanDate, dispatchDate, is_trial } = assignToClientDto;

    if (!cylinderIds && !cylinderRfids) {
      throw new BadRequestException("Either cylinderIds or cylinderRfids must be provided.");
    }

    let finalCylinderIds: string[] = [];

    if (cylinderRfids && cylinderRfids.length > 0) {
      const cylindersByRfid = await this.prisma.cylinders.findMany({
        where: { rfid: { in: cylinderRfids } },
        select: { id: true },
      });
      if (cylindersByRfid.length !== cylinderRfids.length) {
        throw new NotFoundException("Some cylinders with provided RFIDs were not found.");
      }
      finalCylinderIds = cylindersByRfid.map((c) => c.id);
    } else if (cylinderIds && cylinderIds.length > 0) {
      finalCylinderIds = cylinderIds;
    }

    let client: any = null;
    if (clientId) {
      client = await this.prisma.clients.findUnique({
        where: { id: clientId },
      });

      if (!client) {
        throw new NotFoundException("Client not found");
      }
    }

    return this.prisma.$transaction(async (tx) => {
      // Find cylinders that are actually available to be assigned (status must be INSIDE)
      const eligibleCylinders = await tx.cylinders.findMany({
        where: {
          id: { in: finalCylinderIds },
          status: "INSIDE"
        },
        select: { id: true }
      });

      const eligibleIds = eligibleCylinders.map(c => c.id);

      if (eligibleIds.length === 0) {
        return { message: "No eligible cylinders (status INSIDE) were found in the selection." };
      }

      // Update cylinders with lot number and optional client
      await tx.cylinders.updateMany({
        where: {
          id: { in: eligibleIds },
        },
        data: {
          clientId: clientId || null,
          status: 'OUTSIDE',
          lotNumber,
          is_client_owned: clientId ? 1 : 0,
          clientAssignedDate: new Date(),
          challanDate,
          dispatchDate,
        },
      });

      // Create movement logs
      const remark = client
        ? `Cylinder is out for delivery to ${client.name} (Lot: ${lotNumber})`
        : `Cylinder is out for delivery (Lot: ${lotNumber})`;

      const logs = eligibleIds.map((cylinderId) => ({
        cylinderId,
        movement: CylinderMovement.OUTSIDE,
        remark,
        scannedBy: user?.name ?? user?.email,
        lotNumber,
        driverId: driverId || null,
        vehicleId: vehicleId || null,
        driverName: driverName || null,
        vehicleNumber: vehicleNumber || null,
        challanDate,
        dispatchDate,
        is_trial: is_trial ?? 0,
      }));

      await tx.cylinder_logs.createMany({
        data: logs,
      });

      // Create client_cylinders records only if client is assigned
      if (clientId) {
        for (const cylinderId of eligibleIds) {
          await tx.client_cylinders.create({
            data: {
              cylinderId,
              clientId,
              assignedBy: user?.name ?? user?.email,
              lotNumber,
              isCylinderAssigned: true,
              challanDate,
              dispatchDate,
            },
          });
        }
      }

      const message = client
        ? `${eligibleIds.length} cylinders assigned to ${client.name} successfully (Lot: ${lotNumber})`
        : `${eligibleIds.length} cylinders marked as out for delivery successfully (Lot: ${lotNumber})`;

      return { message };
    });
  }

  // ---------------- UPDATE CLIENT FOR LOT ----------------
  async updateLotClientAssignment(lotNumber: string, body: { clientId: string; cylinderIds: string[] }, user: any) {
    const { clientId, cylinderIds } = body;

    const client = await this.prisma.clients.findUnique({
      where: { id: clientId },
    });

    if (!client) {
      throw new NotFoundException("Client not found");
    }

    return this.prisma.$transaction(async (tx) => {
      // Update cylinders with new client
      await tx.cylinders.updateMany({
        where: {
          id: { in: cylinderIds },
          lotNumber,
        },
        data: {
          clientId,
        },
      });

      // Remove existing client_cylinders records for these cylinders
      await tx.client_cylinders.deleteMany({
        where: {
          cylinderId: { in: cylinderIds },
        },
      });

      // Create new client_cylinders records
      const clientCylinders = cylinderIds.map((cylinderId) => ({
        cylinderId,
        clientId,
        assignedBy: user?.name ?? user?.email,
        lotNumber,
        isCylinderAssigned: true
      }));

      await tx.client_cylinders.createMany({
        data: clientCylinders,
      });

      // Create update log
      const logs = cylinderIds.map((cylinderId) => ({
        cylinderId,
        movement: CylinderMovement.OUTSIDE,
        remark: `Client updated to ${client.name} for Lot: ${lotNumber}`,
        scannedBy: user?.name ?? user?.email,
        lotNumber,
      }));

      await tx.cylinder_logs.createMany({
        data: logs,
      });

      return { message: `Client assignment updated successfully for Lot: ${lotNumber}` };
    });
  }


  async reconcilecylinders(body: RfidsDto) {
    try {
      await this.prisma.cylinders.updateMany({
        where: {
          rfid: { in: body.rfids },
        },
        data: {
          last_scanned_datetime: new Date(),
        },
      });
    } catch (error) {
      throw new BadRequestException(error);
    }
  }

  async dissolveCylinders(body: RfidsDto) {
    try {
      await this.prisma.$transaction(async (tx) => {

        const cylinders = await tx.cylinders.findMany({
          where: {
            rfid: { in: body.rfids },
          },
          select: {
            id: true,
          },
        });

        const cylinderIds = cylinders.map(c => c.id);
        if (cylinderIds.length === 0) return;

        const logs = await tx.cylinder_logs.findMany({
          where: {
            cylinderId: { in: cylinderIds },
          },
          orderBy: {
            createdAt: 'desc',
          },
        });

        const logIdsToKeep = new Set<string>();

        for (const cylinderId of cylinderIds) {
          const lastLog = logs.find(
            l =>
              l.cylinderId === cylinderId &&
              l.remark?.startsWith('Cylinder with Serial Number')
          );

          if (lastLog) {
            logIdsToKeep.add(lastLog.id);
          }
        }

        const logIdsToDelete = logs
          .filter(log => !logIdsToKeep.has(log.id))
          .map(log => log.id);

        if (logIdsToDelete.length > 0) {
          await tx.cylinder_logs.deleteMany({
            where: {
              id: { in: logIdsToDelete },
            },
          });
        }

        await tx.cylinders.updateMany({
          where: {
            id: { in: cylinderIds },
          },
          data: {
            rfid: null,
          },
        });
      });

    } catch (error) {
      throw new BadRequestException(error);
    }
  }


  // ---------------- FIND PLANT USERS (PAGINATED) ----------------
  async findPlantUsers(page: number, perPage: number, search?: string) {
    const { skip, take } = getPaginationOptions({ page, perPage });

    const where: any = {
      role: AccountType.PLANT,
    };

    if (search && search.trim() !== '') {
      where.name = {
        contains: search.trim(),
        mode: 'insensitive',
      };
    }

    const [plantUsers, total] = await this.prisma.$transaction([
      this.prisma.users.findMany({
        where,
        select: {
          id: true,
          name: true,
        },
        skip,
        take,
        orderBy: {
          name: 'asc',
        },
      }),
      this.prisma.users.count({ where }),
    ]);

    return formatPaginatedResponse(plantUsers, total, page, perPage);
  }


}