
PRINT N'Creating Schema [sFeedback]...';

GO
CREATE SCHEMA [sFeedback];

GO
PRINT N'Creating User-Defined Table Type [sWellness].[udt_MHIScoreUpdateBulk]...';


GO
CREATE TYPE [sWellness].[udt_MHIScoreUpdateBulk] AS TABLE (
    [EmployeeCode] NVARCHAR (50)   NOT NULL,
    [BMI]          NUMERIC (10, 2) NULL,
    [SystolicBP]   NUMERIC (10)    NULL,
    [DiastolicBP]  NUMERIC (10)    NULL,
    [HBA1C]        NUMERIC (10, 2) NULL,
    [LDL]          NUMERIC (10, 2) NULL,
    [Stress]       NUMERIC (10, 2) NULL,
    [LifeStyle]    NUMERIC (10, 2) NULL);


GO
PRINT N'Creating User-Defined Table Type [sWellness].[udt_MHIScoreUpdation]...';


GO
CREATE TYPE [sWellness].[udt_MHIScoreUpdation] AS TABLE (
    [EmployeeCode] NVARCHAR (100)  NULL,
    [BMI]          NUMERIC (10, 2) NULL,
    [SystolicBP]   NUMERIC (10)    NULL,
    [DiastolicBP]  NUMERIC (10)    NULL,
    [HBA1C]        NUMERIC (10, 2) NULL,
    [LDL]          NUMERIC (10, 2) NULL,
    [Stress]       NUMERIC (10, 2) NULL,
    [LifeStyle]    NUMERIC (10, 2) NULL);


GO
PRINT N'Altering Table [sAppointment].[tAppointment]...';


GO
ALTER TABLE [sAppointment].[tAppointment] ALTER COLUMN [FollowupRemark] VARCHAR (1000) NULL;


GO
ALTER TABLE [sAppointment].[tAppointment]
    ADD [VisitTypeId] INT NULL;


GO
PRINT N'Altering Table [sAppointment].[tArchieveAppointment]...';


GO
ALTER TABLE [sAppointment].[tArchieveAppointment] ALTER COLUMN [FollowupRemark] VARCHAR (1000) NULL;


GO
ALTER TABLE [sAppointment].[tArchieveAppointment]
    ADD [VisitTypeId] INT NULL;


GO
PRINT N'Altering Table [sExamination].[tAdditionalFieldSetup]...';


GO
ALTER TABLE [sExamination].[tAdditionalFieldSetup]
    ADD [VisitPurposeId] INT NULL;


GO
PRINT N'Altering Table [sExamination].[tArchieveComplaint]...';


GO
ALTER TABLE [sExamination].[tArchieveComplaint] ALTER COLUMN [Remark] NVARCHAR (1000) NULL;


GO
PRINT N'Altering Table [sExamination].[tArchieveOPD]...';


GO
ALTER TABLE [sExamination].[tArchieveOPD] ALTER COLUMN [FurtherActionRemark] NVARCHAR (1000) NULL;


GO
PRINT N'Altering Table [sExamination].[tArchievePrescriptionDetail]...';


GO
ALTER TABLE [sExamination].[tArchievePrescriptionDetail]
    ADD [DrugFrequencyPattern] NVARCHAR (200) NULL;


GO
PRINT N'Altering Table [sExamination].[tArchieveRegistration]...';


GO
ALTER TABLE [sExamination].[tArchieveRegistration] ALTER COLUMN [Remark] NVARCHAR (1000) NULL;


GO
ALTER TABLE [sExamination].[tArchieveRegistration]
    ADD [VisitTypeId] INT NULL;


GO
PRINT N'Altering Table [sExamination].[tArchieveVital]...';


GO
ALTER TABLE [sExamination].[tArchieveVital] ALTER COLUMN [Remark] NVARCHAR (1000) NULL;


GO
PRINT N'Altering Table [sExamination].[tOPD]...';


GO
ALTER TABLE [sExamination].[tOPD] ALTER COLUMN [FurtherActionRemark] NVARCHAR (1000) NULL;


GO
ALTER TABLE [sExamination].[tOPD]
    ADD [VisitTypeId] INT NULL;


GO
PRINT N'Altering Table [sExamination].[tOPDFitnessStatus]...';


GO
ALTER TABLE [sExamination].[tOPDFitnessStatus] ALTER COLUMN [FitnessStatusId] INT NULL;

