db.py

# -*- coding: utf-8 -*-
"""
PRA Database — SQLite
儲存病人 PRA 分析結果，支援多次檢驗 MFI 比較
"""

import sqlite3
import shutil
import os
import threading
import logging
from pathlib import Path
from datetime import datetime

DB_PATH = Path(os.environ.get('DB_PATH', Path(__file__).parent / 'pra_data.db'))
BACKUP_DIR = DB_PATH.parent / 'backups'

# --- Auto-push：DB 寫入後立即背景推到雲端 repo ---
_push_lock = threading.Lock()
_push_dirty = False  # 推送中又有新寫入 → 結束後再推一次

logger = logging.getLogger(__name__)


def _do_push():
    """背景執行：把 DB 推到雲端 repo，推完若有新變更再推一次"""
    global _push_dirty
    try:
        token = os.environ.get('HF_TOKEN', '')
        username = os.environ.get('HF_USERNAME', '')
        if not token or not username:
            return
        from huggingface_hub import HfApi
        api = HfApi(token=token)
        repo_id = f'{username}/pradsa'
        while True:
            _push_dirty = False
            api.upload_file(
                path_or_fileobj=str(DB_PATH),
                path_in_repo='pra_data.db',
                repo_id=repo_id,
                repo_type='space',
            )
            logger.info('Auto-push DB to repo OK')
            if not _push_dirty:
                break
            # 推送期間有新寫入，再推一次
            logger.info('DB changed during push, pushing again...')
    except Exception as e:
        logger.warning(f'Auto-push failed: {e}')


def schedule_auto_push():
    """DB 寫入後立即背景推到 repo。若已在推送中，標記 dirty 等推完再補推。"""
    global _push_dirty
    if not os.environ.get('HF_TOKEN'):
        return
    if _push_lock.locked():
        _push_dirty = True
        return
    t = threading.Thread(target=_guarded_push, daemon=True)
    t.start()


def push_upload_to_repo(local_path, filename):
    """同步把單一上傳 XLS 推到 repo 的 uploads/<filename>，
    確保在 request 結束前完成（避免 daemon thread 被容器閒置/重啟中斷）。
    失敗只記錄，不拋出（本地已存檔，可靠 startup 時再推）。"""
    token = os.environ.get('HF_TOKEN', '')
    username = os.environ.get('HF_USERNAME', '')
    if not token or not username:
        return False
    try:
        from huggingface_hub import HfApi
        api = HfApi(token=token)
        api.upload_file(
            path_or_fileobj=str(local_path),
            path_in_repo=f'uploads/{filename}',
            repo_id=f'{username}/pradsa',
            repo_type='space',
        )
        logger.info(f'Upload pushed to repo: uploads/{filename}')
        return True
    except Exception as e:
        logger.warning(f'Upload push failed ({filename}): {e}')
        return False


def pull_uploads_from_repo():
    """容器啟動時從 repo 拉 uploads/* 到本地 UPLOAD_DIR。
    只下載本地沒有的檔案（以檔名判斷），避免重複下載。"""
    token = os.environ.get('HF_TOKEN', '')
    username = os.environ.get('HF_USERNAME', '')
    if not token or not username:
        return
    try:
        from huggingface_hub import HfApi
        api = HfApi(token=token)
        repo_id = f'{username}/pradsa'
        UPLOAD_DIR.mkdir(exist_ok=True)
        existing = {p.name for p in UPLOAD_DIR.iterdir() if p.is_file()}
        files = api.list_repo_files(repo_id, repo_type='space')
        pulled = 0
        for rf in files:
            if not rf.startswith('uploads/'):
                continue
            name = rf[len('uploads/'):]
            if not name or name in existing:
                continue
            try:
                local = api.hf_hub_download(
                    repo_id=repo_id, filename=rf, repo_type='space',
                    local_dir=str(UPLOAD_DIR.parent),
                )
                # hf_hub_download 會照 path_in_repo 結構存放；確保檔案在 UPLOAD_DIR
                dest = UPLOAD_DIR / name
                if not dest.exists():
                    shutil.copy2(local, dest)
                pulled += 1
            except Exception as e:
                logger.warning(f'pull upload {name} failed: {e}')
        if pulled:
            logger.info(f'Pulled {pulled} upload(s) from repo')
    except Exception as e:
        logger.warning(f'pull_uploads_from_repo failed: {e}')


def _guarded_push():
    with _push_lock:
        _do_push()


def backup_db():
    """異動前自動備份 DB。當天最多 5 個，總共最多 15 個。"""
    if not DB_PATH.exists():
        return
    BACKUP_DIR.mkdir(exist_ok=True)
    today = datetime.now().strftime('%Y%m%d')
    ts = datetime.now().strftime('%Y%m%d_%H%M%S')
    dst = BACKUP_DIR / f'pra_data_{ts}.db'
    shutil.copy2(str(DB_PATH), str(dst))

    # 當天只留最近 3 個
    today_backups = sorted(BACKUP_DIR.glob(f'pra_data_{today}_*.db'))
    for old in today_backups[:-5]:
        old.unlink()

    # 總共最多 15 個
    all_backups = sorted(BACKUP_DIR.glob('pra_data_*.db'))
    for old in all_backups[:-15]:
        old.unlink()


def push_backups_to_repo():
    """雲端同步時：把 backup 資料夾推到 repo"""
    token = os.environ.get('HF_TOKEN', '')
    username = os.environ.get('HF_USERNAME', '')
    if not token or not username:
        return
    try:
        from huggingface_hub import HfApi
        api = HfApi(token=token)
        repo_id = f'{username}/pradsa'
        # 上傳所有 local backup
        backups = sorted(BACKUP_DIR.glob('pra_data_*.db'))
        for bk in backups[-15:]:  # 只推最新 10 個
            api.upload_file(
                path_or_fileobj=str(bk),
                path_in_repo=f'backups/{bk.name}',
                repo_id=repo_id,
                repo_type='space',
            )
        # 清理 repo 舊 backup
        all_files = api.list_repo_files(repo_id, repo_type='space')
        repo_backups = sorted([f for f in all_files if f.startswith('backups/pra_data_')])
        for old_f in repo_backups[:-15]:
            try:
                api.delete_file(old_f, repo_id=repo_id, repo_type='space')
            except Exception:
                pass
    except Exception:
        pass


