SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON

/****** Object:  Table [dbo].[ChihAdmsCmd]    Script Date: 2019/3/11 下午 10:54:19 ******/
IF NOT EXISTS( SELECT * FROM sys.tables WHERE name = 'ChihAdmsCmd' )
BEGIN
	CREATE TABLE [dbo].[ChihAdmsCmd](
		[Id] [bigint] IDENTITY(1,1) NOT NULL,
		[Result] [int] NOT NULL CONSTRAINT [DF_ChihAdmsCmd_CmdResult]  DEFAULT ((255)),
		[Cmd] [varchar](50) NOT NULL,
		[CmdExt] [varchar](100) NOT NULL CONSTRAINT [DF_ChihAdmsCmd_CmdExt]  DEFAULT ('{}'),
		[SN] [varchar](50) NOT NULL,
		[CreateTime] [datetime] NOT NULL CONSTRAINT [DF_ChihAdmsCmd_CreateTime]  DEFAULT (getdate()),
		[FinishTime] [datetime] NULL,
	 CONSTRAINT [PK_ChihAdmsCmd] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
END

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='IX_ChihAdmsCmd' AND object_id = OBJECT_ID('ChihAdmsCmd'))
BEGIN
	CREATE INDEX IX_ChihAdmsCmd ON ChihAdmsCmd(SN,Result,Cmd)
END

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='IX_ChihAdmsCmd_Ext' AND object_id = OBJECT_ID('ChihAdmsCmd'))
BEGIN
	CREATE INDEX IX_ChihAdmsCmd_Ext ON ChihAdmsCmd(CmdExt)
END

