import pandas as pd
import re
from datetime import datetime, timedelta
import calendar
from pathlib import Path

# --- Load files ---
base = Path(__file__).resolve().parent
contacts_file = base / "ResultsGrid_ExportData_fixed.csv"
ac_file = base / "All_Contacts.csv"

contacts = pd.read_csv(contacts_file, low_memory=False, encoding="utf-8-sig")
ac = pd.read_csv(ac_file, low_memory=False, encoding="utf-8-sig")

contacts_raw = contacts.copy()  # already loaded as ResultsGrid_ExportData.csv
contacts_raw.columns = contacts_raw.columns.str.strip().str.lower()                                                                              
# --- Normalize column names ---
contacts.columns = contacts.columns.str.strip().str.lower()
ac.columns = ac.columns.str.strip()

# --- Normalize emails (avoid NaN mismatches) ---
contacts["email"] = contacts["email"].fillna("").str.strip().str.lower()
ac["Email"] = ac["Email"].fillna("").str.strip().str.lower()

# --- Keep raw Work/Home phone columns for international check ---
contacts["raw_work_phone"] = contacts["work phone"]
contacts["raw_home_phone"] = contacts["home phone"]

# --- Step 0: Deduplicate contacts by email, keeping latest paid thru ---
if "paid thru" in contacts.columns:
    contacts["paid thru"] = pd.to_datetime(contacts["paid thru"], errors="coerce")
    contacts = contacts.sort_values("paid thru", ascending=False)
    # Split contacts into those with and without email
    contacts_with_email = contacts[contacts["email"].str.strip() != ""].drop_duplicates(subset=["email"], keep="first")
    contacts_no_email = contacts[contacts["email"].str.strip() == ""]

    # Combine back together
    contacts = pd.concat([contacts_with_email, contacts_no_email], ignore_index=True)

# --- Step 1: Merge on Email ---

merged_email = pd.merge(
    contacts,
    ac[["Email", "*INDIVIDUAL ID", "*Members Type", "*DO ID", "*Organization ID", "*Lead Status"]],
    left_on="email",
    right_on="Email",
    how="inner",
    suffixes=("_portal", "_ac")
)

# --- Step 2: Find unmatched by Email ---
#contacts_unmatched = contacts[~contacts["email"].isin(merged_email["email"])]
contacts_unmatched = contacts[~contacts["email"].isin(merged_email["email"])]

# Ensure ID fields are strings for reliable match
contacts_unmatched["id"] = contacts_unmatched["id"].fillna("").astype(str).str.strip()
ac["*INDIVIDUAL ID"] = ac["*INDIVIDUAL ID"].fillna("").astype(str).str.strip()

# --- Step 2b: Export unmatched contacts by email ---
contacts_unmatched.to_csv(base / "contacts_unmatched_by_email.csv", index=False, encoding="utf-8-sig")
print(f"Unmatched contacts by email: {len(contacts_unmatched)}")
print("File saved: contacts_unmatched_by_email.csv")

# Debug: see how many IDs overlap between the two dataframes
id_overlap = set(contacts_unmatched["id"]) & set(ac["*INDIVIDUAL ID"])
print(f"ID overlap count: {len(id_overlap)}")
print("Sample overlaps:", list(id_overlap)[:10])


def normalize_id(x):
    x = str(x).strip()
    if x == "" or x.lower() == "nan":
        return ""
    # Remove leading zeros and decimal .0 if any
    if x.replace(".", "", 1).isdigit():
        return str(int(float(x)))
    return x

contacts_unmatched["id_norm"] = contacts_unmatched["id"].apply(normalize_id)
ac["id_norm"] = ac["*INDIVIDUAL ID"].apply(normalize_id)

id_overlap = set(contacts_unmatched["id_norm"]) & set(ac["id_norm"])
print(f"Normalized ID overlap count: {len(id_overlap)}")
print("Sample overlaps:", list(id_overlap)[:10])


# --- Step 3: Merge unmatched on ID *INDIVIDUAL ID ---
merged_id = pd.merge(
    contacts_unmatched,
    ac[["Email", "*INDIVIDUAL ID", "*Members Type", "*DO ID", "*Organization ID", "*Lead Status", "id_norm"]],
    left_on="id_norm",
    right_on="id_norm",
    how="inner",
    suffixes=("_portal", "_ac")
)

# --- Step 4: Combine results ---
final_matched = pd.concat([merged_email, merged_id], ignore_index=True)

