IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL
BEGIN
    CREATE TABLE [__EFMigrationsHistory] (
        [MigrationId] nvarchar(150) NOT NULL,
        [ProductVersion] nvarchar(32) NOT NULL,
        CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
    );
END;
GO

BEGIN TRANSACTION;
GO

CREATE TABLE [LIS_CFG_LabMachine] (
    [Id] int NOT NULL IDENTITY,
    [MachinedCode] nvarchar(max) NULL,
    [MachinedName] nvarchar(max) NULL,
    [Manufacture] nvarchar(max) NULL,
    [MachineDescription] nvarchar(max) NULL,
    [ParserName] nvarchar(max) NULL,
    [Remarks] nvarchar(max) NULL,
    [IsActive] bit NOT NULL,
    CONSTRAINT [PK_LIS_CFG_LabMachine] PRIMARY KEY ([Id])
);
GO

CREATE TABLE [LAB_LIS_ComponentMaster] (
    [Id] int NOT NULL IDENTITY,
    [MachineId] int NOT NULL,
    [TestName] nvarchar(max) NULL,
    [DisplayName] nvarchar(max) NULL,
    [IsActive] bit NOT NULL,
    CONSTRAINT [PK_LAB_LIS_ComponentMaster] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_LAB_LIS_ComponentMaster_LIS_CFG_LabMachine_MachineId] FOREIGN KEY ([MachineId]) REFERENCES [LIS_CFG_LabMachine] ([Id]) ON DELETE CASCADE
);
GO

CREATE TABLE [LAB_LIS_SampleData] (
    [Id] int NOT NULL IDENTITY,
    [MachineId] int NOT NULL,
    [DateTime] datetime2 NOT NULL,
    [BarCodeNumber] nvarchar(max) NULL,
    [Fluid] int NOT NULL,
    [Quadrant] nvarchar(max) NULL,
    [Cup] nvarchar(max) NULL,
    [TrayName] nvarchar(max) NULL,
    [StatFlag] nvarchar(max) NULL,
    [ControlFlag] nvarchar(max) NULL,
    [Mode] int NOT NULL,
    [DilutionFactor] nvarchar(max) NULL,
    [IsActive] bit NOT NULL,
    CONSTRAINT [PK_LAB_LIS_SampleData] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_LAB_LIS_SampleData_LIS_CFG_LabMachine_MachineId] FOREIGN KEY ([MachineId]) REFERENCES [LIS_CFG_LabMachine] ([Id]) ON DELETE CASCADE
);
GO

CREATE TABLE [LAB_LIS_SampleResult] (
    [Id] int NOT NULL IDENTITY,
    [LisSampleId] int NULL,
    [MachineId] int NOT NULL,
    [ComponentMasterId] int NOT NULL,
    [Test] nvarchar(max) NULL,
    [Value] nvarchar(max) NULL,
    [Unit] nvarchar(max) NULL,
    [ErrorFlag] nvarchar(max) NULL,
    [Error] nvarchar(max) NULL,
    [WarningFlag] nvarchar(max) NULL,
    [Warning] nvarchar(max) NULL,
    [BarCodeNumber] nvarchar(max) NULL,
    [IsSync] bit NOT NULL,
    [IsActive] bit NOT NULL,
    CONSTRAINT [PK_LAB_LIS_SampleResult] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_LAB_LIS_SampleResult_LAB_LIS_SampleData_LisSampleId] FOREIGN KEY ([LisSampleId]) REFERENCES [LAB_LIS_SampleData] ([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FK_LAB_LIS_SampleResult_LIS_CFG_LabMachine_MachineId] FOREIGN KEY ([MachineId]) REFERENCES [LIS_CFG_LabMachine] ([Id]) ON DELETE CASCADE
);
GO

CREATE INDEX [IX_LAB_LIS_ComponentMaster_MachineId] ON [LAB_LIS_ComponentMaster] ([MachineId]);
GO

CREATE INDEX [IX_LAB_LIS_SampleData_MachineId] ON [LAB_LIS_SampleData] ([MachineId]);
GO

CREATE INDEX [IX_LAB_LIS_SampleResult_LisSampleId] ON [LAB_LIS_SampleResult] ([LisSampleId]);
GO

CREATE INDEX [IX_LAB_LIS_SampleResult_MachineId] ON [LAB_LIS_SampleResult] ([MachineId]);
GO

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20210525154423_Initial', N'5.0.6');
GO

COMMIT;
GO

