CREATE SCHEMA IF NOT EXISTS sys;

-- 通用更新时间触发函数（用于模拟 MySQL 的 ON UPDATE CURRENT_TIMESTAMP）
-- CREATE OR REPLACE FUNCTION sys.set_update_time()
-- RETURNS trigger AS $$
-- BEGIN
--   NEW.update_time := CURRENT_TIMESTAMP;
--   RETURN NEW;
-- END;
-- $$ LANGUAGE plpgsql;

-- 部门表
DROP TABLE IF EXISTS sys.tb_sys_dept CASCADE;
CREATE TABLE sys.tb_sys_dept (
  optsn       VARCHAR(50)   NOT NULL,               -- 流水号
  dept_id     VARCHAR(50)   NOT NULL,               -- 部门ID
  name        VARCHAR(100)  NOT NULL,               -- 部门名称
  parent_id    VARCHAR(50)   DEFAULT NULL,          -- 父部门ID
  dept_path   VARCHAR(255)   DEFAULT NULL,               -- 部门全路径
  description  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 (dept_id),
  UNIQUE (optsn)
);
-- 创建索引
CREATE INDEX idx_sys_dept_parent ON sys.tb_sys_dept USING btree (parent_id);
COMMENT ON TABLE sys.tb_sys_dept IS '部门表';
COMMENT ON COLUMN sys.tb_sys_dept.optsn IS '流水号';
COMMENT ON COLUMN sys.tb_sys_dept.dept_id IS '部门ID';
COMMENT ON COLUMN sys.tb_sys_dept.name IS '部门名称';
COMMENT ON COLUMN sys.tb_sys_dept.parent_id IS '父部门ID';
COMMENT ON COLUMN sys.tb_sys_dept.dept_path IS '部门全路径';
COMMENT ON COLUMN sys.tb_sys_dept.description IS '部门描述';
COMMENT ON COLUMN sys.tb_sys_dept.creator IS '创建者';
COMMENT ON COLUMN sys.tb_sys_dept.updater IS '更新者';
COMMENT ON COLUMN sys.tb_sys_dept.create_time IS '创建时间';
COMMENT ON COLUMN sys.tb_sys_dept.update_time IS '更新时间';
COMMENT ON COLUMN sys.tb_sys_dept.delete_time IS '删除时间';
COMMENT ON COLUMN sys.tb_sys_dept.deleted IS '是否删除';

-- 角色表
DROP TABLE IF EXISTS sys.tb_sys_role CASCADE;
CREATE TABLE sys.tb_sys_role (
  optsn       VARCHAR(50)   NOT NULL,               -- 流水号
  role_id     VARCHAR(50)   NOT NULL,               -- 角色ID
  name        VARCHAR(100)  NOT NULL,
  description VARCHAR(200) DEFAULT NULL,             -- 角色名称
  scope       VARCHAR(20)   NOT NULL DEFAULT 'dept', -- 角色作用域：global/dept
  owner_dept_id VARCHAR(50) DEFAULT NULL,           -- 当scope=dept时，所属部门ID
  creator     VARCHAR(50)   DEFAULT NULL,          -- 创建者
  dept_path VARCHAR(255)   DEFAULT NULL,
  status      BOOLEAN       NOT NULL DEFAULT false,               -- 部门全路径
  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 (role_id),
  UNIQUE (optsn)
);
COMMENT ON TABLE sys.tb_sys_role IS '角色表';
COMMENT ON COLUMN sys.tb_sys_role.optsn IS '流水号';
COMMENT ON COLUMN sys.tb_sys_role.role_id IS '角色ID';
COMMENT ON COLUMN sys.tb_sys_role.name IS '角色名称';
COMMENT ON COLUMN sys.tb_sys_role.description IS '角色名称';
COMMENT ON COLUMN sys.tb_sys_role.scope IS '角色作用域：global=通用，dept=部门私有';
COMMENT ON COLUMN sys.tb_sys_role.owner_dept_id IS '部门私有角色的所属部门ID（scope=dept 时必填）';
COMMENT ON COLUMN sys.tb_sys_role.status IS '角色状态';
COMMENT ON COLUMN sys.tb_sys_role.creator IS '创建者';
COMMENT ON COLUMN sys.tb_sys_role.dept_path IS '部门全路径';
COMMENT ON COLUMN sys.tb_sys_role.updater IS '更新者';
COMMENT ON COLUMN sys.tb_sys_role.create_time IS '创建时间';
COMMENT ON COLUMN sys.tb_sys_role.update_time IS '更新时间';
COMMENT ON COLUMN sys.tb_sys_role.delete_time IS '删除时间';
COMMENT ON COLUMN sys.tb_sys_role.deleted IS '是否删除';

