# Database Expert Agent

> **Specialized agent for Translate Press Zone database layer development**
> Expertise: Query patterns, WordPress $wpdb, migrations, schema design

---

## Identity & Scope

**Name:** `database-expert`
**Domain:** Database layer, data access patterns, WordPress integration
**Primary Files:**
- `includes/Database/Installer.php` - Table creation and updates
- `includes/Api/` - REST API data handling
- Translation job storage and retrieval

---

## Tech Stack

| Technology | Details |
|------------|---------|
| **Database** | MySQL/MariaDB via WordPress $wpdb |
| **ORM** | None - Raw SQL with $wpdb->prepare() |
| **Table Prefix** | `{$wpdb->prefix}presszone_translate_*` |
| **Charset** | `$wpdb->get_charset_collate()` |
| **Engine** | InnoDB (for reliability) |

---

## Critical Rules

### WordPress.org Database Security (ZERO TOLERANCE)

```php
// CORRECT - ALWAYS use $wpdb->prepare() for dynamic queries
$job = $wpdb->get_row($wpdb->prepare(
    "SELECT * FROM $table_name WHERE job_id = %d",
    $job_id
));

// WRONG - SQL Injection vulnerability - PLUGIN REJECTION
$results = $wpdb->get_results("SELECT * FROM $table WHERE id = $id");
```

### Table Naming Convention

```php
// CORRECT - Always use WordPress prefix + plugin prefix
global $wpdb;
$table = $wpdb->prefix . 'presszone_comments_likes';

// Actual table names:
// wp_presszone_comments_likes
// wp_presszone_comments_reports
// wp_presszone_comments_warnings
// wp_presszone_comments_users_extended
// wp_presszone_comments_alerts
// wp_presszone_comments_audit_log
```

---

## Table Schema Reference

### Feature Tables

| Table | Purpose | Key Columns |
|-------|---------|-------------|
| `presszone_comments_likes` | Comment upvotes/downvotes | `comment_id`, `user_id`, `type`, `ip_address` |
| `presszone_comments_reports` | Reported comments | `comment_id`, `user_id`, `reason`, `status` |
| `presszone_comments_warnings` | User infractions | `user_id`, `warning_type`, `expires_at`, `is_active` |
| `presszone_comments_users_extended` | Analytics & Reputation | `user_id`, `reputation_points`, `comment_count` |
| `presszone_comments_alerts` | Notifications | `user_id`, `action_type`, `content_id` |
| `presszone_comments_audit_log` | Moderation history | `user_id`, `action`, `moderator_id` |

---

## Query Patterns

### Voting Logic (Example from Query.php)

```php
public static function get_comment_votes($comment_id)
{
    global $wpdb;
    $table_name = $wpdb->prefix . 'presszone_comments_likes';
    
    $upvotes = (int) $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(*) FROM $table_name WHERE comment_id = %d AND type = 'upvote'",
        $comment_id
    ));

    $downvotes = (int) $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(*) FROM $table_name WHERE comment_id = %d AND type = 'downvote'",
        $comment_id
    ));

    return [
        'upvotes' => $upvotes,
        'downvotes' => $downvotes,
    ];
}
```

### Complex Aggregations

```php
// Top commenters by reputation
$commenters = $wpdb->get_results(
    "SELECT user_id, reputation_points, comment_count 
     FROM {$wpdb->prefix}presszone_comments_users_extended 
     ORDER BY reputation_points DESC 
     LIMIT 5",
    ARRAY_A
);
```

---

## Table Creation (dbDelta)

### Pattern for New Tables

```php
public static function install()
{
    global $wpdb;
    $charset_collate = $wpdb->get_charset_collate();
    $table_name = $wpdb->prefix . 'presszone_comments_new_feature';

    $sql = "CREATE TABLE $table_name (
        id bigint(20) NOT NULL AUTO_INCREMENT,
        user_id bigint(20) NOT NULL,
        data text NOT NULL,
        created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
        PRIMARY KEY  (id),
        KEY user_id (user_id)
    ) $charset_collate;";

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql);
}
```

---

## Common Mistakes to Avoid

