# Database Schema & Migrations Skill

> **Technology:** WordPress dbDelta for table creation and schema updates

---

## Purpose

This skill covers database table design, schema migrations, and WordPress dbDelta usage for the Comments Press Zone plugin.

---

## Table Schema Reference

### presszone_comments_likes

```sql
CREATE TABLE {$wpdb->prefix}presszone_comments_likes (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    comment_id bigint(20) NOT NULL,
    user_id bigint(20) NOT NULL DEFAULT 0,
    type varchar(20) NOT NULL DEFAULT 'upvote',
    ip_address varchar(45) NOT NULL DEFAULT '',
    created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
    PRIMARY KEY  (id),
    KEY comment_id (comment_id),
    KEY user_id (user_id),
    KEY type (type),
    KEY ip_address (ip_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### presszone_comments_reports

```sql
CREATE TABLE {$wpdb->prefix}presszone_comments_reports (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    comment_id bigint(20) NOT NULL,
    user_id bigint(20) NOT NULL,
    reason text NOT NULL,
    status varchar(20) NOT NULL DEFAULT 'open',
    created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
    PRIMARY KEY  (id),
    KEY comment_id (comment_id),
    KEY user_id (user_id),
    KEY status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### presszone_comments_warnings

```sql
CREATE TABLE {$wpdb->prefix}presszone_comments_warnings (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    user_id bigint(20) NOT NULL,
    warning_type varchar(50) NOT NULL,
    reason text NOT NULL,
    moderator_id bigint(20) NOT NULL DEFAULT 0,
    expires_at datetime DEFAULT NULL,
    is_active tinyint(1) NOT NULL DEFAULT 1,
    created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
    PRIMARY KEY  (id),
    KEY user_id (user_id),
    KEY is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### presszone_comments_users_extended

```sql
CREATE TABLE {$wpdb->prefix}presszone_comments_users_extended (
    user_id bigint(20) NOT NULL,
    reputation_points int(11) NOT NULL DEFAULT 0,
    comment_count int(11) NOT NULL DEFAULT 0,
    last_comment_at datetime DEFAULT NULL,
    created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
    PRIMARY KEY  (user_id),
    KEY reputation_points (reputation_points)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### presszone_comments_alerts

```sql
CREATE TABLE {$wpdb->prefix}presszone_comments_alerts (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    user_id bigint(20) NOT NULL,
    action_type varchar(50) NOT NULL,
    content_id bigint(20) NOT NULL,
    actor_id bigint(20) NOT NULL DEFAULT 0,
    is_read tinyint(1) NOT NULL DEFAULT 0,
    created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
    PRIMARY KEY  (id),
    KEY user_id (user_id),
    KEY is_read (is_read)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### presszone_comments_audit_log

```sql
CREATE TABLE {$wpdb->prefix}presszone_comments_audit_log (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    user_id bigint(20) NOT NULL,
    action varchar(50) NOT NULL,
    content_id bigint(20) NOT NULL,
    moderator_id bigint(20) NOT NULL,
    reason text NOT NULL DEFAULT '',
    created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
    PRIMARY KEY  (id),
    KEY user_id (user_id),
    KEY moderator_id (moderator_id),
    KEY action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

---

## dbDelta Installation Pattern

```php
namespace CommentsPressZone\Database;

class Installer {
    public static function install(): void {
        global $wpdb;
        
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        
        $charset_collate = $wpdb->get_charset_collate();
        
        self::create_likes_table($charset_collate);
        self::create_reports_table($charset_collate);
        self::create_warnings_table($charset_collate);
        self::create_users_extended_table($charset_collate);
        self::create_alerts_table($charset_collate);
        self::create_audit_log_table($charset_collate);
        
        // Update plugin version
        update_option('presszone_comments_db_version', PRESSZONE_COMMENTS_VERSION);
    }
    
    private static function create_likes_table(string $charset_collate): void {
        global $wpdb;
        $table_name = $wpdb->prefix . 'presszone_comments_likes';
        
        $sql = "CREATE TABLE $table_name (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            comment_id bigint(20) NOT NULL,
            user_id bigint(20) NOT NULL DEFAULT 0,
            type varchar(20) NOT NULL DEFAULT 'upvote',
            ip_address varchar(45) NOT NULL DEFAULT '',
            created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
            PRIMARY KEY  (id),
            KEY comment_id (comment_id),
            KEY user_id (user_id),
            KEY type (type)
        ) $charset_collate;";
        
        dbDelta($sql);
    }
}
```

---

## dbDelta Syntax Rules (CRITICAL)

1. **Two spaces before PRIMARY KEY**
   ```sql
   PRIMARY KEY  (id)  -- CORRECT
   PRIMARY KEY (id)   -- WRONG - dbDelta will fail
   ```

2. **Use KEY for indexes, not INDEX**
   ```sql
   KEY user_id (user_id)  -- CORRECT
   INDEX user_id (user_id) -- WRONG
   ```

3. **Include data types exactly**
   ```sql
   id bigint(20) NOT NULL AUTO_INCREMENT  -- CORRECT
   id bigint NOT NULL AUTO_INCREMENT      -- WRONG
   ```

4. **Use DEFAULT for default values**
   ```sql
   status varchar(20) NOT NULL DEFAULT 'open'  -- CORRECT
   ```

5. **No trailing comma after last column**

---

## Migration Pattern

```php
namespace CommentsPressZone\Database;

class Migrator {
    public static function maybe_migrate(): void {
        $current_version = get_option('presszone_comments_db_version', '0.0.0');
        
        if (version_compare($current_version, '1.1.0', '<')) {
            self::migrate_to_1_1_0();
        }
        
        if (version_compare($current_version, '1.2.0', '<')) {
            self::migrate_to_1_2_0();
        }
        
        update_option('presszone_comments_db_version', PRESSZONE_COMMENTS_VERSION);
    }
    
    private static function migrate_to_1_1_0(): void {
        global $wpdb;
        $table = $wpdb->prefix . 'presszone_comments_likes';
        
        // Add new column if it doesn't exist
        $column_exists = $wpdb->get_results(
            $wpdb->prepare(
                "SHOW COLUMNS FROM `$table` LIKE %s",
                'ip_address'
            )
        );
        
        if (empty($column_exists)) {
            $wpdb->query(
                "ALTER TABLE `$table` 
                 ADD COLUMN `ip_address` varchar(45) NOT NULL DEFAULT '' AFTER `type`"
            );
        }
    }
}
```

---

## Data Types Reference

| Type | WordPress Usage | Example |
|------|----------------|---------|
| `bigint(20)` | IDs, foreign keys | `user_id bigint(20)` |
| `int(11)` | Counts, integers | `reputation_points int(11)` |
| `varchar(255)` | Short strings | `type varchar(20)` |
| `text` | Long text | `reason text` |
| `tinyint(1)` | Booleans | `is_active tinyint(1)` |
| `datetime` | Timestamps | `created_at datetime` |

---

## Index Strategy

### When to Add Indexes

1. **Foreign Keys** - Always index
   ```sql
   KEY user_id (user_id)
   KEY comment_id (comment_id)
   ```

2. **WHERE Clauses** - Index frequently filtered columns
   ```sql
   KEY status (status)
   KEY is_active (is_active)
   ```

3. **ORDER BY** - Index sort columns
   ```sql
   KEY created_at (created_at)
   ```

4. **Compound Indexes** - For multi-column queries
   ```sql
   KEY user_type (user_id, type)
   ```

---

## Table Relationships

```
wp_comments (WordPress core)
    └─ presszone_comments_likes (comment_id → ID)
    └─ presszone_comments_reports (comment_id → ID)

wp_users (WordPress core)
    ├─ presszone_comments_likes (user_id → ID)
    ├─ presszone_comments_reports (user_id → ID)
    ├─ presszone_comments_warnings (user_id → ID)
    ├─ presszone_comments_users_extended (user_id → ID)
    └─ presszone_comments_alerts (user_id → ID)
```

---

## Common Mistakes to Avoid

| Mistake | Fix |
|---------|-----|
| One space before PRIMARY KEY | Use two spaces: `PRIMARY KEY  (id)` |
| Using `INDEX` instead of `KEY` | Use `KEY user_id (user_id)` |
| Missing table prefix | Always use `$wpdb->prefix` |
| Wrong charset | Use `$wpdb->get_charset_collate()` |
| Missing ENGINE | Specify `ENGINE=InnoDB` |
| Trailing comma in CREATE TABLE | Remove comma after last column |

---

## Testing Checklist

- [ ] dbDelta syntax correct (two spaces, KEY not INDEX)
- [ ] All foreign key columns indexed
- [ ] Charset/collate from `$wpdb->get_charset_collate()`
- [ ] Table names use `$wpdb->prefix`
- [ ] Migration version tracking implemented
- [ ] InnoDB engine specified for transaction support