using System.Data.SQLite;

namespace Screenie;

internal class Database : IDisposable {
    private Plugin Plugin { get; }
    private SQLiteConnection Connection { get; }

    internal Database(Plugin plugin) {
        this.Plugin = plugin;

        var dir = this.Plugin.Interface.GetPluginConfigDirectory();
        Directory.CreateDirectory(dir);
        var dbPath = Path.Join(dir, "database.sqlite");

        this.Connection = new SQLiteConnection($"Data Source={dbPath}");
        this.Connection.Open();
        this.RunMigrations();
    }

    public void Dispose() {
        this.Connection.Close();
        this.Connection.Dispose();
    }

    private void RunMigrations() {
        using var t = this.Connection.BeginTransaction();
        this.Execute("create table if not exists _migrations (id int not null primary key, query text not null)");

        // get latest migration
        long max;
        {
            using var reader = this.Query("select coalesce(max(id), 0) from _migrations");
            if (!reader.Read()) {
                throw new Exception("could not get max migration");
            }

            max = reader.GetFieldValue<long>(0);
        }

        if (max < 1) {
            const string migrationOne = """
                                        create table screenshots (
                                            hash text not null primary key,
                                            path text not null,
                                            active_character jsonb not null,
                                            location text,
                                            location_sub text,
                                            area text,
                                            area_sub text,
                                            territory_type int not null,
                                            world text,
                                            world_id int not null,
                                            captured_at_local timestamp not null,
                                            captured_at_utc timestamp not null,
                                            eorzea_time text not null,
                                            weather text,
                                            ward int,
                                            plot int,
                                            visible_characters jsonb not null,
                                            mods_in_use jsonb not null
                                        )
                                        """;
            this.Execute(migrationOne);
            this.Execute("insert into _migrations (id, query) values (1, $query)", new Dictionary<string, object?> {
                ["$query"] = migrationOne,
            });
        }

        t.Commit();
    }

    private SQLiteCommand GetCommand(string query, Dictionary<string, object?>? parameters = null) {
        var command = this.Connection.CreateCommand();
        command.CommandText = query;
        if (parameters != null) {
            foreach (var (key, value) in parameters) {
                command.Parameters.AddWithValue(key, value);
            }
        }

        return command;
    }

    internal SQLiteDataReader Query(string query, Dictionary<string, object?>? parameters = null) {
        using var command = this.GetCommand(query, parameters);
        return command.ExecuteReader();
    }

    internal int Execute(string query, Dictionary<string, object?>? parameters = null) {
        using var command = this.GetCommand(query, parameters);
        return command.ExecuteNonQuery();
    }
}