# -*- coding: utf-8 -*-
"""
PRA Analysis Web App
上傳 HLA Fusion XLS → 自動分析 PRA Class I / II → 產生報告
"""

import io
import os
import re
import sys
import tempfile
from pathlib import Path
from collections import OrderedDict
from datetime import datetime

from flask import Flask, render_template, request, send_file, jsonify, redirect, url_for, session as flask_session

from PRA import NC_BEAD, PC_BEAD, _parse_allele_list

app = Flask(__name__)
app.config['MAX_CONTENT_LENGTH'] = 16 * 1024 * 1024  # 16MB
app.secret_key = 'pra-analysis-2025'

DEFAULT_USERS = {'NEPH': 'NEPH12345', 'okokyytt@gmail.com': '1234'}

ADMIN_USER = 'okokyytt@gmail.com'

import db as _db
_db.seed_default_users(DEFAULT_USERS, admin_user=ADMIN_USER)


def login_required(f):
    from functools import wraps
    @wraps(f)
    def decorated(*args, **kwargs):
        if not flask_session.get('logged_in'):
            return redirect(url_for('login'))
        return f(*args, **kwargs)
    return decorated


# ============================================================
# 分析邏輯
# ============================================================


def build_qc_comment(nc_raw, pc_raw, beads_detail):
    """
    QC 檢查 → 自動產生 Comment。
    nc_raw: 病人樣本的 NC bead (001) Raw 值
    pc_raw: NC 樣本的 PC bead (002) Raw 值（plate-level）
    beads_detail: list of bead dicts, each with 'bead' and 'count'
    """
    lines = []
    # NC background check
    if nc_raw > 1500:
        lines.append('Uninterpretable due to high background bindings, please repeat')
    elif nc_raw > 500:
        lines.append('High background bindings')
    # PC signal check (must be > 500)
    if pc_raw <= 500:
        lines.append('Low PC signal, please repeat')
    # PC/NC ratio check
    if nc_raw > 0 and pc_raw > 0:
        ratio = pc_raw / nc_raw
        if ratio < 2:
            lines.append('Uninterpretable, please repeat')
    # Bead count check
    low_beads = [(b['bead'], b['count']) for b in beads_detail
                 if b.get('count', 0) > 0 and b['count'] < 80]
    if low_beads:
        lines.append('Low HLA Beads count, please repeat')
    return '\n'.join(lines)


def clean_sero(sero_str):
    """清理 sero 字串: 'A2, , B46, , Bw6, , Cw1,' → 'A2, B46, Cw1'"""
    if not sero_str:
        return ''
    skip = {'Bw4', 'Bw6', ''}
    parts = [s.strip() for s in sero_str.split(',')]
    parts = [s for s in parts if s not in skip]
    return ', '.join(parts)


def clean_allele(allele_str):
    """清理 allele 字串:
    - 移除 =alias (DPB1*04:01=DPB1*105:01 → DPB1*04:01)
    - 移除 /276N, /163N null allele 後綴
    - 移除 dash (-) 條目
    - 移除空條目
    """
    if not allele_str:
        return ''
    parts = [a.strip() for a in allele_str.split(',')]
    cleaned = [p for p in parts if p and p != '-']
    return ', '.join(cleaned)


def calculate_pra(bead_results, threshold='X6'):
    threshold_rxn = int(threshold[1:])
    total = len(bead_results)
    positive = sum(1 for r in bead_results.values() if r['rxn'] >= threshold_rxn)
    pra = round(positive / total * 100) if total > 0 else 0
    return pra, positive, total


def get_confident_alleles(bead_results, bead_hla_map, threshold=0.8):
    """
    篩選 confident alleles：80% Rule + Gray Zone（PRA1/PRA2 通用）
    Step 1: 有 X2/X1 → 硬性排除
    Step 2: 只有 X4 → 80% rule（X6X8/total ≥ 80% 才列入）
    Step 3: 全部 X6/X8 → 直接列入
    """
    def _clean(ag):
        ag = ag.split('/')[0]
        ag = ag.split('=')[0]
        return ag

    # 收集每個 allele 在不同 Rxn 層級的 bead
    allele_beads = {}  # allele -> {'x6x8': set, 'x4': set, 'x2x1': set}
    for bid, r in bead_results.items():
        hla = bead_hla_map.get(bid, {})
        alleles = {_clean(a) for a in _parse_allele_list(hla.get('allele', ''))}
        for ag in alleles:
            if ag not in allele_beads:
                allele_beads[ag] = {'x6x8': set(), 'x4': set(), 'x2x1': set()}
            if r['rxn'] >= 6:
                allele_beads[ag]['x6x8'].add(bid)
            elif r['rxn'] >= 4:
                allele_beads[ag]['x4'].add(bid)
            else:
                allele_beads[ag]['x2x1'].add(bid)

    # 建 allele→sero 對照
    a2s = _build_allele_to_sero(bead_hla_map)

    confident = set()
    decisions = []  # 每個 allele 的判定過程
    for ag, levels in allele_beads.items():
        n68 = len(levels['x6x8'])
        n4 = len(levels['x4'])
        n21 = len(levels['x2x1'])
        sero = a2s.get(ag, '')
        # 必須有 X6/X8
        if not levels['x6x8']:
            continue
        # Step 1: 有 X2/X1 → 硬性排除
        if levels['x2x1']:
            decisions.append({'allele': ag, 'sero': sero, 'x6x8': n68, 'x4': n4, 'x2x1': n21, 'decision': 'Excluded (X2/X1)'})
            continue
        # Step 2: 只有 X4 → 80% rule
        if levels['x4']:
            total = n68 + n4
            ratio = n68 / total
            if ratio >= threshold:
                confident.add(ag)
                decisions.append({'allele': ag, 'sero': sero, 'x6x8': n68, 'x4': n4, 'x2x1': n21, 'decision': 'Assign'})
            else:
                decisions.append({'allele': ag, 'sero': sero, 'x6x8': n68, 'x4': n4, 'x2x1': n21, 'decision': f'Excluded (<80%: {ratio*100:.1f}%)'})
        else:
            # 全部都是 X6/X8，直接列入
            confident.add(ag)
            decisions.append({'allele': ag, 'sero': sero, 'x6x8': n68, 'x4': n4, 'x2x1': n21, 'decision': 'Assign'})

    decisions.sort(key=lambda d: d['allele'])
    return confident, decisions


def _build_allele_to_sero(bead_hla_map):
    """從 bead map 建立 allele→sero 對照"""
    mapping = {}
    for bid, hla in bead_hla_map.items():
        sero_parts = [s.strip() for s in hla.get('sero', '').split(',')]
        allele_parts = [a.strip() for a in hla.get('allele', '').split(',')]
        skip = {'Bw4', 'Bw6', ''}
        sero_by_locus = {}
        for s in sero_parts:
            if s in skip:
                continue
            # 判斷 locus
            if s.startswith('A'):
                sero_by_locus.setdefault('A', []).append(s)
            elif s.startswith('B'):
                sero_by_locus.setdefault('B', []).append(s)
            elif s.startswith('Cw') or s.startswith('C'):
                sero_by_locus.setdefault('C', []).append(s)
            elif s.startswith('DR'):
                sero_by_locus.setdefault('DR', []).append(s)
            elif s.startswith('DQ'):
                sero_by_locus.setdefault('DQ', []).append(s)
            elif s.startswith('DP'):
                sero_by_locus.setdefault('DP', []).append(s)

        allele_by_locus = {}
        for a in allele_parts:
            if not a or a == '-':
                continue
            if a.startswith('A'):
                allele_by_locus.setdefault('A', []).append(a)
            elif a.startswith('B'):
                allele_by_locus.setdefault('B', []).append(a)
            elif a.startswith('C'):
                allele_by_locus.setdefault('C', []).append(a)
            elif a.startswith('DRB'):
                allele_by_locus.setdefault('DR', []).append(a)
            elif a.startswith('DQB'):
                allele_by_locus.setdefault('DQ', []).append(a)  # DQ sero = DQB1 only
            elif a.startswith('DPB'):
                allele_by_locus.setdefault('DP', []).append(a)  # DP sero = DPB1 only
            # DQA1/DPA1 不建 sero mapping（用 allele 格式顯示）

        for locus in sero_by_locus:
            seros = sero_by_locus[locus]
            alleles = allele_by_locus.get(locus, [])
            for i, ag in enumerate(alleles):
                if i < len(seros):
                    # 同時建立原始和清理後的 mapping
                    mapping[ag] = seros[i]
                    clean_ag = ag.split('/')[0].split('=')[0]
                    if clean_ag != ag:
                        mapping[clean_ag] = seros[i]
    return mapping


