import pandas as pd
import requests
from tqdm import tqdm
import time
from datetime import datetime

# =======================
# CONFIGURATION
# =======================
API_URL = "https://inp.api-us1.com/api/3"
API_KEY = "0493c4d2d33662a670c7b347077c74dce411fe0261fb00affa7e8cdd34041ec16861355a"  # <-- rotate your key and put the new one here
CSV_FILE = "Contacts_Matched_test.csv"

# Column names in your CSV (case-sensitive) ---------------------
EMAIL_COL = "Email"                  # you said to use "Email"
ACCOUNT_COL = "company"              # link to Account by this
CORE_FIELD_MAP = {                   # contact core fields (direct on contact)
    # "full name": "firstName",        # or split later if you want
    # add "work phone": "phone", etc. if needed
}

# Custom field IDs in AC (Settings → Fields → note the numeric ID)
CUSTOM_FIELD_IDS = {
    "id": 17,
    "cancel date": 79,
    "newmbr date": 86,
    "paid thru": 264,
    "co id": 16,
    "cert status": 183,
    "pcl status": 287,
    "pcl completion date": 289,
    "pf1 status": 291,
    "pf1 completion date": 292,
    "pf2 status": 293,
    "pf2 completion date": 294,
    "intro to accounting status": 295,   
    "intro to accounting completion date": 296,   
    "state province": 4,   
    "c p a   certification": 182,   
    "last  act": 278,   
    "french": 7,
    "last  infoline": 276, 
    "last conf": 277, 
    "Processed member type": 18, 
    "new lead status": 2, 
    "New DO ID": 20, 
    "Number of Passed Courses": 297,  # numeric custom field
}

# rate limit safety
RATE_LIMIT_DELAY = 0.22  # ActiveCampaign limit ~5 req/s

HEADERS = {
    "Api-Token": API_KEY,
    "Content-Type": "application/json",
}

# =======================
# HELPERS
# =======================

def _safe_date(v):
    """Return YYYY-MM-DD for date-like values, else raw string."""
    if pd.isna(v) or str(v).strip() == "":
        return ""
    s = str(v).strip()
    # try common formats
    for fmt in ("%Y-%m-%d", "%Y/%m/%d", "%Y-%m-%d %H:%M:%S", "%m/%d/%Y", "%d/%m/%Y"):
        try:
            return datetime.strptime(s, fmt).strftime("%Y-%m-%d")
        except ValueError:
            pass
    # last resort: let AC try to parse
    return s

def get_contact_id_by_email(email):
    r = requests.get(f"{API_URL}/contacts", headers=HEADERS, params={"email": email})
    if r.status_code != 200:
        return None
    data = r.json()
    contacts = data.get("contacts", [])
    return contacts[0]["id"] if contacts else None

def update_contact_core_and_custom(contact_id, core_payload, custom_values):
    """
    core_payload: dict of direct contact fields (e.g., {"firstName": "Molly"})
    custom_values: list of {"field": <id>, "value": <str>}
    """
    payload = {"contact": core_payload}
    if custom_values:
        payload["contact"]["fieldValues"] = custom_values

    r = requests.put(f"{API_URL}/contacts/{contact_id}", headers=HEADERS, json=payload)
    return r.status_code, r.text

# --- Accounts linking ---
_account_cache = {}
def upsert_account_by_name(name):
    """Return account_id for a given name, creating if needed. Cached."""
    if not name or str(name).strip() == "":
        return None
    key = str(name).strip()
    if key in _account_cache:
        return _account_cache[key]

    # search accounts
    r = requests.get(f"{API_URL}/accounts", headers=HEADERS, params={"search": key})
    if r.status_code == 200:
        items = r.json().get("accounts", [])
        for a in items:
            if a.get("name", "").strip().lower() == key.lower():
                _account_cache[key] = a["id"]
                return a["id"]

    # create if not found
    create = requests.post(f"{API_URL}/accounts", headers=HEADERS, json={"account": {"name": key}})
    if create.status_code in (200, 201):
        aid = create.json()["account"]["id"]
        _account_cache[key] = aid
        return aid
    return None