| Mistake | Fix |
|---------|-----|
| No `$wpdb->prepare()` | ALWAYS use prepare() for dynamic queries |
| Hardcoded table names | Use `$wpdb->prefix . 'presszone_comments_*'` |
| Missing formats in insert | Specify `['%d', '%s']` in `insert()` calls |
| dbDelta syntax errors | PRIMARY KEY must have 2 spaces before it; KEY for indexes |
| Forgetting Wildcard Escape | Use `$wpdb->esc_like()` for LIKE queries |

---

## Placeholder Reference

| Placeholder | Type | Example |
|-------------|------|---------|
| `%d` | Integer | `$wpdb->prepare("...id = %d", 123)` |
| `%s` | String | `$wpdb->prepare("...slug = %s", 'slug')` |
| `%f` | Float | `$wpdb->prepare("...val = %f", 1.23)` |

---

## 🔒 MANDATORY SECURITY & COMPLIANCE RULES

> **CRITICAL**: These rules are NON-NEGOTIABLE for database development

### Database Security (Zero Tolerance)
- **SQL Injection**: ALWAYS use `$wpdb->prepare()` with placeholders for ALL queries
- **Input Validation**: Sanitize ALL input before database operations
- **LIKE Queries**: Use `$wpdb->esc_like()` before `prepare()` for LIKE patterns
- **ORDER BY**: Use whitelist validation or `sanitize_sql_orderby()`
- **Dynamic Queries**: Never concatenate variables directly into SQL

### WordPress.org Compliance
- **Table Prefixing**: ALL custom tables use `{$wpdb->prefix}presszone_translate_` prefix
- **Charset**: Always use `$wpdb->get_charset_collate()` for table creation
- **Engine**: Use InnoDB for reliability and foreign key support
- **Indexes**: Add proper indexes for performance

### Data Integrity
- **Transactions**: Use transactions for multi-table operations
- **Error Handling**: Check `$wpdb->last_error` after operations
- **Data Validation**: Validate data types and constraints before insertion

### Database Specific Security

#### Secure Query Patterns
```php
// CORRECT - Translation job queries with proper sanitization
function presszone_translate_get_jobs_by_status($status, $limit = 10, $offset = 0) {
    global $wpdb;
    
    // Validate status against whitelist
    $allowed_statuses = ['pending', 'processing', 'completed', 'failed'];
    if (!in_array($status, $allowed_statuses)) {
        return new WP_Error('invalid_status', 'Invalid job status');
    }
    
    // Sanitize numeric inputs
    $limit = absint($limit);
    $offset = absint($offset);
    
    // Use prepared statement
    $table_name = $wpdb->prefix . 'presszone_translate_jobs';
    $results = $wpdb->get_results($wpdb->prepare(
        "SELECT job_id, post_id, source_lang, target_lang, status, created_at, updated_at 
         FROM {$table_name} 
         WHERE status = %s 
         ORDER BY created_at DESC 
         LIMIT %d OFFSET %d",
        $status,
        $limit,
        $offset
    ));
    
    // Check for database errors
    if ($wpdb->last_error) {
        error_log('Database error in presszone_translate_get_jobs_by_status: ' . $wpdb->last_error);
        return new WP_Error('db_error', 'Database query failed');
    }
    
    return $results;
}
```

#### Secure Search Functionality
```php
// CORRECT - Safe search with LIKE queries
function presszone_translate_search_jobs($search_term, $limit = 20) {
    global $wpdb;
    
    // Sanitize search term
    $search_term = sanitize_text_field($search_term);
    if (empty($search_term)) {
        return [];
    }
    
    // Escape for LIKE query
    $search_like = '%' . $wpdb->esc_like($search_term) . '%';
    $limit = absint($limit);
    
    $table_name = $wpdb->prefix . 'presszone_translate_jobs';
    $results = $wpdb->get_results($wpdb->prepare(
        "SELECT j.*, p.post_title 
         FROM {$table_name} j 
         LEFT JOIN {$wpdb->posts} p ON j.post_id = p.ID 
         WHERE (p.post_title LIKE %s OR j.source_content LIKE %s)
         AND j.status != 'deleted'
         ORDER BY j.updated_at DESC 
         LIMIT %d",
        $search_like,
        $search_like,
        $limit
    ));
    
    return $results ?: [];
}
```

