<?php

declare(strict_types=1);

function order_table_pair(PDO $pdo): ?array
{
    $hasCommandesPair = table_exists($pdo, 'commandes') && table_exists($pdo, 'commandes_lignes');
    $hasOrdersPair = table_exists($pdo, 'orders') && table_exists($pdo, 'order_items');

    if (!$hasCommandesPair && !$hasOrdersPair) {
        return null;
    }

    $catalogTable = catalog_schema()['articles_table'] ?? '';
    if ($catalogTable === 'products' && $hasOrdersPair) {
        return [
            'order_table' => 'orders',
            'line_table' => 'order_items',
            'user_table' => 'users',
            'stock_table' => 'products',
        ];
    }
    if ($catalogTable === 'articles' && $hasCommandesPair) {
        return [
            'order_table' => 'commandes',
            'line_table' => 'commandes_lignes',
            'user_table' => 'utilisateurs',
            'stock_table' => 'articles',
        ];
    }

    if ($hasOrdersPair) {
        return [
            'order_table' => 'orders',
            'line_table' => 'order_items',
            'user_table' => 'users',
            'stock_table' => 'products',
        ];
    }

    return [
        'order_table' => 'commandes',
        'line_table' => 'commandes_lignes',
        'user_table' => 'utilisateurs',
        'stock_table' => 'articles',
    ];
}

