CREATE TABLE pages (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    path       TEXT NOT NULL UNIQUE,
    html_path  TEXT NOT NULL,
    title      TEXT NOT NULL DEFAULT '',
    date       TEXT DEFAULT '',
    tags       TEXT DEFAULT '[]',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_pages_path ON pages(path);
CREATE INDEX idx_pages_date ON pages(date);

CREATE TABLE posts (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    slug       TEXT NOT NULL UNIQUE,
    title      TEXT NOT NULL DEFAULT '',
    date       TEXT DEFAULT '',
    tags       TEXT DEFAULT '[]',
    draft      INTEGER NOT NULL DEFAULT 0,
    blocks     TEXT NOT NULL DEFAULT '[]',
    updated_at INTEGER NOT NULL DEFAULT (cast(strftime('%s','now') * 1000000 as integer))
);
CREATE INDEX idx_posts_slug ON posts(slug);
CREATE INDEX idx_posts_date ON posts(date);

CREATE TABLE redirects (
    from_slug TEXT PRIMARY KEY,
    to_slug   TEXT NOT NULL
);

CREATE TABLE settings (
    key   TEXT PRIMARY KEY,
    value TEXT NOT NULL DEFAULT ''
);

CREATE VIRTUAL TABLE pages_fts USING fts5(
    path      UNINDEXED,
    title,
    content,
    tokenize = 'porter unicode61'
);