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

// Dashboard statistics - shows overall summary
exports.getDashboardStats = async (req, res) => {
  try {
    // Get counts of all major entities
    const [plantCount] = await db.query('SELECT COUNT(*) as count FROM plants');
    const [surveyCount] = await db.query('SELECT COUNT(*) as count FROM survey_entries');
    const [informantCount] = await db.query('SELECT COUNT(*) as count FROM informants');
    const [locationCount] = await db.query('SELECT COUNT(*) as count FROM locations');
    const [conditionCount] = await db.query('SELECT COUNT(*) as count FROM conditions');
    
    // Recent surveys
    const [recentSurveys] = await db.query(`
      SELECT se.entry_id, se.created_at, p.local_name as plant_name, l.village_name
      FROM survey_entries se
      LEFT JOIN plants p ON se.plant_id = p.plant_id
      LEFT JOIN locations l ON se.location_id = l.location_id
      ORDER BY se.created_at DESC
      LIMIT 5
    `);
    
    res.json({
      success: true,
      data: {
        summary: {
          total_plants: plantCount[0].count,
          total_surveys: surveyCount[0].count,
          total_informants: informantCount[0].count,
          total_locations: locationCount[0].count,
          total_conditions: conditionCount[0].count
        },
        recent_surveys: recentSurveys
      }
    });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
};

// Plant statistics
exports.getPlantStats = async (req, res) => {
  try {
    // Plants by type
    const [plantsByType] = await db.query(`
      SELECT plant_type, COUNT(*) as count 
      FROM plants 
      WHERE plant_type IS NOT NULL 
      GROUP BY plant_type
      ORDER BY count DESC
    `);
    
    // Most documented plants (most surveys)
    const [mostDocumented] = await db.query(`
      SELECT p.plant_id, p.local_name, p.scientific_name, COUNT(se.entry_id) as survey_count
      FROM plants p
      LEFT JOIN survey_entries se ON p.plant_id = se.plant_id
      GROUP BY p.plant_id
      ORDER BY survey_count DESC
      LIMIT 10
    `);
    
    res.json({
      success: true,
      data: {
        by_type: plantsByType,
        most_documented: mostDocumented
      }
    });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
};

// Survey statistics
exports.getSurveyStats = async (req, res) => {
  try {
    // Surveys by usage category
    const [byCategory] = await db.query(`
      SELECT uc.category_name, COUNT(se.entry_id) as count
      FROM survey_entries se
      LEFT JOIN usage_categories uc ON se.usage_category_id = uc.usage_category_id
      WHERE uc.category_name IS NOT NULL
      GROUP BY uc.category_name
      ORDER BY count DESC
    `);
    
    // Surveys by condition
    const [byCondition] = await db.query(`
      SELECT c.condition_name, COUNT(se.entry_id) as count
      FROM survey_entries se
      LEFT JOIN conditions c ON se.condition_id = c.condition_id
      WHERE c.condition_name IS NOT NULL
      GROUP BY c.condition_name
      ORDER BY count DESC
      LIMIT 10
    `);
    
    // Average effectiveness rating
    const [avgRating] = await db.query(`
      SELECT AVG(effectiveness_rating) as avg_rating, COUNT(*) as rated_count
      FROM survey_entries
      WHERE effectiveness_rating IS NOT NULL
    `);
    
    res.json({
      success: true,
      data: {
        by_category: byCategory,
        by_condition: byCondition,
        effectiveness: {
          average_rating: avgRating[0].avg_rating ? parseFloat(avgRating[0].avg_rating).toFixed(2) : null,
          total_rated: avgRating[0].rated_count
        }
      }
    });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
};

// Location-wise statistics
exports.getLocationStats = async (req, res) => {
  try {
    // Surveys by state
    const [byState] = await db.query(`
      SELECT l.state, COUNT(se.entry_id) as survey_count
      FROM survey_entries se
      LEFT JOIN locations l ON se.location_id = l.location_id
      WHERE l.state IS NOT NULL
      GROUP BY l.state
      ORDER BY survey_count DESC
    `);
    
    // Surveys by district
    const [byDistrict] = await db.query(`
      SELECT l.district, l.state, COUNT(se.entry_id) as survey_count
      FROM survey_entries se
      LEFT JOIN locations l ON se.location_id = l.location_id
      WHERE l.district IS NOT NULL
      GROUP BY l.district, l.state
      ORDER BY survey_count DESC
      LIMIT 15
    `);
    
    res.json({
      success: true,
      data: {
        by_state: byState,
        by_district: byDistrict
      }
    });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
};