const { query, getClient } = require('../config/database');

async function findMovements({ limit, offset, productId, movementType, startDate, endDate }) {
  const params = [];
  const where = ['1=1'];
  if (productId)     { params.push(productId);     where.push(`sm.product_id=$${params.length}`); }
  if (movementType)  { params.push(movementType);  where.push(`sm.movement_type=$${params.length}`); }
  if (startDate)     { params.push(startDate);     where.push(`sm.created_at::date >= $${params.length}`); }
  if (endDate)       { params.push(endDate);       where.push(`sm.created_at::date <= $${params.length}`); }

  const whereStr = where.join(' AND ');
  const joins = `
    FROM stock_movements sm
    JOIN products p ON p.id = sm.product_id
    LEFT JOIN users u ON u.id = sm.created_by
    WHERE ${whereStr}
  `;

  // Count query — no ORDER BY
  const countRes = await query(`SELECT COUNT(*) ${joins}`, params);

  // Data query — with ORDER BY, LIMIT, OFFSET
  const dataParams = [...params, limit, offset];
  const res = await query(
    `SELECT sm.*, p.name AS product_name, p.unit, u.name AS created_by_name
     ${joins}
     ORDER BY sm.created_at DESC
     LIMIT $${dataParams.length - 1} OFFSET $${dataParams.length}`,
    dataParams
  );

  return { rows: res.rows, total: parseInt(countRes.rows[0].count) };
}

async function adjust({ product_id, movement_type, qty, reason, created_by }) {
  const client = await getClient();
  try {
    await client.query('BEGIN');
    const prodRes = await client.query('SELECT stock FROM products WHERE id=$1 FOR UPDATE', [product_id]);
    if (!prodRes.rows[0]) throw Object.assign(new Error('Product not found'), { statusCode: 404 });
    const qtyBefore = parseFloat(prodRes.rows[0].stock);
    const delta = movement_type === 'adjustment_in' ? qty : -qty;
    const qtyAfter = qtyBefore + delta;
    if (qtyAfter < 0) throw Object.assign(new Error('Insufficient stock'), { statusCode: 400 });
    await client.query('UPDATE products SET stock=$2 WHERE id=$1', [product_id, qtyAfter]);
    const movRes = await client.query(
      `INSERT INTO stock_movements (product_id,movement_type,qty,qty_before,qty_after,reason,created_by)
       VALUES ($1,$2,$3,$4,$5,$6,$7) RETURNING *`,
      [product_id, movement_type, qty, qtyBefore, qtyAfter, reason, created_by]
    );
    await client.query('COMMIT');
    return movRes.rows[0];
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

module.exports = { findMovements, adjust };
