# InvercargillSql Migration Implementation Plan

## Overview

This document provides a step-by-step implementation plan for migrating the Spry Authentication module from Implexus (document store) to InvercargillSql (relational database). The migration preserves all public APIs while replacing the storage backend.

## Reference Documents

- Analysis: [`plans/authentication-implexus-analysis.md`](authentication-implexus-analysis.md)
- Current UserService: [`src/Authentication/UserService.vala`](../src/Authentication/UserService.vala)
- Current SessionService: [`src/Authentication/SessionService.vala`](../src/Authentication/SessionService.vala)

---

## 1. Repository Interface Design

### 1.1 UserRepository Interface

**File:** `src/Authentication/Repositories/UserRepository.vala`

```vala
using Invercargill.DataStructures;

namespace Spry.Authentication {

    /**
     * Repository interface for User persistence operations.
     * Abstracts the storage mechanism from the service layer.
     */
    public interface UserRepository : GLib.Object {

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

        /**
         * Gets a user by their unique ID.
         *
         * @param id The user's unique identifier
         * @return The User, or null if not found
         * @throws Error on storage failure
         */
        public abstract async User? get_by_id_async(string id) throws Error;

        /**
         * Gets a user by their username.
         *
         * @param username The username to look up
         * @return The User, or null if not found
         * @throws Error on storage failure
         */
        public abstract async User? get_by_username_async(string username) throws Error;

        /**
         * Gets a user by their email address.
         *
         * @param email The email address to look up
         * @return The User, or null if not found
         * @throws Error on storage failure
         */
        public abstract async User? get_by_email_async(string email) throws Error;

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

        /**
         * Creates a new user.
         *
         * @param user The user to create (id should be pre-generated)
         * @return The created User
         * @throws UserError.DUPLICATE_USERNAME if username exists
         * @throws UserError.DUPLICATE_EMAIL if email exists
         * @throws Error on storage failure
         */
        public abstract async User create_async(User user) throws Error;

        /**
         * Updates an existing user.
         *
         * @param user The user to update
         * @throws UserError.USER_NOT_FOUND if user doesn't exist
         * @throws UserError.DUPLICATE_USERNAME if new username conflicts
         * @throws UserError.DUPLICATE_EMAIL if new email conflicts
         * @throws Error on storage failure
         */
        public abstract async void update_async(User user) throws Error;

        /**
         * Deletes a user by their unique ID.
         *
         * @param id The user's unique identifier
         * @throws UserError.USER_NOT_FOUND if user doesn't exist
         * @throws Error on storage failure
         */
        public abstract async void delete_async(string id) throws Error;

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

        /**
         * Checks if a username already exists.
         *
         * @param username The username to check
         * @return true if the username exists
         * @throws Error on storage failure
         */
        public abstract async bool username_exists_async(string username) throws Error;

        /**
         * Checks if an email already exists.
         *
         * @param email The email to check
         * @return true if the email exists
         * @throws Error on storage failure
         */
        public abstract async bool email_exists_async(string email) throws Error;

        /**
         * Lists users with pagination support.
         *
         * @param offset The number of users to skip
         * @param limit The maximum number of users to return
         * @return A Vector of users
         * @throws Error on storage failure
         */
        public abstract async Vector<User> list_async(int offset = 0, int limit = 100) throws Error;

        /**
         * Gets the total count of users.
         *
         * @return The number of users
         * @throws Error on storage failure
         */
        public abstract async int count_async() throws Error;

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

        /**
         * Gets all permissions for a user.
         *
         * @param user_id The user's unique identifier
         * @return A Vector of permission strings
         * @throws Error on storage failure
         */
        public abstract async Vector<string> get_permissions_async(string user_id) throws Error;

        /**
         * Adds a permission to a user.
         *
         * @param user_id The user's unique identifier
         * @param permission The permission to add
         * @throws Error on storage failure
         */
        public abstract async void add_permission_async(string user_id, string permission) throws Error;

        /**
         * Removes a permission from a user.
         *
         * @param user_id The user's unique identifier
         * @param permission The permission to remove
         * @throws Error on storage failure
         */
        public abstract async void remove_permission_async(string user_id, string permission) throws Error;

        /**
         * Clears all permissions from a user.
         *
         * @param user_id The user's unique identifier
         * @throws Error on storage failure
         */
        public abstract async void clear_permissions_async(string user_id) throws Error;

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

        /**
         * Gets app data for a user.
         *
         * @param user_id The user's unique identifier
         * @return A Dictionary of app data key-value pairs
         * @throws Error on storage failure
         */
        public abstract async Dictionary<string, string> get_app_data_async(string user_id) throws Error;

        /**
         * Sets an app data value for a user.
         *
         * @param user_id The user's unique identifier
         * @param key The app data key
         * @param value The app data value
         * @throws Error on storage failure
         */
        public abstract async void set_app_data_value_async(string user_id, string key, string value) throws Error;

        /**
         * Removes an app data key from a user.
         *
         * @param user_id The user's unique identifier
         * @param key The app data key to remove
         * @throws Error on storage failure
         */
        public abstract async void remove_app_data_value_async(string user_id, string key) throws Error;
    }
}
```