import hashlib

def hash_pw(pw):
    return hashlib.sha256(pw.encode()).hexdigest()


def get_conn():
    conn = sqlite3.connect(str(DB_PATH))
    conn.row_factory = sqlite3.Row
    conn.execute('PRAGMA foreign_keys = ON')
    return conn


def init_db():
    conn = get_conn()
    conn.executescript('''
        CREATE TABLE IF NOT EXISTS patients (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            patient_name TEXT NOT NULL,
            chart_no TEXT NOT NULL,
            created_at TEXT DEFAULT (datetime('now','localtime')),
            donor_hla TEXT DEFAULT '',
            UNIQUE(chart_no)
        );

        CREATE TABLE IF NOT EXISTS reports (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            patient_id INTEGER NOT NULL,
            report_date TEXT NOT NULL,
            pra_class TEXT NOT NULL,
            pra_percent INTEGER NOT NULL,
            overall TEXT NOT NULL,
            specificity TEXT,
            comment TEXT,
            status TEXT DEFAULT 'draft',
            created_at TEXT DEFAULT (datetime('now','localtime')),
            updated_at TEXT DEFAULT (datetime('now','localtime')),
            FOREIGN KEY (patient_id) REFERENCES patients(id)
        );

        CREATE TABLE IF NOT EXISTS antibody_strength (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            report_id INTEGER NOT NULL,
            antigen TEXT NOT NULL,
            allele TEXT,
            max_mfi REAL,
            mean_mfi REAL,
            no_of_beads INTEGER,
            bead_ids TEXT,
            FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL UNIQUE,
            display_name TEXT DEFAULT '',
            password_hash TEXT NOT NULL,
            password_plain TEXT DEFAULT '',
            role TEXT DEFAULT 'user',
            created_at TEXT DEFAULT (datetime('now','localtime')),
            edit_time TEXT DEFAULT (datetime('now','localtime'))
        );

        CREATE INDEX IF NOT EXISTS idx_reports_patient ON reports(patient_id);
        CREATE INDEX IF NOT EXISTS idx_reports_date ON reports(report_date);
        CREATE INDEX IF NOT EXISTS idx_ab_report ON antibody_strength(report_id);

        CREATE TABLE IF NOT EXISTS dsa_reports (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            patient_id INTEGER NOT NULL,
            report_date TEXT NOT NULL,
            dsa_class TEXT NOT NULL,
            pct_sa INTEGER NOT NULL,
            overall TEXT NOT NULL,
            specificity TEXT,
            comment TEXT,
            status TEXT DEFAULT 'draft',
            submitted_by TEXT DEFAULT '',
            upload_file TEXT DEFAULT '',
            is_deleted INTEGER DEFAULT 0,
            deleted_at TEXT,
            created_at TEXT DEFAULT (datetime('now','localtime')),
            updated_at TEXT DEFAULT (datetime('now','localtime')),
            FOREIGN KEY (patient_id) REFERENCES patients(id)
        );

        CREATE TABLE IF NOT EXISTS dsa_antibody_strength (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            report_id INTEGER NOT NULL,
            antigen TEXT NOT NULL,
            allele TEXT,
            strength TEXT,
            max_mfi REAL,
            mean_mfi REAL,
            no_of_beads INTEGER,
            bead_ids TEXT,
            FOREIGN KEY (report_id) REFERENCES dsa_reports(id) ON DELETE CASCADE
        );

        CREATE INDEX IF NOT EXISTS idx_dsa_reports_patient ON dsa_reports(patient_id);
        CREATE INDEX IF NOT EXISTS idx_dsa_reports_date ON dsa_reports(report_date);
        CREATE INDEX IF NOT EXISTS idx_dsa_ab_report ON dsa_antibody_strength(report_id);
    ''')
    # migrate patients table
    pat_cols = [r[1] for r in conn.execute('PRAGMA table_info(patients)').fetchall()]
    if 'donor_hla' not in pat_cols:
        conn.execute("ALTER TABLE patients ADD COLUMN donor_hla TEXT DEFAULT ''")

    # migrate: soft delete columns
    report_cols_all = [r[1] for r in conn.execute('PRAGMA table_info(reports)').fetchall()]
    if 'upload_file' not in report_cols_all:
        try:
            conn.execute("ALTER TABLE reports ADD COLUMN upload_file TEXT DEFAULT ''")
        except Exception:
            pass
    if 'is_deleted' not in report_cols_all:
        try:
            conn.execute("ALTER TABLE reports ADD COLUMN is_deleted INTEGER DEFAULT 0")
            conn.execute("ALTER TABLE reports ADD COLUMN deleted_at TEXT")
        except Exception:
            pass

    # migrate users table
    user_cols = [r[1] for r in conn.execute('PRAGMA table_info(users)').fetchall()]
    if 'role' not in user_cols:
        conn.execute("ALTER TABLE users ADD COLUMN role TEXT DEFAULT 'user'")
    if 'display_name' not in user_cols:
        conn.execute("ALTER TABLE users ADD COLUMN display_name TEXT DEFAULT ''")
    if 'password_plain' not in user_cols:
        conn.execute("ALTER TABLE users ADD COLUMN password_plain TEXT DEFAULT ''")
    if 'edit_time' not in [c.lower() for c in user_cols]:
        try:
            conn.execute("ALTER TABLE users ADD COLUMN edit_time TEXT")
        except Exception:
            pass

    # migrate reports table
    report_cols = [r[1] for r in conn.execute('PRAGMA table_info(reports)').fetchall()]
    if 'submitted_by' not in report_cols:
        conn.execute("ALTER TABLE reports ADD COLUMN submitted_by TEXT DEFAULT ''")

    # migrate: add status/updated_at if missing
    cols = [r[1] for r in conn.execute('PRAGMA table_info(reports)').fetchall()]
    if 'status' not in cols:
        conn.execute("ALTER TABLE reports ADD COLUMN status TEXT DEFAULT 'draft'")
    if 'updated_at' not in cols:
        conn.execute("ALTER TABLE reports ADD COLUMN updated_at TEXT")
    conn.commit()
    conn.close()