function order_user_exists(PDO $pdo, string $userTable, int $userId): bool
{
    if ($userId <= 0 || !table_exists($pdo, $userTable)) {
        return false;
    }

    $stmt = $pdo->prepare('SELECT 1 FROM ' . qi($userTable) . ' WHERE id = :id LIMIT 1');
    $stmt->bindValue(':id', $userId, PDO::PARAM_INT);
    $stmt->execute();

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

function order_enum_values(array $columns, string $column): array
{
    $meta = $columns[$column] ?? null;
    if (!is_array($meta)) {
        return [];
    }

    $type = strtolower((string) ($meta['Type'] ?? ''));
    if (!str_starts_with($type, 'enum(')) {
        return [];
    }

    if (preg_match_all("/'([^']*)'/", $type, $matches) < 1) {
        return [];
    }

    return array_map(static fn (string $value): string => trim($value), $matches[1]);
}

function order_pick_status_value(array $columns, ?string $statusColumn): string
{
    if ($statusColumn === null || !isset($columns[$statusColumn])) {
        return 'en_attente';
    }

    $allowed = order_enum_values($columns, $statusColumn);
    if ($allowed === []) {
        return 'en_attente';
    }

    foreach (['en_attente', 'pending', 'paid'] as $candidate) {
        if (in_array($candidate, $allowed, true)) {
            return $candidate;
        }
    }

    return $allowed[0] ?? 'en_attente';
}

function order_money_for_column(float $amount, ?string $column): int|float
{
    if ($column === null) {
        return normalize_decimal($amount);
    }

    $columnLower = strtolower($column);
    if (str_ends_with($columnLower, '_cents') || str_contains($columnLower, 'cents')) {
        return (int) round($amount * 100);
    }

    return normalize_decimal($amount);
}

function fill_required_columns(array $columns, array $data): array
{
    foreach ($columns as $column => $meta) {
        if (array_key_exists($column, $data)) {
            continue;
        }

        $extra = strtolower((string) ($meta['Extra'] ?? ''));
        if (str_contains($extra, 'auto_increment')) {
            continue;
        }

        $nullable = strtoupper((string) ($meta['Null'] ?? 'YES')) === 'YES';
        $hasDefault = array_key_exists('Default', $meta) && $meta['Default'] !== null;

        if ($nullable || $hasDefault) {
            continue;
        }

        $type = strtolower((string) ($meta['Type'] ?? ''));
        if (
            str_contains($type, 'int')
            || str_contains($type, 'decimal')
            || str_contains($type, 'float')
            || str_contains($type, 'double')
            || str_contains($type, 'numeric')
        ) {
            $data[$column] = 0;
            continue;
        }

        if (str_contains($type, 'date') || str_contains($type, 'time') || str_contains($type, 'year')) {
            $data[$column] = date('Y-m-d H:i:s');
            continue;
        }

        $data[$column] = '';
    }

    return $data;
}

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

    $columns = array_keys($data);
    $columnSql = implode(', ', array_map('qi', $columns));
    $placeholderSql = implode(', ', array_map(static fn (string $col): string => ':' . $col, $columns));

    $sql = 'INSERT INTO ' . qi($table) . ' (' . $columnSql . ') VALUES (' . $placeholderSql . ')';

    $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 order_column_max_length(array $columns, string $column): ?int
{
    $meta = $columns[$column] ?? null;
    if (!is_array($meta)) {
        return null;
    }

    $type = strtolower((string) ($meta['Type'] ?? ''));
    if (preg_match('/^(?:varchar|char)\((\d+)\)/', $type, $matches) !== 1) {
        return null;
    }

    $length = (int) ($matches[1] ?? 0);
    return $length > 0 ? $length : null;
}

function order_random_alnum_token(int $length): string
{
    $length = max(1, $length);
    $alphabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    $alphabetLen = strlen($alphabet);
    $token = '';

    try {
        $raw = random_bytes($length);
        for ($i = 0; $i < $length; $i++) {
            $token .= $alphabet[ord($raw[$i]) % $alphabetLen];
        }
        return $token;
    } catch (Throwable) {
        for ($i = 0; $i < $length; $i++) {
            $token .= $alphabet[random_int(0, $alphabetLen - 1)];
        }
        return $token;
    }
}

function order_reference_exists(PDO $pdo, string $orderTable, string $referenceColumn, string $reference): bool
{
    $sql = 'SELECT 1 FROM ' . qi($orderTable)
        . ' WHERE ' . qi($referenceColumn) . ' = :reference LIMIT 1';
    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':reference', $reference, PDO::PARAM_STR);
    $stmt->execute();

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

function order_generate_reference(PDO $pdo, string $orderTable, ?string $referenceColumn, array $orderColumns): string
{
    $maxLength = 64;
    if ($referenceColumn !== null) {
        $colLength = order_column_max_length($orderColumns, $referenceColumn);
        if (is_int($colLength) && $colLength > 0) {
            $maxLength = $colLength;
        }
    }

    $prefix = 'CMD-';
    if ($maxLength <= strlen($prefix)) {
        return substr(order_random_alnum_token($maxLength), 0, $maxLength);
    }

    $payloadLength = $maxLength - strlen($prefix);
    $attempts = 10;

    for ($attempt = 0; $attempt < $attempts; $attempt++) {
        $microToken = strtoupper(base_convert((string) ((int) floor(microtime(true) * 1000000)), 10, 36));
        $randomToken = order_random_alnum_token(12);
        $candidate = $prefix . substr($microToken . $randomToken, 0, $payloadLength);

        if ($referenceColumn === null || !order_reference_exists($pdo, $orderTable, $referenceColumn, $candidate)) {
            return $candidate;
        }

        usleep(10000);
    }

    return $prefix . substr(order_random_alnum_token($payloadLength + 8), 0, $payloadLength);
}

function normalize_order_lines(array $lines): array
{
    $normalized = [];

    foreach ($lines as $line) {
        if (!is_array($line)) {
            continue;
        }

        $articleId = (int) ($line['article_id'] ?? $line['id'] ?? 0);
        $qty = (int) ($line['qty'] ?? $line['quantite'] ?? $line['quantity'] ?? 0);

        if ($articleId <= 0 || $qty <= 0) {
            continue;
        }

        if (!isset($normalized[$articleId])) {
            $normalized[$articleId] = 0;
        }

        $normalized[$articleId] += $qty;
    }

    return $normalized;
}

function order_create(array $payload): array
{
    $utilisateur = $payload['utilisateur'] ?? [];
    if (!is_array($utilisateur)) {
        $utilisateur = [];
    }

    $userId = (int) ($utilisateur['id'] ?? 0);
    $adresse = trim((string) ($payload['adresse'] ?? ''));
    $linesInput = $payload['lignes'] ?? [];

    if ($userId <= 0) {
        throw new InvalidArgumentException('Utilisateur requis pour creer une commande.');
    }

    if (!is_array($linesInput)) {
        throw new InvalidArgumentException('Le champ lignes est invalide.');
    }

    $lines = normalize_order_lines($linesInput);
    if ($lines === []) {
        throw new InvalidArgumentException('Aucune ligne de commande valide.');
    }

    cart_prune_expired_reservations();

    $articleIds = array_keys($lines);
    $articles = repo_find_articles_by_ids($articleIds);
    $reservedByUser = cart_reserved_qty_for_user($userId, $articleIds);
    $reservedByOthers = repo_reserved_qty_by_article($articleIds, $userId);

    foreach ($articleIds as $articleId) {
        $reservedQty = (int) ($reservedByUser[$articleId] ?? 0);
        if ($reservedQty < $lines[$articleId]) {
            throw new RuntimeException('Reservation panier expiree ou insuffisante pour l\'article #' . $articleId . '.');
        }
    }

    foreach ($articleIds as $articleId) {
        if (!isset($articles[$articleId])) {
            throw new RuntimeException('Article introuvable: ' . $articleId);
        }

        $rawStock = (int) ($articles[$articleId]['stock'] ?? 0);
        $reservedElsewhere = (int) ($reservedByOthers[$articleId] ?? 0);
        $available = max(0, $rawStock - $reservedElsewhere);
        if ($available < $lines[$articleId]) {
            throw new RuntimeException('Stock insuffisant pour l\'article #' . $articleId);
        }
    }

    $subtotalHt = 0.0;
    $itemCount = 0;
    $vatRate = 0.20;

    foreach ($lines as $articleId => $qty) {
        $price = (float) ($articles[$articleId]['prix'] ?? 0);
        $subtotalHt += $price * $qty;
        $itemCount += $qty;
    }

    $subtotalHt = normalize_decimal($subtotalHt);
    $tvaAmount = normalize_decimal($subtotalHt * $vatRate);
    $totalTtc = normalize_decimal($subtotalHt + $tvaAmount);
    $pdo = db();

    $tables = order_table_pair($pdo);
    if ($tables === null) {
        throw new RuntimeException('Tables commandes/commandes_lignes introuvables.');
    }
    $orderTable = (string) $tables['order_table'];
    $orderLinesTable = (string) $tables['line_table'];
    $userTable = (string) $tables['user_table'];
    $stockTable = (string) $tables['stock_table'];

    if (!order_user_exists($pdo, $userTable, $userId)) {
        throw new RuntimeException('Utilisateur introuvable dans ' . $userTable . '.');
    }

    $now = date('Y-m-d H:i:s');

    $pdo->beginTransaction();

    try {
        $orderCols = table_columns($pdo, $orderTable);
        $orderData = [];

        $orderUserIdCol = pick_column($pdo, $orderTable, ['utilisateur_id', 'user_id'], false);
        if ($orderUserIdCol !== null) {
            $orderData[$orderUserIdCol] = $userId > 0 ? $userId : null;
        }

        $orderStatusCol = pick_column($pdo, $orderTable, ['statut', 'status'], false);
        if ($orderStatusCol !== null) {
            $orderData[$orderStatusCol] = order_pick_status_value($orderCols, $orderStatusCol);
        }

        $orderSubtotalCol = pick_column($pdo, $orderTable, ['subtotal', 'subtotal_cents'], false);
        if ($orderSubtotalCol !== null) {
            $orderData[$orderSubtotalCol] = order_money_for_column($subtotalHt, $orderSubtotalCol);
        }

        $orderTotalCol = pick_column($pdo, $orderTable, ['total', 'total_cents', 'montant_total', 'total_ttc'], false);
        if ($orderTotalCol !== null) {
            $orderData[$orderTotalCol] = order_money_for_column($totalTtc, $orderTotalCol);
        }

        $orderAddressCol = pick_column($pdo, $orderTable, ['adresse', 'adresse_livraison', 'shipping_address'], false);
        if ($orderAddressCol !== null) {
            $orderData[$orderAddressCol] = $adresse;
        }

        $orderRefCol = pick_column($pdo, $orderTable, ['reference', 'numero', 'order_number'], false);
        $requestedReference = strtoupper(trim((string) ($payload['reference'] ?? '')));
        if ($requestedReference !== '') {
            $requestedReference = (string) preg_replace('/[^A-Z0-9._-]/', '', $requestedReference);
        }
        if ($requestedReference !== '') {
            $maxReferenceLength = 64;
            if ($orderRefCol !== null) {
                $refColLength = order_column_max_length($orderCols, $orderRefCol);
                if (is_int($refColLength) && $refColLength > 0) {
                    $maxReferenceLength = $refColLength;
                }
            }
            $orderReference = substr($requestedReference, 0, $maxReferenceLength);
            if ($orderReference === '') {
                $orderReference = order_generate_reference($pdo, $orderTable, $orderRefCol, $orderCols);
            }
        } else {
            $orderReference = order_generate_reference($pdo, $orderTable, $orderRefCol, $orderCols);
        }
        if ($orderRefCol !== null) {
            $orderData[$orderRefCol] = $orderReference;
        }

        $orderCreatedCol = pick_column($pdo, $orderTable, ['created_at', 'date_creation'], false);
        if ($orderCreatedCol !== null) {
            $orderData[$orderCreatedCol] = $now;
        }

        $orderUpdatedCol = pick_column($pdo, $orderTable, ['updated_at'], false);
        if ($orderUpdatedCol !== null) {
            $orderData[$orderUpdatedCol] = $now;
        }

        $customerNameCol = pick_column($pdo, $orderTable, ['nom_client', 'customer_name'], false);
        if ($customerNameCol !== null) {
            $fullName = trim((string) (($utilisateur['nom'] ?? '') . ' ' . ($utilisateur['prenom'] ?? '')));
            if ($fullName === '') {
                $mail = trim((string) ($utilisateur['mail'] ?? ''));
                $mailLocal = trim((string) strtok($mail, '@'));
                $fullName = $mailLocal !== '' ? $mailLocal : ('Client #' . $userId);
            }
            $orderData[$customerNameCol] = $fullName;
        }

        $customerMailCol = pick_column($pdo, $orderTable, ['email_client', 'customer_email'], false);
        if ($customerMailCol !== null) {
            $orderData[$customerMailCol] = trim((string) ($utilisateur['mail'] ?? ''));
        }

        $orderData = fill_required_columns($orderCols, $orderData);
        $orderId = dynamic_insert($pdo, $orderTable, $orderData);

        $lineCols = table_columns($pdo, $orderLinesTable);
        $lineOrderIdCol = pick_column($pdo, $orderLinesTable, ['commande_id', 'order_id'], false);
        $lineArticleIdCol = pick_column($pdo, $orderLinesTable, ['article_id', 'product_id'], false);
        $lineQtyCol = pick_column($pdo, $orderLinesTable, ['quantite', 'qty', 'quantity'], false);
        $lineUnitPriceCol = pick_column($pdo, $orderLinesTable, ['prix_unitaire', 'unit_price', 'unit_price_cents', 'price'], false);
        $lineTotalCol = pick_column($pdo, $orderLinesTable, ['total_ligne', 'line_total', 'line_total_cents', 'total'], false);
        $lineRefCol = pick_column($pdo, $orderLinesTable, ['reference', 'sku'], false);
        $lineNameCol = pick_column($pdo, $orderLinesTable, ['nom', 'title', 'name', 'product_name'], false);
        $lineCreatedCol = pick_column($pdo, $orderLinesTable, ['created_at', 'date_creation'], false);

        foreach ($lines as $articleId => $qty) {
            $article = $articles[$articleId];
            $unitPrice = normalize_decimal((float) ($article['prix'] ?? 0));
            $lineTotal = normalize_decimal($unitPrice * $qty);

            $lineData = [];

            if ($lineOrderIdCol !== null) {
                $lineData[$lineOrderIdCol] = ctype_digit($orderId) ? (int) $orderId : $orderId;
            }
            if ($lineArticleIdCol !== null) {
                $lineData[$lineArticleIdCol] = $articleId;
            }
            if ($lineQtyCol !== null) {
                $lineData[$lineQtyCol] = $qty;
            }
            if ($lineUnitPriceCol !== null) {
                $lineData[$lineUnitPriceCol] = order_money_for_column($unitPrice, $lineUnitPriceCol);
            }
            if ($lineTotalCol !== null) {
                $lineData[$lineTotalCol] = order_money_for_column($lineTotal, $lineTotalCol);
            }
            if ($lineRefCol !== null) {
                $lineData[$lineRefCol] = (string) ($article['reference'] ?? '');
            }
            if ($lineNameCol !== null) {
                $lineData[$lineNameCol] = (string) ($article['nom'] ?? '');
            }
            if ($lineCreatedCol !== null) {
                $lineData[$lineCreatedCol] = $now;
            }

            $lineData = fill_required_columns($lineCols, $lineData);
            dynamic_insert($pdo, $orderLinesTable, $lineData);

            if (!repo_update_article_stock($articleId, $qty)) {
                throw new RuntimeException('Conflit de stock pour l\'article #' . $articleId);
            }

            if ($stockTable === 'articles') {
                order_write_stock_movement($pdo, $articleId, $qty, $orderId, $orderReference, $now);
            }
        }

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

        throw $exception;
    }

    cart_remove_article_ids(array_keys($lines));

    return [
        'order_id' => ctype_digit($orderId) ? (int) $orderId : $orderId,
        'reference' => $orderReference,
        'status' => 'en_attente',
        'subtotal_ht' => $subtotalHt,
        'tva_rate' => $vatRate,
        'tva_amount' => $tvaAmount,
        'total_ttc' => $totalTtc,
        'total' => $totalTtc,
        'item_count' => $itemCount,
        'created_at' => $now,
        'lines' => array_values(array_map(
            static fn (int $articleId, int $qty): array => [
                'article_id' => $articleId,
                'reference' => (string) ($articles[$articleId]['reference'] ?? ''),
                'nom' => (string) ($articles[$articleId]['nom'] ?? ''),
                'qty' => $qty,
                'prix_unitaire' => normalize_decimal((float) ($articles[$articleId]['prix'] ?? 0)),
            ],
            array_keys($lines),
            array_values($lines)
        )),
    ];
}

function order_pick_stock_type(array $columns, ?string $typeColumn): string
{
    if ($typeColumn === null || !isset($columns[$typeColumn])) {
        return 'sortie';
    }

    $allowed = order_enum_values($columns, $typeColumn);
    if ($allowed === []) {
        return 'sortie';
    }

    foreach (['sortie', 'sortie_commande', 'ajustement'] as $candidate) {
        if (in_array($candidate, $allowed, true)) {
            return $candidate;
        }
    }

    return $allowed[0] ?? 'sortie';
}

function order_write_stock_movement(PDO $pdo, int $articleId, int $qty, string $orderId, string $orderReference, string $now): void
{
    $table = table_exists($pdo, 'stock_mouvements') ? 'stock_mouvements' : null;
    if ($table === null) {
        return;
    }

    $cols = table_columns($pdo, $table);

    $articleIdCol = pick_column($pdo, $table, ['article_id', 'product_id'], false);
    if ($articleIdCol === null) {
        return;
    }

    $data = [
        $articleIdCol => $articleId,
    ];

    $typeCol = pick_column($pdo, $table, ['type', 'movement_type'], false);
    if ($typeCol !== null) {
        $data[$typeCol] = order_pick_stock_type($cols, $typeCol);
    }

    $qtyCol = pick_column($pdo, $table, ['quantite', 'qty', 'quantity'], false);
    if ($qtyCol !== null) {
        $data[$qtyCol] = -1 * $qty;
    }

    $sourceCol = pick_column($pdo, $table, ['source'], false);
    if ($sourceCol !== null) {
        $data[$sourceCol] = 'commande';
    }

    $refCol = pick_column($pdo, $table, ['reference', 'order_reference'], false);
    if ($refCol !== null) {
        $data[$refCol] = $orderReference;
    }

    $orderIdCol = pick_column($pdo, $table, ['commande_id', 'order_id'], false);
    if ($orderIdCol !== null) {
        $data[$orderIdCol] = ctype_digit($orderId) ? (int) $orderId : $orderId;
    }

    $createdCol = pick_column($pdo, $table, ['created_at', 'date_creation'], false);
    if ($createdCol !== null) {
        $data[$createdCol] = $now;
    }

    $data = fill_required_columns($cols, $data);
    dynamic_insert($pdo, $table, $data);
}

function order_read_schema(PDO $pdo): array
{
    $tables = order_table_pair($pdo);
    if ($tables === null) {
        throw new RuntimeException('Tables commandes/commandes_lignes introuvables.');
    }

    $orderTable = (string) $tables['order_table'];
    $lineTable = (string) $tables['line_table'];
    $userTable = isset($tables['user_table']) ? (string) $tables['user_table'] : '';
    if ($userTable === '' || !table_exists($pdo, $userTable)) {
        $userTable = '';
    }
    $userColumns = null;
    if ($userTable !== '') {
        $userColumns = [
            'id' => pick_column($pdo, $userTable, ['id'], false),
            'nom' => pick_column($pdo, $userTable, ['nom', 'name', 'full_name', 'username'], false),
            'prenom' => pick_column($pdo, $userTable, ['prenom', 'first_name'], false),
            'email' => pick_column($pdo, $userTable, ['email', 'mail'], false),
        ];
    }

    return [
        'order_table' => $orderTable,
        'line_table' => $lineTable,
        'user_table' => $userTable !== '' ? $userTable : null,
        'user_columns' => $userColumns,
        'order_columns' => [
            'id' => pick_column($pdo, $orderTable, ['id']),
            'user_id' => pick_column($pdo, $orderTable, ['utilisateur_id', 'user_id'], false),
            'status' => pick_column($pdo, $orderTable, ['statut', 'status'], false),
            'reference' => pick_column($pdo, $orderTable, ['reference', 'numero', 'order_number'], false),
            'subtotal' => pick_column($pdo, $orderTable, ['subtotal', 'subtotal_ht', 'subtotal_cents'], false),
            'total' => pick_column($pdo, $orderTable, ['total', 'total_ttc', 'montant_total', 'total_cents'], false),
            'tva_rate' => pick_column($pdo, $orderTable, ['tva_rate', 'vat_rate'], false),
            'tva_amount' => pick_column($pdo, $orderTable, ['tva_amount', 'montant_tva', 'vat_amount'], false),
            'item_count' => pick_column($pdo, $orderTable, ['item_count', 'total_items', 'qty_total'], false),
            'created_at' => pick_column($pdo, $orderTable, ['created_at', 'date_creation'], false),
            'updated_at' => pick_column($pdo, $orderTable, ['updated_at'], false),
            'customer_name' => pick_column($pdo, $orderTable, ['nom_client', 'customer_name'], false),
            'customer_email' => pick_column($pdo, $orderTable, ['email_client', 'customer_email'], false),
        ],
        'line_columns' => [
            'id' => pick_column($pdo, $lineTable, ['id'], false),
            'order_id' => pick_column($pdo, $lineTable, ['commande_id', 'order_id'], false),
            'article_id' => pick_column($pdo, $lineTable, ['article_id', 'product_id'], false),
            'qty' => pick_column($pdo, $lineTable, ['quantite', 'qty', 'quantity'], false),
            'unit_price' => pick_column($pdo, $lineTable, ['prix_unitaire', 'unit_price', 'unit_price_cents', 'price'], false),
            'line_total' => pick_column($pdo, $lineTable, ['total_ligne', 'line_total', 'line_total_cents', 'total'], false),
            'reference' => pick_column($pdo, $lineTable, ['reference', 'sku'], false),
            'name' => pick_column($pdo, $lineTable, ['nom', 'title', 'name', 'product_name'], false),
        ],
    ];
}

function order_money_from_db(mixed $value, ?string $column): float
{
    if (!is_numeric((string) $value)) {
        return 0.0;
    }

    $amount = (float) $value;
    if ($column !== null) {
        $columnLower = strtolower($column);
        if (str_ends_with($columnLower, '_cents') || str_contains($columnLower, 'cents')) {
            $amount /= 100;
        }
    }

    return normalize_decimal($amount);
}

function order_status_to_app(string $rawStatus): string
{
    $status = strtolower(trim($rawStatus));
    if ($status === '') {
        return 'en_attente';
    }

    if (
        str_contains($status, 'valid')
        || str_contains($status, 'paid')
        || str_contains($status, 'complete')
        || str_contains($status, 'livre')
    ) {
        return 'validee';
    }

    if (
        str_contains($status, 'annul')
        || str_contains($status, 'cancel')
        || str_contains($status, 'refus')
        || str_contains($status, 'reject')
    ) {
        return 'annulee';
    }

    if (
        str_contains($status, 'fail')
        || str_contains($status, 'error')
        || str_contains($status, 'echou')
        || str_contains($status, 'ko')
    ) {
        return 'echouee';
    }

    return 'en_attente';
}

function order_pick_status_target_value(array $columns, ?string $statusColumn, string $target): string
{
    if ($statusColumn === null || !isset($columns[$statusColumn])) {
        return $target;
    }

    $allowed = order_enum_values($columns, $statusColumn);
    if ($allowed === []) {
        return $target;
    }

    $target = strtolower(trim($target));
    $candidates = match ($target) {
        'validee' => ['validee', 'validated', 'paid', 'complete', 'completed'],
        'annulee' => ['annulee', 'cancelled', 'canceled', 'rejected', 'refused'],
        'echouee' => ['echouee', 'failed', 'error'],
        default => ['en_attente', 'pending', 'processing'],
    };

    foreach ($candidates as $candidate) {
        if (in_array($candidate, $allowed, true)) {
            return $candidate;
        }
    }

    foreach ($allowed as $value) {
        $normalized = strtolower(trim($value));
        if ($target === 'validee' && (str_contains($normalized, 'valid') || str_contains($normalized, 'paid'))) {
            return $value;
        }
        if ($target === 'annulee' && (str_contains($normalized, 'cancel') || str_contains($normalized, 'annul') || str_contains($normalized, 'refus'))) {
            return $value;
        }
        if ($target === 'echouee' && (str_contains($normalized, 'fail') || str_contains($normalized, 'error') || str_contains($normalized, 'echou'))) {
            return $value;
        }
        if ($target === 'en_attente' && (str_contains($normalized, 'attente') || str_contains($normalized, 'pending') || str_contains($normalized, 'process'))) {
            return $value;
        }
    }

    return $allowed[0] ?? $target;
}

function order_bind_value(PDOStatement $stmt, string $param, mixed $value): void
{
    if (is_int($value)) {
        $stmt->bindValue($param, $value, PDO::PARAM_INT);
        return;
    }

    if (is_string($value) && ctype_digit($value) && strlen($value) < 19) {
        $stmt->bindValue($param, (int) $value, PDO::PARAM_INT);
        return;
    }

    $stmt->bindValue($param, (string) $value, PDO::PARAM_STR);
}

function order_fetch_lines_map(PDO $pdo, array $schema, array $orderIds): array
{
    $lineCols = $schema['line_columns'];
    if ($lineCols['order_id'] === null || $orderIds === []) {
        return [];
    }

    $placeholders = [];
    $params = [];
    foreach (array_values($orderIds) as $index => $orderId) {
        $key = ':order_' . $index;
        $placeholders[] = $key;
        $params[$key] = $orderId;
    }

    $selectParts = [
        'l.' . qi($lineCols['order_id']) . ' AS order_id',
        ($lineCols['article_id'] !== null ? 'l.' . qi($lineCols['article_id']) : 'NULL') . ' AS article_id_raw',
        ($lineCols['reference'] !== null ? 'l.' . qi($lineCols['reference']) : 'NULL') . ' AS reference_raw',
        ($lineCols['name'] !== null ? 'l.' . qi($lineCols['name']) : 'NULL') . ' AS name_raw',
        ($lineCols['qty'] !== null ? 'l.' . qi($lineCols['qty']) : '0') . ' AS qty_raw',
        ($lineCols['unit_price'] !== null ? 'l.' . qi($lineCols['unit_price']) : '0') . ' AS unit_price_raw',
        ($lineCols['line_total'] !== null ? 'l.' . qi($lineCols['line_total']) : '0') . ' AS line_total_raw',
    ];

    $orderBy = ' ORDER BY l.' . qi($lineCols['order_id']) . ' ASC';
    if ($lineCols['id'] !== null) {
        $orderBy .= ', l.' . qi($lineCols['id']) . ' ASC';
    }

    $sql = 'SELECT ' . implode(', ', $selectParts)
        . ' FROM ' . qi($schema['line_table']) . ' l'
        . ' WHERE l.' . qi($lineCols['order_id']) . ' IN (' . implode(', ', $placeholders) . ')'
        . $orderBy;

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

    $linesByOrder = [];
    foreach ($stmt->fetchAll() as $row) {
        if (!is_array($row)) {
            continue;
        }

        $orderKey = (string) ($row['order_id'] ?? '');
        if ($orderKey === '') {
            continue;
        }

        $qty = max(1, (int) ($row['qty_raw'] ?? 1));
        $unitPrice = order_money_from_db($row['unit_price_raw'] ?? 0, $lineCols['unit_price']);
        $lineTotal = order_money_from_db($row['line_total_raw'] ?? 0, $lineCols['line_total']);
        if ($lineTotal <= 0) {
            $lineTotal = normalize_decimal($unitPrice * $qty);
        }

        $articleIdRaw = $row['article_id_raw'] ?? null;
        $articleId = null;
        if (is_numeric((string) $articleIdRaw)) {
            $articleId = (int) $articleIdRaw;
        } elseif (is_string($articleIdRaw) && trim($articleIdRaw) !== '') {
            $articleId = trim($articleIdRaw);
        }

        if (!isset($linesByOrder[$orderKey])) {
            $linesByOrder[$orderKey] = [];
        }

        $linesByOrder[$orderKey][] = [
            'article_id' => $articleId,
            'reference' => trim((string) ($row['reference_raw'] ?? '')),
            'nom' => trim((string) ($row['name_raw'] ?? '')),
            'qty' => $qty,
            'prix_unitaire' => $unitPrice,
            'total_ligne' => $lineTotal,
        ];
    }

    return $linesByOrder;
}

function order_map_row_payload(array $row, array $schema, array $linesByOrder): array
{
    $orderCols = $schema['order_columns'];
    $orderRawId = $row['order_id'] ?? '';
    $orderKey = (string) $orderRawId;
    $orderId = is_numeric($orderKey) ? (int) $orderKey : $orderKey;

    $lines = $linesByOrder[$orderKey] ?? [];

    $subtotal = order_money_from_db($row['subtotal_raw'] ?? 0, $orderCols['subtotal']);
    if ($subtotal <= 0 && $lines !== []) {
        $subtotal = normalize_decimal(array_reduce(
            $lines,
            static fn (float $acc, array $line): float => $acc + (((float) ($line['prix_unitaire'] ?? 0)) * ((int) ($line['qty'] ?? 0))),
            0.0
        ));
    }

    $total = order_money_from_db($row['total_raw'] ?? 0, $orderCols['total']);
    $tvaAmount = order_money_from_db($row['tva_amount_raw'] ?? 0, $orderCols['tva_amount']);
    $tvaRateRaw = (float) ($row['tva_rate_raw'] ?? 0);
    $tvaRate = $tvaRateRaw > 0 ? normalize_decimal($tvaRateRaw) : 0.20;

    if ($tvaAmount <= 0 && $total > $subtotal) {
        $tvaAmount = normalize_decimal($total - $subtotal);
    }
    if ($total <= 0) {
        $total = normalize_decimal($subtotal + $tvaAmount);
    }

    $itemCount = (int) ($row['item_count_raw'] ?? 0);
    if ($itemCount <= 0 && $lines !== []) {
        $itemCount = array_reduce(
            $lines,
            static fn (int $acc, array $line): int => $acc + max(0, (int) ($line['qty'] ?? 0)),
            0
        );
    }

    $reference = trim((string) ($row['reference_raw'] ?? ''));
    if ($reference === '') {
        $reference = 'CMD-' . strtoupper($orderKey);
    }

    $createdAt = trim((string) ($row['created_at_raw'] ?? ''));
    if ($createdAt === '') {
        $createdAt = date('Y-m-d H:i:s');
    }

    $statusRaw = trim((string) ($row['status_raw'] ?? ''));
    $customerName = trim((string) ($row['customer_name_raw'] ?? ''));
    $customerEmail = strtolower(trim((string) ($row['customer_email_raw'] ?? '')));

    $userIdRaw = $row['user_id_raw'] ?? null;
    $userId = null;
    if (is_numeric((string) $userIdRaw)) {
        $userId = (int) $userIdRaw;
    } elseif (is_string($userIdRaw) && trim($userIdRaw) !== '') {
        $userId = trim($userIdRaw);
    }

    $userNom = trim((string) ($row['user_nom_raw'] ?? ''));
    $userPrenom = trim((string) ($row['user_prenom_raw'] ?? ''));
    $userEmail = strtolower(trim((string) ($row['user_email_raw'] ?? '')));
    $userFullName = trim($userNom . ' ' . $userPrenom);

    if ($customerEmail === '' && $userEmail !== '') {
        $customerEmail = $userEmail;
    }
    if ($customerName === '' && $userFullName !== '') {
        $customerName = $userFullName;
    }
    if ($customerName === '' && $customerEmail !== '') {
        $mailLocal = trim((string) strtok($customerEmail, '@'));
        if ($mailLocal !== '') {
            $customerName = trim((string) preg_replace('/[._-]+/', ' ', $mailLocal));
        }
    }
    if ($customerName === '' && $userId !== null) {
        $customerName = 'Client #' . (string) $userId;
    }
    if ($customerName === '') {
        $customerName = 'Client';
    }

    return [
        'order_id' => $orderId,
        'id' => $orderId,
        'user_id' => $userId,
        'reference' => $reference,
        'status' => order_status_to_app($statusRaw),
        'status_raw' => $statusRaw,
        'subtotal_ht' => $subtotal,
        'tva_rate' => $tvaRate,
        'tva_amount' => $tvaAmount,
        'total_ttc' => $total,
        'total' => $total,
        'item_count' => $itemCount,
        'created_at' => $createdAt,
        'customer_name' => $customerName,
        'customer_email' => $customerEmail,
        'lines' => $lines,
    ];
}

function order_fetch_many(array $whereParts = [], array $params = [], int $limit = 300): array
{
    $pdo = db();
    $schema = order_read_schema($pdo);
    $orderCols = $schema['order_columns'];
    $userCols = is_array($schema['user_columns'] ?? null) ? $schema['user_columns'] : null;
    $userTable = is_string($schema['user_table'] ?? null) ? (string) $schema['user_table'] : '';
    $canJoinUser = $orderCols['user_id'] !== null
        && $userTable !== ''
        && $userCols !== null
        && isset($userCols['id'])
        && $userCols['id'] !== null;

    $selectParts = [
        'o.' . qi($orderCols['id']) . ' AS order_id',
        ($orderCols['user_id'] !== null ? 'o.' . qi($orderCols['user_id']) : 'NULL') . ' AS user_id_raw',
        ($orderCols['reference'] !== null ? 'o.' . qi($orderCols['reference']) : 'NULL') . ' AS reference_raw',
        ($orderCols['status'] !== null ? 'o.' . qi($orderCols['status']) : "'en_attente'") . ' AS status_raw',
        ($orderCols['subtotal'] !== null ? 'o.' . qi($orderCols['subtotal']) : '0') . ' AS subtotal_raw',
        ($orderCols['total'] !== null ? 'o.' . qi($orderCols['total']) : '0') . ' AS total_raw',
        ($orderCols['tva_rate'] !== null ? 'o.' . qi($orderCols['tva_rate']) : '0') . ' AS tva_rate_raw',
        ($orderCols['tva_amount'] !== null ? 'o.' . qi($orderCols['tva_amount']) : '0') . ' AS tva_amount_raw',
        ($orderCols['item_count'] !== null ? 'o.' . qi($orderCols['item_count']) : '0') . ' AS item_count_raw',
        ($orderCols['created_at'] !== null ? 'o.' . qi($orderCols['created_at']) : 'NULL') . ' AS created_at_raw',
        ($orderCols['customer_name'] !== null ? 'o.' . qi($orderCols['customer_name']) : 'NULL') . ' AS customer_name_raw',
        ($orderCols['customer_email'] !== null ? 'o.' . qi($orderCols['customer_email']) : 'NULL') . ' AS customer_email_raw',
        (
            $canJoinUser && $userCols['nom'] !== null
                ? 'u.' . qi($userCols['nom'])
                : 'NULL'
        ) . ' AS user_nom_raw',
        (
            $canJoinUser && $userCols['prenom'] !== null
                ? 'u.' . qi($userCols['prenom'])
                : 'NULL'
        ) . ' AS user_prenom_raw',
        (
            $canJoinUser && $userCols['email'] !== null
                ? 'u.' . qi($userCols['email'])
                : 'NULL'
        ) . ' AS user_email_raw',
    ];

    $whereSql = $whereParts === [] ? '' : (' WHERE ' . implode(' AND ', $whereParts));
    $limit = max(1, min(500, $limit));
    $orderBy = $orderCols['created_at'] !== null
        ? ' ORDER BY o.' . qi($orderCols['created_at']) . ' DESC, o.' . qi($orderCols['id']) . ' DESC'
        : ' ORDER BY o.' . qi($orderCols['id']) . ' DESC';
    $joinSql = $canJoinUser
        ? (' LEFT JOIN ' . qi($userTable) . ' u ON u.' . qi($userCols['id']) . ' = o.' . qi($orderCols['user_id']))
        : '';

    $sql = 'SELECT ' . implode(', ', $selectParts)
        . ' FROM ' . qi($schema['order_table']) . ' o'
        . $joinSql
        . $whereSql
        . $orderBy
        . ' LIMIT ' . $limit;

    $stmt = $pdo->prepare($sql);
    foreach ($params as $name => $value) {
        order_bind_value($stmt, (string) $name, $value);
    }
    $stmt->execute();

    $rows = $stmt->fetchAll();
    if (!is_array($rows) || $rows === []) {
        return [];
    }

    $orderIds = array_values(array_filter(array_map(
        static fn (array $row): string => (string) ($row['order_id'] ?? ''),
        $rows
    )));
    $linesByOrder = order_fetch_lines_map($pdo, $schema, $orderIds);

    $orders = [];
    foreach ($rows as $row) {
        if (!is_array($row)) {
            continue;
        }
        $orders[] = order_map_row_payload($row, $schema, $linesByOrder);
    }

    return $orders;
}

function order_list_for_user(int|string $userId, string $userEmail = ''): array
{
    $pdo = db();
    $schema = order_read_schema($pdo);
    $orderCols = $schema['order_columns'];

    $whereParts = [];
    $params = [];

    if ($orderCols['user_id'] !== null && !((is_int($userId) && $userId <= 0) || (is_string($userId) && trim($userId) === ''))) {
        $whereParts[] = 'o.' . qi($orderCols['user_id']) . ' = :user_id';
        $params[':user_id'] = $userId;
    } elseif ($orderCols['customer_email'] !== null && trim($userEmail) !== '') {
        $whereParts[] = 'LOWER(o.' . qi($orderCols['customer_email']) . ') = :customer_email';
        $params[':customer_email'] = strtolower(trim($userEmail));
    } else {
        throw new RuntimeException('Impossible de filtrer les commandes de ce client sur ce schema.');
    }

    return order_fetch_many($whereParts, $params, 250);
}

function order_list_all(): array
{
    return order_fetch_many([], [], 500);
}

function order_get_by_id(int|string $orderId): ?array
{
    $pdo = db();
    $schema = order_read_schema($pdo);
    $orderCols = $schema['order_columns'];

    $rows = order_fetch_many(
        ['o.' . qi($orderCols['id']) . ' = :order_id'],
        [':order_id' => $orderId],
        1
    );

    return $rows[0] ?? null;
}

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

    $pdo = db();
    $schema = order_read_schema($pdo);
    $orderCols = $schema['order_columns'];
    if ($orderCols['reference'] === null) {
        return null;
    }

    $rows = order_fetch_many(
        ['o.' . qi($orderCols['reference']) . ' = :reference'],
        [':reference' => $reference],
        1
    );

    return $rows[0] ?? null;
}

