"""
src/core/contracts_db.py
------------------------
SQLite + FTS5 database for contracts management.
DB auto-created at: <project_root>/contracts/contracts.db

Tables:
  contracts_sale     — hợp đồng bán hàng
  contracts_purchase — hợp đồng mua hàng
    contracts_other    — hợp đồng loại khác
  contracts_sale_fts     (FTS5 mirror)
  contracts_purchase_fts (FTS5 mirror)
    contracts_other_fts    (FTS5 mirror)
"""
import os
import re
import json
import sqlite3
import uuid
from datetime import datetime

from core.utils import get_company_root_path
# ── Paths ────────────────────────────────────────────────────────────────────
_ROOT = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
DB_DIR  = get_company_root_path("contracts")
DB_PATH = os.path.join(DB_DIR, "contracts.db")

CONTRACT_TABLES = ("contracts_sale", "contracts_purchase", "contracts_other")
CONTRACT_OPTIONAL_COLUMNS = {
    "amount_total": "REAL DEFAULT 0",
}


def _ensure_contract_directories() -> None:
    """Create minimum local folder skeleton for contract storage."""
    for table in CONTRACT_TABLES:
        base_data = get_company_root_path(os.path.join("contracts", table, "data"))
        os.makedirs(base_data, exist_ok=True)

        # Prepare today's buckets so import/sign/sync can write immediately.
        today = datetime.now().strftime("%Y-%m-%d")
        for state in ("draft", "pending", "completed"):
            os.makedirs(os.path.join(base_data, today, state), exist_ok=True)



# ── Quy tắc nghiệp vụ: Vai trò của công ty trong từng loại HĐ ────────────────
# NGÔ GIA PHÁT là Bên B trong HĐ bán hàng, Bên A trong HĐ mua hàng
OWN_PARTY = {
    "contracts_sale":     "b",   # NGP = Bên B (bán hàng/dịch vụ)
    "contracts_purchase": "a",   # NGP = Bên A (mua hàng)
    "contracts_other":    "b",   # Mặc định bên công ty ký vai trò Bên B
}


def own_sign_sequence(table: str, existing_seq: str = "") -> str:
    """
    Trả về sign_sequence mới khi công ty tự ký.

    Ví dụ:
      HĐ bán hàng  + chưa ai ký → 'b'         → id_file_bky.pdf
      HĐ mua hàng  + chưa ai ký → 'a'         → id_file_aky.pdf
      HĐ bán hàng  + khách đã ký trước ('a')  → 'a,b'  → id_file_aky_bky.pdf
      HĐ mua hàng  + đối tác đã ký trước ('b') → 'b,a' → id_file_bky_aky.pdf
    """
    party = OWN_PARTY.get(table, "b")
    if not existing_seq:
        return party                       # mình ký đầu tiên
    parts = [p.strip() for p in existing_seq.split(",") if p.strip()]
    if party not in parts:
        parts.append(party)               # thêm mình vào cuối chuỗi ký
    return ",".join(parts)



# ── Schema ───────────────────────────────────────────────────────────────────
_DDL_TABLE = """
CREATE TABLE IF NOT EXISTS {table} (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    name_file       TEXT DEFAULT '',    -- Tên gốc file (không suffix ký): 20260322-HDDTNGP-DCDR
    drive_id        TEXT DEFAULT '',
    name            TEXT DEFAULT '',
    party_a         TEXT DEFAULT '',
    party_b         TEXT DEFAULT '',
    mst_a           TEXT DEFAULT '',
    mst_b           TEXT DEFAULT '',
    date            TEXT DEFAULT '',
    path            TEXT DEFAULT '' UNIQUE,  -- Đường dẫn file (unique mỗi file)
    sign_sequence   TEXT DEFAULT '',    -- '' | 'b' | 'a' | 'b,a' | 'a,b'
    drive_link      TEXT DEFAULT '',
    note            TEXT DEFAULT '',
    amount_total    REAL DEFAULT 0,
    modified_at     TEXT DEFAULT ''
);
"""

_DDL_FTS = """
CREATE VIRTUAL TABLE IF NOT EXISTS {fts} USING fts5(
    name_file,
    name,
    party_a,
    party_b,
    mst_a,
    mst_b,
    sign_sequence,
    note,
    content='{table}',
    content_rowid='id'
);
"""

