| const db = require('../config/database');
|
|
|
|
|
| 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}%`;
|
|
|
|
|
| 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]);
|
|
|
|
|
| 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 });
|
| }
|
| };
|
|
|
|
|
| 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 });
|
| }
|
| };
|
|
|
|
|
| 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 });
|
| }
|
| };
|
|
|
|
|
| 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 });
|
| }
|
| };
|
|
|
