#!/usr/bin/env python3
"""Import software catalog and business payments from Excel files into Helix DB."""
import asyncio
import sys
from pathlib import Path

import pandas as pd
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import sessionmaker

sys.path.insert(0, str(Path(__file__).resolve().parent.parent / "server"))

from app.core.config import settings  # noqa: E402


def clean(val) -> str | None:
    """Convert pandas value to clean string or None."""
    if val is None or (isinstance(val, float) and pd.isna(val)):
        return None
    s = str(val).strip()
    if s.lower() in ("nan", "none", ""):
        return None
    return s


async def main():
    engine = create_async_engine(settings.DATABASE_URL)
    async_session = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

    # --- Software Catalog ---
    df = pd.read_excel(Path(__file__).resolve().parent.parent / "data" / "软件资产目录.xlsx")
    df = df.where(pd.notna(df), None)

    async with async_session() as session:
        # Clear existing
        await session.execute(text("DELETE FROM asset_software_catalog"))

        for _, row in df.iterrows():
            name = row.get("软件")
            if not name:
                continue
            expiry = row.get("到期时间")
            expiry_val = None
            if expiry is not None and pd.notna(expiry):
                try:
                    expiry_val = pd.Timestamp(expiry).date()
                except Exception:
                    expiry_val = None

            await session.execute(
                text("""
                    INSERT INTO asset_software_catalog
                        (name, asset_type, owner, it_contact, status, remark, department, login_url, expiry_date, license_info, attachment)
                    VALUES
                        (:name, :asset_type, :owner, :it_contact, :status, :remark, :department, :login_url,
                         :expiry_date, :license_info, :attachment)
                """),
                {
                    "name": clean(name),
                    "asset_type": clean(row.get("资产类型")),
                    "owner": clean(row.get("负责人")),
                    "it_contact": clean(row.get("IT支持对接人")),
                    "status": "active" if row.get("状态") == "使用中" else "inactive",
                    "remark": clean(row.get("备注")),
                    "department": clean(row.get("业务部门")),
                    "login_url": clean(row.get("登陆地址")),
                    "expiry_date": expiry_val,
                    "license_info": clean(row.get("许可")),
                    "attachment": clean(row.get("附件")),
                },
            )
        await session.commit()
        count = (await session.execute(text("SELECT count(*) FROM asset_software_catalog"))).scalar()
        print(f"Imported {count} software catalog items")

    # --- Business Payments ---
    df2 = pd.read_excel(Path(__file__).resolve().parent.parent / "data" / "业务付款.xlsx")
    df2 = df2.where(pd.notna(df2), None)

    async with async_session() as session:
        await session.execute(text("DELETE FROM asset_business_payments"))

        for _, row in df2.iterrows():
            name = row.get("付款软件")
            if not name:
                continue
            amount = None
            if row.get("Unnamed: 10") is not None and pd.notna(row.get("Unnamed: 10")):
                try:
                    amount = float(row["Unnamed: 10"])
                except (ValueError, TypeError):
                    pass

            remark_parts = []
            r8 = clean(row.get("Unnamed: 8"))
            r9 = clean(row.get("Unnamed: 9"))
            if r8:
                remark_parts.append(r8)
            if r9:
                remark_parts.append(r9)
            remark = "; ".join(remark_parts) if remark_parts else None

            await session.execute(
                text("""
                    INSERT INTO asset_business_payments
                        (software_name, software_type, payment_frequency, payment_method, signing_entity, po_number, contract_expiry, requester, amount, remark)
                    VALUES
                        (:software_name, :software_type, :payment_frequency, :payment_method, :signing_entity, :po_number, :contract_expiry, :requester, :amount, :remark)
                """),
                {
                    "software_name": clean(name),
                    "software_type": clean(row.get("软件类型")),
                    "payment_frequency": clean(row.get("付款频率")),
                    "payment_method": clean(row.get("付款软件.1")),
                    "signing_entity": clean(row.get("签约主体")),
                    "po_number": clean(row.get("PO单号")),
                    "contract_expiry": clean(row.get("合同到期时间")),
                    "requester": clean(row.get("请款人")),
                    "amount": amount,
                    "remark": remark,
                },
            )
        await session.commit()
        count = (await session.execute(text("SELECT count(*) FROM asset_business_payments"))).scalar()
        print(f"Imported {count} business payment items")

    await engine.dispose()


if __name__ == "__main__":
    asyncio.run(main())