const db = require('../models');
const { Op } = require('sequelize');
const { generateNextNumber } = require('./numberFormatService');

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

async function getCurrentBalance({ companyId, plantId, fiscalYearId = null, itemId, warehouseId, transaction = null }) {
  const where = {
    company_id: companyId,
    plant_id: plantId,
    item_id: itemId,
    warehouse_id: warehouseId
  };
  if (fiscalYearId) where.fiscal_year_id = fiscalYearId;

  const totals = await db.FGStockLedger.findOne({
    where,
    attributes: [
      [db.sequelize.fn('COALESCE', db.sequelize.fn('SUM', db.sequelize.col('inward_qty')), 0), 'total_inward'],
      [db.sequelize.fn('COALESCE', db.sequelize.fn('SUM', db.sequelize.col('outward_qty')), 0), 'total_outward']
    ],
    lock: transaction ? transaction.LOCK.UPDATE : undefined,
    transaction: transaction || undefined
  });

  const totalInward = toNum(totals?.get?.('total_inward'));
  const totalOutward = toNum(totals?.get?.('total_outward'));
  return totalInward - totalOutward;
}

async function createEntry(payload, transaction = null) {
  const inwardQty = toNum(payload.inward_qty);
  const outwardQty = toNum(payload.outward_qty);
  if (inwardQty < 0 || outwardQty < 0) {
    throw new Error('Inward and outward quantities cannot be negative');
  }
  if (inwardQty === 0 && outwardQty === 0) {
    throw new Error('Either inward_qty or outward_qty should be greater than 0');
  }

  const previousBalance = await getCurrentBalance({
    companyId: payload.company_id,
    plantId: payload.plant_id,
    fiscalYearId: payload.fiscal_year_id,
    itemId: payload.item_id,
    warehouseId: payload.warehouse_id,
    transaction
  });
  const balanceQty = previousBalance + inwardQty - outwardQty;

  let stockLedgerNo = null;
  try {
    stockLedgerNo = await generateNextNumber(payload.company_id, 'FG_STOCK_LEDGER', payload.fiscal_year_id, transaction);
  } catch (e) {
    const year = new Date().getFullYear();
    const prefix = `FGL${year}`;
    const last = await db.FGStockLedger.findOne({
      where: {
        company_id: payload.company_id,
        stock_ledger_no: { [Op.like]: `${prefix}%` }
      },
      attributes: ['stock_ledger_no'],
      order: [['stock_ledger_no', 'DESC']],
      ...(transaction ? { transaction } : {})
    });
    let seq = 1;
    if (last?.stock_ledger_no) {
      const parsed = parseInt(String(last.stock_ledger_no).slice(-4), 10) || 0;
      seq = parsed + 1;
    }
    stockLedgerNo = `${prefix}${String(seq).padStart(4, '0')}`;
  }

  return db.FGStockLedger.create(
    {
      stock_ledger_no: stockLedgerNo,
      company_id: payload.company_id,
      plant_id: payload.plant_id,
      fiscal_year_id: payload.fiscal_year_id,
      item_id: payload.item_id,
      warehouse_id: payload.warehouse_id,
      reference_type: payload.reference_type,
      reference_id: payload.reference_id || null,
      movement_date: payload.movement_date ? new Date(payload.movement_date) : new Date(),
      inward_qty: inwardQty,
      outward_qty: outwardQty,
      balance_qty: balanceQty,
      created_by: payload.created_by || null
    },
    { transaction: transaction || undefined }
  );
}

async function list(filters = {}) {
  const where = {};
  if (filters.companyId) where.company_id = filters.companyId;
  if (filters.plantId) where.plant_id = filters.plantId;
  if (filters.fiscalYearId) where.fiscal_year_id = filters.fiscalYearId;
  if (filters.itemId) where.item_id = filters.itemId;
  if (filters.warehouseId) where.warehouse_id = filters.warehouseId;
  if (filters.referenceType) where.reference_type = filters.referenceType;
  if (filters.referenceId) where.reference_id = filters.referenceId;

  if (filters.fromDate || filters.toDate) {
    where.movement_date = {};
    if (filters.fromDate) where.movement_date[Op.gte] = new Date(filters.fromDate);
    if (filters.toDate) {
      const dt = new Date(filters.toDate);
      dt.setHours(23, 59, 59, 999);
      where.movement_date[Op.lte] = dt;
    }
  }

  return db.FGStockLedger.findAndCountAll({
    where,
    include: [
      { model: db.Material, as: 'item', attributes: ['id', 'material_number', 'description'] },
      { model: db.StorageLocation, as: 'warehouse', attributes: ['id', 'storage_location_code', 'description'] }
    ],
    order: [['movement_date', 'ASC'], ['id', 'ASC']],
    limit: filters.limit,
    offset: filters.offset
  });
}