-- 唯一性：
--  全局角色：name 在未删除且 scope=global 下唯一
--  部门私有角色：同一部门下 name 唯一（scope=dept）
CREATE UNIQUE INDEX IF NOT EXISTS uq_sys_role_global_name
  ON sys.tb_sys_role USING btree (name)
  WHERE deleted = false AND scope = 'global';
CREATE UNIQUE INDEX IF NOT EXISTS uq_sys_role_dept_name
  ON sys.tb_sys_role USING btree (owner_dept_id, name)
  WHERE deleted = false AND scope = 'dept';
CREATE INDEX IF NOT EXISTS idx_sys_role_owner_dept ON sys.tb_sys_role USING btree (owner_dept_id) WHERE deleted = false;

-- 部门角色关联表
DROP TABLE IF EXISTS sys.tb_sys_dept_role CASCADE;
CREATE TABLE sys.tb_sys_dept_role (
  optsn       VARCHAR(50)   NOT NULL,             -- 流水号
  dept_id     VARCHAR(50)   NOT NULL,               -- 部门ID
  role_id     VARCHAR(50)   NOT NULL,               -- 角色ID
  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 (dept_id, role_id),
  UNIQUE (optsn)
);
COMMENT ON TABLE sys.tb_sys_dept_role IS '部门角色关联表';
COMMENT ON COLUMN sys.tb_sys_dept_role.optsn IS '流水号';
COMMENT ON COLUMN sys.tb_sys_dept_role.dept_id IS '部门ID';
COMMENT ON COLUMN sys.tb_sys_dept_role.role_id IS '角色ID';
COMMENT ON COLUMN sys.tb_sys_dept_role.dept_path IS '部门全路径';
COMMENT ON COLUMN sys.tb_sys_dept_role.creator IS '创建者';
COMMENT ON COLUMN sys.tb_sys_dept_role.updater IS '更新者';
COMMENT ON COLUMN sys.tb_sys_dept_role.create_time IS '创建时间';
COMMENT ON COLUMN sys.tb_sys_dept_role.update_time IS '更新时间';
COMMENT ON COLUMN sys.tb_sys_dept_role.delete_time IS '删除时间';
COMMENT ON COLUMN sys.tb_sys_dept_role.deleted IS '是否删除';

-- 用户角色关联表
DROP TABLE IF EXISTS sys.tb_sys_user_role CASCADE;
CREATE TABLE sys.tb_sys_user_role (
  optsn       VARCHAR(50)   NOT NULL,               -- 流水号
  user_id     VARCHAR(50)   NOT NULL,               -- 用户ID
  role_id     VARCHAR(50)   NOT NULL,               -- 角色ID
  dept_id     VARCHAR(50)   NOT NULL,               -- 部门ID
  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 (user_id, role_id),
  UNIQUE (optsn)
);
COMMENT ON TABLE sys.tb_sys_user_role IS '用户角色关联表';
COMMENT ON COLUMN sys.tb_sys_user_role.optsn IS '流水号';
COMMENT ON COLUMN sys.tb_sys_user_role.user_id IS '用户ID';
COMMENT ON COLUMN sys.tb_sys_user_role.role_id IS '角色ID';
COMMENT ON COLUMN sys.tb_sys_user_role.dept_id IS '部门ID';
COMMENT ON COLUMN sys.tb_sys_user_role.dept_path IS '部门全路径';
COMMENT ON COLUMN sys.tb_sys_user_role.creator IS '创建者';
COMMENT ON COLUMN sys.tb_sys_user_role.updater IS '更新者';
COMMENT ON COLUMN sys.tb_sys_user_role.create_time IS '创建时间';
COMMENT ON COLUMN sys.tb_sys_user_role.update_time IS '更新时间';
COMMENT ON COLUMN sys.tb_sys_user_role.delete_time IS '删除时间';
COMMENT ON COLUMN sys.tb_sys_user_role.deleted IS '是否删除';

