-- Migration: Create project dashboard charts table
-- Date: 2025-01-XX
-- Description: Creates table to store dashboard chart configurations for projects

-- ===== Project Dashboard Charts =====
CREATE TABLE IF NOT EXISTS project_dashboard_charts (
    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,
    chart_title VARCHAR(255) NOT NULL COMMENT 'グラフのタイトル',
    chart_type ENUM('line', 'bar', 'area', 'pie', 'scatter') NOT NULL DEFAULT 'line' COMMENT 'グラフの種類',
    x_axis_field VARCHAR(255) NOT NULL COMMENT 'X軸に使用するフィールド名',
    y_axis_field VARCHAR(255) NOT NULL COMMENT 'Y軸に使用するフィールド名',
    x_axis_label VARCHAR(255) DEFAULT NULL COMMENT 'X軸のラベル',
    y_axis_label VARCHAR(255) DEFAULT NULL COMMENT 'Y軸のラベル',
    display_order INT DEFAULT 0 COMMENT '表示順序',
    is_enabled BOOLEAN DEFAULT TRUE COMMENT '有効/無効',
    chart_config JSON DEFAULT 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,
    INDEX idx_project (project_id),
    INDEX idx_display_order (project_id, display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Note: chart_config JSON format example:
-- {
--   "colors": ["#667eea", "#f093fb", "#4facfe"],
--   "showLegend": true,
--   "showGrid": true,
--   "stacked": false
-- }

