using System.Text.Json;
using Microsoft.Data.Sqlite;
using ScadaLink.Commons.Entities.Notifications;
using ScadaLink.Commons.Interfaces.Repositories;
using ScadaLink.SiteRuntime.Persistence;

namespace ScadaLink.SiteRuntime.Repositories;

/// <summary>
/// Site-side read-only implementation of <see cref="INotificationRepository"/>
/// backed by the local SQLite database via <see cref="SiteStorageService"/>.
/// Write operations throw <see cref="NotSupportedException"/> because site-local
/// artifacts are managed exclusively through deployment from Central.
/// </summary>
public class SiteNotificationRepository : INotificationRepository
{
    private readonly SiteStorageService _storage;

    public SiteNotificationRepository(SiteStorageService storage)
    {
        _storage = storage ?? throw new ArgumentNullException(nameof(storage));
    }

    // ── NotificationList (read) ──

    public async Task<NotificationList?> GetListByNameAsync(
        string name, CancellationToken cancellationToken = default)
    {
        await using var connection = CreateConnection();
        await connection.OpenAsync(cancellationToken);

        await using var command = connection.CreateCommand();
        command.CommandText = @"
            SELECT name, recipient_emails
            FROM notification_lists
            WHERE name = @name";
        command.Parameters.AddWithValue("@name", name);

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

        return MapNotificationList(reader);
    }

    public async Task<IReadOnlyList<NotificationList>> GetAllNotificationListsAsync(
        CancellationToken cancellationToken = default)
    {
        await using var connection = CreateConnection();
        await connection.OpenAsync(cancellationToken);

        await using var command = connection.CreateCommand();
        command.CommandText = "SELECT name, recipient_emails FROM notification_lists";

        var results = new List<NotificationList>();
        await using var reader = await command.ExecuteReaderAsync(cancellationToken);
        while (await reader.ReadAsync(cancellationToken))
        {
            results.Add(MapNotificationList(reader));
        }

        return results;
    }

    public async Task<NotificationList?> GetNotificationListByIdAsync(
        int id, CancellationToken cancellationToken = default)
    {
        // The SQLite table is keyed by name, not by integer ID.
        // Scan all rows and match on the synthetic ID derived from the name.
        var all = await GetAllNotificationListsAsync(cancellationToken);
        return all.FirstOrDefault(l => l.Id == id);
    }

    // ── NotificationRecipient (read) ──

    public async Task<IReadOnlyList<NotificationRecipient>> GetRecipientsByListIdAsync(
        int notificationListId, CancellationToken cancellationToken = default)
    {
        // Find the parent list to get its name, then parse the recipient_emails JSON.
        var list = await GetNotificationListByIdAsync(notificationListId, cancellationToken);
        if (list is null)
            return Array.Empty<NotificationRecipient>();

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

        await using var command = connection.CreateCommand();
        command.CommandText = @"
            SELECT recipient_emails
            FROM notification_lists
            WHERE name = @name";
        command.Parameters.AddWithValue("@name", list.Name);

        var json = (string?)await command.ExecuteScalarAsync(cancellationToken);
        if (string.IsNullOrWhiteSpace(json))
            return Array.Empty<NotificationRecipient>();

        return ParseRecipientEmails(json, notificationListId);
    }

    public async Task<NotificationRecipient?> GetRecipientByIdAsync(
        int id, CancellationToken cancellationToken = default)
    {
        // Scan all lists and their recipients to find the matching synthetic ID.
        var lists = await GetAllNotificationListsAsync(cancellationToken);
        foreach (var list in lists)
        {
            var recipients = await GetRecipientsByListIdAsync(list.Id, cancellationToken);
            var match = recipients.FirstOrDefault(r => r.Id == id);
            if (match is not null)
                return match;
        }

        return null;
    }

    // ── SmtpConfiguration (read) ──

    public async Task<IReadOnlyList<SmtpConfiguration>> GetAllSmtpConfigurationsAsync(
        CancellationToken cancellationToken = default)
    {
        await using var connection = CreateConnection();
        await connection.OpenAsync(cancellationToken);

        await using var command = connection.CreateCommand();
        command.CommandText = @"
            SELECT name, server, port, auth_mode, from_address, username, password, oauth_config
            FROM smtp_configurations";

        var results = new List<SmtpConfiguration>();
        await using var reader = await command.ExecuteReaderAsync(cancellationToken);
        while (await reader.ReadAsync(cancellationToken))
        {
            results.Add(MapSmtpConfiguration(reader));
        }

        return results;
    }

