CREATE TABLE PA_USER (
  USR_ID INT IDENTITY NOT NULL,
  USR_LOGON_NAME NVARCHAR(440),
  USR_PASSWORD_HASH NVARCHAR(255),
  USR_EMAIL NVARCHAR(MAX),
  USR_DESCRIPTION NVARCHAR(MAX),
  USR_FULL_NAME NVARCHAR(MAX),
  USR_FLAGS INT,
  USR_LAST_LOGIN_DATE DATETIME,
  USR_LOGIN_ATTEMPTS_COUNT INT,
  USR_BLOCKED_DATE DATETIME,
  CONSTRAINT PK_PA_USER PRIMARY KEY CLUSTERED (USR_ID)
);

CREATE UNIQUE NONCLUSTERED INDEX PA_USER_UNIQUE_LOGON_NAME ON PA_USER (USR_LOGON_NAME) WHERE (USR_LOGON_NAME IS NOT NULL);


CREATE TABLE PA_USER_GROUP (
  USG_ID BIGINT IDENTITY NOT NULL,
  USG_GROUP_ID INT,
  USG_MEMBER_ID INT,
  USG_ROLE_ID INT,
  CONSTRAINT PK_PA_USER_GROUP PRIMARY KEY CLUSTERED (USG_ID)
);

ALTER TABLE PA_USER_GROUP ADD CONSTRAINT PA_USER_GROUP_GROUP FOREIGN KEY (USG_GROUP_ID) REFERENCES PA_USER(USR_ID);
ALTER TABLE PA_USER_GROUP ADD CONSTRAINT PA_USER_GROUP_MEMBER FOREIGN KEY (USG_MEMBER_ID) REFERENCES PA_USER(USR_ID);
CREATE INDEX PA_USER_GROUP_GROUP_ID ON PA_USER_GROUP (USG_GROUP_ID);
CREATE INDEX PA_USER_GROUP_MEMBER_ID ON PA_USER_GROUP (USG_MEMBER_ID);

CREATE TABLE PA_PROJECT (
  PRO_ID BIGINT IDENTITY NOT NULL,
  PRO_PARENT_ID BIGINT,
  PRO_NAME NVARCHAR(440),
  PRO_DESCRIPTION NVARCHAR(MAX),
  PRO_CREATED_DATE DATETIME,
  PRO_CREATED_BY INT,
  PRO_LAST_MODIFIED_DATE DATETIME,
  PRO_LAST_MODIFIED_BY INT,
  PRO_DELETED_DATE DATETIME,
  PRO_DELETED_BY INT,
  PRO_FLAGS INT,
  PRO_CONFIGURATION NVARCHAR(MAX),
  CONSTRAINT PK_PA_PROJECT PRIMARY KEY CLUSTERED (PRO_ID)
);
ALTER TABLE PA_PROJECT ADD CONSTRAINT PA_PROJECT_PARENT_ID FOREIGN KEY (PRO_PARENT_ID) REFERENCES PA_PROJECT(PRO_ID);
ALTER TABLE PA_PROJECT ADD CONSTRAINT PA_PROJECT_CREATED_BY FOREIGN KEY (PRO_CREATED_BY) REFERENCES PA_USER(USR_ID);
ALTER TABLE PA_PROJECT ADD CONSTRAINT PA_PROJECT_LAST_MODIFIED_BY FOREIGN KEY (PRO_LAST_MODIFIED_BY) REFERENCES PA_USER(USR_ID);
ALTER TABLE PA_PROJECT ADD CONSTRAINT PA_PROJECT_DELETED_BY FOREIGN KEY (PRO_DELETED_BY) REFERENCES PA_USER(USR_ID);
CREATE NONCLUSTERED INDEX PA_PROJECT_CREATED_BY ON PA_PROJECT (PRO_CREATED_BY);
CREATE NONCLUSTERED INDEX PA_PROJECT_DELETED_BY ON PA_PROJECT (PRO_DELETED_BY);
CREATE NONCLUSTERED INDEX PA_PROJECT_LAST_MODIFIED_BY ON PA_PROJECT (PRO_LAST_MODIFIED_BY);
CREATE NONCLUSTERED INDEX PA_PROJECT_PARENT_ID ON PA_PROJECT (PRO_PARENT_ID);

