#include "db.h"

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

#include "auth.h"
#include "config.h"
#include "log.h"
#include "util.h"

static const char SCHEMA_V1[] =
    "CREATE TABLE meta ("
    "  key   TEXT PRIMARY KEY,"
    "  value TEXT NOT NULL"
    ") STRICT;\n"

    "CREATE TABLE users ("
    "  id           INTEGER PRIMARY KEY,"
    "  username     TEXT NOT NULL COLLATE NOCASE UNIQUE,"
    "  display_name TEXT NOT NULL,"
    "  pw_hash      TEXT NOT NULL,"
    "  is_admin     INTEGER NOT NULL DEFAULT 0 CHECK (is_admin IN (0,1)),"
    "  created_at   TEXT NOT NULL,"
    "  disabled_at  TEXT"
    ") STRICT;\n"

    "CREATE TABLE orgs ("
    "  id                      INTEGER PRIMARY KEY,"
    "  name                    TEXT NOT NULL,"
    "  org_nr                  TEXT,"
    "  vat_nr                  TEXT,"
    "  address                 TEXT,"
    "  postal_code             TEXT,"
    "  city                    TEXT,"
    "  country                 TEXT NOT NULL DEFAULT 'SE',"
    "  email                   TEXT,"
    "  phone                   TEXT,"
    "  fiscal_year_start_month INTEGER NOT NULL DEFAULT 1"
    "    CHECK (fiscal_year_start_month BETWEEN 1 AND 12),"
    "  moms_period             TEXT NOT NULL DEFAULT 'month'"
    "    CHECK (moms_period IN ('month','quarter','year')),"
    "  framework               TEXT NOT NULL DEFAULT 'K2'"
    "    CHECK (framework IN ('K2','K3')),"
    "  created_at              TEXT NOT NULL,"
    "  created_by              INTEGER NOT NULL REFERENCES users(id),"
    "  archived_at             TEXT"
    ") STRICT;\n"

    "CREATE TABLE memberships ("
    "  org_id     INTEGER NOT NULL REFERENCES orgs(id),"
    "  user_id    INTEGER NOT NULL REFERENCES users(id),"
    "  role       TEXT NOT NULL CHECK (role IN ('owner','bookkeeper','viewer')),"
    "  created_at TEXT NOT NULL,"
    "  PRIMARY KEY (org_id, user_id)"
    ") STRICT;\n"

    "CREATE TABLE api_tokens ("
    "  id           INTEGER PRIMARY KEY,"
    "  org_id       INTEGER NOT NULL REFERENCES orgs(id),"
    "  user_id      INTEGER NOT NULL REFERENCES users(id),"
    "  label        TEXT NOT NULL,"
    "  token_hash   BLOB NOT NULL UNIQUE CHECK (length(token_hash) = 32),"
    "  scopes       TEXT NOT NULL,"
    "  created_at   TEXT NOT NULL,"
    "  expires_at   TEXT,"
    "  last_used_at TEXT,"
    "  revoked_at   TEXT"
    ") STRICT;\n"

    "CREATE TABLE accounts ("
    "  org_id     INTEGER NOT NULL REFERENCES orgs(id),"
    "  id         INTEGER PRIMARY KEY,"
    "  number     TEXT NOT NULL"
    "    CHECK (number GLOB '[0-9]*' AND length(number) BETWEEN 1 AND 10),"
    "  name       TEXT NOT NULL,"
    "  type       TEXT NOT NULL"
    "    CHECK (type IN ('asset','liability','equity','revenue','expense')),"
    "  sru_code   TEXT,"
    "  vat_code   TEXT,"
    "  active     INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)),"
    "  created_at TEXT NOT NULL,"
    "  updated_at TEXT,"
    "  UNIQUE (org_id, id),"
    "  UNIQUE (org_id, number)"
    ") STRICT;\n"
    "CREATE INDEX idx_accounts_type ON accounts(org_id, type);\n"

    "CREATE TABLE fiscal_years ("
    "  org_id       INTEGER NOT NULL REFERENCES orgs(id),"
    "  id           INTEGER PRIMARY KEY,"
    "  label        TEXT NOT NULL,"
    "  start_date   TEXT NOT NULL,"
    "  end_date     TEXT NOT NULL,"
    "  status       TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open','closed')),"
    "  locked_until TEXT,"
    "  created_at   TEXT NOT NULL,"
    "  closed_at    TEXT,"
    "  closed_by    INTEGER REFERENCES users(id),"
    "  UNIQUE (org_id, id),"
    "  UNIQUE (org_id, label),"
    "  CHECK (start_date < end_date),"
    "  CHECK (locked_until IS NULL OR locked_until <= end_date)"
    ") STRICT;\n"

    "CREATE TABLE sequences ("
    "  org_id         INTEGER NOT NULL,"
    "  fiscal_year_id INTEGER NOT NULL,"
    "  series         TEXT NOT NULL,"
    "  next_number    INTEGER NOT NULL DEFAULT 1,"
    "  PRIMARY KEY (org_id, fiscal_year_id, series),"
    "  FOREIGN KEY (org_id, fiscal_year_id) REFERENCES fiscal_years(org_id, id)"
    ") STRICT;\n"

    "CREATE TABLE vouchers ("
    "  org_id              INTEGER NOT NULL REFERENCES orgs(id),"
    "  id                  INTEGER PRIMARY KEY,"
    "  fiscal_year_id      INTEGER NOT NULL,"
    "  series              TEXT NOT NULL,"
    "  number              INTEGER NOT NULL CHECK (number > 0),"
    "  date                TEXT NOT NULL CHECK (date LIKE '____-__-__'),"
    "  description         TEXT NOT NULL CHECK (length(description) > 0),"
    "  source              TEXT NOT NULL DEFAULT 'manual'"
    "    CHECK (source IN ('manual','agent','sie_import','system','ib')),"
    "  client_ref          TEXT,"
    "  corrects_voucher_id INTEGER,"
    "  created_at          TEXT NOT NULL,"
    "  created_by_user     INTEGER NOT NULL REFERENCES users(id),"
    "  created_by_token    INTEGER REFERENCES api_tokens(id),"
    "  hash_prev           BLOB NOT NULL CHECK (length(hash_prev) = 32),"
    "  hash                BLOB NOT NULL CHECK (length(hash) = 32),"
    "  UNIQUE (org_id, id),"
    "  UNIQUE (org_id, fiscal_year_id, series, number),"
    "  UNIQUE (org_id, client_ref),"
    "  FOREIGN KEY (org_id, fiscal_year_id) REFERENCES fiscal_years(org_id, id),"
    "  FOREIGN KEY (org_id, corrects_voucher_id) REFERENCES vouchers(org_id, id)"
    ") STRICT;\n"

    "CREATE TABLE voucher_rows ("
    "  org_id      INTEGER NOT NULL,"
    "  id          INTEGER PRIMARY KEY,"
    "  voucher_id  INTEGER NOT NULL,"
    "  line_no     INTEGER NOT NULL,"
    "  account_id  INTEGER NOT NULL,"
    "  debit_ore   INTEGER NOT NULL DEFAULT 0 CHECK (debit_ore  >= 0),"
    "  credit_ore  INTEGER NOT NULL DEFAULT 0 CHECK (credit_ore >= 0),"
    "  description TEXT,"
    "  CHECK ((debit_ore = 0) <> (credit_ore = 0)),"
    "  CHECK (debit_ore > 0 OR credit_ore > 0),"
    "  UNIQUE (org_id, id),"
    "  UNIQUE (org_id, voucher_id, line_no),"
    "  FOREIGN KEY (org_id, voucher_id) REFERENCES vouchers(org_id, id),"
    "  FOREIGN KEY (org_id, account_id) REFERENCES accounts(org_id, id)"
    ") STRICT;\n"
    "CREATE INDEX idx_vouchers_date ON vouchers(org_id, date);\n"
    "CREATE INDEX idx_vouchers_fy ON vouchers(org_id, fiscal_year_id, series, number);\n"
    "CREATE INDEX idx_rows_voucher ON voucher_rows(org_id, voucher_id, line_no);\n"
    "CREATE INDEX idx_rows_account ON voucher_rows(org_id, account_id);\n"

    "CREATE TRIGGER vouchers_no_update BEFORE UPDATE ON vouchers BEGIN"
    "  SELECT RAISE(ABORT, 'vouchers are append-only');"
    "END;\n"
    "CREATE TRIGGER vouchers_no_delete BEFORE DELETE ON vouchers BEGIN"
    "  SELECT RAISE(ABORT, 'vouchers are append-only');"
    "END;\n"
    "CREATE TRIGGER voucher_rows_no_update BEFORE UPDATE ON voucher_rows BEGIN"
    "  SELECT RAISE(ABORT, 'voucher rows are append-only');"
    "END;\n"
    "CREATE TRIGGER voucher_rows_no_delete BEFORE DELETE ON voucher_rows BEGIN"
    "  SELECT RAISE(ABORT, 'voucher rows are append-only');"
    "END;\n"

    "CREATE TABLE attachments ("
    "  org_id     INTEGER NOT NULL REFERENCES orgs(id),"
    "  id         INTEGER PRIMARY KEY,"
    "  sha256     BLOB NOT NULL CHECK (length(sha256) = 32),"
    "  filename   TEXT NOT NULL,"
    "  mime       TEXT NOT NULL,"
    "  size_bytes INTEGER NOT NULL CHECK (size_bytes >= 0),"
    "  content    BLOB NOT NULL,"
    "  created_at TEXT NOT NULL,"
    "  created_by INTEGER NOT NULL REFERENCES users(id),"
    "  UNIQUE (org_id, id),"
    "  UNIQUE (org_id, sha256, filename)"
    ") STRICT;\n"

    "CREATE TABLE voucher_attachments ("
    "  org_id        INTEGER NOT NULL,"
    "  voucher_id    INTEGER NOT NULL,"
    "  attachment_id INTEGER NOT NULL,"
    "  created_at    TEXT NOT NULL,"
    "  PRIMARY KEY (org_id, voucher_id, attachment_id),"
    "  FOREIGN KEY (org_id, voucher_id) REFERENCES vouchers(org_id, id),"
    "  FOREIGN KEY (org_id, attachment_id) REFERENCES attachments(org_id, id)"
    ") STRICT;\n"

    "CREATE TABLE audit_log ("
    "  seq            INTEGER PRIMARY KEY,"
    "  org_id         INTEGER REFERENCES orgs(id),"
    "  at             TEXT NOT NULL,"
    "  actor_user_id  INTEGER REFERENCES users(id),"
    "  actor_token_id INTEGER REFERENCES api_tokens(id),"
    "  action         TEXT NOT NULL,"
    "  request_json   TEXT NOT NULL DEFAULT '{}',"
    "  result_code    TEXT NOT NULL,"
    "  hash_prev      BLOB NOT NULL CHECK (length(hash_prev) = 32),"
    "  hash           BLOB NOT NULL CHECK (length(hash) = 32)"
    ") STRICT;\n"
    "CREATE INDEX idx_audit_org ON audit_log(org_id, seq);\n"

    "CREATE TRIGGER audit_no_update BEFORE UPDATE ON audit_log BEGIN"
    "  SELECT RAISE(ABORT, 'audit log is append-only');"
    "END;\n"
    "CREATE TRIGGER audit_no_delete BEFORE DELETE ON audit_log BEGIN"
    "  SELECT RAISE(ABORT, 'audit log is append-only');"
    "END;\n"

    "CREATE TABLE idempotency ("
    "  org_id        INTEGER NOT NULL,"
    "  client_ref    TEXT NOT NULL,"
    "  cmd           TEXT NOT NULL,"
    "  response_json TEXT NOT NULL,"
    "  created_at    TEXT NOT NULL,"
    "  PRIMARY KEY (org_id, client_ref),"
    "  FOREIGN KEY (org_id) REFERENCES orgs(id)"
    ") STRICT;\n"

    "CREATE TABLE report_rules ("
    "  org_id     INTEGER NOT NULL REFERENCES orgs(id),"
    "  id         INTEGER PRIMARY KEY,"
    "  report     TEXT NOT NULL,"
    "  box        TEXT NOT NULL,"
    "  match_type TEXT NOT NULL CHECK (match_type IN ('account','range','type')),"
    "  pattern    TEXT NOT NULL,"
    "  sign       INTEGER NOT NULL DEFAULT 1 CHECK (sign IN (1,-1)),"
    "  sort_order INTEGER NOT NULL DEFAULT 0,"
    "  UNIQUE (org_id, id)"
    ") STRICT;\n"

    "CREATE TABLE settings ("
    "  org_id INTEGER NOT NULL REFERENCES orgs(id),"
    "  key    TEXT NOT NULL,"
    "  value  TEXT NOT NULL,"
    "  PRIMARY KEY (org_id, key)"
    ") STRICT;\n";