def ensure_account_contact_link(contact_id, account_id):
    """Ensure contact is linked to the given account (replaces old link if necessary)."""
    if not contact_id or not account_id:
        return

    # --- Step 1: get existing accountContact links for this contact ---
    r = requests.get(f"{API_URL}/accountContacts", headers=HEADERS, params={"filters[contact]": contact_id})
    if r.status_code == 200:
        existing_links = r.json().get("accountContacts", [])
    else:
        existing_links = []

    # --- Step 2: unlink from other accounts if already linked ---
    for link in existing_links:
        existing_account = link.get("account")
        if existing_account != str(account_id):
            link_id = link.get("id")
            if link_id:
                requests.delete(f"{API_URL}/accountContacts/{link_id}", headers=HEADERS)

    # --- Step 3: link to the new account if not already linked ---
    already_linked = any(link.get("account") == str(account_id) for link in existing_links)
    if not already_linked:
        r2 = requests.post(
            f"{API_URL}/accountContacts",
            headers=HEADERS,
            json={"accountContact": {"contact": str(contact_id), "account": str(account_id)}},
        )
        if r2.status_code not in (200, 201):
            print(f"⚠ Could not link contact {contact_id} to account {account_id}: {r2.status_code} {r2.text[:200]}")

# =======================
# MAIN BATCH
# =======================

def build_core_payload(row):
    """Map CSV core fields to AC contact fields."""
    payload = {}
    for csv_col, ac_field in CORE_FIELD_MAP.items():
        if csv_col in row and not pd.isna(row[csv_col]) and str(row[csv_col]).strip() != "":
            payload[ac_field] = str(row[csv_col]).strip()
    return payload

def build_custom_values(row):
    """Build list of {'field': id, 'value': str} for custom fields."""
    out = []
    for csv_col, field_id in CUSTOM_FIELD_IDS.items():
        if csv_col in row:
            val = row[csv_col]
            if pd.isna(val) or str(val).strip() == "":
                continue
            v = str(val).strip()
            # basic formatting for known fields
            if csv_col.lower() == "paid thru":
                v = _safe_date(v)
            out.append({"field": str(field_id), "value": v})
    return out

def batch_update_contacts(df):
    total = len(df)
    print(f"Processing {total:,} contacts...")
    ok, fail, skip = 0, 0, 0

    for _, row in tqdm(df.iterrows(), total=total):
        raw_email = str(row.get(EMAIL_COL, "")).strip().lower()
        if not raw_email:
            skip += 1
            continue

        contact_id = get_contact_id_by_email(raw_email)
        if not contact_id:
            # Skip (or create contact here if you want)
            # You can create with POST /contacts, then continue.
            skip += 1
            time.sleep(RATE_LIMIT_DELAY)
            continue

        core_payload = build_core_payload(row)
        # ensure email is maintained (AC requires email in PUT payload when present)
        core_payload["email"] = raw_email

        custom_values = build_custom_values(row)

        status, body = update_contact_core_and_custom(contact_id, core_payload, custom_values)
        if 200 <= status < 300:
            ok += 1
        else:
            fail += 1
            print(f"Update failed ({status}) for {raw_email}: {body[:300]}")

        # Account linking
        account_name = row.get(ACCOUNT_COL)
        if account_name and str(account_name).strip() != "":
            aid = upsert_account_by_name(account_name)
            if aid:
                ensure_account_contact_link(contact_id, aid)

        time.sleep(RATE_LIMIT_DELAY)

    print(f"\n✅ Done: {ok} succeeded, ❌ {fail} failed, ⏭ {skip} skipped.")

if __name__ == "__main__":
    df = pd.read_csv(CSV_FILE, dtype=str, encoding="utf-8-sig")
    # Keep original case headers (no lowercasing) so "Email" stays distinct
    needed = {EMAIL_COL} | set(CORE_FIELD_MAP.keys()) | set(CUSTOM_FIELD_IDS.keys()) | {ACCOUNT_COL}
    missing = [c for c in needed if c not in df.columns]
    if missing:
        print(f"⚠ Missing columns in CSV: {missing}")

    # We still pass entire df rows to builders, so we don't have to subset strictly
    batch_update_contacts(df)