#!/usr/bin/env python3
"""Bank batch-137: handling, placement, transfer, and manipulation verbs."""

from __future__ import annotations

import datetime as dt
import json
import re
import sqlite3
import sys

DB = "/mnt/donto-data/donto-resources/research/translation-training/kuku-yalanji-runpod-2026-06-30/synthetic/claude-synthetic-v1-2026-07-02/synthetic.db"
LABEL = "batch-137"
NOW = dt.datetime.now(dt.timezone.utc).replace(microsecond=0).isoformat().replace("+00:00", "Z")

DENY = {
    "nyawa", "jinbi", "jibin", "walba", "julmbu", "wanan", "ngampula",
    "yalama", "ngana-jinji", "jubuny", "wural", "bigan", "jiwuny",
    "warngkul", "wanaba", "jarrkunji", "munjal", "murku", "buni",
    "kunya", "milkan", "kulur-jambul", "janyji", "buburr", "jarrmba",
    "jinji", "daga", "wanka", "yumbul", "kuku-yumbul", "jubi",
    "yilbay", "jarrakay", "janka-yukil", "jukarr", "kunkul",
    "bubu-jandu", "bubu-warral", "jani-warra", "balbay-nja",
    "bunjurril", "buri", "baral", "table", "gate", "bucket", "truck",
    "motorcar", "cassette", "brake", "torch",
}


def js(values: list[str]) -> str:
    return json.dumps(values, ensure_ascii=False)