def build_sero_mfi_stats(beads_detail, confident_alleles, bead_hla_map):
    """
    計算每個 confident sero 的 Max/Mean Normal MFI。
    回傳 list of dict: [{sero, alleles, max_mfi, mean_mfi, count, beads}, ...]
    按 max_mfi 降序排列。
    """
    a2s = _build_allele_to_sero(bead_hla_map)
    skip_sero = {'Bw4', 'Bw6', ''}

    # 找出 confident sero set, DQA1/DQB1/DPA1/DPB1 用 allele 本身當 key
    conf_seros = set()
    sero_alleles = {}  # sero -> set of confident alleles
    for ag in confident_alleles:
        prefix = ag.split('*')[0] if '*' in ag else ''
        if prefix in ('DQA1', 'DQB1', 'DPA1', 'DPB1'):
            # 用 allele 本身當 key
            conf_seros.add(ag)
            sero_alleles[ag] = {ag}
        else:
            sero = a2s.get(ag)
            if sero and sero not in skip_sero:
                conf_seros.add(sero)
                sero_alleles.setdefault(sero, set()).add(ag)

    # 收集每個 confident sero/allele 在正陽性 bead 上的 Normal 值
    sero_normals = {}
    sero_beads = {}
    for b in beads_detail:
        if b['rxn'] < 6:
            continue
        hla = bead_hla_map.get(b['bead'], {})
        # Sero level
        sero_parts = [s.strip() for s in hla.get('sero', '').split(',')]
        for s in sero_parts:
            if s in skip_sero:
                continue
            if s in conf_seros:
                sero_normals.setdefault(s, []).append(b['normal'])
                sero_beads.setdefault(s, []).append(b['bead'])
        # Allele level (DQA1/DQB1/DPA1/DPB1)
        for ag in _parse_allele_list(hla.get('allele', '')):
            if ag in conf_seros:
                sero_normals.setdefault(ag, []).append(b['normal'])
                sero_beads.setdefault(ag, []).append(b['bead'])

    # 組裝結果
    stats = []
    for sero in conf_seros:
        normals = sero_normals.get(sero, [])
        if not normals:
            continue
        alleles_str = ', '.join(sorted(sero_alleles.get(sero, set())))
        stats.append({
            'sero': sero,
            'alleles': alleles_str,
            'max_mfi': round(max(normals), 1),
            'mean_mfi': round(sum(normals) / len(normals), 1),
            'count': len(normals),
            'beads': ', '.join(sero_beads.get(sero, [])),
        })

    # 排序: 按 locus 分組, 再按 max_mfi 降序
    def sort_key(x):
        s = x['sero']
        if s.startswith('A'):
            locus = 0
        elif s.startswith('B'):
            locus = 1
        elif s.startswith('Cw') or s.startswith('C'):
            locus = 2
        elif s.startswith('DR'):
            locus = 0
        elif s.startswith('DQ'):
            locus = 1
        elif s.startswith('DP'):
            locus = 2
        else:
            locus = 9
        return (locus, -x['max_mfi'])

    stats.sort(key=sort_key)
    return stats


def generate_specificity(confident_alleles, bead_hla_map):
    """
    將 confident alleles 轉為 Specificity 字串。
    Class I: A11(A*11:02) A23 B7 Cw1
    Class II: DR1 DR4 DQ5 DQ7 DQA1(*05:01 *03:03) DQB1(*03:01) DP2 DPA1(*01:03) DPB1(*04:01)
    """
    if not confident_alleles:
        return '(-)'

    a2s = _build_allele_to_sero(bead_hla_map)

    # 統計每個 sero group 在整個 bead panel 上有哪些 allele
    all_alleles_per_sero = {}
    for bid, hla in bead_hla_map.items():
        for ag in _parse_allele_list(hla.get('allele', '')):
            sero = a2s.get(ag)
            if sero:
                all_alleles_per_sero.setdefault(sero, set()).add(ag)

    # 清理 allele 名稱（移除 /276N, =DPB1*105:01 等後綴）
    def clean_allele(ag):
        ag = ag.split('/')[0]  # remove /276N
        ag = ag.split('=')[0]  # remove =DPB1*105:01
        return ag

    # 分組:
    # DQA1/DPA1 (alpha chain, 無 sero) → allele 格式
    # DQB1/DPB1 (beta chain, 有 sero) → 回歸 sero 對照
    # 其他 → sero 對照
    sero_groups = {}
    allele_groups = {'DQA1': set(), 'DPA1': set()}

    for ag in confident_alleles:
        ag_clean = clean_allele(ag)
        prefix = ag_clean.split('*')[0] if '*' in ag_clean else ''
        if prefix in allele_groups:
            # DQA1/DPA1 → allele 格式
            allele_groups[prefix].add(ag_clean)
        else:
            # DQB1/DPB1 和其他 → sero 對照
            sero = a2s.get(ag)
            if not sero:
                sero = a2s.get(ag_clean)
            if sero:
                sero_groups.setdefault(sero, set()).add(ag_clean)

    # 排序: 按 locus 再按數字
    import re as _re
    def sort_key(s):
        if s.startswith('A'):
            locus = 0
        elif s.startswith('B'):
            locus = 1
        elif s.startswith('Cw'):
            locus = 2
        elif s.startswith('DR'):
            locus = 0
        elif s.startswith('DQ'):
            locus = 1
        elif s.startswith('DP'):
            locus = 2
        else:
            locus = 9
        # 提取數字排序
        m = _re.search(r'(\d+)', s)
        num = int(m.group(1)) if m else 0
        return (locus, num, s)

    parts = []

    # 分 DR/DQ sero, DQA1, DP sero, DPA1 四段輸出
    dr_parts = []
    dq_parts = []
    dp_parts = []
    other_parts = []

    for sero in sorted(sero_groups.keys(), key=sort_key):
        conf = sero_groups[sero]
        alleles_str = ' '.join(f'<span style="color:#374151">{a}</span>' for a in sorted(conf))
        entry = f'<span style="color:#1E40AF;font-weight:bold">{sero}</span>({alleles_str})'

        if sero.startswith('DR'):
            dr_parts.append(entry)
        elif sero.startswith('DQ'):
            dq_parts.append(entry)
        elif sero.startswith('DP'):
            dp_parts.append(entry)
        else:
            other_parts.append(entry)

    # 順序: DR → DQ sero → DQA1 → DP sero → DPA1 → 其他(A,B,Cw)
    parts.extend(other_parts)  # A, B, Cw (Class I)
    parts.extend(dr_parts)
    parts.extend(dq_parts)
    for ag in sorted(allele_groups['DQA1']):
        parts.append(f'<span style="color:#374151">{ag}</span>')
    parts.extend(dp_parts)
    for ag in sorted(allele_groups['DPA1']):
        parts.append(f'<span style="color:#374151">{ag}</span>')

    return ' '.join(parts)


