#!/usr/bin/env python3
import csv
from pathlib import Path
import sys
from datetime import datetime

IN_KEYS = [
    "Hearing Type",
    "Judicial Officer",
    "Hearing Date",
    "Hearing Location",
    "Case Number",
    "Defendant",
    "Case Type",
    "Case Location",
]

OUT_HEADERS = [
    "Case Number",
    "Defendant",
    "Case Type",
    "Case Location",
    "Hearing Type",
    "Judicial Officer",
    "Hearing Location",
    "Hearing Date",
    "Hearing Time",
    "Hearing Date Sort",
]

def parse_hearing_datetime(raw):
    raw = (raw or "").strip()
    # Expect formats like "12/02/2025 01:30 PM"
    # Be tolerant to double spaces or missing leading zeros
    for fmt in ["%m/%d/%Y %I:%M %p", "%m/%d/%Y %H:%M"]:
        try:
            dt = datetime.strptime(raw, fmt)
            break
        except ValueError:
            dt = None
    if dt is None:
        # If unparseable, keep columns empty-safe
        return "", "", ""
    date_str = dt.strftime("%Y-%m-%d")
    time_str = dt.strftime("%H:%M")
    sort_str = dt.strftime("%Y-%m-%d-%H-%M")
    return date_str, time_str, sort_str

def is_blank_row(row):
    if not row:
        return True
    if len(row) == 1 and (row[0] or "").strip() == "":
        return True
    if len(row) >= 2 and (row[0] or "").strip() == "" and (row[1] or "").strip() == "":
        return True
    return False

def flush_group(buf, writer):
    if not buf:
        return
    # Must at least have Case Number or Defendant to be meaningful
    if not (buf.get("Case Number") or buf.get("Defendant")):
        return

    # Build output row
    date_str, time_str, sort_str = parse_hearing_datetime(buf.get("Hearing Date", ""))

    row = [
        buf.get("Case Number", "").strip(),
        buf.get("Defendant", "").strip(),
        buf.get("Case Type", "").strip(),
        buf.get("Case Location", "").strip(),
        buf.get("Hearing Type", "").strip(),
        buf.get("Judicial Officer", "").strip(),
        buf.get("Hearing Location", "").strip(),
        date_str,
        time_str,
        sort_str,
    ]
    writer.writerow(row)

def transform(input_csv, output_csv):
    # Read input
    rows = []
    with open(input_csv, newline='', encoding='utf-8') as f:
        rdr = csv.reader(f)
        for r in rdr:
            # normalize to 2 columns
            r = list(r)
            if len(r) < 2:
                r += [''] * (2 - len(r))
            elif len(r) > 2:
                r = r[:2]
            rows.append([c if c is not None else '' for c in r])

    buf = {}
    with open(output_csv, 'w', newline='', encoding='utf-8') as f:
        w = csv.writer(f)
        w.writerow(OUT_HEADERS)

        for k, v in rows:
            k_s = (k or "").strip()
            v_s = (v or "").strip()

            # New group starts at "Result X of Y" or a blank separator; flush the previous
            if k_s.startswith("Result ") or is_blank_row([k, v]):
                flush_group(buf, w)
                buf = {}
                continue

            # Capture only known keys
            if k_s in IN_KEYS:
                buf[k_s] = v_s

        # flush tail
        flush_group(buf, w)

def main():
    if len(sys.argv) < 3:
        print("Usage: transpose_hearings.py <input_csv> <output_csv>")
        sys.exit(1)
    transform(sys.argv[1], sys.argv[2])

if __name__ == "__main__":
    main()