<?php

class Orders {
  public const MODE_SAME      = 1;
  public const MODE_DIFFERENT = 2;

  private PDO $pdo;
  private Cart $cart;
  private int $fallbackCountry;
  

  public function __construct(PDO $pdo, Cart $cart, int $fallbackCountry = 1) {
    $this->pdo  = $pdo;
    $this->cart = $cart;
    $this->fallbackCountry = $fallbackCountry;
  }
  
  
  public function getShippingPreviewForCart(?string $method_key = null, ?int $id_country = null): ?array {
  	$stash = $this->loadCheckoutStash();
  	
  	if (!empty($stash['_lock']['order_id']) && !empty($stash['_lock']['shipping'])) {
  		$locked = $stash['_lock']['shipping'];
    	$locked_method = $locked['method_key'] ?? null;
    	$locked_country = (int)($stash['shipping_raw_country'] ?? $stash['billing_raw_country'] ?? $this->fallbackCountry);

		
	    $country_ok = ($id_country === null || (int)$id_country === $locked_country);
	    if (($method_key === null || $method_key === '' || $method_key === $locked_method) && $country_ok) {
	      $weight = $this->calcCartMetrics(($this->cart->get()['items'] ?? []));
			  return [
			    'method_key'   => $locked['method_key'],
			    'method_label' => $locked['method_label'],
			    'carrier'      => $locked['carrier'] ?? null,
			    'service_code' => $locked['service_code'] ?? null,
			    'tax_rate'     => (float)$locked['tax_rate'],
			    'price_net'    => (float)$locked['price_net'],
			    'price_tax'    => (float)$locked['price_tax'],
			    'price_gross'  => (float)$locked['price_gross'],
			    'weight'       => round((float)$weight, 3),
			    'id_country'   => $locked_country,
			  ];
			}
		}

	  $cart = $this->cart->get();
	  if (!$cart || empty($cart['items'])) return null;
	  
	
	  $method = ($method_key !== null && $method_key !== '') ? $method_key : ($stash['r_shipping_method'] ?? 'standard');
	  
    $effective_country = $this->resolveEffectiveCountryIdForCart($id_country);
    if ($effective_country <= 0) return null;
    
    $quote = $this->quoteBestShipping($method, $effective_country, $cart);
    if (!$quote) return null;
    
    $weight = $this->calcCartMetrics($cart['items'] ?? []);

    $quote['weight'] = round((float)$weight, 3);
    $quote['id_country'] = (int)$effective_country;
    $quote['method_key'] = $method;

    return $quote;
  }
	
	public function getAvailableShippingMethodsByCountry(int $id_country): array {
	  $zone = $this->matchZoneByCountryId($id_country);
	  if (!$zone) return [];
	  $sql = "SELECT DISTINCT shipping_methods.method_key, shipping_methods.label
	          FROM shipping_methods AS shipping_methods
	          JOIN shipping_routes AS shipping_routes ON shipping_routes.id_method = shipping_methods.id
	          WHERE shipping_methods.c_active = '1' AND shipping_routes.c_active = '1' AND shipping_routes.id_zone = :id_zone
	          ORDER BY shipping_methods.sortorder ASC, shipping_methods.label ASC";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id_zone', (int)$zone['id'], PDO::PARAM_INT);
	  $result->execute();
	  return $result->fetchAll(PDO::FETCH_ASSOC) ?: [];
	}
	
	public function onCartMutated(): void {
	  $cart = $this->cart->get();
	  if (!$cart || empty($cart['id'])) return;
	  $snapshot = $this->buildDiscountSnapshot(null);
	  $discount = new Discounts($this->pdo, $cart['id_customer'] ?? null, 'EUR');
	  $discount->refreshAutoDiscounts((int)$cart['id'], $snapshot);
	}
	
