server.py

"""Job Apply Assistant - a local, privacy-first job search web app.

Run it:
    python server.py
Then open http://127.0.0.1:8000 in your browser.

The app uses the standard library plus optional pypdf resume text extraction.

What it does
------------
* Search real jobs via JSearch with exact country and student-focused filters.
    Free no-key sources are used as a fallback.
* Store your resume + basic profile locally under ./data (never uploaded anywhere
  except when *you* open an employer's application page yourself).
* Track which jobs you've applied to.

Honest note about "auto apply to everything"
--------------------------------------------
Final submission happens on each employer's own site/ATS. "Prepare & Open"
queues jobs and opens their real application pages for review. Browsers do not
allow a local web app to set a file-upload control, so resume upload and final
submission remain deliberate user actions.
"""

from http.server import BaseHTTPRequestHandler, ThreadingHTTPServer
from contextlib import contextmanager
from datetime import datetime, timezone
import base64
import csv
import ctypes
from ctypes import wintypes
import hashlib
import io
import json
import os
import re
import sqlite3
import threading
import time
import urllib.error
import urllib.parse
import urllib.request
import zipfile
from xml.etree import ElementTree

HOST = "127.0.0.1"
PORT = 8000

HERE = os.path.dirname(os.path.abspath(__file__))
STATIC_DIR = os.path.join(HERE, "static")
DATA_DIR = os.path.join(HERE, "data")
RESUME_DIR = os.path.join(DATA_DIR, "resume")
APPLICATIONS_FILE = os.path.join(DATA_DIR, "applications.json")
PROFILE_FILE = os.path.join(DATA_DIR, "profile.json")
CONFIG_FILE = os.path.join(DATA_DIR, "config.json")
DATABASE_FILE = os.path.join(DATA_DIR, "job_assistant.db")
RESUME_TEXT_FILE = os.path.join(DATA_DIR, "resume_text.txt")
ANSWERS_FILE = os.path.join(DATA_DIR, "answers.json")

REMOTIVE_JOBS = "https://remotive.com/api/remote-jobs"
REMOTIVE_CATEGORIES = "https://remotive.com/api/remote-jobs/categories"
JSEARCH_URL = "https://jsearch.p.rapidapi.com/search-v2"
JSEARCH_HOST = "jsearch.p.rapidapi.com"
HTTP_TIMEOUT = 20
USER_AGENT = "JobApplyAssistant/1.0 (personal use)"
MAX_UPLOAD = 8 * 1024 * 1024  # 8 MB resume cap
DISPLAY_LIMIT = 200           # max jobs sent to the browser per search
PREVIEW_LIMIT = 60            # jobs fetched just to suggest company names
JSEARCH_PAGES = 3             # pages returned by one JSearch HTTP call
JSEARCH_SG_PAGES = 1          # extra Singapore-targeted page, merged on top
JOBS_CACHE_TTL = 6 * 3600     # reuse identical searches for 6h = 0 API requests
HOME_COUNTRY = "singapore"    # jobs here are sorted to the top
JOBICY_URL = "https://jobicy.com/api/v2/remote-jobs"
ARBEITNOW_URL = "https://www.arbeitnow.com/api/job-board-api"
# Singapore's official government job portal: no key, no quota, ~0.2s responses.
MCF_SEARCH_URL = "https://api.mycareersfuture.gov.sg/v2/search"
MCF_PAGE_SIZE = 100           # results per MyCareersFuture request
MCF_PAGES = 3                 # up to 300 Singapore jobs per search
MCF_PREVIEW_SIZE = 40         # smaller page for company-name suggestions
CACHE_VERSION = "2026-08-04-v3"
MAX_DESCRIPTION = 6000
MAX_QUESTION_LEN = 400
MAX_ANSWER_LEN = 4000
MAX_ANSWERS = 500
ANSWER_MATCH_THRESHOLD = 0.6   # token overlap needed to reuse a saved answer

# Posting-quality heuristics. These raise flags for review - never a verdict.
AGENCY_NAME_HINTS = (
    "recruit", "hr advisory", "manpower", "staffing", "employment agency",
    "search & selection", "hr solutions", "personnel", "outsourcing",
    "hr consult", "talent acquisition", "headhunt",
)
BATCH_CODE_RE = re.compile(r"^\s*\d{3,6}\s*[-\u2013\u2014:]\s*")
SALARY_NOTE_RE = re.compile(r"\s*[\(\[][^)\]]*(?:up to|from|\$|salary)[^)\]]*[\)\]]", re.I)
AGENCY_FLOOD_THRESHOLD = 4     # postings from one employer within a single result set
MIN_PLAUSIBLE_MONTHLY_SGD = 900
WIDE_SALARY_RATIO = 3.0
REPOST_MIN_TIMES = 3
REPOST_MIN_DAYS = 21
APPLICATION_STATUSES = {
    "saved", "queued", "opened", "submitted", "interview", "offer", "rejected"
}

COUNTRY_CODES = {
    "singapore": "sg", "sg": "sg",
    "malaysia": "my", "my": "my",
    "indonesia": "id", "id": "id",
    "philippines": "ph", "ph": "ph",
    "thailand": "th", "th": "th",
    "vietnam": "vn", "vn": "vn",
    "brunei": "bn", "bn": "bn",
    "cambodia": "kh", "kh": "kh",
    "india": "in", "in": "in",
    "china": "cn", "cn": "cn",
    "hong kong": "hk", "hk": "hk",
    "taiwan": "tw", "tw": "tw",
    "japan": "jp", "jp": "jp",
    "south korea": "kr", "korea": "kr", "kr": "kr",
    "australia": "au", "au": "au",
    "new zealand": "nz", "nz": "nz",
    "united kingdom": "gb", "uk": "gb", "gb": "gb",
    "united states": "us", "usa": "us", "us": "us",
    "canada": "ca", "ca": "ca",
    "germany": "de", "de": "de",
    "france": "fr", "fr": "fr",
    "netherlands": "nl", "nl": "nl",
    "switzerland": "ch", "ch": "ch",
    "sweden": "se", "se": "se",
    "norway": "no", "no": "no",
    "denmark": "dk", "dk": "dk",
    "finland": "fi", "fi": "fi",
    "ireland": "ie", "ie": "ie",
    "united arab emirates": "ae", "uae": "ae", "ae": "ae",
    "saudi arabia": "sa", "sa": "sa",
}

RELATED_ROLE_FAMILIES = {
    "mechatronics": [
        "Mechatronics Engineer", "Robotics Engineer", "Automation Engineer",
        "Controls Engineer", "PLC Engineer", "Embedded Systems Engineer",
        "Mechanical Design Engineer", "Test Validation Engineer",
        "Manufacturing Engineer", "Field Service Engineer",
    ],
    "robotics": [
        "Robotics Engineer", "Mechatronics Engineer", "Automation Engineer",
        "Controls Engineer", "ROS Engineer", "Embedded Systems Engineer",
    ],
    "automation": [
        "Automation Engineer", "Controls Engineer", "PLC Engineer",
        "Mechatronics Engineer", "Manufacturing Engineer", "Process Engineer",
    ],
    "data scientist": [
        "Data Scientist", "Data Analyst", "Machine Learning Engineer",
        "AI Engineer", "Business Intelligence Analyst",
    ],
}

SKILL_ALIASES = {
    "PLC": ["plc", "programmable logic controller"],
    "MATLAB": ["matlab"],
    "Simulink": ["simulink"],
    "SolidWorks": ["solidworks"],
    "AutoCAD": ["autocad"],
    "Python": ["python"],
    "C++": ["c++", "cpp"],
    "C": [" c ", "c programming", "c/c++", "c and c++"],
    "C#": ["c#", "c sharp"],
    "ROS": ["robot operating system", " ros "],
    "Arduino": ["arduino"],
    "Raspberry Pi": ["raspberry pi"],
    "Embedded Systems": ["embedded system", "embedded software"],
    "Control Systems": ["control system", "controls engineering"],
    "Robotics": ["robotics", "robotic"],
    "Automation": ["automation", "automated systems"],
    "CAD": [" cad ", "computer-aided design", "computer aided design"],
    "CAM": [" cam ", "computer-aided manufacturing"],
    "LabVIEW": ["labview"],
    "Altium": ["altium"],
    "PCB Design": ["pcb", "printed circuit board"],
    "IoT": ["iot", "internet of things"],
    "Git": [" git ", "github", "gitlab"],
    "SQL": ["sql"],
    "TensorFlow": ["tensorflow"],
    "PyTorch": ["pytorch"],
}

CONTENT_TYPES = {
    ".html": "text/html; charset=utf-8",
    ".css": "text/css; charset=utf-8",
    ".js": "application/javascript; charset=utf-8",
    ".json": "application/json; charset=utf-8",
    ".svg": "image/svg+xml",
    ".ico": "image/x-icon",
    ".png": "image/png",
}

