#!/usr/bin/env python3
"""
Add 'Directories' sheet to master Google Sheet using gog CLI
Track grower associations for future outreach
"""

import json
import subprocess
import sys

SPREADSHEET_ID = '1uVd-xZFF4TEQGqtvw9z6W8fffeaifPCoLsek83GmEoQ'
ACCOUNT = 'jonny@jonnyshannon.com'

def main():
    # Header row
    headers = [
        'Association Name',
        'Country/Region',
        'URL',
        'Member Count',
        'Type',
        'Priority',
        'Directory Access',
        'Cost',
        'Status',
        'Contact Email',
        'Notes',
        'Date Added',
        'Last Updated',
        'Email Drafted',
        'Response'
    ]
    
    # Data rows (from GROWER-ASSOCIATIONS-DIRECTORY.md)
    data = [
        headers,
        # APPLE & PEAR - USA
        ['US Apple Association (USApple)', 'USA', 'https://usapple.org/', '27,463 growers', 'Apple/Pear', '⭐⭐⭐⭐⭐', 'Request Required', '$0-2,000/year', 'Email Drafted', '', 'National advocacy org, 40 states', '2026-02-11', '', 'Yes', ''],
        ['Midwest Apple Improvement Association (MAIA)', 'USA (Midwest)', 'https://maiaapples.com/member-directory/', '1,100+', 'Apple', '⭐⭐⭐⭐⭐', 'Public (Searchable)', 'Free', 'Scraped (Sample)', '', 'Public directory - 20 scraped, 1,100+ total', '2026-02-11', '2026-02-11', 'No', ''],
        ['Wisconsin Apple Growers Association (WAGA)', 'USA (Wisconsin)', 'https://www.waga.org/', '50+', 'Apple', '⭐⭐⭐', 'Public (Map)', 'Free', 'Not Started', '', 'Member orchard map/list', '2026-02-11', '', 'No', ''],
        ['Washington State Fruit Commission', 'USA (Washington)', 'TBD', 'Unknown', 'Apple/Fruit', '⭐⭐⭐⭐⭐', 'Unknown', 'Unknown', 'Not Started', '', 'Largest apple state - need research', '2026-02-11', '', 'No', ''],
        
        # APPLE & PEAR - AUSTRALIA
        ['Apple and Pear Australia Limited (APAL)', 'Australia', 'https://apal.org.au/', 'National', 'Apple/Pear', '⭐⭐⭐⭐⭐', 'PDF (Blocked)', 'Free', 'Blocked', '', 'Exporter directory PDF exists, need workaround', '2026-02-11', '2026-02-11', 'No', ''],
        
        # APPLE & PEAR - NEW ZEALAND
        ['NZ Apples & Pears Inc (NZAPI)', 'New Zealand', 'https://www.applesandpears.nz/', 'Unknown', 'Apple/Pear/Coolstores', '⭐⭐⭐⭐⭐', 'Request Required', '$0-1,500/year', 'Email Drafted', '', 'Includes coolstore operators!', '2026-02-11', '', 'Yes', ''],
        ["Hawke's Bay Fruitgrowers' Association (HBFA)", 'New Zealand', 'https://hbfa.co.nz/', 'Unknown', 'Pipfruit/Kiwi/Summerfruit', '⭐⭐⭐⭐', 'Unknown', 'Unknown', 'Not Started', '', 'Largest independent NZ fruit association', '2026-02-11', '', 'No', ''],
        ['United Fresh NZ', 'New Zealand', 'https://unitedfresh.co.nz/membership/current-members', '90+', 'Multi-Fruit', '⭐⭐⭐⭐', 'Public (List)', 'Free', 'Scraped', '', 'Umbrella org - 90 members scraped', '2026-02-11', '2026-02-11', 'No', ''],
        
        # APPLE & PEAR - UK
        ['British Apples & Pears Limited (BAPL)', 'United Kingdom', 'https://www.britishapplesandpears.co.uk/', 'All UK Commercial', 'Apple/Pear', '⭐⭐⭐⭐⭐', 'Request Required', '$0-1,000/year', 'Email Drafted', '', 'Peak UK body, all commercial growers', '2026-02-11', '', 'Yes', ''],
        ['British Independent Fruit Growers Assoc (BIFGA)', 'United Kingdom', 'https://www.bifga.org.uk/trade-directory', '50+', 'Apple/Pear', '⭐⭐⭐⭐', 'Public (Trade)', 'Free', 'Scraped', '', 'Trade directory (suppliers) scraped - 30 companies', '2026-02-11', '2026-02-11', 'No', ''],
        ['British Growers Association', 'United Kingdom', 'https://britishgrowers.org/our-members/', 'Unknown', 'Multi-Fruit', '⭐⭐⭐', 'Public (List)', 'Free', 'Not Started', '', 'Broader scope, includes apple/berry/stone', '2026-02-11', '', 'No', ''],
        
        # APPLE & PEAR - CANADA
        ['Ontario Apple Growers (OAG)', 'Canada (Ontario)', 'https://www.onapples.com/', '200', 'Apple', '⭐⭐⭐⭐', 'Public (Profiles)', 'Free', 'Email Drafted', '', 'Meet the Growers profiles scraped - 20 growers', '2026-02-11', '2026-02-11', 'Yes', ''],
        ['BC Fruit Growers Association (BCFGA)', 'Canada (BC)', 'https://www.bcfga.com/', 'Unknown', 'Apple/Pear', '⭐⭐⭐⭐', 'Unknown', 'Unknown', 'Not Started', '', 'British Columbia apples/pears', '2026-02-11', '', 'No', ''],
        ['Fruit & Vegetable Growers of Canada (FVGC)', 'Canada', 'https://fvgc.ca/membership-list/', 'Unknown', 'Multi-Fruit', '⭐⭐⭐⭐', 'Public (List)', 'Free', 'Not Started', '', 'National umbrella organization', '2026-02-11', '', 'No', ''],
        
        # APPLE & PEAR - SOUTH AFRICA
        ['Hortgro Pome (formerly SAAPPA)', 'South Africa', 'https://www.hortgro.co.za/members/hortgro-pome/', 'National', 'Apple/Pear', '⭐⭐⭐⭐', 'Unknown', 'Unknown', 'Not Started', '', 'SA apple/pear growers association', '2026-02-11', '', 'No', ''],
        
        # APPLE & PEAR - GLOBAL
        ['World Apple and Pear Association (WAPA)', 'International', 'http://wapa-association.org/asp/page_members.asp', '30+', 'Apple/Pear', '⭐⭐⭐⭐⭐', 'Public (Page)', 'Free', 'Not Started', '', 'Global members from all major regions', '2026-02-11', '', 'No', ''],
        
        # CITRUS - USA
        ['California Citrus Mutual', 'USA (California)', 'https://www.cacitrusmutual.com/about-us/associate-members/', 'Unknown', 'Citrus', '⭐⭐⭐⭐⭐', 'Public (Associates)', 'Free', 'Scraped', '', 'Associate member directory scraped - 12 suppliers', '2026-02-11', '2026-02-11', 'No', ''],
        ['California Citrus Growers Assoc (CCGA)', 'USA (California)', 'https://www.calcitrusgrowers.com/', 'Unknown', 'Citrus (Orange)', '⭐⭐⭐⭐', 'Unknown', 'Unknown', 'Not Started', '559.622.9758', 'Capper-Volstead Cooperative', '2026-02-11', '', 'No', ''],
        ['Sunkist Growers, Inc.', 'USA (CA/AZ)', 'Unknown', 'Major Cooperative', 'Citrus', '⭐⭐⭐⭐⭐', 'Unknown', 'Unknown', 'Not Started', '', 'Leader in citrus packing/processing', '2026-02-11', '', 'No', ''],
        ["Florida's Natural Growers Inc", 'USA (Florida)', 'https://floridasnaturalgrowersinc.com/grower-members/', 'Unknown', 'Citrus', '⭐⭐⭐⭐', 'Public (Page)', 'Free', 'Not Started', '', 'Grower members page available', '2026-02-11', '', 'No', ''],
        ['Highlands County FL Citrus Growers', 'USA (Florida)', 'https://www.hccga.com/', 'Unknown', 'Citrus', '⭐⭐⭐', 'Unknown', 'Unknown', 'Not Started', '863-385-8091', 'Sebring, Florida', '2026-02-11', '', 'No', ''],
        
        # CITRUS - GLOBAL
        ['World Citrus Organisation (WCO)', 'International', 'https://worldcitrusorganisation.org/members/', '12 countries + 21 companies', 'Citrus', '⭐⭐⭐⭐⭐', 'Public (Page)', 'Free', 'Scraped', 'wco@worldcitrusorganisation.org', 'Full + associate members scraped', '2026-02-11', '2026-02-11', 'No', ''],
        
        # CITRUS - SOUTH AFRICA
        ['Citrus Growers Association (CGA)', 'South Africa (+region)', 'https://www.cga.co.za/', '1,400', 'Citrus', '⭐⭐⭐⭐⭐', 'Request Required', '$0-500', 'Email Drafted', 'info@cga.co.za', 'SA/Zimbabwe/Mozambique/Botswana/Eswatini/Namibia', '2026-02-11', '', 'Yes', ''],
    ]
    
    # Convert to JSON for gog
    values_json = json.dumps(data)
    
    print(f"Adding {len(data)-1} directories to 'Directories' sheet...")
    
    # Use gog to create/update sheet
    cmd = [
        'gog', 'sheets', 'update',
        '--account', ACCOUNT,
        '--values-json', values_json,
        SPREADSHEET_ID,
        'Directories!A1'
    ]
    
    result = subprocess.run(cmd, capture_output=True, text=True)
    
    if result.returncode != 0:
        print(f"Error: {result.stderr}")
        sys.exit(1)
    
    print(f"✅ Successfully added {len(data)-1} directories to 'Directories' sheet")
    print(f"\nSummary:")
    email_drafted = sum(1 for row in data[1:] if row[13] == 'Yes')
    scraped = sum(1 for row in data[1:] if 'Scraped' in row[8])
    public = sum(1 for row in data[1:] if 'Public' in row[6])
    not_started = sum(1 for row in data[1:] if row[8] == 'Not Started')
    
    print(f"  - Email Drafted: {email_drafted}")
    print(f"  - Scraped/In Progress: {scraped}")
    print(f"  - Public Access: {public}")
    print(f"  - Not Started: {not_started}")

if __name__ == '__main__':
    main()