import ExcelJS from 'exceljs';
import mongoose, { FilterQuery, Types } from 'mongoose';

import {
  IUnit,
  IUnitDoc,
  IUnitFilter,
  NewCreatedUnit,
  UpdateUnitBody,
} from '@/modules/project/project.interface';
import { addBulkUploadJob } from '@/producers/units/unit.producer.js';
import { parsePopulateString } from '@/shared/utils';
import { getObjectId } from '@/shared/utils/commonHelper';
import { ApiError } from '@/shared/utils/errors';
import { PaginateOptions } from '@/shared/utils/plugins/paginate/paginate';
import { defaultStatus } from '@/shared/utils/responseCode/httpStatusAlias';
import responseCodes from '@/shared/utils/responseCode/responseCode';
import Unit from './unit.model';
import { getCustomFieldsByFormName } from '@/modules/customFields/customFields.service';

const { ProjectResponseCodes } = responseCodes;

export const createUnit = async (
  data: NewCreatedUnit,
): Promise<IUnitDoc | null> => {
  // Get project to check blockLayout
  const project = await import('@/modules/project/project.model').then(m => m.default.findById(data.project).select('hasBlockLayout').lean());
  if (!project)
    throw new ApiError(defaultStatus.BAD_REQUEST, 'Invalid project', true, '', ProjectResponseCodes.PROJECT_ERROR);

  let duplicate;
  if (project.hasBlockLayout) {
    duplicate = await Unit.findOne({ project: data.project, block: data.block, unitNumber: data.unitNumber });
    if (duplicate)
      throw new ApiError(400, 'Unit with same block and unit number already exists', true, '', ProjectResponseCodes.PROJECT_ERROR);
  } else {
    duplicate = await Unit.findOne({ project: data.project, unitNumber: data.unitNumber });
    if (duplicate)
      throw new ApiError(400, 'Unit with same unit number already exists', true, '', ProjectResponseCodes.PROJECT_ERROR);
  }

  let unit: IUnitDoc | null;
  try {
    unit = await Unit.create(data);
  } catch (_error) {
    console.error('[Unit Create Error]', _error);
    throw new ApiError(
      defaultStatus.OK,
      'Failed to create unit',
      true,
      '',
      ProjectResponseCodes.PROJECT_ERROR,
    );
  }

  return unit;
};

export const getUnitById = async (id: string): Promise<IUnitDoc | null> => {
  const populateStr =
    'propertyType:name;project:projectName;updatedBy:firstName email lastName createdAt updatedAt;createdBy:firstName email lastName createdAt updatedAt';
  const populateFields = parsePopulateString(populateStr);
  const unit = await Unit.findById(id).populate(populateFields).lean();
  if (!unit)
    throw new ApiError(
      defaultStatus.OK,
      'Unit not found',
      true,
      '',
      ProjectResponseCodes.PROJECT_NOT_FOUND,
    );

  return unit;
};

export const updateUnit = async (
  id: Types.ObjectId,
  updateData: UpdateUnitBody,
): Promise<IUnitDoc | null> => {
  // Find the unit to get project and block info
  const existing = await Unit.findById(id);
  if (!existing)
    throw new ApiError(defaultStatus.OK, 'Unit not found', true, '', ProjectResponseCodes.PROJECT_NOT_FOUND);

  // Get project to check blockLayout
  const project = await import('@/modules/project/project.model').then(m => m.default.findById(existing.project).select('hasBlockLayout').lean());
  if (!project)
    throw new ApiError(defaultStatus.BAD_REQUEST, 'Invalid project', true, '', ProjectResponseCodes.PROJECT_ERROR);

  // Use updated or existing values for block/unitNumber
  const block = updateData.block !== undefined ? updateData.block : existing.block;
  const unitNumber = updateData.unitNumber !== undefined ? updateData.unitNumber : existing.unitNumber;

  let duplicate;
  if (project.hasBlockLayout) {
    duplicate = await Unit.findOne({ project: existing.project, block, unitNumber, _id: { $ne: id } });
    if (duplicate)
      throw new ApiError(400, 'Unit with same block and unit number already exists', true, '', ProjectResponseCodes.PROJECT_ERROR);
  } else {
    duplicate = await Unit.findOne({ project: existing.project, unitNumber, _id: { $ne: id } });
    if (duplicate)
      throw new ApiError(400, 'Unit with same unit number already exists', true, '', ProjectResponseCodes.PROJECT_ERROR);
  }

  let unit: IUnitDoc | null;
  try {
    unit = await Unit.findByIdAndUpdate(id, updateData, {
      new: true,
      runValidators: true,
    });
  } catch (_error) {
    throw new ApiError(
      defaultStatus.OK,
      'Failed to update unit',
      true,
      '',
      ProjectResponseCodes.PROJECT_ERROR,
    );
  }

  if (!unit)
    throw new ApiError(
      defaultStatus.OK,
      'Unit not found',
      true,
      '',
      ProjectResponseCodes.PROJECT_NOT_FOUND,
    );

  return unit;
};

