packing_parser.py - main - Paff/declarant

packing_parser.py
1 698 строк72 KB
1pash1985-gif
LPD555: col/ecu/ken path patterns + BOJ444 cert AWB + packing fixes
26 июл 2026, 18:59
26 июл 2026, 18:5959dc6f7
# -*- coding: utf-8 -*-
"""Универсальный парсер packing-list *.xls/*.xlsx.

Три известных формата в этой задаче (Ecuador/Colombia/Kenya):
  1) "Alex Kazan"  — простая таблица: [farm, farm_invoice, boxes, variety, length, stems]
  2) "VIVAX"       — блочная (PACKING # NNNN, blocks per farm)
  3) "PRADO"       — суммарная (TALLOS × MEDIDA по цветку)

Логика: единый эвристический проход по всем ячейкам. Собираем:
  - marking (из первой строки или "Customer:" / "CLIENTE:" / имени папки)
  - country (Ecuador/Colombia/Kenya; определяется по имени листа или родительской папке)
  - farm_invoice (последний numeric в колонке B/2 перед data-строкой)
  - farm (последний non-empty в колонке A)
  - variety, length, stems в строках

Возвращает PackingSheet с полем `items: list[PackingItem]`.
"""
from __future__ import annotations
import os
import re
from dataclasses import dataclass, field
from pathlib import Path
from typing import Optional, Iterable

try:
    import xlrd
except ImportError:
    xlrd = None
try:
    from openpyxl import load_workbook
except ImportError:
    load_workbook = None
try:
    from python_calamine import CalamineWorkbook
except ImportError:
    CalamineWorkbook = None

from flower_taxonomy import classify, extract_length, normalize_marking


@dataclass
class PackingItem:
    country: str = ""
    marking: str = ""
    farm: str = ""
    farm_invoice: str = ""     # sub-invoice от фермы (в PDF sertif это `invoice_nr`)
    packing_no: str = ""       # PACKING # / № INVOICE = внутренний номер клиентского packing
    awb: str = ""
    variety: str = ""
    flower: str = ""           # ROSA / ALSTROEMERIA / …
    length_cm: int = 0
    stems: int = 0
    boxes: float = 0.0
    source: str = ""

    def key_import(self) -> tuple:
        """Ключ для агрегации в Таблица импорт: (country, flower, length, marking)."""
        return (self.country, self.flower, self.length_cm, self.marking)


@dataclass
class PackingSheet:
    country: str = ""
    marking: str = ""
    packing_no: str = ""
    awb: str = ""
    source: str = ""
    items: list[PackingItem] = field(default_factory=list)


# --- Утилиты ---

_COUNTRY_FROM_TEXT = [
    (re.compile(r'\bECUADOR\b|\bEC\b|\bECU\b|\bЭКВАД',   re.IGNORECASE), 'Ecuador'),
    (re.compile(r'\bCOLOMBIA\b|\bCOL\b|\bCO\b|\bКОЛУМБ', re.IGNORECASE), 'Colombia'),
    (re.compile(r'\bKENYA\b|\bKEN\b|\bKE\b|\bКЕНИ',       re.IGNORECASE), 'Kenya'),
    (re.compile(r'\bCHILE\b|\bЧИЛИ',                     re.IGNORECASE), 'Chile'),
    (re.compile(r'\bISRAEL\b|\bИЗРАИЛ',                  re.IGNORECASE), 'Israel'),
]


def _detect_country(*hints: str) -> str:
    for h in hints:
        if not h:
            continue
        for pat, c in _COUNTRY_FROM_TEXT:
            if pat.search(str(h)):
                return c
    return ''


_AWB_RE = re.compile(r'\b\d{3}[- ]?\d{4}[- ]?\d{4}\b|\b145[- ]?\d{4}[- ]?\d{4}\b')


def _detect_awb(text: str) -> str:
    if not text:
        return ''
    m = _AWB_RE.search(str(text))
    return m.group(0).replace(' ', '-') if m else ''


def _as_number(v) -> Optional[float]:
    """Преобразовать ячейку в float. Пустые/nan/строки → None."""
    if v is None or v == '':
        return None
    if isinstance(v, (int, float)):
        return float(v)
    try:
        return float(str(v).replace(',', '.').strip())
    except (ValueError, TypeError):
        return None


def _rows_from_xls(path: str) -> Iterable[tuple[str, list[list]]]:
    """Yield (sheet_name, matrix) для .xls через xlrd."""
    if xlrd is None:
        return
    try:
        wb = xlrd.open_workbook(path, formatting_info=False)
    except Exception:
        return
    for s in wb.sheets():
        m = [[s.cell_value(r, c) for c in range(s.ncols)]
             for r in range(s.nrows)]
        yield s.name, m


def _rows_from_xlsx(path: str) -> Iterable[tuple[str, list[list]]]:
    """Yield (sheet_name, matrix) для .xlsx через openpyxl.

    Пробуем сначала read_only=True (быстрее), при ошибке — обычная загрузка,
    затем calamine как последний резерв (для повреждённых файлов).
    """
    if load_workbook is not None:
        # 1) Fast path (openpyxl read_only)
        try:
            wb = load_workbook(path, data_only=True, read_only=True)
            for s in wb.worksheets:
                m = []
                for row in s.iter_rows(values_only=True):
                    m.append(list(row))
                yield s.title, m
            wb.close()
            return
        except Exception:
            pass
        # 2) Fallback: openpyxl full
        try:
            wb = load_workbook(path, data_only=True, read_only=False)
            for s in wb.worksheets:
                m = []
                for row in s.iter_rows(values_only=True):
                    m.append(list(row))
                yield s.title, m
            try:
                wb.close()
            except Exception:
                pass
            return
        except Exception:
            pass
    # 3) Last resort: calamine (быстрый Rust-парсер, читает "битые" файлы)
    if CalamineWorkbook is not None:
        try:
            wb = CalamineWorkbook.from_path(path)
        except Exception:
            return
        for name in wb.sheet_names:
            try:
                s = wb.get_sheet_by_name(name)
                yield name, s.to_python()
            except Exception:
                continue


def _rows(path: str) -> Iterable[tuple[str, list[list]]]:
    """Yield (sheet_name, matrix) для .xls и .xlsx."""
    p = path.lower()
    if p.endswith('.xls'):
        yield from _rows_from_xls(path)
    elif p.endswith('.xlsx') or p.endswith('.xlsm'):
        yield from _rows_from_xlsx(path)


# --- Определение маркинга ---

_MARKING_FROM_CELL = re.compile(
    r'^(?:Customer|Client|Cliente|Клиент)(?:\s+name)?\s*:\s*(.+)$',
    re.IGNORECASE,
)

# Baraka-маркинги: "B-XXX", "b-XXX", "B - XXX" (с пробелами). Ecuador/Kenya 333MBK
# помечают конечного получателя в самой шапке файла (Consignee/Marketing name),
# а Customer/Cliente/первая all-caps строка часто содержит SHIPPER (напр.
# 'JESSANT FLOWERS'), который НЕ является маркингом.
_MARKING_BARAKA_RE = re.compile(
    r'\bB\s*-\s*([A-Z][A-Z0-9.]{2,20})\b',
    re.IGNORECASE,
)


def _find_marking(matrix: list[list], parent_dir: str = '') -> str:
    """Найти маркинг: 0) Baraka B-XXX pattern, 1) Customer:/CLIENTE:, 2) первая строка, 3) parent."""
    # 0) Highest-priority: паттерн "B-XXX" / "B - XXX" в первых 25 строках.
    # Такой маркинг заведомо принадлежит Baraka-клиенту и всегда точнее чем
    # SHIPPER-имя (напр. 'JESSANT FLOWERS' в SWARTON-файле, 'PKL FLORAL' в AQUA).
    for row in matrix[:25]:
        for cell in row:
            if not isinstance(cell, str):
                continue
            m = _MARKING_BARAKA_RE.search(cell.upper())
            if m:
                # Восстанавливаем каноничный формат 'B-XXX' (без пробелов вокруг '-')
                return normalize_marking(f'B-{m.group(1)}')
    # 1) Поиск "Customer: XXX" в любой ячейке
    for row in matrix[:20]:
        for i, cell in enumerate(row):
            if not isinstance(cell, str):
                continue
            m = _MARKING_FROM_CELL.match(cell.strip())
            if m and m.group(1).strip():
                return normalize_marking(m.group(1))
            # "Customer:" в одной ячейке, значение в соседней справа
            if re.fullmatch(r'\s*(?:Customer|Cliente|Client|Клиент)(?:\s+name)?\s*:?\s*',
                            cell, re.IGNORECASE):
                # ищем следующее непустое справа
                for j in range(i + 1, min(len(row), i + 5)):
                    v = row[j]
                    if isinstance(v, str) and v.strip():
                        return normalize_marking(v)
    # 2) Первая непустая ячейка в первых 3 строках, если это строка-заголовок
    for row in matrix[:3]:
        for cell in row:
            if isinstance(cell, str):
                s = cell.strip()
                if s and s.upper() == s and 2 <= len(s) <= 30 and 'PACKING' not in s.upper():
                    if not re.search(r'\d{6,}|@|http|LLC|CIA', s, re.IGNORECASE):
                        return normalize_marking(s)
    # 3) Fallback: из parent-каталога
    if parent_dir:
        base = Path(parent_dir).name
        # "1932 ALEX KAZAN" → "ALEX KAZAN"
        m = re.match(r'^\d+\s+(.+)$', base)
        if m:
            return normalize_marking(m.group(1))
        return normalize_marking(base)
    return ''