def get_or_create_patient(patient_name, chart_no):
    backup_db()
    conn = get_conn()
    row = conn.execute('SELECT id FROM patients WHERE chart_no = ?', (chart_no,)).fetchone()
    if row:
        conn.execute('UPDATE patients SET patient_name = ? WHERE id = ?', (patient_name, row['id']))
        conn.commit()
        pid = row['id']
    else:
        cur = conn.execute('INSERT INTO patients (patient_name, chart_no) VALUES (?, ?)',
                           (patient_name, chart_no))
        conn.commit()
        pid = cur.lastrowid
    conn.close()
    schedule_auto_push()
    return pid


def save_donor_hla(chart_no, donor_hla_json):
    """儲存病人的 Donor HLA"""
    backup_db()
    conn = get_conn()
    conn.execute('UPDATE patients SET donor_hla=? WHERE chart_no=?', (donor_hla_json, chart_no))
    conn.commit()
    conn.close()
    schedule_auto_push()


def get_donor_hla(chart_no):
    conn = get_conn()
    row = conn.execute('SELECT donor_hla FROM patients WHERE chart_no=?', (chart_no,)).fetchone()
    conn.close()
    return row['donor_hla'] if row and row['donor_hla'] else ''


def find_active_duplicate(patient_id, report_date, pra_class):
    """回傳 active（未刪除）重複 report 的 id，沒有則 None。"""
    conn = get_conn()
    row = conn.execute(
        '''SELECT id FROM reports
           WHERE patient_id=? AND report_date=? AND pra_class=?
             AND COALESCE(is_deleted,0)=0''',
        (patient_id, report_date, pra_class)
    ).fetchone()
    conn.close()
    return row['id'] if row else None


def save_report(patient_id, report_date, pra_class, pra_percent, overall,
                specificity, comment, sero_mfi_list, status='draft', submitted_by='',
                upload_file='', mode='overwrite'):
    """mode: 'overwrite' 覆寫 active 重複；'new' 強制 INSERT 新列。"""
    backup_db()
    conn = get_conn()
    existing = None
    if mode == 'overwrite':
        existing = conn.execute(
            '''SELECT id FROM reports
               WHERE patient_id=? AND report_date=? AND pra_class=?
                 AND COALESCE(is_deleted,0)=0''',
            (patient_id, report_date, pra_class)
        ).fetchone()

    if existing:
        report_id = existing['id']
        conn.execute('''UPDATE reports SET pra_percent=?, overall=?, specificity=?, comment=?,
                        status=?, submitted_by=?, upload_file=?, updated_at=datetime('now','localtime') WHERE id=?''',
                     (pra_percent, overall, specificity, comment, status, submitted_by, upload_file or '', report_id))
        conn.execute('DELETE FROM antibody_strength WHERE report_id=?', (report_id,))
    else:
        cur = conn.execute(
            '''INSERT INTO reports (patient_id, report_date, pra_class, pra_percent, overall,
               specificity, comment, status, submitted_by, upload_file) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''',
            (patient_id, report_date, pra_class, pra_percent, overall, specificity, comment, status, submitted_by, upload_file or ''))
        report_id = cur.lastrowid

    for m in sero_mfi_list:
        conn.execute(
            '''INSERT INTO antibody_strength
               (report_id, antigen, allele, max_mfi, mean_mfi, no_of_beads, bead_ids)
               VALUES (?, ?, ?, ?, ?, ?, ?)''',
            (report_id, m['sero'], m['alleles'], m['max_mfi'], m['mean_mfi'],
             m['count'], m['beads']))

    conn.commit()
    conn.close()
    schedule_auto_push()
    return report_id


def get_all_reports(limit=100):
    """取得所有報告 (含病人資訊，排除已刪除)"""
    conn = get_conn()
    rows = conn.execute('''
        SELECT r.*, p.patient_name, p.chart_no, p.donor_hla
        FROM reports r
        JOIN patients p ON p.id = r.patient_id
        WHERE COALESCE(r.is_deleted, 0) = 0
        ORDER BY p.patient_name, p.chart_no, r.report_date DESC, r.pra_class
        LIMIT ?
    ''', (limit,)).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def get_all_patients():
    conn = get_conn()
    rows = conn.execute('''
        SELECT p.id, p.patient_name, p.chart_no,
               COUNT(r.id) as report_count,
               MAX(r.report_date) as latest_date,
               GROUP_CONCAT(DISTINCT r.pra_class) as classes
        FROM patients p
        LEFT JOIN reports r ON r.patient_id = p.id AND COALESCE(r.is_deleted, 0) = 0
        GROUP BY p.id
        HAVING report_count > 0
        ORDER BY latest_date DESC
    ''').fetchall()
    conn.close()
    return [dict(r) for r in rows]


def get_patient_reports(chart_no):
    """取得病人所有報告 + antibody strength"""
    conn = get_conn()
    patient = conn.execute('SELECT * FROM patients WHERE chart_no=?', (chart_no,)).fetchone()
    if not patient:
        conn.close()
        return None, []

    reports = conn.execute('''
        SELECT * FROM reports WHERE patient_id=? AND COALESCE(is_deleted, 0) = 0
        ORDER BY report_date DESC, pra_class
    ''', (patient['id'],)).fetchall()

    result = []
    for r in reports:
        ab = conn.execute(
            'SELECT * FROM antibody_strength WHERE report_id=? ORDER BY id', (r['id'],)
        ).fetchall()
        result.append({**dict(r), 'antibodies': [dict(a) for a in ab]})

    conn.close()
    return dict(patient), result