### 1.2 SessionRepository Interface

**File:** `src/Authentication/Repositories/SessionRepository.vala`

```vala
using Invercargill.DataStructures;

namespace Spry.Authentication {

    /**
     * Repository interface for Session persistence operations.
     * Abstracts the storage mechanism from the service layer.
     */
    public interface SessionRepository : GLib.Object {

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

        /**
         * Gets a session by its unique ID.
         *
         * @param id The session's unique identifier
         * @return The Session, or null if not found
         * @throws Error on storage failure
         */
        public abstract async Session? get_by_id_async(string id) throws Error;

        /**
         * Gets all sessions for a user.
         *
         * @param user_id The user's unique identifier
         * @return A Vector of sessions (excluding expired)
         * @throws Error on storage failure
         */
        public abstract async Vector<Session> get_by_user_async(string user_id) throws Error;

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

        /**
         * Creates a new session.
         *
         * @param session The session to create (id should be pre-generated)
         * @return The created Session
         * @throws Error on storage failure
         */
        public abstract async Session create_async(Session session) throws Error;

        /**
         * Updates an existing session.
         *
         * @param session The session to update
         * @throws SessionError.SESSION_NOT_FOUND if session doesn't exist
         * @throws Error on storage failure
         */
        public abstract async void update_async(Session session) throws Error;

        /**
         * Deletes a session by its unique ID.
         *
         * @param id The session's unique identifier
         * @throws Error on storage failure
         */
        public abstract async void delete_async(string id) throws Error;

        /**
         * Deletes all sessions for a user.
         *
         * @param user_id The user's unique identifier
         * @throws Error on storage failure
         */
        public abstract async void delete_by_user_async(string user_id) throws Error;

        // =========================================================================
        // Cleanup Operations
        // =========================================================================

        /**
         * Removes all expired sessions from storage.
         *
         * @throws Error on storage failure
         */
        public abstract async void cleanup_expired_async() throws Error;
    }
}
```

---

## 2. SQL Schema Design

### 2.1 Users Table

```sql
CREATE TABLE users (
    id TEXT PRIMARY KEY NOT NULL,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL,
    updated_at TEXT,
    last_login_at TEXT
);

-- Index for username lookups (also enforces uniqueness)
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Index for email lookups (also enforces uniqueness)
CREATE UNIQUE INDEX idx_users_email ON users(email);
```

### 2.2 User Permissions Table

```sql
CREATE TABLE user_permissions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id TEXT NOT NULL,
    permission TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE(user_id, permission)
);

-- Index for permission lookups by user
CREATE INDEX idx_user_permissions_user_id ON user_permissions(user_id);
```

### 2.3 User App Data Table

```sql
CREATE TABLE user_app_data (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id TEXT NOT NULL,
    key TEXT NOT NULL,
    value TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE(user_id, key)
);

-- Index for app data lookups by user
CREATE INDEX idx_user_app_data_user_id ON user_app_data(user_id);
```

### 2.4 Sessions Table

```sql
CREATE TABLE sessions (
    id TEXT PRIMARY KEY NOT NULL,
    user_id TEXT NOT NULL,
    created_at TEXT NOT NULL,
    expires_at TEXT NOT NULL,
    ip_address TEXT,
    user_agent TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Index for session lookups by user
CREATE INDEX idx_sessions_user_id ON sessions(user_id);

-- Index for expired session cleanup
CREATE INDEX idx_sessions_expires_at ON sessions(expires_at);
```

### 2.5 Schema Diagram

```mermaid
erDiagram
    users ||--o{ user_permissions : has
    users ||--o{ user_app_data : has
    users ||--o{ sessions : has

    users {
        TEXT id PK
        TEXT username UK
        TEXT email UK
        TEXT password_hash
        INTEGER is_active
        TEXT created_at
        TEXT updated_at
        TEXT last_login_at
    }

    user_permissions {
        INTEGER id PK
        TEXT user_id FK
        TEXT permission
    }

    user_app_data {
        INTEGER id PK
        TEXT user_id FK
        TEXT key UK
        TEXT value
    }

    sessions {
        TEXT id PK
        TEXT user_id FK
        TEXT created_at
        TEXT expires_at
        TEXT ip_address
        TEXT user_agent
    }
```

---

## 3. Implementation Details

### 3.1 SqlUserRepository Implementation

**File:** `src/Authentication/Repositories/SqlUserRepository.vala`

