playlet/sql/secretary/secretary.sql

create table account_detail
(
    id          bigint auto_increment
        primary key,
    ssid        varchar(64) not null comment 'ssid',
    type        int         null comment '类型 0新用户赠送 1充值 2退款 3生成谈话消耗 4大文件对话消耗 5大文件生成总结消耗',
    value       bigint      null comment '值',
    create_time datetime    null comment '创建时间'
)
    comment '账单明细表';

create index idx_ssid
    on account_detail (ssid);

create table agenda
(
    id          bigint auto_increment
        primary key,
    talk_id     varchar(64)   not null comment '谈话id',
    ssid        varchar(64)   not null comment '用户ssid',
    name        varchar(255)  null comment '负责人',
    content     varchar(1000) null comment '待办内容',
    is_todo     int default 0 null comment '我的待办 0否 1是',
    is_deleted  int default 0 null comment '是否删除 0否 1是',
    is_finish   int default 0 null comment '是否完成待办 0否 1是',
    is_example  int default 0 null comment '是否是示例 0否 1是',
    status      int           null comment '状态',
    create_time datetime      null comment '创建时间',
    update_time datetime      null comment '更新时间'
)
    comment '待办事项表';

create index idx_ssid
    on agenda (ssid);

create index idx_talk_iid
    on agenda (talk_id);

create index idx_update_time
    on agenda (update_time);

create table audio
(
    talk_id        varchar(64)                not null comment '谈话id'
        primary key,
    task_id        varchar(64)                null comment '语音任务id',
    audio_url      varchar(255)               null comment '音频地址',
    audio_duration double(11, 2) default 0.00 null comment '音频时长',
    result_detail  json                       null comment '识别详情',
    status         int                        null comment '状态',
    create_time    datetime                   null comment '创建时间'
)
    comment '语音识别记录表';

create index idx_talk_id
    on audio (talk_id);

create table chat_info
(
    id          bigint auto_increment
        primary key,
    ssid        varchar(64)   not null comment 'ssid',
    chat_id     varchar(64)   null comment '对话id,用于关联上下文',
    role        varchar(32)   null comment '角色 user：用户 assistant：AI',
    content     varchar(8000) null comment '聊天内容',
    talk_id     varchar(64)   null comment '谈话id',
    agenda_id   bigint        null comment '待办id',
    status      int           null comment '状态',
    create_time datetime      null comment '创建时间'
)
    comment '对话信息表';

create index idx_create_time
    on chat_info (create_time);

create index idx_ssid
    on chat_info (ssid);

create table complaint
(
    id          bigint auto_increment
        primary key,
    ssid        varchar(64)   not null,
    device_id   varchar(64)   null,
    uid         bigint        null,
    type        int           null comment '类型 1 建议 2投诉',
    reason      varchar(255)  null,
    content     varchar(500)  not null,
    phone       varchar(255)  null,
    images      varchar(1000) null,
    create_time datetime      not null,
    update_time datetime      not null,
    status      int           not null
)
    comment '投诉建议表';

create table folder
(
    id          bigint auto_increment
        primary key,
    ssid        varchar(64)  not null comment 'ssid',
    name        varchar(255) null comment '文件夹名称',
    status      int          null comment '状态',
    create_time datetime     null comment '创建时间',
    update_time datetime     null comment '更新时间'
)
    comment '文件夹记录表';

create index idx_ssid
    on folder (ssid);

create index idx_update_time
    on folder (update_time);

create table folder_ref_talk
(
    id          bigint auto_increment
        primary key,
    folder_id   bigint      not null,
    talk_id     varchar(64) not null,
    create_time datetime    null,
    update_time datetime    null
)
    comment '文件夹关联谈话表';

create index idx_folder_id
    on folder_ref_talk (folder_id);

create index idx_talk_id
    on folder_ref_talk (talk_id);

create table member_auth
(
    id              bigint auto_increment
        primary key,
    ssid            varchar(64)  not null comment 'ssid',
    out_trade_no    varchar(64)  null comment '关联订单号',
    auth            varchar(255) null comment '权益',
    auth_value      bigint       null comment '权益值',
    start_timestamp bigint       null comment '开始时间',
    end_timestamp   bigint       null comment '结束时间',
    create_time     datetime     null comment '创建时间',
    update_time     datetime     null comment '更新时间'
)
    comment '会员权益表';

create index idx_out_trade_no
    on member_auth (out_trade_no);

create index idx_ssid
    on member_auth (ssid);