def parse_xls_file(raw_bytes):
    """
    解析 HLA Fusion XLS 報告。
    可能包含一個病人或 NC 報告。
    回傳 dict: {sample_name, pra_class, date, beads_detail, ...}
    """
    import xlrd
    wb = xlrd.open_workbook(file_contents=raw_bytes)
    sh = wb.sheet_by_index(0)

    # 讀取 metadata
    sample_name = str(sh.cell_value(0, 0)).strip()
    if not sample_name:
        # Row 1 可能有 PATIENT: xxx
        if sh.nrows > 1:
            r1 = str(sh.cell_value(1, 0)).strip()
            if r1.startswith('PATIENT'):
                sample_name = str(sh.cell_value(1, 1)).strip() if sh.ncols > 1 else r1
    session = ''
    date_val = ''
    catalog = ''
    # 掃描 header rows 找 TEST DATE / SESSION / CATALOG（直到找到 date_val 為止）
    # TEST DATE label 和值的欄位位置依 XLS 版本不同，可能在 col 34/38 等
    import re as _re
    max_header_rows = min(sh.nrows, 15)
    for r in range(max_header_rows):
        if date_val and session and catalog:
            break
        row = [str(sh.cell_value(r, c)).strip() for c in range(sh.ncols)]
        for i, v in enumerate(row):
            if not session and v in ('SESSION :', 'SESSION:'):
                for j in range(i + 1, len(row)):
                    if row[j]:
                        session = row[j]; break
            if not date_val and v in ('TEST DATE :', 'TEST DATE:'):
                for j in range(i + 1, len(row)):
                    if row[j] and '/' in row[j]:
                        date_val = row[j]; break
            if not catalog and v in ('CATALOG :', 'CATALOG:'):
                for j in range(i + 1, len(row)):
                    if row[j]:
                        catalog = row[j]; break

    # 仍然沒抓到 TEST DATE → 從 SESSION 撈第一組 8 位數日期
    if not date_val and session:
        m = _re.search(r'\b(\d{8})\b', session)
        if m:
            digits = m.group(1)
            date_val = f'{digits[:4]}/{digits[4:6]}/{digits[6:8]}'

    # 最後 fallback：掃整張表任何含 'YYYY/M/D' 或 'YYYY-M-D' 的 cell
    if not date_val:
        date_re = _re.compile(r'\b(20\d{2})[/-](\d{1,2})[/-](\d{1,2})\b')
        for r in range(min(sh.nrows, 20)):
            for c in range(sh.ncols):
                m = date_re.search(str(sh.cell_value(r, c)))
                if m:
                    date_val = f'{m.group(1)}/{int(m.group(2))}/{int(m.group(3))}'
                    break
            if date_val:
                break

    # 偵測 PRA class
    pra_class = 'PRA1'
    if 'LS2PRA' in catalog.upper() or 'PRA2' in catalog.upper():
        pra_class = 'PRA2'
    elif 'PRA2' in session.upper():
        pra_class = 'PRA2'

    # 讀取 bead 資料 (col 0=BeadID, 3=Raw, 11=NS_Raw, 15=Normal, 20=Ratio, 22=Rxn, 26=Count, 29=Sero, 35=Allele)
    beads_detail = []
    nc_raw = 0
    pc_raw = 0
    for r in range(9, sh.nrows):
        bid = str(sh.cell_value(r, 0)).strip()
        if not bid or bid == 'BeadID':
            continue
        # 整數 bead ID → 補零到 3 位
        try:
            bid_int = int(float(bid))
            bid = f'{bid_int:03d}'
        except (ValueError, TypeError):
            continue

        raw_val = sh.cell_value(r, 3) if sh.ncols > 3 else 0
        ns_raw = sh.cell_value(r, 11) if sh.ncols > 11 else 0
        normal = sh.cell_value(r, 15) if sh.ncols > 15 else 0
        ratio = sh.cell_value(r, 20) if sh.ncols > 20 else 0
        rxn_val = sh.cell_value(r, 22) if sh.ncols > 22 else ''
        count_val = sh.cell_value(r, 26) if sh.ncols > 26 else 0
        sero_raw = str(sh.cell_value(r, 29)).strip() if sh.ncols > 29 else ''
        sero = clean_sero(sero_raw)
        allele = str(sh.cell_value(r, 35)).strip() if sh.ncols > 35 else ''

        # 處理數值
        try:
            raw_val = float(raw_val) if raw_val != '' else 0
        except (ValueError, TypeError):
            raw_val = 0
        try:
            ns_raw = float(ns_raw) if ns_raw != '' else 0
        except (ValueError, TypeError):
            ns_raw = 0
        try:
            normal = float(normal) if normal != '' else 0
        except (ValueError, TypeError):
            normal = 0
        try:
            ratio = float(ratio) if ratio != '' else 0
        except (ValueError, TypeError):
            ratio = 0
        try:
            count_val = int(float(count_val)) if count_val != '' else 0
        except (ValueError, TypeError):
            count_val = 0

        # Rxn: 可能是數字 1/2/4/6/8 或 'NC'/'PC'
        rxn_str = str(rxn_val).strip()
        if rxn_str in ('NC', 'nc'):
            nc_raw = raw_val
            continue
        elif rxn_str in ('PC', 'pc'):
            pc_raw = raw_val
            continue

        try:
            rxn_int = int(float(rxn_val))
        except (ValueError, TypeError):
            continue

        # 跳過沒有 sero/allele 的 bead（非 HLA bead）
        if not sero and not allele:
            continue

        beads_detail.append({
            'bead': bid, 'rxn': rxn_int,
            'raw': round(raw_val, 1), 'ns_raw': round(ns_raw, 1),
            'normal': round(normal, 2), 'ratio': round(ratio, 2),
            'count': count_val,
            'sero': sero,
            'allele': clean_allele(allele),
        })

    if not beads_detail:
        return None  # 沒有有效的 HLA bead 資料

    # 計算 PRA%
    total = len(beads_detail)
    pra_all = {}
    for t in ['X2', 'X4', 'X6', 'X8']:
        t_rxn = int(t[1:])
        pos = sum(1 for b in beads_detail if b['rxn'] >= t_rxn)
        pra_all[t] = round(pos / total * 100) if total > 0 else 0

    pra6 = pra_all['X6']
    overall = 'Positive' if pra6 > 0 else 'Negative'

    # Confident alleles — XLS 路徑：用 XLS 自帶的 allele 建 bead map（lot 可能不同）
    br_dict = OrderedDict()
    xls_bead_map = {}
    for b in beads_detail:
        br_dict[b['bead']] = {'rxn': b['rxn']}
        xls_bead_map[b['bead']] = {'sero': b['sero'], 'allele': b['allele']}
    confident, allele_decisions = get_confident_alleles(br_dict, xls_bead_map)

    # Mark beads that contain confident alleles
    for b in beads_detail:
        bead_alleles = {a.strip().split('=')[0].split('/')[0] for a in b['allele'].split(',') if a.strip() and a.strip() != '-'}
        b['is_confident'] = bool(bead_alleles & confident)

    sero_mfi = build_sero_mfi_stats(beads_detail, confident, xls_bead_map) if overall == 'Positive' else []

    pc_nc_ratio = round(pc_raw / nc_raw, 1) if nc_raw > 0 else 0
    qc_comment = build_qc_comment(nc_raw, pc_raw, beads_detail)

    return {
        'name': sample_name,
        'overall': overall,
        'pra': pra6,
        'pra_all': pra_all,
        'beads': beads_detail,
        'confident_alleles': sorted(confident),
        'allele_decisions': allele_decisions,
        'specificity': generate_specificity(confident, xls_bead_map) if overall == 'Positive' else '(-)',
        'sero_mfi': sero_mfi,
        'comment': qc_comment,
        'qc': {
            'nc_raw': round(nc_raw, 1),
            'pc_raw': round(pc_raw, 1),
            'pc_nc_ratio': pc_nc_ratio,
            'nc_count': 0,
            'pc_count': 0,
            'low_beads': [{'bead': b['bead'], 'count': b['count']} for b in beads_detail if b.get('count', 0) > 0 and b['count'] < 80],
        },
        '_pra_class': pra_class,
        '_date': date_val,
        '_batch': session,
        '_nc_raw': nc_raw,
        '_pc_raw': pc_raw,
    }


