#!/usr/bin/env python3
"""Merge supported English Teams attendance CSVs without changing source values.

Python 3.11+ standard library only. No network, account access, date parsing,
identity matching, duration calculation, or CSV output. See --help.
"""
from __future__ import annotations

import argparse
import csv
import hashlib
import io
import json
import os
from pathlib import Path
import re
import sys
import zipfile
from datetime import datetime, timezone
from xml.etree import ElementTree as ET


PARTICIPANT_FIELDS = (
    "Name", "First Join", "Last Leave", "In-Meeting Duration", "Email",
    "Participant ID (UPN)", "Role",
)
ACTIVITY_FIELDS = ("Name", "Join Time", "Leave Time", "Duration", "Email", "Role")
SUMMARY_FIELDS = (
    "Meeting title", "Attended participants", "Start time", "End time",
    "Meeting duration", "Average attendance time",
)
SECTIONS = ("1. Summary", "2. Participants", "3. In-Meeting Activities")
DELIMITERS = (("tab", "\t"), ("comma", ","), ("semicolon", ";"))
NS = "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
REL = "http://schemas.openxmlformats.org/officeDocument/2006/relationships"
PKG_REL = "http://schemas.openxmlformats.org/package/2006/relationships"
XML_SPACE = "{http://www.w3.org/XML/1998/namespace}space"
ET.register_namespace("", NS)
ET.register_namespace("r", REL)


class InvalidReport(ValueError):
    pass


class BatchError(ValueError):
    def __init__(self, failures):
        self.failures = failures
        super().__init__("All input validation must pass; no output was created.")


def check_text(value: str) -> None:
    # XML 1.0 and Excel cell limits: reject rather than silently lose characters.
    if any(not (c in "\t\n\r" or 0x20 <= ord(c) <= 0xD7FF
                or 0xE000 <= ord(c) <= 0xFFFD or 0x10000 <= ord(c) <= 0x10FFFF)
           for c in value):
        raise InvalidReport("unsupported XML control character in a field")
    if len(value.encode("utf-16-le")) // 2 > 32767:
        raise InvalidReport("field exceeds the workbook's 32,767 UTF-16-unit cell limit")


def decode_report(raw: bytes) -> tuple[str, str]:
    try:
        if raw.startswith((b"\xff\xfe\x00\x00", b"\x00\x00\xfe\xff")):
            raise InvalidReport("UTF-32 is unsupported")
        if raw.startswith((b"\xff\xfe", b"\xfe\xff")):
            return raw.decode("utf-16"), "UTF-16 BOM"
        if raw.startswith(b"\xef\xbb\xbf"):
            return raw.decode("utf-8-sig"), "UTF-8 BOM"
        return raw.decode("utf-8"), "UTF-8"
    except UnicodeError as exc:
        raise InvalidReport("expected UTF-8, UTF-8 BOM, or UTF-16 with BOM") from exc


def marker(row, expected):
    return bool(row) and row[0] == expected and all(v == "" for v in row[1:])


def blank(row):
    return not row or all(v == "" for v in row)


