# Complete MongoDB Schema Migration Plan (v4 - Final)

## Changes in This Version

- Removed `applicable_boards`, `applicable_classes` from `master_subjects`
- Removed `applicable_subjects`, `typical_max_marks`, `description` from `master_subject_components`
- All collections use MongoDB timestamps (`createdAt`, `updatedAt`)
- Confirmed: Key-only approach for subjects and components

---

## Collection 1: `exam_results`

```javascript
{
    "_id": ObjectId("..."),
    
    // === Composite Key ===
    "school_id": 130,
    "session": "2025-26",
    "class": 10,
    "section": "A",
    "board": "CBSE",
    "exam_type": "half_yearly",
    "student_id": 12345,
    
    // === Student Snapshot ===
    "student_snapshot": {
        "admission_no": "12345",
        "name": "Rahul Kumar",
        "roll_no": 12,
        "father_name": "Vijay Kumar"
    },
    
    // === Summary ===
    "summary": {
        "total_obtained": 450.5,
        "total_max": 500,
        "percentage": 90.1,
        "grade": "A1",
        "result_status": "P"
    },
    
    // === Subjects ===
    "subjects": [
        {
            "key": "english",
            "type": "scholastic",
            "obtained": 92.5,
            "max": 100,
            "grade": "A1",
            "is_absent": false,
            "components": [
                { "key": "theory", "max": 80, "obtained": 75 },
                { "key": "periodic_test_avg", "max": 10, "obtained": 9.0 },
                { "key": "notebook", "max": 5, "obtained": 4.5 },
                { "key": "subject_enrichment", "max": 5, "obtained": 4 }
            ]
        },
        {
            "key": "physical_education",
            "type": "co_scholastic",
            "obtained": 5,
            "max": 5,
            "grade": "A",
            "is_absent": false,
            "components": [
                { "key": "grade", "max": 5, "obtained": 5 }
            ]
        }
    ],
    
    // === Workflow ===
    "workflow": {
        "stage": "submitted",
        "submitted_by": 4,
        "submitted_at": ISODate("...")
    },
    
    "createdAt": ISODate("..."),
    "updatedAt": ISODate("...")
}
```

**Indexes:**
```javascript
{ "school_id": 1, "session": 1, "class": 1, "section": 1, "board": 1, "exam_type": 1, "student_id": 1 }, { unique: true }
{ "student_id": 1, "session": 1 }
{ "school_id": 1, "session": 1, "class": 1, "exam_type": 1, "summary.percentage": -1 }
```

---

## Collection 2: `exam_results_history`

```javascript
{
    "_id": ObjectId("..."),
    "parent_result_id": ObjectId("..."),
    "snapshot_type": "pre_moderation",
    "snapshot_reason": "Before grace marks",
    "snapshot_by": 5,
    "document_snapshot": { ... },
    
    "createdAt": ISODate("...")
}
```

---

## Collection 3: `exam_blueprints`

```javascript
{
    "_id": ObjectId("..."),
    "session": "2025-26",
    "school_id": null,
    "class": 10,
    "board": "CBSE",
    "exam_type": "half_yearly",
    "exam_name": "Half Yearly Examination",
    "is_periodic_test": false,
    
    "subjects": [
        {
            "key": "english",
            "name": "English",
            "type": "scholastic",
            "display_order": 1,
            
            "components": [
                {
                    "key": "theory",
                    "label": "Term 1 Exam",
                    "max": 80,
                    "input_type": "number",
                    "component_type": "input",
                    "display_order": 1,
                    "validation": {"min": 0, "max": 80, "decimal": 2}
                },
                {
                    "key": "periodic_test_avg",
                    "label": "PT Average",
                    "max": 10,
                    "input_type": "number",
                    "component_type": "calculated",
                    "display_order": 2,
                    "calculation_event":"BeforMarksSubmission",
                    "showOnForm":true, 
                    "showOnReport":true,
                    "calculation_configration":{
                    "function": "copyAndRecalculate",
                    "inputs": {
                         "term": "pre_mid_term",
                         "component": "theory",
                         "recalculate_marks": 10
                    },
                    "is_readonly": true
                    }
                },
                {
                    "key": "total",
                    "label": "Total",
                    "max": 100,
                    "input_type": "number",
                    "component_type": "calculated",
                    "calculation_event":"AfterMarksSubmission",
                    "showOnForm":false, 
                    "showOnReport":true,
                    "display_order": 5,
                    "calculation_configration":{
                        "function": "sum",
                        "inputs": ["theory", "periodic_test_avg", "notebook", "subject_enrichment"],
                    },
                    "is_readonly": true
                }
            ]
        }
    ],
    
    "status": "active",
    "createdAt": ISODate("..."),
    "updatedAt": ISODate("...")
}
```

---

## Collection 4: `master_subjects`

```javascript
{
    "_id": ObjectId("..."),
    "key": "english",
    "name": "English",
    "type": "scholastic",
    "display_order": 1,
    "is_active": true,
    
    "createdAt": ISODate("..."),
    "updatedAt": ISODate("...")
}
```

**Index:** `{ "key": 1 }, { unique: true }`