ALTER TABLE [sExamination].[tOPDFitnessStatus] ALTER COLUMN [IsObsolete] BIT NULL;

ALTER TABLE [sExamination].[tOPDFitnessStatus] ALTER COLUMN [OrgId] INT NULL;


GO
ALTER TABLE [sExamination].[tOPDFitnessStatus]
    ADD [Code]            NVARCHAR (50)  NULL,
        [Title]           NVARCHAR (100) NULL,
        [CreatedUserId]   BIGINT         NULL,
        [CreatedDateTime] DATETIME       NULL,
        [UpdatedUserId]   BIGINT         NULL,
        [UpdatedDateTime] DATETIME       NULL;


GO
PRINT N'Altering Table [sExamination].[tOutSidePrescriptionDetail]...';


GO
ALTER TABLE [sExamination].[tOutSidePrescriptionDetail]
    ADD [DrugFrequencyPattern] NVARCHAR (200) NULL;


GO
PRINT N'Altering Table [sExamination].[tPrescriptionDetail]...';


GO
ALTER TABLE [sExamination].[tPrescriptionDetail]
    ADD [DrugFrequencyPattern] NVARCHAR (200) NULL;


GO
PRINT N'Altering Table [sExamination].[tRegistration]...';


GO
ALTER TABLE [sExamination].[tRegistration] ALTER COLUMN [Remark] NVARCHAR (1000) NULL;


GO
ALTER TABLE [sExamination].[tRegistration]
    ADD [VisitTypeId] INT NULL;


GO
PRINT N'Altering Table [sExamination].[tVital]...';


GO
ALTER TABLE [sExamination].[tVital] ALTER COLUMN [Remark] NVARCHAR (1000) NULL;


GO
PRINT N'Altering Table [sHospitalization].[tIntimationDetails]...';


GO
ALTER TABLE [sHospitalization].[tIntimationDetails]
    ADD [UnattendedDate]   DATETIME NULL;
GO
ALTER TABLE [sHospitalization].[tIntimationDetails]
    ADD [DateOfFileRecord] NVARCHAR (500) NULL;
GO
ALTER TABLE [sHospitalization].[tIntimationDetails]
     ADD [Message] NVARCHAR (MAX) NULL;
GO
PRINT N'Altering Table [sOrderEntry].[tTestMaster]...';


GO
ALTER TABLE [sOrderEntry].[tTestMaster]
    ADD [IsForOPD] BIT CONSTRAINT [DF_tTestMaster_IsForOPD] DEFAULT ((0)) NOT NULL;


GO
PRINT N'Altering Table [sSupport].[tHealthSurveyMaster]...';


GO
ALTER TABLE [sSupport].[tHealthSurveyMaster]
    ADD [Timeoutminutes] INT DEFAULT ((0)) NULL;


GO
PRINT N'Starting rebuilding table [sSupport].[tHealthSurveyResponseMaster]...';


GO
BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [sSupport].[tmp_ms_xx_tHealthSurveyResponseMaster] (
    [Id]                   INT      NOT NULL,
    [AssignSurveyId]       INT      NULL,
    [AssignSurveyDetailId] INT      NULL,
    [OrgId]                SMALLINT NOT NULL,
    [SourceTypeId]         SMALLINT NOT NULL,
    [SourceId]             BIGINT   NOT NULL,
    [StatusId]             SMALLINT NULL,
    [CreatedDateTime]      DATETIME NOT NULL,
    [CreatedUserId]        BIGINT   NOT NULL,
    [UpdatedDateTime]      DATETIME NULL,
    [UpdatedUserId]        BIGINT   NULL,
    [FillupStartDateTime]  DATETIME NULL,
    [FillupExpireDateTime] DATETIME NULL,
    CONSTRAINT [tmp_ms_xx_constraint_PK__tHealthS__3214EC07403DEBA31] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (FILLFACTOR = 85),
    CONSTRAINT [tmp_ms_xx_constraint_UQ_Folders_UserId_Id1] UNIQUE NONCLUSTERED ([AssignSurveyId] ASC, [SourceId] ASC) WITH (FILLFACTOR = 85)
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [sSupport].[tHealthSurveyResponseMaster])
    BEGIN
        INSERT INTO [sSupport].[tmp_ms_xx_tHealthSurveyResponseMaster] ([Id], [AssignSurveyId], [OrgId], [SourceTypeId], [SourceId], [StatusId], [CreatedDateTime], [CreatedUserId], [UpdatedDateTime], [UpdatedUserId])
        SELECT   [Id],
                 [AssignSurveyId],
                 [OrgId],
                 [SourceTypeId],
                 [SourceId],
                 [StatusId],
                 [CreatedDateTime],
                 [CreatedUserId],
                 [UpdatedDateTime],
                 [UpdatedUserId]
        FROM     [sSupport].[tHealthSurveyResponseMaster]
        ORDER BY [Id] ASC;
    END

