Spry/src/Authentication/SqlUserRepository.vala

using Invercargill;
using Invercargill.DataStructures;
using InvercargillSql;

namespace Spry.Authentication {

    /**
     * SQL implementation of UserRepository using InvercargillSql.
     */
    public class SqlUserRepository : Object, UserRepository {

        private Connection _connection;

        // =========================================================================
        // Constructor
        // =========================================================================

        public SqlUserRepository(Connection connection) {
            _connection = connection;
        }

        // =========================================================================
        // Retrieval Operations
        // =========================================================================

        public async User? get_by_id(string id) throws Error {
            var sql = "SELECT * FROM users WHERE id = :id";

            var results = yield _connection.create_command(sql)
                .with_parameter("id", id)
                .execute_query_async();

            var row = results.first_or_default();
            if (row == null) {
                return null;
            }

            var user = user_from_properties(row);

            // Load permissions
            var permissions = yield get_permissions(id);
            foreach (var perm in permissions) {
                user.add_permission(perm);
            }

            // Load app data
            var app_data = yield get_all_app_data(id);
            foreach (var key in app_data.keys) {
                user.app_data.set(key, app_data.get(key));
            }

            return user;
        }

        public async User? get_by_username(string username) throws Error {
            var sql = "SELECT * FROM users WHERE username = :username";

            var results = yield _connection.create_command(sql)
                .with_parameter("username", username)
                .execute_query_async();

            var row = results.first_or_default();
            if (row == null) {
                return null;
            }

            var user = user_from_properties(row);

            // Load permissions
            var permissions = yield get_permissions(user.id);
            foreach (var perm in permissions) {
                user.add_permission(perm);
            }

            // Load app data
            var app_data = yield get_all_app_data(user.id);
            foreach (var key in app_data.keys) {
                user.app_data.set(key, app_data.get(key));
            }

            return user;
        }

        public async User? get_by_email(string email) throws Error {
            var sql = "SELECT * FROM users WHERE email = :email";

            var results = yield _connection.create_command(sql)
                .with_parameter("email", email)
                .execute_query_async();

            var row = results.first_or_default();
            if (row == null) {
                return null;
            }

            var user = user_from_properties(row);

            // Load permissions
            var permissions = yield get_permissions(user.id);
            foreach (var perm in permissions) {
                user.add_permission(perm);
            }

            // Load app data
            var app_data = yield get_all_app_data(user.id);
            foreach (var key in app_data.keys) {
                user.app_data.set(key, app_data.get(key));
            }

            return user;
        }

        // =========================================================================
        // Mutation Operations
        // =========================================================================

        public async User create(string username, string email, string password_hash) throws Error {
            var id = generate_uuid();
            var now = new DateTime.now_utc();

            var sql = """
                INSERT INTO users (id, username, email, password_hash, created_at, updated_at)
                VALUES (:id, :username, :email, :password_hash, :created_at, :updated_at)
            """;

            yield _connection.create_command(sql)
                .with_parameter("id", id)
                .with_parameter("username", username)
                .with_parameter("email", email)
                .with_parameter("password_hash", password_hash)
                .with_parameter("created_at", now.format_iso8601())
                .with_parameter("updated_at", now.format_iso8601())
                .execute_non_query_async();

            var user = new User();
            user.set_id(id);
            user.set_username(username);
            user.email = email;
            user.password_hash = password_hash;
            user.created_at = now;
            user.updated_at = now;

            return user;
        }

        public async void update(User user) throws Error {
            var now = new DateTime.now_utc();

            var sql = """
                UPDATE users SET
                    username = :username,
                    email = :email,
                    password_hash = :password_hash,
                    updated_at = :updated_at
                WHERE id = :id
            """;

            yield _connection.create_command(sql)
                .with_parameter("id", user.id)
                .with_parameter("username", user.username)
                .with_parameter("email", user.email)
                .with_parameter("password_hash", user.password_hash)
                .with_parameter("updated_at", now.format_iso8601())
                .execute_non_query_async();

            user.updated_at = now;
        }

        public async void delete(string id) throws Error {
            var sql = "DELETE FROM users WHERE id = :id";

            yield _connection.create_command(sql)
                .with_parameter("id", id)
                .execute_non_query_async();
        }

        // =========================================================================
        // Query Operations
        // =========================================================================

        public async bool exists_by_username(string username) throws Error {
            var sql = "SELECT COUNT(*) FROM users WHERE username = :username";

            var scalar = yield _connection.create_command(sql)
                .with_parameter("username", username)
                .execute_scalar_async();

            if (scalar == null) {
                return false;
            }

            return scalar.as<int64?>() > 0;
        }

        public async bool exists_by_email(string email) throws Error {
            var sql = "SELECT COUNT(*) FROM users WHERE email = :email";

            var scalar = yield _connection.create_command(sql)
                .with_parameter("email", email)
                .execute_scalar_async();

            if (scalar == null) {
                return false;
            }

            return scalar.as<int64?>() > 0;
        }

        // =========================================================================
        // Permission Operations
        // =========================================================================

        public async void add_permission(string user_id, string permission) throws Error {
            var sql = """
                INSERT OR IGNORE INTO user_permissions (user_id, permission)
                VALUES (:user_id, :permission)
            """;

            yield _connection.create_command(sql)
                .with_parameter("user_id", user_id)
                .with_parameter("permission", permission)
                .execute_non_query_async();
        }