# Used only if the live job API can't be reached, so the app still demos.
SAMPLE_JOBS = [
    {"id": "s1", "title": "Frontend Engineer", "company": "Acme Corp",
     "category": "Software Development", "location": "Worldwide",
     "job_type": "full_time", "salary": "$90k - $120k",
     "url": "https://example.com/jobs/frontend-engineer", "date": "2026-07-01",
     "logo": ""},
    {"id": "s2", "title": "Data Analyst", "company": "Globex",
     "category": "Data Analysis", "location": "Europe",
     "job_type": "full_time", "salary": "",
     "url": "https://example.com/jobs/data-analyst", "date": "2026-06-28",
     "logo": ""},
    {"id": "s3", "title": "Product Marketing Manager", "company": "Initech",
     "category": "Marketing", "location": "USA / Canada",
     "job_type": "full_time", "salary": "$110k - $140k",
     "url": "https://example.com/jobs/pmm", "date": "2026-06-30",
     "logo": ""},
    {"id": "s4", "title": "Customer Support Specialist", "company": "Umbrella",
     "category": "Customer Service", "location": "Anywhere",
     "job_type": "part_time", "salary": "",
     "url": "https://example.com/jobs/support", "date": "2026-06-25",
     "logo": ""},
    {"id": "s5", "title": "DevOps Engineer", "company": "Hooli",
     "category": "DevOps / Sysadmin", "location": "Asia",
     "job_type": "contract", "salary": "$80k - $110k",
     "url": "https://example.com/jobs/devops", "date": "2026-07-02",
     "logo": ""},
    {"id": "s6", "title": "Mechatronics Engineer", "company": "RoboWorks",
     "category": "Engineering", "location": "Singapore",
     "job_type": "full_time", "salary": "",
     "url": "https://example.com/jobs/mechatronics-engineer", "date": "2026-07-03",
     "logo": ""},
    {"id": "s7", "title": "Robotics / Automation Engineer", "company": "AutoMate",
     "category": "Engineering", "location": "Germany",
     "job_type": "full_time", "salary": "",
     "url": "https://example.com/jobs/robotics-engineer", "date": "2026-07-04",
     "logo": ""},
]


# ---------------------------------------------------------------------------
# Storage helpers
# ---------------------------------------------------------------------------
def ensure_dirs():
    os.makedirs(RESUME_DIR, exist_ok=True)
    init_database()


def load_json(path, default):
    try:
        with open(path, "r", encoding="utf-8") as f:
            return json.load(f)
    except (FileNotFoundError, json.JSONDecodeError):
        return default


def save_json(path, obj):
    os.makedirs(os.path.dirname(path), exist_ok=True)
    tmp = path + ".tmp"
    with open(tmp, "w", encoding="utf-8") as f:
        json.dump(obj, f, indent=2)
    os.replace(tmp, path)


_DB_LOCK = threading.Lock()


def _db():
    conn = sqlite3.connect(DATABASE_FILE, timeout=15)
    conn.row_factory = sqlite3.Row
    return conn


@contextmanager
def db_session():
    """Serialize writes and always close SQLite handles (important on Windows)."""
    with _DB_LOCK:
        conn = _db()
        try:
            with conn:
                yield conn
        finally:
            conn.close()