def _find_packing_no(matrix: list[list]) -> str:
    """Найти PACKING # NNNN / NUMBER INVOICE NNNN / № XXX в шапке."""
    for row in matrix[:20]:
        for cell in row:
            if not isinstance(cell, str):
                continue
            m = re.search(r'PACKING\s*#?\s*(\d{2,6})', cell, re.IGNORECASE)
            if m:
                return m.group(1)
    for row in matrix[:20]:
        for i, cell in enumerate(row):
            if isinstance(cell, str) and re.search(r'NUMBER\s*INVOICE|N[°o]\s*INVOICE',
                                                   cell, re.IGNORECASE):
                # значение обычно правее
                for j in range(i, min(len(row), i + 5)):
                    v = _as_number(row[j])
                    if v is not None:
                        return str(int(v)) if v == int(v) else str(v)
    return ''


def _find_awb(matrix: list[list]) -> str:
    for row in matrix[:20]:
        for cell in row:
            if isinstance(cell, str):
                awb = _detect_awb(cell)
                if awb:
                    return awb
    return ''


# --- Парсер формата "PRADO" (Sheet DETALLE, ROSAS 40 | 50 …) ---
# Row 10 = header "VARIEDAD | | MEDIDA | TALLOS | FULLES | ... | PESO"
_HDR_PRADO_RE = re.compile(r'VARIEDAD|MEDIDA|TALLOS', re.IGNORECASE)


def _try_parse_prado(matrix: list[list], sheet: str) -> Optional[list[dict]]:
    """Формат PRADO: строки [flower_name, '', length, stems, ...].

    Возвращает [{'variety','length','stems','boxes'}] или None.
    """
    hdr_row = None
    for r, row in enumerate(matrix[:20]):
        for cell in row:
            if isinstance(cell, str) and _HDR_PRADO_RE.search(cell):
                hdr_row = r
                break
        if hdr_row is not None:
            break
    if hdr_row is None:
        return None
    # Определяем колонки
    header = [str(c).strip().lower() if c else '' for c in matrix[hdr_row]]
    def _find_col(*names):
        for name in names:
            for i, h in enumerate(header):
                if name in h:
                    return i
        return None
    ci_var    = _find_col('variedad')
    ci_med    = _find_col('medida')
    ci_stems  = _find_col('tallos')
    ci_boxes  = _find_col('fulles', 'full', 'cajas')
    if ci_var is None or ci_stems is None:
        return None

    out = []
    current_flower = ''
    for row in matrix[hdr_row + 1:]:
        var_v = row[ci_var] if ci_var < len(row) else ''
        # "TOTAL ROSAS" → пропустить
        if isinstance(var_v, str) and 'TOTAL' in var_v.upper():
            continue
        if isinstance(var_v, str) and var_v.strip():
            current_flower = var_v.strip()
        med_v   = row[ci_med]   if ci_med   is not None and ci_med   < len(row) else ''
        stems_v = row[ci_stems] if ci_stems is not None and ci_stems < len(row) else ''
        boxes_v = row[ci_boxes] if ci_boxes is not None and ci_boxes < len(row) else ''
        stems_n = _as_number(stems_v)
        boxes_n = _as_number(boxes_v)
        length  = extract_length(med_v)
        if stems_n and stems_n > 0:
            out.append({
                'variety':  current_flower or (var_v if isinstance(var_v, str) else ''),
                'length':   length,
                'stems':    int(stems_n),
                'boxes':    float(boxes_n or 0),
                'farm':     '',
                'farm_invoice': '',
            })
    return out if out else None


# --- Парсер формата "VIVAX" (PACKING blocks per farm) ---
_HDR_VIVAX_RE = re.compile(r'FULL\s*BOXES|DESCRIPTION|GRADE|UNITS', re.IGNORECASE)


# Слова, которые НЕ являются farm (продолжение шапки в data-строках)
_VIVAX_FARM_BLACKLIST = frozenset({
    'BOX', 'BOXES', 'FARM', 'FARM NAME', 'PACKING', 'STEMS', 'STEMS PER',
    'STEMS PER BOX', 'TOTAL STEMS', 'TOTAL', 'BOX NO', 'BOX NO.', 'BOX NUMBER',
    'ACTUAL', 'ACTUAL WEIGHT', 'VOLUMETRIC', 'VOLUMETRIC WEIGHT', 'WEIGHT',
    'GRADE', 'LENGTH', 'VARIETY', 'DESCRIPTION',
})


def _try_parse_vivax(matrix: list[list], sheet: str) -> Optional[list[dict]]:
    hdr_row = None
    for r, row in enumerate(matrix[:30]):
        matches = sum(1 for c in row
                      if isinstance(c, str) and _HDR_VIVAX_RE.search(c))
        # Требуем ≥3 совпадений: real VIVAX даёт FB+Description+Grade+Units (3-4),
        # а Kenya RODEO/TOOCVETOK header 'Description|Grade|Box No|STEMS PER|TOTAL STEMS|FARM'
        # даёт только 2 (Description+Grade) — уходит в ALEX_HEADER.
        if matches >= 3:
            hdr_row = r
            break
    if hdr_row is None:
        return None
    header = [str(c).strip().lower() if c else '' for c in matrix[hdr_row]]
    def _find_col(*names):
        for name in names:
            for i, h in enumerate(header):
                if name in h:
                    return i
        return None
    ci_boxes  = _find_col('full boxes', 'boxes')
    ci_var    = _find_col('description', 'variety')
    ci_length = _find_col('grade', 'length')
    ci_stems  = _find_col('units', 'stems')
    if ci_var is None or ci_stems is None:
        return None
    out = []
    current_farm = ''
    saw_total = False
    for row in matrix[hdr_row + 1:]:
        var_v = row[ci_var] if ci_var < len(row) else ''
        # Проверяем: строка "FB from Farm:" или "Units from Farm:" → пропустить
        if isinstance(var_v, str) and ('FB from Farm' in var_v
                                       or 'Units from Farm' in var_v):
            continue
        # После TOTAL / Grand Total → перестаём читать (в VIVAX xls есть duplicate
        # блок ниже TOTAL с теми же цифрами, но пустой variety)
        row_blob = ' '.join(str(c) for c in row if c).upper()
        if re.search(r'\bTOTAL\b|GRAND\s+TOTAL|VARIETY\s+PACKING', row_blob):
            saw_total = True
        if saw_total:
            continue
        # farm обычно в колонке E (index 4) или отдельной ячейке БЕЗ boxes/units
        boxes_v = row[ci_boxes]  if ci_boxes  is not None and ci_boxes  < len(row) else ''
        stems_v = row[ci_stems]  if ci_stems  is not None and ci_stems  < len(row) else ''
        length_v = row[ci_length] if ci_length is not None and ci_length < len(row) else ''
        boxes_n = _as_number(boxes_v)
        stems_n = _as_number(stems_v)
        # data-строка: есть stems > 0 И непустая variety
        if stems_n and stems_n > 0:
            variety = str(var_v).strip() if isinstance(var_v, str) else ''
            if not variety:
                # пропускаем: это часть summary-блока
                continue
            out.append({
                'variety':  variety,
                'length':   extract_length(length_v),
                'stems':    int(stems_n),
                'boxes':    float(boxes_n or 0),
                'farm':     current_farm,
                'farm_invoice': '',
            })
            continue
        # farm-строка: variety не пустое, stems пусто, boxes пусто
        # или же farm в отдельной колонке (E), variety тоже пусто в D
        if isinstance(var_v, str) and var_v.strip() and not stems_n:
            current_farm = var_v.strip()
            continue
        # либо ищем farm в других колонках этой строки
        for j, v in enumerate(row):
            if j == ci_var:
                continue
            if isinstance(v, str) and v.strip() and not _as_number(v):
                # эвристика: слово all-caps ≥ 3 букв и не служебное
                s = v.strip()
                if (s.upper() == s and 3 <= len(s) <= 60
                        and not _HDR_VIVAX_RE.search(s)
                        and s.upper() not in _VIVAX_FARM_BLACKLIST):
                    current_farm = s
                    break
    return out if out else None


# --- Парсер формата "POPBES" (NL Sheet1: BoxNr | Pcs | Desciption | ... | Sort1 | Kwal | StatCode) ---
_HDR_POPBES_RE = re.compile(r'BOXNR|DESCIPTION|DESCRIPTION.*QUAN|STATCODE|STATDESC', re.IGNORECASE)


