"""
Sync members (Customers only) from Cin7 Contacts to yudao member_user + member_address

Cin7 Contact fields: id, contactType, email, firstName, lastName,
  phone, mobile, company, address1, address2, city, state, postcode, country,
  isActive, createdDate, modifiedDate

yudao member_user: id, email, mobile, nickname, name, company, country,
  state, city, post_code, type, status, ...

yudao member_address: id, user_id, name, mobile, email, area_id,
  detail_address, default_status, lat, lng, ...

Business rules:
1. Only sync contactType == "Customer" (NOT Supplier)
2. Use email as unique key for upsert
3. If address fields exist, create member_address row
4. lat/lng: NOT fetched here (Geocoding requires Google Maps API key — skip for now)
"""
import json
import re
from db import query_one, insert_and_get_id, execute_one
from config import TENANT_ID


MEMBER_ID_MAP = {}  # cin7_contact_id -> yudao_member_id


def _sync_contact(contact, tenant_id=TENANT_ID):
    """
    Sync a single Cin7 contact as yudao member_user.
    Returns (yudao_member_id, created_bool)
    """
    cin7_id = contact["id"]
    if cin7_id in MEMBER_ID_MAP:
        return MEMBER_ID_MAP[cin7_id], False

    contact_type = _str(contact.get("contactType") or "")
    if contact_type != "Customer":
        return None, False  # Skip non-customers

    email = _str(contact.get("email") or "")
    mobile = _str(contact.get("mobile") or contact.get("phone") or "")
    first_name = _str(contact.get("firstName") or "")
    last_name = _str(contact.get("lastName") or "")
    name = f"{first_name} {last_name}".strip() or email
    company = _str(contact.get("company") or "")
    country = _str(contact.get("country") or "")
    state = _str(contact.get("state") or "")
    city = _str(contact.get("city") or "")
    post_code = _str(contact.get("postcode") or "")
    is_active = contact.get("isActive", True)
    status = 0 if is_active else 1  # 0=正常, 1=禁用

    # Check existing by email
    existing = query_one(
        "SELECT id FROM member_user WHERE email=%s AND deleted=0 LIMIT 1",
        (email,)
    ) if email else None

    if existing:
        yudao_id = existing["id"]
        sql = """
            UPDATE member_user SET
                mobile=%s, nickname=%s, name=%s, company=%s,
                country=%s, state=%s, city=%s, post_code=%s,
                type=%s, status=%s, update_time=NOW(), updater='cin7_sync'
            WHERE id=%s
        """
        execute_one(sql, (
            mobile, name, name, company,
            country, state, city, post_code,
            contact_type, status, yudao_id
        ))
        MEMBER_ID_MAP[cin7_id] = yudao_id
        print(f"  [member] updated id={yudao_id} email='{email}'")
        return yudao_id, False

    # Create new member
    yudao_id = insert_and_get_id(
        """INSERT INTO member_user
            (email, mobile, nickname, name, company, country, state, city,
             post_code, type, status, creator, create_time, updater, tenant_id)
           VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
                   'cin7_sync', NOW(), 'cin7_sync', %s)""",
        (email, mobile, name, name, company,
         country, state, city, post_code,
         contact_type, status, tenant_id)
    )
    MEMBER_ID_MAP[cin7_id] = yudao_id
    print(f"  [member] created id={yudao_id} email='{email}'")

    # Also create member_address if address fields exist
    address1 = _str(contact.get("address1") or "")
    address2 = _str(contact.get("address2") or "")
    full_address = " ".join(filter(None, [address1, address2])).strip()

    if full_address:
        area_id = 0  # yudao area system requires area_id — leave as 0
        area_id = _resolve_area(city, state, country)

        insert_and_get_id(
            """INSERT INTO member_address
                (user_id, name, mobile, email, area_id, detail_address,
                 default_status, creator, create_time, updater, tenant_id)
               VALUES (%s, %s, %s, %s, %s, %s, 1, 'cin7_sync', NOW(), 'cin7_sync', %s)""",
            (yudao_id, name, mobile, email, area_id, full_address, tenant_id)
        )
        print(f"    [address] created for member {yudao_id}")

    return yudao_id, True


def _resolve_area(city, state, country):
    """
    Resolve yudao area_id from city/state/country.
    yudao uses system_area table with id, name, pid, ...
    Simplified: try to find by name matching.
    """
    if not city and not state and not country:
        return 0

    # Try to find area matching city name first
    row = query_one(
        "SELECT id FROM system_area WHERE name=%s LIMIT 1",
        (city or state or country,)
    )
    if row:
        return row["id"]
    return 0


def sync_members(cin7_api, tenant_id=TENANT_ID):
    """Fetch all Cin7 Customer contacts and sync to yudao"""
    print("\n=== Sync Members (Customer only) ===")
    raw = cin7_api.get_contacts(contact_type="Customer")
    contacts = raw.get("d", []) if isinstance(raw, dict) else raw

    created, updated, skipped = 0, 0, 0
    for i, c in enumerate(contacts):
        print(f"  [{i+1}/{len(contacts)}] ", end="")
        result, is_new = _sync_contact(c, tenant_id)
        if result is None:
            skipped += 1
        elif is_new:
            created += 1
        else:
            updated += 1

    print(f"\n[member] Done. {created} created, {updated} updated, {skipped} skipped (non-Customer).")
    print(f"[member] Total mapped: {len(MEMBER_ID_MAP)}")
    return MEMBER_ID_MAP