add themes, switch pages and sites to use them

9 files changed, 355 insertions, 101 deletions

diff --git a/data/assets/js/admin/dialogs/page-edit.js b/data/assets/js/admin/dialogs/page-edit.js
index 05d69a7..95c9a63 100644
--- a/data/assets/js/admin/dialogs/page-edit.js
+++ b/data/assets/js/admin/dialogs/page-edit.js
@@ -6,17 +6,20 @@ jQuery(function($) {
   $(p + 'dialog').on('guff.loaded', function(ev) {
     var r = ev.post_data;
 
-    $(p + 'layout a').removeClass('btn-primary').addClass('btn-default');
-    $(p + 'layout a[data-val="' + r.layout + '"]')
-      .toggleClass('btn-primary btn-default');
+    $(p + 'theme').val(r.theme_id || 'site-default');
   });
 
   $(p + 'confirm').click(function() {
+    var theme_id = $(p + 'theme').val();
+    if (theme_id == 'site-default')
+      theme_id = null;
+
     $(p + 'dialog').trigger({
       type: 'guff.save',
 
       post_data: {
-        layout: $(p + 'layout .btn-primary').data('val'),
+        have_theme_id: 't',
+        theme_id: theme_id,
       },
     });
 
diff --git a/data/init.yaml b/data/init.yaml
index 7e177c5..e11f82b 100644
--- a/data/init.yaml
+++ b/data/init.yaml
@@ -1,34 +1,6 @@
 ---
 init_sql:
   - |
-    CREATE TABLE sites (
-      site_id     INTEGER PRIMARY KEY,
-
-      name        TEXT UNIQUE NOT NULL
-                  CHECK (LENGTH(name) > 0),
-
-      is_active   BOOLEAN NOT NULL DEFAULT false,
-
-      is_default  BOOLEAN NOT NULL DEFAULT false
-    )
-
-  - |
-    INSERT INTO sites(site_id, name, is_active, is_default) VALUES
-      (1, 'default', 1, 1)
-
-  - |
-    CREATE TABLE site_domains (
-      site_id     INTEGER NOT NULL
-                  REFERENCES sites(site_id),
-
-      domain      TEXT UNIQUE NOT NULL CHECK (
-                    LENGTH(domain) > 0 AND
-                    domain = LOWER(domain) AND
-                    domain NOT LIKE '% %'
-                  )
-    )
-
-  - |
     CREATE TABLE roles (
       role_id     INTEGER PRIMARY KEY,
 
@@ -64,12 +36,196 @@ init_sql:
                     email LIKE '%@%'
                   ),
 
+      created_at  TIMESTAMP WITH TIME ZONE NOT NULL
+                  DEFAULT CURRENT_TIMESTAMP,
+
       password    TEXT NOT NULL DEFAULT '',
 
       is_active   BOOLEAN NOT NULL DEFAULT false
     )
 
   - |
+    CREATE TABLE themes (
+      theme_id   INTEGER PRIMARY KEY,
+
+      -- generated (by guff) url component of theme
+      theme_slug    TEXT UNIQUE NOT NULL CHECK (
+                      LENGTH(theme_slug) > 0 AND
+                      theme_slug NOT LIKE '% %' AND
+                      theme_slug = LOWER(theme_slug)
+                    ),
+
+      -- name
+      theme_name    TEXT NOT NULL
+                    CHECK (LENGTH(theme_name) > 0),
+
+      -- version
+      theme_version TEXT NOT NULL
+                    CHECK (LENGTH(theme_version) > 0),
+
+      -- theme release date
+      theme_date    DATE NOT NULL,
+
+      -- sha1 digest of theme file
+      theme_hash    TEXT NOT NULL CHECK (
+                      LENGTH(theme_hash) > 0 AND
+                      theme_hash NOT LIKE '% %' AND
+                      theme_hash = LOWER(theme_hash)
+                    ),
+
+      is_system     BOOLEAN NOT NULL DEFAULT false
+    )
+
+  - |
+    INSERT INTO themes(
+      theme_id,
+      theme_slug,
+      theme_name,
+      theme_version,
+      theme_date,
+      theme_hash,
+      is_system
+    ) VALUES
+      (1, 'default', 'Default', '1.0', '2016-07-18', 'n/a', 1),
+      (2, 'blank', 'Blank', '1.0', '2016-07-18', 'n/a', 1)
+
+  - |
+    CREATE TABLE theme_data_types (
+      type_id     INTEGER PRIMARY KEY,
+
+      name        TEXT UNIQUE NOT NULL CHECK (
+                    LENGTH(name) > 0 AND
+                    name NOT LIKE '% %' AND
+                    name = LOWER(name)
+                  )
+    )
+
+  - |
+    INSERT INTO theme_data_types(type_id, name) VALUES
+      (1, 'metadata'),
+      (2, 'template')
+
+  - |
+    CREATE TABLE theme_data (
+      -- theme that data entry is associated with
+      theme_id    INTEGER NOT NULL
+                  REFERENCES themes(theme_id),
+
+      type_id     INTEGER NOT NULL
+                  REFERENCES theme_data_types(type_id),
+
+      -- data key
+      -- e.g. "author", "url"
+      data_key    TEXT NOT NULL CHECK (
+                    LENGTH(data_key) > 0 AND
+                    data_key NOT LIKE '% %' AND
+                    data_key = LOWER(data_key)
+                  ),
+
+      -- data value
+      data_val    TEXT NOT NULL,
+
+      PRIMARY KEY (theme_id, type_id, data_key)
+    )
+
+  - |
+    CREATE TABLE theme_files (
+      file_id     INTEGER PRIMARY KEY,
+
+      -- theme that this file is associated with
+      theme_id    INTEGER NOT NULL
+                  REFERENCES themes(theme_id),
+
+      -- size of file, in bytes
+      file_size   INTEGER NOT NULL
+                  CHECK (file_size > 0),
+
+      -- sha1 digest of file
+      file_hash   TEXT NOT NULL CHECK (
+                    LENGTH(file_hash) > 1 AND
+                    file_hash NOT LIKE '% %'
+                  ),
+
+      -- path of file
+      file_path   TEXT NOT NULL CHECK (
+                    LENGTH(file_path) > 1 AND
+                    file_path NOT LIKE '/%'
+                  ),
+
+      UNIQUE (theme_id, file_path)
+    )
+
+  - |
+    CREATE TABLE theme_asset_types (
+      type_id     INTEGER PRIMARY KEY,
+      name        TEXT UNIQUE NOT NULL
+                  CHECK (LENGTH(name) > 0)
+    )
+
+  - |
+    INSERT INTO theme_asset_types(type_id, name) VALUES
+      (1, 'script'),
+      (2, 'style')
+
+  - |
+    CREATE TABLE theme_assets (
+      -- theme file
+      file_id     INTEGER NOT NULL
+                  REFERENCES theme_files(file_id),
+
+      -- asset type
+      type_id     INTEGER NOT NULL
+                  REFERENCES theme_asset_types(type_id),
+
+      -- load order
+      sort_order  INTEGER NOT NULL,
+
+      PRIMARY KEY (file_id, type_id),
+      UNIQUE (file_id, type_id)
+    )
+
+  - |
+    CREATE TABLE sites (
+      site_id     INTEGER PRIMARY KEY,
+
+      -- make sure name does not begin with a dot or
+      -- contain slashes
+      name        TEXT UNIQUE NOT NULL CHECK (
+                    LENGTH(name) > 0 AND
+                    name NOT LIKE '.%' AND
+                    name NOT LIKE '%/%'
+                  ),
+
+      -- date that site was created
+      created_at  TIMESTAMP WITH TIME ZONE NOT NULL
+                  DEFAULT CURRENT_TIMESTAMP,
+
+      -- theme for this site
+      theme_id    INTEGER NOT NULL
+                  REFERENCES themes(theme_id),
+
+      is_active   BOOLEAN NOT NULL DEFAULT false,
+
+      is_default  BOOLEAN NOT NULL DEFAULT false
+    )
+
+  - |
+    INSERT INTO sites(site_id, name, theme_id, is_active, is_default) VALUES
+      (1, 'default', 1, 1, 1)
+
+  - |
+    CREATE TABLE site_domains (
+      site_id     INTEGER NOT NULL
+                  REFERENCES sites(site_id),
+
+      domain      TEXT UNIQUE NOT NULL CHECK (
+                    LENGTH(domain) > 0 AND
+                    domain = LOWER(domain) AND
+                    domain NOT LIKE '% %'
+                  )
+    )
+
+  - |
     CREATE TABLE states (
       state_id    INTEGER PRIMARY KEY,
 
@@ -154,34 +310,13 @@ init_sql:
     )
 
   - |
-    CREATE TABLE layouts (
-      layout_id   INTEGER PRIMARY KEY,
-
-      -- internal layout name
-      layout      TEXT UNIQUE NOT NULL CHECK (
-                    LENGTH(layout) > 0 AND
-                    layout = LOWER(layout)
-                  ),
-
-      -- user-visible layout name
-      layout_name TEXT UNIQUE NOT NULL
-                  CHECK (LENGTH(layout_name) > 0),
-
-      is_default  BOOLEAN NOT NULL
-    )
-
-  - |
-    INSERT INTO layouts(layout_id, layout, layout_name, is_default) VALUES
-      (1, 'blank', 'Blank', 0),
-      (2, 'default', 'Default', 1)
-
-  - |
     CREATE TABLE pages (
       post_id     INTEGER PRIMARY KEY
                   REFERENCES posts(post_id),
 
-      layout_id   INTEGER NOT NULL
-                  REFERENCES layouts(layout_id)
+      -- nullable, NULL means site theme
+      theme_id    INTEGER DEFAULT NULL
+                  REFERENCES themes(theme_id)
     )
 
   - |
@@ -246,9 +381,9 @@ test_posts:
       SELECT post_id, name, slug, body FROM posts
 
   - |
-    INSERT INTO pages(post_id, layout_id) VALUES (
+    INSERT INTO pages(post_id, theme_id) VALUES (
       1,
-      (SELECT layout_id FROM layouts WHERE layout = 'default')
+      (SELECT theme_id FROM themes WHERE theme_slug = 'default')
     )
 
   - |
diff --git a/src/guff/apis.cr b/src/guff/apis.cr
index dccc01c..bbedd3f 100644
--- a/src/guff/apis.cr
+++ b/src/guff/apis.cr
@@ -41,7 +41,8 @@ module Guff::APIs
         name:             params["name"]?,
         body:             params["body"]?,
 
-        layout:           params["layout"]?,
+        have_theme_id:    true,
+        theme_id:         (params["theme_id"]? && params["theme_id"].size > 0) ? params["theme_id"].to_i32 : nil,
       )
 
       nil
diff --git a/src/guff/model-set.cr b/src/guff/model-set.cr
index afc7c5f..cf3c769 100644
--- a/src/guff/model-set.cr
+++ b/src/guff/model-set.cr
@@ -22,5 +22,6 @@ class Guff::ModelSet
     role:     Models::RoleModel,
     state:    Models::StateModel,
     file:     Models::FileModel,
+    theme:    Models::ThemeModel,
   })
 end
diff --git a/src/guff/models/page.cr b/src/guff/models/page.cr
index 9be3b3a..0ce8d5b 100644
--- a/src/guff/models/page.cr
+++ b/src/guff/models/page.cr
@@ -12,8 +12,8 @@ class Guff::Models::PageModel < Guff::Models::Model
           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)
