utils.py

import gspread
import pandas as pd
from google.oauth2 import service_account

# CREDENTIALS_INFO = {
#     "type": "service_account",
#     "project_id": "cru-ocr",
#     "private_key_id": "ee936d111292eb13521edf3d201eb85ca4391824",
#     "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvAIBADANBgkqhkiG9w0BAQEFAASCBKYwggSiAgEAAoIBAQCxFAxXwyy+VS3M\nVKu42MYGlpTc68vuA5ZxR4ZL3ukXrOtBKTCw6XwXx87dPvczAkxAcxgmUMCOPFJN\nRnt2bpOqGmOxgjIZ245LZXULgjMddLGiPR7mD4mejX/4zHfXZZpnmQrl6Ix4Y2/S\nhD2UbG+bub1qUPbYbycGnVf537tiZlP7OTNRo3S5Xsvacx8Tj8OUiGZhtkbnqqYy\nF9dgheJp0nPICZbTnDswCeSGQKVH65eVakwxAg3Aeeugqhjmoh+ornZwwSFd2UfV\nlo5UuBrxccKmM71p1a2eCudL5wqnwXkCfmoQrylyT5bNRANcEhYGjk6jJFaaKnkQ\nnjIFC2sHAgMBAAECggEANcsWWM7k18k+iXUrWZMYzUWPYXGMWPjkCfOle4TzIIsa\nSIg/z26OkRbU4+dN50QKcAXGz1T2uf7fLbR8qyS6XRF5OaKIn8xP9N2UafOanZcm\no1eX/GG5992ag7VxrpCiEFiws9kqWyQyAyzDHES4vwD05shDxMo3e83uvOzXmvNj\ngiTsdgVYQMzQt5RtsrH+bxKZ5DV7cyDzr2cINjUHziOvdwKEB7konw+rLNPTOlhK\ntK5dG9zN5E8CPnKraYC2tZB5NmJqFUDrq9P1YHWT6EdNsaHLlHHTG/pNrjklfZnl\nZJbwM23Y+3XAdlfKsXSPpskg+DPfxXL7cQTZVDsH+QKBgQDgmttFrHslaHl9pMUt\ny0DNRFHCkm3v5/bJ6lIC7F2MOtw2b48hpzjnvMsJbH6xxUWkQW4FYBpnzqa0YBXk\nwnH/eY+zq4FKQoMDhR75oPNBU6n5BPmTaB5wGjjI4MypjoC79iKaCZ6V6Px4+9ib\nOpoXUOty9LYTNTBB0Qube8BCjQKBgQDJ1IZswI+6LmHy/8zqQpciosBx6ITehQb6\n8X7u3K5mNz/SMxlS9C5YMTAKMefop9QPecaVC1XFiVnZou9LjfKVCeEr4/+1eSPF\nwFP5GJfT5WmWKHQtz0rZTFtSz1zwSAbz0buCIERNAOmF/xb26I3AC6mSy4tcxeHc\n+0pLBGwI4wKBgErWrotnrlzHk/uuhFj+6ae7xPZtLh6LDys2XX9F3OHV1vx4bZvM\nCWUF/i00rn5zegICHzPBUusV62wcvA7OT4fNrHk0g08IHHl2yNxqqcMxqmgkJTjd\nr46w3gzpAqjYp8J5gAwNen7+8+koGYOXojJ0rw9NxMFfrqWvjwuOz4AdAoGAe4PX\nXDif/NWj1d1b30UvTuABG/SrU65fbjVac/2TsTRAl3f0GIMc1ZYMi0CtZwFGUs44\njD/qlsAOv5TqEvfkq/bm2UBn3fwruzqPaVL2n5O3AVDygJJqgP8sqEoE23uI3a/N\nq73pbqKPRxSsTiBVl2DLvu1X9UeYiO80MSKcpvcCgYB295NMYiFwLtvaVk7kFhY3\nGuNTyuDD/sgMCsABDlJlG3KF2l4BWBO1BG1qHyRtmMrFjGHIV8BKDjjJLsT13REb\nVwFr+V0Jo+9f1yyjfpakrGTBO6eQJZhrJcGIkEVS3BVIC4pP0Hxt8UKlo/XtZbPN\n8n4ZEXdlalE56RzEsbuXHA==\n-----END PRIVATE KEY-----\n",
#     "client_email": "cru-ocr-service-account@cru-ocr.iam.gserviceaccount.com",
#     "client_id": "108232587703192834621",
#     "auth_uri": "https://accounts.google.com/o/oauth2/auth",
#     "token_uri": "https://oauth2.googleapis.com/token",
#     "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
#     "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/cru-ocr-service-account%40cru-ocr.iam.gserviceaccount.com",
#     "universe_domain": "googleapis.com",
# }

