---
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,

      -- internal role name
      role        TEXT UNIQUE NOT NULL CHECK (
                    LENGTH(role) > 0 AND
                    role = LOWER(role)
                  ),

      -- user-visible role name
      name        TEXT UNIQUE NOT NULL
                  CHECK (LENGTH(name) > 0),

      sort        INTEGER UNIQUE NOT NULL
    )

  - |
    INSERT INTO roles(role_id, role, name, sort) VALUES
      (1, 'viewer', 'Viewer', 1),
      (2, 'editor', 'Editor', 2),
      (3, 'admin', 'Admin', 3)

  - |
    CREATE TABLE users (
      user_id     INTEGER PRIMARY KEY,

      role_id     INTEGER NOT NULL
                  REFERENCES roles(role_id),

      name        TEXT UNIQUE NOT NULL,
      email       TEXT UNIQUE NOT NULL CHECK (
                    LENGTH(email) > 0 AND
                    email LIKE '%@%'
                  ),

      password    TEXT NOT NULL DEFAULT '',

      is_active   BOOLEAN NOT NULL DEFAULT false
    )

  - |
    CREATE TABLE states (
      state_id    INTEGER PRIMARY KEY,

      -- internal state name
      state       TEXT UNIQUE NOT NULL CHECK (
                    LENGTH(state) > 0 AND
                    state = LOWER(state)
                  ),

      icon        TEXT UNIQUE NOT NULL CHECK (
                    LENGTH(state) > 0 AND
                    state = LOWER(state)
                  ),

      -- user-visible state name
      name        TEXT UNIQUE NOT NULL
                  CHECK (LENGTH(name) > 0),

      sort        INTEGER UNIQUE NOT NULL
    )

  - |
    INSERT INTO states(state_id, state, icon, name, sort) VALUES
      (1, 'draft', 'fa-wrench', 'Draft', 1),
      (2, 'public', 'fa-bullhorn', 'Public', 2),
      (3, 'deleted', 'fa-trash', 'Deleted', 3)

  - |
    CREATE TABLE posts (
      post_id     INTEGER PRIMARY KEY,

      site_id     INTEGER NOT NULL
                  REFERENCES sites(site_id),

      created_at  TIMESTAMP WITH TIME ZONE NOT NULL
                  DEFAULT CURRENT_TIMESTAMP,

      created_by  INTEGER NOT NULL
                  REFERENCES users(user_id),

      state_id    INTEGER NOT NULL
                  REFERENCES states(state_id),

      posted_at   TIMESTAMP WITH TIME ZONE,
      expires_at  TIMESTAMP WITH TIME ZONE,

      name        TEXT NOT NULL DEFAULT '',

      slug        TEXT NOT NULL DEFAULT '' CHECK (
                    slug NOT LIKE '% %' AND
                    slug = LOWER(slug)
                  ),

      slug_lock   BOOLEAN NOT NULL DEFAULT 1,

      body        TEXT NOT NULL DEFAULT ''
    )

  - |
    CREATE INDEX in_posts_site_id ON posts(site_id)

  - |
    CREATE VIRTUAL TABLE posts_fts USING fts4(
      name,
      slug,
      body
    )

  - |
    CREATE TABLE blogs (
      post_id     INTEGER PRIMARY KEY
                  REFERENCES posts(post_id)
    )

  - |
    CREATE TABLE projects (
      post_id     INTEGER PRIMARY KEY
                  REFERENCES posts(post_id),

      repo_url    TEXT NOT NULL DEFAULT ''
                  CHECK (repo_url NOT LIKE '% %')
    )

  - |
    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)
    )

  - |
    CREATE TABLE sessions (
      id          TEXT PRIMARY KEY,

      created_at  TIMESTAMP WITH TIME ZONE NOT NULL
                  DEFAULT CURRENT_TIMESTAMP,

      updated_at  TIMESTAMP WITH TIME ZONE NOT NULL
                  DEFAULT CURRENT_TIMESTAMP,

      data        TEXT NOT NULL
    )

add_user: |
  INSERT INTO users(name, email, password, role_id, is_active) VALUES
    (?, ?, ?, (SELECT role_id FROM roles WHERE role = ?), 1)

test_posts:
  - |
    INSERT INTO posts (
      post_id,
      site_id,
      created_by,
      state_id,
      posted_at,
      name,
      slug,
      body
    ) VALUES (
      1,
      1,
      1,
      (SELECT state_id FROM states WHERE state = 'public'),
      CURRENT_TIMESTAMP,
      'Test Page',
      'test-page',
      'This is the body of a test page.'
    ), (
      2,
      1,
      1,
      (SELECT state_id FROM states WHERE state = 'public'),
      CURRENT_TIMESTAMP,
      'Test Project',
      'test-project',
      'This is the body of a test project.'
    ), (
      3,
      1,
      1,
      (SELECT state_id FROM states WHERE state = 'public'),
      CURRENT_TIMESTAMP,
      'Test Blog',
      'test-blog',
      'This is the body of a test blog entry.'
    )

  - |
    INSERT INTO posts_fts(rowid, name, slug, body)
      SELECT post_id, name, slug, body FROM posts

  - |
    INSERT INTO pages(post_id, layout_id) VALUES (
      1,
      (SELECT layout_id FROM layouts WHERE layout = 'default')
    )

  - |
    INSERT INTO projects(post_id) VALUES (2)

  - |
    INSERT INTO blogs(post_id) VALUES (3)