from __future__ import annotations

import hashlib
import json
import math
import re
import unicodedata
from dataclasses import dataclass
from datetime import datetime
from pathlib import Path

import pandas as pd


INPUT_PATH = Path(r"F:\L'Atelier Com' du Roumois\modele_import_tarifs_depuis_sql.xlsx")
OUTPUT_DIR = Path(r"C:\wamp64\www\atelier-com-site\racine\atelier-com-platform\outputs\pricing_rational_rebuild")
ROWS_PATH = OUTPUT_DIR / "pricing_rational_rows.json"
AUDIT_PATH = OUTPUT_DIR / "pricing_rational_audit.json"


@dataclass(frozen=True)
class Config:
    family: str
    curve: str
    anchor_price: float
    note: str
    actual_qtys: tuple[int, ...] = ()
    actual_totals: tuple[float, ...] = ()
    factor_cap: float = 4.0
    skip: bool = False


def cfg(
    family: str,
    curve: str,
    anchor_price: float,
    note: str,
    actual_qtys: tuple[int, ...] = (),
    actual_totals: tuple[float, ...] = (),
    factor_cap: float = 4.0,
    skip: bool = False,
) -> Config:
    return Config(
        family=family,
        curve=curve,
        anchor_price=float(anchor_price),
        note=note,
        actual_qtys=actual_qtys,
        actual_totals=actual_totals,
        factor_cap=factor_cap,
        skip=skip,
    )


