const db = require('../models');
const { sendResponse, sendError } = require('../utils/response');
const asyncHandler = require('../middlewares/asyncHandler');
const { getPagination, getPagingData } = require('../utils/pagination');
const { Op } = require('sequelize');
const productionQCService = require('../services/productionQCService');

const toFloat = (v) => {
  const n = parseFloat(v);
  return Number.isFinite(n) ? n : 0;
};

const deriveOperationTotalsFromComponents = (rows) => {
  const list = Array.isArray(rows) ? rows : [];
  if (list.length === 0) return { checkedQty: 0, reworkQty: 0 };
  const checkedQty = Math.min(...list.map((r) => toFloat(r.checked_qty)));
  const reworkQty = Math.max(...list.map((r) => toFloat(r.rework_qty)));
  return { checkedQty: Number.isFinite(checkedQty) ? checkedQty : 0, reworkQty: Number.isFinite(reworkQty) ? reworkQty : 0 };
};

const parseIdArray = (value) => {
  if (value == null || value === '') return [];
  if (Array.isArray(value)) {
    return Array.from(
      new Set(
        value
          .map((v) => parseInt(v, 10))
          .filter((v) => Number.isFinite(v) && v > 0)
      )
    );
  }
  if (typeof value === 'number') {
    const n = parseInt(value, 10);
    return Number.isFinite(n) && n > 0 ? [n] : [];
  }
  const str = String(value).trim();
  if (!str) return [];
  try {
    const parsed = JSON.parse(str);
    if (Array.isArray(parsed)) return parseIdArray(parsed);
  } catch (e) {
    // Fallback to CSV/single scalar parsing
  }
  if (str.includes(',')) return parseIdArray(str.split(','));
  const n = parseInt(str, 10);
  return Number.isFinite(n) && n > 0 ? [n] : [];
};

exports.getAll = asyncHandler(async (req, res) => {
  const { page = 1, limit = 25, search, status, companyId, plantId, fiscalYearId } = req.query;
  const { limit: limitNum, offset } = getPagination(page, limit);

  const where = {};
  if (companyId) where.company_id = companyId;
  else if (req.user && !req.user.isSuperAdmin) where.company_id = req.user.companyId;
  if (plantId) where.plant_id = plantId;
  if (fiscalYearId) where.fiscal_year_id = fiscalYearId;
  if (status) where.status = status;

  if (search) {
    where[Op.or] = [
      { production_qc_number: { [Op.like]: `%${search}%` } },
      { operation_code: { [Op.like]: `%${search}%` } },
      { operation_name: { [Op.like]: `%${search}%` } },
      { rejection_remarks: { [Op.like]: `%${search}%` } },
      { rework_remarks: { [Op.like]: `%${search}%` } }
    ];
  }

  const records = await db.ProductionQC.findAndCountAll({
    where,
    include: [
      {
        model: db.RouteCard,
        as: 'routeCard',
        attributes: ['id', 'route_card_number', 'status', 'previous_route_card_id', 'source_production_qc_id'],
        include: [
          {
            model: db.ProductionQC,
            as: 'sourceProductionQC',
            attributes: ['id', 'production_qc_number', 'rework_qty'],
            required: false
          }
        ]
      },
      { model: db.RouteCard, as: 'reworkRouteCard', attributes: ['id', 'route_card_number', 'status'], required: false },
      { model: db.WorkOrder, as: 'workOrder', attributes: ['id', 'work_order_number'], required: false },
      { model: db.ProductionOrder, as: 'productionOrder', attributes: ['id', 'production_order_number'], required: false },
      { model: db.User, as: 'qcByUser', attributes: ['id', 'first_name', 'last_name', 'username'], required: false }
    ],
    limit: limitNum,
    offset,
    order: [['id', 'DESC']]
  });

  const response = getPagingData(records, page, limitNum);
  const items = Array.isArray(response.items) ? response.items : [];
  const qcByIds = Array.from(
    new Set(
      items
        .flatMap((row) => {
          const asJson = typeof row?.toJSON === 'function' ? row.toJSON() : row;
          const ids = parseIdArray(asJson?.qc_by_ids);
          if (ids.length > 0) return ids;
          return asJson?.qc_by ? [parseInt(asJson.qc_by, 10)] : [];
        })
        .filter((id) => Number.isFinite(id) && id > 0)
    )
  );
  const userMap = new Map();
  if (qcByIds.length > 0) {
    const users = await db.User.findAll({
      where: { id: { [Op.in]: qcByIds } },
      attributes: ['id', 'first_name', 'last_name', 'username']
    });
    users.forEach((u) => {
      const fullName = `${u.first_name || ''} ${u.last_name || ''}`.trim();
      userMap.set(
        u.id,
        fullName || u.username || `User ${u.id}`
      );
    });
  }
  response.items = items.map((row) => {
    const asJson = typeof row?.toJSON === 'function' ? row.toJSON() : row;
    const ids = parseIdArray(asJson?.qc_by_ids);
    const fallbackIds = ids.length > 0 ? ids : (asJson?.qc_by ? [parseInt(asJson.qc_by, 10)] : []);
    const names = fallbackIds
      .map((id) => userMap.get(id))
      .filter(Boolean);
    return {
      ...asJson,
      qc_by_ids: fallbackIds,
      qc_by_names: names,
      qc_by_display: names.join(', ')
    };
  });
  sendResponse(res, 200, true, 'Production QC records retrieved successfully', response);
});

