#!/usr/bin/env python3
"""
Extract all kiwifruit companies from global cold storage databases
"""

import csv
import os
import re
from pathlib import Path
from collections import defaultdict

# Define workspace path
workspace = Path("/Users/max/.openclaw/workspace/postharvest")

# All database files to search
database_files = [
    ("nz-sheet-data.tsv", "\t", "New Zealand"),
    ("italy-cold-storage.csv", ",", "Italy"),
    ("verified-scored-facilities.csv", ",", "USA"),
    ("chile-cold-storage.csv", ",", "Chile"),
    ("france-cold-storage.csv", ",", "France"),
    ("argentina-cold-storage.csv", ",", "Argentina"),
    ("belgium-cold-storage.csv", ",", "Belgium"),
    ("brazil-cold-storage.csv", ",", "Brazil"),
    ("canada-cold-storage.csv", ",", "Canada"),
    ("germany-cold-storage.csv", ",", "Germany"),
    ("mexico-cold-storage.csv", ",", "Mexico"),
    ("netherlands-cold-storage.csv", ",", "Netherlands"),
    ("peru-cold-storage.csv", ",", "Peru"),
    ("poland-cold-storage.csv", ",", "Poland"),
    ("portugal-cold-storage.csv", ",", "Portugal"),
    ("spain-cold-storage.csv", ",", "Spain"),
    ("turkey-cold-storage.csv", ",", "Turkey"),
    ("uk-cold-storage.csv", ",", "UK"),
]

# Output columns
output_columns = [
    "Company Name",
    "Website",
    "Country",
    "State/Region",
    "Primary Fruit",
    "CA Storage Confirmed",
    "Qualified",
    "Employee Count",
    "Est. Revenue ($M)",
    "Notes",
    "Contact Name",
    "Contact Title",
    "Contact Email",
    "Contact Phone"
]

# Column name variations for primary fruit/products
FRUIT_COLUMN_NAMES = ['Primary Fruit', 'Produce', 'Products', 'Primary Produce', 'Product']
COMPANY_COLUMN_NAMES = ['Company Name', 'Company']
WEBSITE_COLUMN_NAMES = ['Website', 'Web']
STATE_COLUMN_NAMES = ['State/Region', 'Region', 'State', 'City', 'Location', 'Province']
CA_COLUMN_NAMES = ['CA Storage Confirmed', 'CA Storage', 'CA/MA', 'Confirmed']
EMPLOYEE_COLUMN_NAMES = ['Employee Count', 'Employee', 'Employees']
REVENUE_COLUMN_NAMES = ['Est. Revenue ($M)', 'Revenue', 'Est. Revenue']
CONTACT_EMAIL_COLUMN_NAMES = ['Contact Email', 'Email', 'Contact']
CONTACT_NAME_COLUMN_NAMES = ['Contact Name', 'Name']
CONTACT_TITLE_COLUMN_NAMES = ['Contact Title', 'Title']
CONTACT_PHONE_COLUMN_NAMES = ['Contact Phone', 'Phone', 'Telephone']

def find_column(row, possible_names):
    """Find the first matching column name in the row"""
    for name in possible_names:
        if name in row:
            return name
    return None

def is_kiwifruit(fruit_text):
    """Check if text contains kiwifruit"""
    if not fruit_text:
        return False
    fruit_lower = fruit_text.lower()
    # Search for kiwi-related terms
    return any(term in fruit_lower for term in ['kiwifruit', 'kiwi fruit', 'kiwi'])

def is_valid_facility(row, company_name):
    """Exclude tourist attractions, signs, and non-storage facilities"""
    if not company_name:
        return False
    
    name_lower = company_name.lower()
    
    # Get notes from various possible column names
    notes = ''
    for col in ['Notes', 'Note', 'Description', 'Type']:
        if col in row:
            notes += ' ' + str(row.get(col, '')).lower()
    
    # Exclusion patterns
    exclude_terms = [
        'tourist', 'museum', 'sign company', 'restaurant', 'cafe', 
        'retail only', 'shop', 'market stall', 'orchard only',
        'farm stand only', 'winery only', 'juice bar'
    ]
    
    # Check if it's a tourist attraction or non-storage facility
    for term in exclude_terms:
        if term in name_lower or term in notes:
            return False
    
    # Exclude if it's clearly just an industry organization
    if 'committee' in name_lower or 'association' in name_lower:
        if 'packing' not in notes and 'storage' not in notes and 'cold' not in notes:
            return False
    
    return True

def normalize_value(value):
    """Normalize cell values"""
    if value is None or value == '':
        return ''
    val = str(value).strip()
    # Clean up common issues
    if val == '#ERROR!' or val == 'N/A' or val == 'n/a':
        return ''
    return val

