#!/usr/bin/env python3
"""Import assets from Excel file into helix_db.

Usage:
    cd /Users/amber/claude/helix/server
    uv run python ../scripts/import_assets.py
"""
from datetime import date, datetime
from decimal import Decimal, InvalidOperation
from pathlib import Path

import openpyxl
import pymysql
import pymysql.cursors

# ── Config ──
EXCEL_PATH = Path(__file__).parent.parent / "资产清单-2026-03-09.xlsx"
SHEET_NAME = "资产清单"
DB_URL = "mysql+pymysql://helix:helix@localhost:3306/helix"

# ── Mapping tables ──

STATUS_MAP = {
    "空闲": "idle",
    "领用": "claimed",
    "借用": "borrowed",
    "故障": "faulty",
    "测试使用中": "testing",
    "老旧的Intel芯片": "legacy",
}

CATEGORY_MAP = {
    "笔记本": "laptop",
    "配件": "accessory",
    "显示器": "monitor",
    "打印机": "printer",
    "会议电视": "conference_display",
    "台式机": "desktop",
    "网络设备": "network_device",
    "服务器": "server",
}

BRAND_NORMALIZE = {
    "MacBook": "MacBook",
    "Macbook": "MacBook",
    "macBook": "MacBook",
    "Mac Book": "MacBook",
    "Mac book": "MacBook",
    "Mac": "MacBook",
    "ThinkPad": "ThinkPad",
    "Thinkpad": "ThinkPad",
    "thinkbook": "ThinkPad",
    "dell": "Dell",
    "lenovo": "Lenovo",
    "联想/Lenovo": "Lenovo",
    "Lenovo": "Lenovo",
}

CURRENCY_MAP = {
    "人民币": "CNY",
    "美金": "USD",
    "马来西亚元": "MYR",
    "日元": "JPY",
    "新加坡元": "SGD",
    "泰铢": "THB",
}

DEVICE_STATUS_MAP = {
    "正常": "normal",
}

SOURCE_TYPE_MAP = {
    "采购": "purchased",
}


def clean_str(val) -> str | None:
    """Convert a cell value to a cleaned string, or None if empty."""
    if val is None:
        return None
    s = str(val).strip()
    return s if s else None


def parse_date(val) -> date | None:
    """Parse a date from various formats."""
    if val is None:
        return None
    if isinstance(val, datetime):
        return val.date()
    if isinstance(val, date):
        return val
    s = str(val).strip()
    if not s:
        return None
    # Try common formats
    for fmt in ("%Y/%m/%d", "%Y-%m-%d", "%Y.%m.%d"):
        try:
            return datetime.strptime(s, fmt).date()
        except ValueError:
            continue
    print(f"  WARNING: could not parse date: {repr(s)}")
    return None


def parse_cost(val) -> Decimal | None:
    """Parse a cost value like '2,031.93' or '14,290'."""
    if val is None:
        return None
    if isinstance(val, (int, float)):
        return Decimal(str(val))
    s = str(val).strip().replace(",", "")
    if not s:
        return None
    try:
        return Decimal(s)
    except InvalidOperation:
        print(f"  WARNING: could not parse cost: {repr(val)}")
        return None


def normalize_brand(val) -> str | None:
    """Normalize brand name."""
    s = clean_str(val)
    if s is None:
        return None
    return BRAND_NORMALIZE.get(s, s)


