# ⚠️ LEGACY AGENT - USE expert.md INSTEAD

> **Status:** DEPRECATED
> **Replacement:** Use `.claude/agents/expert.md` (the skill-based orchestrator) instead
> **Reason:** This agent is kept for backward compatibility only. The new architecture uses focused skills (see `.claude/skills/`) composed by the expert.md orchestrator.

---

# Database Expert Agent

> **Specialized agent for Comments 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/Comments/Query.php` - Main query logic
- `includes/Database/Installer.php` - Table creation and updates
- `includes/Api/` - REST API data handling

---

## Tech Stack

| Technology | Details |
|------------|---------|
| **Database** | MySQL/MariaDB via WordPress $wpdb |
| **ORM** | None - Raw SQL with $wpdb->prepare() |
| **Table Prefix** | `{$wpdb->prefix}presszone_comments_*` |
| **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
$upvotes = (int) $wpdb->get_var($wpdb->prepare(
    "SELECT COUNT(*) FROM $table_name WHERE comment_id = %d AND type = 'upvote'",
    $comment_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)` |