<?php

declare(strict_types=1);

function repo_table_row_count(PDO $pdo, string $table): int
{
    if (!table_exists($pdo, $table)) {
        return 0;
    }

    $stmt = $pdo->query('SELECT COUNT(*) FROM ' . qi($table));

    return (int) $stmt->fetchColumn();
}

function repo_bool_from_mixed(mixed $value): bool
{
    if (is_bool($value)) {
        return $value;
    }

    if (is_int($value) || is_float($value)) {
        return (int) $value === 1;
    }

    if (is_string($value)) {
        $normalized = strtolower(trim($value));
        return in_array($normalized, ['1', 'true', 'yes', 'on'], true);
    }

    return false;
}

function repo_normalize_models(array $models): array
{
    $seen = [];
    $output = [];

    foreach ($models as $model) {
        $name = trim((string) $model);
        if ($name === '') {
            continue;
        }

        $key = strtolower($name);
        if (isset($seen[$key])) {
            continue;
        }

        $seen[$key] = true;
        $output[] = $name;
    }

    return $output;
}

function repo_parse_models_input(mixed $modelsRaw): array
{
    if (is_array($modelsRaw)) {
        return repo_normalize_models($modelsRaw);
    }

    if (!is_string($modelsRaw)) {
        return [];
    }

    $value = trim($modelsRaw);
    if ($value === '') {
        return [];
    }

    $parts = preg_split('/[,;|]+/', $value);
    if (!is_array($parts)) {
        return [];
    }

    return repo_normalize_models($parts);
}

function repo_price_sql_expr(string $alias, string $column, bool $isCents): string
{
    $qualified = $alias . '.' . qi($column);
    if ($isCents) {
        return '(' . $qualified . ' / 100)';
    }

    return $qualified;
}

function catalog_schema(): array
{
    static $schema = null;

    if (is_array($schema)) {
        return $schema;
    }

    $pdo = db();

    $preferredCatalogTable = trim((string) env('CATALOG_TABLE', 'products'));
    if ($preferredCatalogTable !== '' && table_exists($pdo, $preferredCatalogTable)) {
        $articlesTable = $preferredCatalogTable;
    } elseif (table_exists($pdo, 'articles') && table_exists($pdo, 'products')) {
        $articlesCount = repo_table_row_count($pdo, 'articles');
        $productsCount = repo_table_row_count($pdo, 'products');
        $articlesTable = $productsCount >= $articlesCount ? 'products' : 'articles';
    } elseif (table_exists($pdo, 'articles')) {
        $articlesTable = 'articles';
    } elseif (table_exists($pdo, 'products')) {
        $articlesTable = 'products';
    } else {
        $articlesTable = null;
    }

    if ($articlesTable === null) {
        throw new RuntimeException('Table articles/products introuvable.');
    }

    $modelesTable = table_exists($pdo, 'modeles') ? 'modeles' : (table_exists($pdo, 'models') ? 'models' : null);
    $marquesTable = table_exists($pdo, 'marques') ? 'marques' : (table_exists($pdo, 'brands') ? 'brands' : null);
    $priceColumn = pick_column($pdo, $articlesTable, ['prix', 'price', 'price_cents']);
    $preferredStockColumn = trim((string) env('CATALOG_STOCK_COLUMN', ''));
    if ($preferredStockColumn !== '' && isset(table_columns($pdo, $articlesTable)[$preferredStockColumn])) {
        $stockColumn = $preferredStockColumn;
    } else {
        $stockColumn = pick_column($pdo, $articlesTable, ['stock_quantity', 'stock', 'quantity', 'qty']);
    }
    $activeColumn = pick_column($pdo, $articlesTable, ['actif', 'is_active', 'active'], false);
    $promoActiveColumn = pick_column($pdo, $articlesTable, ['promo_actif', 'promo_active', 'is_promo'], false);
    $promoPriceColumn = pick_column($pdo, $articlesTable, ['prix_promo', 'promo_price', 'promo_price_cents'], false);
    $oldPriceColumn = pick_column($pdo, $articlesTable, ['old_price', 'prix_initial', 'price_original'], false);

    $schema = [
        'articles_table' => $articlesTable,
        'modeles_table' => $modelesTable,
        'marques_table' => $marquesTable,
        'articles' => [
            'id' => pick_column($pdo, $articlesTable, ['id']),
            'modele_id' => pick_column($pdo, $articlesTable, ['modele_id', 'model_id'], false),
            'reference' => pick_column($pdo, $articlesTable, ['reference', 'sku', 'ref']),
            'reference_alt' => pick_column($pdo, $articlesTable, ['sku2', 'reference2', 'secondary_sku'], false),
            'nom' => pick_column($pdo, $articlesTable, ['nom', 'title', 'name']),
            'category' => pick_column($pdo, $articlesTable, ['category', 'categorie'], false),
            'description' => pick_column($pdo, $articlesTable, ['description', 'details', 'short_description'], false),
            'prix' => $priceColumn,
            'price_is_cents' => $priceColumn === 'price_cents',
            'promo_active' => $promoActiveColumn,
            'promo_price' => $promoPriceColumn,
            'promo_price_is_cents' => $promoPriceColumn === 'promo_price_cents',
            'old_price' => $oldPriceColumn,
            'old_price_is_cents' => $oldPriceColumn !== null && str_contains($oldPriceColumn, 'cents'),
            'stock' => $stockColumn,
            'created_at' => pick_column($pdo, $articlesTable, ['created_at', 'date_creation', 'createdAt'], false),
            'updated_at' => pick_column($pdo, $articlesTable, ['updated_at', 'updatedAt'], false),
            'is_new' => pick_column($pdo, $articlesTable, ['is_new', 'nouveau'], false),
            'image_url' => pick_column($pdo, $articlesTable, ['image_url', 'image'], false),
            'image_key' => pick_column($pdo, $articlesTable, ['image_key', 'image_path'], false),
            'active' => $activeColumn,
        ],
        'modeles' => null,
        'marques' => null,
    ];

    if ($modelesTable !== null) {
        $schema['modeles'] = [
            'id' => pick_column($pdo, $modelesTable, ['id']),
            'marque_id' => pick_column($pdo, $modelesTable, ['marque_id', 'brand_id'], false),
            'nom' => pick_column($pdo, $modelesTable, ['nom', 'name', 'title']),
        ];
    }

    if ($marquesTable !== null) {
        $schema['marques'] = [
            'id' => pick_column($pdo, $marquesTable, ['id']),
            'nom' => pick_column($pdo, $marquesTable, ['nom', 'name', 'title']),
        ];
    }

    return $schema;
}

function article_base_price_sql(array $schema, string $tableAlias = 'a'): string
{
    $a = $schema['articles'];

    return repo_price_sql_expr($tableAlias, $a['prix'], (bool) $a['price_is_cents']);
}

function article_promo_price_sql(array $schema, string $tableAlias = 'a'): ?string
{
    $a = $schema['articles'];
    if ($a['promo_price'] === null) {
        return null;
    }

    return repo_price_sql_expr($tableAlias, $a['promo_price'], (bool) $a['promo_price_is_cents']);
}

function article_original_price_sql(array $schema, string $tableAlias = 'a'): string
{
    $a = $schema['articles'];
    if ($a['old_price'] === null) {
        return article_base_price_sql($schema, $tableAlias);
    }

    return repo_price_sql_expr($tableAlias, $a['old_price'], (bool) $a['old_price_is_cents']);
}

function article_effective_price_sql(array $schema, string $tableAlias = 'a'): string
{
    $a = $schema['articles'];
    $basePrice = article_base_price_sql($schema, $tableAlias);
    $promoPrice = article_promo_price_sql($schema, $tableAlias);

    if ($a['promo_active'] === null || $promoPrice === null) {
        return $basePrice;
    }

    return '(CASE WHEN '
        . $tableAlias . '.' . qi($a['promo_active']) . ' = 1'
        . ' AND ' . $promoPrice . ' > 0'
        . ' THEN ' . $promoPrice
        . ' ELSE ' . $basePrice
        . ' END)';
}

function repo_cart_reservations_table(PDO $pdo): ?string
{
    return table_exists($pdo, 'cart_reservations') ? 'cart_reservations' : null;
}

