build_org_changes.py

#!/usr/bin/env python3
"""
Phase 13c: Surface and record organizational chart changes approved by the board.

Scans minutes items (primary meetings only) for keywords signaling position
additions, eliminations, renames, or FTE changes. Category pre-filter on
'personnel' and 'finance' reduces the candidate pool. Every row requires
human confirmation.

Usage:
    python build_org_changes.py --dry-run          # print candidates, no writes
    python build_org_changes.py --confirm          # write after dry-run review
    python build_org_changes.py --year 2018-2019   # one school year
"""

import re
import sys
from pathlib import Path

import click

sys.path.insert(0, str(Path(__file__).parent / "lib"))

from agenda_db import DB_PATH, ensure_db

_KEYWORD_RE = re.compile(
    r"""
    # Board/district is the subject creating/eliminating the position
    (?:board|district|administration)\s.{0,40}?\b(?:creat|establish|add|approv).{0,30}?\bposition\b
    |
    \bnew\s+position\b.{0,60}(?:created|established|approved|authorized)
    |
    \b(?:eliminat|abolish|remov|cut)\w*\s+\w+\s+position\b
    |
    \bposition\s+(?:eliminat|abolish|remov)\w*\b
    |
    \b(?:reduction\s+in\s+force|RIF)\b
    |
    \b(?:retitl|reclassif|restructur|reorganiz)\w+\b
    |
    \bFTE\s+(?:increas|decreas|reduc|eliminat|add)\w*\b
    |
    \b(?:increas|decreas|reduc)\w*\s+FTE\b
    """,
    re.IGNORECASE | re.VERBOSE,
)

_CHANGE_TYPE_SIGNALS = {
    "position_added":      re.compile(
        r"\b(position\s+(?:creat|add|new|establish)\w*|(?:new|creat|add)\w*\s+position)\b", re.I
    ),
    "position_eliminated": re.compile(
        r"\b(position\s+(?:eliminat|abolish|remov)\w*|(?:eliminat|abolish)\w*\s+position)\b", re.I
    ),
    "position_renamed":    re.compile(
        r"\b(position\s+(?:renam|retitl|redesignat|chang)\w*"
        r"|(?:renam|retitl|redesignat)\w*\s+position"
        r"|administrative\s+position\s+change)\b",
        re.I,
    ),
    "department_restructure": re.compile(
        r"\b(department\s+(?:restructur|reorganiz|consolid)\w*|(?:restructur|reorganiz|consolid)\w*\s+department)\b",
        re.I,
    ),
    "fte_change":          re.compile(
        r"\b(FTE\s+(?:increas|decreas|reduc|eliminat|add)\w*|(?:increas|decreas|reduc)\w*\s+FTE)\b", re.I
    ),
}


def _infer_change_type(text: str) -> str:
    for ctype, pattern in _CHANGE_TYPE_SIGNALS.items():
        if pattern.search(text):
            return ctype
    return "title_change"


def _extract_position_title(title: str, body: str) -> str:
    # Normalize newlines so patterns don't break on wrapped PDF text.
    body = (body or "").replace("\n", " ")
    title = title or ""

    _DELIM = r"(?=,\s*effective\b|\s+effective\b|\s*\.\s+[A-Z]|$)"
    _POS   = r"([A-Za-z][A-Za-z\s\-/,&]{2,99}?)"

    # 1. Body: "appointment of [Title] [Name] as/for [Position]"
    m = re.search(
        r"\bappointment\s+of\s+"
        r"(?:Mr\.|Mrs\.|Ms\.|Dr\.)?\s*\w+(?:\s+\w+){0,3}"
        r"\s+(?:as|for)\s+" + _POS + _DELIM,
        body, re.I,
    )
    if m:
        return m.group(1).strip()[:80]

    # 2. Body: "hire [Title] [Name] as [Position]"
    m = re.search(
        r"\bhire\s+"
        r"(?:Mr\.|Mrs\.|Ms\.|Dr\.)?\s*\w+(?:\s+\w+){0,3}"
        r"\s+as\s+" + _POS + _DELIM,
        body, re.I,
    )
    if m:
        return m.group(1).strip()[:80]

    # 3. Body: "position change of [Name] from [Old] to [New]"
    m = re.search(
        r"\bposition\s+change\s+of\s+"
        r"(?:Mr\.|Mrs\.|Ms\.|Dr\.)?\s*\w+(?:\s+\w+){0,3}"
        r"\s+from\s+.+?\s+to\s+" + _POS + _DELIM,
        body, re.I,
    )
    if m:
        return m.group(1).strip()[:80]

    # 4. Title: "Appointment of [Position]"
    m = re.search(r"\bAppointment\s+of\s+(.+)$", title, re.I)
    if m:
        return m.group(1).strip()[:80]

    # 5. Title: "Hire [Position]" (from "Recommendation to Hire [Position]")
    m = re.search(r"\bHire\s+(.+)$", title, re.I)
    if m:
        return m.group(1).strip()[:80]

    # 6. "position of" / "role of" in combined text
    combined = f"{title} {body}"
    m = re.search(
        r"(?:position\s+of|the\s+position\s+of|titled?\s+|role\s+of)\s*"
        r"([A-Z][A-Za-z\s\-/]{3,60}?)(?=\s+(?:for|at|in|to|was|will|has)\b|[,.\"]|$)",
        combined,
    )
    if m:
        return m.group(1).strip()[:80]

    return title.strip()[:80]


