async def create_poll(question: str, poll_type: str, scope: str, event_id: str):
db = main.get_db()
query = ("""
INSERT INTO poll (
id,
question,
type,
scope,
event_id,
created_at
)
VALUES (
uuid_generate_v4(),
:question,
:type,
:scope,
:event_id,
clock_timestamp()
)
RETURNING poll.id;
""")
values = {
'question': question,
'type': poll_type,
'scope': scope,
'event_id': event_id
}
return await db.execute(query, values)
async def create_group(name: str, description: str, group_img: str, accessibility: str):
db = main.get_db()
query = ("""
INSERT INTO "group" (
id,
name,
description,
group_img,
accessibility,
created_at
)
VALUES (
uuid_generate_v4(),
:name,
:description,
:group_img,
:accessibility,
clock_timestamp()
)
RETURNING id;
""")
values = {
'name': name,
'description': description,
'group_img': group_img,
'accessibility': accessibility
}
return await db.execute(query, values)
async def update_profile(profile_id: str, name: str, profile_img: str,
description: str, phone_number: str, user_id: str):
db = main.get_db()
query = ("""
UPDATE profile
SET name = :name,
profile_img = :profile_img,
description = :description,
phone_number = :phone_number,
user_id = :user_id
updated_at = clock_timestamp()
WHERE
id = :profile_id
AND deleted_at IS NULL
RETURNING
id AS profile_id,
name AS profile_name;
""")
values = {
'name': name,
'profile_img': profile_img,
'description': description,
'phone_number': phone_number,
'profile_id': profile_id,
'user_id': user_id
}
return await db.execute(query, values)
async def create_profile(name: str, profile_img: str, description: str,
phone_number: str, user_id: str):
db = main.get_db()
query = ("""
INSERT INTO profile (
id,
name,
profile_img,
description,
phone_number,
user_id,
created_at
)
VALUES (
uuid_generate_v4(),
:name,
:profile_img,
:description,
:phone_number,
:user_id,
clock_timestamp()
)
RETURNING profile.id;
""")
values = {
'name': name,
'profile_img': profile_img,
'description': description,
'phone_number': phone_number,
'user_id': user_id
}
return await db.execute(query, values)
async def create_user(username: str, password: str, email: str):
db = main.get_db()
query = ("""
INSERT INTO "user" (
id,
username,
password,
email,
created_at
)
VALUES (
uuid_generate_v4(),
:username,
:password,
:email,
clock_timestamp()
)
RETURNING id;
""")
values = {
'username': username,
'password': password,
'email': email
}
return await db.execute(query, values)
async def clear_selection_profiles(selection_id: str):
db = main.get_db()
query = ("""
DELETE FROM
profile_selection
WHERE
selection_id = :selection_id;
""")
values = {"selection_id": selection_id}
return await db.execute(query, values)
async def delete_profile(profile_id: str):
db = main.get_db()
query = ("""
UPDATE profile
SET deleted_at = clock_timestamp()
WHERE
id = :profile_id
RETURNING id;
""")
values = {'profile_id': profile_id}
return await db.execute(query, values)
async def delete_selection(selection_id: str):
db = main.get_db()
query = ("""
UPDATE selection
SET deleted_at = clock_timestamp()
WHERE
id = :selection_id
RETURNING selection.id;
""")
values = {'selection_id': selection_id}
return await db.execute(query, values)
async def delete_tag(tag_id: str):
db = main.get_db()
query = ("""
UPDATE tag
SET deleted_at = clock_timestamp()
WHERE
id = :tag_id
RETURNING tag.id;
""")
values = {'tag_id': tag_id}
return await db.execute(query, values)
async def clear_group_profiles(group_id: str):
db = main.get_db()
query = ("""
DELETE FROM
profile_group_role
WHERE
group_id = :group_id;
""")
values = {
"group_id": group_id
}
return await db.execute(query, values)
async def delete_group(group_id: str):
db = main.get_db()
query = ("""
UPDATE "group"
SET deleted_at = clock_timestamp()
WHERE
id = :group_id
RETURNING id;
""")
values = {
'group_id': group_id
}
return await db.execute(query, values)
async def delete_user(user_id: str):
db = main.get_db()
query = ("""
UPDATE "user"
SET deleted_at = clock_timestamp()
WHERE
id = :user_id
RETURNING id;
""")
values = {
'user_id': user_id
}
return await db.execute(query, values)
async def get_selections_by_poll(poll_id: str):
db = main.get_db()
query = ("""
SELECT
id,
name
FROM
selection
WHERE
poll_id = :poll_id
AND deleted_at IS NULL;
""")
values = {'poll_id': poll_id}
return await db.fetch_all(query, values)
async def get_selection_profiles(selection_id: str):
db = main.get_db()
query = ("""
SELECT
profile.id
FROM
profile_selection,
profile
WHERE
profile_selection.selection_id = :selection_id
AND profile_selection.profile_id = profile.id;
""")
values = {'selection_id': selection_id}
return await db.fetch_all(query, values)
async def get_refresh_token(name: str):
db = main.get_db()
query = ("""
SELECT
id,
name,
token
FROM
refresh_token
WHERE
name = :name;
""")
values = {'name': name}
return await db.fetch_one(query, values)
async def get_tag(tag_id: str):
db = main.get_db()
query = ("""
SELECT
id,
name
FROM
tag
WHERE
id = :tag_id
AND deleted_at IS NULL;
""")
values = {'tag_id': tag_id}
return await db.fetch_one(query, values)
async def update_refresh_token(token_id: str, name: str, token: str):
db = main.get_db()
query = ("""
UPDATE refresh_token
SET name = :name,
token = :token,
updated_at = clock_timestamp()
WHERE
id = :token_id
AND deleted_at IS NULL
RETURNING
id AS token_id;
""")
values = {'token_id': token_id, 'name': name, 'token': token}
return await db.execute(query, values)
async def get_selection(selection_id: str):
db = main.get_db()
query = ("""
SELECT
id,
name,
poll_id
FROM
selection
WHERE
id = :selection_id
AND deleted_at IS NULL;
""")
values = {'selection_id': selection_id}
return await db.fetch_one(query, values)
async def update_tag(tag_id: str, name: str):
db = main.get_db()
query = ("""
UPDATE tag
SET name = :name,
updated_at = clock_timestamp()
WHERE
id = :tag_id
AND deleted_at IS NULL
RETURNING
id AS tag_id,
name AS tag_name;
""")
values = {'name': name, 'tag_id': tag_id}
return await db.execute(query, values)
async def add_selection_profiles(selection_id: str, profiles: str):
db = main.get_db()
query = ("""
INSERT INTO profile_selection (id, selection_id, profile_id)
SELECT
uuid_generate_v4(),
:selection_id,
profile.id
FROM
profile
WHERE
id = ANY(:profiles);
""")
values = {"selection_id": selection_id, "profiles": profiles}
return await db.execute(query, values)
async def update_selection(selection_id: str, name: str, poll_id: str):
db = main.get_db()
query = ("""
UPDATE selection
SET name = :name,
poll_id = :poll_id,
updated_at = clock_timestamp()
WHERE
id = :selection_id
AND deleted_at IS NULL
RETURNING
id AS selection_id,
name AS selection_name,
poll_id AS poll_id;
""")
values = {'name': name, 'selection_id': selection_id, 'poll_id': poll_id}
return await db.execute(query, values)
async def create_tag(name: str):
db = main.get_db()
query = ("""
INSERT INTO tag (
id,
name,
created_at
)
VALUES (
uuid_generate_v4(),
:name,
clock_timestamp()
)
RETURNING tag.id;
""")
values = {'name': name}
return await db.execute(query, values)
async def get_profile(profile_id: str):
db = main.get_db()
query = ("""
SELECT
id,
name,
profile_img,
description,
phone_number
FROM
profile
WHERE
id = :profile_id
AND deleted_at IS NULL;
""")
values = {'profile_id': profile_id}
return await db.fetch_one(query, values)
async def get_user(user_id: str):
db = main.get_db()
query = ("""
SELECT
id,
username,
password,
email
FROM
"user"
WHERE
id = :user_id
AND deleted_at IS NULL;
""")
values = {
'user_id': user_id
}
return await db.fetch_one(query, values)
async def get_poll(poll_id: str):
db = main.get_db()
query = ("""
SELECT
id,
question,
type,
scope
FROM
poll
WHERE
id = :poll_id
AND deleted_at IS NULL;
""")
values = {
'poll_id': poll_id
}
return await db.fetch_one(query, values)
async def get_polls_by_event(event_id: str):
db = main.get_db()
query = ("""
SELECT
id,
question,
type,
scope
FROM
poll
WHERE
event_id = :event_id
AND deleted_at IS NULL;
""")
values = {
'event_id': event_id
}
return await db.fetch_all(query, values)
async def get_group(group_id: str):
db = main.get_db()
query = ("""
SELECT
id,
name,
description,
group_img,
accessibility
FROM
"group"
WHERE
id = :group_id
AND deleted_at IS NULL;
""")
values = {
'group_id': group_id
}
return await db.fetch_one(query, values)
async def create_selection(name: str, poll_id: str):
db = main.get_db()
query = ("""
INSERT INTO selection (
id,
name,
poll_id,
created_at
)
VALUES (
uuid_generate_v4(),
:name,
:poll_id,
clock_timestamp()
)
RETURNING selection.id;
""")
values = {'name': name, 'poll_id': poll_id}
return await db.execute(query, values)
async def create_refresh_token(name: str, token: str):
db = main.get_db()
query = ("""
INSERT INTO refresh_token (
id,
name,
token,
created_at
)
VALUES (
uuid_generate_v4(),
:name,
:token,
clock_timestamp()
)
RETURNING refresh_token.id;
""")
values = {'name': name, 'token': token}
return await db.execute(query, values)
async def add_group_profile(group_id: str, profile_id: str, role: str):
db = main.get_db()
query = ("""
INSERT INTO profile_group_role (id, profile_id, group_id, role)
SELECT
uuid_generate_v4(),
profile.id,
:group_id,
:role
FROM
profile
WHERE
id = :profile_id;
""")
values = {
"group_id": group_id,
"profile_id": profile_id,
"role": role
}
return await db.execute(query, values)
