using CRAWLER.Models;
using Microsoft.Data.SqlClient;

namespace CRAWLER.Services;

internal sealed class InstrumentRepository
{
    private readonly IDatabaseConnectionFactory _connectionFactory;

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

    public async Task<IReadOnlyList<InstrumentSummary>> SearchAsync(string searchText, CancellationToken cancellationToken)
    {
        var items = new List<InstrumentSummary>();
        var hasFilter = !string.IsNullOrWhiteSpace(searchText);

        const string sql = @"
SELECT TOP (500)
    Id,
    RegistryNumber,
    Name,
    TypeDesignation,
    Manufacturer,
    VerificationInterval,
    SourceSystem,
    UpdatedAt
FROM dbo.Instruments
WHERE @Search IS NULL
   OR RegistryNumber LIKE @Like
   OR Name LIKE @Like
   OR TypeDesignation LIKE @Like
   OR Manufacturer LIKE @Like
ORDER BY
    CASE WHEN RegistryNumber IS NULL OR RegistryNumber = N'' THEN 1 ELSE 0 END,
    RegistryNumber DESC,
    UpdatedAt DESC;";

        await using var connection = _connectionFactory.CreateConnection();
        await connection.OpenAsync(cancellationToken);

        await using var command = new SqlCommand(sql, connection)
        {
            CommandTimeout = _connectionFactory.Options.CommandTimeoutSeconds
        };
        command.Parameters.AddWithValue("@Search", hasFilter ? searchText.Trim() : DBNull.Value);
        command.Parameters.AddWithValue("@Like", hasFilter ? $"%{searchText.Trim()}%" : DBNull.Value);

        await using var reader = await command.ExecuteReaderAsync(cancellationToken);
        while (await reader.ReadAsync(cancellationToken))
        {
            items.Add(new InstrumentSummary
            {
                Id = reader.GetInt64(0),
                RegistryNumber = GetString(reader, 1),
                Name = GetString(reader, 2),
                TypeDesignation = GetString(reader, 3),
                Manufacturer = GetString(reader, 4),
                VerificationInterval = GetString(reader, 5),
                SourceSystem = GetString(reader, 6),
                UpdatedAt = reader.GetDateTime(7)
            });
        }

        return items;
    }

    public async Task<InstrumentRecord> GetByIdAsync(long id, CancellationToken cancellationToken)
    {
        const string sql = @"
SELECT
    Id,
    RegistryNumber,
    Name,
    TypeDesignation,
    Manufacturer,
    VerificationInterval,
    CertificateOrSerialNumber,
    AllowsBatchVerification,
    HasPeriodicVerification,
    TypeInfo,
    Purpose,
    Description,
    Software,
    MetrologicalCharacteristics,
    Completeness,
    Verification,
    RegulatoryDocuments,
    Applicant,
    TestCenter,
    DetailUrl,
    SourceSystem,
    LastImportedAt,
    CreatedAt,
    UpdatedAt
FROM dbo.Instruments
WHERE Id = @Id;";

        await using var connection = _connectionFactory.CreateConnection();
        await connection.OpenAsync(cancellationToken);

        await using var command = new SqlCommand(sql, connection)
        {
            CommandTimeout = _connectionFactory.Options.CommandTimeoutSeconds
        };
        command.Parameters.AddWithValue("@Id", id);

        InstrumentRecord item = null;
        await using (var reader = await command.ExecuteReaderAsync(cancellationToken))
        {
            if (await reader.ReadAsync(cancellationToken))
            {
                item = new InstrumentRecord
                {
                    Id = reader.GetInt64(0),
                    RegistryNumber = GetString(reader, 1),
                    Name = GetString(reader, 2),
                    TypeDesignation = GetString(reader, 3),
                    Manufacturer = GetString(reader, 4),
                    VerificationInterval = GetString(reader, 5),
                    CertificateOrSerialNumber = GetString(reader, 6),
                    AllowsBatchVerification = GetString(reader, 7),
                    HasPeriodicVerification = GetString(reader, 8),
                    TypeInfo = GetString(reader, 9),
                    Purpose = GetString(reader, 10),
                    Description = GetString(reader, 11),
                    Software = GetString(reader, 12),
                    MetrologicalCharacteristics = GetString(reader, 13),
                    Completeness = GetString(reader, 14),
                    Verification = GetString(reader, 15),
                    RegulatoryDocuments = GetString(reader, 16),
                    Applicant = GetString(reader, 17),
                    TestCenter = GetString(reader, 18),
                    DetailUrl = GetString(reader, 19),
                    SourceSystem = GetString(reader, 20),
                    LastImportedAt = reader.IsDBNull(21) ? (DateTime?)null : reader.GetDateTime(21),
                    CreatedAt = reader.GetDateTime(22),
                    UpdatedAt = reader.GetDateTime(23)
                };
            }
        }

        if (item == null)
        {
            return null;
        }

        item.Attachments = (await GetAttachmentsAsync(connection, id, cancellationToken)).ToList();
        return item;
    }

    public async Task<long?> FindInstrumentIdByRegistryNumberAsync(string registryNumber, CancellationToken cancellationToken)
    {
        if (string.IsNullOrWhiteSpace(registryNumber))
        {
            return null;
        }

        const string sql = "SELECT Id FROM dbo.Instruments WHERE RegistryNumber = @RegistryNumber;";

        await using var connection = _connectionFactory.CreateConnection();
        await connection.OpenAsync(cancellationToken);

        await using var command = new SqlCommand(sql, connection)
        {
            CommandTimeout = _connectionFactory.Options.CommandTimeoutSeconds
        };
        command.Parameters.AddWithValue("@RegistryNumber", registryNumber.Trim());

        var result = await command.ExecuteScalarAsync(cancellationToken);
        if (result == null || result == DBNull.Value)
        {
            return null;
        }

        return Convert.ToInt64(result);
    }

    public async Task<long> SaveAsync(InstrumentRecord record, CancellationToken cancellationToken)
    {
        if (record == null)
        {
            throw new ArgumentNullException(nameof(record));
        }

        await using var connection = _connectionFactory.CreateConnection();
        await connection.OpenAsync(cancellationToken);

        if (record.Id <= 0)
        {
            const string insertSql = @"
INSERT INTO dbo.Instruments
(
    RegistryNumber,
    Name,
    TypeDesignation,
    Manufacturer,
    VerificationInterval,
    CertificateOrSerialNumber,
    AllowsBatchVerification,
    HasPeriodicVerification,
    TypeInfo,
    Purpose,
    Description,
    Software,
    MetrologicalCharacteristics,
    Completeness,
    Verification,
    RegulatoryDocuments,
    Applicant,
    TestCenter,
    DetailUrl,
    SourceSystem,
    LastImportedAt,
    CreatedAt,
    UpdatedAt
)
OUTPUT INSERTED.Id
VALUES
(
    @RegistryNumber,
    @Name,
    @TypeDesignation,
    @Manufacturer,
    @VerificationInterval,
    @CertificateOrSerialNumber,
    @AllowsBatchVerification,
    @HasPeriodicVerification,
    @TypeInfo,
    @Purpose,
    @Description,
    @Software,
    @MetrologicalCharacteristics,
    @Completeness,
    @Verification,
    @RegulatoryDocuments,
    @Applicant,
    @TestCenter,
    @DetailUrl,
    @SourceSystem,
    @LastImportedAt,
    SYSUTCDATETIME(),
    SYSUTCDATETIME()
);";

            await using var command = CreateRecordCommand(insertSql, connection, record);
            var id = await command.ExecuteScalarAsync(cancellationToken);
            return Convert.ToInt64(id);
        }

        const string updateSql = @"
UPDATE dbo.Instruments
SET
    RegistryNumber = @RegistryNumber,
    Name = @Name,
    TypeDesignation = @TypeDesignation,
    Manufacturer = @Manufacturer,
    VerificationInterval = @VerificationInterval,
    CertificateOrSerialNumber = @CertificateOrSerialNumber,
    AllowsBatchVerification = @AllowsBatchVerification,
    HasPeriodicVerification = @HasPeriodicVerification,
    TypeInfo = @TypeInfo,
    Purpose = @Purpose,
    Description = @Description,
    Software = @Software,
    MetrologicalCharacteristics = @MetrologicalCharacteristics,
    Completeness = @Completeness,
    Verification = @Verification,
    RegulatoryDocuments = @RegulatoryDocuments,
    Applicant = @Applicant,
    TestCenter = @TestCenter,
    DetailUrl = @DetailUrl,
    SourceSystem = @SourceSystem,
    LastImportedAt = @LastImportedAt,
    UpdatedAt = SYSUTCDATETIME()
WHERE Id = @Id;";

        await using (var command = CreateRecordCommand(updateSql, connection, record))
        {
            command.Parameters.AddWithValue("@Id", record.Id);
            await command.ExecuteNonQueryAsync(cancellationToken);
        }

        return record.Id;
    }

