scripts/import_contacts.py

import asyncio
import asyncpg
import csv
import argparse
import os
import re
from dotenv import load_dotenv

load_dotenv()

DATABASE_URL = os.getenv("DATABASE_URL")

def normalize_phone(phone: str) -> str:
    """Normalize phone number to E.164 format for matching."""
    phone = str(phone).strip()
    if not phone:
        return ""
    phone = re.sub(r'[\s\-\(\)]', '', phone)
    if phone.startswith('+'): return phone
    if phone.startswith('91') and len(phone) == 12: return f'+{phone}'
    if len(phone) == 10 and phone[0] in '6789': return f'+91{phone}'
    return f'+{phone}' if not phone.startswith('+') else phone

async def import_contacts(csv_file: str, tenant_id: str):
    if not DATABASE_URL:
        print("ERROR: DATABASE_URL not set in .env")
        return

    print(f"Connecting to database to import contacts for tenant {tenant_id}...")
    conn = await asyncpg.connect(DATABASE_URL)
    
    success_count = 0
    fail_count = 0

    try:
        with open(csv_file, mode='r', encoding='utf-8-sig') as f:
            reader = csv.DictReader(f)
            
            # Expected columns: Name, Phone, Notes (optional)
            for row in reader:
                raw_phone = row.get("Phone", "").strip()
                name = row.get("Name", "").strip()
                notes = row.get("Notes", "").strip()
                
                if not raw_phone or not name:
                    print(f"Skipping invalid row: {row}")
                    fail_count += 1
                    continue
                
                phone = normalize_phone(raw_phone)
                
                try:
                    # Upsert contact
                    # We set call_count = 0 so that they are still considered "first time callers" 
                    # from the system's perspective of having an actual conversation, 
                    # but we WILL know their name and greet them warmly because of the data presence!
                    await conn.execute(
                        """
                        INSERT INTO caller_profiles (tenant_id, phone_number, name, notes, call_count)
                        VALUES ($1, $2, $3, $4, 0)
                        ON CONFLICT (tenant_id, phone_number) 
                        DO UPDATE SET name = EXCLUDED.name, notes = EXCLUDED.notes
                        """,
                        tenant_id, phone, name, notes if notes else None
                    )
                    success_count += 1
                except Exception as e:
                    print(f"Failed to insert {name} ({phone}): {e}")
                    fail_count += 1

        print(f"\n✅ Import complete! Successfully added/updated {success_count} contacts.")
        if fail_count > 0:
            print(f"❌ Failed to import {fail_count} contacts.")
            
    except FileNotFoundError:
        print(f"ERROR: File '{csv_file}' not found.")
    except Exception as e:
        print(f"ERROR: {e}")
    finally:
        await conn.close()

if __name__ == "__main__":
    parser = argparse.ArgumentParser(description="Import existing client contacts into Maya's memory.")
    parser.add_argument("csv_file", help="Path to the CSV file (must have 'Name' and 'Phone' columns)")
    parser.add_argument("--tenant", required=True, help="Tenant UUID")
    
    args = parser.parse_args()
    asyncio.run(import_contacts(args.csv_file, args.tenant))