DROP TABLE IF EXISTS "XXL_JOB"."XXL_JOB_USER";
CREATE TABLE "XXL_JOB"."XXL_JOB_USER"
(
    "ID"         INT IDENTITY(2,1) NOT NULL,
    "USERNAME"   VARCHAR(50) NOT NULL,
    "PASSWORD"   VARCHAR(50) NOT NULL,
    "ROLE"       TINYINT     NOT NULL,
    "PERMISSION" VARCHAR(255) NULL
);

DROP TABLE IF EXISTS "XXL_JOB"."XXL_JOB_REGISTRY";
CREATE TABLE "XXL_JOB"."XXL_JOB_REGISTRY"
(
    "ID"             INT IDENTITY(1,1) NOT NULL,
    "REGISTRY_GROUP" VARCHAR(50)  NOT NULL,
    "REGISTRY_KEY"   VARCHAR(255) NOT NULL,
    "REGISTRY_VALUE" VARCHAR(255) NOT NULL,
    "UPDATE_TIME"    TIMESTAMP(0) NULL
);

DROP TABLE IF EXISTS "XXL_JOB"."XXL_JOB_LOGGLUE";
CREATE TABLE "XXL_JOB"."XXL_JOB_LOGGLUE"
(
    "ID"          INT IDENTITY(1,1) NOT NULL,
    "JOB_ID"      INT          NOT NULL,
    "GLUE_TYPE"   VARCHAR(50) NULL,
    "GLUE_SOURCE" CLOB NULL,
    "GLUE_REMARK" VARCHAR(128) NOT NULL,
    "ADD_TIME"    TIMESTAMP(0) NULL,
    "UPDATE_TIME" TIMESTAMP(0) NULL
);

DROP TABLE IF EXISTS "XXL_JOB"."XXL_JOB_LOG_REPORT";
CREATE TABLE "XXL_JOB"."XXL_JOB_LOG_REPORT"
(
    "ID"            INT IDENTITY(1,1) NOT NULL,
    "TRIGGER_DAY"   TIMESTAMP(0) NULL,
    "RUNNING_COUNT" INT DEFAULT 0
        NOT NULL,
    "SUC_COUNT"     INT DEFAULT 0
        NOT NULL,
    "FAIL_COUNT"    INT DEFAULT 0
        NOT NULL
);

DROP TABLE IF EXISTS "XXL_JOB"."XXL_JOB_LOG";
CREATE TABLE "XXL_JOB"."XXL_JOB_LOG"
(
    "ID"                        BIGINT IDENTITY(1,1) NOT NULL,
    "JOB_GROUP"                 INT NOT NULL,
    "JOB_ID"                    INT NOT NULL,
    "EXECUTOR_ADDRESS"          VARCHAR(255) NULL,
    "EXECUTOR_HANDLER"          VARCHAR(255) NULL,
    "EXECUTOR_PARAM"            VARCHAR(512) NULL,
    "EXECUTOR_SHARDING_PARAM"   VARCHAR(20) NULL,
    "EXECUTOR_FAIL_RETRY_COUNT" INT     DEFAULT 0
                                    NOT NULL,
    "TRIGGER_TIME"              TIMESTAMP(0) NULL,
    "TRIGGER_CODE"              INT NOT NULL,
    "TRIGGER_MSG"               TEXT NULL,
    "HANDLE_TIME"               TIMESTAMP(0) NULL,
    "HANDLE_CODE"               INT NOT NULL,
    "HANDLE_MSG"                TEXT NULL,
    "ALARM_STATUS"              TINYINT DEFAULT 0
                                    NOT NULL
);

DROP TABLE IF EXISTS "XXL_JOB"."XXL_JOB_LOCK";
CREATE TABLE "XXL_JOB"."XXL_JOB_LOCK"
(
    "LOCK_NAME" VARCHAR(50) NOT NULL
);

