;; SPDX-License-Identifier: EUPL-1.2
;; SPDX-FileCopyrightText: 2025 Uko Kokņevičs <perkontevs@gmail.com>
(defpackage :ukkoclot/db
  (:use :c2cl :sqlite)
  (:import-from :log)
  (:export :get-inline-bot-type :set-inline-bot-type :with-db))
(in-package :ukkoclot/db)

(defconstant +target-version+ 1
  "Intended DB version")

(defmacro with-db ((name path) &body body)
  `(let ((,name (connect ,path)))
     (unwind-protect (progn (upgrade ,name) ,@body)
       (disconnect ,name))))

(defun get-inline-bot-type (db id)
  (let ((type-int (execute-single db "SELECT type FROM inline_bots WHERE id = ?" id)))
    (when type-int
      (integer->inline-bot-type type-int))))

(defun set-inline-bot-type (db id type)
  (execute-non-query db
                     "INSERT OR REPLACE INTO inline_bots (id, type) VALUES (?, ?)"
                     id
                     (inline-bot-type->integer type)))

(defun inline-bot-type->integer (type)
  (case type
    (:blacklisted 0)
    (:whitelisted 1)
    (t (error "Unknown inline bot type ~S" type))))

(defun integer->inline-bot-type (num)
  (case num
    (0 :blacklisted)
    (1 :whitelisted)
    (t (error "Unknown inline bot type value ~S" num))))

(defun upgrade (db)
  (execute-non-query db "CREATE TABLE IF NOT EXISTS version(id INTEGER PRIMARY KEY, version INTEGER)")
  (let ((current-ver (execute-single db "SELECT version FROM version WHERE id = 0")))
    (unless current-ver
      (setf current-ver 0))
    (cond
      ((= current-ver +target-version+) (log:info "Database is up to date"))

      ((> current-ver +target-version+)
       (log:error "Database has a higher version than supported?")
       (error "Corrupted Database"))

      (t
       (log:info "Updating database from version ~A to ~A" current-ver +target-version+)
       (loop while (< current-ver +target-version+)
             do (with-transaction db
                  (log:info "Updating database step from ~A" current-ver)
                  (incf current-ver)
                  (upgrade-step db current-ver)
                  (execute-non-query
                   db
                   "INSERT OR REPLACE INTO version(id, version) VALUES (0, ?)"
                   current-ver)))
       (log:info "Database updating complete :)")))))

(defun upgrade-step (db new-version)
  (case new-version
    (1
     (execute-non-query db "DROP TABLE IF EXISTS inline_bots_enum")
     (execute-non-query db "
CREATE TABLE inline_bots_enum (
  id INTEGER PRIMARY KEY,
  value TEXT UNIQUE)")
     (execute-non-query db "
INSERT INTO inline_bots_enum(id, value)
VALUES (?, 'blacklisted'), (?, 'whitelisted')"
                        (inline-bot-type->integer :blacklisted)
                        (inline-bot-type->integer :whitelisted))

     (execute-non-query db "DROP TABLE IF EXISTS inline_bots")
     (execute-non-query db "
CREATE TABLE inline_bots (
  id INTEGER PRIMARY KEY,
  type INTEGER REFERENCES inline_bots_enum(id))"))
    (t (error "Unreachable upgrade step reached ~A" new-version))))