-- createTables.sql:
--   write:
		-- TODO: unconfirmed premise: statements within a trigger are
		--       part of the transaction that caused it, and so are
		--       atomic.
		-- See also:
		-- https://stackoverflow.com/questions/77441888/
		-- https://stackoverflow.com/questions/30511116/

		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
			user_uuid    BLOB    NOT NULL UNIQUE,
			username     TEXT    NOT NULL,
			display_name TEXT    NOT NULL,
			picture_uuid BLOB             UNIQUE,
			deleted      INT     NOT NULL CHECK(deleted IN (0, 1))
		) STRICT;
		CREATE TABLE IF NOT EXISTS "papod_user_changes" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			timestamp    TEXT    NOT NULL DEFAULT (
				strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')
			),
			user_id      INTEGER NOT NULL,
			attribute    TEXT    NOT NULL CHECK(
				attribute IN (
					'username',
					'display_name',
					'picture_uuid',
					'deleted'
				)
			),
			value_text   TEXT,
			value_blob   BLOB,
			value_bool   INT      CHECK(value_bool IN (0, 1)),
			op           INT     NOT NULL CHECK(op IN (0, 1))
		) STRICT;
		CREATE TRIGGER IF NOT EXISTS "papod_user_new"
			AFTER INSERT ON "papod_users"
		BEGIN
			INSERT INTO "papod_user_changes" (
				user_id, attribute, value_text, op
			) VALUES
			(NEW.id, 'username',     NEW.username,     true),
			(NEW.id, 'display_name', NEW.display_name, true)
			;
			INSERT INTO "papod_user_changes" (
				user_id, attribute, value_bool, op
			) VALUES
			(NEW.id, 'deleted',      NEW.deleted,      true)
			;
		END;
		CREATE TRIGGER IF NOT EXISTS "papod_user_new_picture_uuid"
			AFTER INSERT ON "papod_users"
			WHEN NEW.picture_uuid IS NOT NULL
		BEGIN
			INSERT INTO "papod_user_changes" (
				user_id, attribute, value_blob, op
			) VALUES
			(NEW.id, 'picture_uuid', NEW.picture_uuid, true)
			;
		END;
		CREATE TRIGGER IF NOT EXISTS "papod_user_update_username"
			AFTER UPDATE ON "papod_users"
			WHEN OLD.username != NEW.username
		BEGIN
			INSERT INTO "papod_user_changes" (
				user_id, attribute, value_text, op
			) VALUES
			(NEW.id, 'username',     OLD.username,     false),
			(NEW.id, 'username',     NEW.username,     true)
			;
		END;
		CREATE TRIGGER IF NOT EXISTS "papod_user_update_display_name"
			AFTER UPDATE ON "papod_users"
			WHEN OLD.display_name != NEW.display_name
		BEGIN
			INSERT INTO "papod_user_changes" (
				user_id, attribute, value_text, op
			) VALUES
			(NEW.id, 'display_name', OLD.display_name, false),
			(NEW.id, 'display_name', NEW.display_name, true)
			;
		END;
		CREATE TRIGGER IF NOT EXISTS "papod_user_add_picture_uuid"
			AFTER UPDATE ON "papod_users"
			WHEN (
				OLD.picture_uuid IS     NULL AND
				NEW.picture_uuid IS NOT NULL
			)
		BEGIN
			INSERT INTO "papod_user_changes" (
				user_id, attribute, value_blob, op
			) VALUES
			(NEW.id, 'picture_uuid', NEW.picture_uuid, true)
			;
		END;
		CREATE TRIGGER IF NOT EXISTS "papod_user_remove_picture_uuid"
			AFTER UPDATE ON "papod_users"
			WHEN (
				OLD.picture_uuid IS NOT NULL AND
				NEW.picture_uuid IS     NULL
			)
		BEGIN
			INSERT INTO "papod_user_changes" (
				user_id, attribute, value_blob, op
			) VALUES
			(NEW.id, 'picture_uuid', OLD.picture_uuid, false)
			;
		END;
		CREATE TRIGGER IF NOT EXISTS "papod_user_update_picture_uuid"
			AFTER UPDATE ON "papod_users"
			WHEN (
				OLD.picture_uuid IS NOT NULL AND
				NEW.picture_uuid IS NOT NULL AND
				OLD.picture_uuid != NEW.picture_uuid
			)
		BEGIN
			INSERT INTO "papod_user_changes" (
				user_id, attribute, value_blob, op
			) VALUES
			(NEW.id, 'picture_uuid', OLD.picture_uuid, false),
			(NEW.id, 'picture_uuid', NEW.picture_uuid, true)
			;
		END;
		CREATE TRIGGER IF NOT EXISTS "papod_user_update_deleted"
			AFTER UPDATE ON "papod_users"
			WHEN OLD.deleted != NEW.deleted
		BEGIN
			INSERT INTO "papod_user_changes" (
				user_id, attribute, value_bool, op
			) VALUES
			(NEW.id, 'deleted',      OLD.deleted,      false),
			(NEW.id, 'deleted',      NEW.deleted,      true)
			;
		END;

		CREATE TABLE IF NOT EXISTS "papod_sessions" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			timestamp    TEXT    NOT NULL DEFAULT (
				strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')
			),
			-- provided by cracha
			session_uuid BLOB    NOT NULL UNIQUE,
			user_id      INTEGER NOT NULL
				REFERENCES "papod_users"(id),
			finished_at  TEXT
		);
		CREATE TABLE IF NOT EXISTS "papod_connections" (
			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,
			finished_at  TEXT
		);
		CREATE TABLE IF NOT EXISTS "papod_logons" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			timestamp    TEXT    NOT NULL DEFAULT (
				strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')
			),
			session_id   INTEGER NOT NULL
				REFERENCES "papod_sessions"(id),
			connection_id INTEGER NOT NULL
				REFERENCES "papod_connections"(id),
			UNIQUE (session_id, connection_id)
		) STRICT;

		CREATE TABLE IF NOT EXISTS "papod_networks" (
			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,
			type         TEXT    NOT NULL CHECK(
				type IN ('public', 'private', 'unlisted')
			),
			deleted      INT     NOT NULL CHECK(deleted IN (0, 1))
		) STRICT;
		CREATE INDEX IF NOT EXISTS "papod_networks_type"
			ON "papod_networks"(type);
		CREATE TABLE IF NOT EXISTS "papod_network_changes" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			timestamp    TEXT    NOT NULL DEFAULT (
				strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')
			),
			network_id   INTEGER NOT NULL,
			attribute    TEXT    NOT NULL CHECK(
				attribute IN (
					'name',
					'description',
					'type',
					'deleted',
					'logon_id' -- FIXME
				)
			),
			value        TEXT    NOT NULL,
			op           INT     NOT NULL CHECK(op IN (0, 1))
		) STRICT;
		CREATE TRIGGER IF NOT EXISTS "papod_network_new"
			AFTER INSERT ON "papod_networks"
		BEGIN
			INSERT INTO "papod_network_changes" (
				network_id, attribute, value, op
			) VALUES
			(NEW.id, 'name',        NEW.name,        true),
			(NEW.id, 'description', NEW.description, true),
			(NEW.id, 'type',        NEW.type,        true),
			(NEW.id, 'deleted',     NEW.deleted,     true)
			;
		END;
		CREATE TRIGGER IF NOT EXISTS "papod_network_update_name"
			AFTER UPDATE ON "papod_networks"
			WHEN OLD.name != NEW.name
		BEGIN
			INSERT INTO "papod_network_changes" (
				network_id, attribute, value, op
			) VALUES
			(NEW.id, 'name', OLD.name, false),
			(NEW.id, 'name', NEW.name, true)
			;
		END;
		CREATE TRIGGER IF NOT EXISTS "papod_network_update_description"
			AFTER UPDATE ON "papod_networks"
			WHEN OLD.description != NEW.description
		BEGIN
			INSERT INTO "papod_network_changes" (
				network_id, attribute, value, op
			) VALUES
			(NEW.id, 'description', OLD.description, false),
			(NEW.id, 'description', NEW.description, true)
			;
		END;
		CREATE TRIGGER IF NOT EXISTS "papod_network_update_type"
			AFTER UPDATE ON "papod_networks"
			WHEN OLD.description != NEW.description
		BEGIN
			INSERT INTO "papod_network_changes" (
				network_id, attribute, value, op
			) VALUES
			(NEW.id, 'type', OLD.type, false),
			(NEW.id, 'type', NEW.type, true)
			;
		END;
		CREATE TRIGGER IF NOT EXISTS "papod_network_update_deleted"
			AFTER UPDATE ON "papod_networks"
			WHEN OLD.deleted != NEW.deleted
		BEGIN
			INSERT INTO "papod_network_changes" (
				network_id, attribute, value, op
			) VALUES
			(NEW.id, 'deleted', OLD.deleted, false),
			(NEW.id, 'deleted', NEW.deleted, true)
			;
		END;

		CREATE TABLE IF NOT EXISTS "papod_members" (
			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,
			network_id   INTEGER NOT NULL
				REFERENCES "papod_networks"(id),
			user_id      INTEGER NOT NULL,
			username     TEXT    NOT NULL,
			display_name TEXT    NOT NULL,
			picture_uuid BLOB             UNIQUE,
			status       TEXT    NOT NULL CHECK(
				status IN ('active', 'inactive', 'removed')
			),
			active_uniq  TEXT CHECK(
				active_uniq IN ('active', NULL)
			),
			UNIQUE (network_id, username, active_uniq),
			UNIQUE (network_id, user_id)
		) STRICT;
		CREATE TABLE IF NOT EXISTS "papod_member_changes" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			timestamp    TEXT    NOT NULL DEFAULT (
				strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')
			),
			member_id    INTEGER NOT NULL,
			attribute    TEXT    NOT NULL CHECK(
				attribute IN (
					'username',
					'display_name',
					'picture_uuid',
					'status',
					'logon_id' -- FIXME
				)
			),
			value_text   TEXT,
			value_blob   BLOB,
			op           INT     NOT NULL CHECK(op IN (0, 1))
		) STRICT;
		CREATE TRIGGER IF NOT EXISTS "papod_member_new"
			AFTER INSERT ON "papod_members"
		BEGIN
			INSERT INTO "papod_member_changes" (
				member_id, attribute, value_text, op
			) VALUES
			(NEW.id, 'username',     NEW.username,     true),
			(NEW.id, 'display_name', NEW.display_name, true),
			(NEW.id, 'status',       NEW.status,       true)
			;
		END;
		CREATE TRIGGER IF NOT EXISTS "papod_member_new_picture_uuid"
			AFTER INSERT ON "papod_members"
			WHEN NEW.picture_uuid IS NOT NULL
		BEGIN
			INSERT INTO "papod_member_changes" (
				member_id, attribute, value_blob, op
			) VALUES
			(NEW.id, 'picture_uuid', NEW.picture_uuid, true)
			;
		END;
		CREATE TRIGGER IF NOT EXISTS "papod_member_update_username"
			AFTER UPDATE ON "papod_members"
			WHEN OLD.username != NEW.username
		BEGIN
			INSERT INTO "papod_member_changes" (
				member_id, attribute, value_text, op
			) VALUES
			(NEW.id, 'username', OLD.username, false),
			(NEW.id, 'username', NEW.username, true)
			;
		END;
		CREATE TRIGGER IF NOT EXISTS "papod_member_update_display_name"
			AFTER UPDATE ON "papod_members"
			WHEN OLD.display_name != NEW.display_name
		BEGIN
			INSERT INTO "papod_member_changes" (
				member_id, attribute, value_text, op
			) VALUES
			(NEW.id, 'display_name', OLD.display_name, false),
			(NEW.id, 'display_name', NEW.display_name, true)
			;
		END;
		CREATE TRIGGER IF NOT EXISTS "papod_member_update_status"
			AFTER UPDATE ON "papod_members"
			WHEN OLD.status != NEW.status
		BEGIN
			INSERT INTO "papod_member_changes" (
				member_id, attribute, value_text, op
			) VALUES
			(NEW.id, 'status', OLD.status, false),
			(NEW.id, 'status', NEW.status, true)
			;
		END;
		CREATE TRIGGER IF NOT EXISTS "papod_member_add_picture_uuid"
			AFTER UPDATE ON "papod_members"
			WHEN (
				OLD.picture_uuid IS     NULL AND
				NEW.picture_uuid IS NOT NULL
			)
		BEGIN
			INSERT INTO "papod_member_changes" (
				member_id, attribute, value_blob, op
			) VALUES
			(NEW.id, 'picture_uuid', NEW.picture_uuid, true)
			;
		END;
		CREATE TRIGGER IF NOT EXISTS "papod_member_remove_picture_uuid"
			AFTER UPDATE ON "papod_members"
			WHEN (
				OLD.picture_uuid IS NOT NULL AND
				NEW.picture_uuid IS     NULL
			)
		BEGIN
			INSERT INTO "papod_member_changes" (
				member_id, attribute, value_blob, op
			) VALUES
			(NEW.id, 'picture_uuid', OLD.picture_uuid, false)
			;
		END;
		CREATE TRIGGER IF NOT EXISTS "papod_member_update_picture_uuid"
			AFTER UPDATE ON "papod_members"
			WHEN (
				OLD.picture_uuid IS NOT NULL AND
				NEW.picture_uuid IS NOT NULL AND
				OLD.picture_uuid != NEW.picture_uuid
			)
		BEGIN
			INSERT INTO "papod_member_changes" (
				member_id, attribute, value_blob, op
			) VALUES
			(NEW.id, 'picture_uuid', OLD.picture_uuid, false),
			(NEW.id, 'picture_uuid', NEW.picture_uuid, true)
			;
		END;

		CREATE TABLE IF NOT EXISTS "papod_member_roles" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			member_id    INTEGER NOT NULL
				REFERENCES "papod_members"(id),
			role         TEXT    NOT NULL,
			UNIQUE (member_id, role)
		) STRICT;
		CREATE TABLE IF NOT EXISTS "papod_member_role_changes" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			timestamp    TEXT    NOT NULL DEFAULT (
				strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')
			),
			role_id      INTEGER NOT NULL,
			attribute    TEXT    NOT NULL CHECK(
				attribute IN (
					'role',
					'logon_id' -- FIXME
				)
			),
			value        TEXT    NOT NULL,
			op           INT     NOT NULL CHECK(op IN (0, 1))
		) STRICT;
		CREATE TRIGGER IF NOT EXISTS "papod_member_role_add"
			AFTER INSERT ON "papod_member_roles"
		BEGIN
			INSERT INTO "papod_member_role_changes" (
				role_id, attribute, value, op
			) VALUES
			(NEW.id, 'role', NEW.role, true)
			;
		END;
		CREATE TRIGGER IF NOT EXISTS "papod_member_role_remove"
			AFTER DELETE ON "papod_member_roles"
		BEGIN
			INSERT INTO "papod_member_role_changes" (
				role_id, attribute, value, op
			) VALUES
			(OLD.id, 'role', OLD.role, false)
			;
		END;

		CREATE TABLE IF NOT EXISTS "papod_channels" (
			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,
			network_id   INTEGER NOT NULL
				REFERENCES "papod_networks"(id),
			public_name  TEXT,
			label        TEXT    NOT NULL,
			description  TEXT    NOT NULL,
			virtual      INT     NOT NULL CHECK(virtual IN (0, 1)),
			UNIQUE (network_id, public_name)
		) STRICT;
		CREATE TABLE IF NOT EXISTS "papod_channel_changes" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			timestamp    TEXT    NOT NULL DEFAULT (
				strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')
			),
			channel_id   INTEGER NOT NULL,
			attribute    TEXT    NOT NULL CHECK(
				attribute IN (
					'public_name',
					'label',
					'description',
					'virtual',
					'logon_id' -- FIXME
				)
			),
			value_text   TEXT,
			value_bool   INT      CHECK(value_bool IN (0, 1)),
			op           INT     NOT NULL CHECK(op IN (0, 1))
		) STRICT;
		CREATE TRIGGER IF NOT EXISTS "papod_channel_new"
			AFTER INSERT ON "papod_channels"
		BEGIN
			INSERT INTO "papod_channel_changes" (
				channel_id, attribute, value_text, op
			) VALUES
			(NEW.id, 'label',       NEW.label,       true),
			(NEW.id, 'description', NEW.description, true)
			;
			INSERT INTO "papod_channel_changes" (
				channel_id, attribute, value_bool, op
			) VALUES
			(NEW.id, 'virtual', NEW.virtual, true)
			;
		END;
		CREATE TRIGGER IF NOT EXISTS "papod_channel_new_public_name"
			AFTER INSERT ON "papod_channels"
			WHEN NEW.public_name IS NOT NULL
		BEGIN
			INSERT INTO "papod_channel_changes" (
				channel_id, attribute, value_text, op
			) VALUES
			(NEW.id, 'public_name', NEW.public_name, true)
			;
		END;
		CREATE TRIGGER IF NOT EXISTS "papod_channel_update_label"
			AFTER UPDATE ON "papod_channels"
			WHEN OLD.label != NEW.label
		BEGIN
			INSERT INTO "papod_channel_changes" (
				channel_id, attribute, value_text, op
			) VALUES
			(NEW.id, 'label', OLD.label, false),
			(NEW.id, 'label', NEW.label, true)
			;
		END;
		CREATE TRIGGER IF NOT EXISTS "papod_channel_update_description"
			AFTER UPDATE ON "papod_channels"
			WHEN OLD.description != NEW.description
		BEGIN
			INSERT INTO "papod_channel_changes" (
				channel_id, attribute, value_text, op
			) VALUES
			(NEW.id, 'description', OLD.description, false),
			(NEW.id, 'description', NEW.description, true)
			;
		END;
		CREATE TRIGGER IF NOT EXISTS "papod_channel_update_virtual"
			AFTER UPDATE ON "papod_channels"
			WHEN OLD.virtual != NEW.virtual
		BEGIN
			INSERT INTO "papod_channel_changes" (
				channel_id, attribute, value_bool, op
			) VALUES
			(NEW.id, 'virtual', OLD.virtual, false),
			(NEW.id, 'virtual', NEW.virtual, true)
			;
		END;
		CREATE TRIGGER IF NOT EXISTS "papod_channel_add_public_name"
			AFTER UPDATE ON "papod_channels"
			WHEN (
				OLD.public_name IS     NULL AND
				NEW.public_name IS NOT NULL
			)
		BEGIN
			INSERT INTO "papod_channel_changes" (
				channel_id, attribute, value_text, op
			) VALUES
			(NEW.id, 'public_name', NEW.public_name, true)
			;
		END;
		CREATE TRIGGER IF NOT EXISTS "papod_channel_remove_public_name"
			AFTER UPDATE ON "papod_channels"
			WHEN (
				OLD.public_name IS NOT NULL AND
				NEW.public_name IS     NULL
			)
		BEGIN
			INSERT INTO "papod_channel_changes" (
				channel_id, attribute, value_text, op
			) VALUES
			(OLD.id, 'public_name', OLD.public_name, false)
			;
		END;

		CREATE TABLE IF NOT EXISTS "papod_participants" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			channel_id   INTEGER NOT NULL
				REFERENCES "papod_channels"(id),
			member_id    INTEGER NOT NULL
				REFERENCES "papod_members"(id),
			UNIQUE (channel_id, member_id)
		) STRICT;
		CREATE TABLE IF NOT EXISTS "papod_participant_changes" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			timestamp    TEXT    NOT NULL DEFAULT (
				strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')
			),
			participant_id INTEGER NOT NULL,
			attribute    TEXT    NOT NULL CHECK(
				attribute IN (
					'connection_id'
				)
			),
			value        TEXT    NOT NULL,
			op           INT     NOT NULL CHECK(op IN (0, 1))
		) STRICT;

		CREATE TABLE IF NOT EXISTS "papod_channel_events" (
			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,
			channel_id   INTEGER NOT NULL
				REFERENCES "papod_channels"(id),
			source_uuid  BLOB NOT NULL,
			source_type  TEXT NOT NULL CHECK(
				source_type IN (
					'logon'
				)
			),
			source_metadata TEXT,
			type         TEXT NOT NULL CHECK(
				type IN (
					'user-join',
					'user-message'
				)
			),
			payload      TEXT NOT NULL,
			metadata     TEXT
		) STRICT;

	

