-- Workflow 编排核心表。

CREATE TABLE IF NOT EXISTS workflow_definition (
    id BIGSERIAL PRIMARY KEY,
    project_id BIGINT,
    workflow_code VARCHAR(100) NOT NULL,
    workflow_name VARCHAR(200) NOT NULL,
    description VARCHAR(1000),
    bound_agent_id BIGINT,
    status VARCHAR(50) NOT NULL DEFAULT 'DRAFT',
    version INTEGER NOT NULL DEFAULT 1,
    create_time TIMESTAMP,
    update_time TIMESTAMP,
    remark VARCHAR(500) DEFAULT '',
    create_by VARCHAR(64),
    update_by VARCHAR(64),
    CONSTRAINT uk_workflow_definition_code UNIQUE (workflow_code),
    CONSTRAINT fk_workflow_definition_project_id FOREIGN KEY (project_id) REFERENCES studio_project (id),
    CONSTRAINT fk_workflow_definition_agent_id FOREIGN KEY (bound_agent_id) REFERENCES agent_definition (id)
);

CREATE TABLE IF NOT EXISTS workflow_version (
    id BIGSERIAL PRIMARY KEY,
    workflow_id BIGINT NOT NULL,
    version_no INTEGER NOT NULL,
    snapshot_name VARCHAR(100) NOT NULL,
    graph_json JSONB NOT NULL DEFAULT '{}'::jsonb,
    publish_status VARCHAR(50) NOT NULL DEFAULT 'DRAFT',
    published_time TIMESTAMP,
    version INTEGER NOT NULL DEFAULT 1,
    create_time TIMESTAMP,
    update_time TIMESTAMP,
    remark VARCHAR(500) DEFAULT '',
    create_by VARCHAR(64),
    update_by VARCHAR(64),
    CONSTRAINT uk_workflow_version_no UNIQUE (workflow_id, version_no),
    CONSTRAINT fk_workflow_version_workflow_id FOREIGN KEY (workflow_id) REFERENCES workflow_definition (id)
);

CREATE TABLE IF NOT EXISTS workflow_run (
    id BIGSERIAL PRIMARY KEY,
    request_id VARCHAR(64) NOT NULL,
    workflow_id BIGINT,
    workflow_version_id BIGINT,
    agent_id BIGINT,
    run_source VARCHAR(50) NOT NULL,
    status VARCHAR(50) NOT NULL,
    input_json JSONB NOT NULL DEFAULT '{}'::jsonb,
    output_json JSONB NOT NULL DEFAULT '{}'::jsonb,
    duration_ms INTEGER,
    estimated_cost NUMERIC(14, 8),
    version INTEGER NOT NULL DEFAULT 1,
    create_time TIMESTAMP,
    update_time TIMESTAMP,
    remark VARCHAR(500) DEFAULT '',
    create_by VARCHAR(64),
    update_by VARCHAR(64),
    CONSTRAINT uk_workflow_run_request_id UNIQUE (request_id),
    CONSTRAINT fk_workflow_run_workflow_id FOREIGN KEY (workflow_id) REFERENCES workflow_definition (id),
    CONSTRAINT fk_workflow_run_version_id FOREIGN KEY (workflow_version_id) REFERENCES workflow_version (id),
    CONSTRAINT fk_workflow_run_agent_id FOREIGN KEY (agent_id) REFERENCES agent_definition (id)
);

CREATE TABLE IF NOT EXISTS workflow_run_step (
    id BIGSERIAL PRIMARY KEY,
    run_id BIGINT NOT NULL,
    node_id VARCHAR(100) NOT NULL,
    node_type VARCHAR(50) NOT NULL,
    node_name VARCHAR(200),
    status VARCHAR(50) NOT NULL,
    input_json JSONB NOT NULL DEFAULT '{}'::jsonb,
    output_json JSONB NOT NULL DEFAULT '{}'::jsonb,
    duration_ms INTEGER,
    error_message VARCHAR(1000),
    version INTEGER NOT NULL DEFAULT 1,
    create_time TIMESTAMP,
    update_time TIMESTAMP,
    remark VARCHAR(500) DEFAULT '',
    create_by VARCHAR(64),
    update_by VARCHAR(64),
    CONSTRAINT fk_workflow_run_step_run_id FOREIGN KEY (run_id) REFERENCES workflow_run (id)
);

CREATE INDEX IF NOT EXISTS idx_workflow_definition_project_id ON workflow_definition (project_id);
CREATE INDEX IF NOT EXISTS idx_workflow_definition_status ON workflow_definition (status);
CREATE INDEX IF NOT EXISTS idx_workflow_version_workflow_id ON workflow_version (workflow_id);
CREATE INDEX IF NOT EXISTS idx_workflow_run_workflow_id ON workflow_run (workflow_id);
CREATE INDEX IF NOT EXISTS idx_workflow_run_status ON workflow_run (status);
CREATE INDEX IF NOT EXISTS idx_workflow_run_step_run_id ON workflow_run_step (run_id);

