add page rendering

3 files changed, 103 insertions, 26 deletions

diff --git a/src/guff/handlers.cr b/src/guff/handlers.cr
index 8690095..d9b39c8 100644
--- a/src/guff/handlers.cr
+++ b/src/guff/handlers.cr
@@ -362,28 +362,32 @@ module Guff::Handlers
     PATH_RE = %r{^/(?<slug>[^/]+)\.html$}
 
     def call(context : HTTP::Server::Context)
-      if post_id = get_post_id(context.request)
-        # TODO: render page
+      if r = get(context.request)
         context.response.headers["x-frame-options"] = "SAMEORIGIN"
         context.response.content_type = "text/html; charset=utf-8"
         context.response.status_code = 200
-        context.response << "page: #{post_id}"
+
+        Views::PageView.new(@context, r).to_s(context.response)
       else
         # unknown page
         call_next(context)
       end
     end
 
-    private def get_post_id(request : HTTP::Request) : Int64?
+    private def get(request : HTTP::Request) : Hash(String, String)?
       r = nil
 
       if request.method == "GET"
         if md = PATH_RE.match(request.path.not_nil!)
           if site_id = get_site_id(request.headers["host"]?)
-            r = @context.models.page.get_id(
+            results = @context.models.page.find(
               site_id: site_id,
               slug: md["slug"],
             )
+
+            if results.num_rows > 0
+              r = results.rows.first
+            end
           end
         end
       end
@@ -447,10 +451,10 @@ module Guff::Handlers
 
     def call(context : HTTP::Server::Context)
       if r = get(context.request)
-        # TODO: render page
         context.response.headers["x-frame-options"] = "SAMEORIGIN"
         context.response.content_type = "text/html; charset=utf-8"
         context.response.status_code = 200
+
         Views::Blog::PostView.new(@context, r).to_s(context.response)
       else
         # unknown page
diff --git a/src/guff/models/blog.cr b/src/guff/models/blog.cr
index ed5bddc..15a3188 100644
--- a/src/guff/models/blog.cr
+++ b/src/guff/models/blog.cr
@@ -139,19 +139,21 @@ class Guff::Models::BlogModel < Guff::Models::Model
       sql.join(" AND ")
     ], args).not_nil!.to_i64
 
-    # add limit and offset to args
-    args << LIMIT.to_s
-    args << ((page - 1) * LIMIT).to_s
-
     # get rows
     rows = [] of Hash(String, String)
-    @context.dbs.ro.all(SQL[:find] % [
-      sql.join(" AND ")
-    ], args) do |row|
-      # p row
-      rows << row.reduce({} of String => String) do |r, kv|
-        r[kv[0]] = kv[1].to_s
-        r
+
+    if num_rows > 0
+      # add limit and offset to args
+      args.push(LIMIT.to_s, ((page - 1) * LIMIT).to_s)
+
+      @context.dbs.ro.all(SQL[:find] % [
+        sql.join(" AND ")
+      ], args) do |row|
+        # p row
+        rows << row.reduce({} of String => String) do |r, kv|
+          r[kv[0]] = kv[1].to_s
+          r
+        end
       end
     end
 
diff --git a/src/guff/models/page.cr b/src/guff/models/page.cr
index b30e94a..9be3b3a 100644
--- a/src/guff/models/page.cr
+++ b/src/guff/models/page.cr
@@ -1,7 +1,7 @@
 class Guff::Models::PageModel < Guff::Models::Model
   SQL = {
-    get_id: "
-      SELECT b.post_id
+    count: "
+      SELECT COUNT(*)
 
         FROM sites a
         JOIN posts b
@@ -10,13 +10,52 @@ class Guff::Models::PageModel < Guff::Models::Model
           ON (c.post_id = b.post_id)
         JOIN states d
           ON (d.state_id = b.state_id)
+        JOIN users e
+          ON (e.user_id = b.created_by)
+        JOIn layouts f
+          ON (f.layout_id = c.layout_id)
 
        WHERE a.site_id = ?
          AND b.slug = ?
          AND d.state = 'public'
+         AND (0 + strftime('%s'))
+             BETWEEN (0 + strftime('%s', COALESCE(b.posted_at, 'now')))
+                 AND (0 + strftime('%s', COALESCE(b.expires_at,'now')))
+    ",
+
+    find: "
+      SELECT b.post_id,
+             b.posted_at,
+             datetime(b.posted_at, 'localtime') AS posted_at_text,
+             b.slug,
+             b.name,
+             b.body,
+             f.layout,
+             e.user_id,
+             e.name AS user_name
+
+        FROM sites a
+        JOIN posts b
+          ON (b.site_id = a.site_id)
+        JOIN pages c
+          ON (c.post_id = b.post_id)
+        JOIN states d
+          ON (d.state_id = b.state_id)
+        JOIN users e
+          ON (e.user_id = b.created_by)
+        JOIn layouts f
+          ON (f.layout_id = c.layout_id)
 
-       ORDER BY b.created_at DESC
-       LIMIT 1
+       WHERE a.site_id = ?
+         AND b.slug = ?
+         AND d.state = 'public'
+         AND (0 + strftime('%s'))
+             BETWEEN (0 + strftime('%s', COALESCE(b.posted_at, 'now')))
+                 AND (0 + strftime('%s', COALESCE(b.expires_at,'now')))
+
+       ORDER BY b.posted_at DESC
+
+       LIMIT ? OFFSET ?
     ",
 
     add: "
@@ -54,12 +93,44 @@ class Guff::Models::PageModel < Guff::Models::Model
     ",
   }
 
-  def get_id(
+  LIMIT = 50
+
+  def find(
     site_id : Int64,
-    slug    : String
-  ) : Int64?
-    r = @context.dbs.ro.one(SQL[:get_id], [site_id.to_s, slug])
-    r ? r.to_i64 : nil
+    slug    : String,
+    page    : Int32?  = nil
+  ) : PagedResultSet
+    # build query args
+    args = [site_id.to_s, slug]
+
+    # get page
+    page ||= 1
+    raise "invalid page" unless page > 0
+
+    # count rows
+    num_rows = @context.dbs.ro.one(SQL[:count], args).not_nil!.to_i64
+
+    rows = [] of Hash(String, String)
+    if num_rows > 0
+      # add limit and offset to args
+      args.push(LIMIT.to_s, ((page - 1) * LIMIT).to_s)
+
+      # get rows
+      @context.dbs.ro.all(SQL[:find], args) do |row|
+        rows << row.reduce({} of String => String) do |r, kv|
+          r[kv[0]] = kv[1].to_s
+          r
+        end
+      end
+    end
+
+    # return results
+    PagedResultSet.new(
+      page:     page,
+      num_rows: num_rows,
+      limit:    LIMIT,
+      rows:     rows,
+    )
   end
 
   def add(