--    read:

-- memberRoles.sql:
--   write:

--    read:
		SELECT role FROM "papod_member_roles"
		JOIN "papod_members" ON
			"papod_member_roles".member_id = "papod_members".id
		WHERE "papod_members".uuid = ?
		ORDER BY "papod_member_roles".id;
	

-- createUser.sql:
--   write:
		INSERT INTO "papod_users" (
			user_uuid, username, display_name, picture_uuid, deleted
		) VALUES (
			?, ?, ?, NULL, false
		) RETURNING id, timestamp;
	

--    read:

-- userByUUID.sql:
--   write:

--    read:
		SELECT
			id,
			timestamp,
			username,
			display_name,
			picture_uuid
		FROM "papod_users"
		WHERE
			user_uuid = ? AND
			deleted   = false;
	

-- updateUser.sql:
--   write:
		UPDATE "papod_users"
		SET
			username     = ?,
			display_name = ?,
			picture_uuid = ?
		WHERE
			id      = ? AND
			deleted = false
		RETURNING id;
	

--    read:

-- deleteUser.sql:
--   write:
		UPDATE "papod_users"
		SET deleted = true
		WHERE
			user_uuid = ? AND
			deleted   = false
		RETURNING id;
	

--    read:

-- addNetwork.sql:
--   write:
		INSERT INTO "papod_networks" (
			uuid, name, description, type, deleted
		)
		VALUES (
			?,
			?,
			?,
			?,
			false
		) RETURNING id;

		WITH creator AS (
			SELECT username, display_name, picture_uuid
			FROM "papod_users"
			WHERE id = ? AND deleted = false
		), new_network AS (
			SELECT id FROM "papod_networks" WHERE uuid = ?
		)
		INSERT INTO "papod_members" (
			uuid, network_id, user_id, username, display_name,
			picture_uuid, status, active_uniq
		) VALUES (
			?,
			(SELECT id           FROM new_network),
			?,
			(SELECT username     FROM creator),
			(SELECT display_name FROM creator),
			(SELECT picture_uuid FROM creator),
			'active',
			'active'
		) RETURNING id;

		WITH new_member AS (
			SELECT id FROM "papod_members" WHERE uuid = ?
		)
		INSERT INTO "papod_member_roles" (member_id, role)
		VALUES (
			(SELECT id FROM new_member),
			'admin'
		),
		(
			(SELECT id FROM new_member),
			'creator'
		)
			RETURNING id;
	

