-- Migration: Create unified measurement integration settings table
-- Date: 2025-01-XX
-- Description: Creates a unified table to store all measurement integration settings (GA4, Google Search Console, Bing Search Console, Google Adsense, Meta Marketing API)

-- ===== Unified Measurement Integration Settings =====
CREATE TABLE IF NOT EXISTS measurement_integration_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,
    integration_type ENUM('ga4', 'google_search_console', 'bing_search_console', 'google_adsense', 'meta_marketing_api') NOT NULL COMMENT '連携タイプ',
    identifier VARCHAR(500) NOT NULL COMMENT '識別子（URLまたはアカウントID）',
    metrics JSON NOT NULL COMMENT '計測する値の設定（JSON配列）',
    connection_test_passed BOOLEAN DEFAULT FALSE COMMENT '接続テストが完了しているかどうか',
    connection_test_date TIMESTAMP NULL 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,
    UNIQUE KEY unique_project_integration_identifier (project_id, integration_type, identifier),
    INDEX idx_project (project_id),
    INDEX idx_integration_type (integration_type),
    INDEX idx_connection_test (connection_test_passed)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Note: metrics JSON format example:
-- [
--   {"name": "sessions", "displayName": "セッション数", "enabled": true},
--   {"name": "users", "displayName": "ユーザー数", "enabled": true}
-- ]

-- Migrate existing GA4 settings to unified table (if ga4_settings table exists)
-- Note: This will fail silently if the table doesn't exist (handled by migration runner)
INSERT INTO measurement_integration_settings (
    id, project_id, integration_type, identifier, metrics, 
    connection_test_passed, connection_test_date, created_at, updated_at
)
SELECT 
    id, project_id, 'ga4', measurement_url, metrics,
    COALESCE(connection_test_passed, FALSE), connection_test_date, created_at, updated_at
FROM ga4_settings
WHERE NOT EXISTS (
    SELECT 1 FROM measurement_integration_settings 
    WHERE measurement_integration_settings.id = ga4_settings.id
);

-- Migrate existing Google Search Console settings (if table exists)
INSERT INTO measurement_integration_settings (
    id, project_id, integration_type, identifier, metrics, 
    connection_test_passed, connection_test_date, created_at, updated_at
)
SELECT 
    id, project_id, 'google_search_console', site_url, metrics,
    COALESCE(connection_test_passed, FALSE), connection_test_date, created_at, updated_at
FROM google_search_console_settings
WHERE NOT EXISTS (
    SELECT 1 FROM measurement_integration_settings 
    WHERE measurement_integration_settings.id = google_search_console_settings.id
);

-- Migrate existing Bing Search Console settings (if table exists)
INSERT INTO measurement_integration_settings (
    id, project_id, integration_type, identifier, metrics, 
    connection_test_passed, connection_test_date, created_at, updated_at
)
SELECT 
    id, project_id, 'bing_search_console', site_url, metrics,
    COALESCE(connection_test_passed, FALSE), connection_test_date, created_at, updated_at
FROM bing_search_console_settings
WHERE NOT EXISTS (
    SELECT 1 FROM measurement_integration_settings 
    WHERE measurement_integration_settings.id = bing_search_console_settings.id
);

-- Migrate existing Google Adsense settings (if table exists)
INSERT INTO measurement_integration_settings (
    id, project_id, integration_type, identifier, metrics, 
    connection_test_passed, connection_test_date, created_at, updated_at
)
SELECT 
    id, project_id, 'google_adsense', site_url, metrics,
    COALESCE(connection_test_passed, FALSE), connection_test_date, created_at, updated_at
FROM google_adsense_settings
WHERE NOT EXISTS (
    SELECT 1 FROM measurement_integration_settings 
    WHERE measurement_integration_settings.id = google_adsense_settings.id
);

-- Migrate existing Meta Marketing API settings (if table exists)
INSERT INTO measurement_integration_settings (
    id, project_id, integration_type, identifier, metrics, 
    connection_test_passed, connection_test_date, created_at, updated_at
)
SELECT 
    id, project_id, 'meta_marketing_api', account_id, metrics,
    COALESCE(connection_test_passed, FALSE), connection_test_date, created_at, updated_at
FROM meta_marketing_api_settings
WHERE NOT EXISTS (
    SELECT 1 FROM measurement_integration_settings 
    WHERE measurement_integration_settings.id = meta_marketing_api_settings.id
);

-- Note: Old tables can be dropped after confirming migration is successful
-- The following DROP statements use IF EXISTS, so they will skip if tables don't exist
DROP TABLE IF EXISTS ga4_settings;
DROP TABLE IF EXISTS google_search_console_settings;
DROP TABLE IF EXISTS bing_search_console_settings;
DROP TABLE IF EXISTS google_adsense_settings;
DROP TABLE IF EXISTS meta_marketing_api_settings;