exports.getById = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const record = await db.ProductionQC.findByPk(id, {
    include: [
      {
        model: db.RouteCard,
        as: 'routeCard',
        attributes: ['id', 'route_card_number', 'status', 'previous_route_card_id', 'source_production_qc_id', 'production_qty'],
        include: [
          {
            model: db.ProductionQC,
            as: 'sourceProductionQC',
            attributes: ['id', 'production_qc_number', 'rework_qty'],
            required: false
          },
          {
            model: db.RouteCardOperation,
            as: 'operations',
            attributes: ['id', 'operation_sequence_no', 'operation_code', 'operation_name', 'status', 'completed_qty', 'checked_qty', 'rework_qty'],
            required: false
          },
          {
            model: db.Material,
            as: 'finishedMaterial',
            attributes: ['id', 'material_number', 'description'],
            required: false
          }
        ]
      },
      { model: db.RouteCard, as: 'reworkRouteCard', attributes: ['id', 'route_card_number', 'status'], required: false },
      { model: db.WorkOrder, as: 'workOrder', attributes: ['id', 'work_order_number'], required: false },
      {
        model: db.ProductionOrder,
        as: 'productionOrder',
        attributes: ['id', 'production_order_number', 'order_quantity', 'customer_po_id'],
        required: false,
        include: [
          {
            model: db.CustomerPO,
            as: 'customerPO',
            attributes: ['id', 'po_number'],
            required: false,
            include: [
              {
                model: db.BusinessPartner,
                as: 'customer',
                attributes: ['id', 'name'],
                required: false
              }
            ]
          }
        ]
      },
      { model: db.User, as: 'qcByUser', attributes: ['id', 'first_name', 'last_name', 'username'], required: false }
    ]
  });
  if (!record) return sendError(res, 'Production QC record not found', 404);

  const asJson = typeof record?.toJSON === 'function' ? record.toJSON() : record;
  const ids = parseIdArray(asJson?.qc_by_ids);
  const fallbackIds = ids.length > 0 ? ids : (asJson?.qc_by ? [parseInt(asJson.qc_by, 10)] : []);
  let names = [];
  if (fallbackIds.length > 0) {
    const users = await db.User.findAll({
      where: { id: { [Op.in]: fallbackIds } },
      attributes: ['id', 'first_name', 'last_name', 'username']
    });
    const userMap = new Map();
    users.forEach((u) => {
      const fullName = `${u.first_name || ''} ${u.last_name || ''}`.trim();
      userMap.set(u.id, fullName || u.username || `User ${u.id}`);
    });
    names = fallbackIds.map((uid) => userMap.get(uid)).filter(Boolean);
  }

  sendResponse(res, 200, true, 'Production QC record retrieved successfully', {
    ...asJson,
    qc_by_ids: fallbackIds,
    qc_by_names: names,
    qc_by_display: names.join(', ')
  });
});

exports.getComponentTracking = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const qc = await db.ProductionQC.findByPk(id, {
    include: [{ model: db.RouteCard, as: 'routeCard', attributes: ['id'], required: false }]
  });
  if (!qc) return sendError(res, 'Production QC record not found', 404);

  const rows = await db.ProductionQCComponentTracking.findAll({
    where: { production_qc_id: id },
    include: [{ model: db.Material, as: 'material', attributes: ['id', 'material_number', 'description'], required: false }],
    order: [['id', 'ASC']]
  });
  sendResponse(res, 200, true, 'Production QC component tracking retrieved successfully', rows);
});

