# Group Data Flow Documentation

## Overview
This document describes the complete data flow for group (batch) management, including student enrollments, teacher assignments, test series assignments, and activity tracking.

## Database Tables

### 1. `groups` - Main Group/Batch Table

**Purpose:** Stores information about groups (batches) within schools.

**Schema:**
```
- id (primary key)
- name (string) - Group/batch name
- school_id (foreign key -> schools.id) - Which school this group belongs to
- status (boolean) - Active/inactive status
- created_at, updated_at
```

**Indexes:**
- `idx_groups_school_id` on school_id

**Relationships:**
- BelongsTo: School
- HasMany: GroupUser (junction records)
- BelongsToMany: User (students via group_users where type=2)
- BelongsToMany: User (teachers via group_users where type=1)
- BelongsToMany: TestSeries (via group_test_series)
- HasMany: GroupActivityLog

---

### 2. `group_users` - Student & Teacher Junction Table

**Purpose:** Links users (students/teachers) to groups. This is the primary junction table for group membership.

**Schema:**
```
- id (primary key)
- group_id (foreign key -> groups.id)
- user_id (foreign key -> users.id)
- type (integer) - 1=teacher, 2=student
- enrolled_at (timestamp, non-null) - When user was added to group
- status (enum: active, inactive, withdrawn) - Current enrollment status
- created_at, updated_at
```

**Indexes:**
- `idx_group_users_group_id` on group_id
- `idx_group_users_user_id` on user_id
- `idx_group_users_type` on type
- `idx_group_users_status` on status
- `idx_group_users_unique` unique constraint on (group_id, user_id, type)

**Constraints:**
- Unique constraint ensures a user can only be enrolled once per group with the same type
- Foreign keys (commented out until data validation complete):
  - group_id -> groups.id (CASCADE)
  - user_id -> users.id (CASCADE)

**Relationships:**
- BelongsTo: Group
- BelongsTo: User

**Model Constants:**
```php
TYPE_TEACHER = 1
TYPE_STUDENT = 2

STATUS_ACTIVE = 'active'
STATUS_INACTIVE = 'inactive'
STATUS_WITHDRAWN = 'withdrawn'
```

**Key Methods:**
- `scopeStudents()` - Filter only students
- `scopeTeachers()` - Filter only teachers
- `scopeActive()` - Filter only active enrollments
- `isStudent()`, `isTeacher()`, `isActive()` - Check record type/status
- `activate()`, `deactivate()`, `withdraw()` - Change enrollment status

---

### 3. `group_test_series` - Test Assignment Junction Table

**Purpose:** Assigns test series to groups, making tests available to all students in that group.

**Schema:**
```
- id (primary key)
- group_id (foreign key -> groups.id)
- test_series_id (foreign key -> test_series.id)
- created_at, updated_at
```

**Indexes:**
- `idx_group_test_series_group_id` on group_id
- `idx_group_test_series_test_series_id` on test_series_id
- `idx_group_test_series_unique` unique constraint on (group_id, test_series_id)

**Constraints:**
- Unique constraint prevents assigning same test series to a group multiple times
- Foreign keys (commented out until data validation complete):
  - group_id -> groups.id (CASCADE)
  - test_series_id -> test_series.id (CASCADE)

**Relationships:**
- BelongsTo: Group
- BelongsTo: TestSeries

---

### 4. `group_activity_log` - Activity Audit Trail

**Purpose:** Immutable audit log of all group-related activities for compliance and tracking.

**Schema:**
```
- id (primary key)
- group_id (foreign key -> groups.id)
- user_id (foreign key -> users.id) - User affected by action
- student_id (nullable, foreign key -> users.id) - For student-specific actions
- action (string) - Type of action performed
- performed_by (foreign key -> users.id) - Who performed the action
- metadata (JSON) - Additional action details
- created_at (no updated_at - append-only)
```