def _try_parse_popbes(matrix: list[list], sheet: str) -> Optional[list[dict]]:
    """POPBES NL-инвойс. Header row = row 0 или где встречается BoxNr+Desciption.

    Colonnes: BoxNr, Pack Descr, Wgt, Pcs, Desciption, Price, TotalPrice, Remarks,
              Producer, Remark2, Sort1 (length 'Cm: NN Cm'), ..., Kwal, StatCode
    """
    hdr_row = None
    for r, row in enumerate(matrix[:6]):
        matches = sum(
            1 for c in row
            if isinstance(c, str) and re.search(
                r'\b(BoxNr|Desciption|Pack Descr|StatCode|StatDesc)\b', c, re.IGNORECASE)
        )
        if matches >= 2:
            hdr_row = r
            break
    if hdr_row is None:
        return None
    header = [str(c).strip().lower() if c else '' for c in matrix[hdr_row]]

    def _find_col(*names):
        for name in names:
            for i, h in enumerate(header):
                if name in h:
                    return i
        return None

    ci_pcs   = _find_col('pcs', 'quan')
    ci_desc  = _find_col('desciption', 'description')
    ci_boxnr = _find_col('boxnr')
    ci_sort1 = _find_col('sort1')
    ci_prod  = _find_col('producer', 'grower')
    ci_stat  = _find_col('statdesc', 'statcode')
    if ci_desc is None or ci_pcs is None:
        return None

    out = []
    seen_boxes = set()
    for row in matrix[hdr_row + 1:]:
        desc_v = row[ci_desc] if ci_desc < len(row) else ''
        pcs_v  = row[ci_pcs]  if ci_pcs  < len(row) else ''
        pcs_n  = _as_number(pcs_v)
        if not pcs_n or pcs_n <= 0:
            continue
        variety = str(desc_v).strip() if desc_v else ''
        if not variety:
            continue
        length = 0
        if ci_sort1 is not None and ci_sort1 < len(row):
            length = extract_length(row[ci_sort1])
        boxnr = _as_number(row[ci_boxnr]) if ci_boxnr is not None and ci_boxnr < len(row) else None
        # Каждый BoxNr считаем один раз (multiple items per box == 1 box)
        boxes_delta = 0.0
        if boxnr is not None and boxnr not in seen_boxes:
            seen_boxes.add(boxnr)
            boxes_delta = 1.0
        producer = ''
        if ci_prod is not None and ci_prod < len(row):
            producer = str(row[ci_prod] or '').strip()
        # Если variety не распознан по abbrev, попробуем StatDesc ("Chrysanten", "Anjers", "Lelie's")
        stat = ''
        if ci_stat is not None and ci_stat < len(row):
            stat = str(row[ci_stat] or '').strip()
        out.append({
            'variety':      variety,
            'length':       length,
            'stems':        int(pcs_n),
            'boxes':        boxes_delta,
            'farm':         producer,
            'farm_invoice': '',
            'stat_hint':    stat,
        })
    return out if out else None


# --- Парсер формата "PRADZ / GOLDMAN / Astra Fund" (NL: Kolli | Inh. | Aantal | Omschrijving | Grower | S1 | S2 | Statcod) ---
def _try_parse_nl_kolli(matrix: list[list], sheet: str) -> Optional[list[dict]]:
    """Формат Kolli/Inh./Aantal/Omschrijving/Grower/S1/S2/Statcod.

    Kolli = boxes, Inh. = stems/box, Aantal = total stems, S1 = length.
    Также поддерживает MAX12 (Amount|Content|Total|Description|S1) со сдвигом колонок.
    Также Astra Fund (Quantity|Product|S1|...|Box) и ProForma (qua|cnt|total|VBN|sub deb|description|grower).
    """
    hdr_row = None
    hdr_kind = ''
    for r, row in enumerate(matrix[:30]):
        cells = [str(c).strip().lower() if c else '' for c in row]
        has_kolli   = any('kolli' == c or 'colli' == c for c in cells)
        has_omsch   = any('omschrijving' == c or 'description' == c for c in cells)
        has_aantal  = any('aantal' == c for c in cells)
        has_amount  = any('amount' == c or 'total' == c for c in cells)
        has_s1      = any(c == 's1' for c in cells)
        has_content = any('content' == c for c in cells)
        has_product = any('product' == c for c in cells)
        has_quantity = any('quantity' == c for c in cells)
        has_qua     = any(c == 'qua' for c in cells)
        if has_kolli and has_omsch and has_s1:
            hdr_row = r
            hdr_kind = 'kolli'
            break
        if has_amount and has_content and has_s1:
            hdr_row = r
            hdr_kind = 'max12'
            break
        if has_product and has_s1 and has_quantity:
            hdr_row = r
            hdr_kind = 'astra'
            break
        if has_qua and has_omsch and has_amount:
            hdr_row = r
            hdr_kind = 'proforma'
            break
    if hdr_row is None:
        return None
    header = [str(c).strip().lower() if c else '' for c in matrix[hdr_row]]

    def _find_col_exact(*names):
        for name in names:
            for i, h in enumerate(header):
                if h == name:
                    return i
        return None

    def _find_col(*names):
        for name in names:
            for i, h in enumerate(header):
                if name in h and h:
                    return i
        return None

    if hdr_kind == 'kolli':
        ci_boxes  = _find_col_exact('kolli', 'colli')
        ci_stem_per_box = _find_col_exact('inh.', 'inh', 'content')
        ci_total  = _find_col_exact('aantal', 'total')
        ci_var    = _find_col_exact('omschrijving', 'description')
        ci_s1     = _find_col_exact('s1')
        ci_grower = _find_col_exact('grower', 'producer')
        ci_stat   = _find_col('statcod')
    elif hdr_kind == 'astra':
        ci_boxes  = None
        ci_stem_per_box = None
        ci_total  = _find_col_exact('quantity')
        ci_var    = _find_col_exact('product')
        ci_s1     = _find_col_exact('s1')
        ci_grower = None
        ci_stat   = None
    elif hdr_kind == 'proforma':
        ci_boxes  = _find_col_exact('qua')
        ci_stem_per_box = _find_col_exact('cnt')
        ci_total  = _find_col_exact('total')
        ci_var    = _find_col_exact('omschrijving', 'description')
        ci_s1     = None
        ci_grower = _find_col_exact('grower', 'producer')
        ci_stat   = None
    else:  # max12
        ci_boxes  = _find_col_exact('amount')
        ci_stem_per_box = _find_col_exact('content')
        ci_total  = _find_col_exact('total')
        ci_var    = _find_col_exact('description')
        ci_s1     = _find_col_exact('s1')
        ci_grower = _find_col_exact('grower', 'producer')
        ci_stat   = _find_col('statcod', 'ctry')
    if ci_var is None or ci_total is None:
        return None

    out = []
    for row in matrix[hdr_row + 1:]:
        var_v = row[ci_var] if ci_var < len(row) else ''
        total_v = row[ci_total] if ci_total < len(row) else ''
        total_n = _as_number(total_v)
        if not total_n or total_n <= 0:
            continue
        # Sanity: stems > 500_000 = вероятно HS-код или ArtCode прочитанный как stems
        if total_n > 500_000:
            continue
        variety = str(var_v).strip() if var_v else ''
        if not variety:
            continue
        # Отбрасываем строки "TOTAL" / "Subtotal"
        if re.search(r'\b(total|subtotal)\b', variety, re.IGNORECASE):
            continue
        # Отбрасываем summary-строки где variety = только сводное имя (Chrysanten/Anjers/Lelie's/Rozen)
        # без сорта (без capital letter в середине)
        if variety.lower() in {'chrysanten', 'anjers', 'rozen', 'roses', "lelie's",
                                'lelies', 'diversen', 'gerbera', 'lisianthus'}:
            continue
        length = 0
        if ci_s1 is not None and ci_s1 < len(row):
            length = extract_length(row[ci_s1])
        boxes_n = _as_number(row[ci_boxes]) if ci_boxes is not None and ci_boxes < len(row) else 0
        farm = ''
        if ci_grower is not None and ci_grower < len(row):
            farm = str(row[ci_grower] or '').strip()
        stat = ''
        if ci_stat is not None and ci_stat < len(row):
            stat = str(row[ci_stat] or '').strip()
        out.append({
            'variety':      variety,
            'length':       length,
            'stems':        int(total_n),
            'boxes':        float(boxes_n or 0),
            'farm':         farm,
            'farm_invoice': '',
            'stat_hint':    stat,
        })
    return out if out else None


