# Workflow Schema Migration (P2-01)

## Overview

Migration005WorkflowSchema creates the database tables required for the enterprise workflow system, enabling state tracking and translator assignments for translation jobs.

## Version

- **Migration Version**: 1.5.0
- **Migration File**: `Migration005WorkflowSchema.php`

## Tables Created

### 1. `wp_mpz_workflow_states`

Tracks state transitions for translations throughout the workflow lifecycle.

#### Schema

| Column | Type | Description |
|--------|------|-------------|
| `id` | BIGINT(20) UNSIGNED | Primary key |
| `translation_id` | BIGINT(20) UNSIGNED | Reference to translations table |
| `state` | VARCHAR(50) | Current workflow state |
| `previous_state` | VARCHAR(50) | Previous workflow state |
| `changed_by` | BIGINT(20) UNSIGNED | User who changed state |
| `changed_at` | DATETIME | Timestamp of state change |
| `notes` | TEXT | Optional notes about state change |
| `metadata` | LONGTEXT | JSON metadata for extensibility |

#### Indexes

- `PRIMARY KEY (id)`
- `INDEX idx_translation_state (translation_id, state)` - For querying states by translation
- `INDEX idx_changed_at (changed_at)` - For time-based queries
- `INDEX idx_state (state)` - For filtering by state
- `INDEX idx_changed_by (changed_by)` - For user activity tracking
- `INDEX idx_translation_time (translation_id, changed_at)` - For state history queries

#### Workflow States

Typical state values include:
- `draft` - Initial state
- `pending_review` - Awaiting review
- `in_progress` - Being worked on
- `completed` - Translation complete
- `approved` - Reviewed and approved
- `rejected` - Needs rework

### 2. `wp_mpz_workflow_assignments`

Tracks translator assignments with time tracking and priority management.

#### Schema

| Column | Type | Description |
|--------|------|-------------|
| `id` | BIGINT(20) UNSIGNED | Primary key |
| `translation_id` | BIGINT(20) UNSIGNED | Reference to translations table |
| `assigned_to` | BIGINT(20) UNSIGNED | User assigned to translation |
| `assigned_by` | BIGINT(20) UNSIGNED | User who made assignment |
| `assigned_at` | DATETIME | Timestamp of assignment |
| `started_at` | DATETIME | When work started |
| `completed_at` | DATETIME | When work completed |
| `deadline` | DATETIME | Due date for completion |
| `priority` | ENUM | Priority level: `low`, `normal`, `high` |
| `status` | ENUM | Status: `pending`, `in_progress`, `completed`, `cancelled` |
| `estimated_time` | INT | Estimated time in minutes |
| `actual_time` | INT | Actual time spent in minutes |
| `notes` | TEXT | Assignment notes |

#### Indexes

- `PRIMARY KEY (id)`
- `INDEX idx_assigned_to (assigned_to, status)` - For user task lists
- `INDEX idx_deadline (deadline)` - For deadline tracking
- `INDEX idx_translation (translation_id)` - For translation lookups
- `INDEX idx_status (status)` - For status filtering
- `INDEX idx_assigned_at (assigned_at)` - For time-based queries
- `INDEX idx_completion_time (completed_at)` - For completion tracking
- `INDEX idx_assignment_status (translation_id, status, assigned_to)` - Covering index for common queries

## Usage Examples

### Recording a State Change

```php
global $wpdb;

$wpdb->insert(
    $wpdb->prefix . 'mpz_workflow_states',
    [
        'translation_id' => 123,
        'state' => 'in_progress',
        'previous_state' => 'pending_review',
        'changed_by' => get_current_user_id(),
        'changed_at' => current_time('mysql'),
        'notes' => 'Started translation work',
        'metadata' => wp_json_encode(['reviewer_id' => 456])
    ],
    ['%d', '%s', '%s', '%d', '%s', '%s', '%s']
);
```

### Creating an Assignment

```php
global $wpdb;

$wpdb->insert(
    $wpdb->prefix . 'mpz_workflow_assignments',
    [
        'translation_id' => 123,
        'assigned_to' => 789,
        'assigned_by' => get_current_user_id(),
        'assigned_at' => current_time('mysql'),
        'deadline' => date('Y-m-d H:i:s', strtotime('+3 days')),
        'priority' => 'high',
        'status' => 'pending',
        'estimated_time' => 120,
        'notes' => 'Urgent client request'
    ],
    ['%d', '%d', '%d', '%s', '%s', '%s', '%s', '%d', '%s']
);
```

