-- Migration: Create alerts_task_delay table
-- Date: 2025-01-XX
-- Description: Creates table for task delay alerts when tasks are overdue

CREATE TABLE IF NOT EXISTS alerts_task_delay (
    id VARCHAR(36) PRIMARY KEY,
    task_id VARCHAR(36) NOT NULL,
    project_id VARCHAR(36) NOT NULL,
    user_id VARCHAR(36) NOT NULL COMMENT '通知先ユーザーID（本人またはプロジェクトリーダー）',
    user_type ENUM('assignee', 'project_leader') NOT NULL COMMENT 'ユーザータイプ（本人またはプロジェクトリーダー）',
    task_name VARCHAR(255) NOT NULL,
    assignee VARCHAR(255) COMMENT 'タスクの担当者名',
    due_date DATE COMMENT 'タスクの期限日',
    end_date DATE COMMENT 'タスクの終了予定日',
    days_delayed 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 (task_id) REFERENCES project_tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_task_user (task_id, user_id),
    INDEX idx_task (task_id),
    INDEX idx_project (project_id),
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    INDEX idx_due_date (due_date),
    INDEX idx_end_date (end_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default batch execution time (1:00 AM)
INSERT INTO batch_settings (id, setting_key, setting_value, description)
VALUES (UUID(), 'task_delay_alert_time', '01:00', 'タスク遅延アラートバッチの実行時間 (HH:mm形式)')
ON DUPLICATE KEY UPDATE setting_value = '01:00';

