#!/usr/bin/env python3
"""
Syncs activity_log.json → Google Sheet.
First run: opens browser for one-time Google auth. Token saved to .google_token.json.
"""

import json
from datetime import datetime
from pathlib import Path

import gspread
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request

BASE         = Path(__file__).parent
LOG_FILE     = BASE / "activity_log.json"
TOKEN_FILE   = BASE / ".google_token.json"
CREDS_FILE   = BASE / ".google_oauth_creds.json"
SHEET_ID     = "1zmzSweC0NY6gOqUW53HJIQePxyW6-nMhVzfXzcI_lJs"
SCOPES       = ["https://www.googleapis.com/auth/spreadsheets"]
TODAY        = datetime.now().strftime("%d%b%y")  # e.g. 25Apr25

# ── Auth ──────────────────────────────────────────────────────────────────────
def get_creds():
    creds = None
    if TOKEN_FILE.exists():
        creds = Credentials.from_authorized_user_file(str(TOKEN_FILE), SCOPES)
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            if not CREDS_FILE.exists():
                print("""
ERROR: no OAuth credentials file found.

To set up Google Sheets access (one time only):
1. Go to https://console.cloud.google.com/
2. Create project → Enable Google Sheets API
3. Create OAuth 2.0 credentials (Desktop app)
4. Download JSON → save as: ~/keith-dm-scanner/.google_oauth_creds.json
5. Run this script again

""")
                return None
            flow = InstalledAppFlow.from_client_secrets_file(str(CREDS_FILE), SCOPES)
            creds = flow.run_local_server(port=0)
        with open(TOKEN_FILE, "w") as f:
            f.write(creds.to_json())
    return creds

creds = get_creds()
if not creds:
    exit(1)

# ── Load activity log ─────────────────────────────────────────────────────────
with open(LOG_FILE) as f:
    log = json.load(f)

logged_handles = {e["handle"].lower().lstrip("@") for e in log}
print(f"activity log: {len(logged_handles)} unique handles")

# ── Open sheet ────────────────────────────────────────────────────────────────
gc      = gspread.authorize(creds)
sheet   = gc.open_by_key(SHEET_ID).sheet1
data    = sheet.get_all_values()
headers = data[0]  # first row

# find column indices
def col(name):
    try: return headers.index(name)
    except: return None

handle_col     = col("Contact Info")
reached_col    = col("Reached out")
followed_col   = col("Followed up")
note_col       = col("note")

print(f"columns: handle={handle_col}, reached={reached_col}, followed={followed_col}, note={note_col}")

# ── Match and update ──────────────────────────────────────────────────────────
updates    = []
not_found  = []

for row_idx, row in enumerate(data[1:], start=2):  # 1-indexed, skip header
    if not row or len(row) <= (handle_col or 0):
        continue
    cell_handle = row[handle_col].strip().lstrip("@").lower() if handle_col is not None else ""
    if cell_handle in logged_handles:
        # mark followed up + add date
        if followed_col is not None:
            updates.append({"range": f"{chr(65+followed_col)}{row_idx}", "values": [["TRUE"]]})
        if note_col is not None:
            existing_note = row[note_col].strip() if len(row) > note_col else ""
            new_note = f"{existing_note}+{TODAY}" if existing_note else TODAY
            updates.append({"range": f"{chr(65+note_col)}{row_idx}", "values": [[new_note]]})
        updates.append({"range": f"{chr(65+(reached_col or 0))}{row_idx}", "values": [["TRUE"]]})

if updates:
    sheet.spreadsheet.values_batch_update({
        "valueInputOption": "RAW",
        "data": updates
    })
    print(f"\nupdated {len(updates)//3} rows in Google Sheet ✓")
else:
    print("no matching rows found in sheet")

# report what wasn't in the sheet
sheet_handles = {row[handle_col].strip().lstrip("@").lower() for row in data[1:] if len(row) > (handle_col or 0) and row[handle_col].startswith("@")}
not_in_sheet = logged_handles - sheet_handles
if not_in_sheet:
    print(f"\n{len(not_in_sheet)} logged contacts NOT in sheet (new contacts):")
    for h in sorted(not_in_sheet)[:20]:
        print(f"  @{h}")