ai-tagging/agent/update.sql

/**
	行社白名单功能
**/
ALTER TABLE ai_tagging.aitag_tag_log ADD is_marine bit DEFAULT 0;
COMMENT ON COLUMN ai_tagging.aitag_tag_log.is_marine IS '是否匹配行社白名单，匹配上的默认隶属于海洋经济，在大模型处理阶段给予提示，没匹配上的不给提示,0:否,1:是';

-- 添加法人行社代码字段
ALTER TABLE ai_tagging.aitag_tag_log ADD instucde varchar(100);
comment on column ai_tagging.aitag_tag_log.instucde is '法人行社代码';
ALTER TABLE ai_tagging.aitag_tag_log DROP COLUMN org_code;
ALTER TABLE ai_tagging.aitag_tag_log ADD instucde_nm varchar(100);
comment on column ai_tagging.aitag_tag_log.instucde_nm is '法人行社名称';
ALTER TABLE ai_tagging.aitag_tag_log add company_nm varchar(100);
comment on column ai_tagging.aitag_tag_log.company_nm is '企业名称';
ALTER TABLE ai_tagging.aitag_tag_log add company_code varchar(100);
comment on column ai_tagging.aitag_tag_log.company_code is '企业统一社会信用代码';
 ALTER TABLE ai_tagging.aitag_tag_log add start_user_id varchar(100);
comment on column ai_tagging.aitag_tag_log.start_user_id is '发起人ID';
 ALTER TABLE ai_tagging.aitag_tag_log add start_user_nm varchar(100);
comment on column ai_tagging.aitag_tag_log.start_user_nm is '发起人名字';
 ALTER TABLE ai_tagging.aitag_tag_log add start_user_org varchar(100);
comment on column ai_tagging.aitag_tag_log.start_user_org is '发起人机构代码';
 ALTER TABLE ai_tagging.aitag_tag_log add start_user_endpoint varchar(100);
comment on column ai_tagging.aitag_tag_log.start_user_endpoint is '发起人网点';

