"""荷兰(NL)企业数据访问：VAT 表（VATBusinessRecord / VATRegInfo）查询。"""
from __future__ import annotations

from datetime import datetime

from sqlalchemy import text
from sqlalchemy.orm import Session


# NL 生成文件回写分类：main=合并后的最终文件，license=执照
NL_FILE_CATEGORIES = {
    "main": "517a08ce-9889-404a-a508-41196fbd419c",
    "license": "60899571-a553-4e05-8232-cfa09b50605d",
}


# 基础列：记录 / 企业 / 法人（br.Code 供调度器驱动后续按 code 生成）
_BASE_SELECT = """
  br.ID,
  br.Code,
  ri.Id as VATRegInfoID,
  ri.PushTaxBureauStatus,
  ri.PushType,
  bc.NameCN,
  bc.NameEng,
  br.Country,
  bc.RegNumber,
  bc.RegAddress,
  bc.RegAddressEng,
  bc.CompanyAddressLine1En,
  bc.CompanyAddressLine2En,
  bc.CompanyAddressPostcode,
  bc.CompanyAddressProvinceEn,
  bc.RegAddressProvince,
  bc.RegAddressCity,
  bc.CityEngName,
  bc.EstablishmentDate,
  bc.SignatureDate,
  bc.LegalPersonName,
  bc.LegalPersonIdNumber,
  bc.LegalPersonPhone,
  bc.LegalPersonIdStartDate,
  bc.LegalPersonIdEndDate,
  bc.LegalPersonFullNamePinYin,
  bc.LegalPersonBirthDate,
  bc.LegalPersonIdCardAddress,
  bc.LegalPersonIdCardAddressEng,
  bc.LegalPersonGender,
  bc.LegalPersonCityEngName,
  bsps.SellerID,
  bsps.ProductRange"""

# 附件列（仅详情查询需要；一行一文件）
_FILE_SELECT = """
  CASE
    WHEN af.F_Id = bc.BusinessLicensePic THEN 'license'
    WHEN af.F_Id = bc.LegalPersonIdNumberFrontPic THEN 'id_front'
    WHEN af.F_Id = bc.LegalPersonIdNumberBackPic THEN 'id_back'
    ELSE 'other'
  END AS file_role,
  af.F_FileName,
  af.F_FileSize,
  af.F_FileType,
  af.F_FilePath"""

# 基础 JOIN（不含附件）
_BASE_FROM = """
FROM VATBusinessRecord br
  LEFT JOIN VATRegInfo ri ON ri.VATBusinessRecordId=br.ID
  LEFT JOIN Base_Sales_Platform_Shop bsps ON br.Base_ShopInfoID=bsps.ID
  LEFT JOIN Base_customer_company bc ON br.CompanyID = bc.ID"""

# 附件 JOIN（仅详情查询）
_FILE_JOIN = """
  LEFT JOIN Base_AnnexesFile af ON af.F_Id IN (bc.BusinessLicensePic, bc.LegalPersonIdNumberFrontPic, bc.LegalPersonIdNumberBackPic)"""

# 附件查询（供调度流程按 code 取附件，独立于详情查询）
_ANNEX_FROM = """
FROM Base_AnnexesFile af
  JOIN Base_customer_company bc ON af.F_Id IN (bc.BusinessLicensePic, bc.LegalPersonIdNumberFrontPic, bc.LegalPersonIdNumberBackPic)
  JOIN VATBusinessRecord br ON br.CompanyID = bc.ID"""


def _annex_sql() -> str:
    """附件查询 SQL：按企业代码取附件（营业执照 / 身份证正反面）。"""
    return f"SELECT{_FILE_SELECT}{_ANNEX_FROM} WHERE br.Country='nl' AND br.Code = :code"


def _build_detail_sql(*, code: str | None = None) -> tuple[str, dict]:
    """按用途拼企业详情 SQL。

    - code 传入：按企业代码查，含附件（一行一文件）
    - code 为空：定时任务查全部待推送记录，不含附件（一条记录一行）
    """
    if code is not None:
        select = f"SELECT{_BASE_SELECT},{_FILE_SELECT}"
        from_clause = f"{_BASE_FROM}{_FILE_JOIN}"
        where, params = "br.Country='nl' AND br.Code = :code", {"code": code}
    else:
        select = f"SELECT{_BASE_SELECT}"
        from_clause = _BASE_FROM
        where, params = "br.Country='nl' AND ri.PushType='101' AND ri.PushTaxBureauStatus=5", {}
        # where, params = "br.Country='nl' AND ri.PushType='101' AND br.Code = 'POEORI20260907000044'", {}  # 测试 POEORI20260817000075，POEORI20260907000044
    return f"{select}{from_clause} WHERE {where}", params


def get_schedule_recode(session: Session):
    """查全部待推送（PushType=101 且 PushTaxBureauStatus=5）的企业记录，不含附件（一记录一行）。"""
    sql, params = _build_detail_sql()
    return session.execute(text(sql), params).mappings().all()


def get_annex_files(session: Session, code: str):
    """按企业代码查附件文件（营业执照 / 身份证正反面），一行一文件。"""
    return session.execute(text(_annex_sql()), {"code": code}).mappings().all()


def get_enterprise_detail(session: Session, code: str):
    """按企业代码（bc.Code）查企业详情（含订单国家、附件文件）。"""
    sql, params = _build_detail_sql(code=code)
    return session.execute(text(sql), params).mappings().all()


def update_vat_push_status(
    session: Session,
    vat_reg_info_id: str | None,
    push_status: int,
    error_msg: str | None = None,
) -> bool:
    """回写 VATRegInfo 推送状态：成功 push_status=6；失败 push_status=7 并写 PushTaxBureauErrorMsg。

    vat_reg_info_id 为空时返回 False（不执行）。
    """
    if not vat_reg_info_id:
        return False
    if error_msg is None:
        session.execute(
            text("UPDATE VATRegInfo SET PushTaxBureauStatus=:s, PushTaxBureauErrorMsg=:m, PushApiDate=:mod_date WHERE Id=:id"),
            {"s": push_status, "id": vat_reg_info_id, "m": "", "mod_date": datetime.now()},
        )
    else:
        session.execute(
            text(
                "UPDATE VATRegInfo SET PushTaxBureauStatus=:s, PushTaxBureauErrorMsg=:m, PushApiDate=:mod_date WHERE Id=:id"
            ),
            {"s": push_status, "m": error_msg, "id": vat_reg_info_id, "mod_date": datetime.now()},
        )
    session.commit()
    return True