#!/usr/bin/env python3
"""Build the ViewRegatta team-racing commentary corpus workbook.

The workbook is generated from the committed tactical-event catalogue, Director rules, and race
manifests. Keeping the workbook reproducible prevents its taxonomy from drifting away from the
broadcast engine's documented knowledge.
"""

from __future__ import annotations

import json
from pathlib import Path

from openpyxl import Workbook
from openpyxl.formatting.rule import ColorScaleRule
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.datavalidation import DataValidation

ROOT = Path(__file__).resolve().parents[4]
KNOWLEDGE = ROOT / "v3/scoring/knowledge"
RACE_INTELLIGENCE = ROOT / "v3/broadcast/demo/data/race_intelligence"
OUTPUT = RACE_INTELLIGENCE / "ViewRegatta_Team_Racing_Commentary_Corpus.xlsx"

HEADER_FILL = PatternFill("solid", fgColor="17365D")
SECTION_FILL = PatternFill("solid", fgColor="D9EAF7")
HEADER_FONT = Font(color="FFFFFF", bold=True)


def load_json(path: Path) -> dict:
    return json.loads(path.read_text(encoding="utf-8"))


def add_table_sheet(wb: Workbook, title: str, headers: list[str], rows: list[list[object]]):
    ws = wb.create_sheet(title)
    ws.append(headers)
    for row in rows:
        ws.append(row)
    ws.freeze_panes = "A2"
    ws.auto_filter.ref = ws.dimensions
    for cell in ws[1]:
        cell.fill = HEADER_FILL
        cell.font = HEADER_FONT
        cell.alignment = Alignment(wrap_text=True, vertical="top")
    for row in ws.iter_rows(min_row=2):
        for cell in row:
            cell.alignment = Alignment(wrap_text=True, vertical="top")
    for col in range(1, len(headers) + 1):
        values = [str(ws.cell(r, col).value or "") for r in range(1, min(ws.max_row, 100) + 1)]
        width = min(48, max(12, max(map(len, values)) + 2))
        ws.column_dimensions[get_column_letter(col)].width = width
    return ws


def add_list_validation(ws, column: str, values: list[str], start: int = 2, end: int = 5000):
    formula = '"' + ",".join(values) + '"'
    validation = DataValidation(type="list", formula1=formula, allow_blank=True)
    ws.add_data_validation(validation)
    validation.add(f"{column}{start}:{column}{end}")