+        LEFT JOIN themes f
+          ON (f.theme_id = c.theme_id)
 
        WHERE a.site_id = ?
          AND b.slug = ?
@@ -30,9 +30,10 @@ class Guff::Models::PageModel < Guff::Models::Model
              b.slug,
              b.name,
              b.body,
-             f.layout,
              e.user_id,
-             e.name AS user_name
+             e.name AS user_name,
+             c.theme_id,
+             a.theme_id AS site_theme_id
 
         FROM sites a
         JOIN posts b
@@ -43,8 +44,6 @@ class Guff::Models::PageModel < Guff::Models::Model
           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 = ?
@@ -59,13 +58,19 @@ class Guff::Models::PageModel < Guff::Models::Model
     ",
 
     add: "
-      INSERT INTO pages(post_id, layout_id)
-        VALUES (?, (SELECT layout_id FROM layouts WHERE layout = 'default'))
+      INSERT INTO pages(post_id)
+        VALUES (?)
     ",
 
     set: "
       UPDATE pages
-         SET layout_id = (SELECT layout_id FROM layouts WHERE layout = ?)
+         SET theme_id = (SELECT theme_id FROM themes WHERE theme_id = ?)
+       WHERE post_id = ?
+    ",
+
+    set_default: "
+      UPDATE pages
+         SET theme_id = NULL
        WHERE post_id = ?
     ",
 