def get_mfi_comparison(chart_no, pra_class=None):
    """
    取得同一病人跨日期的 MFI 比較資料。
    同一天有多筆 report 時，label 加上 (A)/(B)/... 後綴（按 report_id 升冪）。
    回傳:
        dates: [label1, label2, ...]   # 顯示用字串，同日多筆會帶後綴
        antigens: [{antigen, allele, mfi_by_date: {label: {...}, ...}}, ...]
        pra_by_date: {label: pra_percent}
        labels_by_rid: {report_id: label}  # 用於把 report row 對應到圖上 label
    """
    conn = get_conn()
    patient = conn.execute('SELECT id FROM patients WHERE chart_no=?', (chart_no,)).fetchone()
    if not patient:
        conn.close()
        return [], [], {}, {}

    rep_query = '''
        SELECT id, report_date, pra_percent, specificity
        FROM reports
        WHERE patient_id = ? AND COALESCE(is_deleted, 0) = 0
    '''
    rep_params = [patient['id']]
    if pra_class:
        rep_query += ' AND pra_class = ?'
        rep_params.append(pra_class)
    rep_query += ' ORDER BY report_date, id'
    reports = conn.execute(rep_query, rep_params).fetchall()

    from collections import defaultdict
    date_count = defaultdict(int)
    for r in reports:
        date_count[r['report_date']] += 1

    date_seen = defaultdict(int)
    labels_by_rid = {}
    dates = []
    pra_by_date = {}
    for r in reports:
        d = r['report_date']
        if date_count[d] == 1:
            label = d
        else:
            suffix = chr(ord('A') + date_seen[d])
            label = f'{d} ({suffix})'
            date_seen[d] += 1
        labels_by_rid[r['id']] = label
        dates.append(label)
        pra_by_date[label] = r['pra_percent']

    if not reports:
        conn.close()
        return [], [], {}, {}

    rep_ids = [r['id'] for r in reports]
    placeholders = ','.join('?' * len(rep_ids))
    ab_rows = conn.execute(f'''
        SELECT report_id, antigen, allele, max_mfi, mean_mfi, no_of_beads
        FROM antibody_strength
        WHERE report_id IN ({placeholders})
        ORDER BY antigen
    ''', rep_ids).fetchall()
    conn.close()

    # 以 allele 為主 key 展開：每個 confident allele 一列。
    # 每份 report 的 specificity 字串當作該 report 的 confident allele 來源真理（per-report 獨立）。
    # 同一 sero 下的不同 allele（如 DRB1*11:01 vs DRB1*11:02）會拆成獨立列，
    # 哪些 allele 只在部分 report confident，其他 report 值為 None → 趨勢自然顯示「消失」。
    import re as _re
    parsed = {}  # rid -> {'sero_alleles': {sero: set(alleles)}, 'bare': set(alleles_no_sero)}
    for r in reports:
        spec = r['specificity'] or ''
        sa_map = {}
        bare = set()
        for mt in _re.finditer(r'([A-Za-z][A-Za-z0-9]*)\(([^)]*)\)', spec):
            s = mt.group(1).strip()
            for a in mt.group(2).split():
                a = a.strip()
                if a:
                    sa_map.setdefault(s, set()).add(a)
        # 無括號的 standalone allele（DQA1*, DPA1*）
        no_parens = _re.sub(r'\([^)]*\)', ' ', spec)
        for tok in no_parens.split():
            tok = tok.strip()
            if '*' in tok and ':' in tok:
                bare.add(tok)
        parsed[r['id']] = {'sero_alleles': sa_map, 'bare': bare}

    # allele → sero 對照（全 report 累積，只記有括號對應者）
    allele_to_sero = {}
    for p in parsed.values():
        for s, alleles in p['sero_alleles'].items():
            for a in alleles:
                allele_to_sero[a] = s

    # antibody_strength 以 (rid, antigen) 查表；同時保留 sero 的 allele_list 字串供 fallback
    ab_map = {}
    for ab in ab_rows:
        ab_map[(ab['report_id'], ab['antigen'])] = dict(ab)

    # 收集所有 allele（聯集）
    all_alleles = set()
    for p in parsed.values():
        for alleles in p['sero_alleles'].values():
            all_alleles.update(alleles)
        all_alleles.update(p['bare'])

    # 對每個 allele，逐 report 建 mfi_by_date
    def allele_sort_key(a):
        # sero 排序：A < B < Cw/C < DR/DRB1 < DQ/DQB1/DQA1 < DP/DPB1/DPA1
        locus_order = {'A': 0, 'B': 1, 'C': 2, 'Cw': 2,
                       'DR': 3, 'DRB': 3, 'DRB1': 3, 'DRB3': 3, 'DRB4': 3, 'DRB5': 3,
                       'DQ': 4, 'DQB': 4, 'DQB1': 4, 'DQA1': 5,
                       'DP': 6, 'DPB': 6, 'DPB1': 6, 'DPA1': 7}
        if '*' in a:
            loc = a.split('*')[0]
        else:
            m = _re.match(r'[A-Za-z]+', a)
            loc = m.group() if m else ''
        nums = _re.findall(r'\d+', a)
        n1 = int(nums[0]) if nums else 0
        n2 = int(nums[1]) if len(nums) > 1 else 0
        return (locus_order.get(loc, 99), n1, n2, a)

    antigens_out = []
    for allele in sorted(all_alleles, key=allele_sort_key):
        sero = allele_to_sero.get(allele)
        mfi_by_date = {}
        for r in reports:
            rid = r['id']
            lbl = labels_by_rid[rid]
            p = parsed[rid]
            present = False
            if sero:
                present = allele in p['sero_alleles'].get(sero, set())
            else:
                present = allele in p['bare']
            if not present:
                continue
            # MFI 查表：sero 優先，allele 作 fallback（legacy DQB1/DPB1 存 antigen=allele）
            ab = None
            if sero:
                ab = ab_map.get((rid, sero)) or ab_map.get((rid, allele))
            else:
                ab = ab_map.get((rid, allele))
            if ab:
                mfi_by_date[lbl] = {
                    'max_mfi': ab['max_mfi'],
                    'mean_mfi': ab['mean_mfi'],
                    'no_of_beads': ab['no_of_beads'],
                }
        if not mfi_by_date:
            continue
        antigens_out.append({
            'antigen': sero if sero else allele,
            'allele': allele,
            'mfi_by_date': mfi_by_date,
        })
    return dates, antigens_out, pra_by_date, labels_by_rid


