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

async function findAll({ limit, offset, startDate, endDate }) {
  let sql = `
    SELECT p.*, u.name AS created_by_name,
      json_agg(json_build_object('product_id',pi.product_id,'qty',pi.qty,'product_name',pr.name,'unit',pr.unit)) AS items
    FROM purchases p
    LEFT JOIN users u ON u.id = p.created_by
    LEFT JOIN purchase_items pi ON pi.purchase_id = p.id
    LEFT JOIN products pr ON pr.id = pi.product_id
    WHERE 1=1
  `;
  const params = [];
  if (startDate) { params.push(startDate); sql += ` AND p.purchase_date >= $${params.length}`; }
  if (endDate) { params.push(endDate); sql += ` AND p.purchase_date <= $${params.length}`; }
  sql += ' GROUP BY p.id, u.name ORDER BY p.created_at DESC';

  const countSql = `SELECT COUNT(*) FROM purchases p WHERE 1=1` +
    (startDate ? ` AND p.purchase_date >= '${startDate}'` : '') +
    (endDate ? ` AND p.purchase_date <= '${endDate}'` : '');
  const countRes = await query(countSql);

  sql += ` LIMIT $${params.length + 1} OFFSET $${params.length + 2}`;
  params.push(limit, offset);
  const res = await query(sql, params);
  return { rows: res.rows, total: parseInt(countRes.rows[0].count) };
}

async function findById(id) {
  const res = await query(`
    SELECT p.*, u.name AS created_by_name,
      json_agg(json_build_object('id',pi.id,'product_id',pi.product_id,'qty',pi.qty,'product_name',pr.name,'unit',pr.unit)) AS items
    FROM purchases p
    LEFT JOIN users u ON u.id = p.created_by
    LEFT JOIN purchase_items pi ON pi.purchase_id = p.id
    LEFT JOIN products pr ON pr.id = pi.product_id
    WHERE p.id=$1 GROUP BY p.id, u.name
  `, [id]);
  return res.rows[0] || null;
}

async function create({ purchase_date, notes, created_by, items }) {
  const client = await getClient();
  try {
    await client.query('BEGIN');
    const purchRes = await client.query(
      'INSERT INTO purchases (purchase_date,notes,created_by) VALUES ($1,$2,$3) RETURNING *',
      [purchase_date, notes, created_by]
    );
    const purchase = purchRes.rows[0];
    for (const item of items) {
      await client.query(
        'INSERT INTO purchase_items (purchase_id,product_id,qty) VALUES ($1,$2,$3)',
        [purchase.id, item.product_id, item.qty]
      );
      await client.query('UPDATE products SET stock=stock+$2 WHERE id=$1', [item.product_id, item.qty]);
      const stockRes = await client.query('SELECT stock FROM products WHERE id=$1', [item.product_id]);
      const newStock = parseFloat(stockRes.rows[0].stock);
      await client.query(
        `INSERT INTO stock_movements (product_id,movement_type,qty,qty_before,qty_after,reference_id,reference_type,created_by)
         VALUES ($1,'purchase',$2,$3,$4,$5,'purchase',$6)`,
        [item.product_id, item.qty, newStock - item.qty, newStock, purchase.id, created_by]
      );
    }
    await client.query('COMMIT');
    return purchase;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

module.exports = { findAll, findById, create };