def find_column_positions(header_line):
    """Find the start position of each column based on header using 2+ space gaps"""
    # Split header by 2+ spaces to identify columns
    parts = re.split(r'  +', header_line.rstrip())
    
    positions = []
    search_pos = 0
    
    for part in parts:
        if not part.strip():
            continue
        
        # Find where this column starts
        col_start = header_line.index(part, search_pos)
        positions.append((part.strip(), col_start))
        search_pos = col_start + len(part)
    
    return positions

def parse_nz_file(filepath):
    """Parse NZ file using fixed-width columns based on header positions"""
    with open(filepath, 'r', encoding='utf-8') as f:
        lines = f.readlines()
    
    # Get column positions from header (line 2, after grouped header)
    header_line = lines[1]
    col_positions = find_column_positions(header_line)
    
    # Add end positions for each column
    columns = []
    for i, (name, start) in enumerate(col_positions):
        if i + 1 < len(col_positions):
            end = col_positions[i + 1][1]
        else:
            end = None  # Last column goes to end of line
        columns.append((name, start, end))
    
    # Parse data rows using fixed-width positions
    rows = []
    for line in lines[2:]:  # Skip first 2 header rows
        if not line.strip():
            continue
        
        row = {}
        for col_name, start, end in columns:
            if end:
                value = line[start:end].strip() if start < len(line) else ''
            else:
                value = line[start:].strip() if start < len(line) else ''
            row[col_name] = value
        
        rows.append(row)
    
    return rows

def get_row_value(row, column_names):
    """Get value from row using list of possible column names"""
    col = find_column(row, column_names)
    if col:
        return normalize_value(row.get(col, ''))
    return ''

def extract_kiwifruit_companies():
    """Extract all kiwifruit companies from all databases"""
    
    kiwifruit_companies = []
    stats_by_country = defaultdict(int)
    
    for filename, delimiter, default_country in database_files:
        filepath = workspace / filename
        
        if not filepath.exists():
            print(f"⚠️  File not found: {filename}")
            continue
        
        print(f"🔍 Searching {filename}...")
        
        try:
            # Special handling for NZ file with space-separated columns
            if filename == "nz-sheet-data.tsv":
                rows = parse_nz_file(filepath)
            else:
                with open(filepath, 'r', encoding='utf-8') as f:
                    reader = csv.DictReader(f, delimiter=delimiter)
                    rows = list(reader)
            
            count_in_file = 0
            for row in rows:
                # Find the fruit/product column - check all possible columns
                fruit_text = ''
                for col_name in FRUIT_COLUMN_NAMES:
                    if col_name in row:
                        text = normalize_value(row.get(col_name, ''))
                        if text:
                            fruit_text = text
                            # For kiwifruit, check if this column contains it
                            if is_kiwifruit(text):
                                fruit_text = text
                                break
                
                company_name = get_row_value(row, COMPANY_COLUMN_NAMES)
                
                # Check if it's a kiwifruit company
                if is_kiwifruit(fruit_text) and is_valid_facility(row, company_name):
                    # Determine country (prefer row data, fall back to default)
                    country = normalize_value(row.get('Country', default_country))
                    if not country:
                        country = default_country
                    
                    # Get CA storage info
                    ca_storage = get_row_value(row, CA_COLUMN_NAMES)
                    # Normalize CA storage values
                    if ca_storage.lower() in ['yes', 'y', 'true', '1', 'likely']:
                        ca_storage = 'Yes'
                    elif ca_storage.lower() in ['no', 'n', 'false', '0']:
                        ca_storage = 'No'
                    
                    # Build output row
                    output_row = {
                        'Company Name': company_name,
                        'Website': get_row_value(row, WEBSITE_COLUMN_NAMES),
                        'Country': country,
                        'State/Region': get_row_value(row, STATE_COLUMN_NAMES),
                        'Primary Fruit': fruit_text,
                        'CA Storage Confirmed': ca_storage,
                        'Qualified': normalize_value(row.get('Qualified', '')),
                        'Employee Count': get_row_value(row, EMPLOYEE_COLUMN_NAMES),
                        'Est. Revenue ($M)': get_row_value(row, REVENUE_COLUMN_NAMES),
                        'Notes': normalize_value(row.get('Notes', '')),
                        'Contact Name': get_row_value(row, CONTACT_NAME_COLUMN_NAMES),
                        'Contact Title': get_row_value(row, CONTACT_TITLE_COLUMN_NAMES),
                        'Contact Email': get_row_value(row, CONTACT_EMAIL_COLUMN_NAMES),
                        'Contact Phone': get_row_value(row, CONTACT_PHONE_COLUMN_NAMES)
                    }
                    
                    kiwifruit_companies.append(output_row)
                    stats_by_country[country] += 1
                    count_in_file += 1
            
            if count_in_file > 0:
                print(f"   ✅ Found {count_in_file} kiwifruit companies")
                        
        except Exception as e:
            print(f"❌ Error reading {filename}: {e}")
            import traceback
            traceback.print_exc()
            continue
    
    print(f"\n✅ Found {len(kiwifruit_companies)} kiwifruit entries before deduplication")
    
    return kiwifruit_companies, stats_by_country

