const { Transaction } = require('sequelize');
const db = require('../models');

const EPS = 0.0005;

const round3 = (value) => Number((parseFloat(value || 0) || 0).toFixed(3));

const creditMaterialIssueEnabled = () => String(process.env.PRODUCTION_WIP_CREDIT_MATERIAL_ISSUE || 'true').toLowerCase() === 'true';

/** One balance row per company + plant + material (shared Production Storage pool). */
async function ensureBalanceRow(transaction, { company_id, plant_id, material_id, uom_id }) {
  const [row] = await db.ProductionWipBalance.findOrCreate({
    where: { company_id, plant_id, material_id },
    defaults: {
      company_id,
      plant_id,
      production_order_id: null,
      uom_id: uom_id || null,
      available_qty: 0,
    },
    transaction,
  });
  if (uom_id && !row.uom_id) {
    await row.update({ uom_id }, { transaction });
  }
  await row.reload({ transaction, lock: Transaction.LOCK.UPDATE });
  return row;
}

async function applyMovement(transaction, opts) {
  const {
    company_id,
    plant_id,
    production_order_id,
    material_id,
    uom_id,
    direction,
    movement_type,
    qty,
    route_card_operation_id,
    material_issue_id,
    production_order_material_receipt_line_id,
    remarks,
    created_by,
  } = opts;

  const q = round3(qty);
  if (q <= EPS) return;

  const bal = await ensureBalanceRow(transaction, {
    company_id,
    plant_id,
    material_id,
    uom_id,
  });

  const cur = round3(bal.available_qty);
  const delta = direction === 'IN' ? q : -q;
  const next = round3(cur + delta);
  if (next < -EPS) {
    throw new Error(
      `Insufficient Production Storage (WIP) for material id ${material_id}. Available ${cur}, requested OUT ${q}.`
    );
  }

  await bal.update({ available_qty: next }, { transaction });

  await db.ProductionWipLedger.create(
    {
      company_id,
      plant_id,
      production_order_id,
      material_id,
      uom_id: uom_id || bal.uom_id,
      direction,
      movement_type,
      qty: q,
      route_card_operation_id: route_card_operation_id || null,
      material_issue_id: material_issue_id || null,
      production_order_material_receipt_line_id: production_order_material_receipt_line_id || null,
      remarks: remarks || null,
      created_by: created_by || null,
    },
    { transaction }
  );
}

exports.creditFromReceiptLineDelta = async (
  transaction,
  {
    company_id,
    plant_id,
    production_order_id,
    material_id,
    uom_id,
    deltaQty,
    receipt_line_id,
    userId,
  }
) => {
  const d = round3(deltaQty);
  if (d <= EPS) return;
  await applyMovement(transaction, {
    company_id,
    plant_id,
    production_order_id,
    material_id,
    uom_id,
    direction: 'IN',
    movement_type: 'RECEIPT_PO_RECEIVE',
    qty: d,
    production_order_material_receipt_line_id: receipt_line_id,
    remarks: 'Production receive → WIP',
    created_by: userId,
  });
};

/** When PO material receive qty is reduced, remove qty from WIP (fails if insufficient). */
exports.debitFromReceiptLineDelta = async (
  transaction,
  {
    company_id,
    plant_id,
    production_order_id,
    material_id,
    uom_id,
    deltaQty,
    receipt_line_id,
    userId,
  }
) => {
  const d = round3(deltaQty);
  if (d >= -EPS) return;
  const qtyOut = round3(-d);
  await applyMovement(transaction, {
    company_id,
    plant_id,
    production_order_id,
    material_id,
    uom_id,
    direction: 'OUT',
    movement_type: 'RECEIPT_PO_ADJUST_DOWN',
    qty: qtyOut,
    production_order_material_receipt_line_id: receipt_line_id,
    remarks: 'Production receive reduced — WIP adjusted',
    created_by: userId,
  });
};