CONFIG: dict[int, Config] = {
    1: cfg("card", "print500", 20.72, "Exaprint URL: carte papier classique visible a 20.72 EUR HT les 500 ex", factor_cap=3.2),
    2: cfg("flyer", "print1000", 34, "Realisaprint URL: flyer visible des 34 EUR HT les 1000 ex", factor_cap=4.5),
    3: cfg("print", "print100", 35, "Realisaprint URL: depliant visible des 35 EUR HT les 100 ex"),
    5: cfg("print", "print1000", 54, "Realisaprint URL: enveloppe visible des 54 EUR HT les 1000 ex"),
    6: cfg("print", "print100", 50, "Realisaprint URL: chemise a rabats visible des 50 EUR HT les 100 ex"),
    7: cfg("print", "print1000", 39, "Realisaprint URL: carte postale visible des 39 EUR HT les 1000 ex"),
    8: cfg("print", "print50", 25, "Realisaprint URL: carte de correspondance visible des 25 EUR HT les 50 ex"),
    10: cfg("brochure", "print100", 29, "Realisaprint URL: brochure agrafee visible des 29 EUR HT les 100 ex", factor_cap=5.0),
    11: cfg("brochure", "print50", 61, "Realisaprint URL: dos carre colle visible des 61 EUR HT les 50 ex", factor_cap=5.0),
    13: cfg("print", "print50", 19, "Realisaprint URL: affiche visible des 19 EUR HT les 50 ex"),
    14: cfg("print", "print25", 33, "Realisaprint URL: calendrier mural visible des 33 EUR HT les 25 ex"),
    15: cfg("print", "print100", 184, "Realisaprint URL: calendrier chevalet visible des 184 EUR HT les 100 ex"),
    16: cfg("print", "print100", 21, "Realisaprint URL: bloc-notes visible des 21 EUR HT les 100 ex"),
    17: cfg("brochure", "book20", 125, "Realisaprint URL: autocopiant visible des 125 EUR HT les 20 ex", factor_cap=5.0),
    18: cfg("print", "print1000", 76, "Realisaprint URL: set de table visible des 76 EUR HT les 1000 ex"),
    19: cfg("print", "print100", 35, "Realisaprint URL: menu a pli visible des 35 EUR HT les 100 ex"),
    20: cfg("print", "office40", 364, "Realisaprint URL: sous-main visible des 364 EUR HT les 40 ex"),
    26: cfg("stand", "fixed10", 32, "Fulfiller URL: roll-up eco visible a 32 EUR HT", factor_cap=2.8),
    27: cfg("flag", "fixed10", 83, "Fulfiller URL: drapeau publicitaire visible a 83 EUR HT", factor_cap=3.5),
    29: cfg("area", "fixed10", 39, "123baches URL: bache PVC standard, estimation encadree", factor_cap=4.0),
    31: cfg("area", "fixed10", 18, "123panneaux URL: Forex 3 mm standard, estimation encadree", factor_cap=4.0),
    34: cfg("area", "fixed10", 32, "Realisaprint URL: vitrophanie, estimation encadree", factor_cap=4.0),
    35: cfg("area", "fixed10", 38, "Realisaprint URL: microperfore, estimation encadree", factor_cap=4.0),
    36: cfg("area", "fixed10", 29, "Realisaprint URL: depoli vitrine, estimation encadree", factor_cap=4.0),
    37: cfg("area", "fixed10", 20, "Fulfiller URL: kakemono suspendu visible a 20 EUR HT", factor_cap=4.5),
    38: cfg("stand", "fixed10", 24, "Fulfiller URL: X-banner visible a 24 EUR HT", factor_cap=2.4),
    39: cfg("stand", "fixed10", 66, "Fulfiller URL: stop-trottoir visible a 66 EUR HT", factor_cap=2.8),
    44: cfg("stand", "fixed10", 195, "Fulfiller URL: photocall visible a 195 EUR HT", factor_cap=3.2),
    45: cfg("stand", "fixed10", 69, "Realisaprint URL: nappe ronde, estimation encadree", factor_cap=2.0),
    62: cfg("goodie", "actual", 70, "HelloPrint URL: bracelet Tyvek, paliers visibles", (100, 250, 500, 1000, 2500, 5000), (70, 118, 193, 350, 825, 1550)),
    63: cfg("goodie", "actual", 60, "ObjetRama URL: tour de cou Lungo, paliers visibles", (250, 500, 1000, 2500, 5000, 10000), (60, 115, 210, 450, 850, 1500)),
    67: cfg("stand", "fixed10", 149, "Fulfiller URL: comptoir d'accueil visible a 149 EUR HT", factor_cap=2.5),
    68: cfg("stand", "fixed10", 299, "Fulfiller URL: stand parapluie textile visible a 299 EUR HT", factor_cap=2.8),
    69: cfg("arch", "fixed10", 719, "Fulfiller URL: arche gonflable visible a 719 EUR HT", factor_cap=2.4),
    78: cfg("goodie", "goodie50", 295, "ObjetRama URL: gourde bambou, estimation avec personnalisation standard"),
    79: cfg("goodie", "actual", 242.75, "ObjetRama URL: bouteille Astrio avec personnalisation standard 1 couleur incluse", (25, 50, 100, 250, 500, 1000), (242.75, 412.5, 759, 1662.5, 3115, 5900)),
    80: cfg("goodie", "goodie50", 190, "ObjetRama URL: cle USB bois, estimation avec personnalisation standard"),
    86: cfg("goodie", "goodie100", 129, "ObjetRama URL: briquet Bic, estimation avec personnalisation standard"),
    88: cfg("goodie", "goodie50", 289, "ObjetRama URL: lunch box, estimation avec personnalisation standard"),
    103: cfg("stand", "fixed10", 399, "Fulfiller URL: stand textile courbe, estimation encadree depuis la gamme visible", factor_cap=2.8),
    104: cfg("area", "fixed10", 32, "123panneaux URL: Dibond 4 mm standard, estimation encadree", factor_cap=4.0),
    105: cfg("area", "fixed10", 14, "Source URL incoherente (Dibond pour Akylux): fallback Akylux standard encadre", factor_cap=4.0),
    106: cfg("flag", "fixed10", 72, "Fulfiller URL: drapeau goutte, estimation encadree", factor_cap=3.5),
    107: cfg("stand", "fixed10", 79, "Realisaprint URL: nappe rectangle, estimation encadree", factor_cap=2.0),
    108: cfg("door_hanger", "actual", 68, "Ancrage utilisateur: 250g brillant R/V sans finition", (25, 50, 100, 250, 500, 1000), (68, 68, 79, 123, 171, 237), factor_cap=1.65),
    109: cfg("label", "print500", 50, "Ancrage utilisateur: etiquette ronde 5 cm polypro blanc mat sans finition a 50 EUR HT les 500 ex", factor_cap=3.5),
    110: cfg("print", "print25", 95, "Saxoprint URL: classeur, estimation encadree"),
    111: cfg("print", "print100", 53, "Realisaprint URL: dossier administratif visible des 53 EUR HT les 100 ex"),
    112: cfg("print", "print100", 18, "Realisaprint URL: marque-page visible des 18 EUR HT les 100 ex"),
    113: cfg("print", "print100", 33, "Realisaprint URL: Post-it visible des 33 EUR HT les 100 ex"),
    114: cfg("print", "print1000", 32, "Realisaprint URL: tete de lettre visible des 32 EUR HT les 1000 ex"),
    115: cfg("skip", "none", 0, "Badge ignore selon consigne utilisateur", skip=True),
    116: cfg("goodie", "goodie25", 289, "ObjetRama URL: coussin personnalise, estimation encadree"),
    117: cfg("goodie", "goodie50", 149, "ObjetRama URL: jeu de cartes, estimation encadree"),
    118: cfg("goodie", "goodie100", 119, "ObjetRama URL: magnet, estimation encadree"),
    119: cfg("goodie", "actual", 256, "ObjetRama URL: mug ceramique, paliers visibles", (80, 100, 140, 320, 500, 1000), (212, 256, 346, 739, 1085, 2110)),
    120: cfg("goodie", "goodie50", 279, "ObjetRama URL: mug isotherme, estimation avec personnalisation standard"),
    121: cfg("goodie", "goodie25", 399, "Source URL incoherente (parapluie pour Powerbank): fallback Powerbank standard encadre"),
    122: cfg("goodie", "goodie250", 115, "ObjetRama URL: sac kraft, estimation avec personnalisation standard"),
    123: cfg("goodie", "actual", 70, "ObjetRama URL: stylo RioColor, paliers visibles", (500, 1000, 2500, 5000, 10000, 15000), (70, 130, 300, 550, 1000, 1350)),
    124: cfg("goodie", "goodie50", 189, "ObjetRama URL: tapis de souris, estimation avec personnalisation standard"),
    125: cfg("goodie", "goodie50", 129, "ObjetRama URL: tote bag, estimation avec personnalisation standard"),
    126: cfg("goodie", "actual", 60, "ObjetRama URL: tour de cou Lungo, paliers visibles", (250, 500, 1000, 2500, 5000, 10000), (60, 115, 210, 450, 850, 1500)),
    127: cfg("stand", "fixed10", 495, "Source URL incoherente (boite pizza pour Transat): fallback Transat standard encadre", factor_cap=1.8),
    128: cfg("packaging", "print500", 185, "Source URL proche mais non exacte (boite pizza pour burger): fallback boite burger standard"),
    129: cfg("packaging", "goodie250", 289, "Realisaprint URL: coffret bouteille, estimation encadree"),
    130: cfg("packaging", "print500", 89, "Realisaprint URL: gobelet carton, estimation encadree"),
    131: cfg("packaging", "goodie250", 155, "Source URL proche mais non exacte (gobelet carton pour reutilisable): fallback encadre"),
    132: cfg("goodie", "goodie50", 145, "ObjetRama URL: rond de serviette, estimation avec personnalisation standard"),
    133: cfg("packaging", "print500", 29, "Realisaprint URL: sous-bock, grille standardisee"),
    134: cfg("stand", "fixed10", 399, "Fulfiller URL: mur d'image tissu tendu visible a 399 EUR HT", factor_cap=2.4),
}


