-- 001_initial_schema.sql
-- 这个迁移文件负责创建项目的基础表结构。
-- 迁移脚本会按文件名排序执行，所以 001 会先于 002 执行。

-- 门店表：保存每个门店的基础信息。
-- deleted_at 用于软删除，业务查询通常只查询 deleted_at IS NULL 的数据。
CREATE TABLE IF NOT EXISTS stores (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL COMMENT '门店名称',
  address VARCHAR(255) NULL COMMENT '门店地址',
  phone VARCHAR(30) NULL COMMENT '门店联系电话',
  status ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE' 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 '软删除时间，NULL 表示未删除',
  PRIMARY KEY (id),
  KEY idx_stores_status (status),
  KEY idx_stores_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='门店表';

-- 角色表：保存系统内可分配给员工的角色。
-- code 是稳定的角色编码，适合在代码里做权限判断；name 是展示给用户看的名称。
CREATE TABLE IF NOT EXISTS roles (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(50) NOT NULL COMMENT '角色编码，代码里使用这个值做权限判断',
  name VARCHAR(50) NOT NULL COMMENT '角色名称',
  description VARCHAR(255) 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),
  PRIMARY KEY (id),
  UNIQUE KEY uk_roles_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='角色表';

-- 员工表：保存员工基础资料，并通过 store_id 关联所属门店。
-- 这里不直接存角色列表，因为一个员工可以有多个角色，角色关系放在 employee_roles 表。
CREATE TABLE IF NOT EXISTS employees (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  store_id INT UNSIGNED NOT NULL COMMENT '所属门店',
  name VARCHAR(50) NOT NULL COMMENT '员工姓名',
  phone VARCHAR(30) NOT NULL COMMENT '员工手机号',
  status ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE' COMMENT '员工状态',
  remark VARCHAR(500) 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 '软删除时间，NULL 表示未删除',
  -- MySQL 的唯一索引允许多个 NULL。
  -- 软删除后 active_phone 会变成 NULL，这样同一门店可以重新录入相同手机号的新员工。
  active_phone VARCHAR(30) GENERATED ALWAYS AS (
    CASE WHEN deleted_at IS NULL THEN phone ELSE NULL END
  ) STORED COMMENT '仅用于保证同一门店未删除员工手机号唯一',
  PRIMARY KEY (id),
  UNIQUE KEY uk_employees_store_active_phone (store_id, active_phone),
  KEY idx_employees_store_status (store_id, status),
  KEY idx_employees_deleted_at (deleted_at),
  CONSTRAINT fk_employees_store_id FOREIGN KEY (store_id) REFERENCES stores (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='员工表';

-- 员工角色关系表：连接 employees 和 roles。
-- 一个员工可以有多个角色，一个角色也可以分配给多个员工，所以这是多对多关系表。
CREATE TABLE IF NOT EXISTS employee_roles (
  employee_id INT UNSIGNED NOT NULL,
  role_id INT UNSIGNED NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (employee_id, role_id),
  KEY idx_employee_roles_role_id (role_id),
  CONSTRAINT fk_employee_roles_employee_id FOREIGN KEY (employee_id) REFERENCES employees (id) ON DELETE CASCADE,
  CONSTRAINT fk_employee_roles_role_id FOREIGN KEY (role_id) REFERENCES roles (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='员工角色关系表';