path: root/src/guff/models/post.cr

module Guff
  class PostModel < Model
    SQL = TemplateCache.new({
      tags: "
        SELECT DISTINCT
               post_id

          FROM (%{sql})
      ",

      tag: "
        SELECT a.post_id

          FROM post_tags a
          JOIN tags b
            ON (b.tag_id = a.tag_id)

         WHERE b.name = '%{tag}'
      ",

      count_posts: "
        SELECT COUNT(*)

          FROM posts a
          JOIN (%{tags}) b
            ON (b.post_id = a.post_id)

         WHERE a.site_id = :site_id
           AND %{filter}
      ",

      get_posts: "
        SELECT %{cols}

          FROM posts a
          JOIN (%{tags}) b
            ON (b.post_id = a.post_id)

         WHERE a.site_id = :site_id
           AND %{filter}

         ORDER BY %{sort}
         LIMIT :limit OFFSET :offset
      ",

      update_post: "
        UPDATE posts
           SET %{sets}
         WHERE site_id = :site_id
           AND post_id = :post_id
      ",

      add_post: "
        INSERT INTO posts(site_id, slug, name, raw_body, body)
             VALUES (:site_id, :slug, :name, :raw_body, :body)
      ",

      remove_posts: "
        UPDATE posts

           SET state = (SELECT state
                          FROM post_states
                         WHERE name = 'deleted')

         WHERE site_id = :site_id
           AND post_id IN (%{post_ids})
      ",

      set_tags_delete: "
        DELETE FROM post_tags WHERE post_id = (
          SELECT post_id
            FROM posts
           WHERE site_id = :site_id
             AND post_id = :post_id
        )
      ",

      set_tags_insert: "
        INSERT INTO post_tags(post_id, tag_id)
          SELECT a.post_id,
                 b.tag_id

            FROM posts a
           CROSS JOIN tags b

           WHERE a.site_id = :site_id
             AND a.post_id = :post_id
             AND b.name IN (%{tags})
      ",
    })

    def initialize(models : Models)
      super(models, SQL)
    end

    COLUMNS = {
      "post_id": {
        default:  true,
        sortable: true,
        clause:   "a.post_id",
      },

      "slug": {
        default:  true,
        sortable: true,
        clause:   "a.slug",
      },

      "name": {
        default:  true,
        sortable: true,
        clause:   "a.name",
      },

      "body": {
        default:  true,
        sortable: true,
        clause:   "a.body",
      },

      "raw_body": {
        default:  false,
        sortable: true,
        clause:   "a.raw_body",
      },

      "posted_at": {
        default:  true,
        sortable: true,
        clause:   "a.posted_at",
      },

      "created_at": {
        default:  true,
        sortable: true,
        clause:   "a.created_at",
      },

      "tags": {
        default:  true,
        sortable: false,
        clause:   "
          (SELECT group_concat(d.name, '|')

             FROM post_tags c
             JOIN tags d
               ON (d.tag_id = c.tag_id)

            WHERE c.post_id = a.post_id)
        ",
      },
    }

    #####################
    # get_posts methods #
    #####################

    def get_posts(
      cols    : Array(String)?                = nil,
      site_id : Int?                          = nil,
      filters : Hash(Symbol, String)          = {} of Symbol => String,
      tags    : Array(Array(String))          = [] of Array(String),
      page    : Int                           = 1,
      limit   : Int                           = 20,
      sort    : Array(Hash(String, String))?  = nil,
    )
      # build sql args
      sql_args = {
        "site_id": (site_id || @models.site.get_default).to_s,
      } of String => String

      # build tmpl args
      tmpl_args = {
        "filter":   get_filter_clause(filters),
        "sort":     get_sort_clause(sort),
        "tags":     get_tags_filter(tags),
        "cols":     get_columns_clause(cols),
      }

      # count number of matching rows
      num_rows = (one(:count_posts, sql_args, tmpl_args).to_s || 0).to_i

      # build rows
      rows = [] of Post
      if num_rows > 0
        all(:get_posts, sql_args.merge({
          "offset": ((page - 1) * limit).to_s,
          "limit":  limit.to_s,
        }), tmpl_args) do |row|
          rows << Post.new(row)
          nil
        end
      end

      # return result
      Results(Post).new(
        page:     page,
        limit:    limit,
        num_rows: num_rows,
        rows:     rows,
      )
    end

    FILTERS = {
      posted_year: {
        type: :int,
        re:   /^\d{4}$/,
        sql:  "(strftime('%%Y', a.posted_at) + 0 = %d)",
      },

      posted_month: {
        type: :int,
        re:   /^\d{1,2}$/,
        sql:  "(strftime('%%m', a.posted_at) + 0 = %d)",
      },

      posted_day: {
        type: :int,
        re:   /^\d{1,2}$/,
        sql:  "(strftime('%%d', a.posted_at) + 0 = %d)",
      },

      created_year: {
        type: :int,
        re:   /^\d{4}$/,
        sql:  "(strftime('%%Y', a.created_at) + 0 = %d)",
      },

      created_month: {
        type: :int,
        re:   /^\d{1,2}$/,
        sql:  "(strftime('%%m', a.created_at) + 0 = %d)",
      },

      created_day: {
        type: :int,
        re:   /^\d{1,2}$/,
        sql:  "(strftime('%%d', a.created_at) + 0 = %d)",
      },

      slug: {
        type: :string,
        sql:  "a.slug = '%s'"
      },

      state: {
        type: :string,
        sql: "(
          a.state = (
            SELECT state
              FROM post_states
             WHERE name = '%s'
          )
        )",
      },
    }

    private def get_filter_clause(
      filters : Hash(Symbol, String)
    ) : String
      # define results
      r = ["1 = 1"] # true

      FILTERS.each do |key, f|
        if val = filters[key]?
          # check value format
          if f[:re]? && val !~ (f[:re] as Regex)
            raise "invalid #{key} filter"
          end

          # add to results
          r << (f[:sql] as String) % [case f[:type]
          when :int
            val.to_i
          when :string
            @db.quote(val)
          else
            # never reached
            raise "unknown format type: #{f[:type]}"
          end]
        end
      end

      if filters.has_key?(:q)
        # TODO
      end

      # return filter clause
      r.join(" AND ")
    end

    GET_POSTS_DEFAULT_SORT = [{
      "col": "posted_at",
      "dir": "desc",
    }, {
      "col": "created_at",
      "dir": "desc",
    }]

    private def get_sort_clause(
      sort : Array(Hash(String, String))? = nil
    ) : String
      (sort || GET_POSTS_DEFAULT_SORT).map { |row|
        # verify sort column
        raise "unknown sort column" unless COLUMNS.has_key?(row["col"])
        raise "column is not sortable" unless COLUMNS[row["col"]][:sortable]

        # build result
        "%s %s" % [
          COLUMNS[row["col"]][:clause] as String,
          (row["dir"]? == "desc") ? "desc" : "asc",
        ]
      }.join(',')
    end

    private def get_columns_clause(
      cols : Array(String)?
    ) : String
      (cols || COLUMNS.keys.select { |col|
        COLUMNS[col][:default]
      }).map { |col|
        "%s AS %s" % [COLUMNS[col][:clause], col]
      }.join(", ")
    end

    NO_POSTS = "
      SELECT post_id
        FROM posts
       LIMIT 0
    "

    private def get_tags_filter(
      tag_sets : Array(Array(String))
    ) : String
      (tag_sets.size > 0) ? template(:tags, {
        "sql": tag_sets.map { |tags|
          template(:tags, {
            "sql": tags.map { |tag|
              template(:tag, {
                "tag": @models.db.quote(tag),
              })
            }.join(" INTERSECT "),
          })
        }.join(" UNION "),
      }) : NO_POSTS
    end

    ####################
    # add_post methods #
    ####################

    def add_post(
      site_id : Int?          = nil,
      slug    : String        = "",
      name    : String        = "",
      body    : String        = "",
      tags    : Array(String) = [] of String,
      state   : String?       = "draft",
    ) : Int64
      post_id = -1_i64

      # check slug, name, and body
      { "slug": slug, "name": name, "body": body }.each do |name, text|
        raise "invalid %s" % [name] unless text.size > 0
      end

      transaction do
        query(:add_post, {
          "site_id":  (site_id || @models.site.get_default).to_s,
          "slug":     slug,
          "name":     name,
          "raw_body": body,
          "body":     body,
        }, nil)

        # get post id
        post_id = last_insert_row_id

        # update state and tags
        update_post(
          site_id:  site_id,
          post_id:  post_id,
          tags:     tags,
          state:    state,
        )
      end

      # return post id
      post_id
    end

    #######################
    # update_post methods #
    #######################

    def update_post(
      site_id : Int?            = nil,
      post_id : Int?            = nil,
      slug    : String?         = nil,
      name    : String?         = nil,
      body    : String?         = nil,
      tags    : Array(String)?  = nil,
      state   : String?         = nil
    )
      raise "null post_id" if post_id.nil?

      # build initial query
      sets = [] of String
      args = {
        "site_id": (site_id || @models.site.get_default).to_s,
        "post_id": post_id.to_s,
      }

      if slug
        sets << "slug = :slug"
        args["slug"] = slug
      end

      if name
        sets << "name = :name"
        args["name"] = name
      end

      if body
        sets << "body = :body, raw_body = :body"
        args["body"] = body
      end

      if state
        # update state
        sets << "state = (
          SELECT state
            FROM post_states
           WHERE name = :state
        )"
        args["state"] = state

        # update posted_at
        # FIXME: there should be a better way to do this
        sets << "posted_at = %s" % [
          (state == "posted") ? "CURRENT_TIMESTAMP" : "NULL"
        ]
      end

      if sets.size > 0 || tags
        transaction do
          if sets.size > 0
            # update post
            query(:update_post, args, {
              "sets": sets.join(','),
            })
          end

          # TODO: post history

          if tags
            # update tags
            set_tags(
              site_id:  site_id,
              post_id:  post_id,
              tags:     tags as Array(String),
            )
          end
        end
      end
    end

    ################
    # remove_posts #
    ################

    def remove_posts(
      site_id   : Int?        = nil,
      post_ids  : Array(Int)  = [] of Int,
    )
      query(:remove_posts, {
        "site_id":  (site_id || @models.site.get_default).to_s,
      }, {
        "post_ids": post_ids.map { |post_id|
          "'" + @db.quote(post_id.to_s) + "'"
        }.join(',')
      })

      # no return value
      nil
    end

    ####################
    # set_tags methods #
    ####################

    def set_tags(
      site_id : Int?          = nil,
      post_id : Int?          = nil,
      tags    : Array(String) = [] of String,
    )
      return unless post_id

      # build sql args
      args = {
        "site_id": (site_id || @models.site.get_default).to_s,
        "post_id": post_id.to_s,
      }

      transaction do
        # delete existing post tags
        query(:set_tags_delete, args, nil)

        if tags.size > 0
          @models.tag.add_tags(tags)

          # add new post tags
          query(:set_tags_insert, args, {
            "tags": tags.map { |tag|
              "'" + @db.quote(tag) + "'"
            }.join(','),
          })
        end
      end

      # no return value
      nil
    end
  end
end