def main():
    print(f"Loading Excel: {EXCEL_PATH}")
    wb = openpyxl.load_workbook(str(EXCEL_PATH))
    ws = wb[SHEET_NAME]
    total_rows = ws.max_row - 1  # exclude header
    print(f"Found {total_rows} data rows")

    # Connect to DB
    from urllib.parse import urlparse
    parsed = urlparse(DB_URL.replace("mysql+pymysql://", "mysql://"))
    user_info = parsed.netloc.rsplit("@", 1)[0] if "@" in parsed.netloc else ""
    my_user, my_pass = user_info.split(":", 1) if ":" in user_info else (user_info, "")
    conn = pymysql.connect(
        host=parsed.hostname or "localhost",
        port=parsed.port or 3306,
        user=my_user,
        password=my_pass,
        database=parsed.path.lstrip("/"),
        charset="utf8mb4",
        autocommit=False,
    )
    cur = conn.cursor()

    # Build name -> user_id map from identity_persons
    cur.execute("SELECT display_name, id FROM identity_persons WHERE employment_status = 'active' AND display_name IS NOT NULL")
    name_to_user_id = {}
    for name, uid in cur.fetchall():
        name_to_user_id[name.strip()] = uid
    print(f"Loaded {len(name_to_user_id)} active users from auth_users")

    # Collect data
    inserted = 0
    skipped = 0
    user_matched = 0
    user_unmatched = 0
    unmatched_names = set()
    admin_matched = 0
    admin_unmatched = 0

    rows_to_insert = []

    for row_num in range(2, ws.max_row + 1):
        def cell(col):
            return ws.cell(row=row_num, column=col).value

        # Map columns
        status_raw = clean_str(cell(1))
        status = STATUS_MAP.get(status_raw, "idle") if status_raw else "idle"

        asset_tag = clean_str(cell(2))
        if not asset_tag:
            print(f"  Row {row_num}: skipping - no asset_tag")
            skipped += 1
            continue

        asset_name = clean_str(cell(3))

        category_raw = clean_str(cell(4))
        category = CATEGORY_MAP.get(category_raw, "other") if category_raw else "other"

        brand = normalize_brand(cell(5))
        model_val = clean_str(cell(6))
        serial_number = clean_str(cell(7))

        # Handle empty serial_number -> NULL (to avoid unique constraint issues)
        if serial_number == "":
            serial_number = None

        owner_company = clean_str(cell(8))

        # Lookup assigned user
        user_name = clean_str(cell(9))
        assigned_to = None
        if user_name:
            assigned_to = name_to_user_id.get(user_name)
            if assigned_to:
                user_matched += 1
            else:
                user_unmatched += 1
                unmatched_names.add(user_name)

        usage_company = clean_str(cell(13))
        department = clean_str(cell(14))
        assigned_at = parse_date(cell(15))
        expected_return_date = parse_date(cell(16))
        purchase_date = parse_date(cell(17))
        location = clean_str(cell(18))

        # Admin lookup
        admin_name = clean_str(cell(20))
        admin_id = None
        if admin_name:
            admin_id = name_to_user_id.get(admin_name)
            if admin_id:
                admin_matched += 1
            else:
                admin_unmatched += 1

        device_status_raw = clean_str(cell(21))
        device_status = DEVICE_STATUS_MAP.get(device_status_raw, "normal") if device_status_raw else "normal"

        warranty_until = parse_date(cell(22))

        source_type_raw = clean_str(cell(23))
        source_type = SOURCE_TYPE_MAP.get(source_type_raw, source_type_raw) if source_type_raw else None

        notes = clean_str(cell(24))

        repair_count_raw = clean_str(cell(26))
        repair_count = int(repair_count_raw) if repair_count_raw and repair_count_raw.isdigit() else 0

        repair_total_cost = parse_cost(cell(27))
        if repair_total_cost is None:
            repair_total_cost = Decimal("0")

        print_status = clean_str(cell(28))
        rfid = clean_str(cell(29))
        purchase_entity = clean_str(cell(30))
        invoice_number = clean_str(cell(31))
        user_entity = clean_str(cell(32))
        purchase_cost = parse_cost(cell(33))

        currency_raw = clean_str(cell(34))
        currency = CURRENCY_MAP.get(currency_raw, currency_raw) if currency_raw else None

        rows_to_insert.append({
            "asset_tag": asset_tag,
            "asset_name": asset_name,
            "category": category,
            "brand": brand,
            "model": model_val,
            "serial_number": serial_number,
            "status": status,
            "device_status": device_status,
            "source_type": source_type,
            "purchase_date": purchase_date,
            "purchase_cost": purchase_cost,
            "currency": currency,
            "invoice_number": invoice_number,
            "warranty_until": warranty_until,
            "location": location,
            "department": department,
            "admin_id": admin_id,
            "assigned_to": assigned_to,
            "assigned_at": assigned_at,
            "notes": notes,
            "owner_company": owner_company,
            "usage_company": usage_company,
            "expected_return_date": expected_return_date,
            "repair_count": repair_count,
            "repair_total_cost": repair_total_cost,
            "print_status": print_status,
            "rfid": rfid,
            "purchase_entity": purchase_entity,
            "user_entity": user_entity,
        })

    wb.close()

    # Insert rows
    print(f"\nInserting {len(rows_to_insert)} rows...")

    insert_sql = """
        INSERT INTO asset_items (
            asset_tag, asset_name, category, brand, model, serial_number,
            status, device_status, source_type,
            purchase_date, purchase_cost, currency, invoice_number,
            warranty_until, location, department,
            admin_id, assigned_to, assigned_at, notes,
            owner_company, usage_company, expected_return_date,
            repair_count, repair_total_cost,
            print_status, rfid, purchase_entity, user_entity
        ) VALUES (
            %(asset_tag)s, %(asset_name)s, %(category)s, %(brand)s, %(model)s, %(serial_number)s,
            %(status)s, %(device_status)s, %(source_type)s,
            %(purchase_date)s, %(purchase_cost)s, %(currency)s, %(invoice_number)s,
            %(warranty_until)s, %(location)s, %(department)s,
            %(admin_id)s, %(assigned_to)s, %(assigned_at)s, %(notes)s,
            %(owner_company)s, %(usage_company)s, %(expected_return_date)s,
            %(repair_count)s, %(repair_total_cost)s,
            %(print_status)s, %(rfid)s, %(purchase_entity)s, %(user_entity)s
        )
    """

    for i, row_data in enumerate(rows_to_insert):
        try:
            cur.execute(insert_sql, row_data)
            inserted += 1
        except pymysql.err.IntegrityError as e:
            conn.rollback()
            detail = str(e).split("\n")[0]
            print(f"  SKIP duplicate: {row_data['asset_tag']} - {detail}")
            skipped += 1
        except Exception as e:
            conn.rollback()
            print(f"  ERROR inserting {row_data['asset_tag']}: {e}")
            skipped += 1

    conn.commit()

    # Verify counts
    cur.execute("SELECT count(*) FROM asset_items")
    total_in_db = cur.fetchone()[0]
    cur.execute("SELECT count(*) FROM asset_items WHERE assigned_to IS NOT NULL")
    assigned_count = cur.fetchone()[0]

    cur.close()
    conn.close()

    # Report
    print("\n" + "=" * 60)
    print("IMPORT SUMMARY")
    print("=" * 60)
    print(f"Total data rows in Excel:     {total_rows}")
    print(f"Inserted:                     {inserted}")
    print(f"Skipped:                      {skipped}")
    print(f"User matches (assigned_to):   {user_matched}")
    print(f"User unmatched:               {user_unmatched}")
    print(f"Admin matches:                {admin_matched}")
    print(f"Admin unmatched:              {admin_unmatched}")
    print(f"Total rows in DB now:         {total_in_db}")
    print(f"Rows with assigned_to set:    {assigned_count}")

    if unmatched_names:
        print(f"\nUnmatched user names ({len(unmatched_names)}):")
        for name in sorted(unmatched_names)[:30]:
            print(f"  - {name}")
        if len(unmatched_names) > 30:
            print(f"  ... and {len(unmatched_names) - 30} more")

    print("\nDone.")


if __name__ == "__main__":
    main()