DROP TABLE IF EXISTS "XXL_JOB"."XXL_JOB_INFO";
CREATE TABLE "XXL_JOB"."XXL_JOB_INFO"
(
    "ID"                        INT IDENTITY(2,1) NOT NULL,
    "JOB_GROUP"                 INT          NOT NULL,
    "JOB_CRON"                  VARCHAR(128) NOT NULL,
    "JOB_DESC"                  VARCHAR(255) NOT NULL,
    "ADD_TIME"                  TIMESTAMP(0) NULL,
    "UPDATE_TIME"               TIMESTAMP(0) NULL,
    "AUTHOR"                    VARCHAR(64) NULL,
    "ALARM_EMAIL"               VARCHAR(255) NULL,
    "EXECUTOR_ROUTE_STRATEGY"   VARCHAR(50) NULL,
    "EXECUTOR_HANDLER"          VARCHAR(255) NULL,
    "EXECUTOR_PARAM"            VARCHAR(512) NULL,
    "EXECUTOR_BLOCK_STRATEGY"   VARCHAR(50) NULL,
    "EXECUTOR_TIMEOUT"          INT     DEFAULT 0
                                             NOT NULL,
    "EXECUTOR_FAIL_RETRY_COUNT" INT     DEFAULT 0
                                             NOT NULL,
    "GLUE_TYPE"                 VARCHAR(50)  NOT NULL,
    "GLUE_SOURCE"               CLOB NULL,
    "GLUE_REMARK"               VARCHAR(128) NULL,
    "GLUE_UPDATETIME"           TIMESTAMP(0) NULL,
    "CHILD_JOBID"               VARCHAR(255) NULL,
    "TRIGGER_STATUS"            TINYINT DEFAULT 0
                                             NOT NULL,
    "TRIGGER_LAST_TIME"         BIGINT  DEFAULT 0
                                             NOT NULL,
    "TRIGGER_NEXT_TIME"         BIGINT  DEFAULT 0
                                             NOT NULL
);

DROP TABLE IF EXISTS "XXL_JOB"."XXL_JOB_GROUP";
CREATE TABLE "XXL_JOB"."XXL_JOB_GROUP"
(
    "ID"           INT IDENTITY(2,1) NOT NULL,
    "APP_NAME"     VARCHAR(64) NOT NULL,
    "TITLE"        VARCHAR(20) NOT NULL,
    "ORDER"        INT         NOT NULL,
    "ADDRESS_TYPE" TINYINT DEFAULT 0
                               NOT NULL,
    "ADDRESS_LIST" VARCHAR(512) NULL
);

ALTER TABLE "XXL_JOB"."XXL_JOB_USER"
    ADD CONSTRAINT PRIMARY KEY ("ID");

ALTER TABLE "XXL_JOB"."XXL_JOB_USER"
    ADD CONSTRAINT "I_USERNAME" UNIQUE ("USERNAME");

ALTER TABLE "XXL_JOB"."XXL_JOB_REGISTRY"
    ADD CONSTRAINT PRIMARY KEY ("ID");

ALTER TABLE "XXL_JOB"."XXL_JOB_LOGGLUE"
    ADD CONSTRAINT PRIMARY KEY ("ID");

ALTER TABLE "XXL_JOB"."XXL_JOB_LOG_REPORT"
    ADD CONSTRAINT PRIMARY KEY ("ID");

ALTER TABLE "XXL_JOB"."XXL_JOB_LOG_REPORT"
    ADD CONSTRAINT "I_TRIGGER_DAY" UNIQUE ("TRIGGER_DAY");

ALTER TABLE "XXL_JOB"."XXL_JOB_LOG"
    ADD CONSTRAINT PRIMARY KEY ("ID");

ALTER TABLE "XXL_JOB"."XXL_JOB_LOCK"
    ADD CONSTRAINT PRIMARY KEY ("LOCK_NAME");

ALTER TABLE "XXL_JOB"."XXL_JOB_INFO"
    ADD CONSTRAINT PRIMARY KEY ("ID");

ALTER TABLE "XXL_JOB"."XXL_JOB_GROUP"
    ADD CONSTRAINT PRIMARY KEY ("ID");

CREATE UNIQUE INDEX "PRIMARY"
    ON "XXL_JOB"."XXL_JOB_USER" ("ID");

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_USER"."USERNAME" IS '账号';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_USER"."PASSWORD" IS '密码';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_USER"."ROLE" IS '角色：0-普通用户、1-管理员';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_USER"."PERMISSION" IS '权限：执行器ID列表，多个逗号分割';