# Keep FTS in sync via triggers
_DDL_TRIGGERS = """
-- Insert trigger
CREATE TRIGGER IF NOT EXISTS {table}_ai AFTER INSERT ON {table} BEGIN
    INSERT INTO {fts}(rowid, name_file, name, party_a, party_b, mst_a, mst_b, sign_sequence, note)
    VALUES (new.id, new.name_file, new.name, new.party_a, new.party_b, new.mst_a, new.mst_b, new.sign_sequence, new.note);
END;

-- Delete trigger
CREATE TRIGGER IF NOT EXISTS {table}_ad AFTER DELETE ON {table} BEGIN
    INSERT INTO {fts}({fts}, rowid, name_file, name, party_a, party_b, mst_a, mst_b, sign_sequence, note)
    VALUES ('delete', old.id, old.name_file, old.name, old.party_a, old.party_b, old.mst_a, old.mst_b, old.sign_sequence, old.note);
END;

-- Update trigger
CREATE TRIGGER IF NOT EXISTS {table}_au AFTER UPDATE ON {table} BEGIN
    INSERT INTO {fts}({fts}, rowid, name_file, name, party_a, party_b, mst_a, mst_b, sign_sequence, note)
    VALUES ('delete', old.id, old.name_file, old.name, old.party_a, old.party_b, old.mst_a, old.mst_b, old.sign_sequence, old.note);
    INSERT INTO {fts}(rowid, name_file, name, party_a, party_b, mst_a, mst_b, sign_sequence, note)
    VALUES (new.id, new.name_file, new.name, new.party_a, new.party_b, new.mst_a, new.mst_b, new.sign_sequence, new.note);
END;
"""


def _ensure_optional_columns(conn: sqlite3.Connection, table: str) -> None:
    cols = {row[1] for row in conn.execute(f"PRAGMA table_info({table})")}
    for name, ddl in CONTRACT_OPTIONAL_COLUMNS.items():
        if name not in cols:
            conn.execute(f"ALTER TABLE {table} ADD COLUMN {name} {ddl}")
    conn.commit()


# ── Connection ────────────────────────────────────────────────────────────────
def _ensure_schema(conn: sqlite3.Connection) -> None:
    """Ensure contract tables + signature cache exist on the current connection."""
    _ensure_contract_directories()
    for table in CONTRACT_TABLES:
        _init_table(conn, table)
        _ensure_optional_columns(conn, table)
    conn.execute("""
        CREATE TABLE IF NOT EXISTS signature_cache (
            path TEXT PRIMARY KEY,
            mtime REAL,
            is_signed INTEGER,
            is_valid INTEGER,
            mst_list TEXT,
            checked_at TEXT
        )
    """)
    conn.commit()


def get_connection(ensure_schema: bool = True) -> sqlite3.Connection:
    """Return a connection to contracts.db, creating file/schema if needed."""
    db_dir = get_company_root_path("contracts")
    db_path = os.path.join(db_dir, "contracts.db")
    os.makedirs(db_dir, exist_ok=True)
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")   # concurrent reads + one writer
    conn.execute("PRAGMA synchronous=NORMAL")

    if ensure_schema:
        # Self-heal: if contracts.db was deleted/corrupted schema, recreate required tables.
        has_sale = conn.execute(
            "SELECT 1 FROM sqlite_master WHERE type='table' AND name='contracts_sale'"
        ).fetchone()
        has_cache = conn.execute(
            "SELECT 1 FROM sqlite_master WHERE type='table' AND name='signature_cache'"
        ).fetchone()
        if not has_sale or not has_cache:
            _ensure_schema(conn)

    return conn


def _migrate_if_needed(conn: sqlite3.Connection, table: str):
    """
    Kiểm tra schema hiện tại, nếu không khớp → drop & recreate.
    Schema mới dùng id INTEGER PRIMARY KEY AUTOINCREMENT + name_file.
    """
    exists = conn.execute(
        "SELECT name FROM sqlite_master WHERE type='table' AND name=?", (table,)
    ).fetchone()
    if not exists:
        return  # chưa tồn tại → _init_table sẽ tạo mới

    cols = {row[1] for row in conn.execute(f"PRAGMA table_info({table})")}

    # Schema mới phải có name_file và id là AUTOINCREMENT
    if "name_file" in cols:
        return  # OK, không cần migrate

    # Schema cũ → drop & recreate
    print(f"[contracts_db] Schema cũ → drop & recreate '{table}'.")
    fts = f"{table}_fts"
    conn.executescript(f"""
        DROP TRIGGER IF EXISTS {table}_ai;
        DROP TRIGGER IF EXISTS {table}_ad;
        DROP TRIGGER IF EXISTS {table}_au;
        DROP TABLE IF EXISTS {fts};
        DROP TABLE IF EXISTS {table};
    """)
    conn.commit()


