import pandas as pd
from gspread import authorize
from google.oauth2.credentials import Credentials
import json
import re

# Load credentials
creds_data = json.load(open('credentials.json'))
token_data = json.load(open('token.json'))
creds = Credentials(
    token_data['token'],
    refresh_token=token_data.get('refresh_token'),
    token_uri=token_data['token_uri'],
    client_id=creds_data['installed']['client_id'],
    client_secret=creds_data['installed']['client_secret']
)

gc = authorize(creds)
sh = gc.open_by_key('1ayEGU0h_R7CY55COC1U94-p0rJch109YBGvezjYjHWw')
ws = sh.worksheet('BASE')
data = ws.get_all_records()
df = pd.DataFrame(data)

# Filter urnas
familia_col = 'Familia'
referencia_col = 'Referencia'
urnas_data = df[df[familia_col].astype(str).str.lower().str.contains('urna', na=False)]

print("Sample references from urnas data:")
sample_refs = urnas_data[referencia_col].value_counts().head(30)
for ref, count in sample_refs.items():
    print(f"  '{ref}' : {count}")

print("\nChecking for C122, 122, C124, 124, C126, 126:")
for target in ['C122', '122', 'C124', '124', 'C126', '126']:
    matches = urnas_data[urnas_data[referencia_col].astype(str).str.strip() == target]
    print(f"  {target}: {len(matches)} rows")