'use strict';

const { db } = require('../../utils/dbName');

const MAX_TRANSFER_FOLIO = 99999999;
const VALID_MODES = new Set(['origin', 'destination', 'both']);

function transferError(code, message) {
  return Object.assign(new Error(message), { code });
}

function bounded(value, max, fallback = '') {
  const normalized = String(value ?? fallback).trim();
  if (!normalized) return String(fallback).slice(0, max);
  return normalized.slice(0, max);
}

function validateTransferPayload(payload = {}, config = {}) {
  const folio = Number(payload.folio);
  const mode = payload.mode || 'both';
  const branchId = config.syncContext?.branch_id || config.syncContext?.branchId
    || config.branchId || config.branch_id;

  if (!Number.isSafeInteger(folio) || folio < 1 || folio > MAX_TRANSFER_FOLIO) {
    throw transferError('TRANSFER_FOLIO_OUT_OF_RANGE', 'El folio debe tener entre 1 y 8 digitos para SoftRestaurant');
  }
  if (!VALID_MODES.has(mode)) {
    throw transferError('TRANSFER_MODE_INVALID', 'Modo de traspaso no valido');
  }
  if (payload.local_branch_id && branchId && payload.local_branch_id !== branchId) {
    throw transferError('scope_mismatch', 'local_branch_id no coincide con el agente local');
  }
  if (!payload.origin || !payload.destination || !Array.isArray(payload.items) || !payload.items.length) {
    throw transferError('TRANSFER_PAYLOAD_INCOMPLETE', 'Payload incompleto para inventory.transfer.execute');
  }
  if (bounded(payload.origin, 5) !== String(payload.origin).trim()
    || bounded(payload.destination, 5) !== String(payload.destination).trim()) {
    throw transferError('TRANSFER_WAREHOUSE_TOO_LONG', 'La clave de almacen excede 5 caracteres');
  }

  const items = payload.items.map((item) => {
    const id = bounded(item.id, 15);
    const quantity = Number(item.quantity);
    const cost = Number(item.cost || 0);
    if (!id || id !== String(item.id).trim()) {
      throw transferError('TRANSFER_ITEM_INVALID', 'La presentacion debe tener entre 1 y 15 caracteres');
    }
    if (!Number.isFinite(quantity) || quantity <= 0 || quantity >= 10000000000) {
      throw transferError('TRANSFER_QUANTITY_INVALID', `Cantidad invalida para ${id || 'partida'}`);
    }
    if (!Number.isFinite(cost) || cost < 0) {
      throw transferError('TRANSFER_COST_INVALID', `Costo invalido para ${id}`);
    }
    return { id, quantity, cost };
  });

  return {
    ...payload,
    folio,
    mode,
    origin: bounded(payload.origin, 5),
    destination: bounded(payload.destination, 5),
    user: bounded(payload.user, 15, 'ARKHON'),
    company_id: bounded(payload.company_id, 15, '1'),
    origin_company: bounded(payload.origin_company, 15, '1'),
    destination_company: bounded(payload.destination_company, 15, payload.origin_company || '1'),
    items,
  };
}

function movementSpecs(mode, payload) {
  const movements = [];
  if (mode === 'origin' || mode === 'both') movements.push({ concept: 'STA', warehouse: payload.origin });
  if (mode === 'destination' || mode === 'both') movements.push({ concept: 'ETA', warehouse: payload.destination });
  return movements;
}

function normalizedNumericId(value) {
  const candidate = bounded(value, 15);
  if (!candidate || !/^\d+$/.test(candidate)) return candidate;
  return candidate.replace(/^0+(?=\d)/, '');
}

function resolveLocalCompanyId(requested, available) {
  const companies = [...new Set((available || []).map((value) => bounded(value, 15)).filter(Boolean))];
  const requestedId = bounded(requested, 15);
  if (requestedId && companies.includes(requestedId)) return requestedId;
  if (requestedId) {
    const numeric = companies.filter((value) => normalizedNumericId(value) === normalizedNumericId(requestedId));
    if (numeric.length === 1) return numeric[0];
  }
  if (companies.length === 1) return companies[0];
  throw transferError('TRANSFER_COMPANY_MAPPING_MISSING', 'No se pudo resolver la empresa local de SoftRestaurant');
}

async function loadLocalTransferCatalog(transaction, sql, prefix, payload) {
  const companyResult = await new sql.Request(transaction)
    .query(`SELECT LTRIM(RTRIM(idempresa)) AS idempresa FROM ${prefix}empresas`);
  const companyId = resolveLocalCompanyId(payload.company_id, companyResult.recordset.map((row) => row.idempresa));

  const warehouseResult = await new sql.Request(transaction)
    .input('origin', sql.VarChar(5), payload.origin)
    .input('destination', sql.VarChar(5), payload.destination)
    .query(`SELECT LTRIM(RTRIM(idalmacen)) AS idalmacen FROM ${prefix}almacen
            WHERE LTRIM(RTRIM(idalmacen)) IN (@origin,@destination)`);
  const warehouses = new Set(warehouseResult.recordset.map((row) => bounded(row.idalmacen, 5)));
  const missingWarehouses = [...new Set([payload.origin, payload.destination])].filter((id) => !warehouses.has(id));
  if (missingWarehouses.length) {
    throw transferError('TRANSFER_WAREHOUSE_NOT_FOUND', `Almacen local no encontrado: ${missingWarehouses.join(', ')}`);
  }

  const itemIds = [...new Set(payload.items.map((item) => item.id))];
  const presentationRequest = new sql.Request(transaction);
  const itemParameters = itemIds.map((itemId, index) => {
    presentationRequest.input(`item_${index}`, sql.VarChar(15), itemId);
    return `@item_${index}`;
  });
  const presentationResult = await presentationRequest
    .query(`SELECT LTRIM(RTRIM(idinsumospresentaciones)) AS id
            FROM ${prefix}insumospresentaciones
            WHERE LTRIM(RTRIM(idinsumospresentaciones)) IN (${itemParameters.join(',')})`);
  const presentations = new Set(presentationResult.recordset.map((row) => bounded(row.id, 15)));
  const missingItems = itemIds.filter((id) => !presentations.has(id));
  if (missingItems.length) {
    throw transferError('TRANSFER_PRESENTATION_NOT_FOUND', `Presentacion local no encontrada: ${missingItems.join(', ')}`);
  }
  return { companyId };
}