# ============================================================
# DSA helpers (Single-Antigen / Donor-Specific Antibody)
# ============================================================

def find_active_duplicate_dsa(patient_id, report_date, dsa_class):
    conn = get_conn()
    row = conn.execute(
        '''SELECT id FROM dsa_reports
           WHERE patient_id=? AND report_date=? AND dsa_class=?
             AND COALESCE(is_deleted,0)=0''',
        (patient_id, report_date, dsa_class)
    ).fetchone()
    conn.close()
    return row['id'] if row else None


def save_dsa_report(patient_id, report_date, dsa_class, pct_sa, overall,
                    specificity, comment, sero_mfi_list, status='draft',
                    submitted_by='', upload_file='', mode='overwrite'):
    backup_db()
    conn = get_conn()
    existing = None
    if mode == 'overwrite':
        existing = conn.execute(
            '''SELECT id FROM dsa_reports
               WHERE patient_id=? AND report_date=? AND dsa_class=?
                 AND COALESCE(is_deleted,0)=0''',
            (patient_id, report_date, dsa_class)
        ).fetchone()

    if existing:
        report_id = existing['id']
        conn.execute('''UPDATE dsa_reports SET pct_sa=?, overall=?, specificity=?, comment=?,
                        status=?, submitted_by=?, upload_file=?,
                        updated_at=datetime('now','localtime') WHERE id=?''',
                     (pct_sa, overall, specificity, comment, status, submitted_by,
                      upload_file or '', report_id))
        conn.execute('DELETE FROM dsa_antibody_strength WHERE report_id=?', (report_id,))
    else:
        cur = conn.execute(
            '''INSERT INTO dsa_reports (patient_id, report_date, dsa_class, pct_sa, overall,
               specificity, comment, status, submitted_by, upload_file)
               VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''',
            (patient_id, report_date, dsa_class, pct_sa, overall, specificity, comment,
             status, submitted_by, upload_file or ''))
        report_id = cur.lastrowid

    for m in sero_mfi_list:
        conn.execute(
            '''INSERT INTO dsa_antibody_strength
               (report_id, antigen, allele, strength, max_mfi, mean_mfi, no_of_beads, bead_ids)
               VALUES (?, ?, ?, ?, ?, ?, ?, ?)''',
            (report_id, m.get('sero', ''), m.get('alleles', ''),
             m.get('strength', ''), m.get('max_mfi', 0), m.get('mean_mfi', 0),
             m.get('count', 0), m.get('beads', '')))

    conn.commit()
    conn.close()
    schedule_auto_push()
    return report_id


def get_all_dsa_reports(limit=200):
    conn = get_conn()
    rows = conn.execute('''
        SELECT r.*, p.patient_name, p.chart_no, p.donor_hla
        FROM dsa_reports r JOIN patients p ON p.id = r.patient_id
        WHERE COALESCE(r.is_deleted, 0) = 0
        ORDER BY p.patient_name, p.chart_no, r.report_date DESC, r.dsa_class
        LIMIT ?
    ''', (limit,)).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def get_all_dsa_patients():
    conn = get_conn()
    rows = conn.execute('''
        SELECT p.id, p.patient_name, p.chart_no,
               COUNT(r.id) as report_count,
               MAX(r.report_date) as latest_date,
               GROUP_CONCAT(DISTINCT r.dsa_class) as classes
        FROM patients p
        LEFT JOIN dsa_reports r ON r.patient_id = p.id AND COALESCE(r.is_deleted, 0) = 0
        GROUP BY p.id
        HAVING report_count > 0
        ORDER BY latest_date DESC
    ''').fetchall()
    conn.close()
    return [dict(r) for r in rows]


def get_dsa_patient_reports(chart_no):
    conn = get_conn()
    patient = conn.execute('SELECT * FROM patients WHERE chart_no=?', (chart_no,)).fetchone()
    if not patient:
        conn.close()
        return None, []
    reports = conn.execute('''
        SELECT * FROM dsa_reports WHERE patient_id=? AND COALESCE(is_deleted,0)=0
        ORDER BY report_date DESC, dsa_class
    ''', (patient['id'],)).fetchall()
    out = []
    for r in reports:
        ab = conn.execute('SELECT * FROM dsa_antibody_strength WHERE report_id=? ORDER BY id',
                          (r['id'],)).fetchall()
        out.append({**dict(r), 'antibodies': [dict(a) for a in ab]})
    conn.close()
    return dict(patient), out


