--------- 用户表 ---------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[users]')AND type in (N'U')) DROP TABLE [users];
CREATE TABLE [users] (
    [id] INT IDENTITY NOT NULL,
    [username] VARCHAR(50) NOT NULL,    ---账号
    [password] VARCHAR(255) NULL,   ---密码
    [nickname] NVARCHAR(50) NOT NULL,   ---昵称
    [organizationId] INT DEFAULT 0 NOT NULL,    ---所属机构ID
    [avatar] VARCHAR(255) NULL, ---图像
    [sex] tinyINT DEFAULT 1 NOT NULL,   ---性别
    [phone] VARCHAR(50) NULL,   ---手机号
    [email] VARCHAR(50) NULL,   ---邮箱
    [idcard] VARCHAR(50) NULL,  ---身份证
    [birthday] datetime NULL,   ---生日日期
    [INTroduction] NVARCHAR(100) NULL,  ---个人简介
    [estate] tinyINT DEFAULT 0 NOT NULL,---状态
    [createTime] datetime DEFAULT getdate() NOT NULL,   ---创建时间
    [updateTime] datetime DEFAULT getdate() NOT NULL,   ---更新时间
    PRIMARY KEY NONCLUSTERED ([username])
);

--------- 角色表 ---------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[roles]') AND type in (N'U')) DROP TABLE [roles];
CREATE TABLE [roles] (
    [id] INT IDENTITY NOT NULL,
    [roleName] VARCHAR(50) NOT NULL,    ---角色名称
    [roleCode] VARCHAR(50) NOT NULL,    ---角色标识
    [comments] VARCHAR(100) NULL,   ---备注
    [estate] tinyINT DEFAULT 0 NOT NULL,    ---状态
    [createTime] datetime DEFAULT getdate() NOT NULL,   ---创建时间
    [updateTime] datetime DEFAULT getdate() NOT NULL,   ---更新时间
    PRIMARY KEY NONCLUSTERED ([roleCode])
);

--------- 菜单表 ---------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[menus]') AND type in (N'U')) DROP TABLE [menus];
CREATE TABLE [menus] (
    [id] INT IDENTITY NOT NULL,
    [parentId] INT DEFAULT 0 NOT NULL,  ---上级菜单ID
    [title] NVARCHAR(50) NOT NULL,  ---菜单名称
    [path] VARCHAR(50) NULL,    ---路由地址
    [icon] VARCHAR(50) NULL,    ---图标
    [component] VARCHAR(50) NULL,   ---组件路径
    [menuType] tinyINT DEFAULT 0 NOT NULL,  ---菜单类型
    [authority] VARCHAR(50) NULL,   ---权限标识
    [sortNumber] INT DEFAULT 0 NOT NULL,    ---排序号
    [hide] tinyINT DEFAULT 0 NOT NULL,  ---是否隐藏
    [active] VARCHAR(50) NULL,  ---选中地址
    [target] VARCHAR(50) DEFAULT '_self' NOT NULL,  ---跳转方式
    [closeable] tinyINT DEFAULT 0 NOT NULL, ---多页签中关闭
    [tabUnique] tinyINT DEFAULT 0 NOT NULL, --- 不同参数页签
    [meta] VARCHAR(255) NULL,   ---路由元数据
    [color] VARCHAR(50) NULL,   ---颜色
    [hideFooter] tinyINT DEFAULT 1 NOT NULL,    ---隐藏页脚
    [hideSidebar] tinyINT DEFAULT 0 NOT NULL,   ---隐藏侧边栏
    [createTime] datetime DEFAULT getdate() NOT NULL,   ---创建时间
    [updateTime] datetime DEFAULT getdate() NOT NULL,   ---更新时间
    PRIMARY KEY NONCLUSTERED ([title])
);

--------- 用户角色关联表 ---------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[use_roles]') AND type in (N'U')) DROP TABLE [use_roles];
CREATE TABLE [use_roles] (
    [id] INT IDENTITY NOT NULL,
    [userId] INT NOT NULL,  ---用户ID
    [roleId] INT NOT NULL,  ---角色ID
    [createTime] datetime DEFAULT getdate() NOT NULL,   ---创建时间
    [updateTime] datetime DEFAULT getdate() NOT NULL,   ---更新时间
);