**Indexes:**
- `idx_group_activity_log_group_id` on group_id
- `idx_group_activity_log_user_id` on user_id
- `idx_group_activity_log_student_id` on student_id
- `idx_group_activity_log_performed_by` on performed_by
- `idx_group_activity_log_action` on action
- `idx_group_activity_log_created_at` on created_at

**Action Types:**
```php
ACTION_STUDENT_ADDED = 'student_added'
ACTION_STUDENT_REMOVED = 'student_removed'
ACTION_STUDENT_UPDATED = 'student_updated'
ACTION_TEACHER_ASSIGNED = 'teacher_assigned'
ACTION_TEACHER_REMOVED = 'teacher_removed'
ACTION_TEST_SERIES_ADDED = 'test_series_added'
ACTION_TEST_SERIES_REMOVED = 'test_series_removed'
ACTION_GROUP_CREATED = 'group_created'
ACTION_GROUP_UPDATED = 'group_updated'
```

**Foreign Keys (commented out):**
- group_id -> groups.id (CASCADE)
- user_id -> users.id (CASCADE)
- student_id -> users.id (SET NULL)
- performed_by -> users.id (CASCADE)

**Key Methods:**
```php
// Static logging methods
GroupActivityLog::logStudentAdded($groupId, $studentId, $performedBy, $metadata)
GroupActivityLog::logStudentRemoved($groupId, $studentId, $performedBy, $metadata)
GroupActivityLog::logTeacherAssigned($groupId, $teacherId, $performedBy, $metadata)
GroupActivityLog::logTeacherRemoved($groupId, $teacherId, $performedBy, $metadata)
```

---

## Data Flow Scenarios

### Scenario 1: Adding a Student to a Group

**Steps:**
1. Create record in `group_users`:
   ```php
   GroupUser::create([
       'group_id' => $groupId,
       'user_id' => $studentId,
       'type' => GroupUser::TYPE_STUDENT,
       'enrolled_at' => now(),
       'status' => GroupUser::STATUS_ACTIVE
   ]);
   ```

2. Log activity in `group_activity_log`:
   ```php
   GroupActivityLog::logStudentAdded(
       groupId: $groupId,
       studentId: $studentId,
       performedBy: $teacherId,
       metadata: ['source' => 'manual_enrollment']
   );
   ```

3. Student now has access to:
   - All test series assigned to this group (via `group_test_series`)
   - Group resources and materials
   - Group-specific content

**Query to get student's groups:**
```php
$user->groups() // Returns all groups where user is enrolled
$user->groupEnrollments()->students() // Returns only student enrollments
```

---

### Scenario 2: Assigning a Test Series to a Group

**Steps:**
1. Create record in `group_test_series`:
   ```php
   GroupTestSeries::create([
       'group_id' => $groupId,
       'test_series_id' => $testSeriesId
   ]);
   ```

2. Log activity:
   ```php
   GroupActivityLog::create([
       'group_id' => $groupId,
       'user_id' => $performedBy,
       'action' => GroupActivityLog::ACTION_TEST_SERIES_ADDED,
       'performed_by' => $performedBy,
       'metadata' => ['test_series_id' => $testSeriesId]
   ]);
   ```

3. All active students in the group now have access to this test series.

**Query to get group's test series:**
```php
$group->testSeries() // Returns all assigned test series
$group->activeStudents() // Returns students who can access these tests
```

---

### Scenario 3: Removing a Student from a Group

**Options:**
1. **Soft removal** - Set status to inactive/withdrawn (preserves history):
   ```php
   $groupUser->withdraw(); // Sets status to 'withdrawn'
   // or
   $groupUser->deactivate(); // Sets status to 'inactive'
   ```

2. **Hard removal** - Delete record (loses enrollment history):
   ```php
   $groupUser->delete();
   ```

**Both should log activity:**
```php
GroupActivityLog::logStudentRemoved(
    groupId: $groupId,
    studentId: $studentId,
    performedBy: $teacherId,
    metadata: ['reason' => 'transferred']
);
```

