path: root/src/cmd_employees.c

#include "commands.h"
#include "cmd_util.h"

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#include "audit.h"
#include "db.h"
#include "secret.h"
#include "util.h"

/* ------------------------------------------------------------------ */
/* employee register                                                   */
/* ------------------------------------------------------------------ */

#define EMPLOYEE_COLUMNS                                                    \
    "id,name,personal_no_enc,address,postal_code,city,bank_account,"       \
    "salary_account,monthly_salary_ore,tax_table,tax_column,active,"       \
    "created_at,COALESCE(updated_at,''),email"

static int personal_no_valid(const char *s)
{
    if (!s)
        return 0;
    size_t n = strlen(s);
    if (n != 10 && n != 11 && n != 12 && n != 13)
        return 0;
    if (n == 11 && s[6] != '-')
        return 0;
    if (n == 13 && s[8] != '-')
        return 0;
    for (size_t i = 0; i < n; i++) {
        if (s[i] >= '0' && s[i] <= '9')
            continue;
        if (s[i] == '-' && ((n == 11 && i == 6) || (n == 13 && i == 8)))
            continue;
        return 0;
    }
    return 1;
}

static char *personal_no_mask(const char *pn)
{
    size_t n = pn ? strlen(pn) : 0;
    char *out = xmalloc(n + 1);
    for (size_t i = 0; i < n; i++) {
        if (i + 4 >= n || pn[i] == '-')
            out[i] = pn[i];
        else
            out[i] = '*';
    }
    out[n] = '\0';
    return out;
}

static yyjson_mut_val *employee_json(struct req *r, sqlite3_stmt *st)
{
    char *pn = NULL;
    if (secret_decrypt(sq(sqlite3_column_text(st, 2)), &pn) != 0)
        pn = xstrdup("********");
    char *masked = personal_no_mask(pn);
    free(pn);

    yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
    yyjson_mut_obj_add_int(r->rdoc, o, "id", sqlite3_column_int64(st, 0));
    yyjson_mut_obj_add_strcpy(r->rdoc, o, "name",
                              sq(sqlite3_column_text(st, 1)));
    yyjson_mut_obj_add_strcpy(r->rdoc, o, "personal_no", masked);
    free(masked);
    yyjson_mut_obj_add_strcpy(r->rdoc, o, "address",
                              sq(sqlite3_column_text(st, 3)));
    yyjson_mut_obj_add_strcpy(r->rdoc, o, "postal_code",
                              sq(sqlite3_column_text(st, 4)));
    yyjson_mut_obj_add_strcpy(r->rdoc, o, "city",
                              sq(sqlite3_column_text(st, 5)));
    yyjson_mut_obj_add_strcpy(r->rdoc, o, "bank_account",
                              sq(sqlite3_column_text(st, 6)));
    yyjson_mut_obj_add_strcpy(r->rdoc, o, "salary_account",
                              sq(sqlite3_column_text(st, 7)));
    yyjson_mut_obj_add_int(r->rdoc, o, "monthly_salary_ore",
                           sqlite3_column_int64(st, 8));
    yyjson_mut_obj_add_int(r->rdoc, o, "tax_table",
                           sqlite3_column_int64(st, 9));
    yyjson_mut_obj_add_int(r->rdoc, o, "tax_column",
                           sqlite3_column_int64(st, 10));
    yyjson_mut_obj_add_bool(r->rdoc, o, "active",
                            sqlite3_column_int(st, 11) != 0);
    yyjson_mut_obj_add_strcpy(r->rdoc, o, "created_at",
                              sq(sqlite3_column_text(st, 12)));
    yyjson_mut_obj_add_strcpy(r->rdoc, o, "updated_at",
                              sq(sqlite3_column_text(st, 13)));
    yyjson_mut_obj_add_strcpy(r->rdoc, o, "email",
                              sq(sqlite3_column_text(st, 14)));
    return o;
}