# ============================================================
# FORMATTING LAYER (dates + phones + phone extension)
# ============================================================

# --- Format dates ---
date_like_cols = [
    col for col in final_matched.columns
    if ("date" in col.lower() or "paid thru" in col.lower())
    or col.strip().lower() in ["last  act", "last  infoline", "last conf"]
]

for col in date_like_cols:
    final_matched[col] = pd.to_datetime(final_matched[col], errors="coerce").dt.strftime("%Y-%m-%d")

# --- Normalize phone numbers & extract extension ---
def format_phone(val):
    """
    Normalize phone numbers and extract extensions.
    Rules:
    - Anything in parentheses at the end () → extension
    - Keywords: ext, x, poste, post, #, p., option → extension
    - Canadian numbers (10 digits) → keep as main
    - Long numbers with no spaces/parentheses → keep as-is
    """
    if pd.isna(val) or not str(val).strip():
        return "", ""
    
    val = str(val).strip()

    ext = ""

    # 1️ Look for parentheses at the end e.g., 5142254304 (1128)
    paren_match = re.search(r"\((\d+)\)\s*$", val)
    if paren_match:
        ext = paren_match.group(1)
        val = re.sub(r"\(\d+\)\s*$", "", val)

    # 2️ Look for extension with keywords
    if not ext:
        ext_patterns = [
            r"(?:ext\.?|x[:.\s]?|poste|post)\s*(\d+)$",
            r"(?:#|p\.|option)\s*(\d+)$",
        ]
        for pat in ext_patterns:
            match = re.search(pat, val, flags=re.IGNORECASE)
            if match:
                ext = match.group(1)
                val = re.sub(pat, "", val, flags=re.IGNORECASE)
                break

    # 3️ Remove all non-digit characters except leading +
    digits_only = re.sub(r"[^\d+]", "", val)

    # 4️ Handle Canadian numbers: if 10 digits (or starts with 1 + 10 digits), keep 10 digits
    if re.fullmatch(r"\+?1?\d{10}", digits_only):
        digits = re.sub(r"\D", "", digits_only)[-10:]
    else:
        # Keep long unformatted numbers as-is
        digits = digits_only

    return digits, ext

# --- Keep raw Work/Home phone columns (after merge, before formatting)
final_matched["raw_work_phone"] = final_matched["work phone"]
final_matched["raw_home_phone"] = final_matched["home phone"]

# --- Format all phone numbers except raw_ columns
for col in final_matched.columns:
    if "phone" in col.lower() and not col.startswith("raw_"):
        phone_col = col
        ext_col = f"{col}_ext"
        final_matched[[phone_col, ext_col]] = final_matched[phone_col].apply(
            lambda x: pd.Series(format_phone(x))
        )
# --- Step 4b: Processed member type ---
def transform_type(val):
    """Apply transformation rules to a raw member type value."""
    val = str(val).strip().upper()
    if val in ["AST", "CAND", "PRO", "CORP"]:
        return val
    elif val == "DEL":
        return "CORP"
    elif val == "DELAS":
        return "AST"
    elif val == "DELCD":
        return "CAND"
    elif val == "DELPR":
        return "PRO"
    elif val in ["HON", "NDEL", "NM", "RET"]:
        return None  # special case → handled outside
    else:
        return val if val else None

def safe_str(val):
    """Convert NaN/None to empty string, otherwise uppercase stripped string."""
    if pd.isna(val):
        return ""
    return str(val).strip().upper()

def process_member_type(row):
    mbr = transform_type(safe_str(row.get("member type", "")))
    cancel = transform_type(safe_str(row.get("mbr type cancellation", "")))
    ac_val = safe_str(row.get("*Members Type", ""))

    raw_mbr = safe_str(row.get("member type", ""))
    raw_cancel = safe_str(row.get("mbr type cancellation", ""))

    # --- NEW RULE: If member type = NM and cancellation = FTS → CORP
    if raw_mbr == "NM" and raw_cancel == "FTS":
        return "CORP"

    # Special NM rule
    if raw_mbr == "NM":
        if raw_cancel in ["HON", "NDEL", "NM", "RET", ""]:
            return ac_val if ac_val else mbr
        else:
            return cancel if cancel else mbr

    # If member type is HON, NDEL, NM, RET → use cancellation value
    if raw_mbr in ["HON", "NDEL", "NM", "RET"]:
        return cancel if cancel else raw_mbr

    return mbr if mbr else cancel

