-- tenant job template + per-tenant config (master DB ylrz_saas)
-- A tenant picks template subset; sync writes to tenant sys_job

CREATE TABLE IF NOT EXISTS sys_job_template (
  template_id     BIGINT       NOT NULL AUTO_INCREMENT COMMENT 'template id',
  template_code   VARCHAR(64)  NOT NULL COMMENT 'unique code',
  job_name        VARCHAR(64)  NOT NULL COMMENT 'display name',
  job_group       VARCHAR(64)  NOT NULL DEFAULT 'DEFAULT' COMMENT 'quartz group',
  invoke_target   VARCHAR(500) NOT NULL COMMENT 'bean method',
  cron_expression VARCHAR(255) NOT NULL COMMENT 'default cron',
  misfire_policy  CHAR(1)      NOT NULL DEFAULT '3',
  concurrent      CHAR(1)      NOT NULL DEFAULT '1',
  scope           VARCHAR(16)  NOT NULL DEFAULT 'TENANT' COMMENT 'PLATFORM|TENANT',
  module_tag      VARCHAR(32)  DEFAULT NULL COMMENT 'module tag',
  default_status  CHAR(1)      NOT NULL DEFAULT '1' COMMENT 'default pause 0 run 1 pause',
  status          CHAR(1)      NOT NULL DEFAULT '0' COMMENT '0 enabled 1 disabled',
  remark          VARCHAR(500) DEFAULT NULL,
  create_by       VARCHAR(64)  DEFAULT '',
  create_time     DATETIME     DEFAULT CURRENT_TIMESTAMP,
  update_by       VARCHAR(64)  DEFAULT '',
  update_time     DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (template_id),
  UNIQUE KEY uk_template_code (template_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='scheduled job template library';

CREATE TABLE IF NOT EXISTS tenant_job_config (
  id              BIGINT       NOT NULL AUTO_INCREMENT,
  tenant_id       BIGINT       NOT NULL COMMENT 'tenant id',
  template_id     BIGINT       NOT NULL COMMENT 'sys_job_template.template_id',
  status          CHAR(1)      NOT NULL DEFAULT '1' COMMENT '0 enabled 1 paused',
  cron_expression VARCHAR(255) DEFAULT NULL COMMENT 'override cron',
  sync_status     CHAR(1)      NOT NULL DEFAULT '0' COMMENT '0 pending 1 ok 2 fail',
  sync_time       DATETIME     DEFAULT NULL,
  sync_message    VARCHAR(500) DEFAULT NULL,
  create_by       VARCHAR(64)  DEFAULT '',
  create_time     DATETIME     DEFAULT CURRENT_TIMESTAMP,
  update_by       VARCHAR(64)  DEFAULT '',
  update_time     DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_tenant_template (tenant_id, template_id),
  KEY idx_tenant_id (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='per-tenant scheduled job selection';

INSERT INTO sys_job_template (template_code, job_name, job_group, invoke_target, cron_expression, scope, module_tag, default_status, status, remark)
SELECT 'qw.addTag', '企微-客户打标签', 'QW_TASK', 'qwTask.addTag()', '0 0/5 * * * ?', 'TENANT', 'qw', '1', '0', 'qw task'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_job_template WHERE template_code = 'qw.addTag');

INSERT INTO sys_job_template (template_code, job_name, job_group, invoke_target, cron_expression, scope, module_tag, default_status, status, remark)
SELECT 'course.periodRefresh', '营期课程状态刷新', 'DEFAULT', 'periodTask.refreshPeriodStatus()', '0 0/1 * * * ?', 'TENANT', 'course', '1', '0', 'course task'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_job_template WHERE template_code = 'course.periodRefresh');

INSERT INTO sys_job_template (template_code, job_name, job_group, invoke_target, cron_expression, scope, module_tag, default_status, status, remark)
SELECT 'platform.moduleUsage', '租户模块使用统计', 'DEFAULT', 'lobsterBridgeDataSyncService.executeModuleUsageStatistics()', '0 0 2 * * ?', 'PLATFORM', 'platform', '1', '0', 'platform only'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_job_template WHERE template_code = 'platform.moduleUsage');