async function backfillFromFGInward({ companyId, plantId = null, fiscalYearId = null, createdBy = null, transaction = null } = {}) {
  const where = {};
  if (companyId) where.company_id = companyId;
  if (plantId) where.plant_id = plantId;
  if (fiscalYearId) where.fiscal_year_id = fiscalYearId;
  where.finished_material_id = { [Op.ne]: null };
  where.storage_location_id = { [Op.ne]: null };

  const inwardRows = await db.ProductionFGInward.findAll({
    where,
    attributes: [
      'id',
      'company_id',
      'plant_id',
      'fiscal_year_id',
      'finished_material_id',
      'storage_location_id',
      'inward_entry_qty',
      'inward_entry_date',
      'created_by'
    ],
    order: [['inward_entry_date', 'ASC'], ['id', 'ASC']],
    transaction: transaction || undefined
  });

  let inserted = 0;
  let skipped = 0;
  for (const row of inwardRows) {
    const exists = await db.FGStockLedger.findOne({
      where: { reference_type: 'FG_INWARD', reference_id: row.id },
      attributes: ['id'],
      transaction: transaction || undefined
    });
    if (exists) {
      skipped += 1;
      continue;
    }

    await createEntry(
      {
        company_id: row.company_id,
        plant_id: row.plant_id,
        fiscal_year_id: row.fiscal_year_id,
        item_id: row.finished_material_id,
        warehouse_id: row.storage_location_id,
        reference_type: 'FG_INWARD',
        reference_id: row.id,
        movement_date: row.inward_entry_date,
        inward_qty: toNum(row.inward_entry_qty),
        outward_qty: 0,
        created_by: createdBy || row.created_by || null
      },
      transaction
    );
    inserted += 1;
  }

  return { total: inwardRows.length, inserted, skipped };
}

async function assignMissingLedgerNumbers({ companyId = null, transaction = null } = {}) {
  const where = { stock_ledger_no: null };
  if (companyId) where.company_id = companyId;

  const rows = await db.FGStockLedger.findAll({
    where,
    attributes: ['id', 'movement_date'],
    order: [['id', 'ASC']],
    transaction: transaction || undefined
  });

  for (const row of rows) {
    const dt = row.movement_date ? new Date(row.movement_date) : new Date();
    const year = dt.getFullYear();
    const stockLedgerNo = `FGL${year}${String(row.id).padStart(4, '0')}`;
    await row.update({ stock_ledger_no: stockLedgerNo }, { transaction: transaction || undefined });
  }

  return { updated: rows.length };
}

async function recalculateBalances({ companyId = null, plantId = null, fiscalYearId = null, itemId = null, warehouseId = null, transaction = null } = {}) {
  const where = {};
  if (companyId) where.company_id = companyId;
  if (plantId) where.plant_id = plantId;
  if (fiscalYearId) where.fiscal_year_id = fiscalYearId;
  if (itemId) where.item_id = itemId;
  if (warehouseId) where.warehouse_id = warehouseId;

  const rows = await db.FGStockLedger.findAll({
    where,
    attributes: ['id', 'company_id', 'plant_id', 'fiscal_year_id', 'item_id', 'warehouse_id', 'inward_qty', 'outward_qty', 'balance_qty', 'movement_date'],
    order: [['movement_date', 'ASC'], ['id', 'ASC']],
    transaction: transaction || undefined
  });

  const runningByScope = new Map();
  let updated = 0;

  for (const row of rows) {
    const scopeKey = [
      row.company_id,
      row.plant_id,
      row.fiscal_year_id,
      row.item_id,
      row.warehouse_id
    ].join(':');

    const previous = runningByScope.get(scopeKey) || 0;
    const next = previous + toNum(row.inward_qty) - toNum(row.outward_qty);
    if (toNum(row.balance_qty) !== next) {
      await row.update({ balance_qty: next }, { transaction: transaction || undefined });
      updated += 1;
    }
    runningByScope.set(scopeKey, next);
  }

  return { total: rows.length, updated };
}

module.exports = {
  toNum,
  getCurrentBalance,
  createEntry,
  list,
  backfillFromFGInward,
  assignMissingLedgerNumbers,
  recalculateBalances
};