def _init_table(conn: sqlite3.Connection, table: str):
    """Create main table + FTS5 virtual table + triggers for given table."""
    _migrate_if_needed(conn, table)   # migrate schema cũ nếu cần
    fts = f"{table}_fts"
    conn.executescript(
        _DDL_TABLE.format(table=table)
        + _DDL_FTS.format(fts=fts, table=table)
        + _DDL_TRIGGERS.format(table=table, fts=fts)
    )
    conn.commit()



def init_db():
    """Initialise both tables. Safe to call multiple times (idempotent)."""
    with get_connection(ensure_schema=False) as conn:
        _ensure_schema(conn)
    return DB_PATH



# ── CRUD ──────────────────────────────────────────────────────────────────────
_FIELDS = ["name_file", "drive_id", "name", "party_a", "party_b", "mst_a", "mst_b",
           "date", "path", "sign_sequence", "drive_link", "note", "amount_total", "modified_at"]
_SIDECAR_SUFFIX = ".contract.json"


def _row_to_dict(row) -> dict:
    return dict(row) if row else {}


def _sidecar_path_for_file(file_path: str, name_file: str = "") -> str:
    abs_path = os.path.abspath(file_path)
    contract_id = (name_file or "").strip()
    if not contract_id:
        stem = os.path.splitext(os.path.basename(abs_path))[0]
        contract_id = _extract_id_file(stem)
    return os.path.join(os.path.dirname(abs_path), f"{contract_id}{_SIDECAR_SUFFIX}")


def write_contract_sidecar(file_path: str, data: dict) -> str:
    """Persist contract metadata next to the generated/imported file for later recovery."""
    payload = dict(data or {})
    sidecar_path = _sidecar_path_for_file(file_path, str(payload.get("name_file") or "").strip())
    contract_id = str(payload.get("name_file") or "").strip() or _extract_id_file(os.path.splitext(os.path.basename(file_path))[0])
    payload["name_file"] = contract_id

    # Keep only the fields needed to rebuild contract metadata for later revenue import.
    filtered: dict[str, object] = {"name_file": contract_id}
    for key in ("name", "party_a", "party_b", "mst_a", "mst_b", "date", "note", "amount_total", "path"):
        if key in payload:
            filtered[key] = payload.get(key)

    try:
        filtered["amount_total"] = float(filtered.get("amount_total") or 0)
    except (TypeError, ValueError):
        filtered["amount_total"] = 0.0

    os.makedirs(os.path.dirname(sidecar_path), exist_ok=True)
    with open(sidecar_path, "w", encoding="utf-8") as file_obj:
        json.dump(filtered, file_obj, ensure_ascii=False, indent=2)
    return sidecar_path


def _load_contract_sidecar(base_dir: str, date_dir: str, folder_path: str, name_file: str, stem: str = "") -> dict:
    candidate_dirs = [folder_path]
    date_root = os.path.join(base_dir, date_dir)
    for state in ("draft", "pending", "completed"):
        candidate_dirs.append(os.path.join(date_root, state))

    candidate_names = [f"{name_file}{_SIDECAR_SUFFIX}"]
    if stem and stem != name_file:
        candidate_names.append(f"{stem}{_SIDECAR_SUFFIX}")

    seen_paths: set[str] = set()
    for directory in candidate_dirs:
        if not os.path.isdir(directory):
            continue
        for file_name in candidate_names:
            candidate = os.path.join(directory, file_name)
            normalized = os.path.normpath(candidate)
            if normalized in seen_paths or not os.path.exists(candidate):
                continue
            seen_paths.add(normalized)
            try:
                with open(candidate, "r", encoding="utf-8") as file_obj:
                    data = json.load(file_obj)
                if isinstance(data, dict):
                    return data
            except Exception:
                continue
    return {}


