-- Docker环境初始化SQL
CREATE DATABASE IF NOT EXISTS erp_task DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE erp_task;

-- 定时任务表
CREATE TABLE IF NOT EXISTS scheduled_task (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    task_name VARCHAR(100) NOT NULL,
    description VARCHAR(500),
    task_group VARCHAR(50) NOT NULL,
    cron_expression VARCHAR(100) NOT NULL,
    task_class VARCHAR(500) NOT NULL,
    method_name VARCHAR(100),
    task_params TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'DRAFT',
    concurrent BOOLEAN NOT NULL DEFAULT TRUE,
    sync BOOLEAN NOT NULL DEFAULT FALSE,
    task_type VARCHAR(20) NOT NULL DEFAULT 'BEAN',
    xxl_job_id BIGINT,
    max_retries INT NOT NULL DEFAULT 3,
    retry_interval INT NOT NULL DEFAULT 60,
    timeout INT,
    alert_emails VARCHAR(500),
    alert_phones VARCHAR(200),
    alert_enabled BOOLEAN NOT NULL DEFAULT FALSE,
    misfire_policy VARCHAR(20) DEFAULT 'DO_NOTHING',
    next_fire_time DATETIME,
    prev_fire_time DATETIME,
    execute_count BIGINT NOT NULL DEFAULT 0,
    owner VARCHAR(50),
    remark VARCHAR(500),
    tenant_id VARCHAR(50),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(50),
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updated_by VARCHAR(50),
    INDEX idx_task_name (task_name),
    INDEX idx_task_group (task_group),
    INDEX idx_task_status (status),
    INDEX idx_cron (cron_expression),
    UNIQUE KEY uk_name_group (task_name, task_group)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 任务执行日志表
CREATE TABLE IF NOT EXISTS task_execution_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    task_id BIGINT NOT NULL,
    task_name VARCHAR(100) NOT NULL,
    task_group VARCHAR(50),
    batch_no VARCHAR(64) NOT NULL,
    trigger_time DATETIME NOT NULL,
    start_time DATETIME,
    end_time DATETIME,
    status VARCHAR(20) NOT NULL,
    duration BIGINT,
    executor_node VARCHAR(50),
    result TEXT,
    error_trace TEXT,
    retry_count INT NOT NULL DEFAULT 0,
    is_retry BOOLEAN DEFAULT FALSE,
    task_params TEXT,
    tenant_id VARCHAR(50),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_log_task_id (task_id),
    INDEX idx_log_status (status),
    INDEX idx_log_start_time (start_time),
    INDEX idx_log_trigger_time (trigger_time),
    INDEX idx_batch_no (batch_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;