mongodb_queries.md

# MongoDB Queries for media_clicks Collection

## Database: admin (or your admin database name)
## Collection: media_clicks

---

## 1. Find documents with ai_edit_last_date field

### Find all documents that have ai_edit_last_date
```javascript
db.media_clicks.find({ ai_edit_last_date: { $exists: true } })
```

### Find documents with specific ai_edit_last_date
```javascript
// Find by exact date (format: DD/MM/YYYY)
db.media_clicks.find({ ai_edit_last_date: "12/12/2025" })
```

### Find documents with ai_edit_last_date in date range
```javascript
// Find documents with dates in December 2025
db.media_clicks.find({ 
  ai_edit_last_date: { 
    $regex: /^\\d{2}\/12\/2025$/ 
  } 
})
```

### Find documents with ai_edit_last_date NOT null/empty
```javascript
db.media_clicks.find({ 
  ai_edit_last_date: { 
    $exists: true, 
    $ne: null, 
    $ne: "" 
  } 
})
```

---

## 2. Check if ai_edit_complete field exists

### Find documents that HAVE ai_edit_complete field
```javascript
db.media_clicks.find({ ai_edit_complete: { $exists: true } })
```

### Find documents that DO NOT have ai_edit_complete field (old data)
```javascript
db.media_clicks.find({ ai_edit_complete: { $exists: false } })
```

### Find documents where ai_edit_complete is 0 (new users who haven't used model yet)
```javascript
db.media_clicks.find({ ai_edit_complete: 0 })
```

### Find documents where ai_edit_complete is greater than 0 (users who have used models)
```javascript
db.media_clicks.find({ ai_edit_complete: { $gt: 0 } })
```

---

## 3. Combined Queries

### Find documents with both fields
```javascript
db.media_clicks.find({ 
  ai_edit_complete: { $exists: true },
  ai_edit_last_date: { $exists: true }
})
```

### Find documents missing either field (old data)
```javascript
db.media_clicks.find({ 
  $or: [
    { ai_edit_complete: { $exists: false } },
    { ai_edit_last_date: { $exists: false } }
  ]
})
```

### Find users who have used models (ai_edit_complete > 0) with their last date
```javascript
db.media_clicks.find({ 
  ai_edit_complete: { $gt: 0 },
  ai_edit_last_date: { $exists: true }
})
```

---

## 4. Count Queries

### Count documents with ai_edit_last_date
```javascript
db.media_clicks.countDocuments({ ai_edit_last_date: { $exists: true } })
```

### Count documents with ai_edit_complete field
```javascript
db.media_clicks.countDocuments({ ai_edit_complete: { $exists: true } })
```

### Count documents without ai_edit_complete (old data)
```javascript
db.media_clicks.countDocuments({ ai_edit_complete: { $exists: false } })
```

### Count users who have used models (ai_edit_complete > 0)
```javascript
db.media_clicks.countDocuments({ ai_edit_complete: { $gt: 0 } })
```

---

## 5. Aggregation Queries

### Get statistics for ai_edit_complete
```javascript
db.media_clicks.aggregate([
  {
    $group: {
      _id: null,
      total_users: { $sum: 1 },
      users_with_field: {
        $sum: { $cond: [{ $ifNull: ["$ai_edit_complete", false] }, 1, 0] }
      },
      users_without_field: {
        $sum: { $cond: [{ $ifNull: ["$ai_edit_complete", false] }, 0, 1] }
      },
      avg_ai_edit_complete: { $avg: "$ai_edit_complete" },
      max_ai_edit_complete: { $max: "$ai_edit_complete" },
      min_ai_edit_complete: { $min: "$ai_edit_complete" }
    }
  }
])
```

### Get users by ai_edit_complete range
```javascript
db.media_clicks.aggregate([
  {
    $group: {
      _id: {
        $switch: {
          branches: [
            { case: { $eq: ["$ai_edit_complete", null] }, then: "No field (old data)" },
            { case: { $eq: ["$ai_edit_complete", 0] }, then: "0 (new, not used)" },
            { case: { $lte: ["$ai_edit_complete", 5] }, then: "1-5 uses" },
            { case: { $lte: ["$ai_edit_complete", 10] }, then: "6-10 uses" },
            { case: { $gt: ["$ai_edit_complete", 10] }, then: "10+ uses" }
          ],
          default: "Unknown"
        }
      },
      count: { $sum: 1 }
    }
  },
  { $sort: { _id: 1 } }
])
```

### Get latest ai_edit_last_date for each user
```javascript
db.media_clicks.find(
  { ai_edit_last_date: { $exists: true } },
  { 
    userId: 1, 
    ai_edit_last_date: 1, 
    ai_edit_complete: 1 
  }
).sort({ ai_edit_last_date: -1 })
```

---

## 6. Update Queries (if needed to fix old data)

### Add default ai_edit_complete = 0 to documents missing it
```javascript
db.media_clicks.updateMany(
  { ai_edit_complete: { $exists: false } },
  { $set: { ai_edit_complete: 0 } }
)
```

### Set ai_edit_last_date based on updatedAt for old documents
```javascript
// Note: This converts updatedAt to DD/MM/YYYY format
// Run this carefully as it modifies data
db.media_clicks.find({ 
  ai_edit_last_date: { $exists: false },
  updatedAt: { $exists: true }
}).forEach(function(doc) {
  var date = doc.updatedAt;
  var formatted = date.getDate().toString().padStart(2, '0') + '/' + 
                  (date.getMonth() + 1).toString().padStart(2, '0') + '/' + 
                  date.getFullYear();
  db.media_clicks.updateOne(
    { _id: doc._id },
    { $set: { ai_edit_last_date: formatted } }
  );
})
```

---

## 7. Find by userId (ObjectId)

### Find specific user by userId
```javascript
// Replace with actual ObjectId
db.media_clicks.find({ 
  userId: ObjectId("693652b3f8683fd35b75448a") 
})
```

### Find user and check if fields exist
```javascript
db.media_clicks.findOne(
  { userId: ObjectId("693652b3f8683fd35b75448a") },
  { 
    userId: 1,
    ai_edit_complete: 1,
    ai_edit_last_date: 1,
    categories: 1,
    updatedAt: 1
  }
)
```

---

## 8. Find by Category

### Find users who used specific category with ai_edit fields
```javascript
db.media_clicks.find({
  "categories.categoryId": ObjectId("69368d62b95a6c2a75920505"),
  ai_edit_complete: { $exists: true }
})
```

---

## Quick Test Queries

### Check if field exists in sample document
```javascript
db.media_clicks.findOne({}, { 
  userId: 1, 
  ai_edit_complete: 1, 
  ai_edit_last_date: 1 
})
```

### Get sample of documents with and without fields
```javascript
// With fields
db.media_clicks.find({ ai_edit_complete: { $exists: true } }).limit(5)

// Without fields (old data)
db.media_clicks.find({ ai_edit_complete: { $exists: false } }).limit(5)
```