# ── sign_sequence helpers ─────────────────────────────────────────────────────
def status_from_sequence(seq: str) -> tuple[str, str]:
    """
    Derive party_a_status, party_b_status từ sign_sequence.
      seq = '' → draft/draft
      seq = 'b' → pending/signed
      seq = 'a' → signed/pending
      seq = 'b,a' hoặc 'a,b' → signed/signed
      seq = 'a,b,c' → signed/signed  (bên C không ảnh hưởng trạng thái a/b)
    """
    parts = [p.strip() for p in seq.split(",") if p.strip()] if seq else []
    a = "signed" if "a" in parts else ("pending" if parts else "draft")
    b = "signed" if "b" in parts else ("pending" if parts else "draft")
    return a, b   # party_a_status, party_b_status


def filename_from_sequence(id_file: str, seq: str) -> str:
    """
    Sinh tên file PDF từ id_file + sign_sequence.
      ''      → id_file.pdf
      'b'     → id_file_bky.pdf
      'a'     → id_file_aky.pdf
      'b,a'   → id_file_bky_aky.pdf
      'a,b'   → id_file_aky_bky.pdf
      'a,b,c' → id_file_aky_bky_cky.pdf
    """
    parts = [p.strip() for p in seq.split(",") if p.strip()] if seq else []
    suffix = "".join(f"_{c}ky" for c in parts)
    return f"{id_file}{suffix}.pdf"


def upsert_contract(table: str, data: dict) -> None:
    """Insert or replace a contract row. name_file is the UNIQUE key along with path."""
    if not data.get("name_file"):
        raise ValueError("name_file is required")
    data.setdefault("modified_at", datetime.now().isoformat(timespec="seconds"))
    cols = [f for f in _FIELDS if f in data]
    placeholders = ", ".join([f":{c}" for c in cols])
    col_names = ", ".join(cols)
    sql = f"INSERT OR REPLACE INTO {table} ({col_names}) VALUES ({placeholders})"
    with get_connection() as conn:
        conn.execute(sql, data)
        conn.commit()


def upsert_many(table: str, rows: list[dict]) -> None:
    """Bulk upsert list of contract dicts. name_file is required."""
    if not rows:
        return
    now = datetime.now().isoformat(timespec="seconds")
    with get_connection() as conn:
        for data in rows:
            data.setdefault("modified_at", now)
            # Alias party_B → party_b (normalise key)
            if "party_B" in data:
                data["party_b"] = data.pop("party_B")
            cols = [f for f in _FIELDS if f in data]
            placeholders = ", ".join([f":{c}" for c in cols])
            col_names = ", ".join(cols)
            sql = f"INSERT OR REPLACE INTO {table} ({col_names}) VALUES ({placeholders})"
            conn.execute(sql, data)
        conn.commit()


def count_contracts_in_month(year: int | None = None, month: int | None = None) -> int:
    """Đếm tổng số HĐ đã lưu trong tháng hiện tại hoặc tháng chỉ định.

    Dùng modified_at vì trường này luôn được set khi upsert từ luồng generate/scan.
    """
    now = datetime.now()
    year = year or now.year
    month = month or now.month
    month_key = f"{year:04d}-{month:02d}"

    with get_connection() as conn:
        total = 0
        for table in ("contracts_sale", "contracts_purchase", "contracts_other"):
            row = conn.execute(
                f"SELECT COUNT(*) FROM {table} WHERE substr(COALESCE(modified_at, ''), 1, 7) = ?",
                (month_key,),
            ).fetchone()
            total += int(row[0] if row else 0)
        return total


def count_contracts_total() -> int:
    """Đếm tổng số HĐ đã lưu trên tất cả nhóm hợp đồng."""
    with get_connection() as conn:
        total = 0
        for table in ("contracts_sale", "contracts_purchase", "contracts_other"):
            row = conn.execute(f"SELECT COUNT(*) FROM {table}").fetchone()
            total += int(row[0] if row else 0)
        return total


def delete_contract(table: str, name_file: str) -> None:
    with get_connection() as conn:
        conn.execute(f"DELETE FROM {table} WHERE name_file = ?", (name_file,))
        conn.commit()


def get_contract(table: str, name_file: str) -> dict:
    with get_connection() as conn:
        row = conn.execute(f"SELECT * FROM {table} WHERE name_file = ?", (name_file,)).fetchone()
    return _row_to_dict(row)