function order_update_status(int|string $orderId, string $status): array
{
    $target = strtolower(trim($status));
    if (!in_array($target, ['en_attente', 'validee', 'annulee', 'echouee'], true)) {
        throw new InvalidArgumentException('Statut de commande invalide.');
    }

    $existing = order_get_by_id($orderId);
    if (!is_array($existing)) {
        throw new RuntimeException('Commande introuvable.');
    }

    $previousStatus = strtolower(trim((string) ($existing['status'] ?? 'en_attente')));
    if ($previousStatus === $target) {
        return $existing;
    }

    $pdo = db();
    $schema = order_read_schema($pdo);
    $orderCols = $schema['order_columns'];
    if ($orderCols['status'] === null) {
        throw new RuntimeException('Aucune colonne statut disponible pour les commandes.');
    }

    $orderTableColumns = table_columns($pdo, $schema['order_table']);
    $dbStatus = order_pick_status_target_value($orderTableColumns, $orderCols['status'], $target);

    $setParts = [qi($orderCols['status']) . ' = :status'];
    $params = [':status' => $dbStatus];
    if ($orderCols['updated_at'] !== null) {
        $setParts[] = qi($orderCols['updated_at']) . ' = :updated_at';
        $params[':updated_at'] = date('Y-m-d H:i:s');
    }

    $shouldRestoreStock = $target === 'annulee' && $previousStatus !== 'annulee';
    $shouldConsumeStock = $target !== 'annulee' && $previousStatus === 'annulee';
    $lines = is_array($existing['lines'] ?? null) ? $existing['lines'] : [];

    $pdo->beginTransaction();
    try {
        $sql = 'UPDATE ' . qi($schema['order_table'])
            . ' SET ' . implode(', ', $setParts)
            . ' WHERE ' . qi($orderCols['id']) . ' = :order_id LIMIT 1';

        $stmt = $pdo->prepare($sql);
        foreach ($params as $name => $value) {
            order_bind_value($stmt, $name, $value);
        }
        order_bind_value($stmt, ':order_id', $orderId);
        $stmt->execute();

        if ($shouldRestoreStock || $shouldConsumeStock) {
            foreach ($lines as $line) {
                if (!is_array($line)) {
                    continue;
                }

                $articleIdRaw = $line['article_id'] ?? null;
                if (!is_numeric((string) $articleIdRaw)) {
                    continue;
                }

                $articleId = (int) $articleIdRaw;
                if ($articleId <= 0) {
                    continue;
                }

                $qty = max(1, (int) ($line['qty'] ?? 1));
                $updated = $shouldRestoreStock
                    ? repo_restore_article_stock($articleId, $qty)
                    : repo_update_article_stock($articleId, $qty);
                if (!$updated) {
                    throw new RuntimeException('Conflit de stock pendant la mise a jour du statut commande.');
                }
            }
        }

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

    $updated = order_get_by_id($orderId);
    if (!is_array($updated)) {
        throw new RuntimeException('Commande introuvable apres mise a jour du statut.');
    }

    return $updated;
}

function hash_user_password(string $plainPassword): string
{
    return password_hash($plainPassword, PASSWORD_DEFAULT);
}

function verify_user_password(string $plainPassword, string $hash): bool
{
    return password_verify($plainPassword, $hash);
}
