""" Parse uploaded files: - XLSX → list of CandidateData (same column schema as Sample_Candidate_Data.xlsx) - PDF → raw text for JD parsing """ import ast import re import uuid import logging from io import BytesIO from typing import Optional import pandas as pd import fitz # PyMuPDF from app.models.domain import CandidateData, WorkExperience logger = logging.getLogger(__name__) # ─── Constants ──────────────────────────────────────────────────────────────── REQUIRED_COLUMNS = { "name", "parsed_skills", "parsed_summary", "parsed_work_experience", "parsed_metadata_calculated_years_experience", "engineer_type", "current_position", } ENGINEER_TYPE_MAP = { "ai engineer": "AI Engineer", "ai": "AI Engineer", "ml engineer": "AI Engineer", "machine learning engineer": "AI Engineer", "backend engineer": "Backend Engineer", "backend": "Backend Engineer", "be": "Backend Engineer", "frontend engineer": "Frontend Engineer", "frontend": "Frontend Engineer", "fe": "Frontend Engineer", "full stack engineer": "Full Stack Engineer", "fullstack": "Full Stack Engineer", "full stack": "Full Stack Engineer", "software engineer": "Software Engineer", "sde": "Software Engineer", "data engineer": "Data Engineer", "devops engineer": "DevOps Engineer", "devops": "DevOps Engineer", } RECENT_EXP_MAP = { "companytype.product": "Product Company", "companytype.service": "Service Company", "a company that build its own technology products": "Product Company", "product": "Product Company", "service": "Service Company", "both": "Both", } GEN_AI_POSITIVE = { "6 months - 1 year", "1 to 1.5 years", "1.5 years - 2 years", "2+ years", "only internship/project experience", } GEN_AI_NEGATIVE = {"i do not have gen ai experience"} # ─── XLSX Parsing ───────────────────────────────────────────────────────────── def parse_xlsx(file_bytes: bytes) -> tuple[list[CandidateData], dict[str, int]]: """Return (candidates, engineer_type_distribution).""" df = pd.read_excel(BytesIO(file_bytes)) df.columns = [c.strip().lower().replace(" ", "_") for c in df.columns] missing = REQUIRED_COLUMNS - set(df.columns) if missing: raise ValueError(f"XLSX missing required columns: {missing}") candidates: list[CandidateData] = [] for idx, row in df.iterrows(): try: candidate = _parse_row(row, idx) if candidate: candidates.append(candidate) except Exception as e: logger.warning(f"Skipping row {idx}: {e}") dist: dict[str, int] = {} for c in candidates: dist[c.engineer_type] = dist.get(c.engineer_type, 0) + 1 return candidates, dist def _parse_row(row: pd.Series, idx: int) -> Optional[CandidateData]: def get(col: str, default=None): v = row.get(col, default) if v is None or (isinstance(v, float) and pd.isna(v)): return default return v # ID candidate_id = str(get("id") or get("submission_id") or f"cand_{idx}") # Years yoe = _parse_float(get("parsed_metadata_calculated_years_experience")) if yoe is None: yoe = _parse_float(get("years_of_experience")) if yoe is None: yoe = -1.0 # Engineer type raw_type = str(get("engineer_type", "")).strip() engineer_type = ENGINEER_TYPE_MAP.get(raw_type.lower(), raw_type or "Unknown") # Work experience raw_we = str(get("parsed_work_experience", "") or "") work_experiences, we_text = _parse_work_experience(raw_we, candidate_id) # Gen AI gen_ai_raw = get("gen_ai_experience") gen_ai = str(gen_ai_raw).strip() if gen_ai_raw else None # Active looking active_raw = get("is_actively_or_passively_looking") if active_raw is not None: active = str(active_raw).strip().lower() not in ("false", "no", "0", "nan", "none") else: active = None flags: list[str] = [] if yoe == -1.0: flags.append("years_unknown") if not raw_we or raw_we == "nan": flags.append("work_exp_unavailable") if engineer_type == "Unknown": flags.append("engineer_type_unknown") return CandidateData( id=candidate_id, name=str(get("name", f"Candidate_{idx}")), current_position=str(get("current_position", "")), current_title=get("current_title"), years_of_experience=yoe, engineer_type=engineer_type, gen_ai_experience=gen_ai, parsed_skills=str(get("parsed_skills", "") or ""), parsed_summary=str(get("parsed_summary", "") or ""), parsed_work_experience_raw=raw_we, parsed_work_experience_text=we_text, work_experiences=work_experiences, extracted_skills=[], # filled by skill_parser most_recent_company=get("parsed_metadata_most_recent_company"), notice_period=get("notice_period"), is_actively_looking=active, flags=flags, penalties=[], ) def _parse_work_experience(raw: str, cand_id: str) -> tuple[list[WorkExperience], str]: if not raw or raw.strip() in ("", "nan", "None"): return [], "" entries: list[WorkExperience] = [] try: parsed = ast.literal_eval("[" + raw + "]") for item in parsed: if isinstance(item, dict): entries.append(WorkExperience( company=str(item.get("company", "")), role=str(item.get("role", "")), duration=str(item.get("duration", "")), description=str(item.get("description", "")), )) except Exception: logger.debug(f"ast.literal_eval failed for {cand_id}, using raw text") entries.append(WorkExperience( company="", role="", duration="", description=raw[:1000], )) text_parts = [] for e in entries: text_parts.append( f"{e.role} at {e.company} ({e.duration}): {e.description}" ) return entries, " | ".join(text_parts) def _parse_float(val) -> Optional[float]: if val is None: return None try: f = float(str(val).strip()) return f if f >= 0 else None except (ValueError, TypeError): return None # ─── PDF Parsing ────────────────────────────────────────────────────────────── def parse_pdf(file_bytes: bytes) -> str: """Extract full text from PDF, preserving newlines.""" text_parts: list[str] = [] try: doc = fitz.open(stream=file_bytes, filetype="pdf") for page in doc: text_parts.append(page.get_text("text")) doc.close() except Exception as e: raise ValueError(f"Failed to read PDF: {e}") text = "\n".join(text_parts) # Collapse 3+ blank lines to 2 text = re.sub(r"\n{3,}", "\n\n", text) return text.strip()