CREATE TABLE PA_MODEL (
  MOD_ID BIGINT IDENTITY NOT NULL,
  MOD_PROJECT_ID BIGINT,
  MOD_NAME NVARCHAR(440),
  MOD_DESCRIPTION NVARCHAR(MAX),
  MOD_CREATED_DATE DATETIME,
  MOD_CREATED_BY INT,
  MOD_LAST_MODIFIED_DATE DATETIME,
  MOD_LAST_MODIFIED_BY INT,
  MOD_LAST_IMPORT_DATE DATETIME,
  MOD_LAST_IMPORT_BY INT,
  MOD_ARCHIVED_DATE DATETIME,
  MOD_ARCHIVED_BY INT,
  MOD_FLAGS INT,
  MOD_NUM_EVENTS BIGINT,
  MOD_NUM_ACTIVITIES BIGINT,
  MOD_NUM_CASES BIGINT,
  MOD_NUM_EVENT_ATTRIBUTE_TYPES BIGINT,
  MOD_NUM_CASE_ATTRIBUTE_TYPES BIGINT,
  MOD_CONFIGURATION NVARCHAR(MAX),
  CONSTRAINT PK_PA_MODEL PRIMARY KEY CLUSTERED (MOD_ID)
);
ALTER TABLE PA_MODEL ADD CONSTRAINT PA_MODEL_PROJECT FOREIGN KEY (MOD_PROJECT_ID) REFERENCES PA_PROJECT(PRO_ID);
ALTER TABLE PA_MODEL ADD CONSTRAINT PA_MODEL_CREATED_BY FOREIGN KEY (MOD_CREATED_BY) REFERENCES PA_USER(USR_ID);
ALTER TABLE PA_MODEL ADD CONSTRAINT PA_MODEL_LAST_MODIFIED_BY FOREIGN KEY (MOD_LAST_MODIFIED_BY) REFERENCES PA_USER(USR_ID);
CREATE NONCLUSTERED INDEX PA_MODEL_CREATED_BY ON PA_MODEL (MOD_CREATED_BY);
CREATE NONCLUSTERED INDEX PA_MODEL_LAST_MODIFIED_BY ON PA_MODEL (MOD_LAST_MODIFIED_BY);
CREATE NONCLUSTERED INDEX PA_MODEL_PROJECT_ID ON PA_MODEL (MOD_PROJECT_ID);

CREATE TABLE PA_VIEW (
  VIE_ID BIGINT IDENTITY NOT NULL,
  VIE_MODEL_ID BIGINT,
  VIE_NAME NVARCHAR(MAX),
  VIE_DESCRIPTION NVARCHAR(MAX),
  VIE_FLAGS INT,
  VIE_CREATED_DATE DATETIME,
  VIE_CREATED_BY INT,
  VIE_LAST_MODIFIED_DATE DATETIME,
  VIE_LAST_MODIFIED_BY INT,
  VIE_PUBLISH_MODE INT,
  VIE_CONFIGURATION NVARCHAR(MAX),
  CONSTRAINT PK_PA_VIEW PRIMARY KEY CLUSTERED (VIE_ID)
);
ALTER TABLE PA_VIEW ADD CONSTRAINT PA_VIEW_MODEL FOREIGN KEY
	(VIE_MODEL_ID) REFERENCES PA_MODEL(MOD_ID);
CREATE INDEX PA_VIEW_MODEL_ID ON PA_VIEW (VIE_MODEL_ID);
ALTER TABLE PA_VIEW ADD CONSTRAINT PA_VIEW_CREATED_BY FOREIGN KEY (VIE_CREATED_BY) REFERENCES PA_USER(USR_ID);
ALTER TABLE PA_VIEW ADD CONSTRAINT PA_VIEW_LAST_MODIFIED_BY FOREIGN KEY (VIE_LAST_MODIFIED_BY) REFERENCES PA_USER(USR_ID);
CREATE NONCLUSTERED INDEX PA_VIEW_CREATED_BY ON PA_VIEW (VIE_CREATED_BY);
CREATE NONCLUSTERED INDEX PA_VIEW_LAST_MODIFIED_BY ON PA_VIEW (VIE_LAST_MODIFIED_BY);

CREATE TABLE PA_ROLE (
  ROL_ID INT IDENTITY NOT NULL,
  ROL_NAME NVARCHAR(255),
  ROL_FLAGS INT,
  CONSTRAINT PK_PA_ROLE PRIMARY KEY CLUSTERED (ROL_ID)
);

CREATE TABLE PA_USER_ROLE (
  URO_ID INT IDENTITY NOT NULL,
  URO_PROJECT_ID BIGINT,
  URO_USER_ID INT,
  URO_ROLE_ID INT,
  CONSTRAINT PK_PA_USER_ROLE PRIMARY KEY CLUSTERED (URO_ID)
);
ALTER TABLE PA_USER_ROLE ADD CONSTRAINT PA_USER_ROLE_PROJECT FOREIGN KEY (URO_PROJECT_ID) REFERENCES PA_PROJECT(PRO_ID);
ALTER TABLE PA_USER_ROLE ADD CONSTRAINT PA_USER_ROLE_USER FOREIGN KEY
	(URO_USER_ID) REFERENCES PA_USER(USR_ID);
ALTER TABLE PA_USER_ROLE ADD CONSTRAINT PA_USER_ROLE_ROLE FOREIGN KEY
	(URO_ROLE_ID) REFERENCES PA_ROLE(ROL_ID);
CREATE INDEX PA_USER_ROLE_PROJECT_ID ON PA_USER_ROLE (URO_PROJECT_ID);
CREATE INDEX PA_USER_ROLE_USER_ID ON PA_USER_ROLE (URO_USER_ID);
CREATE INDEX PA_USER_ROLE_ROLE_ID ON PA_USER_ROLE (URO_ROLE_ID);