-- 视图表
DROP TABLE IF EXISTS sys.tb_sys_view CASCADE;
CREATE TABLE sys.tb_sys_view (
  optsn       VARCHAR(50)   NOT NULL,               -- 流水号
  view_id     VARCHAR(50)   NOT NULL,               -- 视图ID
  name        VARCHAR(100)  NOT NULL,               -- 视图名称
  parent_id    VARCHAR(50)   DEFAULT NULL,          -- 父视图ID
  url         VARCHAR(255)  DEFAULT NULL,          -- 视图URL
  component   VARCHAR(255)  DEFAULT NULL,          -- 视图组件
  icon        VARCHAR(100)  DEFAULT NULL,          -- 视图图标
  type        INTEGER       DEFAULT 0,             -- 视图类型：0=导航栏 1=侧边栏 2=按钮,3空白页
  view_type   VARCHAR(20)   DEFAULT 'route',       -- 页面类型：route=路由页面 iframe=嵌入页面
  iframe_url  VARCHAR(500)  DEFAULT NULL,          -- iframe URL（仅当view_type=iframe时有效）
  service     VARCHAR(20)   DEFAULT 'platform',    -- 所属服务：platform=平台应用 bidding=招标应用 workcase=客服应用
  layout      VARCHAR(100)  DEFAULT NULL,          -- 布局组件路径名称
  order_num   INTEGER       DEFAULT 0,             -- 视图排序号
  dept_path   VARCHAR(255)   DEFAULT NULL,               -- 部门全路径
  description 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 (view_id),
  UNIQUE (optsn)
);
-- 创建索引
CREATE INDEX idx_sys_view_parent ON sys.tb_sys_view USING btree (parent_id) WHERE deleted = false;
CREATE INDEX idx_sys_view_service ON sys.tb_sys_view USING btree (service) WHERE deleted = false;
CREATE INDEX idx_sys_view_type ON sys.tb_sys_view USING btree (view_type) WHERE deleted = false;
COMMENT ON TABLE sys.tb_sys_view IS '视图表（菜单表）';
COMMENT ON COLUMN sys.tb_sys_view.optsn IS '流水号';
COMMENT ON COLUMN sys.tb_sys_view.view_id IS '视图ID';
COMMENT ON COLUMN sys.tb_sys_view.name IS '视图名称';
COMMENT ON COLUMN sys.tb_sys_view.parent_id IS '父视图ID';
COMMENT ON COLUMN sys.tb_sys_view.url IS '视图URL';
COMMENT ON COLUMN sys.tb_sys_view.component IS '视图组件';
COMMENT ON COLUMN sys.tb_sys_view.icon IS '视图图标';
COMMENT ON COLUMN sys.tb_sys_view.type IS '视图类型：0=目录 1=菜单页面 2=按钮';
COMMENT ON COLUMN sys.tb_sys_view.view_type IS '页面类型：route=路由页面 iframe=嵌入页面';
COMMENT ON COLUMN sys.tb_sys_view.iframe_url IS 'iframe URL（仅当view_type=iframe时有效）';
COMMENT ON COLUMN sys.tb_sys_view.service IS '所属服务：platform=平台应用 bidding=招标应用 workcase=客服应用';
COMMENT ON COLUMN sys.tb_sys_view.layout IS '布局组件路径名称';
COMMENT ON COLUMN sys.tb_sys_view.order_num IS '视图排序号';
COMMENT ON COLUMN sys.tb_sys_view.description IS '视图描述';
COMMENT ON COLUMN sys.tb_sys_view.dept_path IS '部门全路径';
COMMENT ON COLUMN sys.tb_sys_view.creator IS '创建者';
COMMENT ON COLUMN sys.tb_sys_view.updater IS '更新者';
COMMENT ON COLUMN sys.tb_sys_view.create_time IS '创建时间';
COMMENT ON COLUMN sys.tb_sys_view.update_time IS '更新时间';
COMMENT ON COLUMN sys.tb_sys_view.delete_time IS '删除时间';
COMMENT ON COLUMN sys.tb_sys_view.deleted IS '是否删除';


