湛兮
170 lines
9.5 KiB
SQL
170 lines
9.5 KiB
SQL
-- 008_add_role_user_workbench_tables.sql
|
|
-- C 端正式版第一批能力：公告、任务、排班、凭据审计和临时密码状态。
|
|
|
|
CREATE TABLE IF NOT EXISTS announcements (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
title VARCHAR(120) NOT NULL COMMENT '公告标题',
|
|
content TEXT NOT NULL COMMENT '公告正文',
|
|
level ENUM('NORMAL', 'IMPORTANT', 'URGENT') NOT NULL DEFAULT 'NORMAL' COMMENT '重要级别',
|
|
status ENUM('DRAFT', 'PUBLISHED', 'ARCHIVED') NOT NULL DEFAULT 'DRAFT' COMMENT '发布状态',
|
|
target_type ENUM('ALL', 'STORE', 'ROLE', 'EMPLOYEE') NOT NULL DEFAULT 'ALL' COMMENT '目标范围',
|
|
publisher_admin_id INT UNSIGNED NULL COMMENT '发布超级管理员',
|
|
publisher_employee_id INT UNSIGNED NULL COMMENT '发布员工',
|
|
published_at DATETIME(3) NULL COMMENT '发布时间',
|
|
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
|
deleted_at DATETIME(3) NULL COMMENT '软删除时间',
|
|
PRIMARY KEY (id),
|
|
KEY idx_announcements_status_published_at (status, published_at),
|
|
KEY idx_announcements_target_type (target_type),
|
|
KEY idx_announcements_deleted_at (deleted_at),
|
|
CONSTRAINT fk_announcements_publisher_admin_id FOREIGN KEY (publisher_admin_id) REFERENCES super_admins (id),
|
|
CONSTRAINT fk_announcements_publisher_employee_id FOREIGN KEY (publisher_employee_id) REFERENCES employees (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='公告表';
|
|
|
|
CREATE TABLE IF NOT EXISTS announcement_targets (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
announcement_id INT UNSIGNED NOT NULL,
|
|
target_type ENUM('STORE', 'ROLE', 'EMPLOYEE') NOT NULL,
|
|
target_id INT UNSIGNED NOT NULL,
|
|
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uk_announcement_targets_scope (announcement_id, target_type, target_id),
|
|
KEY idx_announcement_targets_target (target_type, target_id),
|
|
CONSTRAINT fk_announcement_targets_announcement_id FOREIGN KEY (announcement_id) REFERENCES announcements (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='公告目标表';
|
|
|
|
CREATE TABLE IF NOT EXISTS announcement_reads (
|
|
announcement_id INT UNSIGNED NOT NULL,
|
|
employee_id INT UNSIGNED NOT NULL,
|
|
read_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
PRIMARY KEY (announcement_id, employee_id),
|
|
KEY idx_announcement_reads_employee_id (employee_id),
|
|
CONSTRAINT fk_announcement_reads_announcement_id FOREIGN KEY (announcement_id) REFERENCES announcements (id),
|
|
CONSTRAINT fk_announcement_reads_employee_id FOREIGN KEY (employee_id) REFERENCES employees (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='公告已读记录表';
|
|
|
|
CREATE TABLE IF NOT EXISTS tasks (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
store_id INT UNSIGNED NULL COMMENT '任务所属门店，NULL 表示跨门店',
|
|
title VARCHAR(120) NOT NULL COMMENT '任务标题',
|
|
description TEXT NULL COMMENT '任务说明',
|
|
status ENUM('PENDING', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED') NOT NULL DEFAULT 'PENDING',
|
|
priority ENUM('LOW', 'NORMAL', 'HIGH', 'URGENT') NOT NULL DEFAULT 'NORMAL',
|
|
due_at DATETIME(3) NULL COMMENT '截止时间',
|
|
creator_admin_id INT UNSIGNED NULL COMMENT '创建超级管理员',
|
|
creator_employee_id INT UNSIGNED NULL COMMENT '创建员工',
|
|
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
|
deleted_at DATETIME(3) NULL COMMENT '软删除时间',
|
|
PRIMARY KEY (id),
|
|
KEY idx_tasks_store_status (store_id, status),
|
|
KEY idx_tasks_status_due_at (status, due_at),
|
|
KEY idx_tasks_deleted_at (deleted_at),
|
|
CONSTRAINT fk_tasks_store_id FOREIGN KEY (store_id) REFERENCES stores (id),
|
|
CONSTRAINT fk_tasks_creator_admin_id FOREIGN KEY (creator_admin_id) REFERENCES super_admins (id),
|
|
CONSTRAINT fk_tasks_creator_employee_id FOREIGN KEY (creator_employee_id) REFERENCES employees (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='任务表';
|
|
|
|
CREATE TABLE IF NOT EXISTS task_assignees (
|
|
task_id INT UNSIGNED NOT NULL,
|
|
employee_id INT UNSIGNED NOT NULL,
|
|
assigned_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
PRIMARY KEY (task_id, employee_id),
|
|
KEY idx_task_assignees_employee_id (employee_id),
|
|
CONSTRAINT fk_task_assignees_task_id FOREIGN KEY (task_id) REFERENCES tasks (id),
|
|
CONSTRAINT fk_task_assignees_employee_id FOREIGN KEY (employee_id) REFERENCES employees (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='任务分配表';
|
|
|
|
CREATE TABLE IF NOT EXISTS task_events (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
task_id INT UNSIGNED NOT NULL,
|
|
employee_id INT UNSIGNED NULL,
|
|
actor_admin_id INT UNSIGNED NULL,
|
|
actor_employee_id INT UNSIGNED NULL,
|
|
event_type ENUM('CREATED', 'UPDATED', 'STARTED', 'COMPLETED', 'CANCELLED', 'COMMENTED') NOT NULL,
|
|
from_status ENUM('PENDING', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED') NULL,
|
|
to_status ENUM('PENDING', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED') NULL,
|
|
comment VARCHAR(1000) NULL,
|
|
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
PRIMARY KEY (id),
|
|
KEY idx_task_events_task_id (task_id, created_at),
|
|
KEY idx_task_events_employee_id (employee_id),
|
|
CONSTRAINT fk_task_events_task_id FOREIGN KEY (task_id) REFERENCES tasks (id),
|
|
CONSTRAINT fk_task_events_employee_id FOREIGN KEY (employee_id) REFERENCES employees (id),
|
|
CONSTRAINT fk_task_events_actor_admin_id FOREIGN KEY (actor_admin_id) REFERENCES super_admins (id),
|
|
CONSTRAINT fk_task_events_actor_employee_id FOREIGN KEY (actor_employee_id) REFERENCES employees (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='任务事件表';
|
|
|
|
CREATE TABLE IF NOT EXISTS shifts (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
store_id INT UNSIGNED NOT NULL,
|
|
employee_id INT UNSIGNED NOT NULL,
|
|
role_name VARCHAR(50) NULL COMMENT '班次岗位',
|
|
start_at DATETIME(3) NOT NULL,
|
|
end_at DATETIME(3) NOT NULL,
|
|
status ENUM('SCHEDULED', 'CANCELLED') NOT NULL DEFAULT 'SCHEDULED',
|
|
creator_admin_id INT UNSIGNED NULL,
|
|
creator_employee_id INT UNSIGNED NULL,
|
|
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
|
deleted_at DATETIME(3) NULL COMMENT '软删除时间',
|
|
PRIMARY KEY (id),
|
|
KEY idx_shifts_employee_start (employee_id, start_at),
|
|
KEY idx_shifts_store_start (store_id, start_at),
|
|
KEY idx_shifts_deleted_at (deleted_at),
|
|
CONSTRAINT fk_shifts_store_id FOREIGN KEY (store_id) REFERENCES stores (id),
|
|
CONSTRAINT fk_shifts_employee_id FOREIGN KEY (employee_id) REFERENCES employees (id),
|
|
CONSTRAINT fk_shifts_creator_admin_id FOREIGN KEY (creator_admin_id) REFERENCES super_admins (id),
|
|
CONSTRAINT fk_shifts_creator_employee_id FOREIGN KEY (creator_employee_id) REFERENCES employees (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='排班表';
|
|
|
|
CREATE TABLE IF NOT EXISTS credential_audits (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
actor_account_type ENUM('SUPER_ADMIN', 'EMPLOYEE') NOT NULL,
|
|
actor_admin_id INT UNSIGNED NULL,
|
|
actor_employee_id INT UNSIGNED NULL,
|
|
target_employee_id INT UNSIGNED NOT NULL,
|
|
action ENUM('RESET_PASSWORD', 'CHANGE_OWN_PASSWORD') NOT NULL,
|
|
reason VARCHAR(500) NULL,
|
|
ip VARCHAR(64) NULL,
|
|
user_agent VARCHAR(500) NULL,
|
|
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
PRIMARY KEY (id),
|
|
KEY idx_credential_audits_target_created (target_employee_id, created_at),
|
|
KEY idx_credential_audits_actor_employee_id (actor_employee_id),
|
|
CONSTRAINT fk_credential_audits_actor_admin_id FOREIGN KEY (actor_admin_id) REFERENCES super_admins (id),
|
|
CONSTRAINT fk_credential_audits_actor_employee_id FOREIGN KEY (actor_employee_id) REFERENCES employees (id),
|
|
CONSTRAINT fk_credential_audits_target_employee_id FOREIGN KEY (target_employee_id) REFERENCES employees (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='凭据操作审计表';
|
|
|
|
CREATE TABLE IF NOT EXISTS employee_password_states (
|
|
employee_id INT UNSIGNED NOT NULL,
|
|
must_change_password TINYINT(1) NOT NULL DEFAULT 0,
|
|
last_reset_at DATETIME(3) NULL,
|
|
last_reset_by_admin_id INT UNSIGNED NULL,
|
|
last_reset_by_employee_id INT UNSIGNED NULL,
|
|
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
|
PRIMARY KEY (employee_id),
|
|
CONSTRAINT fk_employee_password_states_employee_id FOREIGN KEY (employee_id) REFERENCES employees (id),
|
|
CONSTRAINT fk_employee_password_states_admin_id FOREIGN KEY (last_reset_by_admin_id) REFERENCES super_admins (id),
|
|
CONSTRAINT fk_employee_password_states_employee_actor_id FOREIGN KEY (last_reset_by_employee_id) REFERENCES employees (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='员工密码状态表';
|
|
|
|
INSERT IGNORE INTO role_permissions (role_id, permission_code)
|
|
SELECT r.id, p.permission_code
|
|
FROM roles r
|
|
INNER JOIN (
|
|
SELECT 'admin' AS role_code, 'announcement:view' AS permission_code
|
|
UNION ALL SELECT 'admin', 'announcement:manage'
|
|
UNION ALL SELECT 'admin', 'task:view'
|
|
UNION ALL SELECT 'admin', 'task:manage'
|
|
UNION ALL SELECT 'admin', 'shift:view'
|
|
UNION ALL SELECT 'admin', 'shift:manage'
|
|
UNION ALL SELECT 'admin', 'credential:reset'
|
|
UNION ALL SELECT 'admin', 'credential:audit:view'
|
|
UNION ALL SELECT 'store_manager', 'announcement:view'
|
|
UNION ALL SELECT 'store_manager', 'task:view'
|
|
UNION ALL SELECT 'store_manager', 'shift:view'
|
|
) p ON p.role_code = r.code;
|