function repo_available_stock_sql(PDO $pdo, string $baseStockSql, ?int $excludeUserId = null): string
{
    $table = repo_cart_reservations_table($pdo);
    if ($table === null) {
        return $baseStockSql;
    }

    $excludeSql = '';
    if ($excludeUserId !== null && $excludeUserId > 0) {
        $excludeSql = ' AND r.' . qi('user_id') . ' <> ' . (int) $excludeUserId;
    }

    $reservedSql = '(SELECT COALESCE(SUM(r.' . qi('qty') . '), 0) '
        . 'FROM ' . qi($table) . ' r '
        . 'WHERE r.' . qi('article_id') . ' = a.' . qi(catalog_schema()['articles']['id']) . ' '
        . 'AND r.' . qi('expires_at') . ' > NOW()'
        . $excludeSql
        . ')';

    return 'GREATEST((' . $baseStockSql . ') - (' . $reservedSql . '), 0)';
}

function repo_reserved_qty_by_article(array $articleIds, ?int $excludeUserId = null): array
{
    $articleIds = array_values(array_unique(array_filter(
        array_map('intval', $articleIds),
        static fn (int $id): bool => $id > 0
    )));
    if ($articleIds === []) {
        return [];
    }

    $pdo = db();
    $table = repo_cart_reservations_table($pdo);
    if ($table === null) {
        return [];
    }

    $placeholders = [];
    $params = [];
    foreach ($articleIds as $index => $articleId) {
        $key = ':article_' . $index;
        $placeholders[] = $key;
        $params[$key] = $articleId;
    }

    $sql = 'SELECT r.' . qi('article_id') . ' AS article_id, SUM(r.' . qi('qty') . ') AS qty '
        . 'FROM ' . qi($table) . ' r '
        . 'WHERE r.' . qi('expires_at') . ' > NOW() '
        . 'AND r.' . qi('article_id') . ' IN (' . implode(', ', $placeholders) . ') ';

    if ($excludeUserId !== null && $excludeUserId > 0) {
        $sql .= 'AND r.' . qi('user_id') . ' <> :exclude_user_id ';
        $params[':exclude_user_id'] = $excludeUserId;
    }

    $sql .= 'GROUP BY r.' . qi('article_id');

    $stmt = $pdo->prepare($sql);
    foreach ($params as $name => $value) {
        $stmt->bindValue($name, $value, PDO::PARAM_INT);
    }
    $stmt->execute();

    $map = [];
    foreach ($stmt->fetchAll() as $row) {
        $articleId = (int) ($row['article_id'] ?? 0);
        $qty = (int) ($row['qty'] ?? 0);
        if ($articleId > 0 && $qty > 0) {
            $map[$articleId] = $qty;
        }
    }

    return $map;
}

function article_select_sql(array $schema, ?string $stockSql = null): string
{
    $a = $schema['articles'];
    $referenceAlt = $a['reference_alt'] ? 'a.' . qi($a['reference_alt']) : 'NULL';
    $description = $a['description'] ? 'a.' . qi($a['description']) : 'NULL';
    $category = $a['category'] ? 'a.' . qi($a['category']) : 'NULL';
    $createdAt = $a['created_at'] ? 'a.' . qi($a['created_at']) : 'NULL';
    $isNew = $a['is_new'] ? 'a.' . qi($a['is_new']) : '0';
    $imageUrl = $a['image_url'] ? 'a.' . qi($a['image_url']) : 'NULL';
    $imageKey = $a['image_key'] ? 'a.' . qi($a['image_key']) : 'NULL';
    $price = article_effective_price_sql($schema, 'a');
    $originalPrice = article_original_price_sql($schema, 'a');
    $promoPrice = article_promo_price_sql($schema, 'a') ?? 'NULL';
    $promoActive = $a['promo_active'] ? 'a.' . qi($a['promo_active']) : '0';
    $stock = $stockSql ?? ('a.' . qi($a['stock']));

    $hasModelJoin = $schema['modeles_table'] !== null
        && $schema['modeles'] !== null
        && $a['modele_id'] !== null;
    $hasBrandJoin = $hasModelJoin
        && $schema['marques_table'] !== null
        && $schema['marques'] !== null
        && $schema['modeles']['marque_id'] !== null;

    $modeleId = $hasModelJoin ? 'm.' . qi($schema['modeles']['id']) : 'NULL';
    $modeleNom = $hasModelJoin ? 'm.' . qi($schema['modeles']['nom']) : 'NULL';

    $marqueId = $hasBrandJoin ? 'b.' . qi($schema['marques']['id']) : 'NULL';
    $marqueNom = $hasBrandJoin ? 'b.' . qi($schema['marques']['nom']) : 'NULL';

    return implode(', ', [
        'a.' . qi($a['id']) . ' AS id',
        'a.' . qi($a['reference']) . ' AS reference',
        $referenceAlt . ' AS sku2',
        'a.' . qi($a['nom']) . ' AS nom',
        $category . ' AS category',
        $description . ' AS description',
        $price . ' AS prix',
        $originalPrice . ' AS price_original',
        $promoPrice . ' AS promo_price',
        $promoActive . ' AS promo_active',
        $stock . ' AS stock',
        $createdAt . ' AS created_at',
        $isNew . ' AS is_new',
        $imageUrl . ' AS image_url',
        $imageKey . ' AS image_key',
        $modeleId . ' AS modele_id',
        $modeleNom . ' AS modele_nom',
        $marqueId . ' AS marque_id',
        $marqueNom . ' AS marque_nom',
    ]);
}

function article_from_sql(array $row): array
{
    $reference = (string) ($row['reference'] ?? '');
    $referenceAlt = trim((string) ($row['sku2'] ?? ''));
    $price = normalize_decimal((float) ($row['prix'] ?? 0));
    $priceOriginal = normalize_decimal((float) ($row['price_original'] ?? $price));
    $rawPromoPrice = normalize_decimal((float) ($row['promo_price'] ?? 0));
    $promoActive = repo_bool_from_mixed($row['promo_active'] ?? false);
    $hasPromo = ($promoActive || $priceOriginal > $price) && $priceOriginal > $price;
    $rawIsNew = $row['is_new'] ?? 0;
    $isNew = in_array((string) $rawIsNew, ['1', 'true', 'TRUE'], true) || $rawIsNew === 1 || $rawIsNew === true;
    $category = trim((string) ($row['category'] ?? ''));
    $rawDescription = (string) ($row['description'] ?? '');
    [$descriptionClean, $descriptionModelHint, $descriptionModelsHint] = parse_description_model_hint($rawDescription);
    $modeleNom = isset($row['modele_nom']) && trim((string) $row['modele_nom']) !== ''
        ? trim((string) $row['modele_nom'])
        : $descriptionModelHint;
    if ($modeleNom === '' && $descriptionModelsHint !== []) {
        $modeleNom = $descriptionModelsHint[0];
    }
    $models = $descriptionModelsHint;
    if ($modeleNom !== '') {
        array_unshift($models, $modeleNom);
    }
    $models = repo_normalize_models($models);
    $marqueName = isset($row['marque_nom']) && trim((string) $row['marque_nom']) !== ''
        ? trim((string) $row['marque_nom'])
        : $category;

    return [
        'id' => is_numeric((string) ($row['id'] ?? '')) ? (int) $row['id'] : (string) ($row['id'] ?? ''),
        'reference' => $reference,
        'sku' => $reference,
        'sku2' => $referenceAlt !== '' ? $referenceAlt : null,
        'nom' => (string) ($row['nom'] ?? ''),
        'title' => (string) ($row['nom'] ?? ''),
        'description' => $descriptionClean,
        'prix' => $price,
        'price' => $price,
        'old_price' => $hasPromo ? $priceOriginal : null,
        'price_original' => $hasPromo ? $priceOriginal : null,
        'promo_active' => $hasPromo,
        'promo_price' => $hasPromo ? ($rawPromoPrice > 0 ? $rawPromoPrice : $price) : null,
        'stock' => (int) ($row['stock'] ?? 0),
        'modele_id' => isset($row['modele_id']) && $row['modele_id'] !== null ? (int) $row['modele_id'] : null,
        'modele' => $modeleNom !== '' ? $modeleNom : null,
        'model' => $modeleNom,
        'modeles' => $models,
        'models' => $models,
        'marque_id' => isset($row['marque_id']) && $row['marque_id'] !== null ? (int) $row['marque_id'] : null,
        'marque' => $marqueName !== '' ? $marqueName : null,
        'brand' => $marqueName,
        'is_new' => $isNew,
        'created_at' => $row['created_at'] ?? null,
        'image' => api_base_path() . '/images/' . rawurlencode($reference),
        'image_ref' => $reference,
        'image_url' => (string) ($row['image_url'] ?? ''),
        'image_key' => (string) ($row['image_key'] ?? ''),
    ];
}