def get_all(table: str, status: str | None = None, year: str | None = None) -> list[dict]:
    """Return all contracts, optionally filtered by status and/or year."""
    clauses = []
    params: list = []
    if year:
        clauses.append("date LIKE ?")
        params.append(f"{year}-%")
    where = ("WHERE " + " AND ".join(clauses)) if clauses else ""
    sql = f"SELECT * FROM {table} {where} ORDER BY date DESC"
    with get_connection() as conn:
        rows = conn.execute(sql, params).fetchall()

    items = [_row_to_dict(r) for r in rows]
    if status and status != "all":
        filtered: list[dict] = []
        for item in items:
            party_a_status, party_b_status = status_from_sequence(item.get("sign_sequence", ""))
            if status in (party_a_status, party_b_status):
                filtered.append(item)
        return filtered
    return items


def search(table: str, query: str, year: str | None = None) -> list[dict]:
    """Full-text search across id, name, party_a/b, mst_a/b, note."""
    if not query.strip():
        return get_all(table, year=year)
    fts = f"{table}_fts"
    # Thêm * để match prefix (gõ 1 phần cũng ra)
    fts_query = " OR ".join(f'"{w}"*' for w in query.split() if w)
    sql = f"""
        SELECT t.*
        FROM {table} t
        JOIN {fts} f ON t.rowid = f.rowid
        WHERE {fts} MATCH ?
    """
    params: list = [fts_query]
    if year:
        sql += " AND t.date LIKE ?"
        params.append(f"{year}-%")
    sql += " ORDER BY t.date DESC"
    with get_connection() as conn:
        try:
            rows = conn.execute(sql, params).fetchall()
        except sqlite3.OperationalError:
            # Fallback nếu query FTS syntax lỗi
            rows = []
    return [_row_to_dict(r) for r in rows]


def update_field(table: str, name_file: str, **kwargs) -> None:
    """Update specific fields for a single contract by name_file."""
    kwargs["modified_at"] = datetime.now().isoformat(timespec="seconds")
    sets = ", ".join([f"{k} = :{k}" for k in kwargs])
    kwargs["_name_file"] = name_file
    sql = f"UPDATE {table} SET {sets} WHERE name_file = :_name_file"
    with get_connection() as conn:
        conn.execute(sql, kwargs)
        conn.commit()


def upsert_custom_contract(category: str, contract_data: dict) -> None:
    """
    Helper ghi hợp đồng tạo thủ công. Category in ('sale', 'purchase', 'other').
    """
    category = (category or "").strip().lower()
    if category == "sale":
        table = "contracts_sale"
    elif category == "purchase":
        table = "contracts_purchase"
    else:
        table = "contracts_other"
    upsert_contract(table, contract_data)





# ── File Scanner ──────────────────────────────────────────────────────────────
# ─── Regex nhận diện tất cả biến thể tên file suffix ký số ───────────────
# Mỗi bên (A hoặc B) có thể xuất hiện dưới nhiều dạng:
#   _aky / _bky  |  _asign / _bsign  |  _bena / _benb
#   ky_a / ky_b  |  _sign_a / _sign_b  |  _a_signed / _b_signed
#   ... và tổ hợp theo bất kỳ thứ tự nào
_SIDE_A = r'(?:a(?:ky|sign|_?signed?)|ky_?a|sign_?a|ben_?a)'
_SIDE_B = r'(?:b(?:ky|sign|_?signed?)|ky_?b|sign_?b|ben_?b)'

# Kết hợp A+B hoặc B+A, có thể ngăn bởi _ hoặc không
_BOTH_AB = rf'(?:{_SIDE_A}[_ ]{_SIDE_B}|{_SIDE_B}[_ ]{_SIDE_A})'

# Regex tổng — loại bỏ suffix từ cuối tên file (case-insensitive)
_SIGN_SUFFIX_RE = re.compile(
    rf'(?i)[_ ]({_BOTH_AB}|{_SIDE_A}|{_SIDE_B}|d?a?ky|signed?|sign)([_ ].*)?$'
)

def _parse_sign_suffix(stem: str):
    """
    Trả về (clean_stem, parts) với parts là list chứa 'a', 'b' hoặc rỗng.
    Nhận diện mọi biến thể suffix chữ ký phổ biến.
    """
    s = stem
    m = _SIGN_SUFFIX_RE.search(s)
    if not m:
        return s, []
    clean = s[:m.start()]
    matched = m.group(0).lower()
    parts = []
    pos = []
    ma = re.search(_SIDE_A, matched, re.IGNORECASE)
    mb = re.search(_SIDE_B, matched, re.IGNORECASE)
    if ma:
        pos.append((ma.start(), "a"))
    if mb:
        pos.append((mb.start(), "b"))
    if pos:
        pos.sort(key=lambda x: x[0])
        for _, p in pos:
            if p not in parts:
                parts.append(p)
    return clean, parts