--    read:
		SELECT id, timestamp FROM "papod_networks"
		WHERE uuid = ? AND deleted = false;
	

-- getNetwork.sql:
--   write:

--    read:
		WITH probing_user AS (
			SELECT id FROM "papod_users"
			WHERE id = ? AND deleted = false
		), target_network AS (
			SELECT id FROM "papod_networks"
			WHERE uuid = ? AND deleted = false
		)
		SELECT
			id,
			timestamp,
			name,
			description,
			type
		FROM "papod_networks"
		WHERE
			uuid = ? AND
			deleted = false AND
			? IN probing_user AND
			(
				type IN ('public', 'unlisted') OR
				? IN (
					SELECT user_id FROM "papod_members"
					WHERE
						user_id = ? AND
						network_id IN target_network AND
						status != 'removed'
				)
			);
	%!(EXTRA string=papod, string=papod, string=papod, string=papod, string=papod, string=papod, string=papod, string=papod, string=papod, string=papod, string=papod, string=papod, string=papod, string=papod, string=papod, string=papod, string=papod, string=papod)

-- networks.sql:
--   write:

--    read:
		WITH current_user AS (
			SELECT id, deleted FROM "papod_users" WHERE id = ?
		)
		SELECT
			"papod_networks".id,
			"papod_networks".timestamp,
			"papod_networks".uuid,
			"papod_networks".name,
			"papod_networks".description,
			"papod_networks".type,
			(SELECT deleted FROM current_user)
		FROM "papod_networks"
		JOIN "papod_members" ON
			"papod_networks".id = "papod_members".network_id
		WHERE (
			"papod_networks".type = 'public' OR
			"papod_networks".id IN (
				SELECT network_id FROM "papod_members"
				WHERE user_id IN (SELECT id FROM current_user)
			)
		) AND "papod_networks".deleted = false
		ORDER BY "papod_networks".id;
	