final_matched["Processed member type"] = final_matched.apply(process_member_type, axis=1)

# === NEW: helpers to compute lapsed date from paid_thru ===
def _to_date_or_none(ts):
    """Return a Python date or None for a pandas Timestamp/NaT."""
    if pd.isna(ts):
        return None
    if isinstance(ts, pd.Timestamp):
        return ts.date()
    if isinstance(ts, datetime):
        return ts.date()
    try:
        return pd.to_datetime(ts, errors="coerce").date()
    except Exception:
        return None

def add_one_month_rollover(dt):
    """
    Add 1 month to a date with overflow behavior:
      - If next-month doesn't have the same day, overflow into subsequent days.
        e.g. 2025-08-31 + 1 month -> attempts 2025-09-31 -> becomes 2025-10-01
    Returns a Python date or None.
    """
    if dt is None:
        return None
    year = dt.year
    month = dt.month
    day = dt.day

    # target next month
    target_month = month + 1
    target_year = year
    if target_month == 13:
        target_month = 1
        target_year += 1

    days_in_target = calendar.monthrange(target_year, target_month)[1]

    if day <= days_in_target:
        # straightforward case
        return datetime(target_year, target_month, day).date()
    else:
        # overflow: compute leftover days and add them to the last day of target month
        overflow = day - days_in_target
        base = datetime(target_year, target_month, days_in_target).date()
        return base + timedelta(days=overflow)

def first_day_of_next_month(dt):
    """Return first day of the month after dt (Python date)."""
    if dt is None:
        return None
    year = dt.year
    month = dt.month + 1
    if month == 13:
        month = 1
        year += 1
    return datetime(year, month, 1).date()
    

# --- Step 6: Add company-level fields from CORP records ---

# Normalize IDs
contacts_raw["id"] = contacts_raw["id"].apply(normalize_id)
final_matched["co id"] = final_matched["co id"].apply(normalize_id)
final_matched["*Organization ID"] = (
    final_matched["*Organization ID"]
    .apply(normalize_id)
)

# Normalize member type
contacts_raw["member type norm"] = (
    contacts_raw["member type"]
    .fillna("")
    .astype(str)
    .str.strip()
    .str.upper()
)

# Keep only CORP records
corp_records = contacts_raw.loc[
    contacts_raw["member type norm"] == "CORP"
].copy()

# Create lookup maps
corp_paid_thru_map = (
    corp_records
    .dropna(subset=["id"])
    .drop_duplicates(subset=["id"])
    .set_index("id")["paid thru"]
    .to_dict()
)

corp_cancel_date_map = (
    corp_records
    .dropna(subset=["id"])
    .drop_duplicates(subset=["id"])
    .set_index("id")["cancel date"]
    .to_dict()
)

# Create Company Status map
corp_status_map = (
    corp_records
    .dropna(subset=["id"])
    .drop_duplicates(subset=["id"])
    .set_index("id")["status"]
    .to_dict()
)

# Helper: determine if row is delegate-related
def is_delegate_row(row):

    member_type = str(row.get("member type", "")).strip().upper()
    cancel_type = str(row.get("mbr type cancellation", "")).strip().upper()

    return (
        member_type.startswith("DEL")
        or (
            member_type == "NM"
            and cancel_type.startswith("DEL")
        )
    )
    
def get_company_id(row):
    """
    Priority:
    1. co id
    2. *Organization ID
    """

    co_id = normalize_id(row.get("co id", ""))

    if co_id:
        return co_id

    org_id = normalize_id(row.get("*Organization ID", ""))

    if org_id:
        return org_id

    return ""

# --- Company Paid Thru ---
def get_company_paid_thru(row):

    co_id = get_company_id(row)

    paid_thru_raw = corp_paid_thru_map.get(co_id, "")

    if pd.notna(paid_thru_raw) and paid_thru_raw != "":
        try:
            dt = pd.to_datetime(paid_thru_raw, errors="coerce")

            if pd.notna(dt):
                return dt.strftime("%Y-%m-%d")

        except:
            pass

    return ""

# --- Company Cancel Date ---
def get_company_cancel_date(row):

    co_id = get_company_id(row)

    cancel_date_raw = corp_cancel_date_map.get(co_id, "")

    if pd.notna(cancel_date_raw) and cancel_date_raw != "":
        try:
            dt = pd.to_datetime(cancel_date_raw, errors="coerce")

            if pd.notna(dt):
                return dt.strftime("%Y-%m-%d")

        except:
            pass

    return ""
    