exports.creditFromMaterialIssueReceive = async (transaction, { issue, userId }) => {
  if (!creditMaterialIssueEnabled()) return;
  const poId = issue.production_order_id;
  if (!(poId > 0)) return;

  const items = issue.items || [];
  for (const item of items) {
    const qty = round3(item.quantity);
    if (qty <= EPS) continue;
    await applyMovement(transaction, {
      company_id: issue.company_id,
      plant_id: issue.plant_id,
      production_order_id: poId,
      material_id: item.material_id,
      uom_id: item.uom_id,
      direction: 'IN',
      movement_type: 'RECEIPT_MATERIAL_ISSUE',
      qty,
      material_issue_id: issue.id,
      remarks: `Material issue ${issue.issue_number || issue.id} receive → WIP`,
      created_by: userId,
    });
  }
};

/**
 * Production Storage balances for the plant of this PO (shared pool — same qty for every operation using that material).
 */
exports.listBalancesForProductionOrder = async (productionOrderId) => {
  if (!(productionOrderId > 0)) return [];
  const po = await db.ProductionOrder.findByPk(productionOrderId, {
    attributes: ['id', 'company_id', 'plant_id'],
  });
  if (!po) return [];
  return db.ProductionWipBalance.findAll({
    where: {
      company_id: po.company_id,
      plant_id: po.plant_id,
    },
    include: [
      {
        model: db.Material,
        as: 'material',
        attributes: [
          'id',
          'material_number',
          'description',
          'drawing_number',
          'material_type',
          'length',
          'width',
          'thickness',
          'dimension_unit',
        ],
      },
      { model: db.UOM, as: 'uom', attributes: ['id', 'uom_code', 'uom_name'], required: false },
    ],
    order: [['material_id', 'ASC']],
  });
};

exports.getConsumptionRowsForOperation = async (routeCardOperationId) => {
  return db.RouteCardOperationMaterialConsumption.findAll({
    where: { route_card_operation_id: routeCardOperationId },
    include: [
      { model: db.Material, as: 'material', attributes: ['id', 'material_number', 'description'] },
      { model: db.UOM, as: 'uom', attributes: ['id', 'uom_code'], required: false },
    ],
  });
};

async function applyConsumeOrScrapDelta(
  transaction,
  { company_id, plant_id, production_order_id },
  {
    materialId,
    uomId,
    delta,
    outMovementType,
    routeCardOperationId,
    userId,
    remarkIn,
    remarksOut,
  }
) {
  const d = round3(delta);
  if (Math.abs(d) <= EPS) return;

  if (d > EPS) {
    await applyMovement(transaction, {
      company_id,
      plant_id,
      production_order_id,
      material_id: materialId,
      uom_id: uomId,
      direction: 'OUT',
      movement_type: outMovementType,
      qty: d,
      route_card_operation_id: routeCardOperationId,
      remarks:
        remarksOut ||
        (outMovementType === 'OPERATION_SCRAP' ? 'Route operation scrap' : 'Route operation consumption'),
      created_by: userId,
    });
  } else {
    const returnQty = round3(-d);
    await applyMovement(transaction, {
      company_id,
      plant_id,
      production_order_id,
      material_id: materialId,
      uom_id: uomId,
      direction: 'IN',
      movement_type: 'REVERSAL',
      qty: returnQty,
      route_card_operation_id: routeCardOperationId,
      remarks: remarkIn || 'Route operation qty reduced',
      created_by: userId,
    });
  }
}

exports.getProductionOrderFlowMaterialIdSet = async (productionOrderId, transaction = null) => {
  const opts = transaction ? { transaction } : {};
  const comps = await db.ProductionOrderComponent.findAll({
    where: { production_order_id: productionOrderId },
    include: [{ model: db.Material, as: 'material', attributes: ['material_type'], required: true }],
    ...opts,
  });
  const set = new Set();
  for (const c of comps) {
    const t = String(c.material?.material_type || '').toUpperCase();
    if (t === 'RAW_MATERIAL' || t === 'CONSUMABLE' || t === 'PACKAGING') {
      set.add(c.material_id);
    }
  }
  return set;
};

