"""
create_report.py
Verze: 1.2
Datum: 2026-05-27

Generuje Excel report (.xlsm) pro studii 77242113UCO3001 z MongoDB databáze Clario.
Výstup: U:/Dropbox/!!!Days/Downloads Z230/YYYY-MM-DD 77242113UCO3001 Clario Reports.xlsm

Listy:
  MayoScore    — jeden řádek = pacient × visit; řádky I-0 s Modified Mayo < 5 červeně tučně
  MayoDiary    — jeden řádek = denní záznam deníku pacienta
  EligibleDays — jeden řádek = jeden eligible day z MayoScore obohacený o data z MayoDiary;
                 included/excluded flag, excluded dny šedě na žlutém pozadí

VBA makro (Worksheet_SelectionChange na listu MayoScore):
  Klik na řádek → automaticky přepne na EligibleDays a vyfiltruje záznamy
  pro daného pacienta a visit. Vyžaduje povolení maker při otevření souboru.
"""

from datetime import datetime
from pathlib import Path

from pymongo import MongoClient
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
import xlwings as xw

# ---------------------------------------------------------------------------
# Konfigurace
# ---------------------------------------------------------------------------

MONGO_URI = "mongodb://192.168.1.76:27017"
DB_NAME = "Clario"
OUTPUT_DIR = Path(r"U:\Dropbox\!!!Days\Downloads Z230")

VISIT_ORDER = ["I-0", "I-2", "I-4", "I-8", "I-12"]

COLUMNS_SCORE = [
    ("Site",                        lambda d: d.get("site", {}).get("name", "")),
    ("Subject ID",                  lambda d: d.get("subject", {}).get("id", "")),
    ("Visit",                       lambda d: d["fields"].get("Visit", "")),
    ("Visit Date",                  lambda d: d["fields"].get("Visit Date", "")),
    ("Baseline Stool Frequency",    lambda d: _num(d["fields"].get("Baseline Stool Frequency", ""))),
    ("Central Endoscopy Score",     lambda d: _num(d["fields"].get("Central Endoscopy Score", ""))),
    ("PGA Score",                   lambda d: _num(d["fields"].get("PGA Score", ""))),
    ("Stool Frequency Sub-score",   lambda d: _num(d["fields"].get("Stool Frequency Sub-score", ""))),
    ("Rectal Bleeding Sub-score",   lambda d: _num(d["fields"].get("Rectal Bleeding Sub-score", ""))),
    ("Partial Mayo Score",          lambda d: _num(d["fields"].get("Partial Mayo Score", ""))),
    ("Modified Mayo Score",         lambda d: _num(d["fields"].get("Modified Mayo Score", ""))),
    ("Full Mayo Score",             lambda d: _num(d["fields"].get("Full Mayo Score", ""))),
]

COLUMNS_DIARY = [
    ("Subject ID",              lambda d: d.get("subject", {}).get("id", "")),
    ("Report Date",             lambda d: d["fields"].get("Report Date", "")),
    ("Baseline Stool Count",    lambda d: _num(d["fields"].get("Baseline Stool Count", ""))),
    ("Stool Frequency",         lambda d: _num(d["fields"].get("Stool Frequency", ""))),
    ("MAYO050",                 lambda d: d["fields"].get("MAYO050", "")),
    ("Not Applicable",          lambda d: d["fields"].get("Not Applicable", "")),
    ("Constipation",            lambda d: d["fields"].get("Constipation", "")),
    ("Diarrhea",                lambda d: d["fields"].get("Diarrhea", "")),
    ("Irregularity",            lambda d: d["fields"].get("Irregularity", "")),
]

# ---------------------------------------------------------------------------
# Helpers
# ---------------------------------------------------------------------------

def _num(value):
    """Převede číselný string na int, jinak vrátí původní hodnotu nebo None."""
    if value == "" or value is None:
        return None
    try:
        return int(value)
    except (ValueError, TypeError):
        try:
            return float(value)
        except (ValueError, TypeError):
            return value


def _visit_sort_key(doc):
    visit = doc["fields"].get("Visit", "")
    try:
        idx = VISIT_ORDER.index(visit)
    except ValueError:
        idx = len(VISIT_ORDER)
    return (doc.get("site", {}).get("name", ""), doc.get("subject", {}).get("id", ""), idx, visit)


