#!/usr/bin/env python3
"""
Update New Zealand packhouse data in Google Sheet
Sheet ID: 1uVd-xZFF4TEQGqtvw9z6W8fffeaifPCoLsek83GmEoQ
Tab: "New Zealand"
Account: jonny@jonnyshannon.com
"""

import csv
import sys

# Column order as specified:
# 1. Company Name
# 2. Website
# 3. Country (NZ)
# 4. State/Region
# 5. Primary Fruit
# 6. CA Storage Confirmed
# 7. Qualified (Yes if has website)
# 8. Employee Count
# 9. Revenue
# 10. Notes
# 11. Contact Name
# 12. Contact Title
# 13. Contact Email
# 14. Contact Phone
# 15. Source

companies_data = [
    {
        "Company Name": "Freshco - Wake's Packhouse",
        "Website": "freshco.co.nz",
        "Country": "NZ",
        "State/Region": "Hawke's Bay",
        "Primary Fruit": "Apples, Cherries, Squash",
        "CA Storage Confirmed": "",
        "Qualified": "Yes",
        "Employee Count": "",
        "Revenue": "",
        "Notes": "Part of Freshco NZ - major produce company. Wake's Packhouse at 1061 Riverslea Rd. HQ at 76 Rangitane Rd, Hastings. CEO: Glenn Pool",
        "Contact Name": "Glenn Pool",
        "Contact Title": "CEO",
        "Contact Email": "enquiries@freshco.co.nz",
        "Contact Phone": "+64 6 878 8520",
        "Source": "freshco.co.nz, Google Maps"
    },
    {
        "Company Name": "Kea Apple Packhouse",
        "Website": "integrow.co.nz",
        "Country": "NZ",
        "State/Region": "Hawke's Bay",
        "Primary Fruit": "Apples",
        "CA Storage Confirmed": "",
        "Qualified": "Yes",
        "Employee Count": "",
        "Revenue": "",
        "Notes": "New packhouse opened Feb 2022. Part of Integrow Marketing. Located at 2 Joll Rd, Havelock North. 450K carton capacity. Kea Fruit & Piqa brands",
        "Contact Name": "Richard Burns",
        "Contact Title": "Kea Fruit Contact",
        "Contact Email": "[email protected]",
        "Contact Phone": "+64 6 824 3333",
        "Source": "FreshPlaza, integrow.co.nz"
    },
    {
        "Company Name": "Port Pack & Cool Storage",
        "Website": "portpack.co.nz",
        "Country": "NZ",
        "State/Region": "Bay of Plenty",
        "Primary Fruit": "General cold storage",
        "CA Storage Confirmed": "",
        "Qualified": "Yes",
        "Employee Count": "",
        "Revenue": "",
        "Notes": "Mount Maunganui cold store at 12 Mark Rd. Near Port of Tauranga. MPI Transitional Facility #4938. NZFSA registered",
        "Contact Name": "",
        "Contact Title": "",
        "Contact Email": "",
        "Contact Phone": "",
        "Source": "portpack.co.nz, coldstoragenz.org.nz"
    },
    {
        "Company Name": "Green Planet Limited",
        "Website": "greenplanetapples.wixsite.com/mysite",
        "Country": "NZ",
        "State/Region": "Hawke's Bay",
        "Primary Fruit": "Apples",
        "CA Storage Confirmed": "",
        "Qualified": "Yes",
        "Employee Count": "",
        "Revenue": "",
        "Notes": "Apple packhouse at 10 Hales Rd, Meeanee, Napier. Large seasonal workforce. Basic website",
        "Contact Name": "",
        "Contact Title": "",
        "Contact Email": "",
        "Contact Phone": "+64 6 833 7004",
        "Source": "greenplanetapples.wixsite.com"
    },
    {
        "Company Name": "Maleme Coldstorage",
        "Website": "",
        "Country": "NZ",
        "State/Region": "Bay of Plenty",
        "Primary Fruit": "General cold storage",
        "CA Storage Confirmed": "",
        "Qualified": "No",
        "Employee Count": "",
        "Revenue": "",
        "Notes": "10 Maleme St, Greerton, Tauranga. 4,088 sqm site. Property listed for sale 2025 by Bayleys - status uncertain. Near Port of Tauranga",
        "Contact Name": "",
        "Contact Title": "",
        "Contact Email": "",
        "Contact Phone": "+64 7 541 0772",
        "Source": "Bayleys real estate, near-place.com"
    },
    {
        "Company Name": "NZ Cold Storage Association",
        "Website": "coldstoragenz.org.nz",
        "Country": "NZ",
        "State/Region": "National",
        "Primary Fruit": "N/A - Industry Association",
        "CA Storage Confirmed": "",
        "Qualified": "N/A",
        "Employee Count": "",
        "Revenue": "",
        "Notes": "⚠️ NOT A FACILITY - Industry association representing cold storage operators. REMOVE from facility list or mark as association",
        "Contact Name": "",
        "Contact Title": "",
        "Contact Email": "",
        "Contact Phone": "",
        "Source": "coldstoragenz.org.nz"
    },
    {
        "Company Name": "Kelston Orchard Packhouse",
        "Website": "kelstonpack.com",
        "Country": "NZ",
        "State/Region": "Hawke's Bay",
        "Primary Fruit": "Apples, Summerfruit",
        "CA Storage Confirmed": "",
        "Qualified": "Yes",
        "Employee Count": "",
        "Revenue": "",
        "Notes": "49 Ormond Rd, Twyford, Hastings. Family owned 40+ years. New multi-purpose cool store opened 2022. 10 orchards. MAF Roda technology. Owner: Tony Harington",
        "Contact Name": "Tony Harington",
        "Contact Title": "Owner",
        "Contact Email": "info@kelstonpack.com",
        "Contact Phone": "+64 6 879 4301",
        "Source": "kelstonpack.com, Te Mata Exports"
    },
    {
        "Company Name": "Earnscleugh Orchards",
        "Website": "",
        "Country": "NZ",
        "State/Region": "Central Otago",
        "Primary Fruit": "Stone fruit, cherries",
        "CA Storage Confirmed": "",
        "Qualified": "No",
        "Employee Count": "",
        "Revenue": "",
        "Notes": "⚠️ NOT A SINGLE FACILITY - Earnscleugh is a REGION/VALLEY in Central Otago with multiple independent orchards (Clyde Orchards, Strode Road, Roberts Family, etc). Consider removing or researching specific facility",
        "Contact Name": "",
        "Contact Title": "",
        "Contact Email": "",
        "Contact Phone": "",
        "Source": "centralotagonz.com, farmers market listings"
    },
    {
        "Company Name": "Jazz Apple (T&G Global)",
        "Website": "tandg.global",
        "Country": "NZ",
        "State/Region": "Hawke's Bay",
        "Primary Fruit": "Apples (Jazz, Envy, Pacific Rose)",
        "CA Storage Confirmed": "Yes",
        "Qualified": "Yes",
        "Employee Count": "",
        "Revenue": "",
        "Notes": "T&G Global - one of largest packhouses in Southern Hemisphere. 22 Whakatu Rd, Whakatu. 125M kg capacity, 1.7ha roof, 36K+ sqm. State-of-art automated facility 2023",
        "Contact Name": "",
        "Contact Title": "",
        "Contact Email": "",
        "Contact Phone": "",
        "Source": "tandg.global, LinkedIn, MarketScreener"
    },
    {
        "Company Name": "Prime Produce",
        "Website": "",
        "Country": "NZ",
        "State/Region": "Hawke's Bay (unconfirmed)",
        "Primary Fruit": "Unknown",
        "CA Storage Confirmed": "",
        "Qualified": "No",
        "Employee Count": "",
        "Revenue": "",
        "Notes": "⚠️ CANNOT VERIFY - Generic name, no facility found in searches. May be part of larger operation or no longer operating. Recommend further investigation or removal",
        "Contact Name": "",
        "Contact Title": "",
        "Contact Email": "",
        "Contact Phone": "",
        "Source": "None found"
    },
    {
        "Company Name": "Moffett Orchards",
        "Website": "lovebite.co.nz",
        "Country": "NZ",
        "State/Region": "Hawke's Bay",
        "Primary Fruit": "Apples (Lovebite brand)",
        "CA Storage Confirmed": "",
        "Qualified": "Yes",
        "Employee Count": "",
        "Revenue": "",
        "Notes": "1723 Korokipo Rd, RD3, Napier. One of largest family owned orchards in Hawke's Bay. Founded by Jonathon Moffett (Irish rugby player). Director: Jonathan Wallace Moffett",
        "Contact Name": "Jonathan Wallace Moffett",
        "Contact Title": "Director",
        "Contact Email": "joe@moffetts.co.nz",
        "Contact Phone": "+64 6 879 7985",
        "Source": "lovebite.co.nz, D&B, Companies Office"
    },
    {
        "Company Name": "Golden Oak Orchard",
        "Website": "",
        "Country": "NZ",
        "State/Region": "Hawke's Bay",
        "Primary Fruit": "Apples",
        "CA Storage Confirmed": "",
        "Qualified": "No",
        "Employee Count": "",
        "Revenue": "",
        "Notes": "Golden Oak Orchard Partnership. Address conflicting: 1027 Omahu Rd, Frimley OR 173 Ormond Rd, Twyford. Small local orchard, no website found",
        "Contact Name": "",
        "Contact Title": "",
        "Contact Email": "",
        "Contact Phone": "021 228 3...",
        "Source": "White Pages NZ, Companies Office"
    },
    {
        "Company Name": "Motueka Cold Storage Ltd",
        "Website": "",
        "Country": "NZ",
        "State/Region": "Nelson/Tasman",
        "Primary Fruit": "General cold storage",
        "CA Storage Confirmed": "",
        "Qualified": "No",
        "Employee Count": "",
        "Revenue": "",
        "Notes": "Old Wharf Rd, Port Motueka. Registered 1981. Now part of Cold Storage Nelson Ltd, which was ACQUIRED BY LINEAGE LOGISTICS 2022. Part of Lineage's NZ network expansion",
        "Contact Name": "",
        "Contact Title": "",
        "Contact Email": "",
        "Contact Phone": "",
        "Source": "bizdb.co.nz, Lineage press release"
    },
    {
        "Company Name": "Panmure Orchards",
        "Website": "panmureorchards.co.nz",
        "Country": "NZ",
        "State/Region": "Central Otago",
        "Primary Fruit": "Cherries, Nectarines",
        "CA Storage Confirmed": "",
        "Qualified": "Yes",
        "Employee Count": "",
        "Revenue": "",
        "Notes": "214 Strode Rd, Alexandra. Family orchard since 1952 (Murray Hiscock founder). 6th generation (Jeremy & Jacqui). Premium domestic & export. Hand-selected fruit",
        "Contact Name": "Jeremy (owner)",
        "Contact Title": "6th Gen Orchardist",
        "Contact Email": "admin@panmureorchards.co.nz",
        "Contact Phone": "+64 21 999 307",
        "Source": "panmureorchards.co.nz, Facebook"
    },
    {
        "Company Name": "Dennys Orchard",
        "Website": "",
        "Country": "NZ",
        "State/Region": "Unknown",
        "Primary Fruit": "Unknown",
        "CA Storage Confirmed": "",
        "Qualified": "No",
        "Employee Count": "",
        "Revenue": "",
        "Notes": "⚠️ CANNOT VERIFY - No orchard/packhouse found with this name. Searches only return Denny's restaurant chain. Likely incorrect entry or no longer operating. Recommend removal",
        "Contact Name": "",
        "Contact Title": "",
        "Contact Email": "",
        "Contact Phone": "",
        "Source": "None found"
    },
    {
        "Company Name": "Kathy Forrest Orchard",
        "Website": "",
        "Country": "NZ",
        "State/Region": "Central Otago",
        "Primary Fruit": "Summer fruit",
        "CA Storage Confirmed": "",
        "Qualified": "No",
        "Employee Count": "",
        "Revenue": "",
        "Notes": "⚠️ Previously 'Forrest Orchard' owned by Bill & Kathy Forrest. SOLD to new owners (mentioned in MG Group article). Current name/status unknown. Cannot verify current operations",
        "Contact Name": "Bill & Kathy Forrest (former)",
        "Contact Title": "Former Owners",
        "Contact Email": "",
        "Contact Phone": "",
        "Source": "mggroup.co.nz (historical reference)"
    },
    {
        "Company Name": "Coxco Farming Ltd",
        "Website": "coxco.co.nz",
        "Country": "NZ",
        "State/Region": "Gisborne",
        "Primary Fruit": "Squash, Sweetcorn (NOT apples)",
        "CA Storage Confirmed": "",
        "Qualified": "Yes",
        "Employee Count": "",
        "Revenue": "",
        "Notes": "Poverty Bay Plains, Gisborne. 700 hectares. Est 2006. Specializes in SQUASH for Japan/Korea markets. Also sweetcorn, maize, seeds. NOT primarily fruit - mainly vegetables. Also has labour & transport divisions",
        "Contact Name": "",
        "Contact Title": "",
        "Contact Email": "",
        "Contact Phone": "+64 6 867 4449",
        "Source": "coxco.co.nz"
    }
]

