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)