# --- Парсер формата "BESTFLORA-2" (Boxes | Description | Grower | ↕ cm | Gram | Total stems | Price) ---
def _try_parse_bestflora(matrix: list[list], sheet: str) -> Optional[list[dict]]:
    """BESTFLORA-инвойс eFlowers B.V.

    Header row: [_, _, 'Boxes', 'Description', _, _, _, 'Grower', _, '↕ cm', 'Gram',
                 'Total stems', 'Price', 'Value €']
    """
    hdr_row = None
    for r, row in enumerate(matrix[:25]):
        cells = [str(c).strip().lower() if c else '' for c in row]
        has_boxes = any('boxes' == c for c in cells)
        has_desc  = any('description' == c for c in cells)
        has_total = any('total stems' in c for c in cells)
        if has_boxes and has_desc and has_total:
            hdr_row = r
            break
    if hdr_row is None:
        return None
    header = [str(c).strip().lower() if c else '' for c in matrix[hdr_row]]

    def _find_col_exact(*names):
        for name in names:
            for i, h in enumerate(header):
                if h == name:
                    return i
        return None

    def _find_col(*names):
        for name in names:
            for i, h in enumerate(header):
                if name in h and h:
                    return i
        return None

    ci_boxes  = _find_col_exact('boxes')
    ci_var    = _find_col_exact('description')
    ci_grower = _find_col_exact('grower')
    ci_len    = _find_col('cm')            # '↕ cm'
    ci_stems  = _find_col('total stems')
    if ci_var is None or ci_stems is None:
        return None

    out = []
    for row in matrix[hdr_row + 1:]:
        var_v = row[ci_var] if ci_var < len(row) else ''
        stems_n = _as_number(row[ci_stems]) if ci_stems < len(row) else 0
        if not stems_n or stems_n <= 0:
            continue
        variety = str(var_v).strip() if var_v else ''
        if not variety or re.search(r'\btotal\b', variety, re.IGNORECASE):
            continue
        length = 0
        if ci_len is not None and ci_len < len(row):
            length = extract_length(row[ci_len])
        boxes_n = _as_number(row[ci_boxes]) if ci_boxes is not None and ci_boxes < len(row) else 0
        farm = ''
        if ci_grower is not None and ci_grower < len(row):
            farm = str(row[ci_grower] or '').strip()
        out.append({
            'variety':      variety,
            'length':       length,
            'stems':        int(stems_n),
            'boxes':        float(boxes_n or 0),
            'farm':         farm,
            'farm_invoice': '',
        })
    return out if out else None


# --- Парсер формата "bestflora_dc_cargo" (Box type | Quantity | Type | Species | Length,cm | ST | Seller | Box mark) ---
def _try_parse_dc_cargo(matrix: list[list], sheet: str) -> Optional[list[dict]]:
    """M-Flowers.com truck-manifest: одноуровневая таблица с явным Type=flower и Species=variety."""
    hdr_row = None
    for r, row in enumerate(matrix[:15]):
        cells = [str(c).strip().lower() if c else '' for c in row]
        if 'box type' in cells and 'species' in cells and 'st' in cells:
            hdr_row = r
            break
    if hdr_row is None:
        return None
    header = [str(c).strip().lower() if c else '' for c in matrix[hdr_row]]

    def _find_col_exact(*names):
        for name in names:
            for i, h in enumerate(header):
                if h == name:
                    return i
        return None

    ci_qty     = _find_col_exact('quantity')
    ci_type    = _find_col_exact('type')
    ci_species = _find_col_exact('species')
    ci_len     = _find_col_exact('length, cm', 'length,cm', 'length')
    ci_stems   = _find_col_exact('st')
    ci_seller  = _find_col_exact('seller')
    ci_mark    = _find_col_exact('box mark')
    if ci_species is None or ci_stems is None:
        return None

    out = []
    for row in matrix[hdr_row + 1:]:
        stems_n = _as_number(row[ci_stems]) if ci_stems < len(row) else 0
        if not stems_n or stems_n <= 0:
            continue
        variety = str(row[ci_species]).strip() if ci_species < len(row) else ''
        flower_hint = str(row[ci_type]).strip() if ci_type is not None and ci_type < len(row) else ''
        length = extract_length(row[ci_len]) if ci_len is not None and ci_len < len(row) else 0
        boxes_n = _as_number(row[ci_qty]) if ci_qty is not None and ci_qty < len(row) else 0
        farm = str(row[ci_seller]).strip() if ci_seller is not None and ci_seller < len(row) else ''
        marking = str(row[ci_mark]).strip() if ci_mark is not None and ci_mark < len(row) else ''
        out.append({
            'variety':      variety or flower_hint,
            'length':       length,
            'stems':        int(stems_n),
            'boxes':        float(boxes_n or 0),
            'farm':         farm,
            'farm_invoice': '',
            'marking_hint': marking,
            'stat_hint':    flower_hint,
        })
    return out if out else None


# --- Парсер формата "RUFLORA" (invoice-style с header BOX FARM VARIETY SIZE STEMS) ---

def _try_parse_ruflora(matrix: list[list], sheet: str) -> Optional[list[dict]]:
    """RUFLORA/BUTA-стиль: строка-заголовок 'BOX FARM VARIETY SIZE STEMS ...'.

    Формат:
      col 0=BOX (sticky), col 1=FARM, col 2=VARIETY, col 3=SIZE (length),
      col 4=STEMS, col 5=UNIT_PRICE, col 6=TOTAL_PRICE.
    """
    header_row = -1
    for r, row in enumerate(matrix[:35]):
        cells = [str(c).strip().upper() for c in row[:8]]
        # Ищем последовательность BOX ... FARM ... VARIETY ... SIZE ... STEMS
        joined = '|'.join(cells)
        if ('BOX' in cells and 'FARM' in cells and 'VARIETY' in cells
                and 'STEMS' in cells and ('SIZE' in cells or 'LENGTH' in cells)):
            header_row = r
            break
    if header_row < 0:
        return None

    # Определяем индексы колонок
    hdr = [str(c).strip().upper() for c in matrix[header_row]]

    def _col(*names):
        for i, c in enumerate(hdr):
            if c in names:
                return i
        return -1

    ci_box = _col('BOX')
    ci_farm = _col('FARM')
    ci_var = _col('VARIETY')
    ci_size = _col('SIZE', 'LENGTH')
    ci_stems = _col('STEMS', 'TALLOS')
    if -1 in (ci_farm, ci_var, ci_size, ci_stems):
        return None

    out = []
    current_farm = ''
    for r in range(header_row + 1, len(matrix)):
        row = matrix[r]
        if len(row) <= max(ci_var, ci_stems):
            continue
        var_v = row[ci_var]
        stems_n = _as_number(row[ci_stems])
        # Стоп: TOTAL INVOICE / TOTAL BOX / пустая variety
        var_s = str(var_v).strip() if var_v else ''
        if not var_s or var_s.upper().startswith('TOTAL'):
            # После первой TOTAL-строки прерываем
            if out and re.match(r'TOTAL', var_s, re.IGNORECASE):
                break
            continue
        if not stems_n or stems_n <= 0 or stems_n > 500_000:
            continue
        # Sticky farm
        farm_v = row[ci_farm] if ci_farm < len(row) else ''
        farm_s = str(farm_v).strip() if farm_v else ''
        if farm_s:
            current_farm = farm_s
        length = extract_length(row[ci_size]) if ci_size < len(row) else 0
        boxes_n = _as_number(row[ci_box]) if ci_box >= 0 and ci_box < len(row) else 0
        out.append({
            'variety':      var_s,
            'length':       length,
            'stems':        int(stems_n),
            'boxes':        float(boxes_n) if boxes_n else 0.0,
            'farm':         current_farm,
            'farm_invoice': '',
        })
    return out if out else None


# --- Парсер формата "EC_WIDE" (Ecuador packing: FB | FARM | VARIETY | 40cm | 50cm ...) ---
_LEN_COL_RE = re.compile(r'^(?:st\s*)?(\d{2,3})(?:\s*cm)?\+?$', re.IGNORECASE)


