
-- createTables.sql:
--   write:
		CREATE TABLE IF NOT EXISTS "papod_workspaces" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			timestamp    TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')),
			uuid         BLOB    NOT NULL UNIQUE,
			name         TEXT    NOT NULL,
			description  TEXT    NOT NULL,
			-- "public", "private", "unlisted"
			type         TEXT    NOT NULL
		);
		CREATE TABLE IF NOT EXISTS "papod_workspace_changes (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			timestamp    TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')),
			workspace_id INTEGER NOT NULL
				REFERENCES "papod_workspaces"(id),
			attribute    TEXT    NOT NULL,
			value        TEXT    NOT NULL,
			op           BOOLEAN NOT NULL
		);
		CREATE TABLE IF NOT EXISTS "papod_users" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			timestamp    TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')),
			-- provided by cracha
			uuid         BLOB    NOT NULL UNIQUE,
			username     TEXT    NOT NULL,
			display_name TEXT    NOT NULL,
			picture_uuid BLOB    NOT NULL UNIQUE,
			deleted      BOOLEAN NOT NULL
		);
		CREATE TABLE IF NOT EXISTS "papod_user_changes" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			timestamp    TEXT    NOT NULL DEFAULT (papod),
			user_id      INTEGER NOT NULL REFERENCES "strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')_users"(id),
			attribute    TEXT    NOT NULL,
			value        TEXT    NOT NULL,
			op           BOOLEAN NOT NULL
		);
		CREATE TABLE IF NOT EXISTS "papod_members" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			timestamp    TEXT    NOT NULL DEFAULT (%!s(MISSING)),
			workspace_id INTEGER NOT NULL
				REFERENCES "%!s(MISSING)_workspaces"(id),
			user_id      INTEGER NOT NULL,
			username     TEXT    NOT NULL,
			display_name TEXT    NOT NULL,
			picture_uuid BLOB    NOT NULL UNIQUE,
			-- "active", "inactive", "removed"
			status       TEXT    NOT NULL,
			-- "active", always
			active_uniq  TEXT,
			UNIQUE (workspace_id, username, active_uniq),
			UNIQUE (workspace_id, user_id)
		);
		CREATE TABLE IF NOT EXISTS "%!s(MISSING)_member_roles" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			member_id    INTEGER NOT NULL
				REFERENCES "%!s(MISSING)_members"(id),
			role         TEXT    NOT NULL,
			UNIQUE (member_id, role)
		);
		CREATE TABLE IF NOT EXISTS "%!s(MISSING)_member_changes" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			timestamp    TEXT    NOT NULL DEFAULT (%!s(MISSING)),
			member_id    INTEGER NOT NULL 
				REFERENCES "%!s(MISSING)_members"(id),
			attribute    TEXT    NOT NULL,
			value        TEXT    NOT NULL,
			op           BOOLEAN NOT NULL
		);
		CREATE TABLE IF NOT EXISTS "%!s(MISSING)_channels" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			timestamp    TEXT    NOT NULL DEFAULT (%!s(MISSING)),
			uuid         BLOB    NOT NULL UNIQUE,
			workspace_id INTEGER NOT NULL
				REFERENCES "%!s(MISSING)_workspaces"(id),
			public_name  TEXT             UNIQUE,
			label        TEXT    NOT NULL,
			description  TEXT,
			virtual      BOOLEAN NOT NULL
		);
		CREATE TABLE IF NOT EXISTS "%!s(MISSING)_channel_changes" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			timestamp    TEXT    NOT NULL DEFAULT (%!s(MISSING)),
			channel_id   INTEGER NOT NULL
				REFERENCES "%!s(MISSING)_channels"(id),
			attribute    TEXT    NOT NULL,
			value        TEXT    NOT NULL,
			op           BOOLEAN NOT NULL
		);
		CREATE TABLE IF NOT EXISTS "%!s(MISSING)_participants" (
			member_id
		);
		CREATE TABLE IF NOT EXISTS "%!s(MISSING)_channel_events" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			timestamp    TEXT    NOT NULL DEFAULT (%!s(MISSING)),
			uuid         BLOB    NOT NULL UNIQUE,
			channel_id   INTEGER NOT NULL REFERENCES "%!s(MISSING)"(id),
			connection_id INTEGER NOT NULL,
			-- payload FIXME: vary by type?
		);
		-- FIXME: group conversations?
			-- user: person
			-- member: workspace user
			-- participant: channel member
	

--    read:

-- addNetwork.sql:
--   write:
		-- FIXME
	%!(EXTRA string=papod)

--    read:

-- addChannel.sql:
--   write:
		-- FIXME
	%!(EXTRA string=papod)

--    read:

-- channels.sql:
--   write:

--    read:
		-- FIXME
	%!(EXTRA string=papod)

-- allAfter.sql:
--   write:

--    read:
		-- FIXME
	%!(EXTRA string=papod)

-- addEvent.sql:
--   write:
		-- FIXME
	%!(EXTRA string=papod)

--    read:
