invoices: text rows, duplicate and payment link (schema v12); smtp address checksv0.1.62

8 files changed, 270 insertions, 9 deletions

diff --git a/src/cmd_invoices.c b/src/cmd_invoices.c
index 73273dc..2a6a9c6 100644
--- a/src/cmd_invoices.c
+++ b/src/cmd_invoices.c
@@ -11,6 +11,7 @@
 #include "db.h"
 #include "invoice.h"
 #include "ledger.h"
+#include "mail.h"
 #include "pdf.h"
 #include "secret.h"
 #include "smtp.h"
@@ -139,6 +140,7 @@ struct draft_line {
     const char *note;
     const char *vat_code;
     char account[16];
+    int is_text;
 };
 
 struct invoice_draft {
@@ -211,6 +213,21 @@ static int draft_line_parse(struct req *r, yyjson_val *item, size_t no,
         failf(r, "INVALID_ARGS", "row %zu: description is required", no);
         return -1;
     }
+    int is_text = 0;
+    arg_bool(item, "text", &is_text);
+    if (is_text) {
+        l->article_no = NULL;
+        l->description = description;
+        l->quantity_milli = 1;
+        l->unit = "";
+        l->unit_price_ore = 0;
+        l->amount_ore = 0;
+        l->note = "";
+        l->vat_code = "0";
+        l->is_text = 1;
+        snprintf(l->account, sizeof l->account, "%s", default_account);
+        return 0;
+    }
     const char *qty = arg_str(item, "quantity");
     int64_t quantity_milli = 0;
     if (parse_quantity(qty, &quantity_milli) != 0) {
@@ -460,6 +477,7 @@ static int invoice_view_fill(struct req *r, const struct invoice_draft *d,
         v->lines[i].amount_ore = d->lines[i].amount_ore;
         v->lines[i].note = d->lines[i].note;
         v->lines[i].vat_code = d->lines[i].vat_code;
+        v->lines[i].is_text = d->lines[i].is_text;
     }
 
     v->doc.seller.name = v->seller_name;
@@ -670,8 +688,8 @@ static int invoice_store_invoice(struct req *r, const struct invoice_draft *d,
                 r->db,
                 "INSERT INTO invoice_rows(org_id,invoice_id,line_no,article_no,"
                 "description,quantity_milli,unit,unit_price_ore,amount_ore,note,"
-                "vat_code,account)"
-                " VALUES(?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12)",
+                "vat_code,account,is_text)"
+                " VALUES(?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13)",
                 -1, &st, NULL) != SQLITE_OK) {
             db_error(r);
             return -1;
@@ -689,6 +707,7 @@ static int invoice_store_invoice(struct req *r, const struct invoice_draft *d,
         sqlite3_bind_text(st, 10, l->note ? l->note : "", -1, SQLITE_TRANSIENT);
         sqlite3_bind_text(st, 11, l->vat_code, -1, SQLITE_TRANSIENT);
         sqlite3_bind_text(st, 12, l->account, -1, SQLITE_TRANSIENT);
+        sqlite3_bind_int(st, 13, l->is_text);
         rc = sqlite3_step(st);
         sqlite3_finalize(st);
         if (rc != SQLITE_DONE) {
@@ -892,12 +911,12 @@ static yyjson_mut_val *invoice_row_json(struct req *r, sqlite3_stmt *st)
     return db_row_json(r->rdoc, st,
                        "line_no:i,article_no:s,description:s,"
                        "quantity_milli:i,unit:s,unit_price_ore:i,"
-                       "amount_ore:i,note:s,vat_code:s,account:s");
+                       "amount_ore:i,note:s,vat_code:s,account:s,is_text:b");
 }
 
 #define INVOICE_ROW_COLUMNS                                               \
     "line_no,article_no,description,quantity_milli,unit,unit_price_ore,"  \
-    "amount_ore,note,vat_code,account"
+    "amount_ore,note,vat_code,account,is_text"
 
 static yyjson_mut_val *h_invoice_get(struct req *r)
 {
@@ -910,9 +929,13 @@ static yyjson_mut_val *h_invoice_get(struct req *r)
             "SELECT i.id,i.customer_id,c.name,i.number,i.ocr,i.invoice_date,"
             "i.due_date,i.delivery_date,i.your_ref,i.our_ref,i.notes,i.net_ore,"
             "i.vat_ore,i.total_ore,i.status,i.document_id,i.voucher_id,"
-            "i.last_sent_at,i.last_sent_to,i.created_at,i.created_by"
+            "i.last_sent_at,i.last_sent_to,i.created_at,i.created_by,"
+            "i.paid_date,i.payment_voucher_id,COALESCE(pv.series,''),"
+            "COALESCE(pv.number,0)"
             " FROM invoices i JOIN customers c"
             " ON c.org_id=i.org_id AND c.id=i.customer_id"
+            " LEFT JOIN vouchers pv"
+            " ON pv.org_id=i.org_id AND pv.id=i.payment_voucher_id"
             " WHERE i.org_id=?1 AND i.id=?2",
             -1, &st, NULL) != SQLITE_OK)
         return db_error(r);
@@ -975,6 +998,17 @@ static yyjson_mut_val *h_invoice_get(struct req *r)
                               sq(sqlite3_column_text(st, 19)));
     yyjson_mut_obj_add_int(r->rdoc, o, "created_by",
                            sqlite3_column_int64(st, 20));
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "paid_date",
+                              sq(sqlite3_column_text(st, 21)));
+    if (sqlite3_column_type(st, 22) == SQLITE_NULL)
+        yyjson_mut_obj_add_null(r->rdoc, o, "payment_voucher_id");
+    else
+        yyjson_mut_obj_add_int(r->rdoc, o, "payment_voucher_id",
+                               sqlite3_column_int64(st, 22));
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "paid_voucher_series",
+                              sq(sqlite3_column_text(st, 23)));
+    yyjson_mut_obj_add_int(r->rdoc, o, "paid_voucher_number",
+                           sqlite3_column_int64(st, 24));
     sqlite3_finalize(st);
 
     yyjson_mut_val *rows = yyjson_mut_arr(r->rdoc);
