
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sysloginfo]') AND type in (N'U'))
drop table [dbo].[sysloginfo]
GO
BEGIN
CREATE TABLE [dbo].[sysloginfo](
	[optime] [datetime] NOT NULL,
	[operator] [varchar](20) NOT NULL,
	[loglev] [smallint] NOT NULL,
	[optype] [smallint] NOT NULL,
	[remark] [text] NOT NULL,
	[domain] [varchar](20) NULL CONSTRAINT [DF_sysloginfo_domain]  DEFAULT (NULL)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[userinfo]') AND type in (N'U'))
drop table [dbo].[userinfo]
GO
BEGIN
CREATE TABLE [dbo].[userinfo](
	[username] [varchar](20) NOT NULL,
	[userid] [varchar](30) NOT NULL,
	[contacttel] [varchar](20) NOT NULL,
	[islocked] [smallint] NOT NULL CONSTRAINT [DF_userinfo_islocked]  DEFAULT ((0)),
	[failaccum] [smallint] NOT NULL CONSTRAINT [DF_userinfo_failaccum]  DEFAULT ((0)),
	[preusedate] [datetime] NULL CONSTRAINT [DF_userinfo_preusedate]  DEFAULT (NULL),
	[serialnum] [varchar](20) NOT NULL,
	[isdeleted] [smallint] NOT NULL CONSTRAINT [DF_userinfo_isdeleted]  DEFAULT ((0)),
	[remark] [text] NULL,
	[user_email] [varchar](30) NULL CONSTRAINT [DF_userinfo_user_email]  DEFAULT (NULL),
	[domain] [varchar](20) NULL CONSTRAINT [DF_userinfo_domain]  DEFAULT (NULL),
	[pin] [varchar](20) NOT NULL,
	[authtype] [smallint] NOT NULL CONSTRAINT [DF_userinfo_authtype]  DEFAULT ((2)),
 CONSTRAINT [PK_userinfo] PRIMARY KEY CLUSTERED 
(
	[username] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'userinfo', N'COLUMN',N'authtype'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'authentication type.1 OTP only; 2 OTP+PIN;' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'userinfo', @level2type=N'COLUMN',@level2name=N'authtype'
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[operatorinfo]') AND type in (N'U'))
drop table [dbo].[operatorinfo]
BEGIN
CREATE TABLE [dbo].[operatorinfo](
	[operatorid] [int] NOT NULL,
	[passwd] [nchar](40) NOT NULL,
	[operatorlev] [smallint] NOT NULL,
	[operatorname] [varchar](20) NOT NULL,
	[domain] [varchar](20) NULL,
 CONSTRAINT [PK_operatorinfo] PRIMARY KEY CLUSTERED 
(
	[operatorid] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[commonloginfo]') AND type in (N'U'))
drop table [dbo].[commonloginfo]
BEGIN
CREATE TABLE [dbo].[commonloginfo](
	[optime] [datetime] NOT NULL,
	[operator] [varchar](20) NOT NULL,
	[loglev] [smallint] NOT NULL,
	[optype] [smallint] NOT NULL,
	[remark] [text] NOT NULL,
	[domain] [varchar](20) NULL,
	[remoteip] [varchar](16) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[keyinfo]') AND type in (N'U'))
drop table [dbo].[keyinfo]
GO
BEGIN
CREATE TABLE [dbo].[keyinfo](
	[serialnum] [varchar](20) NOT NULL,
	[userkey] [varchar](20) NOT NULL,
	[hcounter] [bigint] NOT NULL CONSTRAINT [DF_keyinfo_hcounter]  DEFAULT ((0)),
	[lcounter] [bigint] NOT NULL CONSTRAINT [DF_keyinfo_lcounter]  DEFAULT ((0)),
	[tktype] [smallint] NOT NULL CONSTRAINT [DF_keyinfo_tktype]  DEFAULT ((1)),
	[predate] [datetime] NOT NULL,
	[state] [smallint] NOT NULL CONSTRAINT [DF_keyinfo_state]  DEFAULT ((0)),
	[domain] [varchar](20) NULL CONSTRAINT [DF_keyinfo_domain]  DEFAULT (NULL),
	[importdate] [datetime] NOT NULL,
	[diglen] [smallint] NOT NULL CONSTRAINT [DF_keyinfo_diglen] default ((6)),
  [authwnd] [int] NOT NULL CONSTRAINT [DF_keyinfo_authwnd] default ((30)),
  [tmstep] [int] NOT NULL CONSTRAINT [DF_keyinfo_tmstep] default ((60)),
 CONSTRAINT [PK_keyinfo] PRIMARY KEY CLUSTERED 
(
	[serialnum] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'keyinfo', N'COLUMN',N'tktype'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'token type 1 event-based;2 time-based;3 challenge/response-based ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'keyinfo', @level2type=N'COLUMN',@level2name=N'tktype'
GO
--insert the initial operator information
insert into operatorinfo (operatorid, passwd, operatorlev, operatorname, domain) values(1000000001, '7c4a8d09ca3762af61e59520943dc26494f8941b', 1, 'super_administrator', NULL)
GO