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

async function findAll({ limit, offset, search, status }) {
  let sql = `
    SELECT p.*,
      json_agg(json_build_object('id', pi.id,'product_id',pi.product_id,'qty',pi.qty,'product_name',pr.name,'unit',pr.unit,'stock',pr.stock)) AS items
    FROM packages p
    LEFT JOIN package_items pi ON pi.package_id = p.id
    LEFT JOIN products pr ON pr.id = pi.product_id
    WHERE 1=1
  `;
  const params = [];
  if (status) { params.push(status); sql += ` AND p.status=$${params.length}`; }
  if (search) { params.push(`%${search}%`); sql += ` AND p.name ILIKE $${params.length}`; }
  sql += ' GROUP BY p.id ORDER BY p.name';

  const countSql = `SELECT COUNT(*) FROM packages p WHERE 1=1` +
    (status ? ` AND p.status='${status}'` : '') +
    (search ? ` AND p.name ILIKE '%${search}%'` : '');
  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.*,
      json_agg(json_build_object('id',pi.id,'product_id',pi.product_id,'qty',pi.qty,'product_name',pr.name,'unit',pr.unit,'cost_price',pr.cost_price,'stock',pr.stock)) AS items
    FROM packages p
    LEFT JOIN package_items pi ON pi.package_id = p.id
    LEFT JOIN products pr ON pr.id = pi.product_id
    WHERE p.id=$1 GROUP BY p.id
  `, [id]);
  return res.rows[0] || null;
}

async function create({ name, description, selling_price, status = 'active' }) {
  const res = await query(
    'INSERT INTO packages (name,description,selling_price,status) VALUES ($1,$2,$3,$4) RETURNING *',
    [name, description, selling_price, status]
  );
  return res.rows[0];
}

async function addItem(packageId, { product_id, qty }) {
  const res = await query(
    'INSERT INTO package_items (package_id,product_id,qty) VALUES ($1,$2,$3) RETURNING *',
    [packageId, product_id, qty]
  );
  return res.rows[0];
}

async function update(id, fields) {
  const allowed = ['name','description','selling_price','status'];
  const sets = Object.keys(fields).filter(k => allowed.includes(k));
  if (!sets.length) return findById(id);
  const sql = `UPDATE packages SET ${sets.map((k,i)=>`${k}=$${i+2}`).join(',')} WHERE id=$1 RETURNING *`;
  const res = await query(sql, [id, ...sets.map(k => fields[k])]);
  return res.rows[0] || null;
}

async function clearItems(packageId) {
  await query('DELETE FROM package_items WHERE package_id=$1', [packageId]);
}

async function remove(id) {
  await query('DELETE FROM packages WHERE id=$1', [id]);
}

async function findActive() {
  const res = await query(`
    SELECT p.*,
      json_agg(json_build_object('product_id',pi.product_id,'qty',pi.qty,'product_name',pr.name,'unit',pr.unit,'stock',pr.stock,'cost_price',pr.cost_price)) AS items
    FROM packages p
    LEFT JOIN package_items pi ON pi.package_id = p.id
    LEFT JOIN products pr ON pr.id = pi.product_id
    WHERE p.status='active' GROUP BY p.id ORDER BY p.name
  `);
  return res.rows;
}

module.exports = { findAll, findById, create, addItem, update, clearItems, remove, findActive };
