-- Migration: Create admin_settings_task_standard_days table
-- Date: 2025-01-XX
-- Description: Creates table to store standard task execution days based on complexity, quality, and assignee_skill

-- ===== Standard Task Execution Days Settings =====
CREATE TABLE IF NOT EXISTS admin_settings_task_standard_days (
    id VARCHAR(36) PRIMARY KEY,
    complexity ENUM('low', 'medium', 'high') NOT NULL COMMENT '複雑度（低、中、高）',
    quality ENUM('low', 'medium', 'high') NOT NULL COMMENT '品質要求度（低、中、高）',
    assignee_skill ENUM('low', 'medium', 'high') NOT NULL COMMENT '担当者スキル（低、中、高）',
    standard_days INT NOT NULL COMMENT '標準実行日数',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_combination (complexity, quality, assignee_skill),
    INDEX idx_complexity (complexity),
    INDEX idx_quality (quality),
    INDEX idx_assignee_skill (assignee_skill)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample data for all 27 combinations (3 x 3 x 3)
-- Complexity: low, medium, high
-- Quality: low, medium, high
-- Assignee Skill: low, medium, high

-- Low Complexity combinations
INSERT INTO admin_settings_task_standard_days (id, complexity, quality, assignee_skill, standard_days) VALUES
(UUID(), 'low', 'low', 'low', 3),
(UUID(), 'low', 'low', 'medium', 2),
(UUID(), 'low', 'low', 'high', 1),
(UUID(), 'low', 'medium', 'low', 4),
(UUID(), 'low', 'medium', 'medium', 3),
(UUID(), 'low', 'medium', 'high', 2),
(UUID(), 'low', 'high', 'low', 5),
(UUID(), 'low', 'high', 'medium', 4),
(UUID(), 'low', 'high', 'high', 3)
ON DUPLICATE KEY UPDATE standard_days = VALUES(standard_days);

-- Medium Complexity combinations
INSERT INTO admin_settings_task_standard_days (id, complexity, quality, assignee_skill, standard_days) VALUES
(UUID(), 'medium', 'low', 'low', 5),
(UUID(), 'medium', 'low', 'medium', 4),
(UUID(), 'medium', 'low', 'high', 3),
(UUID(), 'medium', 'medium', 'low', 6),
(UUID(), 'medium', 'medium', 'medium', 5),
(UUID(), 'medium', 'medium', 'high', 4),
(UUID(), 'medium', 'high', 'low', 7),
(UUID(), 'medium', 'high', 'medium', 6),
(UUID(), 'medium', 'high', 'high', 5)
ON DUPLICATE KEY UPDATE standard_days = VALUES(standard_days);

-- High Complexity combinations
INSERT INTO admin_settings_task_standard_days (id, complexity, quality, assignee_skill, standard_days) VALUES
(UUID(), 'high', 'low', 'low', 8),
(UUID(), 'high', 'low', 'medium', 7),
(UUID(), 'high', 'low', 'high', 6),
(UUID(), 'high', 'medium', 'low', 10),
(UUID(), 'high', 'medium', 'medium', 8),
(UUID(), 'high', 'medium', 'high', 7),
(UUID(), 'high', 'high', 'low', 12),
(UUID(), 'high', 'high', 'medium', 10),
(UUID(), 'high', 'high', 'high', 8)
ON DUPLICATE KEY UPDATE standard_days = VALUES(standard_days);

