5 files changed, 379 insertions, 0 deletions
diff --git a/backend/src/repos/maps_repo.zig b/backend/src/repos/maps_repo.zig
new file mode 100644
index 0000000..8031827
--- /dev/null
+++ b/backend/src/repos/maps_repo.zig
@@ -0,0 +1,84 @@
+const std = @import("std");
+const zqlite = @import("zqlite");
+const nanoid = @import("../lib/nanoid.zig");
+
+pub const Map = struct {
+    id: []const u8,
+    name: []const u8,
+};
+
+pub fn get_maps(allocator: std.mem.Allocator, conn: zqlite.Conn) !std.ArrayList(Map) {
+    const query =
+        \\SELECT id, name
+        \\FROM maps
+    ;
+
+    var rows = try conn.rows(query, .{});
+    defer rows.deinit();
+
+    var list = std.ArrayList(Map).init(allocator);
+    while (rows.next()) |row| {
+        try list.append(Map{
+            .id = try allocator.dupe(u8, row.text(0)),
+            .name = try allocator.dupe(u8, row.text(1)),
+        });
+    }
+    if (rows.err) |err| return err;
+
+    return list;
+}
+
+pub fn get_map(allocator: std.mem.Allocator, conn: zqlite.Conn, id: []const u8) !?Map {
+    const query =
+        \\SELECT id, name
+        \\FROM maps
+        \\WHERE id = ?
+    ;
+
+    if (try conn.row(query, .{id})) |row| {
+        defer row.deinit();
+        return Map{
+            .id = try allocator.dupe(u8, row.text(0)),
+            .name = try allocator.dupe(u8, row.text(1)),
+        };
+    } else {
+        return null;
+    }
+}
+
+pub fn create(allocator: std.mem.Allocator, conn: zqlite.Conn, name: []const u8) !Map {
+    const query =
+        \\INSERT INTO maps(id, name)
+        \\VALUES (?, ?)
+    ;
+
+    const id: []const u8 = &nanoid.generate(std.crypto.random);
+    try conn.exec(query, .{ id, name });
+    return Map{
+        .id = try allocator.dupe(u8, id),
+        .name = name,
+    };
+}
+
+pub fn update(conn: zqlite.Conn, id: []const u8, name: []const u8) !Map {
+    const query =
+        \\UPDATE maps
+        \\SET name = ?, updated_at = datetime()
+        \\WHERE id = ?
+    ;
+
+    try conn.exec(query, .{ name, id });
+    return Map{
+        .id = id,
+        .name = name,
+    };
+}
+
+pub fn delete(conn: zqlite.Conn, id: []const u8) !void {
+    const query =
+        \\DELETE FROM maps
+        \\WHERE id = ?
+    ;
+
+    try conn.exec(query, .{id});
+}
diff --git a/backend/src/repos/markers_repo.zig b/backend/src/repos/markers_repo.zig
new file mode 100644
index 0000000..232b8b6
--- /dev/null
+++ b/backend/src/repos/markers_repo.zig
@@ -0,0 +1,114 @@
+const std = @import("std");
+const zqlite = @import("zqlite");
+const nanoid = @import("../lib/nanoid.zig");
+
+pub const Marker = struct {
+    id: []const u8,
+    lat: f64,
+    lng: f64,
+    color: []const u8,
+    name: []const u8,
+    description: []const u8,
+    icon: []const u8,
+    radius: i64,
+};
+
+pub fn get_markers(allocator: std.mem.Allocator, conn: zqlite.Conn, map_id: []const u8) !std.ArrayList(Marker) {
+    const query =
+        \\SELECT id, lat, lng, color, name, description, icon, radius
+        \\FROM markers
+        \\WHERE map_id = ?
+    ;
+
+    var rows = try conn.rows(query, .{map_id});
+
+    defer rows.deinit();
+
+    var list = std.ArrayList(Marker).init(allocator);
+    while (rows.next()) |row| {
+        try list.append(Marker{
+            .id = try allocator.dupe(u8, row.text(0)),
+            .lat = row.float(1),
+            .lng = row.float(2),
+            .color = try allocator.dupe(u8, row.text(3)),
+            .name = try allocator.dupe(u8, row.text(4)),
+            .description = try allocator.dupe(u8, row.text(5)),
+            .icon = try allocator.dupe(u8, row.text(6)),
+            .radius = row.int(7),
+        });
+    }
+    if (rows.err) |err| return err;
+
+    return list;
+}
+
+pub const Payload = struct {
+    lat: f64,
+    lng: f64,
+    color: []const u8,
+    name: []const u8,
+    description: []const u8,
+    icon: []const u8,
+    radius: i64,
+};
+
+pub fn create(allocator: std.mem.Allocator, conn: zqlite.Conn, map_id: []const u8, p: Payload) !Marker {
+    const query =
+        \\INSERT INTO markers(id, map_id, lat, lng, color, name, description, icon, radius)
+        \\VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
+    ;
+
+    const id: []const u8 = &nanoid.generate(std.crypto.random);
+
+    try conn.exec(query, .{ id, map_id, p.lat, p.lng, p.color, p.name, p.description, p.icon, p.radius });
+
+    return Marker{
+        .id = try allocator.dupe(u8, id),
+        .lat = p.lat,
+        .lng = p.lng,
+        .color = p.color,
+        .name = p.name,
+        .description = p.description,
+        .icon = p.icon,
+        .radius = p.radius,
+    };
+}
+
+pub fn update(conn: zqlite.Conn, id: []const u8, p: Payload) !Marker {
+    const query =
+        \\UPDATE markers
+        \\SET lat = ?, lng = ?, color = ?, name = ?, description = ?, icon = ?, radius = ?, updated_at = datetime()
+        \\WHERE id = ?
+    ;
+
+    try conn.exec(query, .{ p.lat, p.lng, p.color, p.name, p.description, p.icon, p.radius, id });
+
+    return Marker{
+        .id = id,
+        .lat = p.lat,
+        .lng = p.lng,
+        .color = p.color,
+        .name = p.name,
+        .description = p.description,
+        .icon = p.icon,
+        .radius = p.radius,
+    };
+}
+
+pub fn delete(conn: zqlite.Conn, id: []const u8) !void {
+    const query =
+        \\DELETE FROM markers
+        \\WHERE id = ?
+    ;
+
+    try conn.exec(query, .{id});
+}
+
+pub fn delete_by_map_id(conn: zqlite.Conn, map_id: []const u8) !void {
+    const query =
+        \\DELETE FROM markers
+        \\WHERE map_id = ?
+    ;
+
+    try conn.exec(query, .{map_id});
+}
diff --git a/backend/src/repos/migrations/01.sql b/backend/src/repos/migrations/01.sql
new file mode 100644
index 0000000..cf4131d
--- /dev/null
+++ b/backend/src/repos/migrations/01.sql
@@ -0,0 +1,29 @@
+CREATE TABLE IF NOT EXISTS "users" (
+    "email" TEXT PRIMARY KEY,
+    "created_at" TEXT NOT NULL DEFAULT (datetime()),
+    "updated_at" TEXT NOT NULL DEFAULT (datetime()),
+    "password_hash" TEXT NOT NULL,
+    "name" TEXT NOT NULL,
+    "login_token" TEXT NULL
+) STRICT;
+
+CREATE TABLE IF NOT EXISTS "maps" (
+    "id" TEXT PRIMARY KEY,
+    "created_at" TEXT NOT NULL DEFAULT (datetime()),
+    "updated_at" TEXT NOT NULL DEFAULT (datetime()),
+    "name" TEXT NOT NULL
+) STRICT;
+
+CREATE TABLE IF NOT EXISTS "markers" (
+    "id" TEXT PRIMARY KEY,
+    "created_at" TEXT NOT NULL DEFAULT (datetime()),
+    "updated_at" TEXT NOT NULL DEFAULT (datetime()),
+    "map_id" TEXT NOT NULL REFERENCES maps(id),
+    "lat" REAL NOT NULL,
+    "lng" REAL NOT NULL,
+    "color" TEXT NOT NULL,
+    "name" TEXT NULL,
+    "description" TEXT NULL,
+    "icon" TEXT NULL,
+    "radius" INTEGER NULL
+) STRICT;
diff --git a/backend/src/repos/repos.zig b/backend/src/repos/repos.zig
new file mode 100644
index 0000000..860811d
--- /dev/null
+++ b/backend/src/repos/repos.zig
@@ -0,0 +1,62 @@
+const std = @import("std");
+const zqlite = @import("zqlite");
+
+pub fn init(allocator: std.mem.Allocator, path: [*:0]const u8) !zqlite.Conn {
+    const flags = zqlite.OpenFlags.Create | zqlite.OpenFlags.EXResCode;
+    const conn = try zqlite.open(path, flags);
+    try conn.execNoArgs("PRAGMA foreign_keys = ON");
+    try conn.execNoArgs("PRAGMA journal_mode = WAL");
+    try migrate(allocator, conn, .{@embedFile("migrations/01.sql")});
+    return conn;
+}
+
+fn migrate(allocator: std.mem.Allocator, conn: zqlite.Conn, migrations: [1][]const u8) !void {
+    var version: i64 = 0;
+    if (try conn.row("PRAGMA user_version", .{})) |row| {
+        defer row.deinit();
+        version = row.int(0);
+    }
+
+    // Start transaction
+    try conn.transaction();
+    errdefer conn.rollback();
+
+    for (migrations, 1..) |migration, i| {
+        const v: i64 = @intCast(i);
+        if (version < v) {
+            std.debug.print("Applying migration: {d}\n", .{i});
+
+            var it = std.mem.splitScalar(u8, migration, ';');
+            while (it.next()) |statement| {
+                if (!is_statement_empty(statement)) {
+                    try conn.exec(statement, .{});
+                }
+            }
+
+            try set_version(allocator, conn, @intCast(i));
+        }
+    }
+
+    // Commit transaction
+    try conn.commit();
+}
+
+fn is_statement_empty(str: []const u8) bool {
+    for (str) |c| {
+        if (c == ' ') continue;
+        if (c == '\n') continue;
+        if (c == '\t') continue;
+        return false;
+    }
+    return true;
+}
+
+fn set_version(allocator: std.mem.Allocator, conn: zqlite.Conn, version: i64) !void {
+    const string = try std.fmt.allocPrint(
+        allocator,
+        "PRAGMA user_version = {d}",
+        .{version},
+    );
+    defer allocator.free(string);
+    try conn.exec(string, .{});
+}
diff --git a/backend/src/repos/users_repo.zig b/backend/src/repos/users_repo.zig
new file mode 100644
index 0000000..0f4ea82
--- /dev/null
+++ b/backend/src/repos/users_repo.zig
@@ -0,0 +1,90 @@
+const std = @import("std");
+const zqlite = @import("zqlite");
+const bcrypt = std.crypto.pwhash.bcrypt;
+const rand = std.crypto.random;
+
+const token_length: usize = 20;
+
+pub const User = struct {
+    email: []const u8,
+    name: []const u8,
+};
+
+pub fn get_user(allocator: std.mem.Allocator, conn: zqlite.Conn, login_token: []const u8) !?User {
+    const query =
+        \\SELECT email, name
+        \\FROM users
+        \\WHERE login_token = ?
+    ;
+
+    if (try conn.row(query, .{login_token})) |row| {
+        defer row.deinit();
+        return User{
+            .email = try allocator.dupe(u8, row.text(0)),
+            .name = try allocator.dupe(u8, row.text(1)),
+        };
+    } else {
+        return null;
+    }
+}
+
+pub fn check_password(allocator: std.mem.Allocator, conn: zqlite.Conn, email: []const u8, password: []const u8) !?User {
+    const query =
+        \\SELECT password_hash, email, name
+        \\FROM users
+        \\WHERE email = ?
+    ;
+
+    if (try conn.row(query, .{email})) |row| {
+        defer row.deinit();
+        const hash = row.text(0);
+        const verify_options = bcrypt.VerifyOptions{ .silently_truncate_password = false };
+        std.time.sleep(100000000 + rand.intRangeAtMost(u32, 0, 100000000));
+        bcrypt.strVerify(hash, password, verify_options) catch {
+            return null;
+        };
+        return User{
+            .email = try allocator.dupe(u8, row.text(1)),
+            .name = try allocator.dupe(u8, row.text(2)),
+        };
+    } else {
+        std.time.sleep(500000000 + rand.intRangeAtMost(u32, 0, 500000000));
+        return null;
+    }
+}
+
+pub fn generate_login_token(allocator: std.mem.Allocator, conn: zqlite.Conn, email: []const u8) ![]const u8 {
+    const query =
+        \\UPDATE users
+        \\SET login_token = ?, updated_at = datetime()
+        \\WHERE email = ?
+    ;
+
+    const token = try random_token(allocator);
+    try conn.exec(query, .{ token, email });
+    return token;
+}
+
+fn random_token(allocator: std.mem.Allocator) ![]const u8 {
+    // Generate random bytes
+    var bytes: [token_length]u8 = undefined;
+    rand.bytes(&bytes);
+
+    // Encode as base64
+    const Encoder = std.base64.standard.Encoder;
+    const encoded_length = Encoder.calcSize(token_length);
+    const token = try allocator.alloc(u8, encoded_length);
+    _ = Encoder.encode(token, &bytes);
+
+    return token;
+}
+
+pub fn remove_login_token(conn: zqlite.Conn, email: []const u8) !void {
+    const query =
+        \\ UPDATE users
+        \\ SET login_token = NULL, updated_at = datetime()
+        \\ WHERE email = ?
+    ;
+
+    try conn.exec(query, .{email});
+}