fediglam/src/main/migrations.zig

const std = @import("std");
const sql = @import("sql");
const util = @import("util");
const DateTime = util.DateTime;

pub const Migration = struct {
    name: [:0]const u8,
    up: []const u8,
    down: []const u8,
};

fn execStmt(tx: anytype, stmt: []const u8, alloc: std.mem.Allocator) !void {
    const stmt_null = try std.cstr.addNullByte(alloc, stmt);
    defer alloc.free(stmt_null);
    try tx.exec(stmt_null, {}, null);
}

fn execScript(db: anytype, script: []const u8, alloc: std.mem.Allocator) !void {
    const tx = try db.beginOrSavepoint();
    errdefer tx.rollback();

    var iter = std.mem.split(u8, script, ";");
    while (iter.next()) |stmt| {
        if (stmt.len == 0) continue;
        try execStmt(tx, stmt, alloc);
    }

    try tx.commitOrRelease();
}

fn wasMigrationRan(db: anytype, name: []const u8, alloc: std.mem.Allocator) !bool {
    return if (db.queryRow(
        std.meta.Tuple(&.{i32}),
        "SELECT COUNT(*) FROM migration WHERE name = $1 LIMIT 1",
        .{name},
        alloc,
    )) |row| row[0] != 0 else |err| switch (err) {
        error.NoRows => false,
        else => error.DatabaseFailure,
    };
}

pub fn up(db: anytype) !void {
    var gpa = std.heap.GeneralPurposeAllocator(.{}){};
    defer _ = gpa.deinit();
    std.log.info("Running migrations...", .{});
    try execScript(db, create_migration_table, gpa.allocator());

    for (migrations) |migration| {
        const tx = try db.begin();
        errdefer tx.rollback();
        const was_ran = try wasMigrationRan(tx, migration.name, gpa.allocator());
        if (!was_ran) {
            std.log.info("Running migration {s}", .{migration.name});
            try execScript(tx, migration.up, gpa.allocator());
            try tx.insert("migration", .{
                .name = @as([]const u8, migration.name),
                .applied_at = DateTime.now(),
            }, gpa.allocator());
        }
        try tx.commit();
    }
}

const create_migration_table =
    \\CREATE TABLE IF NOT EXISTS
    \\migration(
    \\  name TEXT NOT NULL PRIMARY KEY,
    \\  applied_at TIMESTAMPTZ NOT NULL
    \\);
;