-- setNetwork.sql:
--   write:
		UPDATE "papod_networks"
		SET
			name        = ?,
			description = ?,
			type        = ?
		WHERE id = ? AND deleted = false
		RETURNING (
			SELECT CASE WHEN EXISTS (
				SELECT role from "papod_member_roles"
				WHERE
					member_id = ? AND
					role IN (
						'admin',
						'network-settings-update'
					) AND ? IN (
						SELECT network_id
						FROM "papod_members"
						WHERE
							id     = ? AND
							status = 'active'
					)
			) THEN true ELSE RAISE(
				ABORT,
				'member not allowed to update network data'
			) END
		);

	

--    read:

-- nipNetwork.sql:
--   write:
		WITH target_network AS (
			SELECT network_id AS id
			FROM "papod_members"
			WHERE
				id     = ? AND
				status = 'active'
		)
		UPDATE "papod_networks"
		SET deleted = true
		WHERE id IN target_network AND deleted = false
		RETURNING (
			SELECT CASE WHEN EXISTS (
				SELECT role FROM "papod_member_roles"
				WHERE
					member_id = ? AND
					role IN (
						'admin'
					)
			) THEN true ELSE RAISE(
				ABORT,
				'member not allowed to delete network'
			) END
		);
	

--    read:

-- membership.sql:
--   write:

--    read:
		SELECT
			"papod_members".id,
			"papod_members".timestamp,
			"papod_members".uuid,
			"papod_members".username,
			"papod_members".display_name,
			"papod_members".picture_uuid,
			"papod_members".status
		FROM "papod_members"
		JOIN "papod_users" ON
			"papod_users".id    = "papod_members".user_id
		JOIN "papod_networks" ON
			"papod_networks".id = "papod_members".network_id
		WHERE
			"papod_members".user_id    = ?        AND
			"papod_members".network_id = ?        AND
			"papod_members".status     = 'active' AND
			"papod_users".deleted      = false    AND
			"papod_networks".deleted   = false;
	

-- addMember.sql:
--   write:
		WITH target_user AS (
			SELECT id, username, display_name, picture_uuid
			FROM "papod_users"
			WHERE user_uuid = ? AND deleted = false
		), target_network AS (
			SELECT "papod_members".network_id AS id
			FROM "papod_members"
			JOIN "papod_networks" ON
				"papod_members".network_id = "papod_networks".id
			WHERE
				"papod_members".id     = ?        AND
				"papod_members".status = 'active' AND
				"papod_networks".deleted = false
		)
		INSERT INTO "papod_members" (
			uuid, network_id, user_id, username, display_name,
			picture_uuid, status, active_uniq
		) VALUES (
			?,
			(SELECT id           FROM target_network),
			(SELECT id           FROM target_user),
			?,
			(SELECT display_name FROM target_user),
			(SELECT picture_uuid FROM target_user),
			'active',
			'active'
		) RETURNING id, timestamp, display_name, picture_uuid, status, (
			SELECT CASE WHEN EXISTS (
				SELECT role from "papod_member_roles"
				WHERE
					member_id = ? AND
					role IN (
						'admin',
						'add-member'
					)
			) THEN true ELSE RAISE(
				ABORT,
				'member not allowed to add another member'
			) END
		);
	

