-- Migration: Create alerts_integration table
-- Date: 2025-01-XX
-- Description: Creates table for integration alerts when data hasn't been fetched for 6+ days

CREATE TABLE IF NOT EXISTS alerts_integration (
    id VARCHAR(36) PRIMARY KEY,
    project_id VARCHAR(36) NOT NULL,
    integration_setting_id VARCHAR(36) NOT NULL,
    integration_type ENUM('ga4', 'google_search_console', 'bing_search_console', 'google_adsense', 'meta_marketing_api') NOT NULL,
    identifier VARCHAR(500) NOT NULL COMMENT '識別子（URLまたはアカウントID）',
    last_fetch_date DATE NULL COMMENT '最終取得日（analytics_dataから取得）',
    days_since_last_fetch INT NOT NULL COMMENT '最終取得からの経過日数',
    status ENUM('pending', 'in_progress', 'resolved') DEFAULT 'pending' 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 (integration_setting_id) REFERENCES measurement_integration_settings(id) ON DELETE CASCADE,
    UNIQUE KEY unique_project_integration_setting (project_id, integration_setting_id),
    INDEX idx_project (project_id),
    INDEX idx_status (status),
    INDEX idx_integration_type (integration_type),
    INDEX idx_last_fetch_date (last_fetch_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

