/* ============================================================================
   FMS 新系统核心表建表脚本 V2
   依据文档：FMS新系统核心表结构设计V2.md

   使用说明：
   1. 执行前先切换到目标数据库（USE [数据库名]）。
   2. 脚本只建表和索引，不含初始化数据。
   3. 不使用外键、CHECK、触发器；关联完整性和业务校验由业务层处理。
   4. 表按依赖顺序排列：组织与用户 -> 模块 -> 字段与界面 -> 配置 -> 关系
      -> 菜单 -> 多语言 -> 日志 -> 权限。
   ============================================================================ */

SET NOCOUNT ON;
GO

/* ============================================================================
   3.1 用户
   ============================================================================ */

-- 用户表
create table dbo.b_user (
    b_id varchar(50) not null primary key, -- 用户编码（业务键主键）
    b_name nvarchar(100) null,             -- 用户名
    b_dept_id varchar(50) null,            -- 所属部门编码
    b_bz nvarchar(400) null,               -- 备注
    b_password nvarchar(255) not null,     -- 密码（明文保存）
    b_canuse tinyint not null default 1,   -- 是否启用（0/1）
    b_logincount int not null default 0,   -- 登录次数
    b_created_by varchar(50) null,         -- 创建人
    b_created_at datetime2 null,           -- 创建时间
    b_updated_by varchar(50) null,         -- 最后修改人
    b_updated_at datetime2 null            -- 最后修改时间
);
GO

create index ix_b_user_dept
    on dbo.b_user (b_dept_id, b_canuse, b_id);
GO

/* ============================================================================
   3.2 部门
   ============================================================================ */

-- 部门表
create table dbo.b_dept (
    b_id varchar(50) not null primary key, -- 部门编码（业务键主键）
    b_parent_id varchar(50) null,          -- 父部门编码，NULL 表示根节点
    b_depth int not null default 0,        -- 树层级，根节点为 0
    b_path varchar(1000) not null,         -- 根到当前部门的路径，例如 /cn/east/
    b_name nvarchar(200) not null,         -- 部门名称
    b_i18n varchar(150) null,              -- 多语言资源键
    b_canuse tinyint not null default 1,   -- 是否启用（0/1）
    b_xh int not null default 0,           -- 同级显示顺序
    b_bz nvarchar(2000) null,              -- 备注
    b_created_by varchar(50) null,         -- 创建人
    b_created_at datetime2 null,           -- 创建时间
    b_updated_by varchar(50) null,         -- 最后修改人
    b_updated_at datetime2 null            -- 最后修改时间
);
GO

create index ix_b_dept_parent
    on dbo.b_dept (b_parent_id, b_canuse, b_depth, b_xh, b_id);
GO

create index ix_b_dept_path
    on dbo.b_dept (b_path, b_canuse, b_id);
GO

/* ============================================================================
   4.1 模块定义
   ============================================================================ */

-- 模块定义表
create table dbo.s_module (
    b_id varchar(50) not null primary key,   -- 模块编码（业务键主键）
    b_parent_id varchar(50) null,            -- 父模块编码，NULL 表示根节点
    b_depth int not null default 0,          -- 树层级，根节点为 0
    b_path varchar(1000) not null,           -- 根到当前节点的路径，例如 /sea/container/
    b_name nvarchar(200) not null,           -- 模块名称
    b_i18n varchar(150) null,                -- 多语言资源键
    b_module_type varchar(20) not null,      -- module / data / virtual
    b_view_table varchar(128) null,          -- 默认查询表或视图
    b_save_table varchar(128) null,          -- 默认保存目标表
    b_key_field varchar(50) null,            -- 主键字段
    b_scope_field varchar(50) null,          -- 数据范围字段（记录归属人字段，如 b_inputuser_id）
    b_order_sql varchar(500) null,           -- 默认排序 SQL
    b_query_sql nvarchar(max) null,          -- 可选查询 SQL 或 SQL 模板
    b_config_json nvarchar(max) null,        -- 低频模块扩展配置
    b_canuse tinyint not null default 1,     -- 是否启用（0/1）
    b_xh int not null default 0,             -- 同级显示顺序
    b_bz nvarchar(2000) null,                -- 备注
    b_updated_at datetime2 null              -- 最后保存时间（本模块任意配置保存时刷新，含字段 / 界面配置等子表）
);
GO