**Constructor:**
```vala
public class SqlUserRepository : GLib.Object, UserRepository {

    private InvercargillSql.Connection _connection;

    public SqlUserRepository(InvercargillSql.Connection connection) {
        _connection = connection;
    }
    
    // ... implementation
}
```

**Key Implementation Patterns:**

1. **INSERT with Parameters:**
```vala
public async User create_async(User user) throws Error {
    var sql = """
        INSERT INTO users (id, username, email, password_hash, is_active, created_at)
        VALUES (:id, :username, :email, :password_hash, :is_active, :created_at)
    """;
    
    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("is_active", 1)
        .with_parameter("created_at", user.created_at.format_iso8601())
        .execute_non_query_async();
    
    return user;
}
```

2. **SELECT with Properties Mapping:**
```vala
public async User? get_by_id_async(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;
    
    return user_from_properties(row);
}

private User user_from_properties(Properties props) {
    var user = new User();
    user.set_id(props.get("id").as<string>());
    user.set_username(props.get("username").as<string>());
    user.email = props.get("email").as<string>();
    user.password_hash = props.get("password_hash").as<string>());
    
    var created_str = props.get("created_at").as<string>();
    user.created_at = new DateTime.from_iso8601(created_str, new TimeZone.utc());
    
    // Handle nullable fields
    if (props.has("updated_at") && props.get("updated_at") != null) {
        var updated_str = props.get("updated_at").as<string>();
        user.updated_at = new DateTime.from_iso8601(updated_str, new TimeZone.utc());
    }
    
    return user;
}
```

3. **Permissions as Sub-query:**
```vala
public async User? get_by_id_async(string id) throws Error {
    // Get user with permissions in single query
    var sql = """
        SELECT u.*, GROUP_CONCAT(up.permission, ',') as permissions
        FROM users u
        LEFT JOIN user_permissions up ON u.id = up.user_id
        WHERE u.id = :id
        GROUP BY u.id
    """;
    
    // ... execute and parse
}
```

### 3.2 SqlSessionRepository Implementation

**File:** `src/Authentication/Repositories/SqlSessionRepository.vala`

**Constructor:**
```vala
public class SqlSessionRepository : GLib.Object, SessionRepository {

    private InvercargillSql.Connection _connection;

    public SqlSessionRepository(InvercargillSql.Connection connection) {
        _connection = connection;
    }
    
    // ... implementation
}
```

**Key Implementation Patterns:**

1. **Create Session:**
```vala
public async Session create_async(Session session) throws Error {
    var sql = """
        INSERT INTO sessions (id, user_id, created_at, expires_at, ip_address, user_agent)
        VALUES (:id, :user_id, :created_at, :expires_at, :ip_address, :user_agent)
    """;
    
    yield _connection.create_command(sql)
        .with_parameter("id", session.id)
        .with_parameter("user_id", session.user_id)
        .with_parameter("created_at", session.created_at.format_iso8601())
        .with_parameter("expires_at", session.expires_at.format_iso8601())
        .with_parameter("ip_address", session.ip_address ?? "")
        .with_parameter("user_agent", session.user_agent ?? "")
        .execute_non_query_async();
    
    return session;
}
```

2. **Get Sessions by User:**
```vala
public async Vector<Session> get_by_user_async(string user_id) throws Error {
    var sql = """
        SELECT * FROM sessions 
        WHERE user_id = :user_id AND expires_at > :now
        ORDER BY created_at DESC
    """;
    
    var now = new DateTime.now_utc().format_iso8601();
    
    var results = yield _connection.create_command(sql)
        .with_parameter("user_id", user_id)
        .with_parameter("now", now)
        .execute_query_async();
    
    var sessions = new Vector<Session>();
    foreach (var row in results) {
        sessions.add(session_from_properties(row));
    }
    return sessions;
}
```

3. **Cleanup Expired:**
```vala
public async void cleanup_expired_async() throws Error {
    var sql = "DELETE FROM sessions WHERE expires_at < :now";
    var now = new DateTime.now_utc().format_iso8601();
    
    yield _connection.create_command(sql)
        .with_parameter("now", now)
        .execute_non_query_async();
}
```

### 3.3 Schema Migration Class

**File:** `src/Authentication/Migrations/CreateAuthTables.vala`

