-- 007_add_soft_delete_to_roles_and_relations.sql
-- 统一删除语义：角色、员工角色关系、角色权限关系都使用逻辑删除。

ALTER TABLE roles
  ADD COLUMN deleted_at DATETIME(3) NULL COMMENT '软删除时间，NULL 表示未删除' AFTER updated_at,
  ADD KEY idx_roles_deleted_at (deleted_at);

ALTER TABLE roles
  DROP INDEX uk_roles_code,
  ADD COLUMN active_code VARCHAR(50) GENERATED ALWAYS AS (
    CASE WHEN deleted_at IS NULL THEN code ELSE NULL END
  ) STORED COMMENT '仅用于保证未删除角色编码唯一' AFTER code,
  ADD UNIQUE KEY uk_roles_active_code (active_code);

ALTER TABLE employee_roles
  DROP FOREIGN KEY fk_employee_roles_employee_id;

ALTER TABLE employee_roles
  ADD CONSTRAINT fk_employee_roles_employee_id
    FOREIGN KEY (employee_id) REFERENCES employees (id);

ALTER TABLE employee_roles
  ADD COLUMN deleted_at DATETIME(3) NULL COMMENT '逻辑解绑时间，NULL 表示当前仍绑定' AFTER created_at,
  ADD KEY idx_employee_roles_deleted_at (deleted_at);

ALTER TABLE role_permissions
  DROP FOREIGN KEY fk_role_permissions_role_id;

ALTER TABLE role_permissions
  ADD CONSTRAINT fk_role_permissions_role_id
    FOREIGN KEY (role_id) REFERENCES roles (id);

ALTER TABLE role_permissions
  ADD COLUMN deleted_at DATETIME(3) NULL COMMENT '逻辑解绑时间，NULL 表示当前仍授权' AFTER created_at,
  ADD KEY idx_role_permissions_deleted_at (deleted_at);