from __future__ import annotations

import re
import sys
from pathlib import Path

from openpyxl import Workbook, load_workbook
from openpyxl.chart import BarChart, Reference
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.worksheet.table import Table, TableStyleInfo


SOURCE = Path(r"C:\Apache24\htdocs\railserp\HR & Payroll Manual E2E Test Script.md")
OUTPUT = Path(sys.argv[1]) if len(sys.argv) > 1 else Path(r"C:\Apache24\htdocs\railserp\HR & Payroll Manual E2E Test Workbook.xlsx")

NAVY = "111827"
TEAL = "0F766E"
LIGHT_TEAL = "CCFBF1"
LIGHT_BLUE = "DBEAFE"
LIGHT_GRAY = "F3F4F6"
WHITE = "FFFFFF"
RED = "DC2626"
LIGHT_RED = "FEE2E2"
AMBER = "D97706"
LIGHT_AMBER = "FEF3C7"
GREEN = "15803D"
LIGHT_GREEN = "DCFCE7"
BLUE = "2563EB"
GRAY = "6B7280"
thin = Side(style="thin", color="D1D5DB")


def clean(value: str) -> str:
    value = value.strip()
    value = re.sub(r"\s{2,}$", "", value)
    return value.replace("**", "").replace("`", "").strip()


def phase_for_position(text: str, position: int) -> str:
    headings = [(m.start(), clean(m.group(1))) for m in re.finditer(r"(?m)^## (\d+\. Phase [^\n]+)", text)]
    eligible = [title for start, title in headings if start < position]
    return eligible[-1] if eligible else "Foundation"


def parse_deep_tests(text: str) -> list[dict[str, str]]:
    matches = list(re.finditer(r"(?m)^### (E2E-\d+): ([^\n]+)", text))
    rows = []
    for index, match in enumerate(matches):
        block = text[match.end(): matches[index + 1].start() if index + 1 < len(matches) else text.find("## 18.", match.end())]
        field = lambda name: clean((re.search(rf"(?m)^\*\*{re.escape(name)}:\*\*\s*(.+)$", block) or [None, ""])[1])
        steps_match = re.search(r"\*\*Steps:\*\*\s*(.*?)(?=\n\*\*Expected outcome:)", block, re.S)
        steps = "\n".join(clean(line) for line in (steps_match.group(1).strip().splitlines() if steps_match else []) if clean(line))
        phase = phase_for_position(text, match.start())
        domain = re.sub(r"^\d+\. Phase [A-Z]:\s*", "", phase)
        rows.append({
            "Test ID": match.group(1), "Phase": phase, "Domain": domain, "Test Case": clean(match.group(2)),
            "Routes": field("Routes"), "Preconditions": "See Sections 4-5 and preceding workflow state.",
            "Role / Tester": field("Role"), "Test Data": "Use unique <RUN> data from Section 5.",
            "Test Steps": steps, "Expected Outcome": field("Expected outcome"), "Actual Outcome": "",
            "Result": field("Result") or "Not Run", "Severity": "", "Defect ID": "", "Evidence": field("Evidence"),
            "Tested By": "", "Test Date": "", "Retest Result": "", "Retest Date": "", "Comments": "",
            "Build / Version": "", "Environment": "",
        })
    return rows


def parse_route_catalogue(text: str) -> list[dict[str, str]]:
    section = text.split("## 21. Complete Route Coverage Catalogue", 1)[1].split("## 22.", 1)[0]
    domain = ""
    rows = []
    for line in section.splitlines():
        if line.startswith("### 21."):
            domain = clean(re.sub(r"^### 21\.\d+\s*", "", line))
        match = re.match(r"^\| `([^`]+)` \| (.*?) \| (.*?) \|$", line)
        if match:
            route, expected, result = match.groups()
            rows.append({"Coverage ID": f"ROUTE-{len(rows)+1:03d}", "Domain": domain, "Route": route,
                         "Feature / Expected Outcome": clean(expected), "Result": clean(result), "Actual Outcome": "",
                         "Severity": "", "Defect ID": "", "Evidence": "", "Tested By": "", "Test Date": "",
                         "Comments": "", "Build / Version": "", "Environment": ""})
    return rows