CREATE TABLE PA_ROLE_PERMISSION (
  ROP_ID INT IDENTITY NOT NULL,
  ROP_ROLE_ID INT NOT NULL,
  ROP_PERMISSION_ID INT NOT NULL,
  CONSTRAINT PK_PA_ROLE_PERMISSION PRIMARY KEY CLUSTERED (ROP_ID)
);
ALTER TABLE PA_ROLE_PERMISSION ADD CONSTRAINT PA_PA_ROLE_PERMISSION_ROLE FOREIGN KEY
	(ROP_ROLE_ID) REFERENCES PA_ROLE(ROL_ID);
CREATE NONCLUSTERED INDEX PA_ROLE_PERMISSION_ROLE_ID ON PA_ROLE_PERMISSION (ROP_ROLE_ID);

CREATE TABLE PA_OPERATION_STATUS (
  OPE_ID BIGINT IDENTITY NOT NULL,
  OPE_NAME NTEXT,
  OPE_SESSION_ID UNIQUEIDENTIFIER,
  OPE_REQUEST_ID NVARCHAR(MAX),
  OPE_VIEW_ID BIGINT,
  OPE_MODEL_ID BIGINT,
  OPE_USER_ID INT,
  OPE_FLAGS INT,
  OPE_START_TIME DATETIME,
  OPE_END_TIME DATETIME,
  OPE_MESSAGE NVARCHAR(MAX),
  OPE_ADDITIONAL_DATA NVARCHAR(MAX),
  OPE_PARAMETERS NVARCHAR(MAX),
  OPE_LAST_POLLING_TIME DATETIME,
  CONSTRAINT PK_PA_OPERATION_STATUS PRIMARY KEY CLUSTERED (OPE_ID)
);
CREATE INDEX PA_OPERATION_STATUS_SESSION_ID ON PA_OPERATION_STATUS (OPE_SESSION_ID);
CREATE INDEX PA_OPERATION_STATUS_START_TIME ON PA_OPERATION_STATUS (OPE_START_TIME);
ALTER TABLE PA_OPERATION_STATUS ADD CONSTRAINT PA_OPERATION_STATUS_USER FOREIGN KEY (OPE_USER_ID) REFERENCES PA_USER(USR_ID);
CREATE NONCLUSTERED INDEX PA_OPERATION_STATUS_USER_ID ON PA_OPERATION_STATUS (OPE_USER_ID);

CREATE TABLE PA_SESSION (
  SES_ID BIGINT IDENTITY NOT NULL,
  SES_SESSION_ID UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL,
  SES_CREATED_DATE DATETIME,
  SES_FINISHED_DATE DATETIME,
  SES_LAST_ACCESS DATETIME,
  SES_USER_ID INT,
  SES_USER_LOGON_NAME NVARCHAR(440),
  SES_USER_IP_ADDRESS NVARCHAR(MAX),
  SES_FLAGS INT,
  SES_CLIENT_VERSION NVARCHAR(MAX),
  SES_SERVER_VERSION NVARCHAR(MAX),
  SES_ADDITIONAL_DATA NVARCHAR(MAX),
  SES_AUTHENTICATION_METHOD INT,
  CONSTRAINT PK_PA_SESSION PRIMARY KEY CLUSTERED (SES_ID)
);
ALTER TABLE PA_SESSION ADD CONSTRAINT PA_SESSION_USER FOREIGN KEY (SES_USER_ID) REFERENCES PA_USER(USR_ID);
CREATE INDEX PA_SESSION_SESSION_ID ON PA_SESSION (SES_SESSION_ID);
CREATE INDEX PA_SESSION_USER_ID ON PA_SESSION (SES_USER_ID);
CREATE INDEX PA_SESSION_FINISHED_DATE ON PA_SESSION (SES_FINISHED_DATE);

CREATE TABLE PA_UI_ELEMENT (
  UIE_ID BIGINT IDENTITY NOT NULL,
  UIE_IDENTIFIER NVARCHAR(MAX),
  UIE_PROJECT_ID BIGINT,
  UIE_CREATED_DATE DATETIME,
  UIE_CREATED_BY INT,
  UIE_LAST_MODIFIED_DATE DATETIME,
  UIE_LAST_MODIFIED_BY INT,
  UIE_PROPERTIES NVARCHAR(MAX),
  UIE_NAME NVARCHAR(440),
  UIE_DESCRIPTION NVARCHAR(MAX),
  UIE_TYPE int,
  CONSTRAINT PK_PA_UI_ELEMENT PRIMARY KEY CLUSTERED (UIE_ID)
);
ALTER TABLE PA_UI_ELEMENT ADD CONSTRAINT PA_UI_ELEMENT_CREATED_BY_USER_ID FOREIGN KEY (UIE_CREATED_BY) REFERENCES PA_USER(USR_ID);
ALTER TABLE PA_UI_ELEMENT ADD CONSTRAINT PA_UI_ELEMENT_LAST_MODIFIED_BY_USER_ID FOREIGN KEY (UIE_LAST_MODIFIED_BY) REFERENCES PA_USER(USR_ID);
ALTER TABLE PA_UI_ELEMENT ADD CONSTRAINT PA_UI_ELEMENT_PROJECT FOREIGN KEY (UIE_PROJECT_ID) REFERENCES PA_PROJECT(PRO_ID);
CREATE INDEX PA_UI_ELEMENT_TYPE ON PA_UI_ELEMENT(UIE_TYPE);
CREATE NONCLUSTERED INDEX PA_UI_ELEMENT_CREATED_BY ON PA_UI_ELEMENT (UIE_CREATED_BY);
CREATE NONCLUSTERED INDEX PA_UI_ELEMENT_LAST_MODIFIED_BY ON PA_UI_ELEMENT (UIE_LAST_MODIFIED_BY);
CREATE NONCLUSTERED INDEX PA_UI_ELEMENT_PROJECT_ID ON PA_UI_ELEMENT (UIE_PROJECT_ID);