CREDENTIALS_INFO = {
    "type": "service_account",
    "project_id": "connect-card-scanner",
    "private_key_id": "54224814c69a155d8bd34128e83e373e0f1caa6f",
    "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQDNCRYK1FfJAVaK\nMxznDAr+SFZfqfD6m3MiAfKHJk83cY9JerNOOhc2TwaP8FhZdRs/+2k0FE84Rms2\nUoa6XMSC7JhlobaRFVjtVxljim+ake19i6iGk1kI6/BUYwwfrd1Emw1Dy1eP0rC0\nRoX8gfurF8HR4k4SuFnsSB62f7ttOiP6e0PFxDJxuRG68ua6gRRjWRZf49KEMz5T\nVnWRNzwnUAfd7XuK1MdXnKWG4KWUtLbYa5bv5n47mV3JLc8nwbDZ0redZNeSYf5L\nj0CD/swoNBm8YLzhPoNGPt/2c77Y9Gj5d9Fo3Pg5VuMQXgzpsyCnFPv/sEJi/JXZ\nHkH9JXnzAgMBAAECggEAYcH2R32MyWKg702Fd0fPqgO1YvE021foogtJplSGqomz\nZrZg7WhXDubI2zId/bEPKAdn1pPkXZF9pq3cXNjEPSQvWS2sTSpfdvHzQfmMUqdH\nE3fWBywT5GQR9zouWqBcAkznGy7FdeZfp+SEF6ul4aJ3H+oFjXlmLnkIY70tENsw\nOBs6Q+ffipJXhv5AQ0fGJWLaJ9cywRgyXODfX3mg38M2M53wx25CyN9kSN1ua1W3\nKhziHPCw2zqtRyTUWiA1vPYpN9mCQgz5TLXIRv+6nuwowlIVI/zvpUt9pWZtec2x\n4LYp63XIlYvl37qckh/yuj8DTkRem+ks3eApEVas2QKBgQDm6RZ5bxpr0qT8GdvY\nm8WvwQwHnnwco+WfJEsjlKkDg3Nw6y1aDtS7+DmMwSI5U2Md2tX4y+zqBYHOdVNf\nVjvSEokQKTd3qEeTiJTDGZ49OsaPhnaouqYPyFk1p/loAh0+31rMlAE6Kxi3USwK\n2EpNi0gMApLnAMsoqgD7o/lubQKBgQDjUEUKUJ2jgOOVFwK6DKy/xOx6BJl12vhY\n+JGGNFtKwmMl9GmYaGcXbmlF2NfzAxO4uxdl11U/7LBAvFGQZLGMDfOJy7xcy+95\nzEjpTuHTxB8lTed2ILIyJOwfJRAQO11VB2R6uHbwIJMJWTDwyIv5EG5a+/ZPqErV\nixg0NJXN3wKBgGG874JfALP66VK8L040QSzvbYQcFTSaOyttVVCuMAwIq+hz4zJn\nbKxTmSh252GUZjPQ2RkCWDmGMzeMecm02oVEyzdH+u5vEDzmZvFd+pi4NCu0Iq2w\ns3Giv//yJaNcobxnFivZydsxOrj9ZsMAYhMIjWpn/H5C27tOmjPpaD7RAoGARrjk\nog7u3L3vEKW2HXhwDsIP6O6haD+WYOgFLsH/XUUZX+epKtfgqzOY4ThUB7F/Y0wi\nPXc/eMIFHD77CXeqna6BhO+0TRLOERDz5lK6hA5SumKAjwohJuTB6fa4BrTRlvDT\n3DKkHpWj6ZasWV2r3vOzwe7+dU4g6kt6XlO1//UCgYEAgcp1IdttP9Yj/P+ZQ3Ld\nd2Ujwu9EVtc0bVOtfpLXWi27Zva2+M5oUof1vLpHhSbnCnmz2E4D4JzxHRsMxsZG\n/LIaLjAGMpPYxgK5CSo5FU5KC9ZL3nNjE/2JXq/Cx9Ua7q0S3vvJ8HUxncrFaA/d\nfOeHHOIZTbRFwo0zCggO16g=\n-----END PRIVATE KEY-----\n",
    "client_email": "connect-card-scanner@connect-card-scanner.iam.gserviceaccount.com",
    "client_id": "114871390457241513072",
    "auth_uri": "https://accounts.google.com/o/oauth2/auth",
    "token_uri": "https://oauth2.googleapis.com/token",
    "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
    "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/connect-card-scanner%40connect-card-scanner.iam.gserviceaccount.com",
    "universe_domain": "googleapis.com",
}