@@ -79,15 +84,16 @@ class Guff::Models::PageModel < Guff::Models::Model
              a.slug_lock,
              a.name,
              a.body,
-             d.layout
+             b.theme_id,
+             d.theme_id AS site_theme_id
 
         FROM posts a
         JOIN pages b
           ON (b.post_id = a.post_id)
         JOIN states c
           ON (c.state_id = a.state_id)
-        JOIN layouts d
-          ON (d.layout_id = b.layout_id)
+        JOIN sites d
+          ON (d.site_id = a.site_id)
 
        WHERE a.post_id = ?
     ",
@@ -166,7 +172,8 @@ class Guff::Models::PageModel < Guff::Models::Model
     name            : String? = nil,
     body            : String? = nil,
 
-    layout          : String? = nil,
+    have_theme_id   : Bool    = false,
+    theme_id        : Int32?  = nil,
   )
     db = @context.dbs.rw
 
@@ -190,8 +197,12 @@ class Guff::Models::PageModel < Guff::Models::Model
         body:             body,
       )
 
-      if layout
-        db.query(SQL[:set], [layout, post_id.to_s])
+      if have_theme_id
+        if theme_id
+          db.query(SQL[:set], [theme_id.to_s, post_id.to_s])
+        else
+          db.query(SQL[:set_default], [post_id.to_s])
+        end
       end
     end
   end