function parse_description_model_hint(string $description): array
{
    $description = trim($description);
    if ($description === '') {
        return ['', '', []];
    }

    if (preg_match('/\[\s*MODELS?\s*:\s*([^\]]+)\]/i', $description, $matches) === 1) {
        $rawModels = trim((string) ($matches[1] ?? ''));
        $models = repo_parse_models_input($rawModels);
        $clean = trim((string) preg_replace('/\[\s*MODELS?\s*:\s*[^\]]+\]/i', '', $description));
        $clean = preg_replace('/\s{2,}/', ' ', $clean) ?? '';
        $primaryModel = $models[0] ?? '';

        return [trim((string) $clean), $primaryModel, $models];
    }

    return [$description, '', []];
}

function repo_add_models_hint_to_description(string $description, array $models): string
{
    [$cleanDescription] = parse_description_model_hint($description);
    $cleanDescription = trim((string) $cleanDescription);
    $normalizedModels = repo_normalize_models($models);

    if ($normalizedModels === []) {
        return $cleanDescription;
    }

    $hint = '[MODELS:' . implode('|', $normalizedModels) . ']';

    return $cleanDescription !== '' ? ($cleanDescription . ' ' . $hint) : $hint;
}

function article_join_sql(array $schema): array
{
    $joins = [];

    if ($schema['modeles_table'] !== null && $schema['articles']['modele_id'] !== null && $schema['modeles'] !== null) {
        $joins[] = 'LEFT JOIN ' . qi($schema['modeles_table']) . ' m ON m.' . qi($schema['modeles']['id']) . ' = a.' . qi($schema['articles']['modele_id']);

        if ($schema['marques_table'] !== null && $schema['modeles']['marque_id'] !== null && $schema['marques'] !== null) {
            $joins[] = 'LEFT JOIN ' . qi($schema['marques_table']) . ' b ON b.' . qi($schema['marques']['id']) . ' = m.' . qi($schema['modeles']['marque_id']);
        }
    }

    return $joins;
}

function article_sort_sql(array $schema, string $sort): string
{
    $a = $schema['articles'];
    $effectivePrice = article_effective_price_sql($schema, 'a');

    return match ($sort) {
        'price_asc' => 'ORDER BY ' . $effectivePrice . ' ASC, a.' . qi($a['id']) . ' DESC',
        'price_desc' => 'ORDER BY ' . $effectivePrice . ' DESC, a.' . qi($a['id']) . ' DESC',
        'stock_desc' => 'ORDER BY a.' . qi($a['stock']) . ' DESC, a.' . qi($a['id']) . ' DESC',
        default => $a['created_at']
            ? 'ORDER BY a.' . qi($a['created_at']) . ' DESC, a.' . qi($a['id']) . ' DESC'
            : 'ORDER BY a.' . qi($a['id']) . ' DESC',
    };
}