/** Raw / consumable / packaging in Material Master (same company) — allows nesting→stores sheet SKUs not on PO BOM. */
const masterMaterialTypeAllowsWipConsumption = (materialType) => {
  const t = String(materialType || '').toUpperCase();
  return t === 'RAW_MATERIAL' || t === 'CONSUMABLE' || t === 'PACKAGING';
};

async function isMaterialAllowedForWipConsumptionFromMaster(materialId, companyId, transaction) {
  if (!(materialId > 0) || !(companyId > 0)) return false;
  const mat = await db.Material.findOne({
    where: { id: materialId, company_id: companyId },
    attributes: ['id', 'material_type'],
    transaction,
  });
  return mat != null && masterMaterialTypeAllowsWipConsumption(mat.material_type);
}

exports.saveOperationConsumptions = async (transaction, { routeCardOperationId, userId, lines }) => {
  const op = await db.RouteCardOperation.findByPk(routeCardOperationId, { transaction });
  if (!op) throw new Error('Route Card operation not found');

  const routeCard = await db.RouteCard.findByPk(op.route_card_id, {
    attributes: ['id', 'company_id', 'plant_id', 'production_order_id', 'work_order_id', 'status'],
    transaction,
  });
  if (!routeCard) throw new Error('Route Card not found');

  const poId = routeCard.production_order_id;
  const workOrderId =
    routeCard.work_order_id != null && parseInt(routeCard.work_order_id, 10) > 0
      ? parseInt(routeCard.work_order_id, 10)
      : null;
  if (!(poId > 0)) {
    throw new Error('Route Card has no Production Order — Production Storage (WIP) is not available');
  }

  const st = String(op.status || '');
  if (st === 'COMPLETED' || st === 'CANCELLED' || st === 'SKIPPED') {
    throw new Error(`Cannot update WIP consumption when operation status is ${st}`);
  }

  const company_id = routeCard.company_id;
  const plant_id = routeCard.plant_id;
  const ctx = { company_id, plant_id, production_order_id: poId };

  const poRow = await db.ProductionOrder.findByPk(poId, {
    attributes: ['id', 'production_order_number'],
    transaction,
  });
  const poRef =
    poRow && String(poRow.production_order_number || '').trim()
      ? String(poRow.production_order_number).trim()
      : `id ${poId}`;
  const remarkConsume = `Route operation consumption · PO ${poRef}`;
  const remarkScrap = `Route operation scrap · PO ${poRef}`;

  const allowedMaterialIds = await exports.getProductionOrderFlowMaterialIdSet(poId, transaction);

  const input = Array.isArray(lines) ? lines : [];
  const seenMaterialIds = new Set();
  for (const row of input) {
    const materialId = parseInt(row.material_id, 10);
    if (!(materialId > 0)) continue;
    if (seenMaterialIds.has(materialId)) {
      throw new Error(`Duplicate material id ${materialId} in the same save request`);
    }
    seenMaterialIds.add(materialId);

    const onPoFlow = allowedMaterialIds.has(materialId);
    const fromMaster = onPoFlow
      ? true
      : await isMaterialAllowedForWipConsumptionFromMaster(materialId, company_id, transaction);
    if (!fromMaster) {
      throw new Error(
        `Material ${materialId} is not on this production order as raw material or fastener, and is not RAW_MATERIAL / CONSUMABLE / PACKAGING in material master for this company — cannot post to WIP consumption`
      );
    }

    const consumed = round3(row.consumed_qty);
    const scrap = round3(row.scrap_qty);
    if (consumed < -EPS || scrap < -EPS) {
      throw new Error('Consumed and scrap quantities cannot be negative');
    }

    const rawSnap =
      row.material_display_snapshot != null && String(row.material_display_snapshot).trim() !== ''
        ? String(row.material_display_snapshot).trim().slice(0, 512)
        : row.material_display != null && String(row.material_display).trim() !== ''
          ? String(row.material_display).trim().slice(0, 512)
          : null;

    const clsIn = String(row.wip_material_class || row.material_class || '').toUpperCase();
    const wipClass = clsIn === 'FASTENER' ? 'FASTENER' : 'RAW_MATERIAL';

    const [existing] = await db.RouteCardOperationMaterialConsumption.findOrCreate({
      where: { route_card_operation_id: routeCardOperationId, material_id: materialId },
      defaults: {
        production_order_id: poId,
        work_order_id: workOrderId,
        uom_id: row.uom_id != null && row.uom_id !== '' ? parseInt(row.uom_id, 10) : null,
        consumed_qty: 0,
        scrap_qty: 0,
        material_display_snapshot: rawSnap,
        wip_material_class: wipClass,
        created_by: userId,
        updated_by: userId,
      },
      transaction,
    });

    const uomId =
      (row.uom_id != null && row.uom_id !== '' ? parseInt(row.uom_id, 10) : null) || existing.uom_id || null;

    const dC = round3(consumed - round3(existing.consumed_qty));
    const dS = round3(scrap - round3(existing.scrap_qty));

    const totalAfter = round3(consumed + scrap);
    const totalBefore = round3(round3(existing.consumed_qty) + round3(existing.scrap_qty));
    const incrementalOut = round3(totalAfter - totalBefore);

    const bal = await db.ProductionWipBalance.findOne({
      where: {
        company_id: routeCard.company_id,
        plant_id: routeCard.plant_id,
        material_id: materialId,
      },
      transaction,
      lock: Transaction.LOCK.UPDATE,
    });
    const wipAvail = bal ? round3(bal.available_qty) : 0;
    if (incrementalOut > wipAvail + EPS) {
      throw new Error(
        `Material id ${materialId}: additional consumption ${incrementalOut.toFixed(3)} exceeds Production Storage available (${wipAvail.toFixed(3)}). ` +
          `(New total ${totalAfter.toFixed(3)} vs previous ${totalBefore.toFixed(3)} on this operation; stock is a shared plant pool.)`
      );
    }

    await applyConsumeOrScrapDelta(transaction, ctx, {
      materialId,
      uomId,
      delta: dC,
      outMovementType: 'OPERATION_CONSUME',
      routeCardOperationId,
      userId,
      remarkIn: 'Consumed qty reduced',
      remarksOut: remarkConsume,
    });
    await applyConsumeOrScrapDelta(transaction, ctx, {
      materialId,
      uomId,
      delta: dS,
      outMovementType: 'OPERATION_SCRAP',
      routeCardOperationId,
      userId,
      remarkIn: 'Scrap qty reduced',
      remarksOut: remarkScrap,
    });

    await existing.update(
      {
        consumed_qty: consumed,
        scrap_qty: scrap,
        uom_id: uomId || existing.uom_id,
        updated_by: userId,
        ...(workOrderId != null ? { work_order_id: workOrderId } : {}),
        wip_material_class: wipClass,
        ...(rawSnap != null ? { material_display_snapshot: rawSnap } : {}),
      },
      { transaction }
    );
  }

  return exports.getConsumptionRowsForOperation(routeCardOperationId);
};

