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 a.post_id,
               a.site_id,
               a.name,
               a.body,
               a.html,

               (SELECT group_agg(d.name, '|')

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

                 WHERE d.post_id = a.post_id) AS tags

          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}
         OFFSET :offset LIMIT :limit
      ",
    })

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

    SORTS = {
      "posted_at": "a.posted_at",
    }

    def get_posts(
      site_id = nil               : Int32?,
      q = ""                      : String,
      tags = [] of Array(String)  : Array(Array(String)),
      page = 1                    : Int32,
      limit = 20                  : Int32,
      sort = "posted_at"          : String,
      dir = "desc"                : String,
    )
      # build sql args
      sql_args = {
        "site_id": "0" # (site_id || @models.default_site_id).to_s,
      } of String => String

      # build tmpl args
      tmpl_args = {
        # TODO
        "filter":   "1 = 1", # true
        "sort":     SORTS[sort],
        "dir":      dir,
        "tags":     get_tags_filter(tags),
      } of String => String

      # 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 Hash(String, ::SQLite3::Value)
      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 << row
          nil
        end
      end

      # return result
      Results(Hash(String, ::SQLite3::Value)).new(
        page:     page,
        limit:    limit,
        num_rows: num_rows,
        rows:     rows,
      )
    end

    private def get_tags_filter(tag_sets : Array(Array(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(" INTERSECTS "),
          })
        }.join(" UNION "),
      }) : "
        SELECT post_id
          FROM posts
         LIMIT 0
      "
    end

    def add_post(req)
      # TODO: return post id
      {ok: true}
    end

    def remove_posts(req)
      # TODO
      {ok: true}
    end

    def set_tags(req)
      # TODO
      {ok: true}
    end
  end
end