CREATE SCHEMA IF NOT EXISTS message;

DROP TABLE IF EXISTS message.tb_message CASCADE;
CREATE TABLE message.tb_message (
  optsn        VARCHAR(50)  NOT NULL,                 -- 流水号
  message_id   VARCHAR(50)  NOT NULL,                 -- 消息ID
  title        VARCHAR(255) NOT NULL,                 -- 消息标题
  content      VARCHAR(255) NOT NULL,                 -- 消息内容
  type         VARCHAR(50)  NOT NULL,                 -- 消息类型
  status       VARCHAR(50)  NOT NULL,                 -- 消息状态
  service_type VARCHAR(50)  NOT NULL,                 -- 服务类型
  dept_path    VARCHAR(255) DEFAULT NULL,             -- 部门全路径(隔离)
  creator      VARCHAR(50)  NOT NULL DEFAULT 'system',-- 创建者
  updater      VARCHAR(50)  DEFAULT NULL,             -- 更新者
  create_time  TIMESTAMPTZ  NOT NULL DEFAULT now(),   -- 创建时间
  update_time  TIMESTAMPTZ  DEFAULT NULL,
  delete_time  TIMESTAMPTZ  DEFAULT NULL,
  deleted      BOOLEAN      NOT NULL DEFAULT false,
  PRIMARY KEY (message_id),
  UNIQUE (optsn)
);

COMMENT ON TABLE message.tb_message IS '消息表';
COMMENT ON COLUMN message.tb_message.optsn IS '流水号';
COMMENT ON COLUMN message.tb_message.message_id IS '消息ID';
COMMENT ON COLUMN message.tb_message.title IS '消息标题';
COMMENT ON COLUMN message.tb_message.content IS '消息内容';
COMMENT ON COLUMN message.tb_message.type IS '消息类型';
COMMENT ON COLUMN message.tb_message.status IS '消息状态';
COMMENT ON COLUMN message.tb_message.service_type IS '服务类型';
COMMENT ON COLUMN message.tb_message.dept_path IS '部门全路径';
COMMENT ON COLUMN message.tb_message.creator IS '创建者';
COMMENT ON COLUMN message.tb_message.updater IS '更新者';
COMMENT ON COLUMN message.tb_message.create_time IS '创建时间';
COMMENT ON COLUMN message.tb_message.update_time IS '更新时间';
COMMENT ON COLUMN message.tb_message.delete_time IS '删除时间';
COMMENT ON COLUMN message.tb_message.deleted IS '是否删除';


-- 消息发送范围定义表（定义消息要发送给哪些对象，通过什么渠道）
DROP TABLE IF EXISTS message.tb_message_range CASCADE;
CREATE TABLE message.tb_message_range (
  optsn         VARCHAR(50)  NOT NULL,                -- 流水号
  message_id    VARCHAR(50)  NOT NULL,                -- 消息ID
  target_type   VARCHAR(20)  NOT NULL,                -- 目标类型：user/dept/role/all
  target_id     VARCHAR(50)  DEFAULT NULL,            -- 目标ID（用户、部门、角色ID等，all类型时为空）
  channel       VARCHAR(20)  NOT NULL DEFAULT 'app',  -- 发送渠道：app/sms/email/wechat_official_account/wechat_applet等
  dept_path     VARCHAR(255) DEFAULT NULL,            -- 部门全路径，支持like递归（如/1/2/3/）
  creator       VARCHAR(50)  NOT NULL DEFAULT 'system',-- 创建者
  updater       VARCHAR(50)  DEFAULT NULL,            -- 更新者
  create_time   TIMESTAMPTZ  NOT NULL DEFAULT now(),  -- 创建时间
  update_time   TIMESTAMPTZ  DEFAULT NULL,            -- 更新时间
  delete_time   TIMESTAMPTZ  DEFAULT NULL,            -- 删除时间
  deleted       BOOLEAN      NOT NULL DEFAULT false,  -- 是否删除
  PRIMARY KEY (optsn),
  UNIQUE (message_id, target_type, target_id, channel)
);

COMMENT ON TABLE message.tb_message_range IS '消息发送范围定义表';
COMMENT ON COLUMN message.tb_message_range.optsn IS '流水号';
COMMENT ON COLUMN message.tb_message_range.message_id IS '消息ID';
COMMENT ON COLUMN message.tb_message_range.target_type IS '目标类型：user-指定用户/dept-部门/role-角色/all-全员';
COMMENT ON COLUMN message.tb_message_range.target_id IS '目标ID（用户、部门、角色ID等，all类型时为空）';
COMMENT ON COLUMN message.tb_message_range.channel IS '发送渠道：app/sms/email/wechat_official_account/wechat_applet等';
COMMENT ON COLUMN message.tb_message_range.dept_path IS '部门全路径';
COMMENT ON COLUMN message.tb_message_range.creator IS '创建者';
COMMENT ON COLUMN message.tb_message_range.updater IS '更新者';
COMMENT ON COLUMN message.tb_message_range.create_time IS '创建时间';
COMMENT ON COLUMN message.tb_message_range.update_time IS '更新时间';
COMMENT ON COLUMN message.tb_message_range.delete_time IS '删除时间';
COMMENT ON COLUMN message.tb_message_range.deleted IS '是否删除';