def _try_parse_ec_wide(matrix: list[list], sheet: str) -> Optional[list[dict]]:
    """Wide-формат Ecuador: длина как заголовок колонки, стебли — в ячейке.

    Ищем header-row где встречается 'VARIETY' + минимум 2 length-колонки
    (`40cm`, `St 40`, `50cm` и т.п.). В data-строках: FARM sticky, VARIETY
    обязательна, STEMS = sum по length-колонкам, при этом каждая непустая
    length-ячейка → отдельный item со своей длиной.

    Также поддерживает формат с 'Type-Sort-Size-Price' вместо VARIETY +
    'Stems Total' (тогда длина извлекается из значения ячейки).
    """
    header_row = -1
    for r, row in enumerate(matrix[:20]):
        cells = [str(c).strip() for c in row[:25]]
        cells_up = [c.upper() for c in cells]
        has_variety = any(c in ('VARIETY', 'SORT') for c in cells_up)
        has_type_sort = any('TYPE-SORT' in c or 'TYPE SORT' in c for c in cells_up)
        if not (has_variety or has_type_sort):
            continue
        len_cols = sum(1 for c in cells if _LEN_COL_RE.match(c))
        if len_cols >= 2:
            header_row = r
            break
    if header_row < 0:
        return None

    hdr = [str(c).strip() for c in matrix[header_row]]
    hdr_up = [c.upper() for c in hdr]

    def _col(*names):
        for i, c in enumerate(hdr_up):
            if c in names:
                return i
        return -1

    ci_var = _col('VARIETY', 'SORT')
    ci_farm = _col('FARM', 'FARMS', 'GROWER', 'PRODUCER')
    ci_buyer = _col('BUYER', 'CLIENT', 'CUSTOMER')
    ci_type_sort = _col('TYPE-SORT-SIZE-PRICE', 'TYPE-SORT-SIZE')
    ci_stems_total = _col('STEMS TOTAL', 'TOTAL STEMS', 'STEMS')
    # Length-колонки: (col_index, length_int)
    len_cols: list[tuple[int, int]] = []
    for i, c in enumerate(hdr):
        m = _LEN_COL_RE.match(c.strip())
        if m:
            len_cols.append((i, int(m.group(1))))
    # Fallback variety: если ci_var==-1, но есть type_sort — используем как variety
    if ci_var < 0 and ci_type_sort < 0:
        return None
    if not len_cols and ci_stems_total < 0:
        return None

    out = []
    current_farm = ''
    for row in matrix[header_row + 1:]:
        if len(row) <= max(ci_var if ci_var >= 0 else 0, ci_stems_total if ci_stems_total >= 0 else 0):
            continue
        # Sticky FARM
        if ci_farm >= 0 and ci_farm < len(row):
            fv = str(row[ci_farm] or '').strip()
            if fv and not re.match(r'^total|^grand', fv, re.IGNORECASE):
                current_farm = fv
        # Variety
        if ci_var >= 0 and ci_var < len(row):
            variety = str(row[ci_var] or '').strip()
        elif ci_type_sort >= 0 and ci_type_sort < len(row):
            # 'Rose-Mondial-60-$0.30' → 'Mondial' + length=60
            variety = str(row[ci_type_sort] or '').strip()
        else:
            variety = ''
        if not variety:
            continue
        if variety.upper().startswith(('TOTAL', 'GRAND', 'SUBTOTAL')):
            continue

        # Buyer/marking hint
        marking_hint = ''
        if ci_buyer >= 0 and ci_buyer < len(row):
            mv = str(row[ci_buyer] or '').strip()
            if mv:
                marking_hint = mv

        # Извлечь variety+length из "Type-Sort-Size-Price" ('Rose-Mondial-60-$0.30')
        parsed_len = 0
        if ci_type_sort >= 0 and ci_var < 0:
            parts = variety.split('-')
            if len(parts) >= 3:
                variety = parts[1].strip() or variety
                try:
                    parsed_len = int(re.sub(r'\D', '', parts[2]) or 0)
                except ValueError:
                    parsed_len = 0

        if len_cols:
            # По одной item на каждую length-колонку с ненулевым stems
            for ci, length in len_cols:
                if ci >= len(row):
                    continue
                stems_n = _as_number(row[ci])
                if not stems_n or stems_n <= 0 or stems_n > 500_000:
                    continue
                out.append({
                    'variety':      variety,
                    'length':       length,
                    'stems':        int(stems_n),
                    'boxes':        0.0,
                    'farm':         current_farm,
                    'farm_invoice': '',
                    'marking_hint': marking_hint,
                })
        else:
            # Один item на строку через Stems Total + parsed_len
            stems_n = _as_number(row[ci_stems_total]) if ci_stems_total < len(row) else 0
            if not stems_n or stems_n <= 0 or stems_n > 500_000:
                continue
            out.append({
                'variety':      variety,
                'length':       parsed_len,
                'stems':        int(stems_n),
                'boxes':        0.0,
                'farm':         current_farm,
                'farm_invoice': '',
                'marking_hint': marking_hint,
            })
    return out if out else None


# --- Парсер формата "ZAIC" (без header row: CLIENT | LOGISTICS | DATE | AWB | FARM | VARIETY | TYPE | LEN | ... | STEMS) ---
def _try_parse_zaic(matrix: list[list], sheet: str) -> Optional[list[dict]]:
    """ZAIC-style: каждая строка — самодостаточная запись без header.

    Определение: col0 = fixed short client marker ('ZAIC'/'ZFR'/...),
    col1 текстовое ('FRESH LOGISTICS'), col3 — AWB-like ('235-8902 3443'),
    col5 — VARIETY (сорт), col6 — flower-type ('ROSE'), col7 — length numeric,
    col10 или col-last — stems.

    Guard: ≥3 подряд идущие строки с одинаковым col0 и AWB-паттерном в col3.
    """
    # Сначала проверяем гид (10 первых строк)
    if len(matrix) < 3:
        return None
    # Кандидат col0 = первое непустое строковое значение
    col0_seen = None
    ok_rows = 0
    for row in matrix[:8]:
        if len(row) < 8:
            continue
        c0 = str(row[0] or '').strip()
        c3 = str(row[3] or '').strip() if len(row) > 3 else ''
        if not c0 or len(c0) > 15:
            continue
        # AWB-подобное: NNN-NNNN NNNN или NNN-NNNNNNNN
        if not re.search(r'\d{3}[-\s]\d{4}[-\s]\d{4}|\d{3}-\d{7,8}', c3):
            continue
        if col0_seen is None:
            col0_seen = c0.upper()
        elif c0.upper() != col0_seen:
            continue
        ok_rows += 1
    if ok_rows < 2 or not col0_seen:
        return None

    # Определяем col_stems: последняя числовая колонка (обычно col 10 или 11)
    # Пробуем sample row
    sample = None
    for row in matrix:
        if len(row) >= 8 and str(row[0] or '').strip().upper() == col0_seen:
            sample = row
            break
    if sample is None:
        return None
    # Ищем stems: numeric column после col 7 (length)
    ci_stems = -1
    for ci in range(min(len(sample) - 1, 12), 7, -1):
        v = _as_number(sample[ci])
        if v and 5 <= v <= 500_000:
            ci_stems = ci
            break
    if ci_stems < 0:
        return None

    out = []
    for row in matrix:
        if len(row) < ci_stems + 1:
            continue
        c0 = str(row[0] or '').strip().upper()
        if c0 != col0_seen:
            continue
        farm    = str(row[4] or '').strip() if len(row) > 4 else ''
        variety = str(row[5] or '').strip() if len(row) > 5 else ''
        if not variety:
            continue
        # flower type hint: col 6 ('ROSE', 'CHRYSANTHEMUM', etc.)
        flower_hint = str(row[6] or '').strip() if len(row) > 6 else ''
        # length: col 7 (numeric)
        length = 0
        len_v = _as_number(row[7]) if len(row) > 7 else None
        if len_v and 15 <= len_v <= 150:
            length = int(len_v)
        stems_n = _as_number(row[ci_stems])
        if not stems_n or stems_n <= 0 or stems_n > 500_000:
            continue
        out.append({
            'variety':      variety,
            'length':       length,
            'stems':        int(stems_n),
            'boxes':        0.0,
            'farm':         farm,
            'farm_invoice': '',
            'marking_hint': col0_seen,     # 'ZAIC' → marking
            'stat_hint':    flower_hint,   # 'ROSE' → flower classifier hint
        })
    return out if out else None


# --- Парсер формата "333MBK Colombia" / header-based Alex ---
# Пример шапки: № | Plantation | W.h | Variety | Length | Boxes | Packing | Units
# Ключи заголовков (все case-insensitive, matched by substring or exact):
_ALEX_HDR_KEYS = {
    'farm':    ('plantation', 'farms', 'farm', 'grower', 'producer', 'shipper', 'finca'),
    'var':     ('variety', 'variedad', 'sort', 'description', 'product', 'omschrijving'),
    'length':  ('length', 'lenght',   # PKL FLORAL / SWARTON — opechatka LENGHT
                'largo', 'medida', 'talla', 'grade', 'size', ' cm', 'cm ', '↕'),
    'boxes':   ('full boxes', 'boxes', 'cajas', 'kolli', 'colli', 'total boxes'),
    'perbox':  ('packing', 'per box', 'per_box', 'stems/box', 'units/box', 'inh.', 'inh', 'content', 'pcs/box', 'st/bunch'),
    'stems':   ('stems per box', 'stems per',    # 7471 Kenya: per-row stems (предпочтительнее total stems, т.к. total — только на последней строке бокса)
                'total units', 't.units', 't. units', 'total stems', 'stems total',
                'units', 'stems', 'tallos', 'aantal', 'quantity', 'pcs', 'total',
                'штук', 'штуки', 'кол-во', 'q-ty', 'qty'),
    'inv':     ('invoice', 'factura', 'inv nr', 'inv.nr', 'invoice nr', 'invoice#', 'invoice #'),
    # 'type' — для fallback извлечения length из Product-строки (PKL FLORAL 'ROSES 60 CM'):
    'type':    ('product', 'pices type', 'pieces type', 'flower type', 'type'),
}


