Database Setup

-- Index for foreign key lookups
CREATE INDEX _articles__author_index ON articles(author);

-- Index for filtering
CREATE INDEX _posts__published_index ON posts(published);

-- Unique index for constraints
CREATE UNIQUE INDEX _profiles__username_index ON profiles(username);

-- Composite index for queries like: WHERE user = ? AND created > ?
CREATE INDEX _todos__user_created_index ON todos(user, created);

-- Unique composite constraint
CREATE UNIQUE INDEX _likes__user_post_index ON likes(user, post_id);

_<table_name>__<column_name(s)>_index

CREATE TABLE comments (
  id      INTEGER PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  user_id BLOB NOT NULL REFERENCES _user(id) ON DELETE CASCADE,
  text    TEXT NOT NULL
) STRICT;

CREATE TABLE products (
  id       INTEGER PRIMARY KEY,
  name     TEXT NOT NULL,
  price    REAL NOT NULL CHECK(price > 0),
  rating   REAL CHECK(rating >= 0 AND rating <= 5),
  email    TEXT CHECK(email LIKE '%_@_%._%'),
  status   TEXT CHECK(status IN ('draft', 'published', 'archived'))
) STRICT;

CREATE TABLE accounts (
  id       INTEGER PRIMARY KEY,
  email    TEXT NOT NULL UNIQUE,
  username TEXT NOT NULL,
  UNIQUE(username)  -- Alternative syntax
) STRICT;

CREATE TABLE todos (
  id          INTEGER PRIMARY KEY,
  text        TEXT NOT NULL,
  completed   INTEGER NOT NULL DEFAULT 0,
  priority    INTEGER DEFAULT 1,
  created_at  INTEGER DEFAULT (UNIXEPOCH()) NOT NULL,
  due_date    INTEGER  -- NULL by default
) STRICT;

CREATE TRIGGER _profiles__updated_trigger AFTER UPDATE ON profiles FOR EACH ROW
  BEGIN
    UPDATE profiles SET updated = UNIXEPOCH() WHERE user = OLD.user;
  END;

CREATE TRIGGER _posts__validate_dates BEFORE INSERT ON posts FOR EACH ROW
  BEGIN
    SELECT RAISE(ABORT, 'published_at must be after created_at')
    WHERE NEW.published_at < NEW.created_at;
  END;

-- Join articles with user profiles
CREATE VIEW articles_view AS 
  SELECT 
    a.*,
    p.username 
  FROM articles AS a 
  LEFT JOIN profiles AS p ON p.user = a.author;

CREATE VIEW profiles_view AS
  SELECT
    p.*,
    -- Type cast required for JSON schema generation
    CAST(CASE
      WHEN avatar.file IS NOT NULL THEN CONCAT('/api/auth/avatar/', uuid_text(p.user))
      ELSE NULL
    END AS TEXT) AS avatar_url,
    CAST(IIF(editors.user IS NULL, FALSE, TRUE) AS INTEGER) AS is_editor
  FROM profiles AS p
    LEFT JOIN _user_avatar AS avatar ON p.user = avatar.user
    LEFT JOIN editors ON p.user = editors.user;

CREATE TABLE posts (
  id       INTEGER PRIMARY KEY,
  title    TEXT NOT NULL,
  -- Single file upload with type constraints
  image    TEXT CHECK(jsonschema('std.FileUpload', image, 'image/png, image/jpeg')),
  -- Multiple file uploads
  gallery  TEXT CHECK(jsonschema('std.FileUploads', gallery))
) STRICT;

-- Create FTS index on articles
CREATE VIRTUAL TABLE articles_fts USING fts5(
  title,
  body,
  content='articles',
  content_rowid='id'
);

-- Triggers to keep FTS index in sync
CREATE TRIGGER articles_fts_insert AFTER INSERT ON articles BEGIN
  INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

CREATE TRIGGER articles_fts_update AFTER UPDATE ON articles BEGIN
  UPDATE articles_fts SET title = new.title, body = new.body WHERE rowid = old.id;
END;

CREATE TRIGGER articles_fts_delete AFTER DELETE ON articles BEGIN
  DELETE FROM articles_fts WHERE rowid = old.id;
END;

-- Create a custom query that checks group membership
CREATE VIRTUAL TABLE _is_editor USING define((
  SELECT EXISTS (SELECT * FROM editors WHERE user = $1) AS is_editor
));