settled-reach/tooling/pql-migrate/seed_tickets.py

#!/usr/bin/env python3
"""Seed pql.db ticket tables from the legacy settledreach.db (pql migration, Phase 2).

Reads the legacy SQLite ticket store (read-only) and INSERTs every ticket, dependency,
label, and history row into <repo>/.pql/pql.db with the `T-N == #N` id bijection. After
this, run `pql plan export` to write the git-tracked changelog, then `pql plan rebuild`
to verify replay.

Transforms:
  - tickets.id -> 'T-'+id ; parent_id -> 'T-'+parent_id ; sprint_id dropped (legacy/archival)
  - enums (type/status/priority) copied verbatim — identical between the two schemas
  - decision_ref kept verbatim (multi-value legacy refs preserved losslessly)
  - team kept verbatim (incl. the single 'server,client' comma-team ticket)
  - deleted_at = NULL ; canonical_version = 1 everywhere
  - hash = NULL for tickets/deps/labels (PK-based ON CONFLICT; updated_at drives LWW);
    ticket_history gets a deterministic content hash so ON CONFLICT(hash) DO NOTHING
    is a real dedup on replay
  - ticket_milestones -> labels: active phase milestone -> 'phase:<N>', others ->
    'milestone:<slug>' (milestones are otherwise vestigial: milestone_deps empty)

Idempotent: --apply clears the four replicated tables first, then re-inserts. Decisions
are markdown-sourced and never touched here. Default is a dry-run report.
"""
import hashlib
import os
import re
import sqlite3
import subprocess
import sys

APPLY = "--apply" in sys.argv
# Derive the repo root from git so the script targets the checkout it's run from
# (a worktree, a fresh clone, or main) rather than a hardcoded path.
REPO = subprocess.check_output(["git", "rev-parse", "--show-toplevel"], text=True).strip()
SRC = os.environ.get("SR_DB_PATH", "/var/home/jeroenschweitzer/Projects/settled-reach/settledreach.db")
DST = f"{REPO}/.pql/pql.db"
SEED_TS = "2026-06-06 00:00:00"  # well-formed stamp for rows the legacy schema didn't timestamp


def tid(n):
    return None if n is None else f"T-{n}"


def slug(s):
    return re.sub(r"[^a-z0-9]+", "-", s.lower()).strip("-")


def hist_hash(row):
    key = "|".join("" if v is None else str(v) for v in row)
    return hashlib.sha256(key.encode()).hexdigest()


def main():
    src = sqlite3.connect(f"file:{SRC}?mode=ro", uri=True)
    src.row_factory = sqlite3.Row
    dst = sqlite3.connect(DST)

    tickets = src.execute("SELECT * FROM tickets").fetchall()
    deps = src.execute("SELECT blocker_id, blocked_id FROM ticket_deps").fetchall()
    labels = src.execute("SELECT ticket_id, label FROM ticket_labels").fetchall()
    history = src.execute(
        "SELECT ticket_id, field, old_value, new_value, changed_by, changed_at FROM ticket_history"
    ).fetchall()
    milestones = {m["id"]: m for m in src.execute("SELECT * FROM milestones").fetchall()}
    tms = src.execute("SELECT ticket_id, milestone_id FROM ticket_milestones").fetchall()

    def ms_label(mid):
        m = milestones[mid]
        return f"phase:{m['cascade_phase']}" if m["cascade_phase"] else f"milestone:{slug(m['name'])}"

    ms_labels = [(r["ticket_id"], ms_label(r["milestone_id"])) for r in tms]

    # --- report ---
    multi = [t["decision_ref"] for t in tickets if t["decision_ref"] and re.search(r"[,\s]", t["decision_ref"].strip())]
    comma_team = [t["id"] for t in tickets if t["team"] and "," in t["team"]]
    print(f"=== seed_tickets.py ({'APPLY' if APPLY else 'DRY-RUN'}) ===")
    print(f"  source: {SRC}")
    print(f"  tickets:        {len(tickets)}")
    print(f"  ticket_deps:    {len(deps)}")
    print(f"  ticket_labels:  {len(labels)} (existing) + {len(ms_labels)} (milestone-derived)")
    print(f"  ticket_history: {len(history)}")
    print("  milestone label mapping: " + ", ".join(f"{mid}->{ms_label(mid)}" for mid in milestones))
    print(f"  multi/space decision_refs kept verbatim: {len(multi)}")
    print(f"  comma-team tickets kept verbatim: {comma_team}")

    if not APPLY:
        print("\nDry run — pass --apply to write into pql.db.")
        return

    cur = dst.cursor()
    for t in ("ticket_history", "ticket_labels", "ticket_deps", "tickets"):
        cur.execute(f"DELETE FROM {t}")

    for t in tickets:
        cur.execute(
            """INSERT INTO tickets (id,type,parent_id,title,description,status,priority,
               assigned_to,team,decision_ref,created_at,updated_at,deleted_at,hash,canonical_version)
               VALUES (?,?,?,?,?,?,?,?,?,?,?,?,NULL,NULL,1)""",
            (tid(t["id"]), t["type"], tid(t["parent_id"]), t["title"], t["description"],
             t["status"], t["priority"], t["assigned_to"], t["team"], t["decision_ref"],
             t["created_at"], t["updated_at"]),
        )
    for d in deps:
        cur.execute(
            """INSERT INTO ticket_deps (blocker_id,blocked_id,created_at,updated_at,deleted_at,hash,canonical_version)
               VALUES (?,?,?,?,NULL,NULL,1)""",
            (tid(d["blocker_id"]), tid(d["blocked_id"]), SEED_TS, SEED_TS),
        )
    for tid_, label in [(r["ticket_id"], r["label"]) for r in labels] + ms_labels:
        cur.execute(
            """INSERT OR IGNORE INTO ticket_labels (ticket_id,label,created_at,updated_at,deleted_at,hash,canonical_version)
               VALUES (?,?,?,?,NULL,NULL,1)""",
            (tid(tid_), label, SEED_TS, SEED_TS),
        )
    for h in history:
        row = (tid(h["ticket_id"]), h["field"], h["old_value"], h["new_value"], h["changed_by"], h["changed_at"])
        cur.execute(
            """INSERT INTO ticket_history (ticket_id,field,old_value,new_value,changed_by,changed_at,
               created_at,updated_at,deleted_at,hash,canonical_version)
               VALUES (?,?,?,?,?,?,?,?,NULL,?,1)""",
            (*row, h["changed_at"], h["changed_at"], hist_hash(row)),
        )
    dst.commit()
    print(f"\nApplied. pql.db ticket rows: "
          f"{cur.execute('SELECT COUNT(*) FROM tickets').fetchone()[0]} tickets, "
          f"{cur.execute('SELECT COUNT(*) FROM ticket_deps').fetchone()[0]} deps, "
          f"{cur.execute('SELECT COUNT(*) FROM ticket_labels').fetchone()[0]} labels, "
          f"{cur.execute('SELECT COUNT(*) FROM ticket_history').fetchone()[0]} history.")
    print("Next: `pql plan export` then `pql plan rebuild`, then verify parity.")


if __name__ == "__main__":
    main()