/*
    |--------------------------------------------------------------------------
    | 用户表
    |--------------------------------------------------------------------------
*/
IF EXISTS(SELECT *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[sys_user]')
            AND type in (N'U'))
    DROP TABLE [sys_user];
CREATE TABLE [sys_user]
(
    [id]             INT IDENTITY               NOT NULL,
    [username]       VARCHAR(50)                NOT NULL, ---账号
    [cipher]         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'[sys_role]')
            AND type in (N'U'))
    DROP TABLE [sys_role];
CREATE TABLE [sys_role]
(
    [id]         INT IDENTITY               NOT NULL,
    [roleName]   VARCHAR(50)                NOT NULL, ---角色名称
    [roleCode]   VARCHAR(50)                NOT NULL, ---角色编码
    [comments]   NVARCHAR(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'[sys_menu]')
            AND type in (N'U'))
    DROP TABLE [sys_menu];
CREATE TABLE [sys_menu]
(
    [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)                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 0         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'[sys_use_role]')
            AND type in (N'U'))
    DROP TABLE [sys_use_role];
CREATE TABLE [sys_use_role]
(
    [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'[sys_role_menu]')
            AND type in (N'U'))
    DROP TABLE [sys_role_menu];
CREATE TABLE [sys_role_menu]
(
    [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'[sys_login_record]')
            AND type in (N'U'))
    DROP TABLE [sys_login_record];
CREATE TABLE [sys_login_record]
(
    [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]   NVARCHAR(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'[sys_organization]')
            AND type in (N'U'))
    DROP TABLE [sys_organization];
CREATE TABLE [sys_organization]
(
    [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]             NVARCHAR(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'[sys_dictionary]')
            AND type in (N'U'))
    DROP TABLE [sys_dictionary];
CREATE TABLE [sys_dictionary]
(
    [id]         INT IDENTITY               NOT NULL,
    [dictName]   NVARCHAR(50)               NOT NULL, ---字典名称
    [dictCode]   VARCHAR(50)                NOT NULL, ---字典编码
    [sortNumber] INT      DEFAULT 0         NOT NULL, ---排序号
    [comments]   NVARCHAR(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'[sys_dictionary_data]')
            AND type in (N'U'))
    DROP TABLE [sys_dictionary_data];
CREATE TABLE [sys_dictionary_data]
(
    [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]     NVARCHAR(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'[sys_operate_record]')
            AND type in (N'U'))
    DROP TABLE [sys_operate_record];
CREATE TABLE [sys_operate_record]
(
    [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)               NULL,     ---调用方法
    [params]        VARCHAR(1000)              NULL,     ---请求参数
    [result]        VARCHAR(500)               NULL,     ---返回结果
    [error]         VARCHAR(500)               NULL,     ---错误内容
    [comments]      NVARCHAR(100)              NULL,     ---备注
    [estate]        TINYINT  DEFAULT 0         NOT NULL, ---状态
    [tenantId]      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'[sys_deploy]')
            AND type in (N'U'))
    DROP TABLE [sys_deploy];
CREATE TABLE [sys_deploy]
(
    [id]         INT IDENTITY               NOT NULL,
    [name]       NVARCHAR(50)               NOT NULL, ---配置名称
    [code]       VARCHAR(100)               NOT NULL, ---配置编码
    [setup]      VARCHAR(1000)              NULL,     ---配置值
    [estate]     TINYINT  DEFAULT 0         NOT NULL, ---状态
    [createTime] DATETIME DEFAULT getdate() NOT NULL, ---创建时间
    [updateTime] DATETIME DEFAULT getdate() NOT NULL, ---更新时间
    PRIMARY KEY NONCLUSTERED ([code])
);

/*
    |--------------------------------------------------------------------------
    | 历史密码表
    |--------------------------------------------------------------------------
*/
IF EXISTS(SELECT *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[sys_history_password]')
            AND type in (N'U'))
    DROP TABLE [sys_history_password];
CREATE TABLE [sys_history_password]
(
    [id]         INT IDENTITY               NOT NULL,
    [username]   VARCHAR(50)                NOT NULL, ---账号
    [cipher]     VARCHAR(100)               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'[basic_level]')
            AND type in (N'U'))
    DROP TABLE [basic_level];
CREATE TABLE [basic_level]
(
    [id]         INT IDENTITY               NOT NULL,
    [leveName]   NVARCHAR(50)               NOT NULL, ---层级名称
    [levelCode]  TINYINT                    NOT NULL, ---层级编码
    [levelMark]  VARCHAR(50)                NOT NULL, ---层级编码
    [comments]   NVARCHAR(100)              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'[basic_line]')
            AND type in (N'U'))
    DROP TABLE [basic_line];
CREATE TABLE [basic_line]
(
    [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]   NVARCHAR(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'[basic_plant]')
            AND type in (N'U'))
    DROP TABLE [basic_plant];
CREATE TABLE [basic_plant]
(
    [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'[basic_company]')
            AND type in (N'U'))
    DROP TABLE [basic_company];
CREATE TABLE [basic_company]
(
    [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'[basic_product]')
            AND type in (N'U'))
    DROP TABLE [basic_product];
CREATE TABLE [basic_product]
(
    [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, ---物料代码
    [shelfLife]    INT      DEFAULT 0         NOT NULL, ---有效期(月)
    [dosageForm]   VARCHAR(50)                NULL,     ---剂型
    [dosageUsage]  VARCHAR(50)                NULL,     ---用量
    [strength]     VARCHAR(50)                NULL,     ---浓度
    [packageSize]  VARCHAR(50)                NULL,     ---产品规格
    [generic]      VARCHAR(50)                NULL,     ---产品通用名
    [composition]  VARCHAR(50)                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)                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'[produce_order]')
            AND type in (N'U'))
    DROP TABLE [produce_order];
CREATE TABLE [produce_order]
(
    [id]           INT IDENTITY               NOT NULL,
    [poNumber]     VARCHAR(100)               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(100)               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])
);
