"""
One-time import script: Parse office addresses and legal entities from a .docx file.

Usage:
    cd server && source .venv/bin/activate
    python scripts/import_company_directory.py /path/to/公司办公室地址及主体信息.docx

This script:
  1. Reads the specified .docx file using python-docx
  2. Parses office addresses and legal entity data from document paragraphs
  3. Upserts into database (idempotent — safe to re-run)
  4. Matches admin names against identity_persons
  5. Links offices to legal entities via legal_entity_id
  6. Reports unmatched admin names for manual binding
"""
import asyncio
import os
import re
import sys

sys.path.insert(0, os.path.join(os.path.dirname(__file__), ".."))

from uuid import UUID

from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import sessionmaker

# Import all models to ensure mapper relationships resolve
from app.auth.models import AuthUser, AuthUserRole  # noqa: F401
from app.core.config import settings
from app.domains.asset.models import AssetLegalEntity, AssetOfficeAddress
from app.identity.person_models import IdentityPerson

# ─── Explicit office → legal entity mapping ───────────────────────────────
# Docx doesn't have a clean 1:1 mapping for domestic offices, so we use an
# explicit mapping table. This was reviewed with the product architect.
OFFICE_ENTITY_MAP: dict[str, str] = {
    # Overseas offices: mapped from docx (entity name appears above address)
    "Sunnyvale": "PINGCAP (US), INC.",
    "Singapore": "PINGCAP PTE LTD",
    "Tokyo": "PingCAP株式会社",
    "Kuala Lumpur": "PINGCAP SDN. BHD.",
    # Domestic offices: all map to the main operating entity
    "北京": "平凯星辰（北京）科技有限公司",
    "上海": "平凯星辰（北京）科技有限公司",
    "杭州": "平凯星辰（北京）科技有限公司",
    "成都": "平凯星辰（北京）科技有限公司",
    "深圳": "平凯星辰（北京）科技有限公司",
    "广州": "平凯星辰（北京）科技有限公司",
}


def _is_company_name(text: str) -> bool:
    """Detect if a line is a company/entity name rather than an address."""
    company_patterns = [
        r"(?i)pingcap",
        r"(?i)\bINC\.?\b",
        r"(?i)\bPTE\b",
        r"(?i)\bLTD\b",
        r"(?i)\bSDN\b",
        r"(?i)\bBHD\b",
        r"(?i)\bK\.K\b",
        r"株式会社",
    ]
    return any(re.search(p, text) for p in company_patterns)