CREATE TABLE PA_USER_ATTRIBUTE (
  UAT_ID BIGINT IDENTITY NOT NULL,
  UAT_USER_ID INT NOT NULL,
  UAT_MODEL_ID BIGINT,
  UAT_PROJECT_ID BIGINT,
  UAT_DASHBOARD_ID BIGINT,
  UAT_KEY NVARCHAR(440),
  UAT_VALUE2 NVARCHAR(MAX) NOT NULL DEFAULT N'3',
  UAT_VALUE SQL_VARIANT,
  UAT_VALUE_STRING NVARCHAR(MAX),
  CONSTRAINT PK_PA_USER_ATTRIBUTE PRIMARY KEY CLUSTERED (UAT_ID)
);
ALTER TABLE PA_USER_ATTRIBUTE ADD CONSTRAINT PA_USER_ATTRIBUTE_MODEL FOREIGN KEY
	(UAT_MODEL_ID) REFERENCES PA_MODEL(MOD_ID);
CREATE INDEX PA_USER_ATTRIBUTE_MODEL_ID ON PA_USER_ATTRIBUTE (UAT_MODEL_ID);
ALTER TABLE PA_USER_ATTRIBUTE ADD CONSTRAINT PA_USER_ATTRIBUTE_USER FOREIGN KEY
	(UAT_USER_ID) REFERENCES PA_USER(USR_ID);
CREATE INDEX PA_USER_ATTRIBUTE_USER_ID ON PA_USER_ATTRIBUTE (UAT_USER_ID);
ALTER TABLE PA_USER_ATTRIBUTE ADD CONSTRAINT PA_USER_ATTRIBUTE_PROJECT FOREIGN KEY
	(UAT_PROJECT_ID) REFERENCES PA_PROJECT(PRO_ID);
CREATE INDEX PA_USER_ATTRIBUTE_PROJECT_ID ON PA_USER_ATTRIBUTE (UAT_PROJECT_ID);
ALTER TABLE PA_USER_ATTRIBUTE ADD CONSTRAINT PA_USER_ATTRIBUTE_DASHBOARD FOREIGN KEY
	(UAT_DASHBOARD_ID) REFERENCES PA_UI_ELEMENT(UIE_ID) ON DELETE CASCADE;
CREATE INDEX PA_USER_ATTRIBUTE_DASHBOARD_ID ON PA_USER_ATTRIBUTE (UAT_DASHBOARD_ID);
CREATE INDEX PA_USER_ATTRIBUTE_KEY ON PA_USER_ATTRIBUTE (UAT_KEY);

CREATE TABLE PA_CONFIGURATION (
  CFG_ID INT IDENTITY NOT NULL,
  CFG_KEY NVARCHAR(440),
  CFG_VALUE SQL_VARIANT,
  CONSTRAINT PK_PA_CONFIGURATION PRIMARY KEY CLUSTERED (CFG_ID)
);
CREATE INDEX PA_CONFIGURATION_KEY ON PA_CONFIGURATION (CFG_KEY);