CREATE INDEX "I_G_K_V"
    ON "XXL_JOB"."XXL_JOB_REGISTRY" ("REGISTRY_GROUP", "REGISTRY_KEY", "REGISTRY_VALUE");


COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_LOGGLUE"."JOB_ID" IS '任务，主键ID';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_LOGGLUE"."GLUE_TYPE" IS 'GLUE类型';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_LOGGLUE"."GLUE_SOURCE" IS 'GLUE源代码';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_LOGGLUE"."GLUE_REMARK" IS 'GLUE备注';


COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_LOG_REPORT"."TRIGGER_DAY" IS '调度-时间';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_LOG_REPORT"."RUNNING_COUNT" IS '运行中-日志数量';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_LOG_REPORT"."SUC_COUNT" IS '执行成功-日志数量';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_LOG_REPORT"."FAIL_COUNT" IS '执行失败-日志数量';

CREATE INDEX "I_HANDLE_CODE"
    ON "XXL_JOB"."XXL_JOB_LOG" ("HANDLE_CODE");

CREATE INDEX "I_TRIGGER_TIME"
    ON "XXL_JOB"."XXL_JOB_LOG" ("TRIGGER_TIME");


COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_LOG"."JOB_GROUP" IS '执行器主键ID';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_LOG"."JOB_ID" IS '任务，主键ID';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_LOG"."EXECUTOR_ADDRESS" IS '执行器地址，本次执行的地址';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_LOG"."EXECUTOR_HANDLER" IS '执行器任务handler';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_LOG"."EXECUTOR_PARAM" IS '执行器任务参数';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_LOG"."EXECUTOR_SHARDING_PARAM" IS '执行器任务分片参数，格式如 1/2';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_LOG"."EXECUTOR_FAIL_RETRY_COUNT" IS '失败重试次数';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_LOG"."TRIGGER_TIME" IS '调度-时间';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_LOG"."TRIGGER_CODE" IS '调度-结果';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_LOG"."TRIGGER_MSG" IS '调度-日志';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_LOG"."HANDLE_TIME" IS '执行-时间';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_LOG"."HANDLE_CODE" IS '执行-状态';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_LOG"."HANDLE_MSG" IS '执行-日志';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_LOG"."ALARM_STATUS" IS '告警状态：0-默认、1-无需告警、2-告警成功、3-告警失败';


COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_LOCK"."LOCK_NAME" IS '锁名称';


COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_INFO"."JOB_GROUP" IS '执行器主键ID';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_INFO"."JOB_CRON" IS '任务执行CRON';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_INFO"."AUTHOR" IS '作者';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_INFO"."ALARM_EMAIL" IS '报警邮件';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_INFO"."EXECUTOR_ROUTE_STRATEGY" IS '执行器路由策略';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_INFO"."EXECUTOR_HANDLER" IS '执行器任务handler';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_INFO"."EXECUTOR_PARAM" IS '执行器任务参数';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_INFO"."EXECUTOR_BLOCK_STRATEGY" IS '阻塞处理策略';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_INFO"."EXECUTOR_TIMEOUT" IS '任务执行超时时间，单位秒';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_INFO"."EXECUTOR_FAIL_RETRY_COUNT" IS '失败重试次数';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_INFO"."GLUE_TYPE" IS 'GLUE类型';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_INFO"."GLUE_SOURCE" IS 'GLUE源代码';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_INFO"."GLUE_REMARK" IS 'GLUE备注';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_INFO"."GLUE_UPDATETIME" IS 'GLUE更新时间';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_INFO"."CHILD_JOBID" IS '子任务ID，多个逗号分隔';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_INFO"."TRIGGER_STATUS" IS '调度状态：0-停止，1-运行';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_INFO"."TRIGGER_LAST_TIME" IS '上次调度时间';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_INFO"."TRIGGER_NEXT_TIME" IS '下次调度时间';


COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_GROUP"."APP_NAME" IS '执行器AppName';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_GROUP"."TITLE" IS '执行器名称';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_GROUP"."ORDER" IS '排序';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_GROUP"."ADDRESS_TYPE" IS '执行器地址类型：0=自动注册、1=手动录入';