-- 模块表
DROP TABLE IF EXISTS sys.tb_sys_module CASCADE;
CREATE TABLE sys.tb_sys_module (
  optsn       VARCHAR(50)   NOT NULL,               -- 流水号
  module_id   VARCHAR(50)   NOT NULL,               -- 模块ID
  name        VARCHAR(100)  NOT NULL,               -- 模块名称
  description VARCHAR(255)  DEFAULT NULL,          -- 模块描述
  creator     VARCHAR(50)   DEFAULT NULL,          -- 创建者
  dept_path   VARCHAR(255)   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 (module_id),
  UNIQUE (optsn)
);
COMMENT ON TABLE sys.tb_sys_module IS '模块表';
COMMENT ON COLUMN sys.tb_sys_module.optsn IS '流水号';
COMMENT ON COLUMN sys.tb_sys_module.module_id IS '模块ID';
COMMENT ON COLUMN sys.tb_sys_module.name IS '模块名称';
COMMENT ON COLUMN sys.tb_sys_module.description IS '模块描述';
COMMENT ON COLUMN sys.tb_sys_module.creator IS '创建者';
COMMENT ON COLUMN sys.tb_sys_module.dept_path IS '部门全路径';
COMMENT ON COLUMN sys.tb_sys_module.updater IS '更新者';
COMMENT ON COLUMN sys.tb_sys_module.create_time IS '创建时间';
COMMENT ON COLUMN sys.tb_sys_module.update_time IS '更新时间';
COMMENT ON COLUMN sys.tb_sys_module.delete_time IS '删除时间';
COMMENT ON COLUMN sys.tb_sys_module.deleted IS '是否删除';

-- 权限表
DROP TABLE IF EXISTS sys.tb_sys_permission CASCADE;
CREATE TABLE sys.tb_sys_permission (
  optsn       VARCHAR(50)   NOT NULL,               -- 流水号
  permission_id VARCHAR(50)   NOT NULL,            -- 权限ID
  name        VARCHAR(100)  NOT NULL,               -- 权限名称
  code        VARCHAR(100)  NOT NULL,               -- 权限代码
  description VARCHAR(255) DEFAULT NULL,          -- 权限描述
  module_id   VARCHAR(50)   DEFAULT NULL,          -- 所属模块ID
  creator     VARCHAR(50)   DEFAULT NULL,          -- 创建者
  dept_path   VARCHAR(255)   DEFAULT NULL,
  status      BOOLEAN       NOT NULL DEFAULT false,              -- 部门全路径
  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 (permission_id),
  UNIQUE (optsn)
);
COMMENT ON TABLE sys.tb_sys_permission IS '权限表';
COMMENT ON COLUMN sys.tb_sys_permission.optsn IS '流水号';
COMMENT ON COLUMN sys.tb_sys_permission.permission_id IS '权限ID';
COMMENT ON COLUMN sys.tb_sys_permission.name IS '权限名称';
COMMENT ON COLUMN sys.tb_sys_permission.code IS '权限代码';
COMMENT ON COLUMN sys.tb_sys_permission.description IS '权限描述' ;
COMMENT ON COLUMN sys.tb_sys_permission.module_id IS '所属模块ID';
COMMENT ON COLUMN sys.tb_sys_permission.creator IS '创建者';
COMMENT ON COLUMN sys.tb_sys_permission.dept_path IS '部门全路径';
COMMENT ON COLUMN sys.tb_sys_permission.status IS '角色状态';
COMMENT ON COLUMN sys.tb_sys_permission.updater IS '更新者';
COMMENT ON COLUMN sys.tb_sys_permission.create_time IS '创建时间';
COMMENT ON COLUMN sys.tb_sys_permission.update_time IS '更新时间';
COMMENT ON COLUMN sys.tb_sys_permission.delete_time IS '删除时间';
COMMENT ON COLUMN sys.tb_sys_permission.deleted IS '是否删除';

