// DATABASE 
use bookmyshowDB

//CREATE COLLECTIONS 
db.createCollection("movies")
db.createCollection("theatres")
db.createCollection("shows")
db.createCollection("users")
db.createCollection("bookings")

// Show collections
show collections


//  INSERT DATA 

// Movies
db.movies.insertMany([
  { _id: 1, title: "Inception", genre: "Sci-Fi", duration: 148, rating: 8.8 },
  { _id: 2, title: "Jawan", genre: "Action", duration: 165, rating: 7.9 },
  { _id: 3, title: "Avatar 2", genre: "Adventure", duration: 192, rating: 8.2 }
])

// Theatres
db.theatres.insertMany([
  { _id: 101, name: "PVR Icon", city: "Mumbai", capacity: 200 },
  { _id: 102, name: "INOX", city: "Pune", capacity: 150 },
  { _id: 103, name: "Carnival", city: "Delhi", capacity: 180 }
])

// Shows
db.shows.insertMany([
  { _id: 201, movie_id: 1, theatre_id: 101, show_time: "2025-11-06T18:00:00", price: 300 },
  { _id: 202, movie_id: 2, theatre_id: 102, show_time: "2025-11-06T21:00:00", price: 250 },
  { _id: 203, movie_id: 3, theatre_id: 103, show_time: "2025-11-06T19:30:00", price: 350 }
])

// Users
db.users.insertMany([
  { _id: 301, name: "Atharva", email: "atharva@gmail.com" },
  { _id: 302, name: "Sneha", email: "sneha@gmail.com" },
  { _id: 303, name: "Raj", email: "raj@gmail.com" }
])

// Bookings
db.bookings.insertMany([
  { booking_id: 401, user_id: 301, show_id: 201, seats: 2, total_price: 600 },
  { booking_id: 402, user_id: 302, show_id: 202, seats: 3, total_price: 750 },
  { booking_id: 403, user_id: 303, show_id: 203, seats: 4, total_price: 1400 }
])


// CRUD OPERATIONS 

// CREATE
db.movies.insertOne({
  title: "Leo",
  genre: "Action",
  duration: 160,
  rating: 7.5
})

// READ
db.movies.find().pretty()
db.movies.find({ genre: "Action" })

// UPDATE
db.movies.updateOne(
  { title: "Leo" },
  { $set: { rating: 8.0 } }
)

// DELETE
db.movies.deleteOne({ title: "Avatar 2" })


//  ANALYTICAL QUERIES 

// Top 3 most booked movies
db.bookings.aggregate([
  {
    $lookup: {
      from: "shows",
      localField: "show_id",
      foreignField: "_id",
      as: "show"
    }
  },
  { $unwind: "$show" },
  {
    $group: {
      _id: "$show.movie_id",
      totalBookings: { $sum: 1 }
    }
  },
  { $sort: { totalBookings: -1 } },
  { $limit: 3 }
])

// Total revenue per movie
db.bookings.aggregate([
  {
    $lookup: {
      from: "shows",
      localField: "show_id",
      foreignField: "_id",
      as: "show"
    }
  },
  { $unwind: "$show" },
  {
    $group: {
      _id: "$show.movie_id",
      totalRevenue: { $sum: "$total_price" }
    }
  }
])

// Total bookings per theatre
db.bookings.aggregate([
  {
    $lookup: {
      from: "shows",
      localField: "show_id",
      foreignField: "_id",
      as: "show"
    }
  },
  { $unwind: "$show" },
  {
    $group: {
      _id: "$show.theatre_id",
      count: { $sum: 1 }
    }
  }
])

// Number of shows per day
db.shows.aggregate([
  {
    $group: {
      _id: { $substr: ["$show_time", 0, 10] },
      showsCount: { $sum: 1 }
    }
  }
])

// Movies count by genre
db.movies.aggregate([
  {
    $group: {
      _id: "$genre",
      count: { $sum: 1 }
    }
  }
])