def full_analyze_xls(raw_bytes_list, filenames):
    """解析一個或多個 XLS 檔，回傳與 full_analyze 相同格式的結果"""
    patients = []
    pra_class = 'PRA1'
    date_val = ''
    batch = ''
    nc_signal = 0
    pc_signal = 0

    errors = []
    for raw_bytes, fname in zip(raw_bytes_list, filenames):
        try:
            pt = parse_xls_file(raw_bytes)
        except Exception as e:
            errors.append(f'{fname}: {type(e).__name__}: {e}')
            continue
        if pt is None:
            errors.append(f'{fname}: 無 bead 資料 (可能非 HLA Fusion 報告)')
            continue
        # 若 sample name 為空，用檔名
        if not pt.get('name'):
            pt['name'] = Path(fname).stem
        pra_class = pt.pop('_pra_class', 'PRA1')
        if pt['_date']:
            date_val = pt.pop('_date')
        else:
            pt.pop('_date')
        if pt['_batch']:
            batch = pt.pop('_batch')
        else:
            pt.pop('_batch')
        nc_signal = pt.pop('_nc_raw', 0) or nc_signal
        pc_signal = pt.pop('_pc_raw', 0) or pc_signal
        patients.append(pt)

    if not patients:
        err_detail = '; '.join(errors) if errors else '無 bead 資料'
        fnames = ', '.join(filenames)
        return None, f'無法從 XLS 中讀取病人資料 [{fnames}] ({err_detail})'

    return {
        'pra_class': pra_class,
        'class_label': 'PRA Class I' if pra_class == 'PRA1' else 'PRA Class II',
        'date': date_val,
        'batch': batch,
        'nc_name': 'NC',
        'pc_signal': round(pc_signal, 0),
        'nc_signal': round(nc_signal, 0),
        'patients': patients,
        'filename': ', '.join(filenames),
    }, None




# ============================================================
# Flask Routes
# ============================================================

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form.get('username', '').strip()
        password = request.form.get('password', '')
        import db
        if db.check_user(username, password):
            flask_session['logged_in'] = True
            flask_session['username'] = username
            flask_session['role'] = db.get_user_role(username)
            flask_session['display_name'] = db.get_user_display_name(username)
            return redirect(url_for('dashboard'))
        # 自動註冊模式：帳號不存在就自動建立（臨時開放）
        ok, msg = db.register_user(username, password, username)
        if ok:
            flask_session['logged_in'] = True
            flask_session['username'] = username
            flask_session['role'] = db.get_user_role(username)
            flask_session['display_name'] = db.get_user_display_name(username)
            return redirect(url_for('dashboard'))
        return render_template('login.html', error='帳號或密碼錯誤')
    return render_template('login.html')


@app.route('/register', methods=['GET', 'POST'])
def register():
    if request.method == 'POST':
        display_name = request.form.get('display_name', '').strip()
        username = request.form.get('username', '').strip()
        password = request.form.get('password', '')
        password2 = request.form.get('password2', '')
        if not display_name:
            return render_template('register.html', error='請輸入姓名')
        if not username:
            return render_template('register.html', error='請輸入帳號')
        if not password or len(password) < 4:
            return render_template('register.html', error='密碼至少 4 碼')
        if password != password2:
            return render_template('register.html', error='兩次密碼不一致')
        import db
        ok, msg = db.register_user(username, password, display_name)
        if ok:
            return render_template('login.html', success='註冊成功，請登入')
        return render_template('register.html', error=msg)
    return render_template('register.html')


@app.route('/logout')
def logout():
    flask_session.clear()
    return redirect(url_for('login'))


@app.route('/')
@login_required
def dashboard():
    return render_template('dashboard.html',
                           username=flask_session.get('username', ''),
                           is_admin=flask_session.get('role') == 'admin')


@app.route('/new')
@login_required
def index():
    return render_template('index.html')


@app.route('/batch_upload', methods=['GET', 'POST'])
@login_required
def batch_upload():
    """批次上傳 XLS，從檔名抓病歷號與姓名，直接存入 DB。"""
    if request.method == 'GET':
        return render_template('batch.html')

    import db
    files = request.files.getlist('file')
    charts = request.form.getlist('chart')
    names = request.form.getlist('name')
    mode = request.form.get('mode', 'new')  # 'new' | 'overwrite' | 'skip'

    results = []
    ok_n = err_n = dup_n = 0
    submitted_by = flask_session.get('display_name', flask_session.get('username', ''))

    for idx, f in enumerate(files):
        fname = f.filename or f'file{idx}'
        chart = (charts[idx] if idx < len(charts) else '').strip()
        name = (names[idx] if idx < len(names) else '').strip()
        if not chart:
            results.append({'file': fname, 'status': 'error', 'msg': '缺病歷號'})
            err_n += 1
            continue
        try:
            raw = f.read()
            pt = parse_xls_file(raw)
        except Exception:
            results.append({'file': fname, 'status': 'error', 'msg': '解析失敗'})
            err_n += 1
            continue
        if pt is None:
            results.append({'file': fname, 'status': 'error', 'msg': '解析失敗（無 bead 資料）'})
            err_n += 1
            continue

        pra_tag = pt.pop('_pra_class', 'PRA1')
        pra_class = 'PRA Class I' if pra_tag == 'PRA1' else 'PRA Class II'
        date_val = pt.pop('_date', '') or ''
        if not date_val:
            results.append({'file': fname, 'status': 'error', 'msg': '抓不到報告日期'})
            err_n += 1
            continue

        patient_id = db.get_or_create_patient(name or fname, chart)

        # 原始檔案先存 uploads/（同名直接覆寫，只留最新）
        upload_dir = db.get_upload_dir()
        orig_name = Path(fname).name.replace('/', '_').replace('\\', '_') or f'file_{idx}.xls'
        save_path = upload_dir / orig_name
        try:
            save_path.write_bytes(raw)
            saved_filename = save_path.name
            db.push_upload_to_repo(save_path, saved_filename)
        except Exception:
            saved_filename = fname

        # 重複偵測（依使用者模式處理 DB report，不影響已存的檔案）
        existing = db.find_active_duplicate(patient_id, date_val, pra_class)
        save_mode = 'overwrite'
        if existing and mode == 'skip':
            results.append({'file': fname, 'status': 'skipped', 'msg': f'檔案已保留；active report_id={existing}'})
            dup_n += 1
            continue
        if existing and mode == 'new':
            save_mode = 'new'

        # strip HTML from specificity（generate_specificity 回傳帶色 span，DB 要存純文字）
        import re as _re
        spec_plain = _re.sub(r'<[^>]*>', '', pt.get('specificity', '')).strip()
        spec_plain = _re.sub(r'\s+', ' ', spec_plain)

        rid = db.save_report(
            patient_id, date_val, pra_class,
            pt.get('pra', 0), pt.get('overall', ''),
            spec_plain, pt.get('comment', ''),
            pt.get('sero_mfi', []),
            'submitted', submitted_by, saved_filename, mode=save_mode
        )
        status = 'duplicate' if existing else 'ok'
        if existing:
            dup_n += 1
            results.append({'file': fname, 'status': 'duplicate', 'msg': f'mode={mode}, report_id={rid}'})
        else:
            ok_n += 1
            results.append({'file': fname, 'status': 'ok', 'msg': f'report_id={rid}'})

    return jsonify({
        'results': results,
        'summary': {'ok': ok_n, 'err': err_n, 'dup': dup_n, 'total': len(files)},
    })


