-- name: GetAccount :one
SELECT * FROM account WHERE
	did = $1
LIMIT 1;

-- name: UpsertIdentity :exec
INSERT INTO account (did, handle, upstream_status)
	VALUES ($1, $2, $3)
ON CONFLICT (did) DO UPDATE SET
	handle = EXCLUDED.handle,
	upstream_status = EXCLUDED.upstream_status;

-- name: UpsertRecord :exec
INSERT INTO record (account_did, collection, rkey, version, data_json)
	VALUES ($1, $2, $3, $4, $5)
ON CONFLICT (account_did, collection, rkey) DO UPDATE SET
	version = EXCLUDED.version,
	data_json = EXCLUDED.data_json;

-- name: DeleteRecord :exec
DELETE FROM record WHERE
	account_did = $1 AND
	collection = $2 AND
	rkey = $3;

-- name: UpsertItem :exec
INSERT INTO item (scene_did, account_did, rkey, version)
	VALUES ($1, $2, $3, $4)
ON CONFLICT (scene_did, account_did, rkey) DO UPDATE SET
	version = EXCLUDED.version;

-- name: DeleteItem :exec
DELETE FROM item WHERE scene_did = $1 AND account_did = $2 AND rkey = $3;

-- name: GetRecord :one
SELECT * FROM record WHERE
	account_did = $1 AND
	collection = $2 AND
	rkey = $3
LIMIT 1;

-- name: ListSceneItems :many
SELECT sqlc.embed(item), sqlc.embed(record)
FROM item
JOIN record ON
	record.rkey = item.rkey AND
	record.account_did = item.account_did AND
	record.collection = 'social.scrumble.beta.collection.item'
JOIN account ON
	account.did = item.account_did AND
	account.upstream_status = 'active' AND
	account.status = 'active'
WHERE
	-- TODO: filter by membership status (etc)
	item.scene_did = $1
ORDER BY item.created_at DESC
LIMIT 30;

-- name: ListAccountItems :many
SELECT sqlc.embed(item), sqlc.embed(record)
FROM item
JOIN record ON
	record.rkey = item.rkey AND
	record.account_did = item.account_did AND
	record.collection = 'social.scrumble.beta.collection.item'
WHERE
	-- TODO: filter by membership status (etc)
	item.account_did = $1
ORDER BY item.created_at DESC
LIMIT 30;

-- name: UpsertAccountJoin :exec
INSERT INTO membership (account_did, scene_did, joined_at)
	VALUES ($1, $2, $3)
ON CONFLICT (account_did, scene_did) DO UPDATE SET
	joined_at = EXCLUDED.joined_at;

-- name: DeleteAccountJoin :exec
INSERT INTO membership (account_did, scene_did, joined_at)
	VALUES ($1, $2, NULL)
ON CONFLICT (account_did, scene_did) DO UPDATE SET
	joined_at = NULL;

-- name: UpsertSceneMember :exec
INSERT INTO membership (scene_did, account_did, invited_at, role)
	VALUES ($1, $2, $3, $4)
ON CONFLICT (account_did, scene_did) DO UPDATE SET
	invited_at = EXCLUDED.invited_at,
	role = EXCLUDED.role;

-- name: DeleteSceneMember :exec
INSERT INTO membership (scene_did, account_did, invited_at, role)
	VALUES ($1, $2, NULL, NULL)
ON CONFLICT (account_did, scene_did) DO UPDATE SET
	invited_at = NULL,
	role = NULL;

-- name: ListSceneMembers :many
SELECT sqlc.embed(membership), sqlc.embed(record)
FROM membership
JOIN account ON
	account.did = membership.account_did AND
	account.upstream_status = 'active' AND
	account.status = 'active'
JOIN record ON
	record.account_did = membership.account_did AND
	record.collection = 'social.scrumble.beta.account.profile' AND
	record.rkey = 'self'
WHERE
	membership.scene_did = $1 AND
	membership.invited_at IS NOT NULL AND
	membership.joined_at IS NOT NULL AND
	(sqlc.narg('cursor')::timestamptz IS NULL OR membership.invited_at > sqlc.narg('cursor')::timestamptz)
ORDER BY membership.invited_at ASC
LIMIT 30;

-- name: ListSceneOpenInvites :many
SELECT sqlc.embed(membership), sqlc.embed(record)
FROM membership
JOIN account ON
	account.did = membership.account_did AND
	account.upstream_status = 'active' AND
	account.status = 'active'
JOIN record ON
	record.account_did = membership.account_did AND
	record.collection = 'social.scrumble.beta.account.profile' AND
	record.rkey = 'self'
WHERE
	membership.scene_did = $1 AND
	membership.invited_at IS NOT NULL AND
	membership.joined_at IS NULL AND
	(sqlc.narg('cursor')::timestamptz IS NULL OR membership.invited_at > sqlc.narg('cursor')::timestamptz)
ORDER BY membership.invited_at ASC
LIMIT 30;

-- name: ListSceneMemberRequests :many
SELECT sqlc.embed(membership), sqlc.embed(record)
FROM membership
JOIN account ON
	account.did = membership.account_did AND
	account.upstream_status = 'active' AND
	account.status = 'active'
JOIN record ON
	record.account_did = membership.account_did AND
	record.collection = 'social.scrumble.beta.account.profile' AND
	record.rkey = 'self'
WHERE
	membership.scene_did = $1 AND
	membership.invited_at IS NULL AND
	membership.joined_at IS NOT NULL AND
	(sqlc.narg('cursor')::timestamptz IS NULL OR membership.joined_at > sqlc.narg('cursor')::timestamptz)
ORDER BY membership.joined_at ASC
LIMIT 30;

-- name: ListAccountScenes :many
SELECT sqlc.embed(membership), sqlc.embed(record)
FROM membership
JOIN account ON
	account.did = membership.scene_did AND
	account.upstream_status = 'active' AND
	account.status = 'active'
JOIN record ON
	record.account_did = membership.scene_did AND
	record.collection = 'social.scrumble.beta.scene.declaration' AND
	record.rkey = 'self'
WHERE
	membership.account_did = $1 AND
	membership.invited_at IS NOT NULL AND
	membership.joined_at IS NOT NULL AND
	(sqlc.narg('cursor')::timestamptz IS NULL OR membership.joined_at > sqlc.narg('cursor')::timestamptz)
ORDER BY membership.joined_at ASC
LIMIT 30;

-- ### OAuth Queries ###

-- name: GetOAuthSession :one
SELECT * FROM oauth_session WHERE
	account_did = $1 AND
	session_id = $2
LIMIT 1;

-- name: UpsertOAuthSession :exec
INSERT INTO oauth_session (account_did, session_id, data_json)
	VALUES ($1, $2, $3)
ON CONFLICT (account_did, session_id) DO UPDATE SET
	data_json = EXCLUDED.data_json,
	updated_at = now();

-- name: DeleteOAuthSession :exec
DELETE FROM oauth_session WHERE
	account_did = $1 AND
	session_id = $2;

-- name: GetOAuthAuthRequest :one
SELECT * FROM oauth_auth_request WHERE
	state = $1
LIMIT 1;

-- name: InsertOAuthAuthRequest :exec
INSERT INTO oauth_auth_request (state, data_json)
	VALUES ($1, $2);

-- name: DeleteOAuthAuthRequest :exec
DELETE FROM oauth_auth_request WHERE
	state = $1;