const db = require('../config/db');

// Get all delivery boy funds
const getAllDeliveryBoyFunds = async (req, res) => {
  try {
    const [rows] = await db.query(`
      SELECT df.*, db.name as delivery_boy_name, db.phone as delivery_boy_phone
      FROM delivery_boy_funds df 
      LEFT JOIN delivery_boys db ON df.delivery_boy_id = db.id 
      ORDER BY df.created_at DESC
    `);
    res.json({ success: true, data: rows });
  } catch (error) {
    console.error('Error fetching delivery boy funds:', error);
    res.status(500).json({ success: false, msg: 'Error fetching delivery boy funds' });
  }
};

// Get delivery boy funds by ID
const getDeliveryBoyFundsById = async (req, res) => {
  try {
    const { id } = req.params;
    const [rows] = await db.query(`
      SELECT df.*, db.name as delivery_boy_name, db.phone as delivery_boy_phone
      FROM delivery_boy_funds df 
      LEFT JOIN delivery_boys db ON df.delivery_boy_id = db.id 
      WHERE df.id = ?
    `, [id]);
    
    if (rows.length === 0) {
      return res.status(404).json({ success: false, msg: 'Delivery boy funds record not found' });
    }
    
    res.json({ success: true, data: rows[0] });
  } catch (error) {
    console.error('Error fetching delivery boy funds:', error);
    res.status(500).json({ success: false, msg: 'Error fetching delivery boy funds' });
  }
};

// Create new delivery boy funds record
const createDeliveryBoyFunds = async (req, res) => {
  try {
    const { delivery_boy_id, amount, type, message } = req.body;
    
    if (!delivery_boy_id || !amount || !type) {
      return res.status(400).json({ success: false, msg: 'Delivery boy ID, amount and type are required' });
    }
    
    // Get current balance for the delivery boy
    const [currentBalance] = await db.query(
      'SELECT closing_balance FROM delivery_boy_funds WHERE delivery_boy_id = ? ORDER BY created_at DESC LIMIT 1',
      [delivery_boy_id]
    );
    
    const opening_balance = currentBalance.length > 0 ? currentBalance[0].closing_balance : 0;
    const closing_balance = type === 'credit' ? opening_balance + parseFloat(amount) : opening_balance - parseFloat(amount);
    
    const [result] = await db.query(
      'INSERT INTO delivery_boy_funds (delivery_boy_id, opening_balance, closing_balance, amount, type, message) VALUES (?, ?, ?, ?, ?, ?)',
      [delivery_boy_id, opening_balance, closing_balance, amount, type, message]
    );
    
    const [newFundsRecord] = await db.query(`
      SELECT df.*, db.name as delivery_boy_name, db.phone as delivery_boy_phone
      FROM delivery_boy_funds df 
      LEFT JOIN delivery_boys db ON df.delivery_boy_id = db.id 
      WHERE df.id = ?
    `, [result.insertId]);
    
    res.status(201).json({ success: true, data: newFundsRecord[0], msg: 'Funds record created successfully' });
  } catch (error) {
    console.error('Error creating funds record:', error);
    res.status(500).json({ success: false, msg: 'Error creating funds record' });
  }
};

// Update delivery boy funds
const updateDeliveryBoyFunds = async (req, res) => {
  try {
    const { id } = req.params;
    const { delivery_boy_id, amount, type, message, status } = req.body;
    
    if (!delivery_boy_id || !amount || !type) {
      return res.status(400).json({ success: false, msg: 'Delivery boy ID, amount and type are required' });
    }
    
    const [result] = await db.query(
      'UPDATE delivery_boy_funds SET delivery_boy_id = ?, amount = ?, type = ?, message = ?, status = ? WHERE id = ?',
      [delivery_boy_id, amount, type, message, status, id]
    );
    
    if (result.affectedRows === 0) {
      return res.status(404).json({ success: false, msg: 'Funds record not found' });
    }
    
    const [updatedFundsRecord] = await db.query(`
      SELECT df.*, db.name as delivery_boy_name, db.phone as delivery_boy_phone
      FROM delivery_boy_funds df 
      LEFT JOIN delivery_boys db ON df.delivery_boy_id = db.id 
      WHERE df.id = ?
    `, [id]);
    
    res.json({ success: true, data: updatedFundsRecord[0], msg: 'Funds record updated successfully' });
  } catch (error) {
    console.error('Error updating funds record:', error);
    res.status(500).json({ success: false, msg: 'Error updating funds record' });
  }
};

// Delete delivery boy funds
const deleteDeliveryBoyFunds = async (req, res) => {
  try {
    const { id } = req.params;
    
    const [result] = await db.query('DELETE FROM delivery_boy_funds WHERE id = ?', [id]);
    
    if (result.affectedRows === 0) {
      return res.status(404).json({ success: false, msg: 'Funds record not found' });
    }
    
    res.json({ success: true, msg: 'Funds record deleted successfully' });
  } catch (error) {
    console.error('Error deleting funds record:', error);
    res.status(500).json({ success: false, msg: 'Error deleting funds record' });
  }
};

module.exports = {
  getAllDeliveryBoyFunds,
  getDeliveryBoyFundsById,
  createDeliveryBoyFunds,
  updateDeliveryBoyFunds,
  deleteDeliveryBoyFunds
};
