require "option_parser"
require "http/server"
require "ecr/macros"
require "json"
require "secure_random"
require "crypto/bcrypt"
require "sqlite3"
require "./guff/*"

private macro define_lazy_getters(hash)
  {% for name, klass in hash %}
    def {{ name.id }} : {{ klass.id }}
      (@cached_{{ name.id }} ||= {{ klass.id }}.new(@context))
    end
  {% end %}
end

private macro include_api_modules(modules)
  {% for mod in modules.resolve %}
    include {{ mod.id }}
  {% end %}
end

private macro api_method_dispatch(modules)
  case namespace
  {% for mod in modules.resolve %}
    {% mod_name = mod.resolve.name.gsub(/^.*:(.*)API$/, "\\1").downcase %}
    when {{ mod_name.stringify }}
    case method
    {% for mod_method in mod.resolve.methods %}
      {% method_name = mod_method.name.gsub(/^do_([^_]+)_/, "") %}
      when {{ method_name.stringify }}
        {{ mod_method.name.id }}(params)
    {% end %}
    else
      raise "unknown method: #{namespace}/#{method}"
    end
  {% end %}
  else
    raise "unknown namespace: #{namespace}"
  end
end

module Guff
  class Config
    property :mode, :env, :host, :port, :data_dir, :system_dir

    DEFAULTS = {
      mode:       "help",
      env:        "production",
      host:       "127.0.0.1",
      port:       "8989",
      data_dir:   "./data",
      system_dir: "/usr/local/share/guff",
    }

    def initialize
      @mode        = DEFAULTS[:mode] as String
      @env         = (ENV["GUFF_ENVIRONMENT"]? || DEFAULTS[:env]) as String
      @host        = (ENV["GUFF_HOST"]? || DEFAULTS[:host]) as String
      @port        = (ENV["GUFF_PORT"]? || DEFAULTS[:port]) as String
      @data_dir    = (ENV["GUFF_DATA_DIR"]? || DEFAULTS[:data_dir]) as String
      @system_dir  = (ENV["GUFF_SYSTEM_DIR"]? || DEFAULTS[:system_dir]) as String
    end

    VALID_MODES = %w{init run help}

    def mode=(mode : String)
      raise "unknown mode: \"#{mode}\"" unless VALID_MODES.includes?(mode)
      @mode = mode
    end

    VALID_ENVS = %w{development production}

    def env=(env : String)
      raise "unknown environment: \"#{env}\"" unless VALID_ENVS.includes?(env)
      @env = env
    end

    def port=(port : String)
      val = port.to_i
      raise "invalid port: #{port}" unless val > 0 && val < 65535
      @port = port
    end

    def system_dir=(dir : String)
      raise "missing system dir: \"#{dir}\"" unless Dir.exists?(dir)
      @system_dir = dir
    end

    def db_path
      File.join(@data_dir, "guff.db")
    end

    def self.parse(
      app   : String,
      args  : Array(String)
    ) : Config
      r = Config.new

      raise "missing mode" unless args.size > 0

      # get mode
      r.mode = case mode = args.shift
      when "-h", "--help"
        "help"
      else
        mode
      end

      # parse arguments
      p = OptionParser.parse(args) do |p|
        p.banner = "Usage: #{app} [mode] <args>"

        p.separator
        p.separator("Run Options:")

        p.on(
          "-H HOST", "--host HOST",
          "TCP host (defaults to \"#{DEFAULTS[:host]}\")"
        ) do |arg|
          r.host = arg
        end

        p.on(
          "-p PORT", "--port PORT",
          "TCP port (defaults to \"#{DEFAULTS[:port]}\")"
        ) do |arg|
          r.port = arg
        end

        p.separator
        p.separator("Directory Options:")

        p.on(
          "-D DIR", "--data-dir DIR",
          "Data directory (defaults to \"#{DEFAULTS[:data_dir]}\")"
        ) do |arg|
          r.data_dir = arg
        end

        p.on(
          "-S DIR", "--system-dir DIR",
          "Guff system directory (defaults to \"#{DEFAULTS[:system_dir]}\")"
        ) do |arg|
          r.system_dir = arg
        end

        p.separator
        p.separator("Development Options:")

        p.on(
          "-E ENV", "--environment ENV",
          "Environment (defaults to \"#{DEFAULTS[:env]})\""
        ) do |arg|
          r.env = arg
        end

        p.separator
        p.separator("Other Options:")

        p.on("-h", "--help", "Print usage.") do
          r.mode = "help"
        end
      end

      case r.mode
      when "init"
        # shortcut for -D parameter
        r.data_dir = args.shift if args.size > 0
      when "help"
        # print help
        puts p
      end

      # return config
      r
    end
  end

  ISO8601 = ::Time::Format.new("%Y-%m-%dT%H:%M:%SZ")

  module MimeType
    TYPES = {
      ".js":    "text/javascript; charset=utf-8",
      ".css":   "text/css; charset=utf-8",
      ".html":  "text/html; charset=utf-8",
      ".png":   "image/png",
      ".jpeg":  "image/jpeg",
      ".jpg":   "image/jpeg",
      ".otf":   "application/vnd.ms-opentype",
      ".eot":   "application/vnd.ms-fontobject",
      ".svg":   "image/svg+xml",
      ".ttf":   "application/x-font-ttf",
      ".woff":  "application/font-woff",
      ".woff2": "application/font-woff",
    }

    def self.from_path(path : String) : String
      TYPES[File.extname(path)]? || "application/octet-stream"
    end
  end

  module Password
    def self.create(password : String) : String
      Crypto::Bcrypt::Password.create(password).to_s
    end

    def self.test(hash : String, password : String) : Bool
      Crypto::Bcrypt::Password.new(hash) == password
    end

    def self.random_password
      SecureRandom.base64(6 + rand(6)).strip.gsub(/\=+$/, "")
    end
  end

  class PagedResultSet
    def initialize(
      @page       : Int32,
      @num_rows   : Int64,
      @limit      : Int32,
      @rows       : Array(Hash(String, String))
    )
    end

    def to_json(io)
      {
        "meta": {
          "page":       @page,
          "num_rows":   @num_rows,
          "num_pages":  (1.0 * @num_rows / @limit).ceil
        },

        "rows": @rows
      }.to_json(io)
    end
  end

  module Models
    abstract class Model
      def initialize(@context : Context)
      end
    end

    class PostModel < Model
      SQL = {
        add: "
          INSERT INTO posts(site_id, created_by, state_id) VALUES
            (?, ?, (SELECT state_id FROM states WHERE state = 'draft'))
        ",

        fts_add: "
          INSERT INTO posts_fts(rowid, name, slug, body)
            SELECT post_id, name, slug, body FROM posts WHERE post_id = ?
        ",

        set: "
          UPDATE posts
             SET %s
           WHERE post_id = ?
        ",

        fts_set: "
          UPDATE posts_fts
             SET %s
           WHERE rowid = ?
        ",

        count_posts: "
          SELECT COUNT(*)

            FROM posts a

            JOIN states b
              ON (b.state_id = a.state_id)
            LEFT JOIN blogs c
              ON (c.post_id = a.post_id)
            LEFT JOIN pages d
              ON (d.post_id = a.post_id)
            LEFT JOIN projects e
              ON (e.post_id = a.post_id)

           WHERE %s
        ",

        get_posts: "
          SELECT a.post_id,
                 a.site_id,
                 c.name AS site_name,
                 b.state,
                 a.posted_at,
                 a.expires_at,
                 a.slug,
                 a.slug_lock,
                 a.name,

                 (CASE WHEN x.post_id IS NOT NULL THEN
                   'blog'
                 WHEN y.post_id IS NOT NULL THEN
                   'page'
                 WHEN z.post_id IS NOT NULL THEN
                   'project'
                 END) as post_type,

                 (CASE WHEN b.state = 'posted' THEN
                   CASE WHEN x.post_id IS NOT NULL THEN
                     strftime('/%%Y/%%m/%%d/', a.posted_at) || a.slug || '.html'
                   WHEN y.post_id IS NOT NULL THEN
                     '/' || a.slug || '.html'
                   WHEN x.post_id IS NOT NULL THEN
                     '/' || a.slug || '/'
                   END
                 ELSE
                   NULL
                 END) AS post_url

            FROM posts a
            JOIN states b
              ON (b.state_id = a.state_id)
            JOIN sites c
              ON (c.site_id = a.site_id)

            LEFT JOIN blogs x
              ON (x.post_id = a.post_id)
            LEFT JOIN pages y
              ON (y.post_id = a.post_id)
            LEFT JOIN projects z
              ON (z.post_id = a.post_id)

           WHERE c.is_active
             AND %s

           ORDER BY COALESCE(a.posted_at, a.created_at) DESC

           LIMIT ? OFFSET ?
        ",
      }

      def add(
        site_id : Int64,
        user_id : Int64,
      ) : Int64
        db = @context.dbs.rw
        post_id = -1_i64

        db.transaction do
          # add entry
          db.query(SQL[:add], [site_id.to_s, user_id.to_s])
          post_id = db.last_insert_row_id.to_i64

          # populate fts index
          db.query(SQL[:fts_add], [post_id.to_s])
        end

        # return post_id
        post_id
      end

      def set(
        post_id         : Int64,

        site_id         : Int64?  = nil,
        state           : String? = nil,

        have_posted_at  : Bool    = false,
        posted_at       : Time?   = nil,

        have_expires_at : Bool    = false,
        expires_at      : Time?   = nil,

        slug            : String? = nil,
        slug_lock       : Bool?   = nil,

        name            : String? = nil,
        body            : String? = nil,
      )
        sets = [] of String
        args = [] of String
        fts_sets = [] of String
        fts_args = [] of String

        if site_id
          sets << "site_id = ?"
          args << site_id.to_s
        end

        if state
          sets << "state_id = (SELECT state_id FROM states WHERE state = ?)"
          args << state

          if state == "posted" && !have_posted_at
            # set posted_at by default
            sets << "posted_at = COALESCE(posted_at, CURRENT_TIMESTAMP)"
          end
        end

        if have_posted_at
          if posted_at
            sets << "posted_at = ?"
            args << ISO8601.format(posted_at)
          else
            sets << "posted_at = NULL"
          end
        end

        if have_expires_at
          if expires_at
            sets << "expires_at = ?"
            args << ISO8601.format(expires_at)
          else
            sets << "expires_at = NULL"
          end
        end

        if slug
          sets << "slug = ?"
          args << slug

          fts_sets << "slug = ?"
          fts_args << slug
        end

        unless slug_lock.nil?
          sets << "slug_lock = ?"
          args << (slug_lock ? "1" : "0")
        end

        if name
          sets << "name = ?"
          args << name

          fts_sets << "name = ?"
          fts_args << name
        end

        if body
          sets << "body = ?"
          args << body

          fts_sets << "body = ?"
          fts_args << body.gsub(/<.+?>/, " ")
        end

        if sets.size > 0
          # update posts
          args << post_id.to_s
          @context.dbs.rw.query(SQL[:set] % sets.join(","), args)

          if fts_sets.size > 0
            # update posts fts
            fts_args << post_id.to_s
            @context.dbs.rw.query(SQL[:fts_set] % fts_sets.join(","), fts_args)
          end
        end
      end

      LIMIT = 50

      def get_posts(
        site_id   : Int64?   = nil,
        post_type : String?  = nil,
        state     : String?  = nil,
        q         : String?  = nil,
        page      : Int32    = 1,
      )
        filters = %w{1}
        args = [] of String

        # check page
        raise "invalid page: #{page}" unless page > 0

        if site_id
          # add site filter
          filters << "a.site_id = ?"
          args << site_id.to_s
        end

        if post_type
          # add type filter
          filters << case post_type
          when "blog"
            "x.post_id IS NOT NULL"
          when "page"
            "y.post_id IS NOT NULL"
          when "project"
            "z.post_id IS NOT NULL"
          else
            raise "unknown post type: #{post_type}"
          end
        end

        if state
          # add state filter
          filters << "b.state = ?"
          args << state
        else
          # default state filter
          filters << "b.state IN ('draft', 'posted')"
        end

        if q && q.match(/\S+/)
          # add search filter
          filters << "a.post_id IN (
            SELECT rowid
              FROM posts_fts 
             WHERE posts_fts MATCH ?
          )"

          args << q
        end

        # build where clause
        filter_sql = filters.join(" AND ")

        # count matching rows
        num_posts = (@context.dbs.ro.one(SQL[:count_posts] % [
          filter_sql
        ], args) || "0").to_i64

        # build result
        rows = [] of Hash(String, String)

        if num_posts > 0
          # get matching rows
          @context.dbs.ro.all(SQL[:get_posts] % [
            filter_sql
          ], args.concat([
            LIMIT.to_s,
            ((page - 1) * LIMIT).to_s,
          ])) do |row|
            # append row to result
            rows << row.reduce({} of String => String) do |r, k, v|
              r[k] = v.to_s
              r
            end
          end
        end

        # return results
        PagedResultSet.new(
          page:     page,
          num_rows: num_posts,
          limit:    LIMIT,
          rows:     rows,
        )
      end
    end

    class PageModel < Model
      SQL = {
        get_id: "
          SELECT b.post_id

            FROM sites a
            JOIN posts b
              ON (b.site_id = a.site_id)
            JOIN pages c
              ON (c.post_id = b.post_id)
            JOIN states d
              ON (d.state_id = b.state_id)

           WHERE a.site_id = ?
             AND b.slug = ?
             AND d.state = 'posted'

           ORDER BY b.created_at DESC
           LIMIT 1
        ",

        add: "
          INSERT INTO pages(post_id, layout_id)
            VALUES (?, (SELECT layout_id FROM layouts WHERE layout = 'default'))
        ",

        set: "
          UPDATE pages
             SET layout_id = (SELECT layout_id FROM layouts WHERE layout = ?)
           WHERE post_id = ?
        ",

        get: "
          SELECT a.post_id,
                 a.site_id,
                 c.state,
                 a.posted_at,
                 a.expires_at,
                 a.slug,
                 a.slug_lock,
                 a.name,
                 a.body,
                 d.layout

            FROM posts a
            JOIN pages b
              ON (b.post_id = a.post_id)
            JOIN states c
              ON (c.state_id = a.state_id)
            JOIN layouts d
              ON (d.layout_id = b.layout_id)

           WHERE a.post_id = ?
        ",
      }

      def get_id(
        site_id : Int64,
        slug    : String
      ) : Int64?
        r = @context.dbs.ro.one(SQL[:get_id], [site_id.to_s, slug])
        r ? r.to_i64 : nil
      end

      def add(
        site_id : Int64,
        user_id : Int64,
      ) : Int64
        db = @context.dbs.rw
        post_id = -1_i64

        db.transaction do
          post_id = @context.models.post.add(site_id, user_id)
          db.query(SQL[:add], [post_id.to_s])
        end

        post_id
      end

      def set(
        post_id         : Int64,

        site_id         : Int64?  = nil,
        state           : String? = nil,

        have_posted_at  : Bool    = false,
        posted_at       : Time?   = nil,

        have_expires_at : Bool    = false,
        expires_at      : Time?   = nil,

        slug            : String? = nil,
        slug_lock       : Bool?   = nil,

        name            : String? = nil,
        body            : String? = nil,

        layout          : String? = nil,
      )
        db = @context.dbs.rw

        db.transaction do
          @context.models.post.set(
            post_id:          post_id,

            site_id:          site_id,
            state:            state,

            have_posted_at:   have_posted_at,
            posted_at:        posted_at,

            have_expires_at:  have_expires_at,
            expires_at:       expires_at,

            slug:             slug,
            slug_lock:        slug_lock,

            name:             name,
            body:             body,
          )

          if layout
            db.query(SQL[:set], [layout, post_id.to_s])
          end
        end
      end

      def get(post_id : Int64)
        @context.dbs.ro.row(SQL[:get], [post_id.to_s]).not_nil!
      end
    end

    class ProjectModel < Model
      SQL = {
        get_id: "
          SELECT b.post_id

            FROM sites a
            JOIN posts b
              ON (b.site_id = a.site_id)
            JOIN projects c
              ON (c.post_id = b.post_id)
            JOIN states d
              ON (d.state_id = b.state_id)

           WHERE a.site_id = ?
             AND b.slug = ?
             AND d.state = 'posted'

           ORDER BY b.created_at DESC
           LIMIT 1
        ",

        add: "
          INSERT INTO projects(post_id) VALUES (?)
        ",

        set: "
          UPDATE projects
             SET repo_url = ?
           WHERE post_id = ?
        ",

        get: "
          SELECT a.post_id,
                 a.site_id,
                 c.state,
                 a.posted_at,
                 a.expires_at,
                 a.slug,
                 a.slug_lock,
                 a.name,
                 a.body,
                 b.repo_url

            FROM posts a
            JOIN projects b
              ON (b.post_id = a.post_id)
            JOIN states c
              ON (c.state_id = a.state_id)

           WHERE a.post_id = ?
        ",
      }

      def get_id(
        site_id : Int64,
        slug    : String
      ) : Int64?
        r = @context.dbs.ro.one(SQL[:get_id], [site_id.to_s, slug])
        r ? r.to_i64 : nil
      end

      def add(
        site_id : Int64,
        user_id : Int64,
      ) : Int64
        db = @context.dbs.rw
        post_id = -1_i64

        db.transaction do
          post_id = @context.models.post.add(site_id, user_id)
          db.query(SQL[:add], [post_id.to_s])
        end

        post_id
      end

      def set(
        post_id         : Int64,

        site_id         : Int64?  = nil,
        state           : String? = nil,

        have_posted_at  : Bool    = false,
        posted_at       : Time?   = nil,

        have_expires_at : Bool    = false,
        expires_at      : Time?   = nil,

        slug            : String? = nil,
        slug_lock       : Bool?   = nil,

        name            : String? = nil,
        body            : String? = nil,

        repo_url        : String? = nil,
      )
        db = @context.dbs.rw

        db.transaction do
          @context.models.post.set(
            post_id:          post_id,

            site_id:          site_id,
            state:            state,

            have_posted_at:   have_posted_at,
            posted_at:        posted_at,

            have_expires_at:  have_expires_at,
            expires_at:       expires_at,

            slug:             slug,
            slug_lock:        slug_lock,

            name:             name,
            body:             body,
          )

          if repo_url
            db.query(SQL[:set], [repo_url, post_id.to_s])
          end
        end
      end

      def get(post_id : Int64)
        @context.dbs.ro.row(SQL[:get], [post_id.to_s]).not_nil!
      end
    end

    class BlogModel < Model
      SQL = {
        get_ids: "
          SELECT b.post_id

            FROM sites a
            JOIN posts b
              ON (b.site_id = a.site_id)
            JOIN blogs c
              ON (c.post_id = b.post_id)
            JOIN states d
              ON (d.state_id = b.state_id)

           WHERE a.site_id = ?
             AND %s
             AND d.state = 'posted'
             -- TODO: handle posted_at and expired_at

           ORDER BY COALESCE(b.posted_at, b.created_at) DESC
        ",

        add: "
          INSERT INTO blogs(post_id) VALUES (?)
        ",

        get: "
          SELECT a.post_id,
                 a.site_id,
                 c.state,
                 a.posted_at,
                 a.expires_at,
                 a.slug,
                 a.slug_lock,
                 a.name,
                 a.body

            FROM posts a
            JOIN blogs b
              ON (b.post_id = a.post_id)
            JOIN states c
              ON (c.state_id = a.state_id)

           WHERE a.post_id = ?
        ",
      }

      def get_id(
        site_id : Int64,
        year    : Int32,
        month   : Int32,
        day     : Int32,
        slug    : String
      ) : Int64?
        ids = get_ids(site_id, year, month, day, slug)
        (ids.size > 0) ? ids.first : nil
      end

      def get_ids(
        site_id : Int64,
        year    : Int32?  = nil,
        month   : Int32?  = nil,
        day     : Int32?  = nil,
        slug    : String? = nil
      ) : Array(Int64)
        sql = [] of String
        args = [site_id.to_s]

        # STDERR.puts "DEBUG: site_id = #{site_id}, year = #{year}, month = #{month}, day = #{day}, slug = \"#{slug}\""

        if year
          # add year filter
          sql << "strftime('%Y', b.posted_at) + 0 = ? + 0"
          args << year.to_s

          if month
            # add month filter
            sql << "strftime('%m', b.posted_at) + 0 = ? + 0"
            args << month.to_s

            if day
              # add day filter
              sql << "strftime('%d', b.posted_at) + 0 = ? + 0"
              args << day.to_s
            end
          end
        end

        if slug
          # add slug filter
          sql << "b.slug = ?"
          args << slug
        end

        # STDERR.puts "DEBUG: args = #{args.to_json}"

        # exec query, build result
        r = [] of Int64
        @context.dbs.ro.all(SQL[:get_ids] % sql.join(" AND "), args) do |row|
          p row
          r << row["post_id"] as Int64
        end

        # return results
        r
      end

      def add(
        site_id : Int64,
        user_id : Int64,
      ) : Int64
        db = @context.dbs.rw
        post_id = -1_i64

        db.transaction do
          post_id = @context.models.post.add(site_id, user_id)
          db.query(SQL[:add], [post_id.to_s])
        end

        post_id
      end

      def set(
        post_id         : Int64,

        site_id         : Int64?  = nil,
        state           : String? = nil,

        have_posted_at  : Bool    = false,
        posted_at       : Time?   = nil,

        have_expires_at : Bool    = false,
        expires_at      : Time?   = nil,

        slug            : String? = nil,
        slug_lock       : Bool?   = nil,

        name            : String? = nil,
        body            : String? = nil,

        repo_url        : String? = nil,
      )
        @context.models.post.set(
          post_id:          post_id,

          site_id:          site_id,
          state:            state,

          have_posted_at:   have_posted_at,
          posted_at:        posted_at,

          have_expires_at:  have_expires_at,
          expires_at:       expires_at,

          slug:             slug,
          slug_lock:        slug_lock,

          name:             name,
          body:             body,
        )
      end

      def get(post_id : Int64)
        @context.dbs.ro.row(SQL[:get], [post_id.to_s]).not_nil!
      end
    end

    class UserModel < Model
      SQL = {
        login: "
          SELECT user_id,
                 password

            FROM users

           WHERE is_active
             AND email = ?
        ",

        has_role: "
          SELECT 1

            FROM users

           WHERE is_active
             AND user_id = ?
             AND role_id IN (SELECT role_id FROM roles WHERE role IN (%s))
        ",

        add: "
          INSERT INTO users(role_id, name, email, password, is_active) VALUES
            ((SELECT role_id FROM roles where role = ?), ?, ?, ?, ?)
        ",

        set: "
          UPDATE users
             SET %s
           WHERE user_id = ?
        ",

        get_users: "
          SELECT a.user_id,
                 a.name,
                 a.email,
                 a.is_active,
                 b.role

            FROM users a
            JOIN roles b
              ON (b.role_id = a.role_id)

           ORDER BY LOWER(a.email)
        ",
      }

      def login(
        email     : String,
        password  : String
      ) : Int64?
        r = nil

        if row = @context.dbs.ro.row(SQL[:login], [email])
          if Password.test(row["password"] as String, password)
            # given email and password matches active user
            r = row["user_id"] as Int64
          end
        end

        # return result
        r
      end

      def has_role?(user_id : Int64, roles : Array(String))
        raise "empty role list" unless roles.size > 0

        !!@context.dbs.ro.one(SQL[:has_role] % [
          (["?"] * roles.size).join(",")
        ], [user_id.to_s].concat(roles))
      end

      def add(
        name      : String,
        email     : String,
        password  : String,
        role      : String,
        active    : Bool,
      ) : Int64
        @context.dbs.rw.query(SQL[:add], [
          role,
          name,
          email,
          Password.create(password),
          active ? "1" : "0",
        ])

        @context.dbs.rw.last_insert_row_id.to_i64
      end

      def set(
        user_id   : Int64,
        name      : String? = nil,
        email     : String? = nil,
        password  : String? = nil,
        role      : String? = nil,
        active    : Bool?   = nil,
      )
        sets = [] of String
        args = [] of String

        if name
          sets << "name = ?"
          args << name
        end

        if email
          sets << "email = ?"
          args << email
        end

        if role
          sets << "role_id = (SELECT role_id FROM roles WHERE role = ?)"
          args << role
        end

        if password
          sets << "password = ?"
          args << Password.create(password)
        end

        if active != nil
          sets << "is_active = ?"
          args << (active ? "1" : "0")
        end

        if sets.size > 0
          args << user_id.to_s
          @context.dbs.rw.query(SQL[:set] % sets.join(", "), args)
        end
      end

      def get_users
        rows = [] of Hash(String, String)

        @context.dbs.ro.all(SQL[:get_users]) do |row|
          # append row to result
          rows << row.reduce({} of String => String) do |r, k, v|
            r[k] = v.to_s
            r
          end
        end

        rows
      end
    end

    # TODO: handle session expiration
    class SessionModel < Model
      SQL = {
        load: "
          SELECT data

            FROM sessions

           WHERE id = ?
             -- TODO:
             -- AND strftime('%s', created_at, '1 week') > strftime('%s')
             -- AND strftime('%s', updated_at, '2 hours') > strftime('%s')
        ",

        save: "
          UPDATE sessions

             SET updated_at = CURRENT_TIMESTAMP,
                 data = ?

           WHERE id = ?
        ",

        delete: "
          DELETE FROM sessions WHERE id = ?
        ",

        create: "
          INSERT INTO sessions(id, data) VALUES (?, ?)
        ",
      }

      def load(id : String) : String?
        @context.dbs.ro.one(SQL[:load], [id])
      end

      def save(id : String, data : String)
        @context.dbs.rw.query(SQL[:save], [data, id])
        nil
      end

      def delete(id : String?)
        @context.dbs.rw.query(SQL[:delete], [id]) if id
        nil
      end

      def create(data : String) : String
        # generate id
        r = SecureRandom.hex(32)

        # save session
        @context.dbs.rw.query(SQL[:create], [r, data])

        # return session id
        r
      end
    end

    class CSRFModel < Model
      getter :minutes

      def initialize(context : Context)
        super(context)
        @cache = {} of String => Int64

        # expire form after 5 minutes
        # TODO: make this configurable
        @minutes = 5
      end

      def create_token
        remove_expired_tokens

        # generate and cache new token
        r = SecureRandom.hex(16)
        @cache[r] = Time.now.epoch + 60 * @minutes

        # return token
        r
      end

      def use_token(id : String)
        remove_expired_tokens

        if @cache.has_key?(id)
          # remove token, return success
          @cache.delete(id)
          true
        else
          # return failure
          false
        end
      end

      private def remove_expired_tokens
        now = Time.now.epoch

        # FIXME: limit the size of the cache
        # to prevent insane memory use

        # remove expired entries
        @cache.delete_if do |key, val|
          val < now
        end
      end
    end

    class SiteModel < Model
      SQL = {
        get_id: "
          SELECT site_id

            FROM (
              SELECT a.site_id

                FROM site_domains a
                JOIN sites b
                  ON (b.site_id = a.site_id)

               WHERE b.is_active
                 AND a.domain = $1

              UNION ALL

              SELECT site_id

                FROM sites

               WHERE is_active
                 AND is_default
            ) a

          LIMIT 1
        ",

        get_default_id: "
          SELECT site_id

            FROM sites

           WHERE is_active
             AND is_default
        "
      }

      def get_id(host : String?) : Int64?
        r = @context.dbs.ro.one(SQL[:get_id], [host || ""])
        r ? r.to_i64 : nil
      end

      def get_default_id : Int64
        @context.dbs.ro.one(SQL[:get_default_id]).not_nil!.to_i64
      end
    end

    class RoleModel < Model
      SQL = {
        get_roles: "
          SELECT role,
                 name

            FROM roles

           ORDER BY sort
        ",
      }

      def get_roles : Array(Hash(String, String))
        r = [] of Hash(String, String)

        @context.dbs.ro.all(SQL[:get_roles]) do |row|
          r << { "role": row["role"] as String, "name": row["name"] as String }
        end

        r
      end
    end
  end

  class ModelSet
    def initialize(@context : Context)
    end

    define_lazy_getters({
      user:     Models::UserModel,
      session:  Models::SessionModel,
      csrf:     Models::CSRFModel,
      post:     Models::PostModel,
      page:     Models::PageModel,
      project:  Models::ProjectModel,
      blog:     Models::BlogModel,
      site:     Models::SiteModel,
      role:     Models::RoleModel,
    })
  end

  class Session < Hash(String, String)
    getter :session_id

    # session cookie name
    # FIXME: does this belong here?
    COOKIE = "guff_session"

    def initialize(@context : Context)
      super()
      @session_id = nil
    end

    def load(id : String)
      begin
        # clear existing session
        clear

        # load session values
        JSON.parse(@context.models.session.load(id).not_nil!).each do |key, val|
          self[key.as_s] = val.as_s
        end

        # save session id
        @session_id = id

        # return success
        true
      rescue err
        STDERR.puts "session load failed: #{err}"
        # invalid session id, return failure
        false
      end
    end

    def create(hash : Hash(String, String)) : String
      clear
      merge!(hash)
      @session_id = @context.models.session.create(hash.to_json)
    end

    def save
      if valid?
        @context.models.session.save(@session_id.not_nil!, to_json)

        # return success
        true
      else
        # no session, return failure
        false
      end
    end

    def clear
      super
      @session_id = nil
    end

    def delete : String?
      r = @session_id

      if valid?
        @context.models.session.delete(r)
        clear
      end

      r
    end

    def valid?
      @session_id != nil
    end
  end

  class DatabasePair
    getter :ro, :rw

    def initialize(
      path  : String,
      debug : Bool = false
    )
      @ro = Database.new(
        path:       path,
        read_only:  true,
        debug:      debug
      )

      @rw = Database.new(
        path:       path,
        read_only:  false,
        debug:      debug
      )
    end
  end

  class Context
    getter :config, :dbs

    def initialize(@config : Config)
      @dbs = DatabasePair.new(@config.db_path, development?)
    end

    def models
      @models ||= ModelSet.new(self)
    end

    def session
      @session ||= Session.new(self)
    end

    def user_id : Int64?
      session["user_id"]? ? session["user_id"].to_i64 : nil
    end

    def has_role?(roles : Array(String))
      id = user_id
      id ? models.user.has_role?(id, roles) : false
    end

    def development?
      @is_development ||= (@config.env == "development") as Bool
    end
  end

  module APIs
    module PostAPI
      def do_post_get_posts(params : HTTP::Params)
        @context.models.post.get_posts(
          site_id:    params["site_id"]? ? params["site_id"].to_i64 : nil,
          state:      params["state"]?,
          post_type:  params["post_type"]?,
          q:          params["q"]?,
          page:       params["page"].to_i32,
        )
      end
    end

    module PageAPI
      def do_page_add(params : HTTP::Params)
        post_id = @context.models.page.add(
          site_id:   params["site_id"]? ? params["site_id"].to_i64 : @context.models.site.get_default_id,
          user_id:   @context.user_id.not_nil!,
        )

        { "post_id": post_id }
      end

      def do_page_set(params : HTTP::Params)
        @context.models.page.set(
          post_id:          params["post_id"].to_i64,

          site_id:          params["site_id"]? ? params["site_id"].to_i64 : nil,
          state:            params["state"]?,

          have_posted_at:   !!params["posted_at"]?,
          posted_at:        params["posted_at"]? ? ISO8601.parse(params["posted_at"]) : nil,

          have_expires_at:  !!params["expires_at"]?,
          expires_at:       params["expires_at"]? ? ISO8601.parse(params["expires_at"]) : nil,

          slug:             params["slug"]?,
          slug_lock:        params["slug_lock"]? ? (params["slug_lock"] == "t") : nil,

          name:             params["name"]?,
          body:             params["body"]?,

          layout:           params["layout"]?,
        )

        nil
      end

      def do_page_get(params : HTTP::Params)
        @context.models.page.get(
          post_id: params["post_id"].to_i64
        ).reduce({} of String => String) do |r, k, v|
          r[k] = v.to_s
          r
        end
      end
    end

    module ProjectAPI
      def do_project_add(params : HTTP::Params)
        post_id = @context.models.project.add(
          site_id:   params["site_id"]? ? params["site_id"].to_i64 : @context.models.site.get_default_id,
          user_id:   @context.user_id.not_nil!,
        )

        { "post_id": post_id }
      end

      def do_project_set(params : HTTP::Params)
        @context.models.project.set(
          post_id:          params["post_id"].to_i64,

          site_id:          params["site_id"]? ? params["site_id"].to_i64 : nil,
          state:            params["state"]?,

          have_posted_at:   !!params["posted_at"]?,
          posted_at:        params["posted_at"]? ? ISO8601.parse(params["posted_at"]) : nil,

          have_expires_at:  !!params["expires_at"]?,
          expires_at:       params["expires_at"]? ? ISO8601.parse(params["expires_at"]) : nil,

          slug:             params["slug"]?,
          slug_lock:        params["slug_lock"]? ? (params["slug_lock"] == "t") : nil,

          name:             params["name"]?,
          body:             params["body"]?,

          repo_url:         params["repo_url"]?,
        )

        nil
      end

      def do_project_get(params : HTTP::Params)
        @context.models.project.get(
          post_id: params["post_id"].to_i64
        ).reduce({} of String => String) do |r, k, v|
          r[k] = v.to_s
          r
        end
      end
    end

    module BlogAPI
      def do_blog_add(params : HTTP::Params)
        post_id = @context.models.blog.add(
          site_id:   params["site_id"]? ? params["site_id"].to_i64 : @context.models.site.get_default_id,
          user_id:   @context.user_id.not_nil!,
        )

        { "post_id": post_id }
      end

      def do_blog_set(params : HTTP::Params)
        @context.models.blog.set(
          post_id:          params["post_id"].to_i64,

          site_id:          params["site_id"]? ? params["site_id"].to_i64 : nil,
          state:            params["state"]?,

          have_posted_at:   !!params["posted_at"]?,
          posted_at:        params["posted_at"]? ? ISO8601.parse(params["posted_at"]) : nil,

          have_expires_at:  !!params["expires_at"]?,
          expires_at:       params["expires_at"]? ? ISO8601.parse(params["expires_at"]) : nil,

          slug:             params["slug"]?,
          slug_lock:        params["slug_lock"]? ? (params["slug_lock"] == "t") : nil,

          name:             params["name"]?,
          body:             params["body"]?,
        )

        nil
      end

      def do_blog_get(params : HTTP::Params)
        @context.models.blog.get(
          post_id: params["post_id"].to_i64
        ).reduce({} of String => String) do |r, k, v|
          r[k] = v.to_s
          r
        end
      end
    end

    module UserAPI
      def do_user_add(params : HTTP::Params)
        user_id = @context.models.user.add(
          name:     params["name"],
          email:    params["email"],
          password: params["password"],
          active:   (params["active"] == "t"),
          role:     params["role"],
        )

        { "user_id": user_id }
      end

      def do_user_set(params : HTTP::Params)
        @context.models.user.set(
          user_id:  params["user_id"].to_i64,
          name:     params["name"]?,
          email:    params["email"]?,
          password: params["password"]?,
          active:   params["active"]? ? (params["active"] == "t") : nil,
          role:     params["role"]?,
        )

        nil
      end

      def do_user_get_users(params : HTTP::Params)
        @context.models.user.get_users
      end
    end
  end

  module Views
    abstract class View
      def initialize(@context : Context)
      end

      def h(s : String) : String
        HTML.escape(s)
      end
    end

    class TabView < View
      def initialize(
        context : Context,
        @prefix : String,
        @tab : Hash(Symbol, String)
      )
        super(context)
        @id = h("%s-tab-%s" % [@prefix, @tab[:id]]) as String
        @target = h("%s-pane-%s" % [@prefix, @tab[:id]]) as String
      end

      private def v(id : Symbol) : String
        raise "unknown id: #{id}" unless @tab.has_key?(id)
        h(@tab[id])
      end

      ECR.def_to_s("src/views/tab.ecr")
    end

    abstract class HTMLView < View
      TEMPLATES = {
        script: "<script type='text/javascript' src='%s'></script>",
        style:  "<link rel='stylesheet' type='text/css' href='%s'/>",
      }

      private def assets(key : Symbol, paths : Array(String))
        String.build do |io|
          paths.each do |path|
            io << TEMPLATES[key] % [h(path)]
          end
        end
      end

      def scripts(paths : Array(String))
        assets(:script, paths)
      end

      def styles(paths : Array(String))
        assets(:style, paths)
      end

      def tabs(prefix : String, rows : Array(Hash(Symbol, String)))
        String.build do |io|
          rows.each do |row|
            TabView.new(@context, prefix, row).to_s(io)
          end
        end
      end
    end

    class AdminPageView < HTMLView
      TITLE = "Guff Admin"

      TABS = {
        "admin": [{
          id:   "home",
          css:  "active",
          icon: "fa-home",
          name: "Home",
          text: "View home tab.",
        }, {
          id:   "posts",
          css:  "",
          icon: "fa-cubes",
          name: "Posts",
          text: "Manage blog, pages, and projects.",
        }, {
          id:   "files",
          css:  "",
          icon: "fa-files-o",
          name: "Files",
          text: "Manage files.",
        }, {
          id:   "users",
          css:  "",
          icon: "fa-users",
          name: "Users",
          text: "Manage users and permissions.",
        }, {
          id:   "settings",
          css:  "",
          icon: "fa-cog",
          name: "Settings",
          text: "Configure site settings.",
        }],
      }

      TEMPLATES = {
        option: "
          <option value='%s'>%s</option>
        ",

        new_post_button: "
          <a
            href='#'
            class='btn btn-primary add-post'
            title='Create new blog post.'
            data-type='blog'
          >
            <i class='fa fa-plus-circle'></i>
            New Post
          </a><!-- btn -->

          <a
            href='#'
            class='btn btn-primary'
            title='Show additonal options.'
            data-toggle='dropdown'
          >
            <i class='fa fa-caret-down'></i>
          </a>

          <ul class='dropdown-menu'>
            <li>
              <a
                href='#'
                title='Create new page.'
                class='add-post'
                data-type='page'
              >
                <i class='fa fa-fw fa-bookmark-o'></i>
                New Page
              </a>
            </li>

            <li>
              <a
                href='#'
                title='Create new project.'
                class='add-post'
                data-type='project'
              >
                <i class='fa fa-fw fa-cube'></i>
                New Project
              </a>
            </li>
          </ul>
        ",

        state_button: "
          <a
            href='#'
            class='btn %s'
            title='Mark as %s.'
            data-val='%s'
          >
            <i class='fa %s'></i>
            %s
          </a>
        ",
      }

      def tabs(id : String)
        super(id, TABS[id])
      end

      private def new_post_button
        TEMPLATES[:new_post_button]
      end

      private def role_options
        @role_options ||= String.build do |io|
          @context.models.role.get_roles.each do |row|
            io << TEMPLATES[:option] % %w{role name}.map { |key| h(row[key]) }
          end
        end
      end

      STATES = [{
        id:   "draft",
        name: "Draft",
        icon: "fa-wrench",
        css:  "btn-primary",
      }, {
        id:   "posted",
        name: "Posted",
        icon: "fa-bullhorn",
        css:  "btn-default",
      }, {
        id:   "deleted",
        name: "Deleted",
        icon: "fa-trash",
        css:  "btn-default",
      }]

      private def state_buttons
        @state_buttons ||= String.build do |io|
          STATES.each do |row|
            io << TEMPLATES[:state_button] % [
              h(row[:css]),
              h(row[:name]),
              h(row[:id]),
              h(row[:icon]),
              h(row[:name])
            ]
          end
        end
      end

      ECR.def_to_s("src/views/admin-page.ecr")
    end

    class LoginPageView < HTMLView
      def initialize(context : Context, @error : String? = nil)
        super(context)
      end

      def get_csrf_token
        @context.models.csrf.create_token
      end

      ECR.def_to_s("src/views/login-page.ecr")
    end

    class LogoutPageView < HTMLView
      ECR.def_to_s("src/views/logout-page.ecr")
    end
  end

  module Handlers
    abstract class Handler < HTTP::Handler
      def initialize(@context : Context)
        super()
      end

      protected def valid_origin_headers?(headers : HTTP::Headers)
        # FIXME: need to compare these against something rather than
        # just making sure that they are there
        %w{origin referer}.any? do |key|
          headers[key]? && headers[key].size > 0
        end
      end

      protected def get_site_id(host : String?) : Int64?
        @context.models.site.get_id(host)
      end
    end

    abstract class AuthenticatedHandler < Handler
      def initialize(context : Context, @roles : Array(String))
        super(context)
      end

      def call(context : HTTP::Server::Context)
        if @context.has_role?(@roles)
          authenticated_call(context)
        else
          call_next(context)
        end
      end

      abstract def authenticated_call(context : HTTP::Server::Context)
    end

    class SessionHandler < Handler
      def call(context : HTTP::Server::Context)
        # clear session
        @context.session.clear

        if cookie = context.request.cookies[Guff::Session::COOKIE]?
          # load session
          @context.session.load(cookie.value)
        end

        call_next(context)

        if @context.session.valid?
          @context.session.save
        end
      end
    end

    class APIHandler < Handler
      PATH_RE = %r{^/guff/api/(?<namespace>[\w_-]+)/(?<method>[\w_-]+)$}

      API_MODULES = [
        APIs::PostAPI,
        APIs::UserAPI,
        APIs::PageAPI,
        APIs::ProjectAPI,
        APIs::BlogAPI,
      ]

      include_api_modules(API_MODULES)

      def call(context : HTTP::Server::Context)
        if context.request.method == "POST" ||
           (@context.development? && context.request.method == "GET")
          if md = PATH_RE.match(context.request.path.not_nil!)
            namespace, method = %w{namespace method}.map { |k| md[k] }

            # get query parameteres
            params = if (context.request.method == "GET")
              context.request.query_params
            else
              HTTP::Params.parse(context.request.body || "")
            end

            code, data = begin
              { 200, api_method_dispatch(API_MODULES) }
            rescue err
              STDERR.puts "ERROR: #{err}"
              { 400, { "error": err.to_s } }
            end

            # send json response
            context.response.status_code = code
            context.response.content_type = "application/json; charset=utf-8"
            data.to_json(context.response)
          else
            call_next(context)
          end
        else
          call_next(context)
        end
      end
    end

    class AssetsHandler < Handler
      def initialize(context : Context)
        super(context)
        @etags = {} of String => String
      end

      def call(context : HTTP::Server::Context)
        req_path = context.request.path.not_nil!

        if matching_request?(context.request.method, req_path) &&
           valid_origin_headers?(context.request.headers)
          # get expanded path to file
          if abs_path = expand_path(req_path)
            # get file digest
            etag = get_file_etag(abs_path)

            # check for cache header
            if context.request.headers["if-none-match"]? == etag
              # cached, send 304 not modified
              context.response.status_code = 304
            else
              # not cached, set code and send headers
              context.response.headers["x-frame-options"] = "SAMEORIGIN"
              context.response.status_code = 200
              context.response.content_type = MimeType.from_path(abs_path)
              context.response.content_length = File.size(abs_path)
              context.response.headers["etag"] = etag

              if context.request.method == "GET"
                # send body for GET requests
                File.open(abs_path) do |fh|
                  IO.copy(fh, context.response)
                end
              end
            end
          else
            # expanded path does not exist
            call_next(context)
          end
        else
          # not a matching request
          call_next(context)
        end
      end

      VALID_METHODS = %w{GET HEAD}
      PATH_RE = %r{^/guff/assets/}

      private def matching_request?(method, path)
        VALID_METHODS.includes?(method) && PATH_RE.match(path)
      end

      private def expand_path(req_path : String) : String?
        # unescape path, check for nil byte
        path = URI.unescape(req_path)
        return nil if path.includes?('\0')

        # build absolute path
        r = File.join(
          @context.config.system_dir,
          "assets",
          File.expand_path(path.gsub(PATH_RE, ""), "/")
        )

        # return path if file exists, or nil otherwise
        File.file?(r) ? r : nil
      end

      private def get_file_etag(path : String) : String
        # FIXME: rather than a hash this should be an HMAC
        @etags[path] ||= OpenSSL::Digest.new("SHA1").file(path).hexdigest
      end
    end

    class AdminPageHandler < AuthenticatedHandler
      def initialize(context : Context)
        super(context, %w{admin editor})
      end

      PATH_RE = %r{^/guff/admin\.html$}

      def authenticated_call(context : HTTP::Server::Context)
        if context.request.path.not_nil!.match(PATH_RE)
          context.response.headers["x-frame-options"] = "SAMEORIGIN"
          context.response.content_type = "text/html; charset=utf-8"
          context.response.status_code = 200
          Views::AdminPageView.new(@context).to_s(context.response)
        else
          call_next(context)
        end
      end
    end

    class LoginPageHandler < Handler
      PATH_RE = %r{^/guff/login\.html$}
      VALID_METHODS = %w{GET POST}

      def call(context : HTTP::Server::Context)
        if VALID_METHODS.includes?(context.request.method) &&
           PATH_RE.match(context.request.path.not_nil!)
          case context.request.method
          when "GET"
            reply(context.response)
          when "POST"
            begin
              # check for valid origin or referer header
              unless valid_origin_headers?(context.request.headers)
                raise "missing origin and referer headers"
              end

              # create session
              session_id = @context.session.create({
                "user_id": login(context.request.body).to_s,
              })

              # add cookie
              context.response.cookies << HTTP::Cookie.new(
                name:       Guff::Session::COOKIE,
                value:      session_id as String,
                http_only:  true,

                # TODO
                # expires:
                # secure:
              )

              # redirect to admin panel
              context.response.headers["location"] = "/guff/admin.html"
              context.response.status_code = 302
            rescue err
              # log error
              STDERR.puts "login failed: #{err}"
              reply(context.response, "invalid login")
            end
          else
            raise "invalid HTTP method"
          end
        else
          call_next(context)
        end
      end

      private def reply(
        response : HTTP::Server::Response,
        error : String? = nil
      )
        response.headers["x-frame-options"] = "SAMEORIGIN"
        response.content_type = "text/html; charset=utf-8"
        response.status_code = 200
        Views::LoginPageView.new(@context, error).to_s(response)
      end

      private def login(body : String?)
        # check body
        raise "empty request body" if body.nil? || body.size == 0

        # parse request parameters
        params = HTTP::Params.parse(body.not_nil!)

        # check login parameters
        raise "missing login parameters" unless %w{
          username
          password
          csrf_token
        }.all? do |key|
          params.has_key?(key) && params[key].size > 0
        end

        # check csrf token
        unless @context.models.csrf.use_token(params["csrf_token"])
          raise "invalid csrf token"
        end

        # try login
        user_id =  @context.models.user.login(
          params["username"],
          params["password"]
        )

        # check user id
        raise "invalid credentials" unless user_id

        # return user id
        user_id.not_nil!
      end
    end

    class LogoutPageHandler < Handler
      PATH_RE = %r{^/guff/logout\.html$}

      def call(context : HTTP::Server::Context)
        if context.request.method == "GET" &&
           PATH_RE.match(context.request.path.not_nil!) &&
           valid_origin_headers?(context.request.headers)
          # delete session
          @context.session.delete

          # clear cookie
          context.response.cookies << HTTP::Cookie.new(
            name:       Guff::Session::COOKIE,
            value:      "",
            expires:    Time.epoch(0),
            http_only:  true,
          )

          # build remaining headers
          context.response.headers["x-frame-options"] = "SAMEORIGIN"
          context.response.content_type = "text/html; charset=utf-8"
          context.response.status_code = 200

          # draw page
          Views::LogoutPageView.new(@context).to_s(context.response)
        else
          call_next(context)
        end
      end
    end

    class PageHandler < Handler
      PATH_RE = %r{^/(?<slug>[^/]+)\.html$}

      def call(context : HTTP::Server::Context)
        if post_id = get_post_id(context.request)
          # TODO: render page
          context.response.headers["x-frame-options"] = "SAMEORIGIN"
          context.response.content_type = "text/html; charset=utf-8"
          context.response.status_code = 200
          context.response << "page: #{post_id}"
        else
          # unknown page
          call_next(context)
        end
      end

      private def get_post_id(request : HTTP::Request) : Int64?
        r = nil

        if request.method == "GET"
          if md = PATH_RE.match(request.path.not_nil!)
            if site_id = get_site_id(request.headers["host"]?)
              r = @context.models.page.get_id(
                site_id: site_id,
                slug: md["slug"],
              )
            end
          end
        end

        # return result
        r
      end
    end

    class ProjectHandler < Handler
      PATH_RE = %r{^/(?<slug>[^/]+)/?$}

      def call(context : HTTP::Server::Context)
        if post_id = get_post_id(context.request)
          path = context.request.path.not_nil!

          if /\/$/.match(path)
            # TODO: render page
            context.response.headers["x-frame-options"] = "SAMEORIGIN"
            context.response.content_type = "text/html; charset=utf-8"
            context.response.status_code = 200
            context.response << "project: #{post_id}"
          else
            # redirect to project
            context.response.headers["location"] = path + "/"
            context.response.status_code = 302
          end
        else
          # unknown page
          call_next(context)
        end
      end

      private def get_post_id(request : HTTP::Request) : Int64?
        r = nil

        if request.method == "GET"
          if md = PATH_RE.match(request.path.not_nil!)
            if site_id = get_site_id(request.headers["host"]?)
              r = @context.models.project.get_id(
                site_id: site_id,
                slug: md["slug"],
              )
            end
          end
        end

        # return result
        r
      end
    end

    class BlogPostHandler < Handler
      PATH_RE = %r{^
        /(?<year>\d{4})
        /(?<month>\d{2})
        /(?<day>\d{2})
        /(?<slug>[^/]+)\.html
      $}x

      def call(context : HTTP::Server::Context)
        if id = get_id(context.request)
          # TODO: render page
          context.response.headers["x-frame-options"] = "SAMEORIGIN"
          context.response.content_type = "text/html; charset=utf-8"
          context.response.status_code = 200
          context.response << "blog post id: #{id}"
        else
          # unknown page
          call_next(context)
        end
      end

      private def get_id(request : HTTP::Request) : Int64?
        r = nil

        if request.method == "GET"
          if md = PATH_RE.match(request.path.not_nil!)
            if site_id = get_site_id(request.headers["host"]?)
              r = @context.models.blog.get_id(
                site_id:  site_id,
                year:     md["year"].to_i,
                month:    md["month"].to_i,
                day:      md["day"].to_i,
                slug:     md["slug"],
              )
            end
          end
        end

        # return result
        r
      end
    end

    class BlogListHandler < Handler
      PATH_RE = %r{^
        /(?<year>\d{4})/
        (
          (?<month>\d{2})/
          ((?<day>\d{2})/)?
        )?
      $}x

      def call(context : HTTP::Server::Context)
        if ids = get_ids(context.request)
          # TODO: render page
          context.response.headers["x-frame-options"] = "SAMEORIGIN"
          context.response.content_type = "text/html; charset=utf-8"
          context.response.status_code = 200
          context.response << "blog list: ids = #{ids.join(", ")}"
        else
          # unknown page
          call_next(context)
        end
      end

      private def get_ids(request : HTTP::Request) : Array(Int64)?
        r = nil

        if request.method == "GET"
          if md = PATH_RE.match(request.path.not_nil!)
            if site_id = get_site_id(request.headers["host"]?)
              r = @context.models.blog.get_ids(
                site_id:  site_id,
                year:     md["year"].to_i,
                month:    md["month"]? ? md["month"].to_i : nil,
                day:      md["day"]? ? md["day"].to_i : nil,
              )
            end
          end
        end

        # return result
        r
      end
    end

    HANDLERS = [{
      dev:  true,
      id:   :error,
    }, {
      dev:  false,
      id:   :log,
    }, {
      dev:  false,
      id:   :deflate,
    }, {
      dev:  false,
      id:   :assets,
    }, {
      dev:  false,
      id:   :page,
    }, {
      dev:  false,
      id:   :project,
    }, {
      dev:  false,
      id:   :blog_post,
    }, {
      dev:  false,
      id:   :blog_list,
    }, {
      dev:  false,
      id:   :login,
    }, {
      dev:  false,
      id:   :session,
    }, {
      dev:  false,
      id:   :api,
    }, {
      dev:  false,
      id:   :admin,
    }, {
      dev:  false,
      id:   :logout,
    }]

    def self.get(context : Context) : Array(HTTP::Handler)
      HANDLERS.select { |row|
        !(row[:dev] as Bool) || context.development?
      }.map { |row|
        make_handler(row[:id] as Symbol, context)
      }
    end

    def self.make_handler(
      handler_id : Symbol,
      context : Context
    ) : HTTP::Handler
      case handler_id
      when :error
        HTTP::ErrorHandler.new
      when :log
        HTTP::LogHandler.new
      when :deflate
        HTTP::DeflateHandler.new
      when :session
        SessionHandler.new(context)
      when :api
        APIHandler.new(context)
      when :assets
        AssetsHandler.new(context)
      when :admin
        AdminPageHandler.new(context)
      when :login
        LoginPageHandler.new(context)
      when :logout
        LogoutPageHandler.new(context)
      when :page
        PageHandler.new(context)
      when :project
        ProjectHandler.new(context)
      when :blog_post
        BlogPostHandler.new(context)
      when :blog_list
        BlogListHandler.new(context)
      else
        raise "unknown handler id: #{handler_id}"
      end
    end
  end

  module CLI
    module Actions
      abstract class Action
        def self.run(config : Config)
          new(config).run
        end

        def initialize(@config : Config)
        end

        abstract def run
      end

      class InitAction < Action
        SQL = [%{
          CREATE TABLE sites (
            site_id     INTEGER PRIMARY KEY,

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

            is_active   BOOLEAN NOT NULL DEFAULT false,

            is_default  BOOLEAN NOT NULL DEFAULT false
          )
        }, %{
          INSERT INTO sites(site_id, name, is_active, is_default) VALUES
            (1, 'default', 1, 1)
        }, %{
          CREATE TABLE site_domains (
            site_id     INTEGER NOT NULL
                        REFERENCES sites(site_id),

            domain      TEXT UNIQUE NOT NULL CHECK (
                          LENGTH(domain) > 0 AND
                          domain = LOWER(domain) AND
                          domain NOT LIKE '% %'
                        )
          )
        }, %{
          CREATE TABLE roles (
            role_id     INTEGER PRIMARY KEY,

            -- internal role name
            role        TEXT UNIQUE NOT NULL CHECK (
                          LENGTH(role) > 0 AND
                          role = LOWER(role)
                        ),

            -- user-visible role name
            name        TEXT UNIQUE NOT NULL
                        CHECK (LENGTH(name) > 0),

            sort        INTEGER UNIQUE NOT NULL
          )
        }, %{
          INSERT INTO roles(role_id, role, name, sort) VALUES
            (1, 'viewer', 'Viewer', 1),
            (2, 'editor', 'Editor', 2),
            (3, 'admin', 'Admin', 3)
        }, %{
          CREATE TABLE users (
            user_id     INTEGER PRIMARY KEY,

            role_id     INTEGER NOT NULL
                        REFERENCES roles(role_id),

            name        TEXT UNIQUE NOT NULL,
            email       TEXT UNIQUE NOT NULL CHECK (
                          LENGTH(email) > 0 AND
                          email LIKE '%@%'
                        ),

            password    TEXT NOT NULL DEFAULT '',

            is_active   BOOLEAN NOT NULL DEFAULT false
          )
        }, %{
          CREATE TABLE states (
            state_id    INTEGER PRIMARY KEY,

            -- internal state name
            state       TEXT UNIQUE NOT NULL CHECK (
                          LENGTH(state) > 0 AND
                          state = LOWER(state)
                        ),

            -- user-visible state name
            state_name  TEXT UNIQUE NOT NULL
                        CHECK (LENGTH(state_name) > 0)
          )
        }, %{
          INSERT INTO states(state_id, state, state_name) VALUES
            (1, 'draft', 'Draft'),
            (2, 'posted', 'Posted'),
            (3, 'deleted', 'Deleted')
        }, %{
          CREATE TABLE posts (
            post_id     INTEGER PRIMARY KEY,

            site_id     INTEGER NOT NULL
                        REFERENCES sites(site_id),

            created_at  TIMESTAMP WITH TIME ZONE NOT NULL
                        DEFAULT CURRENT_TIMESTAMP,

            created_by  INTEGER NOT NULL
                        REFERENCES users(user_id),

            state_id    INTEGER NOT NULL
                        REFERENCES states(state_id),

            posted_at   TIMESTAMP WITH TIME ZONE,
            expires_at  TIMESTAMP WITH TIME ZONE,

            name        TEXT NOT NULL DEFAULT '',

            slug        TEXT NOT NULL DEFAULT '' CHECK (
                          slug NOT LIKE '% %' AND
                          slug = LOWER(slug)
                        ),

            slug_lock   BOOLEAN NOT NULL DEFAULT 1,

            body        TEXT NOT NULL DEFAULT ''
          )
        }, %{
          CREATE INDEX in_posts_site_id ON posts(site_id)
        }, %{
          CREATE VIRTUAL TABLE posts_fts USING fts4(
            name,
            slug,
            body
          )
        }, %{
          CREATE TABLE blogs (
            post_id     INTEGER PRIMARY KEY
                        REFERENCES posts(post_id)
          )
        }, %{
          CREATE TABLE projects (
            post_id     INTEGER PRIMARY KEY
                        REFERENCES posts(post_id),

            repo_url    TEXT NOT NULL DEFAULT ''
                        CHECK (repo_url NOT LIKE '% %')
          )
        }, %{
          CREATE TABLE layouts (
            layout_id   INTEGER PRIMARY KEY,

            -- internal layout name
            layout      TEXT UNIQUE NOT NULL CHECK (
                          LENGTH(layout) > 0 AND
                          layout = LOWER(layout)
                        ),

            -- user-visible layout name
            layout_name TEXT UNIQUE NOT NULL
                        CHECK (LENGTH(layout_name) > 0),

            is_default  BOOLEAN NOT NULL
          )
        }, %{
          INSERT INTO layouts(layout_id, layout, layout_name, is_default) VALUES
            (1, 'blank', 'Blank', 0),
            (2, 'default', 'Default', 1)
        }, %{
          CREATE TABLE pages (
            post_id     INTEGER PRIMARY KEY
                        REFERENCES posts(post_id),

            layout_id   INTEGER NOT NULL
                        REFERENCES layouts(layout_id)
          )
        }, %{
          CREATE TABLE sessions (
            id          TEXT PRIMARY KEY,

            created_at  TIMESTAMP WITH TIME ZONE NOT NULL
                        DEFAULT CURRENT_TIMESTAMP,

            updated_at  TIMESTAMP WITH TIME ZONE NOT NULL
                        DEFAULT CURRENT_TIMESTAMP,

            data        TEXT NOT NULL
          )
        }]

        def run
          STDERR.puts "Initializing data directory"
          Dir.mkdir(@config.data_dir) unless Dir.exists?(@config.data_dir)

          Guff::Database.new(@config.db_path) do |db|
            SQL.each do |sql|
              db.query(sql)
            end

            # gen random password and add admin user
            password = Password.random_password
            add_user(db, "Admin", "admin@admin", password)
            add_user(db, "Test", "test@test", "test")
            add_test_data(db)

            STDERR.puts "admin user: admin@admin, password: #{password}"
          end
        end

        ADD_USER_SQL = %{
          INSERT INTO users(name, email, password, role_id, is_active) VALUES
            (?, ?, ?, (SELECT role_id FROM roles WHERE role = ?), 1)
        }

        private def add_user(
          db        : Database,
          name      : String,
          email     : String,
          password  : String
        ) : Int64
          db.query(ADD_USER_SQL, [
            name,
            email,
            Password.create(password),
            "admin",
          ])
          db.last_insert_row_id.to_i64
        end

        # generate random password
        private def gen_password
          # STDERR.puts "DEBUG: generating random password"
          SecureRandom.base64(6 + rand(6)).strip.gsub(/\=+$/, "")
        end

        TEST_DATA_SQL = [%{
          INSERT INTO posts (
            post_id,
            site_id,
            created_by,
            state_id,
            posted_at,
            name,
            slug,
            body
          ) VALUES (
            1,
            1,
            1,
            (SELECT state_id FROM states WHERE state = 'posted'),
            CURRENT_TIMESTAMP,
            'Test Page',
            'test-page',
            'This is the body of a test page.'
          ), (
            2,
            1,
            1,
            (SELECT state_id FROM states WHERE state = 'posted'),
            CURRENT_TIMESTAMP,
            'Test Project',
            'test-project',
            'This is the body of a test project.'
          ), (
            3,
            1,
            1,
            (SELECT state_id FROM states WHERE state = 'posted'),
            CURRENT_TIMESTAMP,
            'Test Blog',
            'test-blog',
            'This is the body of a test blog entry.'
          )
        }, %{
          INSERT INTO posts_fts(rowid, name, slug, body)
            SELECT post_id, name, slug, body FROM posts
        }, %{
          INSERT INTO pages(post_id, layout_id) VALUES (
            1,
            (SELECT layout_id FROM layouts WHERE layout = 'default')
          )
        }, %{
          INSERT INTO projects(post_id) VALUES (2)
        }, %{
          INSERT INTO blogs(post_id) VALUES (3)
        }]

        private def add_test_data(db)
          # STDERR.puts "DEBUG: adding test data"
          TEST_DATA_SQL.each do |sql|
            db.query(sql)
          end
        end
      end

      class RunAction < Action
        def run
          STDERR.puts "Running web server"
          check_dirs

          # create context
          context = Context.new(@config)

          STDERR.puts "listening on %s:%s" % [@config.host, @config.port]

          # run server
          HTTP::Server.new(
            @config.host,
            @config.port.to_i,
            Handlers.get(context)
          ).listen
        end

        private def check_dirs
          {
            "system": @config.system_dir,
            "data":   @config.data_dir,
          }.each do |name, dir|
            unless Dir.exists?(dir)
              raise "missing #{name} directory: \"#{dir}\""
            end
          end
        end
      end
    end

    def self.run(app : String, args : Array(String))
      begin
        begin
          # parse command-line arguments
          config = Config.parse(app, args)
        rescue err
          raise "#{err}.  Use --help for usage"
        end

        case config.mode
        when "init"
          Actions::InitAction.run(config)
        when "run"
          Actions::RunAction.run(config)
        when "help"
          # do nothing
        else
          # never reached
          raise "unknown mode: #{config.mode}"
        end
      rescue err
        STDERR.puts "ERROR: #{err}."
        exit -1
      end
    end
  end
end

Guff::CLI.run($0, ARGV)