-- 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;