def get_dsa_mfi_comparison(chart_no, dsa_class=None):
    """Same shape as PRA's get_mfi_comparison but reads dsa_reports."""
    conn = get_conn()
    patient = conn.execute('SELECT id FROM patients WHERE chart_no=?', (chart_no,)).fetchone()
    if not patient:
        conn.close()
        return [], [], {}, {}

    q = '''SELECT id, report_date, pct_sa, specificity FROM dsa_reports
           WHERE patient_id = ? AND COALESCE(is_deleted, 0) = 0'''
    params = [patient['id']]
    if dsa_class:
        q += ' AND dsa_class = ?'
        params.append(dsa_class)
    q += ' ORDER BY report_date, id'
    reports = conn.execute(q, params).fetchall()

    from collections import defaultdict
    date_count = defaultdict(int)
    for r in reports:
        date_count[r['report_date']] += 1
    date_seen = defaultdict(int)
    labels_by_rid = {}
    dates = []
    pct_by_date = {}
    for r in reports:
        d = r['report_date']
        if date_count[d] == 1:
            label = d
        else:
            suffix = chr(ord('A') + date_seen[d])
            label = f'{d} ({suffix})'
            date_seen[d] += 1
        labels_by_rid[r['id']] = label
        dates.append(label)
        pct_by_date[label] = r['pct_sa']

    if not reports:
        conn.close()
        return [], [], {}, {}

    rep_ids = [r['id'] for r in reports]
    placeholders = ','.join('?' * len(rep_ids))
    ab_rows = conn.execute(f'''
        SELECT report_id, antigen, allele, max_mfi, mean_mfi, no_of_beads
        FROM dsa_antibody_strength
        WHERE report_id IN ({placeholders})
        ORDER BY antigen
    ''', rep_ids).fetchall()
    conn.close()

    import re as _re
    parsed = {}
    for r in reports:
        spec = r['specificity'] or ''
        sa_map = {}
        bare = set()
        for mt in _re.finditer(r'([A-Za-z][A-Za-z0-9]*)\(([^)]*)\)', spec):
            s = mt.group(1).strip()
            for a in mt.group(2).split():
                a = a.strip()
                if a:
                    sa_map.setdefault(s, set()).add(a)
        no_parens = _re.sub(r'\([^)]*\)', ' ', spec)
        for tok in no_parens.split():
            tok = tok.strip()
            if '*' in tok and ':' in tok:
                bare.add(tok)
        parsed[r['id']] = {'sero_alleles': sa_map, 'bare': bare}

    allele_to_sero = {}
    for p in parsed.values():
        for s, alleles in p['sero_alleles'].items():
            for a in alleles:
                allele_to_sero[a] = s

    ab_map = {}
    for ab in ab_rows:
        ab_map[(ab['report_id'], ab['antigen'])] = dict(ab)

    all_alleles = set()
    for p in parsed.values():
        for alleles in p['sero_alleles'].values():
            all_alleles.update(alleles)
        all_alleles.update(p['bare'])

    def sk(a):
        order = {'A': 0, 'B': 1, 'C': 2, 'Cw': 2,
                 'DR': 3, 'DRB1': 3, 'DRB3': 3, 'DRB4': 3, 'DRB5': 3,
                 'DQ': 4, 'DQB1': 4, 'DQA1': 5,
                 'DP': 6, 'DPB1': 6, 'DPA1': 7}
        loc = a.split('*')[0] if '*' in a else (_re.match(r'[A-Za-z]+', a) or _re.match(r'.', a)).group()
        nums = _re.findall(r'\d+', a)
        n1 = int(nums[0]) if nums else 0
        n2 = int(nums[1]) if len(nums) > 1 else 0
        return (order.get(loc, 99), n1, n2, a)

    out = []
    for allele in sorted(all_alleles, key=sk):
        sero = allele_to_sero.get(allele)
        mfi_by_date = {}
        for r in reports:
            rid = r['id']
            lbl = labels_by_rid[rid]
            p = parsed[rid]
            present = allele in p['sero_alleles'].get(sero, set()) if sero else allele in p['bare']
            if not present:
                continue
            ab = ab_map.get((rid, sero)) if sero else None
            if not ab:
                ab = ab_map.get((rid, allele))
            if ab:
                mfi_by_date[lbl] = {
                    'max_mfi': ab['max_mfi'],
                    'mean_mfi': ab['mean_mfi'],
                    'no_of_beads': ab['no_of_beads'],
                }
        if not mfi_by_date:
            continue
        out.append({'antigen': sero if sero else allele, 'allele': allele,
                    'mfi_by_date': mfi_by_date})
    return dates, out, pct_by_date, labels_by_rid


def get_patients_with_both():
    """Patients having BOTH active PRA and DSA reports."""
    conn = get_conn()
    rows = conn.execute('''
        SELECT p.id, p.patient_name, p.chart_no,
               (SELECT COUNT(*) FROM reports WHERE patient_id=p.id AND COALESCE(is_deleted,0)=0) AS pra_count,
               (SELECT COUNT(*) FROM dsa_reports WHERE patient_id=p.id AND COALESCE(is_deleted,0)=0) AS dsa_count,
               (SELECT MAX(report_date) FROM reports WHERE patient_id=p.id AND COALESCE(is_deleted,0)=0) AS pra_latest,
               (SELECT MAX(report_date) FROM dsa_reports WHERE patient_id=p.id AND COALESCE(is_deleted,0)=0) AS dsa_latest
        FROM patients p
        ORDER BY p.patient_name
    ''').fetchall()
    conn.close()
    out = []
    for r in rows:
        d = dict(r)
        if d['pra_count'] > 0 and d['dsa_count'] > 0:
            d['latest_date'] = max(d['pra_latest'] or '', d['dsa_latest'] or '')
            out.append(d)
    out.sort(key=lambda x: x['latest_date'], reverse=True)
    return out


def get_combined_reports(chart_no):
    """Active PRA + DSA reports for a patient, interleaved by date."""
    conn = get_conn()
    pra_rows = conn.execute('''
        SELECT r.id, r.report_date, r.pra_class AS class_label, r.pra_percent AS pct,
               r.overall, r.specificity, r.comment, r.submitted_by,
               'PRA' AS source
        FROM reports r JOIN patients p ON p.id=r.patient_id
        WHERE p.chart_no=? AND COALESCE(r.is_deleted,0)=0
    ''', (chart_no,)).fetchall()
    dsa_rows = conn.execute('''
        SELECT r.id, r.report_date, r.dsa_class AS class_label, r.pct_sa AS pct,
               r.overall, r.specificity, r.comment, r.submitted_by,
               'DSA' AS source
        FROM dsa_reports r JOIN patients p ON p.id=r.patient_id
        WHERE p.chart_no=? AND COALESCE(r.is_deleted,0)=0
    ''', (chart_no,)).fetchall()
    pra_list = [{**dict(r), 'antibodies': []} for r in pra_rows]
    dsa_list = [{**dict(r), 'antibodies': []} for r in dsa_rows]
    # Pull antibodies for each
    for r in pra_list:
        ab = conn.execute('SELECT * FROM antibody_strength WHERE report_id=? ORDER BY id',
                          (r['id'],)).fetchall()
        r['antibodies'] = [dict(a) for a in ab]
    for r in dsa_list:
        ab = conn.execute('SELECT * FROM dsa_antibody_strength WHERE report_id=? ORDER BY id',
                          (r['id'],)).fetchall()
        r['antibodies'] = [dict(a) for a in ab]
    conn.close()
    all_rows = pra_list + dsa_list
    all_rows.sort(key=lambda r: (r['report_date'], r['source'], r['class_label']), reverse=True)
    return all_rows