static const char SCHEMA_V2[] =
    "CREATE TABLE voucher_templates ("
    "  org_id      INTEGER NOT NULL REFERENCES orgs(id),"
    "  id          INTEGER PRIMARY KEY,"
    "  name        TEXT NOT NULL,"
    "  series      TEXT NOT NULL DEFAULT 'A',"
    "  description TEXT NOT NULL DEFAULT '',"
    "  active      INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)),"
    "  created_at  TEXT NOT NULL,"
    "  updated_at  TEXT,"
    "  UNIQUE (org_id, id),"
    "  UNIQUE (org_id, name)"
    ") STRICT;\n"

    "CREATE TABLE voucher_template_rows ("
    "  org_id      INTEGER NOT NULL,"
    "  id          INTEGER PRIMARY KEY,"
    "  template_id INTEGER NOT NULL,"
    "  line_no     INTEGER NOT NULL,"
    "  account_id  INTEGER NOT NULL,"
    "  formula     TEXT NOT NULL CHECK (length(formula) > 0),"
    "  description TEXT,"
    "  UNIQUE (org_id, id),"
    "  UNIQUE (org_id, template_id, line_no),"
    "  FOREIGN KEY (org_id, template_id)"
    "    REFERENCES voucher_templates(org_id, id),"
    "  FOREIGN KEY (org_id, account_id) REFERENCES accounts(org_id, id)"
    ") STRICT;\n"
    "CREATE INDEX idx_template_rows ON voucher_template_rows(org_id,"
    " template_id, line_no);\n";

