CREATE TABLE "CONTRIBUTORS" (
"user_id"  INTEGER NOT NULL,
"stream_id"  INTEGER NOT NULL,
PRIMARY KEY ("user_id", "stream_id"),
CONSTRAINT "user_key" FOREIGN KEY ("user_id") REFERENCES "USERS" ("user_id"),
CONSTRAINT "stream_key" FOREIGN KEY ("stream_id") REFERENCES "STREAMS" ("stream_id")
);

CREATE INDEX "index1"
ON "CONTRIBUTORS" ("user_id" ASC, "stream_id" ASC);

CREATE INDEX "index2"
ON "CONTRIBUTORS" ("user_id" ASC);

CREATE INDEX "index3"
ON "CONTRIBUTORS" ("stream_id" ASC);

CREATE TABLE "LOCATIONS" (
"location_id"  INTEGER NOT NULL,
"longitude"  TEXT(20),
"lattitude"  TEXT(20),
"name"  TEXT(20),
PRIMARY KEY ("location_id" ASC)
);

CREATE INDEX "index7"
ON "LOCATIONS" ("location_id", "longitude", "lattitude", "name");

CREATE INDEX "index8"
ON "LOCATIONS" ("location_id");

CREATE TABLE "OBJECT_IN_STREAM" (
"object_id"  INTEGER NOT NULL,
"stream_id"  INTEGER NOT NULL,
PRIMARY KEY ("object_id", "stream_id"),
CONSTRAINT "object_key" FOREIGN KEY ("object_id") REFERENCES "OBJECTS" ("object_id"),
CONSTRAINT "stream_key" FOREIGN KEY ("stream_id") REFERENCES "STREAMS" ("stream_id")
);

CREATE INDEX "index4"
ON "OBJECT_IN_STREAM" ("object_id", "stream_id");

CREATE INDEX "index5"
ON "OBJECT_IN_STREAM" ("object_id");

CREATE INDEX "index6"
ON "OBJECT_IN_STREAM" ("stream_id");

CREATE TABLE "OBJECTS" (
"object_id"  INTEGER NOT NULL,
"count"  INTEGER NOT NULL DEFAULT 0,
"server_deleted_at"  INTEGER NOT NULL DEFAULT 0,
"n_streams"  INTEGER NOT NULL,
"text"  TEXT(500) NOT NULL,
"created_at"  TEXT(32) NOT NULL,
"modified_at"  TEXT(32) NOT NULL,
"legacy_v2_share"  TEXT(8) NOT NULL,
"server_modified_at"  INTEGER NOT NULL,
"child_of"  INTEGER,
"catch:starred"  TEXT(8),
"type"  INTEGER NOT NULL,
"location_id"  INTEGER,
"user_id"  INTEGER NOT NULL,
"content_type"  TEXT(32),
"size"  INTEGER,
"filename"  TEXT(32),
"checked"  INTEGER,
"modified_locally"  TEXT(32) NOT NULL DEFAULT 0,
"deleted_locally"  TEXT(8) NOT NULL DEFAULT 0,
"is_up_to_date"  TEXT(8) NOT NULL DEFAULT 0,
"file_path"  TEXT(32),
PRIMARY KEY ("object_id" ASC),
CONSTRAINT "user_key" FOREIGN KEY ("user_id") REFERENCES "USERS" ("user_id"),
CONSTRAINT "location_key" FOREIGN KEY ("location_id") REFERENCES "LOCATIONS" ("location_id"),
CONSTRAINT "unq1" UNIQUE ("file_path")
);

CREATE INDEX "index16"
ON "OBJECTS" ("object_id", "server_deleted_at", "text", "server_modified_at", "type", "location_id", "content_type", "modified_locally", "deleted_locally", "is_up_to_date");

CREATE INDEX "index17"
ON "OBJECTS" ("object_id");

CREATE INDEX "index18"
ON "OBJECTS" ("user_id");

CREATE INDEX "index19"
ON "OBJECTS" ("child_of");

CREATE INDEX "index20"
ON "OBJECTS" ("user_id");

CREATE TABLE "STREAMS" (
"stream_id"  INTEGER NOT NULL,
"count"  INTEGER NOT NULL DEFAULT 0,
"contributor_count"  INTEGER NOT NULL,
"name"  TEXT(32) NOT NULL,
"source"  TEXT(32) NOT NULL,
"created_at"  TEXT(32) NOT NULL,
"modified_at"  TEXT(32) NOT NULL,
"server_modified_at"  INTEGER NOT NULL,
"user_color"  TEXT(10) NOT NULL,
"color"  TEXT(10) NOT NULL,
"server_created_at"  INTEGER NOT NULL,
"server_deleted_at"  INTEGER NOT NULL,
"is_up_to_date"  TEXT(8) NOT NULL DEFAULT 0,
"deleted_locally"  TEXT(32) DEFAULT 0,
"modified_locally"  TEXT(32) DEFAULT 0,
PRIMARY KEY ("stream_id" ASC),
CONSTRAINT "unq1" UNIQUE ("name")
);

CREATE INDEX "index10"
ON "STREAMS" ("stream_id");

CREATE INDEX "index9"
ON "STREAMS" ("stream_id", "name", "server_modified_at", "server_created_at", "server_deleted_at", "is_up_to_date", "deleted_locally", "modified_locally");

CREATE TABLE "TAGS" (
"tag_id"  INTEGER NOT NULL,
"tag_text"  TEXT(20) NOT NULL,
PRIMARY KEY ("tag_id" ASC),
CONSTRAINT "unq1" UNIQUE ("tag_text")
);

CREATE INDEX "index11"
ON "TAGS" ("tag_id", "tag_text");

CREATE INDEX "index12"
ON "TAGS" ("tag_id");

CREATE TABLE "TAGS_IN_OBJECTS" (
"tag_id"  INTEGER NOT NULL,
"object_id"  INTEGER NOT NULL,
PRIMARY KEY ("tag_id", "object_id"),
CONSTRAINT "tag_key" FOREIGN KEY ("tag_id") REFERENCES "TAGS" ("tag_id"),
CONSTRAINT "object_key" FOREIGN KEY ("object_id") REFERENCES "OBJECTS" ("object_id")
);

CREATE INDEX "index13"
ON "TAGS_IN_OBJECTS" ("tag_id", "object_id");

CREATE INDEX "index14"
ON "TAGS_IN_OBJECTS" ("tag_id");

CREATE INDEX "index15"
ON "TAGS_IN_OBJECTS" ("object_id");

CREATE TABLE "USERS" (
"user_id"  INTEGER NOT NULL,
"user_name"  TEXT(20) NOT NULL,
"user_email"  TEXT(20) NOT NULL,
PRIMARY KEY ("user_id" ASC),
CONSTRAINT "unq1" UNIQUE ("user_name"),
CONSTRAINT "unq2" UNIQUE ("user_email")
);