```vala
namespace Spry.Authentication.Migrations {

    /**
     * Creates the authentication database schema.
     * Run once during application initialization.
     */
    public class CreateAuthTables : GLib.Object {

        private InvercargillSql.Connection _connection;

        public CreateAuthTables(InvercargillSql.Connection connection) {
            _connection = connection;
        }

        /**
         * Creates all authentication tables if they don't exist.
         */
        public async void migrate_async() throws Error {
            // Users table
            yield _connection.create_command("""
                CREATE TABLE IF NOT EXISTS users (
                    id TEXT PRIMARY KEY NOT NULL,
                    username TEXT NOT NULL UNIQUE,
                    email TEXT NOT NULL UNIQUE,
                    password_hash TEXT NOT NULL,
                    is_active INTEGER NOT NULL DEFAULT 1,
                    created_at TEXT NOT NULL,
                    updated_at TEXT,
                    last_login_at TEXT
                )
            """).execute_non_query_async();

            // User permissions table
            yield _connection.create_command("""
                CREATE TABLE IF NOT EXISTS user_permissions (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id TEXT NOT NULL,
                    permission TEXT NOT NULL,
                    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                    UNIQUE(user_id, permission)
                )
            """).execute_non_query_async();

            // User app data table
            yield _connection.create_command("""
                CREATE TABLE IF NOT EXISTS user_app_data (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id TEXT NOT NULL,
                    key TEXT NOT NULL,
                    value TEXT,
                    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                    UNIQUE(user_id, key)
                )
            """).execute_non_query_async();

            // Sessions table
            yield _connection.create_command("""
                CREATE TABLE IF NOT EXISTS sessions (
                    id TEXT PRIMARY KEY NOT NULL,
                    user_id TEXT NOT NULL,
                    created_at TEXT NOT NULL,
                    expires_at TEXT NOT NULL,
                    ip_address TEXT,
                    user_agent TEXT,
                    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
                )
            """).execute_non_query_async();

            // Create indexes
            yield create_index_async("idx_users_username", "users(username)");
            yield create_index_async("idx_users_email", "users(email)");
            yield create_index_async("idx_user_permissions_user_id", "user_permissions(user_id)");
            yield create_index_async("idx_user_app_data_user_id", "user_app_data(user_id)");
            yield create_index_async("idx_sessions_user_id", "sessions(user_id)");
            yield create_index_async("idx_sessions_expires_at", "sessions(expires_at)");
        }

        private async void create_index_async(string name, string definition) throws Error {
            yield _connection.create_command(
                "CREATE INDEX IF NOT EXISTS %s ON %s".printf(name, definition)
            ).execute_non_query_async();
        }
    }
}
```

---

## 4. Code Migration Strategy

### 4.1 UserService Changes

**Current Dependencies (to remove):**
```vala
using Implexus.Core;
private Engine _engine = inject<Engine>();
```

**New Dependencies (to add):**
```vala
private UserRepository _user_repository = inject<UserRepository>();
```

**Methods to Modify:**

| Method | Current Implementation | New Implementation |
|--------|----------------------|-------------------|
| [`create_user_async()`](../src/Authentication/UserService.vala:65) | Uses `create_document_async()`, `set_entity_property_async()` | Call `_user_repository.create_async()` |
| [`get_user_async()`](../src/Authentication/UserService.vala:116) | Uses `get_entity_or_null_async()`, `get_properties_async()` | Call `_user_repository.get_by_id_async()` |
| [`get_user_by_username_async()`](../src/Authentication/UserService.vala:137) | Uses catalogue path lookup | Call `_user_repository.get_by_username_async()` |
| [`get_user_by_email_async()`](../src/Authentication/UserService.vala:170) | Uses catalogue path lookup | Call `_user_repository.get_by_email_async()` |
| [`update_user_async()`](../src/Authentication/UserService.vala:207) | Uses `set_entity_property_async()` | Call `_user_repository.update_async()` |
| [`delete_user_async()`](../src/Authentication/UserService.vala:256) | Uses `delete_async()` on entity | Call `_user_repository.delete_async()` |
| [`list_users_async()`](../src/Authentication/UserService.vala:281) | Iterates container children | Call `_user_repository.list_async()` |
| [`username_exists_async()`](../src/Authentication/UserService.vala:414) | Uses `entity_exists_async()` on catalogue path | Call `_user_repository.username_exists_async()` |
| [`email_exists_async()`](../src/Authentication/UserService.vala:426) | Uses `entity_exists_async()` on catalogue path | Call `_user_repository.email_exists_async()` |
| [`user_count_async()`](../src/Authentication/UserService.vala:437) | Counts document children | Call `_user_repository.count_async()` |

**Methods to Remove (Private Helpers):**
- [`get_users_container_async()`](../src/Authentication/UserService.vala:459) - No longer needed
- [`store_user_in_document_async()`](../src/Authentication/UserService.vala:483) - No longer needed
- [`load_user_from_document_async()`](../src/Authentication/UserService.vala:493) - No longer needed
- [`json_to_element()`](../src/Authentication/UserService.vala:503) - No longer needed
- [`properties_to_json()`](../src/Authentication/UserService.vala:534) - No longer needed
- [`element_to_json()`](../src/Authentication/UserService.vala:544) - No longer needed