# --- Company Status ---
def get_company_status(row):


    co_id = get_company_id(row)

    status_raw = corp_status_map.get(co_id, "")

    if pd.notna(status_raw):
        return str(status_raw).strip().upper()

    return ""

# Apply columns
final_matched["Company paid thru"] = final_matched.apply(
    get_company_paid_thru,
    axis=1
)

final_matched["Company cancel date"] = final_matched.apply(
    get_company_cancel_date,
    axis=1
)
    
final_matched["Company status"] = final_matched.apply(
    get_company_status,
    axis=1
)
final_matched["Final Company ID"] = final_matched.apply(
    get_company_id,
    axis=1
)

# --- Step 4c: New Lead Status (updated to use lapsed_date computed as you requested) ---
today = pd.Timestamp.today().date()
cutoff = today + timedelta(days=75)
cutoff_date = cutoff # for comparing with lapsed_date

# --- Helper: check if phone number is international ---
def is_international_phone(row):
    """
    Detect if any phone number is international:
    - If phone starts with '+1' or '+(1' → domestic
    - If phone starts with '+' but not '+1' or '+(1' → international
    - Otherwise → domestic
    """
    phone_vals = [row.get("raw_work_phone", ""), row.get("raw_home_phone", "")]
    
    for val in phone_vals:
        val = str(val).strip()
        if not val or val.lower() in ["nan", "none"]:
            continue
        
        # Remove spaces for checking
        val_no_space = val.replace(" ", "")
        
        if val_no_space.startswith("+"):
            if val_no_space.startswith("+1") or val_no_space.startswith("+(1"):
                continue  # domestic
            else:
                return True  # international
    return False

def determine_new_lead_status(row):
    lead_status_raw = row.get("*Lead Status", "")
    lead_status = "" if pd.isna(lead_status_raw) else str(lead_status_raw).strip()

    processed_type_raw = row.get("Processed member type", "")
    processed_type = "" if pd.isna(processed_type_raw) else str(processed_type_raw).strip()

    members_type_raw = row.get("*Members Type", "")
    members_type = "" if pd.isna(members_type_raw) else str(members_type_raw).strip().upper()

    raw_member_type = str(row.get("member type", "")).strip().upper()
    # Normalize for comparison
    lead_status_check = lead_status.upper()
    processed_type_check = processed_type.upper()
    
    # --- NEW RULE: If RET, force "Refused"    
    if raw_member_type == "RET":
        return "Refused"

    # --- NEW RULE: If international phone, force "Not Relevant"
    relevant_statuses = {"LAPSED MEMBER","LAPSED DEL", "MEMBER", "RENEWED LAPSED", "RENEWED AST", "RENEWED APX", "NOT RELEVANT"}
    if is_international_phone(row) and lead_status_check in relevant_statuses :
        return "Not Relevant"


    # --- NEW RULE: If raw member type = NM or NDEL ---
    if raw_member_type in ["NM", "NDEL"] and lead_status_check in relevant_statuses:

        status_in_portal = str(row.get("status", "")).strip().upper()
        cancel_type = str(row.get("mbr type cancellation", "")).strip().upper()
        company_status = str(row.get("Company status", "")).strip().upper()

        # NEW:
        # NM + DEL cancellation + Active company
        if (
            raw_member_type == "NM"
            and cancel_type.startswith("DEL")
            and company_status == "A"
        ):
            return "Lapsed DEL"

        if raw_member_type == "NDEL":
            if status_in_portal == "I":
                return "Lapsed Member"
            else:
                return "Not Relevant"

        return "Lapsed Member"
        
    # --- compute lapsed_date using the later of "Company paid thru" or "paid thru"
    paid_thru_corp_raw = row.get("Company paid thru", "")
    paid_thru_raw_val = row.get("paid thru", "")

    # convert both to datetime
    dt_corp = pd.to_datetime(paid_thru_corp_raw, errors="coerce")
    dt_raw  = pd.to_datetime(paid_thru_raw_val, errors="coerce")

    # take the later date
    if pd.notna(dt_corp) and pd.notna(dt_raw):
        paid_thru_final = max(dt_corp, dt_raw)
    elif pd.notna(dt_corp):
        paid_thru_final = dt_corp
    else:
        paid_thru_final = dt_raw

    paid_thru_date = _to_date_or_none(paid_thru_final)

    candidate = add_one_month_rollover(paid_thru_date)  # paid_thru + 1 month (with overflow)
    lapsed_date = first_day_of_next_month(candidate)   # rounded to next month first day

    # --- Rule 1: If lead status = Lapsed AND lapsed_date is in the future (or beyond cutoff) -> treat as renewed ---
    # (keeps original intent: treat someone labeled "LAPSED MEMBER" but whose lapsed date hasn't arrived as renewed)
    if lead_status_check in ["LAPSED MEMBER"]  and pd.notna(paid_thru_date) and paid_thru_date > cutoff:
        if processed_type_check == "AST":
            return "Renewed AST"
        elif processed_type_check not in ["HON", "NDEL", "NM", "RET", "AST"]:
            return "Renewed Lapsed"

    # --- Rule 2: If not AST, lapsed_date older than today -> mark Lapsed ---
    if members_type != "AST" and lapsed_date is not None and lapsed_date < today:

        company_status = str(row.get("Company status", "")).strip().upper()

        # DEL with active company -> Lapsed DEL
        if raw_member_type == "DEL" and company_status == "A":
            return "Lapsed DEL"
        elif lead_status_check in ["REFUSED", "PARTNER"]:
            return lead_status

        return "Lapsed Member"          

        
    if members_type != "AST" and paid_thru_date is not None and paid_thru_date > today:
        if lead_status_check in ["LAPSED MEMBER"]:
            return "Not Relevant"    
        if lead_status_check in ["MEMBER"] and members_type != "CAND":
            return "Not Relevant"    
    # --- Default: keep the current lead status (empty instead of NaN) ---
    return lead_status