def parse_docx(filepath: str) -> tuple[list[dict], list[dict]]:
    """Parse office addresses and legal entities from a .docx file."""
    try:
        from docx import Document
    except ImportError:
        print("ERROR: python-docx is required. Install with: pip install python-docx")
        sys.exit(1)

    doc = Document(filepath)
    paragraphs = [p.text.strip() for p in doc.paragraphs if p.text.strip()]

    offices: list[dict] = []
    entities: list[dict] = []

    i = 0
    section = None  # "offices_china", "offices_global", "entities"

    while i < len(paragraphs):
        line = paragraphs[i]

        # Detect section headers
        if "中国大陆地区" in line:
            section = "offices_china"
            i += 1
            continue
        if line.startswith("全球"):
            section = "offices_global"
            i += 1
            continue
        if "公司各主体信息" in line:
            section = "entities"
            i += 1
            continue

        # ── Parse offices ──
        if section in ("offices_china", "offices_global"):
            # Look for city header lines like "中国-北京 Beijing,China"
            # or "美国 Silicon Valley, USA"
            if ("中国-" in line or section == "offices_global") and "办公室地址" not in line and "资产管理员" not in line:
                office: dict = {}

                # Determine city and region
                if section == "offices_china":
                    # Pattern: "中国-北京 Beijing,China"
                    city_match = re.search(r"中国[--](\S+)", line)
                    if city_match:
                        raw_city = city_match.group(1).split()[0]
                        # Strip trailing English text (e.g. "上海Shanghai,China" → "上海")
                        chinese_only = re.match(r"[\u4e00-\u9fff]+", raw_city)
                        office["city"] = chinese_only.group(0) if chinese_only else raw_city
                    office["country_or_region"] = "中国"
                    office["office_name"] = line
                else:
                    # Global offices: "美国 Silicon Valley, USA", "新加坡 Singapore", etc.
                    office["office_name"] = line
                    # Extract city from known patterns
                    if "Silicon Valley" in line or "USA" in line:
                        office["city"] = "Sunnyvale"
                        office["country_or_region"] = "美国"
                    elif "Singapore" in line.split():
                        office["city"] = "Singapore"
                        office["country_or_region"] = "新加坡"
                    elif "Tokyo" in line or "Japan" in line:
                        office["city"] = "Tokyo"
                        office["country_or_region"] = "日本"
                    elif "Malaysia" in line or "马来西亚" in line:
                        office["city"] = "Kuala Lumpur"
                        office["country_or_region"] = "马来西亚"
                    else:
                        office["city"] = line.split()[0] if line.split() else line
                        office["country_or_region"] = ""

                # Read subsequent lines for address and admin
                j = i + 1
                while j < len(paragraphs):
                    next_line = paragraphs[j]

                    if next_line == "办公室地址：" or next_line.startswith("办公室地址"):
                        j += 1
                        continue

                    if next_line.startswith("资产管理员"):
                        # Parse admin: "@Name，phone" or "@Name phone" or "@Name"
                        admin_text = next_line.replace("资产管理员：", "").replace("资产管理员:", "").strip()
                        admin_text = admin_text.lstrip("@")
                        # Split name from phone
                        phone_match = re.search(r"[，,]?\s*(\d{11,})", admin_text)
                        if phone_match:
                            name = admin_text[:phone_match.start()].strip().rstrip("，,")
                            office["contact_phone"] = phone_match.group(1)
                        else:
                            name = admin_text.strip()
                        office["contact_name"] = name
                        office["_admin_raw"] = name
                        j += 1
                        break

                    # Skip company name lines for global offices
                    if section == "offices_global" and _is_company_name(next_line):
                        j += 1
                        continue

                    # This line is an address
                    if "address" not in office and "address_en" not in office:
                        # First address line
                        if re.search(r"[\u4e00-\u9fff]", next_line):
                            # Contains Chinese characters — it's a Chinese address
                            if section == "offices_global" and not re.search(r"[省市区县街道路号楼层]", next_line):
                                # Likely a company name in Chinese, skip
                                j += 1
                                continue
                            office["address"] = next_line
                        else:
                            office["address_en"] = next_line
                    elif "address" in office and "address_en" not in office:
                        # Second address line is English
                        office["address_en"] = next_line
                    elif "address_en" not in office and "address" not in office:
                        office["address_en"] = next_line

                    j += 1

                # For overseas offices: address should be null if no Chinese address found
                # (don't store company names as address)
                if section == "offices_global" and "address" not in office:
                    office["address"] = None

                if "city" in office:
                    offices.append(office)
                i = j
                continue

        # ── Parse legal entities ──
        if section == "entities":
            entity = _try_parse_entity(paragraphs, i)
            if entity:
                entities.append(entity["data"])
                i = entity["next_i"]
                continue

        i += 1

    return offices, entities