### Querying Active Assignments

```php
global $wpdb;

$assignments = $wpdb->get_results($wpdb->prepare(
    "SELECT * FROM {$wpdb->prefix}mpz_workflow_assignments
    WHERE assigned_to = %d
    AND status IN ('pending', 'in_progress')
    ORDER BY deadline ASC",
    get_current_user_id()
));
```

### Tracking State History

```php
global $wpdb;

$history = $wpdb->get_results($wpdb->prepare(
    "SELECT * FROM {$wpdb->prefix}mpz_workflow_states
    WHERE translation_id = %d
    ORDER BY changed_at DESC",
    123
));
```

## Running the Migration

### Automatic (Plugin Activation)

The migration runs automatically when the plugin is activated or when the migration system detects pending migrations.

### Manual (WP-CLI)

```bash
wp eval "
\$migrations = new \MultilingualPressZone\Core\Migrations();
\$results = \$migrations->migrate();
print_r(\$results);
"
```

### Manual (PHP)

```php
$migrations = new \MultilingualPressZone\Core\Migrations();
$results = $migrations->migrate();
```

## Rollback

To rollback this migration:

```php
$migrations = new \MultilingualPressZone\Core\Migrations();
$result = $migrations->rollback();
```

This will drop both `workflow_states` and `workflow_assignments` tables.

## Database Size Estimates

### Initial Size

- Empty tables: ~16 KB each (schema only)

### Growth Estimates

#### workflow_states
- Average row size: ~200 bytes
- 1000 translations with 5 state changes each: ~1 MB
- 10,000 translations: ~10 MB

#### workflow_assignments
- Average row size: ~150 bytes
- 1000 active assignments: ~150 KB
- 10,000 historical assignments: ~1.5 MB

## Performance Considerations

### Indexing Strategy

1. **Covering indexes** minimize table lookups for common queries
2. **Composite indexes** optimize multi-column WHERE clauses
3. **Time-based indexes** support efficient date range queries

### Query Optimization

- Use prepared statements to leverage query cache
- Limit result sets with `LIMIT` clauses
- Index all foreign key columns
- Consider partitioning for very large datasets (>1M rows)

### Maintenance

- Run `OPTIMIZE TABLE` monthly for tables with frequent updates
- Monitor index usage with `EXPLAIN` queries
- Archive old state history after 1 year

## Integration Points

### Future Features

This schema supports:

1. **Workflow Engine** (P2-02) - State machine implementation
2. **Assignment System** (P2-03) - Translator workload management
3. **Analytics** (P2-04) - Time tracking and performance metrics
4. **Notifications** (P2-05) - State change alerts
5. **Audit Trail** (P2-06) - Complete workflow history

### Related Tables

- `wp_mpz_translations` - Parent translation records
- `wp_users` - User references for assignments and state changes
- `wp_mpz_audit_logs` (Migration006) - Detailed audit trail

## Security Notes

### Data Protection

- User IDs validated against WordPress user table
- SQL injection prevented via prepared statements
- Metadata stored as JSON with validation

### Access Control

- Check user capabilities before recording state changes
- Verify assignment permissions before creating assignments
- Audit all state transitions in audit_logs table

## Testing

### Verification Script

```bash
php /path/to/plugin/tmp/verify-workflow-schema.php
```

### Manual Verification

```sql
-- Check tables exist
SHOW TABLES LIKE 'wp_mpz_workflow%';

-- Check indexes on workflow_states
SHOW INDEX FROM wp_mpz_workflow_states;

-- Check indexes on workflow_assignments
SHOW INDEX FROM wp_mpz_workflow_assignments;

-- Check column structure
DESCRIBE wp_mpz_workflow_states;
DESCRIBE wp_mpz_workflow_assignments;
```

## Troubleshooting

### Migration Failed

Check WordPress error logs:
```bash
tail -f /var/log/wordpress/debug.log | grep "MPZ Migration"
```

### Table Already Exists

Migration uses `CREATE TABLE IF NOT EXISTS`, so it's safe to re-run.

### Foreign Key Issues

This migration does NOT use foreign key constraints to avoid issues with:
- Table drops during development
- WordPress multisite compatibility
- Performance on large tables

Foreign key relationships are enforced at the application level.

## Changelog

### Version 1.5.0 (2026-01-26)

- Initial workflow schema migration
- Created workflow_states table with state tracking
- Created workflow_assignments table with time tracking
- Added comprehensive indexes for performance
- Documented schema and usage patterns