using Microsoft.Extensions.Logging;
using ZLogger;

namespace SharpChat.SQLite;

public class SQLiteMigrations(ILogger logger, SQLiteConnection conn) {
    public async Task RunMigrations() {
        long currentVersion = await conn.RunQueryValue<long>("PRAGMA user_version");
        long version = currentVersion;

        async Task doMigration(int expect, Func<Task> action) {
            if(version < expect) {
                logger.ZLogInformation($"Upgrading to version {version + 1}...");
                await action();
                ++version;
            }
        };

        await doMigration(1, CreateMessagesTable);

        if(currentVersion != version)
            await conn.RunCommand($"PRAGMA user_version = {version}");
    }

    private async Task CreateMessagesTable() {
        await conn.RunCommand(
            @"CREATE TABLE ""messages"" ("
            + @"""msg_id"" INTEGER NOT NULL,"
            + @"""msg_type"" TEXT NOT NULL COLLATE NOCASE,"
            + @"""msg_created"" TEXT NOT NULL COLLATE NOCASE,"
            + @"""msg_deleted"" TEXT DEFAULT NULL COLLATE NOCASE,"
            + @"""msg_channel"" TEXT DEFAULT NULL COLLATE NOCASE,"
            + @"""msg_sender"" TEXT DEFAULT NULL COLLATE BINARY,"
            + @"""msg_sender_name"" TEXT DEFAULT NULL COLLATE NOCASE,"
            + @"""msg_sender_colour"" INTEGER DEFAULT NULL,"
            + @"""msg_sender_rank"" INTEGER DEFAULT NULL,"
            + @"""msg_sender_nick"" TEXT DEFAULT NULL COLLATE NOCASE,"
            + @"""msg_sender_perms"" INTEGER DEFAULT NULL,"
            + @"""msg_data"" BLOB DEFAULT NULL CHECK(JSON_VALID(""msg_data"") AND JSON_TYPE(""msg_data"") = ""object"") COLLATE BINARY,"
            + @"PRIMARY KEY(""msg_id"")"
            + @");"
        );
        await conn.RunCommand(@"CREATE INDEX ""messages_channel_index"" ON ""messages"" (""msg_channel"");");
        await conn.RunCommand(@"CREATE INDEX ""messages_created_index"" ON ""messages"" (""msg_created"");");
        await conn.RunCommand(@"CREATE INDEX ""messages_deleted_index"" ON ""messages"" (""msg_deleted"");");
        await conn.RunCommand(@"CREATE INDEX ""messages_event_type"" ON ""messages"" (""msg_type"");");
        await conn.RunCommand(@"CREATE INDEX ""messages_sender_index"" ON ""messages"" (""msg_sender"");");
    }
}