function repo_list_articles(array $filters): array
{
    $pdo = db();
    $schema = catalog_schema();

    $page = max(1, (int) ($filters['page'] ?? 1));
    $limit = (int) ($filters['limit'] ?? 24);
    $limit = max(1, min(100, $limit));
    $offset = ($page - 1) * $limit;

    $q = trim((string) ($filters['q'] ?? ''));
    $modeleId = isset($filters['modele_id']) ? (int) $filters['modele_id'] : 0;
    $marqueId = isset($filters['marque_id']) ? (int) $filters['marque_id'] : 0;
    $inStock = !empty($filters['in_stock']);

    $allowedSorts = ['newest', 'price_asc', 'price_desc', 'stock_desc'];
    $sort = (string) ($filters['sort'] ?? 'newest');
    if (!in_array($sort, $allowedSorts, true)) {
        $sort = 'newest';
    }

    $where = [];
    $params = [];

    $a = $schema['articles'];
    $useRawStock = !empty($filters['raw_stock']);
    $stockSql = $useRawStock
        ? ('a.' . qi($a['stock']))
        : repo_available_stock_sql($pdo, 'a.' . qi($a['stock']));

    if ($q !== '') {
        $altReferenceExpr = $a['reference_alt'] !== null ? (' OR a.' . qi($a['reference_alt']) . ' LIKE :q_ref_alt') : '';
        if (strlen($q) >= 3 && !str_contains($q, ' ')) {
            $where[] = '(a.' . qi($a['reference']) . ' LIKE :q_prefix_ref OR a.' . qi($a['nom']) . ' LIKE :q_prefix_name' . $altReferenceExpr . ')';
            $params[':q_prefix_ref'] = $q . '%';
            $params[':q_prefix_name'] = $q . '%';
            if ($a['reference_alt'] !== null) {
                $params[':q_ref_alt'] = $q . '%';
            }
        } else {
            $where[] = '(a.' . qi($a['reference']) . ' LIKE :q_contains_ref OR a.' . qi($a['nom']) . ' LIKE :q_contains_name' . $altReferenceExpr . ')';
            $params[':q_contains_ref'] = '%' . $q . '%';
            $params[':q_contains_name'] = '%' . $q . '%';
            if ($a['reference_alt'] !== null) {
                $params[':q_ref_alt'] = '%' . $q . '%';
            }
        }
    }

    if ($modeleId > 0 && $a['modele_id'] !== null) {
        $where[] = 'a.' . qi($a['modele_id']) . ' = :modele_id';
        $params[':modele_id'] = $modeleId;
    }

    if (
        $marqueId > 0
        && $schema['modeles'] !== null
        && $schema['modeles']['marque_id'] !== null
        && $schema['marques'] !== null
    ) {
        $where[] = 'm.' . qi($schema['modeles']['marque_id']) . ' = :marque_id';
        $params[':marque_id'] = $marqueId;
    }

    if ($inStock) {
        $where[] = $stockSql . ' > 0';
    }

    if ($a['active'] !== null) {
        $where[] = 'a.' . qi($a['active']) . ' = 1';
    }

    $whereSql = $where ? 'WHERE ' . implode(' AND ', $where) : '';
    $joinSql = implode(' ', article_join_sql($schema));

    $countSql = 'SELECT COUNT(*) FROM ' . qi($schema['articles_table']) . ' a ' . $joinSql . ' ' . $whereSql;
    $countStmt = $pdo->prepare($countSql);
    foreach ($params as $name => $value) {
        $countStmt->bindValue($name, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
    }
    $countStmt->execute();
    $total = (int) $countStmt->fetchColumn();

    $sql = 'SELECT ' . article_select_sql($schema, $stockSql)
        . ' FROM ' . qi($schema['articles_table']) . ' a '
        . $joinSql . ' '
        . $whereSql . ' '
        . article_sort_sql($schema, $sort)
        . ' LIMIT :limit OFFSET :offset';

    $stmt = $pdo->prepare($sql);
    foreach ($params as $name => $value) {
        $stmt->bindValue($name, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
    }
    $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();

    $items = array_map('article_from_sql', $stmt->fetchAll());

    return [
        'items' => $items,
        'pagination' => [
            'page' => $page,
            'limit' => $limit,
            'total' => $total,
            'pages' => $limit > 0 ? (int) ceil($total / $limit) : 0,
        ],
        'filters' => [
            'q' => $q,
            'modele_id' => $modeleId > 0 ? $modeleId : null,
            'marque_id' => $marqueId > 0 ? $marqueId : null,
            'in_stock' => $inStock,
            'sort' => $sort,
        ],
    ];
}

function repo_get_article(int $articleId, bool $useAvailableStock = true): ?array
{
    $pdo = db();
    $schema = catalog_schema();
    $a = $schema['articles'];
    $stockSql = $useAvailableStock
        ? repo_available_stock_sql($pdo, 'a.' . qi($a['stock']))
        : ('a.' . qi($a['stock']));

    $sql = 'SELECT ' . article_select_sql($schema, $stockSql)
        . ' FROM ' . qi($schema['articles_table']) . ' a '
        . implode(' ', article_join_sql($schema))
        . ' WHERE a.' . qi($a['id']) . ' = :id LIMIT 1';

    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':id', $articleId, PDO::PARAM_INT);
    $stmt->execute();

    $row = $stmt->fetch();
    if (!is_array($row)) {
        return null;
    }

    return article_from_sql($row);
}

function repo_list_marques(): array
{
    $pdo = db();
    $schema = catalog_schema();

    if ($schema['marques_table'] === null || $schema['marques'] === null) {
        return [];
    }

    $b = $schema['marques'];

    $sql = 'SELECT b.' . qi($b['id']) . ' AS id, b.' . qi($b['nom']) . ' AS nom, COUNT(a.' . qi($schema['articles']['id']) . ') AS article_count '
        . 'FROM ' . qi($schema['marques_table']) . ' b ';

    if ($schema['modeles_table'] !== null && $schema['modeles'] !== null && $schema['modeles']['marque_id'] !== null) {
        $m = $schema['modeles'];
        $sql .= 'LEFT JOIN ' . qi($schema['modeles_table']) . ' m ON m.' . qi($m['marque_id']) . ' = b.' . qi($b['id']) . ' ';
        if ($schema['articles']['modele_id'] !== null) {
            $sql .= 'LEFT JOIN ' . qi($schema['articles_table']) . ' a ON a.' . qi($schema['articles']['modele_id']) . ' = m.' . qi($m['id']) . ' ';
        } else {
            $sql .= 'LEFT JOIN ' . qi($schema['articles_table']) . ' a ON 1 = 0 ';
        }
    } else {
        $sql .= 'LEFT JOIN ' . qi($schema['articles_table']) . ' a ON 1 = 0 ';
    }

    $sql .= 'GROUP BY b.' . qi($b['id']) . ', b.' . qi($b['nom']) . ' ORDER BY b.' . qi($b['nom']) . ' ASC';

    $stmt = $pdo->query($sql);

    return array_map(
        static fn (array $row): array => [
            'id' => (int) $row['id'],
            'nom' => (string) $row['nom'],
            'article_count' => (int) $row['article_count'],
        ],
        $stmt->fetchAll()
    );
}

function repo_list_modeles(?int $marqueId = null): array
{
    $pdo = db();
    $schema = catalog_schema();

    if ($schema['modeles_table'] === null || $schema['modeles'] === null) {
        return [];
    }

    $m = $schema['modeles'];
    $a = $schema['articles'];

    $sql = 'SELECT m.' . qi($m['id']) . ' AS id, m.' . qi($m['nom']) . ' AS nom';
    if ($m['marque_id'] !== null) {
        $sql .= ', m.' . qi($m['marque_id']) . ' AS marque_id';
    } else {
        $sql .= ', NULL AS marque_id';
    }

    if ($schema['marques_table'] !== null && $schema['marques'] !== null && $m['marque_id'] !== null) {
        $sql .= ', b.' . qi($schema['marques']['nom']) . ' AS marque_nom';
    } else {
        $sql .= ', NULL AS marque_nom';
    }

    $sql .= ', COUNT(a.' . qi($a['id']) . ') AS article_count '
        . 'FROM ' . qi($schema['modeles_table']) . ' m ';

    if ($schema['marques_table'] !== null && $schema['marques'] !== null && $m['marque_id'] !== null) {
        $sql .= 'LEFT JOIN ' . qi($schema['marques_table']) . ' b ON b.' . qi($schema['marques']['id']) . ' = m.' . qi($m['marque_id']) . ' ';
    }

    if ($a['modele_id'] !== null) {
        $sql .= 'LEFT JOIN ' . qi($schema['articles_table']) . ' a ON a.' . qi($a['modele_id']) . ' = m.' . qi($m['id']) . ' ';
    } else {
        $sql .= 'LEFT JOIN ' . qi($schema['articles_table']) . ' a ON 1 = 0 ';
    }

    $params = [];
    if ($marqueId !== null && $marqueId > 0 && $m['marque_id'] !== null) {
        $sql .= 'WHERE m.' . qi($m['marque_id']) . ' = :marque_id ';
        $params[':marque_id'] = $marqueId;
    }

    $sql .= 'GROUP BY m.' . qi($m['id']) . ', m.' . qi($m['nom']);
    if ($m['marque_id'] !== null) {
        $sql .= ', m.' . qi($m['marque_id']);
    }
    $sql .= ' ORDER BY m.' . qi($m['nom']) . ' ASC';

    $stmt = $pdo->prepare($sql);
    foreach ($params as $name => $value) {
        $stmt->bindValue($name, $value, PDO::PARAM_INT);
    }
    $stmt->execute();

    return array_map(
        static fn (array $row): array => [
            'id' => (int) $row['id'],
            'nom' => (string) $row['nom'],
            'marque_id' => isset($row['marque_id']) && $row['marque_id'] !== null ? (int) $row['marque_id'] : null,
            'marque_nom' => $row['marque_nom'] !== null ? (string) $row['marque_nom'] : null,
            'article_count' => (int) $row['article_count'],
        ],
        $stmt->fetchAll()
    );
}

function repo_find_articles_by_ids(array $articleIds): array
{
    $articleIds = array_values(array_unique(array_filter(array_map('intval', $articleIds), static fn (int $id): bool => $id > 0)));
    if ($articleIds === []) {
        return [];
    }

    $pdo = db();
    $schema = catalog_schema();
    $a = $schema['articles'];

    $placeholders = [];
    $params = [];
    foreach ($articleIds as $index => $articleId) {
        $key = ':id_' . $index;
        $placeholders[] = $key;
        $params[$key] = $articleId;
    }

    $sql = 'SELECT ' . article_select_sql($schema)
        . ' FROM ' . qi($schema['articles_table']) . ' a '
        . implode(' ', article_join_sql($schema))
        . ' WHERE a.' . qi($a['id']) . ' IN (' . implode(', ', $placeholders) . ')';

    $stmt = $pdo->prepare($sql);
    foreach ($params as $name => $value) {
        $stmt->bindValue($name, $value, PDO::PARAM_INT);
    }
    $stmt->execute();

    $rows = $stmt->fetchAll();
    $result = [];

    foreach ($rows as $row) {
        $mapped = article_from_sql($row);
        $result[(int) $mapped['id']] = $mapped;
    }

    return $result;
}

function repo_find_article_for_cart(int $articleId): ?array
{
    $rows = repo_find_articles_by_ids([$articleId]);

    return $rows[$articleId] ?? null;
}

function repo_update_article_stock(int $articleId, int $qty): bool
{
    $qty = max(1, $qty);
    $pdo = db();
    $schema = catalog_schema();
    $articleColumns = table_columns($pdo, $schema['articles_table']);
    $stockColumns = [$schema['articles']['stock']];
    foreach (['stock_quantity', 'stock', 'quantity', 'qty'] as $stockColumnName) {
        if (!isset($articleColumns[$stockColumnName]) || in_array($stockColumnName, $stockColumns, true)) {
            continue;
        }
        $stockColumns[] = $stockColumnName;
    }

    $setParts = [];
    foreach ($stockColumns as $stockColumn) {
        $setParts[] = qi($stockColumn) . ' = GREATEST(COALESCE(' . qi($stockColumn) . ', 0) - :qty_dec, 0)';
    }

    $sql = 'UPDATE ' . qi($schema['articles_table'])
        . ' SET ' . implode(', ', $setParts) . ' '
        . 'WHERE ' . qi($schema['articles']['id']) . ' = :id '
        . 'AND ' . qi($schema['articles']['stock']) . ' >= :qty_min';

    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':qty_dec', $qty, PDO::PARAM_INT);
    $stmt->bindValue(':qty_min', $qty, PDO::PARAM_INT);
    $stmt->bindValue(':id', $articleId, PDO::PARAM_INT);
    $stmt->execute();

    return $stmt->rowCount() > 0;
}

