GroupStats

GroupStats.csproj

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net8.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
    <PublishAot>true</PublishAot>
    <InvariantGlobalization>true</InvariantGlobalization>
    <LangVersion>preview</LangVersion>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Spectre.Console" Version="0.47.0" />
    <PackageReference Include="Spectre.Console.Analyzer" Version="0.47.0">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="2.1.6" Condition=" '$(OS)' == 'UNIX' " />
    <PackageReference Include="SQLitePCLRaw.bundle_winsqlite3" Version="2.1.6" Condition=" '$(OS)' == 'Windows_NT'" />
    <PackageReference Include="SQLitePCLRaw.ugly" Version="2.1.6" />
  </ItemGroup>

  <ItemGroup>
    <None Update="result.json">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
  </ItemGroup>

</Project>

Program.cs

using Spectre.Console;
using SQLitePCL;
using System.Text.Json;
using System.Text.Json.Serialization;
using static SQLitePCL.raw;
using static SQLitePCL.Ugly.ugly;

Batteries_V2.Init();

var fileName = args switch
{
    { Length: 0 } => "result.json",
    [string n] => n,
    [string n, _] => n,
    _ => null
};

if (fileName is null || !File.Exists(fileName))
{
    AnsiConsole.MarkupLine("[red]File does not exist[/]");
    return;
}

using var stream = File.OpenRead(fileName);
var chat = await JsonSerializer.DeserializeAsync(stream, ChatJsonContext.Default.ChatExport);

if (chat is null)
{
    AnsiConsole.MarkupLine("[red]Could not parse chat export[/]");
    return;
}

AnsiConsole.MarkupLine($"[b]Chat name:[/] {chat.Name}");
var dbName = Path.GetFileNameWithoutExtension(fileName) + ".db";
AnsiConsole.WriteLine($"Preparing DB ({dbName})");
using var db = open_v2(dbName, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, null);
db.CreateTables();
AnsiConsole.MarkupLine($"[green]DB Ready[/]");

db.exec("BEGIN TRANSACTION");
using var statement = db.PrepareInsertion();

AnsiConsole.Progress()
    .AutoClear(false)
    .HideCompleted(false)
    .Columns(new ProgressColumn[]
    {
        new TaskDescriptionColumn(),
        new ProgressBarColumn(),
        new PercentageColumn()
    })
    .Start(ctx =>
    {
        var msgTask = ctx.AddTask("[green]Inserting messages[/]", autoStart: true, maxValue: chat.Messages.Count);
        
        foreach (var msg in chat.Messages)
        {
            msgTask.Increment(1);

            if (msg.Type != "message")
            {
                continue;
            }

            statement.Insert(msg);
        }
    });

db.exec("COMMIT TRANSACTION");

AnsiConsole.MarkupLine("[green]Completed[/]");

public record ChatExport(string Name, string Type, long Id, List<ChatMessage> Messages);
public record ChatMessage(long Id, string Type, string? From, string? FromId, string? MediaType);
[JsonSourceGenerationOptions(PropertyNamingPolicy = JsonKnownNamingPolicy.SnakeCaseLower)]
[JsonSerializable(typeof(ChatExport))]
[JsonSerializable(typeof(ChatMessage))]
internal partial class ChatJsonContext : JsonSerializerContext
{
}

internal static class Database
{
    public static void CreateTables(this sqlite3 db)
    {
#if DEBUG
        db.exec("DROP TABLE IF EXISTS Stats");
#endif

        db.exec("""
            CREATE TABLE IF NOT EXISTS Stats (
                msg_id INTEGER NOT NULL,
                from_user TEXT NOT NULL,
                from_id TEXT NOT NULL,
                media_type TEXT NOT NULL
            );
            """);
    }

    public static sqlite3_stmt PrepareInsertion(this sqlite3 db) => db.prepare("""
            INSERT INTO Stats (
                msg_id, from_user, from_id, media_type
            ) VALUES (
                ?, ?, ?, ?
            )
            """);

    public static void Insert(this sqlite3_stmt stmt, ChatMessage message)
    {
        ArgumentNullException.ThrowIfNull(message.FromId, nameof(message.FromId));

        stmt.reset();
        stmt.bind_int64(1, message.Id);
        stmt.bind_text(2, message.From ?? "Deleted Account");
        stmt.bind_text(3, message.FromId);
        stmt.bind_text(4, message.MediaType ?? "text");

        stmt.step_done();
    }
}