/**
 * Return consumed+scrap from WIP for this operation (ledger OUT − reversal for this op is net).
 * Used when cancelling: restore full current consumption row totals to WIP.
 */
exports.reverseAllConsumptionsForOperation = async (transaction, { routeCardOperationId, userId }) => {
  const op = await db.RouteCardOperation.findByPk(routeCardOperationId, { transaction });
  if (!op) throw new Error('Route Card operation not found');

  const routeCard = await db.RouteCard.findByPk(op.route_card_id, {
    attributes: ['id', 'company_id', 'plant_id', 'production_order_id'],
    transaction,
  });
  if (!routeCard || !(routeCard.production_order_id > 0)) return;

  const rows = await db.RouteCardOperationMaterialConsumption.findAll({
    where: { route_card_operation_id: routeCardOperationId },
    transaction,
  });

  for (const row of rows) {
    const total = round3(row.consumed_qty) + round3(row.scrap_qty);
    if (total <= EPS) continue;

    await applyMovement(transaction, {
      company_id: routeCard.company_id,
      plant_id: routeCard.plant_id,
      production_order_id: routeCard.production_order_id,
      material_id: row.material_id,
      uom_id: row.uom_id,
      direction: 'IN',
      movement_type: 'REVERSAL',
      qty: total,
      route_card_operation_id: routeCardOperationId,
      remarks: 'Operation cancelled — WIP restored',
      created_by: userId,
    });

    await row.update({ consumed_qty: 0, scrap_qty: 0, updated_by: userId }, { transaction });
  }
};

