USE [LOGIX_AUDIT]
GO
/****** Object:  Table [TBL_PRODUCT]    Script Date: 04/30/2014 14:22:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [TBL_PRODUCT](
	[PRODUCT_ID] [int] NOT NULL,
	[PRODUCT_NAME] [varchar](50) NOT NULL,
 CONSTRAINT [PK_TBL_PRODUCT] PRIMARY KEY CLUSTERED 
(
	[PRODUCT_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]
GO
SET ANSI_PADDING OFF
GO
INSERT [TBL_PRODUCT] ([PRODUCT_ID], [PRODUCT_NAME]) VALUES (1, N'Logix Professional')
INSERT [TBL_PRODUCT] ([PRODUCT_ID], [PRODUCT_NAME]) VALUES (2, N'Logix xClient')
INSERT [TBL_PRODUCT] ([PRODUCT_ID], [PRODUCT_NAME]) VALUES (3, N'Logix Eco')
/****** Object:  Table [TBL_LOGIN_STATUS]    Script Date: 04/30/2014 14:22:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [TBL_LOGIN_STATUS](
	[STATUS_ID] [int] NOT NULL,
	[STATUS_DESC] [varchar](20) NOT NULL,
 CONSTRAINT [PK_TBL_LOGIN_STATUS] PRIMARY KEY CLUSTERED 
(
	[STATUS_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]
GO
SET ANSI_PADDING OFF
GO
INSERT [TBL_LOGIN_STATUS] ([STATUS_ID], [STATUS_DESC]) VALUES (1, N'Success')
INSERT [TBL_LOGIN_STATUS] ([STATUS_ID], [STATUS_DESC]) VALUES (2, N'Failure')
/****** Object:  Table [TBL_VIEW_AUDIT]    Script Date: 04/30/2014 14:22:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [TBL_VIEW_AUDIT](
	[SESSION_ID] [varchar](50) NOT NULL,
	[VIEW_ID] [int] NOT NULL,
	[DASHBOARD_ID] [int] NULL,
	[VIEW_NAME] [varchar](50) NULL,
	[DASHBOARD_NAME] [varchar](50) NULL,
	[DATE_ACCESSED] [datetime] NOT NULL,
	[LOAD_TIME] [float] NULL,
	[PRODUCT_ID] [int] NOT NULL,
	[DRILL_PATH] [varchar](100) NULL,
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[LOGIN_NAME] [varchar](30) NOT NULL,
	[SYSTEM_CODE] [varchar](10) NOT NULL,
 CONSTRAINT [PK_TBL_VIEW_AUDIT] 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]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [TBL_LOGIN]    Script Date: 04/30/2014 14:22:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [TBL_LOGIN](
	[LOGIN_NAME] [varchar](30) NOT NULL,
	[LOGIN_DATE] [datetime] NOT NULL,
	[SYSTEM_CODE] [varchar](10) NOT NULL,
	[SESSION_ID] [varchar](50) NOT NULL,
	[STATUS_ID] [int] NOT NULL,
	[PRODUCT_ID] [int] NOT NULL,
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_TBL_LOGIN] 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]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  ForeignKey [FK_TBL_VIEW_AUDIT_TBL_PRODUCT]    Script Date: 04/30/2014 14:22:37 ******/
ALTER TABLE [TBL_VIEW_AUDIT]  WITH CHECK ADD  CONSTRAINT [FK_TBL_VIEW_AUDIT_TBL_PRODUCT] FOREIGN KEY([PRODUCT_ID])
REFERENCES [TBL_PRODUCT] ([PRODUCT_ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [TBL_VIEW_AUDIT] CHECK CONSTRAINT [FK_TBL_VIEW_AUDIT_TBL_PRODUCT]
GO
/****** Object:  ForeignKey [FK_TBL_LOGIN_TBL_LOGIN_STATUS]    Script Date: 04/30/2014 14:22:37 ******/
ALTER TABLE [TBL_LOGIN]  WITH CHECK ADD  CONSTRAINT [FK_TBL_LOGIN_TBL_LOGIN_STATUS] FOREIGN KEY([STATUS_ID])
REFERENCES [TBL_LOGIN_STATUS] ([STATUS_ID])
ON DELETE CASCADE
GO
ALTER TABLE [TBL_LOGIN] CHECK CONSTRAINT [FK_TBL_LOGIN_TBL_LOGIN_STATUS]
GO



---Added by reporting development

--DROP TABLE TBL_SAVED_REPORT
CREATE TABLE TBL_SAVED_REPORT(
      SAVED_REPORT_ID int IDENTITY(1,1) NOT NULL,
      
      SYSTEM_CODE VARCHAR(10) NOT NULL,
      SCHEDULE_ID int NOT NULL,
      REPORT_ID INT NOT NULL,
      SESSION_ID VARchar(50) NOT NULL,
      FIXED_FORMAT_OUTPUT     varbinary(max) NULL,
      EXCEL_OUTPUT            varbinary(max) NULL,

      RELEASED_FLAG numeric(1, 0) NOT NULL DEFAULT 0,
      AUTHORISED_BY_LOGIN_NAME VARCHAR(30) NULL,
      
      LAST_MODIFIED datetime NOT NULL,
      
      CONSTRAINT PK_TBL_SAVED_REPORT PRIMARY KEY CLUSTERED 
      (
            SAVED_REPORT_ID
      )
)
GO

-- DROP TABLE TBL_SAVED_REPORT_BY_USER
CREATE TABLE TBL_SAVED_REPORT_BY_USER(
      SYSTEM_CODE VARchar(10) NOT NULL,
      LOGIN_NAME VARchar(30) NOT NULL,
      SAVED_REPORT_ID int NOT NULL
      
      CONSTRAINT PK_TBL_SAVED_REPORT_BY_USER PRIMARY KEY CLUSTERED 
      (
            SYSTEM_CODE ,
            LOGIN_NAME ,
            SAVED_REPORT_ID
      )
)
GO

ALTER TABLE TBL_SAVED_REPORT_BY_USER  
ADD  CONSTRAINT FK_SAVED_REPORT_BY_USER_ID FOREIGN KEY(SAVED_REPORT_ID)
REFERENCES TBL_SAVED_REPORT (SAVED_REPORT_ID)
GO

-- AUDIT

-- drop table TBL_SAVED_REPORT_AUDIT
CREATE TABLE TBL_SAVED_REPORT_AUDIT(
      ID bigint IDENTITY(1,1) NOT NULL,

      SESSION_ID VARchar(50) NOT NULL,
      SYSTEM_CODE VARchar(10) NOT NULL,
      REPORT_ID int NOT NULL,
      SCHEDULE_ID int NOT NULL,
      LOGIN_NAME VARchar(30) NOT NULL,
      SAVED_REPORT_ID int NOT NULL,

      DATE_ACCESSED datetime NOT NULL,
      LOAD_TIME float NULL,
      PRODUCT_ID int NOT NULL,
      DRILL_PATH varchar(100) NULL
      
CONSTRAINT PK_TBL_SAVED_REPORT_AUDIT PRIMARY KEY CLUSTERED 
(
      ID 
)
) 

GO

ALTER TABLE TBL_SAVED_REPORT_AUDIT  
ADD  CONSTRAINT FK_SAVED_REPORT_AUDIT FOREIGN KEY(SAVED_REPORT_ID)
REFERENCES TBL_SAVED_REPORT (SAVED_REPORT_ID)


GO

-- drop table TBL_SAVED_REPORT_AUDIT_PARAMETERS
CREATE TABLE TBL_SAVED_SCHEDULE_AUDIT_PARAMETERS(
      SYSTEM_CODE VARchar(10) NOT NULL,
	  SESSION_ID VARchar(50) NOT NULL,         
      PARAM_NAME varchar(50) NOT NULL,
      PARAM_VALUE varchar(4000) NULL,
      PARAM_DISPLAY varchar(4000) NULL,
	  LAST_MODIFIED datetime NOT NULL,
      CONSTRAINT PK_SAVED_REPORT_AUDIT_PARAMETERS PRIMARY KEY CLUSTERED 
      (
            SYSTEM_CODE ,
			SESSION_ID,
            PARAM_NAME 
      )
)

GO

-- drop table TBL_SAVED_REPORT_AUDIT_VIEW_PARAMETERS
CREATE TABLE TBL_SAVED_REPORT_AUDIT_VIEW_PARAMETERS(
      SYSTEM_CODE VARchar(10) NOT NULL,
	  SAVED_REPORT_ID int NOT NULL,
      VIEW_ID int NOT NULL,
      VIEW_RUN_ID int NOT NULL,           
      PARAM_NAME varchar(50) NOT NULL,
      PARAM_VALUE varchar(4000) NULL,
      PARAM_DISPLAY varchar(4000) NULL,
      CONSTRAINT PK_TBL_SAVED_REPORT_AUDIT_VIEW_PARAMETERS PRIMARY KEY CLUSTERED 
      (
            SYSTEM_CODE ,
            SAVED_REPORT_ID ,
            VIEW_ID,
            PARAM_NAME 
      )
)

GO

ALTER TABLE TBL_SAVED_REPORT_AUDIT_VIEW_PARAMETERS  
ADD  CONSTRAINT FK_SAVED_SCH_VW_PARAMS FOREIGN KEY(SAVED_REPORT_ID)
REFERENCES TBL_SAVED_REPORT (SAVED_REPORT_ID)


GO

      
CREATE TABLE [TBL_REPORT_GENERATION_AUDIT](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[SYSTEM_CODE] [varchar](10) NOT NULL,
	[REPORT_ID] [int] NOT NULL,
	[REPORT_NAME] [varchar](60) NOT NULL,
	[SCHEDULE_ID] [int] not NULL,
	[VIEW_RUN_ID] [int] not null,
	[GENERATED_DATE] [datetime] not null,
	[MESSAGE] [varchar](1000) NULL,
	[SAVED_REPORT_ID] [int] NULL,
	 CONSTRAINT [PK_REP_GEN_AUDIT] 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]
GO

/****** Object:  ForeignKey [FK_TBL_VIEW_AUDIT_TBL_PRODUCT]    Script Date: 04/30/2014 14:22:37 ******/
ALTER TABLE [TBL_REPORT_GENERATION_AUDIT]  WITH CHECK ADD  CONSTRAINT [FK_TBL_REP_GEN_AUDIT] FOREIGN KEY([SAVED_REPORT_ID])
REFERENCES [TBL_SAVED_REPORT] ([SAVED_REPORT_ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO


--DROP TABLE TBL_DATASET_CACHE_AUDIT
CREATE TABLE TBL_DATASET_CACHE_AUDIT(
      DATASET_CACHE_ID int IDENTITY(1,1) NOT NULL,
      
      SYSTEM_CODE VARCHAR(10) NOT NULL,
      SCHEDULE_ID int NOT NULL,
      OBJECT_ID INT NOT NULL,
	  VIEW_ID INT NULL,
	  EXECUTION_TIME float NULL,      
      LAST_MODIFIED datetime NOT NULL,
	  STATUS VARCHAR(10) NOT NULL,
	  RECORD_COUNT int NULL,
      MESSAGE VARCHAR(300) NULL,	  
	  NEXT_SCH_RUN_TIME datetime NULL,
      CONSTRAINT PK_TBL_DATASET_CACHE_AUDIT PRIMARY KEY CLUSTERED 
      (
            DATASET_CACHE_ID
      )
)
GO

-- Add columns to TBL_SAVED_REPORTS
ALTER TABLE TBL_SAVED_REPORT
ADD
	XLSX_OUTPUT	VARBINARY(MAX) NULL,--XLXS File
	CSV_OUTPUT	VARBINARY(MAX) NULL,--CSV File
	RTF_OUTPUT	VARBINARY(MAX) NULL,--RTF File
	-- PDF Covered in FIXED_FORMAT_OUTPUT
	-- XLS Covered in EXCEL_OUTPUT

	-- Will show full path to file locations
	FILE_UNC_FTP VARCHAR(400) NULL,
	FILE_UNC_SAVE_FILE VARCHAR(400) NULL

-- LGXECO-1371 Audit - Save the parameters that users pass to a view
-- view and cache status enhancements

CREATE TABLE TBL_VIEW_AUDIT_PARAMETERS
(
	VIEW_AUDIT_ID BIGINT NOT NULL,
	PARAM_NAME varchar(50) NOT NULL,
	PARAM_DISPLAY varchar(50) NOT NULL,
	PARAM_VALUE varchar(100) NULL
	CONSTRAINT PK_TBL_VIEW_AUDIT_PARAMETERS PRIMARY KEY CLUSTERED 
	(
		VIEW_AUDIT_ID ,
		PARAM_NAME 
	)
)
GO

ALTER TABLE TBL_VIEW_AUDIT_PARAMETERS ADD  CONSTRAINT FK_TBL_VIEW_AUDIT_PARAMETERS FOREIGN KEY(VIEW_AUDIT_ID)
REFERENCES TBL_VIEW_AUDIT (ID)
GO


CREATE TABLE TBL_CACHE_STATUS
(
	CACHE_STATUS_ID		INT	NOT NULL,
	CACHE_DESCRIPTION	NVARCHAR(40),
	CONSTRAINT PK_CACHE_STATUS PRIMARY KEY CLUSTERED (CACHE_STATUS_ID)
)
GO

INSERT INTO TBL_CACHE_STATUS VALUES (0, 'Cache Disabled') ;
INSERT INTO TBL_CACHE_STATUS VALUES (1, 'Dataset cached, view not cached') ;
INSERT INTO TBL_CACHE_STATUS VALUES (2, 'Dataset cached, view caching disabled') ;
INSERT INTO TBL_CACHE_STATUS VALUES (3, 'Dataset cached, view cached') ;
INSERT INTO TBL_CACHE_STATUS VALUES (4, 'Dataset not cached, view cached') ;
INSERT INTO TBL_CACHE_STATUS VALUES (5, 'Dataset not cached, view not cached') ;

ALTER TABLE TBL_VIEW_AUDIT 
ADD
	SOURCE_IP_ADDRESS					VARCHAR(15)		NULL,
	ROW_COUNT							INT				NULL,
	CACHE_STATUS_ID						INT				NULL,			
	RETRIEVED_FROM_CACHE_FLAG			NUMERIC(1,0)	NULL
GO


ALTER TABLE TBL_VIEW_AUDIT ADD  CONSTRAINT FK_TBL_VIEW_AUDIT_CACHE_STATUS FOREIGN KEY(CACHE_STATUS_ID)
REFERENCES TBL_CACHE_STATUS (CACHE_STATUS_ID)
GO

CREATE TABLE TBL_SPREADSHEET_MODIFICATIONS_AUDIT (
    SYSTEM_CODE varchar(10) NOT NULL,
    LOGIN_NAME varchar(30) NOT NULL,
    FIELD_NAME varchar(255),
    CELL_ADDRESS varchar(10),
    SPREADSHEET_ACTION varchar(10), 
	OLD_VALUE text, --newvalue and oldvalue need to be text fields
	NEW_VALUE text,
	MODIFIED_DATE_TIME datetime,
	SAVED_DATE_TIME datetime,
	SESSION_ID varchar(255) NOT NULL,
	DOCUMENT_ID varchar(255),
	OBJECT_ID int,
	SPREADSHEET_ID int,
	MODIFICATION_ID int NOT NULL,
	SUCCESSFUL_SAVE numeric(1,0),
	SQL_STATEMENT text,
	PRIMARY KEY(MODIFICATION_ID)
);


-- Add missing field from TBL_SAVED_REPORTS.  Not actually used in the code!
alter TABLE [TBL_SAVED_REPORT]
add 
	[ARCHIVED_AND_NOT_APPROVED] [numeric](1, 0) NULL
 
GO