# Agents Table Conversion Summary

## Current Structure Analysis

### Table: `agents`

**Key Columns:**
- `id` - int (PRIMARY KEY)
- `username` - varchar(100) (UNIQUE)
- `email` - varchar(255) (UNIQUE)
- `role` - **enum('agent','super_agent','dealers')** ⚠️ Missing 'store_owner'
- `is_store_owner` - tinyint(1) (DEFAULT 0) ✓ Correct
- `store_id` - int (NULL) ✓ Exists

### Issues Found

1. **Role ENUM Missing 'store_owner'**
   - Current: `enum('agent','super_agent','dealers')`
   - Needed: `enum('agent','super_agent','dealers','store_owner')`

2. **Data Inconsistency**
   - 8 store owners exist (from `stores` table)
   - 0 have `role = 'store_owner'`
   - 8 have `is_store_owner = 1`
   - All need to be updated to `role = 'store_owner'`

## Conversion Queries

### Query 1: Add 'store_owner' to ENUM
```sql
ALTER TABLE agents 
MODIFY COLUMN role ENUM('agent','super_agent','dealers','store_owner') 
DEFAULT 'agent';
```

### Query 2: Update All Store Owners
```sql
UPDATE agents a
INNER JOIN stores s ON s.owner_id = a.id
SET a.role = 'store_owner', 
    a.is_store_owner = 1, 
    a.store_id = s.id
WHERE a.role != 'store_owner' 
   OR a.is_store_owner != 1 
   OR a.store_id != s.id;
```

### Query 3: Fix Agents Who Shouldn't Be Store Owners
```sql
UPDATE agents a
LEFT JOIN stores s ON s.owner_id = a.id
SET a.role = 'agent', 
    a.is_store_owner = 0
WHERE s.id IS NULL 
  AND (a.role = 'store_owner' OR a.is_store_owner = 1);
```

## Execution

Run the conversion script:
```bash
php convert_agents_table.php
```

This script will:
1. Execute all queries in a transaction
2. Rollback if any query fails
3. Verify the conversion
4. Report any remaining issues

## Expected Result

After conversion:
- ✅ Role ENUM includes 'store_owner'
- ✅ All 8 store owners have `role = 'store_owner'`
- ✅ All 8 store owners have `is_store_owner = 1`
- ✅ All 8 store owners have correct `store_id`
- ✅ All agents have `role = 'agent'` and `is_store_owner = 0`

## Multi-Layer Security

After conversion, store owners will be identified by:
1. **Layer 1**: `role = 'store_owner'` (distinct from 'agent')
2. **Layer 2**: `is_store_owner = 1`
3. **Layer 3**: `store_id` matches owned store
4. **Layer 4**: Record exists in `stores` table where `owner_id = agent.id`