function repo_restore_article_stock(int $articleId, int $qty): bool
{
    $qty = max(1, $qty);
    $pdo = db();
    $schema = catalog_schema();

    $articleColumns = table_columns($pdo, $schema['articles_table']);
    $stockColumns = [$schema['articles']['stock']];
    foreach (['stock_quantity', 'stock', 'quantity', 'qty'] as $stockColumnName) {
        if (!isset($articleColumns[$stockColumnName]) || in_array($stockColumnName, $stockColumns, true)) {
            continue;
        }
        $stockColumns[] = $stockColumnName;
    }

    $setParts = [];
    foreach ($stockColumns as $stockColumn) {
        $setParts[] = qi($stockColumn) . ' = COALESCE(' . qi($stockColumn) . ', 0) + :qty_inc';
    }

    $sql = 'UPDATE ' . qi($schema['articles_table'])
        . ' SET ' . implode(', ', $setParts)
        . ' WHERE ' . qi($schema['articles']['id']) . ' = :id LIMIT 1';

    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':qty_inc', $qty, PDO::PARAM_INT);
    $stmt->bindValue(':id', $articleId, PDO::PARAM_INT);
    $stmt->execute();

    return $stmt->rowCount() > 0;
}

function repo_find_article_image_source(string $reference): ?array
{
    $pdo = db();
    $schema = catalog_schema();
    $a = $schema['articles'];

    $imageUrlSql = $a['image_url'] ? 'a.' . qi($a['image_url']) : 'NULL';
    $imageKeySql = $a['image_key'] ? 'a.' . qi($a['image_key']) : 'NULL';

    $sql = 'SELECT '
        . 'a.' . qi($a['id']) . ' AS id, '
        . 'a.' . qi($a['reference']) . ' AS reference, '
        . $imageUrlSql . ' AS image_url, '
        . $imageKeySql . ' AS image_key '
        . 'FROM ' . qi($schema['articles_table']) . ' a '
        . 'WHERE (UPPER(a.' . qi($a['reference']) . ') = UPPER(:reference_primary)';
    if ($a['reference_alt'] !== null) {
        $sql .= ' OR UPPER(a.' . qi($a['reference_alt']) . ') = UPPER(:reference_alt)';
    }
    $sql .= ') '
        . 'LIMIT 1';

    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':reference_primary', $reference, PDO::PARAM_STR);
    if ($a['reference_alt'] !== null) {
        $stmt->bindValue(':reference_alt', $reference, PDO::PARAM_STR);
    }
    $stmt->execute();

    $row = $stmt->fetch();
    if (!is_array($row)) {
        return null;
    }

    $articleId = (int) ($row['id'] ?? 0);
    $imageKey = trim((string) ($row['image_key'] ?? ''));
    if ($imageKey === '') {
        $imageKey = repo_find_image_key_for_article($articleId) ?? '';
    }

    return [
        'article_id' => $articleId,
        'reference' => (string) ($row['reference'] ?? $reference),
        'image_url' => trim((string) ($row['image_url'] ?? '')),
        'image_key' => $imageKey,
    ];
}

function repo_find_image_key_for_article(int $articleId): ?string
{
    if ($articleId <= 0) {
        return null;
    }

    $pdo = db();
    $table = table_exists($pdo, 'images_articles') ? 'images_articles' : null;
    if ($table === null) {
        return null;
    }

    $articleIdCol = pick_column($pdo, $table, ['article_id', 'product_id'], false);
    $imageKeyCol = pick_column($pdo, $table, ['image_key', 'url', 'path'], false);
    $orderCol = pick_column($pdo, $table, ['ordre', 'sort_order', 'position'], false);

    if ($articleIdCol === null || $imageKeyCol === null) {
        return null;
    }

    $sql = 'SELECT ' . qi($imageKeyCol) . ' AS image_key '
        . 'FROM ' . qi($table)
        . ' WHERE ' . qi($articleIdCol) . ' = :article_id ';

    if ($orderCol !== null) {
        $sql .= 'ORDER BY ' . qi($orderCol) . ' ASC ';
    }

    $sql .= 'LIMIT 1';

    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':article_id', $articleId, PDO::PARAM_INT);
    $stmt->execute();

    $value = $stmt->fetchColumn();
    if (!is_string($value) || trim($value) === '') {
        return null;
    }

    return trim($value);
}

function repo_parse_article_payload(array $payload): array
{
    $reference = strtoupper(trim((string) ($payload['reference'] ?? $payload['sku'] ?? '')));
    $referenceAlt = strtoupper(trim((string) ($payload['reference_alt'] ?? $payload['sku2'] ?? '')));
    $title = trim((string) ($payload['nom'] ?? $payload['title'] ?? $payload['name'] ?? ''));
    $description = trim((string) ($payload['description'] ?? ''));
    $category = strtoupper(trim((string) ($payload['category'] ?? '')));
    $brand = strtoupper(trim((string) ($payload['marque'] ?? $payload['brand'] ?? '')));
    $model = trim((string) ($payload['modele'] ?? $payload['model'] ?? ''));
    $models = repo_parse_models_input($payload['modeles'] ?? $payload['models'] ?? []);
    if ($model !== '') {
        array_unshift($models, $model);
    }
    $models = repo_normalize_models($models);
    $model = $models[0] ?? '';
    $image = trim((string) ($payload['image_url'] ?? $payload['image_key'] ?? $payload['image'] ?? ''));
    $modeleId = (int) ($payload['modele_id'] ?? 0);
    $priceRaw = $payload['prix'] ?? $payload['price'] ?? 0;
    $promoEnabledRaw = $payload['promo_enabled'] ?? $payload['promo_active'] ?? false;
    $promoPriceRaw = $payload['promo_price'] ?? $payload['prix_promo'] ?? null;
    $stockRaw = $payload['stock'] ?? 0;

    if ($reference === '' || !validate_reference($reference)) {
        throw new InvalidArgumentException('Reference SKU invalide.');
    }
    if ($referenceAlt !== '' && !validate_reference($referenceAlt)) {
        throw new InvalidArgumentException('Reference SKU2 invalide.');
    }
    if ($title === '') {
        throw new InvalidArgumentException('Le nom du produit est requis.');
    }

    $price = is_numeric($priceRaw) ? (float) $priceRaw : -1;
    if ($price < 0) {
        throw new InvalidArgumentException('Prix invalide.');
    }

    $stock = is_numeric($stockRaw) ? (int) floor((float) $stockRaw) : -1;
    if ($stock < 0) {
        throw new InvalidArgumentException('Stock invalide.');
    }

    $promoEnabled = repo_bool_from_mixed($promoEnabledRaw);
    $promoPrice = null;
    if ($promoEnabled) {
        $promoPriceValue = is_numeric($promoPriceRaw) ? (float) $promoPriceRaw : -1;
        if ($promoPriceValue <= 0 || $promoPriceValue >= $price) {
            throw new InvalidArgumentException('Prix promo invalide.');
        }
        $promoPrice = normalize_decimal($promoPriceValue);
    }

    return [
        'reference' => $reference,
        'reference_alt' => $referenceAlt,
        'title' => $title,
        'description' => $description,
        'category' => $category,
        'brand' => $brand,
        'model' => $model,
        'models' => $models,
        'image' => $image,
        'modele_id' => $modeleId,
        'price' => normalize_decimal($price),
        'promo_enabled' => $promoEnabled,
        'promo_price' => $promoPrice,
        'stock' => $stock,
    ];
}

function repo_insert_dynamic(PDO $pdo, string $table, array $data): string
{
    if ($data === []) {
        throw new RuntimeException('Insertion vide impossible.');
    }

    $columns = array_keys($data);
    $sql = 'INSERT INTO ' . qi($table)
        . ' (' . implode(', ', array_map('qi', $columns)) . ') '
        . 'VALUES (' . implode(', ', array_map(static fn (string $col): string => ':' . $col, $columns)) . ')';

    $stmt = $pdo->prepare($sql);
    foreach ($data as $column => $value) {
        if (is_int($value)) {
            $stmt->bindValue(':' . $column, $value, PDO::PARAM_INT);
        } elseif ($value === null) {
            $stmt->bindValue(':' . $column, null, PDO::PARAM_NULL);
        } else {
            $stmt->bindValue(':' . $column, (string) $value, PDO::PARAM_STR);
        }
    }
    $stmt->execute();

    return (string) $pdo->lastInsertId();
}