static void set_err(char **err, const char *fmt, ...)
    __attribute__((format(printf, 2, 3)));

static void set_err(char **err, const char *fmt, ...)
{
    if (!err)
        return;
    char buf[1024];
    va_list ap;
    va_start(ap, fmt);
    vsnprintf(buf, sizeof buf, fmt, ap);
    va_end(ap);
    free(*err);
    *err = xstrdup(buf);
}

int db_exec(sqlite3 *db, const char *sql, char **err)
{
    char *msg = NULL;
    int rc = sqlite3_exec(db, sql, NULL, NULL, &msg);
    if (rc != SQLITE_OK) {
        set_err(err, "%s", msg ? msg : sqlite3_errmsg(db));
        sqlite3_free(msg);
        return -1;
    }
    return 0;
}

int db_migrate(sqlite3 *db, char **err)
{
    if (db_exec(db, "BEGIN IMMEDIATE", err) != 0)
        return -1;
    if (db_exec(db, SCHEMA_V1, err) != 0) {
        db_exec(db, "ROLLBACK", NULL);
        return -1;
    }
    if (db_exec(db, SCHEMA_V2, err) != 0) {
        db_exec(db, "ROLLBACK", NULL);
        return -1;
    }
    char ts[32];
    util_iso8601(util_now(), ts, sizeof ts);
    char *sql = sqlite3_mprintf(
        "INSERT INTO meta(key,value) VALUES('schema_version','%d')"
        " ON CONFLICT(key) DO UPDATE SET value=excluded.value;"
        "INSERT INTO meta(key,value) VALUES('created_at','%q');",
        BOKF_SCHEMA_VERSION, ts);
    int rc = db_exec(db, sql, err);
    sqlite3_free(sql);
    if (rc != 0) {
        db_exec(db, "ROLLBACK", NULL);
        return -1;
    }
    if (db_exec(db, "COMMIT", err) != 0)
        return -1;
    log_info("database schema created at version %d", BOKF_SCHEMA_VERSION);
    return 0;
}

