controllers/queryController.js

// controllers/queryController.js
const mongoose = require('mongoose');
const Play = require('../models/Play');
const TheatreGroup = require('../models/TheatreGroup');
const Venue = require('../models/Venue');
const Critic = require('../models/Critic');
const Actor = require('../models/Actor');
const Workshop = require('../models/Workshop');

/**
 * 1. List all plays performed in Kannada language.
 */
async function getKannadaPlays(req, res) {
  try {
    const plays = await Play.find({ language: { $regex: '^kannada$', $options: 'i' } })
      .populate('venues', 'name location')
      .populate('theatreGroup', 'name')
      .populate('actors', 'name')
      .populate('ratings.critic', 'name');
    return res.status(200).json(plays);
  } catch (err) {
    console.error('getKannadaPlays:', err);
    return res.status(500).json({ error: 'Server error', message: err.message });
  }
}

/**
 * 2. Find the group that performed the maximum number of plays.
 */
async function getGroupWithMaxPlays(req, res) {
  try {
    const result = await TheatreGroup.aggregate([
      { $project: { name: 1, playCount: { $size: { $ifNull: ['$plays', []] } } } },
      { $sort: { playCount: -1 } },
      { $limit: 1 }
    ]);
    return res.status(200).json(result[0] || null);
  } catch (err) {
    console.error('getGroupWithMaxPlays:', err);
    return res.status(500).json({ error: 'Server error', message: err.message });
  }
}

/**
 * 3. Show venues where more than 3 plays were staged.
 */
async function getVenuesWithMoreThan3Plays(req, res) {
  try {
    const venues = await Venue.aggregate([
      { $addFields: { playCount: { $size: { $ifNull: ['$plays', []] } } } },
      { $match: { playCount: { $gt: 3 } } }
    ]);
    return res.status(200).json(venues);
  } catch (err) {
    console.error('getVenuesWithMoreThan3Plays:', err);
    return res.status(500).json({ error: 'Server error', message: err.message });
  }
}

/**
 * 4. Retrieve critics who reviewed more than 5 plays.
 */
async function getCriticsReviewedMoreThan5(req, res) {
  try {
    const critics = await Critic.aggregate([
      { $project: { name: 1, reviewCount: { $size: { $ifNull: ['$reviews', []] } } } },
      { $match: { reviewCount: { $gt: 5 } } }
    ]);
    return res.status(200).json(critics);
  } catch (err) {
    console.error('getCriticsReviewedMoreThan5:', err);
    return res.status(500).json({ error: 'Server error', message: err.message });
  }
}

/**
 * 5. Calculate average audience rating per genre.
 * Note: Play model stores ratings array (score). We compute average of ratings.score by genre.
 */
async function getAvgRatingPerGenre(req, res) {
  try {
    const result = await Play.aggregate([
      // unwind ratings (skip plays with no ratings)
      { $unwind: { path: '$ratings', preserveNullAndEmptyArrays: false } },
      { $group: { _id: '$genre', avgRating: { $avg: '$ratings.score' }, count: { $sum: 1 } } },
      { $project: { genre: '$_id', avgRating: { $round: ['$avgRating', 2] }, ratingCount: '$count', _id: 0 } },
      { $sort: { avgRating: -1 } }
    ]);
    return res.status(200).json(result);
  } catch (err) {
    console.error('getAvgRatingPerGenre:', err);
    return res.status(500).json({ error: 'Server error', message: err.message });
  }
}

/**
 * 6. Find plays that won both Best Play and Best Script awards.
 */
async function getPlaysWithBothAwards(req, res) {
  try {
    const plays = await Play.find({ awards: { $all: ['Best Play', 'Best Script'] } })
      .populate('theatreGroup', 'name')
      .populate('venues', 'name location')
      .populate('actors', 'name');
    return res.status(200).json(plays);
  } catch (err) {
    console.error('getPlaysWithBothAwards:', err);
    return res.status(500).json({ error: 'Server error', message: err.message });
  }
}

/**
 * 7. Show actors who performed in more than one play.
 */
async function getActorsInMultiplePlays(req, res) {
  try {
    // matches actors where plays array length > 1
    const actors = await Actor.find({ 'plays.1': { $exists: true } }).select('name plays');
    return res.status(200).json(actors);
  } catch (err) {
    console.error('getActorsInMultiplePlays:', err);
    return res.status(500).json({ error: 'Server error', message: err.message });
  }
}

/**
 * 8. Identify genres with the most plays staged.
 */
async function getGenresWithMostPlays(req, res) {
  try {
    const result = await Play.aggregate([
      { $group: { _id: '$genre', playCount: { $sum: 1 } } },
      { $project: { genre: '$_id', playCount: 1, _id: 0 } },
      { $sort: { playCount: -1 } }
    ]);
    return res.status(200).json(result);
  } catch (err) {
    console.error('getGenresWithMostPlays:', err);
    return res.status(500).json({ error: 'Server error', message: err.message });
  }
}

/**
 * 9. Retrieve workshops attended by more than 50 participants.
 */
async function getWorkshopsWithMoreThan50(req, res) {
  try {
    const workshops = await Workshop.find({ participantCount: { $gt: 50 } });
    return res.status(200).json(workshops);
  } catch (err) {
    console.error('getWorkshopsWithMoreThan50:', err);
    return res.status(500).json({ error: 'Server error', message: err.message });
  }
}

/**
 * 10. Show critics who gave ratings below 5 consistently.
 * (Require at least one review and none with score >= 5)
 */
async function getCriticsAlwaysBelow5(req, res) {
  try {
    const critics = await Critic.find({
      reviews: { $exists: true, $ne: [] },
      // no review with score >= 5
      $nor: [{ reviews: { $elemMatch: { score: { $gte: 5 } } } }]
    }).select('name reviews');
    return res.status(200).json(critics);
  } catch (err) {
    console.error('getCriticsAlwaysBelow5:', err);
    return res.status(500).json({ error: 'Server error', message: err.message });
  }
}

/**
 * 11. Find plays performed in multiple venues.
 */
async function getPlaysInMultipleVenues(req, res) {
  try {
    const plays = await Play.find({ 'venues.1': { $exists: true } })
      .populate('venues', 'name location')
      .populate('theatreGroup', 'name')
      .populate('actors', 'name');
    return res.status(200).json(plays);
  } catch (err) {
    console.error('getPlaysInMultipleVenues:', err);
    return res.status(500).json({ error: 'Server error', message: err.message });
  }
}

/**
 * 12. List theatre groups that never won awards.
 */
async function getGroupsWithNoAwards(req, res) {
  try {
    const groups = await TheatreGroup.find({
      $or: [{ awards: { $exists: false } }, { awards: { $eq: [] } }]
    }).select('name plays awards');
    return res.status(200).json(groups);
  } catch (err) {
    console.error('getGroupsWithNoAwards:', err);
    return res.status(500).json({ error: 'Server error', message: err.message });
  }
}

module.exports = {
  getKannadaPlays,
  getGroupWithMaxPlays,
  getVenuesWithMoreThan3Plays,
  getCriticsReviewedMoreThan5,
  getAvgRatingPerGenre,
  getPlaysWithBothAwards,
  getActorsInMultiplePlays,
  getGenresWithMostPlays,
  getWorkshopsWithMoreThan50,
  getCriticsAlwaysBelow5,
  getPlaysInMultipleVenues,
  getGroupsWithNoAwards
};