COMMENT ON TABLE workflow_definition IS 'Workflow定义表';
COMMENT ON COLUMN workflow_definition.id IS 'ID';
COMMENT ON COLUMN workflow_definition.project_id IS '所属项目ID';
COMMENT ON COLUMN workflow_definition.workflow_code IS 'Workflow编码';
COMMENT ON COLUMN workflow_definition.workflow_name IS 'Workflow名称';
COMMENT ON COLUMN workflow_definition.description IS 'Workflow描述';
COMMENT ON COLUMN workflow_definition.bound_agent_id IS '绑定Agent ID';
COMMENT ON COLUMN workflow_definition.status IS '状态';
COMMENT ON COLUMN workflow_definition.version IS '版本';
COMMENT ON COLUMN workflow_definition.create_time IS '创建时间';
COMMENT ON COLUMN workflow_definition.update_time IS '更新时间';
COMMENT ON COLUMN workflow_definition.remark IS '备注';
COMMENT ON COLUMN workflow_definition.create_by IS '创建者';
COMMENT ON COLUMN workflow_definition.update_by IS '更新者';
COMMENT ON TABLE workflow_version IS 'Workflow版本快照表';
COMMENT ON COLUMN workflow_version.id IS 'ID';
COMMENT ON COLUMN workflow_version.workflow_id IS 'Workflow定义ID';
COMMENT ON COLUMN workflow_version.version_no IS '版本号';
COMMENT ON COLUMN workflow_version.snapshot_name IS '快照名称';
COMMENT ON COLUMN workflow_version.graph_json IS '编排图JSON';
COMMENT ON COLUMN workflow_version.publish_status IS '发布状态';
COMMENT ON COLUMN workflow_version.published_time IS '发布时间';
COMMENT ON COLUMN workflow_version.version IS '版本';
COMMENT ON COLUMN workflow_version.create_time IS '创建时间';
COMMENT ON COLUMN workflow_version.update_time IS '更新时间';
COMMENT ON COLUMN workflow_version.remark IS '备注';
COMMENT ON COLUMN workflow_version.create_by IS '创建者';
COMMENT ON COLUMN workflow_version.update_by IS '更新者';
COMMENT ON TABLE workflow_run IS 'Workflow运行记录表';
COMMENT ON COLUMN workflow_run.id IS 'ID';
COMMENT ON COLUMN workflow_run.request_id IS '请求唯一ID';
COMMENT ON COLUMN workflow_run.workflow_id IS 'Workflow定义ID';
COMMENT ON COLUMN workflow_run.workflow_version_id IS 'Workflow版本ID';
COMMENT ON COLUMN workflow_run.agent_id IS '执行Agent ID';
COMMENT ON COLUMN workflow_run.run_source IS '运行来源';
COMMENT ON COLUMN workflow_run.status IS '运行状态';
COMMENT ON COLUMN workflow_run.input_json IS '输入参数JSON';
COMMENT ON COLUMN workflow_run.output_json IS '输出结果JSON';
COMMENT ON COLUMN workflow_run.duration_ms IS '耗时(毫秒)';
COMMENT ON COLUMN workflow_run.estimated_cost IS '预估成本';
COMMENT ON COLUMN workflow_run.version IS '版本';
COMMENT ON COLUMN workflow_run.create_time IS '创建时间';
COMMENT ON COLUMN workflow_run.update_time IS '更新时间';
COMMENT ON COLUMN workflow_run.remark IS '备注';
COMMENT ON COLUMN workflow_run.create_by IS '创建者';
COMMENT ON COLUMN workflow_run.update_by IS '更新者';
COMMENT ON TABLE workflow_run_step IS 'Workflow运行步骤表';
COMMENT ON COLUMN workflow_run_step.id IS 'ID';
COMMENT ON COLUMN workflow_run_step.run_id IS '运行记录ID';
COMMENT ON COLUMN workflow_run_step.node_id IS '节点ID';
COMMENT ON COLUMN workflow_run_step.node_type IS '节点类型';
COMMENT ON COLUMN workflow_run_step.node_name IS '节点名称';
COMMENT ON COLUMN workflow_run_step.status IS '步骤状态';
COMMENT ON COLUMN workflow_run_step.input_json IS '步骤输入JSON';
COMMENT ON COLUMN workflow_run_step.output_json IS '步骤输出JSON';
COMMENT ON COLUMN workflow_run_step.duration_ms IS '耗时(毫秒)';
COMMENT ON COLUMN workflow_run_step.error_message IS '错误信息';
COMMENT ON COLUMN workflow_run_step.version IS '版本';
COMMENT ON COLUMN workflow_run_step.create_time IS '创建时间';
COMMENT ON COLUMN workflow_run_step.update_time IS '更新时间';
COMMENT ON COLUMN workflow_run_step.remark IS '备注';
COMMENT ON COLUMN workflow_run_step.create_by IS '创建者';
COMMENT ON COLUMN workflow_run_step.update_by IS '更新者';