#!/usr/bin/env python3
"""Create Google Sheet with Roncesvalles rental listings."""
import subprocess, json, sys

SHEET_TITLE = "Roncesvalles Rentals Tracker"

# Create the spreadsheet
result = subprocess.run(
    ["gog", "sheets", "create", SHEET_TITLE, "--account", "camwest@gmail.com", "--json"],
    capture_output=True, text=True
)
print(result.stdout)
print(result.stderr, file=sys.stderr)

# Parse spreadsheet ID
data = json.loads(result.stdout)
sheet_id = data.get("spreadsheetId", data.get("id", ""))
print(f"Sheet ID: {sheet_id}", file=sys.stderr)

# Headers
headers = [
    "MLS#", "Address", "Price", "Beds", "Bath", "Sqft", 
    "Age", "Days Listed", "Type", "Status", "In Geofence",
    "Listing URL", "Last Seen", "Notes"
]

# Current listings (from Zolo scrape)
listings = [
    ["W12946350", "205-430 Roncesvalles Ave", "2350", "1", "1", "500-599", "", "1", "Apartment", "Active", "YES", "https://www.zolo.ca/toronto-real-estate/430-roncesvalles-avenue/205", "2026-04-02", ""],
    ["W12943452", "2-63A Garden Ave", "2900", "3+1", "1", "700-1100", "", "1", "Townhouse", "Active", "YES", "https://www.zolo.ca/toronto-real-estate/63a-garden-avenue/2", "2026-04-02", ""],
    ["W12943448", "BSMT-63 Garden Ave", "2300", "3+1", "1", "<700", "", "1", "Townhouse", "Active", "YES", "https://www.zolo.ca/toronto-real-estate/63-garden-avenue/bsmt", "2026-04-02", "Basement"],
    ["W12938186", "Laneway-168 Pearson Ave", "5750", "3", "3", "1100-1500", "", "2", "House", "Active", "YES", "https://www.zolo.ca/toronto-real-estate/168-pearson-avenue/laneway-ho", "2026-04-02", "Over budget"],
    ["W12938180", "Lower-168 Pearson Ave", "2400", "2", "1", "700-1100", "New", "2", "Townhouse", "Active", "YES", "https://www.zolo.ca/toronto-real-estate/168-pearson-avenue/lower-unit", "2026-04-02", "New build"],
    ["W12921700", "101-2076 Dundas W", "2200", "1", "1", "<700", "100+", "7", "Home", "Active", "YES", "https://www.zolo.ca/toronto-real-estate/2076-dundas-street-west/101", "2026-04-02", ""],
    ["W12921260", "210-24 Noble St", "2100", "–", "1", "500-599", "100+", "7", "Apartment", "Active", "EDGE", "https://www.zolo.ca/toronto-real-estate/24-noble-street/210", "2026-04-02", "Near Sorauren boundary"],
    ["W12920122", "1D-1540 King W", "2895", "2", "2", "800-899", "0-5", "7", "Apartment", "Active", "NO", "https://www.zolo.ca/toronto-real-estate/1540-king-street-west/1d", "2026-04-02", "South of Queen"],
    ["W12912404", "2nd Floor-1658 Queen W", "2600", "2", "1", "700-1100", "", "9", "Home", "Active", "YES", "https://www.zolo.ca/toronto-real-estate/1658-queen-street-west/2nd-flr", "2026-04-02", ""],
    ["W12903536", "Main#1-2011 Dundas W", "2300", "2", "1", "700-1100", "", "13", "House", "Active", "YES", "https://www.zolo.ca/toronto-real-estate/2011-dundas-street-west/main1", "2026-04-02", ""],
    ["W12903436", "BSMT-7 Harvard Ave", "1700", "2", "1", "<700", "", "13", "House", "Active", "YES", "https://www.zolo.ca/toronto-real-estate/7-harvard-avenue/bsmt", "2026-04-02", "Under budget, basement"],
    ["W12889838", "201-34 Noble St", "3045", "2", "1", "700-1100", "", "16", "Townhouse", "Active", "EDGE", "https://www.zolo.ca/toronto-real-estate/34-noble-street/201", "2026-04-02", "Near Sorauren boundary"],
    ["W12880208", "BSMT-95 Macdonell Ave", "1500", "1", "1", "2000-2500", "", "20", "House", "Active", "YES", "https://www.zolo.ca/toronto-real-estate/95-macdonell-avenue/bsmt", "2026-04-02", "Under budget, basement"],
    ["W12863066", "Main-1706 Queen W", "2899", "–", "–", "1250", "", "23", "Property", "Active", "YES", "https://www.zolo.ca/toronto-real-estate/1706-queen-street-west/main", "2026-04-02", "Commercial?"],
    ["W12836624", "2nd Floor-1454 Queen W", "2000", "1+1", "1", "<700", "", "30", "Home", "Active", "NO", "https://www.zolo.ca/toronto-real-estate/1454-queen-street-west/2nd-floor", "2026-04-02", "East of Sorauren"],
    ["W12829006", "3B-411 Roncesvalles Ave", "2500", "2", "1", "<700", "", "32", "Townhouse", "Active", "YES", "https://www.zolo.ca/toronto-real-estate/411-roncesvalles-avenue/3b", "2026-04-02", ""],
    ["W12825510", "B2-60 Brock Ave", "1699", "3", "1", "<700", "", "33", "House", "Active", "NO", "https://www.zolo.ca/toronto-real-estate/60-brock-avenue/b2", "2026-04-02", "East of Sorauren"],
    ["W12825502", "Basement-147 Lansdowne Ave", "2000", "2", "1", "1100-1500", "", "33", "Townhouse", "Active", "NO", "https://www.zolo.ca/toronto-real-estate/147-lansdowne-avenue/basement", "2026-04-02", "East of Sorauren"],
    ["W12811174", "Main-459 Roncesvalles Ave", "3800", "–", "2", "805", "", "26", "Property", "Reduced", "YES", "https://www.zolo.ca/toronto-real-estate/459-roncesvalles-avenue/main-floor", "2026-04-02", "Over budget, reduced from higher"],
    ["W12782386", "C-22 Lansdowne Ave", "3050", "2", "2", "700-1100", "", "48", "Townhouse", "Reduced", "NO", "https://www.zolo.ca/toronto-real-estate/22-lansdowne-avenue/c", "2026-04-02", "East of Sorauren"],
    ["W12763310", "Lower-2132 Dundas St", "1700", "1", "1", "<700", "", "54", "Home", "Active", "YES", "https://www.zolo.ca/toronto-real-estate/2132-dundas-street/lower", "2026-04-02", "Under budget"],
    ["W12738744", "3-70 Lansdowne Ave", "1950", "1", "1", "2500-3000", "", "64", "Townhouse", "Reduced", "NO", "https://www.zolo.ca/toronto-real-estate/70-lansdowne-avenue/3", "2026-04-02", "East of Sorauren"],
    ["W12689980", "Main-263 Dunn Ave", "3500", "–", "1", "1200", "", "80", "Home", "Active", "YES", "https://www.zolo.ca/toronto-real-estate/263-dunn-avenue/main", "2026-04-02", ""],
    ["W12649258", "B-1558 Queen W", "2300", "2", "1", "700-1100", "", "99", "Property", "Reduced", "YES", "https://www.zolo.ca/toronto-real-estate/1558-queen-street-west/b", "2026-04-02", "Long listed, reduced"],
    ["W12644916", "7-5 Sorauren Ave", "2750", "2", "2", "<700", "", "102", "Townhouse", "Reduced", "EDGE", "https://www.zolo.ca/toronto-real-estate/5-sorauren-avenue/7", "2026-04-02", "On east boundary, 102 days"],
    ["W12644898", "6-5 Sorauren Ave", "1850", "2", "1", "<700", "", "102", "Townhouse", "Reduced", "EDGE", "https://www.zolo.ca/toronto-real-estate/5-sorauren-avenue/6", "2026-04-02", "On east boundary, 102 days"],
    ["W12627854", "201-1496 Queen W", "1995", "1", "1", "<700", "", "111", "Townhouse", "Active", "NO", "https://www.zolo.ca/toronto-real-estate/1496-queen-street-west/201", "2026-04-02", "East of Sorauren"],
    ["W12946990", "403-1660 Bloor W", "3300", "2", "2", "700-799", "New", "0", "Condo Apt", "Active", "YES", "https://app.realmmlp.ca/dAD6w66ka", "2026-04-02", "WestBend. Parking+EV+locker+internet incl. SHOWING Apr 3"],
]

# Write headers
header_range = f"Sheet1!A1:{chr(65+len(headers)-1)}1"
header_data = json.dumps({"values": [headers]})

subprocess.run([
    "gog", "sheets", "update", sheet_id, header_range,
    "--account", "camwest@gmail.com",
    "--value-input-option", "USER_ENTERED",
    "--body", header_data
], capture_output=True, text=True)

# Write data
end_col = chr(65+len(headers)-1)
data_range = f"Sheet1!A2:{end_col}{len(listings)+1}"
data_body = json.dumps({"values": listings})

result = subprocess.run([
    "gog", "sheets", "update", sheet_id, data_range,
    "--account", "camwest@gmail.com",
    "--value-input-option", "USER_ENTERED",
    "--body", data_body
], capture_output=True, text=True)
print(result.stdout)
print(result.stderr, file=sys.stderr)

print(f"\nhttps://docs.google.com/spreadsheets/d/{sheet_id}")