def build() -> Path:
    events = load_json(KNOWLEDGE / "tactical_events.json")["events"]
    director = load_json(KNOWLEDGE / "director_rules.json")["rules"]
    manifests = [load_json(path) for path in sorted(RACE_INTELLIGENCE.glob("*_manifest.json"))]

    wb = Workbook()
    readme = wb.active
    readme.title = "Read Me"
    readme_rows = [
        ("ViewRegatta Team-Racing Commentary Corpus", "Version 2 — generated workbook"),
        ("Purpose", "Create traceable training and evaluation data for race intelligence, automated directing, graphics, replay selection, and commentary."),
        ("Authority", "Observed facts and official race data outrank tactical inference. Never convert a telemetry candidate into a rule breach, penalty, OCS, or official result without confirmation."),
        ("Workflow 1", "Register the video and telemetry snapshot in Race Register."),
        ("Workflow 2", "Add one row per meaningful moment in Annotations. Silence and correct wide shots are valid annotations."),
        ("Workflow 3", "Link every tactical claim to a Tactical Event ID and every camera decision to a Director Rule ID."),
        ("Workflow 4", "Have a sailing expert review tactical meaning and a broadcast reviewer assess framing, timing, and clarity."),
        ("Workflow 5", "Use only rows with QA Status=approved as gold evaluation or training material."),
        ("Copyright", "Store normalized meanings and short review excerpts. Do not redistribute full third-party transcripts without permission."),
        ("Regeneration", "Run: PYTHONPATH=<openpyxl> python3 v3/broadcast/demo/export/build_commentary_corpus.py"),
    ]
    for key, value in readme_rows:
        readme.append([key, value])
    readme.column_dimensions["A"].width = 24
    readme.column_dimensions["B"].width = 110
    for row in readme.iter_rows():
        row[0].font = Font(bold=True)
        row[0].fill = SECTION_FILL
        for cell in row:
            cell.alignment = Alignment(wrap_text=True, vertical="top")

    source_rows = [
        ["SRC-GSYS-PLAYBOOK", "GSYS Team Racing Clinic 2026", "tactical playbook", "Per Englund; Andreas Rosenlew; Bo Johannisson", "2026", "user supplied", "licensed/internal review required", "imported", "Canonical tactical source; 56 pages."],
        ["SRC-GSYS06-VIDEO", "GSYS06 expert-commentary video", "video", "Martin Alsén", "2026", "local file", "internal", "available", "Bound to Race 0001 by SHA-256."],
        ["SRC-ICSA", "ICSA team-racing broadcast archive", "source family", "ICSA", "", "URL to verify", "research only until rights confirmed", "discovery", "Prioritize races with expert commentary and results."],
        ["SRC-WILSON", "Wilson Trophy broadcasts", "source family", "Wilson Trophy / event producers", "", "URL to verify", "research only until rights confirmed", "discovery", "Useful British team-racing vocabulary and specialist commentary."],
    ]
    source_ws = add_table_sheet(wb, "Source Register", ["Source ID", "Title", "Type", "Creator", "Year", "Location", "Rights", "Status", "Notes"], source_rows)
    add_list_validation(source_ws, "H", ["discovery", "queued", "available", "imported", "blocked", "retired"])

    race_rows = []
    for manifest in manifests:
        video = manifest["video"]
        sync = manifest["sync"]
        race_rows.append([
            manifest["corpus_id"], manifest["race_id"], manifest["race"]["format"],
            manifest["race"]["gun_utc"], video["source_filename"], video["duration_seconds"],
            f'{video["width"]}x{video["height"]}', video["sha256"],
            sync["primary_anchor"]["video_time_seconds"], sync["status"],
            manifest["telemetry"]["snapshot_status"], "not_started", ""
        ])
    race_ws = add_table_sheet(wb, "Race Register", ["Corpus Race ID", "Race ID", "Format", "Gun UTC", "Video", "Duration s", "Resolution", "SHA-256", "Gun Video Time s", "Sync Status", "Telemetry Status", "Review Status", "Notes"], race_rows)
    add_list_validation(race_ws, "L", ["not_started", "in_progress", "expert_review", "approved", "blocked"])

    event_rows = [[
        event["id"], event["label_en"], event["phase"], ", ".join(event["applies_to"]),
        event["level"], " AND ".join(event["observables"]),
        ", ".join(map(str, event["rules"])) or "none", event["confidence_floor"],
        event["broadcast_value"], event["shot"], event["implementation"], event.get("note", "")
    ] for event in events]
    event_ws = add_table_sheet(wb, "Tactical Taxonomy", ["Event ID", "English Label", "Phase", "Formats", "Inference Level", "Observable Signature", "Rules", "Confidence Floor", "Broadcast Value", "Recommended Shot", "Implementation", "Notes"], event_rows)
    event_ws.conditional_formatting.add(f"I2:I{event_ws.max_row}", ColorScaleRule(start_type="min", start_color="63BE7B", mid_type="percentile", mid_value=50, mid_color="FFEB84", end_type="max", end_color="F8696B"))

    director_rows = [[
        rule["id"], rule["priority"], " AND ".join(rule.get("when", [])), rule.get("subject", ""),
        rule.get("shot", ""), rule.get("commentary", ""), rule.get("hold_until", ""),
        ", ".join(rule.get("allow", [])), ", ".join(rule.get("forbid", [])), rule.get("then", rule.get("action", ""))
    ] for rule in director]
    add_table_sheet(wb, "Director Rules", ["Rule ID", "Priority", "Conditions", "Subject", "Shot", "Commentary Action", "Hold Until", "Allowed", "Forbidden", "Then / Action"], director_rows)

    annotation_headers = [
        "Annotation ID", "Corpus Race ID", "Video Start s", "Video End s", "Race UTC",
        "Phase", "Observed Facts", "Tactical Event ID", "Inference Level", "Confidence",
        "Why It Matters", "Winning Team Before", "Winning Team After", "Focus Boats",
        "Camera Actual", "Camera Verdict", "Director Rule ID", "Camera Recommended",
        "Commentary Actual", "Commentary Function", "Commentary Recommended", "Speak?",
        "Broadcast Value", "Expert Reviewer", "QA Status", "QA Notes"
    ]
    annotations = add_table_sheet(wb, "Annotations", annotation_headers, [])
    add_list_validation(annotations, "F", ["prestart", "start", "upwind", "windward_mark", "reach", "reach_mark", "downwind", "leeward_mark", "finish", "post_finish"])
    add_list_validation(annotations, "I", ["observed", "derived_candidate", "confirmed"])
    add_list_validation(annotations, "P", ["correct", "late", "wrong_subject", "too_tight", "too_wide", "missed_event", "not_applicable"])
    add_list_validation(annotations, "T", ["observation", "interpretation", "prediction", "evaluation", "instruction", "teaching", "story", "silence"])
    add_list_validation(annotations, "V", ["yes", "no", "optional"])
    add_list_validation(annotations, "Y", ["draft", "needs_expert", "needs_broadcast_review", "approved", "rejected"])

    queue_rows = []
    for manifest in manifests:
        queue_rows.extend([
            ["Q-GSYS06-001", manifest["corpus_id"], "Identify start-gun video timestamp", "sync", 100, "ready", "Set primary video-to-UTC anchor."],
            ["Q-GSYS06-002", manifest["corpus_id"], "First-pass race narrative", "story", 90, "blocked_by_sync", "Record turning points without tactical overclaiming."],
            ["Q-GSYS06-003", manifest["corpus_id"], "Expert attention and camera divergence", "director", 95, "ready", "Mark every point where expert focus differs from the actual camera."],
            ["Q-GSYS06-004", manifest["corpus_id"], "Telemetry-backed tactical event log", "race_intelligence", 100, "awaiting_snapshot", "Validate candidates against full-course telemetry."],
        ])
    queue_ws = add_table_sheet(wb, "Extraction Queue", ["Queue ID", "Corpus Race ID", "Task", "Workstream", "Priority", "Status", "Acceptance Note"], queue_rows)
    add_list_validation(queue_ws, "F", ["ready", "in_progress", "blocked_by_sync", "awaiting_snapshot", "expert_review", "done"])

    schema_rows = [
        [header, "required" if header in {"Annotation ID", "Corpus Race ID", "Video Start s", "Observed Facts", "Inference Level", "QA Status"} else "conditional", "See sheet validation or controlled vocabulary.", "Do not mix observations with interpretations."]
        for header in annotation_headers
    ]
    add_table_sheet(wb, "Annotation Schema", ["Field", "Requirement", "Value Rule", "Guidance"], schema_rows)

    vocab_rows = [
        ["inference_level", "observed", "Directly measurable or visible fact."],
        ["inference_level", "derived_candidate", "Tactical interpretation with confidence and evidence."],
        ["inference_level", "confirmed", "Official, manual, or otherwise authoritative race fact."],
        ["camera_verdict", "missed_event", "The decisive event was not shown."],
        ["commentary_function", "instruction", "Expert attention cue such as 'watch the trailing pair'."],
        ["commentary_function", "silence", "No line should be spoken."],
        ["qa_status", "approved", "Suitable as gold evaluation/training material."],
    ]
    add_table_sheet(wb, "Controlled Vocabulary", ["Vocabulary", "Value", "Definition"], vocab_rows)

    wb.calculation.fullCalcOnLoad = True
    wb.calculation.forceFullCalc = True
    OUTPUT.parent.mkdir(parents=True, exist_ok=True)
    wb.save(OUTPUT)
    return OUTPUT


if __name__ == "__main__":
    print(build())