@@ -1010,7 +1044,7 @@ static yyjson_mut_val *h_invoice_list(struct req *r)
             r->db,
             "SELECT i.id,i.number,i.ocr,i.customer_id,c.name,i.invoice_date,"
             "i.due_date,i.total_ore,i.status,i.document_id,i.voucher_id,"
-            "i.last_sent_at,i.last_sent_to"
+            "i.last_sent_at,i.last_sent_to,i.paid_date"
             " FROM invoices i JOIN customers c"
             " ON c.org_id=i.org_id AND c.id=i.customer_id"
             " WHERE i.org_id=?1"
@@ -1063,6 +1097,8 @@ static yyjson_mut_val *h_invoice_list(struct req *r)
         else
             yyjson_mut_obj_add_strcpy(r->rdoc, o, "last_sent_to",
                                       sq(sqlite3_column_text(st, 12)));
+        yyjson_mut_obj_add_strcpy(r->rdoc, o, "paid_date",
+                                  sq(sqlite3_column_text(st, 13)));
     }
     sqlite3_finalize(st);
     yyjson_mut_val *out = yyjson_mut_obj(r->rdoc);
@@ -1215,6 +1251,10 @@ static yyjson_mut_val *h_invoice_send(struct req *r)
         fail(r, "SMTP_NOT_CONFIGURED", "smtp_host and smtp_from must be set");
         goto done;
     }