-- 角色权限
DROP TABLE IF EXISTS sys.tb_sys_role_permission CASCADE;
CREATE TABLE sys.tb_sys_role_permission (
  optsn       VARCHAR(50)   NOT NULL,               -- 流水号
  role_id     VARCHAR(50)   NOT NULL,               -- 角色ID
  permission_id VARCHAR(50)   NOT NULL,            -- 权限ID
  creator     VARCHAR(50)   DEFAULT NULL,          -- 创建者
  dept_path   VARCHAR(255)   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 (role_id, permission_id),
  UNIQUE (optsn)
);
COMMENT ON TABLE sys.tb_sys_role_permission IS '角色权限表';
COMMENT ON COLUMN sys.tb_sys_role_permission.optsn IS '流水号';
COMMENT ON COLUMN sys.tb_sys_role_permission.role_id IS '角色ID';
COMMENT ON COLUMN sys.tb_sys_role_permission.permission_id IS '权限ID';
COMMENT ON COLUMN sys.tb_sys_role_permission.creator IS '创建者';
COMMENT ON COLUMN sys.tb_sys_role_permission.dept_path IS '部门全路径';
COMMENT ON COLUMN sys.tb_sys_role_permission.updater IS '更新者';
COMMENT ON COLUMN sys.tb_sys_role_permission.create_time IS '创建时间';
COMMENT ON COLUMN sys.tb_sys_role_permission.update_time IS '更新时间';
COMMENT ON COLUMN sys.tb_sys_role_permission.delete_time IS '删除时间';
COMMENT ON COLUMN sys.tb_sys_role_permission.deleted IS '是否删除';

-- 视图权限
DROP TABLE IF EXISTS sys.tb_sys_view_permission CASCADE;
CREATE TABLE sys.tb_sys_view_permission (
  optsn       VARCHAR(50)   NOT NULL,               -- 流水号
  view_id     VARCHAR(50)   NOT NULL,               -- 视图ID
  permission_id VARCHAR(50)   NOT NULL,            -- 权限ID
  creator     VARCHAR(50)   DEFAULT NULL,          -- 创建者
  dept_path   VARCHAR(255)   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 (view_id, permission_id),
  UNIQUE (optsn)
);
COMMENT ON TABLE sys.tb_sys_view_permission IS '视图权限表';
COMMENT ON COLUMN sys.tb_sys_view_permission.optsn IS '流水号';
COMMENT ON COLUMN sys.tb_sys_view_permission.view_id IS '视图ID';
COMMENT ON COLUMN sys.tb_sys_view_permission.permission_id IS '权限ID';
COMMENT ON COLUMN sys.tb_sys_view_permission.creator IS '创建者';
COMMENT ON COLUMN sys.tb_sys_view_permission.dept_path IS '部门全路径';
COMMENT ON COLUMN sys.tb_sys_view_permission.updater IS '更新者';
COMMENT ON COLUMN sys.tb_sys_view_permission.create_time IS '创建时间';
COMMENT ON COLUMN sys.tb_sys_view_permission.update_time IS '更新时间';
COMMENT ON COLUMN sys.tb_sys_view_permission.delete_time IS '删除时间';
COMMENT ON COLUMN sys.tb_sys_view_permission.deleted IS '是否删除';

-- 为所有表创建更新时间触发器
-- DROP TRIGGER IF EXISTS trg_tb_sys_dept_update_time ON sys.tb_sys_dept;
-- CREATE TRIGGER trg_tb_sys_dept_update_time
--   BEFORE UPDATE ON sys.tb_sys_dept
--   FOR EACH ROW
--   EXECUTE FUNCTION sys.set_update_time();

-- DROP TRIGGER IF EXISTS trg_tb_sys_role_update_time ON sys.tb_sys_role;
-- CREATE TRIGGER trg_tb_sys_role_update_time
--   BEFORE UPDATE ON sys.tb_sys_role
--   FOR EACH ROW
--   EXECUTE FUNCTION sys.set_update_time();

-- DROP TRIGGER IF EXISTS trg_tb_sys_dept_role_update_time ON sys.tb_sys_dept_role;
-- CREATE TRIGGER trg_tb_sys_dept_role_update_time
--   BEFORE UPDATE ON sys.tb_sys_dept_role
--   FOR EACH ROW
--   EXECUTE FUNCTION sys.set_update_time();