/* The register's audit entries must not contain the personnummer. */
static char *employee_audit_json(yyjson_val *args)
{
    if (!args || !yyjson_is_obj(args))
        return xstrdup("{}");
    yyjson_mut_doc *doc = yyjson_mut_doc_new(NULL);
    yyjson_mut_val *out = yyjson_mut_obj(doc);
    yyjson_mut_doc_set_root(doc, out);
    yyjson_obj_iter it = yyjson_obj_iter_with(args);
    yyjson_val *key;
    while ((key = yyjson_obj_iter_next(&it))) {
        const char *k = yyjson_get_str(key);
        yyjson_val *v = yyjson_obj_iter_get_val(key);
        if (!k || !v)
            continue;
        if (strcmp(k, "personal_no") == 0) {
            yyjson_mut_obj_add_strcpy(doc, out, k, "[redacted]");
            continue;
        }
        yyjson_mut_val *copy = yyjson_val_mut_copy(doc, v);
        if (copy)
            yyjson_mut_obj_add(out, yyjson_mut_strcpy(doc, k), copy);
    }
    char *json = yyjson_mut_write(doc, 0, NULL);
    yyjson_mut_doc_free(doc);
    return json ? json : xstrdup("{}");
}

struct employee_input {
    const char *name;
    const char *personal_no;
    const char *address;
    const char *postal_code;
    const char *city;
    const char *bank_account;
    const char *salary_account;
    const char *email;
    int64_t monthly_salary_ore;
    int have_salary;
    int64_t tax_table;
    int have_table;
    int64_t tax_column;
    int have_column;
    int active;
    int have_active;
};

static void employee_input_read(struct req *r, struct employee_input *in)
{
    memset(in, 0, sizeof *in);
    in->name = arg_str(r->args, "name");
    in->personal_no = arg_str(r->args, "personal_no");
    in->address = arg_str(r->args, "address");
    in->postal_code = arg_str(r->args, "postal_code");
    in->city = arg_str(r->args, "city");
    in->bank_account = arg_str(r->args, "bank_account");
    in->salary_account = arg_str(r->args, "salary_account");
    in->email = arg_str(r->args, "email");
    in->have_salary = arg_int(r->args, "monthly_salary_ore",
                              &in->monthly_salary_ore);
    in->have_table = arg_int(r->args, "tax_table", &in->tax_table);
    in->have_column = arg_int(r->args, "tax_column", &in->tax_column);
    in->have_active = arg_bool(r->args, "active", &in->active);
}

static int employee_input_validate(struct req *r,
                                   const struct employee_input *in,
                                   int is_create)
{
    if (is_create && (!in->name || !*in->name)) {
        fail(r, "INVALID_ARGS", "name is required");
        return -1;
    }
    if (in->name && !*in->name) {
        fail(r, "INVALID_ARGS", "name cannot be empty");
        return -1;
    }
    if (in->name && strlen(in->name) > 200) {
        fail(r, "INVALID_ARGS", "name is too long");
        return -1;
    }
    if (is_create && !in->personal_no) {
        fail(r, "INVALID_ARGS", "personal_no is required");
        return -1;
    }
    if (in->personal_no && !personal_no_valid(in->personal_no)) {
        fail(r, "INVALID_ARGS",
             "personal_no must be 10 or 12 digits, with or without a hyphen");
        return -1;
    }
    static const char *const names[] = { "address", "postal_code", "city",
                                         "bank_account" };
    static const size_t maxlen[] = { 500, 32, 120, 64 };
    const char *values[] = { in->address, in->postal_code, in->city,
                             in->bank_account };
    for (size_t i = 0; i < sizeof maxlen / sizeof maxlen[0]; i++) {
        if (values[i] && strlen(values[i]) > maxlen[i]) {
            failf(r, "INVALID_ARGS", "%s is too long", names[i]);
            return -1;
        }
    }
    if (in->salary_account &&
        (!is_digits(in->salary_account) ||
         strlen(in->salary_account) > 10)) {
        fail(r, "INVALID_ARGS", "salary_account must be digits only");
        return -1;
    }
    if (in->have_salary && in->monthly_salary_ore < 0) {
        fail(r, "INVALID_ARGS", "monthly_salary_ore must be >= 0");
        return -1;
    }
    if (in->have_table && (in->tax_table < 29 || in->tax_table > 42)) {
        fail(r, "INVALID_ARGS", "tax_table must be between 29 and 42");
        return -1;
    }
    if (in->have_column && (in->tax_column < 1 || in->tax_column > 6)) {
        fail(r, "INVALID_ARGS", "tax_column must be between 1 and 6");
        return -1;
    }
    if (in->email) {
        size_t len = strlen(in->email);
        if (len > 254) {
            fail(r, "INVALID_ARGS", "email is too long");
            return -1;
        }
        for (const char *p = in->email; *p; p++) {
            unsigned char ch = (unsigned char)*p;
            if (ch < 32 || ch == 127) {
                fail(r, "INVALID_ARGS",
                     "email must not contain control characters");
                return -1;
            }
        }
    }
    return 0;
}

