-- 2026-01-04
ALTER TABLE cc_ivr ADD COLUMN tts_text_wav VARCHAR(255) DEFAULT '' COMMENT 'the wav audio file for tts_text';
ALTER TABLE cc_ivr ADD COLUMN press_key_invalid_tips_wav VARCHAR(255) DEFAULT '' COMMENT 'the wav path for  press_key_invalid_tips';
ALTER TABLE cc_ivr ADD COLUMN hangup_tips_wav VARCHAR(255) DEFAULT '' COMMENT 'the wav path for  hangup tips.';

-- 2026-01-09
ALTER TABLE cc_gateways MODIFY COLUMN caller VARCHAR(3000) COMMENT '外呼的主叫号码';

-- 2026-01-14
ALTER TABLE `cc_inbound_cdr` ADD COLUMN hangup_cause VARCHAR(500) DEFAULT '' COMMENT '挂机原因';
ALTER TABLE `cc_call_phone` MODIFY COLUMN hangup_cause VARCHAR(500) DEFAULT '' COMMENT '挂机原因';


-- 2026-01-19
DROP TABLE IF EXISTS cc_llm_kb;
CREATE TABLE `cc_llm_kb` (
                             `id` int(11) NOT NULL AUTO_INCREMENT,
                             `title` varchar(1024) NOT NULL COMMENT 'kb title',
                             `content` text NOT NULL COMMENT '答案',
                             `cat_id` int(11) NOT NULL COMMENT 'kb cat id.',
                             PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS cc_llm_kb_cat;
CREATE TABLE `cc_llm_kb_cat` (
                                 `id` INT NOT NULL AUTO_INCREMENT COMMENT '主键id',
                                 `cat` VARCHAR(255) DEFAULT NULL COMMENT '分类',
                                 `description` VARCHAR(255) DEFAULT NULL COMMENT '描述',
                                 PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 菜单 SQL
INSERT INTO sys_menu (menu_id, menu_name, menu_code, parent_id, order_num, url, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES('3088', '知识库管理', 'kbcat', '2000', '6', '/aicall/kbcat', 'C', '0', 'aicall:kbcat:view', '#', 'admin', SYSDATE(), '', NULL, '知识库菜单');

-- 按钮 SQL
INSERT INTO sys_menu (menu_id, menu_name, menu_code, parent_id, order_num, url, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES('3089', '知识库查询', 'kbcatQuery', '3088', '1',  '#',  'F', '0', 'aicall:kbcat:list',         '#', 'admin', SYSDATE(), '', NULL, '');

INSERT INTO sys_menu (menu_id, menu_name, menu_code, parent_id, order_num, url, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES('3090', '知识库新增', 'kbcatAdd', '3088', '2',  '#',  'F', '0', 'aicall:kbcat:add',          '#', 'admin', SYSDATE(), '', NULL, '');

INSERT INTO sys_menu (menu_id, menu_name, menu_code, parent_id, order_num, url, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES('3091', '知识库修改', 'kbcatEdit', '3088', '3',  '#',  'F', '0', 'aicall:kbcat:edit',         '#', 'admin', SYSDATE(), '', NULL, '');

INSERT INTO sys_menu (menu_id, menu_name, menu_code, parent_id, order_num, url, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES('3092', '知识库删除', 'kbcatDel', '3088', '4',  '#',  'F', '0', 'aicall:kbcat:remove',       '#', 'admin', SYSDATE(), '', NULL, '');

INSERT INTO sys_menu (menu_id, menu_name, menu_code, parent_id, order_num, url, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES('3093', '知识库内容查询', 'kbcontentQuery', '3088', '1',  '#',  'F', '0', 'aicall:kbcontent:list',         '#', 'admin', SYSDATE(), '', NULL, '');

INSERT INTO sys_menu (menu_id, menu_name, menu_code, parent_id, order_num, url, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES('3094', '知识库内容新增', 'kbcontentAdd', '3088', '2',  '#',  'F', '0', 'aicall:kbcontent:add',          '#', 'admin', SYSDATE(), '', NULL, '');

INSERT INTO sys_menu (menu_id, menu_name, menu_code, parent_id, order_num, url, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES('3095', '知识库内容修改', 'kbcontentEdit', '3088', '3',  '#',  'F', '0', 'aicall:kbcontent:edit',         '#', 'admin', SYSDATE(), '', NULL, '');

INSERT INTO sys_menu (menu_id, menu_name, menu_code, parent_id, order_num, url, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES('3096', '知识库内容删除', 'kbcontentDel', '3088', '4',  '#',  'F', '0', 'aicall:kbcontent:remove',       '#', 'admin', SYSDATE(), '', NULL, '');


-- 2026-01-20
ALTER TABLE cc_params ADD COLUMN allow_edit INT(1) DEFAULT 1 COMMENT '是否允许编辑';
UPDATE cc_params SET allow_edit = 0 WHERE param_code IN ('aliyun-tts-account-json', 'doubao-tts-account-json', 'system_license_info');

INSERT INTO sys_menu (menu_id, menu_name, menu_code, parent_id, order_num, url, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES('4000', '授权管理', 'licenseconf', '2000', '8', '/cc/fsconf/licenseconf', 'C', '0', 'cc:licenseconf:view', '#', 'admin', SYSDATE(), '', NULL, '授权管理');


-- 2026-01-26
ALTER TABLE cc_inbound_llm_account ADD COLUMN ivr_id VARCHAR(32) DEFAULT '' COMMENT 'IVR id';
ALTER TABLE cc_inbound_llm_account ADD COLUMN satisf_survey_ivr_id VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'the IVR menu ID for satisfaction surveys';

insert  into `cc_params`(`id`,`param_name`,`param_code`,`param_value`,`param_type`,`hide_value`,`allow_edit`) values (95,'system license info','system_license_info','{\"concurrency\":10,\"expireDate\":4087324800000,\"modules\":\"aliyun_tts,funasr,aliyun_asr,chinatelecom_asr,chinatelecom_tts,doubao_vcl_tts\",\"updateTime\":1769047682619}','sys',0,0);
insert  into `cc_params`(`id`,`param_name`,`param_code`,`param_value`,`param_type`,`hide_value`,`allow_edit`) values (97,'system_hw_fingerprint_cmd','system_hw_fingerprint_cmd','docker exec -i freeswitch-debian12 /usr/local/freeswitchvideo/bin/hw_fingerprint','sys',0,1);

-- 2026-01-28
ALTER TABLE cc_inbound_llm_account ADD COLUMN inbound_alias VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'inbound alias';
UPDATE cc_inbound_llm_account SET inbound_alias = callee WHERE inbound_alias = '';

-- 2026-02-02
ALTER TABLE cc_llm_agent_account ADD COLUMN kb_cat_id INT NOT NULL DEFAULT -1 COMMENT 'kb_cat_id';

-- 2026-02-03
DROP TABLE IF EXISTS `cc_outbound_cdr`;
CREATE TABLE `cc_outbound_cdr` (
                                   `id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
                                   `caller` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'caller',
                                   `opnum` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '工号',
                                   `callee` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'callee',
                                   `start_time` bigint NOT NULL COMMENT 'outbound call start time',
                                   `answered_time` bigint NOT NULL DEFAULT '0' COMMENT 'callee answerd time',
                                   `end_time` bigint NOT NULL COMMENT 'hangup time',
                                   `uuid` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'call uuid',
                                   `call_type` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'audio' COMMENT ' audio or  video',
                                   `time_len` int NOT NULL COMMENT 'duration of call session',
                                   `time_len_valid` int NOT NULL COMMENT 'duration of call session since callee answered.',
                                   `record_filename` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'record filename',
                                   `chat_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Dialogue content',
                                   `hangup_cause` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'hangup cause',
                                   PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='call detail records for outbound calls.';

-- 2026-02-05
ALTER TABLE cc_inbound_cdr ADD COLUMN manual_answered_time BIGINT(20) DEFAULT 0 COMMENT 'manual agent answered time.';
ALTER TABLE cc_inbound_cdr ADD COLUMN manual_answered_time_len BIGINT(20) DEFAULT 0 COMMENT 'The duration of the manual agent service time.';

ALTER TABLE cc_call_phone ADD COLUMN manual_answered_time BIGINT(20) DEFAULT 0 COMMENT 'manual agent answered time.';
ALTER TABLE cc_call_phone ADD COLUMN manual_answered_time_len BIGINT(20) DEFAULT 0 COMMENT 'The duration of the manual agent service time.';


-- 2026-02-09
CREATE TABLE his_cc_call_phone LIKE cc_call_phone;
CREATE TABLE his_cc_call_task LIKE cc_call_task;
INSERT INTO `cc_params` VALUES ('98', 'netdata服务器监控数据api地址', 'netdata_api_base', 'http://192.168.67.238:19999/api/v1/data', 'sys', '0', '1');
INSERT INTO `cc_params` VALUES ('99', '间隔多少天允许删除任务及通话记录', 'callTask_allowDel_days', '3', 'sys', '0', '1');
INSERT INTO sys_menu (menu_id, menu_name, menu_code, parent_id, order_num, url, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES('3037', '外呼任务删除', 'callTaskDel', '3030', '7',  '#',  'F', '0', 'aicall:callTask:remove',       '#', 'admin', SYSDATE(), '', NULL, '');
INSERT INTO sys_menu (menu_id, menu_name, menu_code, parent_id, order_num, url, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark)
VALUES('2064', '线路配置删除', 'gatewaysDel', '2060', '4',  '#',  'F', '0', 'cc:gateways:remove',       '#', 'admin', SYSDATE(), '', NULL, '');

-- 2026-02-17
update sys_config set config_value='v20260217' where config_key='sys.version';