def get_combined_mfi(chart_no, class_label='I'):
    """Combined (PRA + DSA) MFI trend for one Class.
    Returns:
      dates: sorted unique date labels from both sources
      antigens: [{allele, antigen (sero), pra_mfi: {date: max_mfi}, dsa_mfi: {date: max_mfi}}, ...]
    """
    pra_full = f'PRA Class {class_label}'
    dsa_full = f'DSA Class {class_label}'

    pra_dates, pra_ag, _, _ = get_mfi_comparison(chart_no, pra_full)
    dsa_dates, dsa_ag, _, _ = get_dsa_mfi_comparison(chart_no, dsa_full)

    combined = {}
    for ag in pra_ag:
        key = ag['allele']
        rec = combined.setdefault(key, {'allele': key, 'antigen': ag['antigen'],
                                        'pra_mfi': {}, 'dsa_mfi': {}})
        rec['antigen'] = rec.get('antigen') or ag['antigen']
        for d, v in ag['mfi_by_date'].items():
            rec['pra_mfi'][d] = v.get('max_mfi')
    for ag in dsa_ag:
        key = ag['allele']
        rec = combined.setdefault(key, {'allele': key, 'antigen': ag['antigen'],
                                        'pra_mfi': {}, 'dsa_mfi': {}})
        rec['antigen'] = rec.get('antigen') or ag['antigen']
        for d, v in ag['mfi_by_date'].items():
            rec['dsa_mfi'][d] = v.get('max_mfi')

    import re as _re
    def date_key(lbl):
        parts = lbl.split(' ')[0].split('/')
        try:
            return (int(parts[0]), int(parts[1]), int(parts[2]), lbl)
        except (IndexError, ValueError):
            return (9999, 0, 0, lbl)
    all_dates = sorted(set(pra_dates) | set(dsa_dates), key=date_key)
    return all_dates, list(combined.values())


def delete_dsa_report(report_id):
    backup_db()
    conn = get_conn()
    row = conn.execute('SELECT upload_file FROM dsa_reports WHERE id=?', (report_id,)).fetchone()
    upload_file = row['upload_file'] if row and row['upload_file'] else ''
    conn.execute("""UPDATE dsa_reports SET is_deleted=1, deleted_at=datetime('now','localtime'),
                    updated_at=datetime('now','localtime') WHERE id=?""", (report_id,))
    conn.commit()
    if upload_file:
        still_used = conn.execute(
            'SELECT COUNT(*) FROM dsa_reports WHERE upload_file=? AND id!=? AND COALESCE(is_deleted,0)=0',
            (upload_file, report_id)
        ).fetchone()[0]
        # Also check PRA reports
        still_used += conn.execute(
            'SELECT COUNT(*) FROM reports WHERE upload_file=? AND COALESCE(is_deleted,0)=0',
            (upload_file,)
        ).fetchone()[0]
        conn.close()
        if still_used == 0:
            delete_upload(upload_file)
    else:
        conn.close()
    schedule_auto_push()


def delete_report(report_id):
    """軟刪除報告（標記 is_deleted=1，不真的刪）。
    若 upload_file 不再被任何 active report 引用，同步刪除 uploads/ 下的檔案。"""
    backup_db()
    conn = get_conn()
    row = conn.execute('SELECT upload_file FROM reports WHERE id=?', (report_id,)).fetchone()
    upload_file = row['upload_file'] if row and row['upload_file'] else ''
    conn.execute("""UPDATE reports SET is_deleted=1, deleted_at=datetime('now','localtime'),
                    updated_at=datetime('now','localtime') WHERE id=?""", (report_id,))
    conn.commit()
    if upload_file:
        still_used = conn.execute(
            'SELECT COUNT(*) FROM reports WHERE upload_file=? AND id!=? AND COALESCE(is_deleted,0)=0',
            (upload_file, report_id)
        ).fetchone()[0]
        conn.close()
        if still_used == 0:
            delete_upload(upload_file)
    else:
        conn.close()
    schedule_auto_push()


UPLOAD_DIR = DB_PATH.parent / 'uploads'


def get_upload_dir():
    UPLOAD_DIR.mkdir(exist_ok=True)
    return UPLOAD_DIR


def get_storage_stats():
    """取得 uploads 資料夾使用量"""
    d = get_upload_dir()
    files = list(d.glob('*.xls')) + list(d.glob('*.xlsx')) + list(d.glob('*.csv'))
    total_size = sum(f.stat().st_size for f in files)
    return {'count': len(files), 'size_mb': round(total_size / 1024 / 1024, 2)}