DROP TABLE  ai_tagging.ai_tagging.aitag_org_whitelist;
-- 创建行社白名单表
CREATE TABLE ai_tagging.aitag_instucde_whitelist (
	id varchar(100),
	instucde_name varchar(100),
	instucde_code varchar(100),
	tag_type varchar(100),
	CONSTRAINT aitag_instucde_whitelist_PK PRIMARY KEY (id)
);
COMMENT ON TABLE ai_tagging.aitag_instucde_whitelist IS '行社白名单表';
COMMENT ON COLUMN ai_tagging.aitag_instucde_whitelist.tag_type IS '标签类型,marine:海洋标签';
INSERT INTO ai_tagging.aitag_instucde_whitelist (id, instucde_name, instucde_code, tag_type) VALUES ('1', '福建福州农村商业银行股份有限公司', '901020300', 'marine');
INSERT INTO ai_tagging.aitag_instucde_whitelist (id, instucde_name, instucde_code, tag_type) VALUES ('2', '福建长乐农村商业银行股份有限公司', '901060300', 'marine');
INSERT INTO ai_tagging.aitag_instucde_whitelist (id, instucde_name, instucde_code, tag_type) VALUES ('3', '福建福清汇通农村商业银行股份有限公司', '901070300', 'marine');
INSERT INTO ai_tagging.aitag_instucde_whitelist (id, instucde_name, instucde_code, tag_type) VALUES ('4', '福建平潭农村商业银行股份有限公司', '901080300', 'marine');
INSERT INTO ai_tagging.aitag_instucde_whitelist (id, instucde_name, instucde_code, tag_type) VALUES ('5', '福建连江农村商业银行股份有限公司', '901090300', 'marine');
INSERT INTO ai_tagging.aitag_instucde_whitelist (id, instucde_name, instucde_code, tag_type) VALUES ('6', '罗源县农村信用合作联社', '901100300', 'marine');
INSERT INTO ai_tagging.aitag_instucde_whitelist (id, instucde_name, instucde_code, tag_type) VALUES ('7', '厦门农村商业银行股份有限公司', '902010200', 'marine');
INSERT INTO ai_tagging.aitag_instucde_whitelist (id, instucde_name, instucde_code, tag_type) VALUES ('8', '福建莆田农村商业银行股份有限公司', '904020300', 'marine');
INSERT INTO ai_tagging.aitag_instucde_whitelist (id, instucde_name, instucde_code, tag_type) VALUES ('9', '福建宁德农村商业银行股份有限公司', '906020300', 'marine');
INSERT INTO ai_tagging.aitag_instucde_whitelist (id, instucde_name, instucde_code, tag_type) VALUES ('10', '福鼎市农村信用合作联社', '906030300', 'marine');
INSERT INTO ai_tagging.aitag_instucde_whitelist (id, instucde_name, instucde_code, tag_type) VALUES ('11', '霞浦县农村信用合作联社', '906040300', 'marine');
INSERT INTO ai_tagging.aitag_instucde_whitelist (id, instucde_name, instucde_code, tag_type) VALUES ('12', '福安市农村信用合作联社', '906050300', 'marine');
INSERT INTO ai_tagging.aitag_instucde_whitelist (id, instucde_name, instucde_code, tag_type) VALUES ('13', '泉州农村商业银行股份有限公司', '907020300', 'marine');
INSERT INTO ai_tagging.aitag_instucde_whitelist (id, instucde_name, instucde_code, tag_type) VALUES ('14', '惠安县农村信用合作联社', '907030300', 'marine');
INSERT INTO ai_tagging.aitag_instucde_whitelist (id, instucde_name, instucde_code, tag_type) VALUES ('15', '福建晋江农村商业银行股份有限公司', '907040300', 'marine');
INSERT INTO ai_tagging.aitag_instucde_whitelist (id, instucde_name, instucde_code, tag_type) VALUES ('16', '福建石狮农村商业银行股份有限公司', '907060300', 'marine');
INSERT INTO ai_tagging.aitag_instucde_whitelist (id, instucde_name, instucde_code, tag_type) VALUES ('17', '福建南安农村商业银行股份有限公司', '907070300', 'marine');
INSERT INTO ai_tagging.aitag_instucde_whitelist (id, instucde_name, instucde_code, tag_type) VALUES ('18', '福建漳州农村商业银行股份有限公司', '908020300', 'marine');
INSERT INTO ai_tagging.aitag_instucde_whitelist (id, instucde_name, instucde_code, tag_type) VALUES ('19', '福建龙海农村商业银行股份有限公司', '908030300', 'marine');
INSERT INTO ai_tagging.aitag_instucde_whitelist (id, instucde_name, instucde_code, tag_type) VALUES ('20', '云霄县农村信用合作联社', '908040300', 'marine');
INSERT INTO ai_tagging.aitag_instucde_whitelist (id, instucde_name, instucde_code, tag_type) VALUES ('21', '福建漳浦农村商业银行股份有限公司', '908050300', 'marine');
INSERT INTO ai_tagging.aitag_instucde_whitelist (id, instucde_name, instucde_code, tag_type) VALUES ('22', '诏安县农村信用合作联社', '908060300', 'marine');
INSERT INTO ai_tagging.aitag_instucde_whitelist (id, instucde_name, instucde_code, tag_type) VALUES ('23', '东山县农村信用合作联社', '908080300', 'marine');

/**
 预设规则的匹配，可以不用人工确认，直接通过推送给画像系统
**/

update ai_tagging.ai_tagging.aitag_tag_category set category_code = 'marine' where id = 'f47ac10b-58cc-4372-a567-0e02b2c3d479' ;

CREATE TABLE ai_tagging.aitag_predefined_rules (
	id varchar(100) NOT NULL,
	defined_rule varchar(100),
	tag_type varchar(100),
	tag_nm varchar(100),
	CONSTRAINT aitag_predefined_rules_PK PRIMARY KEY (id)
);