**Refactored UserService (simplified):**
```vala
using Invercargill.DataStructures;
using Inversion;

namespace Spry.Authentication {

    public class UserService : GLib.Object {

        private UserRepository _user_repository = inject<UserRepository>();
        private CryptographyProvider _crypto = inject<CryptographyProvider>();

        public async User create_user_async(string username, string email, string password) throws Error {
            // Validate username uniqueness
            if (yield _user_repository.username_exists_async(username)) {
                throw new UserError.DUPLICATE_USERNAME("Username already exists");
            }

            // Validate email uniqueness
            if (yield _user_repository.email_exists_async(email)) {
                throw new UserError.DUPLICATE_EMAIL("Email already exists");
            }

            // Hash password
            var password_hash = hash_password(password);
            if (password_hash == null) {
                throw new UserError.STORAGE_ERROR("Failed to hash password");
            }

            // Create user object
            var user = new User();
            user.set_id(generate_uuid());
            user.set_username(username);
            user.email = email;
            user.password_hash = (!)password_hash;
            user.created_at = new DateTime.now_utc();

            // Persist via repository
            return yield _user_repository.create_async(user);
        }

        public async User? get_user_async(string user_id) throws Error {
            return yield _user_repository.get_by_id_async(user_id);
        }

        public async User? get_user_by_username_async(string username) throws Error {
            return yield _user_repository.get_by_username_async(username);
        }

        public async User? get_user_by_email_async(string email) throws Error {
            return yield _user_repository.get_by_email_async(email);
        }

        public async void update_user_async(User user) throws Error {
            // Check for username/email conflicts before update
            var existing = yield _user_repository.get_by_id_async(user.id);
            if (existing == null) {
                throw new UserError.USER_NOT_FOUND("User not found");
            }

            if (existing.username != user.username) {
                if (yield _user_repository.username_exists_async(user.username)) {
                    throw new UserError.DUPLICATE_USERNAME("Username already exists");
                }
            }

            if (existing.email != user.email) {
                if (yield _user_repository.email_exists_async(user.email)) {
                    throw new UserError.DUPLICATE_EMAIL("Email already exists");
                }
            }

            user.updated_at = new DateTime.now_utc();
            yield _user_repository.update_async(user);
        }

        public async void delete_user_async(string user_id) throws Error {
            yield _user_repository.delete_async(user_id);
        }

        public async Vector<User> list_users_async(int offset = 0, int limit = 100) throws Error {
            return yield _user_repository.list_async(offset, limit);
        }

        public async bool username_exists_async(string username) throws Error {
            return yield _user_repository.username_exists_async(username);
        }

        public async bool email_exists_async(string email) throws Error {
            return yield _user_repository.email_exists_async(email);
        }

        public async int user_count_async() throws Error {
            return yield _user_repository.count_async();
        }

        // Password methods remain unchanged
        public string? hash_password(string password) {
            return Sodium.PasswordHashing.hash(password);
        }

        public bool verify_password(User user, string password) {
            return Sodium.PasswordHashing.check(user.password_hash, password);
        }

        public async void set_password_async(User user, string new_password) throws Error {
            var password_hash = hash_password(new_password);
            if (password_hash == null) {
                throw new UserError.STORAGE_ERROR("Failed to hash password");
            }
            user.password_hash = (!)password_hash;
            user.updated_at = new DateTime.now_utc();
            yield update_user_async(user);
        }

        public async User? authenticate_async(string username_or_email, string password) throws Error {
            User? user = yield get_user_by_username_async(username_or_email);
            if (user == null) {
                user = yield get_user_by_email_async(username_or_email);
            }
            if (user == null) return null;

            if (!verify_password(user, password)) return null;
            return user;
        }

        private string generate_uuid() {
            uint8[] bytes = new uint8[16];
            Sodium.Random.random_bytes(bytes);
            bytes[6] = (bytes[6] & 0x0f) | 0x40;
            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]
            );
        }
    }
}
```

### 4.2 SessionService Changes

**Current Dependencies (to remove):**
```vala
using Implexus.Core;
private Engine _engine = inject<Engine>();
```

**New Dependencies (to add):**
```vala
private SessionRepository _session_repository = inject<SessionRepository>();
```

**Methods to Modify:**

| Method | Current Implementation | New Implementation |
|--------|----------------------|-------------------|
| [`create_session_async()`](../src/Authentication/SessionService.vala:209) | Creates document + updates user index | Call `_session_repository.create_async()` |
| [`get_session_async()`](../src/Authentication/SessionService.vala:387) | Uses `get_entity_or_null_async()` | Call `_session_repository.get_by_id_async()` |
| [`get_sessions_for_user_async()`](../src/Authentication/SessionService.vala:412) | Uses secondary index | Call `_session_repository.get_by_user_async()` |
| [`delete_session_async()`](../src/Authentication/SessionService.vala:441) | Deletes document + updates index | Call `_session_repository.delete_async()` |
| [`delete_all_sessions_for_user_async()`](../src/Authentication/SessionService.vala:468) | Iterates index + deletes | Call `_session_repository.delete_by_user_async()` |
| [`cleanup_expired_sessions_async()`](../src/Authentication/SessionService.vala:562) | Iterates all sessions | Call `_session_repository.cleanup_expired_async()` |

