from .errors import invalid def create_user(cur, user_id, username, display_name, password_hash): cur.execute( "INSERT INTO users (id, username, display_name, password_hash) " "VALUES (%s, %s, %s, %s) RETURNING id, username, display_name, created_at", (user_id, username, display_name, password_hash), ) return cur.fetchone() def get_user_by_username(cur, username): cur.execute( "SELECT id, username, display_name, password_hash, created_at FROM users WHERE username = %s", (username,), ) return cur.fetchone() def get_user_by_id(cur, user_id): cur.execute( "SELECT id, username, display_name, created_at FROM users WHERE id = %s", (user_id,), ) return cur.fetchone() def create_session(cur, fingerprint, user_id, expires_at): cur.execute( "INSERT INTO sessions (token_hash, user_id, expires_at) VALUES (%s, %s, %s)", (fingerprint, user_id, expires_at), ) def get_valid_session(cur, fingerprint): cur.execute( "SELECT user_id, expires_at FROM sessions " "WHERE token_hash = %s AND revoked_at IS NULL AND expires_at > now()", (fingerprint,), ) return cur.fetchone() def revoke_session(cur, fingerprint): cur.execute( "UPDATE sessions SET revoked_at = now() WHERE token_hash = %s AND revoked_at IS NULL", (fingerprint,), ) return cur.rowcount def is_member(cur, chat_id, user_id): cur.execute( "SELECT 1 FROM chat_members WHERE chat_id = %s AND user_id = %s", (chat_id, user_id), ) return cur.fetchone() is not None def create_chat(cur, chat_id, title, member_ids): cur.execute( "SELECT id FROM users WHERE id = ANY(%s::uuid[])", (list(member_ids),), ) found = {str(row["id"]) for row in cur.fetchall()} if found != set(member_ids): raise invalid("Неизвестные участники чата") cur.execute( "INSERT INTO chats (id, title) VALUES (%s, %s) RETURNING id, title, created_at", (chat_id, title), ) row = cur.fetchone() cur.executemany( "INSERT INTO chat_members (chat_id, user_id) VALUES (%s, %s)", [(chat_id, member) for member in member_ids], ) return row def get_chat(cur, chat_id): cur.execute("SELECT id, title, created_at FROM chats WHERE id = %s", (chat_id,)) return cur.fetchone() def chat_members(cur, chat_ids): if not chat_ids: return {} cur.execute( "SELECT chat_id, user_id FROM chat_members WHERE chat_id = ANY(%s::uuid[]) " "ORDER BY chat_id, user_id", (list(chat_ids),), ) result = {} for row in cur.fetchall(): result.setdefault(str(row["chat_id"]), []).append(str(row["user_id"])) return result def list_chats(cur, user_id, after, limit): sql = ( "SELECT c.id, c.title, c.created_at FROM chats c " "JOIN chat_members m ON m.chat_id = c.id WHERE m.user_id = %s" ) params = [user_id] if after is not None: sql += " AND (c.created_at, c.id) > (%s::timestamptz, %s::uuid)" params.extend([after[0], after[1]]) sql += " ORDER BY c.created_at ASC, c.id ASC LIMIT %s" params.append(limit) cur.execute(sql, params) return cur.fetchall() def create_message(cur, message_id, chat_id, sender_id, text): cur.execute( "INSERT INTO messages (id, chat_id, sender_id, text) VALUES (%s, %s, %s, %s) " "RETURNING id, chat_id, sender_id, text, created_at", (message_id, chat_id, sender_id, text), ) return cur.fetchone() def list_messages(cur, chat_id, after, limit): sql = ( "SELECT id, chat_id, sender_id, text, created_at FROM messages WHERE chat_id = %s" ) params = [chat_id] if after is not None: sql += " AND (created_at, id) > (%s::timestamptz, %s::uuid)" params.extend([after[0], after[1]]) sql += " ORDER BY created_at ASC, id ASC LIMIT %s" params.append(limit) cur.execute(sql, params) return cur.fetchall()