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

// GET all survey entries
exports.getAllSurveyEntries = async (req, res) => {
  try {
    const [rows] = await db.query(`
      SELECT se.*, p.local_name as plant_name, i.tribe_name, l.village_name, l.district, l.state
      FROM survey_entries se
      LEFT JOIN plants p ON se.plant_id = p.plant_id
      LEFT JOIN informants i ON se.informant_id = i.informant_id
      LEFT JOIN locations l ON se.location_id = l.location_id
      ORDER BY se.created_at DESC
    `);
    res.json({ success: true, data: rows });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
};

// GET single survey entry
exports.getSurveyEntryById = async (req, res) => {
  try {
    const [rows] = await db.query('SELECT * FROM survey_entries WHERE entry_id = ?', [req.params.id]);
    if (rows.length === 0) {
      return res.status(404).json({ success: false, error: 'Survey entry not found' });
    }
    res.json({ success: true, data: rows[0] });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
};

// GET full survey entry with all related data
exports.getFullSurveyEntry = async (req, res) => {
  try {
    const entryId = req.params.id;
    
    // Main entry data
    const [entry] = await db.query('SELECT * FROM survey_entries WHERE entry_id = ?', [entryId]);
    if (entry.length === 0) {
      return res.status(404).json({ success: false, error: 'Survey entry not found' });
    }
    
    // Plant parts used
    const [parts] = await db.query(`
      SELECT pp.* FROM plant_parts pp
      JOIN entry_parts_used epu ON pp.part_id = epu.part_id
      WHERE epu.entry_id = ?
    `, [entryId]);
    
    // Ingredients
    const [ingredients] = await db.query(`
      SELECT i.*, ei.notes FROM ingredients i
      JOIN entry_ingredients ei ON i.ingredient_id = ei.ingredient_id
      WHERE ei.entry_id = ?
    `, [entryId]);
    
    // Animal types
    const [animals] = await db.query(`
      SELECT at.* FROM animal_types at
      JOIN entry_animal_types eat ON at.animal_type_id = eat.animal_type_id
      WHERE eat.entry_id = ?
    `, [entryId]);
    
    // Plant combinations
    const [combinations] = await db.query(
      'SELECT * FROM entry_plant_combinations WHERE entry_id = ?',
      [entryId]
    );
    
    // Ritual details
    const [ritual] = await db.query('SELECT * FROM ritual_details WHERE entry_id = ?', [entryId]);
    
    // Safety info
    const [safety] = await db.query('SELECT * FROM safety_info WHERE entry_id = ?', [entryId]);
    
    // Availability
    const [availability] = await db.query('SELECT * FROM availability_status WHERE entry_id = ?', [entryId]);
    
    // Media
    const [media] = await db.query('SELECT * FROM entry_media WHERE entry_id = ?', [entryId]);
    
    const fullData = {
      ...entry[0],
      parts_used: parts,
      ingredients: ingredients,
      animal_types: animals,
      plant_combinations: combinations,
      ritual_details: ritual[0] || null,
      safety_info: safety[0] || null,
      availability_status: availability[0] || null,
      media: media
    };
    
    res.json({ success: true, data: fullData });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
};

// CREATE new survey entry
exports.createSurveyEntry = async (req, res) => {
  const connection = await db.getConnection();
  
  try {
    await connection.beginTransaction();
    
    const {
      original_entry_id, submitted_at, timestamp_2, consent_given,
      plant_id, informant_id, location_id, gps_latitude, gps_longitude,
      usage_category_id, purpose_description, is_used_for_animals,
      condition_id, condition_as_reported, detailed_disease_description,
      preparation_method, mode_of_use, ingredients_mixed_raw, used_alone_or_combined,
      dosage_quantity, dosage_unit, frequency_of_administration,
      effectiveness_rating, has_cultural_ritual_use, is_culturally_sensitive
    } = req.body;
    
    const [result] = await connection.query(
      `INSERT INTO survey_entries (
        original_entry_id, submitted_at, timestamp_2, consent_given,
        plant_id, informant_id, location_id, gps_latitude, gps_longitude,
        usage_category_id, purpose_description, is_used_for_animals,
        condition_id, condition_as_reported, detailed_disease_description,
        preparation_method, mode_of_use, ingredients_mixed_raw, used_alone_or_combined,
        dosage_quantity, dosage_unit, frequency_of_administration,
        effectiveness_rating, has_cultural_ritual_use, is_culturally_sensitive
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        original_entry_id, submitted_at, timestamp_2, consent_given,
        plant_id, informant_id, location_id, gps_latitude, gps_longitude,
        usage_category_id, purpose_description, is_used_for_animals,
        condition_id, condition_as_reported, detailed_disease_description,
        preparation_method, mode_of_use, ingredients_mixed_raw, used_alone_or_combined,
        dosage_quantity, dosage_unit, frequency_of_administration,
        effectiveness_rating, has_cultural_ritual_use, is_culturally_sensitive
      ]
    );
    
    const entryId = result.insertId;
    
    await connection.commit();
    
    res.status(201).json({ 
      success: true, 
      message: 'Survey entry created successfully',
      data: { entry_id: entryId }
    });
  } catch (error) {
    await connection.rollback();
    res.status(500).json({ success: false, error: error.message });
  } finally {
    connection.release();
  }
};

// UPDATE survey entry
exports.updateSurveyEntry = async (req, res) => {
  try {
    const fields = req.body;
    const updates = Object.keys(fields).map(key => `${key} = ?`).join(', ');
    const values = [...Object.values(fields), req.params.id];
    
    const [result] = await db.query(
      `UPDATE survey_entries SET ${updates} WHERE entry_id = ?`,
      values
    );
    
    if (result.affectedRows === 0) {
      return res.status(404).json({ success: false, error: 'Survey entry not found' });
    }
    
    res.json({ success: true, message: 'Survey entry updated successfully' });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
};

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

// GET ritual details
exports.getRitualDetails = async (req, res) => {
  try {
    const [rows] = await db.query('SELECT * FROM ritual_details WHERE entry_id = ?', [req.params.id]);
    res.json({ success: true, data: rows[0] || null });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
};

// GET safety info
exports.getSafetyInfo = async (req, res) => {
  try {
    const [rows] = await db.query('SELECT * FROM safety_info WHERE entry_id = ?', [req.params.id]);
    res.json({ success: true, data: rows[0] || null });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
};

// GET availability status
exports.getAvailabilityStatus = async (req, res) => {
  try {
    const [rows] = await db.query('SELECT * FROM availability_status WHERE entry_id = ?', [req.params.id]);
    res.json({ success: true, data: rows[0] || null });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
};

// GET entry media
exports.getEntryMedia = async (req, res) => {
  try {
    const [rows] = await db.query('SELECT * FROM entry_media WHERE entry_id = ?', [req.params.id]);
    res.json({ success: true, data: rows });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
};