export const deleteUnit = async (id: string): Promise<boolean | null> => {
  let unit: boolean | null;
  try {
    unit = await Unit.findByIdAndDelete(id);
  } catch (_error) {
    throw new ApiError(
      defaultStatus.OK,
      'Failed to delete unit',
      true,
      '',
      ProjectResponseCodes.PROJECT_ERROR,
    );
  }

  if (!unit)
    throw new ApiError(
      defaultStatus.OK,
      'Unit not found',
      true,
      '',
      ProjectResponseCodes.PROJECT_NOT_FOUND,
    );

  return true;
};

export const queryUnits = async (
  filter: IUnitFilter = {},
  options: PaginateOptions = {},
) => {
  if (!options.fields)
    options.fields =
      'id unitNumber size price propertyType project floor status createdBy createdAt';

  return Unit.paginate(filter, options);
};

export const getUnitsByProjectId = async (
  projectId: Types.ObjectId,
  options: PaginateOptions = {},
  rowFilter: IUnitFilter = {},
) => {
  const { search, status, statuses, createdBy, propertyType, ...baseFilters } = rowFilter;
  const searchAsNumber = Number(search);
  const isNumeric = !isNaN(searchAsNumber);

  const merged = {
    ...baseFilters,
    ...(projectId && { project: getObjectId(projectId) }),
    ...(propertyType && { propertyType: getObjectId(propertyType) }),
    ...(createdBy && { createdBy: getObjectId(createdBy) }),
    ...(status && status !== 'all' && { status }),
  };

  const filter: FilterQuery<IUnit> = Object.fromEntries(
    Object.entries(merged).filter(([, v]) => v != null),
  ) as FilterQuery<IUnit>;

  if (typeof statuses === 'string' && statuses.trim().length > 0) {
    const allowed = new Set(['sold', 'hold', 'available']);
    const statusList = statuses
      .split(',')
      .map((s) => s.trim())
      .filter((s) => allowed.has(s));

    if (statusList.length > 0) filter.status = { $in: statusList };
  }

  // Build search filter separately to apply after lookups
  const searchFilter: any[] = [];
  if (search && typeof search === 'string') {
    searchFilter.push(
      { unitNumber: { $regex: search, $options: 'i' } },
      ...(isNumeric ? [{ price: searchAsNumber }] : []),
      ...(isNumeric ? [{ size: searchAsNumber }] : []),
      { customerName: { $regex: search, $options: 'i' } },
      { soldBy: { $regex: search, $options: 'i' } },
    );
  }

  const aggregation: any[] = [
    { $match: filter },
    
    // Lookup PropertyType (Category)
    {
      $lookup: {
        from: 'categories',
        localField: 'propertyType',
        foreignField: '_id',
        as: 'propertyTypeArray',
      },
    },
    { $addFields: { propertyType: { $arrayElemAt: ['$propertyTypeArray', 0] } } },
    
    // Lookup UnitBookingOrHold (most recent)

    {
      $lookup: {
        from: 'unitbookingorholds',
        let: { unitId: '$_id' },
        pipeline: [
          { $match: { $expr: { $eq: ['$unit', '$$unitId'] } } },
          { $sort: { createdAt: -1 } },
          { $limit: 1 },
        ],
        as: 'bookingArray',
      },
    },
    { $addFields: { booking: { $arrayElemAt: ['$bookingArray', 0] } } },
    
    // Lookup Customer (only active bookings, not cancelled)

    {
      $lookup: {
        from: 'customers',
        let: { bookingId: '$booking._id' },
        pipeline: [
          {
            $match: {
              $expr: {
                $and: [
                  { $eq: ['$unitBookingOrHold', '$$bookingId'] },
                  { $ne: ['$bookingStatus', 'cancelled'] },
                ],
              },
            },
          },
          { $limit: 1 },
        ],
        as: 'customerArray',
      },
    },
    { $addFields: { customer: { $arrayElemAt: ['$customerArray', 0] } } },
    
    // Lookup soldBy user
    {
      $lookup: {
        from: 'users',
        let: { soldById: '$booking.soldBy' },
        pipeline: [
          { $match: { $expr: { $eq: ['$_id', '$$soldById'] } } },
          { $limit: 1 },
        ],
        as: 'soldByUserArray',
      },
    },
    { $addFields: { soldByUser: { $arrayElemAt: ['$soldByUserArray', 0] } } },
    
    // Lookup customer payments
    {
      $lookup: {
        from: 'customerpayments',
        let: { customerId: '$customer._id' },
        pipeline: [
          { $match: { $expr: { $eq: ['$customerId', '$$customerId'] } } },
        ],
        as: 'payments',
      },
    },
    { $addFields: { payment: { $arrayElemAt: ['$payments', 0] } } },
    
    // Add computed fields
    {
      $addFields: {
        soldAt: { $ifNull: ['$payment.totalAmount', null] },
        soldRate: {
          $cond: {
            if: { $and: [{ $gt: ['$size', 0] }, { $ne: ['$payment.totalAmount', null] }] },
            then: { $divide: ['$payment.totalAmount', '$size'] },
            else: null,
          },
        },
        customerName: { $ifNull: ['$customer.name', null] },
        soldBy: {
          $trim: {
            input: {
              $concat: [
                { $ifNull: ['$soldByUser.firstName', ''] },
                ' ',
                { $ifNull: ['$soldByUser.lastName', ''] },
              ],
            },
          },
        },
        salesDate: { $ifNull: ['$booking.createdAt', null] },
        paymentReceived: { $ifNull: ['$payment.paidAmount', 0] },
        paymentDue: {
          $subtract: [
            { $ifNull: ['$payment.totalAmount', 0] },
            { $ifNull: ['$payment.paidAmount', 0] },
          ],
        },
      },
    },
    
    // Apply search filter if provided
    ...(searchFilter.length > 0 ? [{ $match: { $or: searchFilter } }] : []),
    
    // Clean up temporary fields
    {
      $project: {
        propertyTypeArray: 0,
        bookingArray: 0,
        booking: 0,
        customerArray: 0,
        customer: 0,
        soldByUserArray: 0,
        soldByUser: 0,
        payments: 0,
        payment: 0,
      },
    },
  ];

  return Unit.paginate(filter, { ...options, aggregation });
};

