-- Phase 3 TaskInstance persistence (Session JSON projection). Do not DROP.

CREATE TABLE `ai_task_instance`
(
    `id`                VARCHAR(32)  NOT NULL COMMENT '主键=task_session_id',
    `mac_address`       VARCHAR(50)  DEFAULT NULL COMMENT '设备MAC',
    `agent_id`          VARCHAR(32)  DEFAULT NULL COMMENT '智能体id，可空',
    `ws_session_id`     VARCHAR(64)  DEFAULT NULL COMMENT 'WS连接级session_id',
    `app_id`            VARCHAR(32)  NOT NULL COMMENT 'App唯一名',
    `task_id`           VARCHAR(32)  NOT NULL COMMENT 'Task id',
    `source`            VARCHAR(24)  DEFAULT NULL COMMENT 'voice/mqtt_immediate/schedule',
    `interaction_mode`  VARCHAR(32)  DEFAULT NULL COMMENT 'SHADOWING/PLAY_AUDIO/...',
    `status`            VARCHAR(16)  NOT NULL DEFAULT 'running' COMMENT 'running/completed/cancelled',
    `end_reason`        VARCHAR(32)  DEFAULT NULL COMMENT 'finished/user_exit/disconnected/...',
    `started_at`        BIGINT       DEFAULT NULL COMMENT 'epoch ms',
    `ended_at`          BIGINT       DEFAULT NULL COMMENT 'epoch ms',
    `created_at`        DATETIME(3)  DEFAULT CURRENT_TIMESTAMP(3) NOT NULL COMMENT '创建时间',
    `updated_at`        DATETIME(3)  DEFAULT CURRENT_TIMESTAMP(3) NOT NULL ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
    PRIMARY KEY (`id`),
    KEY `idx_task_instance_mac_started` (`mac_address`, `started_at`),
    KEY `idx_task_instance_app_task_started` (`app_id`, `task_id`, `started_at`),
    KEY `idx_task_instance_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Task实例（跟读/复读会话）';

CREATE TABLE `ai_task_step`
(
    `id`             BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键',
    `instance_id`    VARCHAR(32)  NOT NULL COMMENT 'ai_task_instance.id',
    `step_index`     INT          NOT NULL COMMENT 'turns[].index',
    `prompt_text`    TEXT         COMMENT '范读文本或SD路径',
    `asr_text`       TEXT         COMMENT '用户ASR',
    `echo_rejected`  TINYINT(1)   NOT NULL DEFAULT 0 COMMENT '是否回声拒绝',
    `audio_id`       VARCHAR(32)  DEFAULT NULL COMMENT 'ai_agent_chat_audio.id',
    `recorded_at`    BIGINT       DEFAULT NULL COMMENT 'epoch ms',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_task_step_instance_index` (`instance_id`, `step_index`),
    KEY `idx_task_step_audio` (`audio_id`),
    CONSTRAINT `fk_task_step_instance` FOREIGN KEY (`instance_id`) REFERENCES `ai_task_instance` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Task步骤（跟读一轮）';

CREATE TABLE `ai_task_event`
(
    `id`           BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键',
    `instance_id`  VARCHAR(32)  NOT NULL COMMENT 'ai_task_instance.id',
    `name`         VARCHAR(64)  NOT NULL COMMENT 'audio.completed/audio.stopped/listen_timeout/...',
    `app_id`       VARCHAR(32)  DEFAULT NULL,
    `task_id`      VARCHAR(32)  DEFAULT NULL,
    `at_ms`        BIGINT       DEFAULT NULL COMMENT 'JSON at，epoch ms',
    PRIMARY KEY (`id`),
    KEY `idx_task_event_instance` (`instance_id`),
    CONSTRAINT `fk_task_event_instance` FOREIGN KEY (`instance_id`) REFERENCES `ai_task_instance` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Task事件';