**Recommendation:** Use soft removal (status change) to preserve audit trail.

---

### Scenario 4: Viewing Group Activity History

**Query recent activities:**
```php
// Last 7 days of student-related activities
GroupActivityLog::byGroup($groupId)
    ->studentActions()
    ->recent(7)
    ->orderBy('created_at', 'desc')
    ->get();

// All activities performed by a specific teacher
GroupActivityLog::byGroup($groupId)
    ->byPerformer($teacherId)
    ->get();

// Timeline of a specific student's enrollment history
GroupActivityLog::byStudent($studentId)
    ->whereIn('action', [
        GroupActivityLog::ACTION_STUDENT_ADDED,
        GroupActivityLog::ACTION_STUDENT_REMOVED,
        GroupActivityLog::ACTION_STUDENT_UPDATED
    ])
    ->orderBy('created_at', 'desc')
    ->get();
```

---

## Query Patterns

### Get all students in a group
```php
// Using relationship
$students = $group->students; // Returns User models with pivot data
$activeStudents = $group->activeStudents; // Only active enrollments

// Direct query
$students = GroupUser::where('group_id', $groupId)
    ->where('type', GroupUser::TYPE_STUDENT)
    ->where('status', GroupUser::STATUS_ACTIVE)
    ->with('user')
    ->get();
```

### Get all groups a student is enrolled in
```php
// Using relationship
$groups = $student->groups; // Returns Group models

// Direct query
$groups = GroupUser::where('user_id', $studentId)
    ->where('type', GroupUser::TYPE_STUDENT)
    ->where('status', GroupUser::STATUS_ACTIVE)
    ->with('group')
    ->get();
```

### Get all test series available to a student
```php
// Get student's group IDs
$groupIds = GroupUser::where('user_id', $studentId)
    ->where('type', GroupUser::TYPE_STUDENT)
    ->where('status', GroupUser::STATUS_ACTIVE)
    ->pluck('group_id');

// Get test series from those groups
$testSeries = GroupTestSeries::whereIn('group_id', $groupIds)
    ->with('testSeries')
    ->get()
    ->pluck('testSeries')
    ->unique('id');
```

### Check if a student can access a test series
```php
$hasAccess = GroupUser::where('user_id', $studentId)
    ->where('type', GroupUser::TYPE_STUDENT)
    ->where('status', GroupUser::STATUS_ACTIVE)
    ->whereIn('group_id', function($query) use ($testSeriesId) {
        $query->select('group_id')
              ->from('group_test_series')
              ->where('test_series_id', $testSeriesId);
    })
    ->exists();
```

---

## Data Integrity & Validation

### Current Status
✅ **Tables exist and are properly indexed**
✅ **group_users is the correct student-group junction table**
✅ **enrolled_at timestamps are populated**
✅ **status column exists with proper enum values**
⚠️ **Foreign keys are disabled** pending data validation

### Before Enabling Foreign Keys

Run the validation command:
```bash
# Check for issues
php artisan groups:validate-relationships --verbose

# Automatically fix orphaned records
php artisan groups:validate-relationships --fix

# After all issues resolved, enable foreign keys in migrations
```

### What the Validation Command Checks

1. **group_users integrity:**
   - All group_id values exist in groups table
   - All user_id values exist in users table
   - No NULL enrolled_at values
   - No duplicate enrollments

2. **groups integrity:**
   - All school_id values exist in schools table (if table exists)

3. **group_test_series integrity:**
   - All group_id values exist in groups table
   - All test_series_id values exist in test_series/testseries table

4. **group_activity_log integrity:**
   - All group_id values exist in groups table
   - All user_id values exist in users table
   - All performed_by values exist in users table

5. **Duplicate detection:**
   - No duplicate (group_id, user_id, type) in group_users
   - No duplicate (group_id, test_series_id) in group_test_series

---