// NOTE: I might fuck with these until the v0.1 release. After that, I'll guarantee that you
// can upgrade to any v0.x release by just running unapplied migrations in order. You might
// need extra work to upgrade to v1.0 but you shouldn't have to recreate the db.
const migrations: []const Migration = &.{
    .{
        .name = "accounts and actors",
        .up = 
        \\CREATE TABLE actor(
        \\  id UUID NOT NULL PRIMARY KEY,
        \\  username TEXT NOT NULL,
        \\
        \\  created_at TIMESTAMPTZ NOT NULL
        \\);
        \\
        \\CREATE TABLE account(
        \\  id UUID NOT NULL PRIMARY KEY REFERENCES actor(id),
        \\
        \\  kind TEXT NOT NULL CHECK (kind IN ('admin', 'user')),
        \\  email TEXT
        \\);
        \\
        \\CREATE TABLE password(
        \\  account_id UUID NOT NULL PRIMARY KEY REFERENCES account(id),
        \\
        \\  hash BLOB NOT NULL,
        \\  changed_at TIMESTAMPTZ NOT NULL
        \\);
        ,
        .down = 
        \\DROP TABLE password;
        \\DROP TABLE account;
        \\DROP TABLE actor;
        ,
    },
    .{
        .name = "notes",
        .up = 
        \\CREATE TABLE note(
        \\  id UUID NOT NULL,
        \\
        \\  content TEXT NOT NULL,
        \\  author_id UUID NOT NULL REFERENCES actor(id),
        \\
        \\  created_at TIMESTAMPTZ NOT NULL
        \\);
        ,
        .down = "DROP TABLE note;",
    },
    .{
        .name = "note reactions",
        .up = 
        \\CREATE TABLE reaction(
        \\  id UUID NOT NULL PRIMARY KEY,
        \\
        \\  author_id UUID NOT NULL REFERENCES actor(id),
        \\  note_id UUID NOT NULL REFERENCES note(id),
        \\
        \\  created_at TIMESTAMPTZ NOT NULL
        \\);
        ,
        .down = "DROP TABLE reaction;",
    },
    .{
        .name = "account tokens",
        .up = 
        \\CREATE TABLE token(
        \\  hash TEXT NOT NULL PRIMARY KEY,
        \\  account_id UUID NOT NULL REFERENCES account(id),
        \\
        \\  issued_at TIMESTAMPTZ NOT NULL
        \\);
        ,
        .down = "DROP TABLE token;",
    },
    .{
        .name = "account invites",
        .up = 
        \\CREATE TABLE invite(
        \\  id UUID NOT NULL PRIMARY KEY,
        \\
        \\  name TEXT NOT NULL,
        \\  code TEXT NOT NULL UNIQUE,
        \\  created_by UUID NOT NULL REFERENCES account(id),
        \\
        \\  max_uses INTEGER,
        \\
        \\  created_at TIMESTAMPTZ NOT NULL,
        \\  expires_at TIMESTAMPTZ,
        \\
        \\  kind TEXT NOT NULL CHECK (kind in ('system_user', 'community_owner', 'user'))
        \\);
        \\ALTER TABLE account ADD COLUMN invite_id UUID REFERENCES invite(id);
        ,
        .down = 
        \\ALTER TABLE account DROP COLUMN invite_id;
        \\DROP TABLE invite;
        ,
    },
    .{
        .name = "communities",
        .up = 
        \\CREATE TABLE community(
        \\  id UUID NOT NULL PRIMARY KEY,
        \\
        \\  owner_id UUID REFERENCES account(id),
        \\  name TEXT NOT NULL,
        \\  host TEXT NOT NULL UNIQUE,
        \\  scheme TEXT NOT NULL CHECK (scheme IN ('http', 'https')),
        \\  kind TEXT NOT NULL CHECK (kind in ('admin', 'local')),
        \\
        \\  created_at TIMESTAMPTZ NOT NULL
        \\);
        \\ALTER TABLE actor ADD COLUMN community_id UUID REFERENCES community(id);
        \\ALTER TABLE invite ADD COLUMN community_id UUID REFERENCES community(id);
        ,
        .down = 
        \\ALTER TABLE invite DROP COLUMN community_id;
        \\ALTER TABLE actor DROP COLUMN community_id;
        \\DROP TABLE community;
        ,
    },
    .{
        .name = "follows",
        .up = 
        \\CREATE TABLE follow(
        \\  id UUID NOT NULL PRIMARY KEY,
        \\
        \\  followed_by_id UUID NOT NULL,
        \\  followee_id UUID NOT NULL,
        \\
        \\  created_at TIMESTAMPTZ NOT NULL,
        \\
        \\  UNIQUE(followed_by_id, followee_id)
        \\);
        ,
        .down = "DROP TABLE follow",
    },
    .{
        .name = "files",
        .up = 
        \\CREATE TABLE file_upload(
        \\  id UUID NOT NULL PRIMARY KEY,
        \\
        \\  owner_id UUID REFERENCES actor(id),
        \\  size INTEGER NOT NULL,
        \\
        \\  filename TEXT NOT NULL,
        \\  description TEXT,
        \\  content_type TEXT,
        \\  sensitive BOOLEAN NOT NULL,
        \\
        \\  status TEXT NOT NULL,
        \\
        \\  created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
        \\  updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
        \\);
        \\
        \\CREATE TABLE drive_entry(
        \\  id UUID NOT NULL PRIMARY KEY,
        \\
        \\  owner_id UUID REFERENCES actor(id),
        \\
        \\  name TEXT,
        \\  parent_directory_id UUID REFERENCES drive_entry(id),
        \\
        \\  file_id UUID REFERENCES file_upload(id),
        \\
        \\  CHECK(
        \\      (name IS NULL AND parent_directory_id IS NULL AND file_id IS NULL)
        \\      OR (name IS NOT NULL AND parent_directory_id IS NOT NULL)
        \\  )
        \\);
        \\CREATE UNIQUE INDEX drive_entry_uniqueness
        \\ON drive_entry(
        \\  name,
        \\  COALESCE(parent_directory_id, ''),
        \\  owner_id
        \\);
        ,
        .down = 
        \\DROP INDEX drive_entry_uniqueness;
        \\DROP TABLE drive_entry;
        \\DROP TABLE file_upload;
        ,
    },
    .{
        .name = "drive_entry_path",
        .up = 
        \\CREATE VIEW drive_entry_path(
        \\  id,
        \\  path,
        \\  owner_id,
        \\  name,
        \\  parent_directory_id,
        \\  file_id,
        \\  kind
        \\) AS WITH RECURSIVE full_path(
        \\  id,
        \\  path,
        \\  owner_id
        \\) AS (
        \\      SELECT
        \\          id,
        \\          '' AS path,
        \\          owner_id
        \\      FROM drive_entry
        \\      WHERE parent_directory_id IS NULL
        \\  UNION ALL
        \\      SELECT
        \\          base.id,
        \\          (dir.path || '/' || base.name) AS path,
        \\          base.owner_id
        \\      FROM drive_entry AS base
        \\          JOIN full_path AS dir ON
        \\              base.parent_directory_id = dir.id
        \\              AND base.owner_id = dir.owner_id
        \\)
        \\SELECT
        \\  full_path.id,
        \\  (CASE WHEN LENGTH(full_path.path) = 0 THEN '/' ELSE full_path.path END) AS path,
        \\  full_path.owner_id,
        \\  drive_entry.name,
        \\  drive_entry.parent_directory_id,
        \\  drive_entry.file_id,
        \\  (CASE WHEN drive_entry.file_id IS NULL THEN 'dir' ELSE 'file' END) as kind
        \\FROM full_path JOIN drive_entry ON full_path.id = drive_entry.id;
        ,
        .down = 
        \\DROP VIEW drive_entry_path;
        ,
    },
    .{
        .name = "create drive root directories",
        .up = 
        \\INSERT INTO drive_entry(
        \\  id,
        \\  owner_id,
        \\  parent_directory_id,
        \\  name,
        \\  file_id
        \\) SELECT
        \\  id,
        \\  id AS owner_id,
        \\  NULL AS parent_directory_id,
        \\  NULL AS name,
        \\  NULL AS file_id
        \\FROM actor;
        ,
        .down = "",
    },
    .{
        .name = "community actors",
        .up = "ALTER TABLE community ADD COLUMN community_actor_id UUID REFERENCES actor(id)",
        .down = "ALTER COLUMN community DROP COLUMN community_actor_id",
    },
    .{
        .name = "create community actors",
        .up = 
        \\INSERT INTO actor(
        \\  id,
        \\  username,
        \\  community_id,
        \\  created_at
        \\) SELECT
        \\  id,
        \\  host AS username,
        \\  id AS community_id,
        \\  CURRENT_TIMESTAMP AS created_at
        \\FROM community;
        \\UPDATE community SET community_actor_id = id;
        ,
        .down = "",
    },
    .{
        .name = "user profiles",
        .up = 
        \\ALTER TABLE actor ADD COLUMN bio TEXT NOT NULL DEFAULT '';
        \\ALTER TABLE actor ADD COLUMN display_name TEXT;
        \\ALTER TABLE actor ADD COLUMN avatar_file_id UUID REFERENCES file_upload(id);
        \\ALTER TABLE actor ADD COLUMN header_file_id UUID REFERENCES file_upload(id);
        \\ALTER TABLE actor ADD COLUMN profile_fields JSON DEFAULT '[]';
        \\ALTER TABLE actor ADD COLUMN updated_at TIMESTAMPTZ DEFAULT 0;
        ,
        .down = 
        \\ALTER TABLE actor DROP COLUMN bio;
        \\ALTER TABLE actor DROP COLUMN display_name;
        \\ALTER TABLE actor DROP COLUMN avatar_file_id;
        \\ALTER TABLE actor DROP COLUMN header_file_id;
        \\ALTER TABLE actor DROP COLUMN profile_fields;
        \\ALTER TABLE actor DROP COLUMN updated_at;
        ,
    },
};