function repo_get_article_by_reference(string $reference): ?array
{
    $reference = strtoupper(trim($reference));
    if ($reference === '') {
        return null;
    }

    $pdo = db();
    $schema = catalog_schema();
    $a = $schema['articles'];

    $sql = 'SELECT ' . article_select_sql($schema)
        . ' FROM ' . qi($schema['articles_table']) . ' a '
        . implode(' ', article_join_sql($schema))
        . ' WHERE (UPPER(a.' . qi($a['reference']) . ') = UPPER(:reference_primary)';
    if ($a['reference_alt'] !== null) {
        $sql .= ' OR UPPER(a.' . qi($a['reference_alt']) . ') = UPPER(:reference_alt)';
    }
    $sql .= ') LIMIT 1';

    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':reference_primary', $reference, PDO::PARAM_STR);
    if ($a['reference_alt'] !== null) {
        $stmt->bindValue(':reference_alt', $reference, PDO::PARAM_STR);
    }
    $stmt->execute();

    $row = $stmt->fetch();
    if (!is_array($row)) {
        return null;
    }

    return article_from_sql($row);
}

function repo_resolve_modele_id(array $schema, string $brand, string $model): ?int
{
    if (
        $model === ''
        || $schema['modeles_table'] === null
        || $schema['modeles'] === null
    ) {
        return null;
    }

    $pdo = db();
    $m = $schema['modeles'];

    $sql = 'SELECT m.' . qi($m['id']) . ' AS id FROM ' . qi($schema['modeles_table']) . ' m ';
    $params = [
        ':model_name' => strtolower($model),
    ];

    if (
        $brand !== ''
        && $schema['marques_table'] !== null
        && $schema['marques'] !== null
        && $m['marque_id'] !== null
    ) {
        $sql .= 'INNER JOIN ' . qi($schema['marques_table']) . ' b ON b.' . qi($schema['marques']['id']) . ' = m.' . qi($m['marque_id']) . ' ';
        $sql .= 'WHERE LOWER(m.' . qi($m['nom']) . ') = :model_name AND LOWER(b.' . qi($schema['marques']['nom']) . ') = :brand_name ';
        $params[':brand_name'] = strtolower($brand);
    } else {
        $sql .= 'WHERE LOWER(m.' . qi($m['nom']) . ') = :model_name ';
    }

    $sql .= 'ORDER BY m.' . qi($m['id']) . ' DESC LIMIT 1';

    $stmt = $pdo->prepare($sql);
    foreach ($params as $name => $value) {
        $stmt->bindValue($name, $value, PDO::PARAM_STR);
    }
    $stmt->execute();

    $value = $stmt->fetchColumn();
    if (!is_numeric((string) $value)) {
        return null;
    }

    return (int) $value;
}

function repo_create_article(array $payload): array
{
    $data = repo_parse_article_payload($payload);
    $pdo = db();
    $schema = catalog_schema();
    $a = $schema['articles'];

    $existing = repo_get_article_by_reference($data['reference']);
    if ($existing !== null) {
        throw new RuntimeException('Cette reference existe deja.');
    }

    $row = [
        $a['reference'] => $data['reference'],
        $a['nom'] => $data['title'],
        $a['stock'] => $data['stock'],
    ];
    if ($a['reference_alt'] !== null && $data['reference_alt'] !== '') {
        $row[$a['reference_alt']] = $data['reference_alt'];
    }
    if ($a['active'] !== null) {
        $row[$a['active']] = 1;
    }

    $row[$a['prix']] = $a['price_is_cents']
        ? (int) round($data['price'] * 100)
        : $data['price'];

    if ($a['promo_active'] !== null) {
        $row[$a['promo_active']] = $data['promo_enabled'] ? 1 : 0;
    }

    if ($a['promo_price'] !== null) {
        $promoPriceValue = 0;
        if ($data['promo_enabled'] && is_float($data['promo_price'])) {
            $promoPriceValue = $a['promo_price_is_cents']
                ? (int) round($data['promo_price'] * 100)
                : $data['promo_price'];
        }
        $row[$a['promo_price']] = $promoPriceValue;
    }

    if ($a['old_price'] !== null) {
        $row[$a['old_price']] = $a['old_price_is_cents']
            ? (int) round($data['price'] * 100)
            : $data['price'];
    }

    if ($a['description'] !== null) {
        $storedDescription = repo_add_models_hint_to_description(
            $data['description'],
            $data['models'] ?? []
        );
        if ($storedDescription !== '') {
            $row[$a['description']] = $storedDescription;
        }
    }
    if ($a['category'] !== null) {
        $category = $data['category'] !== '' ? $data['category'] : ($data['brand'] !== '' ? $data['brand'] : 'GENERAL');
        $row[$a['category']] = $category;
    }
    if ($a['created_at'] !== null) {
        $row[$a['created_at']] = date('Y-m-d H:i:s');
    }
    if ($a['updated_at'] !== null) {
        $row[$a['updated_at']] = date('Y-m-d H:i:s');
    }
    if ($a['is_new'] !== null) {
        $row[$a['is_new']] = 1;
    }

    if ($data['image'] !== '') {
        $isImageUrl = filter_var($data['image'], FILTER_VALIDATE_URL) !== false;
        if ($a['image_url'] !== null && $isImageUrl) {
            $row[$a['image_url']] = $data['image'];
            if ($a['image_key'] !== null && $a['image_key'] === 'image_path') {
                $row[$a['image_key']] = $data['image'];
            }
        } elseif ($a['image_key'] !== null) {
            $row[$a['image_key']] = $data['image'];
        }
    }

    if ($a['modele_id'] !== null) {
        $modeleId = $data['modele_id'];
        if ($modeleId <= 0) {
            $modeleId = repo_resolve_modele_id($schema, $data['brand'], $data['model']) ?? 0;
        }
        if ($modeleId <= 0) {
            throw new InvalidArgumentException('modele_id requis pour la table articles.');
        }
        $row[$a['modele_id']] = $modeleId;
    }

    try {
        repo_insert_dynamic($pdo, $schema['articles_table'], $row);
    } catch (PDOException $exception) {
        if ($exception->getCode() === '23000') {
            throw new RuntimeException('Reference deja existante ou contrainte SQL invalide.');
        }
        throw $exception;
    }

    $created = repo_get_article_by_reference($data['reference']);
    if ($created === null) {
        throw new RuntimeException('Produit cree mais introuvable apres insertion.');
    }

    return $created;
}