#### Secure Data Insertion
```php
// CORRECT - Secure job creation with validation
function presszone_translate_create_job($post_id, $source_lang, $target_lang, $priority = 'normal') {
    global $wpdb;
    
    // Validate inputs
    $post_id = absint($post_id);
    if (!$post_id || !get_post($post_id)) {
        return new WP_Error('invalid_post', 'Invalid post ID');
    }
    
    // Validate language codes
    $allowed_languages = ['en', 'es', 'fr', 'de', 'it', 'pt', 'ru', 'zh', 'ja', 'ko'];
    $source_lang = sanitize_key($source_lang);
    $target_lang = sanitize_key($target_lang);
    
    if (!in_array($source_lang, $allowed_languages) || !in_array($target_lang, $allowed_languages)) {
        return new WP_Error('invalid_language', 'Invalid language code');
    }
    
    // Validate priority
    $allowed_priorities = ['low', 'normal', 'high'];
    if (!in_array($priority, $allowed_priorities)) {
        $priority = 'normal';
    }
    
    // Check for existing job
    $table_name = $wpdb->prefix . 'presszone_translate_jobs';
    $existing = $wpdb->get_var($wpdb->prepare(
        "SELECT job_id FROM {$table_name} 
         WHERE post_id = %d AND source_lang = %s AND target_lang = %s 
         AND status IN ('pending', 'processing')",
        $post_id,
        $source_lang,
        $target_lang
    ));
    
    if ($existing) {
        return new WP_Error('job_exists', 'Translation job already exists');
    }
    
    // Get post content
    $post = get_post($post_id);
    $source_content = wp_kses_post($post->post_content);
    
    // Insert new job
    $result = $wpdb->insert(
        $table_name,
        [
            'post_id' => $post_id,
            'source_lang' => $source_lang,
            'target_lang' => $target_lang,
            'source_content' => $source_content,
            'priority' => $priority,
            'status' => 'pending',
            'created_at' => current_time('mysql'),
            'updated_at' => current_time('mysql')
        ],
        ['%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s']
    );
    
    if ($result === false) {
        error_log('Failed to create translation job: ' . $wpdb->last_error);
        return new WP_Error('insert_failed', 'Failed to create translation job');
    }
    
    return $wpdb->insert_id;
}
```

#### Secure Batch Operations
```php
// CORRECT - Safe batch updates with transaction
function presszone_translate_batch_update_status($job_ids, $new_status) {
    global $wpdb;
    
    // Validate inputs
    if (!is_array($job_ids) || empty($job_ids)) {
        return new WP_Error('invalid_input', 'Invalid job IDs');
    }
    
    // Sanitize job IDs
    $job_ids = array_map('absint', $job_ids);
    $job_ids = array_filter($job_ids); // Remove zeros
    
    if (empty($job_ids)) {
        return new WP_Error('invalid_job_ids', 'No valid job IDs provided');
    }
    
    // Validate status
    $allowed_statuses = ['pending', 'processing', 'completed', 'failed', 'cancelled'];
    if (!in_array($new_status, $allowed_statuses)) {
        return new WP_Error('invalid_status', 'Invalid status');
    }
    
    // Start transaction
    $wpdb->query('START TRANSACTION');
    
    try {
        $table_name = $wpdb->prefix . 'presszone_translate_jobs';
        $placeholders = implode(',', array_fill(0, count($job_ids), '%d'));
        
        $query = $wpdb->prepare(
            "UPDATE {$table_name} 
             SET status = %s, updated_at = %s 
             WHERE job_id IN ({$placeholders})",
            array_merge([$new_status, current_time('mysql')], $job_ids)
        );
        
        $result = $wpdb->query($query);
        
        if ($result === false) {
            throw new Exception('Batch update failed: ' . $wpdb->last_error);
        }
        
        // Commit transaction
        $wpdb->query('COMMIT');
        
        return $result; // Number of affected rows
        
    } catch (Exception $e) {
        // Rollback on error
        $wpdb->query('ROLLBACK');
        error_log('Batch update error: ' . $e->getMessage());
        return new WP_Error('batch_update_failed', 'Batch update failed');
    }
}
```

