# Month-Based Sequential Task Numbering System

## Overview
This implementation adds a custom task numbering system where task IDs are based on the month and reset to 1 at the start of each new month.

## Task ID Format
- **Format**: `YYYY-MM-N`
- **Example**: `2026-03-1`, `2026-03-2`, `2026-04-1`
- **N**: Sequential number starting from 1 each month

## Implementation Details

### Database Changes
1. **New Column**: `custom_task_id` added to `tasks` table
   - Type: VARCHAR(20)
   - Indexed for performance
   - Nullable (for backward compatibility with existing tasks)

2. **View Update**: `admin_all_tasks` view updated to include `custom_task_id`

### Application Changes
1. **Task Assignment** (`task_add.php`):
   - Automatically generates sequential task numbers when admin assigns tasks
   - Queries the maximum task number for the current month
   - Increments and assigns the next number

2. **Task Display** (`task_assignment.php`):
   - All task lists now show custom task IDs instead of auto-increment IDs
   - Falls back to auto-increment ID if custom ID is not set (for old tasks)

## Installation Instructions

### Step 1: Add custom_task_id Column
Run this SQL in phpMyAdmin or MySQL command line:

```sql
-- File: add_custom_task_id.sql
ALTER TABLE tasks 
ADD COLUMN custom_task_id VARCHAR(20) NULL AFTER task_id,
ADD INDEX idx_custom_task_id (custom_task_id);

-- Update existing tasks with custom IDs
UPDATE tasks t
SET custom_task_id = CONCAT(
    DATE_FORMAT(t.scheduled_date, '%Y-%m'),
    '-',
    (
        SELECT COUNT(*) + 1
        FROM tasks t2
        WHERE DATE_FORMAT(t2.scheduled_date, '%Y-%m') = DATE_FORMAT(t.scheduled_date, '%Y-%m')
        AND t2.task_id < t.task_id
    )
)
WHERE custom_task_id IS NULL;
```

### Step 2: Update admin_all_tasks View
Run this SQL to update the view:

```sql
-- File: update_admin_all_tasks_view.sql
DROP VIEW IF EXISTS admin_all_tasks;

CREATE VIEW admin_all_tasks AS
SELECT 
    t.task_id,
    t.custom_task_id,
    t.request_id,
    t.assigned_staff_id,
    t.assigned_by_admin_id,
    t.scheduled_date,
    t.scheduled_time,
    t.status AS task_status,
    t.created_at AS task_created_at,
    t.updated_at AS task_updated_at,
    COALESCE(staff_user.full_name, 'Unassigned') AS staff_name,
    COALESCE(staff_user.email, '') AS staff_email,
    customer_user.full_name AS customer_name,
    customer_user.email AS customer_email,
    sr.customer_id,
    sr.service_id,
    sr.property_address,
    sr.pest_type,
    sr.severity_level,
    sr.preferred_date,
    sr.preferred_time,
    sr.status AS request_status,
    s.service_name,
    s.description AS service_description,
    p.payment_id,
    p.payment_status,
    p.payment_method,
    p.due_date AS payment_due_date
FROM tasks t
LEFT JOIN service_requests sr ON t.request_id = sr.request_id
LEFT JOIN users customer_user ON sr.customer_id = customer_user.user_id
LEFT JOIN users staff_user ON t.assigned_staff_id = staff_user.user_id
LEFT JOIN services s ON sr.service_id = s.service_id
LEFT JOIN payments p ON sr.request_id = p.request_id;
```

## How It Works

### When Admin Assigns a Task:
1. System extracts the year-month from the scheduled date (e.g., "2026-03")
2. Queries database for the highest task number in that month
3. Increments by 1 to get the next sequential number
4. Generates custom_task_id: `YYYY-MM-N`
5. Stores it in the tasks table

### Monthly Reset:
- Task numbers automatically reset to 1 when a new month begins
- Example:
  - March 2026: `2026-03-1`, `2026-03-2`, `2026-03-3`
  - April 2026: `2026-04-1`, `2026-04-2`, `2026-04-3`

### Display:
- Admin panel shows custom task IDs (e.g., `#2026-03-5`)
- Falls back to auto-increment ID for old tasks without custom IDs

## Benefits
1. **Easy Month Tracking**: Quickly identify which month a task belongs to
2. **Sequential Numbering**: Clear daily/monthly task progression
3. **Automatic Reset**: No manual intervention needed for monthly resets
4. **Backward Compatible**: Old tasks still work with auto-increment IDs

## Files Modified
- `public/admin/task_add.php` - Task assignment logic
- `public/admin/task_assignment.php` - Task display queries and UI
- `database/add_custom_task_id.sql` - Database migration
- `database/update_admin_all_tasks_view.sql` - View update

## Testing
After running the SQL migrations:
1. Go to Admin Panel > Task Assignment
2. Assign a new task for the current month
3. Verify the task shows with format `YYYY-MM-N`
4. Assign another task in the same month
5. Verify the number increments (e.g., `2026-03-1` → `2026-03-2`)
6. Change to next month and assign a task
7. Verify numbering resets to 1 (e.g., `2026-04-1`)