create index ix_s_module_parent
    on dbo.s_module (b_parent_id, b_canuse, b_depth, b_xh, b_id);
GO

create index ix_s_module_path
    on dbo.s_module (b_path, b_canuse, b_id);
GO

create index ix_s_module_type
    on dbo.s_module (b_module_type, b_canuse, b_xh, b_id);
GO

create index ix_s_module_view_table
    on dbo.s_module (b_view_table, b_canuse, b_id);
GO

create index ix_s_module_save_table
    on dbo.s_module (b_save_table, b_canuse, b_id);
GO

/* ============================================================================
   5. 字段定义
   ============================================================================ */

-- 字段定义表
create table dbo.s_field (
    b_module_id varchar(50) not null,              -- 所属 data / virtual 模块编码
    b_field varchar(50) not null,                  -- 模块字段编码或查询结果别名
    b_name nvarchar(200) not null,                 -- 字段名称
    b_i18n varchar(150) null,                      -- 多语言资源键
    b_type varchar(30) not null,                   -- 业务类型
    b_canuse tinyint not null default 1,           -- 是否启用（0/1）
    b_xh int not null default 0,                   -- 默认顺序
    primary key (b_module_id, b_field)
);
GO

create index ix_s_field_module_order
    on dbo.s_field (b_module_id, b_canuse, b_xh, b_field);
GO

/* ============================================================================
   6. 模块界面配置
   ============================================================================ */

-- 模块界面配置表
create table dbo.s_module_schema (
    b_module_id varchar(50) not null,         -- 所属 data / virtual 模块编码
    b_schema_type varchar(20) not null,       -- view / edit / query
    b_schema_json nvarchar(max) not null,     -- 完整 JSON 配置
    b_canuse tinyint not null default 1,      -- 是否启用（0/1）
    b_updated_by varchar(50) null,            -- 最后修改人
    b_updated_at datetime2 null,              -- 最后修改时间
    primary key (b_module_id, b_schema_type)
);
GO

create index ix_s_module_schema_type
    on dbo.s_module_schema (b_module_id, b_schema_type, b_canuse);
GO

/* ============================================================================
   7. 用户个性化
   ============================================================================ */

-- 用户模块偏好表（个人覆盖：在 s_module_schema 系统默认之上）
create table dbo.s_user_module_pref (
    b_user_id varchar(50) not null,       -- 用户编码（关联 b_user.b_id）
    b_module_id varchar(50) not null,     -- 模块编码
    b_schema_type varchar(20) not null,   -- view / edit / query
    b_schema_json nvarchar(max) not null, -- 个人覆盖 JSON（相对系统默认）
    b_updated_by varchar(50) null,        -- 最后修改人
    b_updated_at datetime2 null,          -- 最后修改时间
    primary key (b_user_id, b_module_id, b_schema_type)
);
GO

create index ix_s_user_module_pref_module
    on dbo.s_user_module_pref (b_module_id, b_user_id);
GO

/* ============================================================================
   8. 自动编码
   ============================================================================ */

-- 自动编码表
create table dbo.s_autocode (
    b_module_id varchar(50) not null,                    -- data 模块编码
    b_field varchar(50) not null,                        -- 编码字段
    b_prefix nvarchar(200) not null default N'',         -- 编码前缀
    b_dateformat varchar(30) not null default 'yyyyMM',  -- 日期格式
    b_separator nvarchar(20) not null default N'-',      -- 分隔符
    b_seqwidth int not null default 5,                   -- 序号宽度
    b_resettype varchar(10) not null default 'month',    -- 重置类型
    b_startvalue bigint not null default 1,              -- 起始值
    b_currentperiod varchar(20) null,                    -- 当前周期
    b_currentvalue bigint not null default 0,            -- 当前值
    b_canuse tinyint not null default 1,                 -- 是否启用（0/1）
    primary key (b_module_id, b_field)
);
GO