def parse_with_delimiter(text, delimiter):
    reader = csv.reader(io.StringIO(text, newline=""), delimiter=delimiter,
                        quotechar='"', doublequote=True, strict=True)
    records = []
    try:
        for row in reader:
            for value in row:
                check_text(value)
            records.append((reader.line_num, row))
    except (csv.Error, InvalidReport) as exc:
        raise InvalidReport(f"CSV ending at physical line {reader.line_num}: {exc}") from exc
    # Blank separator records may have harmless trailing empty fields.
    records = [(line, row) for line, row in records if not blank(row)]
    pos = 0

    def take_section(section):
        nonlocal pos
        if pos >= len(records) or not marker(records[pos][1], section):
            line = records[pos][0] if pos < len(records) else "EOF"
            raise InvalidReport(f"line {line}: expected section {section!r}")
        pos += 1

    def take_header(fields):
        nonlocal pos
        if pos >= len(records) or tuple(records[pos][1]) != fields:
            line = records[pos][0] if pos < len(records) else "EOF"
            raise InvalidReport(f"line {line}: expected exact header {list(fields)!r}")
        pos += 1

    take_section(SECTIONS[0])
    summary = []
    seen = set()
    while pos < len(records) and not marker(records[pos][1], SECTIONS[1]):
        line, row = records[pos]
        if (len(row) < 2 or any(row[2:]) or not row[0].strip()
                or re.match(r"^\d+\.\s", row[0])):
            raise InvalidReport(f"line {line}: expected Summary key/value or 2. Participants")
        key, value = row[:2]
        if key in seen:
            raise InvalidReport(f"line {line}: duplicate Summary key {key!r}")
        seen.add(key)
        summary.append([key, value])
        pos += 1
    missing = [key for key in SUMMARY_FIELDS if key not in seen]
    if missing:
        raise InvalidReport(f"missing required Summary keys: {missing!r}")
    values = dict(summary)
    if any(not values[key].strip() for key in SUMMARY_FIELDS[1:]):
        raise InvalidReport("only Meeting title may be blank among the six required Summary values")
    take_section(SECTIONS[1])
    take_header(PARTICIPANT_FIELDS)
    participants = []
    while pos < len(records) and not marker(records[pos][1], SECTIONS[2]):
        line, row = records[pos]
        if len(row) != len(PARTICIPANT_FIELDS) or tuple(row) == PARTICIPANT_FIELDS:
            raise InvalidReport(f"line {line}: expected one Participants row with exactly 7 fields")
        if any(not row[i].strip() for i in (0, 1, 2, 3, 6)):
            raise InvalidReport(f"line {line}: required Participants value is blank")
        participants.append(row)
        pos += 1
    take_section(SECTIONS[2])
    take_header(ACTIVITY_FIELDS)
    activity_rows = 0
    while pos < len(records):
        line, row = records[pos]
        if len(row) != len(ACTIVITY_FIELDS) or tuple(row) == ACTIVITY_FIELDS:
            raise InvalidReport(f"line {line}: expected one Activities row with exactly 6 fields")
        if any(not row[i].strip() for i in (0, 1, 2, 3, 5)):
            raise InvalidReport(f"line {line}: required Activities value is blank")
        activity_rows += 1
        pos += 1
    return {"summary": summary, "participants": participants,
            "activity_rows_excluded": activity_rows}


def parse_report(raw):
    text, encoding = decode_report(raw)
    matches, errors = [], []
    for name, delimiter in DELIMITERS:
        try:
            parsed = parse_with_delimiter(text, delimiter)
            parsed.update(encoding=encoding, delimiter=name)
            matches.append(parsed)
        except InvalidReport as exc:
            errors.append(f"{name}: {exc}")
    if len(matches) != 1:
        if matches:
            raise InvalidReport("ambiguous delimiter: more than one complete structural match")
        raise InvalidReport("unsupported or malformed report; " + " | ".join(errors))
    return matches[0]


def count_check(raw_value, row_count):
    if re.fullmatch(r"[0-9]+", raw_value):
        declared = int(raw_value)
        if declared == row_count:
            return "matches Participants row count; not a unique-person verification"
        return f"DIFFERS: Summary={raw_value}; Participants rows={row_count}; inspect source"
    return "not compared: Summary count is not plain decimal digits"


def inspect_inputs(input_dir):
    if not input_dir.is_dir():
        raise ValueError("input must be an existing directory")
    paths = sorted((p for p in input_dir.iterdir() if p.suffix.lower() == ".csv"),
                   key=lambda p: (p.name.casefold(), p.name))
    if not paths:
        raise ValueError("no .csv files found directly inside the input directory")
    files, retained, failures, seen = [], [], [], {}
    for path in paths:
        try:
            if path.is_symlink() or not path.is_file():
                raise InvalidReport("CSV input must be a regular file, not a symlink or directory")
            check_text(path.name)
            raw = path.read_bytes()
            sha = hashlib.sha256(raw).hexdigest()
            parsed = parse_report(raw)
            duplicate_of = seen.get(sha, "")
            info = {"filename": path.name, "sha256": sha, "bytes": len(raw),
                    "encoding": parsed["encoding"], "delimiter": parsed["delimiter"],
                    "participant_rows": len(parsed["participants"]),
                    "activity_rows_excluded": parsed["activity_rows_excluded"],
                    "summary": parsed["summary"],
                    "summary_attended_participants": dict(parsed["summary"])["Attended participants"],
                    "duplicate_of": duplicate_of,
                    "status": "exact_duplicate_skipped" if duplicate_of else "included"}
            info["count_check"] = count_check(info["summary_attended_participants"], info["participant_rows"])
            files.append(info)
            if not duplicate_of:
                seen[sha] = path.name
                retained.append((info, parsed["participants"]))
        except (OSError, ValueError) as exc:
            failures.append({"filename": path.name, "error": str(exc)})
    if failures:
        raise BatchError(failures)
    return files, retained