CREDENTIALS = service_account.Credentials.from_service_account_info(
    CREDENTIALS_INFO,
    scopes=[
        "https://www.googleapis.com/auth/cloud-platform",
        "https://www.googleapis.com/auth/spreadsheets",
        "https://www.googleapis.com/auth/drive",
    ],
)

PROJECT_ID = "connect-card-scanner"
LOCATION = "us"
PROCESSOR_ID = "30861bab1d979b83"

# 1kzkUCcgvuS5AQ04fnivru93-G1RZIZnjxwLrRZUMccM

ALL_FIELDS_COMBINED = [
    "Name",
    "Phone",
    "Email",
    "Cadet",
    "Greek or Going Greek",
    "Transfer Student",
    "Military Veteran",
    "International Student",
    "Res Hall",
    "Room #",
    "Off Campus",
    "Fr",
    "So",
    "Jr",
    "Sr",
    "Grad Student",
    "Male",
    "Female",
    "Non Binary",
    "Spiritual Survey Yes",
    "Spiritual Survey No",
    "Spiritual Survey Maybe",
    "Social Event Yes",
    "Social Event No",
    "Social Event Maybe",
    "Small Group Yes",
    "Small Group No",
    "Small Group Maybe",
]

SHEET_COLUMNS = [
    "Timestamp",
    "Name",
    "Gender",
    "Year",
    "Phone Number",
    "Email",
    "Do any of these describe you?",
    "Do you live...",  # Off campus / On campus
    "Which Res Hall are you in?",
    "What is your room number?",
    "Giving your opinion in a campus wide spiritual survey",
    "Social Events with Cru",
    "A small group Bible Study",
    "Getting our Cru weekly email",  # if three 'yeses', then add this
]