DROP TABLE [sSupport].[tHealthSurveyResponseMaster];

EXECUTE sp_rename N'[sSupport].[tmp_ms_xx_tHealthSurveyResponseMaster]', N'tHealthSurveyResponseMaster';

EXECUTE sp_rename N'[sSupport].[tmp_ms_xx_constraint_PK__tHealthS__3214EC07403DEBA31]', N'PK__tHealthS__3214EC07403DEBA3', N'OBJECT';

EXECUTE sp_rename N'[sSupport].[tmp_ms_xx_constraint_UQ_Folders_UserId_Id1]', N'UQ_Folders_UserId_Id', N'OBJECT';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


GO
PRINT N'Altering Table [sWellness].[tWellnessActivityCategoryMaster]...';


GO
ALTER TABLE [sWellness].[tWellnessActivityCategoryMaster]
    ADD [OrgId] SMALLINT NULL;


GO
PRINT N'Altering Table [sWellness].[tWellnessActivityMaster]...';


GO
ALTER TABLE [sWellness].[tWellnessActivityMaster]
    ADD [OrgId] SMALLINT NULL;


GO
PRINT N'Altering Table [sWellness].[tWellnessActivityTaskMaster]...';


GO
ALTER TABLE [sWellness].[tWellnessActivityTaskMaster]
    ADD [OrgId] SMALLINT NULL;


GO
PRINT N'Altering Table [sWellness].[tWellnessConsultation]...';


GO
ALTER TABLE [sWellness].[tWellnessConsultation]
    ADD [OtherName]    NVARCHAR (250) NULL,
        [EmailAddress] NVARCHAR (100) NULL;


GO
PRINT N'Creating Table [sFeedback].[tFeedbackSubCategoryMaster]...';


GO
CREATE TABLE [sFeedback].[tFeedbackSubCategoryMaster] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [Code]       NVARCHAR (100) NOT NULL,
    [Name]       NVARCHAR (100) NOT NULL,
    [IsObsolete] BIT            NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating Table [sFeedback].[tFeedbackTypeMaster]...';


