1 files changed, 39 insertions, 0 deletions
diff --git a/src/db.py b/src/db.py
index 9e1619a..b971a13 100644
--- a/src/db.py
+++ b/src/db.py
@@ -31,10 +31,22 @@ async def init_db():
                 message_id TEXT NOT NULL,
                 channel_id TEXT NOT NULL,
                 sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+                voting_message_ts TEXT,
                 FOREIGN KEY (deployment_id) REFERENCES Deployments(id) ON DELETE CASCADE,
                 FOREIGN KEY (user_id) REFERENCES Users(slack_id)
              )
          """)
+        await db.execute("""
+             CREATE TABLE IF NOT EXISTS Votes (
+                deployment_id INTEGER NOT NULL,
+                voter_id TEXT NOT NULL,
+                vote TEXT NOT NULL,
+                voted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+                FOREIGN KEY (deployment_id) REFERENCES Deployments(id) ON DELETE CASCADE,
+                FOREIGN KEY (voter_id) REFERENCES Users(slack_id)
+                PRIMARY KEY (deployment_id, voter_id)
+             )
+         """)
         await db.commit()
 
 async def add_user(slack_id: str, oauth_token: str):
@@ -129,3 +141,30 @@ async def get_active_users() -> list[dict]:
         cursor = await db.execute("SELECT * FROM Users")
         rows = await cursor.fetchall()
         return [dict(row) for row in rows]
+
+
+async def add_vote(deployment_id: int, voter_id: str, vote: str):
+    async with aiosqlite.connect(DB_PATH) as db:
+        await db.execute("INSERT OR REPLACE INTO Votes (deployment_id, voter_id, vote) VALUES (?, ?, ?)", (deployment_id, voter_id, vote))
+        await db.commit()
+
+async def get_votes(deployment_id: int):
+    async with aiosqlite.connect(DB_PATH) as db:
+        db.row_factory = aiosqlite.Row
+        cursor = await db.execute("SELECT voter_id, vote FROM Votes WHERE deployment_id = ?", (deployment_id,))
+        rows = await cursor.fetchall()
+        
+        votes = {"approve": [], "reject": []}
+        for row in rows:
+            votes[row["vote"]].append(row["voter_id"])
+        return votes
+
+async def get_user_vote(deployment_id: int, voter_id: str):
+    async with aiosqlite.connect(DB_PATH) as db:
+        cursor = await db.execute("SELECT vote from Votes WHERE deployment_id = ? AND voter_id = ?", (deployment_id, voter_id))
+        row = await cursor.fetchone()
+        return row[0] if row else None
+
+async def get_vote_counts(deployment_id: int):
+    votes = await get_votes(deployment_id)
+    return len(votes["approve"]), len(votes["reject"])