<?php
declare(strict_types=1);

/* doc-project | caisse-aqp/public/api/_lib/clientUpsert.php | Fournit les helpers de normalisation, validation et upsert fusionné des clients pour CREATE/UPDATE, avec canonicalisation du couple ville / code postal autorisé selon le PDV quand une option valide est réellement sélectionnée, et conservation explicite des valeurs déjà présentes en base quand le sélecteur composite est vide ou hors correspondance pour le PDV courant. | Expose: normalizePhoneForDb, cleanStr, bindValueNullable, executeClientUpdateWithNullFallback, upsertClientFromPayload | Dépend de: clientPhoneResolver.php, deliveryCityChoices.php, PDO, PDOStatement, PDOException | Impacte: persistance SQL std_clients, mise à jour/insert des données client | Tables: std_clients(phoneNumber, nom_prenom, adresse, complement_adresse, code_postal, ville, num_supp1, num_supp2, probleme, stop_sms, order_online, points_fid, delivery_near_confirmed) */
@require_once __DIR__ . '/clientPhoneResolver.php';
@require_once __DIR__ . '/deliveryCityChoices.php';
/**
 * Shared client upsert helpers.
 * Goal: keep client persistence rules identical across CREATE and UPDATE.
 *
 * Rules:
 * - phone is normalized (same as clientLookup.php) and is the primary matching key:
 *   std_clients.phoneNumber OR num_supp1/2
 * - merge (merge-patch semantics):
 *   - key ABSENT in payload => do not modify column
 *   - key PRESENT with null => clear column (set NULL; if DB rejects NULL, fallback to '')
 *   - key PRESENT with non-empty string => update column
 *   - key PRESENT with empty string => ignore (backward compatibility with old clients)
 */

function normalizePhoneForDb(string $input): string {
  $raw = trim($input);
  if ($raw === '') return '';

  $s = preg_replace('/\s+/', '', $raw);
  if ($s === null) $s = $raw;

  // Handle + / 00 prefixes
  if (strlen($s) > 0 && $s[0] === '+') {
    $s = substr($s, 1);
  }
  if (strpos($s, '00') === 0) {
    $s = substr($s, 2);
  }

  // Digits only
  $digits = preg_replace('/\D+/', '', $s);
  if ($digits === null) $digits = '';
  $digits = trim($digits);
  if ($digits === '') return '';

  // France local: 10 digits starting with 0 => 33 + last 9
  if (preg_match('/^0\d{9}$/', $digits) === 1) {
    return '33' . substr($digits, 1);
  }
  // France already normalized: 33 + 9 digits
  if (preg_match('/^33\d{9}$/', $digits) === 1) {
    return $digits;
  }
  // International: keep digits
  return $digits;
}

function cleanStr($v, int $maxLen = 0): string {
  $s = trim((string)($v ?? ''));
  if ($s === '') return '';
  if ($maxLen > 0 && mb_strlen($s, 'UTF-8') > $maxLen) {
    $s = mb_substr($s, 0, $maxLen, 'UTF-8');
  }
  return $s;
}

/**
 * Bind helper that supports NULL explicitly.
 */