--    read:

-- addRole.sql:
--   write:
		INSERT INTO "papod_member_roles" (member_id, role)
		VALUES (?, ?);
	

--    read:

-- dropRole.sql:
--   write:
		DELETE FROM "papod_member_roles"
		WHERE
			member_id = ? AND
			role      = ?
		RETURNING 1;
	

--    read:

-- showMember.sql:
--   write:

--    read:
		WITH current_network AS (
			SELECT network_id
			FROM "papod_members"
			WHERE id = ?
		)
		SELECT
			id,
			timestamp,
			username,
			display_name,
			picture_uuid,
			status
		FROM "papod_members"
		WHERE
			uuid = ? AND
			network_id IN current_network;
	

-- members.sql:
--   write:

--    read:
		WITH target_network AS (
			SELECT "papod_members".network_id
			FROM "papod_members"
			JOIN "papod_networks" ON
				"papod_members".network_id = "papod_networks".id
			WHERE
				"papod_members".id = ? AND
				"papod_networks".deleted = false
		)
		SELECT
			id,
			timestamp,
			uuid,
			username,
			display_name,
			picture_uuid,
			status
		FROM "papod_members"
		WHERE
			network_id IN target_network AND
			status = 'active';
	

-- editMember.sql:
--   write:
		UPDATE "papod_members"
		SET
			status = ?
		WHERE id = ?
		RETURNING id;
	