    public async Task<PdfAttachment> FindAttachmentBySourceUrlAsync(long instrumentId, string sourceUrl, CancellationToken cancellationToken)
    {
        if (string.IsNullOrWhiteSpace(sourceUrl))
        {
            return null;
        }

        const string sql = @"
SELECT
    Id,
    InstrumentId,
    Kind,
    Title,
    SourceUrl,
    LocalPath,
    IsManual,
    CreatedAt
FROM dbo.PdfAttachments
WHERE InstrumentId = @InstrumentId
  AND SourceUrl = @SourceUrl;";

        await using var connection = _connectionFactory.CreateConnection();
        await connection.OpenAsync(cancellationToken);

        await using var command = new SqlCommand(sql, connection)
        {
            CommandTimeout = _connectionFactory.Options.CommandTimeoutSeconds
        };
        command.Parameters.AddWithValue("@InstrumentId", instrumentId);
        command.Parameters.AddWithValue("@SourceUrl", sourceUrl);

        await using var reader = await command.ExecuteReaderAsync(cancellationToken);
        if (!await reader.ReadAsync(cancellationToken))
        {
            return null;
        }

        return new PdfAttachment
        {
            Id = reader.GetInt64(0),
            InstrumentId = reader.GetInt64(1),
            Kind = GetString(reader, 2),
            Title = GetString(reader, 3),
            SourceUrl = GetString(reader, 4),
            LocalPath = GetString(reader, 5),
            IsManual = reader.GetBoolean(6),
            CreatedAt = reader.GetDateTime(7)
        };
    }

    public async Task SaveAttachmentAsync(PdfAttachment attachment, CancellationToken cancellationToken)
    {
        if (attachment == null)
        {
            throw new ArgumentNullException(nameof(attachment));
        }

        await using var connection = _connectionFactory.CreateConnection();
        await connection.OpenAsync(cancellationToken);

        if (attachment.Id <= 0)
        {
            const string insertSql = @"
INSERT INTO dbo.PdfAttachments
(
    InstrumentId,
    Kind,
    Title,
    SourceUrl,
    LocalPath,
    IsManual,
    CreatedAt
)
VALUES
(
    @InstrumentId,
    @Kind,
    @Title,
    @SourceUrl,
    @LocalPath,
    @IsManual,
    SYSUTCDATETIME()
);";

            await using var command = CreateAttachmentCommand(insertSql, connection, attachment);
            await command.ExecuteNonQueryAsync(cancellationToken);
            return;
        }

        const string updateSql = @"
UPDATE dbo.PdfAttachments
SET
    Kind = @Kind,
    Title = @Title,
    SourceUrl = @SourceUrl,
    LocalPath = @LocalPath,
    IsManual = @IsManual
WHERE Id = @Id;";

        await using (var command = CreateAttachmentCommand(updateSql, connection, attachment))
        {
            command.Parameters.AddWithValue("@Id", attachment.Id);
            await command.ExecuteNonQueryAsync(cancellationToken);
        }
    }

    public async Task DeleteAttachmentAsync(long attachmentId, CancellationToken cancellationToken)
    {
        const string sql = "DELETE FROM dbo.PdfAttachments WHERE Id = @Id;";

        await using var connection = _connectionFactory.CreateConnection();
        await connection.OpenAsync(cancellationToken);

        await using var command = new SqlCommand(sql, connection)
        {
            CommandTimeout = _connectionFactory.Options.CommandTimeoutSeconds
        };
        command.Parameters.AddWithValue("@Id", attachmentId);
        await command.ExecuteNonQueryAsync(cancellationToken);
    }

    public async Task DeleteInstrumentAsync(long id, CancellationToken cancellationToken)
    {
        const string sql = "DELETE FROM dbo.Instruments WHERE Id = @Id;";

        await using var connection = _connectionFactory.CreateConnection();
        await connection.OpenAsync(cancellationToken);

        await using var command = new SqlCommand(sql, connection)
        {
            CommandTimeout = _connectionFactory.Options.CommandTimeoutSeconds
        };
        command.Parameters.AddWithValue("@Id", id);
        await command.ExecuteNonQueryAsync(cancellationToken);
    }