GO
CREATE TABLE [sFeedback].[tFeedbackTypeMaster] (
    [Id]            INT            IDENTITY (1, 1) NOT NULL,
    [Code]          NVARCHAR (100) NOT NULL,
    [Name]          NVARCHAR (100) NOT NULL,
    [FeedbackScale] NUMERIC (18)   NULL,
    [IsObsolete]    BIT            NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating Table [sFeedback].[tFeedbackTargetTypeMaster]...';


GO
CREATE TABLE [sFeedback].[tFeedbackTargetTypeMaster] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [Code]       NVARCHAR (100) NOT NULL,
    [Name]       NVARCHAR (100) NOT NULL,
    [IsObsolete] BIT            NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating Table [sFeedback].[tFeedbackTemplate]...';


GO
CREATE TABLE [sFeedback].[tFeedbackTemplate] (
    [Id]                  INT            IDENTITY (1, 1) NOT NULL,
    [OrgId]               SMALLINT       NULL,
    [Name]                NVARCHAR (250) NOT NULL,
    [FeedbackCategoryId]  INT            NULL,
    [FeedbackTypeId]      INT            NULL,
    [FeedbackDescription] NVARCHAR (250) NULL,
    [IsAnonymus]          BIT            NULL,
    [IsObsolete]          BIT            NULL,
    [CreatedUserId]       BIGINT         NULL,
    [CreatedDatetime]     DATETIME       NULL,
    [UpdatedUserId]       BIGINT         NULL,
    [UpdatedDatetime]     DATETIME       NULL,
    [ExpireDuration]      DATETIME       NULL,
    [TrigerDuration]      DATETIME       NULL,
    [Priority]            TINYINT        NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating Table [sFeedback].[tFeedbackTemplateDetail]...';


GO
CREATE TABLE [sFeedback].[tFeedbackTemplateDetail] (
    [Id]                 INT      IDENTITY (1, 1) NOT NULL,
    [FeedbackTemplateId] INT      NULL,
    [QuestionText]       INT      NULL,
    [AnswerType]         TINYINT  NULL,
    [IsRequired]         BIT      NULL,
    [OrderIndex]         SMALLINT NULL,
    [IsObsolete]         BIT      NULL,
    [CreatedUserId]      BIGINT   NULL,
    [CreatedDatetime]    DATETIME NULL,
    [UpdatedUserId]      BIGINT   NULL,
    [UpdatedDatetime]    DATETIME NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating Table [sFeedback].[tFeedback]...';


GO
CREATE TABLE [sFeedback].[tFeedback] (
    [Id]                     INT            IDENTITY (1, 1) NOT NULL,
    [FeedbackTemplateId]     INT            NULL,
    [TargetSourceTypeId]     INT            NULL,
    [TargetSourceId]         BIGINT         NULL,
    [RecordSourceTypeId]     INT            NULL,
    [RecordSourceId]         BIGINT         NULL,
    [AssignedToSourceTypeId] INT            NULL,
    [AssignedToSourceId]     BIGINT         NULL,
    [StatusId]               TINYINT        NULL,
    [Rating]                 NUMERIC (5, 2) NULL,
    [Priority]               TINYINT        NULL,
    [ExpireDateTime]         DATETIME       NULL,
    [TrigerDateTime]         DATETIME       NULL,
    [TrigerGroupId]          INT            NULL,
    [CreatedUserId]          BIGINT         NULL,
    [CreatedDatetime]        DATETIME       NULL,
    [UpdatedUserId]          BIGINT         NULL,
    [UpdatedDatetime]        DATETIME       NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating Table [sFeedback].[tFeedbackDetail]...';


GO
CREATE TABLE [sFeedback].[tFeedbackDetail] (
    [Id]                       INT            IDENTITY (1, 1) NOT NULL,
    [FeedbackId]               INT            NULL,
    [FeedbackTemplateDetailId] INT            NULL,
    [QuestionId]               INT            NULL,
    [TargetSourceId]           BIGINT         NULL,
    [AnswerText]               NVARCHAR (250) NULL,
    [AnswerRating]             TINYINT        NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating Table [sFeedback].[tFeedbackCategoryMaster]...';


GO
CREATE TABLE [sFeedback].[tFeedbackCategoryMaster] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [Code]       NVARCHAR (100) NOT NULL,
    [Name]       NVARCHAR (100) NOT NULL,
    [IsObsolete] BIT            NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating Table [sExamination].[tVisitTypeMaster]...';


GO
CREATE TABLE [sExamination].[tVisitTypeMaster] (
    [Id]              INT            NOT NULL,
    [Code]            NVARCHAR (30)  NOT NULL,
    [Title]           NVARCHAR (100) NOT NULL,
    [IsObsolete]      BIT            NOT NULL,
    [OrgId]           SMALLINT       NULL,
    [CreatedUserId]   BIGINT         NULL,
    [CreatedDateTime] DATETIME       NULL,
    [UpdatedUserId]   BIGINT         NULL,
    [UpdatedDateTime] DATETIME       NULL,
    CONSTRAINT [PK_tVisitTypeMaster] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (FILLFACTOR = 85)
);


GO
PRINT N'Creating Table [sSupport].[tAssignHealthSurveyTemplate]...';


GO
CREATE TABLE [sSupport].[tAssignHealthSurveyTemplate] (
    [Id]              INT      IDENTITY (1, 1) NOT NULL,
    [HealthSurveyId]  INT      NOT NULL,
    [AssignTypeId]    INT      NULL,
    [OrgId]           INT      NULL,
    [CreatedUserId]   BIGINT   NULL,
    [CreatedDateTime] DATETIME NULL,
    [UpdatedUserId]   BIGINT   NULL,
    [UpdatedDateTime] DATETIME NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating Table [sSupport].[tStaticAssignType]...';


GO
CREATE TABLE [sSupport].[tStaticAssignType] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [Code]       NVARCHAR (200) NOT NULL,
    [Title]      NVARCHAR (200) NOT NULL,
    [IsObsolete] BIT            NOT NULL,
    [OrgId]      INT            NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating Table [sWellness].[tConsultationRemarkLog]...';


GO
CREATE TABLE [sWellness].[tConsultationRemarkLog] (
    [Id]              INT             IDENTITY (1, 1) NOT NULL,
    [ConsultationId]  BIGINT          NOT NULL,
    [Remark]          NVARCHAR (1000) NULL,
    [CreatedUserId]   BIGINT          NOT NULL,
    [CreatedDateTime] DATETIME        NOT NULL,
    [UpdatedUserId]   BIGINT          NULL,
    [UpdatedDateTime] DATETIME        NULL,
    CONSTRAINT [PK_sWellness.tConsultationRemarkLog] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating Index [sWellness].[tMHIScore].[IX_tMHIScore_EmployeeId]...';


GO
CREATE NONCLUSTERED INDEX [IX_tMHIScore_EmployeeId]
    ON [sWellness].[tMHIScore]([EmployeeId] ASC);


GO
PRINT N'Creating Default Constraint unnamed constraint on [sFeedback].[tFeedbackSubCategoryMaster]...';


GO
ALTER TABLE [sFeedback].[tFeedbackSubCategoryMaster]
    ADD DEFAULT ((0)) FOR [IsObsolete];


GO
PRINT N'Creating Default Constraint unnamed constraint on [sFeedback].[tFeedbackTypeMaster]...';


GO
ALTER TABLE [sFeedback].[tFeedbackTypeMaster]
    ADD DEFAULT ((0)) FOR [IsObsolete];


GO
PRINT N'Creating Default Constraint unnamed constraint on [sFeedback].[tFeedbackTargetTypeMaster]...';


GO
ALTER TABLE [sFeedback].[tFeedbackTargetTypeMaster]
    ADD DEFAULT ((0)) FOR [IsObsolete];


GO
PRINT N'Creating Default Constraint unnamed constraint on [sFeedback].[tFeedbackTemplate]...';


GO
ALTER TABLE [sFeedback].[tFeedbackTemplate]
    ADD DEFAULT ((0)) FOR [IsAnonymus];


GO
PRINT N'Creating Default Constraint unnamed constraint on [sFeedback].[tFeedbackTemplate]...';


GO
ALTER TABLE [sFeedback].[tFeedbackTemplate]
    ADD DEFAULT ((0)) FOR [IsObsolete];


GO
PRINT N'Creating Default Constraint unnamed constraint on [sFeedback].[tFeedbackTemplateDetail]...';


GO
ALTER TABLE [sFeedback].[tFeedbackTemplateDetail]
    ADD DEFAULT ((0)) FOR [IsRequired];


GO
PRINT N'Creating Default Constraint unnamed constraint on [sFeedback].[tFeedbackTemplateDetail]...';


GO
ALTER TABLE [sFeedback].[tFeedbackTemplateDetail]
    ADD DEFAULT ((0)) FOR [IsObsolete];


GO
PRINT N'Creating Default Constraint unnamed constraint on [sFeedback].[tFeedbackCategoryMaster]...';


GO
ALTER TABLE [sFeedback].[tFeedbackCategoryMaster]
    ADD DEFAULT ((0)) FOR [IsObsolete];


GO
PRINT N'Creating Default Constraint unnamed constraint on [sSupport].[tStaticAssignType]...';


GO
ALTER TABLE [sSupport].[tStaticAssignType]
    ADD DEFAULT ((0)) FOR [IsObsolete];


GO
PRINT N'Creating Default Constraint [sWellness].[DF__tAssigned__Remin__30614D9F]...';


GO
ALTER TABLE [sWellness].[tAssignedActivity]
    ADD DEFAULT ((0)) FOR [ReminderEnabled];


GO
PRINT N'Creating Default Constraint [sWellness].[DF__tAssigned__Statu__315571D8]...';


GO
ALTER TABLE [sWellness].[tAssignedActivity]
    ADD DEFAULT ((1)) FOR [Status];


GO
PRINT N'Creating Default Constraint [sWellness].[DF__tMHIScore__Actio__1AAA123C]...';


GO
ALTER TABLE [sWellness].[tMHIScore_History]
    ADD DEFAULT ([scommon].[fgetdate]()) FOR [ActionDateTime];


GO
PRINT N'Creating Default Constraint [sWellness].[DF__tWellness__IsObs__3C080A75]...';


GO
ALTER TABLE [sWellness].[tWellnessActivityCategoryMaster]
    ADD DEFAULT ((0)) FOR [IsObsolete];


GO
PRINT N'Creating Default Constraint [sWellness].[DF_tWellnessVisitPurposeMaster_IsObsolete]...';


GO
ALTER TABLE [sWellness].[tWellnessVisitPurposeMaster]
    ADD DEFAULT ((0)) FOR [IsObsolete];