COMMENT ON COLUMN ai_tagging.aitag_predefined_rules.defined_rule is '预设关键字';
COMMENT ON COLUMN ai_tagging.aitag_predefined_rules.tag_type is '规则所属标签体系,对应aitag_tag_category的category_code字段';
COMMENT ON COLUMN ai_tagging.aitag_predefined_rules.tag_nm is '预设关键字对应的标签名称，默认在同一个体系下标签名称不重复，用ID或者代码不直观，所以用名称';

INSERT INTO ai_tagging.aitag_predefined_rules (id, defined_rule, tag_type, tag_nm) VALUES
('rule_01', '海水养殖', 'marine', '海水养殖'),
('rule_02', '海水捕捞', 'marine', '海洋捕捞'),
('rule_03', '海洋石油开采', 'marine', '海洋石油和天然气开采'),
('rule_04', '海洋天然气及可燃冰开采', 'marine', '海洋石油和天然气开采'),
('rule_05', '海洋工程装备制造', 'marine', '海洋工程装备制造业'),
('rule_06', '深海石油钻探设备制造', 'marine', '海洋油气资源勘探开发装备制造及修理'),
('rule_07', '海洋油气资源开发利用工程建筑', 'marine', '海上工程建筑'),
('rule_08', '海洋能源开发利用工程建筑', 'marine', '海上工程建筑'),
('rule_09', '海底隧道工程建筑', 'marine', '海底工程建筑'),
('rule_10', '海底设施铺设工程建筑', 'marine', '海底工程建筑'),
('rule_11', '其他海洋工程建筑', 'marine', '海上工程建筑'),
('rule_12', '港口及航运设施工程建筑', 'marine', '近岸工程建筑'),
('rule_13', '海水淡化处理', 'marine', '海水淡化'),
('rule_14', '海上旅客运输', 'marine', '海洋交通运输业'),
('rule_15', '客运轮渡运输', 'marine', '海洋交通运输业'),
('rule_16', '远洋货物运输', 'marine', '海洋交通运输业'),
('rule_17', '沿海货物运输', 'marine', '海洋交通运输业'),
('rule_18', '海底管道运输', 'marine', '海洋交通运输业'),
('rule_19', '海洋气象服务', 'marine', '海洋自然科学研究和试验发展'),
('rule_20', '海洋环境服务', 'marine', '海洋信息集成服务'),
('rule_21', '其他海洋服务', 'marine', '海洋技术服务');

/**
	企业白名单
**/
CREATE TABLE ai_tagging.aitag_company_whitelist (
	id varchar(100) NOT NULL,
	tag_name varchar(100),
	tag_code varchar(100),
	is_valid int,
	create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT aitag_company_whitelist_pk PRIMARY KEY (id)
);
COMMENT ON TABLE  ai_tagging.aitag_company_whitelist IS '企业白名单';
COMMENT ON COLUMN ai_tagging.aitag_company_whitelist.tag_name is '标签名称';
COMMENT ON COLUMN ai_tagging.aitag_company_whitelist.tag_code is '标签代码';
COMMENT ON COLUMN ai_tagging.aitag_company_whitelist.is_valid is '0启用；1停用';

/**
	企业列表
**/
CREATE TABLE ai_tagging.aitag_company_whitelist_detail(
	id varchar(100) NOT NULL,
	aitag_company_whitelist_id varchar(100),
	tag_code varchar(100),
	company_name varchar(100),
	company_code varchar(100),
	create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT aitag_company_whitelist_detail_pk PRIMARY KEY(id)
);
COMMENT ON TABLE  ai_tagging.aitag_company_whitelist_detail IS '企业白名单明细';
COMMENT ON COLUMN ai_tagging.aitag_company_whitelist_detail.aitag_company_whitelist_id is '外键';
COMMENT ON COLUMN ai_tagging.aitag_company_whitelist_detail.tag_code is '标签代码';
COMMENT ON COLUMN ai_tagging.aitag_company_whitelist_detail.company_name is '企业名称';
COMMENT ON COLUMN ai_tagging.aitag_company_whitelist_detail.company_code is '企业统一社会信用代码';