# Fourth Subject Management System

## Overview
This document describes the Fourth Subject (Optional Subject) Management System for higher classes (Grade 9 to 12). Students who fail in their designated fourth subject will not be considered as having failed overall.

---

## Table of Contents
1. [System Architecture](#system-architecture)
2. [Database Structure](#database-structure)
3. [Installation Steps](#installation-steps)
4. [User Interface](#user-interface)
5. [API Endpoints](#api-endpoints)
6. [Integration with Results](#integration-with-results)
7. [Usage Guide](#usage-guide)
8. [Testing](#testing)

---

## System Architecture

### Components Created

1. **Database Schema**
   - File: `database/add_fourth_subject_column.sql`
   - Creates/modifies `student_subjects` table
   - Adds `is_fourth_subject` column

2. **Management Interface**
   - File: `students/manage_fourth_subject.php`
   - Full-featured UI for managing fourth subjects
   - Individual and bulk operations

3. **API Endpoint**
   - File: `api/check_fourth_subject.php`
   - RESTful API for checking fourth subject status
   - Used by other modules

---

## Database Structure

### Table: `student_subjects`

```sql
CREATE TABLE student_subjects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    subject_id INT NOT NULL,
    is_fourth_subject TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
    UNIQUE KEY unique_student_subject (student_id, subject_id)
);
```

### Key Columns

- **is_fourth_subject**: `0` = Regular subject, `1` = Fourth/Optional subject
- **Constraint**: Each student can have only ONE subject marked as fourth subject
- **Index**: Added for optimized queries on `(student_id, is_fourth_subject)`

---

## Installation Steps

### Step 1: Run Database Migration

```bash
# Navigate to your MySQL command line or phpMyAdmin
mysql -u root -p school_management < database/add_fourth_subject_column.sql
```

Or through phpMyAdmin:
1. Open phpMyAdmin
2. Select `school_management` database
3. Go to SQL tab
4. Paste contents of `add_fourth_subject_column.sql`
5. Click "Go"

### Step 2: Verify Installation

```sql
-- Check if table exists
SHOW TABLES LIKE 'student_subjects';

-- Check if column exists
DESCRIBE student_subjects;

-- Should show is_fourth_subject column
```

### Step 3: Access Management Page

Navigate to:
```
http://localhost/bidda/students/manage_fourth_subject.php
```

---

## User Interface

### Main Features

#### 1. Dashboard Statistics
- **Total Students**: Count of filtered students
- **Fourth Subject Set**: Students with assigned fourth subject
- **No Fourth Subject**: Students without fourth subject
- **Bulk Action Button**: Quick access to bulk operations

#### 2. Filter Section
- **Class Filter**: Filter by class
- **Group Filter**: Filter by group (Science, Arts, Commerce)
- **Search**: Search by name, ID, or roll number
- **Reset Button**: Clear all filters

#### 3. Student Cards
Each card displays:
- Student name, ID, and roll number
- Class and group information
- All enrolled subjects with visual badges
- Fourth subject highlighted with star icon
- Quick action dropdown to set/change fourth subject
- Remove button for existing fourth subject

#### 4. Visual Indicators
- **Purple gradient badge**: Fourth subject
- **Gray badge**: Regular subject
- **Green left border**: Student has fourth subject set
- **Pulse animation**: Fourth subject badge animates

#### 5. Bulk Operations Modal
- Select class
- Select group (optional)
- Select subject to mark as fourth subject
- Applies to all students in selected class/group

---

## API Endpoints

### Check Fourth Subject Status

**Endpoint**: `/api/check_fourth_subject.php`

**Method**: GET

**Parameters**:
- `student_id` (required): Student's database ID
- `subject_id` (required): Subject's database ID

**Example Request**:
```
GET /api/check_fourth_subject.php?student_id=123&subject_id=456
```

**Example Response**:
```json
{
    "success": true,
    "is_fourth_subject": true,
    "subject_info": {
        "name": "পদার্থবিজ্ঞান",
        "code": "PHY"
    },
    "student_info": {
        "name": "আবদুল করিম",
        "roll": "HSC2024001"
    },
    "message": "This is a fourth/optional subject"
}
```

**Error Response**:
```json
{
    "success": false,
    "is_fourth_subject": false,
    "message": "No relation found between student and subject"
}
```

---

## Integration with Results

### Modify Result Calculation Logic

When calculating overall results, the system must check if a failed subject is the fourth subject.

#### Example Integration Code:

```php
// In your result calculation function
function calculateOverallResult($student_id, $results) {
    $failed_in_regular_subject = false;
    
    foreach ($results as $result) {
        if ($result['grade'] == 'F') {
            // Check if this is the fourth subject
            $stmt = $pdo->prepare("
                SELECT is_fourth_subject 
                FROM student_subjects 
                WHERE student_id = ? AND subject_id = ?
            ");
            $stmt->execute([$student_id, $result['subject_id']]);
            $relation = $stmt->fetch();
            
            // Only mark as failed if it's NOT the fourth subject
            if (!$relation || !$relation['is_fourth_subject']) {
                $failed_in_regular_subject = true;
                break;
            }
        }
    }
    
    return $failed_in_regular_subject ? 'FAIL' : 'PASS';
}
```

### Update Tabulation Sheet

Add this check in `reports/tabulation_sheet.php`:

```php
// When checking if student failed
foreach ($subjects as $subject) {
    if (isset($results_matrix[$student['id']][$subject['id']])) {
        $result = $results_matrix[$student['id']][$subject['id']];
        
        if ($result['grade'] == 'F') {
            // Check if fourth subject
            $stmt = $pdo->prepare("
                SELECT is_fourth_subject 
                FROM student_subjects 
                WHERE student_id = ? AND subject_id = ?
            ");
            $stmt->execute([$student['id'], $subject['id']]);
            $is_fourth = $stmt->fetch();
            
            // Only count as failure if NOT fourth subject
            if (!$is_fourth || !$is_fourth['is_fourth_subject']) {
                $student_failed = true;
            }
        }
    }
}
```

### Update Marksheet

Add this check in `reports/marksheet.php`:

```php
// Calculate overall grade considering fourth subject
$failed_in_required_subjects = false;

foreach ($results as $result) {
    if ($result['grade'] == 'F') {
        // Check if this is fourth subject
        $stmt = $pdo->prepare("
            SELECT is_fourth_subject 
            FROM student_subjects 
            WHERE student_id = ? AND subject_id = ?
        ");
        $stmt->execute([$student_id, $result['subject_id']]);
        $is_fourth = $stmt->fetch();
        
        if (!$is_fourth || !$is_fourth['is_fourth_subject']) {
            $failed_in_required_subjects = true;
        }
    }
}

$overall_grade = $failed_in_required_subjects ? 'F' : calculateGrade($percentage);
```

---

## Usage Guide

### For Teachers/Administrators

#### Setting Fourth Subject for Individual Student

1. **Navigate** to Fourth Subject Management page
2. **Filter** by class and group if needed
3. **Locate** the student card
4. **Select** the subject from dropdown
5. **Click** "সংরক্ষণ" (Save) button
6. **Confirmation** message will appear

#### Bulk Setting for Entire Class

1. **Click** "একসাথে নির্ধারণ করুন" button
2. **Select** class from dropdown
3. **Select** group (optional)
4. **Select** subject to mark as fourth subject
5. **Click** "সংরক্ষণ করুন" (Save)
6. **Confirm** the action in popup
7. **Success** message shows number of students updated

#### Removing Fourth Subject

1. **Locate** student with fourth subject (green border)
2. **Click** "চতুর্থ বিষয় সরান" button
3. **Confirm** removal
4. Subject returns to regular status

### For Developers

#### Checking Fourth Subject in Code

```php
// Quick check function
function isF ourthSubject($pdo, $student_id, $subject_id) {
    $stmt = $pdo->prepare("
        SELECT is_fourth_subject 
        FROM student_subjects 
        WHERE student_id = ? AND subject_id = ?
    ");
    $stmt->execute([$student_id, $subject_id]);
    $result = $stmt->fetch();
    
    return $result && $result['is_fourth_subject'] == 1;
}

// Usage
if (isF ourthSubject($pdo, 123, 456)) {
    echo "This is an optional subject - failure won't affect overall result";
}
```

#### Getting Fourth Subject for Student

```php
function getFourthSubject($pdo, $student_id) {
    $stmt = $pdo->prepare("
        SELECT s.* 
        FROM subjects s
        JOIN student_subjects ss ON s.id = ss.subject_id
        WHERE ss.student_id = ? AND ss.is_fourth_subject = 1
        LIMIT 1
    ");
    $stmt->execute([$student_id]);
    return $stmt->fetch();
}
```

---

## Testing

### Test Cases

#### Test Case 1: Set Fourth Subject
**Steps**:
1. Login as admin
2. Navigate to Fourth Subject Management
3. Select a student
4. Choose a subject from dropdown
5. Click Save

**Expected**:
- Success message appears
- Subject badge turns purple with star
- Card border turns green

#### Test Case 2: Bulk Set
**Steps**:
1. Click bulk action button
2. Select class "11" and group "Science"
3. Select subject "Physics"
4. Confirm

**Expected**:
- All Science students in Class 11 have Physics as fourth subject
- Success message shows count

#### Test Case 3: Result Calculation
**Steps**:
1. Set subject as fourth subject
2. Enter failing grade for that subject
3. View marksheet

**Expected**:
- Student should not be marked as overall FAIL
- Other subjects' failures still cause FAIL

#### Test Case 4: API Endpoint
**Test**:
```bash
curl "http://localhost/bidda/api/check_fourth_subject.php?student_id=1&subject_id=5"
```

**Expected**:
```json
{
    "success": true,
    "is_fourth_subject": true,
    "message": "This is a fourth/optional subject"
}
```

### SQL Testing Queries

```sql
-- Check fourth subjects by class
SELECT 
    s.first_name,
    s.last_name,
    c.name as class_name,
    sub.name as fourth_subject
FROM students s
JOIN classes c ON s.class_id = c.id
LEFT JOIN student_subjects ss ON s.id = ss.student_id AND ss.is_fourth_subject = 1
LEFT JOIN subjects sub ON ss.subject_id = sub.id
WHERE c.name LIKE '11%'
ORDER BY s.first_name;

-- Count students with fourth subject
SELECT 
    c.name as class_name,
    COUNT(DISTINCT ss.student_id) as students_with_fourth_subject
FROM classes c
LEFT JOIN students s ON c.id = s.class_id
LEFT JOIN student_subjects ss ON s.id = ss.student_id AND ss.is_fourth_subject = 1
GROUP BY c.id;

-- Find students with multiple fourth subjects (should be 0)
SELECT 
    student_id,
    COUNT(*) as fourth_subject_count
FROM student_subjects
WHERE is_fourth_subject = 1
GROUP BY student_id
HAVING COUNT(*) > 1;
```

---

## Key Features

✅ **Visual Management**: Card-based interface with color coding  
✅ **Bulk Operations**: Set fourth subject for entire class at once  
✅ **Smart Filtering**: Filter by class, group, or search  
✅ **Real-time Updates**: Immediate visual feedback  
✅ **API Support**: RESTful API for integrations  
✅ **Mobile Responsive**: Works on all devices  
✅ **Bilingual**: Bengali and English support  
✅ **Database Integrity**: Foreign key constraints and unique indexes  
✅ **Transaction Safety**: Uses database transactions  
✅ **Error Handling**: Comprehensive error messages  

---

## Best Practices

### For Administrators

1. **Set fourth subjects at the beginning of academic year**
2. **Review periodically** to ensure accuracy
3. **Use bulk operations** for efficiency
4. **Always confirm** before bulk operations
5. **Test with one student** before bulk update

### For Developers

1. **Always check fourth subject** before failing a student
2. **Use API endpoint** for consistent checks
3. **Handle null cases** properly
4. **Log fourth subject changes** for audit trail
5. **Test result calculations** thoroughly

---

## Troubleshooting

### Issue: Column doesn't exist
**Solution**: Run the SQL migration script again

### Issue: Can't set fourth subject
**Solution**: Check if student_subjects table has data for that student

### Issue: Bulk operation affects wrong students
**Solution**: Always verify filters before bulk operations

### Issue: API returns 404
**Solution**: Ensure .htaccess allows API directory or use full path

---

## Future Enhancements

1. **Audit Log**: Track who changed fourth subjects when
2. **History**: View history of fourth subject changes
3. **Validation Rules**: Automatic validation based on class level
4. **Import/Export**: Bulk import from Excel
5. **Reports**: Generate reports on fourth subject statistics
6. **Notifications**: Email notifications on changes
7. **Permissions**: Role-based access control
8. **Subject Restrictions**: Limit which subjects can be fourth subjects

---

## URLs

- **Management Page**: `http://localhost/bidda/students/manage_fourth_subject.php`
- **API Endpoint**: `http://localhost/bidda/api/check_fourth_subject.php`
- **Student List**: `http://localhost/bidda/students/list.php`

---

## Support

For questions or issues:
1. Check this documentation first
2. Review SQL migration status
3. Check browser console for JavaScript errors
4. Review PHP error logs
5. Test API endpoint directly

---

**Version**: 1.0  
**Date**: October 5, 2025  
**Author**: System Administrator  
**Status**: Production Ready