CURVES: dict[str, tuple[tuple[int, ...], tuple[float, ...]]] = {
    "print25": ((25, 50, 100, 250, 500, 1000), (1.00, 1.38, 1.90, 3.35, 5.55, 9.40)),
    "print50": ((25, 50, 100, 250, 500, 1000), (0.72, 1.00, 1.52, 2.70, 4.45, 7.60)),
    "print100": ((100, 250, 500, 1000, 2500, 5000), (1.00, 1.58, 2.35, 3.75, 7.10, 12.30)),
    "print500": ((100, 250, 500, 1000, 2500, 5000), (0.58, 0.80, 1.00, 1.40, 2.46, 4.34)),
    "print1000": ((100, 250, 500, 1000, 2500, 5000), (0.52, 0.66, 0.80, 1.00, 1.72, 2.82)),
    "book20": ((20, 50, 100, 250, 500, 1000), (1.00, 1.95, 3.45, 7.65, 13.90, 25.20)),
    "office40": ((10, 25, 40, 100, 250, 500), (0.44, 0.73, 1.00, 2.14, 4.75, 8.95)),
    "fixed10": ((1, 2, 5, 10), (1.00, 1.85, 4.25, 7.80)),
    "goodie25": ((10, 25, 50, 100, 250, 500), (0.56, 1.00, 1.78, 3.35, 7.20, 13.40)),
    "goodie50": ((25, 50, 100, 250, 500, 1000), (0.72, 1.00, 1.82, 4.10, 7.60, 14.20)),
    "goodie100": ((50, 100, 250, 500, 1000, 2500), (0.62, 1.00, 2.30, 4.20, 7.90, 18.70)),
    "goodie250": ((100, 250, 500, 1000, 2500, 5000), (0.56, 1.00, 1.82, 3.35, 7.60, 14.40)),
}