final_matched["new lead status"] = final_matched.apply(determine_new_lead_status, axis=1)

# --- Step 4d: New DO ID assignment (fixed: case-insensitive checks + width preservation) ---
def extract_prefix_number(do_id):
    """
    Return (prefix_upper, int_number, num_len) for strings like "AST00027" or "L25A00225".
    If no match, return (None, None, 0).
    """
    if pd.isna(do_id):
        return None, None, 0
    s = str(do_id).strip()
    if not s:
        return None, None, 0
    match = re.match(r"^([A-Za-z0-9]+?)(\d+)$", s, flags=re.IGNORECASE)
    if not match:
        return None, None, 0
    prefix = match.group(1).upper()
    num_str = match.group(2)
    try:
        num = int(num_str)
    except ValueError:
        return prefix, None, len(num_str)
    return prefix, num, len(num_str)

# prefixes we care about
prefixes = ["AST", "L25A", "L25C", "L25P", "L25O"]

# initialise maps
prefix_max = {p: 0 for p in prefixes}
prefix_width = {p: 5 for p in prefixes}  # default width if none found

# scan All_Contacts '*DO ID' column for current maxima and widths
if "*DO ID" in ac.columns:
    for do_id in ac["*DO ID"].dropna().astype(str):
        prefix, num, num_len = extract_prefix_number(do_id)
        if prefix in prefix_max and num is not None:
            prefix_max[prefix] = max(prefix_max[prefix], num)
            prefix_width[prefix] = max(prefix_width[prefix], num_len)

# counters start from the current max
counters = prefix_max.copy()

def get_next_id(prefix):
    # ensure prefix known
    if prefix not in counters:
        counters[prefix] = 0
        prefix_width[prefix] = prefix_width.get(prefix, 5)
    counters[prefix] += 1
    width = prefix_width.get(prefix, 5)
    return f"{prefix}{counters[prefix]:0{width}d}"

# normalized helper
def norm(val):
    if pd.isna(val):
        return ""
    return str(val).upper().strip()