COMMENT
ON COLUMN "XXL_JOB"."XXL_JOB_GROUP"."ADDRESS_LIST" IS '执行器地址列表，多地址逗号分隔';

SET
IDENTITY_INSERT "XXL_JOB"."xxl_job_group" ON;
INSERT INTO "XXL_JOB"."xxl_job_group"(id, app_name, title, "order", address_type, address_list)
VALUES (15, 'phoenix', '新单体执行器', 1, 0, '10.42.1.211:7079'),
       (17, 'phoenix-posting', '单体过账Posting', 1, 0, '10.42.1.205:7227');
SET
IDENTITY_INSERT "XXL_JOB"."xxl_job_group" OFF;

SET
IDENTITY_INSERT XXL_JOB.XXL_JOB_INFO ON;
INSERT INTO "XXL_JOB"."xxl_job_info"(id, job_group, job_cron, job_desc, add_time, update_time, author,
                                     alarm_email, executor_route_strategy, executor_handler,
                                     executor_param,
                                     executor_block_strategy, executor_timeout, executor_fail_retry_count,
                                     glue_type, glue_source, glue_remark, glue_updatetime, child_jobid,
                                     trigger_status, trigger_last_time, trigger_next_time)
VALUES (36, 15, '0 0 18 * * ?', '每天更新工位生产日历', '2021-12-21 13:28:38', '2022-01-17 09:26:55', 'qizheng.sun', '', 'FIRST',
        'workstationCalendarJobHandler', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN', '', 'GLUE代码初始化', '2021-12-21 13:28:38',
        '', 1, 1653559200000, 1653645600000),
       (40, 17, '0 0/6 * * * ?', '聚合工位原始采集数据', '2021-12-22 18:35:21', '2022-01-17 10:07:55', 'POSTING', '', 'FIRST',
        'workStationAggregateJobHandler', '', 'DISCARD_LATER', 0, 0, 'BEAN', '', 'GLUE代码初始化', '2021-12-22 18:35:21', '',
        1, 1653566040000, 1653566400000),
       (41, 17, '0 56 23 * * ?', '构建明日生产日历时间切片缓存', '2021-12-22 18:36:55', '2022-01-14 10:37:18', 'POSTING', '', 'FIRST',
        'productionTimeCacheJobHandler', '', 'DISCARD_LATER', 0, 0, 'BEAN', '', 'GLUE代码初始化', '2021-12-22 18:36:55', '',
        1, 1653559800000, 1653646200000),
       (42, 17, '0 30 23 * * ?', '构建工位状态明日班次固定点', '2021-12-22 18:38:25', '2022-01-14 10:36:59', 'POSTING', '', 'FIRST',
        'stateFixPointJobHandler', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN', '', 'GLUE代码初始化', '2021-12-22 18:38:25', '', 1,
        1653559500000, 1653645900000),
       (48, 15, '0 56 23 * * ?', '生产日历切片缓存', '2022-03-23 18:32:50', '2022-03-23 18:39:03', 'wenchao.long', '', 'FIRST',
        'calendarCacheJobHandler', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN', '', 'GLUE代码初始化', '2022-03-23 18:32:50', '', 1,
        1653494160000, 1653580560000),
       (49, 15, '0/5 * * * * ?', '同步当前时间全量参数', '2022-07-16 11:06:46', '2022-07-16 11:06:46', 'wenchao.long', '',
        'FIRST',
        'allDmpCollectDataJobHandler', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN', '', 'GLUE代码初始化', '2022-07-16 11:06:46', '',
        1, 0, 0),
       (44, 15, '0 0 0 * * ?', '保养计划定时任务', '2022-03-02 15:07:10', '2022-03-02 15:07:36', 'jiangxiaodong', '', 'FIRST',
        'maintainPlanJobHandler', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN', '', 'GLUE代码初始化', '2022-03-02 15:07:10', '', 1,
        1653494400000, 1653580800000),
        (50, 15, '0 30 8 * * ?', '逾期通知定时任务', '2022-08-26 13:17:03', '2022-08-26 15:15:53', 'haolv', '', 'FIRST', 'maintain-overdueJobHandler', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN', '', 'GLUE代码初始化', '2022-08-26 13:17:03', '', 1, 1661585400000, 1661587200000),
        (51, 15, '0 30 8 * * ?', '预警通知定时任务', '2022-08-26 13:18:07', '2022-08-26 15:15:50', 'haolv', '', 'FIRST', 'maintenance-warningJobHandler', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN', '', 'GLUE代码初始化', '2022-08-26 13:18:07', '', 1, 1661585400000, 1661587200000);
SET
IDENTITY_INSERT XXL_JOB.XXL_JOB_INFO OFF;

INSERT INTO "XXL_JOB"."xxl_job_lock"(lock_name)
VALUES ('schedule_lock');


INSERT INTO "XXL_JOB"."xxl_job_registry"(registry_group, registry_key, registry_value, update_time)
VALUES ('EXECUTOR', 'phoenix-posting', '10.42.1.205:7227', '2022-03-24 16:52:49'),
       ('EXECUTOR', 'phoenix', '10.42.1.211:7079', '2022-03-24 16:52:59');


INSERT INTO "XXL_JOB"."xxl_job_user"(username, password, role, permission)
VALUES ('admin', 'e10adc3949ba59abbe56e057f20f883e', 1, NULL);

SET IDENTITY_INSERT XXL_JOB.XXL_JOB_INFO ON;
INSERT INTO xxl_job.xxl_job_info (id, job_group, job_cron, job_desc, add_time, update_time, author, alarm_email, executor_route_strategy, executor_handler, executor_param, executor_block_strategy, executor_timeout, executor_fail_retry_count, glue_type, glue_source, glue_remark, glue_updatetime, child_jobid, trigger_status, trigger_last_time, trigger_next_time)
VALUES (53, 15, '0/2 * * * * ?', 'fms实时数据推送', '2022-08-29 10:13:30', '2022-08-29 10:13:30', 'XXL', '', 'FIRST', 'fmsPushDataJob', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN', '', 'GLUE代码初始化', '2022-08-29 10:13:30', '', 1, 1661761462000, 1661761464000);
INSERT INTO xxl_job.xxl_job_info (id, job_group, job_cron, job_desc, add_time, update_time, author, alarm_email, executor_route_strategy, executor_handler, executor_param, executor_block_strategy, executor_timeout, executor_fail_retry_count, glue_type, glue_source, glue_remark, glue_updatetime, child_jobid, trigger_status, trigger_last_time, trigger_next_time)
VALUES (54, 17, '0/10 * * * * ?', '定时扫描状态反馈执行记录', '2022-09-07 17:12:02', '2022-09-15 13:19:56', 'POSTING', '', 'FIRST', 'feedbackAnalyseJobHandler', '', 'DISCARD_LATER', 0, 0, 'BEAN', '', 'GLUE代码初始化', '2022-09-07 17:12:02', '', 1, 1663312480000, 1663312490000);
SET IDENTITY_INSERT XXL_JOB.XXL_JOB_INFO OFF;

-- 定时清除dmp日志任务
SET IDENTITY_INSERT XXL_JOB.XXL_JOB_INFO ON;
INSERT INTO xxl_job.xxl_job_info (id, job_group, job_cron, job_desc, add_time, update_time, author, alarm_email, executor_route_strategy, executor_handler, executor_param, executor_block_strategy, executor_timeout, executor_fail_retry_count, glue_type, glue_source, glue_remark, glue_updatetime, child_jobid, trigger_status, trigger_last_time, trigger_next_time)
VALUES (55, 15, '0 0 2 L 3/3 ? ', 'DMP日志定期清除任务', '2022-10-11 19:45:11', '2022-10-11 19:45:47', 'wangshaofei', '', 'FIRST', 'dmpLogRegularCleanJobHandler', '3', 'SERIAL_EXECUTION', 0, 0, 'BEAN', '', 'GLUE代码初始化', '2022-10-11 19:45:11', '', 0, 0, 0);
SET IDENTITY_INSERT XXL_JOB.XXL_JOB_INFO OFF;