## Migration Status

### Completed Migrations
- ✅ `add_indexes_and_foreign_keys_to_groups_table` - Added school_id index
- ✅ `add_indexes_and_foreign_keys_to_group_users_table` - Added indexes and unique constraint
- ✅ `add_missing_columns_to_group_users_table` - Added enrolled_at and status columns
- ✅ `add_indexes_and_foreign_keys_to_group_test_series_table` - Added indexes and unique constraint
- ✅ `create_group_activity_log_table` - Created audit log table
- ✅ `migrate_group_log_to_group_activity_log` - Migrated old log data (if applicable)

### Next Steps
1. Run `php artisan groups:validate-relationships` to check data integrity
2. Fix any orphaned records with `--fix` option
3. Uncomment foreign key constraints in migrations:
   - `2025_11_12_100001_add_indexes_and_foreign_keys_to_groups_table.php`
   - `2025_11_12_100002_add_indexes_and_foreign_keys_to_group_users_table.php`
   - `2025_11_12_100004_add_indexes_and_foreign_keys_to_group_test_series_table.php`
   - `2025_11_12_100005_create_group_activity_log_table.php`
4. Run migrations again to enable foreign keys: `php artisan migrate`

---

## Best Practices

### DO:
- ✅ Always log activities to `group_activity_log` when modifying group memberships
- ✅ Use soft removal (status change) instead of hard deletion when possible
- ✅ Include metadata in activity logs for better audit trails
- ✅ Use Eloquent relationships for queries to maintain consistency
- ✅ Check for unique constraint violations before inserting

### DON'T:
- ❌ Don't hard delete group_users records unless necessary (loses history)
- ❌ Don't bypass activity logging when making changes
- ❌ Don't create duplicate enrollments (unique constraint will prevent this)
- ❌ Don't modify `group_activity_log` records (append-only table)
- ❌ Don't enable foreign keys until data validation passes

---

## Troubleshooting

### Issue: Cannot add student to group
**Check:**
1. Does the group exist? `Group::find($groupId)`
2. Does the user exist? `User::find($userId)`
3. Is there already an enrollment? Check unique constraint
4. Is the user's role correct? Students should have appropriate role

### Issue: Student can't access test series
**Check:**
1. Is student active in group? `status = 'active'`
2. Is test series assigned to group? Check `group_test_series` table
3. Is group active? `groups.status = 1`

### Issue: Validation command reports orphaned records
**Options:**
1. **Automatic fix:** Run with `--fix` flag to delete orphans
2. **Manual fix:** Create missing parent records if data is important
3. **Data correction:** Update invalid foreign key values to valid ones

---

## Performance Optimization

### Indexes in Place
All critical columns are indexed for optimal query performance:
- Foreign key columns (group_id, user_id, etc.)
- Status and type columns for filtering
- created_at for date-based queries

### Query Optimization Tips
1. Use eager loading to avoid N+1 queries:
   ```php
   $groups = Group::with(['students', 'testSeries'])->get();
   ```

2. Use joins for complex filters:
   ```php
   $activeStudentGroups = Group::join('group_users', 'groups.id', '=', 'group_users.group_id')
       ->where('group_users.type', GroupUser::TYPE_STUDENT)
       ->where('group_users.status', GroupUser::STATUS_ACTIVE)
       ->select('groups.*')
       ->distinct()
       ->get();
   ```

3. Cache frequently accessed data:
   ```php
   Cache::remember("group.{$groupId}.students", 3600, function() use ($groupId) {
       return Group::find($groupId)->activeStudents;
   });
   ```

---

## Summary

The group data structure is **properly normalized and optimized**:
- `group_users` is the correct junction table for student-teacher-group relationships
- All necessary indexes are in place
- Activity logging provides complete audit trail
- Foreign keys are ready to enable after data validation
- Models have proper relationships and helper methods

**No structural changes needed** - the system is well-designed and ready for use after validation.