/* Forward-only upgrades for existing databases. */
static int db_upgrade(sqlite3 *db, int from, char **err)
{
    if (db_exec(db, "BEGIN IMMEDIATE", err) != 0)
        return -1;
    if (from < 2 && db_exec(db, SCHEMA_V2, 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);
    int rc = db_exec(db, sql, err);
    sqlite3_free(sql);
    if (rc != 0) {
        db_exec(db, "ROLLBACK", NULL);
        return -1;
    }
    if (db_exec(db, "COMMIT", err) != 0)
        return -1;
    log_info("database schema upgraded from version %d to %d", from,
             BOKF_SCHEMA_VERSION);
    return 0;
}

int db_open(const char *path, sqlite3 **out, char **err)
{
    sqlite3 *db = NULL;
    int rc = sqlite3_open_v2(path, &db,
                             SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
    if (rc != SQLITE_OK) {
        set_err(err, "cannot open database %s: %s", path,
                db ? sqlite3_errmsg(db) : "unknown error");
        sqlite3_close(db);
        return -1;
    }
    sqlite3_extended_result_codes(db, 1);

    char pragmas[512];
    snprintf(pragmas, sizeof pragmas,
             "PRAGMA journal_mode=WAL;"
             "PRAGMA foreign_keys=ON;"
             "PRAGMA busy_timeout=5000;"
             "PRAGMA synchronous=%s;"
             "PRAGMA wal_autocheckpoint=1000;",
             g_cfg.synchronous ? g_cfg.synchronous : "FULL");
    if (db_exec(db, pragmas, err) != 0) {
        sqlite3_close(db);
        return -1;
    }

    sqlite3_stmt *st = NULL;
    rc = sqlite3_prepare_v2(db, "SELECT value FROM meta WHERE key='schema_version'",
                            -1, &st, NULL);
    if (rc == SQLITE_OK && sqlite3_step(st) == SQLITE_ROW) {
        int version = atoi((const char *)sqlite3_column_text(st, 0));
        sqlite3_finalize(st);
        if (version > BOKF_SCHEMA_VERSION) {
            set_err(err, "database schema version %d is newer than this binary (%d)",
                    version, BOKF_SCHEMA_VERSION);
            sqlite3_close(db);
            return -1;
        }
        if (version < BOKF_SCHEMA_VERSION) {
            if (db_upgrade(db, version, err) != 0) {
                sqlite3_close(db);
                return -1;
            }
        }
    } else {
        sqlite3_finalize(st);
        if (db_migrate(db, err) != 0) {
            sqlite3_close(db);
            return -1;
        }
    }

    *out = db;
    return 0;
}

int64_t db_last_id(sqlite3 *db)
{
    return sqlite3_last_insert_rowid(db);
}

int64_t db_count(sqlite3 *db, const char *sql)
{
    sqlite3_stmt *st = NULL;
    int64_t n = -1;
    if (sqlite3_prepare_v2(db, sql, -1, &st, NULL) == SQLITE_OK &&
        sqlite3_step(st) == SQLITE_ROW)
        n = sqlite3_column_int64(st, 0);
    sqlite3_finalize(st);
    return n;
}

int db_create_user(sqlite3 *db, const char *username, const char *display_name,
                   const char *password, int is_admin, int64_t *out_id,
                   char **err)
{
    if (!username || !*username) {
        set_err(err, "username is required");
        return -1;
    }
    char *phc = NULL;
    if (auth_hash_password(password, &phc) != 0) {
        set_err(err, "password hashing failed");
        return -1;
    }
    char ts[32];
    util_iso8601(util_now(), ts, sizeof ts);
    sqlite3_stmt *st = NULL;
    int rc = sqlite3_prepare_v2(
        db,
        "INSERT INTO users(username,display_name,pw_hash,is_admin,created_at)"
        " VALUES(?1,?2,?3,?4,?5)",
        -1, &st, NULL);
    if (rc != SQLITE_OK) {
        set_err(err, "prepare failed: %s", sqlite3_errmsg(db));
        free(phc);
        return -1;
    }
    sqlite3_bind_text(st, 1, username, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(st, 2, display_name && *display_name ? display_name : username,
                      -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(st, 3, phc, -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(st, 4, is_admin ? 1 : 0);
    sqlite3_bind_text(st, 5, ts, -1, SQLITE_TRANSIENT);
    rc = sqlite3_step(st);
    sqlite3_finalize(st);
    free(phc);
    if (rc != SQLITE_DONE) {
        if ((rc & 0xff) == SQLITE_CONSTRAINT)
            set_err(err, "username already exists");
        else
            set_err(err, "insert failed: %s", sqlite3_errmsg(db));
        return -1;
    }
    if (out_id)
        *out_id = db_last_id(db);
    return 0;
}

char *db_setting(sqlite3 *db, int64_t org_id, const char *key)
{
    sqlite3_stmt *st = NULL;
    char *value = NULL;
    if (sqlite3_prepare_v2(
            db, "SELECT value FROM settings WHERE org_id=?1 AND key=?2", -1,
            &st, NULL) != SQLITE_OK)
        return NULL;
    sqlite3_bind_int64(st, 1, org_id);
    sqlite3_bind_text(st, 2, key, -1, SQLITE_TRANSIENT);
    if (sqlite3_step(st) == SQLITE_ROW) {
        const unsigned char *v = sqlite3_column_text(st, 0);
        if (v)
            value = xstrdup((const char *)v);
    }
    sqlite3_finalize(st);
    return value;
}

char *db_membership_role(sqlite3 *db, int64_t org_id, int64_t user_id)
{
    sqlite3_stmt *st = NULL;
    char *role = NULL;
    if (sqlite3_prepare_v2(
            db,
            "SELECT role FROM memberships WHERE org_id=?1 AND user_id=?2", -1,
            &st, NULL) != SQLITE_OK)
        return NULL;
    sqlite3_bind_int64(st, 1, org_id);
    sqlite3_bind_int64(st, 2, user_id);
    if (sqlite3_step(st) == SQLITE_ROW) {
        const unsigned char *r = sqlite3_column_text(st, 0);
        if (r)
            role = xstrdup((const char *)r);
    }
    sqlite3_finalize(st);
    return role;
}