require "./database"

module Guff
  class DatabaseUpdater
    CURRENT_VERSION = 2_i64

    SQL = {
      get_version: "
        SELECT value
          FROM metadata
         WHERE name = 'version'
      ",

      set_version_delete: "
        DELETE FROM metadata WHERE name = 'version'
      ",

      set_version_insert: "
        INSERT INTO metadata(name, value) VALUES ('version', ?)
      ",
    }

    MIGRATIONS = [{
      id: "0-null",
      backup: false,
      sql: %w{},
    }, {
      id: "1-create-tags-and-posts",
      backup: false,

      sql: [%{
        CREATE TABLE metadata (
          name    TEXT PRIMARY KEY,
          value   TEXT NOT NULL
        )
      }, %{
        CREATE TABLE sites (
          -- site id
          site_id     INTEGER PRIMARY KEY,

          -- name of site
          name        TEXT UNIQUE NOT NULL
                      CHECK (LENGTH(name) > 0),

          -- is this the default site?
          is_default  BOOLEAN NOT NULL DEFAULT false
        )
      }, %{
        CREATE TABLE posts (
          -- unique id
          post_id     INTEGER PRIMARY KEY,

          site_id     INTEGER NOT NULL
                      REFERENCES sites(site_id),

          -- false if this post has been deleted
          is_active   BOOLEAN NOT NULL
                      DEFAULT true,

          -- when this post was created
          created_at  TIMESTAMP WITH TIME ZONE
                      NOT NULL DEFAULT CURRENT_TIMESTAMP,

          -- when this post was posted
          -- (that is, the draft status was removed)
          posted_at   TIMESTAMP WITH TIME ZONE
                      NOT NULL DEFAULT CURRENT_TIMESTAMP,

          -- title of post
          name        TEXT NOT NULL
                      CHECK (LENGTH(name) > 0),

          -- slug of post (url fragment)
          slug        TEXT NOT NULL
                      CHECK (LENGTH(slug) > 0),

          -- body (raw text before filters)
          body        TEXT NOT NULL
                      CHECK (LENGTH(body) > 0),

          -- generated html (after filters)
          html        TEXT NOT NULL
                      CHECK (LENGTH(html) > 0)
        )
      }, %{
        CREATE INDEX in_posts_slug ON posts(slug)
      }],
    }, {
      id: "2-create-tags",
      backup: false,

      sql: [%{
        CREATE TABLE tags (
          tag_id      INTEGER PRIMARY KEY,
          name        TEXT UNIQUE NOT NULL
        )
      }, %{
        CREATE INDEX in_tags_name ON tags(name)
      }, %{
        CREATE TABLE post_tags (
          tag_id      INTEGER NOT NULL 
                      REFERENCES tags(tag_id),

          post_id     INTEGER NOT NULL 
                      REFERENCES posts(post_id),

          UNIQUE(tag_id, post_id)
        )
      }, %{
        CREATE INDEX in_post_tags_tag_id ON post_tags(tag_id)
      }, %{
        CREATE INDEX in_post_tags_post_id ON post_tags(post_id)
      }],
    }, {
      id: "3-create-domains",
      backup: false,

      sql: [%{
        CREATE TABLE domains (
          -- domain
          domain      TEXT PRIMARY KEY CHECK (
                        LENGTH(domain) > 0 AND
                        domain = LOWER(domain)
                      ),

          -- site id
          site_id     INTEGER NOT NULL
                      REFERENCES sites(site_id)
        )
      }],
    }]

    def self.run(path, config)
      new(path, config).run
    end

    def initialize(
      @path   : String,
      @config : Config
    )
      @db = Database.new(@path)
    end

    def run
      # get version from database
      db_version = @db.table_exists?("metadata") ? get_version : 0
      app_version = MIGRATIONS.size - 1

      if db_version < app_version
        puts "starting db migration (db = %d, app = %d)" % [
          db_version,
          app_version
        ]

        (db_version + 1).upto(app_version) do |v|
          migrate_to(v)
        end
      end
    end

    private def migrate_to(version)
      # map version number to migration id
      m = MIGRATIONS[version]
      puts "  * migration: %s" % [m[:id]]

      @db.transaction do
        # exec queries
        (m[:sql] as Array(String)).each do |sql|
          @db.query(sql)
        end

        # update database version
        set_version(version)
      end
    end

    private def get_version
      (@db.one(SQL[:get_version]) || 0).to_i
    end

    private def set_version(version)
      @db.query(SQL[:set_version_delete])
      @db.query(SQL[:set_version_insert], [version.to_s])
    end
  end
end