def _try_parse_entity(paragraphs: list[str], start: int) -> dict | None:
    """Try to parse a legal entity starting from the given paragraph index."""
    line = paragraphs[start]

    # Chinese entities: start with "名称：" or "公司名称：" or a known company name
    # Foreign entities: start with "JP", "PTE", "US", "MY" country codes
    entity: dict = {}
    i = start

    # Check for country-code entity blocks (JP, PTE, US, MY)
    if line in ("JP", "PTE", "US", "MY"):
        country_map = {"JP": "日本", "PTE": "新加坡", "US": "美国", "MY": "马来西亚"}
        entity["country_or_region"] = country_map.get(line, "")
        i += 1
        while i < len(paragraphs):
            kv = paragraphs[i]
            if kv.startswith("Account Name:"):
                entity["bank_account_name"] = kv.split(":", 1)[1].strip()
                if "name" not in entity:
                    entity["name"] = entity["bank_account_name"]
            elif kv.startswith("Account Number:"):
                entity["bank_account"] = kv.split(":", 1)[1].strip()
            elif kv.startswith("Bank Name:"):
                entity["bank_name"] = kv.split(":", 1)[1].strip()
            elif kv.startswith("Bank Address:"):
                entity["bank_address"] = kv.split(":", 1)[1].strip()
            elif kv.startswith("SWIFT:"):
                entity["swift_code"] = kv.split(":", 1)[1].strip()
            elif kv.startswith("Currency:"):
                entity["currency"] = kv.split(":", 1)[1].strip()
            elif kv.startswith("Account Type"):
                entity["account_type"] = kv.split("：" if "：" in kv else ":", 1)[1].strip()
            else:
                # End of this entity block
                break
            i += 1

        if "name" in entity:
            return {"data": entity, "next_i": i}
        return None

    # Chinese entity blocks: start with company name or "名称：xxx"
    # They have tax IDs, addresses, bank info in Chinese format
    if "名称" in line or "公司名称" in line:
        entity["name"] = line.split("：" if "：" in line else ":", 1)[-1].strip()
        i += 1
    elif re.search(r"[\u4e00-\u9fff]{4,}", line) and ("公司" in line or "有限" in line):
        # A standalone company name line
        entity["name"] = line
        i += 1
    else:
        return None

    entity["country_or_region"] = "中国"

    while i < len(paragraphs):
        kv = paragraphs[i]
        if "纳税人识别号" in kv:
            entity["tax_identifier"] = kv.split("：" if "：" in kv else ":", 1)[-1].strip()
        elif kv.startswith("地址") and "电话" in kv:
            # "地址、电话：xxx 010-xxx"
            rest = kv.split("：" if "：" in kv else ":", 1)[-1].strip()
            phone_match = re.search(r"(\d{3}[-‐]?\d{8,})", rest)
            if phone_match:
                entity["registered_address"] = rest[:phone_match.start()].strip()
                entity["phone"] = phone_match.group(1)
            else:
                entity["registered_address"] = rest
        elif kv.startswith("地址"):
            entity["registered_address"] = kv.split("：" if "：" in kv else ":", 1)[-1].strip()
        elif kv.startswith("电话"):
            entity["phone"] = kv.split("：" if "：" in kv else ":", 1)[-1].strip()
        elif "开户" in kv and ("银行" in kv or "账号" in kv):
            rest = kv.split("：" if "：" in kv else ":", 1)[-1].strip()
            # Try to split "银行名 账号"
            parts = rest.rsplit(" ", 1)
            if len(parts) == 2 and parts[1].isdigit():
                entity["bank_name"] = parts[0]
                entity["bank_account"] = parts[1]
            else:
                entity["bank_name"] = rest
        else:
            # Check if next line starts a new entity or section
            if ("名称" in kv or "公司名称" in kv or kv in ("JP", "PTE", "US", "MY")
                    or ("公司" in kv and "有限" in kv and kv != entity.get("name"))):
                break
            # Skip unknown lines within entity
        i += 1

    if "name" in entity:
        # Set currency for Chinese entities
        if entity.get("country_or_region") == "中国":
            entity["currency"] = "CNY"
        return {"data": entity, "next_i": i}
    return None


async def match_person(session: AsyncSession, raw_name: str) -> UUID | None:
    """Try to match a raw name to an identity_persons record."""
    normalized = raw_name.strip().lower()
    result = await session.execute(
        select(IdentityPerson.id).where(
            IdentityPerson.display_name.ilike(normalized)
        )
    )
    row = result.first()
    if row:
        return row[0]

    # Try partial match
    parts = normalized.split()
    if len(parts) >= 2:
        for pattern in [f"%{parts[0]}%{parts[-1]}%", f"%{parts[-1]}%{parts[0]}%"]:
            result = await session.execute(
                select(IdentityPerson.id).where(
                    IdentityPerson.display_name.ilike(pattern)
                )
            )
            row = result.first()
            if row:
                return row[0]

    return None