    private async Task<IReadOnlyList<PdfAttachment>> GetAttachmentsAsync(SqlConnection connection, long instrumentId, CancellationToken cancellationToken)
    {
        const string sql = @"
SELECT
    Id,
    InstrumentId,
    Kind,
    Title,
    SourceUrl,
    LocalPath,
    IsManual,
    CreatedAt
FROM dbo.PdfAttachments
WHERE InstrumentId = @InstrumentId
ORDER BY CreatedAt DESC, Id DESC;";

        var items = new List<PdfAttachment>();

        await using var command = new SqlCommand(sql, connection)
        {
            CommandTimeout = _connectionFactory.Options.CommandTimeoutSeconds
        };
        command.Parameters.AddWithValue("@InstrumentId", instrumentId);

        await using var reader = await command.ExecuteReaderAsync(cancellationToken);
        while (await reader.ReadAsync(cancellationToken))
        {
            items.Add(new PdfAttachment
            {
                Id = reader.GetInt64(0),
                InstrumentId = reader.GetInt64(1),
                Kind = GetString(reader, 2),
                Title = GetString(reader, 3),
                SourceUrl = GetString(reader, 4),
                LocalPath = GetString(reader, 5),
                IsManual = reader.GetBoolean(6),
                CreatedAt = reader.GetDateTime(7)
            });
        }

        return items;
    }

    private SqlCommand CreateRecordCommand(string sql, SqlConnection connection, InstrumentRecord record)
    {
        var command = new SqlCommand(sql, connection)
        {
            CommandTimeout = _connectionFactory.Options.CommandTimeoutSeconds
        };

        command.Parameters.AddWithValue("@RegistryNumber", ToDbValue(record.RegistryNumber));
        command.Parameters.AddWithValue("@Name", string.IsNullOrWhiteSpace(record.Name) ? "Без названия" : record.Name.Trim());
        command.Parameters.AddWithValue("@TypeDesignation", ToDbValue(record.TypeDesignation));
        command.Parameters.AddWithValue("@Manufacturer", ToDbValue(record.Manufacturer));
        command.Parameters.AddWithValue("@VerificationInterval", ToDbValue(record.VerificationInterval));
        command.Parameters.AddWithValue("@CertificateOrSerialNumber", ToDbValue(record.CertificateOrSerialNumber));
        command.Parameters.AddWithValue("@AllowsBatchVerification", ToDbValue(record.AllowsBatchVerification));
        command.Parameters.AddWithValue("@HasPeriodicVerification", ToDbValue(record.HasPeriodicVerification));
        command.Parameters.AddWithValue("@TypeInfo", ToDbValue(record.TypeInfo));
        command.Parameters.AddWithValue("@Purpose", ToDbValue(record.Purpose));
        command.Parameters.AddWithValue("@Description", ToDbValue(record.Description));
        command.Parameters.AddWithValue("@Software", ToDbValue(record.Software));
        command.Parameters.AddWithValue("@MetrologicalCharacteristics", ToDbValue(record.MetrologicalCharacteristics));
        command.Parameters.AddWithValue("@Completeness", ToDbValue(record.Completeness));
        command.Parameters.AddWithValue("@Verification", ToDbValue(record.Verification));
        command.Parameters.AddWithValue("@RegulatoryDocuments", ToDbValue(record.RegulatoryDocuments));
        command.Parameters.AddWithValue("@Applicant", ToDbValue(record.Applicant));
        command.Parameters.AddWithValue("@TestCenter", ToDbValue(record.TestCenter));
        command.Parameters.AddWithValue("@DetailUrl", ToDbValue(record.DetailUrl));
        command.Parameters.AddWithValue("@SourceSystem", string.IsNullOrWhiteSpace(record.SourceSystem) ? "Manual" : record.SourceSystem.Trim());
        command.Parameters.AddWithValue("@LastImportedAt", record.LastImportedAt.HasValue ? record.LastImportedAt.Value : DBNull.Value);

        return command;
    }

    private SqlCommand CreateAttachmentCommand(string sql, SqlConnection connection, PdfAttachment attachment)
    {
        var command = new SqlCommand(sql, connection)
        {
            CommandTimeout = _connectionFactory.Options.CommandTimeoutSeconds
        };

        command.Parameters.AddWithValue("@InstrumentId", attachment.InstrumentId);
        command.Parameters.AddWithValue("@Kind", string.IsNullOrWhiteSpace(attachment.Kind) ? "PDF" : attachment.Kind.Trim());
        command.Parameters.AddWithValue("@Title", ToDbValue(attachment.Title));
        command.Parameters.AddWithValue("@SourceUrl", ToDbValue(attachment.SourceUrl));
        command.Parameters.AddWithValue("@LocalPath", ToDbValue(attachment.LocalPath));
        command.Parameters.AddWithValue("@IsManual", attachment.IsManual);

        return command;
    }

    private static object ToDbValue(string value)
    {
        return string.IsNullOrWhiteSpace(value) ? DBNull.Value : value.Trim();
    }

    private static string GetString(SqlDataReader reader, int index)
    {
        return reader.IsDBNull(index) ? null : reader.GetString(index);
    }
}