CREATE TABLE PA_INTEGRATION_TABLE (
  ITA_ID BIGINT IDENTITY NOT NULL,
  ITA_PROJECT_ID BIGINT NOT NULL,
  ITA_NAME NVARCHAR(440),
  ITA_DESCRIPTION NVARCHAR(MAX),
  ITA_CONFIGURATION NVARCHAR(MAX),
  ITA_FLAGS INT,
  ITA_CREATED_DATE DATETIME,
  ITA_CREATED_BY INT,
  ITA_LAST_MODIFIED_DATE DATETIME,
  ITA_LAST_MODIFIED_BY INT,
  ITA_LAST_IMPORT_DATE DATETIME,
  ITA_LAST_IMPORT_BY INT,
  ITA_LAST_CHANGE_COMMIT_DATE DATETIME,
  ITA_LAST_CHANGE_COMMIT_TOKEN NVARCHAR(40),
  ITA_LOG NVARCHAR(MAX),
  ITA_MAX_NUM_COLUMNS BIGINT,
  ITA_MAX_NUM_ROWS BIGINT,
  ITA_NUM_ROWS BIGINT,
  CONSTRAINT PK_PA_INTEGRATION_TABLE PRIMARY KEY CLUSTERED (ITA_ID)
);
ALTER TABLE PA_INTEGRATION_TABLE ADD CONSTRAINT PA_INTEGRATION_TABLE_CREATED_BY FOREIGN KEY (ITA_CREATED_BY) REFERENCES PA_USER(USR_ID);
ALTER TABLE PA_INTEGRATION_TABLE ADD CONSTRAINT PA_INTEGRATION_TABLE_LAST_MODIFIED_BY FOREIGN KEY (ITA_LAST_MODIFIED_BY) REFERENCES PA_USER(USR_ID);
ALTER TABLE PA_INTEGRATION_TABLE ADD CONSTRAINT PA_INTEGRATION_TABLE_LAST_IMPORT_BY FOREIGN KEY (ITA_LAST_IMPORT_BY) REFERENCES PA_USER(USR_ID);
ALTER TABLE PA_INTEGRATION_TABLE ADD CONSTRAINT PA_INTEGRATION_TABLE_PROJECT FOREIGN KEY (ITA_PROJECT_ID) REFERENCES PA_PROJECT(PRO_ID);
CREATE UNIQUE NONCLUSTERED INDEX PA_INTEGRATION_TABLE_UNIQUE_NAME_IN_PROJECT ON PA_INTEGRATION_TABLE (ITA_PROJECT_ID, ITA_NAME) WHERE (ITA_NAME IS NOT NULL);
CREATE INDEX PA_INTEGRATION_TABLE_PROJECT_ID ON PA_INTEGRATION_TABLE (ITA_PROJECT_ID);
CREATE INDEX PA_INTEGRATION_TABLE_CREATED_BY ON PA_INTEGRATION_TABLE (ITA_CREATED_BY);
CREATE NONCLUSTERED INDEX PA_INTEGRATION_TABLE_LAST_IMPORT_BY ON PA_INTEGRATION_TABLE (ITA_LAST_IMPORT_BY);
CREATE NONCLUSTERED INDEX PA_INTEGRATION_TABLE_LAST_MODIFIED_BY ON PA_INTEGRATION_TABLE(ITA_LAST_MODIFIED_BY);

CREATE TABLE PA_SCRIPT (
  SCR_ID BIGINT IDENTITY NOT NULL,
  SCR_PROJECT_ID BIGINT,
  SCR_USER_ID INT,
  SCR_CREATED_DATE DATETIME,
  SCR_CREATED_BY INT,
  SCR_LAST_MODIFIED_DATE DATETIME,
  SCR_LAST_MODIFIED_BY INT,
  SCR_TITLE NVARCHAR(440),
  SCR_TEXT NVARCHAR(MAX),
  SCR_DESCRIPTION NVARCHAR(MAX),
  SCR_FLAGS INT,
  SCR_LANGUAGE INT,
  CONSTRAINT PK_PA_SCRIPT PRIMARY KEY CLUSTERED (SCR_ID)
);
ALTER TABLE PA_SCRIPT ADD CONSTRAINT PA_SCRIPT_PROJECT FOREIGN KEY (SCR_PROJECT_ID) REFERENCES PA_PROJECT(PRO_ID);
ALTER TABLE PA_SCRIPT ADD CONSTRAINT PA_SCRIPT_CREATED_BY FOREIGN KEY (SCR_CREATED_BY) REFERENCES PA_USER(USR_ID);
ALTER TABLE PA_SCRIPT ADD CONSTRAINT PA_SCRIPT_LAST_MODIFIED_BY FOREIGN KEY (SCR_LAST_MODIFIED_BY) REFERENCES PA_USER(USR_ID);
CREATE INDEX PA_SCRIPT_PROJECT ON PA_SCRIPT (SCR_PROJECT_ID);
CREATE INDEX PA_SCRIPT_USER ON PA_SCRIPT (SCR_USER_ID);
CREATE NONCLUSTERED INDEX PA_SCRIPT_LAST_MODIFIED_BY ON PA_SCRIPT (SCR_LAST_MODIFIED_BY);
CREATE NONCLUSTERED INDEX PA_SCRIPT_CREATED_BY ON PA_SCRIPT (SCR_CREATED_BY);

CREATE TABLE PA_SCRIPT_PARAMETER (
  SCP_ID BIGINT IDENTITY NOT NULL,
  SCP_SCRIPT_ID BIGINT NOT NULL,
  SCP_TYPE INT,
  SCP_FLAGS INT,
  SCP_NAME NVARCHAR(440),
  SCP_DESCRIPTION NVARCHAR(MAX),
  CONSTRAINT PK_PA_SCRIPT_PARAMETER PRIMARY KEY CLUSTERED (SCP_ID)
);
ALTER TABLE PA_SCRIPT_PARAMETER ADD CONSTRAINT PA_SCRIPT_PARAMETER_SCRIPT FOREIGN KEY (SCP_SCRIPT_ID) REFERENCES PA_SCRIPT(SCR_ID);
CREATE INDEX PA_SCRIPT_PARAMETER_SCRIPT ON PA_SCRIPT_PARAMETER (SCP_SCRIPT_ID);

