CREATE TABLE IF NOT EXISTS "users" (
    "id" INTEGER,
    "email" TEXT NOT NULL,
    "password" TEXT NOT NULL,
    "username" TEXT NOT NULL,
    "photo" TEXT,
    "role" INTEGER DEFAULT(0),
    PRIMARY KEY ("id" AUTOINCREMENT),
    UNIQUE ("email" COLLATE NOCASE),
    UNIQUE ("username" COLLATE NOCASE)
);

CREATE TABLE IF NOT EXISTS "nodes" (
    "id" INTEGER,
    "type" INTEGER,
    "text" TEXT,
    "author_id" INTEGER NOT NULL,
    "parent_id" INTEGER,
    "created_at" INTEGER NOT NULL,
    "updated_at" INTEGER NOT NULL,
    "deleted_at" INTEGER,
    "permission" INTEGER,
    PRIMARY KEY ("id" AUTOINCREMENT),
    FOREIGN KEY ("author_id") REFERENCES "users" ("id"),
    FOREIGN KEY ("parent_id") REFERENCES "posts" ("id")
);