A_FORMATS = {
    "a0": 0.999,
    "a1": 0.500,
    "a2": 0.250,
    "a3": 0.125,
    "a4": 0.062,
    "a5": 0.031,
    "a6": 0.016,
}


def clean_text(value: object) -> str:
    if value is None or (isinstance(value, float) and math.isnan(value)):
        return ""
    return str(value).strip()


def normalize_text(value: object) -> str:
    text = unicodedata.normalize("NFKD", clean_text(value))
    text = "".join(ch for ch in text if not unicodedata.combining(ch))
    return text.lower()


def slug(value: object) -> str:
    text = normalize_text(value)
    text = re.sub(r"[^a-z0-9]+", "-", text).strip("-")
    return text


def numbers(value: object) -> list[float]:
    return [float(item.replace(",", ".")) for item in re.findall(r"\d+(?:[.,]\d+)?", clean_text(value))]


def clamp(value: float, low: float, high: float) -> float:
    return max(low, min(value, high))


def parse_area(value: object) -> float | None:
    text = normalize_text(value)
    if not text:
        return None

    for code, area in A_FORMATS.items():
        if re.search(rf"(^|[^a-z0-9]){code}([^a-z0-9]|$)", text):
            return area

    vals = numbers(text)
    if len(vals) < 2:
        return None

    width, height = vals[0], vals[1]
    if "mm" in text:
        width, height = width / 1000.0, height / 1000.0
    elif "cm" in text:
        width, height = width / 100.0, height / 100.0
    elif re.search(r"(^|[^a-z])m([^a-z]|$)", text):
        pass
    else:
        width, height = width / 100.0, height / 100.0

    return width * height


def parse_area_from_row(row: dict[str, object]) -> float | None:
    direct = parse_area(row.get("format"))
    if direct:
        return direct

    width = numbers(row.get("largeur"))
    height = numbers(row.get("hauteur"))
    if not width or not height:
        return None

    joined = normalize_text(f"{clean_text(row.get('largeur'))} {clean_text(row.get('hauteur'))}")
    factor = 0.01 if "cm" in joined else 1.0
    return width[0] * factor * height[0] * factor


def parse_grammage(row: dict[str, object]) -> float | None:
    for key in ("grammage-epaisseur", "support-matiere", "support-mati-ere"):
        text = normalize_text(row.get(key))
        if "g" not in text:
            continue
        vals = numbers(text)
        if vals:
            return vals[0]
    return None


def parse_count(row: dict[str, object], keys: tuple[str, ...]) -> float | None:
    values: list[float] = []
    for key in keys:
        values.extend(numbers(row.get(key)))
    return max(values) if values else None