async function handleInventoryTransfer(command, config = {}, deps = {}) {
  const payload = validateTransferPayload(command.payload || {}, config);
  if (payload.dry_run) {
    return { status: 'validated', folio: payload.folio, mode: payload.mode, items: payload.items.length };
  }

  const pool = deps.pool || await require('../../db').getPool();
  const sql = deps.sql || require('mssql');
  const transaction = new sql.Transaction(pool);
  let begun = false;

  try {
    await transaction.begin();
    begun = true;
    const prefix = db();
    const localCatalog = await loadLocalTransferCatalog(transaction, sql, prefix, payload);
    const marker = bounded(`ARKHON:${payload.transfer_id || payload.folio}`, 150);
    const existing = await new sql.Request(transaction)
      .input('folio', sql.Numeric(8, 0), payload.folio)
      .query(`SELECT folio, nota FROM ${prefix}traspasosalmacen WITH (UPDLOCK, HOLDLOCK) WHERE folio=@folio`);

    if (existing.recordset.length && !String(existing.recordset[0].nota || '').includes(marker)) {
      throw transferError('TRANSFER_FOLIO_CONFLICT', `El folio ${payload.folio} ya pertenece a otro traspaso`);
    }
    if (!existing.recordset.length) {
      await new sql.Request(transaction)
        .input('folio', sql.Numeric(8, 0), payload.folio)
        .input('fecha', sql.DateTime, new Date(payload.date || Date.now()))
        .input('almacenorigen', sql.VarChar(5), payload.origin)
        .input('almacendestino', sql.VarChar(5), payload.destination)
        .input('usuario', sql.VarChar(15), payload.user)
        .input('nota', sql.VarChar(150), marker)
        .input('idempresa', sql.VarChar(15), localCatalog.companyId)
        .input('idempresaorigen', sql.VarChar(15), payload.origin_company || localCatalog.companyId)
        .input('idempresadestino', sql.VarChar(15), payload.destination_company || localCatalog.companyId)
        .query(`INSERT INTO ${prefix}traspasosalmacen
          (folio, fecha, almacenorigen, almacendestino, cancelado, usuario, nota, idempresa,
           idempresaorigen, idempresadestino, enviadoacentral, editadosucursal)
          VALUES (@folio,@fecha,@almacenorigen,@almacendestino,0,@usuario,@nota,@idempresa,
                  @idempresaorigen,@idempresadestino,0,0)`);
    }

    let inserted = 0;
    let skipped = 0;
    for (const item of payload.items) {
      for (const movement of movementSpecs(payload.mode, payload)) {
        const exists = await new sql.Request(transaction)
          .input('folio', sql.Numeric(8, 0), payload.folio)
          .input('concept', sql.VarChar(5), movement.concept)
          .input('item', sql.VarChar(15), item.id)
          .input('warehouse', sql.VarChar(5), movement.warehouse)
          .query(`SELECT TOP 1 1 AS found FROM ${prefix}movtosalmacen WITH (UPDLOCK, HOLDLOCK)
                  WHERE traspaso=@folio AND idconcepto=@concept
                    AND idinsumospresentaciones=@item AND idalmacen=@warehouse`);
        if (exists.recordset.length) {
          skipped += 1;
          continue;
        }
        await new sql.Request(transaction)
          .input('fecha', sql.DateTime, new Date(payload.date || Date.now()))
          .input('movto', sql.Numeric(8, 0), 0)
          .input('traspaso', sql.Numeric(8, 0), payload.folio)
          .input('concept', sql.VarChar(5), movement.concept)
          .input('item', sql.VarChar(15), item.id)
          .input('cost', sql.Money, item.cost)
          .input('quantity', sql.Numeric(14, 4), item.quantity)
          .input('warehouse', sql.VarChar(5), movement.warehouse)
          .query(`INSERT INTO ${prefix}movtosalmacen
            (fecha,movto,traspaso,idconcepto,idinsumospresentaciones,costo,cantidad,idalmacen,enviadoacentral)
            VALUES (@fecha,@movto,@traspaso,@concept,@item,@cost,@quantity,@warehouse,0)`);
        inserted += 1;
      }
    }

    await transaction.commit();
    begun = false;
    return { status: 'applied', folio: payload.folio, mode: payload.mode, inserted_movements: inserted, skipped_movements: skipped };
  } catch (err) {
    if (begun) await transaction.rollback().catch(() => {});
    throw err;
  }
}

module.exports = {
  MAX_TRANSFER_FOLIO,
  validateTransferPayload,
  movementSpecs,
  normalizedNumericId,
  resolveLocalCompanyId,
  loadLocalTransferCatalog,
  handleInventoryTransfer,
};