def convert_df_to_cleaned_format(df):
    """Convert dataframe to cleaned format"""
    # df = pd.DataFrame([list(dict_values)], columns=ALL_FIELDS_COMBINED)

    # Year processing
    year_map = {
        "Fr": "Freshman",
        "So": "Sophomore",
        "Jr": "Junior",
        "Sr": "Senior",
        "Grad Student": "Graduate Student",
    }
    df["Year"] = df.apply(
        lambda row: next(
            (
                year
                for year in [
                    "Fr",
                    "So",
                    "Jr",
                    "Sr",
                    "Grad Student",
                ]
                if row[year]
            ),
            "",
        ),
        axis=1,
    )
    df["Year"] = df["Year"].map(year_map)
    df.drop(
        columns=["Fr", "So", "Jr", "Sr", "Grad Student"],
        inplace=True,
    )

    # Add timestamp
    df["Timestamp"] = pd.Timestamp.now().strftime("%Y-%m-%d %H:%M:%S")

    # Combine Male and Female into Gender
    df["Gender"] = df.apply(
        lambda row: next(
            (gender for gender in ["Male", "Female", "Non Binary"] if row[gender]), ""
        ),
        axis=1,
    )
    df.drop(columns=["Male", "Female", "Non Binary"], inplace=True)

    # Combine Small Group Yes, No, Maybe into one column
    df["A small group Bible Study"] = df.apply(
        lambda row: "Yes"
        if row["Small Group Yes"]
        else (
            "No"
            if row["Small Group No"]
            else ("Maybe" if row["Small Group Maybe"] else "")
        ),
        axis=1,
    )
    df.drop(
        columns=["Small Group Yes", "Small Group No", "Small Group Maybe"], inplace=True
    )

    # Combine Social Event Yes, No, Maybe into one column
    df["Social Events with Cru"] = df.apply(
        lambda row: "Yes"
        if row["Social Event Yes"]
        else (
            "No"
            if row["Social Event No"]
            else ("Maybe" if row["Social Event Maybe"] else "")
        ),
        axis=1,
    )
    df.drop(
        columns=["Social Event Yes", "Social Event No", "Social Event Maybe"],
        inplace=True,
    )

    # Combine Spiritual Survey Yes, No, Maybe into one column
    df["Giving your opinion in a campus wide spiritual survey"] = df.apply(
        lambda row: "Yes"
        if row["Spiritual Survey Yes"]
        else (
            "No"
            if row["Spiritual Survey No"]
            else ("Maybe" if row["Spiritual Survey Maybe"] else "")
        ),
        axis=1,
    )
    df.drop(
        columns=[
            "Spiritual Survey Yes",
            "Spiritual Survey No",
            "Spiritual Survey Maybe",
        ],
        inplace=True,
    )

    df["Do any of these describe you?"] = df.apply(
        lambda row: ", ".join(
            [
                field
                for field in [
                    "Cadet",
                    "Greek or Going Greek",
                    "Transfer Student",
                    "Military Veteran",
                    "International Student",
                ]
                if row[field]
            ]
        ),
        axis=1,
    )
    df.drop(
        columns=[
            "Cadet",
            "Greek or Going Greek",
            "Transfer Student",
            "Military Veteran",
            "International Student",
        ],
        inplace=True,
    )

    # Res Hall processing
    res_hall_map = {
        "cochrange": "Cochrane",
        "cid": "Creativity & Innovation District",
        "creativity and innovation district": "Creativity & Innovation District",
        "creativity & innovation district": "Creativity & Innovation District",
        "east aj": "EAJ",
        "eaj": "EAJ",
        "east campbell": "East Campbell",
        "east egg": "East Eggleston",
        "east eggleston": "East Eggleston",
        "donaldson brown": "GLC",
        "graduate life center": "GLC",
        "graduate life center at donaldson brown": "GLC",
        "glc": "GLC",
        "harper": "Harper",
        "hoge": "Hoge",
        "hillcrest": "Hillcrest",
        "johnson": "Johnson",
        "johnson hall": "Johnson",
        "main campbell": "Main Campbell",
        "main egg": "Main Eggleston",
        "main eggleston": "Main Eggleston",
        "miles": "Miles",
        "new hall": "New Hall West",
        "new hall west": "New Hall West",
        "nhw": "New Hall West",
        "new res": "New Res East",
        "new res east": "New Res East",
        "nre": "New Res East",
        "shag": "OShag",
        "oshag": "OShag",
        "oshaughnessy": "OShag",
        "payne": "Payne",
        "pearson - east": "Pearson - East",
        "pearson-east": "Pearson - East",
        "pe": "Pearson - East",
        "ep": "Pearson - East",
        "phe": "Pearson - East",
        "pearson - west": "Pearson - West",
        "pearson-west": "Pearson - West",
        "pw": "Pearson - West",
        "wp": "Pearson - West",
        "phw": "Pearson - West",
        "py": "PY",
        "p-y": "PY",
        "peddrew-yates": "PY",
        "peddrew yates": "PY",
        "p.y.": "PY",
        "slusher tower": "Slusher Tower",
        "slusher": "Slusher Tower",
        "slusher wing": "Slusher Wing",
        "upper quad north": "Upper Quad North",
        "uqhn": "Upper Quad North",
        "uqn": "Upper Quad North",
        "vawter": "Vawter",
        "west aj": "WAJ",
        "waj": "WAJ",
        "west egg": "West Eggleston",
        "west eggleston": "West Eggleston",
        "whitehurst": "Whitehurst",
    }
    df["Which Res Hall are you in?"] = (
        df["Res Hall"]
        .str.lower()
        .str.strip()
        .str.replace("'", "")
        .replace(".", "")
        .replace("\n", " ")
        .map(res_hall_map)
        .fillna(df["Res Hall"])
    )
    df.drop(columns=["Res Hall"], inplace=True)

    df["Getting our Cru weekly email"] = df.apply(
        lambda row: "Yes"
        if all(
            row[field] == "Yes"
            for field in [
                "A small group Bible Study",
                "Social Events with Cru",
                "Giving your opinion in a campus wide spiritual survey",
            ]
        )
        else "No",
        axis=1,
    )

    df["Phone Number"] = df["Phone"][:10]  # keep only first 10 digits
    df.drop(columns=["Phone"], inplace=True)

    df["Do you live..."] = df.apply(
        lambda row: "On Campus" if not row["Off Campus"] else "Off Campus", axis=1
    )
    df.drop(columns=["Off Campus"], inplace=True)

    df["What is your room number?"] = df["Room #"]
    df.drop(columns=["Room #"], inplace=True)

    df = df.replace({"☐": "", None: ""})

    # reorder columns to match SHEET_COLUMNS
    df = df[SHEET_COLUMNS]

    return df