@app.route('/analyze', methods=['POST'])
@login_required
def analyze():
    files = request.files.getlist('file')
    if not files or not files[0].filename:
        return render_template('index.html', error='請選擇檔案')

    # 收集所有結果，按 PRA class 分組
    all_patients = {'PRA1': [], 'PRA2': []}
    meta = {'PRA1': {}, 'PRA2': {}}
    errors = []

    for f in files:
        raw_bytes = f.read()
        fname = f.filename or 'unknown'
        ext = Path(fname).suffix.lower()
        is_xls = ext in ('.xls', '.xlsx') or (
            len(raw_bytes) > 8 and raw_bytes[:8] == b'\xd0\xcf\x11\xe0\xa1\xb1\x1a\xe1')

        if is_xls:
            try:
                pt = parse_xls_file(raw_bytes)
            except Exception as e:
                errors.append(f'{fname}: {e}')
                continue
            if pt is None:
                errors.append(f'{fname}: 無 bead 資料')
                continue
            if not pt.get('name'):
                pt['name'] = Path(fname).stem
            pc = pt.pop('_pra_class', 'PRA1')
            date_val = pt.pop('_date', '')
            batch = pt.pop('_batch', '')
            nc_raw = pt.pop('_nc_raw', 0)
            pc_raw = pt.pop('_pc_raw', 0)

            # 保存原始檔案
            import db as _dbmod
            import xlrd as _xlrd
            upload_dir = _dbmod.get_upload_dir()
            patient_name = request.form.get('patient_name', '').strip()
            chart_no = request.form.get('patient_id', '').strip()
            class_tag = 'PRA1' if pc == 'PRA1' else 'PRA2'
            # 從 XLS row 0 抓 EL 編號 (如 EL20022_27996823)
            el_part = ''
            try:
                _wb = _xlrd.open_workbook(file_contents=raw_bytes)
                _sh = _wb.sheet_by_index(0)
                _r0 = str(_sh.cell_value(0, 0)).strip()
                if not _r0:
                    _r0 = str(_sh.cell_value(0, 1)).strip()
                if 'EL' in _r0.upper():
                    el_part = _r0.split('_')[0]  # EL20022
            except Exception:
                pass
            # 保留原始檔名，同名直接覆寫（只留最新一筆）
            orig = Path(fname).name.replace('/', '_').replace('\\', '_') or f'{patient_name}_{class_tag}{ext}'
            save_path = upload_dir / orig
            save_path.write_bytes(raw_bytes)
            safe_name = save_path.name
            import db as _dbmod2
            _dbmod2.push_upload_to_repo(save_path, safe_name)
            pt['_upload_file'] = safe_name

            all_patients[pc].append(pt)
            if date_val:
                meta[pc]['date'] = date_val
            if batch:
                meta[pc]['batch'] = batch
            meta[pc]['nc_signal'] = nc_raw
            meta[pc]['pc_signal'] = pc_raw
        else:
            errors.append(f'{fname}: 不支援的檔案格式（僅支援 .xls）')
            continue

    # 組裝兩邊結果
    def build_result(pc):
        pts = all_patients[pc]
        if not pts:
            return None
        m = meta.get(pc, {})
        return {
            'pra_class': pc,
            'class_label': 'PRA Class I' if pc == 'PRA1' else 'PRA Class II',
            'date': m.get('date', ''),
            'batch': m.get('batch', ''),
            'nc_name': m.get('nc_name', 'NC'),
            'pc_signal': round(m.get('pc_signal', 0)),
            'nc_signal': round(m.get('nc_signal', 0)),
            'patients': pts,
        }

    result_pra1 = build_result('PRA1')
    result_pra2 = build_result('PRA2')

    if not result_pra1 and not result_pra2:
        err_msg = '; '.join(errors) if errors else '無法辨識檔案格式'
        return render_template('index.html', error=err_msg)

    patient_name = request.form.get('patient_name', '').strip()
    patient_id = request.form.get('patient_id', '').strip()

    # 從 DB 帶入已存的 Donor HLA
    donor_hla = ''
    if patient_id:
        import db
        donor_hla = db.get_donor_hla(patient_id)

    return render_template('index.html',
                           result_pra1=result_pra1,
                           result_pra2=result_pra2,
                           patient_name=patient_name,
                           patient_id=patient_id,
                           donor_hla=donor_hla,
                           errors=errors if errors else None)


@app.route('/export_docx', methods=['POST'])
@login_required
def export_docx():
    """匯出 DOCX 報告"""
    from docx import Document
    from docx.shared import Pt, Cm
    from docx.enum.text import WD_ALIGN_PARAGRAPH

    doc = Document()
    style = doc.styles['Normal']
    style.font.name = 'Calibri'
    style.font.size = Pt(11)

    data = request.json
    pra_class_label = data.get('class_label', 'PRA Class I')
    date_str = data.get('date', '')
    pra_tag = 'PRA1' if 'I' in pra_class_label else 'PRA2'

    # 標題
    p = doc.add_paragraph(f'{date_str} {pra_tag}')
    p.runs[0].bold = True
    p.runs[0].font.size = Pt(14)

    for pt in data.get('patients', []):
        doc.add_paragraph('')
        p = doc.add_paragraph(pt['name'])
        p.runs[0].bold = True

        doc.add_paragraph(pra_class_label)
        doc.add_paragraph(f'Overall: {pt["overall"]}')
        doc.add_paragraph(f'%SA (or %PRA): {pt["pra"]}')
        doc.add_paragraph('Specificity:')

        spec = pt.get('specificity', '').strip()
        if not spec or spec == '':
            spec = '(-)' if pt['overall'] == 'Negative' else '(-)'
        doc.add_paragraph(spec)
        doc.add_paragraph('COMMENT:')

    buf = io.BytesIO()
    doc.save(buf)
    buf.seek(0)

    filename = f'{date_str.replace("/", "")}_{pra_tag}_report.docx'
    return send_file(buf, as_attachment=True, download_name=filename,
                     mimetype='application/vnd.openxmlformats-officedocument.wordprocessingml.document')


@app.route('/save', methods=['POST'])
@login_required
def save():
    """儲存分析結果到資料庫。
    mode: 'auto' (default) 偵測到重複回 {duplicate:[...]} 不寫入；
          'overwrite' 覆寫 active 重複；'new' 強制 INSERT 新列。"""
    import db
    data = request.json
    patient_name = data.get('patient_name', '').strip()
    chart_no = data.get('chart_no', '').strip()
    if not chart_no:
        return jsonify({'error': '請輸入病歷號'}), 400

    patient_id = db.get_or_create_patient(patient_name, chart_no)
    is_submitted = data.get('submitted', False)
    status = 'submitted' if is_submitted else 'draft'
    mode = data.get('mode', 'auto')
    reports = data.get('reports', [])

    # auto 模式：先偵測 active 重複
    if mode == 'auto':
        dups = []
        for r in reports:
            rid = db.find_active_duplicate(patient_id, r.get('report_date', ''), r.get('pra_class', ''))
            if rid:
                dups.append({'report_id': rid,
                             'pra_class': r.get('pra_class', ''),
                             'report_date': r.get('report_date', '')})
        if dups:
            return jsonify({'ok': False, 'duplicate': dups})

    save_mode = 'new' if mode == 'new' else 'overwrite'
    saved = []
    for r in reports:
        submitted_by = flask_session.get('display_name', flask_session.get('username', ''))
        rid = db.save_report(patient_id, r.get('report_date', ''), r.get('pra_class', ''),
                             r.get('pra_percent', 0), r.get('overall', ''),
                             r.get('specificity', ''), r.get('comment', ''),
                             r.get('sero_mfi', []), status, submitted_by,
                             r.get('upload_file', ''), mode=save_mode)
        saved.append({'report_id': rid, 'pra_class': r.get('pra_class', '')})

    return jsonify({'ok': True, 'patient_id': patient_id, 'saved': saved})


def format_donor_hla(s):
    """Donor HLA JSON → 緊湊字串，例 'A:2,24 B:7,46 DRB1*:04:01,07:01 ...'"""
    if not s:
        return ''
    import json as _json
    try:
        obj = _json.loads(s)
    except Exception:
        return ''
    parts = []
    for locus in ['A', 'B', 'Cw', 'DR', 'DQ', 'DP']:
        vs = [obj.get(f'donor-{locus}-{n}') for n in ('1', '2')]
        vs = [v for v in vs if v]
        if vs:
            parts.append(f'{locus}:{",".join(vs)}')
    for locus in ['A', 'B', 'C', 'DRB1', 'DQB1', 'DQA1', 'DPB1', 'DPA1']:
        vs = [obj.get(f'donor-dna-{locus}-{n}') for n in ('1', '2')]
        vs = [v for v in vs if v]
        if vs:
            parts.append(f'{locus}*:{",".join(vs)}')
    return ' '.join(parts)


@app.route('/history')
@login_required
def history():
    """顯示所有報告紀錄"""
    import db
    reports = db.get_all_reports()
    for r in reports:
        r['donor_hla_fmt'] = format_donor_hla(r.get('donor_hla'))
    # DB stats
    conn = db.get_conn()
    db_stats = {
        'patients': conn.execute('SELECT COUNT(*) as c FROM patients').fetchone()['c'],
        'reports': conn.execute('SELECT COUNT(*) as c FROM reports WHERE COALESCE(is_deleted,0)=0').fetchone()['c'],
        'users': conn.execute('SELECT COUNT(*) as c FROM users').fetchone()['c'],
    }
    conn.close()
    return render_template('history.html', reports=reports, db_stats=db_stats,
                           is_admin=flask_session.get('role') == 'admin')