def extract_contact_info(text):
    """Extract email, phone, and name from text using regex"""
    import re
    
    result = {}
    
    # Extract email
    email_match = re.search(r'\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}\b', text)
    if email_match:
        result['email'] = email_match.group()
    
    # Extract phone (various formats)
    phone_match = re.search(r'(\+\d{1,3}\s?)?(\(?\d{1,4}\)?[\s.-]?)?\d{3,4}[\s.-]?\d{3,4}', text)
    if phone_match:
        result['phone'] = phone_match.group()
    
    return result

def clean_contact_data(companies):
    """Post-process to extract contact info from misaligned fields"""
    for company in companies:
        # Check all fields for email addresses if contact email is missing or invalid
        if not company['Contact Email'] or '@' not in company['Contact Email']:
            # Search in Notes, Contact Name, Contact Title, Contact Phone fields
            all_text = ' '.join([
                company.get('Notes', ''),
                company.get('Contact Name', ''),
                company.get('Contact Title', ''),
                company.get('Contact Phone', '')
            ])
            
            contact_info = extract_contact_info(all_text)
            if contact_info.get('email'):
                company['Contact Email'] = contact_info['email']
            if contact_info.get('phone') and not company.get('Contact Phone'):
                company['Contact Phone'] = contact_info['phone']
    
    return companies

def deduplicate_companies(companies):
    """Remove duplicates, keeping entries with most complete data"""
    
    # Group by company name + country
    grouped = defaultdict(list)
    for company in companies:
        key = (company['Company Name'].lower().strip(), company['Country'].lower().strip())
        grouped[key].append(company)
    
    deduplicated = []
    
    for key, entries in grouped.items():
        if len(entries) == 1:
            # Mark verified facilities as Qualified if not already set
            if entries[0]['Qualified'] == '':
                entries[0]['Qualified'] = 'Yes'
            deduplicated.append(entries[0])
        else:
            # Multiple entries - pick the one with most complete data
            best_entry = max(entries, key=lambda x: sum(1 for v in x.values() if v and v.strip()))
            # Mark as Qualified if not already set
            if best_entry['Qualified'] == '':
                best_entry['Qualified'] = 'Yes'
            deduplicated.append(best_entry)
            print(f"📋 Merged {len(entries)} duplicates for: {key[0]} ({key[1]})")
    
    return deduplicated

def save_to_csv(companies, output_path):
    """Save companies to CSV file"""
    
    with open(output_path, 'w', encoding='utf-8', newline='') as f:
        writer = csv.DictWriter(f, fieldnames=output_columns, quoting=csv.QUOTE_ALL)
        writer.writeheader()
        writer.writerows(companies)
    
    print(f"\n💾 Saved to: {output_path}")

def generate_report(companies, stats_by_country):
    """Generate summary report"""
    
    print("\n" + "="*60)
    print("📊 KIWIFRUIT EXTRACTION REPORT")
    print("="*60)
    
    print(f"\n✅ Total kiwifruit companies: {len(companies)}")
    
    print(f"\n📍 Breakdown by country:")
    # Recalculate stats for deduplicated data
    final_stats = defaultdict(int)
    for company in companies:
        final_stats[company['Country']] += 1
    
    for country in sorted(final_stats.keys()):
        print(f"   {country}: {final_stats[country]}")
    
    # Count companies with contacts
    with_contacts = sum(1 for c in companies if c['Contact Email'] or c['Contact Phone'])
    without_contacts = len(companies) - with_contacts
    
    print(f"\n📧 Contact information:")
    print(f"   With contacts: {with_contacts}")
    print(f"   Without contacts: {without_contacts}")
    
    print("\n" + "="*60)

def main():
    """Main extraction process"""
    
    print("🥝 KIWIFRUIT COMPANY EXTRACTION")
    print("="*60)
    
    # Extract from all databases
    companies, initial_stats = extract_kiwifruit_companies()
    
    if not companies:
        print("\n⚠️  No kiwifruit companies found!")
        return
    
    # Deduplicate
    print("\n🔄 Deduplicating entries...")
    deduplicated = deduplicate_companies(companies)
    
    # Clean up contact data
    print("\n🔧 Extracting contact information...")
    deduplicated = clean_contact_data(deduplicated)
    
    # Save to output file
    output_path = workspace / "kiwifruit-global-upload.csv"
    save_to_csv(deduplicated, output_path)
    
    # Generate report
    generate_report(deduplicated, initial_stats)

if __name__ == "__main__":
    main()