-- Migration: Create article generation tables
-- Date: 2025-01-XX
-- Description: Creates tables for article themes, articles, and WordPress site settings

-- ===== WordPress Sites =====
CREATE TABLE IF NOT EXISTS article_wordpress_sites (
    id VARCHAR(36) PRIMARY KEY,
    project_id VARCHAR(36) NOT NULL,
    site_name VARCHAR(255) NOT NULL COMMENT 'サイト名',
    site_url VARCHAR(500) NOT NULL COMMENT 'WordPressサイトのURL',
    api_url VARCHAR(500) NOT NULL COMMENT 'WordPress REST APIのURL（通常は /wp-json/wp/v2）',
    username VARCHAR(255) NOT NULL COMMENT 'WordPress APIユーザー名',
    application_password VARCHAR(255) NOT NULL COMMENT 'WordPress Application Password',
    is_active BOOLEAN DEFAULT TRUE COMMENT '有効/無効フラグ',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    INDEX idx_project (project_id),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== Article Themes =====
CREATE TABLE IF NOT EXISTS article_themes (
    id VARCHAR(36) PRIMARY KEY,
    project_id VARCHAR(36) NOT NULL,
    theme_text TEXT NOT NULL COMMENT '記事テーマのテキスト',
    priority INT DEFAULT 0 COMMENT '優先度（高いほど優先）',
    is_created BOOLEAN DEFAULT FALSE COMMENT '記事作成済みフラグ',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    INDEX idx_project_priority (project_id, priority DESC, is_created),
    INDEX idx_created (is_created)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== Articles =====
CREATE TABLE IF NOT EXISTS articles (
    id VARCHAR(36) PRIMARY KEY,
    project_id VARCHAR(36) NOT NULL,
    wordpress_site_id VARCHAR(36) NOT NULL,
    article_theme_id VARCHAR(36) NOT NULL,
    title VARCHAR(500) NOT NULL COMMENT '記事タイトル',
    content TEXT NOT NULL COMMENT '記事本文（マークダウン形式）',
    wordpress_post_id INT DEFAULT NULL COMMENT 'WordPressに投稿された記事のID',
    wordpress_post_url VARCHAR(500) DEFAULT NULL COMMENT 'WordPressに投稿された記事のURL',
    status ENUM('draft', 'published', 'failed') DEFAULT 'draft' COMMENT '記事のステータス',
    error_message TEXT DEFAULT NULL COMMENT 'エラーメッセージ（失敗時）',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (wordpress_site_id) REFERENCES article_wordpress_sites(id) ON DELETE CASCADE,
    FOREIGN KEY (article_theme_id) REFERENCES article_themes(id) ON DELETE CASCADE,
    INDEX idx_project (project_id),
    INDEX idx_wordpress_site (wordpress_site_id),
    INDEX idx_theme (article_theme_id),
    INDEX idx_status (status),
    INDEX idx_wordpress_post_id (wordpress_post_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