**Methods to Remove (Private Helpers):**
- [`get_sessions_container_async()`](../src/Authentication/SessionService.vala:641) - No longer needed
- [`get_sessions_by_user_container_async()`](../src/Authentication/SessionService.vala:665) - No longer needed
- [`store_session_in_document_async()`](../src/Authentication/SessionService.vala:688) - No longer needed
- [`load_session_from_document_async()`](../src/Authentication/SessionService.vala:698) - No longer needed
- [`json_to_element()`](../src/Authentication/SessionService.vala:708) - No longer needed
- [`properties_to_json()`](../src/Authentication/SessionService.vala:740) - No longer needed
- [`element_to_json()`](../src/Authentication/SessionService.vala:750) - No longer needed
- [`add_session_to_user_index_async()`](../src/Authentication/SessionService.vala:834) - No longer needed
- [`remove_session_from_user_index_async()`](../src/Authentication/SessionService.vala:873) - No longer needed
- [`clear_user_sessions_index_async()`](../src/Authentication/SessionService.vala:897) - No longer needed
- [`get_session_ids_for_user_async()`](../src/Authentication/SessionService.vala:906) - No longer needed
- [`load_session_ids_from_document_async()`](../src/Authentication/SessionService.vala:917) - No longer needed
- [`store_session_ids_in_document_async()`](../src/Authentication/SessionService.vala:940) - No longer needed

### 4.3 PermissionService Changes

PermissionService already delegates to UserService, so minimal changes are needed. The `update_user_async()` call in PermissionService will work transparently with the repository-based UserService.

---

## 5. Build System Changes

### 5.1 Root meson.build Changes

**File:** [`meson.build`](../meson.build)

**Add InvercargillSql dependency:**
```meson
# Add after existing dependencies (around line 17)
invercargill_sql_dep = dependency('invercargill-sql-1')
```

**Note:** The `implexus_dep` can be removed from the root meson.build if no other modules use it. For this migration, keep it temporarily until full removal is confirmed.

### 5.2 src/meson.build Changes

**File:** [`src/meson.build`](../src/meson.build)

No changes needed - the main Spry library doesn't directly use Implexus.

### 5.3 src/Authentication/meson.build Changes

**File:** [`src/Authentication/meson.build`](../src/Authentication/meson.build)

**Current content:**
```meson
authentication_sources = files(
    'User.vala',
    'Session.vala',
    'UserService.vala',
    'SessionService.vala',
    'PermissionService.vala',
    'UserIdentityProvider.vala',
    'Components/LoginFormComponent.vala',
    'Components/UserManagementComponent.vala',
    'Components/UserDetailsComponent.vala',
    'Components/NewUserComponent.vala',
    'AuthenticationMigration.vala'
)

libspry_authentication = static_library('spry-authentication',
    authentication_sources,
    dependencies: [spry_dep, spry_authorisation_dep, implexus_dep, sodium_deps, invercargill_dep, astralis_dep],
    include_directories: include_directories('..')
)

spry_authentication_inc = include_directories('.')
spry_authentication_dep = declare_dependency(
    link_with: libspry_authentication,
    include_directories: spry_authentication_inc,
    dependencies: [spry_dep, spry_authorisation_dep, implexus_dep]
)
```

**New content:**
```meson
authentication_sources = files(
    'User.vala',
    'Session.vala',
    'UserService.vala',
    'SessionService.vala',
    'PermissionService.vala',
    'UserIdentityProvider.vala',
    'Components/LoginFormComponent.vala',
    'Components/UserManagementComponent.vala',
    'Components/UserDetailsComponent.vala',
    'Components/NewUserComponent.vala',
    'Repositories/UserRepository.vala',
    'Repositories/SqlUserRepository.vala',
    'Repositories/SessionRepository.vala',
    'Repositories/SqlSessionRepository.vala',
    'Migrations/CreateAuthTables.vala'
)

libspry_authentication = static_library('spry-authentication',
    authentication_sources,
    dependencies: [spry_dep, spry_authorisation_dep, invercargill_sql_dep, sodium_deps, invercargill_dep, astralis_dep],
    include_directories: include_directories('..')
)

spry_authentication_inc = include_directories('.')
spry_authentication_dep = declare_dependency(
    link_with: libspry_authentication,
    include_directories: spry_authentication_inc,
    dependencies: [spry_dep, spry_authorisation_dep, invercargill_sql_dep]
)
```

**Summary of changes:**
1. Remove `'AuthenticationMigration.vala'` from sources
2. Add new repository files to sources
3. Add new migration file to sources
4. Replace `implexus_dep` with `invercargill_sql_dep` in dependencies