def main():
    print("Preparing to update Google Sheet...")
    print(f"Total companies: {len(companies_data)}")
    print("\nCompanies with websites (Qualified=Yes):")
    qualified = [c for c in companies_data if c['Qualified'] == 'Yes']
    print(f"  {len(qualified)} companies")
    for c in qualified:
        print(f"  - {c['Company Name']}: {c['Website']}")
    
    print("\nCompanies without websites (Qualified=No):")
    not_qualified = [c for c in companies_data if c['Qualified'] == 'No']
    print(f"  {len(not_qualified)} companies")
    for c in not_qualified:
        print(f"  - {c['Company Name']}")
    
    print("\n⚠️  WARNINGS - Companies to review:")
    warnings = [c for c in companies_data if '⚠️' in c['Notes']]
    for c in warnings:
        print(f"  - {c['Company Name']}: {c['Notes'][:80]}...")
    
    print("\n" + "="*80)
    print("DATA READY FOR SHEET UPDATE")
    print("="*80)
    print("\nTo update the sheet, you need Google Sheets API credentials.")
    print("Sheet ID: 1uVd-xZFF4TEQGqtvw9z6W8fffeaifPCoLsek83GmEoQ")
    print("Tab: 'New Zealand'")
    print("Account: jonny@jonnyshannon.com")
    
    # Export to CSV for manual upload if needed
    csv_file = 'postharvest/nz_companies_update.csv'
    with open(csv_file, 'w', newline='', encoding='utf-8') as f:
        fieldnames = ["Company Name", "Website", "Country", "State/Region", "Primary Fruit", 
                     "CA Storage Confirmed", "Qualified", "Employee Count", "Revenue", "Notes",
                     "Contact Name", "Contact Title", "Contact Email", "Contact Phone", "Source"]
        writer = csv.DictWriter(f, fieldnames=fieldnames)
        writer.writeheader()
        writer.writerows(companies_data)
    
    print(f"\n✅ CSV exported to: {csv_file}")
    print("You can import this CSV to Google Sheets manually if API access not available")

if __name__ == "__main__":
    main()