ROWS = [
    (1, "Mother got a fish.",
     "Ngamu-ngku kuyu manin.",
     "ngamu-ngku kuyu mani-ny | mother-ERG fish get-PAST",
     "handling_get_food", 4, js(["ngamu", "kuyu", "manil"]), js([]),
     "Patz ex.46/209/391: manil get/catch with fish object.", "0.88"),
    (2, "The old woman got the dog.",
     "Wulbuman-angka kaya manin.",
     "wulbuman-angka kaya mani-ny | old.woman-ERG dog get-PAST",
     "handling_get_animal", 4, js(["wulbuman", "kaya", "manil"]), js([]),
     "Text 51 line 40: old woman went and got the dog.", "0.88"),
    (3, "The child got firewood.",
     "Karrkay-ngku baya manin.",
     "karrkay-ngku baya mani-ny | child-ERG firewood get-PAST",
     "handling_get_firewood", 4, js(["karrkay", "baya", "manil"]), js([]),
     "Manil get with ordinary camp object; baya covers fire/firewood in living errata.", "0.84"),
    (4, "The man got a very big stone.",
     "Dingkar-angka kulji yalbay-ku manin.",
     "dingkar-angka kulji yalbay-ku mani-ny | man-ERG stone big-EMPH1 get-PAST",
     "handling_get_stone", 5, js(["dingkar", "kulji", "yalbay", "manil", "-ku"]), js([]),
     "Patz text 12 line 40: kulji yalbay-ku mani-ny.", "0.90"),
    (5, "The woman left the dog there.",
     "Jalbu-ngku kaya yinyay bawan.",
     "jalbu-ngku kaya yinyay bawa-ny | woman-ERG dog there leave-PAST",
     "handling_leave_object", 5, js(["jalbu", "kaya", "yinyay", "bawal"]), js([]),
     "Bawal leave; text 51 has kaya bawa-ny up there.", "0.86"),
    (6, "I left the spear at camp.",
     "Ngayu kalka bayan-ba bawan.",
     "ngayu kalka bayan-ba bawa-ny | I spear camp-LOC leave-PAST",
     "handling_leave_location", 4, js(["ngayu", "kalka", "bayan", "bawal"]), js([]),
     "Bawal leave something/some place; LOC location retained.", "0.84"),
    (7, "The child put the stone on the ground.",
     "Karrkay-ngku kulji bubu-ngu yijarrin.",
     "karrkay-ngku kulji bubu-ngu yijarri-ny | child-ERG stone ground-LOC put-PAST",
     "handling_put_location", 5, js(["karrkay", "kulji", "bubu", "yijarril"]), js([]),
     "Yijarril put/place; LOC placement target.", "0.86"),
    (8, "The woman put the meat in the ground oven.",
     "Jalbu-ngku minya kurrma-nga yijarrin.",
     "jalbu-ngku minya kurrma-nga yijarri-ny | woman-ERG meat ground.oven-LOC put-PAST",
     "handling_put_ground_oven", 5, js(["jalbu", "minya", "kurrma", "yijarril"]), js([]),
     "Text 51 line 65: kurrma-nga yijarri-nya minya.", "0.90"),
    (9, "The man put his foot on the stone.",
     "Dingkar-angka jina kulji-nga yijarrin.",
     "dingkar-angka jina kulji-nga yijarri-ny | man-ERG foot stone-LOC put-PAST",
     "handling_put_body_part", 5, js(["dingkar", "jina", "kulji", "yijarril"]), js([]),
     "Patz ex.638 has jina yijarri-ny with a LOC target.", "0.88"),
    (10, "The man pushed the big stone.",
     "Dingkar-angka kulji yalbay jurrngan.",
     "dingkar-angka kulji yalbay jurrnga-ny | man-ERG stone big push-PAST",
     "handling_push", 5, js(["dingkar", "kulji", "yalbay", "jurrngal"]), js([]),
     "Patz text 12 line 41: kulji yalbay-ku jurrnga-ny.", "0.90"),

    (11, "The man pushed the big stone and set it up.",
     "Dingkar-angka kulji yalbay jurrngan, yijarrin.",
     "dingkar-angka kulji yalbay jurrnga-ny, yijarri-ny | man-ERG stone big push-PAST put-PAST",
     "handling_push_put", 5, js(["dingkar", "kulji", "yalbay", "jurrngal", "yijarril"]), js([]),
     "Patz text 12 line 41: pushed the big rock and put/set it.", "0.90"),
    (12, "The young man pulled out the bamboo.",
     "Warru-ngku nambarr kulban.",
     "warru-ngku nambarr kulba-ny | young.man-ERG bamboo pull-PAST",
     "handling_pull_out", 5, js(["warru", "nambarr", "kulbal"]), js([]),
     "Patz ex.384: nambarr kulba-ny.", "0.90"),
    (13, "The old man lifted the log.",
     "Mukul-dingkar-angka walmba kulban.",
     "mukul-dingkar-angka walmba kulba-ny | old-man-ERG log lift-PAST",
     "handling_lift", 4, js(["mukul-dingkar", "walmba", "kulbal"]), js([]),
     "Dictionary kulbal = pull, lift; walmba log.", "0.84"),
    (14, "The woman tied the bag.",
     "Jalbu-ngku kurmun kajan.",
     "jalbu-ngku kurmun kaja-ny | woman-ERG bag tie-PAST",
     "handling_tie", 4, js(["jalbu", "kurmun", "kajal"]), js([]),
     "Dictionary kajal = tie.", "0.84"),
    (15, "The man tied the spear.",
     "Dingkar-angka kalka kajan.",
     "dingkar-angka kalka kaja-ny | man-ERG spear tie-PAST",
     "handling_tie_weapon", 4, js(["dingkar", "kalka", "kajal"]), js([]),
     "Kajal tie with familiar spear object.", "0.84"),
    (16, "The two women gave eel-food to the man.",
     "Bula jalbu-bulal-angka mayi kubarr dingkar-anda dajin.",
     "bula jalbu-bulal-angka mayi kubarr dingkar-anda daji-ny | they.two woman-pair-ERG food eel man-LOC give-PAST",
     "transfer_give_recipient", 5, js(["bula", "jalbu", "-bulal", "mayi", "kubarr", "dingkar", "dajil"]), js([]),
     "Patz ex.529: Bula jalbu-bulal-angka mayi kubarr daji-ny dingkar-anda.", "0.92"),
    (17, "Mother gave food to the child.",
     "Ngamu-ngku mayi karrkay-anda dajin.",
     "ngamu-ngku mayi karrkay-anda daji-ny | mother-ERG food child-LOC:pt give-PAST",
     "transfer_give_recipient", 5, js(["ngamu", "mayi", "karrkay", "dajil"]), js([]),
     "Dajil give; recipient in LOC:pt by Patz give examples.", "0.88"),
    (18, "Grandmother gave green ant to the child.",
     "Babi-ngka yangka karrkay-anda dajin.",
     "babi-ngka yangka karrkay-anda daji-ny | grandmother-ERG green.ant child-LOC:pt give-PAST",
     "transfer_give_medicine", 5, js(["babi", "yangka", "karrkay", "dajil"]), js([]),
     "Patz ex.545: Babi-ngka yangka daji-ny karrkay-anda.", "0.90"),
    (19, "I gave fish to mother.",
     "Ngayu kuyu ngamu-ndu dajin.",
     "ngayu kuyu ngamu-ndu daji-ny | I fish mother-LOC:pt give-PAST",
     "transfer_give_recipient", 4, js(["ngayu", "kuyu", "ngamu", "dajil"]), js([]),
     "Recipient LOC:pt frame for dajil; familiar nouns.", "0.86"),
    (20, "They gave clothes to the girl.",
     "Jana kambi maral-anda dajin.",
     "jana kambi maral-anda daji-ny | they clothes girl-LOC:pt give-PAST",
     "transfer_give_recipient", 5, js(["jana", "kambi", "maral", "dajil"]), js([]),
     "Patz ex.59: Jana kambi daji-ny maral-anda ...", "0.90"),

    (21, "The woman brought food.",
     "Jalbu-ngku mayi wundin.",
     "jalbu-ngku mayi wundi-ny | woman-ERG food bring-PAST",
     "transfer_bring", 4, js(["jalbu", "mayi", "wundil"]), js([]),
     "Wundil bring/take; simple transfer row.", "0.84"),
    (22, "The man took the spear.",
     "Dingkar-angka kalka wundin.",
     "dingkar-angka kalka wundi-ny | man-ERG spear take-PAST",
     "transfer_take", 4, js(["dingkar", "kalka", "wundil"]), js([]),
     "Wundil covers take; familiar object.", "0.84"),
    (23, "The crocodile grabbed the child.",
     "Bilngkumu-ngku karrkay karrban.",
     "bilngkumu-ngku karrkay karrba-ny | crocodile-ERG child grab-PAST",
     "handling_grab", 5, js(["bilngkumu", "karrkay", "karrbal"]), js([]),
     "Text 36 line 40: karrba-ny grab; dictionary karrbal catch/grab.", "0.88"),
    (24, "The child grabbed the bag.",
     "Karrkay-ngku kurmun karrban.",
     "karrkay-ngku kurmun karrba-ny | child-ERG bag grab-PAST",
     "handling_grab_object", 4, js(["karrkay", "kurmun", "karrbal"]), js([]),
     "Karrbal grab/catch with everyday object.", "0.84"),
    (25, "The woman carried the baby.",
     "Jalbu-ngku ngawa jarrakulban.",
     "jalbu-ngku ngawa jarrakulba-ny | woman-ERG baby carry-PAST",
     "handling_carry_person", 4, js(["jalbu", "ngawa", "jarrakulbal"]), js([]),
     "Jarrakulbal carry; baby object frame.", "0.86"),
    (26, "The old man carried wood.",
     "Mukul-dingkar-angka juku jarrakulban.",
     "mukul-dingkar-angka juku jarrakulba-ny | old-man-ERG wood carry-PAST",
     "handling_carry_object", 4, js(["mukul-dingkar", "juku", "jarrakulbal"]), js([]),
     "Jarrakulbal carry with broad juku wood/log object.", "0.84"),
    (27, "The child threw the stone.",
     "Karrkay-ngku kulji yilban.",
     "karrkay-ngku kulji yilba-ny | child-ERG stone throw-PAST",
     "handling_throw", 4, js(["karrkay", "kulji", "yilbal"]), js([]),
     "Yilbal throw; text 36 has yilba-ny with gathered mussels.", "0.86"),
    (28, "The child threw some onto the ground.",
     "Karrkay-ngku kulur bubu-ngu yilban.",
     "karrkay-ngku kulur bubu-ngu yilba-ny | child-ERG some ground-LOC throw-PAST",
     "handling_throw_location", 5, js(["karrkay", "kulur", "bubu", "yilbal"]), js([]),
     "Patz text 36 line 29: Bubu-ngu yilba-ny kulur.", "0.88"),
    (29, "The man threw the spear.",
     "Dingkar-angka kalka yilban.",
     "dingkar-angka kalka yilba-ny | man-ERG spear throw-PAST",
     "handling_throw_weapon", 4, js(["dingkar", "kalka", "yilbal"]), js([]),
     "Yilbal throw with familiar spear object.", "0.84"),
    (30, "They gathered mussels.",
     "Jana mukirr mujan.",
     "jana mukirr muja-ny | they mussel gather-PAST",
     "handling_gather_food", 5, js(["jana", "mukirr", "mujal"]), js([]),
     "Patz text 36 line 26: Mukirr muja-l-muja-ny.", "0.88"),

    (31, "I gathered firewood.",
     "Ngayu baya mujan.",
     "ngayu baya muja-ny | I firewood gather-PAST",
     "handling_gather_firewood", 5, js(["ngayu", "baya", "mujal"]), js([]),
     "Dictionary mujal example: Ngayu baya mujan.", "0.90"),
    (32, "The child gathered food.",
     "Karrkay-ngku mayi mujan.",
     "karrkay-ngku mayi muja-ny | child-ERG food gather-PAST",
     "handling_gather_food", 4, js(["karrkay", "mayi", "mujal"]), js([]),
     "Mujal gather/collect with food object.", "0.84"),
    (33, "They gathered green ants.",
     "Jana yangka mujan.",
     "jana yangka muja-ny | they green.ant gather-PAST",
     "handling_gather_medicine", 4, js(["jana", "yangka", "mujal"]), js([]),
     "Mujal gather/collect with verified green-ant noun.", "0.84"),
    (34, "The man bailed water from the boat.",
     "Dingkar-angka bana buwun-muny wuljin.",
     "dingkar-angka bana buwun-muny wulji-ny | man-ERG water boat-ABL bail.out-PAST",
     "handling_bail_source", 5, js(["dingkar", "bana", "buwun", "wuljil"]), js([]),
     "Patz ex.506: bana wulji-l-wulji-nya buwun-muny.", "0.88"),
    (35, "The two emptied the honey.",
     "Bula wunba wuljin.",
     "bula wunba wulji-ny | they.two honey empty.out-PAST",
     "handling_empty", 4, js(["bula", "wunba", "wuljil"]), js([]),
     "Dictionary wuljil = empty out/bail out; example has honey/bee nest.", "0.84"),
    (36, "The child cut the meat.",
     "Karrkay-ngku minya yakan.",
     "karrkay-ngku minya yaka-ny | child-ERG meat cut-PAST",
     "handling_cut", 4, js(["karrkay", "minya", "yakal"]), js([]),
     "Dictionary yakal = cut; simple meat object.", "0.82"),
    (37, "The woman cut the fish.",
     "Jalbu-ngku kuyu yakan.",
     "jalbu-ngku kuyu yaka-ny | woman-ERG fish cut-PAST",
     "handling_cut", 4, js(["jalbu", "kuyu", "yakal"]), js([]),
     "Yakal cut with familiar fish object.", "0.82"),
    (38, "The man broke the billycan.",
     "Dingkar-angka jungkalu dumbarrin.",
     "dingkar-angka jungkalu dumbarri-ny | man-ERG billycan break-PAST",
     "handling_break", 5, js(["dingkar", "jungkalu", "dumbarril"]), js([]),
     "Patz ex.593: jungkalu ... dumbarri-ny; surface dumbarrin.", "0.88"),
    (39, "I did not break the billycan.",
     "Ngayu jungkalu kari dumbarrin.",
     "ngayu jungkalu kari dumbarri-ny | I billycan NEG break-PAST",
     "handling_break_negated", 5, js(["ngayu", "jungkalu", "kari", "dumbarril"]), js([]),
     "Patz ex.594/595 negation placement with dumbarril.", "0.88"),
    (40, "The child broke the stick.",
     "Karrkay-ngku jaba dumbarrin.",
     "karrkay-ngku jaba dumbarri-ny | child-ERG stick break-PAST",
     "handling_break_object", 4, js(["karrkay", "jaba", "dumbarril"]), js([]),
     "Dumbarril break/tear/rip with familiar stick object.", "0.84"),

    (41, "The man buried the dog.",
     "Dingkar-angka kaya nandan.",
     "dingkar-angka kaya nanda-ny | man-ERG dog bury-PAST",
     "handling_bury", 4, js(["dingkar", "kaya", "nandal"]), js([]),
     "Dictionary nandal = close, shut, bury; simple burial frame.", "0.84"),
    (42, "The woman opened the bag.",
     "Jalbu-ngku kurmun walngan.",
     "jalbu-ngku kurmun walnga-ny | woman-ERG bag open-PAST",
     "handling_open", 4, js(["jalbu", "kurmun", "walngal"]), js([]),
     "Dictionary walngal = open/wake/take clothes off.", "0.84"),
    (43, "Mother woke the child.",
     "Ngamu-ngku karrkay walngan.",
     "ngamu-ngku karrkay walnga-ny | mother-ERG child wake-PAST",
     "handling_wake", 5, js(["ngamu", "karrkay", "walngal"]), js([]),
     "Dictionary walngal usage: Ngawa kari walnga, 'Don't wake the baby'.", "0.88"),
    (44, "The man took off the clothes.",
     "Dingkar-angka kambi walngan.",
     "dingkar-angka kambi walnga-ny | man-ERG clothes take.off-PAST",
     "handling_take_off", 5, js(["dingkar", "kambi", "walngal"]), js([]),
     "Dictionary walngal usage: Nyulu dambal walngan, 'He took off his shoes'.", "0.88"),
    (45, "The woman put food in the bag.",
     "Jalbu-ngku mayi kurmun-ba buyandin.",
     "jalbu-ngku mayi kurmun-ba buyandi-ny | woman-ERG food bag-LOC put.in.container-PAST",
     "handling_put_container", 5, js(["jalbu", "mayi", "kurmun", "buyandi-l"]), js([]),
     "Patz-added buyandi-l = put in container; kept literal with bag LOC.", "0.86"),
    (46, "The man prepared the spear.",
     "Dingkar-angka kalka yanday-bungan.",
     "dingkar-angka kalka yanday-bunga-ny | man-ERG spear ready-CAUS-PAST",
     "handling_prepare", 4, js(["dingkar", "kalka", "yanday-bungal"]), js([]),
     "Dictionary yanday-bungal = get ready/prepare.", "0.84"),
    (47, "The woman prepared the grass.",
     "Jalbu-ngku jikan yanday-bungan.",
     "jalbu-ngku jikan yanday-bunga-ny | woman-ERG grass ready-CAUS-PAST",
     "handling_prepare", 5, js(["jalbu", "jikan", "yanday-bungal"]), js([]),
     "Dictionary yanday-bungal usage has jikan yanday-bunga.", "0.88"),
    (48, "They put the bones together.",
     "Jana bajibay muru-bungan.",
     "jana bajibay muru-bunga-ny | they bone together-CAUS-PAST",
     "handling_put_together", 4, js(["jana", "bajibay", "muru-bungal"]), js([]),
     "Muru-bungal = put things together; text 51 dog-bone context supports object domain.", "0.84"),
    (49, "The man tied the spear with rope.",
     "Dingkar-angka kalka karra-bu kuna-bungan.",
     "dingkar-angka kalka karra-bu kuna-bunga-ny | man-ERG spear rope-INST join/tie-CAUS-PAST",
     "handling_join_tie", 5, js(["dingkar", "kalka", "karra", "kuna-bungal"]), js([]),
     "Kuna-bungal = join/tie something to something; instrument rope included.", "0.84"),
    (50, "The woman closed the bag.",
     "Jalbu-ngku kurmun nandan.",
     "jalbu-ngku kurmun nanda-ny | woman-ERG bag close-PAST",
     "handling_close", 4, js(["jalbu", "kurmun", "nandal"]), js([]),
     "Dictionary nandal = close/shut/bury; simple close-object row.", "0.84"),
]


