backend/backend.py

import json
import gspread
import pandas as pd
from datetime import datetime
from oauth2client.service_account import ServiceAccountCredentials


class Backend:
    def __init__(self, sheet_name: str, credentials: str):
        creds_dict = json.loads(credentials)
        scope = [
            "https://spreadsheets.google.com/feeds",
            "https://www.googleapis.com/auth/drive",
        ]
        credentials = ServiceAccountCredentials.from_json_keyfile_dict(
            creds_dict, scope
        )
        client = gspread.authorize(credentials)
        self.sheet = client.open(sheet_name).sheet1
        self.header = self.sheet.row_values(1)

    def get_all_rows(self) -> pd.DataFrame:
        records = self.sheet.get_all_records()
        return pd.DataFrame.from_records(records)

    def add_row(
        self,
        index_in_dataset,
        interpretation_id,
        transcription_id,
        user_id,
        answer,
        user_name,
        is_correct=None,
    ):
        timestamp = datetime.utcnow().isoformat()
        self.sheet.append_row(
            [
                index_in_dataset,
                interpretation_id,
                transcription_id,
                user_id,
                user_name,
                answer,
                is_correct if is_correct is not None else "",
                timestamp,
            ]
        )

    def update_row(
        self, index_in_dataset, interpretation_id, user_id, new_answer, is_correct=None
    ):
        records = self.get_all_rows().to_dict("records")
        for idx, row in enumerate(records):
            if (
                row["interpretation_id"] == interpretation_id
                and row["index_in_dataset"] == index_in_dataset
                and row["user_id"] == user_id
            ):
                # Update the is_correct field if provided
                sheet_row = (
                    idx + 2
                )  # +2 because sheet rows are 1-indexed and header is row 1
                if row["answer"] != new_answer:
                    self.sheet.update_cell(
                        sheet_row, self.header.index("answer") + 1, new_answer
                    )
                    self.sheet.update_cell(
                        sheet_row,
                        self.header.index("timestamp") + 1,
                        datetime.utcnow().isoformat(),
                    )
                    # update is_correct column
                    self.sheet.update_cell(
                        sheet_row,
                        self.header.index("is_correct") + 1,
                        is_correct if is_correct is not None else "",
                    )

                return True
        return False

    def get_answer_count(self, interpretation_id):
        df = self.get_all_rows()
        return df[df["interpretation_id"] == interpretation_id]["user_id"].nunique()