/****** Object:  Table [dbo].[ChihAdmsDev]    Script Date: 2019/3/11 下午 10:54:19 ******/
IF NOT EXISTS( SELECT * FROM sys.tables WHERE name = 'ChihAdmsDev' )
BEGIN
	CREATE TABLE [dbo].[ChihAdmsDev](
		[SN] [varchar](50) NOT NULL,
		[DevName] [varchar](50) NOT NULL CONSTRAINT [DF_ChihAdmsDev_DevName]  DEFAULT (''),
		[UserDefineDevNumber] [varchar](20) NOT NULL CONSTRAINT [DF_ChihAdmsDev_UserDefineNum]  DEFAULT (''),
		[IP] [varchar](50) NULL CONSTRAINT [DF_ChihAdmsDev_IP]  DEFAULT ('0.0.0.0'),
		[GroupId] [int] NOT NULL CONSTRAINT [DF_ChihAdmsDev_GroupId]  DEFAULT ((0)),
		[Encoding] [varchar](5) NOT NULL CONSTRAINT [DF_ChihAdmsDev_Encoding]  DEFAULT ('big5'),
		[Stamp] [varchar](15) NOT NULL CONSTRAINT [DF_ChihAdmsDev_Stamp]  DEFAULT ('999999999'),
		[OpStamp] [varchar](15) NOT NULL CONSTRAINT [DF_ChihAdmsDev_OpStamp]  DEFAULT ('999999999'),
		[PhotoStamp] [varchar](15) NOT NULL CONSTRAINT [DF_ChihAdmsDev_PhotoStamp]  DEFAULT ('999999999'),
		[ErrorDelay] [int] NOT NULL CONSTRAINT [DF_ChihAdmsDev_ErrorDelay]  DEFAULT ((30)),
		[Delay] [int] NOT NULL CONSTRAINT [DF_ChihAdmsDev_Delay]  DEFAULT ((30)),
		[TransInterval] [int] NOT NULL CONSTRAINT [DF_ChihAdmsDev_TransInterval]  DEFAULT ((1)),
		[TransFlag] [varchar](20) NOT NULL CONSTRAINT [DF_ChihAdmsDev_TransFlag_1]  DEFAULT ('1111111111'),
		[TransData] [varchar](100) NOT NULL CONSTRAINT [DF_ChihAdmsDev_TransFlag]  DEFAULT ('AttLog\tOpLog\tAttPhoto\tEnrollUser\tChgUser\tEnrollFP\tChgFp'),
		[Realtime] [int] NOT NULL CONSTRAINT [DF_ChihAdmsDev_Realtime]  DEFAULT ((1)),
		[ATTLOGStamp] [varchar](15) NOT NULL CONSTRAINT [DF_ChihAdmsDev_ATTLOGStamp]  DEFAULT ('999999999'),
		[OPERLOGStamp] [varchar](15) NOT NULL CONSTRAINT [DF_ChihAdmsDev_OPERLOGStamp]  DEFAULT ('999999999'),
		[ATTPHOTOStamp] [varchar](15) NOT NULL CONSTRAINT [DF_ChihAdmsDev_ATTPHOTO]  DEFAULT ('999999999'),
		[USERINFOStamp] [varchar](15) NOT NULL CONSTRAINT [DF_ChihAdmsDev_USERINFOStamp]  DEFAULT ('999999999'),
		[FINGERTMPStamp] [varchar](15) NOT NULL CONSTRAINT [DF_ChihAdmsDev_FINGERTMPStamp]  DEFAULT ('999999999'),
		[PushVer] [varchar](15) NOT NULL CONSTRAINT [DF_ChihAdmsDev_PushVer]  DEFAULT (''),
		[LastConnectTime] [datetime] NULL,
		[CreateTime] [datetime] NULL CONSTRAINT [DF_ChihAdmsDev_CreateTime]  DEFAULT (getdate()),
	 CONSTRAINT [PK_ChihAdmsDev] PRIMARY KEY CLUSTERED 
	(
		[SN] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]

END

/****** Object:  Table [dbo].[ChihAttLog]    Script Date: 2019/3/11 下午 10:54:19 ******/
IF NOT EXISTS( SELECT * FROM sys.tables WHERE name = 'ChihAttLog' )
BEGIN
	CREATE TABLE [dbo].[ChihAttLog](
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[UserId] [int] NOT NULL,
		[CheckTime] [datetime] NOT NULL,
		[CheckStatus] [varchar](5) NOT NULL,
		[Verify] [varchar](10) NOT NULL,
		[SN] [varchar](50) NOT NULL CONSTRAINT [DF_ChihAttLog_SN]  DEFAULT (''),
		[Exported] [bit] NOT NULL CONSTRAINT [DF_ChihAttLog_Exported]  DEFAULT ((0)),
		[CreateTime] [datetime] NOT NULL CONSTRAINT [DF_ChihAttLog_CreateTime]  DEFAULT (getdate()),
	 CONSTRAINT [PK_ChihAttLog] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
	 CONSTRAINT [unix_attlog] UNIQUE NONCLUSTERED 
	(
		[UserId] ASC,
		[CheckTime] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
END

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='IX_ExportedAttlog' AND object_id = OBJECT_ID('ChihAttLog'))
BEGIN
	CREATE INDEX IX_ExportedAttlog ON ChihAttLog(Exported,CheckTime)
END


/****** Object:  Table [dbo].[ChihDepartment]    Script Date: 2019/3/11 下午 10:54:19 ******/
IF NOT EXISTS( SELECT * FROM sys.tables WHERE name = 'ChihDepartment' )
BEGIN
	CREATE TABLE [dbo].[ChihDepartment](
		[DeptId] [int] IDENTITY(1,1) NOT NULL,
		[ParentId] [int] NULL,
		[DeptName] [varchar](50) NULL,
		[DeptNumber] [varchar](50) NULL,
	 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED 
	(
		[DeptId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
END

/****** Object:  Table [dbo].[ChihFinger]    Script Date: 2019/3/11 下午 10:54:19 ******/
IF NOT EXISTS( SELECT * FROM sys.tables WHERE name = 'ChihFinger' )
BEGIN
	CREATE TABLE [dbo].[ChihFinger](
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[UserId] [int] NOT NULL,
		[FId] [int] NOT NULL,
		[Size] [int] NOT NULL,
		[TMP] [varchar](max) NOT NULL,
		[SN] [varchar](50) NOT NULL CONSTRAINT [DF_ChihFinger_SN]  DEFAULT (''),
		[CreateTime] [datetime] NOT NULL CONSTRAINT [DF_ChihFinger_CreateTime]  DEFAULT (getdate()),
	 CONSTRAINT [PK_ChihFinger] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
	 CONSTRAINT [uix_userfinger] UNIQUE NONCLUSTERED 
	(
		[UserId] ASC,
		[FId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='IX_FingerUser' AND object_id = OBJECT_ID('ChihFinger'))
BEGIN
	CREATE INDEX IX_FingerUser ON ChihFinger(UserId)
END

/****** Object:  Table [dbo].[ChihUserInfo]    Script Date: 2019/3/11 下午 10:54:19 ******/
IF NOT EXISTS( SELECT * FROM sys.tables WHERE name = 'ChihUserInfo' )
BEGIN
	CREATE TABLE [dbo].[ChihUserInfo](
		[UserId] [int] IDENTITY(1,1) NOT NULL,
		[Pin] [varchar](15) NOT NULL,
		[Name] [varchar](30) NOT NULL CONSTRAINT [DF_ChihUserInfo_Name]  DEFAULT (''),
		[Pri] [int] NOT NULL CONSTRAINT [DF_ChihUserInfo_Pri]  DEFAULT ((0)),
		[Password] [varchar](20) NOT NULL CONSTRAINT [DF_ChihUserInfo_Password]  DEFAULT (''),
		[CardNo] [varchar](20) NOT NULL CONSTRAINT [DF_ChihUserInfo_CardNo]  DEFAULT ((0)),
		[UserNumber] [varchar](50) NOT NULL CONSTRAINT [DF_ChihUserInfo_UserNumver]  DEFAULT (''),
		[DeptId] [int] NOT NULL CONSTRAINT [DF_ChihUserInfo_DeptId]  DEFAULT ((1)),
		[SN] [varchar](50) NOT NULL CONSTRAINT [DF_ChihUserInfo_SN]  DEFAULT (''),
		[CreateTime] [datetime] NOT NULL CONSTRAINT [DF_ChihUserInfo_CreateTime]  DEFAULT (getdate()),
	 CONSTRAINT [PK_ChihUserInfo] PRIMARY KEY CLUSTERED 
	(
		[UserId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
	 CONSTRAINT [uix_pin] UNIQUE NONCLUSTERED 
	(
		[Pin] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
END

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='IX_ChihUserInfo_SearchName' AND object_id = OBJECT_ID('ChihUserInfo'))
BEGIN
	CREATE INDEX IX_ChihUserInfo_SearchName ON ChihUserInfo(Name)
END

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='IX_ChihUserInfo_SearchPri' AND object_id = OBJECT_ID('ChihUserInfo'))
BEGIN
	CREATE INDEX IX_ChihUserInfo_SearchPri ON ChihUserInfo(Pri)
END

/***********************  如果部門表為空就Insert一筆總公司  *************************/
IF (SELECT TOP 1 DeptId FROM ChihDepartment)  IS NULL
BEGIN
	INSERT INTO ChihDepartment(DeptName) VALUES('總公司');
END;


/****** Object:  Table [dbo].[ChihFace]    Script Date: 2019/09/17 下午 23:00:00 ******/
IF NOT EXISTS( SELECT * FROM sys.tables WHERE name = 'ChihFace' )
BEGIN
	CREATE TABLE [dbo].[ChihFace](
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[UserId] [int] NOT NULL,
		[FId] [int] NOT NULL,
		[Size] [int] NOT NULL,
		[TMP] [varchar](max) NOT NULL,
		[SN] [varchar](50) NOT NULL DEFAULT (''),
		[CreateTime] [datetime] NOT NULL DEFAULT (getdate()),
	PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END 

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='IX_UserFace' AND object_id = OBJECT_ID('ChihFace'))
BEGIN
	CREATE INDEX IX_UserFace ON ChihFace(UserId)
END

/****** Object:  Table [dbo].[ChihAppParams]    Script Date: 2019/09/17 下午 23:00:00 ******/
IF NOT EXISTS( SELECT * FROM sys.tables WHERE name = 'ChihAppParams' )
BEGIN
    CREATE TABLE ChihAppParams
	(
		AppKey VARCHAR(50) PRIMARY KEY,
		AppValue VARCHAR(50) NOT NULL
	);

	INSERT INTO ChihAppParams(AppKey, AppValue) VALUES('DbVersion','1.0')
END 

/****** Object:  Table [dbo].[ChihFingerSensor]    Script Date: 2020/01/17 上午 00:00:00 ******/
IF NOT EXISTS( SELECT * FROM sys.tables WHERE name = 'ChihFingerSensor' )
BEGIN
	CREATE TABLE [dbo].[ChihFingerSensor] (
		[SensorSN]  VARCHAR (50) NOT NULL,
		[SensorName] VARCHAR(50) DEFAULT('') NOT NULL,
		[EnrollPwd] VARCHAR (100) NOT NULL,
		CONSTRAINT [PK_ChihFingerSensor] PRIMARY KEY CLUSTERED ([SensorSN] ASC)
	);
END 

/* DB 升級1.1 */
IF (SELECT AppValue FROM ChihAppParams WHERE AppKey='DbVersion')='1.0' 
BEGIN
	ALTER TABLE ChihAdmsDev ADD EnableFaceSyn Bit Default 0 Not null

	CREATE INDEX ix_EnFaceSynDev ON ChihAdmsDev(EnableFaceSyn)

	ALTER TABLE ChihUserInfo ADD UserPic VARCHAR(MAX);

	ALTER TABLE ChihAdmsDev ADD EnableUserPicSyn Bit Default 0 Not null

	CREATE INDEX ix_EnUserPicSynDev ON ChihAdmsDev(EnableUserPicSyn)

	UPDATE ChihAppParams SET AppValue='1.1' WHERE AppKey='DbVersion'
END

/* DB 升級1.1.1 */
IF (SELECT AppValue FROM ChihAppParams WHERE AppKey='DbVersion')='1.1' 
BEGIN
	ALTER TABLE ChihAttLog ADD SensorSN VARCHAR(50) Default ('') Not null

	UPDATE ChihAppParams SET AppValue='1.1.1' WHERE AppKey='DbVersion'
END

/****************************************************************************************/
SET ANSI_PADDING OFF