"""法国(FR)企业数据访问：EPR 表（EPRBusinessRecord / EPRRegInfo）查询。"""
from __future__ import annotations

from datetime import datetime

from sqlalchemy import text
from sqlalchemy.orm import Session


# FR 模板配置：每个模板 → 语义文件名(name) + 回写分类(category)。key 是模板名，仅用于匹配。
FR_TEMPLATE_CONFIG = {
    "2026年电池POA模板": {
        "name": "POA",
        "category": "fa8762a6-787f-47bd-9626-2522dfdc7b80",
    },
    "TEMPLATE data new registrations battery categories": {
        "name": "battery_categories",
        "category": "1c199ef6-71ac-4f21-806a-f76407522e5c",
    },
}


# FR（EPR）调度查询：来源表与 NL(VAT) 不同，关键字段归一化到 Code/VATRegInfoID/Country，
# 其余 FR 特有字段（LocalVATNumber/ServiceItemName 等）保留为附加列。
# 真实上线时去掉 TOP 10 与 BusinessSerialNumber 硬编码。
_FR_SCHEDULE_SQL = """
SELECT TOP 10
    br.BusinessSerialNumber     AS Code,
    br.ID                       AS RegisterID,
    br.Country                  AS Country,
    br.VATNumber                AS VATNumber,
    bc.NameEng                  AS NameEng,
    bc.NameCN                   AS NameCN,
    bc.RegNumber                AS RegNumber,
    bc.CompanyCode              AS CompanyCode,
    bc.RegAddressEng            AS RegAddressEng,
    bc.RoomNumber               AS RoomNumber,
    bc.RoomNumberSuffix         AS RoomNumberSuffix,
    bc.CompanyAddressLine1En    AS CompanyAddressLine1En,
    bc.CompanyAddressLine2En    AS CompanyAddressLine2En,
    bc.CompanyAddressPostcode   AS CompanyAddressPostcode,
    bc.CityEngName              AS CityEngName,
    bc.Country                  AS CompanyCountry,
    bc.RegisteredCapital        AS RegisteredCapital,
    bc.LegalPersonFullNamePinYin AS LegalPersonFullNamePinYin,
    bc.LegalPersonGender        AS LegalPersonGender,
    bc.LegalPersonEmail         AS LegalPersonEmail,
    bc.LegalPersonPhone         AS LegalPersonPhone,
    
    ri.ID                       AS VATRegInfoID,
    
    si.ServiceItemName          AS ServiceItemName,
    
    c.CountryName_en            AS CountryNameEn,

    od.CategoryBrandJson        AS CategoryBrandJson,
    
    ri.PushType                 AS PushType
FROM EPRBusinessRecord br
LEFT JOIN EPRRegInfo ri            ON br.ID = ri.EPRBusinessRecordId
LEFT JOIN Base_Customer_Company bc ON bc.ID = br.CompanyID
LEFT JOIN Country c                ON c.CountryName = bc.Country
LEFT JOIN ServiceItems si          ON si.ID = br.ServiceItemID
LEFT JOIN MS_OrderDetails od       ON br.MSOrderDetailsId = od.ID
WHERE br.Country = 'FR'
    -- AND br.BusinessSerialNumber = 'POEPR20260916000012'  -- 测试
    AND ri.PushType = '301'
    AND si.ServiceItemName='电池法注册'
    AND ri.PushTaxBureauStatus=5
ORDER BY br.ID ASC
"""


def get_schedule_recode(session: Session):
    """查 FR 待推送记录（EPR 表，字段已归一化），一记录一行。"""
    return session.execute(text(_FR_SCHEDULE_SQL), {}).mappings().all()


def get_epr_category_names(session: Session, ids: list[str]) -> list:
    """按 id 列表查 EPRCategory 的名称（电池分类方案二用）。表名/列名按实际表调整。"""
    if not ids:
        return []
    marks = ",".join(f":id{i}" for i in range(len(ids)))
    params = {f"id{i}": v for i, v in enumerate(ids)}
    sql = f"SELECT Id, CategoryName AS CategoryName FROM EPRCategory WHERE Id IN ({marks})"
    return session.execute(text(sql), params).mappings().all()


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

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