journal/Journal.Core/Repositories/SqliteEntryFileRepository.cs

using Journal.Core.Services.Database;
using Journal.Core.Services.Entries;

namespace Journal.Core.Repositories;

public sealed class SqliteEntryFileRepository(IDatabaseSessionService session) : IEntryFileRepository
{
    private const string EntryPrefix = "db://entry/";
    private const string TemplatePrefix = "db://template/";
    private readonly IDatabaseSessionService _session = session;

    public IReadOnlyList<string> ListMarkdownFiles()
    {
        var conn = _session.GetConnection();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = """
            SELECT file_name
            FROM entry_documents
            ORDER BY file_name;
            """;

        var paths = new List<string>();
        using var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            if (reader.IsDBNull(0))
                continue;

            var fileName = reader.GetString(0);
            paths.Add(ToCanonicalPath(fileName));
        }

        return paths;
    }

    public string ReadFile(string filePath)
    {
        var fileName = ResolveFileName(filePath);
        var conn = _session.GetConnection();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = """
            SELECT content
            FROM entry_documents
            WHERE file_name = @fileName;
            """;
        cmd.Parameters.AddWithValue("@fileName", fileName);
        var result = cmd.ExecuteScalar();
        if (result is null || result is DBNull)
            throw new FileNotFoundException($"Entry file not found: {fileName}");
        return Convert.ToString(result) ?? "";
    }

    public void WriteFile(string filePath, string content)
    {
        var fileName = ResolveFileName(filePath);
        var isTemplate = EntryFileNaming.IsTemplateFileName(fileName) ? 1 : 0;
        var conn = _session.GetConnection();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = """
            INSERT INTO entry_documents (guid, file_name, content, is_template, updated_at)
            VALUES (@guid, @fileName, @content, @isTemplate, @updatedAt)
            ON CONFLICT(file_name) DO UPDATE SET
                content = excluded.content,
                is_template = excluded.is_template,
                updated_at = excluded.updated_at;
            """;
        cmd.Parameters.AddWithValue("@guid", Guid.NewGuid().ToString("D"));
        cmd.Parameters.AddWithValue("@fileName", fileName);
        cmd.Parameters.AddWithValue("@content", content ?? "");
        cmd.Parameters.AddWithValue("@isTemplate", isTemplate);
        cmd.Parameters.AddWithValue("@updatedAt", DateTimeOffset.UtcNow.ToString("O"));
        cmd.ExecuteNonQuery();
    }

    public void AppendFile(string filePath, string content)
    {
        var fileName = ResolveFileName(filePath);
        var existing = FileExists(fileName) ? ReadFile(fileName) : "";
        WriteFile(fileName, existing + content);
    }

    public bool FileExists(string filePath)
    {
        var fileName = ResolveFileName(filePath);
        var conn = _session.GetConnection();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = """
            SELECT 1
            FROM entry_documents
            WHERE file_name = @fileName
            LIMIT 1;
            """;
        cmd.Parameters.AddWithValue("@fileName", fileName);
        return cmd.ExecuteScalar() is not null;
    }

    public string GetFullPath(string filePath)
    {
        var fileName = ResolveFileName(filePath);
        return ToCanonicalPath(fileName);
    }

    public string GetFileName(string filePath) => ResolveFileName(filePath);

    public string GetFileNameWithoutExtension(string filePath)
        => Path.GetFileNameWithoutExtension(ResolveFileName(filePath));

    public void EnsureDirectory(string path) { }

    public void DeleteFile(string filePath)
    {
        var fileName = ResolveFileName(filePath);
        var conn = _session.GetConnection();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = "DELETE FROM entry_documents WHERE file_name = @fileName;";
        cmd.Parameters.AddWithValue("@fileName", fileName);
        cmd.ExecuteNonQuery();
    }

    private static string ResolveFileName(string input)
    {
        if (string.IsNullOrWhiteSpace(input))
            return "";

        if (input.StartsWith(EntryPrefix, StringComparison.OrdinalIgnoreCase))
            return Uri.UnescapeDataString(input[EntryPrefix.Length..]);
        if (input.StartsWith(TemplatePrefix, StringComparison.OrdinalIgnoreCase))
            return Uri.UnescapeDataString(input[TemplatePrefix.Length..]);

        var fileName = Path.GetFileName(input);
        return fileName ?? input.Trim();
    }

    private static string ToCanonicalPath(string fileName)
    {
        var prefix = EntryFileNaming.IsTemplateFileName(fileName) ? TemplatePrefix : EntryPrefix;
        return prefix + Uri.EscapeDataString(fileName);
    }
}