diff --git a/src/guff/models/site.cr b/src/guff/models/site.cr
index 8ab4162..99c09dd 100644
--- a/src/guff/models/site.cr
+++ b/src/guff/models/site.cr
@@ -38,6 +38,7 @@ class Guff::Models::SiteModel < Guff::Models::Model
     get_sites: "
       SELECT site_id,
              name,
+             theme_id,
              is_active,
              is_default
 
diff --git a/src/guff/models/theme.cr b/src/guff/models/theme.cr
new file mode 100644
index 0000000..a8682f7
--- /dev/null
+++ b/src/guff/models/theme.cr
@@ -0,0 +1,102 @@
+class Guff::Models::ThemeModel < Guff::Models::Model
+  SQL = {
+    all: "
+      SELECT a.theme_id,
+             a.theme_name,
+             a.theme_version,
+             a.theme_date,
+             a.is_system,
+
+             (SELECT COUNT(*)
+                FROM sites
+               WHERE theme_id = a.theme_id) AS num_sites,
+             (SELECT COUNT(*)
+                FROM pages
+               WHERE theme_id = a.theme_id) AS num_pages
+
+        FROM themes a
+
+       ORDER BY LOWER(a.theme_name), a.theme_date
+    ",
+
+    get_data: "
+      SELECT a.theme_id,
+             a.data_key,
+             a.data_val
+
+        FROM theme_data a
+        JOIN theme_data_types b
+          ON (b.type_id = a.type_id)
+
+       WHERE b.name = ?
+         AND a.theme_id IN (%s)
+    ",
+
+    get: "
+      SELECT a.theme_id,
+             a.theme_name,
+             a.theme_version,
+             a.theme_date,
+             a.is_system,
+
+             (SELECT COUNT(*)
+                FROM sites
+               WHERE theme_id = a.theme_id) AS num_sites,
+             (SELECT COUNT(*)
+                FROM pages
+               WHERE theme_id = a.theme_id) AS num_pages
+        FROM themes a
+
+       ORDER BY LOWER(a.theme_name), a.theme_date
+    ",
+  }
+
+  def all(with_metadata : Bool = false)
+    # get rows
+    rows = [] of Hash(String, String)
+
+    @context.dbs.ro.all(SQL[:all]) do |row|
+      rows << row.reduce({} of String => String) do |r, kv|
+        r[kv[0]] = kv[1].to_s
+        r
+      end
+    end
+
+    if with_metadata
+      # get metadata lut
+      lut = get_data("metadata", rows.map { |row| row["theme_id"].to_i })
+
+      # build and return result
+      rows.map { |row|
+        lut[row["theme_id"]]? ? row.merge(lut[row["theme_id"]]) : row
+      }
+    else
+      rows
+    end
+  end
+
+  def get(theme_id : Int32)
+    id = theme_id.to_s
+    r = @context.dbs.ro.row(SQL[:get], [id]).not_nil!
+    lut = get_data("metadata", [theme_id])
+    lut[id]? ? r.merge(lut[id]) : r
+  end
+
+  def get_data(type : String, theme_ids : Array(Int32))
+    r = {} of String => Hash(String, String)
+
+    if theme_ids.size > 0
+      @context.dbs.ro.all(SQL[:get_data] % [
+        (["?"] * theme_ids.size).join(",")
+      ], [type].concat(theme_ids.map { |id| id.to_s })) do |row|
+        r[row["theme_id"].to_s] ||= {} of String => String
+        r[row["theme_id"].to_s][
+          "%s/%s" % [type, row["data_key"].to_s]
+        ] = row["data_val"].to_s
+      end
+    end
+
+    # return results
+    r
+  end
+end
diff --git a/src/guff/views/admin-page.cr b/src/guff/views/admin-page.cr
index 094b5f7..7761994 100644
--- a/src/guff/views/admin-page.cr
+++ b/src/guff/views/admin-page.cr
@@ -198,5 +198,22 @@ class Guff::Views::AdminPageView < Guff::Views::HTMLView
     end
   end
 