exports.saveComponentTracking = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;

  const qc = await db.ProductionQC.findByPk(id);
  if (!qc) return sendError(res, 'Production QC record not found', 404);
  if (!['IN_PROGRESS', 'COMPLETED'].includes(qc.status)) {
    return sendError(res, 'Component QC can be saved only for IN_PROGRESS or COMPLETED records', 400);
  }

  const rows = Array.isArray(req.body?.rows) ? req.body.rows : [];
  if (rows.length === 0) return sendError(res, 'Rows are required', 400);

  const transaction = await db.sequelize.transaction();
  try {
    for (const row of rows) {
      const routeCardOperationId = row.route_card_operation_id ?? qc.route_card_operation_id;
      const materialId = row.material_id;
      if (!routeCardOperationId || !materialId) {
        await transaction.rollback();
        return sendError(res, 'route_card_operation_id and material_id are required', 400);
      }
      const checkedQty = toFloat(row.checked_qty);
      const reworkQty = toFloat(row.rework_qty);
      const remarks = String(row.remarks || '').trim();
      if (checkedQty < 0 || reworkQty < 0) {
        await transaction.rollback();
        return sendError(res, 'Checked Qty and Rework Qty cannot be negative', 400);
      }
      if (reworkQty > checkedQty) {
        await transaction.rollback();
        return sendError(res, 'Rework Qty cannot be greater than Checked Qty', 400);
      }
      if (reworkQty > 0 && !remarks) {
        await transaction.rollback();
        return sendError(res, 'Remarks is required when Rework Qty is greater than 0', 400);
      }

      const payload = {
        production_qc_id: qc.id,
        company_id: qc.company_id,
        plant_id: qc.plant_id,
        fiscal_year_id: qc.fiscal_year_id,
        customer_po_id: qc.customer_po_id || qc.customer_po,
        production_order_id: qc.production_order_id,
        route_card_id: qc.route_card_id,
        route_card_operation_id: routeCardOperationId,
        material_id: materialId,
        checked_qty: checkedQty,
        rework_qty: reworkQty,
        remarks: remarks || null,
        updated_by: userId
      };

      const existing = await db.ProductionQCComponentTracking.findOne({
        where: { production_qc_id: qc.id, material_id: materialId },
        transaction
      });
      if (existing) {
        await existing.update(payload, { transaction });
      } else {
        await db.ProductionQCComponentTracking.create({ ...payload, created_by: userId }, { transaction });
      }
    }

    const saved = await db.ProductionQCComponentTracking.findAll({
      where: { production_qc_id: qc.id },
      transaction
    });
    const derived = deriveOperationTotalsFromComponents(saved);

    // Keep operation-level QC aligned for reporting/flow; quantities are derived (not summed).
    await qc.update(
      {
        checked_qty: derived.checkedQty,
        rework_qty: derived.reworkQty,
        rework_remarks: derived.reworkQty > 0 ? (qc.rework_remarks || 'Derived from component QC') : null,
        updated_by: userId
      },
      { transaction }
    );

    await transaction.commit();
    sendResponse(res, 200, true, 'Production QC component tracking saved successfully', {
      derived_checked_qty: derived.checkedQty,
      derived_rework_qty: derived.reworkQty
    });
  } catch (error) {
    await transaction.rollback();
    return sendError(res, error.message || 'Failed to save component QC tracking', 500);
  }
});

exports.getQCInspectors = asyncHandler(async (req, res) => {
  const reqCompanyId = req.user?.companyId || null;
  const companyId = req.query.companyId || reqCompanyId || null;

  const users = await db.User.findAll({
    where: {
      ...(companyId ? { company_id: companyId } : {}),
      status: 'ACTIVE'
    },
    include: [
      {
        model: db.Role,
        as: 'roles',
        attributes: ['id', 'role_code', 'role_name'],
        through: { attributes: [] },
        required: true,
        where: {
          [Op.or]: [
            { role_code: { [Op.like]: '%QA%' } },
            { role_code: { [Op.like]: '%INSPECTOR%' } },
            { role_name: { [Op.like]: '%QA%' } },
            { role_name: { [Op.like]: '%Inspector%' } }
          ]
        }
      }
    ],
    attributes: ['id', 'first_name', 'last_name', 'username'],
    order: [['first_name', 'ASC'], ['username', 'ASC']]
  });

  const rows = users.map((u) => {
    const fullName = `${u.first_name || ''} ${u.last_name || ''}`.trim();
    return {
      id: u.id,
      username: u.username,
      full_name: fullName || u.username,
      display_name: fullName ? `${fullName} (${u.username})` : u.username
    };
  });

  sendResponse(res, 200, true, 'QC inspectors retrieved successfully', rows);
});

