from datetime import datetime from pydantic import Field from cbh.api.client.dto import ExportLayout, ExportProfileType, InputField, OutputField from cbh.api.common.dto import CustomsProcedure, TradeDirection from cbh.core.database import MongoBaseModel, MongoSerializableModel class ExportColumn(MongoSerializableModel): field: OutputField header: str class ProfileDefaults(MongoSerializableModel): direction: TradeDirection = TradeDirection.IMPORT procedure: CustomsProcedure = CustomsProcedure.RELEASE_FOR_FREE_CIRCULATION country: str | None = None destinationCountry: str | None = None # The currency the client's value column is written in. The pack does not # say so on every line, and the header total is meaningless in the wrong one. currency: str = "EUR" useTodayWhenDateMissing: bool = True class TotalsConfig(MongoSerializableModel): enabled: bool = True label: str = "TOTAL" sumFields: list[str] = Field( default_factory=lambda: [ InputField.PACKAGES.value, InputField.GROSS_WEIGHT.value, InputField.VALUE.value, ] ) # A parcel manifest carries one row per item but states weight and piece # count per parcel, repeated on every row of that parcel. Adding the rows # up inflates the shipment totals, which is the figure the broker checks # the consignment against, so those fields are counted once per parcel. parcelKeyColumn: str | None = None parcelLevelFields: list[str] = Field(default_factory=list) packagesFromParcelCount: bool = False def default_export_columns() -> list[ExportColumn]: return [ ExportColumn(field=OutputField.RESOLVED_CODE, header="resolved_code"), ExportColumn(field=OutputField.DOCUMENTS_REQUIRED, header="documents_required"), ExportColumn(field=OutputField.DOCUMENTS_EXEMPTION, header="documents_exemption"), ExportColumn(field=OutputField.WARNINGS, header="warnings"), ExportColumn(field=OutputField.STATUS, header="status"), ] def preference_export_columns() -> list[ExportColumn]: """The tariff preference of an import line, as the declaration box takes it.""" return [ ExportColumn(field=OutputField.PREFERENCE_CODE, header="Preferentie"), ExportColumn(field=OutputField.PREFERENTIAL_RATE, header="Preferentieel tarief"), ExportColumn(field=OutputField.ORIGIN_PROOF, header="Oorsprongsbewijs"), ExportColumn(field=OutputField.QUOTA_ORDER_NUMBER, header="Contingentnummer"), ] class ClientProfileModel(MongoBaseModel): name: str description: str | None = None exportProfile: ExportProfileType = ExportProfileType.SOFTPAK columnMapping: dict[str, str] = Field(default_factory=dict) exportColumns: list[ExportColumn] = Field(default_factory=default_export_columns) defaults: ProfileDefaults = Field(default_factory=ProfileDefaults) totals: TotalsConfig = Field(default_factory=TotalsConfig) headerRow: int = 1 sheetName: str | None = None highlightForReview: bool = True exportLayout: ExportLayout = ExportLayout.APPEND_COLUMNS softpakHeaderRows: list[list[str]] = Field(default_factory=list) datetimeInserted: datetime = Field(default_factory=datetime.now) datetimeUpdated: datetime = Field(default_factory=datetime.now) def missing_required_fields(self) -> list[str]: return [ field.value for field in InputField if field.required and not self.columnMapping.get(field.value) ] def goenee_softpak_profile() -> ClientProfileModel: return ClientProfileModel( name="Goenee Softpak", description="Jac. Goenee B.V., Vianen. Softpak line list, Excel in and out.", exportProfile=ExportProfileType.SOFTPAK, exportLayout=ExportLayout.SOFTPAK_SHEET, headerRow=7, columnMapping={ InputField.CODE.value: "hs code", InputField.DESCRIPTION.value: "description", InputField.GROSS_WEIGHT.value: "WEIGHT GROSS", InputField.NET_WEIGHT.value: "WEIGHT nett", InputField.COUNTRY.value: "origin", InputField.QUANTITY.value: "sup_quantity", InputField.COLLI_TYPE.value: "type colli", InputField.PACKAGES.value: "nr colli", InputField.REMARKS.value: "remarks", InputField.VALUE.value: "value", }, exportColumns=[ExportColumn(field=OutputField.YCODES, header="Ycodes")], softpakHeaderRows=[ ["Government procedure", "10", " "], ["Previous ", "00"], ["Invoice N935", " "], ["Currency", "EUR"], ["Container"], ], defaults=ProfileDefaults( direction=TradeDirection.EXPORT, useTodayWhenDateMissing=True ), totals=TotalsConfig( sumFields=[ InputField.GROSS_WEIGHT.value, InputField.NET_WEIGHT.value, InputField.PACKAGES.value, InputField.VALUE.value, ] ), ) def goenee_export_line_list_profile() -> ClientProfileModel: """The line list Goenee produces from a G-Star manifest — `Export Goenee.xls`. It is the same shipment as the ecom manifest but already worked: the Y-code column holds the exemptions a declarant assigned by hand, which is what the gold-set scoring is measured against. """ return ClientProfileModel( name="Goenee export line list", description="Jac. Goenee B.V. line list for a G-Star export, legacy .xls.", exportProfile=ExportProfileType.SOFTPAK, columnMapping={ InputField.CODE.value: "HS Code", InputField.DESCRIPTION.value: "Item Descr", InputField.GROSS_WEIGHT.value: "Total Item Weight", InputField.COUNTRY.value: "Item COO", InputField.PACKAGES.value: "Pieces", InputField.COLLI_TYPE.value: "Colli type", # The header is truncated in the file itself; it has no closing # bracket, and the mapping matches what is actually written. InputField.REMARKS.value: "Remarks (EAN", InputField.VALUE.value: "Total Value (GBP)", }, exportColumns=[ExportColumn(field=OutputField.YCODES, header="Y-code")], defaults=ProfileDefaults( direction=TradeDirection.EXPORT, destinationCountry="GB", currency="GBP" ), totals=TotalsConfig( sumFields=[ InputField.PACKAGES.value, InputField.GROSS_WEIGHT.value, InputField.VALUE.value, ], parcelKeyColumn="Tracking no", parcelLevelFields=[ InputField.PACKAGES.value, InputField.GROSS_WEIGHT.value, ], packagesFromParcelCount=True, ), ) def goenee_bescheiden_profile() -> ClientProfileModel: """Jac. Goenee B.V. packs, one line per declaration line, `CF-GOE-*-bescheiden.csv`. Goenee sends every pack in the same semicolon CSV whatever the goods: the promo-goods and G-Star exports to GB, the parcel imports from GB, the broadcast kit imported for an event, the used SPMTs shipped to Jakarta and Singapore. Direction, origin, destination and declaration date are read per row, because a week of packs mixes all of them. The code comes at four, six or eight digits, or not at all, and `hs_conflict` says the shipper's sources disagreed on it. The column is kept in the file as sent; a short code comes back with its candidates rather than an answer. """ return ClientProfileModel( name="Goenee bescheiden", description="Jac. Goenee B.V. declaration lines per pack, CSV, import NL and export.", exportProfile=ExportProfileType.SOFTPAK, columnMapping={ InputField.CODE.value: "goederencode", InputField.DESCRIPTION.value: "omschrijving", InputField.COUNTRY.value: "oorsprong", InputField.DESTINATION_COUNTRY.value: "bestemming", InputField.DIRECTION.value: "richting", InputField.DATE.value: "datum", InputField.REMARKS.value: "regel_id", }, exportColumns=[ ExportColumn(field=OutputField.RESOLVED_CODE, header="CargoFlow code"), ExportColumn(field=OutputField.CODE_DESCRIPTION, header="Omschrijving TARIC"), ExportColumn(field=OutputField.CANDIDATES, header="Keuze subdivisie"), ExportColumn(field=OutputField.DOCUMENTS_REQUIRED, header="Bescheiden"), ExportColumn(field=OutputField.YCODES, header="Y-codes"), *preference_export_columns(), ExportColumn(field=OutputField.WARNINGS, header="Signalen"), ExportColumn(field=OutputField.STATUS, header="Status"), ], defaults=ProfileDefaults( direction=TradeDirection.IMPORT, useTodayWhenDateMissing=True ), # The packs carry no quantity, weight or value, so there is nothing to add up. totals=TotalsConfig(enabled=False), ) def efg_henx_transit_profile() -> ClientProfileModel: """EFG Arabia to Henx Europe — the packs Goenee files under external transit. The goods are not released on arrival: they move under T1 to Henx and are declared there, so duty, VAT and excise are secured rather than paid. The prohibitions and restrictions still bind, which is what this profile is for. The codes in these packs are eight-digit CN. An import declaration is filed on ten, so most lines come back with their declarable candidates rather than an answer — that is the pack's gap, not the engine's, and it is left visible. """ return ClientProfileModel( name="EFG Arabia to Henx (T1)", description="EFG Arabia commercial invoice / packing list, external transit to Henx Europe BV.", exportProfile=ExportProfileType.SOFTPAK, # The pack opens with the invoice head — shipper, receiver, totals — and # the line table starts at row 20. The workbook's other sheet is a pivot # of the same lines and holds no declarable detail. sheetName="1. SUMMARY (EQ LIST FOR SHIPPIN", headerRow=20, columnMapping={ InputField.CODE.value: "HS CODE", InputField.COUNTRY.value: "(country)", InputField.DESCRIPTION.value: "Description", InputField.QUANTITY.value: "Quantity", InputField.GROSS_WEIGHT.value: "Case weight", InputField.VALUE.value: "Total Value (EUR)|Total Value", InputField.REMARKS.value: "Serial No.", }, exportColumns=[ExportColumn(field=OutputField.YCODES, header="Y-code")], defaults=ProfileDefaults( direction=TradeDirection.IMPORT, procedure=CustomsProcedure.EXTERNAL_TRANSIT_T1, destinationCountry="NL", useTodayWhenDateMissing=True, ), totals=TotalsConfig( sumFields=[ InputField.GROSS_WEIGHT.value, InputField.QUANTITY.value, InputField.VALUE.value, ] ), ) def gstar_ecom_profile() -> ClientProfileModel: return ClientProfileModel( name="G-Star ecom GB", description="Group eStore BV parcel manifest, NL to GB. One row per item.", exportProfile=ExportProfileType.SOFTPAK, columnMapping={ InputField.CODE.value: "HS Code", InputField.DESCRIPTION.value: "Item Descr", InputField.GROSS_WEIGHT.value: "Total Weight", InputField.COUNTRY.value: "Item COO", InputField.DESTINATION_COUNTRY.value: "Cust Country", InputField.PACKAGES.value: "Pieces", InputField.QUANTITY.value: "Item Qty", InputField.VALUE.value: "Item Value", InputField.REMARKS.value: "SKU", }, exportColumns=[ExportColumn(field=OutputField.YCODES, header="Y-code")], defaults=ProfileDefaults( direction=TradeDirection.EXPORT, destinationCountry="GB", currency="GBP" ), # One row per item, many rows per parcel. "Total Weight" and the piece # count belong to the parcel, so both are counted once per tracking # number. Checked against the Master Invoice for 04-09-2026: 167 # packages and GBP 11,376.42, which only reconcile this way. totals=TotalsConfig( sumFields=[ InputField.PACKAGES.value, InputField.GROSS_WEIGHT.value, InputField.VALUE.value, ], parcelKeyColumn="Tracking no", parcelLevelFields=[ InputField.PACKAGES.value, InputField.GROSS_WEIGHT.value, ], packagesFromParcelCount=True, ), ) def spare_cargo_line_list_profile() -> ClientProfileModel: """Spare Cargo B.V. — ship spares and offshore equipment, by air and sea. One sheet carries several packs, import and export mixed: the direction, origin, destination and declaration date are read per row. The shipper's HS code comes at six, eight or ten digits, or not at all. Spare Cargo declares in Scope, whose import specification has not been received, so the checked columns are appended to the file as sent. """ return ClientProfileModel( name="Spare Cargo line list", description="Spare Cargo B.V. declaration lines per pack, import NL and export.", exportProfile=ExportProfileType.GENERIC, sheetName="Regels", columnMapping={ InputField.CODE.value: "HS op factuur|HS code|HS", InputField.DESCRIPTION.value: "Omschrijving (factuur)|Omschrijving|Description", InputField.COUNTRY.value: "Herkomst|Origin", InputField.DESTINATION_COUNTRY.value: "Bestemming|Destination", InputField.DIRECTION.value: "Richting|Direction", InputField.DATE.value: "Aangiftedatum|Declaration date", InputField.QUANTITY.value: "Aantal|Qty", InputField.NET_WEIGHT.value: "Netto kg|Net kg", InputField.VALUE.value: "Waarde|Value", InputField.REMARKS.value: "Artikelnr|Item / part no.", }, exportColumns=[ ExportColumn(field=OutputField.RESOLVED_CODE, header="CargoFlow code"), ExportColumn(field=OutputField.CODE_DESCRIPTION, header="Omschrijving TARIC"), ExportColumn(field=OutputField.CANDIDATES, header="Keuze subdivisie"), ExportColumn(field=OutputField.DOCUMENTS_REQUIRED, header="Bescheiden"), ExportColumn(field=OutputField.DOCUMENTS_EXEMPTION, header="Y-codes"), ExportColumn(field=OutputField.WARNINGS, header="Signalen"), ExportColumn(field=OutputField.STATUS, header="Status"), ], defaults=ProfileDefaults( direction=TradeDirection.IMPORT, destinationCountry="NL", useTodayWhenDateMissing=True, ), totals=TotalsConfig( sumFields=[ InputField.QUANTITY.value, InputField.NET_WEIGHT.value, InputField.VALUE.value, ] ), )