def _iso_to_date(value):
    """ISO string → Python date pro Excel."""
    if not isinstance(value, str):
        return value
    try:
        return datetime.fromisoformat(value).date()
    except ValueError:
        return value


# ---------------------------------------------------------------------------
# Styly
# ---------------------------------------------------------------------------

HEADER_FILL = PatternFill("solid", fgColor="1F497D")
HEADER_FONT = Font(bold=True, color="FFFFFF", size=10)
CELL_FONT   = Font(size=10)
ALIGN_CTR   = Alignment(horizontal="center", vertical="center", wrap_text=False)
ALIGN_LEFT  = Alignment(horizontal="left",   vertical="center")

THIN = Side(style="thin", color="BFBFBF")
BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)

# zebra
FILL_ODD  = PatternFill("solid", fgColor="FFFFFF")
FILL_EVEN = PatternFill("solid", fgColor="EBF1DE")

SCORE_COLS = {"Partial Mayo Score", "Modified Mayo Score", "Full Mayo Score"}
SCORE_FILL = PatternFill("solid", fgColor="FFC7CE")   # červená pro skóre ≥ 5 (placeholder — nepoužíváme podmíněné formátování)


# ---------------------------------------------------------------------------
# Sestavení sheetu
# ---------------------------------------------------------------------------

def _build_sheet(ws, docs, columns, date_cols, center_cols, col_widths, row_font_fn=None):
    headers = [c[0] for c in columns]

    for col_idx, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_idx, value=header)
        cell.font = HEADER_FONT
        cell.fill = HEADER_FILL
        cell.alignment = ALIGN_CTR
        cell.border = BORDER
    ws.row_dimensions[1].height = 28

    for row_idx, doc in enumerate(docs, 2):
        fill = FILL_EVEN if row_idx % 2 == 0 else FILL_ODD
        font = row_font_fn(doc) if row_font_fn else CELL_FONT
        for col_idx, (col_name, getter) in enumerate(columns, 1):
            value = getter(doc)
            if col_name in date_cols and isinstance(value, str):
                value = _iso_to_date(value)
            cell = ws.cell(row=row_idx, column=col_idx, value=value)
            cell.font = font
            cell.fill = fill
            cell.border = BORDER
            cell.alignment = ALIGN_CTR if col_name in center_cols else ALIGN_LEFT

    for col_idx, (col_name, _) in enumerate(columns, 1):
        ws.column_dimensions[get_column_letter(col_idx)].width = col_widths.get(col_name, 14)

    for col_name in date_cols:
        if col_name in headers:
            letter = get_column_letter(headers.index(col_name) + 1)
            for row_idx in range(2, len(docs) + 2):
                ws[f"{letter}{row_idx}"].number_format = "DD-MMM-YYYY"

    ws.freeze_panes = "A2"
    ws.auto_filter.ref = f"A1:{get_column_letter(len(headers))}1"


def _score_row_font(doc):
    visit = doc["fields"].get("Visit", "")
    try:
        mod_mayo = int(doc["fields"].get("Modified Mayo Score", ""))
    except (ValueError, TypeError):
        mod_mayo = None
    if visit == "I-0" and mod_mayo is not None and mod_mayo < 5:
        return Font(size=10, bold=True, color="FF0000")
    return CELL_FONT


def build_mayo_score_sheet(ws, docs):
    _build_sheet(
        ws, docs, COLUMNS_SCORE,
        date_cols={"Visit Date"},
        center_cols={"Visit", "Central Endoscopy Score", "PGA Score",
                     "Stool Frequency Sub-score", "Rectal Bleeding Sub-score",
                     "Partial Mayo Score", "Modified Mayo Score", "Full Mayo Score",
                     "Baseline Stool Frequency"},
        col_widths={
            "Site": 18, "Subject ID": 16, "Visit": 12, "Visit Date": 14,
            "Baseline Stool Frequency": 14, "Central Endoscopy Score": 14,
            "PGA Score": 10, "Stool Frequency Sub-score": 14,
            "Rectal Bleeding Sub-score": 14, "Partial Mayo Score": 14,
            "Modified Mayo Score": 14, "Full Mayo Score": 13,
        },
        row_font_fn=_score_row_font,
    )