/**
 * Reverse a specific material's consumed qty for an operation (partial reversal).
 * - Credits Production Storage (WIP) by the reverse qty (ledger movement_type: REVERSAL).
 * - Reduces RouteCardOperationMaterialConsumption.consumed_qty by reverse qty.
 * - Records reversal history (date/user/material/qty + before/after).
 */
exports.reverseOperationConsumption = async (
  transaction,
  { routeCardOperationId, materialId, reverseQty, userId, remarks = null }
) => {
  const opId = parseInt(routeCardOperationId, 10);
  const mid = parseInt(materialId, 10);
  const rq = round3(reverseQty);
  if (!(opId > 0)) throw new Error('Invalid route card operation id');
  if (!(mid > 0)) throw new Error('Invalid material id');
  if (!(rq > EPS)) throw new Error('Reverse quantity must be greater than 0');

  const op = await db.RouteCardOperation.findByPk(opId, { transaction });
  if (!op) throw new Error('Route Card operation not found');

  const routeCard = await db.RouteCard.findByPk(op.route_card_id, {
    attributes: ['id', 'company_id', 'plant_id', 'production_order_id', 'work_order_id'],
    transaction,
  });
  if (!routeCard) throw new Error('Route Card not found');
  if (!(routeCard.production_order_id > 0)) {
    throw new Error('Route Card has no Production Order — Production Storage (WIP) is not available');
  }

  const st = String(op.status || '');
  if (st === 'COMPLETED' || st === 'CANCELLED' || st === 'SKIPPED') {
    throw new Error(`Cannot reverse WIP consumption when operation status is ${st}`);
  }

  const existing = await db.RouteCardOperationMaterialConsumption.findOne({
    where: { route_card_operation_id: opId, material_id: mid },
    transaction,
    lock: Transaction.LOCK.UPDATE,
  });
  if (!existing) throw new Error('No consumption found for this material on this operation');

  const before = round3(existing.consumed_qty);
  if (rq > before + EPS) {
    throw new Error(`Reverse quantity cannot exceed available to reverse (${before.toFixed(3)})`);
  }

  // Credit back to WIP by reducing consumed_qty (delta negative) — uses REVERSAL movement.
  const ctx = { company_id: routeCard.company_id, plant_id: routeCard.plant_id, production_order_id: routeCard.production_order_id };
  await applyConsumeOrScrapDelta(transaction, ctx, {
    materialId: mid,
    uomId: existing.uom_id || null,
    delta: -rq,
    outMovementType: 'OPERATION_CONSUME',
    routeCardOperationId: opId,
    userId,
    remarkIn: 'Manual reverse — consumed qty reduced',
    remarksOut: null,
  });

  const after = round3(before - rq);
  await existing.update({ consumed_qty: after, updated_by: userId }, { transaction });

  const remarkText = remarks != null && String(remarks).trim() !== '' ? String(remarks).trim().slice(0, 500) : null;
  await db.RouteCardOperationMaterialReversal.create(
    {
      route_card_operation_id: opId,
      production_order_id: routeCard.production_order_id,
      material_id: mid,
      uom_id: existing.uom_id || null,
      reversed_qty: rq,
      consumed_before_qty: before,
      consumed_after_qty: after,
      remarks: remarkText,
      created_by: userId || null,
    },
    { transaction }
  );

  return { material_id: mid, reversed_qty: rq, consumed_before_qty: before, consumed_after_qty: after };
};