-- 用户消息接收记录表（记录每个用户实际收到的消息及处理状态）
DROP TABLE IF EXISTS message.tb_message_receiver CASCADE;
CREATE TABLE message.tb_message_receiver (
  optsn         VARCHAR(50)  NOT NULL,                -- 流水号
  message_id    VARCHAR(50)  NOT NULL,                -- 消息ID
  user_id       VARCHAR(50)  NOT NULL,                -- 用户ID
  channel       VARCHAR(20)  DEFAULT 'app',           -- 接收渠道：app/sms/email/wechat等
  status        VARCHAR(20)  NOT NULL DEFAULT 'unread', -- 消息状态：unread-未读/read-已读/handled-已处理/deleted-已删除
  read_time     TIMESTAMPTZ  DEFAULT NULL,            -- 阅读时间
  handle_time   TIMESTAMPTZ  DEFAULT NULL,            -- 处理时间
  dept_path     VARCHAR(255) DEFAULT NULL,            -- 部门全路径（数据隔离）
  creator       VARCHAR(50)  NOT NULL DEFAULT 'system',-- 创建者
  updater       VARCHAR(50)  DEFAULT NULL,            -- 更新者
  create_time   TIMESTAMPTZ  NOT NULL DEFAULT now(),  -- 创建时间（接收时间）
  update_time   TIMESTAMPTZ  DEFAULT NULL,            -- 更新时间
  delete_time   TIMESTAMPTZ  DEFAULT NULL,            -- 删除时间
  deleted       BOOLEAN      NOT NULL DEFAULT false,  -- 是否删除
  PRIMARY KEY (optsn),
  UNIQUE (message_id, user_id, channel)
);

-- 创建索引以提高查询效率
CREATE INDEX idx_message_user_user_status ON message.tb_message_receiver(user_id, status, create_time DESC) WHERE deleted = false;
CREATE INDEX idx_message_user_message ON message.tb_message_receiver(message_id) WHERE deleted = false;

COMMENT ON TABLE message.tb_message_receiver IS '用户消息接收记录表';
COMMENT ON COLUMN message.tb_message_receiver.optsn IS '流水号';
COMMENT ON COLUMN message.tb_message_receiver.message_id IS '消息ID';
COMMENT ON COLUMN message.tb_message_receiver.user_id IS '用户ID';
COMMENT ON COLUMN message.tb_message_receiver.channel IS '接收渠道：app/sms/email/wechat等';
COMMENT ON COLUMN message.tb_message_receiver.status IS '消息状态：unread-未读/read-已读/handled-已处理/deleted-已删除';
COMMENT ON COLUMN message.tb_message_receiver.read_time IS '阅读时间';
COMMENT ON COLUMN message.tb_message_receiver.handle_time IS '处理时间';
COMMENT ON COLUMN message.tb_message_receiver.dept_path IS '部门全路径';
COMMENT ON COLUMN message.tb_message_receiver.creator IS '创建者';
COMMENT ON COLUMN message.tb_message_receiver.updater IS '更新者';
COMMENT ON COLUMN message.tb_message_receiver.create_time IS '创建时间（接收时间）';
COMMENT ON COLUMN message.tb_message_receiver.update_time IS '更新时间';
COMMENT ON COLUMN message.tb_message_receiver.delete_time IS '删除时间';
COMMENT ON COLUMN message.tb_message_receiver.deleted IS '是否删除';


-- 消息渠道配置表（管理各种消息发送渠道的配置）
DROP TABLE IF EXISTS message.tb_message_channel CASCADE;
CREATE TABLE message.tb_message_channel (
  optsn         VARCHAR(50)  NOT NULL,                -- 流水号
  channel_id    VARCHAR(50)  NOT NULL,                -- 渠道ID
  channel_code  VARCHAR(50)  NOT NULL,                -- 渠道编码：app/sms/email/wechat/dingtalk等
  channel_name  VARCHAR(100) NOT NULL,                -- 渠道名称
  channel_desc  VARCHAR(255) DEFAULT NULL,            -- 渠道描述
  config        JSON         DEFAULT NULL,            -- 渠道配置（如API密钥、服务器地址等）
  status        VARCHAR(20)  NOT NULL DEFAULT 'enabled', -- 渠道状态：enabled-启用/disabled-禁用/maintenance-维护中
  priority      INTEGER      DEFAULT 0,                -- 优先级（数字越大优先级越高）
  dept_path     VARCHAR(255) DEFAULT NULL,            -- 部门全路径（数据隔离）
  creator       VARCHAR(50)  NOT NULL DEFAULT 'system',-- 创建者
  updater       VARCHAR(50)  DEFAULT NULL,            -- 更新者
  create_time   TIMESTAMPTZ  NOT NULL DEFAULT now(),  -- 创建时间
  update_time   TIMESTAMPTZ  DEFAULT NULL,            -- 更新时间
  delete_time   TIMESTAMPTZ  DEFAULT NULL,            -- 删除时间
  deleted       BOOLEAN      NOT NULL DEFAULT false,  -- 是否删除
  PRIMARY KEY (channel_id),
  UNIQUE (optsn),
  UNIQUE (channel_code)
);

