# Database Documentation

## Database: `aiwebreporter`

### Tables Overview

| Table | Purpose |
|-------|---------|
| `roles` | User role definitions (admin, manager, subscriber) |
| `users` | User accounts with auth and profile data |
| `categories` | Hierarchical content categories |
| `posts` | Blog articles and AI-generated content |
| `post_views` | Per-post view tracking |
| `settings` | Key-value application settings |
| `activity_log` | User and system activity audit trail |
| `newsletter_subscribers` | Email subscription records |
| `newsletter_logs` | Campaign send history |
| `newsletter_queue` | Pending email delivery queue |
| `contact_messages` | Contact form submissions |
| `pages` | Static content pages (About, Privacy, etc.) |
| `login_attempts` | IP-based login rate limiting |
| `bot_keywords` | AI bot keyword/topic queue |
| `bot_articles` | AI-generated article drafts |
| `bot_errors` | AI bot error logs |

---

### Table Details

#### `roles`
| Column | Type | Description |
|--------|------|-------------|
| id | INT PK AUTO | Role ID |
| name | VARCHAR(50) | Display name |
| slug | VARCHAR(50) UNIQUE | URL-safe identifier |
| description | TEXT | Role description |

Default rows: admin, manager, subscriber

#### `users`
| Column | Type | Description |
|--------|------|-------------|
| id | INT PK AUTO | User ID |
| role_id | INT FK→roles | Assigned role |
| username | VARCHAR(50) UNIQUE | Login username |
| email | VARCHAR(191) UNIQUE | Email address |
| password_hash | VARCHAR(255) | Bcrypt hash |
| display_name | VARCHAR(100) | Public display name |
| avatar | VARCHAR(255) | Avatar filename |
| bio | TEXT | User biography |
| status | ENUM | pending, approved, rejected, suspended |
| login_attempts | TINYINT | Failed login counter |
| locked_until | DATETIME | Account lockout expiry |
| last_login | DATETIME | Last successful login |
| created_at | DATETIME | Registration date |
| updated_at | DATETIME | Last profile update |

#### `categories`
| Column | Type | Description |
|--------|------|-------------|
| id | INT PK AUTO | Category ID |
| parent_id | INT FK→categories | Parent category (NULL=root) |
| name | VARCHAR(100) | Category name |
| slug | VARCHAR(100) UNIQUE | URL slug |
| description | TEXT | Category description |
| sort_order | INT | Display order |
| created_at | DATETIME | Creation date |

#### `posts`
| Column | Type | Description |
|--------|------|-------------|
| id | INT PK AUTO | Post ID |
| user_id | INT FK→users | Author |
| category_id | INT FK→categories | Assigned category |
| title | VARCHAR(255) | Post title |
| slug | VARCHAR(255) UNIQUE | URL slug |
| excerpt | TEXT | Short summary |
| body | LONGTEXT | Full HTML content |
| featured_image | VARCHAR(255) | Image filename |
| status | ENUM | draft, pending, published, rejected |
| is_featured | TINYINT | Featured flag |
| views | INT | View counter |
| seo_title | VARCHAR(255) | SEO title override |
| meta_description | VARCHAR(500) | Meta description |
| published_at | DATETIME | Publication date |
| created_at | DATETIME | Creation date |
| updated_at | DATETIME | Last edit date |
| source | ENUM | manual, ai_generated |

#### `newsletter_subscribers`
| Column | Type | Description |
|--------|------|-------------|
| id | INT PK AUTO | Subscriber ID |
| email | VARCHAR(191) UNIQUE | Email address |
| status | ENUM | pending, confirmed, unsubscribed |
| token | VARCHAR(64) | Verification/unsubscribe token |
| confirmed_at | DATETIME | Verification date |
| unsubscribed_at | DATETIME | Unsubscribe date |
| created_at | DATETIME | Subscription date |
| updated_at | DATETIME | Last status change |

#### `newsletter_logs`
| Column | Type | Description |
|--------|------|-------------|
| id | INT PK AUTO | Log ID |
| post_id | INT FK→posts | Related post (nullable) |
| subject | VARCHAR(255) | Email subject line |
| body | LONGTEXT | Email HTML body |
| recipient_count | INT | Total recipients |
| sent_count | INT | Successfully sent |
| failed_count | INT | Failed deliveries |
| status | ENUM | pending, sending, sent, failed |
| created_at | DATETIME | Campaign created |

#### `newsletter_queue`
| Column | Type | Description |
|--------|------|-------------|
| id | INT PK AUTO | Queue item ID |
| log_id | INT FK→newsletter_logs | Parent campaign |
| email | VARCHAR(191) | Recipient email |
| status | ENUM | pending, sending, sent, failed |
| attempts | TINYINT | Send attempt count |
| error_message | TEXT | Last error detail |
| sent_at | DATETIME | Successful send time |
| created_at | DATETIME | Queued time |

#### `contact_messages`
| Column | Type | Description |
|--------|------|-------------|
| id | INT PK AUTO | Message ID |
| name | VARCHAR(100) | Sender name |
| email | VARCHAR(191) | Sender email |
| subject | VARCHAR(255) | Message subject |
| message | TEXT | Message body |
| ip | VARCHAR(45) | Sender IP address |
| is_read | TINYINT | Read status flag |
| created_at | DATETIME | Submission date |

#### `pages`
| Column | Type | Description |
|--------|------|-------------|
| id | INT PK AUTO | Page ID |
| title | VARCHAR(255) | Page title |
| slug | VARCHAR(255) UNIQUE | URL slug |
| body | LONGTEXT | Page HTML content |
| seo_title | VARCHAR(255) | SEO title override |
| meta_description | VARCHAR(500) | Meta description |
| is_published | TINYINT | Published flag |
| created_at | DATETIME | Creation date |
| updated_at | DATETIME | Last edit date |

#### `login_attempts`
| Column | Type | Description |
|--------|------|-------------|
| id | INT PK AUTO | Attempt ID |
| ip_address | VARCHAR(45) | Client IP |
| email | VARCHAR(191) | Attempted email |
| attempted_at | DATETIME | Attempt timestamp |

#### `bot_keywords`
| Column | Type | Description |
|--------|------|-------------|
| id | INT PK AUTO | Keyword ID |
| keyword | VARCHAR(255) | Topic/keyword text |
| source | VARCHAR(100) | Origin source |
| status | ENUM | new, selected, skipped, used |
| created_at | DATETIME | Added date |

#### `bot_articles`
| Column | Type | Description |
|--------|------|-------------|
| id | INT PK AUTO | Article ID |
| keyword_id | INT FK→bot_keywords | Source keyword |
| title | VARCHAR(255) | Generated title |
| body | LONGTEXT | Generated content |
| status | ENUM | draft, approved, rejected, published |
| post_id | INT FK→posts | Published post link |
| created_at | DATETIME | Generation date |
| updated_at | DATETIME | Last edit date |

#### `bot_errors`
| Column | Type | Description |
|--------|------|-------------|
| id | INT PK AUTO | Error ID |
| keyword_id | INT | Related keyword |
| error_type | VARCHAR(100) | Error category |
| error_message | TEXT | Error details |
| created_at | DATETIME | Error timestamp |

---

### Migrations

Run migrations in order:
1. `001_schema.sql` — Core tables (roles, users, categories, posts, settings, etc.)
2. `002_categories_seed.sql` — Default category hierarchy
3. `003_bot_system.sql` — Bot tables (keywords, articles, errors)
4. `004_newsletter_system.sql` — Newsletter enhancements and queue table
5. `005_static_pages.sql` — Static pages data and login_attempts table