def build_mayo_diary_sheet(ws, docs):
    _build_sheet(
        ws, docs, COLUMNS_DIARY,
        date_cols={"Report Date"},
        center_cols={"Baseline Stool Count", "Stool Frequency", "Not Applicable",
                     "Constipation", "Diarrhea", "Irregularity"},
        col_widths={
            "Subject ID": 16, "Report Date": 14, "Baseline Stool Count": 14,
            "Stool Frequency": 14, "MAYO050": 48, "Not Applicable": 14,
            "Constipation": 14, "Diarrhea": 12, "Irregularity": 14,
        },
    )


def build_eligible_days_sheet(ws, score_docs, diary_docs):
    # Lookup diary records by (subject_id, date_part YYYY-MM-DD)
    diary_lookup: dict[tuple, dict] = {}
    for d in diary_docs:
        subj = d.get("subject", {}).get("id", "")
        date_iso = d["fields"].get("Report Date", "")
        date_part = date_iso[:10] if date_iso else ""
        if subj and date_part:
            diary_lookup[(subj, date_part)] = d

    headers = [
        "Included", "Subject ID", "Visit", "Visit Date", "Day",
        "Report Date", "Baseline Stool Count", "Stool Frequency",
        "MAYO050", "Not Applicable", "Constipation", "Diarrhea", "Irregularity",
    ]
    col_widths = {
        "Included": 10, "Subject ID": 16, "Visit": 10, "Visit Date": 14, "Day": 8,
        "Report Date": 14, "Baseline Stool Count": 14, "Stool Frequency": 14,
        "MAYO050": 48, "Not Applicable": 14, "Constipation": 14,
        "Diarrhea": 12, "Irregularity": 14,
    }
    center_cols = {"Included", "Visit", "Day", "Baseline Stool Count", "Stool Frequency",
                   "Not Applicable", "Constipation", "Diarrhea", "Irregularity"}
    date_cols = {"Visit Date", "Report Date"}
    no_fill = PatternFill("solid", fgColor="FFF2CC")  # žlutá pro excluded dny

    for col_idx, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_idx, value=header)
        cell.font = HEADER_FONT
        cell.fill = HEADER_FILL
        cell.alignment = ALIGN_CTR
        cell.border = BORDER
    ws.row_dimensions[1].height = 28

    row_idx = 2
    for score_doc in score_docs:
        subj = score_doc.get("subject", {}).get("id", "")
        visit = score_doc["fields"].get("Visit", "")
        visit_date = score_doc["fields"].get("Visit Date", "")

        for n in range(1, 11):
            day_date_iso = score_doc["fields"].get(f"Eligible Day (-{n})")
            if not day_date_iso or day_date_iso == "-":
                continue
            date_part = day_date_iso[:10]
            excl_reason = score_doc["fields"].get(f"Day (-{n}) Excluded Reason(s)", "")
            included = "No" if excl_reason and excl_reason != "-" else "Yes"

            diary = diary_lookup.get((subj, date_part), {})
            df = diary.get("fields", {})

            fill = no_fill if included == "No" else (FILL_EVEN if row_idx % 2 == 0 else FILL_ODD)
            font = Font(size=10, color="808080") if included == "No" else CELL_FONT

            values = [
                included,
                subj,
                visit,
                _iso_to_date(visit_date) if isinstance(visit_date, str) else visit_date,
                f"-{n}",
                _iso_to_date(day_date_iso),
                _num(df.get("Baseline Stool Count", "")),
                _num(df.get("Stool Frequency", "")),
                df.get("MAYO050", ""),
                df.get("Not Applicable", ""),
                df.get("Constipation", ""),
                df.get("Diarrhea", ""),
                df.get("Irregularity", ""),
            ]

            for col_idx, (header, value) in enumerate(zip(headers, values), 1):
                cell = ws.cell(row=row_idx, column=col_idx, value=value)
                cell.font = font
                cell.fill = fill
                cell.border = BORDER
                if header in date_cols:
                    cell.number_format = "DD-MMM-YYYY"
                cell.alignment = ALIGN_CTR if header in center_cols else ALIGN_LEFT

            row_idx += 1

    for col_idx, header in enumerate(headers, 1):
        ws.column_dimensions[get_column_letter(col_idx)].width = col_widths.get(header, 14)

    ws.freeze_panes = "A2"
    ws.auto_filter.ref = f"A1:{get_column_letter(len(headers))}1"