def scan_static() -> list[str]:
    issues: list[str] = []
    if len(ROWS) != 50:
        issues.append(f"expected 50 rows, got {len(ROWS)}")
    seqs = [r[0] for r in ROWS]
    if seqs != list(range(1, 51)):
        issues.append(f"seqs must be 1..50, got {seqs[:5]}...{seqs[-5:]}")
    seen_ku: set[str] = set()
    for row in ROWS:
        seq, english, kuku, analysis, _frame, _tier, words_used, loans_used, _evidence, _confidence = row
        label = f"{LABEL}.{seq:02d}"
        if not english[:1].isupper() or english.rstrip("'\"").rstrip()[-1:] not in ".!?":
            issues.append(f"{label}: English must be capitalized and final-punctuated")
        if " | " not in analysis:
            issues.append(f"{label}: analysis missing gloss separator")
        if not kuku[:1].isupper():
            issues.append(f"{label}: Kuku surface must start uppercase")
        for field_name, payload in (("words_used", words_used), ("loans_used", loans_used)):
            try:
                json.loads(payload)
            except Exception as exc:
                issues.append(f"{label}: {field_name} is not JSON: {exc}")
        low = f" {kuku.lower()} {analysis.lower()} "
        for word in DENY:
            if re.search(rf"(?<![a-z]){re.escape(word)}(?![a-z])", low):
                issues.append(f"{label}: denied phantom/wrong-sense/loan token {word!r}")
        if kuku.lower() in seen_ku:
            issues.append(f"{label}: duplicate Kuku surface within batch")
        seen_ku.add(kuku.lower())
    return issues


