# R/db.R
# Twee databases:
#   DB_DATA         -> responses, audit_log
#   DB_PARTICIPANTS -> participants, companies, counters
# Beide op TeamID gekoppeld.

library(DBI)
library(RSQLite)

#shared_folder <- "C:/Users/dionw/Documents/InTense/shiny-apps/shared"
shared_folder <- "/srv/shiny-server/shared"

DB_DATA_FILE         <- "mycelia.sqlite"
DB_PARTICIPANTS_FILE <- "participants.sqlite"

db_data_path         <- function() file.path(shared_folder, DB_DATA_FILE)
db_participants_path <- function() file.path(shared_folder, DB_PARTICIPANTS_FILE)

open_db <- function(path) {
  dir.create(dirname(path), recursive = TRUE, showWarnings = FALSE)
  conn <- DBI::dbConnect(RSQLite::SQLite(), path)
  DBI::dbExecute(conn, "PRAGMA journal_mode = WAL;")
  DBI::dbExecute(conn, "PRAGMA busy_timeout = 5000;")
  DBI::dbExecute(conn, "PRAGMA foreign_keys = ON;")
  conn
}

db_connect_data         <- function() open_db(db_data_path())
db_connect_participants <- function() open_db(db_participants_path())

# ---------------------------------------------------------------------------
# Schema
# ---------------------------------------------------------------------------

db_create_schema_participants <- function(conn) {
  DBI::dbExecute(conn, "
    CREATE TABLE IF NOT EXISTS companies (
      company_code TEXT PRIMARY KEY,
      created_at   TEXT NOT NULL DEFAULT (datetime('now'))
    )")
  DBI::dbExecute(conn, "
    CREATE TABLE IF NOT EXISTS company_counters (
      company_code TEXT PRIMARY KEY,
      last_seq     INTEGER NOT NULL DEFAULT 0,
      FOREIGN KEY (company_code) REFERENCES companies(company_code)
    )")
  DBI::dbExecute(conn, "
    CREATE TABLE IF NOT EXISTS participants (
      TeamID         TEXT PRIMARY KEY,
      RespID         TEXT NOT NULL DEFAULT '',
      Team           TEXT NOT NULL DEFAULT '',
      lang           TEXT NOT NULL DEFAULT 'nl',
      Company        TEXT NOT NULL,
      created_at     TEXT NOT NULL DEFAULT (datetime('now')),
      first_login_at TEXT
    )")
  invisible(TRUE)
}

db_create_schema_data <- function(conn) {
  DBI::dbExecute(conn, "
    CREATE TABLE IF NOT EXISTS responses (
      response_id    INTEGER PRIMARY KEY AUTOINCREMENT,
      TeamID         TEXT NOT NULL,
      type           TEXT NOT NULL CHECK (type IN ('Self','Others')),
      RespondentName TEXT,
      payload_json   TEXT NOT NULL,
      created_at     TEXT NOT NULL DEFAULT (datetime('now'))
    )")
  DBI::dbExecute(conn,
                 "CREATE INDEX IF NOT EXISTS idx_responses_team_type
     ON responses(TeamID, type)")
  DBI::dbExecute(conn, "
    CREATE TABLE IF NOT EXISTS audit_log (
      id         INTEGER PRIMARY KEY AUTOINCREMENT,
      action     TEXT NOT NULL,
      table_name TEXT NOT NULL,
      TeamID     TEXT,
      detail     TEXT,
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    )")
  invisible(TRUE)
}

# ---------------------------------------------------------------------------
# Participants-database
# ---------------------------------------------------------------------------

db_find_participant <- function(conn, teamid) {
  DBI::dbGetQuery(conn,
                  "SELECT * FROM participants WHERE TeamID = ?",
                  params = list(teamid))
}

db_create_placeholder <- function(conn, teamid) {
  company <- substr(teamid, 1, 1)
  seq     <- as.integer(substr(teamid, 2, nchar(teamid)))
  DBI::dbExecute(conn,
                 "INSERT OR IGNORE INTO companies(company_code) VALUES (?)",
                 params = list(company))
  DBI::dbExecute(conn,
                 "INSERT OR IGNORE INTO company_counters(company_code, last_seq) VALUES (?, 0)",
                 params = list(company))
  DBI::dbExecute(conn,
                 "INSERT OR IGNORE INTO participants(TeamID, Company, first_login_at)
     VALUES (?, ?, datetime('now'))",
                 params = list(teamid, company))
  DBI::dbExecute(conn,
                 "UPDATE company_counters SET last_seq = MAX(last_seq, ?) WHERE company_code = ?",
                 params = list(seq, company))
  invisible(TRUE)
}

db_add_participant <- function(conn, teamid, resp_id, team, lang = "nl") {
  company <- substr(teamid, 1, 1)
  seq     <- as.integer(substr(teamid, 2, nchar(teamid)))
  DBI::dbExecute(conn,
                 "INSERT OR IGNORE INTO companies(company_code) VALUES (?)",
                 params = list(company))
  DBI::dbExecute(conn,
                 "INSERT OR IGNORE INTO company_counters(company_code, last_seq) VALUES (?, 0)",
                 params = list(company))
  DBI::dbExecute(conn,
                 "INSERT OR IGNORE INTO participants(TeamID, RespID, Team, lang, Company)
   VALUES (?, ?, ?, ?, ?)",
                 params = list(teamid, resp_id, team, lang, company))
  DBI::dbExecute(conn,
                 "UPDATE company_counters SET last_seq = MAX(last_seq, ?) WHERE company_code = ?",
                 params = list(seq, company))
  invisible(TRUE)
}

# ---------------------------------------------------------------------------
# Data-database
# ---------------------------------------------------------------------------

db_count_others <- function(conn, teamid) {
  DBI::dbGetQuery(conn,
                  "SELECT COUNT(*) AS n FROM responses
     WHERE TeamID = ? AND type = 'Others'",
                  params = list(teamid))$n[[1]]
}
db_count_self <- function(conn, teamid) {
  DBI::dbGetQuery(conn,
                  "SELECT COUNT(*) AS n FROM responses
     WHERE TeamID = ? AND type = 'Self'",
                  params = list(teamid))$n[[1]]
}

db_report_ready <- function(conn, teamid, threshold = 3) {
  db_count_others(conn, teamid) >= threshold
}

db_update_participant_details <- function(conn, teamid, resp_id, team, lang = "nl") {
  DBI::dbExecute(conn,
                 "UPDATE participants SET RespID = ?, Team = ?, lang = ? WHERE TeamID = ?",
                 params = list(resp_id, team, lang, teamid))
}