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

// Global search - searches across plants and surveys
exports.globalSearch = async (req, res) => {
  try {
    const { q } = req.query;
    
    if (!q || q.trim().length < 2) {
      return res.status(400).json({ 
        success: false, 
        error: 'Search query must be at least 2 characters' 
      });
    }
    
    const searchTerm = `%${q}%`;
    
    // Search in plants
    const [plants] = await db.query(`
      SELECT plant_id, local_name, scientific_name, plant_type, 'plant' as result_type
      FROM plants 
      WHERE local_name LIKE ? OR scientific_name LIKE ?
      LIMIT 10
    `, [searchTerm, searchTerm]);
    
    // Search in survey purposes
    const [surveys] = await db.query(`
      SELECT se.entry_id, se.purpose_description, p.local_name as plant_name, 'survey' as result_type
      FROM survey_entries se
      LEFT JOIN plants p ON se.plant_id = p.plant_id
      WHERE se.purpose_description LIKE ? OR se.preparation_method LIKE ?
      LIMIT 10
    `, [searchTerm, searchTerm]);
    
    res.json({
      success: true,
      query: q,
      results: {
        plants: plants,
        surveys: surveys,
        total_found: plants.length + surveys.length
      }
    });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
};

// Search plants by name
exports.searchPlants = async (req, res) => {
  try {
    const { name, type } = req.query;
    
    let query = 'SELECT * FROM plants WHERE 1=1';
    const params = [];
    
    if (name) {
      query += ' AND (local_name LIKE ? OR scientific_name LIKE ?)';
      params.push(`%${name}%`, `%${name}%`);
    }
    
    if (type) {
      query += ' AND plant_type = ?';
      params.push(type);
    }
    
    query += ' ORDER BY local_name LIMIT 50';
    
    const [results] = await db.query(query, params);
    
    res.json({
      success: true,
      count: results.length,
      data: results
    });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
};

// Search by medical condition
exports.searchByCondition = async (req, res) => {
  try {
    const { condition } = req.query;
    
    if (!condition) {
      return res.status(400).json({ 
        success: false, 
        error: 'Condition parameter is required' 
      });
    }
    
    const [results] = await db.query(`
      SELECT 
        p.plant_id,
        p.local_name,
        p.scientific_name,
        c.condition_name,
        se.preparation_method,
        se.mode_of_use,
        se.effectiveness_rating
      FROM survey_entries se
      LEFT JOIN plants p ON se.plant_id = p.plant_id
      LEFT JOIN conditions c ON se.condition_id = c.condition_id
      WHERE c.condition_name LIKE ? OR se.condition_as_reported LIKE ?
      ORDER BY se.effectiveness_rating DESC
      LIMIT 20
    `, [`%${condition}%`, `%${condition}%`]);
    
    res.json({
      success: true,
      condition: condition,
      count: results.length,
      data: results
    });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
};

// Advanced filter with multiple criteria
exports.advancedFilter = async (req, res) => {
  try {
    const { 
      plant_type, 
      usage_category_id, 
      location_id, 
      min_effectiveness,
      has_side_effects 
    } = req.body;
    
    let query = `
      SELECT 
        se.entry_id,
        p.local_name,
        p.plant_type,
        uc.category_name,
        l.village_name,
        l.district,
        se.effectiveness_rating
      FROM survey_entries se
      LEFT JOIN plants p ON se.plant_id = p.plant_id
      LEFT JOIN usage_categories uc ON se.usage_category_id = uc.usage_category_id
      LEFT JOIN locations l ON se.location_id = l.location_id
      WHERE 1=1
    `;
    
    const params = [];
    
    if (plant_type) {
      query += ' AND p.plant_type = ?';
      params.push(plant_type);
    }
    
    if (usage_category_id) {
      query += ' AND se.usage_category_id = ?';
      params.push(usage_category_id);
    }
    
    if (location_id) {
      query += ' AND se.location_id = ?';
      params.push(location_id);
    }
    
    if (min_effectiveness) {
      query += ' AND se.effectiveness_rating >= ?';
      params.push(min_effectiveness);
    }
    
    if (has_side_effects !== undefined) {
      if (has_side_effects) {
        query += ' AND EXISTS (SELECT 1 FROM safety_info si WHERE si.entry_id = se.entry_id AND si.side_effects IS NOT NULL)';
      } else {
        query += ' AND NOT EXISTS (SELECT 1 FROM safety_info si WHERE si.entry_id = se.entry_id AND si.side_effects IS NOT NULL)';
      }
    }
    
    query += ' ORDER BY se.effectiveness_rating DESC LIMIT 50';
    
    const [results] = await db.query(query, params);
    
    res.json({
      success: true,
      filters_applied: req.body,
      count: results.length,
      data: results
    });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
};