def upload_to_google_sheets(df):
    """Uploads the edited DataFrame to a Google Sheet by appending to existing data."""

    df = convert_df_to_cleaned_format(df)

    spreadsheet_name = "AI Scanning Hold"
    worksheet_name = "2025-2026"

    # Authenticate with Google Sheets
    gc = gspread.authorize(CREDENTIALS)

    # Open the Google Sheet
    try:
        spreadsheet = gc.open(spreadsheet_name)
    except gspread.SpreadsheetNotFound:
        spreadsheet = gc.create(spreadsheet_name)

    # Select the worksheet
    try:
        worksheet = spreadsheet.worksheet(worksheet_name)
    except gspread.WorksheetNotFound:
        worksheet = spreadsheet.add_worksheet(
            title=worksheet_name, rows="100", cols="20"
        )

    # Check if the worksheet is empty or has headers
    existing_data = worksheet.get_all_values()
    df_headers = df.columns.values.tolist()

    if not existing_data:
        # If worksheet is empty, add headers first, then all data rows
        all_data = [df_headers] + df.values.tolist()
        worksheet.update(all_data)
        return f"Data uploaded successfully to {spreadsheet_name} - {worksheet_name} (new sheet with headers). Added {len(df)} rows."
    else:
        # Check if headers exist and match
        existing_headers = existing_data[0]

        if existing_headers == df_headers:
            # Headers match exactly, append all data rows
            for _, row in df.iterrows():
                worksheet.append_row(row.tolist())
            return f"Data appended successfully to {spreadsheet_name} - {worksheet_name}. Added {len(df)} rows."

        elif len(existing_headers) == len(df_headers) and all(
            h.strip() for h in existing_headers
        ):
            # Sheet has headers but they don't match exactly
            # Create a mapping to ensure data goes to correct columns
            header_mapping = {}

            # Try to map columns by matching header names (case-insensitive, strip whitespace)
            for i, df_header in enumerate(df_headers):
                for j, existing_header in enumerate(existing_headers):
                    if df_header.strip().lower() == existing_header.strip().lower():
                        header_mapping[i] = j
                        break

            if len(header_mapping) == len(df_headers):
                # All columns can be mapped
                for _, row in df.iterrows():
                    # Reorder the row data to match existing column order
                    reordered_row = [""] * len(existing_headers)
                    for df_col_idx, existing_col_idx in header_mapping.items():
                        reordered_row[existing_col_idx] = row.iloc[df_col_idx]
                    worksheet.append_row(reordered_row)
                return f"Data appended successfully to {spreadsheet_name} - {worksheet_name}. Added {len(df)} rows (columns reordered to match existing headers)."
            else:
                # Cannot map all columns - add headers and data anyway but with warning
                # First add the new headers as a comment row or handle differently
                for _, row in df.iterrows():
                    worksheet.append_row(row.tolist())
                return f"Data appended to {spreadsheet_name} - {worksheet_name}. Added {len(df)} rows (WARNING: Column headers don't match existing headers)."

        else:
            # Sheet appears to be empty (no real headers) or has different number of columns
            # Treat as empty and add headers
            worksheet.clear()
            all_data = [df_headers] + df.values.tolist()
            worksheet.update(all_data)
            return f"Data uploaded successfully to {spreadsheet_name} - {worksheet_name} (replaced existing data with headers). Added {len(df)} rows."