    public async Task<SmtpConfiguration?> GetSmtpConfigurationByIdAsync(
        int id, CancellationToken cancellationToken = default)
    {
        var all = await GetAllSmtpConfigurationsAsync(cancellationToken);
        return all.FirstOrDefault(s => s.Id == id);
    }

    // ── Write operations (not supported on site) ──

    public Task AddNotificationListAsync(NotificationList list, CancellationToken cancellationToken = default)
        => throw new NotSupportedException("Managed via artifact deployment from Central");

    public Task UpdateNotificationListAsync(NotificationList list, CancellationToken cancellationToken = default)
        => throw new NotSupportedException("Managed via artifact deployment from Central");

    public Task DeleteNotificationListAsync(int id, CancellationToken cancellationToken = default)
        => throw new NotSupportedException("Managed via artifact deployment from Central");

    public Task AddRecipientAsync(NotificationRecipient recipient, CancellationToken cancellationToken = default)
        => throw new NotSupportedException("Managed via artifact deployment from Central");

    public Task UpdateRecipientAsync(NotificationRecipient recipient, CancellationToken cancellationToken = default)
        => throw new NotSupportedException("Managed via artifact deployment from Central");

    public Task DeleteRecipientAsync(int id, CancellationToken cancellationToken = default)
        => throw new NotSupportedException("Managed via artifact deployment from Central");

    public Task AddSmtpConfigurationAsync(SmtpConfiguration configuration, CancellationToken cancellationToken = default)
        => throw new NotSupportedException("Managed via artifact deployment from Central");

    public Task UpdateSmtpConfigurationAsync(SmtpConfiguration configuration, CancellationToken cancellationToken = default)
        => throw new NotSupportedException("Managed via artifact deployment from Central");

    public Task DeleteSmtpConfigurationAsync(int id, CancellationToken cancellationToken = default)
        => throw new NotSupportedException("Managed via artifact deployment from Central");

    public Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
        => throw new NotSupportedException("Managed via artifact deployment from Central");

    // ── Private helpers ──

    /// <summary>
    /// Creates a new SQLite connection against the site database via
    /// <see cref="SiteStorageService.CreateConnection"/> (SiteRuntime-006) instead of
    /// reaching into its private connection-string field via reflection.
    /// </summary>
    private SqliteConnection CreateConnection() => _storage.CreateConnection();

    private static NotificationList MapNotificationList(SqliteDataReader reader)
    {
        var name = reader.GetString(0);
        var list = new NotificationList(name)
        {
            Id = GenerateSyntheticId(name)
        };

        // Eagerly populate Recipients from the JSON column.
        var emailsJson = reader.GetString(1);
        var recipients = ParseRecipientEmails(emailsJson, list.Id);
        foreach (var r in recipients)
        {
            list.Recipients.Add(r);
        }

        return list;
    }

    private static IReadOnlyList<NotificationRecipient> ParseRecipientEmails(
        string json, int notificationListId)
    {
        try
        {
            var emails = JsonSerializer.Deserialize<List<string>>(json,
                new JsonSerializerOptions { PropertyNameCaseInsensitive = true });

            if (emails is null)
                return Array.Empty<NotificationRecipient>();

            return emails.Select(email => new NotificationRecipient(
                name: email,
                emailAddress: email)
            {
                Id = GenerateSyntheticId($"{notificationListId}:{email}"),
                NotificationListId = notificationListId
            }).ToList();
        }
        catch (JsonException)
        {
            return Array.Empty<NotificationRecipient>();
        }
    }

    private static SmtpConfiguration MapSmtpConfiguration(SqliteDataReader reader)
    {
        var name = reader.GetString(0);
        return new SmtpConfiguration(
            host: reader.GetString(1),
            authType: reader.GetString(3),
            fromAddress: reader.GetString(4))
        {
            Id = GenerateSyntheticId(name),
            Port = reader.GetInt32(2),
            Credentials = reader.IsDBNull(5) ? null : reader.GetString(5),
            TlsMode = reader.IsDBNull(7) ? null : reader.GetString(7)
        };
    }

    /// <summary>
    /// Generates a stable positive integer ID from a string name (SiteRuntime-007).
    /// Uses a deterministic FNV-1a hash rather than <see cref="string.GetHashCode()"/>,
    /// which is randomized per process on .NET Core and would change every restart.
    /// </summary>
    private static int GenerateSyntheticId(string name) => SyntheticId.From(name);
}