---

## Collection 5: `master_subject_components`

```javascript
{
    "_id": ObjectId("..."),
    "key": "theory",
    "name": "Theory Exam",
    "default_input_type": "number",
    "is_active": true,
    
    "createdAt": ISODate("..."),
    "updatedAt": ISODate("...")
}
```

**Sample data:**
```javascript
{ "key": "theory", "name": "Theory Exam", "default_input_type": "number" }
{ "key": "practical", "name": "Practical Exam", "default_input_type": "number" }
{ "key": "notebook", "name": "Notebook", "default_input_type": "number" }
{ "key": "periodic_test", "name": "Periodic Test", "default_input_type": "number" }
{ "key": "periodic_test_avg", "name": "PT Average", "default_input_type": "number" }
{ "key": "subject_enrichment", "name": "Subject Enrichment", "default_input_type": "number" }
{ "key": "portfolio", "name": "Portfolio", "default_input_type": "number" }
{ "key": "grade", "name": "Grade", "default_input_type": "grade" }
{ "key": "total", "name": "Total Marks", "default_input_type": "number" }
{ "key": "percentage", "name": "Percentage", "default_input_type": "number" }
```

**Index:** `{ "key": 1 }, { unique: true }`

---

## Collection 6: `master_exam_types`

```javascript
{
    "_id": ObjectId("..."),
    "key": "half_yearly",
    "name": "Half Yearly",
    "display_name": "Half Yearly Examination",
    "is_periodic_test": false,
    "target_exam_type": null,
    "display_order": 2,
    "is_active": true,
    
    "createdAt": ISODate("..."),
    "updatedAt": ISODate("...")
}
```

**Index:** `{ "key": 1 }, { unique: true }`

---

## Collection 7: `grade_systems`

```javascript
{
    "_id": ObjectId("..."),
    "key": "cbse_secondary",
    "name": "CBSE Secondary",
    "grades": [
        {"grade": "A1", "min": 91, "max": 100, "points": 10},
        {"grade": "A2", "min": 81, "max": 90, "points": 9},
        {"grade": "B1", "min": 71, "max": 80, "points": 8},
        {"grade": "B2", "min": 61, "max": 70, "points": 7},
        {"grade": "C1", "min": 51, "max": 60, "points": 6},
        {"grade": "C2", "min": 41, "max": 50, "points": 5},
        {"grade": "D", "min": 33, "max": 40, "points": 4},
        {"grade": "E", "min": 0, "max": 32, "points": 0}
    ],
    "is_active": true,
    
    "createdAt": ISODate("..."),
    "updatedAt": ISODate("...")
}
```

---

## Collection 8: `workflow_status`

```javascript
{
    "_id": ObjectId("..."),
    "school_id": 130,
    "session": "2025-26",
    "class": 10,
    "section": "A",
    "board": "CBSE",
    "exam_type": "half_yearly",
    
    "status": 2,
    "teacher": {"id": 4, "submitted_at": ISODate("...")},
    "observer": {"id": 5, "submitted_at": ISODate("...")},
    "principal": {"id": null, "submitted_at": null},
    
    "createdAt": ISODate("..."),
    "updatedAt": ISODate("...")
}
```

---

## Collection 9: `teacher_subject_mapping`

Maps which teacher teaches which subject for a specific class/section/exam type.

```javascript
{
    "_id": ObjectId("..."),
    "school_id": 115,
    "session": "2025-26",
    "exam_type": "half_yearly",
    "class": 7,
    "section": "A",
    "subject_key": "english",      // Using key instead of subject_id
    "teacher_id": 8869,            // emp_id from original table
    "from_school": 115,
    "created_by": 1833,
    
    "createdAt": ISODate("..."),
    "updatedAt": ISODate("...")
}
```

**Indexes:**
```javascript
// Unique mapping per teacher-subject-class-section-exam
{ "school_id": 1, "session": 1, "exam_type": 1, "class": 1, "section": 1, "subject_key": 1 }, { unique: true }

// Find all subjects for a teacher
{ "teacher_id": 1, "session": 1 }

// Find teacher for a class/subject
{ "school_id": 1, "session": 1, "class": 1, "section": 1, "subject_key": 1 }
```

**Notes:**
- Replaces MySQL `subject_teacher_mapping` table
- Uses `subject_key` instead of `subject_id` for consistency with other collections
- `from_school` kept for multi-school teacher sharing scenarios

---

## Final Collection Summary

| Collection | Purpose |
|------------|---------|
| `exam_results` | Student marks |
| `exam_results_history` | Audit snapshots |
| `exam_blueprints` | Exam structure |
| `master_subjects` | Subject registry |
| `master_subject_components` | Component registry |
| `master_exam_types` | Exam type registry |
| `grade_systems` | Grading scales |
| `workflow_status` | Submission tracking |
| `teacher_subject_mapping` | Teacher-subject assignments |

---

## Notes

- All collections use MongoDB `timestamps: true` option for automatic `createdAt`/`updatedAt`
- All identifiers use `key` (string) instead of numeric IDs
- Keys are lowercase, snake_case format