def _try_parse_alex_header(matrix: list[list], sheet: str) -> Optional[list[dict]]:
    """Header-based packing (333MBK Colombia и подобные многоколонные форматы).

    Ищет header-row где явно присутствуют VARIETY + (STEMS/UNITS или BOXES+PACKING).
    Определяет farm/variety/length/boxes/packing/units по названиям колонок.
    stems = ci_units либо boxes × per_box если ci_units нет/пустая.
    farm sticky вниз по колонке Plantation.
    """
    def _find_col(header_low: list[str], names: tuple[str, ...]) -> int:
        # Exact match первым, потом substring
        for name in names:
            for i, h in enumerate(header_low):
                if h == name:
                    return i
        for name in names:
            for i, h in enumerate(header_low):
                if name in h:
                    return i
        return -1

    header_row = -1
    ci = {}
    data_start = -1
    # Пробегаем до 40 верхних строк: Kenya RODEO/TOOCVETOK имеют header на строке 22.
    for r, row in enumerate(matrix[:40]):
        header_low = [str(c or '').strip().lower() for c in row]
        if not any(header_low):
            continue
        c_var    = _find_col(header_low, _ALEX_HDR_KEYS['var'])
        c_stems  = _find_col(header_low, _ALEX_HDR_KEYS['stems'])
        c_boxes  = _find_col(header_low, _ALEX_HDR_KEYS['boxes'])
        c_perbox = _find_col(header_low, _ALEX_HDR_KEYS['perbox'])
        # Guard: variety обязательна, и либо stems, либо (boxes И per-box) для fallback
        if c_var < 0:
            continue
        if c_stems < 0 and (c_boxes < 0 or c_perbox < 0):
            continue
        # (Ранее был guard `recognized < 3` — он отбрасывал Kenya 7471 header,
        # где есть только VARIETY+STEMS PER. Снял: var+stems — уже надёжная сигнатура.)

        # Multi-row header merge (PKL FLORAL Ecuador: r19 'TOTAL' + r20 'UNITS' → 'TOTAL UNITS'):
        # если следующая строка содержит короткие текстовые ячейки (не числа) — мерджим в header.
        _data_start = r + 1
        if r + 1 < len(matrix):
            next_row = matrix[r + 1]
            next_low = [str(c or '').strip().lower() for c in next_row]
            non_empty_next = [c for c in next_low if c]
            has_num = any(_as_number(c) is not None for c in next_row if c)
            if (not has_num and non_empty_next
                    and all(len(c) <= 25 for c in non_empty_next)
                    and len(non_empty_next) <= max(1, sum(1 for c in header_low if c))):
                merged_low = []
                for i in range(max(len(header_low), len(next_low))):
                    a = header_low[i] if i < len(header_low) else ''
                    b = next_low[i] if i < len(next_low) else ''
                    if a and b:
                        merged_low.append(f'{a} {b}')
                    else:
                        merged_low.append(a or b)
                # Пересчитываем колонки на merged; применяем только если улучшилось покрытие
                c_var_m    = _find_col(merged_low, _ALEX_HDR_KEYS['var'])
                c_stems_m  = _find_col(merged_low, _ALEX_HDR_KEYS['stems'])
                c_boxes_m  = _find_col(merged_low, _ALEX_HDR_KEYS['boxes'])
                c_perbox_m = _find_col(merged_low, _ALEX_HDR_KEYS['perbox'])
                # Улучшение = больше recognized column ILI stems column сместилась на более специфичную (total units > stems)
                improved = (
                    sum(1 for x in (c_var_m, c_stems_m, c_boxes_m, c_perbox_m) if x >= 0)
                    > sum(1 for x in (c_var, c_stems, c_boxes, c_perbox) if x >= 0)
                    or (c_stems_m >= 0 and c_stems_m != c_stems
                        and 'total' in merged_low[c_stems_m])
                )
                if improved:
                    header_low = merged_low
                    c_var, c_stems, c_boxes, c_perbox = c_var_m, c_stems_m, c_boxes_m, c_perbox_m
                    _data_start = r + 2

        header_row = r
        data_start = _data_start
        ci['var']    = c_var
        ci['stems']  = c_stems
        ci['boxes']  = c_boxes
        ci['perbox'] = c_perbox
        ci['farm']   = _find_col(header_low, _ALEX_HDR_KEYS['farm'])
        ci['length'] = _find_col(header_low, _ALEX_HDR_KEYS['length'])
        ci['inv']    = _find_col(header_low, _ALEX_HDR_KEYS['inv'])
        ci['type']   = _find_col(header_low, _ALEX_HDR_KEYS['type'])
        # Суб-маркинг в col 0 (Kenya 7471: BELLAROSE / FIORITIS / ORKSEY39 / FASTX в первой колонке,
        # если сама header-ячейка col 0 пуста — то в data-строках там суб-marking).
        ci['group'] = 0 if (0 < len(matrix[r])
                            and not str(matrix[r][0] or '').strip()) else -1
        break
    if header_row < 0:
        return None

    out: list[dict] = []
    current_farm = ''
    current_inv = ''
    saw_data = False
    # Плоский набор всех ключей для отсева header-литералов из data-строк
    _all_hdr_keys = set()
    for _tup in _ALEX_HDR_KEYS.values():
        for _k in _tup:
            _all_hdr_keys.add(_k.strip().lower())
    for row in matrix[data_start:]:
        if not row:
            continue
        # Стоп-строки: TOTAL / Grand Total / TRANSPORT / Subtotal
        blob = ' '.join(str(c) for c in row if c).upper()
        if re.search(r'\bTOTAL\b|GRAND\s+TOTAL|SUBTOTAL|TRANSPORT\s+TO', blob):
            # Читаем ниже эту строку не пробуем (данные закончились)
            if saw_data:
                break
            continue

        # Row-level sub-marking из col 0 (Kenya 7471: BELLAROSE / FIORITIS / ORKSEY39 / FASTX)
        marking_hint = ''
        if ci['group'] >= 0 and ci['group'] < len(row):
            gv = row[ci['group']]
            gs = str(gv).strip() if gv else ''
            if gs and gs.lower() not in _all_hdr_keys and _as_number(gs) is None:
                # Стрипаем технические суффиксы: 'FASTX (PODKOVA)' → 'FASTX', 'ORKSEY39 S.O' → 'ORKSEY39'
                gs = re.sub(r'\s*\([^)]*\)\s*$', '', gs)
                gs = re.sub(r'\s+S\.\s*O\.?\s*$', '', gs, flags=re.IGNORECASE)
                marking_hint = gs.strip()

        var_v = row[ci['var']] if ci['var'] < len(row) else ''
        variety = str(var_v).strip() if var_v else ''
        # Sticky farm: если непустое значение в колонке Plantation — обновить
        if ci['farm'] >= 0 and ci['farm'] < len(row):
            fv = row[ci['farm']]
            fs = str(fv).strip() if fv else ''
            # Игнорируем заголовочные значения (Plantation/Farm/…)
            if fs and fs.lower() not in _ALEX_HDR_KEYS['farm']:
                # Также игнорируем чисто-числовые (это может быть № строки)
                if _as_number(fs) is None:
                    current_farm = fs
        # Sticky invoice number
        if ci['inv'] >= 0 and ci['inv'] < len(row):
            iv = _as_number(row[ci['inv']])
            if iv is not None and iv > 0:
                current_inv = str(int(iv)) if iv == int(iv) else str(iv)

        if not variety:
            continue
        # Пропустить строки-подзаголовки (variety = литерал из ключей)
        if variety.lower() in sum(_ALEX_HDR_KEYS.values(), ()):
            continue

        # stems: сначала явная колонка, потом fallback boxes*per_box
        stems_n: Optional[float] = None
        if ci['stems'] >= 0 and ci['stems'] < len(row):
            stems_n = _as_number(row[ci['stems']])
        boxes_n = _as_number(row[ci['boxes']]) if ci['boxes'] >= 0 and ci['boxes'] < len(row) else None
        perbox_n = _as_number(row[ci['perbox']]) if ci['perbox'] >= 0 and ci['perbox'] < len(row) else None
        if (not stems_n or stems_n <= 0) and boxes_n and perbox_n and boxes_n > 0 and perbox_n > 0:
            stems_n = boxes_n * perbox_n
        if not stems_n or stems_n <= 0 or stems_n > 500_000:
            continue

        length = 0
        if ci['length'] >= 0 and ci['length'] < len(row):
            length = extract_length(row[ci['length']])
        if length == 0:
            # Fallback: length из Product/Type колонки (PKL FLORAL: 'ROSES 60 CM')
            # или из самой variety-ячейки ('EXPLORER 60cm')
            for idx in (ci.get('type', -1), ci['var']):
                if idx is None or idx < 0 or idx >= len(row):
                    continue
                L = extract_length(row[idx])
                if L > 0:
                    length = L
                    break

        out.append({
            'variety':      variety,
            'length':       length,
            'stems':        int(stems_n),
            'boxes':        float(boxes_n or 0),
            'farm':         current_farm,
            'farm_invoice': current_inv,
            'marking_hint': marking_hint,
        })
        saw_data = True
    return out if out else None