def _infer_status_from_file(stem: str, ext: str, folder: str) -> dict:
    """
    Trả về sign_sequence từ tên file + thư mục.
    Nhận diện tất cả biến thể suffix chữ ký:
      _aky/_bky, _asign/_bsign, _bena/_benb, ky_a/ky_b, v.v
    """
    folder_name = os.path.basename(folder).lower()
    _, parts = _parse_sign_suffix(stem)

    # DOCX hoặc thư mục draft → luôn là nháp
    if ext.lower() == ".docx" or folder_name == "draft":
        return {"sign_sequence": ""}

    if parts:
        return {"sign_sequence": ",".join(parts)}

    if folder_name == "completed":
        return {"sign_sequence": "b,a"}   # mặc định completed → cả hai
    return {"sign_sequence": ""}



def _extract_id_file(stem: str) -> str:
    """
    Bóc id_file từ tên file — loại bỏ mọi suffix ký số đã biết.
    Ví dụ: 'HD-001_aky_bky' → 'HD-001'
            'HD-001_bena_benb' → 'HD-001'
    """
    clean, _ = _parse_sign_suffix(stem)
    return clean if clean else stem






def scan_contract_files(category: str = "sale", full_refresh: bool = True) -> dict:
    """
    Quét toàn bộ thư mục contracts_sale / contracts_purchase,
    tự động nhận diện trạng thái từ tên file và thư mục,
    sau đó upsert/cập nhật DB.

    full_refresh=True (mặc định): xóa toàn bộ data cũ trước khi scan
                                  → không giữ lại record không còn file.
    full_refresh=False:           chỉ upsert, giữ nguyên record cũ.

    Trả về: {"added": int, "updated": int, "total": int}
    """
    init_db()
    category = (category or "").strip().lower()
    if category == "sale":
        table = "contracts_sale"
    elif category == "purchase":
        table = "contracts_purchase"
    else:
        table = "contracts_other"
    base_dir  = get_company_root_path(os.path.join("contracts", table, "data"))

    if not os.path.isdir(base_dir):
        return {"added": 0, "updated": 0, "total": 0}

    counts = {"added": 0, "updated": 0, "total": 0}
    now    = datetime.now().isoformat(timespec="seconds")

    with get_connection() as conn:

        # full_refresh: xóa toàn bộ data cũ → scan lại từ đầu
        if full_refresh:
            pass # Không DELETE data nữa vì SQLite giờ là source of truth chứa metadata (partner, mst, amount)

        # Duyệt: contracts_sale/data/<date>/<pending|completed>/

        for date_dir in sorted(os.listdir(base_dir)):
            date_path = os.path.join(base_dir, date_dir)
            if not os.path.isdir(date_path):
                continue

            for folder_name in ("draft", "pending", "completed"):
                folder_path = os.path.join(date_path, folder_name)
                if not os.path.isdir(folder_path):
                    continue

                for fname in os.listdir(folder_path):
                    fpath = os.path.join(folder_path, fname)
                    if not os.path.isfile(fpath):
                        continue

                    stem, ext = os.path.splitext(fname)
                    ext = ext.lower()
                    if ext not in (".pdf", ".docx"):
                        continue

                    name_file = _extract_id_file(stem)   # tên gốc bỏ suffix ký
                    status    = _infer_status_from_file(stem, ext, folder_path)
                    seq       = status.get("sign_sequence", "")
                    sidecar_data = _load_contract_sidecar(base_dir, date_dir, folder_path, name_file, stem)
                    
                    # Match theo name_file (để lấy MST nếu có)
                    existing = conn.execute(
                        f"SELECT * FROM {table} WHERE name_file = ?",
                        (name_file,)
                    ).fetchone()
                    
                    m_a = existing["mst_a"].strip().lower() if existing and existing["mst_a"] else ""
                    m_b = existing["mst_b"].strip().lower() if existing and existing["mst_b"] else ""
                    if name_file == "20260327-HDDTNGP-VAOW":
                        print(f"[{name_file}] Before scan: m_a={m_a}, m_b={m_b}, existing_seq={seq}")

                    
                    # Xác minh chữ ký số nếu là PDF
                    if ext == ".pdf":
                        try:
                            file_mtime = os.path.getmtime(fpath)
                            cache_row = conn.execute("SELECT mtime, is_signed, mst_list FROM signature_cache WHERE path = ?", (fpath,)).fetchone()
                            sig_msts = []
                            
                            if cache_row and abs(cache_row["mtime"] - file_mtime) < 1.0:
                                if cache_row["is_signed"]:
                                    sig_msts = cache_row["mst_list"].split(",") if cache_row["mst_list"] else []
                            else:
                                from core.pdf_signature_verifier import verify_pdf_signature
                                sig_info = verify_pdf_signature(fpath)
                                is_signed = 1 if sig_info.get("is_signed") else 0
                                is_valid = 1 if sig_info.get("is_valid") else 0
                                sig_msts = [s["mst"] for s in sig_info.get("signatures", []) if s.get("mst")]
                                mst_str = ",".join(sig_msts)
                                
                                conn.execute(
                                    "INSERT OR REPLACE INTO signature_cache (path, mtime, is_signed, is_valid, mst_list, checked_at) VALUES (?, ?, ?, ?, ?, ?)",
                                    (fpath, file_mtime, is_signed, is_valid, mst_str, now)
                                )
                                conn.commit()
                            
                            # Auto-fill missing MSTs and override sequence
                            if sig_msts:
                                host_mst = ""
                                try:
                                    import json
                                    with open(os.path.join(_ROOT, "settings.json"), "r", encoding="utf-8") as fs:
                                        host_mst = json.load(fs).get("company", {}).get("tax_id", "").strip().lower()
                                except:
                                    pass

                                parts = [p.strip() for p in seq.split(",") if p.strip()]
                                msts_clean = [m.strip().lower() for m in sig_msts]
                                
                                if existing:
                                    update_m_a, update_m_b = None, None
                                    
                                    for m in msts_clean:
                                        # Đã gán đúng slot rồi → bỏ qua
                                        if m == m_a and category in ("sale",) and "b" not in parts:
                                            pass  # m_a đã khớp, tiếp tục kiểm tra m_b
                                        if m == m_a and m == m_b:
                                            continue  # MST đã gán cả 2 slot, skip

                                        is_host = host_mst and m.replace("-","") == host_mst.replace("-","")

                                        if category == "sale":
                                            # Bán hàng: host = Bên B, khách = Bên A
                                            if is_host:
                                                if m != m_b and not update_m_b:
                                                    update_m_b = m; m_b = m
                                            else:
                                                if m != m_a and not update_m_a:
                                                    update_m_a = m; m_a = m
                                        elif category == "purchase":
                                            # Mua hàng: host = Bên A, khách = Bên B
                                            if is_host:
                                                if m != m_a and not update_m_a:
                                                    update_m_a = m; m_a = m
                                            else:
                                                if m != m_b and not update_m_b:
                                                    update_m_b = m; m_b = m
                                        else:
                                            # Loại khác: ưu tiên gán host vào mst_b, còn lại vào mst_a
                                            if is_host:
                                                if m != m_b and not update_m_b:
                                                    update_m_b = m; m_b = m
                                            else:
                                                if m != m_a and not update_m_a:
                                                    update_m_a = m; m_a = m


                                    if update_m_a or update_m_b:
                                        set_cs = []
                                        params = []
                                        if update_m_a:
                                            set_cs.append("mst_a = ?")
                                            params.append(update_m_a)
                                        if update_m_b:
                                            set_cs.append("mst_b = ?")
                                            params.append(update_m_b)
                                        params.append(existing["id"])
                                        conn.execute(f"UPDATE {table} SET {', '.join(set_cs)} WHERE id = ?", params)
                                        conn.commit()

                                # Chỉ override seq bằng MST signature nếu tên file KHÔNG có suffix rõ ràng.
                                # Nếu đã có suffix (_bky, _aky...) → tin vào suffix, không cho signature override.
                                _, suffix_parts = _parse_sign_suffix(stem)
                                seq_has_suffix = bool(suffix_parts)

                                if not seq_has_suffix:
                                    if m_a and (m_a in msts_clean) and ("a" not in parts):
                                        parts.append("a")
                                    if m_b and (m_b in msts_clean) and ("b" not in parts):
                                        parts.append("b")
                                    seq = ",".join(parts)
                                
                        except Exception as e:
                            print(f"[scan] Lỗi kiểm tra chữ ký {fpath}: {e}")

                    rel_path  = os.path.relpath(fpath, get_company_root_path("")).replace("\\", "/")

                    if existing:
                        update_values: dict[str, object] = {
                            "sign_sequence": seq,
                            "path": rel_path,
                            "modified_at": now,
                            "name_file": name_file,
                        }

                        for field in ("name", "party_a", "party_b", "mst_a", "mst_b", "date", "note"):
                            side_value = sidecar_data.get(field)
                            if side_value in (None, ""):
                                continue
                            current_value = str(existing[field] or "").strip()
                            if not current_value:
                                update_values[field] = side_value

                        try:
                            current_amount = float(existing["amount_total"] or 0)
                        except (TypeError, ValueError):
                            current_amount = 0.0
                        try:
                            side_amount = float(sidecar_data.get("amount_total") or 0)
                        except (TypeError, ValueError):
                            side_amount = 0.0
                        if side_amount > 0 and current_amount <= 0:
                            update_values["amount_total"] = side_amount

                        assignments = ", ".join(f"{key} = :{key}" for key in update_values if key != "name_file")
                        conn.execute(
                            f"UPDATE {table} SET {assignments} WHERE name_file = :name_file",
                            update_values,
                        )
                        counts["updated"] += 1
                    else:
                        new_row = {
                            "name_file":     name_file,
                            "date":          str(sidecar_data.get("date") or date_dir),
                            "path":          rel_path,
                            "sign_sequence": seq,
                            "modified_at":   now,
                        }
                        for field in ("name", "party_a", "party_b", "mst_a", "mst_b", "note"):
                            side_value = sidecar_data.get(field)
                            if side_value not in (None, ""):
                                new_row[field] = side_value
                        try:
                            side_amount = float(sidecar_data.get("amount_total") or 0)
                        except (TypeError, ValueError):
                            side_amount = 0.0
                        if side_amount > 0:
                            new_row["amount_total"] = side_amount
                        cols_str = ", ".join(new_row.keys())
                        placeholders = ", ".join([f":{k}" for k in new_row.keys()])
                        conn.execute(
                            f"INSERT INTO {table} ({cols_str}) VALUES ({placeholders})",
                            new_row,
                        )
                        counts["added"] += 1

                    counts["total"] += 1


        conn.commit()

    return counts


