# SQLite3 wrapper
# Copyright (C) 2023  Nguyễn Gia Phong
#
# This file if part of hybring.
#
# Hybring is free software: you can redistribute it and/or modify
# it under the terms of the GNU Affero General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# Hybring is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU Affero General Public License for more details.
#
# You should have received a copy of the GNU Affero General Public License
# along with hybring.  If not, see <https://www.gnu.org/licenses/>.

@[Link("sqlite3")]
lib SQLite
  OK = 0
  ROW = 100
  DONE = 101

  enum UpdateAction : LibC::Int
    Delete = 9
    Insert = 18
    Update = 23
  end

  type Database = Void*
  type Statement = Void*

  fun errstr = sqlite3_errstr(rc : LibC::Int) : LibC::Char*
  fun mprintf = sqlite3_mprintf(format : LibC::Char*, ...) : LibC::Char*
  fun free = sqlite3_free(format : Void*, ...)

  fun open = sqlite3_open(filename : LibC::Char*, db : Database*) : LibC::Int
  fun close = sqlite3_close(db : Database) : LibC::Int
  fun update_hook = sqlite3_update_hook(db : Database,
                                        f : (Void*, UpdateAction, LibC::Char*,
                                             LibC::Char*, Int64 ->),
                                        arg : Void*)

  fun prepare = sqlite3_prepare_v2(db : Database, query : LibC::Char*,
                                   length : LibC::Int, stmt : Statement*,
                                   query_tail : LibC::Char**) : LibC::Int
  fun finalize = sqlite3_finalize(stmt : Statement) : LibC::Int
  fun step = sqlite3_step(stmt : Statement) : Int32

  fun column_int = sqlite3_column_int(stmt : Statement,
                                      col : LibC::Int) : LibC::Int
  fun column_int64 = sqlite3_column_int64(stmt : Statement,
                                          col : LibC::Int) : Int64
  fun column_text = sqlite3_column_text(stmt : Statement,
                                        col : LibC::Int) : LibC::Char*
end

class Database
  MIGRATIONS = [
    ["CREATE TABLE member (id INTEGER PRIMARY KEY,
        nick TEXT NOT NULL UNIQUE,
        opennic TEXT NOT NULL UNIQUE,
        icann TEXT NOT NULL UNIQUE)",
     "CREATE TABLE applicant (id INTEGER PRIMARY KEY,
        nick TEXT NOT NULL UNIQUE,
        opennic TEXT NOT NULL UNIQUE,
        icann TEXT NOT NULL UNIQUE)"]]
  SELECT_MEMBER_ROW = "SELECT nick, opennic, icann FROM member
    WHERE rowid = %lli"
  SELECT_APPLICANT_ROW = "SELECT nick, opennic, icann FROM applicant
    WHERE rowid = %lli"
  INSERT_MEMBER = "INSERT INTO member (nick, opennic, icann)
    VALUES (%Q, %Q, %Q)"
  INSERT_APPLICANT = "INSERT INTO applicant (nick, opennic, icann)
    VALUES (%Q, %Q, %Q)"

  class Statement
    def initialize(db, query)
      Database.check SQLite.prepare db, query, LibC.strlen(query),
                                    out @ref, out _
    end

    def step : LibC::Int
      SQLite.step @ref
    end

    def row
      Row.new @ref
    end

    def finalize
      Database.check SQLite.finalize @ref
    end
  end

  class Column
    def initialize(stmt : SQLite::Statement, i : LibC::Int)
      @stmt = stmt
      @i = i
    end

    def int
      SQLite.column_int @stmt, @i
    end

    def int64
      SQLite.column_int64 @stmt, @i
    end

    def text
      String.new SQLite.column_text @stmt, @i
    end
  end

  class Row
    def initialize(stmt : SQLite::Statement)
      @stmt = stmt
    end

    def [](i : LibC::Int)
      Column.new @stmt, i
    end
  end

  def initialize(path, opennic, icann)
    Dir.mkdir_p path.parent
    Database.check SQLite.open path.to_s, out @ref
    @members = {} of Int64 => {String, String, String}
    @applicants = {} of Int64 => {String, String, String}

    self.exec "PRAGMA user_version" do |row|
      version = row[0].int
      raise "negative schema version" if version < 0
      raise "schema version newer than supported" if version > MIGRATIONS.size
      self.transact do
        MIGRATIONS[version..].each do |migration|
          migration.each do |sql|
            self.exec sql do end
          end
        end
        self.exec "PRAGMA user_version = %u", MIGRATIONS.size do end
        if version == 0 # avoid out-of-bound when looking for neighbors
          self.exec INSERT_MEMBER, "self", opennic, icann do end
        end
      end
    rescue ex
      self.finalize
      raise ex
    end

    begin
      self.exec "SELECT rowid, nick, opennic, icann FROM member" do |row|
        @members[row[0].int64] = {row[1].text, row[2].text, row[3].text}
      end
      self.exec "SELECT rowid, nick, opennic, icann FROM applicant" do |row|
        @applicants[row[0].int64] = {row[1].text, row[2].text, row[3].text}
      end
    rescue ex
      self.finalize
      raise ex
    end
    SQLite.update_hook @ref, ->(arg, action, db, table, rowid) {
      return unless db == "main"
      obj = arg.as Database
      case table
      when "member"
        case action
        in .delete?
          obj.members.delete rowid
        in .insert?, .update?
          obj.exec SELECT_MEMBER_ROW, rowid do |row|
            obj.members[rowid] = {row[0].text, row[1].text, row[2].text}
          end
        end
      when "applicant"
        case action
        in .delete?
          obj.applicants.delete rowid
        in .insert?, .update?
          obj.exec SELECT_APPLICANT_ROW, rowid do |row|
            obj.applicants[rowid] = {row[0].text, row[1].text, row[2].text}
          end
        end
      end
    }, self.as Void*
  end

  def exec(query : String, *values)
    sql = SQLite.mprintf query, *values
    stmt = Statement.new @ref, sql
    SQLite.free sql
    loop do
      rc = stmt.step
      case rc
      when SQLite::ROW
        yield stmt.row
      when SQLite::DONE
        break
      else
        Database.check rc
      end
    end
  end

  def transact
    self.exec "BEGIN TRANSACTION" do end
    yield
    self.exec "COMMIT" do end
  end

  getter members
  getter applicants

  def add_applicant(nick, opennic, icann)
    self.exec INSERT_APPLICANT, nick, opennic, icann do end
  end

  def finalize
    Database.check SQLite.close @ref
  end
end

def Database.check(rc : LibC::Int)
  raise String.new SQLite.errstr rc if rc != SQLite::OK
end