--    read:

-- dropMember.sql:
--   write:
		UPDATE "papod_members" SET status = 'removed'
		WHERE uuid = ? RETURNING id;

		DELETE FROM "papod_member_roles"
		WHERE
			role != 'creator' AND
			member_id IN (
				SELECT id FROM "papod_members"
				WHERE uuid = ?
			)
	

--    read:

-- addChannel.sql:
--   write:
		WITH target_network AS (
			SELECT network_id AS id
			FROM "papod_members"
			WHERE id = ?
		)
		INSERT INTO "papod_channels" (
			uuid,
			network_id,
			public_name,
			label,
			description,
			virtual
		) VALUES (
			?,
			(SELECT id FROM target_network),
			?,
			?,
			?,
			?
		) RETURNING id, timestamp;

		WITH new_channel AS (
			SELECT id FROM "papod_channels" WHERE uuid = ?
		)
		INSERT INTO "papod_participants" (channel_id, member_id)
		VALUES (
			(SELECT id FROM new_channel),
			?
		);
	

--    read:
		SELECT id, timestamp FROM "papod_channels"
		WHERE uuid = ?;
	

-- channels.sql:
--   write:

--    read:
		WITH current_network AS (
			SELECT network_id AS id
			FROM "papod_members"
			WHERE id = ?
		), member_private_channels AS (
			SELECT channel_id AS id
			FROM "papod_participants"
			WHERE member_id = ?
		)
		SELECT
			id,
			timestamp,
			uuid,
			public_name,
			label,
			description,
			virtual
		FROM "papod_channels"
		WHERE
			network_id IN current_network AND
			(
				public_name IS NOT NULL OR
				id IN member_private_channels
			)
		ORDER BY id;
	