export const getUnitsAnalytics = async (projectId: Types.ObjectId) => {
  // Same booking + customer resolution as getUnitsByProjectId (latest booking, non-cancelled customer).
  // Payment totals use $sum over the payments array — $paymentInfo.paidAmount is an array after $lookup,
  // so { $sum: { $ifNull: ['$paymentInfo.paidAmount', 0] } } was non-numeric and summed to 0 in $group.
  const stats = await Unit.aggregate([
    {
      $match: { project: projectId },
    },

    {
      $lookup: {
        from: 'unitbookingorholds',
        let: { unitId: '$_id' },
        pipeline: [
          { $match: { $expr: { $eq: ['$unit', '$$unitId'] } } },
          { $sort: { createdAt: -1 } },
          { $limit: 1 },
        ],
        as: 'bookingArray',
      },
    },
    { $addFields: { booking: { $arrayElemAt: ['$bookingArray', 0] } } },

    {
      $lookup: {
        from: 'customers',
        let: { bookingId: '$booking._id' },
        pipeline: [
          {
            $match: {
              $expr: {
                $and: [
                  { $eq: ['$unitBookingOrHold', '$$bookingId'] },
                  { $ne: ['$bookingStatus', 'cancelled'] },
                ],
              },
            },
          },
          { $limit: 1 },
        ],
        as: 'customerArray',
      },
    },
    { $addFields: { customer: { $arrayElemAt: ['$customerArray', 0] } } },

    {
      $lookup: {
        from: 'customerpayments',
        let: { customerId: '$customer._id' },
        pipeline: [{ $match: { $expr: { $eq: ['$customerId', '$$customerId'] } } }],
        as: 'payments',
      },
    },

    {
      $addFields: {
        totalAmountFromPayments: { $ifNull: [{ $sum: '$payments.totalAmount' }, 0] },
        paidAmountFromPayments: { $ifNull: [{ $sum: '$payments.paidAmount' }, 0] },
      },
    },

    {
      $addFields: {
        finalTotalAmount: {
          $cond: {
            if: { $gt: ['$totalAmountFromPayments', 0] },
            then: '$totalAmountFromPayments',
            else: '$price',
          },
        },
        dueAmount: {
          $subtract: [
            {
              $cond: {
                if: { $gt: ['$totalAmountFromPayments', 0] },
                then: '$totalAmountFromPayments',
                else: '$price',
              },
            },
            '$paidAmountFromPayments',
          ],
        },
      },
    },

    {
      $group: {
        _id: '$status',
        count: { $sum: 1 },
        totalValue: { $sum: '$finalTotalAmount' },
        totalPaid: { $sum: '$paidAmountFromPayments' },
        totalDue: { $sum: '$dueAmount' },
      },
    },
  ]);

  const sold = stats.find((s) => s._id === 'sold') || { count: 0, totalValue: 0, totalPaid: 0, totalDue: 0 };
  const available = stats.find((s) => s._id === 'available') || { count: 0, totalValue: 0, totalPaid: 0, totalDue: 0 };
  const hold = stats.find((s) => s._id === 'hold') || { count: 0, totalValue: 0, totalPaid: 0, totalDue: 0 };

  const totalUnits = sold.count + available.count + hold.count;

  const totalReceived = sold.totalPaid + hold.totalPaid;
  const totalDue = sold.totalDue + hold.totalDue;

  const totalValue = totalReceived + totalDue;
  const receivedPercentage = totalValue > 0 ? Math.round((totalReceived / totalValue) * 100) : 0;
  const duePercentage = totalValue > 0 ? Math.round((totalDue / totalValue) * 100) : 0;

  return {
    totalUnits,
    soldUnits: sold.count,
    holdUnits: hold.count,
    availableUnits: available.count,
    totalSalesValue: totalValue,
    totalReceived,
    totalDue,
    receivedPercentage,
    duePercentage,
  };
};