def column_name(index):
    result = ""
    while index:
        index, rem = divmod(index - 1, 26)
        result = chr(65 + rem) + result
    return result


def xml_bytes(root):
    # Literal CR must use a character reference: XML parsers otherwise normalize it.
    return ET.tostring(root, encoding="utf-8", xml_declaration=True).replace(b"\r", b"&#13;")


def literal_xstring(value):
    # OOXML ST_Xstring: escape the leading underscore of literal _xHHHH_ text.
    # Match only that underscore so adjoining/overlapping patterns are preserved.
    # MS-OI29500 2.1.1747, Part 1 section 22.9.2.19.
    escaped = re.sub(r"_(?=x[0-9A-Fa-f]{4}_)", "_x005F_", value)
    # Escape real CR only after protecting literal tokens. LF/tab remain literal.
    return escaped.replace("\r", "_x000D_")


def make_sheet(headers, rows):
    if len(headers) > 16384 or len(rows) + 1 > 1048576:
        raise ValueError("workbook sheet exceeds Excel's row or column limit")
    sheet = ET.Element(f"{{{NS}}}worksheet")
    ET.SubElement(sheet, f"{{{NS}}}dimension", ref=f"A1:{column_name(len(headers))}{len(rows) + 1}")
    views = ET.SubElement(sheet, f"{{{NS}}}sheetViews")
    view = ET.SubElement(views, f"{{{NS}}}sheetView", workbookViewId="0")
    ET.SubElement(view, f"{{{NS}}}pane", ySplit="1", topLeftCell="A2", activePane="bottomLeft", state="frozen")
    cols = ET.SubElement(sheet, f"{{{NS}}}cols")
    for n, header in enumerate(headers, 1):
        width = 32 if "file" in header.lower() else 25
        if "SHA" in header:
            width = 22
        ET.SubElement(cols, f"{{{NS}}}col", min=str(n), max=str(n), width=str(width), customWidth="1")
    data = ET.SubElement(sheet, f"{{{NS}}}sheetData")
    for n, values in enumerate([headers, *rows], 1):
        if len(values) != len(headers):
            raise ValueError("internal worksheet width mismatch")
        row = ET.SubElement(data, f"{{{NS}}}row", r=str(n))
        for col, value in enumerate(values, 1):
            value = str(value)
            check_text(value)
            cell = ET.SubElement(row, f"{{{NS}}}c", r=f"{column_name(col)}{n}",
                                 t="inlineStr", s="1" if n == 1 else "0")
            inline = ET.SubElement(cell, f"{{{NS}}}is")
            text = ET.SubElement(inline, f"{{{NS}}}t", {XML_SPACE: "preserve"})
            text.text = literal_xstring(value)
    ET.SubElement(sheet, f"{{{NS}}}autoFilter", ref=f"A1:{column_name(len(headers))}{len(rows) + 1}")
    return xml_bytes(sheet)