        public async void remove_permission(string user_id, string permission) throws Error {
            var sql = """
                DELETE FROM user_permissions
                WHERE user_id = :user_id AND permission = :permission
            """;

            yield _connection.create_command(sql)
                .with_parameter("user_id", user_id)
                .with_parameter("permission", permission)
                .execute_non_query_async();
        }

        public async bool has_permission(string user_id, string permission) throws Error {
            var sql = """
                SELECT COUNT(*) FROM user_permissions
                WHERE user_id = :user_id AND permission = :permission
            """;

            var scalar = yield _connection.create_command(sql)
                .with_parameter("user_id", user_id)
                .with_parameter("permission", permission)
                .execute_scalar_async();

            if (scalar == null) {
                return false;
            }

            return scalar.as<int64?>() > 0;
        }

        public async Vector<string> get_permissions(string user_id) throws Error {
            var sql = "SELECT permission FROM user_permissions WHERE user_id = :user_id";

            var results = yield _connection.create_command(sql)
                .with_parameter("user_id", user_id)
                .execute_query_async();

            var permissions = new Vector<string>();
            foreach (var row in results) {
                var perm_elem = row.get("permission");
                if (perm_elem != null) {
                    var perm = perm_elem.as<string>();
                    if (perm != null && perm.length > 0) {
                        permissions.add(perm);
                    }
                }
            }

            return permissions;
        }

        // =========================================================================
        // App Data Operations
        // =========================================================================

        public async void set_app_data(string user_id, string key, string value) throws Error {
            var sql = """
                INSERT OR REPLACE INTO user_app_data (user_id, key, value)
                VALUES (:user_id, :key, :value)
            """;

            yield _connection.create_command(sql)
                .with_parameter("user_id", user_id)
                .with_parameter("key", key)
                .with_parameter("value", value)
                .execute_non_query_async();
        }

        public async string? get_app_data(string user_id, string key) throws Error {
            var sql = """
                SELECT value FROM user_app_data
                WHERE user_id = :user_id AND key = :key
            """;

            var scalar = yield _connection.create_command(sql)
                .with_parameter("user_id", user_id)
                .with_parameter("key", key)
                .execute_scalar_async();

            if (scalar == null) {
                return null;
            }

            return scalar.as<string>();
        }

        // =========================================================================
        // Private Helpers
        // =========================================================================

        private User user_from_properties(Properties props) {
            var user = new User();

            // Required fields
            user.set_id(get_string_or_empty(props, "id"));
            user.set_username(get_string_or_empty(props, "username"));
            user.email = get_string_or_empty(props, "email");
            user.password_hash = get_string_or_empty(props, "password_hash");

            // created_at
            var created_str = get_string_or_empty(props, "created_at");
            if (created_str.length > 0) {
                user.created_at = new DateTime.from_iso8601(created_str, new TimeZone.utc());
            }

            // updated_at (nullable)
            var updated_str = get_string_or_null(props, "updated_at");
            if (updated_str != null && updated_str.length > 0) {
                user.updated_at = new DateTime.from_iso8601(updated_str, new TimeZone.utc());
            }

            return user;
        }

        private string get_string_or_empty(Properties props, string key) {
            if (!props.has(key)) {
                return "";
            }
            var elem = props.get(key);
            if (elem == null) {
                return "";
            }
            var str = elem.as<string>();
            return str ?? "";
        }

        private string? get_string_or_null(Properties props, string key) {
            if (!props.has(key)) {
                return null;
            }
            var elem = props.get(key);
            if (elem == null) {
                return null;
            }
            return elem.as<string>();
        }

        private async Dictionary<string, string> get_all_app_data(string user_id) throws Error {
            var sql = "SELECT key, value FROM user_app_data WHERE user_id = :user_id";

            var results = yield _connection.create_command(sql)
                .with_parameter("user_id", user_id)
                .execute_query_async();

            var app_data = new Dictionary<string, string>();
            foreach (var row in results) {
                var key_elem = row.get("key");
                var value_elem = row.get("value");

                if (key_elem != null) {
                    var key = key_elem.as<string>();
                    var value = value_elem != null ? value_elem.as<string>() ?? "" : "";
                    if (key != null && key.length > 0) {
                        app_data.set(key, value);
                    }
                }
            }

            return app_data;
        }

        private string generate_uuid() {
            uint8[] bytes = new uint8[16];
            Sodium.Random.random_bytes(bytes);
            // Set version 4 (random UUID)
            bytes[6] = (bytes[6] & 0x0f) | 0x40;
            // Set variant RFC 4122
            bytes[8] = (bytes[8] & 0x3f) | 0x80;
            return "%02x%02x%02x%02x-%02x%02x-%02x%02x-%02x%02x-%02x%02x%02x%02x%02x%02x".printf(
                bytes[0], bytes[1], bytes[2], bytes[3],
                bytes[4], bytes[5], bytes[6], bytes[7],
                bytes[8], bytes[9], bytes[10], bytes[11],
                bytes[12], bytes[13], bytes[14], bytes[15]
            );
        }
    }
}