COMMENT ON TABLE message.tb_message_channel IS '消息渠道配置表';
COMMENT ON COLUMN message.tb_message_channel.optsn IS '流水号';
COMMENT ON COLUMN message.tb_message_channel.channel_id IS '渠道ID';
COMMENT ON COLUMN message.tb_message_channel.channel_code IS '渠道编码：app/sms/email/wechat/dingtalk等';
COMMENT ON COLUMN message.tb_message_channel.channel_name IS '渠道名称';
COMMENT ON COLUMN message.tb_message_channel.channel_desc IS '渠道描述';
COMMENT ON COLUMN message.tb_message_channel.config IS '渠道配置（JSON格式）';
COMMENT ON COLUMN message.tb_message_channel.status IS '渠道状态：enabled-启用/disabled-禁用/maintenance-维护中';
COMMENT ON COLUMN message.tb_message_channel.priority IS '优先级（数字越大优先级越高）';
COMMENT ON COLUMN message.tb_message_channel.dept_path IS '部门全路径';
COMMENT ON COLUMN message.tb_message_channel.creator IS '创建者';
COMMENT ON COLUMN message.tb_message_channel.updater IS '更新者';
COMMENT ON COLUMN message.tb_message_channel.create_time IS '创建时间';
COMMENT ON COLUMN message.tb_message_channel.update_time IS '更新时间';
COMMENT ON COLUMN message.tb_message_channel.delete_time IS '删除时间';
COMMENT ON COLUMN message.tb_message_channel.deleted IS '是否删除';

-- =============================
-- 消息模板表
-- =============================
DROP TABLE IF EXISTS message.tb_message_template CASCADE;
CREATE TABLE message.tb_message_template (
  optsn            VARCHAR(50)   NOT NULL,
  template_id      VARCHAR(50)   NOT NULL,
  template_code    VARCHAR(100)  NOT NULL,               -- 模板编码
  template_name    VARCHAR(255)  NOT NULL,               -- 模板名称
  template_type    VARCHAR(30)   NOT NULL,               -- 模板类型：system-系统/business-业务
  title_template   TEXT,                                 -- 标题模板（支持变量）
  content_template TEXT          NOT NULL,               -- 内容模板（支持变量）
  variables        JSONB,                                -- 模板变量定义
  service_type     VARCHAR(50)   NOT NULL,               -- 服务类型
  dept_path        VARCHAR(255)  DEFAULT NULL,           -- 部门全路径
  creator          VARCHAR(50)   DEFAULT NULL,           -- 创建者
  updater          VARCHAR(50)   DEFAULT NULL,           -- 更新者
  create_time      TIMESTAMPTZ   NOT NULL DEFAULT now(), -- 创建时间
  update_time      TIMESTAMPTZ   DEFAULT NULL,           -- 更新时间
  delete_time      TIMESTAMPTZ   DEFAULT NULL,           -- 删除时间
  deleted          BOOLEAN       NOT NULL DEFAULT false, -- 是否删除
  PRIMARY KEY (template_id),
  UNIQUE (optsn),
  UNIQUE (template_code)
);

COMMENT ON TABLE message.tb_message_template IS '消息模板表';
COMMENT ON COLUMN message.tb_message_template.optsn IS '流水号';
COMMENT ON COLUMN message.tb_message_template.template_id IS '模板ID';
COMMENT ON COLUMN message.tb_message_template.template_code IS '模板编码';
COMMENT ON COLUMN message.tb_message_template.template_name IS '模板名称';
COMMENT ON COLUMN message.tb_message_template.template_type IS '模板类型：system-系统/business-业务';
COMMENT ON COLUMN message.tb_message_template.title_template IS '标题模板（支持变量）';
COMMENT ON COLUMN message.tb_message_template.content_template IS '内容模板（支持变量）';
COMMENT ON COLUMN message.tb_message_template.variables IS '模板变量定义';
COMMENT ON COLUMN message.tb_message_template.service_type IS '服务类型';
COMMENT ON COLUMN message.tb_message_template.dept_path IS '部门全路径';
COMMENT ON COLUMN message.tb_message_template.creator IS '创建者';
COMMENT ON COLUMN message.tb_message_template.updater IS '更新者';
COMMENT ON COLUMN message.tb_message_template.create_time IS '创建时间';
COMMENT ON COLUMN message.tb_message_template.update_time IS '更新时间';
COMMENT ON COLUMN message.tb_message_template.delete_time IS '删除时间';
COMMENT ON COLUMN message.tb_message_template.deleted IS '是否删除';

CREATE INDEX idx_template_type ON message.tb_message_template(template_type) WHERE deleted = false;