+    if (!mail_addr_valid(smtp_from)) {
+        fail(r, "SMTP_NOT_CONFIGURED", "smtp_from must be an email address");
+        goto done;
+    }
     const char *user = smtp_user && *smtp_user ? smtp_user : "";
     if (*user) {
         if (!smtp_password || !*smtp_password) {
@@ -1354,6 +1394,122 @@ static const struct cmd_arg args_invoice_sequence_set[] = {
     { "next_number", ARG_INT, 1, NULL, NULL, "Next invoice number" },
 };
 
+static const struct cmd_arg args_invoice_pay[] = {
+    { "id", ARG_INT, 1, NULL, NULL, "Invoice id" },
+    { "voucher_id", ARG_INT, 1, NULL, NULL, "Payment voucher id" },
+};
+
+/* Marks an invoice paid and links the voucher that settles it. The voucher
+   must credit the invoice receivable account with the invoice total. */
+static yyjson_mut_val *h_invoice_pay(struct req *r)
+{
+    int64_t id = 0, voucher_id = 0;
+    if (!arg_int(r->args, "id", &id) || id <= 0)
+        return fail(r, "INVALID_ARGS", "id is required");
+    if (!arg_int(r->args, "voucher_id", &voucher_id) || voucher_id <= 0)
+        return fail(r, "INVALID_ARGS", "voucher_id is required");
+
+    sqlite3_stmt *st = NULL;
+    if (sqlite3_prepare_v2(
+            r->db,
+            "SELECT number,total_ore,status,paid_date FROM invoices"
+            " 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);
+    if (sqlite3_step(st) != SQLITE_ROW) {
+        sqlite3_finalize(st);
+        return fail(r, "NOT_FOUND", "invoice not found");
+    }
+    int64_t number = sqlite3_column_int64(st, 0);
+    int64_t total = sqlite3_column_int64(st, 1);
+    char status[16], paid[16];
+    snprintf(status, sizeof status, "%s", sq(sqlite3_column_text(st, 2)));
+    snprintf(paid, sizeof paid, "%s", sq(sqlite3_column_text(st, 3)));
+    sqlite3_finalize(st);
+    if (*paid)
+        return fail(r, "CONFLICT", "invoice is already paid");
+    if (strcmp(status, "credited") == 0)
+        return fail(r, "INVALID_ARGS", "a credited invoice cannot be paid");
+
+    char receivable[16];
+    db_setting_copy(r->db, r->org_id, "invoice_receivable_account", "1510",
+                    receivable, sizeof receivable);
+
+    char date[16], series[16] = "";
+    int64_t vnumber = 0;
+    if (sqlite3_prepare_v2(
+            r->db,
+            "SELECT date,series,number FROM vouchers 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, voucher_id);
+    if (sqlite3_step(st) != SQLITE_ROW) {
+        sqlite3_finalize(st);
+        return fail(r, "NOT_FOUND", "voucher not found");
+    }
+    snprintf(date, sizeof date, "%s", sq(sqlite3_column_text(st, 0)));
+    snprintf(series, sizeof series, "%s", sq(sqlite3_column_text(st, 1)));
+    vnumber = sqlite3_column_int64(st, 2);
+    sqlite3_finalize(st);
+
+    if (sqlite3_prepare_v2(
+            r->db,
+            "SELECT COALESCE(SUM(r.credit_ore),0) FROM voucher_rows r"
+            " JOIN accounts a ON a.org_id=r.org_id AND a.id=r.account_id"
+            " WHERE r.org_id=?1 AND r.voucher_id=?2 AND a.number=?3",
+            -1, &st, NULL) != SQLITE_OK)
+        return db_error(r);
+    sqlite3_bind_int64(st, 1, r->org_id);
+    sqlite3_bind_int64(st, 2, voucher_id);
+    sqlite3_bind_text(st, 3, receivable, -1, SQLITE_TRANSIENT);
+    int64_t credited = 0;
+    if (sqlite3_step(st) == SQLITE_ROW)
+        credited = sqlite3_column_int64(st, 0);
+    sqlite3_finalize(st);
+    if (credited != total)
+        return failf(r, "INVALID_ARGS",
+                     "the payment voucher must credit %s with the invoice"
+                     " total (%lld), not (%lld)",
+                     receivable, (long long)total, (long long)credited);
+
+    if (!r->dry_run) {
+        if (sqlite3_prepare_v2(
+                r->db,
+                "UPDATE invoices SET paid_date=?3,payment_voucher_id=?4"
+                " WHERE org_id=?1 AND id=?2 AND paid_date=''",
+                -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_text(st, 3, date, -1, SQLITE_TRANSIENT);
+        sqlite3_bind_int64(st, 4, voucher_id);
+        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, "CONFLICT", "invoice is already paid");
+        char *reqjson = audit_args_json(r->args);
+        audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id,
+                     "invoice.pay", 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_int(r->rdoc, o, "number", number);
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "paid_date", date);
+    yyjson_mut_obj_add_int(r->rdoc, o, "payment_voucher_id", voucher_id);
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "voucher_series", series);
+    yyjson_mut_obj_add_int(r->rdoc, o, "voucher_number", vnumber);
+    if (r->dry_run)
+        yyjson_mut_obj_add_bool(r->rdoc, o, "dry_run", true);
+    return o;
+}
+
 static const struct cmd_arg args_invoice_draft[] = {
     { "customer_id", ARG_INT, 1, NULL, NULL, "Customer id" },
     { "invoice_date", ARG_DATE, 1, NULL, NULL, "Invoice date (YYYY-MM-DD)" },
@@ -1364,7 +1520,7 @@ static const struct cmd_arg args_invoice_draft[] = {
     { "notes", ARG_STR, 0, NULL, NULL, "Free-text notes" },
     { "rows", ARG_JSON, 1, NULL, NULL,
       "Array of {article_no,description,quantity,unit,unit_price_ore,note,"
-      "vat_code,account}" },
+      "vat_code,account,text}; text rows carry only the description" },
 };
 
 static const struct cmd_arg args_invoice_get[] = {
@@ -1401,6 +1557,8 @@ const struct command g_cmd_invoices[] = {
       h_invoice_pdf, CMD_ARGS(args_invoice_get) },
     { "invoice.send", "E-mail the stored invoice PDF to the customer",
       PERM_WRITE, 1, 1, 1, h_invoice_send, CMD_ARGS(args_invoice_send) },
+    { "invoice.pay", "Mark an invoice paid with a payment voucher",
+      PERM_WRITE, 1, 1, 1, h_invoice_pay, CMD_ARGS(args_invoice_pay) },
 };
 
 const struct cmd_table g_cmd_table_invoices = {
diff --git a/src/cmd_settings.c b/src/cmd_settings.c
index 28167de..f41f904 100644
--- a/src/cmd_settings.c
+++ b/src/cmd_settings.c
@@ -8,6 +8,7 @@
 #include "audit.h"
 #include "config.h"
 #include "db.h"
+#include "mail.h"
 #include "secret.h"
 #include "util.h"
 
@@ -217,6 +218,10 @@ static yyjson_mut_val *h_settings_set(struct req *r)
         strcmp(value, "tls") != 0 && strcmp(value, "plain") != 0)
         return failf(r, "INVALID_ARGS", "%s must be starttls, tls or plain",
                      key);
+    if ((strcmp(key, "smtp_from") == 0 ||
+         strcmp(key, "smtp_reply_to") == 0) &&
+        !mail_addr_valid(value))
+        return failf(r, "INVALID_ARGS", "%s must be an email address", key);
     if (r->dry_run)
         return settings_result(r, key, value, 1);
     sqlite3_stmt *st = NULL;
diff --git a/src/db.c b/src/db.c
index 4618a73..200fff8 100644
--- a/src/db.c
+++ b/src/db.c
@@ -351,6 +351,8 @@ static const char SCHEMA_V1[] =
     "                  CHECK (status IN ('issued','credited')),"
     "  document_id   INTEGER,"
     "  voucher_id    INTEGER,"
+    "  paid_date     TEXT NOT NULL DEFAULT '',"
+    "  payment_voucher_id INTEGER,"
     "  last_sent_at  TEXT,"
     "  last_sent_to  TEXT,"
     "  created_at    TEXT NOT NULL,"
@@ -359,7 +361,9 @@ static const char SCHEMA_V1[] =
     "  UNIQUE (org_id, number),"
     "  FOREIGN KEY (org_id, customer_id) REFERENCES customers(org_id, id),"
     "  FOREIGN KEY (org_id, document_id) REFERENCES attachments(org_id, id),"
-    "  FOREIGN KEY (org_id, voucher_id) REFERENCES vouchers(org_id, id)"
+    "  FOREIGN KEY (org_id, voucher_id) REFERENCES vouchers(org_id, id),"
+    "  FOREIGN KEY (org_id, payment_voucher_id)"
+    "    REFERENCES vouchers(org_id, id)"
     ") STRICT;\n"
 
     "CREATE TABLE invoice_rows ("
@@ -377,6 +381,7 @@ static const char SCHEMA_V1[] =
     "  vat_code       TEXT NOT NULL DEFAULT '25'"
     "                   CHECK (vat_code IN ('25','12','6','0','rc','eu')),"
     "  account        TEXT NOT NULL DEFAULT '',"
+    "  is_text        INTEGER NOT NULL DEFAULT 0,"
     "  UNIQUE (org_id, id),"
     "  UNIQUE (org_id, invoice_id, line_no),"
     "  FOREIGN KEY (org_id, invoice_id) REFERENCES invoices(org_id, id)"
@@ -911,6 +916,49 @@ static int db_upgrade_v11(sqlite3 *db, char **err)
                    err);
 }
 
+static int db_column_exists(sqlite3 *db, const char *table, const char *col,
+                            char **err)
+{
+    sqlite3_stmt *st = NULL;
+    char sql[160];
+    snprintf(sql, sizeof sql,
+             "SELECT count(*) FROM pragma_table_info('%s') WHERE name='%s'",
+             table, col);
+    if (sqlite3_prepare_v2(db, sql, -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);
+    return have;
+}
+
+static int db_add_column(sqlite3 *db, const char *table, const char *col,
+                         const char *decl, char **err)
+{
+    int have = db_column_exists(db, table, col, err);
+    if (have != 0)
+        return have < 0 ? -1 : 0;
+    char sql[384];
+    snprintf(sql, sizeof sql, "ALTER TABLE %s ADD COLUMN %s %s", table, col,
+             decl);
+    return db_exec(db, sql, err);
+}
+
+/* v12: invoice text rows (is_text) and the payment link (paid_date,
+   payment_voucher_id). Fresh databases already carry the columns. */
+static int db_upgrade_v12(sqlite3 *db, char **err)
+{
+    if (db_add_column(db, "invoice_rows", "is_text",
+                      "INTEGER NOT NULL DEFAULT 0", err) != 0)
+        return -1;
+    if (db_add_column(db, "invoices", "paid_date",
+                      "TEXT NOT NULL DEFAULT ''", err) != 0)
+        return -1;
+    return db_add_column(db, "invoices", "payment_voucher_id", "INTEGER",
+                         err);
+}
+
 static int db_upgrade(sqlite3 *db, int from, char **err)
 {
     if (db_exec(db, "BEGIN IMMEDIATE", err) != 0)
@@ -955,6 +1003,10 @@ static int db_upgrade(sqlite3 *db, int from, char **err)
         db_exec(db, "ROLLBACK", NULL);
         return -1;
     }
+    if (from < 12 && db_upgrade_v12(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);
diff --git a/src/db.h b/src/db.h
index e8d891b..b541f66 100644
--- a/src/db.h
+++ b/src/db.h
@@ -5,7 +5,7 @@
 #include <stddef.h>
 #include <stdint.h>
 
-#define BOKF_SCHEMA_VERSION 11
+#define BOKF_SCHEMA_VERSION 12
 
 int db_open(const char *path, sqlite3 **out, char **err);
 int db_migrate(sqlite3 *db, char **err);
diff --git a/src/invoice.c b/src/invoice.c
index 622c05a..34b2a85 100644
--- a/src/invoice.c
+++ b/src/invoice.c
@@ -430,6 +430,24 @@ static void draw_table(struct pdf *p, const struct invoice_doc *d)
         const char *s = l->description ? l->description : "";
         size_t line = 0;
 
+        if (l->is_text) {
+            for (;;) {
+                const char *nl = strchr(s, '\n');
+
+                if (nl) {
+                    put_desc_line(p, y + desc_offset(line), s,
+                                  (size_t)(nl - s));
+                    s = nl + 1;
+                    line++;
+                } else {
+                    put_desc_line(p, y + desc_offset(line), s, strlen(s));
+                    break;
+                }
+            }
+            y += (double)(line + 2) * ROW_STEP;
+            continue;
+        }
+
         fmt_quantity(l->quantity_milli, qty, sizeof qty);
         fmt_kronor(l->unit_price_ore, price, sizeof price, 0, 0);
         fmt_kronor(l->amount_ore, amount, sizeof amount, 0, 0);
diff --git a/src/invoice.h b/src/invoice.h
index c92677d..7d64f21 100644
--- a/src/invoice.h
+++ b/src/invoice.h
@@ -33,6 +33,7 @@ struct invoice_line {
     int64_t amount_ore;
     const char *note;
     const char *vat_code;
+    int is_text;
 };
 
 struct invoice_doc {
diff --git a/src/mail.c b/src/mail.c
index ca35f6d..e1b2131 100644
--- a/src/mail.c
+++ b/src/mail.c
@@ -38,6 +38,21 @@ static void set_err(char *err, size_t errlen, const char *msg)
         snprintf(err, errlen, "%s", msg);
 }
 
+int mail_addr_valid(const char *addr)
+{
+    if (!addr || !*addr)
+        return 0;
+    const char *at = strchr(addr, '@');
+    if (!at || at == addr || !at[1] || strchr(at + 1, '@'))
+        return 0;
+    for (const char *p = addr; *p; p++) {
+        unsigned char ch = (unsigned char)*p;
+        if (ch <= 0x20 || ch == 0x7f || ch == '<' || ch == '>')
+            return 0;
+    }
+    return 1;
+}
+
 static int mail_cfg_load(sqlite3 *db, int64_t org_id, struct mail_cfg *c,
                          char *err, size_t errlen)
 {
@@ -54,6 +69,14 @@ static int mail_cfg_load(sqlite3 *db, int64_t org_id, struct mail_cfg *c,
         set_err(err, errlen, "smtp_host and smtp_from must be set");
         goto fail;
     }
+    if (!mail_addr_valid(c->from)) {
+        set_err(err, errlen, "smtp_from must be an email address");
+        goto fail;
+    }
+    if (c->reply_to && *c->reply_to && !mail_addr_valid(c->reply_to)) {
+        set_err(err, errlen, "smtp_reply_to must be an email address");
+        goto fail;
+    }
     if (c->user && *c->user) {
         if (!c->password || !*c->password) {
             set_err(err, errlen,
diff --git a/src/mail.h b/src/mail.h
index 5efade5..2c5da2f 100644
--- a/src/mail.h
+++ b/src/mail.h
@@ -14,6 +14,10 @@ struct mail_message {
     size_t attach_len;
 };
 
+/* True for an address the SMTP client can send to: one @, no spaces or
+   control characters, not empty. */
+int mail_addr_valid(const char *addr);
+
 /* Checks the org's SMTP settings (including decrypting the stored
    smtp_password) without sending. 0 configured, -1 not configured. */
 int mail_config_check(sqlite3 *db, int64_t org_id, char *err, size_t errlen);