exports.moveFromRouteCardOperation = asyncHandler(async (req, res) => {
  const { routeCardOperationId } = req.params;
  const userId = req.user?.userId || null;
  const reqCompanyId = req.user?.companyId || null;

  const transaction = await db.sequelize.transaction();
  try {
    const operation = await db.RouteCardOperation.findByPk(routeCardOperationId, {
      include: [
        {
          model: db.RouteCard,
          as: 'routeCard',
          attributes: ['id', 'company_id', 'plant_id', 'fiscal_year_id', 'work_order_id', 'production_order_id', 'finished_material_id', 'route_card_number']
        }
      ],
      transaction
    });

    if (!operation) {
      await transaction.rollback();
      return sendError(res, 'Route Card operation not found', 404);
    }

    const routeCard = operation.routeCard;
    if (!routeCard) {
      await transaction.rollback();
      return sendError(res, 'Route Card not found for this operation', 404);
    }
    if (reqCompanyId && String(routeCard.company_id) !== String(reqCompanyId)) {
      await transaction.rollback();
      return sendError(res, 'Access denied for this Route Card operation', 403);
    }
    if (operation.status !== 'COMPLETED') {
      await transaction.rollback();
      return sendError(res, 'Only COMPLETED operations can be moved to QC', 400);
    }

    const existing = await db.ProductionQC.findOne({
      where: { route_card_operation_id: operation.id },
      transaction
    });
    if (existing) {
      await transaction.rollback();
      return sendError(res, 'This operation is already moved to Production QC', 400);
    }

    const created = await productionQCService.createFromRouteCardOperationIfNeeded({
      routeCardOperationId: operation.id,
      userId,
      transaction
    });

    await transaction.commit();

    const record = await db.ProductionQC.findByPk(created.id, {
      include: [
        { model: db.RouteCard, as: 'routeCard', attributes: ['id', 'route_card_number', 'status'] },
        { model: db.WorkOrder, as: 'workOrder', attributes: ['id', 'work_order_number'], required: false },
        { model: db.ProductionOrder, as: 'productionOrder', attributes: ['id', 'production_order_number'], required: false }
      ]
    });
    sendResponse(res, 201, true, 'Operation moved to Production QC successfully', record);
  } catch (error) {
    await transaction.rollback();
    sendError(res, error.message || 'Failed to move operation to Production QC', 500);
  }
});

exports.startQC = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const record = await db.ProductionQC.findByPk(id);
  if (!record) return sendError(res, 'Production QC record not found', 404);

  if (record.status !== 'PENDING') {
    return sendError(res, 'Only PENDING records can be started', 400);
  }

  await record.update({
    status: 'IN_PROGRESS',
    actual_start: record.actual_start || new Date(),
    actual_end: null
  });
  sendResponse(res, 200, true, 'Production QC started successfully', record);
});

