-- Migration: Create GA4 settings table
-- Date: 2025-01-XX
-- Description: Creates table to store GA4 settings for projects (URL and metrics configuration)

-- ===== GA4 Settings =====
CREATE TABLE IF NOT EXISTS ga4_settings (
    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_url VARCHAR(500) NOT NULL COMMENT '計測するURL',
    metrics JSON NOT NULL COMMENT '計測する値の設定（JSON配列）',
    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,
    UNIQUE KEY unique_project_url (project_id, measurement_url),
    INDEX idx_project (project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Note: metrics JSON format example:
-- [
--   {"name": "sessions", "displayName": "セッション数", "enabled": true},
--   {"name": "users", "displayName": "ユーザー数", "enabled": true},
--   {"name": "pageviews", "displayName": "ページビュー数", "enabled": true},
--   {"name": "bounceRate", "displayName": "直帰率", "enabled": true},
--   {"name": "avgSessionDuration", "displayName": "平均セッション時間", "enabled": false}
-- ]