function repo_update_article(int $articleId, array $payload): array
{
    if ($articleId <= 0) {
        throw new InvalidArgumentException('ID article invalide.');
    }

    $pdo = db();
    $schema = catalog_schema();
    $a = $schema['articles'];

    $existing = repo_get_article($articleId);
    if ($existing === null) {
        throw new RuntimeException('Article introuvable.');
    }

    $row = [];

    if (array_key_exists('sku', $payload) || array_key_exists('reference', $payload)) {
        $reference = strtoupper(trim((string) ($payload['reference'] ?? $payload['sku'] ?? '')));
        if ($reference === '' || !validate_reference($reference)) {
            throw new InvalidArgumentException('Reference SKU invalide.');
        }
        $row[$a['reference']] = $reference;
    }

    if (array_key_exists('sku2', $payload) || array_key_exists('reference_alt', $payload)) {
        if ($a['reference_alt'] === null) {
            throw new InvalidArgumentException('Colonne SKU2 introuvable sur ce schema.');
        }
        $referenceAlt = strtoupper(trim((string) ($payload['reference_alt'] ?? $payload['sku2'] ?? '')));
        if ($referenceAlt !== '' && !validate_reference($referenceAlt)) {
            throw new InvalidArgumentException('Reference SKU2 invalide.');
        }
        $row[$a['reference_alt']] = $referenceAlt !== '' ? $referenceAlt : null;
    }

    if (array_key_exists('title', $payload) || array_key_exists('nom', $payload) || array_key_exists('name', $payload)) {
        $title = trim((string) ($payload['title'] ?? $payload['nom'] ?? $payload['name'] ?? ''));
        if ($title === '') {
            throw new InvalidArgumentException('Le nom du produit est requis.');
        }
        $row[$a['nom']] = $title;
    }

    if (array_key_exists('price', $payload) || array_key_exists('prix', $payload)) {
        $priceRaw = $payload['price'] ?? $payload['prix'];
        $price = is_numeric($priceRaw) ? (float) $priceRaw : -1;
        if ($price < 0) {
            throw new InvalidArgumentException('Prix invalide.');
        }

        $normalizedPrice = normalize_decimal($price);
        $row[$a['prix']] = $a['price_is_cents']
            ? (int) round($normalizedPrice * 100)
            : $normalizedPrice;

        // Keep legacy alternate price columns aligned when they coexist.
        $articleColumns = table_columns($pdo, $schema['articles_table']);
        foreach (['prix', 'price', 'price_cents'] as $priceColumnName) {
            if ($priceColumnName === $a['prix'] || !isset($articleColumns[$priceColumnName])) {
                continue;
            }
            $row[$priceColumnName] = str_contains($priceColumnName, 'cents')
                ? (int) round($normalizedPrice * 100)
                : $normalizedPrice;
        }

        if ($a['old_price'] !== null) {
            $row[$a['old_price']] = $a['old_price_is_cents']
                ? (int) round($normalizedPrice * 100)
                : $normalizedPrice;
        }

        if ($a['promo_active'] !== null && $a['promo_price'] !== null) {
            $promoActive = repo_bool_from_mixed($existing['promo_active'] ?? false);
            $promoPrice = is_numeric($existing['promo_price'] ?? null)
                ? (float) $existing['promo_price']
                : 0.0;
            if ($promoActive && $promoPrice > 0 && $promoPrice >= $normalizedPrice) {
                $row[$a['promo_active']] = 0;
                $row[$a['promo_price']] = $a['promo_price_is_cents'] ? 0 : 0.0;
            }
        }
    }

    if (array_key_exists('stock', $payload)) {
        $stockRaw = $payload['stock'];
        $stock = is_numeric($stockRaw) ? (int) floor((float) $stockRaw) : -1;
        if ($stock < 0) {
            throw new InvalidArgumentException('Stock invalide.');
        }
        $row[$a['stock']] = $stock;

        // Some legacy schemas keep multiple stock columns; keep them aligned on admin update.
        $articleColumns = table_columns($pdo, $schema['articles_table']);
        foreach (['stock_quantity', 'stock', 'quantity', 'qty'] as $stockColumnName) {
            if ($stockColumnName === $a['stock'] || !isset($articleColumns[$stockColumnName])) {
                continue;
            }
            $row[$stockColumnName] = $stock;
        }
    }

    $brandProvided = array_key_exists('brand', $payload) || array_key_exists('marque', $payload);
    $modelProvided = array_key_exists('model', $payload) || array_key_exists('modele', $payload);
    $modelsProvided = array_key_exists('modeles', $payload) || array_key_exists('models', $payload);
    $brand = strtoupper(trim((string) ($payload['marque'] ?? $payload['brand'] ?? '')));
    $model = trim((string) ($payload['modele'] ?? $payload['model'] ?? ''));
    $models = repo_parse_models_input($payload['modeles'] ?? $payload['models'] ?? []);
    if ($model !== '') {
        array_unshift($models, $model);
    }
    $models = repo_normalize_models($models);
    $primaryModel = $models[0] ?? '';

    if ($a['category'] !== null && $brandProvided && $brand !== '') {
        $row[$a['category']] = $brand;
    }

    if ($a['description'] !== null && ($modelProvided || $modelsProvided)) {
        $baseDescription = array_key_exists('description', $payload)
            ? trim((string) ($payload['description'] ?? ''))
            : trim((string) ($existing['description'] ?? ''));
        $storedDescription = repo_add_models_hint_to_description($baseDescription, $models);
        if ($storedDescription !== '' || array_key_exists('description', $payload)) {
            $row[$a['description']] = $storedDescription;
        }
    }

    if ($a['modele_id'] !== null && ($brandProvided || $modelProvided || $modelsProvided)) {
        $resolvedBrand = $brandProvided
            ? $brand
            : strtoupper(trim((string) ($existing['brand'] ?? $existing['marque'] ?? '')));
        $resolvedModel = ($modelProvided || $modelsProvided)
            ? $primaryModel
            : trim((string) ($existing['model'] ?? $existing['modele'] ?? ''));

        if ($resolvedModel !== '') {
            $resolvedModeleId = repo_resolve_modele_id($schema, $resolvedBrand, $resolvedModel);
            if ($resolvedModeleId !== null && $resolvedModeleId > 0) {
                $row[$a['modele_id']] = $resolvedModeleId;
            }
        }
    }

    if (array_key_exists('image_url', $payload) || array_key_exists('image_key', $payload) || array_key_exists('image', $payload)) {
        $image = trim((string) ($payload['image_url'] ?? $payload['image_key'] ?? $payload['image'] ?? ''));
        $isImageUrl = filter_var($image, FILTER_VALIDATE_URL) !== false;

        if ($a['image_url'] !== null) {
            if ($image === '') {
                $row[$a['image_url']] = null;
            } elseif ($isImageUrl) {
                $row[$a['image_url']] = $image;
            } elseif ($a['image_key'] === null) {
                throw new InvalidArgumentException('URL image invalide.');
            } else {
                $row[$a['image_url']] = null;
            }
        }

        if ($a['image_key'] !== null) {
            if ($image === '') {
                $row[$a['image_key']] = null;
            } elseif ($isImageUrl) {
                // Keep products.image_path in sync even if image_url exists in hybrid schemas.
                $mustStoreAbsoluteInImageKey = $a['image_url'] === null || $a['image_key'] === 'image_path';
                $row[$a['image_key']] = $mustStoreAbsoluteInImageKey ? $image : null;
            } else {
                $row[$a['image_key']] = $image;
            }
        }
    }

    if ($row !== [] && $a['updated_at'] !== null) {
        $row[$a['updated_at']] = date('Y-m-d H:i:s');
    }

    if ($row === []) {
        return $existing;
    }

    $setParts = [];
    foreach ($row as $column => $unused) {
        $setParts[] = qi($column) . ' = :set_' . $column;
    }

    $sql = 'UPDATE ' . qi($schema['articles_table'])
        . ' SET ' . implode(', ', $setParts)
        . ' WHERE ' . qi($a['id']) . ' = :article_id LIMIT 1';

    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':article_id', $articleId, PDO::PARAM_INT);
    foreach ($row as $column => $value) {
        $param = ':set_' . $column;
        if (is_int($value)) {
            $stmt->bindValue($param, $value, PDO::PARAM_INT);
        } elseif ($value === null) {
            $stmt->bindValue($param, null, PDO::PARAM_NULL);
        } else {
            $stmt->bindValue($param, (string) $value, PDO::PARAM_STR);
        }
    }

    try {
        $stmt->execute();
    } catch (PDOException $exception) {
        if ($exception->getCode() === '23000') {
            throw new RuntimeException('Conflit SQL pendant la mise a jour de l\'article.');
        }
        throw $exception;
    }

    $updated = repo_get_article($articleId, false);
    if ($updated === null) {
        throw new RuntimeException('Article introuvable apres mise a jour.');
    }

    return $updated;
}