def material_factor(row: dict[str, object], family: str) -> float:
    text = normalize_text(" ".join(clean_text(row.get(key)) for key in ("support-matiere", "support-mati-ere", "matiere", "type")))
    if family == "door_hanger":
        if "350g" in text:
            return 1.12
        if "300g" in text:
            return 1.06
        return 1.0

    factor = 1.0
    if "recycle" in text:
        factor *= 1.06
    if "textile stretch" in text:
        factor *= 1.08
    if "opaque" in text:
        factor *= 1.06
    if "pvc 500" in text or "pro 450" in text:
        factor *= 1.10
    if "microperfore" in text:
        factor *= 1.10
    if "depoli" in text:
        factor *= 1.08
    if "forex 5" in text:
        factor *= 1.10
    if "aludibon 6" in text:
        factor *= 1.16
    if "akylux 4.5" in text:
        factor *= 1.08
    if "polypro translucide" in text:
        factor *= 1.05
    if "polypro opaque" in text:
        factor *= 1.03
    return clamp(factor, 0.85, 1.35)


def option_factor(row: dict[str, object], family: str) -> float:
    finish = normalize_text(row.get("finition"))
    pelliculage = normalize_text(row.get("pelliculage"))
    lamination = normalize_text(row.get("lamination"))
    impression = normalize_text(row.get("impression"))
    if family == "door_hanger":
        factor = 1.0 if "verso" in impression else 0.90
        if finish in {"mat", "brillant"}:
            factor *= 1.12
        return factor

    other = normalize_text(
        " ".join(
            clean_text(row.get(key))
            for key in ("oeillets", "ourlet", "ganse", "embase", "gonfleur", "visuel", "grammage-epaisseur", "rainage")
        )
    )
    factor = 1.0

    if "verso" in impression:
        factor *= 1.14 if family not in {"area", "flag"} else 1.35
    if "vernis 3d" in finish:
        factor *= 1.22
    elif "vernis selectif" in finish:
        factor *= 1.16
    elif "vernis" in finish:
        factor *= 1.08
    if "dorure" in finish:
        factor *= 1.25
    if finish in {"mat", "brillant", "pelliculage"}:
        factor *= 1.16
    if "pelliculage" in finish or ("lamination" in lamination and "aucune" not in lamination):
        factor *= 1.14
    if pelliculage in {"mat", "brillant"}:
        factor *= 1.14
    if "soft" in pelliculage:
        factor *= 1.20
    if "double rainage" in other:
        factor *= 1.05
    if "oeillet" in other and "aucune" not in other:
        factor *= 1.08
    if "ourlet" in other and "aucune" not in other:
        factor *= 1.07
    if "ganse imprimee" in other:
        factor *= 1.06
    if "embase lestable" in other:
        factor *= 1.16
    elif "platine" in other:
        factor *= 1.12
    elif "pied parasol" in other:
        factor *= 1.10
    if "gonfleur" in other and "sans" not in other:
        factor *= 1.08
    if "2 visuels" in other:
        factor *= 1.10
    elif "3 visuels" in other:
        factor *= 1.18
    if "2 spots" in other:
        factor *= 1.10
    return clamp(factor, 0.80, 2.20)


def build_features(row: dict[str, object], family: str) -> dict[str, float | None]:
    return {
        "area": parse_area_from_row(row),
        "grammage": parse_grammage(row),
        "pages": parse_count(row, ("nbr-pages", "nbr-pages-interieures")),
        "sheets": parse_count(row, ("nbr-feuillets", "nbr-feuilles")),
        "material": material_factor(row, family),
        "option": option_factor(row, family),
    }


def min_positive(values: list[float | None]) -> float | None:
    positive = [value for value in values if value is not None and value > 0]
    return min(positive) if positive else None


def score(features: dict[str, float | None], mins: dict[str, float | None], family: str) -> float:
    value = float(features["material"] or 1.0) * float(features["option"] or 1.0)

    if features["area"] and mins["area"] and family not in {"goodie", "packaging"}:
        exponent = 0.62 if family in {"area", "stand", "flag", "arch"} else 0.36
        value *= clamp((features["area"] / mins["area"]) ** exponent, 1.0, 4.0)

    if features["grammage"] and mins["grammage"] and family in {"print", "card", "flyer", "brochure", "packaging"}:
        value *= clamp(1.0 + ((features["grammage"] / mins["grammage"]) - 1.0) * 0.12, 1.0, 1.45)

    if features["pages"] and mins["pages"]:
        value *= clamp((features["pages"] / mins["pages"]) ** 0.36, 1.0, 2.5)

    if features["sheets"] and mins["sheets"]:
        value *= clamp((features["sheets"] / mins["sheets"]) ** 0.30, 1.0, 2.2)

    return value