--------- 角色菜单关联表 ---------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[role_menus]') AND type in (N'U')) DROP TABLE [role_menus];
CREATE TABLE [role_menus] (
    [id] INT IDENTITY NOT NULL,
    [roleId] INT NOT NULL,  ---角色ID
    [menuId] INT NOT NULL,  ---菜单ID
    [createTime] datetime DEFAULT getdate() NOT NULL,   ---创建时间
    [updateTime] datetime DEFAULT getdate() NOT NULL,   ---更新时间
);

--------- 登陆日志表 ---------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[logs]') AND type in (N'U')) DROP TABLE [logs];
CREATE TABLE [logs] (
    [id] INT IDENTITY NOT NULL,
    [username] VARCHAR(50) NOT NULL,    ---账号
    [nickname] NVARCHAR(50) NULL,   ---昵称
    [ip] INT NOT NULL,  ---ip地址
    [device] VARCHAR(50) NULL,  ---设备名称
    [os] VARCHAR(50) NULL,  ---操作系统
    [browser] VARCHAR(50) NULL, ---浏览器
    [comments] VARCHAR(100) NULL,   ---备注
    [loginType] tinyINT DEFAULT 0 NOT NULL, ---登陆类型
    [createTime] datetime DEFAULT getdate() NOT NULL,   ---创建时间
    [updateTime] datetime DEFAULT getdate() NOT NULL,   ---更新时间
);

--------- 机构表 ---------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[organizations]') AND type in (N'U')) DROP TABLE [organizations];
CREATE TABLE [organizations] (
    [id] INT IDENTITY NOT NULL,
    [organizationName] NVARCHAR(50) NOT NULL,    ---机构名称
    [organizationFullName] NVARCHAR(100) NOT NULL,   ---机构全称
    [parentId] INT DEFAULT 0 NOT NULL,  ---上级机构ID
    [organizationCode] VARCHAR(50) NULL,   ---机构代码
    [organizationType] VARCHAR(50) NOT NULL,   ---机构类型
    [sortNumber] INT DEFAULT 0 NOT NULL,  ---排序号
    [comments] VARCHAR(100) NULL,   ---备注
    [tenantId] INT DEFAULT 0 NOT NULL, ---租户ID
    [leaderId] INT DEFAULT 0 NOT NULL, ---领导者ID
    [createTime] datetime DEFAULT getdate() NOT NULL,   ---创建时间
    [updateTime] datetime DEFAULT getdate() NOT NULL,   ---更新时间
    PRIMARY KEY NONCLUSTERED ([organizationFullName])
);

--------- 字典表 ---------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dictionarys]') AND type in (N'U')) DROP TABLE [dictionarys];
CREATE TABLE [dictionarys] (
    [id] INT IDENTITY NOT NULL,
    [dictName] NVARCHAR(50) NOT NULL,    ---字典名称
    [dictCode] VARCHAR(50) NOT NULL,   ---字典标识
    [sortNumber] INT DEFAULT 0 NOT NULL,  ---排序号
    [comments] VARCHAR(100) NULL,   ---备注
    [createTime] datetime DEFAULT getdate() NOT NULL,   ---创建时间
    [updateTime] datetime DEFAULT getdate() NOT NULL,   ---更新时间
    PRIMARY KEY NONCLUSTERED ([dictCode])
);

--------- 字典项表 ---------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dictionary_datas]') AND type in (N'U')) DROP TABLE [dictionary_datas];
CREATE TABLE [dictionary_datas] (
    [id] INT IDENTITY NOT NULL,
    [dictDataName] NVARCHAR(50) NOT NULL,   ---字典项名称
    [dictDataCode] VARCHAR(50) NOT NULL,   ---字典项标识
    [dictId] INT DEFAULT 0 NOT NULL,  ---所属字典ID
    [sortNumber] INT DEFAULT 0 NOT NULL,  ---排序号
    [comments] VARCHAR(100) NULL,   ---备注
    [createTime] datetime DEFAULT getdate() NOT NULL,   ---创建时间
    [updateTime] datetime DEFAULT getdate() NOT NULL,   ---更新时间
    PRIMARY KEY NONCLUSTERED ([dictDataCode])
);