export const generateUnitsExcelTemplate = async (
  hasBlockLayout: boolean,
  companyId?: Types.ObjectId,
): Promise<Buffer> => {
  try {
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('Units');

    const baseColumns = [
      { header: 'Unit Number', key: 'unitNumber', width: 15 },
      { header: 'Size (sqyds)', key: 'size', width: 15 },
      { header: 'Price', key: 'price', width: 15 },
      { header: 'Status', key: 'status', width: 15 },
    ];
    const blockFloorColumns = [
      { header: 'Block', key: 'block', width: 15 },
      { header: 'Floor', key: 'floor', width: 15 },
    ];

    // Fetch custom fields for unit form
    const customFieldsResult = await getCustomFieldsByFormName(companyId, 'unit');

    // Define interface for custom field
    interface CustomFieldType {
      label: string;
      key: string;
      type: string;
      options?: { label: string; value: string }[];
    }

    // Cast to proper type
    const customFields: CustomFieldType[] = customFieldsResult as unknown as CustomFieldType[];

    // Build custom field columns
    const customFieldColumns = customFields.map((field) => ({
      header: field.label,
      key: field.key,
      width: 20,
    }));

    // Combine all columns
    worksheet.columns = hasBlockLayout
      ? [...blockFloorColumns, ...baseColumns, ...customFieldColumns]
      : [...baseColumns, ...customFieldColumns];

    worksheet.getRow(1).font = { bold: true };
    worksheet.getRow(1).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFD3D3D3' },
    };

    // Generate sample data with custom fields
    const generateSampleCustomFields = () => {
      const sampleData: Record<string, unknown> = {};
      customFields.forEach((field) => {
        // Generate sample data based on field type
        switch (field.type) {
          case 'text':
          case 'textarea':
            sampleData[field.key] = `Sample ${field.label}`;
            break;
          case 'number':
            sampleData[field.key] = Math.floor(Math.random() * 1000) + 1;
            break;
          case 'email':
            sampleData[field.key] = 'sample@example.com';
            break;
          case 'phone':
            sampleData[field.key] = '9876543210';
            break;
          case 'date':
            sampleData[field.key] = new Date().toISOString().split('T')[0];
            break;
          case 'dropdown':
          case 'radio':
            // Use first option if available
            if (field.options && field.options.length > 0) {
              sampleData[field.key] = field.options[0].value;
            } else {
              sampleData[field.key] = 'Option 1';
            }
            break;
          case 'checkbox':
            sampleData[field.key] = true;
            break;
          default:
            sampleData[field.key] = '';
        }
      });
      return sampleData;
    };

    const baseSampleData = hasBlockLayout
      ? [
          {
            block: 'Block - A',
            floor: 1,
            unitNumber: 'A-101',
            size: 1200,
            price: 150000,
            status: 'available',
          },
          {
            block: 'Block - A',
            floor: 1,
            unitNumber: 'A-102',
            size: 1500,
            price: 180000,
            status: 'hold',
          },
        ]
      : [
          {
            unitNumber: '101',
            size: 1200,
            price: 150000,
            status: 'available',
          },
          {
            unitNumber: '102',
            size: 1500,
            price: 180000,
            status: 'hold',
          },
        ];

    // Merge base sample data with custom fields
    const sampleData = baseSampleData.map(base => ({
      ...base,
      ...generateSampleCustomFields(),
    }));

    sampleData.forEach((unit) => {
      worksheet.addRow(unit);
    });

    worksheet.eachRow((row, rowNumber) => {
      if (rowNumber > 1)
        row.eachCell((cell) => {
          cell.border = {
            top: { style: 'thin' },
            left: { style: 'thin' },
            bottom: { style: 'thin' },
            right: { style: 'thin' },
          };
        });
    });

    return (await workbook.xlsx.writeBuffer()) as Buffer;
  } catch (_error) {
    console.error('Error generating Excel template:', _error);
    throw new ApiError(
      defaultStatus.OK,
      'Failed to generate Excel template',
      true,
      '',
      ProjectResponseCodes.PROJECT_ERROR,
    );
  }
};