static yyjson_mut_val *employee_lookup(struct req *r, int64_t id, int *found)
{
    sqlite3_stmt *st = NULL;
    *found = 0;
    if (sqlite3_prepare_v2(
            r->db,
            "SELECT " EMPLOYEE_COLUMNS " FROM employees"
            " WHERE org_id=?1 AND id=?2",
            -1, &st, NULL) != SQLITE_OK) {
        *found = -1;
        db_error(r);
        return NULL;
    }
    sqlite3_bind_int64(st, 1, r->org_id);
    sqlite3_bind_int64(st, 2, id);
    if (sqlite3_step(st) != SQLITE_ROW) {
        sqlite3_finalize(st);
        return NULL;
    }
    yyjson_mut_val *o = employee_json(r, st);
    sqlite3_finalize(st);
    *found = 1;
    return o;
}

/* Encrypts the personnummer and rejects a duplicate (compared on the
   decrypted value, since the stored ciphertext has a fresh nonce). */
static int employee_personal_no_store(struct req *r, const char *plain,
                                      int64_t except_id, char **stored_out)
{
    sqlite3_stmt *st = NULL;
    if (sqlite3_prepare_v2(
            r->db,
            "SELECT personal_no_enc FROM employees WHERE org_id=?1 AND id<>?2",
            -1, &st, NULL) != SQLITE_OK) {
        db_error(r);
        return -1;
    }
    sqlite3_bind_int64(st, 1, r->org_id);
    sqlite3_bind_int64(st, 2, except_id);
    while (sqlite3_step(st) == SQLITE_ROW) {
        char *other = NULL;
        if (secret_decrypt(sq(sqlite3_column_text(st, 0)), &other) != 0)
            continue;
        int same = strcmp(other, plain) == 0;
        free(other);
        if (same) {
            sqlite3_finalize(st);
            fail(r, "CONFLICT",
                 "an employee with this personal_no already exists");
            return -1;
        }
    }
    sqlite3_finalize(st);
    if (!secret_available()) {
        fail(r, "INTERNAL", "BOKFD_SECRET_KEY is missing or invalid");
        return -1;
    }
    char *enc = NULL;
    if (secret_encrypt(plain, &enc) != 0) {
        fail(r, "INTERNAL", "could not encrypt the personnummer");
        return -1;
    }
    *stored_out = enc;
    return 0;
}

static yyjson_mut_val *h_employee_list(struct req *r)
{
    int active_only = 0;
    arg_bool(r->args, "active_only", &active_only);
    sqlite3_stmt *st = NULL;
    if (sqlite3_prepare_v2(
            r->db,
            "SELECT " EMPLOYEE_COLUMNS " FROM employees WHERE org_id=?1"
            " AND (?2=0 OR active=1) ORDER BY name COLLATE NOCASE, id",
            -1, &st, NULL) != SQLITE_OK)
        return db_error(r);
    sqlite3_bind_int64(st, 1, r->org_id);
    sqlite3_bind_int(st, 2, active_only);
    yyjson_mut_val *items = yyjson_mut_arr(r->rdoc);
    while (sqlite3_step(st) == SQLITE_ROW)
        yyjson_mut_arr_add_val(items, employee_json(r, st));
    sqlite3_finalize(st);
    yyjson_mut_val *out = yyjson_mut_obj(r->rdoc);
    yyjson_mut_obj_add_val(r->rdoc, out, "items", items);
    return out;
}

static yyjson_mut_val *h_employee_get(struct req *r)
{
    int64_t id = 0;
    if (!arg_int(r->args, "id", &id) || id <= 0)
        return fail(r, "INVALID_ARGS", "id is required");
    int found = 0;
    yyjson_mut_val *o = employee_lookup(r, id, &found);
    if (found < 0)
        return NULL;
    if (!found)
        return fail(r, "NOT_FOUND", "employee not found");
    return o;
}

