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

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

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

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

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

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

      "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_agg(d.name, '|')

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

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

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

    def get_posts(
      cols = nil                  : Array(String)?,
      site_id = nil               : Int?,
      q = ""                      : 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":   "1 = 1", # true
        "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

    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(" INTERSECTS "),
          })
        }.join(" UNION "),
      }) : NO_POSTS
    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