# --- Парсер формата "Alex Kazan" (простая таблица) ---
def _try_parse_alex(matrix: list[list], sheet: str) -> Optional[list[dict]]:
    """Формат: A=farm(sticky), B=farm_invoice(sticky), C=boxes, D=variety, E=length, F=stems.

    Пропускаем шапку (первые 8 строк ~ метаданные), ищем первую строку где
    есть непустая variety в D и число в F.
    """
    # Guard: Alex Kazan имеет характерную шапку "PACKING" в первых 5 строках
    # или "FARM|VARIETY|LENGTH|STEMS" в header row, или Ecuador-инвойс с
    # 'DATE OF SHIPMENT / AWB NUMBER / NUMBER INVOICE' (без явного PACKING).
    blob_top = '\n'.join(
        ' '.join(str(c) for c in row if c) for row in matrix[:12]
    ).upper()
    has_pack_header = 'PACKING' in blob_top
    has_alex_columns = bool(re.search(
        r'(FARM|SHIPPER)\s+.*?(VARIETY|SORT).*?(LENGTH|STEMS)', blob_top, re.DOTALL
    )) or bool(re.search(r'VARIETY.*STEMS|SORT.*STEMS', blob_top))
    has_ecu_shipment = bool(re.search(
        r'DATE\s+OF\s+SHIPMENT|AWB\s+NUMBER|NUMBER\s+INVOICE', blob_top
    ))
    if not (has_pack_header or has_alex_columns or has_ecu_shipment):
        return None

    out = []
    current_farm = ''
    current_inv = ''
    data_started = False
    for r, row in enumerate(matrix):
        if len(row) < 6:
            continue
        farm_v, inv_v, boxes_v, var_v, len_v, stems_v = row[0], row[1], row[2], row[3], row[4], row[5]
        stems_n = _as_number(stems_v)
        # Sanity: не HS-код
        if stems_n and stems_n > 500_000:
            continue
        # Строка "TOTAL / TOTAL full" → стоп
        if isinstance(inv_v, str) and 'TOTAL' in inv_v.upper():
            break
        if isinstance(farm_v, str) and 'TOTAL' in farm_v.upper():
            break
        # Sticky farm
        if isinstance(farm_v, str) and farm_v.strip():
            s = farm_v.strip()
            if not re.search(r'shipment|awb|weight|invoice|number', s, re.IGNORECASE):
                current_farm = s
        inv_n = _as_number(inv_v)
        if inv_n is not None and inv_n > 0:
            current_inv = str(int(inv_n)) if inv_n == int(inv_n) else str(inv_n)
        # data строка
        var_s = str(var_v).strip() if var_v else ''
        if stems_n and stems_n > 0 and var_s:
            length = extract_length(len_v)
            boxes_n = _as_number(boxes_v) or 0
            out.append({
                'variety':  var_s,
                'length':   length,
                'stems':    int(stems_n),
                'boxes':    float(boxes_n),
                'farm':     current_farm,
                'farm_invoice': current_inv,
            })
            data_started = True
    return out if out else None


# --- Публичный API ---

def parse_packing(path: str) -> PackingSheet:
    """Распарсить один packing-list *.xls/*.xlsx.

    Пробует все известные форматы в порядке специфичности:
    POPBES → BESTFLORA → DC_CARGO → NL_KOLLI → PRADO → VIVAX → ALEX_KAZAN.
    Возвращает PackingSheet со списком items (может быть пустым).
    """
    result = PackingSheet(source=path)
    parent = str(Path(path).parent)
    parent_name = Path(parent).name
    grand_name = Path(parent).parent.name   # 'Ken' / 'Col' / 'Ecu' — country hint
    fname = Path(path).name

    for sheet_name, matrix in _rows(path):
        # Определяем country / marking / packing_no / awb — общий blob
        blob_lines = [' '.join(str(c) for c in row if c) for row in matrix[:20]]
        blob = '\n'.join(blob_lines)
        country = _detect_country(sheet_name, blob, parent_name, grand_name)
        marking = _find_marking(matrix, parent) or normalize_marking(parent_name)
        packing_no = _find_packing_no(matrix)
        awb = _find_awb(matrix)

        # Пробуем форматы: NL-специфичные (Boxes/Kolli/BoxNr) → EC-специфичные (PRADO/VIVAX/ALEX)
        parsers = [
            ('POPBES',      _try_parse_popbes),
            ('BESTFLORA',   _try_parse_bestflora),
            ('DC_CARGO',    _try_parse_dc_cargo),
            ('NL_KOLLI',    _try_parse_nl_kolli),
            ('PRADO',       _try_parse_prado),
            ('VIVAX',       _try_parse_vivax),
            ('EC_WIDE',     _try_parse_ec_wide),
            ('ZAIC',        _try_parse_zaic),
            ('RUFLORA',     _try_parse_ruflora),
            ('ALEX_HEADER', _try_parse_alex_header),   # 333MBK Colombia: header-based, must be before ALEX_KAZAN
            ('ALEX_KAZAN',  _try_parse_alex),
        ]
        items = None
        format_used = ''
        for name, fn in parsers:
            try:
                items = fn(matrix, sheet_name)
            except Exception:
                items = None
            if items:
                format_used = name
                break
        if not items:
            continue

        # Country/marking для NL-форматов = Netherlands + маркинг из имени файла
        # DC_CARGO — универсальный M-Flowers формат (Ecuador/NL), country из папки
        if format_used in ('POPBES', 'BESTFLORA', 'NL_KOLLI'):
            country = 'Netherlands'
            m_from_name = _marking_from_nl_filename(fname)
            if not m_from_name:
                m_from_name = _marking_from_nl_content(matrix)
            if m_from_name:
                marking = m_from_name
        elif format_used == 'DC_CARGO':
            # marking = Box mark из первой строки данных (обрабатывается ниже per-row)
            # country — из parent-каталога; если не определился, оставляем пустым
            # (manifest_builder применит AWB→country map из сертификатов)
            if not country:
                country = _detect_country(parent_name)
            # sheet-level marking из первой data-строки, если есть Box mark
            first_marking = ''
            for d in items:
                mh = d.get('marking_hint', '')
                if mh:
                    first_marking = normalize_marking(mh)
                    break
            if first_marking:
                marking = first_marking
        elif format_used in ('EC_WIDE', 'ZAIC'):
            # Ecuador wide / ZAIC — country=Ecuador, marking from items or filename
            if not country:
                country = 'Ecuador'
            first_marking = ''
            for d in items:
                mh = d.get('marking_hint', '')
                if mh:
                    first_marking = normalize_marking(mh)
                    break
            if first_marking:
                marking = first_marking

        # Первый лист с данными — используем как основной
        if not result.country:
            result.country = country
            result.marking = marking
            result.packing_no = packing_no
            result.awb = awb

        for d in items:
            variety = d['variety']
            flower = classify(variety)
            if not flower:
                # Иногда только в StatDesc есть родовое имя (Chrysanten/Anjers/Lelie's)
                hint = d.get('stat_hint', '')
                if hint:
                    flower = classify(hint)
            if not flower:
                # Fallback на sheet name
                flower = classify(sheet_name)
            if not flower:
                # Country-специфичный fallback: Kenya в 333MBK-датасете — только розы
                # (эталон Kenia содержит исключительно 'Роза NN см' строки)
                if (country or result.country) == 'Kenya':
                    flower = 'ROSA'
            # Marking override для DC_CARGO (Box mark в строке)
            row_marking = marking
            mh = d.get('marking_hint', '')
            if mh:
                row_marking = normalize_marking(mh)
            item = PackingItem(
                country      = country or result.country,
                marking      = row_marking or result.marking,
                farm         = d['farm'],
                farm_invoice = d['farm_invoice'],
                packing_no   = packing_no,
                awb          = awb,
                variety      = variety,
                flower       = flower,
                length_cm    = d['length'],
                stems        = d['stems'],
                boxes        = d['boxes'],
                source       = f'{path}::{sheet_name}::{format_used}',
            )
            result.items.append(item)
        break                        # первый лист с данными — достаточно

    return result


_NL_MARKING_PATTERNS = [
    # POPBES: '8207560   POPBES   BUTA.XLS' → BUTA
    re.compile(r'\d+\s+POPBES\s+([A-Z0-9\-]+)', re.IGNORECASE),
    # BESTFLORA: 'BESTFLORA-2_b-BOOM55_03_Jul_2026.xlsx' → BOOM55
    re.compile(r'BESTFLORA[-_]?\d*[_-]?b[-_]([A-Z0-9.]+)_', re.IGNORECASE),
    # PRADZ: '03-07-26 18-24-46-202603995   PRADZ - B.XLS' → PRADZ-B
    re.compile(r'\d+\s+([A-Z][A-Z0-9]+)\s*-\s*([A-Z0-9]+)\.XLS', re.IGNORECASE),
    # Kiro/Boss/Neft/Nero/Okty/Ronc/Ela — '02603995   KIRO - A.XLS'
    re.compile(r'\d+\s+([A-Z]+)\s*-\s*[A-Z]\.XLS', re.IGNORECASE),
    # Invoice 309871 03-07-2026 BORT-20.xls → BORT-20
    re.compile(r'Invoice\s+\d+.*?(?:20\d\d[- ])?([A-Z0-9]+-\d+)\.xls', re.IGNORECASE),
    # Invoice 309873 03-07-2026 CAD.xls → CAD (simple word marking)
    re.compile(r'Invoice\s+\d+.*?20\d\d\s+([A-Z][A-Z0-9.]+)\.xls', re.IGNORECASE),
    # MAX12 522.XLS → MAX12
    re.compile(r'^([A-Z0-9]+)\s+\d+\.XLS', re.IGNORECASE),
    # Simple name: '202603953   ROZAFL.XLS' → ROZAFL, '202604042   ORCHP.XLS' → ORCHP
    re.compile(r'\d{9,}\s+([A-Z][A-Z0-9.]+)\.XLS', re.IGNORECASE),
    # Bestflora dc_cargo: '01_07_2026_bestflora_dc_cargo(5762).xlsx' → отсутствует маркинг явно
]


def _marking_from_nl_filename(fname: str) -> str:
    """Извлечь marking из имени NL-инвойса.

    Возвращает нормализованное имя или '' если не распознано.
    """
    for pat in _NL_MARKING_PATTERNS:
        m = pat.search(fname)
        if m:
            grps = [g for g in m.groups() if g]
            token = '-'.join(grps)
            return normalize_marking(token)
    return ''


