payroll: lönebesked PDF and mail (schema v11)

1 files changed, 33 insertions, 4 deletions

diff --git a/src/db.c b/src/db.c
index f77d9e5..95ecf3f 100644
--- a/src/db.c
+++ b/src/db.c
@@ -413,10 +413,11 @@ static const char SCHEMA_V2[] =
     "CREATE INDEX idx_template_rows ON voucher_template_rows(org_id,"
     " template_id, line_no);\n";
 
-/* v10: payroll — the employee register, the monthly runs with their lines
-   and Skatteverket's national tax tables. tax_tables and tax_table_meta are
-   reference data, not tenant data, so they carry no org_id. Shared by the
-   fresh schema and the v10 migration. */
+/* v10/v11: payroll — the employee register, the monthly runs with their
+   lines and Skatteverket's national tax tables. tax_tables and
+   tax_table_meta are reference data, not tenant data, so they carry no
+   org_id. Shared by the fresh schema and the v10 migration; v11 adds the
+   employee e-mail column. */
 static const char SCHEMA_PAYROLL[] =
     "CREATE TABLE IF NOT EXISTS employees ("
     "  org_id             INTEGER NOT NULL REFERENCES orgs(id),"
@@ -437,6 +438,7 @@ static const char SCHEMA_PAYROLL[] =
     "  active             INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)),"
     "  created_at         TEXT NOT NULL,"
     "  updated_at         TEXT,"
+    "  email              TEXT NOT NULL DEFAULT '',"
     "  UNIQUE (org_id, id)"
     ") STRICT;\n"
 
@@ -886,6 +888,29 @@ static int db_upgrade_v10(sqlite3 *db, char **err)
         err);
 }
 
+/* v11: the employee e-mail address for lönebesked delivery. Fresh
+   databases already carry the column, so the ALTER is skipped when a
+   downgraded database (the migration test replays v3) still has it. */
+static int db_upgrade_v11(sqlite3 *db, char **err)
+{
+    sqlite3_stmt *st = NULL;
+    if (sqlite3_prepare_v2(db,
+                           "SELECT count(*) FROM pragma_table_info('employees')"
+                           " WHERE name='email'",
+                           -1, &st, NULL) != SQLITE_OK) {
+        set_err(err, "database error");
+        return -1;
+    }
+    int have = sqlite3_step(st) == SQLITE_ROW && sqlite3_column_int(st, 0) > 0;
+    sqlite3_finalize(st);
+    if (have)
+        return 0;
+    return db_exec(db,
+                   "ALTER TABLE employees ADD COLUMN email TEXT NOT NULL"
+                   " DEFAULT ''",
+                   err);
+}
+
 static int db_upgrade(sqlite3 *db, int from, char **err)
 {
     if (db_exec(db, "BEGIN IMMEDIATE", err) != 0)
@@ -926,6 +951,10 @@ static int db_upgrade(sqlite3 *db, int from, char **err)
         db_exec(db, "ROLLBACK", NULL);
         return -1;
     }
+    if (from < 11 && db_upgrade_v11(db, err) != 0) {
+        db_exec(db, "ROLLBACK", NULL);
+        return -1;
+    }
     char *sql = sqlite3_mprintf(
         "UPDATE meta SET value='%d' WHERE key='schema_version'",
         BOKF_SCHEMA_VERSION);