def title(ws, text: str, subtitle: str, columns: int) -> None:
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=columns)
    ws["A1"] = text
    ws["A1"].font = Font(size=18, bold=True, color=WHITE)
    ws["A1"].fill = PatternFill("solid", fgColor=NAVY)
    ws["A1"].alignment = Alignment(vertical="center")
    ws.row_dimensions[1].height = 30
    ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=columns)
    ws["A2"] = subtitle
    ws["A2"].font = Font(size=10, color=GRAY, italic=True)
    ws["A2"].alignment = Alignment(wrap_text=True)
    ws.row_dimensions[2].height = 28


def write_table(ws, headers: list[str], records: list[dict[str, object]], table_name: str, widths: dict[str, int]) -> None:
    header_row = 4
    for col, header in enumerate(headers, 1):
        cell = ws.cell(header_row, col, header)
        cell.font = Font(bold=True, color=WHITE)
        cell.fill = PatternFill("solid", fgColor=TEAL)
        cell.alignment = Alignment(wrap_text=True, vertical="center")
        cell.border = Border(bottom=thin)
        ws.column_dimensions[cell.column_letter].width = widths.get(header, 16)
    for row_index, record in enumerate(records, header_row + 1):
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row_index, col, record.get(header, ""))
            cell.alignment = Alignment(wrap_text=True, vertical="top")
            cell.border = Border(bottom=thin)
        ws.row_dimensions[row_index].height = 52
    end = max(header_row + 1, header_row + len(records))
    table = Table(displayName=table_name, ref=f"A{header_row}:{ws.cell(end, len(headers)).coordinate}")
    table.tableStyleInfo = TableStyleInfo(name="TableStyleMedium2", showRowStripes=True, showFirstColumn=False, showLastColumn=False)
    ws.add_table(table)
    ws.freeze_panes = "A5"
    ws.auto_filter.ref = f"A{header_row}:{ws.cell(end, len(headers)).coordinate}"
    ws.sheet_view.showGridLines = False
    ws.page_setup.orientation = "landscape"
    ws.page_setup.fitToWidth = 1
    ws.sheet_properties.pageSetUpPr.fitToPage = True


def add_dropdown(ws, column: int, start: int, end: int, formula: str) -> None:
    validation = DataValidation(type="list", formula1=formula, allow_blank=True)
    validation.error = "Select a value from the list."
    validation.errorTitle = "Invalid value"
    ws.add_data_validation(validation)
    validation.add(f"{ws.cell(start, column).coordinate}:{ws.cell(end, column).coordinate}")


def add_status_formatting(ws, column: int, start: int, end: int) -> None:
    letter = ws.cell(start, column).column_letter
    colors = {"Pass": LIGHT_GREEN, "Fail": LIGHT_RED, "Blocked": LIGHT_AMBER, "Not Run": LIGHT_GRAY, "N/A": LIGHT_BLUE}
    for value, color in colors.items():
        ws.conditional_formatting.add(f"{letter}{start}:{letter}{end}", FormulaRule(formula=[f'${letter}{start}="{value}"'], fill=PatternFill("solid", fgColor=color)))