def find_candidates(conn, year: str = "") -> list[dict]:
    query = """
        SELECT ai.id            AS agenda_item_id,
               ai.title,
               ai.body_text,
               ai.outcome,
               ai.category,
               ai.meeting_id,
               m.meeting_date,
               m.school_year
          FROM agenda_items ai
          JOIN meetings m ON m.id = ai.meeting_id
         WHERE m.is_primary_meeting = 1
           AND ai.doc_type = 'minutes'
           AND ai.body_text IS NOT NULL
           AND ai.category IN ('personnel', 'action', 'finance', 'uncategorized')
    """
    params: list = []
    if year:
        query += " AND m.school_year = ?"
        params.append(year)
    query += " ORDER BY m.meeting_date"

    candidates = []
    for row in conn.execute(query, params).fetchall():
        text = (row["body_text"] or "") + " " + (row["title"] or "")
        if _KEYWORD_RE.search(text):
            candidates.append(dict(row))
    return candidates


def print_candidates(candidates: list[dict]) -> None:
    if not candidates:
        click.echo("No candidates found.")
        return
    click.echo(f"\n{'='*70}")
    click.echo(f"  {len(candidates)} org-change candidate agenda items")
    click.echo(f"{'='*70}\n")
    for i, c in enumerate(candidates, 1):
        text = (c["body_text"] or "") + " " + (c["title"] or "")
        ctype = _infer_change_type(text)
        pos = _extract_position_title(c["title"], c["body_text"])
        click.echo(f"[{i:>3}] {c['meeting_date']}  {c['school_year']}")
        click.echo(f"       item_id={c['agenda_item_id']}  meeting_id={c['meeting_id']}")
        click.echo(f"       title:     {c['title']}")
        click.echo(f"       category:  {c['category']}")
        click.echo(f"       change:    {ctype}")
        click.echo(f"       position:  {pos}")
        click.echo(f"       outcome:   {c['outcome']}")
        snippet = (c["body_text"] or "")[:200].replace("\n", " ")
        click.echo(f"       text:      {snippet}…")
        click.echo()


def write_org_changes(conn, candidates: list[dict]) -> int:
    written = 0
    for c in candidates:
        text = (c["body_text"] or "") + " " + (c["title"] or "")
        change_type = _infer_change_type(text)
        position_title = _extract_position_title(c["title"], c["body_text"])
        conn.execute(
            """
            INSERT OR IGNORE INTO org_changes
                (meeting_id, agenda_item_id, change_type, position_title,
                 rationale, outcome, source_page, notes)
            VALUES (?, ?, ?, ?, ?, ?, NULL, NULL)
            """,
            (
                c["meeting_id"],
                c["agenda_item_id"],
                change_type,
                position_title,
                (c["body_text"] or "")[:300].strip() or None,
                c["outcome"],
            ),
        )
        if conn.execute("SELECT changes()").fetchone()[0]:
            written += 1
    conn.commit()
    return written


@click.command()
@click.option("--db",      default=str(DB_PATH), show_default=True)
@click.option("--dry-run", is_flag=True, help="Print candidates; no DB writes.")
@click.option("--confirm", is_flag=True, help="Write all candidates after review.")
@click.option("--year",    default="", help="Filter to one school year e.g. 2018-2019.")
def main(db: str, dry_run: bool, confirm: bool, year: str) -> None:
    """Phase 13c: surface and record organizational chart changes."""
    if not dry_run and not confirm:
        click.echo(
            "ERROR: pass --dry-run to review candidates, then --confirm to write.",
            err=True,
        )
        sys.exit(1)

    db_path = Path(db)
    if not db_path.exists():
        click.echo(f"ERROR: {db_path} not found.", err=True)
        sys.exit(1)

    conn = ensure_db(db_path)
    candidates = find_candidates(conn, year=year)
    print_candidates(candidates)

    if confirm and candidates:
        written = write_org_changes(conn, candidates)
        click.echo(
            f"Wrote {written} new org_changes rows "
            f"({len(candidates) - written} already present)."
        )
    elif confirm and not candidates:
        click.echo("Nothing to write.")
    else:
        click.echo(
            f"\nDry-run complete. Run with --confirm to write {len(candidates)} candidate(s)."
        )

    conn.close()


if __name__ == "__main__":
    main()