---

## 6. Migration Path

### 6.1 Implementation Order

```mermaid
flowchart TB
    subgraph Phase 1 - Infrastructure
        A1[Add invercargill_sql_dep to meson.build]
        A2[Create Repositories directory]
        A3[Create Migrations directory]
    end
    
    subgraph Phase 2 - Repository Layer
        B1[Create UserRepository interface]
        B2[Create SessionRepository interface]
        B3[Implement SqlUserRepository]
        B4[Implement SqlSessionRepository]
        B5[Create CreateAuthTables migration]
    end
    
    subgraph Phase 3 - Service Migration
        C1[Update UserService to use UserRepository]
        C2[Update SessionService to use SessionRepository]
        C3[Remove Implexus imports and helpers]
    end
    
    subgraph Phase 4 - Build Updates
        D1[Update src/Authentication/meson.build]
        D2[Delete AuthenticationMigration.vala]
        D3[Remove implexus_dep from root meson.build]
    end
    
    subgraph Phase 5 - IoC Registration
        E1[Register Connection in IoC container]
        E2[Register UserRepository to SqlUserRepository]
        E3[Register SessionRepository to SqlSessionRepository]
        E4[Run migration at startup]
    end
    
    A1 --> A2 --> A3 --> B1
    B1 --> B2 --> B3 --> B4 --> B5
    B5 --> C1 --> C2 --> C3
    C3 --> D1 --> D2 --> D3
    D3 --> E1 --> E2 --> E3 --> E4
```

### 6.2 Step-by-Step Implementation Checklist

#### Phase 1: Infrastructure Setup
- [ ] Add `invercargill_sql_dep = dependency('invercargill-sql-1')` to root [`meson.build`](../meson.build)
- [ ] Create directory `src/Authentication/Repositories/`
- [ ] Create directory `src/Authentication/Migrations/`

#### Phase 2: Repository Interfaces
- [ ] Create [`src/Authentication/Repositories/UserRepository.vala`](../src/Authentication/Repositories/UserRepository.vala) with interface definition
- [ ] Create [`src/Authentication/Repositories/SessionRepository.vala`](../src/Authentication/Repositories/SessionRepository.vala) with interface definition

#### Phase 3: Repository Implementations
- [ ] Create [`src/Authentication/Repositories/SqlUserRepository.vala`](../src/Authentication/Repositories/SqlUserRepository.vala)
  - Implement `get_by_id_async()`
  - Implement `get_by_username_async()`
  - Implement `get_by_email_async()`
  - Implement `create_async()`
  - Implement `update_async()`
  - Implement `delete_async()`
  - Implement `username_exists_async()`
  - Implement `email_exists_async()`
  - Implement `list_async()`
  - Implement `count_async()`
  - Implement permission methods
  - Implement app data methods
- [ ] Create [`src/Authentication/Repositories/SqlSessionRepository.vala`](../src/Authentication/Repositories/SqlSessionRepository.vala)
  - Implement `get_by_id_async()`
  - Implement `get_by_user_async()`
  - Implement `create_async()`
  - Implement `update_async()`
  - Implement `delete_async()`
  - Implement `delete_by_user_async()`
  - Implement `cleanup_expired_async()`

#### Phase 4: Schema Migration
- [ ] Create [`src/Authentication/Migrations/CreateAuthTables.vala`](../src/Authentication/Migrations/CreateAuthTables.vala)

#### Phase 5: Service Refactoring
- [ ] Update [`src/Authentication/UserService.vala`](../src/Authentication/UserService.vala):
  - Remove `using Implexus.Core;`
  - Replace `Engine _engine` with `UserRepository _user_repository`
  - Refactor all methods to use repository
  - Remove private helper methods for Implexus
- [ ] Update [`src/Authentication/SessionService.vala`](../src/Authentication/SessionService.vala):
  - Remove `using Implexus.Core;`
  - Replace `Engine _engine` with `SessionRepository _session_repository`
  - Refactor all methods to use repository
  - Remove private helper methods for Implexus
  - Remove user index management methods

#### Phase 6: Build System Updates
- [ ] Update [`src/Authentication/meson.build`](../src/Authentication/meson.build):
  - Add new repository files to sources
  - Add new migration file to sources
  - Remove `AuthenticationMigration.vala` from sources
  - Replace `implexus_dep` with `invercargill_sql_dep`
- [ ] Delete [`src/Authentication/AuthenticationMigration.vala`](../src/Authentication/AuthenticationMigration.vala)
- [ ] Optionally remove `implexus_dep` from root [`meson.build`](../meson.build) if no other modules use it

#### Phase 7: IoC Registration Updates
- [ ] Update application startup code to:
  - Create/open SQLite database connection
  - Register `InvercargillSql.Connection` as singleton in IoC container
  - Register `UserRepository` → `SqlUserRepository` in IoC container
  - Register `SessionRepository` → `SqlSessionRepository` in IoC container
  - Run `CreateAuthTables.migrate_async()` on first startup

