전자동 콘텐츠 파이프라인

-- ============================================
-- 콘텐츠 파이프라인 DB 스키마
-- ============================================

-- 키워드 풀 (수집된 키워드 저장)
CREATE TABLE wp_pipeline_keywords (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    keyword VARCHAR(255) NOT NULL,
    search_volume INT DEFAULT 0,
    difficulty DECIMAL(5,2) DEFAULT 0,
    cpc DECIMAL(8,2) DEFAULT 0,
    source ENUM('gsc','semrush','ahrefs','manual','trending') DEFAULT 'manual',
    status ENUM('new','approved','used','rejected') DEFAULT 'new',
    priority INT DEFAULT 50,
    category VARCHAR(100),
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    used_at DATETIME NULL,
    INDEX idx_status (status),
    INDEX idx_priority (priority DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 콘텐츠 캘린더 (발행 계획)
CREATE TABLE wp_pipeline_calendar (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    keyword_id BIGINT NOT NULL,
    scheduled_date DATE NOT NULL,
    content_type ENUM('blog','listicle','howto','review','comparison','news') DEFAULT 'blog',
    target_word_count INT DEFAULT 2000,
    status ENUM('planned','generating','draft','optimizing','scheduled','published','failed') DEFAULT 'planned',
    post_id BIGINT NULL,
    ai_model VARCHAR(50) DEFAULT 'gpt-4o',
    generation_attempts INT DEFAULT 0,
    error_message TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_date_status (scheduled_date, status),
    FOREIGN KEY (keyword_id) REFERENCES wp_pipeline_keywords(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 배포 상태 추적
CREATE TABLE wp_pipeline_distributions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT NOT NULL,
    channel ENUM('twitter','facebook','instagram','linkedin','email','kakao','pinterest') NOT NULL,
    status ENUM('pending','sent','failed') DEFAULT 'pending',
    external_id VARCHAR(255) NULL,
    engagement_data JSON NULL,
    sent_at DATETIME NULL,
    INDEX idx_post_channel (post_id, channel)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 콘텐츠 성과 추적
CREATE TABLE wp_pipeline_performance (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT NOT NULL,
    date DATE NOT NULL,
    pageviews INT DEFAULT 0,
    unique_visitors INT DEFAULT 0,
    avg_time_on_page DECIMAL(6,2) DEFAULT 0,
    bounce_rate DECIMAL(5,2) DEFAULT 0,
    search_impressions INT DEFAULT 0,
    search_clicks INT DEFAULT 0,
    avg_position DECIMAL(5,2) DEFAULT 0,
    conversions INT DEFAULT 0,
    revenue DECIMAL(10,2) DEFAULT 0,
    UNIQUE KEY uk_post_date (post_id, date),
    INDEX idx_date (date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;