_FACTUUR_RE = re.compile(r'Factuur|Invoice\s*n', re.IGNORECASE)
_FACTUUR_MARK_RE = re.compile(r'\d+\s*/\s*([A-Z][A-Z0-9.]+)', re.IGNORECASE)
_FLORUNNER_RE = re.compile(
    r'Florunner\s+B\.?V\.?\s*\((\w+)\)\s*(\d+)\s*(.*)', re.IGNORECASE
)


def _marking_from_nl_content(matrix: list[list]) -> str:
    """Извлечь marking из содержимого NL-файла (Goldman-формат):
    1) 'Factuur nr:' + 'NNN / MARKING' в соседней ячейке
    2) 'Florunner B.V. (Code) N [Suffix]' → HU-CODEN[SUFFIX]
    """
    for row in matrix[:6]:
        for i, cell in enumerate(row):
            if not isinstance(cell, str):
                continue
            # Pattern 1: Factuur nr: ... → соседняя ячейка NNN / MARKING
            if _FACTUUR_RE.search(cell):
                for j in range(i + 1, min(len(row), i + 4)):
                    v = row[j]
                    if isinstance(v, str):
                        m = _FACTUUR_MARK_RE.search(v)
                        if m:
                            return normalize_marking(m.group(1))
            # Pattern 2: Florunner B.V. (Omks) N [AQUA/AA]
            m = _FLORUNNER_RE.search(cell)
            if m:
                code = m.group(1).upper()
                num = m.group(2)
                suffix = m.group(3).strip().upper()
                if suffix == 'AQUA':
                    suffix = 'AQ'
                return f'HU-{code}{num}{suffix}'
    return ''


# --- Хинты страны/AWB/клиента из иерархии папок ---
# ключ: подстрока в PATH → страна
_FOLDER_COUNTRY_HINTS = [
    (re.compile(r'\d{3,4}ecu|ecuador|эквад', re.IGNORECASE),  'Ecuador'),
    (re.compile(r'columbia|colombia|\d{3,4}col\b|колумб', re.IGNORECASE),  'Colombia'),
    (re.compile(r'kenya|kenia|\d{3,4}ken\b|кен',      re.IGNORECASE),      'Kenya'),
    (re.compile(r'israel|израил',        re.IGNORECASE),      'Israel'),
    (re.compile(r'thailand|тайлан',      re.IGNORECASE),      'Thailand'),
    (re.compile(r'ethiopia|эфиоп',       re.IGNORECASE),      'Ethiopia'),
    (re.compile(r'uganda|уганд',         re.IGNORECASE),      'Uganda'),
    (re.compile(r'costa\s*rica',         re.IGNORECASE),      'Costa Rica'),
    (re.compile(r'\bindia\b|инди',       re.IGNORECASE),      'India'),
    (re.compile(r'chile|чили',           re.IGNORECASE),      'Chile'),
    (re.compile(r'\bholl\b|netherl|голланд|нидерланд', re.IGNORECASE), 'Netherlands'),
]

# 'NNNN CLIENTNAME [Xкор]' в parent-каталоге
_AWB_CLIENT_RE = re.compile(r'^(\d{3,5})\s+(.+?)(?:\s+\d+кор)?$', re.IGNORECASE)


def _folder_hints(source_path: str) -> dict:
    """Извлечь (country, awb_hint, client_hint) из иерархии папок.

    Пример: '.../Инвойсы/700ecu/1932 ALEX KAZAN/packing.xls' →
        country='Ecuador', awb='1932', client='ALEX KAZAN'
    """
    p = Path(source_path)
    parent = p.parent.name       # '1932 ALEX KAZAN'
    grandpa = p.parent.parent.name  # '700ecu'
    great = p.parent.parent.parent.name  # 'Инвойсы'
    hint = {'country': '', 'awb': '', 'client': ''}
    # AWB + client hint
    m = _AWB_CLIENT_RE.match(parent)
    if m:
        hint['awb'] = m.group(1)
        hint['client'] = m.group(2).strip()
    # Country: пробуем grandparent → great-grandparent
    for src in (grandpa, great, parent):
        for pat, cty in _FOLDER_COUNTRY_HINTS:
            if pat.search(src):
                hint['country'] = cty
                break
        if hint['country']:
            break
    return hint


def parse_folder(root: str) -> list[PackingSheet]:
    """Рекурсивно распарсить все packing-list *.xls / *.xlsx в папке.

    Пропускает файлы явно НЕ packing (invoice/factuur/pro forma по имени).
    Дополнительно: если parse_packing не смог определить country/marking,
    восстанавливаем из имени родительских папок ('700ecu/1932 ALEX KAZAN').
    """
    root_p = Path(root)
    out: list[PackingSheet] = []
    if not root_p.is_dir():
        return out

    skip_pat = re.compile(
        # ^DD.MM.YYYY... matches master files like '03.07.2026-700.xlsx' — but NOT
        # invoices with a date inside their name (e.g. 'INVOICE PRADO 01.07.2026.xls').
        r'_autofilled|florunner|Таблица|Табл\.|^\d{2}\.\d{2}\.\d{4}',
        re.IGNORECASE,
    )
    # Явные "не-данные" (pro forma / master invoice / factuur сводный)
    only_meta_pat = re.compile(
        r'pro\s*forma|proforma',
        re.IGNORECASE,
    )

    for f in sorted(root_p.rglob('*.xls*')):
        name = f.name
        ext = f.suffix.lower()
        if ext not in ('.xls', '.xlsx', '.xlsm'):
            continue
        # Skip явные "output" таблицы + pro forma
        if skip_pat.search(name):
            continue
        if only_meta_pat.search(name):
            continue
        try:
            sheet = parse_packing(str(f))
        except Exception as e:
            print(f'  [!] parse_packing failed: {f.name}: {e}')
            continue
        # Восстановление country/marking/awb из папки, если parse_packing не нашёл
        h = _folder_hints(str(f))
        if not sheet.country and h['country']:
            sheet.country = h['country']
        # Marking: если parent = 'NNN CLIENTNAME', папка — точный источник истины.
        # Заменяем ошибочно-детектированные из содержимого ('INFORMATION',
        # 'CLIENT', 'CUSTOMER', 'INVOICE'), а также генерические.
        if h['client']:
            cur = (sheet.marking or '').strip().upper()
            bogus = cur in ('', 'INFORMATION', 'CLIENT', 'CUSTOMER',
                            'INVOICE', 'ORDER', 'NUMBER', 'PACKING')
            if bogus:
                sheet.marking = normalize_marking(h['client'])
        # Доп. проверка: маркировка похожа на название компании (EFANA S.R.O. и т.п.)
        # → пытаемся восстановить из corrections (filename_to_marking)
        from marking_utils import is_client_name as _is_client
        cur_mark = (sheet.marking or '').strip()
        if _is_client(cur_mark) or not cur_mark:
            # Попробуем взять из corrections
            try:
                from corrections import load_corrections as _lc
                _c = _lc()
                fname_mark = _c.resolve_marking_from_filename(f.name)
                if fname_mark:
                    sheet.marking = fname_mark
            except Exception:
                pass
        if not sheet.awb and h['awb']:
            sheet.awb = h['awb']
        # Пропишем в items тоже (для агрегаций по стране)
        for it in sheet.items:
            if not it.country and sheet.country:
                it.country = sheet.country
            # Marking в item: если совпадает со старым sheet-marking, обновим
            if h['client'] and (it.marking or '').strip().upper() in (
                    '', 'INFORMATION', 'CLIENT', 'CUSTOMER', 'INVOICE',
                    'ORDER', 'NUMBER', 'PACKING'):
                it.marking = sheet.marking
        # Sanity check: если суммарные стебли > 200k, это ошибка парсинга (неверные колонки)
        total_stems = sum(it.stems for it in sheet.items)
        if total_stems > 200_000:
            print(f'  [!] SKIP {f.name}: total_stems={total_stems} (sanity cap 200k exceeded)')
            continue
        if sheet.items:
            out.append(sheet)
    return out


if __name__ == '__main__':
    import sys
    try:
        sys.stdout.reconfigure(encoding='utf-8')
    except Exception:
        pass
    root = sys.argv[1] if len(sys.argv) > 1 else (
        r'c:\Users\0\Desktop\Задача по логистике\03.07.2026\MTL 700'
    )
    print(f'Root: {root}')
    sheets = parse_folder(root)
    total_stems = sum(it.stems for sh in sheets for it in sh.items)
    print(f'Packing-list-ов: {len(sheets)}  items: {sum(len(s.items) for s in sheets)}  stems: {total_stems}')
    for s in sheets:
        src = os.path.basename(s.source)
        print(f'\n  {src!r}  country={s.country!r}  marking={s.marking!r}  '
              f'packing_no={s.packing_no!r}  awb={s.awb!r}  items={len(s.items)}')
        # Показать первые 3 items
        for it in s.items[:3]:
            print(f'    · {it.flower:<12} {it.variety:<25} '
                  f'len={it.length_cm:>3}  stems={it.stems:>5}  farm={it.farm!r}')