src/controllers/informantController.js

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

exports.getAllInformants = async (req, res) => {
  try {
    const [rows] = await db.query(`
      SELECT i.*, l.village_name, l.district, l.state 
      FROM informants i
      LEFT JOIN locations l ON i.location_id = l.location_id
    `);
    res.json({ success: true, data: rows });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
};

exports.getInformantById = async (req, res) => {
  try {
    const [rows] = await db.query(`
      SELECT i.*, l.village_name, l.district, l.state 
      FROM informants i
      LEFT JOIN locations l ON i.location_id = l.location_id
      WHERE i.informant_id = ?
    `, [req.params.id]);
    
    if (rows.length === 0) {
      return res.status(404).json({ success: false, error: 'Informant not found' });
    }
    res.json({ success: true, data: rows[0] });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
};

exports.createInformant = async (req, res) => {
  try {
    const { tribe_name, age_raw, age_min, age_max, gender, role_in_community, knowledge_source, location_id } = req.body;
    
    const [result] = await db.query(
      `INSERT INTO informants (tribe_name, age_raw, age_min, age_max, gender, role_in_community, knowledge_source, location_id) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
      [tribe_name, age_raw, age_min, age_max, gender, role_in_community, knowledge_source, location_id]
    );
    
    res.status(201).json({ 
      success: true, 
      message: 'Informant created successfully',
      data: { informant_id: result.insertId }
    });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
};

exports.updateInformant = async (req, res) => {
  try {
    const { tribe_name, age_raw, age_min, age_max, gender, role_in_community, knowledge_source, location_id } = req.body;
    
    const [result] = await db.query(
      `UPDATE informants SET tribe_name = ?, age_raw = ?, age_min = ?, age_max = ?, gender = ?, 
       role_in_community = ?, knowledge_source = ?, location_id = ? WHERE informant_id = ?`,
      [tribe_name, age_raw, age_min, age_max, gender, role_in_community, knowledge_source, location_id, req.params.id]
    );
    
    if (result.affectedRows === 0) {
      return res.status(404).json({ success: false, error: 'Informant not found' });
    }
    
    res.json({ success: true, message: 'Informant updated successfully' });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
};

exports.deleteInformant = async (req, res) => {
  try {
    const [result] = await db.query('DELETE FROM informants WHERE informant_id = ?', [req.params.id]);
    
    if (result.affectedRows === 0) {
      return res.status(404).json({ success: false, error: 'Informant not found' });
    }
    
    res.json({ success: true, message: 'Informant deleted successfully' });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
};