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

IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20230617155449_InitialMigration'
)
BEGIN
    CREATE TABLE [Employees] (
        [EmployeeNo] nchar(8) NOT NULL,
        [LastUpdate] datetime2 NOT NULL,
        CONSTRAINT [PK_Employees] PRIMARY KEY ([EmployeeNo])
    );
END;
GO

IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20230617155449_InitialMigration'
)
BEGIN
    CREATE TABLE [Materials] (
        [MaterialNo] nvarchar(450) NOT NULL,
        CONSTRAINT [PK_Materials] PRIMARY KEY ([MaterialNo])
    );
END;
GO

IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20230617155449_InitialMigration'
)
BEGIN
    CREATE TABLE [OrgModelOrgUnits] (
        [OrgId] nchar(8) NOT NULL,
        [LastUpdate] datetime2 NOT NULL,
        CONSTRAINT [PK_OrgModelOrgUnits] PRIMARY KEY ([OrgId])
    );
END;
GO

IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20230617155449_InitialMigration'
)
BEGIN
    CREATE TABLE [EmployeeOrgData] (
        [Id] uniqueidentifier NOT NULL,
        [EmployeeNo] nchar(8) NOT NULL,
        [FromId] bigint NOT NULL,
        [ToId] bigint NOT NULL,
        [ValidFrom] datetime2 NOT NULL,
        [ValidTo] datetime2 NOT NULL,
        [EmployeeGroup] nchar(1) NULL,
        [EmployeeSubGroup] nchar(2) NULL,
        [CompanyCode] nchar(4) NULL,
        [Plant] nchar(4) NULL,
        [CostCenter] nchar(10) NULL,
        [OrgUnitId] nchar(8) NULL,
        CONSTRAINT [PK_EmployeeOrgData] PRIMARY KEY ([Id]),
        CONSTRAINT [AK_EmployeeOrgData_EmployeeNo_FromId_ToId] UNIQUE ([EmployeeNo], [FromId], [ToId]),
        CONSTRAINT [FK_EmployeeOrgData_Employees_EmployeeNo] FOREIGN KEY ([EmployeeNo]) REFERENCES [Employees] ([EmployeeNo]) ON DELETE CASCADE
    );
END;
GO

IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20230617155449_InitialMigration'
)
BEGIN
    CREATE TABLE [EmployeePersonalData] (
        [Id] uniqueidentifier NOT NULL,
        [EmployeeNo] nchar(8) NOT NULL,
        [FromId] bigint NOT NULL,
        [ToId] bigint NOT NULL,
        [ValidFrom] datetime2 NOT NULL,
        [ValidTo] datetime2 NOT NULL,
        [FirstName] nvarchar(40) NULL,
        [LastName] nvarchar(40) NULL,
        CONSTRAINT [PK_EmployeePersonalData] PRIMARY KEY ([Id]),
        CONSTRAINT [AK_EmployeePersonalData_EmployeeNo_FromId_ToId] UNIQUE ([EmployeeNo], [FromId], [ToId]),
        CONSTRAINT [FK_EmployeePersonalData_Employees_EmployeeNo] FOREIGN KEY ([EmployeeNo]) REFERENCES [Employees] ([EmployeeNo]) ON DELETE CASCADE
    );
END;
GO

IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20230617155449_InitialMigration'
)
BEGIN
    CREATE TABLE [OrgModelOrgUnitNames] (
        [Id] uniqueidentifier NOT NULL,
        [OrgId] nchar(8) NOT NULL,
        [FromId] bigint NOT NULL,
        [ToId] bigint NOT NULL,
        [ValidFrom] datetime2 NOT NULL,
        [ValidTo] datetime2 NOT NULL,
        [Language] nchar(2) NOT NULL,
        [ShortName] nvarchar(12) NULL,
        [Name] nvarchar(40) NULL,
        CONSTRAINT [PK_OrgModelOrgUnitNames] PRIMARY KEY ([Id]),
        CONSTRAINT [AK_OrgModelOrgUnitNames_OrgId_FromId_ToId_Language] UNIQUE ([OrgId], [FromId], [ToId], [Language]),
        CONSTRAINT [FK_OrgModelOrgUnitNames_OrgModelOrgUnits_OrgId] FOREIGN KEY ([OrgId]) REFERENCES [OrgModelOrgUnits] ([OrgId]) ON DELETE CASCADE
    );
END;
GO

IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20230617155449_InitialMigration'
)
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'20230617155449_InitialMigration', N'8.0.20');
END;
GO

COMMIT;
GO

BEGIN TRANSACTION;
GO

IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20240507050046_EmployeeActions'
)
BEGIN
    CREATE TABLE [EmployeeActionData] (
        [Id] uniqueidentifier NOT NULL,
        [EmployeeNo] nchar(8) NOT NULL,
        [FromId] bigint NOT NULL,
        [ToId] bigint NOT NULL,
        [ValidFrom] datetime2 NOT NULL,
        [ValidTo] datetime2 NOT NULL,
        [Action] nvarchar(2) NULL,
        [Status] nvarchar(1) NOT NULL,
        CONSTRAINT [PK_EmployeeActionData] PRIMARY KEY ([Id]),
        CONSTRAINT [AK_EmployeeActionData_EmployeeNo_FromId_ToId] UNIQUE ([EmployeeNo], [FromId], [ToId]),
        CONSTRAINT [FK_EmployeeActionData_Employees_EmployeeNo] FOREIGN KEY ([EmployeeNo]) REFERENCES [Employees] ([EmployeeNo]) ON DELETE CASCADE
    );
END;
GO

IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20240507050046_EmployeeActions'
)
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'20240507050046_EmployeeActions', N'8.0.20');
END;
GO

COMMIT;
GO

BEGIN TRANSACTION;
GO

IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20251015143238_UpgradeDotnet'
)
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'20251015143238_UpgradeDotnet', N'8.0.20');
END;
GO

COMMIT;
GO

