-- Flyway migration V1 — initial schema for 小羚羊
-- Generated: 2026-05-13T07:35:36Z
-- Source: 由 A4 db-init 从 docs/03-数据库设计文档.md 翻译生成（schema SSoT 是 docs/03）
-- This is the FIRST migration; subsequent schema changes must be written as new files sql/migrations/V2__<desc>.sql, V3__... etc.
-- Apply: Flyway runs this automatically at Spring Boot startup.
-- Do not hand-edit this file after it is committed; write a new migration instead.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================
-- tUser — 系统用户主表
-- ============================================================
CREATE TABLE `tUser` (
  `iIncrement`       INT          NOT NULL AUTO_INCREMENT          COMMENT '整数主键 ID（标准列）',
  `sId`              VARCHAR(100) NULL                              COMMENT '业务 ID（标准列）',
  `sBrandsId`        VARCHAR(100) NULL                              COMMENT '品牌 ID（多租户隔离，标准列）',
  `sSubsidiaryId`    VARCHAR(100) NULL                              COMMENT '子公司 ID（组织层级隔离，标准列）',
  `tCreateDate`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间（标准列）',
  `sUserCode`        VARCHAR(32)  NOT NULL                          COMMENT '用户号，业务唯一编码',
  `sUserName`        VARCHAR(50)  NOT NULL                          COMMENT '用户名（登录标识），全局唯一',
  `iEmployeeId`      INT          NULL     DEFAULT NULL             COMMENT '关联 tEmployee.iIncrement，可选；选填后页面回显员工名/部门',
  `sUserType`        VARCHAR(20)  NOT NULL DEFAULT 'NORMAL'         COMMENT '用户类型：NORMAL=普通用户，ADMIN=超级管理员',
  `sLanguage`        VARCHAR(16)  NOT NULL DEFAULT 'zh-CN'          COMMENT '界面语言：zh-CN 中文，en 英文，zh-TW 繁体',
  `iCanEditDoc`      TINYINT(1)   NOT NULL DEFAULT 0                COMMENT '单据修改权限：0=否，1=是',
  `sPasswordHash`    VARCHAR(100) NOT NULL                          COMMENT '密码哈希（BCrypt 或同强度算法），初始值由系统生成；以哈希形式存储',
  `iIsDisabled`      TINYINT(1)   NOT NULL DEFAULT 0                COMMENT '作废标志：0=有效，1=已作废（被作废后不可登录）',
  `tLastLoginDate`   DATETIME     NULL     DEFAULT NULL             COMMENT '最近一次登录时间，登录成功时更新',
  `iLoginFailCount`  INT          NOT NULL DEFAULT 0                COMMENT '连续登录失败计数，登录成功清零（用于阈值锁定）',
  `tLockedUntil`     DATETIME     NULL     DEFAULT NULL             COMMENT '临时锁定截止时间；非空且大于当前时刻视为锁定',
  `sCreatedBy`       VARCHAR(50)  NULL     DEFAULT NULL             COMMENT '制单人（创建该用户的操作员用户名）',
  `tUpdateDate`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`iIncrement`),
  UNIQUE KEY `uk_tUser_sUserName` (`sUserName`),
  UNIQUE KEY `uk_tUser_sUserCode` (`sUserCode`),
  KEY `idx_tUser_iEmployeeId` (`iEmployeeId`),
  KEY `idx_tUser_tenant_status` (`sBrandsId`, `sSubsidiaryId`, `iIsDisabled`),
  KEY `idx_tUser_tLastLoginDate` (`tLastLoginDate`),
  KEY `idx_tUser_sUserType` (`sUserType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统用户主表';

-- ============================================================
-- tEmployee — 职员字典
-- ============================================================
CREATE TABLE `tEmployee` (
  `iIncrement`      INT          NOT NULL AUTO_INCREMENT          COMMENT '整数主键 ID（标准列）',
  `sId`             VARCHAR(100) NULL                              COMMENT '业务 ID（标准列）',
  `sBrandsId`       VARCHAR(100) NULL                              COMMENT '品牌 ID（多租户隔离，标准列）',
  `sSubsidiaryId`   VARCHAR(100) NULL                              COMMENT '子公司 ID（组织层级隔离，标准列）',
  `tCreateDate`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间（标准列）',
  `sEmployeeCode`   VARCHAR(32)  NOT NULL                          COMMENT '员工号，业务唯一编码',
  `sEmployeeName`   VARCHAR(50)  NOT NULL                          COMMENT '员工姓名',
  `sDepartment`     VARCHAR(100) NULL     DEFAULT NULL             COMMENT '所属部门名',
  `iIsDisabled`     TINYINT(1)   NOT NULL DEFAULT 0                COMMENT '作废标志：0=有效，1=已作废',
  `tUpdateDate`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`iIncrement`),
  UNIQUE KEY `uk_tEmployee_sEmployeeCode` (`sEmployeeCode`),
  KEY `idx_tEmployee_sEmployeeName` (`sEmployeeName`),
  KEY `idx_tEmployee_sDepartment` (`sDepartment`),
  KEY `idx_tEmployee_tenant` (`sBrandsId`, `sSubsidiaryId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='职员字典';

-- ============================================================
-- tPermission — 权限分类字典
-- ============================================================
CREATE TABLE `tPermission` (
  `iIncrement`      INT          NOT NULL AUTO_INCREMENT          COMMENT '整数主键 ID（标准列）',
  `sId`             VARCHAR(100) NULL                              COMMENT '业务 ID（标准列）',
  `sBrandsId`       VARCHAR(100) NULL                              COMMENT '品牌 ID（多租户隔离，标准列）',
  `sSubsidiaryId`   VARCHAR(100) NULL                              COMMENT '子公司 ID（组织层级隔离，标准列）',
  `tCreateDate`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间（标准列）',
  `sCategory`       VARCHAR(100) NOT NULL                          COMMENT '权限分类编码（如 usr:user:create）',
  `sCategoryName`   VARCHAR(100) NOT NULL                          COMMENT '权限分类中文名（用于权限组复选框展示）',
  `sDescription`    VARCHAR(255) NULL     DEFAULT NULL             COMMENT '权限说明',
  `iIsDisabled`     TINYINT(1)   NOT NULL DEFAULT 0                COMMENT '作废标志：0=有效，1=已作废',
  `tUpdateDate`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`iIncrement`),
  UNIQUE KEY `uk_tPermission_sCategory` (`sCategory`),
  KEY `idx_tPermission_sCategoryName` (`sCategoryName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='权限分类字典';

-- ============================================================
-- tUserPermission — 用户 ↔ 权限多对多关联表
-- ============================================================
CREATE TABLE `tUserPermission` (
  `iIncrement`      INT          NOT NULL AUTO_INCREMENT          COMMENT '整数主键 ID（标准列）',
  `sId`             VARCHAR(100) NULL                              COMMENT '业务 ID（标准列）',
  `sBrandsId`       VARCHAR(100) NULL                              COMMENT '品牌 ID（多租户隔离，标准列）',
  `sSubsidiaryId`   VARCHAR(100) NULL                              COMMENT '子公司 ID（组织层级隔离，标准列）',
  `tCreateDate`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间（标准列）',
  `iUserId`         INT          NOT NULL                          COMMENT '用户主键，关联 tUser.iIncrement',
  `iPermissionId`   INT          NOT NULL                          COMMENT '权限主键，关联 tPermission.iIncrement',
  `sGrantedBy`      VARCHAR(50)  NULL     DEFAULT NULL             COMMENT '授权操作员用户名（审计用）',
  PRIMARY KEY (`iIncrement`),
  UNIQUE KEY `uk_tUserPermission_user_perm` (`iUserId`, `iPermissionId`),
  KEY `idx_tUserPermission_iPermissionId` (`iPermissionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户 ↔ 权限多对多关联表';

-- ============================================================
-- tCompany — 公司及版本字典
-- ============================================================
CREATE TABLE `tCompany` (
  `iIncrement`      INT          NOT NULL AUTO_INCREMENT          COMMENT '整数主键 ID（标准列）',
  `sId`             VARCHAR(100) NULL                              COMMENT '业务 ID（标准列）',
  `sBrandsId`       VARCHAR(100) NULL                              COMMENT '品牌 ID（多租户隔离，标准列）',
  `sSubsidiaryId`   VARCHAR(100) NULL                              COMMENT '子公司 ID（组织层级隔离，标准列）',
  `tCreateDate`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间（标准列）',
  `sCompanyName`    VARCHAR(100) NOT NULL                          COMMENT '公司名称',
  `sEdition`        VARCHAR(32)  NOT NULL DEFAULT '标准版'           COMMENT '版本：标准版 / 专业版 / 企业版 等',
  `iIsDisabled`     TINYINT(1)   NOT NULL DEFAULT 0                COMMENT '作废标志',
  `tUpdateDate`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`iIncrement`),
  KEY `idx_tCompany_sCompanyName` (`sCompanyName`),
  KEY `idx_tCompany_sEdition` (`sEdition`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='公司及版本字典';

-- ============================================================
-- 外键
-- ============================================================
ALTER TABLE `tUser`
  ADD CONSTRAINT `fk_tUser_iEmployeeId`
  FOREIGN KEY (`iEmployeeId`) REFERENCES `tEmployee` (`iIncrement`)
  ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `tUserPermission`
  ADD CONSTRAINT `fk_tUserPermission_iUserId`
  FOREIGN KEY (`iUserId`) REFERENCES `tUser` (`iIncrement`)
  ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `tUserPermission`
  ADD CONSTRAINT `fk_tUserPermission_iPermissionId`
  FOREIGN KEY (`iPermissionId`) REFERENCES `tPermission` (`iIncrement`)
  ON DELETE RESTRICT ON UPDATE CASCADE;

SET FOREIGN_KEY_CHECKS = 1;