-- Migration: Create analytics_search_keywords table
-- Date: 2025-01-XX
-- Description: Creates a table to store search keywords (queries) from Google Search Console and Bing Search Console

CREATE TABLE IF NOT EXISTS analytics_search_keywords (
    id VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci PRIMARY KEY,
    project_id VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    measurement_integration_setting_id VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '計測統合設定ID',
    source ENUM('Google Search Console', 'Bing Search Console') NOT NULL COMMENT 'データソース',
    query VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '検索キーワード',
    keyword_date DATE NOT NULL COMMENT 'キーワードの日付',
    clicks INT DEFAULT 0 COMMENT 'クリック数',
    impressions INT DEFAULT 0 COMMENT 'インプレッション数',
    ctr DECIMAL(10, 4) DEFAULT 0.0000 COMMENT 'CTR（クリック率）',
    position DECIMAL(10, 2) DEFAULT 0.00 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 (measurement_integration_setting_id) REFERENCES measurement_integration_settings(id) ON DELETE SET NULL,
    INDEX idx_project (project_id),
    INDEX idx_measurement_integration_setting (measurement_integration_setting_id),
    INDEX idx_source (source),
    INDEX idx_keyword_date (keyword_date),
    INDEX idx_query (query(255)),
    UNIQUE KEY unique_project_query_date_source (
        project_id, 
        measurement_integration_setting_id, 
        query, 
        keyword_date, 
        source
    )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

