import gspread
from schemas.simulation_case import SimulationCase
from typing import List
from google.oauth2.service_account import Credentials

SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']
KEYFILE_PATH = 'secrets/walkai-datapipeline-162431057be9.json'

def load_scenarios_from_sheet(sheet_url: str, tab_name: str = 'All Output') -> List[SimulationCase]:
    creds = Credentials.from_service_account_file(KEYFILE_PATH, scopes=SCOPES)
    client = gspread.authorize(creds)

    sheet = client.open_by_url(sheet_url)
    worksheet = sheet.worksheet(tab_name)
    rows = worksheet.get_all_records()

    cases = []
    for i, row in enumerate(rows):
        try:
            case = SimulationCase(
                persona_id=row["Persona ID"],
                module_id=row["Module ID"],
                prompt=row["Module Prompt"],
                emotion_before=row["Emotion Before"],
                desired_ai=row.get("Was AI Desired", ""),
                ai_behavior=row.get("Suggested AI Behavior", ""),
                tone=row.get("AI Tone", "")
            )
            cases.append(case)
        except Exception as e:
            print(f"[ERROR] Row {i+2} failed to validate: {e}")
            continue

    return cases