	public function buildDiscountSnapshot(?array $shipping_preview = null): array {
	  $cart = $this->cart->get() ?? [];
	  $items = $cart['items'] ?? [];
	  foreach ($items AS &$item) {
	    if (!isset($item['line_subtotal_net']) && isset($item['line_total_net'])) {
	      $item['line_subtotal_net'] = (float)$item['line_total_net'];
	    }
	    if (!isset($item['discount_exempt'])) $item['discount_exempt'] = 0;
	  } unset($item);
	
	  $subtotal = (float)($cart['totals']['subtotal_net'] ?? 0.0);
	
	  return [
	    'items' 							=> $items,
	    'totals' 							=> ['subtotal_net' => $subtotal],
	    'totals_subtotal_net' => $subtotal,
	    'shipping_price_net' 	=> (float)($shipping_preview['price_net'] ?? 0.0),
	    'shipping_tax_rate' 	=> isset($shipping_preview['tax_rate']) ? (float)$shipping_preview['tax_rate'] : 0.0,
	  ];
	}
	
	
  public function getOrderIdByProviderRef(string $provider_ref): ?int {
	  $sql = "SELECT id_module FROM orders_payment WHERE provider_ref = :provider_ref ORDER BY id DESC LIMIT 1";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':provider_ref', $provider_ref, PDO::PARAM_STR);
	  $result->execute();
	  $id = $result->fetchColumn();
	  return $id ? (int)$id : null;
	}
  
  public function finalizeOrder(int $id_order, ?int $id_customer, string $final_status, string $changed_by = 'system'): array {
    $cart = $this->cart->get();
    if (!$cart || empty($cart['id'])) {
      return ['ok' => false, 'errors' => ['CART_MISSING']];
    }
    
    $token = bin2hex(random_bytes(16));
	  $sql = "UPDATE orders SET processing_token = :processing_token WHERE id = :id AND (processing_token IS NULL OR processing_token = '')";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':processing_token', $token, PDO::PARAM_STR);
	  $result->bindValue(':id', $id_order, PDO::PARAM_INT);
	  $result->execute();
	  if ($result->rowCount() === 0) {
	    return ['ok' => true];
	  }
	  
	  $sql = "SELECT c_stock_committed FROM orders WHERE id = :id LIMIT 1";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id', $id_order, PDO::PARAM_INT);
	  $result->execute();
	  $committed = (string)$result->fetchColumn() === '1';
	
	  if (!$committed) {
	    $ok = $this->cart->commitForOrder($id_customer);
	    if (!$ok) {
	      $this->insertStatusHistory($id_order, 'payment', 'pending', $changed_by, 'stock.consume_failed');
	      $sql = "UPDATE orders SET processing_token = NULL WHERE id = :id";
	      $result = $this->pdo->prepare($sql);
	      $result->bindValue(':id', $id_order, PDO::PARAM_INT);
	      $result->execute();
	      return ['ok' => false, 'errors' => ['CART_COMMIT']];
	    }
      $sql = "UPDATE orders SET c_stock_committed = '1' WHERE id = :id";
      $result = $this->pdo->prepare($sql);
      $result->bindValue(':id', $id_order, PDO::PARAM_INT);
      $result->execute();
	  }
	  
	  $order_number = $this->ensureOrderNumber($id_order);

    $this->updatePaymentStatus($id_order, $final_status);
    $this->insertStatusHistory($id_order, 'payment', $final_status, $changed_by);
    $this->cart->clear();
    $this->deleteCheckoutStashByCartId((int)$cart['id']);
    $discount = new Discounts($this->pdo, $id_customer, 'EUR');
  	$discount->captureOrderRedemptions($id_order);
    
    $sql = "UPDATE orders SET processing_token = NULL WHERE id = :id";
    $result = $this->pdo->prepare($sql);
    $result->bindValue(':id', $id_order, PDO::PARAM_INT);
    $result->execute();
    
    try {
	    require_once $_SERVER['DOCUMENT_ROOT'].'/includes/function/orders_mail.php';
	    $mailConfig = [
        'shop_name'      => 'Mein Shop',
        'from_email'     => 'ulbig@entire-media.de',
        'from_name'      => 'Mein Shop',
        'operator_email' => 'betreiber@agentur-hof.de'
    	];
    	$dispatcher = new \MailDispatcher($this->pdo, $mailConfig);
    	$dispatcher->enqueue($id_order, 'order_confirmation', 'customer',  "oc:customer:".$id_order);
    	$dispatcher->enqueue($id_order, 'order_confirmation', 'operator',  "oc:operator:".$id_order);
    	if ($final_status === 'paid') {
        #### create invoice and send invoice
        ###$dispatcher->enqueue($id_order, 'invoice', 'customer', "invoice:customer:{$id_order}");
    	}
    	$max_age_seconds = 300;
    	$heartbeat_file = $_SERVER['DOCUMENT_ROOT'].'/comator/cron/orders_heartbeat.log';
			$last_run_time = 0;
			if (file_exists($heartbeat_file)) {
			  $last_run = file_get_contents($heartbeat_file);
			  $last_run_time = is_numeric($last_run) ? (int)$last_run : 0;
			}
			
			
			if (time() - $last_run_time >= $max_age_seconds) {
				$sql = "SELECT * FROM orders_mail_queue WHERE status = 'queued' AND id_module = :id_module ORDER BY id ASC";
				$result = $this->pdo->prepare($sql);
        $result->bindValue(':id_module', $id_order, PDO::PARAM_INT);
        $result->execute();
        $arr = $result->fetchAll(PDO::FETCH_ASSOC) ?: [];

        $reflector = (new \ReflectionClass($dispatcher))->getMethod('deliver');
        $reflector->setAccessible(true);
        foreach ($arr AS $item) {
        	$reflector->invoke($dispatcher, $item);
        }
			}
    	
	  } catch (\Throwable $e) {
	    error_log('order_mail_failed '.$id_order.' '.$e->getMessage());
	  }

    return ['ok' => true];
  }
  
	public function getCartSnapshot(): array {
	  $cart = $this->cart->get();
	  if (!$cart || empty($cart['id'])) return [];
	
	  $signature = [
	    'id_cart' => $cart['id'],
	    'items'   => $cart['items'] ?? [],
	    'total'   => $cart['totals']['total_gross'] ?? null,
	    'currency'=> 'EUR',
	    'locale'  => $cart['locale'] ?? 'de',
	  ];
	  return [
	    'id_cart' 		=> (int)$cart['id'],
	    'session_id' 	=> $cart['session_id'] ?? null,
	    'items'   		=> $cart['items'] ?? [],
	    'totals'  		=> $cart['totals'] ?? [],
	    'currency'		=> 'EUR',
	    'locale'  		=> $cart['locale'] ?? 'de',
	    'hash'    		=> hash('sha256', json_encode($signature, JSON_UNESCAPED_UNICODE)),
	  ];
	}
	
	public function stashPaymentApproval(array $payment, string $session_id): bool {
	  $cart = $this->cart->get();
	  $id_cart = $cart['id'] ?? null;
	  if (!$id_cart) return false;
	
	  $sql = "SELECT data_json FROM carts_checkout WHERE id_module = :id_module LIMIT 1";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id_module', $id_cart, PDO::PARAM_INT);
	  $result->execute();
	  $current = $result->fetchColumn();
	  $data = $current ? (json_decode($current, true) ?: []) : [];
	
	  $data['payment'] = array_merge(($data['payment'] ?? []), [
	    'key'          => $payment['key'] ?? ($data['payment']['key'] ?? null),
	    'approved'     => !empty($payment['approved']),
	    'provider_ref' => $payment['provider_ref'] ?? ($data['payment']['provider_ref'] ?? null),
	    'session_id'   => $session_id,
	    'cart_hash'    => $payment['cart_hash'] ?? ($data['payment']['cart_hash'] ?? null),
	  ]);
	
	  $json = json_encode($data, JSON_UNESCAPED_UNICODE);
	  $sql = "INSERT INTO carts_checkout SET 
		  			datetime = :datetime, 
		  			id_module = :id_module,
		  			session_id = :session_id, 
		  			data_json = :data_json 
		  			ON DUPLICATE KEY UPDATE data_json = VALUES(data_json), datetime = VALUES(datetime)";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':datetime', time(), PDO::PARAM_INT);
	  $result->bindValue(':id_module', $id_cart, PDO::PARAM_INT);
	  $result->bindValue(':session_id', $session_id);
	  $result->bindValue(':data_json', $json);
	  return $result->execute();
	}

  private function deleteCheckoutStashByCartId(int $id_cart): void {
	  $sql = "DELETE FROM carts_checkout WHERE id_module = :id_module";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id_module', $id_cart, PDO::PARAM_INT);
	  $result->execute();
	}

  public function loadCustomerData(?int $id_customer, ?callable $decryptor = null): array {
    if (!$id_customer) return [];
    $sql = "SELECT * FROM customers WHERE id = :id LIMIT 1";
    $result = $this->pdo->prepare($sql);
    $result->bindValue(':id', $id_customer, PDO::PARAM_INT);
    $result->execute();
    $arr = $result->fetch(PDO::FETCH_ASSOC) ?: [];
    if (!$arr) return [];

    if ($decryptor) {
      foreach ($arr AS $key => $value) {
        $arr[$key] = $decryptor($key, $value);
      }
    }
    if (!empty($arr['birthday'])) {
      $arr['birthday_day'] = (int)date('j', (int)$arr['birthday']);
      $arr['birthday_month'] = (int)date('n', (int)$arr['birthday']);
      $arr['birthday_year'] = (int)date('Y', (int)$arr['birthday']);
    }
    return $arr;
  }
  
  public function loadCustomerAddresses(int $id_customer): array {
	  $sql = "SELECT * FROM customers_addresses WHERE id_module = :id_module ORDER BY c_default DESC, id DESC";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id_module', $id_customer, PDO::PARAM_INT);
	  $result->execute();
	  return $result->fetchAll(PDO::FETCH_ASSOC) ?: [];
	}
	
	public function ensureDefaultCustomerAddress(int $id_customer, array $seed): int {
	  $list = $this->loadCustomerAddresses($id_customer);
	  if ($list) {
	    foreach ($list as $address) { if ((string)$address['c_default'] === '1') return (int)$address['id']; }
	  }

	  $fullname = trim(($seed['firstname'] ?? '').' '.($seed['lastname'] ?? ''));
	  $sql = "INSERT INTO customers_addresses SET
            datetime = :datetime,
            id_module = :id_module,
            c_default = '1',
            email = :email,
            fullname = :fullname,
            company = :company,
            addressline1 = :addressline1,
            addressline2 = :addressline2,
            zip = :zip,
            city = :city,
            id_state = :id_state,
            id_country = :id_country,
            phone = :phone";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':datetime', time(), PDO::PARAM_INT);
	  $result->bindValue(':id_module', $id_customer, PDO::PARAM_INT);
	  $result->bindValue(':email', $seed['email'] ?? null);
	  $result->bindValue(':fullname', $fullname ?: null);
	  $result->bindValue(':company', $seed['company'] ?? null);
	  $result->bindValue(':addressline1', $seed['addressline1'] ?? null);
	  $result->bindValue(':addressline2', $seed['addressline2'] ?? null);
	  $result->bindValue(':zip', $seed['zip'] ?? null);
	  $result->bindValue(':city', $seed['city'] ?? null);
	  $result->bindValue(':id_state', $seed['id_state'] ?? null);
	  $result->bindValue(':id_country', $seed['id_country'] ?? null);
	  $result->bindValue(':phone', $seed['phone'] ?? null);
	  $result->execute();
	  return (int)$this->pdo->lastInsertId();
	}

	public function saveCustomerAddress(int $id_customer, array $address, bool $make_default = false): int {
	  if ($make_default) {
	    $sql = "UPDATE customers_addresses SET c_default = '0' WHERE id_module = :id_module";
	    $result = $this->pdo->prepare($sql);
	    $result->bindValue(':id_module', $id_customer, PDO::PARAM_INT);
	    $result->execute();
	  }
	
	  $sql = "INSERT INTO customers_addresses SET
            datetime = :dt,
            id_module = :id_module,
            c_default = :c_default,
            email = :email,
            fullname = :fullname,
            company = :company,
            addressline1 = :addressline1,
            addressline2 = :addressline2,
            zip = :zip,
            city = :city,
            id_state = :id_state,
            id_country = :id_country,
            phone = :phone";
	  $st = $this->pdo->prepare($sql);
	  $st->bindValue(':dt', time(), PDO::PARAM_INT);
	  $st->bindValue(':id_module', $id_customer, PDO::PARAM_INT);
	  $st->bindValue(':c_default', $make_default ? '1' : '0');
	  $st->bindValue(':email', $address['email'] ?? null);
	  $st->bindValue(':fullname', $address['fullname'] ?? null);
	  $st->bindValue(':company', $address['company'] ?? null);
	  $st->bindValue(':addressline1', $address['addressline1'] ?? null);
	  $st->bindValue(':addressline2', $address['addressline2'] ?? null);
	  $st->bindValue(':zip', $address['zip'] ?? null);
	  $st->bindValue(':city', $address['city'] ?? null);
	  $st->bindValue(':id_state', $address['id_state'] ?? null);
	  $st->bindValue(':id_country', $address['id_country'] ?? null);
	  $st->bindValue(':phone', $address['phone'] ?? null);
	  $st->execute();
	
	  return (int)$this->pdo->lastInsertId();
	}

  public function getActivePaymentProviders(?string $create_point = null): array {
    if ($create_point) {
    	$sql = "SELECT * FROM payment_providers WHERE c_active = '1' AND create_point = :create_point ORDER BY sortorder ASC";
      $result = $this->pdo->prepare($sql);
      $result->bindValue(':create_point', $create_point, PDO::PARAM_STR);
    } else {
    	$sql = "SELECT * FROM payment_providers WHERE c_active = '1' ORDER BY sortorder ASC";
      $result = $this->pdo->prepare($sql);
    }
    $result->execute();
    return $result->fetchAll(PDO::FETCH_ASSOC) ?: [];
  }

  public function getPaymentProviderById(int $id): ?array {
  	$sql = "SELECT * FROM payment_providers WHERE id = :id AND c_active = '1' LIMIT 1";
    $result = $this->pdo->prepare($sql);
    $result->bindValue(':id', $id, PDO::PARAM_INT);
    $result->execute();
    $arr = $result->fetch(PDO::FETCH_ASSOC);
    return $arr ?: null;
  }

  public function mapState($state): string {
    if ($state === '' || $state === null) return '';
    $str = (string)$state;
    if (ctype_digit($str)) {
    	$sql = "SELECT label FROM states WHERE id = :id LIMIT 1";
    	$result = $this->pdo->prepare($sql);
      $result->bindValue(':id', (int)$str, PDO::PARAM_INT);
      $result->execute();
      $label = $result->fetchColumn();
      if ($label) return (string)$label;
    }
    return $str;
  }

  public function mapCountry($country): string {
    if ($country === '' || $country === null) return '';
    $str = (string)$country;
    if (ctype_digit($str)) {
    	$sql = "SELECT label FROM country WHERE id = :id LIMIT 1";
    	$result = $this->pdo->prepare($sql);
      $result->bindValue(':id', (int)$str, PDO::PARAM_INT);
      $result->execute();
      $label = $result->fetchColumn();
      if ($label) return (string)$label;
    }
    return $str;
  }
  
  private function composeFullname(string $firstname = '', string $lastname = '', string $fallback = ''): string {
	  $fullname = trim($firstname.' '.$lastname);
	  return $fullname !== '' ? $fullname : trim($fallback);
	}

  public function extractAddressesFromPost(array $post): array {
  	
	  $shipping_id = (int)($post['shipping_address_id'] ?? 0);
	  
	  $to_timestamp = static function (?string $ymd): ?int {
	    $ymd = trim((string)$ymd);
	    if ($ymd === '') return null;
	    $dt = DateTime::createFromFormat('!Y-m-d', $ymd);
	    return $dt ? $dt->getTimestamp() : null;
	  };
	  
	  if (isset($post['birthday']) && $post['birthday']){
			$post['birthday'] = $to_timestamp($post['birthday']);
		}
		
	  if (isset($post['birthday_day']) && $post['birthday_day'] && isset($post['birthday_month']) && $post['birthday_month'] && isset($post['birthday_year']) && $post['birthday_year']){
			$post['birthday'] = $to_timestamp($post['birthday_year'].'-'.$post['birthday_month'].'-'.$post['birthday_day']);
		}

    $billing = [
      'type'           		=> 'billing',
      'birthday' 					=> isset($post['birthday']) && $post['birthday'] !== '' ? (int)$post['birthday'] : null,
      'email'          		=> trim($post['email'] ?? ''),
      'fullname'      		=> trim($post['fullname'] ?? ''),
      'company'        		=> trim($post['company'] ?? ''),
      'addressline1'   		=> trim($post['addressline1'] ?? ''),
      'addressline2'   		=> trim($post['addressline2'] ?? ''),
      'zip'            		=> trim($post['zip'] ?? ''),
      'city'           		=> trim($post['city'] ?? ''),
      'phone'    					=> trim($post['phone'] ?? ''),
    ];
    

    $mode = (int)($post['r_shipping_address'] ?? self::MODE_SAME);

    if ($mode === self::MODE_SAME) {
      $shipping = $billing;
      $shipping['type'] = 'shipping';
      $email_shipping = trim($post['delivery_email'] ?? '');
    	if ($email_shipping !== '') $shipping['email'] = $email_shipping;
    } elseif ($mode === self::MODE_DIFFERENT) {
      $shipping = [
        'type'            	=> 'shipping',
        'birthday'          => isset($post['delivery_birthday']) && $post['delivery_birthday'] !== '' ? (int)$post['delivery_birthday'] : ($billing['birthday'] ?? null),
        'email'           	=> trim($post['delivery_email'] ?? $billing['email']),
	      'fullname'      		=> trim($post['delivery_fullname'] ?? ''),
	      'company'        		=> trim($post['delivery_company'] ?? ''),
	      'addressline1'   		=> trim($post['delivery_addressline1'] ?? ''),
	      'addressline2'   		=> trim($post['delivery_addressline2'] ?? ''),
	      'zip'            		=> trim($post['delivery_zip'] ?? ''),
	      'city'           		=> trim($post['delivery_city'] ?? ''),
        'phone'      				=> trim($post['delivery_phone'] ?? $billing['phone']),
      ];
    }
    return [$billing, $shipping, $shipping_id, $mode];
  }

  public function validateAddresses(array $billing, array $shipping): array {
    $errors = [];
    foreach (['fullname','addressline1','zip','city','email'] AS $key) {
      if (($billing[$key] ?? '') === '') $errors[] = "BILLING_ERROR_".strtoupper($key);
    }
    if (($billing['email'] ?? '') !== '' && !filter_var($billing['email'], FILTER_VALIDATE_EMAIL)) {
			$errors[] = 'billing.email_format';
		}
    foreach (['fullname','addressline1','zip','city'] AS $key) {
      if (($shipping[$key] ?? '') === '') $errors[] = "SHIPPING_ERROR_".strtoupper($key);
    }
    return $errors;
  }

  public function stashCheckoutData(array $post, string $session_id): array {
  	$existing = $this->loadCheckoutStash();
    $cart = $this->cart->get();
    $id_cart = $cart['id'] ?? null;
    if (!$id_cart) return ['ok' => false, 'errors' => ['CART_MISSING']];
    
    if (isset($post['website']) && trim((string)$post['website']) !== '') {
    	return ['ok' => false, 'errors' => ['HONEYPOT']];
  	}

    [$billing, $shipping, $shipping_id, $mode] = $this->extractAddressesFromPost($post);
    
    $billing_state  = $post['id_state'] ?? '';
    $billing_country  = $post['id_country'] ?? '';
    
    $chosenMethod = strtolower(trim((string)($post['r_shipping_method'] ?? '')));
		if ($chosenMethod === 'pickup') {
			$post['r_shipping_address'] = self::MODE_SAME;
		}
    
    if ($mode === self::MODE_SAME) {
    	$shipping_state = $billing_state;
    	$shipping_country = $billing_country;
    } elseif ($mode === self::MODE_DIFFERENT) {
    	$shipping_state = $post['delivery_id_state'] ?? $billing_state;
    	$shipping_country = $post['delivery_id_country'] ?? $billing_country;
    }
    $id_customer = $this->cart->get()['id_customer'] ?? null;
    
	  if ($id_customer) {
	    if ($mode !== self::MODE_SAME) {
	      if ($shipping_id > 0) {
	        $address = $this->loadAddressById($id_customer, $shipping_id);
	        if ($address) {
	          $shipping = $this->fromCustomerAddressRow($address, 'shipping');
	          $shipping_state = (int)$address['id_state'];
	          $shipping_country = (int)$address['id_country'];
	        }
	      } else {
	        $c_save_address = !empty($post['c_save_address']);
	        if ($c_save_address) {
	          $this->saveCustomerAddress($id_customer, [
	            'email'        => $shipping['email'] ?? null,
	            'fullname'     => $shipping['fullname'] ?? null,
	            'company'      => $shipping['company'] ?? null,
	            'addressline1' => $shipping['addressline1'] ?? null,
	            'addressline2' => $shipping['addressline2'] ?? null,
	            'zip'          => $shipping['zip'] ?? null,
	            'city'         => $shipping['city'] ?? null,
	            'id_state'     => $shipping_state ?: null,
	            'id_country'   => $shipping_country ?: null,
	            'phone'  			 => $shipping['phone'] ?? null,
	          ], false);
	        }
	      }
	    }
	  }



		$errors = [];
    $c_terms = !empty($post['c_terms']);
    $c_cancellation = !empty($post['c_cancellation']);
    if (!$c_terms) $errors[] = 'CONSENT_TERMS';
    if (!$c_cancellation) $errors[] = 'CONSENT_CANCELLATION';
    
    $errors = array_merge($errors, $this->validateAddresses($billing, $shipping));


    $id_payment = (int)($post['r_payment_method'] ?? 0);
    $payment_provider  = $id_payment ? $this->getPaymentProviderById($id_payment) : null;
    if (!$payment_provider) {
      $payment_provider = $this->getPaymentProviderById(1);
    }
    if (!$payment_provider) $errors[] = 'PAYMENT_INVALID';

    if ($errors) return ['ok' => false,'errors' => $errors];

    $billing['state']  = $this->mapState($billing_state);
    $shipping['state'] = $this->mapState($shipping_state);
    $billing['country']  = $this->mapCountry($billing_country);
    $shipping['country'] = $this->mapCountry($shipping_country);
    
    
    if (!empty($billing['birthday'])) {
      $billing['birthday_day'] = (int)date('j', (int)$billing['birthday']);
      $billing['birthday_month'] = (int)date('n', (int)$billing['birthday']);
      $billing['birthday_year'] = (int)date('Y', (int)$billing['birthday']);
    }
    
    if (!empty($shipping['birthday'])) {
      $shipping['birthday_day'] = (int)date('j', (int)$shipping['birthday']);
      $shipping['birthday_month'] = (int)date('n', (int)$shipping['birthday']);
      $shipping['birthday_year'] = (int)date('Y', (int)$shipping['birthday']);
    }
    
    $tmp_payment = [
	    'id'    => (int)$payment_provider['id'],
	    'key'   => $payment_provider['provider_key'],
	    'label' => $payment_provider['label'],
	  ];
	  
	  $merged_payment = array_merge(($existing['payment'] ?? []), $tmp_payment);

    $payload = [
      'billing'     					=> $billing,
      'shipping'    					=> $shipping,
      'billing_raw_state'  		=> $billing_state,
      'shipping_raw_state' 		=> $shipping_state,
      'billing_raw_country'  	=> $billing_country,
      'shipping_raw_country' 	=> $shipping_country,
      'shipping_mode' 				=> $mode,
      'r_shipping_method' 		=> trim($post['r_shipping_method'] ?? 'standard'),
  		'shipping_address_id'   => (int)$shipping_id,
  		'c_save_address'        => ($shipping_id > 0 ? 0 : (!empty($post['c_save_address']) ? 1 : 0)),
      'payment'     					=> $merged_payment,
      'r_payment_method'			=> (int)$payment_provider['id'],
      'notes_customer'     		=> trim($post['notes_customer'] ?? ''),
      'c_terms'  							=> $c_terms,
      'c_cancellation'  			=> $c_cancellation,
      'c_newsletter'  				=> !empty($post['c_newsletter']) ? 1 : 0,
      'coupons'    						=> [],
    ];
    
    $payload['_meta'] = [
		  'id_cart'     => (int)$id_cart,
		  'id_customer' => (int)($this->cart->get()['id_customer'] ?? 0),
		  'ts'          => time(),
		];

    $json = json_encode($payload, JSON_UNESCAPED_UNICODE);
    
    $sql = "INSERT INTO carts_checkout SET 
    				datetime = :datetime, 
    				id_module = :id_module, 
    				session_id = :session_id, 
    				data_json = :data_json 
    				ON DUPLICATE KEY UPDATE data_json = VALUES(data_json), datetime = VALUES(datetime) ";
    $result = $this->pdo->prepare($sql);
    $result->bindValue(':datetime',  time(), PDO::PARAM_INT);
    $result->bindValue(':id_module', $id_cart, PDO::PARAM_INT);
    $result->bindValue(':session_id', $session_id);
    $result->bindValue(':data_json',  $json);
    $result->execute();

    return ['ok' => true];
  }
  
  private function loadAddressById(int $id_customer, int $id_address): ?array {
	  $sql = "SELECT * FROM customers_addresses WHERE id_module = :id_module AND id = :id LIMIT 1";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id_module', $id_customer, PDO::PARAM_INT);
	  $result->bindValue(':id', $id_address, PDO::PARAM_INT);
	  $result->execute();
	  $arr = $result->fetch(PDO::FETCH_ASSOC);
	  return $arr ?: null;
	}
	
	private function fromCustomerAddressRow(array $arr, string $type): array {
	  return [
	    'type'         => $type,
	    'email'        => $arr['email'] ?? '',
	    'fullname'     => $arr['fullname'] ?? '',
	    'company'      => $arr['company'] ?? '',
	    'addressline1' => $arr['addressline1'] ?? '',
	    'addressline2' => $arr['addressline2'] ?? '',
	    'zip'          => $arr['zip'] ?? '',
	    'city'         => $arr['city'] ?? '',
	    'phone'  			 => $arr['phone'] ?? '',
	  ];
	}

  public function loadCheckoutStash(): array {
  	$cart = $this->cart->get();
    $id_cart = $cart['id'] ?? null;
    if (!$id_cart) return [];
    $sql = "SELECT data_json FROM carts_checkout WHERE id_module = :id_module LIMIT 1";
    $result = $this->pdo->prepare($sql);
    $result->bindValue(':id_module', $id_cart, PDO::PARAM_INT);
    $result->execute();
    $json = $result->fetchColumn();
    if (!$json) return [];

	  $data = json_decode($json, true) ?: [];
	  $stash_customer = (int)($data['_meta']['id_customer'] ?? 0);
	  $current_customer = (int)($cart['id_customer'] ?? 0);
	
	  if ($current_customer > 0 && $stash_customer !== $current_customer) {
	    return [];
	  }
	  return $data;
  }

  public function createOrderFromCart(?int $id_customer): array {
    $cart = $this->cart->get();
    if (!$cart || !isset($cart['items'])) return ['ok' => false,'errors' => ['CART_MISSING']];

    $stash = $this->loadCheckoutStash();
    if (!$stash) return ['ok' => false, 'errors' => ['CHECKOUT_MISSING']];
    
    $id_cart = (int)($cart['id'] ?? 0);
    
    $method_key = $stash['r_shipping_method'] ?? 'standard';

		$shipping_country_raw = $stash['shipping_raw_country'] ?? '';
		$id_shipping_country = ctype_digit((string)$shipping_country_raw) ? (int)$shipping_country_raw : 0;
		if ($id_shipping_country <= 0) {
		  $id_shipping_country = ctype_digit((string)($stash['billing_raw_country'] ?? '')) ? (int)$stash['billing_raw_country'] : 0;
		}
		
    $snapshot = $this->getCartSnapshot();
    $cart_hash = $snapshot['hash'] ?? null;
    
    
		$ship = $this->quoteBestShipping($method_key, $id_shipping_country, $cart);
		if (!$ship) {
		  return ['ok' => false, 'errors' => ['SHIPPING_UNAVAILABLE']];
		}
    
		$cart_now = $this->cart->get();
		$discount = new Discounts($this->pdo, $cart_now['id_customer'] ?? null, 'EUR');
		$applied = $discount->loadCartDiscounts((int)$cart_now['id']);
		$discount_snapshot = $this->buildDiscountSnapshot($ship);
		$final_totals = $discount->recalculateTotals($discount_snapshot, $applied);
		
		$sum_discount_net = 0.0;
		$sum_discount_tax = 0.0;
		$sum_discount_gross = 0.0;
		foreach ($applied AS $discount_data) {
		  $sum_discount_net += (float)$discount_data['applied_amount'];
		  $sum_discount_tax += (float)($discount_data['applied_tax'] ?? 0.0);
		  $sum_discount_gross += (float)($discount_data['applied_gross'] ?? ((float)$discount_data['applied_amount']));
		}
		    
	  $signature = [
	    'id_cart' => $id_cart,
	    'items'   => $cart['items'],
	    'total'   => $cart['totals']['total_gross'] ?? null,
	    'payment' => $stash['payment']['key'] ?? null,
	    'shipping' => [
		    'key'     => $method_key,
		    'country' => $id_shipping_country,
		  ],
		  'discounts' => array_map(static function($discount){
		      return [
		        'type' => $discount['type'],
		        'code' => $discount['code'] ?? null,
		        'method' => $discount['method'],
		        'scope'  => $discount['scope'],
		        'applied'=> (float)$discount['applied_amount'],
		      ];
		  }, $applied),
		  'after_discount_total_net' => (float)$final_totals['total_net_after_discounts'],
		  'cart_hash' => $cart_hash,
	  ];
	  $idempotency_key = hash('sha256', json_encode($signature, JSON_UNESCAPED_UNICODE));
	  
	
		$sql = "SELECT id FROM orders WHERE idempotency_key = :idempotency_key LIMIT 1";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':idempotency_key', $idempotency_key, PDO::PARAM_STR);
	  $result->execute();
	  $existing_id = $result->fetchColumn();
	  if ($existing_id) {
    	$this->updatePaymentStatus($existing_id, 'pending');
			$this->insertStatusHistory($existing_id, 'payment', 'pending', 'system');
			$this->lockStashToOrder((int)$existing_id); 
	    return ['ok' => true, 'id_order' => (int)$existing_id];
	  }

    $billing = $stash['billing'];
    $shipping = $stash['shipping'];
    $payment = $stash['payment'];
    $notes_customer = $stash['notes_customer'] ?? '';
    $c_newsletter = (int)($stash['c_newsletter'] ?? 0);
    
		
		$discount_order_net = (float)$final_totals['discount_order_net'];
		$discount_shipping_net = (float)$final_totals['discount_shipping_net'];
		
		$totals_sub_net = (float)($cart['totals']['subtotal_net'] ?? 0.0);
		$ship_net = (float)$ship['price_net'];
		$ship_tax = (float)$ship['price_tax'];
		$ship_gross = (float)$ship['price_gross'];
		$cart_tax = (float)($cart['totals']['tax'] ?? 0.0);
		
		$net_after = max(0.0, $totals_sub_net - $discount_order_net) + max(0.0, $ship_net - $discount_shipping_net);
		$totals_tax = $cart_tax + $ship_tax - $sum_discount_tax;
		$totals_gross = $net_after + $totals_tax;
		
    
    $sql = "INSERT INTO orders SET 
    				datetime = :datetime, 
    				id_customer = :id_customer, 
    				currency_code = :currency_code, 
    				currency = :currency, 
    				locale = :locale,
    				status = :status,
    				totals_subtotal_net = :totals_subtotal_net, 
    				totals_discount_net = :totals_discount_net,
    				totals_discount_tax = :totals_discount_tax,
    				totals_discount_gross = :totals_discount_gross,
    				totals_tax = :totals_tax, 
    				totals_total_gross = :totals_total_gross,
						totals_shipping_net = :totals_shipping_net,
						totals_shipping_tax = :totals_shipping_tax,
						totals_shipping_gross = :totals_shipping_gross,
    				notes_customer = :notes_customer,
            idempotency_key = :idempotency_key ";
    $result = $this->pdo->prepare($sql);
    $result->bindValue(':datetime', time(), PDO::PARAM_INT);
    if ($id_customer === null) $result->bindValue(':id_customer', null, PDO::PARAM_NULL);
    else $result->bindValue(':id_customer', $id_customer, PDO::PARAM_INT);

    $result->bindValue(':currency_code', $cart['id_currency'] ? 'EUR' : 'EUR');
    $result->bindValue(':currency', 'Euro');
    $result->bindValue(':locale', $cart['locale'] ?? 'de');
    $result->bindValue(':status', 'unprocessed');
    $result->bindValue(':totals_subtotal_net', $totals_sub_net);
    $result->bindValue(':totals_discount_net', $sum_discount_net);
    $result->bindValue(':totals_discount_tax', $sum_discount_tax);
    $result->bindValue(':totals_discount_gross', $sum_discount_gross);
    $result->bindValue(':totals_tax', $totals_tax);
    $result->bindValue(':totals_total_gross', $totals_gross);
		$result->bindValue(':totals_shipping_net', $ship_net);
		$result->bindValue(':totals_shipping_tax', $ship_tax);
		$result->bindValue(':totals_shipping_gross', $ship_gross);
    if ($notes_customer === '' || $notes_customer === null) $result->bindValue(':notes_customer', null, PDO::PARAM_NULL);
    else $result->bindValue(':notes_customer', $notes_customer, PDO::PARAM_STR);
    $result->bindValue(':idempotency_key', $idempotency_key, PDO::PARAM_STR);
    
    $result->execute();
    $id_order = (int)$this->pdo->lastInsertId();

		$this->upsertOrderShipping($id_order, $ship, 'unshipped');
    $this->upsertAddressesForOrderId($id_order, $billing, $shipping);
    $this->copyCartItemsToOrder($id_order, $cart['items']);
    $discount->commitCartDiscountsToOrder($id_order, (int)$cart_now['id']);
    $this->insertPaymentRow($id_order, $payment['key'] ?? 'prepayment', $totals_gross, 'EUR', NULL);
    $this->insertStatusHistory($id_order, 'order', 'unprocessed', 'system');
		$this->lockStashToOrder($id_order);
		
    return ['ok' => true, 'id_order' => $id_order];
  }
  
  private function upsertOrderShipping(int $id_order, array $ship, string $status = 'unshipped'): void {
  	$sql = "DELETE FROM orders_shipping WHERE id_module = :id_module";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id_module', $id_order, PDO::PARAM_INT);
	  $result->execute();
	
	  $sql = "INSERT INTO orders_shipping SET
	          id_module = :id_module,
	          method_key = :method_key,
	          method_label = :method_label,
	          status = :status,
	          carrier = :carrier,
	          service_code = :service_code,
	          eta_hint = NULL,
	          tax_rate = :tax_rate,
	          price_net = :price_net,
	          price_tax = :price_tax,
	          price_gross = :price_gross,
	          label_meta_json = NULL";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id_module', $id_order, PDO::PARAM_INT);
	  $result->bindValue(':method_key', $ship['method_key']);
	  $result->bindValue(':method_label', $ship['method_label']);
	  $result->bindValue(':status', $status);
	  if (isset($ship['carrier']) && $ship['carrier'] !== null) {
		  $result->bindValue(':carrier', $ship['carrier'], PDO::PARAM_STR);
		} else {
		  $result->bindValue(':carrier', null, PDO::PARAM_NULL);
		}
		if (isset($ship['service_code']) && $ship['service_code'] !== null) {
		  $result->bindValue(':service_code', $ship['service_code'], PDO::PARAM_STR);
		} else {
		  $result->bindValue(':service_code', null, PDO::PARAM_NULL);
		}
	  $result->bindValue(':tax_rate', $ship['tax_rate']);
	  $result->bindValue(':price_net', $ship['price_net']);
	  $result->bindValue(':price_tax', $ship['price_tax']);
	  $result->bindValue(':price_gross', $ship['price_gross']);
	  $result->execute();
	}


  private function upsertAddressesForOrderId(int $id_order, array $billing, array $shipping): void {
  	$sql = "DELETE FROM orders_addresses WHERE id_module = :id_module";
    $result = $this->pdo->prepare($sql);
    $result->bindValue(':id_module', $id_order, PDO::PARAM_INT);
    $result->execute();

		$sql = "INSERT INTO orders_addresses SET
						id_module = :id_module,
						type = :type, 
						birthday = :birthday,
						email = :email, 
						fullname = :fullname,
						company = :company,
						addressline1 = :addressline1,
						addressline2 = :addressline2,
						zip = :zip,
						city = :city, 
						state = :state,
						country = :country,
						phone = :phone";
		
    $result = $this->pdo->prepare($sql);
    foreach ([$billing, $shipping] AS $address) {
      $result->bindValue(':id_module', $id_order, PDO::PARAM_INT);
      $result->bindValue(':type', $address['type'], PDO::PARAM_STR);
  		if (isset($address['birthday']) && $address['birthday'] !== null) {
      	$result->bindValue(':birthday', $address['birthday'], PDO::PARAM_INT);
      } else {
      	$result->bindValue(':birthday', NULL, PDO::PARAM_NULL);
      }
      $result->bindValue(':email', $address['email'], PDO::PARAM_STR);
      $result->bindValue(':fullname', $address['fullname'], PDO::PARAM_STR);
      $result->bindValue(':company', $address['company'], PDO::PARAM_STR);;
      $result->bindValue(':addressline1', $address['addressline1'], PDO::PARAM_STR);;
      $result->bindValue(':addressline2', $address['addressline2'], PDO::PARAM_STR);
      $result->bindValue(':zip', $address['zip'], PDO::PARAM_STR);
      $result->bindValue(':city', $address['city'], PDO::PARAM_STR);
      $result->bindValue(':state', $address['state'], PDO::PARAM_STR);
      $result->bindValue(':country', $address['country'], PDO::PARAM_STR);
      $result->bindValue(':phone', $address['phone'], PDO::PARAM_STR);
      $result->execute();
    }
  }

  private function copyCartItemsToOrder(int $id_order, array $items): void {
  	$sql = "INSERT INTO orders_items SET
  					id_module = :id_module, 
  					id_product = :id_product, 
  					quantity = :quantity, 
  					tax_rate = :tax_rate, 
  					unit_base_net = :unit_base_net, 
  					unit_base_tax = :unit_base_tax, 
  					unit_addon_net = :unit_addon_net, 
  					unit_addon_tax = :unit_addon_tax,
  					unit_total_gross = :unit_total_gross, 
  					line_tax = :line_tax, 
  					line_total_net = :line_total_net, 
  					line_total_gross = :line_total_gross, 
  					sku = :sku, 
  					title = :title, 
  					addons_json = :addons_json, 
  					properties_json = :properties_json,
  					tags_json = :tags_json";
    $result = $this->pdo->prepare($sql);
    foreach ($items AS $item) {
      $quantity = (int)$item['quantity'];
      $tax_rate = (float)$item['tax_rate'];
      $line_total_net = (float)$item['line_total_net'];
      $line_tax = (float)$item['line_tax'];
      $line_total_gross = (float)$item['line_total_gross'];

      $unit_base_net = (float)$item['price_net'];
      $unit_addon_net = (float)($item['addons_net'] ?? 0.0);
      $unit_base_tax = $unit_base_net * ($tax_rate / 100.0);
      $unit_addon_tax = $unit_addon_net * ($tax_rate / 100.0);
      $unit_total_gross = $unit_base_net + $unit_addon_net + $unit_base_tax + $unit_addon_tax;
      
      $snapshot = $this->buildAttributesI18nForProduct((int)$item['id_product'], $quantity);

      $result->bindValue(':id_module', $id_order, PDO::PARAM_INT);
      $result->bindValue(':id_product', (int)$item['id_product'], PDO::PARAM_INT);
      $result->bindValue(':quantity', $quantity, PDO::PARAM_INT);
      $result->bindValue(':tax_rate', $tax_rate);
      $result->bindValue(':unit_base_net', $unit_base_net);
      $result->bindValue(':unit_base_tax', $unit_base_tax);
      $result->bindValue(':unit_addon_net', $unit_addon_net);
      $result->bindValue(':unit_addon_tax', $unit_addon_tax);
      $result->bindValue(':unit_total_gross', $unit_total_gross);
      $result->bindValue(':line_tax', $line_tax);
      $result->bindValue(':line_total_net', $line_total_net);
      $result->bindValue(':line_total_gross', $line_total_gross);
      $result->bindValue(':sku', $item['sku'] ?? null);
      if ($snapshot['title'] !== null) {
				$result->bindValue(':title', $snapshot['title'], PDO::PARAM_STR);
			} else {
				$result->bindValue(':title', $item['title'] ?? null, $item['title'] ?? null ? PDO::PARAM_STR : PDO::PARAM_NULL);
			}
      $result->bindValue(':addons_json', isset($item['addons_json']) ? $item['addons_json'] : (isset($item['addons']) ? json_encode($item['addons'], JSON_UNESCAPED_UNICODE) : null), isset($item['addons_json']) || isset($item['addons']) ? PDO::PARAM_STR : PDO::PARAM_NULL);
      if ($snapshot['properties'] !== null) {
		    $result->bindValue(':properties_json', $snapshot['properties'], PDO::PARAM_STR);
			} else {
		    $result->bindValue(':properties_json', null, PDO::PARAM_NULL);
			}
			if ($snapshot['tags'] !== null) {
		    $result->bindValue(':tags_json', $snapshot['tags'], PDO::PARAM_STR);
			} else {
		    $result->bindValue(':tags_json', null, PDO::PARAM_NULL);
			}
      $result->execute();
    }
  }

  private function insertPaymentRow(int $id_order, string $provider, $amount, string $currency, ?string $method = NULL): void {
  	$sql = "INSERT INTO orders_payment SET 
  					datetime = :datetime, 
  					id_module = :id_module, 
  					provider = :provider, 
  					method = :method,
  					status = :status, 
  					currency = :currency, 
  					amount = :amount ";
    $result = $this->pdo->prepare($sql);
    $result->bindValue(':datetime', time(), PDO::PARAM_INT);
    $result->bindValue(':id_module', $id_order, PDO::PARAM_INT);
    $result->bindValue(':provider', $provider, PDO::PARAM_STR);
    if ($method === null) $result->bindValue(':method', null, PDO::PARAM_NULL);
    else $result->bindValue(':method', $method, PDO::PARAM_STR);
    $result->bindValue(':status', 'pending', PDO::PARAM_STR);
    $result->bindValue(':currency', $currency, PDO::PARAM_STR);
    $result->bindValue(':amount', $amount);
    $result->execute();
  }
  
   public function updateOrderStatus(int $id_order, string $status, string $changed_by, string $note = ''): void {
    $sql = "UPDATE orders SET status = :status WHERE id = :id";
    $result = $this->pdo->prepare($sql);
    $result->bindValue(':status', $status, PDO::PARAM_STR);
    $result->bindValue(':id', $id_order, PDO::PARAM_INT);
    $result->execute();

    $this->insertStatusHistory($id_order, 'order', $status, $changed_by, $note);
  }
  
  public function updatePaymentStatus(int $id_order, ?string $status = 'pending'): void {
    $sql = "UPDATE orders_payment SET status = :status WHERE id_module = :id_module ORDER BY id DESC LIMIT 1";
    $result = $this->pdo->prepare($sql);
    $result->bindValue(':status', $status, PDO::PARAM_STR);
    $result->bindValue(':id_module', $id_order, PDO::PARAM_INT);
    $result->execute();
  }

  public function insertStatusHistory(int $id_order, string $type, string $status, string $changed_by, string $note = ''): void {
  	$sql = "INSERT INTO orders_status_history SET
  					datetime = :datetime,
  					id_module = :id_module,
  					type = :type,
  					status = :status,
  					changed_by = :changed_by,
  					note = :note ";
    $result = $this->pdo->prepare($sql);
    $result->bindValue(':datetime', time(), PDO::PARAM_INT);
    $result->bindValue(':id_module', $id_order, PDO::PARAM_INT);
    $result->bindValue(':type',  $type, PDO::PARAM_STR);
    $result->bindValue(':status',  $status, PDO::PARAM_STR);
    $result->bindValue(':changed_by',  $changed_by, PDO::PARAM_STR);
    $result->bindValue(':note',  $note, PDO::PARAM_STR);
    $result->execute();
  }
  
  
  public function getOrderForPayment(int $id_order, ?string $language = null): array {
	  $sql = "SELECT id, totals_total_gross, totals_tax, totals_subtotal_net,
	          	(SELECT email FROM orders_addresses WHERE id_module = orders.id AND type='billing' LIMIT 1) AS billing_email
	          FROM orders AS orders WHERE id = :id LIMIT 1";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id', $id_order, PDO::PARAM_INT);
	  $result->execute();
	  $order = $result->fetch(PDO::FETCH_ASSOC) ?: [];
	
		$sql = "SELECT sku, title, quantity, unit_total_gross, line_total_gross FROM orders_items WHERE id_module = :id_module";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id_module', $id_order, PDO::PARAM_INT);
	  $result->execute();
	  $items = $result->fetchAll(PDO::FETCH_ASSOC) ?: [];
	  
	  $lang = $language ?: ($order['locale'] ?? null);
	  
	  foreach ($items AS &$item) {
	    $title = $item['title'] ?? null;
	    $resolved = $this->resolveOrderItemTitle($title, $lang, $item['sku'] ?? null);
	    $item['title_raw'] = $title;
	    $item['title'] = $resolved;
	  }
	  unset($item);
	
	  return ['order' => $order, 'items' => $items];
	}
	
	public function updateLastPaymentMeta(int $id_order, array $meta): void {
	  $sql = "UPDATE orders_payment SET 
	  				method = :method, 
	  				method_brand = :method_brand, 
	  				method_last4 = :method_last4, 
	  				wallet = :wallet, 
	  				provider_ref = :provider_ref
	          WHERE id_module = :id_module 
	          ORDER BY id DESC LIMIT 1";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id_module', $id_order, PDO::PARAM_INT);
	  $result->bindValue(':method', $meta['method'] ?? null);
	  $result->bindValue(':method_brand', $meta['brand'] ?? null);
	  $result->bindValue(':method_last4', $meta['last4'] ?? null);
	  $result->bindValue(':wallet', $meta['wallet'] ?? null);
	  $result->bindValue(':provider_ref', $meta['ref'] ?? null);
	  $result->execute();
	}
	
	private function ensureOrderNumber(int $id_order, ?string $pattern = null): ?string {
		$sql = "SELECT order_number, datetime FROM orders WHERE id = :id LIMIT 1";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id', $id_order, PDO::PARAM_INT);
	  $result->execute();
	  $arr = $result->fetch(PDO::FETCH_ASSOC);
	  if (!$arr) return null;
	  if (!empty($arr['order_number'])) return $arr['order_number'];
	
	  $pattern = $pattern ?: $this->getOrderNumberPattern();
	  $timestamp = (int)($arr['datetime'] ?? time());
	
	  for ($i = 0; $i < 5; $i++) {
	    $candidate = generateFormattedNumber($pattern, $id_order, 'orders', 'order_number', $timestamp);
	
	    $candidate = preg_replace('/[^A-Za-z0-9\-_.]/', '-', $candidate);
	    
	    $sql = "UPDATE orders SET order_number = :order_number WHERE id = :id AND order_number IS NULL";
	    $result = $this->pdo->prepare($sql);
	    $result->bindValue(':order_number', $candidate, PDO::PARAM_STR);
	  	$result->bindValue(':id', $id_order, PDO::PARAM_INT);
	    $result->execute();
	
	    if ($result->rowCount() > 0) return $candidate;
			
			$sql = "SELECT order_number FROM orders WHERE id = :id LIMIT 1";
		  $result = $this->pdo->prepare($sql);
		  $result->bindValue(':id', $id_order, PDO::PARAM_INT);
		  $result->execute();
	    $already = $result->fetchColumn();
	    if (!empty($already)) return $already;
	
	    if (strpos($pattern, '[RAND') === false) {
	      $pattern .= '-[RAND3]';
	    }
	  }
	
	  $fallback = sprintf('%s-%06d-%03d', date('Y', $timestamp), $id_order, random_int(0, 999));
	  
    $sql = "UPDATE orders SET order_number = :order_number WHERE id = :id AND order_number IS NULL";
    $result = $this->pdo->prepare($sql);
    $result->bindValue(':order_number', $fallback, PDO::PARAM_STR);
  	$result->bindValue(':id', $id_order, PDO::PARAM_INT);
    $result->execute();
	
		$sql = "SELECT order_number FROM orders WHERE id = :id LIMIT 1";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id', $id_order, PDO::PARAM_INT);
	  $result->execute();
	  return $result->fetchColumn() ?: $fallback;
	}
		
	private function getOrderNumberPattern(): string {
		$default = 'OR-[YEAR2][MONTH]-[NR4:1001]-[ID6]';
		
		$sql = "SELECT value FROM orders_options WHERE title = 'orders_number' LIMIT 1";
	  $result = $this->pdo->prepare($sql);
		$result->execute();
		$arr = $result->fetch(PDO::FETCH_ASSOC) ?: [];
		
	  $pattern = trim((string)($arr['value'] ?? ''));
	  return $pattern !== '' ? $pattern : $default;
	}
	
	
	private function buildAttributesI18nForProduct(int $id_product, int $quantity): array {
		$languages = [];
		$sql = "SELECT iso FROM languages ORDER BY sortorder ASC";
    $result  = $this->pdo->prepare($sql);
    $result->execute();
    while ($arr = $result->fetch()) {
      $languages[] = $arr['iso'];
    }
    
    $languages = array_values(array_unique($languages));

    if (!$languages) {
      return [
        'title' => null,
        'properties' => null,
        'tags' => null,
      ];
    }
    
	  $titles = [];	
	  $properties = [];
	  $tags = [];
	
	  foreach ($languages AS $language) {	
	    $product_details = new \ProductDetails();
	    $product_details->setConnection($this->pdo);
	    $product_details->setCurrency('EUR', $language, 2);
	    $product_details->setPricingFallbackCountry(1);
	    $product_details->setPricingContext(1, max(1, (int)$quantity));
	    
	    $product_details->setAttributeVisibility('order');
	    $product_details->onlyCheckedAttributes(true, ['properties']);
	    $product_details->setInput(['id' => (int)$id_product]);
	
	    $data = $product_details->getDisplayData();
	    
			$title = trim((string)($data['content']['title'] ?? ''));
      if ($title !== '') {
      	$titles[$language] = $title;
      }
      
	    $tags_data = [];
	    if (!empty($data['attributes']['tags']) && is_array($data['attributes']['tags'])) {
	      foreach ($data['attributes']['tags'] AS $group) {
	        if (!empty($group['values'])) {
	          foreach ($group['values'] AS $value) {
	            $val = trim((string)($value['title'] ?? ''));
	            if ($val !== '') $tags_data[] = $val;
	          }
	        }
	      }
	    }
	    $tags_data = array_values(array_unique($tags_data));
      if ($tags_data) $tags[$language] = $tags_data;
	
	    $properties_data = [];
	    if (!empty($data['attributes']['properties']) && is_array($data['attributes']['properties'])) {
	      foreach ($data['attributes']['properties'] AS $group) {
	        $group_title = trim((string)($group['title'] ?? ''));
	        if ($group_title === '') continue;
	        $values = [];
	        if (!empty($group['values'])) {
	          foreach ($group['values'] AS $value) {
	            $val = trim((string)($value['title'] ?? ''));
	            if ($val !== '') $values[] = $val;
	          }
	        }
	        $values = array_values(array_unique($values));
	        if ($values) {
	          $properties_data[] = ['title' => $group_title, 'values' => $values];
	        }
	      }
	    }
	    
	    if ($properties_data) $properties[$language] = $properties_data;
	  }
	
	  $properties_json = $properties ? json_encode($properties, JSON_UNESCAPED_UNICODE) : null;
    $tags_json = $tags ? json_encode($tags, JSON_UNESCAPED_UNICODE) : null;
    $title_json = $titles ? json_encode($titles, JSON_UNESCAPED_UNICODE) : null;

    return [
	    'title' => $title_json,
	    'properties' => $properties_json,
	    'tags' => $tags_json,
    ];
	}

	private function resolveOrderItemTitle($data, ?string $language, ?string $fallback = null): string {
	  if (is_string($data) && $data !== '') {
	    $decoded = json_decode($data, true);
	    if (json_last_error() === JSON_ERROR_NONE && is_array($decoded)) {
	      if ($language && isset($decoded[$language]) && trim((string)$decoded[$language]) !== '') {
	        return trim((string)$decoded[$language]);
	      }
	      foreach ($decoded AS $value) {
	        if (is_string($value) && trim($value) !== '') return trim($value);
	      }
	    } else {
	      return trim($data);
	    }
	  }
	  return $fallback ? (string)$fallback : 'Item';
	}
	
	
	private function calcCartMetrics(array $items): float {
	  $total = 0.0; 
	  foreach ($items AS $item) {
	    $quantity = max(1, (int)($item['quantity'] ?? 1));
	    $weight = (float)($item['weight'] ?? 0);
	    $total += $weight * $quantity;
	  }
	  return $total;
	}
	
	private function matchZoneByCountryId(int $id_country): ?array {
	  $sql = "SELECT shipping_zones.id, shipping_zones.method_key, shipping_zones.label
	          FROM shipping_zones AS shipping_zones
	          JOIN shipping_zones_countries AS shipping_zones_countries ON shipping_zones_countries.id_zone = shipping_zones.id
	          WHERE shipping_zones_countries.id_country = :id_country LIMIT 1";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id_country', $id_country, PDO::PARAM_INT);
	  $result->execute();
	  $arr = $result->fetch(PDO::FETCH_ASSOC);
	  return $arr ?: null;
	}
	
	private function loadRoutesByMethodAndZone(string $key, int $id_zone): array {
	  $sql = "SELECT shipping_routes.*, shipping_methods.label AS method_label
	          FROM shipping_routes AS shipping_routes
	          JOIN shipping_methods AS shipping_methods ON shipping_methods.id = shipping_routes.id_method
	          WHERE shipping_methods.method_key = :method_key AND shipping_routes.id_zone = :id_zone AND shipping_routes.c_active = '1'
	          ORDER BY shipping_routes.priority ASC, shipping_routes.price_net ASC";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':method_key', $key, PDO::PARAM_STR);
	  $result->bindValue(':id_zone', $id_zone, PDO::PARAM_INT);
	  $result->execute();
	  return $result->fetchAll(PDO::FETCH_ASSOC) ?: [];
	}
		
	private function fitsRouteByWeight(array $route, float $weight): bool {
	  return empty($route['max_weight']) || $weight <= (float)$route['max_weight'];
	}
	
	private function quoteBestShipping(string $key, int $id_country, array $cart): ?array {
	  $zone = $this->matchZoneByCountryId($id_country);
	  if (!$zone) return null;
	
	  $weight = $this->calcCartMetrics($cart['items'] ?? []);
	  $subtotal_net = (float)($cart['totals']['subtotal_net'] ?? 0.0);
	
	  $routes = $this->loadRoutesByMethodAndZone($key, (int)$zone['id']);
	  foreach ($routes AS $route) {
	    if (!$this->fitsRouteByWeight($route, $weight)) continue;
	
	    $net = (float)$route['price_net'];
	    if (!empty($route['free_over_net']) && $subtotal_net >= (float)$route['free_over_net']) {
	      $net = 0.0;
	    }
	    $tax = $net * ((float)$route['tax_rate']/100.0);
	    $gross = $net + $tax;
	
	    return [
	      'method_key'   => $key,
	      'method_label' => $route['method_label'],
	      'carrier'      => $route['carrier'],
	      'service_code' => $route['service_code'],
	      'tax_rate'     => (float)$route['tax_rate'],
	      'price_net'    => $net,
	      'price_tax'    => $tax,
	      'price_gross'  => $gross,
	    ];
	  }
	  return null;
	}

	private function resolveEffectiveCountryIdForCart(?int $id_country): int {
    if ($id_country && $id_country > 0) return (int)$id_country;

    $stash = $this->loadCheckoutStash();
    if (isset($stash['shipping_raw_country']) && $stash['shipping_raw_country'] > 0) return $stash['shipping_raw_country'];
    if (isset($stash['billing_raw_country']) && $stash['billing_raw_country'] > 0) return $stash['billing_raw_country'];

    $cart = $this->cart->get();
    $id_customer = $cart['id_customer'] ?? null;
    if ($id_customer) {
      $addresses = $this->loadCustomerAddresses((int)$id_customer);
      if ($addresses) {
        foreach ($addresses AS $address) {
          if ((string)($address['c_default'] ?? '0') === '1' && ctype_digit((string)$address['id_country']) && (int)$address['id_country'] > 0) {
            return (int)$address['id_country'];
          }
        }
        foreach ($addresses AS $address) {
          if (ctype_digit((string)$address['id_country']) && (int)$address['id_country'] > 0) return (int)$address['id_country'];
        }
      }
    }

    return $this->fallbackCountry > 0 ? $this->fallbackCountry : 1;
  }
  
  public function rebindCheckoutStashToCustomer(int $id_customer): void {
	  $cart = $this->cart->get();
	  $id_cart = $cart['id'] ?? null;
	  if (!$id_cart || $id_customer <= 0) return;
	
	  $sql = "SELECT data_json FROM carts_checkout WHERE id_module = :id_module LIMIT 1";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id_module', $id_cart, PDO::PARAM_INT);
	  $result->execute();
	  $json = $result->fetchColumn();
	  if (!$json) return;
	
	  $data = json_decode($json, true) ?: [];
	  $stash_customer = (int)($data['_meta']['id_customer'] ?? 0);
	  if ($stash_customer === $id_customer) return;
	
	  $addresses = $this->loadCustomerAddresses($id_customer);
	  $default = null;
	  foreach ($addresses AS $address) { if ((string)$address['c_default'] === '1') { $default = $address; break; } }
	  if (!$default && $addresses) $default = $addresses[0];
	
	  if ($default) {
	    $fullname = $default['fullname'] ?? '';
	    $billing = [
	      'type'        	=> 'billing',
	      'email'       	=> $default['email'] ?? '',
	      'fullname'    	=> $fullname,
	      'company'     	=> $default['company'] ?? '',
	      'addressline1'	=> $default['addressline1'] ?? '',
	      'addressline2'	=> $default['addressline2'] ?? '',
	      'zip'         	=> $default['zip'] ?? '',
	      'city'        	=> $default['city'] ?? '',
	      'phone'       	=> $default['phone'] ?? '',
	      'state'       	=> $this->mapState($default['id_state'] ?? ''),
	      'country'     	=> $this->mapCountry($default['id_country'] ?? ''), 
	    ];
	    $shipping = $billing; $shipping['type'] = 'shipping';
	
	    $data['billing'] = $billing;
	    $data['shipping'] = $shipping;
	    $data['billing_raw_state'] = (string)($default['id_state'] ?? '');
	    $data['billing_raw_country'] = (string)($default['id_country'] ?? '');
	    $data['shipping_raw_state'] = (string)($default['id_state'] ?? '');
	    $data['shipping_raw_country']	= (string)($default['id_country'] ?? '');
	    $data['shipping_mode'] = self::MODE_SAME;
	    $data['shipping_address_id'] = (int)($default['id'] ?? 0);
	    $data['c_save_address'] = 0;
	  }
	
	  $data['_meta']['id_customer'] = $id_customer;
	  $data['_meta']['id_cart'] = (int)$id_cart;
	  $data['_meta']['ts'] = time();
	
	  $json = json_encode($data, JSON_UNESCAPED_UNICODE);
	  $sql = "INSERT INTO carts_checkout SET
	  				datetime = :datetime,
	  				id_module = :id_module, 
	  				session_id = :session_id,
	  				data_json = :data_json
	  				ON DUPLICATE KEY UPDATE data_json = VALUES(data_json), datetime = VALUES(datetime)";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':datetime', time(), PDO::PARAM_INT);
	  $result->bindValue(':id_module', $id_cart, PDO::PARAM_INT);
	  $result->bindValue(':session_id', $cart['session_id'] ?? '');
	  $result->bindValue(':data_json', $json);
	  $result->execute();
	}

	private function getOrderShippingRow(int $id_order): ?array {
	  $sql = "SELECT method_key, method_label, tax_rate, price_net, price_tax, price_gross, carrier, service_code
	          FROM orders_shipping WHERE id_module = :id LIMIT 1";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id', $id_order, PDO::PARAM_INT);
	  $result->execute();
	  $arr = $result->fetch(PDO::FETCH_ASSOC) ?: null;
	  return $arr ?: null;
	}
	
	private function lockStashToOrder(int $id_order): void {
	  $cart = $this->cart->get();
	  $id_cart = $cart['id'] ?? null;
	  if (!$id_cart) return;
	
	  $sql = "SELECT data_json FROM carts_checkout WHERE id_module = :id LIMIT 1";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id', $id_cart, PDO::PARAM_INT);
	  $result->execute();
	  $current = $result->fetchColumn();
	  $data = $current ? (json_decode($current, true) ?: []) : [];
	
	  $ship = $this->getOrderShippingRow($id_order);
	  $data['_lock'] = ['order_id' => $id_order];
		if ($ship) { 
			$data['_lock']['shipping'] = $ship;
		}

	  $json = json_encode($data, JSON_UNESCAPED_UNICODE);
	
	  $sql = "INSERT INTO carts_checkout SET
	          datetime = :datetime, 
	          id_module = :id_module, 
	          session_id = :session_id, 
	          data_json = :data_json
	          ON DUPLICATE KEY UPDATE data_json = VALUES(data_json), datetime = VALUES(datetime)";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':datetime', time(), PDO::PARAM_INT);
	  $result->bindValue(':id_module', $id_cart, PDO::PARAM_INT);
	  $result->bindValue(':session_id', $cart['session_id'] ?? '');
	  $result->bindValue(':data_json', $json);
	  $result->execute();
	}


}