def price_grid(config: Config, ratio: float) -> tuple[list[int], list[int | float]]:
    ratio = clamp(ratio, 0.75, config.factor_cap)
    if config.curve == "actual":
        return list(config.actual_qtys), [max(1, round(total * ratio)) for total in config.actual_totals]
    qtys, multipliers = CURVES[config.curve]
    return list(qtys), [max(1, round(config.anchor_price * multiplier * ratio)) for multiplier in multipliers]


def json_attributes(row: dict[str, object], attribute_columns: list[str]) -> dict[str, str]:
    attrs: dict[str, str] = {}
    for column in attribute_columns:
        value = clean_text(row.get(column))
        if value:
            attrs[column] = value
    return attrs


def readable_configuration(attrs: dict[str, str]) -> str:
    labels = {
        "format": "Format",
        "support-matiere": "Support / matiere",
        "support-mati-ere": "Support / matiere",
        "grammage-epaisseur": "Grammage / epaisseur",
        "impression": "Impression",
        "finition": "Finition",
    }
    order = ("format", "support-matiere", "support-mati-ere", "grammage-epaisseur", "impression", "finition")
    return " | ".join(f"{labels[key]} : {attrs[key]}" for key in order if key in attrs)


def combination_key(product_id: int, attrs: dict[str, str]) -> str:
    raw = json.dumps(attrs, ensure_ascii=False, sort_keys=True, separators=(",", ":"))
    digest = hashlib.sha1(raw.encode("utf-8")).hexdigest()[:16]
    seed = "-".join([f"p{product_id}"] + [slug(value) for value in attrs.values() if value][:2])
    return f"{seed}-{digest}"


def rebuild_flyer_rows(df: pd.DataFrame, attribute_columns: list[str]) -> pd.DataFrame:
    mask = df["sku"] == "PAP-FLYERS"
    source = df.loc[mask].copy()
    template = source.iloc[0].copy()

    formats = list(dict.fromkeys(clean_text(value) for value in source["format"]))
    supports = list(dict.fromkeys(clean_text(value) for value in source["support-matiere"]))
    grammages = list(dict.fromkeys(clean_text(value) for value in source["grammage-epaisseur"]))
    impressions = list(dict.fromkeys(clean_text(value) for value in source["impression"]))

    rows: list[pd.Series] = []
    for fmt in formats:
        for support in supports:
            for grammage in grammages:
                finishes = ["Aucune"] if grammage in {"135g", "170g"} else ["Aucune", "Mat", "Brillant"]
                for impression in impressions:
                    for finish in finishes:
                        row = template.copy()
                        row["format"] = fmt
                        row["support-matiere"] = support
                        row["grammage-epaisseur"] = grammage
                        row["impression"] = impression
                        row["finition"] = finish
                        attrs = json_attributes(row.to_dict(), attribute_columns)
                        row.iloc[6] = combination_key(2, attrs)
                        row.iloc[7] = readable_configuration(attrs)
                        row.iloc[8] = json.dumps(attrs, ensure_ascii=False, sort_keys=True, separators=(",", ":"))
                        rows.append(row)

    if len(rows) != len(source):
        raise RuntimeError(f"Unexpected flyer row count: {len(rows)} instead of {len(source)}")

    rebuilt = df.copy()
    rebuilt.loc[mask, :] = pd.DataFrame(rows, columns=df.columns).to_numpy()
    return rebuilt


def config_for(product_id: int, row: dict[str, object]) -> Config:
    if product_id in CONFIG:
        return CONFIG[product_id]
    source = normalize_text(row.get("Source"))
    if "objetrama" in source:
        return cfg("goodie", "goodie50", 199, "ObjetRama URL: fallback standard encadre")
    if "fulfiller" in source or "123" in source:
        return cfg("area", "fixed10", 59, "Grand format URL: fallback standard encadre")
    return cfg("print", "print1000", 39, "Print URL: fallback standard encadre")