/* ============================================================================
   9. 模块关系
   ============================================================================ */

-- 模块关系表
create table dbo.s_relation (
    b_source_module_id varchar(50) not null,   -- 源模块
    b_source_field varchar(50) not null,       -- 源字段
    b_target_module_id varchar(50) not null,   -- 目标模块
    b_target_field varchar(50) not null,       -- 目标字段
    b_relation_type varchar(20) not null,      -- one_to_one / one_to_many / many_to_one
    b_on_delete varchar(20) not null default 'restrict', -- 删除处置：restrict有外部引用即拒绝 / cascade连带删除 / none不处理不保护
    b_canuse tinyint not null default 1,       -- 是否启用（0/1）
    b_xh int not null default 0,               -- 显示顺序
    primary key (
        b_source_module_id,
        b_source_field,
        b_target_module_id,
        b_target_field
    )
);
GO

create index ix_s_relation_source
    on dbo.s_relation (b_source_module_id, b_canuse, b_xh, b_source_field, b_target_module_id);
GO

create index ix_s_relation_target
    on dbo.s_relation (b_target_module_id, b_canuse, b_source_module_id);
GO

/* ============================================================================
   9.1 删除 SQL 规则

   模块级“什么情况下不允许删除”的规则：b_sql 是只读查询，返回至少一行即拒绝本批删除。
   只服务模块删除（target=module）；无模块的技术表走直接删除，不受本表约束。
   关系上的关联处置（含引用保护）由 s_relation.b_on_delete 表达，不再挂规则行。
   主键用雪花 id（新增时前端取号）：规则没有天然业务键，也不要求 IT 填编码。
   ============================================================================ */

create table dbo.s_delete_rule (
    b_id bigint not null,                      -- 雪花主键（新增时前端经 /data/nextid 取号）
    b_module_id varchar(50) not null,          -- 规则所属数据模块编码（对应 s_module.b_id）
    b_sql nvarchar(max) not null,              -- 只读查询 SQL，用 :ids 标记本次待删 ID 集合
    b_message nvarchar(500) null,              -- 删除失败时的提示文案（第一阶段固定文案）
    b_canuse tinyint not null default 1,       -- 是否启用（0/1）
    b_xh int not null default 0,               -- 执行顺序（并列按 b_xh、b_id）
    b_created_by varchar(50) null,             -- 审计列由服务端维护，配置界面不提交
    b_created_at datetime2 null,
    b_updated_by varchar(50) null,
    b_updated_at datetime2 null,
    primary key (b_id)
);
GO

create index ix_s_delete_rule_module
    on dbo.s_delete_rule (b_module_id, b_canuse, b_xh);
GO

/* ============================================================================
   10. 菜单
   ============================================================================ */

-- 菜单表
create table dbo.s_menu (
    b_id varchar(50) not null primary key, -- 菜单编码（业务键主键）
    b_parent_id varchar(50) null,          -- 父菜单编码，NULL 表示根节点
    b_depth int not null default 0,        -- 树层级，根节点为 0
    b_path varchar(1000) not null,         -- 根到当前菜单的路径，例如 /system/user/
    b_name nvarchar(200) not null,         -- 菜单名称
    b_i18n varchar(150) null,              -- 多语言资源键
    b_menu_type varchar(20) not null,      -- directory / page / external
    b_route varchar(500) null,             -- 页面路由或外链地址
    b_icon varchar(50) null,               -- 图标
    b_xh int not null default 0,           -- 显示顺序
    b_canuse tinyint not null default 1    -- 是否启用（0/1）
);
GO