create table order_info
(
    id              bigint auto_increment
        primary key,
    out_trade_no    varchar(128) not null,
    ssid            varchar(64)  not null,
    device_id       varchar(64)  not null,
    phone           varchar(64)  null,
    item_id         bigint       not null,
    item_name       varchar(255) not null,
    original_amount bigint       not null,
    amount          bigint       not null,
    app_platform    int          not null,
    package_name    varchar(255) not null,
    channel_name    varchar(255) null,
    ip              varchar(255) null,
    create_time     datetime     not null,
    update_time     datetime     not null,
    pay_status      int          not null comment '订单支付状态 0:已创建 1:未支付 2:已支付 3:已关闭 4:已退款',
    status          int          not null
)
    comment '订单信息表';

create index out_trade_no_index
    on order_info (out_trade_no);

create table risk_check_log
(
    id               int auto_increment
        primary key,
    user_id          varchar(64) null,
    phone            varchar(32) null,
    source_type      varchar(16) null,
    content_type     varchar(16) null,
    content          mediumtext  null,
    response_code    varchar(16) null,
    response_message varchar(64) null,
    risk_level       varchar(32) null,
    risk_level_msg   varchar(64) null,
    create_time      datetime    null,
    ip               varchar(64) null,
    data             mediumtext  null
)
    comment '风险检测表';

create table talk_generate_log
(
    task_id     varchar(64)   not null
        primary key,
    user_id     varchar(64)   null,
    ssid        varchar(64)   null,
    device_id   varchar(64)   null,
    phone       varchar(32)   null,
    talk_id     varchar(64)   null,
    start_time  datetime      null,
    send_time   datetime      null,
    handle_time datetime      null,
    finish_time datetime      null,
    fail_reason varchar(5000) null
)
    comment '谈话生成日志表';

create index idx_start_time
    on talk_generate_log (start_time);

create table talk_info
(
    id                varchar(64)                not null
        primary key,
    task_id           varchar(64)                null,
    ssid              varchar(64)                not null,
    title             varchar(255)               null comment '标题',
    summary           varchar(8000)              null comment '总结',
    duration          double(11, 2) default 0.00 not null comment '时长',
    characters        bigint        default 0    null comment '文件字符数',
    audio_url         varchar(255)               null comment '音频地址',
    original_file_url varchar(500)               null comment '原文文件地址',
    summary_file_url  varchar(255)               null comment '总结文件地址',
    detail            json                       null comment '谈话详情',
    audio_status      int                        null comment '语音生成状态 0等待 1生成中 2成功 3失败',
    is_deleted        int           default 0    null comment '是否删除 0否 1是',
    is_example        int           default 0    null comment '是否是示例 0否 1是',
    status            int                        null comment '状态',
    template_id       bigint        default 0    null comment '引用的模板id',
    create_time       datetime                   null comment '创建时间',
    update_time       datetime                   null comment '更新时间'
)
    comment '谈话信息表';

create index idx_create_time
    on talk_info (create_time);

create index idx_ssid
    on talk_info (ssid);

create index idx_update_time
    on talk_info (update_time);

create table talk_summary
(
    id          bigint auto_increment
        primary key,
    ssid        varchar(64)   null comment 'ssid',
    talk_id     varchar(64)   null comment '谈话id',
    template_id bigint        null comment '模板id',
    summary     varchar(8000) null comment '总结',
    status      int           null comment '状态',
    create_time datetime      null comment '创建时间',
    update_time datetime      null comment '更新时间'
)
    comment '谈话总结记录表';

create index idx_talk_id
    on talk_summary (talk_id);

create index idx_template_id
    on talk_summary (template_id);

create table template
(
    id          bigint auto_increment
        primary key,
    ssid        varchar(64)   not null comment '用户ssid',
    name        varchar(50)   null comment '模板名称',
    title       varchar(2000) null comment '标题列表',
    status      int           null comment '状态',
    create_time datetime      null comment '创建时间',
    update_time datetime      null comment '更新时间'
)
    comment '模板记录表';

create index idx_create_time
    on template (create_time);

create index idx_ssid
    on template (ssid);

create table upload_log
(
    id                bigint auto_increment
        primary key,
    ssid              varchar(64)   not null,
    cdn_host          varchar(255)  not null,
    file_key          varchar(1000) not null,
    original_filename varchar(255)  null,
    ip                varchar(255)  null,
    create_time       datetime      not null,
    update_time       datetime      not null,
    status            int           not null
)
    comment '上传日志表';

create index ssid_index
    on upload_log (ssid);

create table user
(
    ssid        varchar(64) not null
        primary key,
    profession  varchar(32) null comment '行业',
    post        varchar(32) null comment '岗位',
    create_time datetime    null comment '创建时间',
    update_time datetime    null comment '更新时间'
)
    comment '用户信息表';

create index idx_ssid
    on user (ssid);