@app.route('/history/<chart_no>')
@login_required
def patient_history(chart_no):
    """顯示單一病人的報告歷史 + MFI 比較"""
    import db
    patient, reports = db.get_patient_reports(chart_no)
    if not patient:
        return render_template('history.html', reports=db.get_all_reports(),
                               error=f'找不到病歷號 {chart_no}')

    # MFI comparison (Class I and Class II) — 已以 allele 為主 key 展開
    dates1, antigens1, pra1, labels1 = db.get_mfi_comparison(chart_no, 'PRA Class I')
    dates2, antigens2, pra2, labels2 = db.get_mfi_comparison(chart_no, 'PRA Class II')
    all_labels = {**labels1, **labels2}
    for r in reports:
        r['chart_label'] = all_labels.get(r['id'], r['report_date'])

    class Comp:
        def __init__(self, dates, antigens, pra_by_date):
            self.dates = dates
            self.antigens = antigens
            self.pra_by_date = pra_by_date

    comp1 = Comp(dates1, antigens1, pra1) if dates1 else None
    comp2 = Comp(dates2, antigens2, pra2) if dates2 else None

    donor_hla = db.get_donor_hla(chart_no)

    return render_template('patient.html', patient=patient, reports=reports,
                           comparison_class1=comp1, comparison_class2=comp2,
                           donor_hla=donor_hla)


@app.route('/cloud_sync', methods=['POST'])
@login_required
def cloud_sync():
    if flask_session.get('role') != 'admin':
        return jsonify({'error': '無權限'}), 403
    try:
        import sync as sync_module
        import importlib
        importlib.reload(sync_module)

        local_info = sync_module.get_db_info(sync_module.LOCAL_DB)

        # 下載雲端 DB
        cloud_path = sync_module.download_cloud_db()
        cloud_info = sync_module.get_db_info(cloud_path) if cloud_path else {'patients': 0, 'reports': 0, 'users': 0}

        result = {
            'ok': True,
            'before': {
                'local': f"{local_info['patients']} 病患 / {local_info['reports']} 報告",
                'cloud': f"{cloud_info['patients']} 病患 / {cloud_info['reports']} 報告",
            }
        }

        # Append-only merge: 以雲端為底，合併 Local 新增/更新
        if cloud_path:
            import tempfile
            merged_path = Path(tempfile.gettempdir()) / 'pra_data_merged.db'
            sync_module.merge_local_into_cloud(str(sync_module.LOCAL_DB), cloud_path, str(merged_path))
            sync_module.upload_db(merged_path)
            merged_info = sync_module.get_db_info(merged_path)
        else:
            # 雲端無 DB，直接上傳
            sync_module.upload_db(sync_module.LOCAL_DB)
            merged_info = local_info

        # 同時推 backup 到 repo
        import db as _dbmod
        _dbmod.push_backups_to_repo()

        result['status'] = 'synced'
        result['message'] = f'Append-only merge 完成'
        result['after'] = f"{merged_info['patients']} 病患 / {merged_info['reports']} 報告 / {merged_info['users']} 使用者"
        return jsonify(result)
    except Exception as e:
        return jsonify({'error': str(e)})


@app.route('/update_report', methods=['POST'])
@login_required
def update_report():
    import db
    data = request.json
    report_id = data.get('report_id')
    specificity = data.get('specificity', '')
    comment = data.get('comment', '')
    if not report_id:
        return jsonify({'error': 'missing report_id'}), 400
    conn = db.get_conn()
    db.backup_db()
    conn.execute("""UPDATE reports SET specificity=?, comment=?,
                    updated_at=datetime('now','localtime') WHERE id=?""",
                 (specificity, comment, report_id))
    conn.commit()
    conn.close()
    db.schedule_auto_push()
    return jsonify({'ok': True})


@app.route('/delete_report/<int:report_id>', methods=['POST'])
@login_required
def delete_report_route(report_id):
    import db
    db.delete_report(report_id)
    return jsonify({'ok': True})


@app.route('/admin')
@login_required
def admin():
    if flask_session.get('role') != 'admin':
        return redirect(url_for('dashboard'))
    import db
    users = db.get_all_users()
    storage = db.get_storage_stats()
    uploads = db.list_uploads()
    return render_template('admin.html', users=users, storage=storage, uploads=uploads,
                           username=flask_session.get('username', ''))


@app.route('/admin/delete_upload', methods=['POST'])
@login_required
def admin_delete_upload():
    if flask_session.get('role') != 'admin':
        return jsonify({'error': '無權限'}), 403
    import db
    filename = (request.json or {}).get('filename', '')
    if not filename:
        return jsonify({'error': '缺檔名'}), 400
    ok = db.delete_upload(filename)
    return jsonify({'ok': ok})


@app.route('/admin/delete_user/<int:user_id>', methods=['POST'])
@login_required
def admin_delete_user(user_id):
    if flask_session.get('role') != 'admin':
        return jsonify({'error': '無權限'}), 403
    import db
    db.delete_user(user_id)
    return jsonify({'ok': True})


@app.route('/admin/update_user/<int:user_id>', methods=['POST'])
@login_required
def admin_update_user(user_id):
    if flask_session.get('role') != 'admin':
        return jsonify({'error': '無權限'}), 403
    import db
    data = request.json
    db.update_user(user_id, data.get('display_name'), data.get('username'),
                   data.get('password'), data.get('role'))
    return jsonify({'ok': True})


@app.route('/save_donor_hla', methods=['POST'])
@login_required
def save_donor_hla_route():
    import db
    data = request.json
    chart_no = data.get('chart_no', '')
    donor_hla = data.get('donor_hla', '')
    if not chart_no:
        return jsonify({'error': 'missing chart_no'}), 400
    db.save_donor_hla(chart_no, donor_hla)
    return jsonify({'ok': True})


@app.route('/get_donor_hla/<chart_no>')
@login_required
def get_donor_hla_route(chart_no):
    import db
    donor_hla = db.get_donor_hla(chart_no)
    return jsonify({'donor_hla': donor_hla})


@app.route('/analysis')
@login_required
def analysis():
    """統計分析頁面 — 選擇病人查看 MFI 趨勢"""
    import db
    patients = db.get_all_patients()
    chart_no = request.args.get('chart_no', '')

    patient = None
    reports = []
    comp1 = None
    comp2 = None

    if chart_no:
        patient, reports = db.get_patient_reports(chart_no)
        if patient:
            dates1, antigens1, pra1, labels1 = db.get_mfi_comparison(chart_no, 'PRA Class I')
            dates2, antigens2, pra2, labels2 = db.get_mfi_comparison(chart_no, 'PRA Class II')
            # 新版 get_mfi_comparison 已以 allele 為主 key 展開，antigen 已是 sero（或無 sero 時 = allele）
            all_labels = {**labels1, **labels2}
            for r in reports:
                r['chart_label'] = all_labels.get(r['id'], r['report_date'])

            class Comp:
                def __init__(self, dates, antigens, pra_by_date):
                    self.dates = dates
                    self.antigens = antigens
                    self.pra_by_date = pra_by_date

            comp1 = Comp(dates1, antigens1, pra1) if dates1 else None
            comp2 = Comp(dates2, antigens2, pra2) if dates2 else None

    donor_hla = ''
    if chart_no:
        donor_hla = db.get_donor_hla(chart_no)

    return render_template('analysis.html', patients=patients, chart_no=chart_no,
                           patient=patient, reports=reports,
                           comparison_class1=comp1, comparison_class2=comp2,
                           donor_hla=donor_hla)


@app.route('/download_upload/<path:filename>')
@login_required
def download_upload(filename):
    """下載上傳的原始檔案"""
    import db
    upload_dir = db.get_upload_dir()
    fpath = upload_dir / filename
    if not fpath.exists():
        return 'File not found', 404
    return send_file(str(fpath), as_attachment=True, download_name=filename)