-- setChannel.sql:
--   write:
		WITH participant_channel AS (
			SELECT channel_id AS id
			FROM "papod_participants"
			WHERE
				member_id  = ? AND
				channel_id = ?
		)
		UPDATE "papod_channels"
		SET
			description = ?,
			public_name = ?
		WHERE id IN participant_channel
		RETURNING id;
	

--    read:
		SELECT (
			SELECT network_id AS id
			FROM "papod_channels"
			WHERE id = ?
		) AS channel_network_id, (
			SELECT network_id AS id
			FROM "papod_members"
			WHERE id = ?
		) AS member_network_id;
	

-- endChannel.sql:
--   write:
		-- FIXME papod
	

--    read:

-- join.sql:
--   write:
		WITH target_channel AS (
			SELECT id
			FROM "papod_channels"
			WHERE
				uuid = ? AND
				public_name IS NOT NULL
		)
		INSERT INTO "papod_participants" (channel_id, member_id)
		VALUES (
			(SELECT id FROM target_channel),
			?
		) RETURNING id;
	

--    read:
		SELECT (
			SELECT network_id AS id
			FROM "papod_channels"
			WHERE
				uuid = ? AND
				public_name IS NOT NULL
		) AS channel_network_id, (
			SELECT network_id AS id
			FROM "papod_members" WHERE id = ?
		) AS member_network_id;
	

-- part.sql:
--   write:
		WITH target_channel AS (
			SELECT id
			FROM "papod_channels"
			WHERE
				id = ? AND
				virtual = false
		)
		DELETE FROM "papod_participants"
		WHERE
			member_id  = ? AND
			channel_id IN target_channel
		RETURNING 1;
	

--    read:

-- names.sql:
--   write:

--    read:
		-- FIXME papod
	

-- addEvent.sql:
--   write:
		INSERT INTO "papod_channel_events" (
			uuid, channel_id, source_uuid, source_type,
			source_metadata, type, payload, metadata
		) VALUES (
			?,
			(SELECT id FROM "papod_channels" WHERE uuid = ?),
			?,
			?,
			?,
			?,
			?,
			?
		) RETURNING id, timestamp;
	

--    read:

-- allAfter.sql:
--   write:

--    read:
		WITH landmark_event AS (
			SELECT id, channel_id
			FROM "papod_channel_events"
			WHERE uuid = ?
		)
		SELECT
			"papod_channel_events".id,
			"papod_channel_events".timestamp,
			"papod_channel_events".uuid,
			"papod_channels".uuid,
			-- "papod_channel_events".connection_uuid,
			"papod_channel_events".type,
			"papod_channel_events".payload
		FROM "papod_channel_events"
		JOIN "papod_channels" ON
			"papod_channel_events".channel_id = "papod_channels".id
		WHERE
			"papod_channel_events".id > (
				SELECT id FROM landmark_event
			) AND channel_id = (
				SELECT channel_id FROM landmark_event
			);
	

-- logMessage.sql:
--   write:
		-- FIXME papod
	

--    read: