# AIAuto Content Writing Assistant Basic - Database Structure

This document describes the database tables created by the AIAuto Content Writing Assistant Basic WordPress plugin.

## Overview

The plugin creates 4 main database tables to store generated posts, chapters, subchapters, and post information. All tables use the WordPress database prefix (e.g., `wp_aiauto_*`).

## Table Structure

### 1. `{prefix}_aiauto_generate_posts`

Main table for storing post generation requests and metadata.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint(20) unsigned | Primary key, auto-increment |
| `status` | varchar(50) | Current status (pending, processing, completed, failed) |
| `topic` | varchar(255) | Main topic/subject of the post |
| `post_status` | varchar(50) | WordPress post status (draft, publish, etc.) |
| `generate_image` | enum('yes', 'no') | Whether to generate images for the post |
| `target_audience` | text | Target audience description |
| `writing_style` | text | Writing style preferences |
| `max_word_count` | int(11) unsigned | Maximum word count for the post |
| `chapter_number` | int(11) unsigned | Number of chapters to generate |
| `subchapter_number` | int(11) unsigned | Number of subchapters per chapter |
| `deeper_research` | enum('yes', 'no') | Whether to enable deeper research functionality |
| `cta` | text | Call-to-action text |
| `about_company` | text | Company information |
| `image_title` | text | Generated image title |
| `image_caption` | text | Generated image caption |
| `image_alt_text` | text | Generated image alt text |
| `image_url` | text | Generated image URL |
| `error_log` | text | Error messages if generation fails |
| `created_date` | datetime | Record creation timestamp |
| `updated_date` | datetime | Record last update timestamp |

**Indexes:**
- Primary key on `id`
- Index on `status`
- Index on `post_status`
- Index on `created_date`

### 2. `{prefix}_aiauto_chapters`

Stores individual chapters for each generated post.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint(20) unsigned | Primary key, auto-increment |
| `post_id` | bigint(20) unsigned | Foreign key to generate_posts.id |
| `title` | text | Chapter title |
| `content` | longtext | Chapter content |
| `image_title` | text | Chapter image title |
| `image_caption` | text | Chapter image caption |
| `image_alt_text` | text | Chapter image alt text |
| `image_url` | text | Chapter image URL |
| `created_date` | datetime | Record creation timestamp |
| `updated_date` | datetime | Record last update timestamp |

**Indexes:**
- Primary key on `id`
- Index on `post_id`
- Foreign key constraint to `aiauto_generate_posts(id)` with CASCADE delete

### 3. `{prefix}_aiauto_subchapters`

Stores subchapters within each chapter.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint(20) unsigned | Primary key, auto-increment |
| `chapter_id` | bigint(20) unsigned | Foreign key to chapters.id |
| `title` | text | Subchapter title |
| `content` | longtext | Subchapter content |
| `created_date` | datetime | Record creation timestamp |
| `updated_date` | datetime | Record last update timestamp |

**Indexes:**
- Primary key on `id`
- Index on `chapter_id`
- Foreign key constraint to `aiauto_chapters(id)` with CASCADE delete

### 4. `{prefix}_aiauto_post_information`

Stores final post information and metadata.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint(20) unsigned | Primary key, auto-increment |
| `post_id` | bigint(20) unsigned | Foreign key to generate_posts.id |
| `title` | text | Final post title |
| `introduction` | longtext | Post introduction |
| `slug` | text | Post slug/URL |
| `excerpt` | text | Post excerpt |
| `meta_title` | text | SEO meta title |
| `meta_description` | text | SEO meta description |
| `tags` | text | Post tags |
| `conclusion` | longtext | Post conclusion |
| `research_overview` | longtext | Research overview and findings from the topic research |
| `categories` | text | Post categories |
| `created_date` | datetime | Record creation timestamp |
| `updated_date` | datetime | Record last update timestamp |

**Indexes:**
- Primary key on `id`
- Index on `post_id`
- Foreign key constraint to `aiauto_generate_posts(id)` with CASCADE delete

## Relationships

```
aiauto_generate_posts (1) ←→ (many) aiauto_chapters
aiauto_chapters (1) ←→ (many) aiauto_subchapters
aiauto_generate_posts (1) ←→ (1) aiauto_post_information
```

## Installation

Tables are automatically created when the plugin is activated using WordPress's `dbDelta()` function. The plugin also includes:

- **Database Installer Class**: Handles table creation and updates
- **Database Model Class**: Provides CRUD operations for all tables
- **Progress Management Interface**: Built-in admin page to view and manage progress data

## Usage Examples

### Using the Database Model

```php
// Initialize the model
$db_model = new AIAuto_Database_Model();

// Insert a new generate post
$post_id = $db_model->insert_generate_post(array(
    'topic' => 'AI in Healthcare',
    'target_audience' => 'Healthcare professionals',
    'max_word_count' => 2000,
    'chapter_number' => 3
));

// Get chapters for a post
$chapters = $db_model->get_chapters_by_post_id($post_id);

// Get post information
$post_info = $db_model->get_post_information($post_id);
```

### Direct Database Queries

```php
global $wpdb;

// Get all posts with status 'completed'
$posts = $wpdb->get_results(
    "SELECT * FROM {$wpdb->prefix}aiauto_generate_posts 
     WHERE status = 'completed' 
     ORDER BY created_date DESC"
);

// Get chapters with post information
$chapters = $wpdb->get_results(
    "SELECT c.*, gp.topic 
     FROM {$wpdb->prefix}aiauto_chapters c 
     LEFT JOIN {$wpdb->prefix}aiauto_generate_posts gp ON c.post_id = gp.id"
);
```

## Maintenance

- Tables are automatically created on plugin activation
- Foreign key constraints ensure data integrity
- CASCADE delete removes related records when parent records are deleted
- Admin interface provides easy data viewing and management

## Notes

- All tables use WordPress database charset and collation
- Timestamps are automatically managed with `CURRENT_TIMESTAMP`
- The plugin stores database version for future updates
- Tables are automatically dropped on plugin uninstall