# ---------------------------------------------------------------------------
# Main
# ---------------------------------------------------------------------------

def main():
    client = MongoClient(MONGO_URI, serverSelectionTimeoutMS=5000)
    client.admin.command("ping")
    db = client[DB_NAME]

    score_docs = list(db["Clario.MayoScore"].find({}))
    diary_docs = list(db["Clario.MayoDiary"].find({}))
    client.close()

    score_docs.sort(key=_visit_sort_key)
    diary_docs.sort(key=lambda d: (
        d.get("subject", {}).get("id", ""),
        d["fields"].get("Report Date", ""),
    ))

    wb = Workbook()
    ws_score = wb.active
    ws_score.title = "MayoScore"
    build_mayo_score_sheet(ws_score, score_docs)

    ws_diary = wb.create_sheet("MayoDiary")
    build_mayo_diary_sheet(ws_diary, diary_docs)

    ws_days = wb.create_sheet("EligibleDays")
    build_eligible_days_sheet(ws_days, score_docs, diary_docs)

    OUTPUT_DIR.mkdir(parents=True, exist_ok=True)
    today = datetime.now().strftime("%Y-%m-%d")
    filename = f"{today} 77242113UCO3001 Clario Reports.xlsx"
    output_path = OUTPUT_DIR / filename

    # Uložit jako .xlsx nejdřív, pak přepsat na .xlsm přes xlwings + injektovat VBA
    xlsx_path = output_path.with_suffix(".xlsx")
    xlsm_path = output_path.with_suffix(".xlsm")
    wb.save(str(xlsx_path))

    inject_vba(xlsx_path, xlsm_path)
    xlsx_path.unlink(missing_ok=True)

    print(f"Uloženo: {xlsm_path}")
    print(f"MayoScore: {len(score_docs)} záznamů")
    print(f"MayoDiary: {len(diary_docs)} záznamů")
    print(f"EligibleDays: generováno z {len(score_docs)} score záznamů")


def inject_vba(xlsx_path: Path, xlsm_path: Path) -> None:
    vba_code = '''\
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row < 2 Or Target.Column < 1 Then Exit Sub
    If Target.Rows.Count > 1 Then Exit Sub

    Dim subjectId As String
    Dim visit As String
    subjectId = CStr(Me.Cells(Target.Row, 2).Value)
    visit = CStr(Me.Cells(Target.Row, 3).Value)

    If subjectId = "" Or visit = "" Then Exit Sub

    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets("EligibleDays")
    On Error GoTo 0
    If ws Is Nothing Then Exit Sub

    Application.ScreenUpdating = False

    ws.AutoFilterMode = False
    ws.Range("A1").AutoFilter
    ws.Range("A1").AutoFilter Field:=2, Criteria1:=subjectId
    ws.Range("A1").AutoFilter Field:=3, Criteria1:=visit

    ws.Activate
    ws.Range("A2").Select

    Application.ScreenUpdating = True
End Sub
'''

    app = xw.App(visible=False)
    try:
        wb = app.books.open(str(xlsx_path))
        # Najdi VBComponent odpovídající listu "MayoScore" podle tab názvu
        vb_comp = None
        for comp in wb.api.VBProject.VBComponents:
            if comp.Type == 100:  # xlSheet
                try:
                    if comp.Properties("Name").Value == "MayoScore":
                        vb_comp = comp
                        break
                except Exception:
                    pass
        if vb_comp is None:
            # fallback: první sheet (Sheet1)
            vb_comp = wb.api.VBProject.VBComponents("Sheet1")
        vb_comp.CodeModule.AddFromString(vba_code)
        wb.api.SaveAs(str(xlsm_path), FileFormat=52)  # 52 = xlOpenXMLWorkbookMacroEnabled
        wb.close()
    finally:
        app.quit()


if __name__ == "__main__":
    main()
