CREATE TABLE `project_rule`
(
    `rule_id`          bigint   NOT NULL COMMENT '规则id，unique_key',
    `project_id`       bigint   NOT NULL COMMENT '项目id，unique_key',
    `rule_update_time` datetime NULL COMMENT '项目匹配规则的更新时间'
) ENGINE = OLAP UNIQUE KEY(`rule_id`, `project_id`)
DISTRIBUTED BY HASH(`rule_id`) BUCKETS 10
PROPERTIES (
    "replication_num" = "1"
);

CREATE TABLE `rule`
(
    `id`            bigint   NOT NULL AUTO_INCREMENT (1) COMMENT '规则id，unique_key',
    `interval`      bigint   NOT NULL COMMENT '执行周期',
    `condition` string NOT NULL COMMENT '规则内容',
    `remark` string NULL COMMENT '备注信息',
    `active`        boolean  NULL COMMENT '激活状态',
    `delete`        boolean  NULL COMMENT '是否删除',
    `create_time`   datetime NULL COMMENT '创建时间',
    `schedule_time` datetime NULL COMMENT '最近调度时间',
    `max_parallel`  int      NULL COMMENT '最大并行度'
) ENGINE = OLAP UNIQUE KEY(`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 10
PROPERTIES (
    "replication_num" = "1"
);

CREATE TABLE `rule_executor`
(
    `id`          bigint   NOT NULL AUTO_INCREMENT (1) COMMENT '实例 id，unique_key',
    `rule_id`     bigint   NOT NULL COMMENT '规则ID',
    `start_time`  datetime NOT NULL COMMENT '开始时间',
    `end_time`    datetime NULL COMMENT '结束时间',
    `duration`    BIGINT   NOT NULL DEFAULT 0 COMMENT '实例执行时长',
    `target_num`  BIGINT   NOT NULL DEFAULT 0 COMMENT '命中规则项目数',
    `process_num` BIGINT   NOT NULL DEFAULT 0 COMMENT '实例已处理的数据条数',
    `status`      int      NOT NULL COMMENT '实例状态, 0：运行中;1：正常结束;-1：异常结束'
) ENGINE = OLAP UNIQUE KEY(`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 10
PROPERTIES (
    "replication_num" = "1"
);


CREATE TABLE `rule_execute_record`
(
    `rule_id`         bigint   NOT NULL COMMENT '规则ID',
    `executor_id`     bigint   NOT NULL COMMENT '执行器ID',
    `id`              bigint   NOT NULL AUTO_INCREMENT (1) COMMENT '执行记录片段id，unique_key',
    `start_time`      DATETIME NOT NULL COMMENT '记录的开始时间',
    `end_time`        DATETIME NULL COMMENT '记录的结束时间',
    `project_id_from` bigint   NOT NULL COMMENT '规划的扫描project_id 起始值 ',
    `project_id_to`   bigint   NOT NULL COMMENT '规划的扫描project_id 终止值',
    `duration`        BIGINT   NOT NULL DEFAULT 0 COMMENT '实例执行时长',
    `target_num`      BIGINT   NOT NULL DEFAULT 0 COMMENT '命中规则项目数',
    `process_num`     BIGINT   NOT NULL DEFAULT 0 COMMENT '实例已处理的数据条数',
    `status`          bigint   NOT NULL COMMENT '记录的状态',
) ENGINE = OLAP UNIQUE KEY(`rule_id`,`executor_id`, `id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 10
PROPERTIES (
    "replication_num" = "1"
);

CREATE TABLE `important_project_crwal_info`
(
    `id`         bigint       NOT NULL AUTO_INCREMENT COMMENT '任务ID，自增，unique_key',
    `project_id` bigint       NOT NULL COMMENT '项目ID',
    `name`       varchar(100) NULL COMMENT '项目名',
    `full_name`  varchar(255) NULL COMMENT 'Github 平台内唯一的项目全称',
    `url`        varchar(500) NULL COMMENT '项目链接',
    `commit_id`  varchar(100) NULL COMMENT '上次采集的commit_id',
    `pt`         datetime     NULL COMMENT '上次采集的pt时间',
    `task_time`  datetime     NULL COMMENT '任务插入时间',
    INDEX full_name_inverted_index (`full_name`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true")
) ENGINE = OLAP UNIQUE KEY( `id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 10
PROPERTIES (
  "replication_num" = "1"
);

CREATE TABLE `suspected_tool`
(
    `id`                bigint        NOT NULL AUTO_INCREMENT (1) COMMENT '序号/ID',
    `tool_name`         varchar(255)  NULL COMMENT '工具名',
    `full_name`         varchar(255)  NULL COMMENT '首发项目全称',
    `url`               varchar(1024) NULL COMMENT '工具链接',
    `version_number`    varchar(100)  NULL COMMENT '版本号(About信息)',
    `platform`          tinyint       NULL COMMENT '平台(1:Android,2:Hardware,3:IOS,4:JAVA,5:MAC,6:PC,7:VM,8:Linux)',
    `release_time`      datetime      NULL COMMENT '版本发布时间',
    `package_name`      varchar(255)  NULL COMMENT '包名',
    `tool_release_time` datetime      NULL COMMENT '工具发布时间',
    `finder_id`         varchar(255)  NULL COMMENT 'Downloadurl来源',
    `download_url`      varchar(1024) NULL COMMENT '样本下载链接',
    `remark`            text          NULL COMMENT '备注信息',
    `user_id`           bigint        NULL COMMENT '首发项目作者ID',
    `user_login`        varchar(255)  NULL COMMENT '首发项目作者登录名',
    `release`           varchar(1024) NULL COMMENT 'Release链接',
    `ft`                datetime      NULL COMMENT '发现时间',
    `ss`                tinyint       NULL DEFAULT "0" COMMENT '发送状态(0:默认值未发送,1:发送成功,-1:未发送成功)',
    `st`                datetime      NULL COMMENT '发送时间',
    `sr`                text          NULL COMMENT '发送结果',
    `vr`                tinyint       NULL DEFAULT "0" COMMENT '验证结果(-1:验证不通过,0:默认值,1:验证通过为新增网址,2:验证通过为新增网站)',
    INDEX tool_name_inverted_index (`tool_name`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"),
    INDEX url_inverted_index (`url`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"),
    INDEX full_name_inverted_index (`full_name`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"),
    INDEX user_login_inverted_index (`user_login`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true")
) ENGINE = OLAP UNIQUE KEY(`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 10
PROPERTIES (
"replication_num" = "1"
);

CREATE TABLE `sample_url`
(
    `id`         BIGINT        NOT NULL AUTO_INCREMENT COMMENT '序号/ID',
    `name`       VARCHAR(255)  NULL COMMENT '网站名',
    `url`        VARCHAR(1024) NULL COMMENT '样本链接地址',
    `full_name`  VARCHAR(255)  NULL COMMENT '首发项目全称',
    `user_id`    BIGINT        NULL COMMENT '首发项目作者ID',
    `user_login` VARCHAR(255)  NULL COMMENT '作者登录名',
    `file_path`  VARCHAR(1024) NULL COMMENT '首发项目文件路径',
    `ft`         DATETIME      NULL COMMENT '发现时间',
    `ss`         TINYINT       NULL DEFAULT "0" COMMENT '发送状态(0:默认值未发送,1:发送成功,-1:未发送成功)',
    `st`         DATETIME      NULL COMMENT '发送时间',
    `sr`         TEXT          NULL COMMENT '发送结果',
    `vr`         TINYINT       NULL DEFAULT "0" COMMENT '验证结果(-1:验证不通过,0:默认值,1:验证通过为新增网址,2:验证通过为新增网站)',

    -- 倒排索引定义
    INDEX name_inverted_index (`name`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"),
    INDEX url_inverted_index (`url`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"),
    INDEX full_name_inverted_index (`full_name`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"),
    INDEX user_login_inverted_index (`user_login`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true")
)
    ENGINE = OLAP UNIQUE KEY(`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 10
PROPERTIES (
    "replication_num" = "1"
);

CREATE TABLE `mirror_site`
(
    `id`         BIGINT        NOT NULL AUTO_INCREMENT COMMENT '序号/ID',
    `name`       VARCHAR(255)  NULL COMMENT '网站名',
    `url`        VARCHAR(1024) NULL COMMENT '网站首页地址',
    `full_name`  VARCHAR(255)  NULL COMMENT '项目全称',
    `user_id`    BIGINT        NULL COMMENT '首发项目作者ID',
    `user_login` VARCHAR(255)  NULL COMMENT '作者登录名',
    `commit_id`  VARCHAR(255)  NULL COMMENT 'commit id信息',
    `file_path`  VARCHAR(1024) NULL COMMENT '首发项目文件路径',
    `type`       INT           NULL COMMENT '类型(采用二进制位标记法: 1-机场, 2-涉政, 3-机场+涉政)',
    `ft`         DATETIME      NULL COMMENT '发现时间',
    `ss`         TINYINT       NULL DEFAULT "0" COMMENT '发送状态(0:默认值未发送,1:发送成功,-1:未发送成功)',
    `st`         DATETIME      NULL COMMENT '发送时间',
    `sr`         TEXT          NULL COMMENT '发送结果',
    `vr`         TINYINT       NULL DEFAULT "0" COMMENT '验证结果(-1:验证不通过,0:默认值,1:新增网址,2:新增网站)',

    -- 倒排索引定义
    INDEX name_inverted_idx (`name`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"),
    INDEX url_inverted_idx (`url`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"),
    INDEX full_name_inverted_idx (`full_name`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"),
    INDEX user_login_inverted_idx (`user_login`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true")
)
    ENGINE = OLAP UNIQUE KEY(`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 10
PROPERTIES (
    "replication_num" = "1"
);

CREATE TABLE `resource_url`
(
    `id`         BIGINT        NOT NULL AUTO_INCREMENT COMMENT '序号/ID',
    `protocol`   VARCHAR(50)   NULL COMMENT '协议类型',
    `url`        VARCHAR(1024) NULL COMMENT '资源URL',
    `full_name`  VARCHAR(255)  NULL COMMENT '项目全称',
    `user_id`    BIGINT        NULL COMMENT '首发项目作者ID',
    `user_login` VARCHAR(255)  NULL COMMENT '作者登录名',
    `commit_id`  VARCHAR(255)  NULL COMMENT 'commit id信息',
    `file_path`  VARCHAR(1024) NULL COMMENT '首发项目文件路径',
    `type`       TINYINT       NULL COMMENT '类型(1:资源链接, 2:订阅链接)',
    `ft`         DATETIME      NULL COMMENT '发现时间',
    `ss`         TINYINT       NULL DEFAULT "0" COMMENT '发送状态(0:默认值未发送,1:发送成功,-1:未发送成功)',
    `st`         DATETIME      NULL COMMENT '发送时间',
    `sr`         TEXT          NULL COMMENT '发送结果',
    `vr`         TINYINT       NULL DEFAULT "0" COMMENT '验证结果(-1:验证不通过,0:默认值,1:新增网址,2:新增网站)',

    -- 倒排索引定义
    INDEX url_inverted_idx (`url`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"),
    INDEX full_name_inverted_idx (`full_name`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"),
    INDEX user_login_inverted_idx (`user_login`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true")
)
    ENGINE = OLAP UNIQUE KEY(`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 10
PROPERTIES (
    "replication_num" = "1"
);

-- 分析结果记录表
create table `analysis_res`
(
    `project_id`        BIGINT NOT NULL COMMENT '项目ID,Github平台唯一',
    `type`              INT NULL COMMENT '发现的信息类型1:疑似工具；2：样本链接；3：sz镜像网站；4：机场镜像网站;5：FQ资源；6：订阅资源',
    `source`            INT NULL COMMENT '分析来源1：公开项目信息；2：重点项目信息；3：重点项目文件；4：重点项目release',
    `relation_id`       BIGINT NOT NULL COMMENT '关联表ID 某个类型（工具/资源/网站）下的ID',
    `project_full_name` varchar(255) NULL COMMENT '项目全名信息 [user_login]/[project_name]',
    `file_path`         varchar(500) NULL COMMENT '分析的具体文件',
    `release_tag`       VARCHAR(200) NULL COMMENT '分析的某个release 的tag',
    `release_asserts`   VARCHAR(500) NULL COMMENT '分析的某个release 的 assert，同release表',
    `release_commit_id` VARCHAR(200) NULL COMMENT '分析的某个release 的 commit_id',
    `ft`                datetime NULL COMMENT '发现时间'
) ENGINE=OLAP
UNIQUE KEY(`project_id`, `type`, `source`, `relation_id`)
DISTRIBUTED BY HASH(`project_id`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V2",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
)

-- 基础应用信息表
CREATE TABLE app_basic_info
(
    `api_key`           VARCHAR(64) COMMENT '应用唯一标识ID',
    `secret`            VARCHAR(128) COMMENT '应用密钥',
    `app_name`          VARCHAR(255) COMMENT '应用名称',
    `description`       VARCHAR COMMENT '描述',
    `industry_category` VARCHAR(100) COMMENT '行业分类',
    `email`             VARCHAR(100) COMMENT '邮箱',
    `phone`             VARCHAR(20) COMMENT '手机',
    `company`           VARCHAR(255) COMMENT '联系人/单位',
    `qps_limit`         INT      DEFAULT 1000 COMMENT 'QPS限流值',
    `status`            TINYINT  DEFAULT 0 COMMENT '假删除标记(0:未删除,1:已删除)',
    `create_time`       DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time`       DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    -- 倒排索引定义
    INDEX               company_inverted_idx (`company`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"),
    INDEX               app_name_inverted_idx (`app_name`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"),
    INDEX               description_inverted_idx (`description`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true")
) ENGINE=OLAP
UNIQUE KEY(`api_key`, `secret`)
DISTRIBUTED BY HASH(`api_key`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V2",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
)

-- 应用权限点关联表
CREATE TABLE app_permission_relation
(
    `app_id`          VARCHAR(64) COMMENT '应用的app_id',
    `permission_id`   VARCHAR(64) COMMENT '权限点ID',
    `id`              BIGINT COMMENT '表ID',
    `permission_name` VARCHAR(255) COMMENT '权限点名称',
    `data_limit`      BIGINT COMMENT '权限点查询的数据量限制',
    `rate_limit`      INT COMMENT '限流次数(次数/s)',
    `create_time`     DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time`     DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=OLAP
UNIQUE KEY(`app_id`, `permission_id`)
DISTRIBUTED BY HASH(`app_id`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V2",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
)

-- 权限点表
CREATE TABLE permission_points
(
    `permission_id`   VARCHAR(64) COMMENT '权限点ID',
    `permission_name` VARCHAR(255) COMMENT '权限点名称',
    `description`     TEXT COMMENT '权限点描述',
    `create_time`     DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time`     DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) UNIQUE KEY(`permission_id`)
DISTRIBUTED BY HASH(`permission_id`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V2",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
)

-- 日志记录表
CREATE TABLE api_logs
(
    `log_id`          BIGINT COMMENT '日志ID',
    `api_key`         VARCHAR(64) COMMENT 'API标识ID',
    `permission_name` VARCHAR(64) COMMENT '查询的权限点',
    `data_size`       BIGINT COMMENT '获取数据量(size)',
    `query_time`      DATETIME COMMENT '查询时间',
    `response_time`   INT COMMENT '接口响应时间(ms)',
    `limited_flag`    TINYINT  DEFAULT 0 COMMENT '是否触发限流，0没有限流 1 限流',
    `limited_type`    VARCHAR(50) COMMENT '限流类型(QPS/数据量/其他)',
    `limited_desc`    TEXT COMMENT '限流原因描述',
    `create_time`     DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '日志创建时间'
) ENGINE=OLAP
UNIQUE KEY(`log_id`,`api_key`)
DISTRIBUTED BY HASH(`api_key`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V2",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
)