def list_uploads():
    """列出 uploads/ 所有檔案。上傳時間優先取 reports.created_at（持久值），
    orphan 檔案才 fallback 到 file mtime。每筆附 source ('PRA' / 'DSA')。
    判定優先序：reports → dsa_reports → 檔名 (LS?A* / *PRA1* / *PRA2*) → 預設 PRA。"""
    d = get_upload_dir()
    files = list(d.glob('*.xls')) + list(d.glob('*.xlsx')) + list(d.glob('*.csv'))
    conn = get_conn()
    pra_rows = conn.execute("""SELECT upload_file, MAX(COALESCE(updated_at, created_at)) as ts
                               FROM reports WHERE upload_file != ''
                               GROUP BY upload_file""").fetchall()
    dsa_rows = conn.execute("""SELECT upload_file, MAX(COALESCE(updated_at, created_at)) as ts
                               FROM dsa_reports WHERE upload_file != ''
                               GROUP BY upload_file""").fetchall()
    conn.close()
    pra_times = {r['upload_file']: r['ts'] for r in pra_rows if r['upload_file']}
    dsa_times = {r['upload_file']: r['ts'] for r in dsa_rows if r['upload_file']}

    def classify(name):
        if name in pra_times: return 'PRA'
        if name in dsa_times: return 'DSA'
        u = name.upper()
        # PRA panel filenames contain "PRA" (e.g. LS1PRA021_*, *PRA1*.xls).
        # SA / DSA filenames are LS1*/LS2* without "PRA" (LS1A bead, plain LS1.xls etc).
        if 'PRA' in u: return 'PRA'
        if 'LS1' in u or 'LS2' in u: return 'DSA'
        return 'PRA'

    out = []
    for f in files:
        st = f.stat()
        ts = pra_times.get(f.name) or dsa_times.get(f.name)
        if not ts:
            ts = datetime.fromtimestamp(st.st_mtime).strftime('%Y-%m-%d %H:%M')
        else:
            ts = ts[:16]
        out.append({
            'name': f.name,
            'size_kb': round(st.st_size / 1024, 1),
            'mtime': ts,
            'source': classify(f.name),
        })
    out.sort(key=lambda x: x['mtime'], reverse=True)
    return out


def delete_upload(filename):
    """刪除 uploads/<filename>（本地 + 同步刪除 repo 對應檔案）。"""
    d = get_upload_dir()
    # 防目錄穿越
    safe_name = os.path.basename(filename)
    p = d / safe_name
    if not p.exists():
        return False
    try:
        p.unlink()
    except Exception as e:
        logger.warning(f'local delete failed: {e}')
        return False
    token = os.environ.get('HF_TOKEN', '')
    username = os.environ.get('HF_USERNAME', '')
    if token and username:
        def _go():
            try:
                from huggingface_hub import HfApi
                api = HfApi(token=token)
                api.delete_file(
                    path_in_repo=f'uploads/{safe_name}',
                    repo_id=f'{username}/pradsa',
                    repo_type='space',
                )
                logger.info(f'Deleted uploads/{safe_name} from repo')
            except Exception as e:
                logger.warning(f'repo delete failed: {e}')
        threading.Thread(target=_go, daemon=True).start()
    return True


def register_user(username, password, display_name=''):
    """註冊新使用者"""
    backup_db()
    conn = get_conn()
    existing = conn.execute('SELECT id FROM users WHERE username=? OR display_name=?',
                            (username, display_name)).fetchone()
    if existing:
        conn.close()
        return False, '此資料已經存在，請直接登入'
    conn.execute('INSERT INTO users (username, password_hash, password_plain, display_name, edit_time) VALUES (?, ?, ?, ?, datetime("now","localtime"))',
                 (username, hash_pw(password), password, display_name))
    conn.commit()
    conn.close()
    schedule_auto_push()
    return True, '註冊成功'


def check_user(username, password):
    """驗證使用者"""
    conn = get_conn()
    row = conn.execute('SELECT password_hash FROM users WHERE username=?', (username,)).fetchone()
    conn.close()
    if row and row['password_hash'] == hash_pw(password):
        return True
    return False


def seed_default_users(users_dict, admin_user=None):
    """初始化預設帳號"""
    conn = get_conn()
    for username, password in users_dict.items():
        role = 'admin' if username == admin_user else 'user'
        conn.execute('INSERT OR IGNORE INTO users (username, password_hash, password_plain, role, edit_time) VALUES (?, ?, ?, ?, datetime("now","localtime"))',
                     (username, hash_pw(password), password, role))
        if username == admin_user:
            conn.execute('UPDATE users SET role = ? WHERE username = ?', ('admin', username))
    conn.commit()
    conn.close()


def get_user_role(username):
    conn = get_conn()
    row = conn.execute('SELECT role FROM users WHERE username=?', (username,)).fetchone()
    conn.close()
    return row['role'] if row else 'user'


def get_user_display_name(username):
    conn = get_conn()
    row = conn.execute('SELECT display_name FROM users WHERE username=?', (username,)).fetchone()
    conn.close()
    return row['display_name'] if row and row['display_name'] else username


def get_all_users():
    conn = get_conn()
    rows = conn.execute('SELECT id, username, display_name, password_plain, role, created_at FROM users ORDER BY id').fetchall()
    conn.close()
    return [dict(r) for r in rows]


def delete_user(user_id):
    backup_db()
    conn = get_conn()
    conn.execute('DELETE FROM users WHERE id=?', (user_id,))
    conn.commit()
    conn.close()
    schedule_auto_push()


def update_user_role(user_id, role):
    backup_db()
    conn = get_conn()
    conn.execute('UPDATE users SET role=? WHERE id=?', (role, user_id))
    conn.execute("UPDATE users SET edit_time=datetime('now','localtime') WHERE id=?", (user_id,))
    conn.commit()
    conn.close()
    schedule_auto_push()


def update_user(user_id, display_name=None, username=None, password=None, role=None):
    backup_db()
    conn = get_conn()
    if display_name is not None:
        conn.execute('UPDATE users SET display_name=? WHERE id=?', (display_name, user_id))
    if username:
        conn.execute('UPDATE users SET username=? WHERE id=?', (username, user_id))
    if password:
        conn.execute('UPDATE users SET password_hash=?, password_plain=? WHERE id=?',
                     (hash_pw(password), password, user_id))
    if role:
        conn.execute('UPDATE users SET role=? WHERE id=?', (role, user_id))
    conn.execute("UPDATE users SET edit_time=datetime('now','localtime') WHERE id=?", (user_id,))
    conn.commit()
    conn.close()
    schedule_auto_push()


init_db()
# 容器啟動時把 repo 上的 uploads/ 拉下來，確保 list_uploads() 能看到其他 session 上傳的檔案
try:
    pull_uploads_from_repo()
except Exception as _e:
    logger.warning(f'startup pull_uploads skipped: {_e}')