-- DROP TRIGGER IF EXISTS trg_tb_sys_user_role_update_time ON sys.tb_sys_user_role;
-- CREATE TRIGGER trg_tb_sys_user_role_update_time
--   BEFORE UPDATE ON sys.tb_sys_user_role
--   FOR EACH ROW
--   EXECUTE FUNCTION sys.set_update_time();

-- DROP TRIGGER IF EXISTS trg_tb_sys_view_update_time ON sys.tb_sys_view;
-- CREATE TRIGGER trg_tb_sys_view_update_time
--   BEFORE UPDATE ON sys.tb_sys_view
--   FOR EACH ROW
--   EXECUTE FUNCTION sys.set_update_time();

-- DROP TRIGGER IF EXISTS trg_tb_sys_module_update_time ON sys.tb_sys_module;
-- CREATE TRIGGER trg_tb_sys_module_update_time
--   BEFORE UPDATE ON sys.tb_sys_module
--   FOR EACH ROW
--   EXECUTE FUNCTION sys.set_update_time();

-- DROP TRIGGER IF EXISTS trg_tb_sys_permission_update_time ON sys.tb_sys_permission;
-- CREATE TRIGGER trg_tb_sys_permission_update_time
--   BEFORE UPDATE ON sys.tb_sys_permission
--   FOR EACH ROW
--   EXECUTE FUNCTION sys.set_update_time();

-- DROP TRIGGER IF EXISTS trg_tb_sys_role_permission_update_time ON sys.tb_sys_role_permission;
-- CREATE TRIGGER trg_tb_sys_role_permission_update_time
--   BEFORE UPDATE ON sys.tb_sys_role_permission
--   FOR EACH ROW
--   EXECUTE FUNCTION sys.set_update_time();

-- DROP TRIGGER IF EXISTS trg_tb_sys_view_permission_update_time ON sys.tb_sys_view_permission;
-- CREATE TRIGGER trg_tb_sys_view_permission_update_time
--   BEFORE UPDATE ON sys.tb_sys_view_permission
--   FOR EACH ROW
--   EXECUTE FUNCTION sys.set_update_time();

-- =============================
-- 通用对象级权限（ACL）
-- 支持对象类型：任意（如 article/file/course/...）
-- 支持主体：user/dept/role
-- 权限位：1=读(read)，2=写(write)，4=执行(exec)，可相加
-- include_descendants：当主体为 dept/role 时，是否包含其子级（便于“所有子级可查看”场景）
-- allow：true=允许；false=显式拒绝（拒绝优先生效）
-- dept_path：用于数据范围隔离（与现有规则一致）
-- =============================

DROP TABLE IF EXISTS sys.tb_sys_acl CASCADE;
CREATE TABLE sys.tb_sys_acl (
  optsn             VARCHAR(50)   NOT NULL,               -- 流水号
  acl_id            VARCHAR(50)   NOT NULL,               -- ACL主键
  object_type       VARCHAR(50)   NOT NULL,               -- 对象类型：article/file/course/...
  object_id         VARCHAR(50)   NOT NULL,               -- 对象ID
  principal_type    VARCHAR(20)   NOT NULL,               -- 主体类型：user/dept/role
  principal_id      VARCHAR(50)   NOT NULL,               -- 主体ID
  principal_dept_id VARCHAR(50)   DEFAULT NULL,           -- 当主体为role且限定到某部门时的部门ID（支持“某部门的某角色”）
  permission        SMALLINT      NOT NULL,               -- 权限位：1读 2写 4执行
  allow             BOOLEAN       NOT NULL DEFAULT true,  -- 允许或显式拒绝
  include_descendants BOOLEAN     NOT NULL DEFAULT false, -- 是否包含子级（对dept/role生效）
  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 (acl_id),
  UNIQUE (object_type, object_id, principal_type, principal_id, principal_dept_id, deleted),
  UNIQUE (optsn)
);