@app.route('/admin/download_all_uploads')
@login_required
def download_all_uploads():
    """一鍵下載所有上傳檔案 (ZIP)。
    ?source=PRA / DSA：只下載該類，檔案放在 zip 根目錄。
    無 source（預設）：下載全部，依分類放入 PRA/ 與 DSA/ 子資料夾。
    """
    if flask_session.get('role') != 'admin':
        return 'Forbidden', 403
    import db, zipfile
    source = (request.args.get('source') or '').upper()
    uploads = db.list_uploads()
    name_to_source = {u['name']: u['source'] for u in uploads}
    if source in ('PRA', 'DSA'):
        wanted = {n for n, s in name_to_source.items() if s == source}
    else:
        wanted = set(name_to_source)
    upload_dir = db.get_upload_dir()
    files = [f for f in (list(upload_dir.glob('*.xls')) + list(upload_dir.glob('*.xlsx')) + list(upload_dir.glob('*.csv')))
             if f.name in wanted]
    if not files:
        return 'No files', 404

    buf = io.BytesIO()
    with zipfile.ZipFile(buf, 'w', zipfile.ZIP_DEFLATED) as zf:
        for f in files:
            if source in ('PRA', 'DSA'):
                arcname = f.name
            else:
                arcname = f"{name_to_source.get(f.name, 'PRA')}/{f.name}"
            zf.write(str(f), arcname)
    buf.seek(0)
    from datetime import datetime
    tag = f'_{source.lower()}' if source in ('PRA', 'DSA') else ''
    fname = f'uploads{tag}_{datetime.now().strftime("%Y%m%d_%H%M%S")}.zip'
    return send_file(buf, as_attachment=True, download_name=fname,
                     mimetype='application/zip')


@app.route('/admin/cleanup_uploads', methods=['POST'])
@login_required
def cleanup_uploads():
    """清理 N 個月前的上傳檔案"""
    if flask_session.get('role') != 'admin':
        return jsonify({'error': '無權限'}), 403
    import db
    from datetime import datetime, timedelta
    months = request.json.get('months', 6)
    cutoff = datetime.now() - timedelta(days=months * 30)
    upload_dir = db.get_upload_dir()
    files = list(upload_dir.glob('*.xls')) + list(upload_dir.glob('*.xlsx')) + list(upload_dir.glob('*.csv'))
    deleted = 0
    for f in files:
        mtime = datetime.fromtimestamp(f.stat().st_mtime)
        if mtime < cutoff:
            f.unlink()
            deleted += 1
    return jsonify({'ok': True, 'deleted': deleted})


# ============================================================
# DSA Routes (Single-Antigen / Donor-Specific Antibody)
# Mirror of PRA flow but reads SA xls and uses Strong/Weak cutoffs.
# ============================================================

import dsa as _dsa


def _dsa_class_label(tag):
    return 'DSA Class I' if tag == 'DSA1' else 'DSA Class II'


def _build_dsa_result(pc, all_patients, meta):
    pts = all_patients[pc]
    if not pts:
        return None
    m = meta.get(pc, {})
    return {
        'pra_class': pc,
        'class_label': _dsa_class_label(pc),
        'date': m.get('date', ''),
        'batch': m.get('batch', ''),
        'nc_name': m.get('nc_name', 'NC'),
        'pc_signal': round(m.get('pc_signal', 0)),
        'nc_signal': round(m.get('nc_signal', 0)),
        'patients': pts,
    }


@app.route('/dsa/new')
@login_required
def dsa_index():
    return render_template('dsa_index.html')


@app.route('/dsa/analyze', methods=['POST'])
@login_required
def dsa_analyze():
    files = request.files.getlist('file')
    if not files or not files[0].filename:
        return render_template('dsa_index.html', error='請選擇檔案')

    import db as _dbmod
    all_patients = {'DSA1': [], 'DSA2': []}
    meta = {'DSA1': {}, 'DSA2': {}}
    errors = []

    for f in files:
        raw = f.read()
        fname = f.filename or 'unknown'
        ext = Path(fname).suffix.lower()
        is_xls = ext in ('.xls', '.xlsx') or (
            len(raw) > 8 and raw[:8] == b'\xd0\xcf\x11\xe0\xa1\xb1\x1a\xe1')
        if not is_xls:
            errors.append(f'{fname}: 僅支援 .xls')
            continue
        try:
            pt = _dsa.parse_xls_file(raw)
        except Exception as e:
            errors.append(f'{fname}: {e}')
            continue
        if pt is None:
            errors.append(f'{fname}: 無 bead 資料')
            continue
        if not pt.get('name'):
            pt['name'] = Path(fname).stem
        pc = pt.pop('_pra_class', 'DSA1')
        date_val = pt.pop('_date', '')
        batch = pt.pop('_batch', '')
        nc_raw = pt.pop('_nc_raw', 0)
        pc_raw = pt.pop('_pc_raw', 0)

        upload_dir = _dbmod.get_upload_dir()
        orig = Path(fname).name.replace('/', '_').replace('\\', '_') or f'dsa_{pc}{ext}'
        save_path = upload_dir / orig
        try:
            save_path.write_bytes(raw)
            _dbmod.push_upload_to_repo(save_path, save_path.name)
            pt['_upload_file'] = save_path.name
        except Exception:
            pt['_upload_file'] = fname

        all_patients[pc].append(pt)
        if date_val: meta[pc]['date'] = date_val
        if batch: meta[pc]['batch'] = batch
        meta[pc]['nc_signal'] = nc_raw
        meta[pc]['pc_signal'] = pc_raw

    result_dsa1 = _build_dsa_result('DSA1', all_patients, meta)
    result_dsa2 = _build_dsa_result('DSA2', all_patients, meta)

    if not result_dsa1 and not result_dsa2:
        err_msg = '; '.join(errors) if errors else '無法辨識檔案格式'
        return render_template('dsa_index.html', error=err_msg)

    patient_name = request.form.get('patient_name', '').strip()
    patient_id = request.form.get('patient_id', '').strip()
    donor_hla = ''
    if patient_id:
        donor_hla = _dbmod.get_donor_hla(patient_id)

    return render_template('dsa_index.html',
                           result_dsa1=result_dsa1, result_dsa2=result_dsa2,
                           patient_name=patient_name, patient_id=patient_id,
                           donor_hla=donor_hla,
                           errors=errors if errors else None)


@app.route('/dsa/save', methods=['POST'])
@login_required
def dsa_save():
    import db as _dbmod
    data = request.json
    patient_name = data.get('patient_name', '').strip()
    chart_no = data.get('chart_no', '').strip()
    if not chart_no:
        return jsonify({'error': '請輸入病歷號'}), 400

    patient_id = _dbmod.get_or_create_patient(patient_name, chart_no)
    is_submitted = data.get('submitted', False)
    status = 'submitted' if is_submitted else 'draft'
    mode = data.get('mode', 'auto')
    reports = data.get('reports', [])

    if mode == 'auto':
        dups = []
        for r in reports:
            rid = _dbmod.find_active_duplicate_dsa(
                patient_id, r.get('report_date', ''), r.get('dsa_class', ''))
            if rid:
                dups.append({'report_id': rid,
                             'dsa_class': r.get('dsa_class', ''),
                             'report_date': r.get('report_date', '')})
        if dups:
            return jsonify({'ok': False, 'duplicate': dups})

    save_mode = 'new' if mode == 'new' else 'overwrite'
    saved = []
    for r in reports:
        submitted_by = flask_session.get('display_name', flask_session.get('username', ''))
        rid = _dbmod.save_dsa_report(
            patient_id, r.get('report_date', ''), r.get('dsa_class', ''),
            r.get('pct_sa', 0), r.get('overall', ''),
            r.get('specificity', ''), r.get('comment', ''),
            r.get('sero_mfi', []), status, submitted_by,
            r.get('upload_file', ''), mode=save_mode)
        saved.append({'report_id': rid, 'dsa_class': r.get('dsa_class', '')})
    return jsonify({'ok': True, 'patient_id': patient_id, 'saved': saved})