CREATE TABLE PA_SCRIPT_LOG (
  SCL_ID BIGINT IDENTITY NOT NULL,
  SCL_SCRIPT_ID BIGINT,
  SCL_TYPE INT,
  SCL_FLAGS INT,
  SCL_START_TIME DATETIME,
  SCL_END_TIME DATETIME,
  SCL_USER_ID INT,
  SCL_RESULT NVARCHAR(MAX),
  SCL_LOG NVARCHAR(MAX),
  SCL_ADDITIONAL_DATA NVARCHAR(MAX),
  SCL_OPERATION_ID BIGINT,
  CONSTRAINT PK_PA_SCRIPT_LOG PRIMARY KEY CLUSTERED (SCL_ID)
);
ALTER TABLE PA_SCRIPT_LOG ADD CONSTRAINT PA_SCRIPT_LOG_USER_ID FOREIGN KEY (SCL_USER_ID) REFERENCES PA_USER(USR_ID);
CREATE NONCLUSTERED INDEX PA_SCRIPT_LOG_TYPE ON PA_SCRIPT_LOG (SCL_SCRIPT_ID, SCL_TYPE) INCLUDE (SCL_OPERATION_ID);
CREATE NONCLUSTERED INDEX PA_SCRIPT_LOG_END_TIME ON PA_SCRIPT_LOG (SCL_END_TIME);
CREATE NONCLUSTERED INDEX PA_SCRIPT_LOG_OPERATION_ID ON PA_SCRIPT_LOG (SCL_OPERATION_ID);
CREATE NONCLUSTERED INDEX PA_SCRIPT_LOG_USER_ID ON PA_SCRIPT_LOG (SCL_USER_ID);

CREATE TABLE PA_KEY_VALUE_STORE (
  KVS_ID BIGINT IDENTITY NOT NULL,
  KVS_TYPE INT,
  KVS_PROJECT_ID BIGINT,
  KVS_KEY NVARCHAR(440),
  KVS_VALUE NVARCHAR(MAX),
  CONSTRAINT PK_PA_KEY_VALUE_STORE PRIMARY KEY CLUSTERED (KVS_ID)
);
ALTER TABLE PA_KEY_VALUE_STORE ADD CONSTRAINT PA_KEY_VALUE_STORE_PROJECT FOREIGN KEY (KVS_PROJECT_ID) REFERENCES PA_PROJECT(PRO_ID);
CREATE NONCLUSTERED INDEX PA_KEY_VALUE_STORE_PROJECT_ID ON PA_KEY_VALUE_STORE (KVS_PROJECT_ID);

CREATE TABLE PA_MODEL_ATTRIBUTE (
  MA_ID BIGINT IDENTITY NOT NULL,
  MA_MODEL_ID BIGINT NOT NULL,
  MA_TYPE INT NOT NULL,
  MA_NAME NVARCHAR(440) NOT NULL,
  MA_DESCRIPTION NVARCHAR(MAX),
  MA_CONTENT NVARCHAR(MAX) NOT NULL,
  MA_CREATED_DATE DATETIME NOT NULL,
  MA_CREATED_BY INT NOT NULL,
  MA_LAST_MODIFIED_DATE DATETIME NOT NULL,
  MA_LAST_MODIFIED_BY INT NOT NULL,
  CONSTRAINT PK_PA_MODEL_ATTRIBUTE PRIMARY KEY CLUSTERED (MA_ID)
);
ALTER TABLE PA_MODEL_ATTRIBUTE ADD CONSTRAINT PA_MODEL_ATTRIBUTE_CREATED_BY_USER_ID FOREIGN KEY (MA_CREATED_BY) REFERENCES PA_USER(USR_ID);
ALTER TABLE PA_MODEL_ATTRIBUTE ADD CONSTRAINT PA_MODEL_ATTRIBUTE_LAST_MODIFIED_BY_USER_ID FOREIGN KEY (MA_LAST_MODIFIED_BY) REFERENCES PA_USER(USR_ID);
ALTER TABLE PA_MODEL_ATTRIBUTE ADD CONSTRAINT PA_MODEL_ATTRIBUTE_MODEL FOREIGN KEY (MA_MODEL_ID) REFERENCES PA_MODEL(MOD_ID) ON DELETE CASCADE;

CREATE INDEX PA_MODEL_ATTRIBUTE_MODEL_ID ON PA_MODEL_ATTRIBUTE (MA_TYPE, MA_MODEL_ID);
CREATE UNIQUE INDEX PA_MODEL_ATTRIBUTE_MODEL_ID_NAME ON PA_MODEL_ATTRIBUTE (MA_TYPE, MA_MODEL_ID, MA_NAME);
CREATE NONCLUSTERED INDEX PA_MODEL_ATTRIBUTE_CREATED_BY ON PA_MODEL_ATTRIBUTE (MA_CREATED_BY);
CREATE NONCLUSTERED INDEX PA_MODEL_ATTRIBUTE_LAST_MODIFIED_BY ON PA_MODEL_ATTRIBUTE (MA_LAST_MODIFIED_BY);
CREATE NONCLUSTERED INDEX PA_MODEL_ATTRIBUTE_MODEL_ID_ONLY ON PA_MODEL_ATTRIBUTE (MA_MODEL_ID);

CREATE TABLE PA_MIGRATION_HISTORY (
  MH_ID NVARCHAR(150) NOT NULL,
  MH_EF_VERSION NVARCHAR(32) NOT NULL,
  CONSTRAINT [PK_PA_MIGRATION_HISTORY] PRIMARY KEY CLUSTERED (MH_ID)
);