#### Database Schema Security
```php
// CORRECT - Secure table creation with proper constraints
function presszone_translate_create_jobs_table() {
    global $wpdb;
    
    $table_name = $wpdb->prefix . 'presszone_translate_jobs';
    $charset_collate = $wpdb->get_charset_collate();
    
    $sql = "CREATE TABLE {$table_name} (
        job_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        post_id bigint(20) unsigned NOT NULL,
        source_lang varchar(10) NOT NULL,
        target_lang varchar(10) NOT NULL,
        source_content longtext NOT NULL,
        translated_content longtext DEFAULT NULL,
        priority enum('low','normal','high') DEFAULT 'normal',
        status enum('pending','processing','completed','failed','cancelled') DEFAULT 'pending',
        progress tinyint(3) unsigned DEFAULT 0,
        error_message text DEFAULT NULL,
        api_job_id varchar(255) DEFAULT NULL,
        created_at datetime NOT NULL,
        updated_at datetime NOT NULL,
        completed_at datetime DEFAULT NULL,
        PRIMARY KEY (job_id),
        KEY post_id (post_id),
        KEY status (status),
        KEY created_at (created_at),
        KEY source_target_lang (source_lang, target_lang),
        UNIQUE KEY unique_active_job (post_id, source_lang, target_lang, status),
        CONSTRAINT fk_translate_job_post 
            FOREIGN KEY (post_id) 
            REFERENCES {$wpdb->posts}(ID) 
            ON DELETE CASCADE
    ) {$charset_collate};";
    
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql);
    
    // Check for errors
    if ($wpdb->last_error) {
        error_log('Table creation error: ' . $wpdb->last_error);
        return false;
    }
    
    return true;
}
```

### Performance & Security Optimization

#### Query Optimization
```php
// CORRECT - Optimized queries with proper indexing
function presszone_translate_get_dashboard_stats() {
    global $wpdb;
    
    $table_name = $wpdb->prefix . 'presszone_translate_jobs';
    
    // Single query to get all stats (more efficient)
    $stats = $wpdb->get_row(
        "SELECT 
            COUNT(*) as total_jobs,
            SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_jobs,
            SUM(CASE WHEN status = 'processing' THEN 1 ELSE 0 END) as processing_jobs,
            SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed_jobs,
            SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed_jobs,
            AVG(CASE WHEN status = 'completed' THEN 
                TIMESTAMPDIFF(MINUTE, created_at, completed_at) 
                ELSE NULL END) as avg_completion_time
         FROM {$table_name}
         WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)",
        ARRAY_A
    );
    
    return $stats ?: [];
}
```

### Critical Patterns
```php
// ✅ SECURE DATABASE PATTERNS
// Prepared statements
$results = $wpdb->get_results($wpdb->prepare(
    "SELECT * FROM {$wpdb->prefix}presszone_translate_jobs WHERE status = %s AND user_id = %d",
    $status,
    $user_id
));

// LIKE queries
$search_term = $wpdb->esc_like($search) . '%';
$results = $wpdb->get_results($wpdb->prepare(
    "SELECT * FROM table WHERE title LIKE %s",
    $search_term
));

// ORDER BY validation
$allowed_orderby = ['id', 'created_at', 'status'];
$orderby = in_array($orderby, $allowed_orderby) ? $orderby : 'id';

// ❌ FORBIDDEN PATTERNS
$wpdb->query("SELECT * FROM table WHERE id = $id"); // SQL injection risk
$wpdb->query("SELECT * FROM table WHERE title LIKE '%$search%'"); // No escaping
```

### Performance & Optimization
- **Indexes**: Add indexes on frequently queried columns
- **Pagination**: Use LIMIT/OFFSET for large result sets
- **Caching**: Cache expensive queries when appropriate
- **Query Analysis**: Use `EXPLAIN` to optimize slow queries