### 6.3 IoC Registration Example

**In application startup code (e.g., main application entry point):**

```vala
using Inversion;
using InvercargillSql;

// Create and open database connection
var db_path = Path.build_filename(Environment.get_user_data_dir(), "spry", "authentication.db");
var connection = new Connection(db_path);
yield connection.open_async();

// Register in IoC container
var container = new Container();
container.register<Connection>(() => connection).as_singleton();
container.register<UserRepository, SqlUserRepository>().as_singleton();
container.register<SessionRepository, SqlSessionRepository>().as_singleton();
container.register<UserService>().as_singleton();
container.register<SessionService>().as_singleton();
container.register<PermissionService>().as_singleton();

// Run schema migration
var migration = new Migrations.CreateAuthTables(connection);
yield migration.migrate_async();
```

### 6.4 Data Migration Considerations

If there is existing production data in Implexus:

1. **Create a data migration script** that:
   - Reads all users from Implexus
   - Reads all sessions from Implexus
   - Inserts them into the SQL tables
   - Verifies data integrity

2. **Migration script outline:**
```vala
public async void migrate_data_from_implexus_async(
    Engine implexus_engine,
    InvercargillSql.Connection sql_connection
) throws Error {
    // Migrate users
    var users_path = new EntityPath("/spry/authentication/users");
    var users_container = yield implexus_engine.get_entity_or_null_async(users_path);
    
    if (users_container != null) {
        var children = yield users_container.get_children_async();
        foreach (var child in children) {
            if (child.entity_type == EntityType.DOCUMENT) {
                var props = yield child.get_properties_async();
                // Convert to User object and insert into SQL
            }
        }
    }
    
    // Migrate sessions similarly
}
```

### 6.5 Testing Strategy

1. **Unit Tests for Repositories:**
   - Test each repository method in isolation
   - Use in-memory SQLite database for fast tests
   - Test edge cases (null values, empty results, duplicates)

2. **Integration Tests for Services:**
   - Test UserService with SqlUserRepository
   - Test SessionService with SqlSessionRepository
   - Verify public API remains unchanged

3. **Migration Tests:**
   - Test schema creation on fresh database
   - Test idempotency (running migration twice)
   - Test data migration from Implexus (if applicable)

4. **Performance Tests:**
   - Benchmark user lookup by username
   - Benchmark session listing by user
   - Compare with Implexus performance

---

## 7. Files Summary

### 7.1 New Files to Create

| File | Purpose |
|------|---------|
| `src/Authentication/Repositories/UserRepository.vala` | User repository interface |
| `src/Authentication/Repositories/SessionRepository.vala` | Session repository interface |
| `src/Authentication/Repositories/SqlUserRepository.vala` | SQLite user repository implementation |
| `src/Authentication/Repositories/SqlSessionRepository.vala` | SQLite session repository implementation |
| `src/Authentication/Migrations/CreateAuthTables.vala` | SQL schema migration |

### 7.2 Files to Modify

| File | Changes |
|------|---------|
| [`meson.build`](../meson.build) | Add `invercargill_sql_dep` dependency |
| [`src/Authentication/meson.build`](../src/Authentication/meson.build) | Update sources and dependencies |
| [`src/Authentication/UserService.vala`](../src/Authentication/UserService.vala) | Replace Implexus with repository |
| [`src/Authentication/SessionService.vala`](../src/Authentication/SessionService.vala) | Replace Implexus with repository |

### 7.3 Files to Delete

| File | Reason |
|------|--------|
| [`src/Authentication/AuthenticationMigration.vala`](../src/Authentication/AuthenticationMigration.vala) | Replaced by SQL migration |

---

## 8. Risk Mitigation

| Risk | Mitigation |
|------|------------|
| Breaking existing API | Repository pattern isolates changes; public service APIs unchanged |
| Data loss during migration | Create backup before migration; implement data migration script |
| Performance regression | SQL indexes replicate catalogue performance; benchmark critical paths |
| Async behavior differences | Both use async/await; InvercargillSql uses thread-based async (transparent) |
| Missing features in InvercargillSql | Verify API coverage before starting; implement workarounds if needed |

---

## 9. Verification Checklist

After implementation, verify:

- [ ] All existing tests pass
- [ ] User CRUD operations work correctly
- [ ] Session CRUD operations work correctly
- [ ] Username/email uniqueness is enforced
- [ ] Permissions are correctly stored and retrieved
- [ ] App data is correctly stored and retrieved
- [ ] Expired sessions are cleaned up
- [ ] Foreign key constraints work (deleting user deletes sessions)
- [ ] Build completes without errors
- [ ] No Implexus references remain in Authentication module