csv_io.py

"""
CSV I/O — reading input data and writing output results.
"""

import csv
import uuid
from pathlib import Path
from urllib.parse import urlparse


def _is_safe_url(url: str) -> bool:
    try:
        p = urlparse(url)
        return p.scheme in ("http", "https") and bool(p.netloc)
    except Exception:
        return False


# ── Output schema ─────────────────────────────────────────────────────────────
CSV_COLUMNS = ["id", "company_name", "match_title", "position_title", "match_position_url", "time_found", "reviewed", "comment"]


def load_companies(path: str) -> list[dict]:
    """
    Read companies CSV and return a sorted, deduplicated list of company dicts.

    Each dict contains: company_name, open_positions_url, hr_platform, api_token.

    Filtering:
    - Only rows where no_click == "TRUE" are included.
    - Rows with missing name or URL are skipped.
    - Duplicate URLs are skipped.

    Ordering:
    - Sorted by rating descending (1–5). Companies with no rating go last.
    """
    companies, seen = [], set()
    with open(path, newline="", encoding="utf-8") as f:
        for row in csv.DictReader(f):
            no_click = (row.get("no_click") or "").strip().upper()
            if no_click != "TRUE":
                continue

            name = (row.get("company_name") or "").strip()
            url = (row.get("open_positions_url") or "").strip()
            if not name or not url or url in seen:
                continue
            if not _is_safe_url(url):
                print(f"⚠️  Skipping {name!r} — unsafe URL scheme: {url!r}")
                continue

            seen.add(url)
            raw_rating = (row.get("rating") or "").strip()
            rating = float(raw_rating) if raw_rating else 0.0
            companies.append(
                {
                    "company_name": name,
                    "open_positions_url": url,
                    "hr_platform": (row.get("hr_platform") or "").strip().lower(),
                    "api_token": (row.get("api_token") or "").strip(),
                    "_rating": rating,
                }
            )

    companies.sort(key=lambda c: c["_rating"], reverse=True)
    for c in companies:
        del c["_rating"]
    return companies


def load_titles(path: str) -> list[str]:
    """
    Read titles CSV and return a deduplicated list of job title strings.
    Deduplication is case-insensitive.
    """
    titles, seen = [], set()
    with open(path, newline="", encoding="utf-8") as f:
        for row in csv.DictReader(f):
            title = row["title"].strip()
            if title and title.lower() not in seen:
                seen.add(title.lower())
                titles.append(title)
    return titles


def load_known_urls(path: str) -> set[str]:
    """
    Read all match_position_url values from the existing output CSV.
    Returns an empty set if the file does not exist.
    Called once at startup to pre-populate the in-memory duplicate guard.
    Rows with an empty id field are skipped (artifact of Google Sheets edits).
    """
    p = Path(path)
    if not p.exists():
        return set()
    with open(p, newline="", encoding="utf-8") as f:
        return {row["match_position_url"] for row in csv.DictReader(f) if row.get("id") and row.get("match_position_url")}


def _migrate_header_if_needed(path: Path) -> None:
    """
    If the file's header row doesn't match CSV_COLUMNS, replace just the header line.
    All existing data rows are left completely unchanged — old rows will simply have
    empty values for any new columns when read back via DictReader.
    """
    content = path.read_text(encoding="utf-8")
    lines = content.splitlines(keepends=True)
    if not lines:
        return
    expected_header = ",".join(CSV_COLUMNS)
    if lines[0].rstrip("\r\n") == expected_header:
        return  # already up to date
    lines[0] = expected_header + "\n"
    path.write_text("".join(lines), encoding="utf-8")


def append_match_row(match: dict, path: str) -> None:
    """
    Append a single match row to the output CSV.
    Creates the file and header row if it does not exist.
    Must be called under asyncio.Lock — never invoked concurrently.

    Guard: if the file exists but its last byte is not \\n (e.g. after a
    Google Sheets export), a newline is prepended so the new row starts
    on its own line rather than being fused with the previous last line.

    Schema migration: if the file exists with an old header, the header is
    updated to CSV_COLUMNS in place — existing data rows are left unchanged.
    """
    output = Path(path)
    output.parent.mkdir(parents=True, exist_ok=True)
    file_exists = output.exists()

    if file_exists and output.stat().st_size > 0:
        # Migrate header to new schema if needed (e.g. after adding new columns)
        _migrate_header_if_needed(output)

        # Guard: Google Sheets export strips trailing newline — fix before appending
        with open(output, "rb") as f_check:
            f_check.seek(-1, 2)
            if f_check.read(1) != b"\n":
                with open(output, "a", encoding="utf-8") as f_fix:
                    f_fix.write("\n")

    with open(output, "a", newline="", encoding="utf-8") as f:
        writer = csv.DictWriter(f, fieldnames=CSV_COLUMNS)
        if not file_exists:
            writer.writeheader()
        writer.writerow(
            {
                "id": str(uuid.uuid4()),
                "company_name": match["company_name"],
                "match_title": match["match_title"],
                "position_title": match["position_title"],
                "match_position_url": match["match_position_url"],
                "time_found": match["time_found"],
                "reviewed": "",
                "comment": "",
            }
        )