--------- 操作日志表 ---------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[operate_logs]') AND type in (N'U')) DROP TABLE [operate_logs];
CREATE TABLE [operate_logs] (
    [id] INT IDENTITY NOT NULL,
    [username] VARCHAR(50) NOT NULL,    ---账号
    [nickname] NVARCHAR(50) NULL,   ---昵称
    [ip] INT NOT NULL,  ---ip地址
    [device] VARCHAR(50) NULL,  ---设备名称
    [os] VARCHAR(50) NULL,  ---操作系统
    [browser] VARCHAR(50) NULL, ---浏览器
    [module] VARCHAR(50) NOT NULL,  ---操作模块
    [description] VARCHAR(50) NOT NULL, ---操作功能
    [url] VARCHAR(50) NOT NULL, ---请求地址
    [requestMethod] VARCHAR(20) NOT NULL,   ---请求方式
    [spendTime] INT DEFAULT 0 NOT NULL, ---请求耗时间
    [method] VARCHAR(500) NOT NULL, ---调用方法
    [params] VARCHAR(500) NULL, ---请求参数
    [result] VARCHAR(500) NULL, ---返回结果
    [error] VARCHAR(500) NULL,  ---错误内容
    [comments] VARCHAR(100) NULL,   ---备注
    [estate] tinyINT DEFAULT 0 NOT NULL,    ---状态
    [tenanId] INT DEFAULT 0 NOT NULL,   ---租户ID
    [createTime] datetime DEFAULT getdate() NOT NULL,   ---创建时间
    [updateTime] datetime DEFAULT getdate() NOT NULL,   ---更新时间
);

--------- 层级表 ---------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[levels]') AND type in (N'U')) DROP TABLE [levels];
CREATE TABLE [levels] (
    [id] INT IDENTITY NOT NULL,
    [levelCode] tinyINT NOT NULL,   ---层级标识
    [leveName] NVARCHAR(50) NOT NULL,   ---层级名称
    [estate] tinyINT DEFAULT 0 NOT NULL,    ---状态
    [createTime] datetime DEFAULT getdate() NOT NULL,   ---创建时间
    [updateTime] datetime DEFAULT getdate() NOT NULL,   ---更新时间
    PRIMARY KEY NONCLUSTERED ([levelCode])
);

--------- 产线表 ---------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[lines]') AND type in (N'U')) DROP TABLE [lines];
CREATE TABLE [lines] (
    [id] INT IDENTITY NOT NULL,
    [lineName] NVARCHAR(50) NOT NULL,   ---产线名称
    [lineCode] NVARCHAR(50) NOT NULL,   ---产线标识
    [plantId] INT DEFAULT 0 NOT NULL,   ---所属工厂ID
    [lineIp] INT DEFAULT 0 NOT NULL,    ---登陆IP
    [comments] VARCHAR(100) NULL,   ---备注
    [estate] tinyINT DEFAULT 0 NOT NULL,    ---状态
    [createTime] datetime DEFAULT getdate() NOT NULL,   ---创建时间
    [updateTime] datetime DEFAULT getdate() NOT NULL,   ---更新时间
    PRIMARY KEY NONCLUSTERED ([lineCode])
);

--------- 工厂表 ---------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[plants]') AND type in (N'U')) DROP TABLE [plants];
CREATE TABLE [plants] (
    [id] INT IDENTITY NOT NULL,
    [plantName] NVARCHAR(50) NOT NULL,   ---工厂名称
    [plantCode] VARCHAR(50) NOT NULL,   ---工厂标识
    [licNumber] VARCHAR(50) NULL,   ---许可证号
    [licAgency] VARCHAR(50) NULL,   ---许可证代理
    [licState] VARCHAR(50) NULL,   ---许可证所在地
    [gln] INT DEFAULT 0 NOT NULL,   ---全球位置代码   
    [address1] VARCHAR(50) NULL,    ---地址1
    [address2] VARCHAR(50) NULL,    ---地址2
    [pin] INT DEFAULT 0 NOT NULL,   ---pin码
    [city] NVARCHAR(50) NULL,   ---城市
    [localState] NVARCHAR(50) NULL, ---所在地
    [country]  NVARCHAR(50) NULL,   ---国家
    [phone] VARCHAR(50) NULL,   ---手机号
    [isSame] tinyINT DEFAULT 0 NOT NULL,    ---是否与公司一致
    [estate] tinyINT DEFAULT 0 NOT NULL,    ---状态
    [createTime] datetime DEFAULT getdate() NOT NULL,   ---创建时间
    [updateTime] datetime DEFAULT getdate() NOT NULL,   ---更新时间
    PRIMARY KEY NONCLUSTERED ([plantCode])
);