def move_to_completed(
    category: str,
    id_file: str,
    sign_sequence: str,         # 'b' | 'a' | 'b,a' | 'a,b'
) -> str | None:
    """
    Di chuyển file PDF từ pending → completed với tên đúng quy tắc.
    Tên file điếu khiển bởi sign_sequence:
      'b'   → id_file_bky.pdf
      'a'   → id_file_aky.pdf
      'b,a' → id_file_bky_aky.pdf
      'a,b' → id_file_aky_bky.pdf
    Trả về đường dẫn tương đối mới, hoặc None nếu không tìm thấy file.
    """
    category = (category or "").strip().lower()
    if category == "sale":
        table = "contracts_sale"
    elif category == "purchase":
        table = "contracts_purchase"
    else:
        table = "contracts_other"
    base_dir = get_company_root_path(os.path.join("contracts", table, "data"))
    new_name = filename_from_sequence(id_file, sign_sequence)

    # Tìm file gốc trong pending
    src_path = None
    dest_dir = None
    for date_dir in os.listdir(base_dir):
        pending = os.path.join(base_dir, date_dir, "pending")
        for fname in (f"{id_file}.pdf", f"{id_file}.docx"):
            candidate = os.path.join(pending, fname)
            if os.path.isfile(candidate):
                src_path = candidate
                dest_dir = os.path.join(base_dir, date_dir, "completed")
                break
        if src_path:
            break

    if not src_path:
        return None

    os.makedirs(dest_dir, exist_ok=True)
    dest_path = os.path.join(dest_dir, new_name)
    import shutil
    shutil.move(src_path, dest_path)

    rel_path = os.path.relpath(dest_path, get_company_root_path("")).replace("\\", "/")

    # Cập nhật DB
    with get_connection() as conn:
        conn.execute(
            f"UPDATE {table} SET path = ?, sign_sequence = ?, modified_at = ? WHERE id_file = ?",
            (rel_path, sign_sequence, datetime.now().isoformat(timespec="seconds"), id_file),
        )
        conn.commit()

    return rel_path



