const db = require('../config/db');
const { logAuditFromReq } = require('../middlewares/audit');

exports.getAll = async (req, res, next) => {
  try {
    const { type, from_date, to_date } = req.query;
    let sql = 'SELECT de.*, u.full_name AS user_name FROM daily_entries de JOIN users u ON u.id = de.user_id WHERE 1=1';
    const params = [];

    if (type) { params.push(type); sql += ' AND de.type = ?'; }
    if (from_date) { params.push(from_date); sql += ' AND de.entry_date >= ?'; }
    if (to_date) { params.push(to_date); sql += ' AND de.entry_date <= ?'; }

    sql += ' ORDER BY de.entry_date DESC, de.id DESC';
    const [rows] = await db.query(sql, params);
    res.json(rows);
  } catch (err) {
    next(err);
  }
};

exports.getById = async (req, res, next) => {
  try {
    const [rows] = await db.query(
      'SELECT de.*, u.full_name AS user_name FROM daily_entries de JOIN users u ON u.id = de.user_id WHERE de.id = ?',
      [req.params.id]
    );
    if (rows.length === 0) return res.status(404).json({ error: 'Daily entry not found' });
    res.json(rows[0]);
  } catch (err) {
    next(err);
  }
};

exports.create = async (req, res, next) => {
  try {
    const { entry_date, type, rmt, bkash, product, bill, ticket, others, notes, receipt_url, fields_data } = req.body;
    if (!entry_date || !type) {
      return res.status(400).json({ error: 'entry_date and type are required' });
    }

    // Compute total from fields_data JSON if provided, else from legacy columns
    let total = 0;
    const fieldsJson = fields_data ? JSON.stringify(fields_data) : null;
    if (fields_data && typeof fields_data === 'object') {
      total = Object.values(fields_data).reduce((s, v) => s + (parseFloat(v) || 0), 0);
    } else {
      total = parseFloat(rmt || 0) + parseFloat(bkash || 0) + parseFloat(product || 0) +
              parseFloat(bill || 0) + parseFloat(ticket || 0) + parseFloat(others || 0);
    }

    const [result] = await db.query(
      `INSERT INTO daily_entries (entry_date, type, rmt, bkash, product, bill, ticket, others, fields_data, total, notes, receipt_url, user_id)
       VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)`,
      [entry_date, type, rmt || 0, bkash || 0, product || 0, bill || 0, ticket || 0, others || 0, fieldsJson, total, notes || null, receipt_url || null, req.user.id]
    );
    const [rows] = await db.query('SELECT * FROM daily_entries WHERE id = ?', [result.insertId]);

    logAuditFromReq(req, 'create', 'daily_entry', result.insertId, {
      type, entry_date, total,
    });

    res.status(201).json(rows[0]);
  } catch (err) {
    next(err);
  }
};

exports.update = async (req, res, next) => {
  try {
    const { entry_date, type, rmt, bkash, product, bill, ticket, others, notes, receipt_url, fields_data } = req.body;

    const [existing] = await db.query('SELECT * FROM daily_entries WHERE id = ?', [req.params.id]);
    if (existing.length === 0) return res.status(404).json({ error: 'Daily entry not found' });

    const e = existing[0];

    // Compute total from fields_data JSON if provided, else from legacy columns
    let total = 0;
    const fieldsJson = fields_data ? JSON.stringify(fields_data) : e.fields_data || null;
    if (fields_data && typeof fields_data === 'object') {
      total = Object.values(fields_data).reduce((s, v) => s + (parseFloat(v) || 0), 0);
    } else {
      const newRmt = rmt != null ? parseFloat(rmt) : parseFloat(e.rmt);
      const newBkash = bkash != null ? parseFloat(bkash) : parseFloat(e.bkash);
      const newProduct = product != null ? parseFloat(product) : parseFloat(e.product);
      const newBill = bill != null ? parseFloat(bill) : parseFloat(e.bill);
      const newTicket = ticket != null ? parseFloat(ticket) : parseFloat(e.ticket);
      const newOthers = others != null ? parseFloat(others) : parseFloat(e.others);
      total = newRmt + newBkash + newProduct + newBill + newTicket + newOthers;
    }

    await db.query(
      `UPDATE daily_entries SET
         entry_date  = COALESCE(?, entry_date),
         type        = COALESCE(?, type),
         rmt         = ?, bkash = ?, product = ?,
         bill        = ?, ticket = ?, others = ?,
         fields_data = ?,
         total       = ?,
         notes       = COALESCE(?, notes),
         receipt_url = COALESCE(?, receipt_url)
       WHERE id = ?`,
      [entry_date, type,
       rmt != null ? rmt : e.rmt, bkash != null ? bkash : e.bkash, product != null ? product : e.product,
       bill != null ? bill : e.bill, ticket != null ? ticket : e.ticket, others != null ? others : e.others,
       fieldsJson, total, notes, receipt_url, req.params.id]
    );
    const [rows] = await db.query('SELECT * FROM daily_entries WHERE id = ?', [req.params.id]);

    logAuditFromReq(req, 'update', 'daily_entry', parseInt(req.params.id, 10));

    res.json(rows[0]);
  } catch (err) {
    next(err);
  }
};

exports.remove = async (req, res, next) => {
  try {
    const [result] = await db.query('DELETE FROM daily_entries WHERE id = ?', [req.params.id]);
    if (result.affectedRows === 0) return res.status(404).json({ error: 'Daily entry not found' });

    logAuditFromReq(req, 'delete', 'daily_entry', parseInt(req.params.id, 10));

    res.json({ message: 'Daily entry deleted' });
  } catch (err) {
    next(err);
  }
};
