/* Nacos 初始化 SQL For Kingbase (人大金仓) / PostgreSQL
   修改记录：已删除 encrypted_data_key 字段以解决兼容性报错
*/

/******************************************/
/* 表名称 = config_info                 */
/******************************************/
CREATE TABLE config_info (
  id bigserial PRIMARY KEY, 
  data_id varchar(255) NOT NULL, 
  group_id varchar(128), 
  content text NOT NULL, 
  md5 varchar(32), 
  gmt_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  gmt_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  src_user text, 
  src_ip varchar(50), 
  app_name varchar(128), 
  tenant_id varchar(128) DEFAULT '', 
  c_desc varchar(256), 
  c_use varchar(64), 
  effect varchar(64), 
  type varchar(64), 
  c_schema text
);

-- 唯一约束（data_id, group_id, tenant_id）
ALTER TABLE config_info ADD CONSTRAINT uk_configinfo_datagrouptenant UNIQUE (data_id, group_id, tenant_id);

-- 表注释
COMMENT ON TABLE config_info IS 'config_info';

/******************************************/
/* 表名称 = config_info_gray            */
/******************************************/
CREATE TABLE config_info_gray (
  id bigserial PRIMARY KEY, 
  data_id varchar(255) NOT NULL, 
  group_id varchar(128) NOT NULL, 
  content text NOT NULL, 
  md5 varchar(32), 
  src_user text, 
  src_ip varchar(100), 
  gmt_create timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), 
  gmt_modified timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), 
  app_name varchar(128), 
  tenant_id varchar(128) DEFAULT '', 
  gray_name varchar(128) NOT NULL, 
  gray_rule text NOT NULL
);

-- 唯一约束（data_id, group_id, tenant_id, gray_name）
ALTER TABLE config_info_gray ADD CONSTRAINT uk_configinfogray_datagrouptenantgray UNIQUE (data_id, group_id, tenant_id, gray_name);

-- 索引
CREATE INDEX idx_dataid_gmt_modified ON config_info_gray (data_id, gmt_modified);
CREATE INDEX idx_gmt_modified ON config_info_gray (gmt_modified);

COMMENT ON TABLE config_info_gray IS 'config_info_gray';

/******************************************/
/* 表名称 = config_tags_relation        */
/******************************************/
CREATE TABLE config_tags_relation (
  id bigint NOT NULL, 
  tag_name varchar(128) NOT NULL, 
  tag_type varchar(64), 
  data_id varchar(255) NOT NULL, 
  group_id varchar(128) NOT NULL, 
  tenant_id varchar(128) DEFAULT '', 
  nid bigserial NOT NULL, 
  PRIMARY KEY (nid)
);

-- 唯一约束
ALTER TABLE config_tags_relation ADD CONSTRAINT uk_configtagrelation_configidtag UNIQUE (id, tag_name, tag_type);

-- 索引
CREATE INDEX idx_tenant_id_tags ON config_tags_relation (tenant_id);

COMMENT ON TABLE config_tags_relation IS 'config_tag_relation';

/******************************************/
/* 表名称 = group_capacity              */
/******************************************/
CREATE TABLE group_capacity (
  id bigserial PRIMARY KEY, 
  group_id varchar(128) NOT NULL DEFAULT '', 
  quota integer NOT NULL DEFAULT 0, 
  usage integer NOT NULL DEFAULT 0, 
  max_size integer NOT NULL DEFAULT 0, 
  max_aggr_count integer NOT NULL DEFAULT 0, 
  max_aggr_size integer NOT NULL DEFAULT 0, 
  max_history_count integer NOT NULL DEFAULT 0, 
  gmt_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  gmt_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 唯一约束
ALTER TABLE group_capacity ADD CONSTRAINT uk_group_id UNIQUE (group_id);

COMMENT ON TABLE group_capacity IS '集群、各Group容量信息表';

/******************************************/
/* 表名称 = his_config_info             */
/******************************************/
CREATE TABLE his_config_info (
  id bigint NOT NULL, 
  nid bigserial PRIMARY KEY, 
  data_id varchar(255) NOT NULL, 
  group_id varchar(128) NOT NULL, 
  app_name varchar(128), 
  content text NOT NULL, 
  md5 varchar(32), 
  gmt_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  gmt_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  src_user text, 
  src_ip varchar(50), 
  op_type char(10), 
  tenant_id varchar(128) DEFAULT '', 
  publish_type varchar(50) DEFAULT 'formal', 
  gray_name varchar(50), 
  ext_info text
);

-- 索引
CREATE INDEX idx_gmt_create_his ON his_config_info (gmt_create);
CREATE INDEX idx_gmt_modified_his ON his_config_info (gmt_modified);
CREATE INDEX idx_did_his ON his_config_info (data_id);

COMMENT ON TABLE his_config_info IS '多租户改造';

/******************************************/
/* 表名称 = tenant_capacity             */
/******************************************/
CREATE TABLE tenant_capacity (
  id bigserial PRIMARY KEY, 
  tenant_id varchar(128) NOT NULL DEFAULT '', 
  quota integer NOT NULL DEFAULT 0, 
  usage integer NOT NULL DEFAULT 0, 
  max_size integer NOT NULL DEFAULT 0, 
  max_aggr_count integer NOT NULL DEFAULT 0, 
  max_aggr_size integer NOT NULL DEFAULT 0, 
  max_history_count integer NOT NULL DEFAULT 0, 
  gmt_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  gmt_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 唯一约束
ALTER TABLE tenant_capacity ADD CONSTRAINT uk_tenant_id_cap UNIQUE (tenant_id);

COMMENT ON TABLE tenant_capacity IS '租户容量信息表';

/******************************************/
/* 表名称 = tenant_info                 */
/******************************************/
CREATE TABLE tenant_info (
  id bigserial PRIMARY KEY, 
  kp varchar(128) NOT NULL, 
  tenant_id varchar(128) DEFAULT '', 
  tenant_name varchar(128) DEFAULT '', 
  tenant_desc varchar(256), 
  create_source varchar(32), 
  gmt_create bigint NOT NULL, 
  gmt_modified bigint NOT NULL
);

-- 唯一约束
ALTER TABLE tenant_info ADD CONSTRAINT uk_tenant_info_kptenantid UNIQUE (kp, tenant_id);
CREATE INDEX idx_tenant_id_info ON tenant_info (tenant_id);

COMMENT ON TABLE tenant_info IS 'tenant_info';

/******************************************/
/* 表名称 = roles                       */
/******************************************/
CREATE TABLE roles (
  username varchar(50) NOT NULL, 
  role varchar(50) NOT NULL
);

-- 唯一索引
CREATE UNIQUE INDEX idx_user_role ON roles (username, role);

COMMENT ON TABLE roles IS 'roles';

/******************************************/
/* 表名称 = permissions                 */
/******************************************/
CREATE TABLE permissions (
  role varchar(50) NOT NULL, 
  resource varchar(128) NOT NULL, 
  action varchar(8) NOT NULL
);

-- 唯一索引
CREATE UNIQUE INDEX uk_role_permission ON permissions (role, resource, action);

COMMENT ON TABLE permissions IS 'permissions';
INSERT INTO roles (username, role) VALUES ('nacos', 'ROLE_ADMIN');
