create_table

建表语句

-- 1. 全局角色表（仅保留超级管理员和普通用户）
CREATE TABLE global_roles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    role_name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. 用户表（存储所有用户基础信息）
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL COMMENT '登录用户名',
    password_hash VARCHAR(255) NOT NULL COMMENT '加密密码',
    feishu_user_name VARCHAR(255) UNIQUE COMMENT '飞书用户名',
    role_name VARCHAR(50) NOT NULL COMMENT '全局角色身份',
    open_id VARCHAR(255) NULL COMMENT '飞书平台唯一标识',
    created_at int(11) NULL DEFAULT NULL COMMENT '创建时间戳',
    updated_at int(11) NULL DEFAULT NULL COMMENT '更新时间戳'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. 应用表（存储所有应用基础信息）--------
CREATE TABLE app (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL COMMENT '应用名称',
    description TEXT COMMENT '应用描述',
    feishu_error_group VARCHAR(100) NULL COMMENT '飞书报错群ID或名称',
    created_at int(11) NULL DEFAULT NULL COMMENT '创建时间戳',
    updated_at int(11) NULL DEFAULT NULL COMMENT '更新时间戳',
    creator_id INT NULL COMMENT '创建者用户ID（关联users表）',
    UNIQUE KEY uk_app_name (name),
    CONSTRAINT fk_app_creator FOREIGN KEY (creator_id) 
        REFERENCES users(id) 
        ON DELETE SET NULL  -- 用户记录删除时，creator_id设为NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 4. 应用角色表（明确应用内的角色：管理员、成员）--------
CREATE TABLE app_roles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    app_id INT NOT NULL,
    role_name VARCHAR(50) NOT NULL,
    is_admin TINYINT(1) DEFAULT 0 COMMENT '1=应用内管理员，0=普通成员',
    UNIQUE KEY uk_app_role (app_id, role_name),
    FOREIGN KEY (app_id) REFERENCES app(id) ON DELETE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. 用户-应用-角色关联表（控制授权逻辑）
CREATE TABLE user_app_roles (
    user_id INT NOT NULL,
    app_id INT NOT NULL,
    role_id INT NOT NULL COMMENT '应用内角色ID',
    granted_by VARCHAR(255) NULL COMMENT '授权人账号',
    granted_at int(11) NULL DEFAULT NULL COMMENT '授权时间戳',
    PRIMARY KEY (user_id, app_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (app_id) REFERENCES app(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES app_roles(id) ON DELETE CASCADE,
    FOREIGN KEY (granted_by) REFERENCES users(username) ON DELETE SET NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. 应用关键词表
CREATE TABLE keywords (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  app_name varchar(100) NOT NULL COMMENT '应用名称（关联apps表）',
  key_name varchar(255) NOT NULL COMMENT '关键词',
  created_at int(11) NOT NULL COMMENT '创建时间',
  PRIMARY KEY (id),
  UNIQUE KEY `uk_app_key` (app_name, key_name) COMMENT '应用+关键词唯一索引',
  KEY `idx_app_name` (app_name) COMMENT '应用名称索引',
  -- 外键约束：关联 apps 表的 app_name，级联删除
  CONSTRAINT `fk_keywords_app` 
    FOREIGN KEY (app_name) 
    REFERENCES app (name) 
    ON DELETE CASCADE  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='应用关键词表';

-- 7. 应用关键词屏蔽表
CREATE TABLE blocked_keywords (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  app_name varchar(100) NOT NULL COMMENT '应用名称（关联apps表）',
  key_name varchar(255) NOT NULL COMMENT '关键词',
  reason varchar(500) DEFAULT NULL COMMENT '屏蔽原因',
  end int(11) DEFAULT NULL COMMENT '屏蔽结束时间（NULL表示永久屏蔽）',
  redmine varchar(100) DEFAULT NULL COMMENT '关联的Redmine问题编号',
  PRIMARY KEY (id),
  KEY `idx_app_name` (app_name) COMMENT '应用名称索引',
  KEY `idx_end_time` (end) COMMENT '屏蔽结束时间索引',
  -- 外键约束：关联 apps 表的 app_name，级联删除
  CONSTRAINT `fk_blocked_keywords_app` 
    FOREIGN KEY (app_name) 
    REFERENCES app (name) 
    ON DELETE CASCADE  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='屏蔽关键词表';

-- 8.类别屏蔽表
CREATE TABLE mask (
  id int(11) NOT NULL AUTO_INCREMENT,
  fp_id varchar(255) DEFAULT NULL,
  end int(11) NULL DEFAULT NULL COMMENT '结束时间戳',
  reason varchar(1000) DEFAULT NULL,
  redmine varchar(256) DEFAULT NULL,
  app_id varchar(256) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY `idx_fp_id` (fp_id),
  KEY `idx_app_id` (app_id),
  KEY `idx_redmine` (redmine(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--9.应用绑定飞书账号表
CREATE TABLE app_feishu_user (
  id int NOT NULL AUTO_INCREMENT COMMENT '记录ID',
  app_id int NOT NULL COMMENT '关联的应用ID',
  feishu_username varchar(100) NOT NULL COMMENT '飞书用户名',
  open_id varchar(128) DEFAULT NULL COMMENT '飞书用户open_id',
  PRIMARY KEY (id),
  KEY `fk_app_feishu_user_app_id` (app_id),  -- 外键索引，加速查询
  CONSTRAINT `fk_app_feishu_user_app_id` FOREIGN KEY (app_id) REFERENCES `app` (id) ON DELETE CASCADE,
  UNIQUE KEY `uk_app_id_username` (app_id, feishu_username)  -- 确保同一应用下用户名不重复
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='应用关联的飞书用户表';