create index ix_s_menu_parent
    on dbo.s_menu (b_parent_id, b_canuse, b_depth, b_xh, b_id);
GO

create index ix_s_menu_path
    on dbo.s_menu (b_path, b_canuse, b_id);
GO

create index ix_s_menu_route
    on dbo.s_menu (b_route, b_canuse, b_id);
GO

-- 菜单使用模块关系表
create table dbo.s_menu_module (
    b_menu_id varchar(50) not null,        -- 菜单编码
    b_module_id varchar(50) not null,      -- 页面实际使用的 data / virtual 模块编码
    b_xh int not null default 0,           -- 页面内模块顺序
    b_canuse tinyint not null default 1,   -- 是否启用（0/1）
    primary key (b_menu_id, b_module_id)
);
GO

create index ix_s_menu_module_module
    on dbo.s_menu_module (b_module_id, b_canuse, b_menu_id);
GO

/* ============================================================================
   11. 多语言
   ============================================================================ */

-- 多语言类型表
create table dbo.s_i18n_type (
    b_id varchar(20) not null primary key, -- 语言区域编码，例如 zh-CN / en
    b_name nvarchar(200) not null,         -- 语言名称
    b_canuse tinyint not null default 1,   -- 是否启用（0/1）
    b_default tinyint not null default 0,  -- 是否默认（0/1）
    b_xh int not null default 0            -- 显示顺序
);
GO

-- 多语言资源表
create table dbo.s_i18n (
    b_key varchar(150) not null,            -- 资源键
    b_locale varchar(20) not null,          -- 语言区域
    b_value nvarchar(1000) not null,        -- 翻译值
    b_canuse tinyint not null default 1,    -- 是否启用（0/1）
    primary key (b_key, b_locale)
);
GO

create index ix_s_i18n_locale
    on dbo.s_i18n (b_locale, b_canuse, b_key);
GO

/* ============================================================================
   12. 日志
   ============================================================================ */

-- 技术日志表
create table dbo.s_log_technical (
    b_id uniqueidentifier not null primary key, -- 日志 ID（UUIDv7）
    b_user_id varchar(50) null,                  -- 用户编码
    b_sourcetype varchar(20) null,               -- 来源类型
    b_loglevel varchar(16) not null,             -- 日志级别
    b_logcategory varchar(64) null,              -- 日志分类
    b_operation varchar(100) null,               -- 操作
    b_module_id varchar(50) null,                -- 模块编码
    b_request_id varchar(64) null,               -- 请求 ID
    b_trace_id varchar(64) null,                 -- 链路 ID
    b_server_node varchar(128) null,             -- 服务节点
    b_duration_ms bigint null,                   -- 耗时（毫秒）
    b_result_code varchar(50) null,              -- 结果码
    b_error_code varchar(50) null,               -- 错误码
    b_error_message nvarchar(4000) null,         -- 错误信息
    b_exception_text nvarchar(max) null,         -- 异常文本
    b_context_text nvarchar(max) null,           -- 上下文文本
    b_occurdatetime datetime2 default sysutcdatetime() -- 发生时间（UTC 墙钟值：全链路统一口径，见后端 TimeUtils）
);
GO

-- 登录日志表
create table dbo.s_log_login (
    b_id uniqueidentifier not null primary key, -- 日志 ID（UUIDv7）
    b_user_id varchar(50) null,                 -- 用户编码
    b_login_type varchar(20) not null,          -- 登录类型
    b_login_result tinyint not null default 0,  -- 登录结果（0/1）
    b_fail_reason nvarchar(400) null,           -- 失败原因
    b_ip varchar(50) null,                      -- IP
    b_ip_location nvarchar(400) null,           -- IP 归属地
    b_browser varchar(100) null,                -- 浏览器
    b_browser_version varchar(50) null,         -- 浏览器版本
    b_os varchar(100) null,                     -- 操作系统
    b_os_version varchar(50) null,              -- 系统版本
    b_device_type varchar(20) null,             -- 设备类型
    b_user_agent nvarchar(max) null,            -- UserAgent
    b_session_id varchar(50) null,              -- 会话 ID
    b_request_id varchar(64) null,              -- 请求 ID
    b_trace_id varchar(64) null,                -- 链路 ID
    b_occurdatetime datetime2 default sysutcdatetime() -- 发生时间（UTC 墙钟值：全链路统一口径，见后端 TimeUtils）
);
GO

