# Employee Dropdown Fix - Complete Summary

## Problem
The branch and role dropdowns in `employee-add.php` were not loading data properly due to:
1. API endpoints not supporting `length=-1` parameter for fetching all records
2. Form using deprecated `designation_id` instead of `role_id`
3. Database schema missing `role_id` column in employees table

## Solution Overview
Changed the employee form to use the `roles` table instead of `designations` table and fixed all related API endpoints.

## Files Modified

### 1. API Files - Fixed to support `length=-1`

#### `api/branch/read.php`
- Added check for `length != -1` to handle unlimited record fetching
- Fixed pagination logic to skip LIMIT clause when `length=-1`
- Updated error response format to match other APIs

#### `api/role/read.php` (NEW)
- Created new API endpoint for roles table
- Supports `length=-1` for fetching all roles
- Queries the `roles` table with columns: id, name, prefix, is_system

### 2. Employee Add Form - `employee-add.php`
- Changed field label from "Designation (Role)" to "Role"
- Changed field ID from `designation_id` to `role_id`
- Updated JavaScript to load from `api/role/read.php` instead of `api/designation/read.php`
- Updated form field display to show `role.name` instead of `designation.designation`
- Added proper error handling for failed API calls
- Updated validation to check `role_id` instead of `designation_id`
- Updated edit form loading to set `role_id` value correctly

### 3. Employee API Endpoints - Updated to use `role_id`

#### `api/employee/create.php`
- Changed required fields from `designation_id` to `role_id`
- Updated INSERT query to insert into `role_id` column
- Updated bind parameter from `:designation_id` to `:role_id`

#### `api/employee/update.php`
- Changed required fields from `designation_id` to `role_id`
- Updated UPDATE query to update `role_id` column
- Updated bind parameter from `:designation_id` to `:role_id`

#### `api/employee/read.php`
- Updated SQL JOIN from `tbl_designations` to `roles` table
- Changed filter parameter from `designation_id` to `role_id`
- Updated column mapping for ordering
- Updated search filter to use role name from roles table

#### `api/employee/read_single.php`
- Updated SQL JOIN to use `roles` table instead of `tbl_designations`
- Changed to select `r.name as role_name` for consistency

### 4. Database Migration (NEW)

#### `database/migrations/add_role_id_to_employees.sql`
- Adds `role_id` column to `tbl_employees` table
- Adds index on `role_id` for better query performance
- Column placed after `branch_id` for logical ordering

**Important**: This migration must be run on the database to add the `role_id` column:
```sql
ALTER TABLE `tbl_employees` 
ADD COLUMN `role_id` INT(11) DEFAULT NULL COMMENT 'Link to roles table' AFTER `branch_id`;

ALTER TABLE `tbl_employees` 
ADD KEY `idx_role_id` (`role_id`);
```

## Testing Instructions

1. **Run the migration** to add `role_id` column to employees table
2. **Open `employee-add.php`** in browser
3. **Verify dropdowns load**:
   - Branch dropdown should show all branches
   - Role dropdown should show all roles from the `roles` table
4. **Test adding a new employee**:
   - Select a branch
   - Select a role
   - Fill in employee details
   - Submit the form
   - Verify employee is created with correct role_id
5. **Test editing an employee**:
   - Navigate to employee-list.php
   - Click edit on an existing employee
   - Verify branch and role values load correctly
   - Update and verify changes are saved

## API Changes Summary

| Endpoint | Parameter Changed | Old | New |
|----------|------------------|-----|-----|
| `/api/employee/create.php` | Required field | `designation_id` | `role_id` |
| `/api/employee/update.php` | Required field | `designation_id` | `role_id` |
| `/api/employee/read.php` | Filter parameter | `designation_id` | `role_id` |
| `/api/employee/read.php` | JOIN table | `tbl_designations` | `roles` |
| `/api/employee/read_single.php` | JOIN table | `tbl_designations` | `roles` |

## Database Schema Changes

### New Column in `tbl_employees`
```sql
role_id INT(11) DEFAULT NULL COMMENT 'Link to roles table'
```

### Referenced Table: `roles`
- id (int)
- name (varchar)
- prefix (varchar)
- is_system (varchar)

## Notes

- The `roles` table already exists in your database
- All changes are backward compatible with the existing role system
- The old `designation_id` field is no longer used in employee records
- If you have existing employee records with `designation_id`, you may need to manually migrate them to use `role_id` or update the migration script accordingly
