CROWLER/Services/DatabaseInitializer.cs

using Microsoft.Data.SqlClient;

namespace CRAWLER.Services;

internal sealed class DatabaseInitializer
{
    private readonly IDatabaseConnectionFactory _connectionFactory;

    public DatabaseInitializer(IDatabaseConnectionFactory connectionFactory)
    {
        _connectionFactory = connectionFactory;
    }

    public async Task EnsureCreatedAsync(CancellationToken cancellationToken)
    {
        await EnsureDatabaseExistsAsync(cancellationToken);
        await EnsureSchemaAsync(cancellationToken);
    }

    private async Task EnsureDatabaseExistsAsync(CancellationToken cancellationToken)
    {
        await using var connection = _connectionFactory.CreateMasterConnection();
        await connection.OpenAsync(cancellationToken);

        var safeDatabaseName = _connectionFactory.Options.Database.Replace("]", "]]");
        var sql = $@"
IF DB_ID(N'{safeDatabaseName}') IS NULL
BEGIN
    CREATE DATABASE [{safeDatabaseName}];
END";

        await using var command = new SqlCommand(sql, connection)
        {
            CommandTimeout = _connectionFactory.Options.CommandTimeoutSeconds
        };
        await command.ExecuteNonQueryAsync(cancellationToken);
    }

    private async Task EnsureSchemaAsync(CancellationToken cancellationToken)
    {
        await using var connection = _connectionFactory.CreateConnection();
        await connection.OpenAsync(cancellationToken);

        var scripts = new[]
        {
            @"
IF OBJECT_ID(N'dbo.Instruments', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.Instruments
    (
        Id BIGINT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Instruments PRIMARY KEY,
        RegistryNumber NVARCHAR(64) NULL,
        Name NVARCHAR(512) NOT NULL,
        TypeDesignation NVARCHAR(512) NULL,
        Manufacturer NVARCHAR(2000) NULL,
        VerificationInterval NVARCHAR(512) NULL,
        CertificateOrSerialNumber NVARCHAR(512) NULL,
        AllowsBatchVerification NVARCHAR(256) NULL,
        HasPeriodicVerification NVARCHAR(256) NULL,
        TypeInfo NVARCHAR(256) NULL,
        Purpose NVARCHAR(MAX) NULL,
        Description NVARCHAR(MAX) NULL,
        Software NVARCHAR(MAX) NULL,
        MetrologicalCharacteristics NVARCHAR(MAX) NULL,
        Completeness NVARCHAR(MAX) NULL,
        Verification NVARCHAR(MAX) NULL,
        RegulatoryDocuments NVARCHAR(MAX) NULL,
        Applicant NVARCHAR(MAX) NULL,
        TestCenter NVARCHAR(MAX) NULL,
        DetailUrl NVARCHAR(1024) NULL,
        SourceSystem NVARCHAR(64) NOT NULL CONSTRAINT DF_Instruments_SourceSystem DEFAULT N'Manual',
        LastImportedAt DATETIME2 NULL,
        CreatedAt DATETIME2 NOT NULL CONSTRAINT DF_Instruments_CreatedAt DEFAULT SYSUTCDATETIME(),
        UpdatedAt DATETIME2 NOT NULL CONSTRAINT DF_Instruments_UpdatedAt DEFAULT SYSUTCDATETIME()
    );
END",
            @"
IF NOT EXISTS (
    SELECT 1
    FROM sys.indexes
    WHERE name = N'UX_Instruments_RegistryNumber'
      AND object_id = OBJECT_ID(N'dbo.Instruments')
)
BEGIN
    CREATE UNIQUE INDEX UX_Instruments_RegistryNumber
        ON dbo.Instruments (RegistryNumber)
        WHERE RegistryNumber IS NOT NULL AND RegistryNumber <> N'';
END",
            @"
IF OBJECT_ID(N'dbo.PdfAttachments', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.PdfAttachments
    (
        Id BIGINT IDENTITY(1,1) NOT NULL CONSTRAINT PK_PdfAttachments PRIMARY KEY,
        InstrumentId BIGINT NOT NULL,
        Kind NVARCHAR(128) NOT NULL,
        Title NVARCHAR(256) NULL,
        SourceUrl NVARCHAR(1024) NULL,
        LocalPath NVARCHAR(1024) NULL,
        IsManual BIT NOT NULL CONSTRAINT DF_PdfAttachments_IsManual DEFAULT (0),
        CreatedAt DATETIME2 NOT NULL CONSTRAINT DF_PdfAttachments_CreatedAt DEFAULT SYSUTCDATETIME(),
        CONSTRAINT FK_PdfAttachments_Instruments
            FOREIGN KEY (InstrumentId) REFERENCES dbo.Instruments(Id)
            ON DELETE CASCADE
    );
END",
            @"
IF NOT EXISTS (
    SELECT 1
    FROM sys.indexes
    WHERE name = N'IX_PdfAttachments_InstrumentId'
      AND object_id = OBJECT_ID(N'dbo.PdfAttachments')
)
BEGIN
    CREATE INDEX IX_PdfAttachments_InstrumentId
        ON dbo.PdfAttachments (InstrumentId, CreatedAt DESC);
END",
            @"
IF NOT EXISTS (
    SELECT 1
    FROM sys.indexes
    WHERE name = N'UX_PdfAttachments_InstrumentId_SourceUrl'
      AND object_id = OBJECT_ID(N'dbo.PdfAttachments')
)
BEGIN
    CREATE UNIQUE INDEX UX_PdfAttachments_InstrumentId_SourceUrl
        ON dbo.PdfAttachments (InstrumentId, SourceUrl)
        WHERE SourceUrl IS NOT NULL AND SourceUrl <> N'';
END"
        };

        foreach (var script in scripts)
        {
            await using var command = new SqlCommand(script, connection)
            {
                CommandTimeout = _connectionFactory.Options.CommandTimeoutSeconds
            };
            await command.ExecuteNonQueryAsync(cancellationToken);
        }
    }
}