@app.route('/dsa/batch_upload', methods=['GET', 'POST'])
@login_required
def dsa_batch_upload():
    if request.method == 'GET':
        return render_template('dsa_batch.html')

    import db as _dbmod
    files = request.files.getlist('file')
    charts = request.form.getlist('chart')
    names = request.form.getlist('name')
    mode = request.form.get('mode', 'new')

    results = []
    ok_n = err_n = dup_n = 0
    submitted_by = flask_session.get('display_name', flask_session.get('username', ''))

    for idx, f in enumerate(files):
        fname = f.filename or f'file{idx}'
        chart = (charts[idx] if idx < len(charts) else '').strip()
        name = (names[idx] if idx < len(names) else '').strip()
        if not chart:
            results.append({'file': fname, 'status': 'error', 'msg': '缺病歷號'})
            err_n += 1
            continue
        try:
            raw = f.read()
            pt = _dsa.parse_xls_file(raw)
        except Exception:
            results.append({'file': fname, 'status': 'error', 'msg': '解析失敗'})
            err_n += 1
            continue
        if pt is None:
            results.append({'file': fname, 'status': 'error', 'msg': '無 bead 資料'})
            err_n += 1
            continue

        dsa_tag = pt.pop('_pra_class', 'DSA1')
        dsa_class = _dsa_class_label(dsa_tag)
        date_val = pt.pop('_date', '') or ''
        if not date_val:
            results.append({'file': fname, 'status': 'error', 'msg': '抓不到報告日期'})
            err_n += 1
            continue

        patient_id = _dbmod.get_or_create_patient(name or fname, chart)

        upload_dir = _dbmod.get_upload_dir()
        orig_name = Path(fname).name.replace('/', '_').replace('\\', '_') or f'file_{idx}.xls'
        save_path = upload_dir / orig_name
        try:
            save_path.write_bytes(raw)
            saved_filename = save_path.name
            _dbmod.push_upload_to_repo(save_path, saved_filename)
        except Exception:
            saved_filename = fname

        existing = _dbmod.find_active_duplicate_dsa(patient_id, date_val, dsa_class)
        save_mode = 'overwrite'
        if existing and mode == 'skip':
            results.append({'file': fname, 'status': 'skipped',
                            'msg': f'檔案已保留；active report_id={existing}'})
            dup_n += 1
            continue
        if existing and mode == 'new':
            save_mode = 'new'

        import re as _re
        spec_plain = _re.sub(r'<[^>]*>', '', pt.get('specificity', '')).strip()
        spec_plain = _re.sub(r'\s+', ' ', spec_plain)

        rid = _dbmod.save_dsa_report(
            patient_id, date_val, dsa_class,
            pt.get('pra', 0), pt.get('overall', ''),
            spec_plain, pt.get('comment', ''),
            pt.get('sero_mfi', []),
            'submitted', submitted_by, saved_filename, mode=save_mode)
        if existing:
            dup_n += 1
            results.append({'file': fname, 'status': 'duplicate',
                            'msg': f'mode={mode}, report_id={rid}'})
        else:
            ok_n += 1
            results.append({'file': fname, 'status': 'ok', 'msg': f'report_id={rid}'})

    return jsonify({'results': results,
                    'summary': {'ok': ok_n, 'err': err_n, 'dup': dup_n, 'total': len(files)}})


@app.route('/dsa/history')
@login_required
def dsa_history():
    import db as _dbmod
    reports = _dbmod.get_all_dsa_reports(limit=500)
    for r in reports:
        r['donor_hla_fmt'] = format_donor_hla(r.get('donor_hla', ''))
    db_stats = {'patients': len(_dbmod.get_all_dsa_patients()), 'reports': len(reports)}
    is_admin = flask_session.get('role') == 'admin'
    return render_template('dsa_history.html', reports=reports, db_stats=db_stats, is_admin=is_admin)


@app.route('/dsa/history/<chart_no>')
@login_required
def dsa_patient_history(chart_no):
    import db as _dbmod
    patient, reports = _dbmod.get_dsa_patient_reports(chart_no)
    if not patient:
        return redirect(url_for('dsa_history'))

    dates1, antigens1, pra1, labels1 = _dbmod.get_dsa_mfi_comparison(chart_no, 'DSA Class I')
    dates2, antigens2, pra2, labels2 = _dbmod.get_dsa_mfi_comparison(chart_no, 'DSA Class II')
    all_labels = {**labels1, **labels2}
    for r in reports:
        r['chart_label'] = all_labels.get(r['id'], r['report_date'])

    class Comp:
        def __init__(self, dates, antigens, pra_by_date):
            self.dates = dates
            self.antigens = antigens
            self.pra_by_date = pra_by_date

    comp1 = Comp(dates1, antigens1, pra1) if dates1 else None
    comp2 = Comp(dates2, antigens2, pra2) if dates2 else None
    donor_hla = _dbmod.get_donor_hla(chart_no)

    return render_template('dsa_patient.html', patient=patient, reports=reports,
                           comparison_class1=comp1, comparison_class2=comp2,
                           donor_hla=donor_hla)


@app.route('/dsa/analysis')
@login_required
def dsa_analysis():
    import db as _dbmod
    patients = _dbmod.get_all_dsa_patients()
    chart_no = request.args.get('chart_no', '')

    patient = None
    reports = []
    comp1 = None
    comp2 = None

    if chart_no:
        patient, reports = _dbmod.get_dsa_patient_reports(chart_no)
        if patient:
            dates1, antigens1, pra1, labels1 = _dbmod.get_dsa_mfi_comparison(chart_no, 'DSA Class I')
            dates2, antigens2, pra2, labels2 = _dbmod.get_dsa_mfi_comparison(chart_no, 'DSA Class II')
            all_labels = {**labels1, **labels2}
            for r in reports:
                r['chart_label'] = all_labels.get(r['id'], r['report_date'])

            class Comp:
                def __init__(self, dates, antigens, pra_by_date):
                    self.dates = dates
                    self.antigens = antigens
                    self.pra_by_date = pra_by_date

            comp1 = Comp(dates1, antigens1, pra1) if dates1 else None
            comp2 = Comp(dates2, antigens2, pra2) if dates2 else None

    donor_hla = _dbmod.get_donor_hla(chart_no) if chart_no else ''
    return render_template('dsa_analysis.html', patients=patients, chart_no=chart_no,
                           patient=patient, reports=reports,
                           comparison_class1=comp1, comparison_class2=comp2,
                           donor_hla=donor_hla)


@app.route('/dsa/update_report', methods=['POST'])
@login_required
def dsa_update_report():
    import db as _dbmod
    data = request.json
    rid = int(data.get('report_id', 0))
    if not rid:
        return jsonify({'error': 'missing report_id'}), 400
    spec = data.get('specificity', '')
    comment = data.get('comment', '')
    conn = _dbmod.get_conn()
    conn.execute("""UPDATE dsa_reports SET specificity=?, comment=?,
                    updated_at=datetime('now','localtime') WHERE id=?""",
                 (spec, comment, rid))
    conn.commit()
    conn.close()
    _dbmod.schedule_auto_push()
    return jsonify({'ok': True})


@app.route('/dsa/delete_report/<int:report_id>', methods=['POST'])
@login_required
def dsa_delete_report_route(report_id):
    import db as _dbmod
    _dbmod.delete_dsa_report(report_id)
    return jsonify({'ok': True})


# ============================================================
# Combined (PRA + DSA) Analysis Route
# ============================================================

@app.route('/combined')
@login_required
def combined_analysis():
    """Combined PRA + DSA trend on one page (per patient)."""
    import db as _dbmod
    patients = _dbmod.get_patients_with_both()
    chart_no = request.args.get('chart_no', '')

    patient = None
    reports = []
    comp1 = comp1_alleles = None
    comp2 = comp2_alleles = None
    donor_hla = ''

    if chart_no:
        # Resolve patient
        conn = _dbmod.get_conn()
        row = conn.execute('SELECT * FROM patients WHERE chart_no=?', (chart_no,)).fetchone()
        conn.close()
        if row:
            patient = dict(row)
            reports = _dbmod.get_combined_reports(chart_no)
            d1, ag1 = _dbmod.get_combined_mfi(chart_no, 'I')
            d2, ag2 = _dbmod.get_combined_mfi(chart_no, 'II')
            class Comp:
                def __init__(self, dates, antigens):
                    self.dates = dates
                    self.antigens = antigens
            comp1 = Comp(d1, ag1) if d1 else None
            comp2 = Comp(d2, ag2) if d2 else None
            donor_hla = _dbmod.get_donor_hla(chart_no)

    return render_template('combined_analysis.html',
                           patients=patients, chart_no=chart_no,
                           patient=patient, reports=reports,
                           comparison_class1=comp1, comparison_class2=comp2,
                           donor_hla=donor_hla)


if __name__ == '__main__':
    print('PRA / DSA Analysis Web App')
    print('http://127.0.0.1:5000')
    app.run(debug=True, port=5000)