+  private def theme_options
+    [{
+      "id"    => "site-default",
+      "name"  => "Site Default",
+    }].concat(@context.models.theme.all.map { |row|
+      {
+        "id"    => row["theme_id"],
+        "name"  => "%s (%s)" % %w{
+          name
+          version
+        }.map { |k| row["theme_#{k}"] },
+      }
+    }).map { |row|
+      TEMPLATES[:option] % %w{id name}.map { |k| row[k] }
+    }.join("")
+  end
+
   ECR.def_to_s("src/views/admin-page.ecr")
 end
diff --git a/src/views/admin-page.ecr b/src/views/admin-page.ecr
index b441014..8581c01 100644
--- a/src/views/admin-page.ecr
+++ b/src/views/admin-page.ecr
@@ -1185,37 +1185,20 @@
 
             <div class='row'>
               <div class='col-md-6'>
-                <label>
-                  Layout
+                <label for='page-edit-theme'>
+                  Theme
                 </label>
 
-                <div
-                  id='page-edit-layout'
-                  class='btn-group btn-group-justified state-buttons'
-                >
-                  <a
-                    href='#'
-                    class='btn btn-default'
-                    title='Use blank layout for this page.'
-                    data-val='blank'
-                  >
-                    <i class='fa fa-file-o'></i>
-                    Blank
-                  </a>
-
-                  <a
-                    href='#'
-                    class='btn btn-primary'
-                    title='Use default layout for this page.'
-                    data-val='default'
-                  >
-                    <i class='fa fa-newspaper-o'></i>
-                    Default
-                  </a>
-                </div><!-- btn-group -->
+                <select
+                  id='page-edit-theme'
+                  class='form-control'
+                  title='Choose theme for this page.'
+                ><%=
+                  theme_options
+                %></select>
 
                 <p class='help-block'>
-                  Layout for this page.
+                  Theme for this page.
                 </p>
               </div><!-- col-md-6 -->