def init_database():
    os.makedirs(DATA_DIR, exist_ok=True)
    with db_session() as conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS job_cache (
                cache_key TEXT PRIMARY KEY,
                created_at REAL NOT NULL,
                result_json TEXT NOT NULL
            )
        """)
        conn.execute("""
            CREATE TABLE IF NOT EXISTS api_usage (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                called_at REAL NOT NULL,
                endpoint TEXT NOT NULL,
                status INTEGER,
                remaining INTEGER,
                limit_count INTEGER,
                reset_value TEXT
            )
        """)
        conn.execute("CREATE INDEX IF NOT EXISTS api_usage_called_at ON api_usage(called_at)")
        conn.execute("""
            CREATE TABLE IF NOT EXISTS posting_history (
                fingerprint TEXT PRIMARY KEY,
                first_seen REAL NOT NULL,
                last_seen REAL NOT NULL,
                times INTEGER NOT NULL DEFAULT 1,
                last_post_id TEXT
            )
        """)


def _cache_key(payload):
    raw = CACHE_VERSION + "|" + json.dumps(payload, sort_keys=True, separators=(",", ":"))
    return hashlib.sha256(raw.encode("utf-8")).hexdigest()


def cache_get(key):
    now = time.time()
    with db_session() as conn:
        row = conn.execute(
            "SELECT created_at, result_json FROM job_cache WHERE cache_key = ?", (key,)
        ).fetchone()
        if not row:
            return None
        age = now - row["created_at"]
        if age >= JOBS_CACHE_TTL:
            conn.execute("DELETE FROM job_cache WHERE cache_key = ?", (key,))
            return None
        try:
            result = json.loads(row["result_json"])
        except json.JSONDecodeError:
            conn.execute("DELETE FROM job_cache WHERE cache_key = ?", (key,))
            return None
    result["cached"] = True
    result["cache_age_seconds"] = int(age)
    return result


def cache_set(key, result):
    stored = dict(result)
    stored.pop("cached", None)
    stored.pop("cache_age_seconds", None)
    with db_session() as conn:
        conn.execute(
            """INSERT INTO job_cache(cache_key, created_at, result_json)
               VALUES (?, ?, ?)
               ON CONFLICT(cache_key) DO UPDATE SET
                   created_at = excluded.created_at,
                   result_json = excluded.result_json""",
            (key, time.time(), json.dumps(stored, separators=(",", ":"))),
        )


def cache_clear():
    with db_session() as conn:
        count = conn.execute("SELECT COUNT(*) FROM job_cache").fetchone()[0]
        conn.execute("DELETE FROM job_cache")
    return count


def _header_int(headers, *names):
    for name in names:
        value = headers.get(name) if headers else None
        if value is not None:
            try:
                return int(value)
            except (TypeError, ValueError):
                pass
    return None


def record_api_usage(headers, status, endpoint="jsearch/search-v2"):
    remaining = _header_int(
        headers, "x-ratelimit-requests-remaining", "X-RateLimit-Requests-Remaining"
    )
    limit_count = _header_int(
        headers, "x-ratelimit-requests-limit", "X-RateLimit-Requests-Limit"
    )
    reset_value = ""
    if headers:
        reset_value = headers.get("x-ratelimit-requests-reset", "")
    with db_session() as conn:
        conn.execute(
            """INSERT INTO api_usage
               (called_at, endpoint, status, remaining, limit_count, reset_value)
               VALUES (?, ?, ?, ?, ?, ?)""",
            (time.time(), endpoint, status, remaining, limit_count, str(reset_value)),
        )
        conn.execute("DELETE FROM api_usage WHERE called_at < ?", (time.time() - 90 * 86400,))


def usage_summary():
    now = datetime.now(timezone.utc)
    month_start = datetime(now.year, now.month, 1, tzinfo=timezone.utc).timestamp()
    with db_session() as conn:
        latest = conn.execute(
            """SELECT remaining, limit_count, reset_value, called_at
               FROM api_usage ORDER BY id DESC LIMIT 1"""
        ).fetchone()
        calls = conn.execute(
            "SELECT COUNT(*) FROM api_usage WHERE called_at >= ?", (month_start,)
        ).fetchone()[0]
        cache_entries = conn.execute(
            "SELECT COUNT(*) FROM job_cache WHERE created_at >= ?",
            (time.time() - JOBS_CACHE_TTL,),
        ).fetchone()[0]
    return {
        "observed_calls_this_month": calls,
        "remaining": latest["remaining"] if latest else None,
        "limit": latest["limit_count"] if latest else None,
        "reset": latest["reset_value"] if latest else "",
        "last_call_at": (
            datetime.fromtimestamp(latest["called_at"], timezone.utc).isoformat()
            if latest else None
        ),
        "cache_entries": cache_entries,
        "cache_ttl_hours": JOBS_CACHE_TTL // 3600,
    }


class _DataBlob(ctypes.Structure):
    _fields_ = [("cbData", wintypes.DWORD),
                ("pbData", ctypes.POINTER(ctypes.c_byte))]


def _protect_secret(value):
    """Encrypt a secret for the current Windows user via DPAPI."""
    if os.name != "nt" or not value:
        return None
    raw = value.encode("utf-8")
    buffer = (ctypes.c_byte * len(raw)).from_buffer_copy(raw)
    source = _DataBlob(len(raw), ctypes.cast(buffer, ctypes.POINTER(ctypes.c_byte)))
    target = _DataBlob()
    ok = ctypes.windll.crypt32.CryptProtectData(
        ctypes.byref(source), "Job Apply Assistant", None, None, None, 0,
        ctypes.byref(target),
    )
    if not ok:
        raise ctypes.WinError()
    try:
        encrypted = ctypes.string_at(target.pbData, target.cbData)
        return base64.b64encode(encrypted).decode("ascii")
    finally:
        ctypes.windll.kernel32.LocalFree(target.pbData)


def _unprotect_secret(value):
    if os.name != "nt" or not value:
        return None
    encrypted = base64.b64decode(value)
    buffer = (ctypes.c_byte * len(encrypted)).from_buffer_copy(encrypted)
    source = _DataBlob(
        len(encrypted), ctypes.cast(buffer, ctypes.POINTER(ctypes.c_byte))
    )
    target = _DataBlob()
    ok = ctypes.windll.crypt32.CryptUnprotectData(
        ctypes.byref(source), None, None, None, None, 0, ctypes.byref(target)
    )
    if not ok:
        raise ctypes.WinError()
    try:
        return ctypes.string_at(target.pbData, target.cbData).decode("utf-8")
    finally:
        ctypes.windll.kernel32.LocalFree(target.pbData)


def save_rapidapi_key(key):
    cfg = load_json(CONFIG_FILE, {})
    key = (key or "").strip()
    cfg.pop("rapidapi_key", None)
    cfg.pop("rapidapi_key_protected", None)
    if key:
        protected = _protect_secret(key)
        if protected:
            cfg["rapidapi_key_protected"] = protected
        else:
            # Non-Windows fallback; prefer RAPIDAPI_KEY environment variable there.
            cfg["rapidapi_key"] = key
    save_json(CONFIG_FILE, cfg)
    cache_clear()


# ---------------------------------------------------------------------------
# Job source (Remotive)
# ---------------------------------------------------------------------------
def fetch_url(url, timeout=HTTP_TIMEOUT):
    req = urllib.request.Request(url, headers={"User-Agent": USER_AGENT})
    with urllib.request.urlopen(req, timeout=timeout) as resp:
        return json.loads(resp.read().decode("utf-8"))


def normalize_job(j):
    return {
        "id": j.get("id"),
        "title": j.get("title", ""),
        "company": j.get("company_name", ""),
        "category": j.get("category", ""),
        "location": j.get("candidate_required_location", "") or "Anywhere",
        "job_type": j.get("job_type", ""),
        "salary": j.get("salary", ""),
        "url": j.get("url", ""),
        "date": (j.get("publication_date", "") or "")[:10],
        "logo": j.get("company_logo", ""),
    }


def get_categories():
    try:
        data = fetch_url(REMOTIVE_CATEGORIES)
        cats = [
            {"name": c.get("name", ""), "slug": c.get("slug", "")}
            for c in data.get("jobs-categories", [])
            if c.get("slug")
        ]
        if cats:
            return cats
    except (urllib.error.URLError, json.JSONDecodeError, TimeoutError, OSError):
        pass
    # Fallback list of Remotive categories.
    return [
        {"name": "Software Development", "slug": "software-dev"},
        {"name": "Customer Service", "slug": "customer-support"},
        {"name": "Design", "slug": "design"},
        {"name": "Marketing", "slug": "marketing"},
        {"name": "Sales / Business", "slug": "sales"},
        {"name": "Product", "slug": "product"},
        {"name": "Business", "slug": "business"},
        {"name": "Data Analysis", "slug": "data"},
        {"name": "DevOps / Sysadmin", "slug": "devops"},
        {"name": "Finance / Legal", "slug": "finance-legal"},
        {"name": "Human Resources", "slug": "hr"},
        {"name": "Quality Assurance", "slug": "qa"},
        {"name": "Writing", "slug": "writing"},
        {"name": "Project Management", "slug": "project-management"},
        {"name": "All others", "slug": "all-others"},
    ]


# Map our industry dropdown values to search keywords for JSearch.
INDUSTRY_KEYWORDS = {
    "engineering": "engineer",
    "software-dev": "software developer",
    "data": "data analyst",
    "design": "designer",
    "marketing": "marketing",
    "sales": "sales",
    "product": "product manager",
    "devops": "devops engineer",
    "finance-legal": "finance",
    "hr": "human resources",
    "qa": "qa engineer",
    "customer-support": "customer support",
    "writing": "writer",
    "project-management": "project manager",
    "business": "business",
    "all-others": "",
}


def industry_keyword(industry):
    if not industry:
        return ""
    if industry in INDUSTRY_KEYWORDS:
        return INDUSTRY_KEYWORDS[industry]
    return industry.replace("-", " ")


def get_rapidapi_key():
    env = os.environ.get("RAPIDAPI_KEY", "").strip()
    if env:
        return env
    cfg = load_json(CONFIG_FILE, {})
    protected = str(cfg.get("rapidapi_key_protected", "")).strip()
    if protected:
        try:
            return (_unprotect_secret(protected) or "").strip()
        except (ValueError, OSError):
            return ""
    plain = str(cfg.get("rapidapi_key", "")).strip()
    if plain and os.name == "nt":
        # One-time migration from the old plaintext config format.
        try:
            save_rapidapi_key(plain)
        except OSError:
            pass
    return plain


def normalize_jsearch(j):
    city = j.get("job_city") or ""
    country = j.get("job_country") or ""
    loc = j.get("job_location") or ", ".join(p for p in (city, country) if p)
    if not loc:
        loc = "Remote" if j.get("job_is_remote") else "N/A"
    lo, hi = j.get("job_min_salary"), j.get("job_max_salary")
    if lo and hi:
        salary = f"${int(lo):,} - ${int(hi):,}"
    elif lo or hi:
        salary = f"${int(lo or hi):,}"
    else:
        salary = ""
    etype = (j.get("job_employment_type") or "").replace("_", " ").title()
    # Prefer the LinkedIn apply link when JSearch lists one for this job.
    url = j.get("job_apply_link", "") or ""
    publisher = j.get("job_publisher", "") or ""
    for opt in (j.get("apply_options") or []):
        if "linkedin" in (opt.get("publisher", "") or "").lower():
            url = opt.get("apply_link") or url
            publisher = opt.get("publisher") or publisher
            break
    return {
        "id": j.get("job_id"),
        "title": j.get("job_title", "") or "",
        "company": j.get("employer_name", "") or "",
        "category": etype,
        "location": loc,
        "job_type": etype,
        "salary": salary,
        "url": url,
        "publisher": publisher,
        "date": (j.get("job_posted_at_datetime_utc", "") or "")[:10],
        "logo": j.get("employer_logo", "") or "",
        "country_code": country.upper(),
        "city": city,
        "seniority": j.get("seniority_level") or "",
        "experience_years": j.get("required_experience_years"),
        "work_arrangement": (
            j.get("work_arrangement")
            or ("remote" if j.get("job_is_remote") else "")
        ),
        "visa_sponsorship": j.get("visa_sponsorship"),
        "education": j.get("education_required") or {},
        "required_skills": j.get("required_technologies") or [],
        "preferred_skills": j.get("preferred_technologies") or [],
        "description": (j.get("job_description") or "")[:MAX_DESCRIPTION],
    }


def country_code_for(location):
    value = (location or "").strip().lower()
    return COUNTRY_CODES.get(value, "")


def expanded_role_query(role, industry, include_related):
    base = role.strip() if role else industry_keyword(industry)
    if not include_related:
        return base
    lowered = base.lower()
    family = None
    for trigger, roles in RELATED_ROLE_FAMILIES.items():
        if trigger in lowered:
            family = roles
            break
    if family is None and industry == "engineering" and lowered in {"", "engineer"}:
        family = RELATED_ROLE_FAMILIES["mechatronics"]
    if not family:
        return base
    return "(" + " OR ".join(f'\"{item}\"' for item in family) + ")"


def fetch_jsearch(role, industry, company, location, key, pages=1, options=None):
    options = options or {}
    base = expanded_role_query(role, industry, options.get("related_roles", False))
    terms = [t for t in (base, company.strip() if company else "") if t]
    query = " ".join(terms).strip() or "jobs"
    if location:
        query = f"{query} in {location.strip()}"
    params = {
        "query": query,
        "num_pages": str(pages),
        "language": "en",
    }
    country_code = country_code_for(location)
    if country_code:
        params["country"] = country_code
    date_posted = options.get("date_posted", "all")
    if date_posted in {"today", "3days", "week", "month"}:
        params["date_posted"] = date_posted
    url = JSEARCH_URL + "?" + urllib.parse.urlencode(params)
    req = urllib.request.Request(url, headers={
        "X-RapidAPI-Key": key,
        "X-RapidAPI-Host": JSEARCH_HOST,
        "User-Agent": USER_AGENT,
    })
    try:
        with urllib.request.urlopen(req, timeout=HTTP_TIMEOUT) as resp:
            raw = resp.read()
            record_api_usage(resp.headers, resp.status)
    except urllib.error.HTTPError as exc:
        record_api_usage(exc.headers, exc.code)
        raise
    data = json.loads(raw.decode("utf-8"))
    # /search-v2 nests jobs under data.jobs; guard for the older list shape too.
    payload = data.get("data", {})
    items = payload.get("jobs", []) if isinstance(payload, dict) else payload
    return [normalize_jsearch(j) for j in items if isinstance(j, dict)]


def _dedupe(jobs):
    seen, out = set(), []
    for j in jobs:
        key = (j.get("url") or "").strip().lower()
        if not key:
            key = (j.get("title", "") + "|" + j.get("company", "")).lower()
        if key in seen:
            continue
        seen.add(key)
        out.append(j)
    return out


def _sg_first(jobs):
    # Stable sort: Singapore-located jobs first, everything else after.
    return sorted(
        jobs,
        key=lambda j: 0 if HOME_COUNTRY in (j.get("location", "") or "").lower() else 1,
    )


def _strip_html(text):
    return re.sub(r"\s+", " ", re.sub(r"<[^>]+>", " ", text or "")).strip()


# MyCareersFuture position levels that rule a job out for a student/new grad.
_MCF_SENIOR_LEVELS = ("senior", "management", "manager", "director", "head")


def normalize_mcf(j):
    meta = j.get("metadata") or {}
    company = j.get("postedCompany") or {}
    salary = j.get("salary") or {}
    low, high = salary.get("minimum"), salary.get("maximum")
    period = ((salary.get("type") or {}).get("salaryType") or "").lower()
    suffix = ""
    for prefix, unit in (("month", "/mo"), ("ann", "/yr"), ("year", "/yr"),
                         ("hour", "/hr"), ("week", "/wk"), ("dai", "/day"),
                         ("day", "/day")):
        if period.startswith(prefix):
            suffix = unit
            break
    if low and high:
        pay = f"S${int(low):,} - S${int(high):,}{suffix}"
    elif low or high:
        pay = f"S${int(low or high):,}{suffix}"
    else:
        pay = ""

    levels = ", ".join(
        lvl.get("position") or "" for lvl in (j.get("positionLevels") or [])
    ).strip(", ")
    seniority = "senior" if any(k in levels.lower() for k in _MCF_SENIOR_LEVELS) else "entry"

    districts = [
        d.get("region") or d.get("location") or ""
        for d in ((j.get("address") or {}).get("districts") or [])
    ]
    area = ", ".join(dict.fromkeys(p for p in districts if p)) or "Singapore"
    location = area if "singapore" in area.lower() else f"{area}, Singapore"

    etype = ", ".join(
        t.get("employmentType") or "" for t in (j.get("employmentTypes") or [])
    ).strip(", ")
    arrangement = ", ".join(
        w.get("flexibleWorkArrangement") or ""
        for w in (j.get("flexibleWorkArrangements") or [])
    ).strip(", ").lower()

    return {
        "id": meta.get("jobPostId") or "",
        "title": j.get("title", "") or "",
        "company": company.get("name", "") or "",
        "category": etype,
        "location": location,
        "job_type": etype,
        "salary": pay,
        "salary_min": low or None,
        "salary_max": high or None,
        "salary_period": period,
        "url": meta.get("jobDetailsUrl", "") or "",
        "publisher": "MyCareersFuture",
        "date": (meta.get("newPostingDate") or meta.get("updatedAt") or "")[:10],
        "logo": company.get("logoUploadPath", "") or "",
        "country_code": "SG",
        "city": area,
        # MCF states outright when an agency posts for an undisclosed employer.
        "posted_on_behalf": bool(meta.get("isPostedOnBehalf")),
        "employer_hidden": bool(meta.get("isHideHiringEmployerName")),
        "seniority": seniority,
        "experience_years": j.get("minimumYearsExperience"),
        "work_arrangement": arrangement,
        "visa_sponsorship": None,
        "education": {},
        # MCF tags every posting with structured skills - feeds resume matching.
        "required_skills": [
            s.get("skill") for s in (j.get("skills") or []) if s.get("skill")
        ],
        "preferred_skills": [],
        "description": _strip_html(j.get("description", ""))[:MAX_DESCRIPTION],
    }


def targets_singapore(location):
    value = (location or "").strip().lower()
    return not value or HOME_COUNTRY in value


def fetch_mycareersfuture(role, industry, company, options=None, pages=1,
                          page_size=MCF_PAGE_SIZE):
    options = options or {}
    terms = [role.strip() if role else industry_keyword(industry)]
    if company:
        terms.append(company.strip())
    search = " ".join(t for t in terms if t).strip() or "engineer"
    jobs = []
    for page in range(max(1, pages)):
        url = f"{MCF_SEARCH_URL}?limit={page_size}&page={page}"
        body = json.dumps({
            "search": search,
            "sessionId": "",
            "sortBy": ["new_posting_date"],
        }).encode("utf-8")
        req = urllib.request.Request(url, data=body, method="POST", headers={
            "Content-Type": "application/json",
            "Accept": "application/json",
            "User-Agent": USER_AGENT,
        })
        with urllib.request.urlopen(req, timeout=HTTP_TIMEOUT) as resp:
            data = json.loads(resp.read().decode("utf-8", "replace"))
        results = [r for r in (data.get("results") or []) if isinstance(r, dict)]
        jobs.extend(normalize_mcf(r) for r in results)
        if len(results) < page_size:
            break
    return jobs


def fetch_remotive(industry, role):
    params = {"limit": 200}
    if industry and industry != "engineering":
        params["category"] = industry
    search = role or ("engineer" if industry == "engineering" else "")
    if search:
        params["search"] = search
    url = REMOTIVE_JOBS + "?" + urllib.parse.urlencode(params)
    data = fetch_url(url)
    return [normalize_job(j) for j in data.get("jobs", [])]


def fetch_jobicy(role):
    params = {"count": 50}
    if role:
        params["tag"] = role
    url = JOBICY_URL + "?" + urllib.parse.urlencode(params)
    data = fetch_url(url, timeout=12)
    out = []
    for j in data.get("jobs", []):
        types = j.get("jobType") or []
        industries = j.get("jobIndustry") or []
        out.append({
            "id": j.get("id"),
            "title": j.get("jobTitle", "") or "",
            "company": j.get("companyName", "") or "",
            "category": industries[0] if industries else "",
            "location": j.get("jobGeo", "") or "Remote",
            "job_type": types[0] if types else "",
            "salary": "",
            "url": j.get("url", "") or "",
            "date": (j.get("pubDate", "") or "")[:10],
            "logo": j.get("companyLogo", "") or "",
        })
    return out


def fetch_arbeitnow(role):
    data = fetch_url(ARBEITNOW_URL, timeout=12)
    out = []
    for j in data.get("data", []):
        types = j.get("job_types") or []
        tags = j.get("tags") or []
        date = ""
        ts = j.get("created_at")
        if ts:
            try:
                date = datetime.fromtimestamp(int(ts), tz=timezone.utc).date().isoformat()
            except (ValueError, OSError, OverflowError):
                date = ""
        out.append({
            "id": j.get("slug"),
            "title": j.get("title", "") or "",
            "company": j.get("company_name", "") or "",
            "category": tags[0] if tags else "",
            "location": j.get("location", "") or ("Remote" if j.get("remote") else ""),
            "job_type": types[0] if types else "",
            "salary": "",
            "url": j.get("url", "") or "",
            "date": date,
            "logo": "",
        })
    if role:
        r = role.lower()
        out = [j for j in out if r in j["title"].lower()]
    return out


NET_ERRORS = (urllib.error.URLError, json.JSONDecodeError, TimeoutError, OSError)

SENIOR_TITLE_RE = re.compile(
    r"\b(?:senior|sr\.?|lead|principal|staff|manager|director|head|chief|vp)\b",
    re.IGNORECASE,
)
ENTRY_TITLE_RE = re.compile(
    r"\b(?:intern|internship|graduate|junior|jr\.?|trainee|assistant|associate|entry)\b",
    re.IGNORECASE,
)


def extract_resume_text(path):
    ext = os.path.splitext(path)[1].lower()
    try:
        if ext == ".txt":
            with open(path, "r", encoding="utf-8", errors="ignore") as f:
                return f.read()
        if ext == ".docx":
            with zipfile.ZipFile(path) as archive:
                xml = archive.read("word/document.xml")
            root = ElementTree.fromstring(xml)
            return " ".join(
                node.text for node in root.iter()
                if node.tag.endswith("}t") and node.text
            )
        if ext == ".pdf":
            try:
                from pypdf import PdfReader
            except ImportError:
                return ""
            reader = PdfReader(path)
            return "\n".join((page.extract_text() or "") for page in reader.pages)
    except (OSError, ValueError, KeyError, zipfile.BadZipFile):
        return ""
    return ""


def candidate_text():
    profile = load_json(PROFILE_FILE, {})
    parts = [str(profile.get("skills", ""))]
    try:
        with open(RESUME_TEXT_FILE, "r", encoding="utf-8") as f:
            parts.append(f.read())
    except FileNotFoundError:
        pass
    return "\n".join(parts).strip()


def known_skills(text):
    haystack = " " + re.sub(r"\s+", " ", (text or "").lower()) + " "
    found = set()
    for canonical, aliases in SKILL_ALIASES.items():
        if any(alias.lower() in haystack for alias in aliases):
            found.add(canonical)
    return found


# Words that carry no meaning when telling two application questions apart.
_QUESTION_STOPWORDS = frozenset({
    "a", "an", "the", "is", "are", "was", "do", "does", "did", "you", "your",
    "yours", "please", "kindly", "enter", "provide", "tell", "us", "we", "i",
    "my", "me", "this", "that", "it", "to", "of", "for", "in", "on", "at",
    "and", "or", "if", "be", "been", "have", "has", "had", "will", "would",
    "can", "could", "may", "might", "should", "what", "which", "any", "about",
})


def question_key(text):
    """Canonical form of a question, used to match a page field to a saved answer."""
    lowered = (text or "").lower()
    lowered = re.sub(r"\(.*?\)", " ", lowered)
    lowered = re.sub(r"[^a-z0-9 ]+", " ", lowered)
    tokens = [t for t in lowered.split() if t and t not in _QUESTION_STOPWORDS]
    return " ".join(tokens)[:200]


def load_answers():
    stored = load_json(ANSWERS_FILE, {})
    answers = stored.get("answers") if isinstance(stored, dict) else None
    return [a for a in (answers or []) if isinstance(a, dict) and a.get("key")]


def save_answer_records(items):
    if not isinstance(items, list):
        raise ValueError("answers must be a list")
    answers = load_answers()
    index = {a["key"]: a for a in answers}
    order = [a["key"] for a in answers]
    now = datetime.now(timezone.utc).isoformat(timespec="seconds")
    saved = 0
    for item in items:
        if not isinstance(item, dict):
            continue
        question = str(item.get("question", "")).strip()[:MAX_QUESTION_LEN]
        answer = str(item.get("answer", "")).strip()[:MAX_ANSWER_LEN]
        key = question_key(question)
        if not key or not answer:
            continue
        record = index.get(key)
        if record is None:
            record = {"key": key, "created": now}
            order.append(key)
        record["question"] = question
        record["answer"] = answer
        record["field_type"] = str(item.get("field_type", ""))[:40]
        record["updated"] = now
        index[key] = record
        saved += 1
    # Newest first, capped, so the file cannot grow without bound.
    kept = [index[k] for k in order if k in index][-MAX_ANSWERS:]
    save_json(ANSWERS_FILE, {"answers": kept})
    return saved, kept


def delete_answers(keys):
    if not isinstance(keys, list):
        raise ValueError("keys must be a list")
    drop = {str(k) for k in keys}
    kept = [a for a in load_answers() if a["key"] not in drop]
    save_json(ANSWERS_FILE, {"answers": kept})
    return kept


def find_answer(question, answers):
    """Best saved answer for `question`, or None when nothing is close enough."""
    key = question_key(question)
    if not key:
        return None
    tokens = set(key.split())
    best, best_score = None, 0.0
    for record in answers:
        saved_key = record.get("key") or ""
        if saved_key == key:
            return record
        other = set(saved_key.split())
        if not tokens or not other:
            continue
        score = len(tokens & other) / len(tokens | other)
        if score > best_score:
            best, best_score = record, score
    return best if best_score >= ANSWER_MATCH_THRESHOLD else None


def match_answers(questions):
    answers = load_answers()
    matches = {}
    for question in questions or []:
        if not isinstance(question, str):
            continue
        record = find_answer(question, answers)
        if record:
            matches[question] = record["answer"]
    return matches


_RESUME_HEADINGS = frozenset({
    "summary", "objective", "profile", "about", "about me",
    "experience", "work experience", "professional experience", "employment",
    "employment history", "education", "skills", "technical skills",
    "core skills", "key skills", "projects", "personal projects",
    "certifications", "certificates", "achievements", "awards", "activities",
    "publications", "languages", "interests", "references", "training",
    "internship", "internships", "volunteer", "leadership",
})

_BULLET_PREFIX = re.compile(r"^\s*[\u2022\u25cf\u25aa\u25e6\u00b7\u2013\u2014\-\*>]+\s*")

_JD_STOPWORDS = frozenset({
    "the", "and", "for", "with", "you", "our", "are", "will", "have", "has",
    "this", "that", "from", "your", "their", "they", "who", "all", "any",
    "job", "role", "work", "working", "team", "teams", "company", "candidate",
    "candidates", "applicant", "responsibilities", "requirements", "required",
    "preferred", "experience", "years", "year", "ability", "able", "strong",
    "good", "excellent", "including", "such", "other", "more", "must", "should",
    "would", "can", "may", "well", "also", "etc", "per", "within", "across",
    "using", "use", "used", "new", "provide", "ensure", "support", "related",
})


def _is_resume_heading(line):
    stripped = line.strip().strip(":").strip()
    if not stripped or len(stripped) > 60:
        return False
    lowered = stripped.lower()
    # Matches "Skills" as well as "Selected Projects" and "Certifications & Awards".
    if len(stripped) <= 45 and any(
        lowered == head or lowered.startswith(head + " ") or lowered.endswith(" " + head)
        for head in _RESUME_HEADINGS
    ):
        return True
    letters = [c for c in stripped if c.isalpha()]
    # Short ALL-CAPS lines are almost always section headings in a resume.
    return bool(letters) and len(letters) > 2 and len(stripped) <= 40 and stripped == stripped.upper()


def parse_resume_blocks(text):
    sections, current = [], {"heading": "Header", "bullets": []}
    for raw in (text or "").splitlines():
        line = raw.strip()
        if not line:
            continue
        if _is_resume_heading(line):
            if current["bullets"]:
                sections.append(current)
            current = {"heading": line.strip(":").strip(), "bullets": []}
            continue
        bulleted = bool(_BULLET_PREFIX.match(line))
        cleaned = _BULLET_PREFIX.sub("", line).strip()
        if len(cleaned) < 3:
            continue
        # PDF extraction yields one entry per visual line, so stitch wrapped
        # continuations back onto the line they belong to.
        previous = current["bullets"][-1] if current["bullets"] else ""
        if (
            not bulleted
            and previous
            and not previous.endswith((".", ";", "!", "?", ":"))
            and cleaned[:1].islower()
        ):
            current["bullets"][-1] = (previous + " " + cleaned)[:600]
            continue
        current["bullets"].append(cleaned[:600])
    if current["bullets"]:
        sections.append(current)
    return sections


def job_keywords(job):
    """Canonical skills, high-signal phrases and title tokens for one posting."""
    phrases = [
        str(s).strip() for s in
        ((job.get("required_skills") or []) + (job.get("preferred_skills") or []))
        if str(s).strip()
    ]
    text = " ".join([
        job.get("title", "") or "",
        job.get("description", "") or "",
        " ".join(phrases),
    ])
    skills = known_skills(text)
    title_tokens = {
        t for t in re.findall(r"[a-z0-9+#]{3,}", (job.get("title", "") or "").lower())
        if t not in _JD_STOPWORDS
    }
    return skills, phrases, title_tokens


def score_bullet(bullet, skills, phrases, title_tokens):
    lowered = " " + bullet.lower() + " "
    hits, score = [], 0
    for skill in sorted(known_skills(bullet) & skills):
        score += 10
        hits.append(skill)
    for phrase in phrases:
        cleaned = phrase.lower().strip()
        if len(cleaned) > 3 and cleaned in lowered:
            score += 6
            hits.append(phrase)
    tokens = set(re.findall(r"[a-z0-9+#]{3,}", bullet.lower()))
    score += len(tokens & title_tokens) * 3
    # Stable, de-duplicated, and case-preserving.
    return score, list(dict.fromkeys(hits))


def tailor_resume(job):
    """Rank the candidate's own resume lines against one posting.

    Never invents content: it reorders what the resume already says and reports
    what the posting asks for that the resume does not evidence.
    """
    try:
        with open(RESUME_TEXT_FILE, "r", encoding="utf-8") as handle:
            resume_text = handle.read()
    except FileNotFoundError:
        resume_text = ""
    if not resume_text.strip():
        raise ValueError("Upload a resume in the app first - there is nothing to tailor.")

    profile = load_json(PROFILE_FILE, {})
    skills, phrases, title_tokens = job_keywords(job)
    haystack = (resume_text + " " + str(profile.get("skills", ""))).lower()

    sections = []
    for section in parse_resume_blocks(resume_text):
        scored = []
        for bullet in section["bullets"]:
            score, matched = score_bullet(bullet, skills, phrases, title_tokens)
            scored.append({"text": bullet, "score": score, "matched": matched})
        scored.sort(key=lambda item: -item["score"])
        sections.append({"heading": section["heading"], "bullets": scored})

    resume_skills = known_skills(haystack)
    matched_skills = sorted(skills & resume_skills)
    missing_skills = sorted(skills - resume_skills)
    missing_phrases = [
        phrase for phrase in dict.fromkeys(phrases)
        if phrase.lower() not in haystack
    ][:15]
    coverage = round(100 * len(matched_skills) / len(skills)) if skills else None

    return {
        "job_title": job.get("title", "") or "",
        "company": job.get("company", "") or "",
        "coverage": coverage,
        "matched_skills": matched_skills,
        "missing_skills": missing_skills,
        "missing_phrases": missing_phrases,
        "sections": sections,
        "evidenced": sum(
            1 for s in sections for b in s["bullets"] if b["score"] > 0
        ),
        "total_bullets": sum(len(s["bullets"]) for s in sections),
    }


def _xml_escape(text):
    return (str(text).replace("&", "&amp;").replace("<", "&lt;").replace(">", "&gt;"))


def build_docx(blocks):
    """Minimal but valid .docx from (kind, text) pairs, using only the stdlib."""
    styles = {
        "title": ('<w:b/><w:sz w:val="36"/>', '<w:spacing w:after="120"/>'),
        "heading": ('<w:b/><w:sz w:val="26"/>', '<w:spacing w:before="240" w:after="80"/>'),
        "para": ("", '<w:spacing w:after="80"/>'),
        "bullet": ("", '<w:ind w:left="360" w:hanging="180"/><w:spacing w:after="60"/>'),
    }
    paragraphs = []
    for kind, text in blocks:
        run_props, para_props = styles.get(kind, styles["para"])
        safe = _xml_escape("\u2022 " + text if kind == "bullet" else text)
        paragraphs.append(
            f"<w:p><w:pPr>{para_props}</w:pPr><w:r>"
            f"{'<w:rPr>' + run_props + '</w:rPr>' if run_props else ''}"
            f'<w:t xml:space="preserve">{safe}</w:t></w:r></w:p>'
        )
    document = (
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        '<w:document xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main">'
        "<w:body>" + "".join(paragraphs) +
        '<w:sectPr><w:pgSz w:w="11906" w:h="16838"/>'
        '<w:pgMar w:top="1134" w:right="1134" w:bottom="1134" w:left="1134"/>'
        "</w:sectPr></w:body></w:document>"
    )
    content_types = (
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">'
        '<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>'
        '<Default Extension="xml" ContentType="application/xml"/>'
        '<Override PartName="/word/document.xml" ContentType="application/vnd.openxmlformats-'
        'officedocument.wordprocessingml.document.main+xml"/></Types>'
    )
    rels = (
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
        '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/'
        'relationships/officeDocument" Target="word/document.xml"/></Relationships>'
    )
    buffer = io.BytesIO()
    with zipfile.ZipFile(buffer, "w", zipfile.ZIP_DEFLATED) as archive:
        archive.writestr("[Content_Types].xml", content_types)
        archive.writestr("_rels/.rels", rels)
        archive.writestr("word/document.xml", document)
    return buffer.getvalue()


def tailored_resume_docx(draft, keep=None):
    profile = load_json(PROFILE_FILE, {})
    contact = " | ".join(p for p in (
        profile.get("email", ""), profile.get("phone", ""),
        profile.get("linkedin", ""), profile.get("home_country", ""),
    ) if p)
    blocks = [("title", profile.get("name") or "Resume")]
    if contact:
        blocks.append(("para", contact))
    for section in draft.get("sections", []):
        bullets = [
            b for b in section.get("bullets", [])
            if keep is None or b["text"] in keep
        ]
        if not bullets:
            continue
        blocks.append(("heading", section.get("heading", "")))
        blocks.extend(("bullet", b["text"]) for b in bullets)
    return build_docx(blocks)


def normalize_posting_title(title):
    cleaned = SALARY_NOTE_RE.sub(" ", BATCH_CODE_RE.sub("", title or ""))
    cleaned = re.sub(r"[^a-z0-9 ]+", " ", cleaned.lower())
    return " ".join(cleaned.split())


def posting_fingerprint(job):
    company = (job.get("company") or "").strip().lower()
    raw = company + "|" + normalize_posting_title(job.get("title"))
    return hashlib.sha256(raw.encode("utf-8")).hexdigest()


def record_postings(jobs):
    """Remember each role we have seen, counting genuine re-postings."""
    rows = [
        (posting_fingerprint(job), str(job.get("id") or ""))
        for job in jobs if (job.get("title") or "").strip()
    ]
    if not rows:
        return
    now = time.time()
    with db_session() as conn:
        for fingerprint, post_id in rows:
            conn.execute(
                """
                INSERT INTO posting_history (fingerprint, first_seen, last_seen, times, last_post_id)
                VALUES (?, ?, ?, 1, ?)
                ON CONFLICT(fingerprint) DO UPDATE SET
                    last_seen = excluded.last_seen,
                    times = posting_history.times + (
                        CASE WHEN excluded.last_post_id <> '' AND
                                  excluded.last_post_id <> posting_history.last_post_id
                             THEN 1 ELSE 0 END
                    ),
                    last_post_id = CASE WHEN excluded.last_post_id <> ''
                                        THEN excluded.last_post_id
                                        ELSE posting_history.last_post_id END
                """,
                (fingerprint, now, now, post_id),
            )


def posting_history(fingerprints):
    unique = list(dict.fromkeys(f for f in fingerprints if f))
    if not unique:
        return {}
    found = {}
    with db_session() as conn:
        for start in range(0, len(unique), 400):
            chunk = unique[start:start + 400]
            placeholders = ",".join("?" * len(chunk))
            for row in conn.execute(
                f"SELECT fingerprint, first_seen, times FROM posting_history "
                f"WHERE fingerprint IN ({placeholders})", chunk
            ):
                found[row[0]] = {"first_seen": row[1], "times": row[2]}
    return found


def salary_verdict(job, target):
    if not target:
        return ""
    if not (job.get("salary_period") or "").startswith("month"):
        return ""
    low, high = job.get("salary_min"), job.get("salary_max")
    if not (low or high):
        return ""
    if (high or low) < target:
        return "below"
    return "above" if (low or high) >= target else "meets"


def assess_postings(jobs, target_salary=None):
    """Flag agency spam, mislabelled pay and long-running reposts for review."""
    per_company = {}
    for job in jobs:
        name = (job.get("company") or "").strip().lower()
        if name:
            per_company[name] = per_company.get(name, 0) + 1
    history = posting_history([posting_fingerprint(job) for job in jobs])
    now = time.time()

    for job in jobs:
        flags = []
        company = (job.get("company") or "").strip().lower()
        count = per_company.get(company, 0)
        if count >= AGENCY_FLOOD_THRESHOLD:
            flags.append(f"{count} listings from this employer in these results")
        if job.get("posted_on_behalf") or job.get("employer_hidden"):
            flags.append("Posted by an agency for an undisclosed employer")
        elif any(hint in company for hint in AGENCY_NAME_HINTS):
            flags.append("Recruitment agency, not the hiring employer")
        if BATCH_CODE_RE.match(job.get("title") or ""):
            flags.append("Batch-coded title, typical of bulk agency posting")

        low, high = job.get("salary_min"), job.get("salary_max")
        if (job.get("salary_period") or "").startswith("month"):
            if low and low < MIN_PLAUSIBLE_MONTHLY_SGD:
                flags.append("Monthly pay looks mislabelled (likely hourly)")
            if low and high and high / low >= WIDE_SALARY_RATIO:
                flags.append("Very wide salary band, scope may be unclear")

        record = history.get(posting_fingerprint(job))
        if record and record["times"] >= REPOST_MIN_TIMES:
            days = int((now - record["first_seen"]) / 86400)
            if days >= REPOST_MIN_DAYS:
                flags.append(
                    f"Reposted {record['times']}x over {days} days, possible ghost job"
                )

        job["flags"] = flags
        job["quality"] = "suspect" if len(flags) >= 2 else ("check" if flags else "ok")
        job["salary_verdict"] = salary_verdict(job, target_salary)
    return jobs


def is_student_friendly(job):
    title = job.get("title", "") or ""
    if ENTRY_TITLE_RE.search(title):
        return True
    if SENIOR_TITLE_RE.search(title):
        return False
    seniority = (job.get("seniority", "") or "").lower()
    if seniority in {"senior", "lead", "manager", "director", "executive"}:
        return False
    years = job.get("experience_years")
    try:
        if years is not None and float(years) > 2:
            return False
    except (TypeError, ValueError):
        pass
    return True


def _date_within(date_text, period):
    days = {"today": 1, "3days": 3, "week": 7, "month": 31}.get(period)
    if not days or not date_text:
        return True
    try:
        posted = datetime.fromisoformat(date_text[:10]).date()
    except (TypeError, ValueError):
        return True
    return (datetime.now(timezone.utc).date() - posted).days <= days


def filter_jobs(jobs, location, options):
    country_code = country_code_for(location).upper()
    experience = options.get("experience", "student")
    job_type = options.get("job_type", "any").lower()
    arrangement = options.get("arrangement", "any").lower()
    date_posted = options.get("date_posted", "all")
    filtered = []
    for job in jobs:
        if country_code:
            actual = (job.get("country_code", "") or "").upper()
            if actual and actual != country_code:
                continue
            if not actual and location.lower() not in (job.get("location", "") or "").lower():
                continue
        if experience in {"student", "entry"} and not is_student_friendly(job):
            continue
        haystack_type = " ".join(
            (job.get("job_type", "") or "", job.get("title", "") or "")
        ).lower()
        type_needles = {
            "internship": ("intern", "trainee"),
            "fulltime": ("full time", "full-time", "fulltime"),
            "contract": ("contract", "contractor"),
            "parttime": ("part time", "part-time", "parttime"),
        }
        if job_type != "any" and not any(
            needle in haystack_type for needle in type_needles.get(job_type, (job_type,))
        ):
            continue
        actual_arrangement = (job.get("work_arrangement", "") or "").lower()
        if arrangement != "any" and actual_arrangement and arrangement not in actual_arrangement:
            continue
        if not _date_within(job.get("date", ""), date_posted):
            continue
        job["student_friendly"] = is_student_friendly(job)
        filtered.append(job)
    return filtered


def add_match_scores(jobs, role):
    profile_text = candidate_text()
    candidate_skills = known_skills(profile_text)
    role_tokens = {
        token for token in re.findall(r"[a-z0-9+#]+", (role or "").lower())
        if len(token) > 2 and token not in {"engineer", "engineering", "jobs"}
    }
    for job in jobs:
        job_text = " ".join([
            job.get("title", "") or "",
            job.get("description", "") or "",
            " ".join(job.get("required_skills", []) or []),
            " ".join(job.get("preferred_skills", []) or []),
        ])
        job_skills = known_skills(job_text)
        matched = sorted(candidate_skills & job_skills)
        missing = sorted(job_skills - candidate_skills)
        reasons = []
        score = 0
        title_tokens = set(re.findall(r"[a-z0-9+#]+", (job.get("title", "") or "").lower()))
        if role_tokens:
            role_overlap = len(role_tokens & title_tokens) / len(role_tokens)
            score += round(35 * role_overlap)
            if role_overlap:
                reasons.append("Role/title match")
        if candidate_skills:
            score += min(45, len(matched) * 9)
            if matched:
                reasons.append("Skills: " + ", ".join(matched[:4]))
        if job.get("student_friendly"):
            score += 15
            reasons.append("Student/entry friendly")
        if (job.get("country_code", "") or "").upper() == "SG":
            score += 5
            reasons.append("Singapore")
        job["match_score"] = min(100, score) if profile_text else None
        job["match_reasons"] = reasons[:3]
        job["missing_skills"] = missing[:5] if profile_text else []
    return bool(profile_text)


def rank_jobs(jobs):
    def date_ordinal(value):
        try:
            return datetime.fromisoformat((value or "")[:10]).date().toordinal()
        except (TypeError, ValueError):
            return 0

    def key(job):
        location = (job.get("location", "") or "").lower()
        home = (job.get("country_code", "") or "").upper() == "SG" or HOME_COUNTRY in location
        match = job.get("match_score")
        return (
            0 if home else 1,
            0 if job.get("student_friendly") else 1,
            -(match if isinstance(match, (int, float)) else -1),
            -date_ordinal(job.get("date", "")),
        )
    return sorted(jobs, key=key)


def decorate_result(result, role):
    decorated = dict(result)
    jobs = [dict(job) for job in result.get("jobs", [])]
    decorated["resume_indexed"] = add_match_scores(jobs, role)
    profile = load_json(PROFILE_FILE, {})
    try:
        target = float(profile.get("target_salary") or 0) or None
    except (TypeError, ValueError):
        target = None
    assess_postings(jobs, target)
    decorated["target_salary"] = target
    jobs = rank_jobs(jobs)
    decorated["jobs"] = jobs
    decorated["count"] = len(jobs)
    return decorated


def get_jobs(industry, role, company, location, preview=False, options=None,
             refresh=False):
    # As many jobs as possible, Singapore-first. JSearch (with a key) is the
    # richest source; otherwise we aggregate several free, no-key boards.
    # Identical searches are served from SQLite (0 API requests, survives restarts).
    options = options or {}
    limit = PREVIEW_LIMIT if preview else DISPLAY_LIMIT
    key_payload = {
        "industry": industry.lower().strip(),
        "role": role.lower().strip(),
        "company": company.lower().strip(),
        "location": location.lower().strip(),
        "preview": preview,
        "options": options,
    }
    search_cache_key = _cache_key(key_payload)
    if not refresh:
        hit = cache_get(search_cache_key)
        if hit:
            return decorate_result(hit, role)
    note = ""

    # Singapore searches go to MyCareersFuture first: no key, no quota, and it
    # carries local on-site roles the remote-only boards never list.
    if targets_singapore(location):
        try:
            mcf = fetch_mycareersfuture(
                role, industry, company, options=options,
                pages=1 if preview else MCF_PAGES,
                page_size=MCF_PREVIEW_SIZE if preview else MCF_PAGE_SIZE,
            )
            if mcf:
                jobs = _sg_first(filter_jobs(_dedupe(mcf), location, options))[:limit]
                if jobs:
                    result = {"source": "mycareersfuture", "count": len(jobs),
                              "jobs": jobs}
                    record_postings(jobs)
                    cache_set(search_cache_key, result)
                    return decorate_result(result, role)
        except NET_ERRORS:
            note = "Couldn't reach MyCareersFuture. Showing other sources instead."

    key = get_rapidapi_key()
    if key:
        pages = 1 if preview else JSEARCH_PAGES
        for attempt in ((pages, 1) if pages > 1 else (1,)):
            try:
                jobs = fetch_jsearch(role, industry, company, location, key,
                                     pages=attempt, options=options)
                # No country picked -> guarantee some Singapore roles on top.
                if not preview and not location.strip():
                    try:
                        sg = fetch_jsearch(role, industry, company, "Singapore", key,
                                           pages=(JSEARCH_SG_PAGES if attempt > 1 else 1),
                                           options=options)
                        jobs = _dedupe(sg + jobs)
                    except NET_ERRORS:
                        pass
                jobs = filter_jobs(_dedupe(jobs), location, options)
                jobs = _sg_first(jobs)[:limit]
                result = {"source": "jsearch", "count": len(jobs[:limit]),
                          "jobs": jobs[:limit]}
                record_postings(jobs[:limit])
                cache_set(search_cache_key, result)
                return decorate_result(result, role)
            except urllib.error.HTTPError as e:
                if e.code == 403:
                    note = ("JSearch rejected the key or subscription. Check RapidAPI "
                            "and try again. Showing free-source jobs meanwhile.")
                elif e.code == 404:
                    note = ("JSearch's search endpoint is temporarily unavailable. "
                            "Showing free-source jobs meanwhile.")
                elif e.code == 429:
                    note = ("JSearch monthly/rate limit reached for your plan. Showing "
                            "free-source jobs meanwhile.")
                else:
                    note = (f"JSearch returned HTTP {e.code} - check your RapidAPI key "
                            "and JSearch subscription. Showing free-source jobs instead.")
                # Retry with fewer pages only for a bad/unsupported request shape.
                # Auth and rate-limit failures would merely waste another API call.
                if e.code in (400, 422) and attempt != 1:
                    continue
                break
            except NET_ERRORS:
                note = "Couldn't reach JSearch. Showing free-source jobs instead."
                break

    # Free, no-key sources aggregated for maximum volume.
    jobs = []
    try:
        jobs.extend(fetch_remotive(industry, role))
    except NET_ERRORS:
        pass
    if not preview:  # heavier extra sources only for the real search
        try:
            jobs.extend(fetch_jobicy(role))
        except NET_ERRORS:
            pass
        try:
            jobs.extend(fetch_arbeitnow(role))
        except NET_ERRORS:
            pass

    source = "aggregated" if jobs else "sample"
    if not jobs:
        jobs = [dict(j) for j in SAMPLE_JOBS]
        if role:
            r = role.lower()
            jobs = [j for j in jobs if r in j["title"].lower()
                    or r in j["category"].lower()]

    jobs = _dedupe(jobs)
    if company:
        c = company.lower()
        jobs = [j for j in jobs if c in j["company"].lower()]
    if location:
        loc = location.lower()
        jobs = [j for j in jobs if loc in j["location"].lower()]
    jobs = filter_jobs(jobs, location, options)
    jobs = _sg_first(jobs)[:limit]

    result = {"source": source, "count": len(jobs[:limit]), "jobs": jobs[:limit]}
    if note:
        result["note"] = note
    elif source != "sample":
        record_postings(jobs[:limit])
        cache_set(search_cache_key, result)
    return decorate_result(result, role)


def load_applications():
    apps = load_json(APPLICATIONS_FILE, [])
    changed = False
    for app in apps:
        # Old versions marked a tab as "applied" before the user submitted it.
        if app.get("status") == "applied":
            app["status"] = "opened"
            changed = True
        if "created_at" not in app:
            app["created_at"] = app.get("applied_at", "")
            changed = True
        for key in ("notes", "follow_up"):
            if key not in app:
                app[key] = ""
                changed = True
    if changed:
        save_json(APPLICATIONS_FILE, apps)
    return apps


def record_applications(items, resume_name):
    apps = load_applications()
    seen = {a.get("url") for a in apps}
    now = datetime.now(timezone.utc).isoformat(timespec="seconds")
    added = []
    for it in items:
        url = it.get("url")
        if not url or url in seen:
            continue
        rec = {
            "id": it.get("id"),
            "title": it.get("title", ""),
            "company": it.get("company", ""),
            "location": it.get("location", ""),
            "url": url,
            "resume": resume_name,
            "status": "queued",
            "created_at": now,
            "updated_at": now,
            "notes": "",
            "follow_up": "",
        }
        apps.append(rec)
        added.append(rec)
        seen.add(url)
    save_json(APPLICATIONS_FILE, apps)
    return added, apps


def update_applications(urls, status=None, notes=None, follow_up=None):
    if status is not None and status not in APPLICATION_STATUSES:
        raise ValueError("invalid application status")
    wanted = set(urls)
    apps = load_applications()
    updated = 0
    now = datetime.now(timezone.utc).isoformat(timespec="seconds")
    for app in apps:
        if app.get("url") not in wanted:
            continue
        if status is not None:
            app["status"] = status
            if status == "opened" and not app.get("opened_at"):
                app["opened_at"] = now
            if status == "submitted" and not app.get("submitted_at"):
                app["submitted_at"] = now
        if notes is not None:
            app["notes"] = str(notes)[:2000]
        if follow_up is not None:
            app["follow_up"] = str(follow_up)[:20]
        app["updated_at"] = now
        updated += 1
    if updated:
        save_json(APPLICATIONS_FILE, apps)
    return updated, apps


def applications_csv():
    output = io.StringIO(newline="")
    fields = [
        "title", "company", "location", "status", "created_at", "opened_at",
        "submitted_at", "follow_up", "notes", "resume", "url",
    ]
    writer = csv.DictWriter(output, fieldnames=fields, extrasaction="ignore")
    writer.writeheader()
    for app in load_applications():
        writer.writerow(app)
    return output.getvalue().encode("utf-8-sig")


# ---------------------------------------------------------------------------
# HTTP handler
# ---------------------------------------------------------------------------
class Handler(BaseHTTPRequestHandler):
    server_version = "JobApplyAssistant/1.0"

    def log_message(self, fmt, *args):
        print("[server] " + (fmt % args))

    # -- response helpers -------------------------------------------------
    def send_json(self, obj, status=200):
        body = json.dumps(obj).encode("utf-8")
        self.send_response(status)
        self.send_header("Content-Type", "application/json; charset=utf-8")
        self.send_header("Content-Length", str(len(body)))
        self.end_headers()
        self.wfile.write(body)

    def send_bytes(self, body, content_type, filename=None, status=200):
        self.send_response(status)
        self.send_header("Content-Type", content_type)
        self.send_header("Content-Length", str(len(body)))
        if filename:
            self.send_header("Content-Disposition", f'attachment; filename="{filename}"')
        self.end_headers()
        self.wfile.write(body)

    def send_static(self, rel_path):
        rel_path = rel_path.lstrip("/")
        full = os.path.normpath(os.path.join(STATIC_DIR, rel_path))
        if not full.startswith(STATIC_DIR) or not os.path.isfile(full):
            self.send_error(404, "Not found")
            return
        ext = os.path.splitext(full)[1].lower()
        ctype = CONTENT_TYPES.get(ext, "application/octet-stream")
        with open(full, "rb") as f:
            body = f.read()
        self.send_response(200)
        self.send_header("Content-Type", ctype)
        self.send_header("Content-Length", str(len(body)))
        self.end_headers()
        self.wfile.write(body)

    def read_json(self):
        length = int(self.headers.get("Content-Length", 0) or 0)
        if length <= 0:
            return {}
        if length > 12 * 1024 * 1024:
            raise ValueError("payload too large")
        raw = self.rfile.read(length)
        try:
            return json.loads(raw.decode("utf-8"))
        except json.JSONDecodeError:
            return {}

    # -- routing ----------------------------------------------------------
    def do_GET(self):
        parsed = urllib.parse.urlsplit(self.path)
        path = parsed.path
        query = urllib.parse.parse_qs(parsed.query)

        if path == "/" or path == "/index.html":
            self.send_static("index.html")
        elif path == "/api/categories":
            self.send_json({"categories": get_categories()})
        elif path == "/api/jobs":
            options = {
                "experience": (query.get("experience", ["student"])[0]).strip(),
                "job_type": (query.get("job_type", ["any"])[0]).strip(),
                "arrangement": (query.get("arrangement", ["any"])[0]).strip(),
                "date_posted": (query.get("date_posted", ["all"])[0]).strip(),
                "related_roles": query.get("related_roles", ["1"])[0] == "1",
            }
            result = get_jobs(
                industry=(query.get("industry", [""])[0]).strip(),
                role=(query.get("role", [""])[0]).strip(),
                company=(query.get("company", [""])[0]).strip(),
                location=(query.get("location", [""])[0]).strip(),
                preview=(query.get("preview", ["0"])[0] == "1"),
                options=options,
                refresh=(query.get("refresh", ["0"])[0] == "1"),
            )
            result["usage"] = usage_summary()
            self.send_json(result)
        elif path == "/api/applications":
            self.send_json({"applications": load_applications()})
        elif path == "/api/applications.csv":
            self.send_bytes(
                applications_csv(), "text/csv; charset=utf-8",
                filename="job_applications.csv",
            )
        elif path == "/api/profile":
            self.send_json({"profile": load_json(PROFILE_FILE, {})})
        elif path == "/api/answers":
            self.send_json({"answers": load_answers()})
        elif path == "/api/usage":
            self.send_json(usage_summary())
        elif path == "/api/config":
            has_key = bool(get_rapidapi_key())
            self.send_json({
                "has_key": has_key,
                "source": "jsearch" if has_key else "remotive",
            })
        else:
            self.send_static(path)

    def do_POST(self):
        parsed = urllib.parse.urlsplit(self.path)
        path = parsed.path
        try:
            body = self.read_json()
        except ValueError:
            self.send_json({"ok": False, "error": "payload too large"}, 413)
            return

        if path == "/api/apply":
            items = body.get("jobs", [])
            if not isinstance(items, list):
                self.send_json({"ok": False, "error": "jobs must be a list"}, 400)
                return
            profile = load_json(PROFILE_FILE, {})
            resume = profile.get("resume")
            added, apps = record_applications(items, resume)
            self.send_json({
                "ok": True,
                "added": len(added),
                "resume": resume,
                "applications": apps,
            })
        elif path == "/api/profile":
            profile = load_json(PROFILE_FILE, {})
            for key in (
                "name", "email", "phone", "skills", "home_country",
                "address", "linkedin", "target_salary",
            ):
                if key in body:
                    limit = 5000 if key == "skills" else 200
                    profile[key] = str(body[key])[:limit]
            save_json(PROFILE_FILE, profile)
            self.send_json({"ok": True, "profile": profile})
        elif path == "/api/resume":
            self.handle_resume(body)
        elif path == "/api/application":
            urls = body.get("urls", [])
            if isinstance(body.get("url"), str):
                urls = [body["url"]]
            if not isinstance(urls, list) or not all(isinstance(u, str) for u in urls):
                self.send_json({"ok": False, "error": "urls must be a list"}, 400)
                return
            try:
                updated, apps = update_applications(
                    urls,
                    status=body.get("status"),
                    notes=body.get("notes") if "notes" in body else None,
                    follow_up=body.get("follow_up") if "follow_up" in body else None,
                )
            except ValueError as exc:
                self.send_json({"ok": False, "error": str(exc)}, 400)
                return
            self.send_json({"ok": True, "updated": updated, "applications": apps})
        elif path == "/api/cache/clear":
            self.send_json({"ok": True, "cleared": cache_clear()})
        elif path == "/api/answers":
            try:
                saved, answers = save_answer_records(body.get("answers", []))
            except ValueError as exc:
                self.send_json({"ok": False, "error": str(exc)}, 400)
                return
            self.send_json({"ok": True, "saved": saved, "answers": answers})
        elif path == "/api/answers/match":
            questions = body.get("questions", [])
            if not isinstance(questions, list):
                self.send_json({"ok": False, "error": "questions must be a list"}, 400)
                return
            self.send_json({"ok": True, "matches": match_answers(questions)})
        elif path == "/api/answers/delete":
            try:
                answers = delete_answers(body.get("keys", []))
            except ValueError as exc:
                self.send_json({"ok": False, "error": str(exc)}, 400)
                return
            self.send_json({"ok": True, "answers": answers})
        elif path in ("/api/tailor", "/api/tailor.docx"):
            job = body.get("job")
            if not isinstance(job, dict):
                self.send_json({"ok": False, "error": "job must be an object"}, 400)
                return
            try:
                draft = tailor_resume(job)
            except ValueError as exc:
                self.send_json({"ok": False, "error": str(exc)}, 400)
                return
            if path == "/api/tailor":
                self.send_json({"ok": True, "draft": draft})
                return
            keep = body.get("keep")
            keep = set(keep) if isinstance(keep, list) else None
            name = re.sub(r"[^A-Za-z0-9]+", "-", draft["job_title"] or "resume").strip("-")
            self.send_bytes(
                tailored_resume_docx(draft, keep),
                "application/vnd.openxmlformats-officedocument.wordprocessingml.document",
                filename=f"resume-{name[:50] or 'tailored'}.docx",
            )
        elif path == "/api/config":
            if "rapidapi_key" in body:
                save_rapidapi_key(str(body["rapidapi_key"]))
            self.send_json({"ok": True, "has_key": bool(get_rapidapi_key())})
        else:
            self.send_error(404, "Not found")

    def handle_resume(self, body):
        filename = os.path.basename(str(body.get("filename", "resume")))
        if not filename:
            filename = "resume"
        content = body.get("content", "")
        if content.strip().startswith("data:") and "," in content:
            content = content.split(",", 1)[1]
        try:
            raw = base64.b64decode(content)
        except (ValueError, TypeError):
            self.send_json({"ok": False, "error": "invalid file data"}, 400)
            return
        if len(raw) > MAX_UPLOAD:
            self.send_json({"ok": False, "error": "file too large (max 8 MB)"}, 413)
            return
        ensure_dirs()
        with open(os.path.join(RESUME_DIR, filename), "wb") as f:
            f.write(raw)
        resume_path = os.path.join(RESUME_DIR, filename)
        text = extract_resume_text(resume_path)
        if text:
            with open(RESUME_TEXT_FILE, "w", encoding="utf-8") as f:
                f.write(text[:250000])
        else:
            try:
                os.remove(RESUME_TEXT_FILE)
            except FileNotFoundError:
                pass
        profile = load_json(PROFILE_FILE, {})
        profile["resume"] = filename
        profile["resume_indexed"] = bool(text)
        profile["resume_text_chars"] = len(text)
        save_json(PROFILE_FILE, profile)
        self.send_json({
            "ok": True,
            "resume": filename,
            "indexed": bool(text),
            "warning": (
                "Resume saved, but text could not be read. Add skills manually or "
                "install pypdf for PDF matching."
                if not text else ""
            ),
        })


def main():
    ensure_dirs()
    server = ThreadingHTTPServer((HOST, PORT), Handler)
    print(f"Job Apply Assistant running at http://{HOST}:{PORT}")
    print("Press Ctrl+C to stop.")
    try:
        server.serve_forever()
    except KeyboardInterrupt:
        print("\nShutting down.")
        server.shutdown()


if __name__ == "__main__":
    main()