-- 审计日志表
create table dbo.s_log_audit (
    b_id uniqueidentifier not null primary key, -- 日志 ID（UUIDv7）
    b_event_code varchar(50) not null,          -- 事件编码
    b_module_id varchar(50) null,               -- 模块编码
    b_data_id varchar(50) null,                 -- 数据 ID（雪花主键按字符串保存）
    b_business_no varchar(50) null,             -- 业务单号
    b_operation varchar(100) null,              -- 操作
    b_operator_id varchar(50) null,             -- 操作人 ID
    b_operator_name nvarchar(200) null,         -- 操作人名称
    b_source_type varchar(20) not null default 'user', -- 来源类型
    b_visibility varchar(20) not null default 'internal', -- 可见性
    b_request_id varchar(64) null,              -- 请求 ID
    b_trace_id varchar(64) null,                -- 链路 ID
    b_payload_text nvarchar(max) null,          -- 载荷文本
    b_occurdatetime datetime2 default sysutcdatetime() -- 发生时间（UTC 墙钟值：全链路统一口径，见后端 TimeUtils）
);
GO

-- 审计字段明细表
create table dbo.s_log_audit_field (
    b_id uniqueidentifier not null primary key, -- 日志 ID（UUIDv7）
    b_event_id uniqueidentifier not null,       -- 审计事件 ID
    b_module_id varchar(50) null,               -- 模块编码
    b_data_id varchar(50) null,                 -- 数据 ID（雪花主键按字符串保存）
    b_field varchar(50) not null,               -- 字段编码
    b_field_label nvarchar(200) null,           -- 字段名称
    b_before_value nvarchar(max) null,          -- 变更前值
    b_after_value nvarchar(max) null,           -- 变更后值
    b_before_display nvarchar(1000) null,       -- 变更前显示值
    b_after_display nvarchar(1000) null,        -- 变更后显示值
    b_visibility varchar(20) not null default 'internal' -- 可见性
);
GO

create index ix_s_log_technical_occur
    on dbo.s_log_technical (b_occurdatetime, b_id);
GO

create index ix_s_log_technical_trace
    on dbo.s_log_technical (b_trace_id, b_occurdatetime);
GO

create index ix_s_log_login_occur
    on dbo.s_log_login (b_occurdatetime, b_id);
GO

create index ix_s_log_login_user
    on dbo.s_log_login (b_user_id, b_occurdatetime);
GO

create index ix_s_log_audit_occur
    on dbo.s_log_audit (b_occurdatetime, b_id);
GO

create index ix_s_log_audit_module
    on dbo.s_log_audit (b_module_id, b_data_id, b_occurdatetime);
GO

create index ix_s_log_audit_field_event
    on dbo.s_log_audit_field (b_event_id, b_id);
GO

/* ============================================================================
   14. 权限设计
   ============================================================================ */

-- 权限点定义表
create table dbo.s_power (
    b_id varchar(150) not null primary key,  -- 权限编码，如 module.sea、menu.sea_list
    b_name nvarchar(200) not null,           -- 权限名称
    b_i18n varchar(150) null,                -- 多语言资源键
    b_type varchar(20) not null,             -- menu / module / action（权限点性质）
    b_object_type varchar(20) not null,      -- menu / module（挂在谁身上）
    b_object_id varchar(50) not null,        -- s_menu.b_id 或 s_module.b_id
    b_action varchar(30) not null,           -- access（menu / module 使用权限）或具体业务动作（action）
    b_canuse tinyint not null default 1,     -- 是否启用（0/1）
    b_xh int not null default 0,             -- 显示顺序
    b_bz nvarchar(500) null                  -- 备注
);
GO