def build() -> None:
    markdown = SOURCE.read_text(encoding="utf-8")
    deep_tests = parse_deep_tests(markdown)
    route_rows = parse_route_catalogue(markdown)
    wb = Workbook()
    wb.remove(wb.active)

    ws = wb.create_sheet("Test Execution")
    headers = list(deep_tests[0].keys())
    title(ws, "HR & Payroll Manual Test Execution", "Deep end-to-end scenarios. Record actual outcomes and attach evidence before assigning Pass.", len(headers))
    write_table(ws, headers, deep_tests, "TestExecutionTable", {"Test ID": 12, "Phase": 25, "Domain": 24, "Test Case": 30, "Routes": 50, "Preconditions": 35, "Role / Tester": 22, "Test Data": 28, "Test Steps": 65, "Expected Outcome": 65, "Actual Outcome": 50, "Result": 13, "Severity": 12, "Defect ID": 14, "Evidence": 32, "Tested By": 18, "Test Date": 14, "Retest Result": 15, "Retest Date": 14, "Comments": 35, "Build / Version": 18, "Environment": 16})
    add_dropdown(ws, headers.index("Result") + 1, 5, 1000, "=Lists!$A$2:$A$6")
    add_dropdown(ws, headers.index("Retest Result") + 1, 5, 1000, "=Lists!$A$2:$A$6")
    add_dropdown(ws, headers.index("Severity") + 1, 5, 1000, "=Lists!$B$2:$B$5")
    add_status_formatting(ws, headers.index("Result") + 1, 5, 1000)

    ws = wb.create_sheet("Route Coverage")
    headers = list(route_rows[0].keys())
    title(ws, "HR & Payroll Route Coverage", "Every registered HR route plus user/access and accounting integration routes. Each row requires functional evidence.", len(headers))
    write_table(ws, headers, route_rows, "RouteCoverageTable", {"Coverage ID": 13, "Domain": 30, "Route": 42, "Feature / Expected Outcome": 72, "Result": 13, "Actual Outcome": 48, "Severity": 12, "Defect ID": 14, "Evidence": 30, "Tested By": 18, "Test Date": 14, "Comments": 35, "Build / Version": 18, "Environment": 16})
    add_dropdown(ws, headers.index("Result") + 1, 5, 1500, "=Lists!$A$2:$A$6")
    add_dropdown(ws, headers.index("Severity") + 1, 5, 1500, "=Lists!$B$2:$B$5")
    add_status_formatting(ws, headers.index("Result") + 1, 5, 1500)

    reconciliation = [
        ("Current employee directory", "Canonical current employee population"), ("Workforce headcount", "Same as current employee population"),
        ("Compensation headcount", "Same eligible population for period"), ("Payroll run item count", "Eligible payable employees only"),
        ("Payroll gross", "Sum of employee item gross"), ("Payroll deductions", "Sum of employee deductions and tax"),
        ("Payroll net", "Gross minus deductions"), ("Accrual journal", "Debits equal credits"),
        ("Payment journal", "Debits equal credits"), ("Published payslips", "Finalized eligible payroll items"),
        ("Attendance posted rows", "Valid nonduplicate matched rows"), ("Approved leave movement", "Approved working days"),
        ("Separated employees in current selectors", "0"),
    ]
    ws = wb.create_sheet("Reconciliation")
    rec_headers = ["Reconciliation", "Expected", "Actual", "Result", "Evidence", "Owner", "Date", "Comments"]
    rec_records = [dict(zip(rec_headers, [name, expected, "", "Not Run", "", "", "", ""])) for name, expected in reconciliation]
    title(ws, "HR & Payroll Reconciliation", "Financial and population controls required before production approval.", len(rec_headers))
    write_table(ws, rec_headers, rec_records, "ReconciliationTable", {"Reconciliation": 34, "Expected": 38, "Actual": 22, "Result": 13, "Evidence": 35, "Owner": 20, "Date": 14, "Comments": 40})
    add_dropdown(ws, 4, 5, 100, "=Lists!$A$2:$A$6")
    add_status_formatting(ws, 4, 5, 100)

    ws = wb.create_sheet("Defect Log")
    defect_headers = ["Defect ID", "Test / Coverage ID", "Domain", "Severity", "Summary", "Steps to Reproduce", "Expected", "Actual", "Owner", "Status", "Raised Date", "Target Date", "Retest Result", "Retest Evidence", "Comments"]
    defect_records = [dict.fromkeys(defect_headers, "") for _ in range(30)]
    title(ws, "HR & Payroll Defect Log", "Record every failure and link it back to a stable test or route coverage ID.", len(defect_headers))
    write_table(ws, defect_headers, defect_records, "DefectLogTable", {"Defect ID": 14, "Test / Coverage ID": 18, "Domain": 24, "Severity": 12, "Summary": 38, "Steps to Reproduce": 55, "Expected": 45, "Actual": 45, "Owner": 18, "Status": 15, "Raised Date": 14, "Target Date": 14, "Retest Result": 15, "Retest Evidence": 30, "Comments": 35})
    add_dropdown(ws, 4, 5, 1000, "=Lists!$B$2:$B$5")
    add_dropdown(ws, 10, 5, 1000, "=Lists!$C$2:$C$7")
    add_dropdown(ws, 13, 5, 1000, "=Lists!$A$2:$A$6")

    ws = wb.create_sheet("Test Data & Users")
    title(ws, "Test Data and Users", "Use fictional, tenant-scoped data. Do not store passwords or production secrets in this workbook.", 7)
    data_headers = ["Type", "Reference / User", "Role / Purpose", "Tenant", "Status", "Owner", "Notes"]
    users = [("User", "HR-PREP", "HR operations/recruiter"), ("User", "MANAGER-A", "Line/hiring manager"), ("User", "HR-APPROVER", "HR approver"), ("User", "PAY-PREP", "Payroll operator"), ("User", "PAY-APPROVER", "Payroll approver"), ("User", "PAY-AUTH", "Payment authorizer"), ("User", "FINANCE", "Accountant"), ("User", "PRIVACY", "Privacy/compliance"), ("User", "EMPLOYEE-A", "Employee self-service"), ("User", "RESTRICTED", "Read-only negative testing"), ("User", "TENANT-B-ADMIN", "Tenant isolation"), ("Data", "E2E Operations <RUN>", "Department"), ("Data", "E2E Operations Analyst <RUN>", "Position"), ("Data", "E2E Candidate <RUN>", "Candidate"), ("Data", "CC-E2E-<RUN>", "Cost centre"), ("Data", "Future open period", "Payroll period")]
    records = [dict(zip(data_headers, [kind, ref, purpose, "", "Not Prepared", "", ""])) for kind, ref, purpose in users]
    write_table(ws, data_headers, records, "TestDataUsersTable", {"Type": 12, "Reference / User": 28, "Role / Purpose": 34, "Tenant": 22, "Status": 16, "Owner": 20, "Notes": 45})
    add_dropdown(ws, 5, 5, 200, "=Lists!$D$2:$D$5")

    ws = wb.create_sheet("Sign-Off")
    sign_headers = ["Approval Area", "Name", "Decision", "Date", "Signature / Reference", "Conditions / Comments"]
    sign_records = [dict(zip(sign_headers, [area, "", "Pending", "", "", ""])) for area in ["Product Owner", "HR Process Owner", "Payroll / Finance Owner", "QA Owner", "Technical Owner", "Security / Privacy Owner"]]
    title(ws, "Production Readiness Sign-Off", "Production remains not approved until every required owner approves and exit criteria are satisfied.", len(sign_headers))
    write_table(ws, sign_headers, sign_records, "SignOffTable", {"Approval Area": 28, "Name": 24, "Decision": 16, "Date": 14, "Signature / Reference": 30, "Conditions / Comments": 55})
    add_dropdown(ws, 3, 5, 100, "=Lists!$E$2:$E$4")

    ws = wb.create_sheet("Summary Dashboard", 0)
    title(ws, "HR & Payroll UAT Summary Dashboard", "Formula-driven status. Refreshes automatically when execution and route results are updated.", 8)
    ws["A4"], ws["B4"], ws["D4"], ws["E4"] = "Deep Tests", len(deep_tests), "Route Coverage", len(route_rows)
    for cell in ("A4", "D4"):
        ws[cell].font = Font(bold=True, color=WHITE); ws[cell].fill = PatternFill("solid", fgColor=TEAL)
    for cell in ("B4", "E4"):
        ws[cell].font = Font(size=16, bold=True); ws[cell].fill = PatternFill("solid", fgColor=LIGHT_TEAL)
    statuses = ["Pass", "Fail", "Blocked", "Not Run", "N/A"]
    ws.append([])
    ws.append(["Result", "Deep Tests", "Route Coverage", "Total"])
    for cell in ws[6]:
        cell.font = Font(bold=True, color=WHITE); cell.fill = PatternFill("solid", fgColor=NAVY)
    for idx, status in enumerate(statuses, 7):
        ws.cell(idx, 1, status)
        ws.cell(idx, 2, f'=COUNTIF(\'Test Execution\'!L:L,A{idx})')
        ws.cell(idx, 3, f'=COUNTIF(\'Route Coverage\'!E:E,A{idx})')
        ws.cell(idx, 4, f'=SUM(B{idx}:C{idx})')
    ws["A14"], ws["B14"] = "Open Critical / High Defects", '=COUNTIFS(\'Defect Log\'!D:D,"Critical",\'Defect Log\'!J:J,"<>Closed")+COUNTIFS(\'Defect Log\'!D:D,"High",\'Defect Log\'!J:J,"<>Closed")'
    ws["A15"], ws["B15"] = "Production Decision", '=IF(AND(B8=0,B9=0,B10=0,B14=0,COUNTIF(\'Sign-Off\'!C:C,"Approved")=6),"APPROVED","NOT APPROVED")'
    ws["A15"].font = Font(bold=True); ws["B15"].font = Font(bold=True, color=WHITE); ws["B15"].fill = PatternFill("solid", fgColor=RED)
    ws.conditional_formatting.add("B15", FormulaRule(formula=['B15="APPROVED"'], fill=PatternFill("solid", fgColor=GREEN)))
    chart = BarChart(); chart.title = "Execution Status"; chart.y_axis.title = "Tests"; chart.height = 7; chart.width = 13
    chart.add_data(Reference(ws, min_col=2, max_col=3, min_row=6, max_row=11), titles_from_data=True)
    chart.set_categories(Reference(ws, min_col=1, min_row=7, max_row=11)); ws.add_chart(chart, "F6")
    for col, width in {"A": 30, "B": 18, "C": 18, "D": 18, "E": 18, "F": 16}.items(): ws.column_dimensions[col].width = width
    ws.sheet_view.showGridLines = False; ws.freeze_panes = "A4"

    ws = wb.create_sheet("Lists")
    lists = {"A": ["Result", "Not Run", "Pass", "Fail", "Blocked", "N/A"], "B": ["Severity", "Critical", "High", "Medium", "Low"], "C": ["Defect Status", "Open", "In Progress", "Ready for Retest", "Closed", "Deferred", "Rejected"], "D": ["Preparation Status", "Not Prepared", "Ready", "In Use", "Retired"], "E": ["Decision", "Pending", "Approved", "Rejected"]}
    for column, values in lists.items():
        for row, value in enumerate(values, 1): ws[f"{column}{row}"] = value
    ws.sheet_state = "hidden"

    for sheet in wb.worksheets:
        sheet.sheet_properties.tabColor = TEAL if sheet.title != "Summary Dashboard" else NAVY
    wb.calculation.fullCalcOnLoad = True
    wb.calculation.forceFullCalc = True
    OUTPUT.parent.mkdir(parents=True, exist_ok=True)
    wb.save(OUTPUT)

    check = load_workbook(OUTPUT, data_only=False)
    assert len(check["Test Execution"].tables) == 1
    assert len(check["Route Coverage"].tables) == 1
    assert check["Test Execution"].max_row - 4 == len(deep_tests)
    assert check["Route Coverage"].max_row - 4 == len(route_rows)
    print(f"Created {OUTPUT}")
    print(f"Deep tests: {len(deep_tests)}; route rows: {len(route_rows)}; sheets: {len(check.sheetnames)}")


if __name__ == "__main__":
    build()