COMMENT ON TABLE sys.tb_sys_acl IS '通用对象级权限表（ACL）';
COMMENT ON COLUMN sys.tb_sys_acl.object_type IS '对象类型：article/file/course/...';
COMMENT ON COLUMN sys.tb_sys_acl.object_id IS '对象ID';
COMMENT ON COLUMN sys.tb_sys_acl.principal_type IS '主体类型：user/dept/role';
COMMENT ON COLUMN sys.tb_sys_acl.principal_id IS '主体ID';
COMMENT ON COLUMN sys.tb_sys_acl.principal_dept_id IS '当主体为角色时，可选的限定部门ID（某部门的某角色）';
COMMENT ON COLUMN sys.tb_sys_acl.permission IS '权限位：1读 2写 4执行，可相加';
COMMENT ON COLUMN sys.tb_sys_acl.allow IS '是否允许（false=显式拒绝）';
COMMENT ON COLUMN sys.tb_sys_acl.include_descendants IS '是否包含子级（dept/role时启用）';
COMMENT ON COLUMN sys.tb_sys_acl.dept_path IS '部门全路径（用于数据范围隔离）';

CREATE INDEX idx_sys_acl_object ON sys.tb_sys_acl USING btree (object_type, object_id) WHERE deleted = false;
CREATE INDEX idx_sys_acl_principal ON sys.tb_sys_acl USING btree (principal_type, principal_id) WHERE deleted = false;
CREATE INDEX idx_sys_acl_dept_path ON sys.tb_sys_acl USING btree (dept_path) WHERE deleted = false;
-- 针对“某部门的某角色”的检索优化
CREATE INDEX idx_sys_acl_role_scoped ON sys.tb_sys_acl USING btree (principal_id, principal_dept_id)
  WHERE deleted = false AND principal_type = 'role';

-- =============================
-- ACL 策略表：定义对象类型的层级可见/可编辑规则
-- 例如：
--   - 同级与父级管理员可修改（edit_hierarchy_rule = 'parent_or_same_admin'）
--   - 子级全部可查看（view_hierarchy_rule = 'children_all'）
--   - 课程：下级只有指定部门/角色可查看（view_hierarchy_rule = 'children_specified'）
-- 说明：业务侧需结合“管理员”的判定（通常由角色判定）在应用层实现；本表做规则声明
-- =============================

DROP TABLE IF EXISTS sys.tb_sys_acl_policy CASCADE;
CREATE TABLE sys.tb_sys_acl_policy (
  optsn               VARCHAR(50)   NOT NULL,               -- 流水号
  policy_id           VARCHAR(50)   NOT NULL,               -- 策略ID
  name                VARCHAR(255)  NOT NULL,               -- 策略名称
  object_type         VARCHAR(50)   NOT NULL,               -- 目标对象类型
  edit_hierarchy_rule VARCHAR(50)   DEFAULT NULL,           -- 编辑层级规则：parent_only/parent_or_same_admin/owner_only/none
  view_hierarchy_rule VARCHAR(50)   DEFAULT NULL,           -- 查看层级规则：children_all/children_specified/none
  default_permission  SMALLINT      DEFAULT 0,              -- 默认权限位（无显式ACL时应用）
  default_allow       BOOLEAN       DEFAULT true,           -- 默认是否允许
  apply_to_children   BOOLEAN       DEFAULT true,          -- 是否默认应用到子级
  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 (policy_id),
  UNIQUE (object_type, deleted),
  UNIQUE (optsn)
);

COMMENT ON TABLE sys.tb_sys_acl_policy IS 'ACL对象类型策略表（层级可见/可编辑规则）';
COMMENT ON COLUMN sys.tb_sys_acl_policy.object_type IS '对象类型：article/file/course/...';
COMMENT ON COLUMN sys.tb_sys_acl_policy.edit_hierarchy_rule IS '编辑层级规则：parent_only/parent_or_same_admin/owner_only/none';
COMMENT ON COLUMN sys.tb_sys_acl_policy.view_hierarchy_rule IS '查看层级规则：children_all/children_specified/none';
COMMENT ON COLUMN sys.tb_sys_acl_policy.default_permission IS '默认权限位（无显式ACL时兜底）';
COMMENT ON COLUMN sys.tb_sys_acl_policy.apply_to_children IS '默认是否应用到子级';

CREATE INDEX idx_sys_acl_policy_object ON sys.tb_sys_acl_policy USING btree (object_type) WHERE deleted = false;