create index ix_s_power_type
    on dbo.s_power (b_type, b_canuse, b_xh, b_id);
GO

create index ix_s_power_object
    on dbo.s_power (b_object_type, b_object_id, b_canuse, b_action, b_id);
GO

create unique index ux_s_power_object_action
    on dbo.s_power (b_object_type, b_object_id, b_action);
GO

-- 用户权限表
create table dbo.s_user_power (
    b_user_id varchar(50) not null,       -- 用户编码
    b_power_id varchar(150) not null,     -- 权限编码
    b_canuse tinyint not null default 1,  -- 是否启用（0/1）
    b_created_by varchar(50) null,        -- 创建人
    b_created_at datetime2 null,          -- 创建时间
    b_updated_by varchar(50) null,        -- 最后修改人
    b_updated_at datetime2 null,          -- 最后修改时间
    primary key (b_user_id, b_power_id)
);
GO

create index ix_s_user_power_power
    on dbo.s_user_power (b_power_id, b_user_id);
GO

-- 用户字段权限表
create table dbo.s_user_field_power (
    b_user_id varchar(50) not null,       -- 用户编码
    b_module_id varchar(50) not null,     -- 模块编码
    b_field_id varchar(50) not null,      -- 字段编码，对应 s_field.b_field
    b_view tinyint not null default 1,    -- 查看（0/1）
    b_edit tinyint not null default 0,    -- 编辑（0/1）
    b_query tinyint not null default 1,   -- 查询（0/1）
    b_export tinyint not null default 1,  -- 导出（0/1）
    b_canuse tinyint not null default 1,  -- 是否启用（0/1）
    b_created_by varchar(50) null,        -- 创建人
    b_created_at datetime2 null,          -- 创建时间
    b_updated_by varchar(50) null,        -- 最后修改人
    b_updated_at datetime2 null,          -- 最后修改时间
    primary key (b_user_id, b_module_id, b_field_id)
);
GO

create index ix_s_user_field_power_module
    on dbo.s_user_field_power (b_module_id, b_user_id, b_field_id);
GO

-- 用户数据范围表
create table dbo.s_user_data_power (
    b_user_id varchar(50) not null,               -- 用户编码
    b_module_id varchar(50) not null,             -- 模块编码
    b_operation varchar(30) not null default '*', -- 操作，* 表示全部操作
    b_scope_type varchar(20) not null,            -- all / self / dept / dept_tree / custom
    b_scope_field varchar(50) null,               -- 模块中用于范围判断的字段
    b_condition_sql nvarchar(max) null,           -- 自定义条件 SQL（custom 时使用）
    b_xh int not null default 0,                  -- 同操作内规则顺序
    b_canuse tinyint not null default 1,          -- 是否启用（0/1）
    b_created_by varchar(50) null,                -- 创建人
    b_created_at datetime2 null,                  -- 创建时间
    b_updated_by varchar(50) null,                -- 最后修改人
    b_updated_at datetime2 null,                  -- 最后修改时间
    primary key (b_user_id, b_module_id, b_operation, b_xh)
);
GO

create index ix_s_user_data_power_module
    on dbo.s_user_data_power (b_module_id, b_user_id, b_operation, b_xh);
GO

/* ============================================================================
   13. 业务表主键约定（示例，非核心表，按实际业务表参照执行）
   ============================================================================ */

/*
-- 业务表示例
create table dbo.b_example (
    b_id bigint not null primary key, -- 主键（应用层雪花 ID）
    b_inputuser_id varchar(50) null,  -- 录入用户 ID
    b_inputdatetime datetime2 default sysutcdatetime() -- 录入时间
);
*/