insert into PA_MIGRATION_HISTORY (MH_ID, MH_EF_VERSION) values ('20250113102046_B-08566', '8.0.11');

DECLARE	@administratorRoleId BIGINT; INSERT INTO PA_ROLE (ROL_NAME, ROL_FLAGS) VALUES ('Administrator', 3); SELECT @administratorRoleId = SCOPE_IDENTITY();
DECLARE	@modelCreatorRoleId BIGINT; INSERT INTO PA_ROLE (ROL_NAME, ROL_FLAGS) VALUES ('ModelCreator', 1); SELECT @modelCreatorRoleId = SCOPE_IDENTITY();
DECLARE	@runScriptsRoleId BIGINT; INSERT INTO PA_ROLE (ROL_NAME, ROL_FLAGS) VALUES ('RunScripts', 1); SELECT @runScriptsRoleId = SCOPE_IDENTITY();
DECLARE	@designerRoleId BIGINT; INSERT INTO PA_ROLE (ROL_NAME, ROL_FLAGS) VALUES ('Designer', 2); SELECT @designerRoleId = SCOPE_IDENTITY();
DECLARE	@analyzerRoleId BIGINT; INSERT INTO PA_ROLE (ROL_NAME, ROL_FLAGS) VALUES ('Analyzer', 2); SELECT @analyzerRoleId = SCOPE_IDENTITY();
DECLARE	@viewerRoleId BIGINT; INSERT INTO PA_ROLE (ROL_NAME, ROL_FLAGS) VALUES ('Viewer', 2); SELECT @viewerRoleId = SCOPE_IDENTITY();

DECLARE	@administratorUserId BIGINT; INSERT INTO PA_USER (USR_LOGON_NAME, USR_PASSWORD_HASH) VALUES ('Administrator', 'JoqKyoDqMicuu0narinCyvHQ7Z2PuANZrnJEiqHsORzd3xzBDAutTdFE6EeqrHpr'); SELECT @administratorUserId = SCOPE_IDENTITY();

DECLARE	@genericReadPermissionId BIGINT       ; SELECT @genericReadPermissionId        = 1;
DECLARE	@genericWritePermissionId BIGINT      ; SELECT @genericWritePermissionId       = 2;
DECLARE	@deleteModelPermissionId BIGINT       ; SELECT @deleteModelPermissionId        = 3;
DECLARE	@createModelPermissionId BIGINT       ; SELECT @createModelPermissionId        = 4;
DECLARE	@resetDatabasePermissionId BIGINT     ; SELECT @resetDatabasePermissionId      = 5;
DECLARE	@filteringPermissionId BIGINT         ; SELECT @filteringPermissionId          = 6;
DECLARE	@manageUsersPermissionId BIGINT       ; SELECT @manageUsersPermissionId        = 7;
DECLARE	@sqlImportPermissionId BIGINT         ; SELECT @sqlImportPermissionId          = 8;
DECLARE	@manageOperationsPermissionId BIGINT  ; SELECT @manageOperationsPermissionId   = 9;
DECLARE	@manageViewsPermissionId BIGINT       ; SELECT @manageViewsPermissionId        = 10;
DECLARE	@manageReportsPermissionId BIGINT     ; SELECT @manageReportsPermissionId      = 11;
DECLARE	@manageIdeasPermissionId BIGINT       ; SELECT @manageIdeasPermissionId        = 12;
DECLARE	@manageProjectPermissionId BIGINT     ; SELECT @manageProjectPermissionId      = 13;
DECLARE	@manageIntegrationsPermissionId BIGINT; SELECT @manageIntegrationsPermissionId = 14;
DECLARE	@runScriptsPermissionId BIGINT        ; SELECT @runScriptsPermissionId         = 15;
DECLARE	@manageScriptsPermissionId BIGINT     ; SELECT @manageScriptsPermissionId      = 16;
DECLARE	@editDashboardsPermissionId BIGINT    ; SELECT @editDashboardsPermissionId     = 17;

INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@administratorRoleId, @genericReadPermissionId);
INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@administratorRoleId, @genericWritePermissionId);
INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@administratorRoleId, @deleteModelPermissionId);
INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@administratorRoleId, @createModelPermissionId);
INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@administratorRoleId, @resetDatabasePermissionId);
INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@administratorRoleId, @filteringPermissionId);
INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@administratorRoleId, @manageUsersPermissionId);
INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@administratorRoleId, @manageOperationsPermissionId);
INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@administratorRoleId, @manageViewsPermissionId);
INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@administratorRoleId, @manageReportsPermissionId);
INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@administratorRoleId, @manageIdeasPermissionId);
INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@administratorRoleId, @manageProjectPermissionId);
INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@administratorRoleId, @manageIntegrationsPermissionId);
INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@administratorRoleId, @manageScriptsPermissionId);
INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@administratorRoleId, @editDashboardsPermissionId);

INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@designerRoleId, @genericReadPermissionId);
INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@designerRoleId, @genericWritePermissionId);
INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@designerRoleId, @createModelPermissionId);
INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@designerRoleId, @filteringPermissionId);
INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@designerRoleId, @editDashboardsPermissionId);

INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@analyzerRoleId, @genericReadPermissionId);
INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@analyzerRoleId, @filteringPermissionId);
INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@analyzerRoleId, @editDashboardsPermissionId);

INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@viewerRoleId, @genericReadPermissionId);

INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@modelCreatorRoleId, @createModelPermissionId);

INSERT INTO PA_ROLE_PERMISSION (ROP_ROLE_ID, ROP_PERMISSION_ID) VALUES (@runScriptsRoleId, @runScriptsPermissionId);

INSERT INTO PA_USER_ROLE (URO_USER_ID, URO_ROLE_ID) VALUES (@administratorUserId, @administratorRoleId);
INSERT INTO PA_USER_ROLE (URO_USER_ID, URO_ROLE_ID) VALUES (@administratorUserId, @runScriptsRoleId);

INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('DatabaseId', CONVERT(NVARCHAR(255), NEWID()));
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('DatabaseBulkCopyTimeout', '600');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('DatabaseBulkCopyBatchSize', '5000');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('DatabaseAvailabilityMode', '0');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('SessionIdleTimeout', '3600');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('SessionMaximumDuration', '86400');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('DeleteModelsInRecycleBinOnStartup', '0');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('SandboxDatabaseConnectionString', '');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('SandboxDatabaseBulkCopyTimeout', '600');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('SandboxDatabaseBulkCopyBatchSize', '5000');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('StartupModelLoadingMaxParallelism', '2');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('AllowExternalDatasources', 'true');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('ServiceProviderLocation', '');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('SAMLMetadataUrl', '');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('SAMLUserIdAttribute', '');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('SAMLEncryptionCertificate', null);
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('SAMLSigningCertificate', null);
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('DefaultUiLanguage', null);
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('NumberOfParallelModelReaders', '4');

/*
  Disable non-temporary ETL target table by default.
*/
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('AllowNonTemporaryETLTargetTable', 'false');

/*
  Authentication settings
*/
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('AuthenticationMethod', '1');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('LDAPConnectionString', '');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('LDAPUserFilter', '');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('LDAPUserSearchBase', '');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('LDAPUserIdAttributeName', '');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('LDAPServerUserName', '');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('LDAPServerPassword', '');

/*
  SMTP mail server settings
*/
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('SmtpServer', '');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('SmtpPort', '25');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('SmtpAuthenticationUsername', '');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('SmtpAuthenticationPassword', '');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('SmtpEnableSSL', 'false');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('SmtpFromAddress', '');

/*
  Connection strings to external systems.
*/
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('RedshiftConnectionString', '');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('SqlServerConnectionString', '');
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('SnowflakeConnectionString', '');

/*
  QueryTimeout value for api/expression and api/expression/query in seconds
*/
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('QueryTimeout', '300');

/*
  Defines to which datasource the datatables are created by default
*/
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('DefaultDataSource', 'Snowflake');

/*
  API Key to be used to access services provided by OpenAI.
*/
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('OpenAIAPIKey', '');

/*
  Default Model to use by OpenAI API.
*/
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('OpenAIDefaultModelName', '');

/*
  By default filter cache tables should contain all data table columns.
*/
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('CacheOnlyPrimaryKeysForFilters', '0');

DECLARE @dbVersion SQL_VARIANT
SET @dbVersion = '106'

INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('DatabaseVersion', @dbVersion);
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('InitializationScriptDatabaseVersion', @dbVersion);
INSERT INTO PA_CONFIGURATION (CFG_KEY, CFG_VALUE) VALUES ('MinimumDatabaseVersion', @dbVersion);



/*
  Enable better non-blocking concurrent connection handling.

  NOTE: 
    These commands must be run using implicit transactions. 
    Otherwise we will get: "ALTER DATABASE statement not allowed within multi-statement transaction"-exception.
*/
DECLARE @databaseName NVARCHAR(MAX)
SELECT @databaseName = DB_NAME();
DECLARE @userAccess INT;
DECLARE @recoveryModel INT;
DECLARE @isReadCommittedSnapshotOn INT;
DECLARE @snapshotIsolationState INT;
SELECT @userAccess = user_access, @isReadCommittedSnapshotOn = is_read_committed_snapshot_on, @recoveryModel = recovery_model, @snapshotIsolationState = snapshot_isolation_state FROM sys.databases WHERE name = @databaseName;

IF ((@userAccess <> 0) OR (@recoveryModel <> 3) OR (@isReadCommittedSnapshotOn <> 1) OR (@snapshotIsolationState <> 1)) BEGIN 
  DECLARE @dynamicSql NVARCHAR(MAX);
  SET @dynamicSql = '
  ALTER DATABASE [' + @databaseName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  ALTER DATABASE [' + @databaseName + '] SET ALLOW_SNAPSHOT_ISOLATION ON;
  ALTER DATABASE [' + @databaseName + '] SET READ_COMMITTED_SNAPSHOT ON;
  ALTER DATABASE [' + @databaseName + '] SET RECOVERY SIMPLE;
  ALTER DATABASE [' + @databaseName + '] SET MULTI_USER;
  '
  EXEC (@dynamicSql) 
END;
GO