def build_workbook(sheets):
    types_ns = "http://schemas.openxmlformats.org/package/2006/content-types"
    types = ET.Element(f"{{{types_ns}}}Types")
    for ext, mime in (("rels", "application/vnd.openxmlformats-package.relationships+xml"), ("xml", "application/xml")):
        ET.SubElement(types, f"{{{types_ns}}}Default", Extension=ext, ContentType=mime)
    for name, kind in (("workbook.xml", "sheet.main"), ("styles.xml", "styles")):
        ET.SubElement(types, f"{{{types_ns}}}Override", PartName=f"/xl/{name}",
                      ContentType=f"application/vnd.openxmlformats-officedocument.spreadsheetml.{kind}+xml")
    root_rel = ET.Element(f"{{{PKG_REL}}}Relationships")
    ET.SubElement(root_rel, f"{{{PKG_REL}}}Relationship", Id="rId1", Type=REL + "/officeDocument", Target="xl/workbook.xml")
    workbook = ET.Element(f"{{{NS}}}workbook")
    sheet_list = ET.SubElement(workbook, f"{{{NS}}}sheets")
    workbook_rels = ET.Element(f"{{{PKG_REL}}}Relationships")
    parts = {}
    for n, (name, headers, rows) in enumerate(sheets, 1):
        ET.SubElement(sheet_list, f"{{{NS}}}sheet", name=name, sheetId=str(n), **{f"{{{REL}}}id": f"rId{n}"})
        ET.SubElement(workbook_rels, f"{{{PKG_REL}}}Relationship", Id=f"rId{n}", Type=REL + "/worksheet", Target=f"worksheets/sheet{n}.xml")
        ET.SubElement(types, f"{{{types_ns}}}Override", PartName=f"/xl/worksheets/sheet{n}.xml",
                      ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml")
        parts[f"xl/worksheets/sheet{n}.xml"] = make_sheet(headers, rows)
    ET.SubElement(workbook_rels, f"{{{PKG_REL}}}Relationship", Id=f"rId{len(sheets)+1}", Type=REL + "/styles", Target="styles.xml")
    styles = f'''<?xml version="1.0" encoding="UTF-8"?>
<styleSheet xmlns="{NS}"><fonts count="2"><font><sz val="11"/><name val="Calibri"/></font><font><b/><sz val="11"/><color rgb="FFFFFFFF"/><name val="Calibri"/></font></fonts><fills count="3"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill><fill><patternFill patternType="solid"><fgColor rgb="FF23465A"/><bgColor indexed="64"/></patternFill></fill></fills><borders count="1"><border/></borders><cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs><cellXfs count="2"><xf numFmtId="49" fontId="0" fillId="0" borderId="0" xfId="0" applyAlignment="1"><alignment vertical="top" wrapText="1"/></xf><xf numFmtId="49" fontId="1" fillId="2" borderId="0" xfId="0" applyAlignment="1"><alignment vertical="top" wrapText="1"/></xf></cellXfs><cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles></styleSheet>'''
    parts.update({"[Content_Types].xml": xml_bytes(types), "_rels/.rels": xml_bytes(root_rel),
                  "xl/workbook.xml": xml_bytes(workbook), "xl/_rels/workbook.xml.rels": xml_bytes(workbook_rels),
                  "xl/styles.xml": styles.encode("utf-8")})
    output = io.BytesIO()
    with zipfile.ZipFile(output, "w", zipfile.ZIP_DEFLATED) as archive:
        for name, payload in parts.items():
            archive.writestr(name, payload)
    return output.getvalue()


def prepare_outputs(files, retained, input_dir):
    extra_keys = sorted({key for info in files for key, _ in info["summary"] if key not in SUMMARY_FIELDS})
    keys = [*SUMMARY_FIELDS, *extra_keys]
    provenance = ["Source file", "Source SHA256"]
    summary_headers = ["Summary: " + key for key in keys]
    participant_rows, meeting_rows = [], []
    for info, participants in retained:
        mapping = dict(info["summary"])
        values = [mapping.get(key, "") for key in keys]
        prefix = [info["filename"], info["sha256"]]
        for row in participants:
            participant_rows.append([*row, *prefix, *values])
        meeting_rows.append([*prefix, str(info["participant_rows"]), info["count_check"], *values])
    file_headers = ["Source file", "Source SHA256", "Status", "Duplicate of", "Bytes", "Encoding", "Delimiter",
                    "Participants rows", "Activities rows excluded", "Summary: Attended participants", "Count check"]
    file_rows = [[f["filename"], f["sha256"], f["status"], f["duplicate_of"], str(f["bytes"]), f["encoding"], f["delimiter"],
                  str(f["participant_rows"]), str(f["activity_rows_excluded"]), f["summary_attended_participants"], f["count_check"]]
                 for f in files]
    workbook = build_workbook([
        ("Participants", [*PARTICIPANT_FIELDS, *provenance, *summary_headers], participant_rows),
        ("Meetings", [*provenance, "Participants rows", "Count check", *summary_headers], meeting_rows),
        ("Files", file_headers, file_rows),
    ])
    audit = {"schema_version": 1, "status": "complete", "created_at": datetime.now(timezone.utc).isoformat(),
             "input_directory": str(input_dir.resolve()),
             "input_file_count": len(files), "retained_report_count": len(retained),
             "exact_duplicate_file_count": len(files) - len(retained),
             "participant_rows_written": len(participant_rows), "files": files,
             "retained_reports": [
                 {"source_file": info["filename"], "source_sha256": info["sha256"],
                  "summary": info["summary"], "participant_headers": list(PARTICIPANT_FIELDS),
                  "participants": participants}
                 for info, participants in retained],
             "workbook": {"filename": "teams-attendance.xlsx", "sha256": hashlib.sha256(workbook).hexdigest()},
             "rules": ["Only directly contained CSV files are read; source files are not changed.",
                       "Only byte-identical SHA256 copies are skipped; different hashes may still describe the same meeting.",
                       "Meetings contains one row per retained report, not a verified count of distinct meetings.",
                       "Participants rows are not merged by name, email or UPN; row counts are not unique-person counts.",
                       "Original dates and durations are text; no date interpretation or duration arithmetic is performed.",
                       "All workbook cells are literal strings; there are no formulas.",
                       "Audit retained_reports preserves every original Participants value independently of spreadsheet readers or resaving.",
                       "All Summary keys and values are retained; audit summary arrays preserve their source order.",
                       "Activities are syntax/shape-validated but excluded from the Participants output."]}
    return workbook, (json.dumps(audit, ensure_ascii=False, indent=2) + "\n").encode("utf-8")


def merge(input_dir: Path, output_dir: Path):
    if output_dir.exists() or output_dir.is_symlink():
        raise ValueError("output path already exists; choose a new directory")
    if not output_dir.parent.is_dir():
        raise ValueError("output directory's parent must already exist")
    files, retained = inspect_inputs(input_dir)
    workbook, audit = prepare_outputs(files, retained, input_dir)
    # Exclusive mkdir prevents replacement even if the destination appeared mid-run.
    output_dir.mkdir(mode=0o700)
    created = []
    try:
        for name, payload in (("teams-attendance.xlsx", workbook), ("audit.json", audit)):
            path = output_dir / name
            with path.open("xb") as handle:
                created.append(path)
                handle.write(payload)
                handle.flush()
                os.fsync(handle.fileno())
    except BaseException:
        for path in created:
            path.unlink(missing_ok=True)
        output_dir.rmdir()
        raise
    return json.loads(audit)


def main(argv=None):
    parser = argparse.ArgumentParser(description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter)
    parser.add_argument("input_directory", type=Path, help="folder containing only the reports you intend to combine (direct .csv files)")
    parser.add_argument("--output-dir", required=True, type=Path, help="new, nonexistent directory; parent must exist")
    args = parser.parse_args(argv)
    try:
        audit = merge(args.input_directory, args.output_dir)
    except BatchError as exc:
        print(json.dumps({"status": "failed", "output_created": False, "errors": exc.failures}, ensure_ascii=False, indent=2), file=sys.stderr)
        return 2
    except (OSError, ValueError, zipfile.BadZipFile) as exc:
        print(f"ERROR: {exc}. No successful result was produced.", file=sys.stderr)
        return 2
    print(f"Created {args.output_dir / 'teams-attendance.xlsx'} and audit.json: "
          f"{audit['participant_rows_written']} participant rows from {audit['retained_report_count']} retained reports; "
          f"{audit['exact_duplicate_file_count']} exact-copy files skipped. Review Files, Meetings and audit.json.")
    return 0


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