function repo_delete_article(int $articleId): array
{
    if ($articleId <= 0) {
        throw new InvalidArgumentException('ID article invalide.');
    }

    $pdo = db();
    $schema = catalog_schema();
    $a = $schema['articles'];

    $existing = repo_get_article($articleId, false);
    if ($existing === null) {
        throw new RuntimeException('Article introuvable.');
    }

    if ($a['active'] !== null) {
        $row = [
            $a['active'] => 0,
        ];
        if ($a['updated_at'] !== null) {
            $row[$a['updated_at']] = date('Y-m-d H:i:s');
        }

        $setParts = [];
        foreach ($row as $column => $unused) {
            $setParts[] = qi($column) . ' = :set_' . $column;
        }

        $sql = 'UPDATE ' . qi($schema['articles_table'])
            . ' SET ' . implode(', ', $setParts)
            . ' WHERE ' . qi($a['id']) . ' = :article_id LIMIT 1';

        $stmt = $pdo->prepare($sql);
        $stmt->bindValue(':article_id', $articleId, PDO::PARAM_INT);
        foreach ($row as $column => $value) {
            $param = ':set_' . $column;
            if (is_int($value)) {
                $stmt->bindValue($param, $value, PDO::PARAM_INT);
            } elseif ($value === null) {
                $stmt->bindValue($param, null, PDO::PARAM_NULL);
            } else {
                $stmt->bindValue($param, (string) $value, PDO::PARAM_STR);
            }
        }
        $stmt->execute();

        return [
            'id' => $articleId,
            'deleted' => true,
            'mode' => 'soft',
        ];
    }

    try {
        $pdo->beginTransaction();

        if (table_exists($pdo, 'images_articles')) {
            $imageTable = 'images_articles';
            $imageArticleIdCol = pick_column($pdo, $imageTable, ['article_id', 'product_id'], false);
            if ($imageArticleIdCol !== null) {
                $stmt = $pdo->prepare(
                    'DELETE FROM ' . qi($imageTable) . ' WHERE ' . qi($imageArticleIdCol) . ' = :article_id'
                );
                $stmt->bindValue(':article_id', $articleId, PDO::PARAM_INT);
                $stmt->execute();
            }
        }

        if (table_exists($pdo, 'cart_reservations')) {
            $cartTable = 'cart_reservations';
            $cartArticleIdCol = pick_column($pdo, $cartTable, ['article_id', 'product_id'], false);
            if ($cartArticleIdCol !== null) {
                $stmt = $pdo->prepare(
                    'DELETE FROM ' . qi($cartTable) . ' WHERE ' . qi($cartArticleIdCol) . ' = :article_id'
                );
                $stmt->bindValue(':article_id', $articleId, PDO::PARAM_INT);
                $stmt->execute();
            }
        }

        $stmt = $pdo->prepare(
            'DELETE FROM ' . qi($schema['articles_table'])
            . ' WHERE ' . qi($a['id']) . ' = :article_id LIMIT 1'
        );
        $stmt->bindValue(':article_id', $articleId, PDO::PARAM_INT);
        $stmt->execute();

        if ($stmt->rowCount() <= 0) {
            throw new RuntimeException('Article introuvable.');
        }

        $pdo->commit();
    } catch (Throwable $exception) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }

        if ($exception instanceof PDOException && $exception->getCode() === '23000') {
            throw new RuntimeException('Suppression impossible: article lie a des commandes.');
        }

        throw $exception;
    }

    return [
        'id' => $articleId,
        'deleted' => true,
        'mode' => 'hard',
    ];
}

function repo_create_marque(array $payload): array
{
    $pdo = db();
    $schema = catalog_schema();
    if ($schema['marques_table'] === null || $schema['marques'] === null) {
        throw new RuntimeException('Table marques introuvable.');
    }

    $b = $schema['marques'];
    $nom = strtoupper(trim((string) ($payload['nom'] ?? $payload['name'] ?? '')));
    if ($nom === '') {
        throw new InvalidArgumentException('Nom de marque requis.');
    }

    $sql = 'SELECT b.' . qi($b['id']) . ' AS id FROM ' . qi($schema['marques_table']) . ' b '
        . 'WHERE LOWER(b.' . qi($b['nom']) . ') = :nom LIMIT 1';
    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':nom', strtolower($nom), PDO::PARAM_STR);
    $stmt->execute();
    if (is_array($stmt->fetch())) {
        throw new RuntimeException('Cette marque existe deja.');
    }

    $row = [
        $b['nom'] => $nom,
    ];

    repo_insert_dynamic($pdo, $schema['marques_table'], $row);
    $id = (int) $pdo->lastInsertId();

    if ($id > 0) {
        return [
            'id' => $id,
            'nom' => $nom,
            'article_count' => 0,
        ];
    }

    $stmt = $pdo->prepare(
        'SELECT b.' . qi($b['id']) . ' AS id, b.' . qi($b['nom']) . ' AS nom '
        . 'FROM ' . qi($schema['marques_table']) . ' b '
        . 'WHERE LOWER(b.' . qi($b['nom']) . ') = :nom LIMIT 1'
    );
    $stmt->bindValue(':nom', strtolower($nom), PDO::PARAM_STR);
    $stmt->execute();
    $row = $stmt->fetch();
    if (!is_array($row)) {
        throw new RuntimeException('Marque creee mais introuvable.');
    }

    return [
        'id' => (int) ($row['id'] ?? 0),
        'nom' => (string) ($row['nom'] ?? $nom),
        'article_count' => 0,
    ];
}

function repo_create_modele(array $payload): array
{
    $pdo = db();
    $schema = catalog_schema();
    if ($schema['modeles_table'] === null || $schema['modeles'] === null) {
        throw new RuntimeException('Table modeles introuvable.');
    }

    $m = $schema['modeles'];
    $nom = trim((string) ($payload['nom'] ?? $payload['name'] ?? ''));
    if ($nom === '') {
        throw new InvalidArgumentException('Nom de modele requis.');
    }

    $marqueId = (int) ($payload['marque_id'] ?? 0);
    if ($m['marque_id'] !== null && $marqueId <= 0) {
        throw new InvalidArgumentException('marque_id requis pour ce modele.');
    }

    if (
        $m['marque_id'] !== null
        && $schema['marques_table'] !== null
        && $schema['marques'] !== null
    ) {
        $stmt = $pdo->prepare(
            'SELECT 1 FROM ' . qi($schema['marques_table'])
            . ' WHERE ' . qi($schema['marques']['id']) . ' = :id LIMIT 1'
        );
        $stmt->bindValue(':id', $marqueId, PDO::PARAM_INT);
        $stmt->execute();
        if (!(bool) $stmt->fetchColumn()) {
            throw new InvalidArgumentException('Marque introuvable.');
        }
    }

    $sql = 'SELECT m.' . qi($m['id']) . ' AS id FROM ' . qi($schema['modeles_table']) . ' m '
        . 'WHERE LOWER(m.' . qi($m['nom']) . ') = :nom ';
    if ($m['marque_id'] !== null) {
        $sql .= 'AND m.' . qi($m['marque_id']) . ' = :marque_id ';
    }
    $sql .= 'LIMIT 1';
    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':nom', strtolower($nom), PDO::PARAM_STR);
    if ($m['marque_id'] !== null) {
        $stmt->bindValue(':marque_id', $marqueId, PDO::PARAM_INT);
    }
    $stmt->execute();
    if (is_array($stmt->fetch())) {
        throw new RuntimeException('Ce modele existe deja.');
    }

    $row = [
        $m['nom'] => $nom,
    ];
    if ($m['marque_id'] !== null) {
        $row[$m['marque_id']] = $marqueId;
    }
    $createdAtCol = pick_column($pdo, $schema['modeles_table'], ['created_at', 'date_creation'], false);
    if ($createdAtCol !== null) {
        $row[$createdAtCol] = date('Y-m-d H:i:s');
    }

    repo_insert_dynamic($pdo, $schema['modeles_table'], $row);
    $id = (int) $pdo->lastInsertId();
    if ($id > 0) {
        return [
            'id' => $id,
            'nom' => $nom,
            'marque_id' => $m['marque_id'] !== null ? $marqueId : null,
        ];
    }

    $sql = 'SELECT m.' . qi($m['id']) . ' AS id, m.' . qi($m['nom']) . ' AS nom';
    if ($m['marque_id'] !== null) {
        $sql .= ', m.' . qi($m['marque_id']) . ' AS marque_id';
    } else {
        $sql .= ', NULL AS marque_id';
    }
    $sql .= ' FROM ' . qi($schema['modeles_table']) . ' m '
        . 'WHERE LOWER(m.' . qi($m['nom']) . ') = :nom ';
    if ($m['marque_id'] !== null) {
        $sql .= 'AND m.' . qi($m['marque_id']) . ' = :marque_id ';
    }
    $sql .= 'ORDER BY m.' . qi($m['id']) . ' DESC LIMIT 1';
    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':nom', strtolower($nom), PDO::PARAM_STR);
    if ($m['marque_id'] !== null) {
        $stmt->bindValue(':marque_id', $marqueId, PDO::PARAM_INT);
    }
    $stmt->execute();
    $created = $stmt->fetch();
    if (!is_array($created)) {
        throw new RuntimeException('Modele cree mais introuvable.');
    }

    return [
        'id' => (int) ($created['id'] ?? 0),
        'nom' => (string) ($created['nom'] ?? $nom),
        'marque_id' => isset($created['marque_id']) ? (int) $created['marque_id'] : null,
    ];
}