export const processBulkUpload = async (
  filePath: string,
  fileName: string,
  projectId: string,
  propertyTypeId: string,
  userId: string,
  companyId?: string,
): Promise<string> => {
  try {
    const jobId = await addBulkUploadJob({
      filePath,
      fileName,
      projectId,
      propertyTypeId,
      userId,
      companyId,
    });

    return jobId;
  } catch (error) {
    console.error('Error queuing bulk upload job:', error);
    throw new ApiError(
      defaultStatus.OK,
      'Failed to process bulk upload',
      true,
      '',
      ProjectResponseCodes.PROJECT_ERROR,
    );
  }
};

export const getUnitsPerBlockAndFloor = async (projectId?: string) => {
  try {
    const matchStage = projectId ? { project: getObjectId(projectId) } : {};

    const data = await Unit.aggregate([
      { $match: matchStage },
      {
        $addFields: {
          __unitNumberInt: {
            $convert: {
              input: '$unitNumber',
              to: 'int',
              onError: null,
              onNull: null,
            },
          },
        },
      },
      {
        $sort: {
          block: 1,
          floor: 1,
          __unitNumberInt: 1,
          unitNumber: 1,
        },
      },
      {
        $group: {
          _id: {
            block: '$block',
            floor: '$floor',
          },
          units: {
            $push: {
              unitNo: '$unitNumber',
              details: {
                status: '$status',

                beds: { $ifNull: ['$beds', 2] },
                baths: { $ifNull: ['$baths', 2] },
                balconies: { $ifNull: ['$balconies', 1] },
                facing: { $ifNull: ['$facing', 'East'] },

                parking: { $ifNull: ['$parking', 2] },
                price: '$price',
              },
            },
          },
        },
      },
      {
        $sort: {
          '_id.block': 1,
          '_id.floor': 1,
        },
      },
      {
        $group: {
          _id: '$_id.block',
          floors: {
            $push: {
              name: { $concat: ['Floor ', { $toString: '$_id.floor' }] },
              units: '$units',
            },
          },
        },
      },
      {
        $sort: {
          _id: 1,
        },
      },
      {
        $project: {
          _id: 0,
          name: '$_id',
          floors: 1,
        },
      },
    ]);

    return data;
  } catch (error) {
    console.error('Aggregation failed:', error);

    throw new ApiError(
      defaultStatus.INTERNAL_SERVER_ERROR,
      'Failed to fetch grouped unit data',
      true,
      '',
      ProjectResponseCodes.PROJECT_ERROR,
    );
  }
};