def assign_new_do_id(row):
    processed_type_norm = norm(row.get("Processed member type", ""))
    new_lead_status_norm = norm(row.get("new lead status", ""))
    current_do_id_raw = "" if pd.isna(row.get("*DO ID", "")) else str(row.get("*DO ID", "")).strip()
    current_do_id_norm = current_do_id_raw.upper().strip()

    memberish_statuses = {"MEMBER", "NOT RELEVANT", "RENEWED AST", "RENEWED LAPSED", "RENEWED APX"}
    active_ast_statuses = {"MEMBER", "RENEWED APX", "RENEWED AST", "RENEWED LAPSED"}

    # 1) If CORP/PRO/CAND and member-ish statuses -> leave New DO ID empty
    if processed_type_norm in {"CORP", "PRO", "CAND"} and new_lead_status_norm in memberish_statuses:
        return ""

    # 2) AST & active statuses -> keep existing AST DO ID if already AST*, else assign next AST
    if processed_type_norm == "AST" and new_lead_status_norm in active_ast_statuses:
        if current_do_id_norm.startswith("AST"):
            return current_do_id_raw
        return get_next_id("AST")

    # 3) AST & Lapsed -> keep existing L25A if present, else assign next L25A
    if processed_type_norm == "AST" and new_lead_status_norm == "LAPSED MEMBER":
        if current_do_id_norm.startswith("L25A"):
            return current_do_id_raw
        return get_next_id("L25A")

    # 4) CAND & Lapsed -> keep existing L25C if present, else assign next L25C
    if processed_type_norm == "CAND" and new_lead_status_norm == "LAPSED MEMBER":
        if current_do_id_norm.startswith("L25C"):
            return current_do_id_raw
        return get_next_id("L25C")

    # 5) PRO & Lapsed -> keep existing L25P if present, else assign next L25P
    if processed_type_norm == "PRO" and new_lead_status_norm == "LAPSED MEMBER":
        if current_do_id_norm.startswith("L25P"):
            return current_do_id_raw
        return get_next_id("L25P")

    # 6) CORP & Lapsed -> keep existing L25O if present, else assign next L25O
    if processed_type_norm == "CORP" and new_lead_status_norm == "LAPSED MEMBER":
        if current_do_id_norm.startswith("L25O"):
            return current_do_id_raw
        return get_next_id("L25O")

    # 7) Default: keep existing DO ID if present, otherwise empty
    return current_do_id_raw if current_do_id_raw else ""

final_matched["New DO ID"] = final_matched.apply(assign_new_do_id, axis=1)


# --- Convert 'french' column to English/French labels ---
if "french" in final_matched.columns:
    final_matched["french"] = final_matched["french"].apply(
        lambda x: "French" if str(x).strip().upper() == "TRUE" else "English"
    )
    
# --- Step 4e: Compare new lead status vs original *Lead Status ---
final_matched["Compare new lead status"] = final_matched.apply(
    lambda row: str(row.get("new lead status", "")).strip().upper() ==
                str(row.get("*Lead Status", "")).strip().upper(),
    axis=1
)

# --- Step 4f: Export changed to Renewed Lapsed or Renewed AST ---
today_str = datetime.today().strftime("%y%m%d")

# Normalize both columns
final_matched["new lead status norm"] = final_matched["new lead status"].str.strip().str.upper()
final_matched["lead status norm"] = final_matched["*Lead Status"].fillna("").astype(str).str.strip().str.upper()

# Filter where new lead status changed to Renewed Lapsed
changed_to_lapsed = final_matched[
    (final_matched["new lead status norm"] == "RENEWED LAPSED") &
    (~final_matched["lead status norm"].isin(["RENEWED LAPSED", "RENEWED AST"]))
]

# Filter where new lead status changed to Renewed AST
changed_to_ast = final_matched[
    (final_matched["new lead status norm"] == "RENEWED AST") &
    (~final_matched["lead status norm"].isin(["RENEWED LAPSED", "RENEWED AST"]))
]

# Save to CSV
if not changed_to_lapsed.empty:
    changed_to_lapsed.to_csv(base / f"{today_str} Renewed Lapsed.csv", index=False, encoding="utf-8-sig")

if not changed_to_ast.empty:
    changed_to_ast.to_csv(base / f"{today_str} Renewed AST.csv", index=False, encoding="utf-8-sig")    
    
# --- Step 4g: Count number of passed courses ---
course_cols = ["pcl status", "pf1 status", "pf2 status", "intro to accounting status"]

# Ensure all exist (in case some columns are missing)
existing_courses = [c for c in course_cols if c in final_matched.columns]

def count_passed_courses(row):
    return sum(str(row.get(col, "")).strip().upper() == "P" for col in existing_courses)

final_matched["Number of Passed Courses"] = final_matched.apply(count_passed_courses, axis=1)


# --- Step 5: Save output ---
final_matched.to_csv(base / "Contacts_Matched.csv", index=False, encoding="utf-8-sig")


print(f"Done! {len(final_matched)} contacts from contacts.csv matched in All_Contacts.csv")
print("File saved: Contacts_Matched.csv")