static yyjson_mut_val *h_employee_create(struct req *r)
{
    struct employee_input in;
    employee_input_read(r, &in);
    if (employee_input_validate(r, &in, 1) != 0)
        return NULL;
    if (!in.have_salary)
        in.monthly_salary_ore = 0;
    if (!in.have_table)
        in.tax_table = 30;
    if (!in.have_column)
        in.tax_column = 1;

    char *stored = NULL;
    if (employee_personal_no_store(r, in.personal_no, 0, &stored) != 0)
        return NULL;

    char account[16];
    if (in.salary_account) {
        snprintf(account, sizeof account, "%s", in.salary_account);
    } else {
        char *setting = db_setting(r->db, r->org_id, "payroll_salary_account");
        snprintf(account, sizeof account, "%s",
                 setting && *setting ? setting : "7210");
        free(setting);
    }
    const char *address = in.address ? in.address : "";
    const char *postal = in.postal_code ? in.postal_code : "";
    const char *city = in.city ? in.city : "";
    const char *bank = in.bank_account ? in.bank_account : "";
    const char *email = in.email ? in.email : "";

    if (r->dry_run) {
        free(stored);
        char *masked = personal_no_mask(in.personal_no);
        yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
        yyjson_mut_obj_add_int(r->rdoc, o, "id", 0);
        yyjson_mut_obj_add_strcpy(r->rdoc, o, "name", in.name);
        yyjson_mut_obj_add_strcpy(r->rdoc, o, "personal_no", masked);
        free(masked);
        yyjson_mut_obj_add_strcpy(r->rdoc, o, "address", address);
        yyjson_mut_obj_add_strcpy(r->rdoc, o, "postal_code", postal);
        yyjson_mut_obj_add_strcpy(r->rdoc, o, "city", city);
        yyjson_mut_obj_add_strcpy(r->rdoc, o, "bank_account", bank);
        yyjson_mut_obj_add_strcpy(r->rdoc, o, "salary_account", account);
        yyjson_mut_obj_add_int(r->rdoc, o, "monthly_salary_ore",
                               in.monthly_salary_ore);
        yyjson_mut_obj_add_int(r->rdoc, o, "tax_table", in.tax_table);
        yyjson_mut_obj_add_int(r->rdoc, o, "tax_column", in.tax_column);
        yyjson_mut_obj_add_bool(r->rdoc, o, "active", true);
        yyjson_mut_obj_add_strcpy(r->rdoc, o, "email", email);
        yyjson_mut_obj_add_bool(r->rdoc, o, "dry_run", true);
        return o;
    }

    char ts[32];
    util_iso8601(util_now(), ts, sizeof ts);
    sqlite3_stmt *st = NULL;
    if (sqlite3_prepare_v2(
            r->db,
            "INSERT INTO employees(org_id,name,personal_no_enc,address,"
            "postal_code,city,bank_account,salary_account,monthly_salary_ore,"
            "tax_table,tax_column,created_at,email)"
            " VALUES(?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13)",
            -1, &st, NULL) != SQLITE_OK) {
        free(stored);
        return db_error(r);
    }
    sqlite3_bind_int64(st, 1, r->org_id);
    sqlite3_bind_text(st, 2, in.name, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(st, 3, stored, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(st, 4, address, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(st, 5, postal, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(st, 6, city, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(st, 7, bank, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(st, 8, account, -1, SQLITE_TRANSIENT);
    sqlite3_bind_int64(st, 9, in.monthly_salary_ore);
    sqlite3_bind_int64(st, 10, in.tax_table);
    sqlite3_bind_int64(st, 11, in.tax_column);
    sqlite3_bind_text(st, 12, ts, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(st, 13, email, -1, SQLITE_TRANSIENT);
    int rc = sqlite3_step(st);
    sqlite3_finalize(st);
    free(stored);
    if (rc != SQLITE_DONE) {
        if ((rc & 0xff) == SQLITE_CONSTRAINT)
            return fail(r, "CONFLICT",
                        "an employee with this personal_no already exists");
        return db_sqlite_error(r);
    }
    int64_t id = db_last_id(r->db);
    char *reqjson = employee_audit_json(r->args);
    audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id,
                 "employee.create", reqjson, "OK", NULL);
    free(reqjson);
    int found = 0;
    yyjson_mut_val *o = employee_lookup(r, id, &found);
    if (found < 0)
        return NULL;
    if (!found)
        return fail(r, "INTERNAL", "could not read the new employee");
    return o;
}

static yyjson_mut_val *h_employee_update(struct req *r)
{
    int64_t id = 0;
    if (!arg_int(r->args, "id", &id) || id <= 0)
        return fail(r, "INVALID_ARGS", "id is required");
    struct employee_input in;
    employee_input_read(r, &in);
    if (employee_input_validate(r, &in, 0) != 0)
        return NULL;
    if (!in.name && !in.personal_no && !in.address && !in.postal_code &&
        !in.city && !in.bank_account && !in.salary_account && !in.email &&
        !in.have_salary && !in.have_table && !in.have_column &&
        !in.have_active)
        return fail(r, "INVALID_ARGS", "nothing to update");

    int found = 0;
    yyjson_mut_val *existing = employee_lookup(r, id, &found);
    (void)existing;
    if (found < 0)
        return NULL;
    if (!found)
        return fail(r, "NOT_FOUND", "employee not found");

    char *stored = NULL;
    if (in.personal_no &&
        employee_personal_no_store(r, in.personal_no, id, &stored) != 0)
        return NULL;

    if (r->dry_run) {
        sqlite3_stmt *st = NULL;
        if (sqlite3_prepare_v2(
                r->db,
                "SELECT id,COALESCE(?3,name),COALESCE(?4,personal_no_enc),"
                "COALESCE(?5,address),COALESCE(?6,postal_code),"
                "COALESCE(?7,city),COALESCE(?8,bank_account),"
                "COALESCE(?9,salary_account),"
                "CASE WHEN ?10<0 THEN monthly_salary_ore ELSE ?10 END,"
                "CASE WHEN ?11<0 THEN tax_table ELSE ?11 END,"
                "CASE WHEN ?12<0 THEN tax_column ELSE ?12 END,"
                "CASE WHEN ?13<0 THEN active ELSE ?13 END,"
                "created_at,COALESCE(updated_at,''),COALESCE(?14,email)"
                " FROM employees WHERE org_id=?1 AND id=?2",
                -1, &st, NULL) != SQLITE_OK) {
            free(stored);
            return db_error(r);
        }
        sqlite3_bind_int64(st, 1, r->org_id);
        sqlite3_bind_int64(st, 2, id);
        bind_text_or_null(st, 3, in.name);
        bind_text_or_null(st, 4, stored);
        bind_text_or_null(st, 5, in.address);
        bind_text_or_null(st, 6, in.postal_code);
        bind_text_or_null(st, 7, in.city);
        bind_text_or_null(st, 8, in.bank_account);
        bind_text_or_null(st, 9, in.salary_account);
        sqlite3_bind_int64(st, 10, in.have_salary ? in.monthly_salary_ore
                                                  : -1);
        sqlite3_bind_int64(st, 11, in.have_table ? in.tax_table : -1);
        sqlite3_bind_int64(st, 12, in.have_column ? in.tax_column : -1);
        sqlite3_bind_int64(st, 13, in.have_active ? in.active : -1);
        bind_text_or_null(st, 14, in.email);
        yyjson_mut_val *o = NULL;
        if (sqlite3_step(st) == SQLITE_ROW)
            o = employee_json(r, st);
        sqlite3_finalize(st);
        free(stored);
        if (!o)
            return fail(r, "NOT_FOUND", "employee not found");
        yyjson_mut_obj_add_bool(r->rdoc, o, "dry_run", true);
        return o;
    }

    char ts[32];
    util_iso8601(util_now(), ts, sizeof ts);
    sqlite3_stmt *st = NULL;
    if (sqlite3_prepare_v2(
            r->db,
            "UPDATE employees SET"
            " name=COALESCE(?3,name),"
            " personal_no_enc=COALESCE(?4,personal_no_enc),"
            " address=COALESCE(?5,address),"
            " postal_code=COALESCE(?6,postal_code),"
            " city=COALESCE(?7,city),"
            " bank_account=COALESCE(?8,bank_account),"
            " salary_account=COALESCE(?9,salary_account),"
            " monthly_salary_ore=CASE WHEN ?10<0 THEN monthly_salary_ore"
            "   ELSE ?10 END,"
            " tax_table=CASE WHEN ?11<0 THEN tax_table ELSE ?11 END,"
            " tax_column=CASE WHEN ?12<0 THEN tax_column ELSE ?12 END,"
            " active=CASE WHEN ?13<0 THEN active ELSE ?13 END,"
            " email=COALESCE(?14,email),"
            " updated_at=?15 WHERE org_id=?1 AND id=?2",
            -1, &st, NULL) != SQLITE_OK) {
        free(stored);
        return db_error(r);
    }
    sqlite3_bind_int64(st, 1, r->org_id);
    sqlite3_bind_int64(st, 2, id);
    bind_text_or_null(st, 3, in.name);
    bind_text_or_null(st, 4, stored);
    bind_text_or_null(st, 5, in.address);
    bind_text_or_null(st, 6, in.postal_code);
    bind_text_or_null(st, 7, in.city);
    bind_text_or_null(st, 8, in.bank_account);
    bind_text_or_null(st, 9, in.salary_account);
    sqlite3_bind_int64(st, 10, in.have_salary ? in.monthly_salary_ore : -1);
    sqlite3_bind_int64(st, 11, in.have_table ? in.tax_table : -1);
    sqlite3_bind_int64(st, 12, in.have_column ? in.tax_column : -1);
    sqlite3_bind_int64(st, 13, in.have_active ? in.active : -1);
    bind_text_or_null(st, 14, in.email);
    sqlite3_bind_text(st, 15, ts, -1, SQLITE_TRANSIENT);
    int rc = sqlite3_step(st);
    sqlite3_finalize(st);
    free(stored);
    if (rc != SQLITE_DONE) {
        if ((rc & 0xff) == SQLITE_CONSTRAINT)
            return fail(r, "CONFLICT",
                        "an employee with this personal_no already exists");
        return db_sqlite_error(r);
    }
    if (sqlite3_changes(r->db) == 0)
        return fail(r, "NOT_FOUND", "employee not found");
    char *reqjson = employee_audit_json(r->args);
    audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id,
                 "employee.update", reqjson, "OK", NULL);
    free(reqjson);
    found = 0;
    yyjson_mut_val *o = employee_lookup(r, id, &found);
    if (found < 0)
        return NULL;
    if (!found)
        return fail(r, "INTERNAL", "could not read the employee");
    return o;
}

static yyjson_mut_val *h_employee_archive(struct req *r)
{
    int64_t id = 0;
    int active = 0;
    if (!arg_int(r->args, "id", &id) || id <= 0 ||
        !arg_bool(r->args, "active", &active))
        return fail(r, "INVALID_ARGS", "id and active are required");
    int found = 0;
    yyjson_mut_val *existing = employee_lookup(r, id, &found);
    (void)existing;
    if (found < 0)
        return NULL;
    if (!found)
        return fail(r, "NOT_FOUND", "employee not found");

    if (r->dry_run) {
        yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
        yyjson_mut_obj_add_int(r->rdoc, o, "id", id);
        yyjson_mut_obj_add_bool(r->rdoc, o, "active", active != 0);
        yyjson_mut_obj_add_bool(r->rdoc, o, "dry_run", true);
        return o;
    }

    char ts[32];
    util_iso8601(util_now(), ts, sizeof ts);
    sqlite3_stmt *st = NULL;
    if (sqlite3_prepare_v2(
            r->db,
            "UPDATE employees SET active=?3, updated_at=?4"
            " WHERE org_id=?1 AND id=?2",
            -1, &st, NULL) != SQLITE_OK)
        return db_error(r);
    sqlite3_bind_int64(st, 1, r->org_id);
    sqlite3_bind_int64(st, 2, id);
    sqlite3_bind_int(st, 3, active);
    sqlite3_bind_text(st, 4, ts, -1, SQLITE_TRANSIENT);
    int rc = sqlite3_step(st);
    sqlite3_finalize(st);
    if (rc != SQLITE_DONE)
        return db_sqlite_error(r);
    if (sqlite3_changes(r->db) == 0)
        return fail(r, "NOT_FOUND", "employee not found");
    char *reqjson = employee_audit_json(r->args);
    audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id,
                 "employee.archive", reqjson, "OK", NULL);
    free(reqjson);
    yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
    yyjson_mut_obj_add_int(r->rdoc, o, "id", id);
    yyjson_mut_obj_add_bool(r->rdoc, o, "active", active != 0);
    return o;
}

static const struct cmd_arg args_employee_list[] = {
    { "active_only", ARG_BOOL, 0, NULL, NULL, "Only active employees" },
};

static const struct cmd_arg args_employee_get[] = {
    { "id", ARG_INT, 1, NULL, NULL, "Employee id" },
};

static const struct cmd_arg args_employee_create[] = {
    { "name", ARG_STR, 1, NULL, NULL, "Employee name" },
    { "personal_no", ARG_STR, 1, NULL, NULL,
      "10 or 12 digits, with or without a hyphen; encrypted at rest" },
    { "address", ARG_STR, 0, NULL, NULL, "Street address" },
    { "postal_code", ARG_STR, 0, NULL, NULL, "Postal code" },
    { "city", ARG_STR, 0, NULL, NULL, "City" },
    { "bank_account", ARG_STR, 0, NULL, NULL, "Bank account for the net pay" },
    { "email", ARG_STR, 0, NULL, NULL, "E-mail for lönebesked" },
    { "salary_account", ARG_STR, 0, NULL, NULL,
      "Salary account, digits only; defaults to payroll_salary_account" },
    { "monthly_salary_ore", ARG_INT, 0, "0", NULL,
      "Monthly gross salary in öre" },
    { "tax_table", ARG_INT, 0, "30", NULL, "Skatteverket table 29-42" },
    { "tax_column", ARG_INT, 0, "1", NULL, "Tax column 1-6" },
};

static const struct cmd_arg args_employee_update[] = {
    { "id", ARG_INT, 1, NULL, NULL, "Employee id" },
    { "name", ARG_STR, 0, NULL, NULL, "Employee name" },
    { "personal_no", ARG_STR, 0, NULL, NULL,
      "New personnummer, encrypted at rest" },
    { "address", ARG_STR, 0, NULL, NULL, "Street address" },
    { "postal_code", ARG_STR, 0, NULL, NULL, "Postal code" },
    { "city", ARG_STR, 0, NULL, NULL, "City" },
    { "bank_account", ARG_STR, 0, NULL, NULL, "Bank account for the net pay" },
    { "email", ARG_STR, 0, NULL, NULL, "E-mail for lönebesked" },
    { "salary_account", ARG_STR, 0, NULL, NULL, "Salary account, digits only" },
    { "monthly_salary_ore", ARG_INT, 0, NULL, NULL,
      "Monthly gross salary in öre" },
    { "tax_table", ARG_INT, 0, NULL, NULL, "Skatteverket table 29-42" },
    { "tax_column", ARG_INT, 0, NULL, NULL, "Tax column 1-6" },
    { "active", ARG_BOOL, 0, NULL, NULL, "Active flag" },
};

static const struct cmd_arg args_employee_archive[] = {
    { "id", ARG_INT, 1, NULL, NULL, "Employee id" },
    { "active", ARG_BOOL, 1, NULL, NULL, "false archives, true reactivates" },
};

const struct command g_cmd_employees[] = {
    { "employee.list", "List employees ordered by name", PERM_READ, 1, 0, 0,
      h_employee_list, CMD_ARGS(args_employee_list) },
    { "employee.get", "Get one employee (personnummer masked)", PERM_READ, 1,
      0, 0, h_employee_get, CMD_ARGS(args_employee_get) },
    { "employee.create", "Create an employee", PERM_WRITE, 1, 1, 1,
      h_employee_create, CMD_ARGS(args_employee_create) },
    { "employee.update", "Update an employee (merged)", PERM_WRITE, 1, 1, 1,
      h_employee_update, CMD_ARGS(args_employee_update) },
    { "employee.archive", "Archive or reactivate an employee", PERM_WRITE, 1,
      1, 1, h_employee_archive, CMD_ARGS(args_employee_archive) },
};

const struct cmd_table g_cmd_table_employees = {
    g_cmd_employees, sizeof g_cmd_employees / sizeof g_cmd_employees[0]
};