exports.completeQC = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const record = await db.ProductionQC.findByPk(id);
  if (!record) return sendError(res, 'Production QC record not found', 404);

  if (record.status !== 'IN_PROGRESS') {
    return sendError(res, 'Only IN_PROGRESS records can be completed', 400);
  }

  const totalQty = parseFloat(record.total_qty || 0);
  const acceptedQty = req.body.accepted_qty !== undefined ? parseFloat(req.body.accepted_qty) || 0 : parseFloat(record.accepted_qty || 0);
  const hasCheckedQty = req.body.checked_qty !== undefined && req.body.checked_qty !== null && String(req.body.checked_qty).trim() !== '';
  if (!hasCheckedQty) {
    return sendError(res, 'Checked Qty is required', 400);
  }
  const checkedQty = req.body.checked_qty !== undefined ? parseFloat(req.body.checked_qty) || 0 : 0;
  const reworkQty = req.body.rework_qty !== undefined ? parseFloat(req.body.rework_qty) || 0 : 0;
  const reworkRemarks = String(req.body.rework_remarks || '').trim();
  const qcByIds = parseIdArray(
    req.body.qc_by_ids !== undefined
      ? req.body.qc_by_ids
      : (req.body.qc_by !== undefined ? req.body.qc_by : (record.qc_by_ids || record.qc_by))
  );
  const qcBy = qcByIds[0] || null;
  const actualStart = req.body.actual_start ? new Date(req.body.actual_start) : (record.actual_start ? new Date(record.actual_start) : null);
  const actualEnd = req.body.actual_end ? new Date(req.body.actual_end) : new Date();

  if (acceptedQty < 0 || checkedQty < 0 || reworkQty < 0) {
    return sendError(res, 'Accepted Qty, Checked Qty and Rework Qty cannot be negative', 400);
  }
  if (acceptedQty > totalQty) {
    return sendError(res, `Accepted Qty cannot be greater than Total Qty (${totalQty})`, 400);
  }
  if (checkedQty > acceptedQty) {
    return sendError(res, 'Checked Qty cannot be greater than Accepted Qty', 400);
  }
  if (reworkQty > checkedQty) {
    return sendError(res, 'Rework Qty cannot be greater than Checked Qty', 400);
  }
  if (reworkQty > 0 && !reworkRemarks) {
    return sendError(res, 'Rework Remarks is required when Rework Qty is greater than 0', 400);
  }
  if (!qcBy || qcByIds.length === 0) {
    return sendError(res, 'QC Name is required', 400);
  }
  const qcUsers = await db.User.findAll({
    where: {
      id: { [Op.in]: qcByIds },
      status: 'ACTIVE'
    },
    attributes: ['id']
  });
  if (qcUsers.length !== qcByIds.length) {
    return sendError(res, 'One or more selected QC users are invalid or inactive', 400);
  }
  if (actualStart && Number.isNaN(actualStart.getTime())) {
    return sendError(res, 'Invalid Actual Start date/time', 400);
  }
  if (actualEnd && Number.isNaN(actualEnd.getTime())) {
    return sendError(res, 'Invalid Actual End date/time', 400);
  }
  if (actualStart && actualEnd && actualEnd < actualStart) {
    return sendError(res, 'Actual End date/time cannot be earlier than Actual Start date/time', 400);
  }

  await record.update({
    accepted_qty: acceptedQty,
    checked_qty: checkedQty,
    rework_qty: reworkQty,
    rework_remarks: reworkQty > 0 ? reworkRemarks : null,
    qc_by: qcBy,
    qc_by_ids: JSON.stringify(qcByIds),
    qc_at: new Date(),
    actual_start: actualStart,
    actual_end: actualEnd,
    status: 'COMPLETED'
  });

  sendResponse(res, 200, true, 'Production QC completed successfully', record);
});

exports.approveQC = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const transaction = await db.sequelize.transaction();
  try {
    const record = await db.ProductionQC.findByPk(id, { transaction });
    if (!record) {
      await transaction.rollback();
      return sendError(res, 'Production QC record not found', 404);
    }

    if (!['COMPLETED', 'APPROVED'].includes(record.status)) {
      await transaction.rollback();
      return sendError(res, 'Only COMPLETED or APPROVED records can be processed for approval', 400);
    }

    if (record.status !== 'APPROVED') {
      await record.update({ status: 'APPROVED' }, { transaction });
    }

    const operation = await db.RouteCardOperation.findByPk(record.route_card_operation_id, { transaction });
    if (operation) {
      await operation.update(
        {
          checked_qty: parseFloat(record.checked_qty || 0),
          rework_qty: parseFloat(record.rework_qty || 0),
          updated_by: req.user?.userId || operation.updated_by
        },
        { transaction }
      );
    }

    await transaction.commit();
    sendResponse(res, 200, true, 'Production QC approved successfully', {
      ...record.toJSON(),
      rework_route_card: null,
      rework_in_existing_route_card: true
    });
  } catch (error) {
    await transaction.rollback();
    sendError(res, error.message || 'Failed to approve Production QC', 500);
  }
});

exports.generateReworkRouteCard = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const transaction = await db.sequelize.transaction();
  try {
    const record = await db.ProductionQC.findByPk(id, { transaction });
    if (!record) {
      await transaction.rollback();
      return sendError(res, 'Production QC record not found', 404);
    }

    if (record.status !== 'APPROVED') {
      await transaction.rollback();
      return sendError(res, 'Rework can be marked only after QC APPROVED', 400);
    }

    const reworkQty = parseFloat(record.rework_qty || 0);
    if (!(reworkQty > 0)) {
      await transaction.rollback();
      return sendError(res, 'Rework Qty should be greater than 0', 400);
    }

    await transaction.commit();
    sendResponse(res, 200, true, 'Rework marked in existing Route Card successfully', {
      route_card_id: record.route_card_id,
      route_card_operation_id: record.route_card_operation_id,
      rework_qty: reworkQty
    });
  } catch (error) {
    await transaction.rollback();
    const detail = Array.isArray(error?.errors) && error.errors.length
      ? error.errors.map((e) => e.message).join(', ')
      : null;
    sendError(res, detail || error.message || 'Failed to generate rework Route Card', 500);
  }
});
