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} %{dir}
         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 is_active = 0
         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 = nil                        : Array(String)?,
      site_id = nil                     : Int?,
      filters = {} of Symbol => String  : Hash(Symbol, String),
      tags = [] of Array(String)        : Array(Array(String)),
      page = 1                          : Int,
      limit = 20                        : Int,
      sort = "posted_at"                : String,
      dir = "desc"                      : String,
    )
      # build sql args
      sql_args = {
        "site_id": (site_id || @models.site.get_default).to_s,
      } of String => String

      # build tmpl args
      tmpl_args = {
        # TODO
        "filter":   get_filter_clause(filters),
        "sort":     get_sort_clause(sort),
        "dir":      dir,
        "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

      # TODO: add Post class?
      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

    DATE_FILTERS = {
      :year => {
        :re   => /^\d{4}$/,
        :fmt  => "%Y",
      },

      :month => {
        :re   => /^\d{1,2}$/,
        :fmt  => "%m",
      },

      :day => {
        :re   => /^\d{1,2}$/,
        :fmt  => "%d",
      },
    }

    DATE_FILTER_CLAUSE = "(
      strftime('%s', a.posted_at) + 0 = %d
    )"

    SLUG_FILTER_CLAUSE = "(
      a.slug = '%s'
    )"

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

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

          # add to filters
          r << DATE_FILTER_CLAUSE % [f[:fmt] as String, val.to_i]
        end
      end

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

      # add slug filter
      if filters.has_key?(:slug)
        r << SLUG_FILTER_CLAUSE % [@db.quote(filters[:slug])]
      end

      # return result
      r.join(" AND ")
    end

    private def get_sort_clause(
      sort : String
    ) : String
      # verify sort column
      raise "unknown sort column" unless COLUMNS.has_key?(sort)
      raise "column is not sortable" unless COLUMNS[sort][:sortable]

      # return result
      COLUMNS[sort][:clause] as String
    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 = nil       : Int?,
      slug = ""           : String,
      name = ""           : String,
      body = ""           : String,
      tags = [] of String : Array(String),
    ) : 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

        # set post tags
        set_tags(
          site_id:          site_id,
          post_id:          post_id,
          tags:             tags,
          use_transaction:  false,
        )
      end

      # return post id
      post_id
    end

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

      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

      unless posted.nil?
        val = posted ? "CURRENT_TIMESTAMP" : "NULL"
        sets << "posted_at = %s" % [val]
      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,
              use_transaction:  false,
            )
          end
        end
      end
    end

    def remove_posts(
      site_id = nil : Int?,
      post_ids = [] of Int : Array(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

    def set_tags(
      site_id = nil           : Int?,
      post_id = nil           : Int?,
      tags = [] of String     : Array(String)?,
      use_transaction = true  : Bool
    )
      if use_transaction
        transaction do
          raw_set_tags(site_id, post_id, tags)
        end
      else
        raw_set_tags(site_id, post_id, tags)
      end

      nil
    end

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

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

      # 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
  end
end