def main() -> int:
    issues = scan_static()
    if issues:
        print("pre-bank scan failed:")
        for issue in issues:
            print(f"  - {issue}")
        return 1

    db = sqlite3.connect(DB)
    db.execute("PRAGMA foreign_keys = ON")
    db.execute("BEGIN")
    existing = db.execute("SELECT id FROM batches WHERE label=?", (LABEL,)).fetchone()
    if existing:
        batch_id = existing[0]
        accepted = db.execute(
            "SELECT COUNT(*) FROM sentences WHERE batch_id=? AND status IN ('accepted','revised')",
            (batch_id,),
        ).fetchone()[0]
        if accepted:
            raise SystemExit(f"{LABEL} already has accepted/revised rows; refusing to overwrite")
        db.execute("DELETE FROM reviews WHERE sentence_id IN (SELECT id FROM sentences WHERE batch_id=?)", (batch_id,))
        db.execute("DELETE FROM sentences WHERE batch_id=?", (batch_id,))
        db.execute("UPDATE batches SET notes=?, created_at=? WHERE id=?", ("rebanked under_review", NOW, batch_id))
    else:
        cur = db.execute(
            "INSERT INTO batches(label, cheatsheet_version, guide_version, notes, created_at) VALUES(?,?,?,?,?)",
            (LABEL, "Rule 95 draft: handling/transfer verbs", "operator-guide-2026-07-07", "under_review", NOW),
        )
        batch_id = cur.lastrowid

    dupes = []
    for seq, _english, kuku, *_rest in ROWS:
        hit = db.execute(
            """
            SELECT b.label, s.seq
            FROM sentences s JOIN batches b ON b.id=s.batch_id
            WHERE lower(trim(s.kuku))=lower(trim(?))
              AND s.status!='rejected'
              AND s.batch_id!=?
            """,
            (kuku, batch_id),
        ).fetchone()
        if hit:
            dupes.append(f"{LABEL}.{seq:02d} duplicates {hit[0]}.{hit[1]:02d}: {kuku}")
    if dupes:
        db.rollback()
        print("cross-corpus duplicate check failed:")
        for dupe in dupes:
            print(f"  - {dupe}")
        return 1

    db.executemany(
        """
        INSERT INTO sentences(
            batch_id, seq, english, kuku, analysis, frame, tier, words_used,
            loans_used, evidence, status, confidence, rights_status, author,
            created_at, updated_at
        ) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
        """,
        [
            (
                batch_id, seq, english, kuku, analysis, frame, tier, words_used,
                loans_used, evidence, "under_review", confidence,
                "project_approved_synthetic_pending_elder_verification",
                "claude-main", NOW, NOW,
            )
            for seq, english, kuku, analysis, frame, tier, words_used, loans_used, evidence, confidence in ROWS
        ],
    )
    db.commit()
    count = db.execute("SELECT COUNT(*) FROM sentences WHERE batch_id=?", (batch_id,)).fetchone()[0]
    avg_words = db.execute(
        "SELECT AVG(LENGTH(kuku)-LENGTH(REPLACE(kuku,' ',''))+1.0) FROM sentences WHERE batch_id=?",
        (batch_id,),
    ).fetchone()[0]
    print(f"banked {LABEL}: {count} rows under_review; avg Kuku words/row={avg_words:.2f}")
    return 0


if __name__ == "__main__":
    sys.exit(main())