function bindValueNullable(PDOStatement $stmt, string $key, $value): void {
  if ($value === null) {
    $stmt->bindValue($key, null, PDO::PARAM_NULL);
    return;
  }
  $stmt->bindValue($key, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
}

/**
 * Execute an UPDATE that may include NULL binds.
 * If DB rejects NULL for some columns (NOT NULL), retry by converting NULL -> '' for those fields.
 */
function executeClientUpdateWithNullFallback(PDO $pdo, string $sqlU, array $bind): void {
  $stmtU = $pdo->prepare($sqlU);
  foreach ($bind as $k => $v) bindValueNullable($stmtU, $k, $v);
  try {
    $stmtU->execute();
    return;
  } catch (PDOException $e) {
    $code = (string)$e->getCode();
    $msg = strtolower((string)$e->getMessage());
    $looksLikeNullViolation = ($code === '23000' || str_contains($msg, 'cannot be null') || str_contains($msg, 'not null'));
    if (!$looksLikeNullViolation) throw $e;

    // Retry with NULL -> '' (best-effort for NOT NULL columns).
    $bind2 = $bind;
    foreach ($bind2 as $k => $v) {
      if ($v === null && $k !== ':id' && $k !== ':phone') $bind2[$k] = '';
    }
    $stmt2 = $pdo->prepare($sqlU);
    foreach ($bind2 as $k => $v) bindValueNullable($stmt2, $k, $v);
    $stmt2->execute();
  }
}

function shouldPreserveExistingDeliveryCity(array $payload): bool {
  $raw = $payload['deliveryCityChoicePreserveExisting'] ?? false;
  if ($raw === true || $raw === 1 || $raw === '1') return true;
  $s = strtolower(trim((string)$raw));
  return in_array($s, ['true', 'on', 'yes'], true);
}

function upsertClientFromPayload(PDO $pdo, string $phoneRaw, $clientPayload, string $storeId = ''): int {
  $phone = normalizePhoneForDb($phoneRaw);
  if ($phone === '') return 0;

  // Basic validation (E.164 max 15 digits; accept down to 6 to avoid noisy junk)
  if (preg_match('/^\d+$/', $phone) !== 1) return 0;
  $len = strlen($phone);
  if ($len < 6 || $len > 15) return 0;

  $cp = (is_array($clientPayload) ? $clientPayload : []);
  $preserveDeliveryCity = shouldPreserveExistingDeliveryCity($cp);
  unset($cp['deliveryCityChoicePreserveExisting']);
  if ($storeId !== '') {
    if ($preserveDeliveryCity) {
      unset(
        $cp['ville'],
        $cp['code_postal'],
        $cp['codePostal'],
        $cp['city'],
        $cp['deliveryCityChoice'],
        $cp['city_choice']
      );
    } elseif (array_key_exists('ville', $cp) || array_key_exists('code_postal', $cp)) {
      $normalized = caisseNormalizeDeliveryCityPayload($cp, $storeId, 'ville', 'code_postal');
      if (!empty($normalized['ok']) && is_array($normalized['payload'])) {
        $cp = $normalized['payload'];
      } else {
        return 0;
      }
    }
  }

  // Allowed fields (DB columns) + merge-patch interpretation
  // IMPORTANT: use array_key_exists() to distinguish "absent" vs "present => null".
  $fieldDefs = [
    ['key' => 'nom_prenom',          'col' => 'nom_prenom',          'max' => 120],
    ['key' => 'adresse',             'col' => 'adresse',             'max' => 255],
    ['key' => 'complement_adresse',  'col' => 'complement_adresse',  'max' => 255],
    ['key' => 'code_postal',         'col' => 'code_postal',         'max' => 20],
    ['key' => 'ville',               'col' => 'ville',               'max' => 120],
    ['key' => 'num_supp1',           'col' => 'num_supp1',           'max' => 30],
    ['key' => 'num_supp2',           'col' => 'num_supp2',           'max' => 30],
    ['key' => 'probleme',            'col' => 'probleme',            'max' => 500],
  ];

  // 1) Lookup existing client (deterministic: prefer primary phoneNumber owner,
  // then unique supplementary owner; never rely on LIMIT 1 over OR matches).
  $row = resolveClientByPhoneMatch($pdo, $phone);
  if (is_array($row) && isset($row['id'])) {
    $id = (int)$row['id'];
    if ($id <= 0) return 0;

    // 2) UPDATE with merge-patch rule (absent=no change, null=clear, non-empty string=set).
    $sets = [];
    $bind = [':id' => $id, ':phone' => $phone];

    // Always keep phoneNumber normalized to the committed phone (safe)
    $sets[] = "phoneNumber = :phone";

    foreach ($fieldDefs as $fd) {
      $k = (string)$fd['key'];
      $col = (string)$fd['col'];
      $max = (int)$fd['max'];

      if (!array_key_exists($k, $cp)) continue; // absent => no change

      $rawV = $cp[$k];
      if ($rawV === null) {
        // present null => clear
        $ph = ':' . $k;
        $sets[] = "{$col} = {$ph}";
        $bind[$ph] = null;
        continue;
      }

      // present non-null => set only if non-empty after cleaning (backward compat)
      $clean = cleanStr($rawV, $max);
      if ($clean === '') continue;
      $ph = ':' . $k;
      $sets[] = "{$col} = {$ph}";
      $bind[$ph] = $clean;
    }

    $sqlU = "UPDATE std_clients SET " . implode(", ", $sets) . " WHERE id = :id";
    executeClientUpdateWithNullFallback($pdo, $sqlU, $bind);
    return $id;
  }

  // 3) INSERT new client
  $sqlI = "INSERT INTO std_clients (
    phoneNumber,
    nom_prenom,
    adresse,
    complement_adresse,
    code_postal,
    ville,
    num_supp1,
    num_supp2,
    probleme,
    stop_sms,
    order_online,
    points_fid,
    delivery_near_confirmed
  ) VALUES (
    :phoneNumber,
    :nom_prenom,
    :adresse,
    :complement_adresse,
    :code_postal,
    :ville,
    :num_supp1,
    :num_supp2,
    :probleme,
    0,
    0,
    0,
    0
  )";
  $stmtI = $pdo->prepare($sqlI);
  // For INSERT: keep existing behavior (empty strings allowed), but accept explicit null too.
  $nomPrenom = array_key_exists('nom_prenom', $cp) && $cp['nom_prenom'] === null ? null : cleanStr($cp['nom_prenom'] ?? '', 120);
  $adresse   = array_key_exists('adresse', $cp) && $cp['adresse'] === null ? null : cleanStr($cp['adresse'] ?? '', 255);
  $comp      = array_key_exists('complement_adresse', $cp) && $cp['complement_adresse'] === null ? null : cleanStr($cp['complement_adresse'] ?? '', 255);
  $codePostal= array_key_exists('code_postal', $cp) && $cp['code_postal'] === null ? null : cleanStr($cp['code_postal'] ?? '', 20);
  $ville     = array_key_exists('ville', $cp) && $cp['ville'] === null ? null : cleanStr($cp['ville'] ?? '', 120);
  $num1      = array_key_exists('num_supp1', $cp) && $cp['num_supp1'] === null ? null : cleanStr($cp['num_supp1'] ?? '', 30);
  $num2      = array_key_exists('num_supp2', $cp) && $cp['num_supp2'] === null ? null : cleanStr($cp['num_supp2'] ?? '', 30);
  $probleme  = array_key_exists('probleme', $cp) && $cp['probleme'] === null ? null : cleanStr($cp['probleme'] ?? '', 500);

  $stmtI->bindValue(':phoneNumber', $phone, PDO::PARAM_STR);
  bindValueNullable($stmtI, ':nom_prenom', $nomPrenom);
  bindValueNullable($stmtI, ':adresse', $adresse);
  bindValueNullable($stmtI, ':complement_adresse', $comp);
  bindValueNullable($stmtI, ':code_postal', $codePostal);
  bindValueNullable($stmtI, ':ville', $ville);
  bindValueNullable($stmtI, ':num_supp1', $num1);
  bindValueNullable($stmtI, ':num_supp2', $num2);
  bindValueNullable($stmtI, ':probleme', $probleme);
  $stmtI->execute();

  $newId = (int)$pdo->lastInsertId();
  return ($newId > 0) ? $newId : 0;
}