def serializable(value: object) -> object:
    if value is None or (isinstance(value, float) and math.isnan(value)):
        return None
    if hasattr(value, "item"):
        return value.item()
    return value


def main() -> None:
    df = pd.read_excel(INPUT_PATH, sheet_name="Import_Tarifs")
    for column in df.columns[11:14]:
        df[column] = df[column].astype("object")
    attribute_columns = list(df.columns[14:])
    df = rebuild_flyer_rows(df, attribute_columns)

    audit_products: list[dict[str, object]] = []
    skipped_rows = 0
    estimate_rows = 0

    for product_id, indexes in df.groupby("product_id", sort=True).groups.items():
        pid = int(product_id)
        subset = df.loc[indexes]
        first = subset.iloc[0].to_dict()
        config = config_for(pid, first)

        if config.skip:
            for index in indexes:
                df.at[index, df.columns[11]] = ""
                df.at[index, df.columns[12]] = ""
                df.at[index, df.columns[13]] = config.note
                skipped_rows += 1
            continue

        rows = [df.loc[index].to_dict() for index in indexes]
        features = [build_features(row, config.family) for row in rows]
        mins = {
            "area": min_positive([item["area"] for item in features]),
            "grammage": min_positive([item["grammage"] for item in features]),
            "pages": min_positive([item["pages"] for item in features]),
            "sheets": min_positive([item["sheets"] for item in features]),
        }
        raw_scores = [score(item, mins, config.family) for item in features]
        baseline = 1.0 if config.family == "door_hanger" else (min(raw_scores) if raw_scores else 1.0)

        product_prices: list[float] = []
        for index, raw_score in zip(indexes, raw_scores):
            qtys, totals = price_grid(config, raw_score / baseline)
            df.at[index, df.columns[11]] = ";".join(str(qty) for qty in qtys)
            df.at[index, df.columns[12]] = ";".join(str(total) for total in totals)
            df.at[index, df.columns[13]] = f"grille rationnelle | {config.note} | facteurs bornes; URL conservee dans Source"
            product_prices.extend(float(total) for total in totals)
            estimate_rows += 1

        audit_products.append(
            {
                "product_id": pid,
                "sku": clean_text(first.get("sku")),
                "name": clean_text(first.get("Nom produit")),
                "source": clean_text(first.get("Source")),
                "rows": len(rows),
                "family": config.family,
                "note": config.note,
                "minimum_price": min(product_prices) if product_prices else None,
                "maximum_price": max(product_prices) if product_prices else None,
            }
        )

    records = [[serializable(value) for value in row] for row in df.to_numpy().tolist()]
    OUTPUT_DIR.mkdir(parents=True, exist_ok=True)
    ROWS_PATH.write_text(
        json.dumps({"columns": list(df.columns), "rows": records}, ensure_ascii=False),
        encoding="utf-8",
    )

    audit_products.sort(key=lambda item: float(item["maximum_price"] or 0), reverse=True)
    audit = {
        "generated_at": datetime.now().isoformat(timespec="seconds"),
        "input": str(INPUT_PATH),
        "rows": len(df),
        "products": int(df["product_id"].nunique()),
        "filled_rows": estimate_rows,
        "skipped_rows": skipped_rows,
        "flyer_none_rows": int(((df["sku"] == "PAP-FLYERS") & (df["finition"] == "Aucune")).sum()),
        "card_none_finish_rows": int(((df["sku"] == "PAP-CARTES-DE-VISITE") & (df["finition"] == "Aucune")).sum()),
        "card_none_lamination_rows": int(((df["sku"] == "PAP-CARTES-DE-VISITE") & (df["pelliculage"] == "Aucune")).sum()),
        "products_by_maximum_price": audit_products,
    }
    AUDIT_PATH.write_text(json.dumps(audit, ensure_ascii=False, indent=2), encoding="utf-8")
    print(json.dumps({"rows_payload": str(ROWS_PATH), "audit": str(AUDIT_PATH), **{key: audit[key] for key in ("rows", "products", "filled_rows", "skipped_rows", "flyer_none_rows", "card_none_finish_rows", "card_none_lamination_rows")}}, ensure_ascii=False))


if __name__ == "__main__":
    main()