module Guff
  class TagModel < Model
    SQL = TemplateCache.new({
      get_tag_posts: "
        SELECT a.post_id

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

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

      get_tags: "
        SELECT a.tag_id,
               a.name,
               COUNT(*) AS num_posts

          FROM tags a
          JOIN post_tags b
            ON (b.tag_id = a.tag_id)
          JOIN (SELECT DISTINCT post_id FROM (%{tags})) c
            ON (c.post_id = b.post_id)

         WHERE %{filter}

         GROUP BY a.tag_id, a.name
         ORDER BY num_posts, a.name DESC
      ",

      add_tags: "
        INSERT INTO tags(name) VALUES %{tags}
      ",

      get_ids: "
        SELECT tag_id,
               name

          FROM tags

         WHERE name IN (%{tags})
      ",
    })

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

    def get_tags(
      tags      : Array(String) = [] of String,
      show_all  : Bool          = false,
    ) : Array(Hash(String, String | Int32))
      r = [] of Hash(String, String | Int32)

      # build filter
      if show_all
        filter = "1 = 1"
      else
        # exclude internal tags
        filter = "substr(a.name, 1, 1) <> '_'"
      end

      # exec query
      all(:get_tags, nil, {
        "filter": filter,
        "tags": tags.map { |tag|
          template(:get_tag_posts, {
            "tag": @db.quote(tag),
          })
        }.join(" INTERSECT "),
      }) do |row|
        r << {
          "tag_id":     row["tag_id"].to_s.to_i,
          "name":       row["name"].to_s,
          "num_posts":  row["num_posts"].to_s.to_i,
        }
      end

      # return results
      r
    end

    def add_tags(
      tags : Array(String) = [] of String,
    )
      missing_tags = get_missing_tags(tags)

      if missing_tags.size > 0
        query(:add_tags, nil, {
          "tags": missing_tags.map { |tag|
            "('" + @db.quote(tag) + "')"
          }.join(','),
        })
      end
    end

    private def get_missing_tags(
      tags : Array(String) = [] of String,
    ) : Array(String)
      # get ids of existing tags
      ids = get_ids(tags)

      # return missing tags
      tags.reject { |tag| ids[tag]? }
    end

    private def get_ids(
      tags : Array(String) = [] of String,
    ) : Hash(String, Int32)
      r = {} of String => Int32

      all(:get_ids, nil, {
        "tags": tags.map { |tag|
          "'" + @db.quote(tag) + "'"
        }.join(','),
      }) do |row|
        # add to results
        r[row["name"] as String] = row["tag_id"].to_s.to_i
      end

      # return result
      r
    end
  end
end