--------- 公司表 ---------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[companies]') AND type in (N'U')) DROP TABLE [companies];
CREATE TABLE [companies] (
    [id] INT IDENTITY NOT NULL,
    [companyName] NVARCHAR(100) NOT NULL,   ---公司名称
    [companyPrefix] VARCHAR(100) NOT NULL,  ---公司前缀
    [estate] tinyINT DEFAULT 0 NOT NULL,    ---状态
    [createTime] datetime DEFAULT getdate() NOT NULL,   ---创建时间
    [updateTime] datetime DEFAULT getdate() NOT NULL,   ---更新时间
    PRIMARY KEY NONCLUSTERED ([companyPrefix])
);

--------- 产品表 ---------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[products]') AND type in (N'U')) DROP TABLE [products];
CREATE TABLE [products] (
    [id] INT IDENTITY NOT NULL,
    [companyId] INT DEFAULT 0 NOT NULL,   ---公司ID
    [plantId] INT DEFAULT 0 NOT NULL,   ---工厂ID
    [productName] NVARCHAR(100) NOT NULL,  ---产品名称
    [productCode] VARCHAR(100) NOT NULL,  ---产品标识
    [ndc] VARCHAR(50) NOT NULL, ---打印在标签上的产品代码
    [material] VARCHAR(50) NOT NULL,    ---物料代码
    [dosageForm] VARCHAR(50) NOT NULL,  ---剂型
    [dosageUsage]VARCHAR(50) NOT NULL,  ---用量
    [strength] VARCHAR(50) NOT NULL,    ---浓度
    [packageSize] VARCHAR(50) NOT NULL, ---产品规格
    [generic] VARCHAR(50) NOT NULL, ---产品通用名
    [composition] VARCHAR(50) NOT NULL, ---成分
    [unitPrice] INT DEFAULT 0 NOT NULL, ---单价
    [description] VARCHAR(100) NULL,    ---产品描述
    [scheduled] tinyINT DEFAULT 0 NOT NULL, ---是否预定
    [imageDir] VARCHAR(500) NULL,   ---图片地址
    [remark] VARCHAR(50) NULL,  ---备注
    [primaryCount] INT DEFAULT 0 NOT NULL,  ---主要计数
    [productType] tinyINT DEFAULT 0 NOT NULL,   ---产品类别
    [country] VARCHAR(50) NOT NULL, ---所属产地（国家）
    [packageRules] VARCHAR(1000) NULL,  ---包装规则
    [estate] tinyINT DEFAULT 0 NOT NULL,    ---状态
    [createTime] datetime DEFAULT getdate() NOT NULL,   ---创建时间
    [updateTime] datetime DEFAULT getdate() NOT NULL,   ---更新时间
    PRIMARY KEY NONCLUSTERED ([productCode])
);

--------- 批次表 ---------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[orders]') AND type in (N'U')) DROP TABLE [orders];
CREATE TABLE [orders] (
    [id] INT IDENTITY NOT NULL,
    [poNumber] VARCHAR(50) NOT NULL,    ---采购订单号
    [productId] INT DEFAULT 0 NOT NULL, ---产品ID
    [plantId] INT DEFAULT 0 NOT NULL,   ---工厂ID
    [lineId] INT DEFAULT 0 NOT NULL,    ---产线ID
    [batchNo] VARCHAR(50) NOT NULL, ---批次号
    [makeDate] datetime NOT NULL,   ---生产日期
    [validDate] datetime NOT NULL,  ---有效期至
    [planAmount] INT DEFAULT 0 NOT NULL,    ---计划产量
    [actualAmount] INT DEFAULT 0 NOT NULL,  ---实际产量
    [estate] tinyINT DEFAULT 0 NOT NULL,    ---状态
    [createTime] datetime DEFAULT getdate() NOT NULL,   ---创建时间
    [updateTime] datetime DEFAULT getdate() NOT NULL,   ---更新时间
    PRIMARY KEY NONCLUSTERED ([batchNo])
);

--------- 系统配置表 ---------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[deploys]') AND type in (N'U')) DROP TABLE [deploys];
CREATE TABLE [deploys] (
    [id] INT IDENTITY NOT NULL,
    [deployName] NVARCHAR(50) NOT NULL, ---配置名称
    [deployCode] VARCHAR(50) NOT NULL,  ---配置标识
    [settings] VARCHAR(1000) NULL,  ---配置值
    [estate] tinyINT DEFAULT 0 NOT NULL,    ---状态
    [createTime] datetime DEFAULT getdate() NOT NULL,   ---创建时间
    [updateTime] datetime DEFAULT getdate() NOT NULL,   ---更新时间
    PRIMARY KEY NONCLUSTERED ([deployCode])
);