async def main(docx_path: str):
    if not os.path.isfile(docx_path):
        print(f"ERROR: File not found: {docx_path}")
        sys.exit(1)

    print(f"Reading docx: {docx_path}")
    offices, entities = parse_docx(docx_path)
    print(f"Parsed {len(offices)} offices, {len(entities)} legal entities from docx\n")

    from app.core.database_tls import database_engine_options
    db_url, connect_args = database_engine_options(str(settings.DATABASE_URL))
    engine = create_async_engine(db_url, connect_args=connect_args)
    async_session = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

    unmatched_admins: list[dict] = []
    linked_count = 0
    unlinked_count = 0

    async with async_session() as session:
        # ── Import legal entities ──
        print("=== Importing Legal Entities ===")
        entity_name_to_id: dict[str, UUID] = {}
        for ent_data in entities:
            name = ent_data["name"]
            existing = (await session.execute(
                select(AssetLegalEntity).where(AssetLegalEntity.name == name)
            )).scalar_one_or_none()

            if existing:
                for k, v in ent_data.items():
                    setattr(existing, k, v)
                entity_name_to_id[name] = existing.id
                print(f"  [UPDATE] {name}")
            else:
                item = AssetLegalEntity(**ent_data)
                session.add(item)
                await session.flush()
                entity_name_to_id[name] = item.id
                print(f"  [CREATE] {name}")

        # ── Import offices ──
        print("\n=== Importing Office Addresses ===")
        for off_data in offices:
            city = off_data["city"]
            admin_raw = off_data.pop("_admin_raw", None)

            existing = (await session.execute(
                select(AssetOfficeAddress).where(AssetOfficeAddress.city == city)
            )).scalar_one_or_none()

            # Match admin person
            admin_person_id = None
            if admin_raw:
                admin_person_id = await match_person(session, admin_raw)
                if not admin_person_id:
                    unmatched_admins.append({"city": city, "raw_name": admin_raw})

            # Resolve legal_entity_id from mapping
            entity_name = OFFICE_ENTITY_MAP.get(city)
            legal_entity_id = entity_name_to_id.get(entity_name) if entity_name else None

            clean_data = {k: v for k, v in off_data.items() if not k.startswith("_")}
            clean_data["admin_person_id"] = admin_person_id
            clean_data["legal_entity_id"] = legal_entity_id

            entity_status = f"→{entity_name}" if legal_entity_id else "→UNLINKED"
            admin_status = f"admin:{admin_raw}→matched" if admin_person_id else (f"admin:{admin_raw}→UNMATCHED" if admin_raw else "")

            if existing:
                for k, v in clean_data.items():
                    setattr(existing, k, v)
                print(f"  [UPDATE] {city} ({admin_status}) ({entity_status})")
            else:
                item = AssetOfficeAddress(**clean_data)
                session.add(item)
                print(f"  [CREATE] {city} ({admin_status}) ({entity_status})")

            if legal_entity_id:
                linked_count += 1
            else:
                unlinked_count += 1

        await session.commit()

    await engine.dispose()

    # ── Report ──
    print("\n=== Import Summary ===")
    print(f"Legal entities: {len(entities)}")
    print(f"Office addresses: {len(offices)}")
    print(f"  - Linked to entity: {linked_count}")
    print(f"  - Not linked: {unlinked_count}")

    if unmatched_admins:
        print(f"\n⚠ Unmatched admin names ({len(unmatched_admins)}):")
        for item in unmatched_admins:
            print(f"  - {item['city']}: {item['raw_name']}")
        print("\nPlease bind these manually in the UI (办公室地址 → 编辑 → 资产管理员).")
    else:
        print("\n✓ All admin names matched successfully.")


if __name__ == "__main__":
    if len(sys.argv) < 2:
        print("Usage: python scripts/import_company_directory.py <path-to-docx>")
        print("Example: python scripts/import_company_directory.py /Users/amber/Downloads/公司办公室地址及主体信息.docx")
        sys.exit(1)
    asyncio.run(main(sys.argv[1]))